这是个比喻,我们经常会用已知的知识来推断未知的内容,在Excel中我们经常用MIN、MAX函数进行数据处理,还有很多巧妙的运用,例如用MIN、MAX函数来替代IF函数来做判断,使我们的公式更加简洁:
这里的A,B如果用其他的计算公式来替换,你就会发现,我们少写了一倍的代码,每个公式都少写了一遍。
到了Power Query中,我们仍然想使用这样的方法时,我们发现 Number 类的目录下,并没有Number.Min、Number.Max函数,但是Power Query这么强大,怎么可能没有呢?原来比较大小这类的函数被放到了List类中,不但有MIN、MAX更有LARGE、SMALL函数:
- List.Max:MAX
- List.MaxN:LARGE
- List.Min:MIN
- List.MinN:SMALL
我们通过一个小例子来表述一下:
计算加班时间:
这里就有个问题,上班时间与8:00之间的关系,下班时间与17:00的关系,用我们Excel的思维就是:
- MAX(TIME(8,0,0),上班时间)
- MIN(TIME(17,0,0),下班时间)
然后再来计算加班时长。
那么在Power Query中就要用List.Min函数和List.Max函数来替换一下:
- List.Max({#time(8,0,0),上班时间})
- List.Min({#time(17,0,0),下班时间})
如果我们不用MIN 、MAX这种方法,就要用if … then … else …语句来写。
另外Duration.TotalHours函数可以用来统计加班时长的小时数,我们还需要一个把小数时长圆整到0.5小时的方法:
使用Number.IntegerDivide函数与Number.Mod函数就可以实现了,等下我们在公式中就可以看到了。
当加班发生在平时(周一至周五)
这个比较简单,我们直接用下班时间减去17:40这个加班开始时间就可以了,为了防止出现负值,用List.Max替代if判断,取最小是0值。
- 当下班时间大于17:40时,就会得到加班的小时数
- 当下班时间小于17:40时,加班时间为0
= List.Max({0,
Duration.TotalHours(#time(20,43,0)-#time(17,40,0))})
这里第一个#time就是下班时间。
加班时间向0.5小时圆整
我们可以做一个自定义函数来处理这个半值圆整:
我们用上面的查询结果来测试一下:
3.05小时圆整成3小时。
当加班发生在周末(周六、周日)
为了便于理解,我们预先设定两个时间:
- 上班时间:7:48
- 下班时间:20:41
我们直接写出这个计算公式,其实加班时间由三个部分构成:
- 上午到12:00的时间
- 下午13:00到下班时间(下班时间小于17:40)
- 晚上17:40到加班结束
= fx(List.Max({0,Duration.TotalHours(下班时间-#time(17,40,0))})
+Duration.TotalHours(List.Min({#time(17,0,0),下班时间})-#time(13,0,0))+Duration.TotalHours(#time(12,0,0)-List.Max({#time(8,0,0),上班时间})))
最外层的数值圆整就不用看了,里面的三个部分:
晚间加班时间直接用平时加班的计算方法就可以了
List.Max({0,Duration.TotalHours(下班时间-#time(17,40,0))})
下午加班:
Duration.TotalHours(List.Min({#time(17,0,0),下班时间})-#time(13,0,0))
我们用到了前面说的List.Min
上午加班:
Duration.TotalHours(#time(12,0,0)-List.Max({#time(8,0,0),上班时间}))
同样的用到了List.Max
我们修改一下上下班时间来看看计算结果:
- 上班时间:8:10
- 下班时间:19:41
计算结果是9.5小时。未圆整前是9.85小时,当然了既然是加班,加班的人肯定会自己会计算好加班的时段,不会犯这种错误,明明再多加班几分钟就可以到10小时了。
通过这个例子,我们用List.Min函数和List.Max函数简单的做了二选一的判断,如果使用if判断,这个公式的长度还要增加很多。