开工大吉！Excel巧算连续打卡最长的天数
标签：Excel公式练习
打卡，已经成为我们这个时代的热词。上班打卡，下班打卡，跑步打卡，健身打卡，阅读打卡……，确实，通过打卡能够激励我们坚持做一件事情。然而，有时由于各种原因，我们可能偶尔会中断打卡，那么，我们要知道在打卡记录中，连续打卡（也就是坚持）的最长天数，应该怎么做呢？这就是今天我们想要用公式来解决的问题。
示例数据如下图1所示。TRUE表示打开成功，FALSE表示没有打卡。
图1
注：你可以到知识星球完美Excel社群下载示例数据工作簿，也可以自己制作一个。示例数据来源chandoo.org。
希望使用公式得到：
1.连续打卡的最长天数
2.最长坚持打卡是从哪里开始的
这里的关键是找出由TRUE变到FALSE或者由FALSE变到TRUE的位置。可以错位比较，找出单元格值变化的位置。
各块开始的位置：
SMALL(IF(C4:C244<>C5:C245,B5:B245),B5:B245)各块结束的位置：
SMALL(IF(C6:C245<>C5:C244,B5:B244),B5:B244)两者相减，即为各块长度：
SMALL(IF(C6:C245<>C5:C244,B5:B244),B5:B244)-SMALL(IF(C4:C244<>C5:C245,B5:B245),B5:B245)其中，有错误值，使用IFERROR函数将其转换为0：
IFERROR(SMALL(IF(C6:C245<>C5:C244,B5:B244),B5:B244)-SMALL(IF(C4:C244<>C5:C245,B5:B245),B5:B245),0)取其最大值，加上1，即为连续最长天数。
=MAX(IFERROR(SMALL(IF(C6:C245<>C5:C244,B5:B244),B5:B244)-SMALL(IF(C4:C244<>C5:C245,B5:B245),B5:B245),0))+1这是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。
下面求开始位置。
在得到由各区间天数组成的数组后：
IFERROR(SMALL(IF(C6:C245<>C5:C244,B5:B244),B5:B244)-SMALL(IF(C4:C244<>C5:C245,B5:B245),B5:B245),0)在其中查找最长天数出现的位置：
MATCH(F6-1, IFERROR(SMALL(IF(C6:C245<>C5:C244,B5:B244),B5:B244)-SMALL(IF(C4:C244<>C5:C245,B5:B245),B5:B245),0),0)然后在数组：
IF(C4:C243<>C5:C244,B5:B244)
中查找最小值，即为最长天数开始的位置
完整的公式为：
=SMALL(IF(C4:C243<>C5:C244,B5:B244),MATCH(F6-1, IFERROR(SMALL(IF(C6:C245<>C5:C244,B5:B244),B5:B244)-SMALL(IF(C4:C244<>C5:C245,B5:B245),B5:B245),0),0))
这是一个数组公式，输入完后要按Ctrl+Shift+Enter组合键。
如果我们使用名称，那么公式将会更简单。
名称：id
引用位置：B5:B244
名称：list
引用位置：C5:C244
名称：size
引用位置：=COUNTA(list)
名称：list.a
引用位置：=OFFSET(list,-1,0,size)
名称：list.b
引用位置：=OFFSET(list,1,0,size)
这样，求出连续打卡最长天数公式：
=MAX(IFERROR(SMALL(IF(list.b<>list,id),id)-SMALL(IF(list.a<>list,id),id), 0))+1
最长天数开始位置：
=SMALL(IF(list.a<>list,id),MATCH(F6-1,IFERROR(SMALL(IF(list.b<>list,id),id)-SMALL(IF(list.a<>list,id),id), 0),0))
注意，以上都是数组公式，输入完后要按Ctrl+Shift+Enter组合键。
Bug：本示例假设连续打卡的天数总是大于连续中断的天数，否则上述结果不正确。
下面是更酷的公式。
连续打卡最长天数：
=MAX(FREQUENCY(IF(list,id),IF(list,,id)))
最长天数开始位置：
=MATCH(F20,FREQUENCY(IF(list,id),IF(list,,id)),)-F20
这些都是数组公式，输入完后要按Ctrl+Shift+Enter组合键。
欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。