在使用EXCEL时经常会碰到一个表里的同一款产品每天的销量都不一样,然后我们需要查导出每一款产品每天的销量,即一对多条件查找。这个教复杂,我们要用到好几个函数的综合,下面小编来教你吧。
工具/原料
- EXCEL软件(本文使用2016版)
方法/步骤
-
打开需要操作的数据表,如图,需要将表格1中的数据按照表二中的产品来将该产品每日的销量显示在表三中。
-
首先,由于产品对应多列,所以我们需要插入一个辅助列将产品进行一个区分,鼠标右击,选择“插入”,如图
-
用COUNTIF公式来实现产品的区分,我们输入“=COUNTIF($C$2:$C2,C2)”,我们会发现此时返回的是数字1,2,3,4,因此我们需要在COUNTIF前加上产品名称,用&符号连接,即“=C2&COUNTIF($C$2:$C2,C2)”,回车并拖动复制函数后,显示的结果为A1,A2,A3,B1。。。
-
接下来,因为匹配的产品为A1、A2,因此我们可以用到ROW函数(返回选择某一单元格的行数)。当输入“=ROW(A1)”时返回的是数字1,所以,我们需要在函数前加入G2中的产品,即“=$G$2&ROW(A1)”【注意产品名为绝对引用】,即可返回产品名称A1、A2、A3等等
-
接下来要进行返回函数的输入了,用VLOOKUP函数进行查找,查找值为“$G$2&ROW(A1)”,查找区间可以设置为A列到D列(绝对引用:$A:$D),因为为多列查找,所以用COLUMN函数来设置查找列数,因此输入公式为“=VLOOKUP($G$2&ROW(A1),$A$D,COLUMN(B1),0”,回车。
-
如果此时进行拖动复制时,我们发现销售额列也显示的是日期格式的数据,所以,我们可以复制G6单元格后选择复制函数,即可返回正确的数据。
-
然后我们发现后面两行显示的是“#N/A”,我们想让它不显示,那么此时可用到IFERROR函数,即“=IFERROR(VLOOPKUP($G$2&ROW(A1),$A$D,COLUMN(B1),0),"")”""即错误值显示为空值。回车,复制函数,即可得到正确的返回,如图。
-
我们改动G2单元格里的产品名称时,表三会返回相应产品的数据。
-
注意事项
- 最终公式为“=IFERROR(VLOOPKUP($G$2&ROW(A1),$A$D,COLUMN(B1),0),"")”
- 注意各个函数的使用
- 注意需绝对引用的数据
-