【已解决】excel中用公式实现单元格内容的条件判断:根据单元格内容长度执行不同的逻辑

【背景】

之前折腾过了:

【记录】Excel内置函数实现两列的时间相减以及条件格式化

但是现在遇到一个问题:

H2(以及同列的值),除了8.12之类的值之外,还可能出现8这样的值,所以公式就失效了。

希望可以去用公式实现条件判断,实现对应的时间差的计算。

 

【解决过程】

1.参考:

[筆記]Excel IF 語法範例說明

知道IF的写法:

Excel IF 函數公式說明:
IF(logical_test,[value_if_true],[value_if_false])

2.参考:

Create conditional formulas

搞懂如何做逻辑判断

Formula
Description (Result)

=AND(A2>A3, A2<A4)
Is 15 greater than 9 and less than 8? (FALSE)

=OR(A2>A3, A2<A4)
Is 15 greater than 9 or less than 8? (TRUE)

=NOT(A2+A3=24)
Is 15 plus 9 not equal to 24? (FALSE)

=NOT(A5="Sprockets")
Is A5 not equal to "Sprockets"? (FALSE)

=OR(A5<>"Sprockets",A6 = "Widgets")
Is A5 not equal to "Sprockets" or A6 equal to "Widgets"? (TRUE)

 

3.参考:

How to read characters Count “Text Length” for Excel cell?

得知如何获得单元格内容长度:

=LEN(A1)

Where A1 is the Cell whose text length you want.

 

4.然后最后去用公式:

=($F2 - TIME(VALUE(MID($H2,1,1)),IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0)*60/100, 0))*24

得到要的效果。

(1)公式语法详解:

=($F2 – TIME(VALUE(MID($H2,1,1)),IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0)*60/100, 0))*24

= ($F2 – H2单元格所表示的时间值)*24

H2单元格所表示的时间值

=TIME(VALUE(MID($H2,1,1)),IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0)*60/100, 0))

=TIME(小时,分钟, 秒)

    小时

    =VALUE(MID($H2,1,1))

    =VALUE( $H2单元格的内容,从第一个算起,长度为1个 )

    分钟

    =IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0)*60/100

    =IF(判断条件,条件为真时的值,条件为假时的值)

        判断条件

        =LEN($H2)>1

        =H2单元格的长度是否大于1

        条件为真时的值

        =VALUE(MID($H2,3,2))

        =VALUE(需要转换为数值的文本字符)

            需要转换为数值的文本字符

            =MID($H2,3,2)

            =H2单元格中,从第三个算起,长度为2,的文本内容

        条件为假时的值

        =0

    秒

    =0

 

(2)公式含义解释:

完整含义:

将F2单元格的(时间)值,减去H2单元格的时间值,得到对应的excel中的时间差值(其是一个介于0-1的小数)

然后乘于24,得到对应的以小时为单位的差值

其中的H2单元格的时间值:

可能为x.yz,比如8.12

或x,比如8

所以

获得小时的数字字符,可以直接通过:

MID($H2,1,1)

而得到然后再通过:

VALUE(MID($H2,1,1))

将数字字符转换为数字的值

而获得分钟和秒的值,则需要用:

IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0)

去判断:

当H2的值的长度大于1:

LEN($H2)>1

时,即x.yz的形式时,才去用:

VALUE(MID($H2,3,2))

得到分钟所对应的数字值

否则,对于x,即8的形式,比如分钟是0,所以直接写为0

最后,将组合出来的,分别对应着,小时,分钟,秒的值:

VALUE(MID($H2,1,1))

IF(LEN($H2)>1, VALUE(MID($H2,3,2))

0

放到TIME中:

TIME(VALUE(MID($H2,1,1)),IF(LEN($H2)>1, VALUE(MID($H2,3,2)), 0)*60/100, 0)

而得到H2所表示的时间的值

 

(3)最终效果为:

excel cell formula calc time diff support h2 two mode

 

【总结】

excel中的函数,还是不错的。



发表评论

电子邮件地址不会被公开。 必填项已用*标注

无觅相关文章插件,快速提升流量