Excel中有一个几乎“万能”的函数组合,在N多种问题的解决过程中都少不了她的身影,她就是Index+small+if组合!
这个Excel万金油组合在工作中应用广泛,我专门挑选了她的几种典型应用,扩展大家的思路和方法。
今天的内容涉及数组运算,还未晋升到中级的同学们可能不易理解,不过没关系,大家可以先收藏教程,工作中按此套用。
1 从单列提取不重复值
在黄色区域输入公式,提取a列的不重复值
定义名称
a =OFFSET(数据源!$A$2,,,COUNTA(数据源!$A:$A)-1)
C2单元格输入以下数组公式,按组合键
=IFERROR(INDEX(A:A,SMALL(IF(MATCH(a,a,)=ROW(a)-1,ROW(a),4^8),ROW(A1)))&"","")
2 从单列提取重复值
在黄色区域输入公式,提取a列的重复值
定义名称
a =OFFSET(数据源!$A$2,,,COUNTA(数据源!$A:$A)-1)
C2单元格输入以下数组公式,按组合键
=IFERROR(INDEX(A:A,SMALL(IF(MATCH(a,a,)=ROW(a)-1,4^8,ROW(a)),ROW(A1)))&"","")
3 按要求提取数据
结合前两种技术,配合Excel中的控件,可以定制工作中的数据查找模板,如下图
定义名称
a =OFFSET('1'!$A$2,,,COUNTA('1'!$A:$A)-1)
C2单元格输入以下数组公式,按组合键
=IFERROR(INDEX(A:A,SMALL(IF(B$1=1,IF(MATCH(a,a,)=ROW(a)-1,ROW(a),4^8),IF(MATCH(a,a,)=ROW(a)-1,4^8,ROW(a))),ROW(A1)))&"","")
4 一对多查找所有符合条件的数据
E2单元格输入以下数组公式,按组合键
=INDEX(B:B,SMALL(IF(A$2:A$11=D$2,ROW($2:$11),4^8),ROW(A1)))&""
5 比对并提取两列数据的差异记录
定义名称
a =OFFSET('1'!$A$2,,,COUNTA('1'!$A:$A)-1)
b =OFFSET('1'!$B$2,,,COUNTA('1'!$B:$B)-1)
D2单元格输入以下数组公式,按组合键
=IFERROR(INDEX(a,SMALL(IF(COUNTIF(b,a),4^10,ROW(a)-1),ROW(A1))),"")
F2单元格输入以下数组公式,按组合键
=IFERROR(INDEX(b,SMALL(IF(COUNTIF(a,b),4^10,ROW(b)-1),ROW(A1))),"")
师父领进门,修行在个人,除了以上几种应用,Index+small+if组合还有很多小奥妙等待你在实际运用过程中慢慢发现,那种惊喜的感觉还是留给有心人慢慢体味吧~
本文公众号 Excel函数与公式 友情推荐
正式课时间:2017-6-1 — 6-27
讲师:三土
课程详情:http://t.excelhome.net/thread-45015-1-1.html
免费报名公开课:Excel数据之美-公开课QQ群 596409684