【辅助列解决实际问题_系列11】查找符合条件的全部信息

cleverzhzhf

在做查询的时候，有两个经典并且常用的函数：
一个是拯救万千文员于水火种的VLOOKUP
一个是万金油的经典组合INDEX+MATCH

可这两组都有一个共同的特点，那就是它们都是查找第一个符合条件的值。
而如果需要列出所有符合条件的值怎么办呢？
很简单，使用辅助列，简化复杂思路，瞬间搞定。

三国公司销售部销售电器，刘备、曹操、孙权三人的销量情况如下：
现在需要做一个查询表，通过选择不同的姓名，将此人的全部销售记录查询出来。
公式如下：

1. E2：=COUNTIF($A$1:A2,$H$1)
   
2. G3：=IFERROR(INDEX(A:A,MATCH(ROW(1:1),$E:$E,)),"")

复制代码

查找符合条件的全部信息.zip (10.4 KB, 下载次数: 80)

2016-3-28 11:19 上传

点击文件名下载附件

---

不使用辅助列，一个公式搞定的方式：
在G2输入以下数组公式，并向右向下拖动：

1. =IFERROR(INDEX(A:A,SMALL(IF($A$2:$A$30=$H$1,ROW($A$2:$A$30)),ROW(1:1))),"")

复制代码

cleverzhzhf

公式解读：
第一步：=COUNTIF($A$1:A2,$H$1)
在E列对A列的姓名做统计，从第一行开始，截止到目前该姓名出现了几次。
细节：$A$1:A2
第一个参数，用图钉把A1单元格按住，当E2单元格的公式向下拖动，统计的区域逐渐变成：$A$1:A3，$A$1:A4，$A$1:A5……
其它可达到类似效果的公式，如：
=MAX($E$1:E1)+(A2=$H$1)

第二步：=IFERROR(INDEX(A:A,MATCH(ROW(1:1),$E:$E,)),"")
首先是MATCH(ROW(1:1),$E:$E,)
由于E列得到的统计全部是数字，而且利用MATCH的特点，只匹配第一次出现的位置。
即由ROW生成的1、2、3……第一次出现的位置
之后由INDEX分别提取相应的信息，最后是IFERROR屏蔽掉错误值。

lfspecter

小翟老师，好久不见，又有好东西分享了。搬个凳子，学习。

东奇冰雪7

搬板凳 学习中

ZhouFfett

据我所知，这并不是效率最高的公式