excel停机时间计算公式 Excel如何计算连续停机天数的问题

前几天有个朋友问了一个关于连续时间统计的问题，使用Power Query for Excel来解决的。这个问题是一个非常经典的例子，在许多的编程的案例甚至面试中都会遇到。

关于SQL的解决方案与思路，具体可以拜读俊红老师的文章：

文章链接：

张俊红，公众号：俊红的数据分析之路Sql如何统计连续打卡天数

这类连续的问题主要是有连续打卡时间，连续登陆时间统计，连续迟到时间统计、连续购买、连续乘坐、连续感染新冠病毒、连续服务时间等。

今天来讲的是与俊红老师的文章的题目稍微有所不同，但是都是属于同一类问题。

将左侧的数据处理成右侧的数据。即计算每个车队每个对应的各个自编号的车辆的停机的开始日期与结束日期，最后计算停机的天数。

这个问题，我们使用Excel中的Power Query功能来解决。

首先，将数据从Excel中加载至Power Query中。修改日期的类型。（如果时间不是顺序的，先对日期进行排序）

其次，使用Table.Group函数的第4个参数的作用，局部分组的作用来进行处理数据。这里先放出M函数的代码看看。

上述公式中最核心的步骤就是"分组"这一步骤。这个步骤里面主要的核心的部分是添加索引列以及判断列。即下面这两分部分代码：

添加索引列：

a=Table.AddIndexColumn(_,"index",0,1)

添加判断列：

b=Table.AddColumn(a,"判断", (x)=> x[停机日期]-#duration(x[index],0,0,0))

添加的判断的列的有原理就是比较当前行中的日期与上下两个行中的日期是否连续。然后生成一列日期，最后再使用Table.Group函数的第4个参数为0时（局部分组）对每一个连队下面的自编号的车辆进行分组。

Table.Group( [ a=Table.AddIndexColumn(_,"index",0,1 ), b=Table.AddColumn(a,"判断", (x)=> x[停机日期]-#duration(x[index],0,0,0)) ] [b], "判断", { "s", (y)=> [ 开始停机日期=List.Min(y[停机日期]), 结束停机日期=List.Max(y[停机日期]), 天数=Number.From(结束停机日期-开始停机日期 ) 1 ] } ,0 )

最后使用List.Max函数与List.Min函数取出最大值与最小值作为开始停机日期与结束停机日期，停机天数是最大日期减去最小日期再加1.

[ 开始停机日期=List.Min(y[停机日期]), 结束停机日期=List.Max(y[停机日期]), 天数=Number.From(结束停机日期-开始停机日期 ) 1 ]

对于这个问题，还可以有其他的方法也可以实现这个问题，可以不用在第二个Table.Group函数中使用Table.AddIndexColumns函数（使用List.Positions函数代替）。但是使用到了Table.Group函数的第5个函数，作用是对于分组的条件进行判断。

所以整个公式还可以写成：

上面的这个问题还可以再做变形，即要连续的天数在1天以上的（不含1天），这样的问题只需要在最后将天数为1的筛选掉即可。

在Excel中对于这类问题的处理，也可以根据上面的思路来使用函数与数据透视表来完成这个问题。

如果素材的小伙伴，可以在后台联系小必老师。

,