EXCEL，所谓的分组查询的万金油数组公式, index() + small() + if() 套路，也可以用数据透视表实现
分类:
文章
•
2025-02-27 20:44:47
1 使用数组公式
1.0 感想，这些公式都需要太多小技巧了，很麻烦
-
公式这么复杂， 用了很多奇怪技巧后对非专职研究EXCEL的人来说失去意义了
-
一般人能这么做，也就是写成一个工具表后，平时很少改了，
-
不实用了，实用性不如用 透视表，或VBA
1.1 公式从内层逐层解析
- IFERROR(INDEX($A$2:$A$14,SMALL(IF($C$2:$C$14=$F$2,ROW($1:$13),99),ROW(A1))),"")
- 第1层 ----------IF
- IF($C$2:$C$14=$F$2,ROW($1:$13),99)----生成一个新的数组，数组元素内容就是其行号。
- 如果查到第1个元素符合，那么就取 往上-1的行号，如果没查到，就取99
- ROW($1:$13)为什么从1开始? 我认为必须从1开始的原因是excel里 range 或数组 序号必须从1开始?
- 为什么取99，因为现在列里就10来个元素，99够用，写999也没问题，据说也可以写成9^9 很大的这种数。
- 第2层--------small
- SMALL(IF($C$2:$C$14=$F$2,ROW($1:$13),99),ROW(A1))
- 所以 row(a1) row(a2) 就是起到1,2,3 自动化的作用，small取处理过的数组里的，第1，第2，第3小的值
- 第3层----index 取出数组里的前几位的元素，small()---变相成为了index
- INDEX($A$2:$A$14,small(99,1)) ---最小的index
- INDEX($A$2:$A$14,small(99,2)) ---第2小的index
- 第4 层
- iferror(,"")
- 因为数据的第1行，数组公式是自己写的 ROW(A1)
- 而数据的第2行，是往下拖的 ROW(A2) ROW(A3)等
- 避免错误值不好看
参考
2 利用F9看公式内的计算中间结果
3 使用数据透视表
