送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好,今天和大家分享一则在工作中经常会遇到的问题。有时候,一个数据会重复存在于多个行或列中,而我们有需要将它找出来,例如下面这个例子。
我们知道,确定一个重复值可以使用COUNTIF函数,那么,这道题目是否可以使用COUNTIF函数呢?
COUNTIF函数的的确确可以使用在这里。
在单元格F2中输入公式“=IFERROR(INDEX($A$1:$A$6,SMALL(IF(COUNTIF($A$1:$D$6,$A$1:$A$6)=4,ROW($A$1:$D$6)),ROW(A1)),),"")”,三键回车后向下拖曳即可。
思路:
利用COUNTIF函数在单元格区域$A$1:$A$6中统计$A$1:$D$6中的值
利用IF函数做一个逻辑判断,凡是统计结果等于4的,都返回对应的行号
SMALL函数从小到大依次返回行号
INDEX函数返回重复的数据
这个其实就是一个一对多查询的经典应用公式。
借助MMULT和OFFSET函数也可以达到相同的目的。
在单元格F2中输入公式“=IFERROR(INDEX($A$1:$A$6,SMALL(IF(MMULT(COUNTIF(OFFSET($A$1:$A$6,,COLUMN($B:$D)-1),$A$1:$A$6),ROW($1:$3)^0)=3,ROW($A$1:$D$6)),ROW(A1))),"")”,三键回车并向下拖曳。
思路:
OFFSET函数,向右分别移动1列、2列、3列
COUNTIF函数对新生成的内存数组在单元格区域$A$1:$A$6计数
利用MMULT函数进行矩阵乘积
利用IF函数做逻辑判断,凡是乘积结果等于3,则返回对应的行号
余下的部分就很简单了,和上面的是一样的
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
联系客服