「Excel」一组“万金油”公式 的前世今生

“万金油”公式

在excel中有一个“万能”的函数组合，它们就是Index+small+If+row函数，它们被称之为“万金油”公式，主要就是因为这一组“万能”函数组合在工作中应用广泛，在此对“万金油”公式构造原来进行剖析。

问题一：一对多查询，要查询名称叫“李明”对应的产品有哪些？

分析：如果能够知道名称叫“李明”单元格对应的行数，使用INDEX函数就可以找出对应的产品值。

公式1：ROW函数

ROW(单元格)表示返回对应单元格的行号，例如=ROW(B4),返回值为“4”；

ROW(1:4)返回数组{1;2;3;4}

公式2：SMALL函数

SMALL（数组，N），表示在一列数值中，按从小到大的顺序取第n个值。

例如：=SMALL(A1:A10,5)，意思是，从A1:A10的10个数值，从小到大取第5个。

公式3:如何构造一函数，对符合条件的单元格，返回对应单元格行号

例如下表，A列中名称为“李明”单元格的行号可以用以下公式：

=SMALL(IF($A$2:$A$10=$D$2,ROW($1:$9)),ROW(A1))，

此公式是数组公式，需要按 Ctrl+Shift+Enter 结束公式

套上INDEX函数就可以查询出对应的产品值：

公式4：INDEX函数

=INDEX（引用数组，N）返回值为引用数组中第N行（列）单元格的数值

例如：上表中=INDEX（B2:B10，2），返回值'西瓜';

回到“一对多问题”

=INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,ROW($1:$9)),ROW(A1)))

对错误值显示为空值

=IFERROR(INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,ROW($1:$9)),ROW(A1))),' ')

问题二：筛选显示不重复的数值

如下表，如何筛选不重复的名称数值

公式1：MATCH函数

MATCH（查找对象，指定查找的范围或者数组，查找方式）

查找的方式主要有三种：

0、1、-1，分别表示精确匹配、升序查找、降序查找模式。

注：MATCH函数查找只会返回区域中首个匹配的数据，所以重复出现的数据都只有一个位置。

IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($1:$9),ROW($1:$9),20) 用MATCH函数查找A2:A16中数据在A2:A16区域中出现的位置。再用IF函数判断MATCH函数查找出的位置是否等于所在的行号，如果等于则返回所在的行号，如果不等于则返回一个比数据行号要大的一个数字，然后用INDEX+SMALL函数提取出数字。

取除错误值：

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,0)=ROW($1:$9),ROW($1:$9),20),ROW(A1))),' ')

这个万精油公式，核心的几个思路都在这了，举一反三，会有更多的应用哦。