EXCEL一对多条件查找显示多个结果.doc

EXCEL一对多条件查找显示多个结果（INDEX SMALL IF ROW函数组合） 2017-09-19?? L罗乐?? 来源??阅?263??转?3 转藏到我的图书馆 微信分享： 【INDEX SMALL IF ROW】组合函数： ①难度：★★★☆☆ ②作用：实现查找时返回多个符合条件的结果。 ③结果放在行的写法： INDEX(结果列,SMALL(IF(条件,ROW(条件列),4^8),COLUMN(A1))) ④结果放在列的写法： INDEX(结果列,SMALL(IF(条件,ROW(条件列),4^8),ROW(A1))) ⑤结束键：数组公式须同时按CTRL SHIFT ENTER三键结束。 这个组合函数在简单的报表里用得不频繁，所以每到用时方恨练得少。接下来直接举“栗子”啦，想要进阶的同学多练习几遍，能从头到尾自己打代码把公式写下来才算真正掌握！ 温馨提示：书写公式必须把输入法调整到英文半角状态，否则显示出错。 例：A、B、C列是源数据，要求在E列提取A列“省份”数据（重复的只保留一个），并查找各省份对应的城市（在B列找），把结果放到同一行不同单元格里。 01 原始表 02 结果表 03 操作过程 04 步骤分解 ①在E列提取A列省份（重复的只留一个） 方法1：复制A列粘贴到E列，EXCEL2007以上版本可直接点菜单栏“数据”，然后点击“删除重复项”，简单粗暴就OK了。 方法2：INDEX MATCH（上例用的是此法，详细的下次再探讨） E2输入公式 =INDEX(A:A,SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($A$2:$A$13)-1,ROW($2:$13),4^8),ROW(1:1))) 按CTRL SHIFT ENTER三键结束，下拉。 ②在F2单元格里为E2省匹配B列中对应的第1个城市 F2输入公式 =INDEX($B:$B,SMALL(IF($A$2:$A$13=$E2,ROW($A$2:$A$13),4^8),COLUMN(A1))) 三键结束。 ③把F2的公式右拉，分别匹配E2省对应的第2、3、4、5个城市...直到显示空白。 ④把E2右边存放有公式的单元格选中后下拉，为E3、E4单元格里的省份匹配到对应的各个城市。 ⑤附加题：为省份匹配非省会的城市 公式的写法同上面一样道理，只是增加了一个条件——C列等于否。 那就在第②点的公式里插入一个新条件 =INDEX($B:$B,SMALL(IF(($A$2:$A$13=$E2)*($C$2:$C$13=否),ROW($A$2:$A$13),4^8),COLUMN(A1))) 同样三键结束。记得哦，增加条件后该增加的括号()也得成对增加哈~ ⑥同理，如有更多条件要求，比照第⑤点用*星号连接插入相关条件即可。 05 公式翻译 先安利F9这个功能键：如果公式很长，在编辑栏抹黑某段公式，按F9可以得到公式结果解析。 尤其像INDEX SMALL IF ROW这种组合函数，由多个函数嵌套组合在一起，得先理解各个函数的语法结构及功能作用，才能理解整个组合函数的工作原理。上图瞧瞧： 1-1 1-2 2-1 2-2 遇到复杂的公式，可以按F9解析难理解的中间步骤。现在先来翻译F2单元格的公式。 INDEX($B:$B,SMALL(IF($A$2:$A$13=$E2,ROW($A$2:$A$13),4^8),COLUMN(A1))) 翻译： IF(A列省份若是“广东”,返回那一行的行号,不是“广东”则返回一个很大的数65536) SMALL(IF()得到的行号数组从小到大排列,第几小的行号) INDEX(城市列,SMALL()得到的行信息) 由此可以得到 INDEX(城市列,A列省份是“广东”对应的第1个城市) INDEX(城市列,A列省份是“广东”对应的第2个城市) INDEX(城市列,A列省份是“广东”对应的第3个城市) INDEX(城市列,A列省份是“广东”对应的第N个城市)