经典函数组合SMALL+IF+MATCH的应用实例

点击上方

蓝色

文字  关注我们吧!

送人玫瑰,手有余香,请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径!

前一段时间和一些朋友们聊天,谈到了经典的SMALL+IF+MATCH函数的组合应用。下面我们就通过这样一个例子来重温一下它们的经典妙用吧!

如上图,如何利用函数,去重列出部门,并在部门下列出姓名?

01

方法一

部门公式:

在单元格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函数屏蔽错误

02

方法二

部门公式:

在单元格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三键回车,向右向下拖曳即可。

思路:

  • 部门部分思路和上例基本相同。

  • 人员部分增加了“等于当前部门”这个条件,并返回所对应的位置

  • 其余部分思路相同

03

方法三

部门部分:

在单元格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操作问题时不再迷茫无助

推荐阅读:

VLOOKUP函数竟然也可以合并同类项,快来看看吧!

两列数据各有重复,如何筛选唯一对应关系?

听说VLOOKUP函数要退休了,MAX函数要夺权上位!

利用EXCEL制作的抽奖工具

我能熟练运用的8组逆天的函数组合,你会几个?

戳原文,更有料!免费模板文档!

(0)

相关推荐

  • 统计带空白单元格的区域中不重复值的个数

    下表记录的是一些人的信息,包含姓名,年龄和职务.其中有些人没有职务,就是空白单元格.求不重复职务的个数.通过动图的演示,可以看到不重复的职务有5个.如何通过函数计算出来呢? 假如没有空白单元格,很多小 ...

  • 查找不重复记录的几个套路(删除重复项)

    今天和小伙伴们分享几个查找不重复记录的套路,这个不重复的记录相当于用删除重复项这个功能,比如表中有2个相同的名字,只取第一次出现的记录.也就是如果一条记录重复出现多次,只取第一次出现的记录. -01- ...

  • 逆透视:二维表转一维表!你可能会用到!

    你好,我是刘卓.欢迎来到我的公号,excel函数解析.逆透视是power query中的功能,非常的好用,可以将表头的信息转化到记录中,常用的就是将二维表转为一维表.今天就来分享下如何用函数做出逆透视 ...

  • 12个经典函数组合

    12个经典函数组合

  • Excel中八大经典函数组合,帮你整理齐了!

    在Excel公式中,有些函数是独行侠,而有些函数经常是成对出现的,今天兰色就带大家一起盘点Excel表格中八大经典函数组合. 1.IF + And 作用:并列多条件判断 [例]如下图所示,在C列设置公 ...

  • 一对多查询经典函数组合再例

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 这个帖子分享的也是网友问我的一个真实例子.如上图,怎样将供应商清单用公式转 ...

  • 一对多查询经典函数组合对比并提取两列数据差异

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 对比两列数据差异,我们使用最多的方法是条件格式.今天向大家分享一个函数的方 ...

  • 一对多查询经典函数组合拓展应用--多对多查询

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 小伙伴们好,前一段时间我有几篇帖子专门介绍了一对多数据查询的经典函数组合, ...

  • 一对多查询经典函数组合的实例应用

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 前两天看到这样一个操作,感觉非常有意思,现在分享给大家.如上图,人员可以根 ...

  • 经典的MATCH函数+INDEX函数组合

    在Excel中,MATCH函数和INDEX函数是一对非常经典的组合,我们经常能够在Excel公式中看到他俩的"身影".MATCH函数返回查找值在单元格区域或者数组中的位置,INDE ...

  • Excel经典查找引用函数公式组合:Index+Match

    学会这189套Excel组合公式,比你苦干三年都重要!

  • 必学函数组合INDEX MATCH,比VLOOKUP函数好用100倍

    工作中,我们常常会使用VLOOKUP来进行各种各样的查找,但有时候问题并不简单,用VLOOKUP函数实现比较难,这时候我们就可以考虑使用其它方法,比如我今天要重点跟大家讲解的INDEX+MATCH函数 ...