在一列数据中，如何将连续出现至少N次的任意数识别并标识出来，以下面这张图为例，1、4、7连续出现了至少3次以上，就用红色将其标识出来。实际上这类“连续同质数据检测”问题在日常的工作中经常会涉及，典型的应用场景包括：
- 质量管控报表中连续几天出现不合格品的识别问题；
- 公司考评连续几年考核为优秀者工资涨幅定级问题；
- 排班的时候不能一人同时连续几天值班问题。
- 新冠肺炎疫情下某地连续多少天无新增可降低风险等级问题。
所以咱们今天就以这个基本问题为例，探讨一下这类问题的解法。
说实话，这个问题看似简单实际上并不是十分容易，当然了如果用VBA来做的话思路还是十分清晰的，但从来都坚信一条Excel界的信条：“能用公式搞定的尽量不用VBA”，所以本着“刁难自己成全粉丝”的原则，决然走上了一条用公式解决这个问题
第一步：思路分析（以上面这个图为例，即N=3的时候）
当一个数连续出现至少3次（如3次、4次、5次等）的时候，该连续数列中的任一单元格（以下称目标单元格）来说，它必须得满足以下三种条件中的任一种，而反过来，一个单元格中的数如果满足以上三个条件之一，该数肯定连续出现至少3次以上。（V表示目标单元格单元格数值， 和-分别表示该单元格后/前单元格）
- 目标单元格与它下面两个单元格数值相等，即V=V ,V=V 。
- 目标单元格与它上下两个单元格数值相等,即V=V-,V=V 。
- 目标单元格与它上面两个单元格数值相等，即V=V-,V=V--。
以上三种情况简单示意如下，绿色格表示目标单元格，红色格表示与目标单元格相等的单元格，绿色格位置的不同对应以上三种情况。
这三种情况对于任何一个满足条件的连续数来说，只可能是其中一种，因此这三种情况逻辑上应该是“或”的关系，而每种情况中的两个条件都是必须同时满足的，即逻辑上应该是“与”的关系，所以自然而然的我们想到了用逻辑函数中的OR和AND来构建我们公式的基础框架。
第二步：公式构建
有了第一步的分析，我们知道了公式的基础架构大致应该是这样的：
OR（情况1，情况2，情况3）
紧接着将每种情况进行细化：
OR(AND(条件1，条件2)，AND(条件1，条件2)，AND(条件1，条件2))
最后是将条件进行细化，判断一个单元格与前后单元格的关系，小伙伴们想到了哪个函数？聪明如你，一定第一时间想到了我们神通广大的OFFSET函数,不错，就是它：
OR(AND($A3=OFFSET($A3,1,0),$A3=OFFSET($A3,2,0))，AND(条件1，条件2)，AND(条件1，条件2))
写到这里，想必小伙伴们想必可以顺利地补充完公式剩下的部分了（注意：因为A1和A2单元格上面只有0个和1个单元格，所以OFFSET函数用于A1和A2单元格的时候会提示无效引用，所以这里我们的数据列从A3单元格开始）：
=OR(AND($A3=OFFSET($A3,1,0),$A3=OFFSET($A3,2,0)),AND($A3=OFFSET($A3,-1,0),$A3=OFFSET($A3,1,0)),AND($A3=OFFSET($A3,-1,0),$A3=OFFSET($A3,-2,0)))
到这里公式的部分就算完成了，最后就是如何用我们构建的公式给单元格上色了（不给它点颜色看看，它还真以为公式搞不定它……）。
第三步：用条件格式“给单元格来点颜色看看”
选中A3:A16单元格>条件格式>新建格式规则>使用公式确定要设置格式的单元格，在输入框中输入我们刚才构造的公式，然后在格式中选择用来上色的颜色，如下图所示，点击确定，是不是得到了我们想要的效果？
以上是针对最简单的相同数检测给出了解法，具体到实际工作场景中，比如连续几天出现不达标产品的异常检测问题（产品质量连续3天小于90视为质量管控异常），如下图所示，
这时候我们的公式就变成了下面这样子：
=OR(AND($B2<90,OFFSET($B2,1,0)<90,OFFSET($B2,2,0)<90),AND($B2<90,OFFSET($B2,-1,0)<90,OFFSET($B2,1,0)<90),AND($B2<90,OFFSET($B2,-1,0) <90,OFFSET($B2,-2,0) <90))
到这里本期的教程就结束了，小伙伴们可以用以上的方法尝试一下当N=4时的公式，不过小编要提醒一下的是，随着N的增大，公式复杂度是成倍增长的，所以当N比较大的时候，还是推荐大家上VBA。
评论 (0)