请选择 进入手机版 | 继续访问电脑版

地球村私人订制

 找回密码
 立即注册

扫一扫,访问微社区

搜索
热搜: 活动 交友 discuz
查看: 663|回复: 1

第2课【根据指定条件提取不重复内容】(20181202)

[复制链接]
回帖奖励 2 金钱 回复本帖可获得 2 金钱奖励! 每人限 1 次
发表于 2018-12-2 23:57:41 | 显示全部楼层 |阅读模式
本帖最后由 一村之长 于 2018-12-3 00:49 编辑

今天要跟大家分享的案例是早上群里一个朋友提的一个需求,村长简单模拟了一下数据,如下图:
1.jpg
要求是根据指定的商品类别,然后查找A列商品类别对应的品牌,其实这就是数组函数中比较常用的一对多查询,但是这里增加了一点难度,需要将查找到的品牌去除重复值,村长给出的方法比较暴力,数组公式,虽然很长,但是应该比较好理解
2.jpg
  1. =IFERROR(INDEX(B:B,SMALL(IF(($A$2:$A$15=$F$2)*(MATCH($A$2:$A$15&$B$2:$B$15,$A$2:$A$15&$B$2:$B$15,0)=ROW($2:$15)-1),ROW($2:$15),9^9),ROW(A1))),"")
  2. 数组公式,CTRL+SHIFT+回车键三键结束公式
复制代码
可能有朋友会觉得这个公式实在太长了,看都看不懂,那么有没有简单一点的呢?
3.jpg
辅助列比较多,所有辅助列公式如下:
判断类别
  1. =A2=$J$2
复制代码
判断类别的型号是否第一次出现
  1. =MATCH($A$2:$A$15&$B$2:$B$15,$A$2:$A$15&$B$2:$B$15,0)=ROW($2:$15)-1
  2. 数组公式,CTRL+SHIFT+回车三键结束
复制代码
类别型号唯一(同时满足类别和第一次出现两个条件)
  1. =E2*F2
复制代码
所在行号(满足条件返回所在行号,不满足返回9^9)
  1. =IF(G2,ROW(A2),9^9)
复制代码
型号(最终结果公式)
  1. =IFERROR(INDEX(B:B,SMALL($H$2:$H$15,ROW(A1))),"")
复制代码

备注:9^9是一个超出工作簿最大行号的数值,作为INDEX返回行号引用时会因为无效行号得到错误值,我们经常需要用到这个方法去将不符合我们需求的数据排除掉,而符合我们需求的数据通过其所在行号返回其内容。上面通过辅助列多个公式得到的结果与前面直接数组公式得到的结果是一样的,多个辅助公式层层嵌套就得到了一个完整的数组公式,而最前面的完整数组公式也可以拆分成一个个较为简单的函数公式,想要真正理解和学好数组公式,一定要学会理解拆分和嵌套的关系。


每日一练
根据指定的商品类别和品牌,查找所有对应的不重复型号
4.jpg

第2课【根据指定条件提取不重复内容】(20181202).zip (13.52 KB, 下载次数: 4, 售价: 2 金钱)
 楼主| 发表于 2018-12-3 22:20:01 | 显示全部楼层
每日一练公式:
  1. =IFERROR(INDEX(C:C,SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2)*(MATCH($A$2:$A$18&$B$2:$B$18&$C$2:$C$18,$A$2:$A$18&$B$2:$B$18&$C$2:$C$18,0)=ROW($2:$18)-1),ROW($2:$18),9^9),ROW(A1))),"")
复制代码

数组公式,CTRL+SHIFT+回车键三键结束
回复

使用道具 举报

Archiver|手机版|小黑屋|地球村私人订制

GMT+8, 2019-3-27 09:22 , Processed in 0.163287 second(s), 26 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表