怒赞一个!这些经典的EXCEL套路技巧都学会,你就是办公室里最靓的仔!
- 2026-11-05 07:26:07
欢迎转发和点一下“在看”，文末留言互动!
置顶公众号或设为星标及时接收更新不迷路
小伙伴们好，今天和大家分享一道按条件查询的题目。
小时候我们都有个这样的经历：在学校或家庭中如果表现优异，就会得到一朵小红花以示表彰。连续得到小红花的时间越长，越说明好的表现是一以贯之和持续的，就越会受到他人的羡慕和表扬。
而今天的这道题目就是这样的。我们要找到一贯优秀的同学来。
源数据中连续获得6次优秀的已加粗显示了。有什么好的办法找到他们吗？
01
FREQUENCY函数
这类题目可以使用FREQUENCY函数来解决。
在单元格A13中输入下列公式，三键确认并向下拖曳即可。
=IF(MAX(FREQUENCY(IF($B2:$K2="优秀",COLUMN(B2:K2)),IF($B2:$K2<>"优秀",COLUMN(B2:K2))))>5,A2,"")简单介绍一下这条公式。
IF($B2:$K2="优秀",COLUMN(B2:K2))找出“优秀”对应的列号。
IF($B2:$K2<>"优秀",COLUMN(B2:K2))找出非“优秀”对应的列号。
FREQUENCY(IF($B2:$K2="优秀",COLUMN(B2:K2)),IF($B2:$K2<>"优秀",COLUMN(B2:K2)))利用FREQUENCY函数来对“优秀”计频。计频的结果就是“优秀”连续出现的次数。
IF(MAX(FREQUENCY(IF($B2:$K2="优秀",COLUMN(B2:K2)),IF($B2:$K2<>"优秀",COLUMN(B2:K2))))>5,A2,"")剩下的就简单了。找出计频结果中的最大值和5作比较，并用IF函数返回相应的结果。
02
直接查找6次的记录
下面这条公式思路非常新颖。
首先选中单元格区域A13:A18并输入下列公式，三键确认并向下拖曳即可。
=REPT(A2:A7,1-ISERR(FIND(111111,MMULT(N(B2:K7=$E$2),10^ROW($1:$10)))))这里巧妙使用了REPT函数、FIND函数和MMULT函数。
N(B2:K7=$E$2)判断源数据中那些等于“优秀”，并用N函数将逻辑值“TRUE”转换为1，“FALSE”转换为0。
MMULT(N(B2:K7=$E$2),10^ROW($1:$10))利用MMULT函数矩阵求和。以第二行为例，它返回的就结果是{111110000;10111111100;11111100000;11111001110;11101111110;11100111000}。
FIND(111111,MMULT(N(B2:K7=$E$2),10^ROW($1:$10)))接下来就可以用FIND函数在上面这个内存数组中来查找“111111”了。这里“111111”就表示了连续6次。
1-ISERR(FIND(111111,MMULT(N(B2:K7=$E$2),10^ROW($1:$10))))这部分简单说就是将数值转换为“TRUE”，错误值转换为“FALSE”。
REPT(A2:A7,1-ISERR(FIND(111111,MMULT(N(B2:K7=$E$2),10^ROW($1:$10)))))最后REPT函数根据第二参数的结果来返回答案。
03
COUNTIF函数
这条公式和第二条相似。
在单元格A13中输入下列公式，三键确认并向下拖曳即可。
=REPT(A2,OR(COUNTIF(OFFSET(A2,,COLUMN($A:$F),,6),"优秀")=6))一句话解释：
利用COUNTIF函数来统计OFFSET函数生成的区域中“优秀”的个数，并返回正确答案。
04
几行简单代码
最后，我们也可以用VBA来做这道题目。
完整代码如下：
Sub test2()Dim i As Integer, s As Variant, arr As Variant, brr As Variant, crr As Variantbrr = Range("A1").CurrentRegionReDim crr(1 To UBound(brr) - 1, 1 To 1)For i = 2 To UBound(brr)arr = Range(Cells(i, 2), Cells(i, 11))s = Join(Application.Transpose(Application.Transpose(arr)), "")If InStr(s, "优秀优秀优秀优秀优秀优秀") > 0 Thencrr(i - 1, 1) = brr(i, 1)End IfNext[G13].Resize(UBound(crr)) = crrEnd Sub
这段代码相对比较简单，只有两点需要讲一下。
arr = Range(Cells(i, 2), Cells(i, 11))我们可以用Cells(i, 2)和Cells(i, 11)来表示一个单元格对象的起、止单元格。这样，就可以利用参数i的变化来定位到不同的行。
s = Join(Application.Transpose(Application.Transpose(arr)), "")对于一个一行的二维数组，我们除了可以使用循环的方法将行内的数据合并为一个一维字符串之外，还可以连续2次使用Transpose将他们合并为一个一维的字符串。
05
循环方法
下面这段则是利用循环来完成的。
完整代码如下：
Sub test()Dim i As Integer, j As Integer, k As Integer, arr As Variant, brrarr = Range("A1").CurrentRegionReDim brr(1 To UBound(arr), 1 To 1)For i = 2 To UBound(arr)For j = 2 To UBound(arr, 2)If arr(i, j) = "优秀" Thenk = k + 1If k > 5 Thenbrr(i - 1, 1) = arr(i, 1)End IfElsek = 0End IfNextk = 0Next[E13].Resize(UBound(arr), 1) = brrEnd Sub
一句话解释：
这段代码的核心思路就是，循环，遇到“优秀”就对变量k累计加1。当k大于5时，对动态数组brr赋值。
我就知道你“在看”
