invalid column index是什么意思_INDEX+IF+SMALL函数组合解决一对多匹配问题

作者：音子，微信公众号：自在不思量，转载请注明出处。

有时候，你永远想象不到，当你不提供模板或者规定标准就开始向许多人去收集信息时，你收到的信息的样式会有多么千奇百怪、不可思议。
下面，我要介绍一个还（bu）算（tai）友好的案例。
有一次，我遇到了一个这样的问题。我们拿到手的信息是这个样子的：

然而，我们最终需要将信息汇总成这个样子：

数据少时，Ctrl+C再Ctrl+V就解决了。当数据比较多时，又该怎么办呢？
一个比较快的解决办法就是使用INDEX+IF+SMALL函数组合公式来进行一对多匹配。

INDEX+IF+SMALL函数组合

1 先说整个公式怎么写

如图所示，待处理的数据位于A、B两列，处理结果存储在D~H列。
首先，在D2单元格中输入如下公式： =INDEX($A:$B,SMALL(IF($A$1:$A$25=D$1,ROW($A$1:$A$25)),ROW(A1)),2)
然后，按Ctrl+Shift+Enter组合键完成输出，结果如下：

注意：图中公式两侧的花括号不是手打上去的，而是按组合键输出的结果。
最后，将鼠标停留在D2单元格右下角时，指针会变成实心小十字“＋”，此时按下鼠标左键向右、再向下拖动，完成公式填充，结果如下：

注意：#NUM！表示公式或函数中某个数字有问题，在这个案例中是因为只有三个值，所以在输出第四行及以后的结果时会报错。
删掉 #NUM！所在的行，就将信息汇总完毕啦~2 搞明白为什么这么用
示例中D2单元格中的完整公式：=INDEX($A:$B,SMALL(IF($A$1:$A$25=D$1,ROW($A$1:$A$25)),ROW(A1)),2)
首先，可以看到，这个组合公式共涉及了4个函数，它们的标准解释分别是：
① INDEX(array，row_num，column_num)
在给定的单元格区域中，返回指定行列交叉处单元格的值或引用。
② SMALL(array,k) 返回数组中第 k 个最小值。
③ IF(logical_test,value_if_true,value_if_false) 判断是否满足某个条件，如果满足返回一个值，如果不满足返回另一个值。
④ ROW(reference) 返回一个引用的行号。
然后，我们将这个组合公式由内向外分解，它的作用可以分为以下三步：第一步，使用公式“=IF($A$1:$A$25=D$1,ROW($A$1:$A$25)) ”判断 D1 也就是“工号”与 A1：A25 这个区域的每一个值是否相同，如果相同，返回其所在行号。结果得到“工号”所在的行号集合，我们暂且称其为“行号数组”。
我们可以试一下在单元格中输入
=IF($A$1:$A$25=D$1,ROW($A$1:$A$25)) ，并按F9，看到的结果是这样的：={1;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE;13;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}（该数组内共有25个值）第二步，使用公式“=SMALL(IF($A$1:$A$25=D$1,ROW($A$1:$A$25)),ROW(A1))”返回“行号数组”中第 1 个最小的行号，结果为 1。
注意：当这个公式位于示例图中的D2单元格中时，返回第1个最小值，将该公式向下拖动填充至D3、D4时，公式中 ROW(A1) 随之变为 ROW(A2)、ROW(A3)，分别返回第2个、第3个最小值，即返回的结果分别为7和13。 第三步，使用完整公式“=INDEX($A:$B,SMALL(IF($A$1:$A$25=D$1,ROW($A$1:$A$25)),ROW(A1)),2)”返回A、B两列区域中行号为1，列号为2的单元格中的值，结果得到 101。
其中，列号固定为2的原因是我们可以明确想要匹配的结果位于B列，即第2列。
最后，为什么将这个公式复制到其它单元格中后，得到的结果不都是“101”，而能够得到我们想要的所有结果呢？这涉及到另一个知识点：绝对引用和相对引用。
例如，A1单元格的相对引用表示为“A1”；绝对引用表示为“$A$1”；当我们想要仅引用它的列号、需要行号能够变化时，引用方式为“$A1”。
不同引用方式的灵活使用需要在练习中自行体会。3 再填一组试试

完美~
要时常练习的吖~本文对应的Excel练习文件分享地址
链接：https://pan.baidu.com/s/1O731wqEghPer_BsQTuW30g
提取码：
mucy
--------------- End ---------------
可能对你有帮助
Excel中{1,2,3,4,5}和{1;2;3;4;5}有什么区别?
Excel中内容太多时，如何折叠行和列方便查看？