经典函数组合SMALL+IF+MATCH的应用实例
点击上方
蓝色
文字 关注我们吧!
送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
前一段时间和一些朋友们聊天,谈到了经典的SMALL+IF+MATCH函数的组合应用。下面我们就通过这样一个例子来重温一下它们的经典妙用吧!
如上图,如何利用函数,去重列出部门,并在部门下列出姓名?
方法一
部门公式:
在单元格H2中输入“=IFERROR(INDEX($A$3:$A$18,SMALL(IF(MATCH($A$3:$A$18,$A$3:$A$18,0)=ROW($A$3:$A$18)-2,ROW($A$3:$A$18)-2),COLUMN(A2))),"")”并CTRL+SHIFT+ENTER三键回车,向右拖曳即可。
人员公式:
在单元格H3中输入“=IFERROR(INDEX($B$3:$B$18,SMALL(IF(MATCH($B$3:$B$18,$B$3:$B$18,0)=ROW($B$3:$B$18)-2,IF($A$3:$A$18=H$2,ROW($B$3:$B$18)-2)),ROW(A1))),"")”并CTRL+SHIFT+ENTER三键回车,向右向下拖曳即可。
思路:
利用MATCH=ROW函数确定不重复的部门/人员在数据区域内的位置
利用IF函数返回该位置信息
在人员公式中,又一次利用IF函数返回了当前部门下人员的位置信息
INDEX函数返回部门和人员名称
IFERROR函数屏蔽错误
方法二
部门公式:
在单元格H2中输入“=INDEX($A:$A,SMALL(IF(MATCH($A$3:$A$18,$A$3:$A$18,)=ROW($A$3:$A$18)-2,ROW($A$3:$A$18),4^8),COLUMN(A1)))&""”
并CTRL+SHIFT+ENTER三键回车,向右拖曳即可。
人员公式:
在单元格H3中输入“=INDEX($B:$B,SMALL(IF(IFERROR(MATCH(IF($A$3:$A$18=D$3,$A$3:$A$18)&$B$3:$B$18,$A$3:$A$18&$B$3:$B$18,),4^8)=ROW($B$3:$B$18)-2,ROW($B$3:$B$18),4^8),ROW(A1)))&""”
并CTRL+SHIFT+ENTER三键回车,向右向下拖曳即可。
思路:
部门部分思路和上例基本相同。
人员部分增加了“等于当前部门”这个条件,并返回所对应的位置
其余部分思路相同
方法三
部门部分:
在单元格I3中输入“=IFNA(INDEX($A:$A,MATCH(,COUNTIF($H3:H3,$A3:$A18),)+2),"")”,CTRL+SHIFT+ENTER,向右拖曳即可。
人员部分:
在单元格I3中输入“=IFNA(INDEX($B:$B,MATCH(,COUNTIF(I$3:I3,IF($A$3:$A$18=I$3,$B$3:$B$18,I$3)),)+2),"")”,CTRL+SHIFT+ENTER,向右向下拖曳即可。
此例巧妙地利用的COUNTIF函数来解决了去重问题。
此例的应用技巧比较复杂,暂时不理解没有关系,记住会使用就可以了!
文章推荐理由:
经典的SMALL+IF+MATCH的函数组合应用技巧。
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
推荐阅读:
戳原文,更有料!免费模板文档!