我们遇到的很多问题都是围绕查找来进行的,下面这个有趣的问题也是。
示例数据如下图1所示,其中顾客列中有很多重复输入,项目列也是。
图1
注:示例数据来源于chandoo.org。
我们要获取:
1.指定顾客对应的购买数量中,出现次数最多的数量(最常购买的数量值)。
2.指定顾客对应的购买项目中,出现次数最多的项目(最常购买的项目)。
我们以G列中指定的顾客为例,来编写公式。
第1个问题
首先获取指定顾客对应的购买数量组成的数组,然后取其中出现最多的数值,因此,公式为:
=MODE(IF(G4=B4:B270,D4:D270,""))
这是一个数组公式,输入完成后要按Ctrl+Shift+Enter组合键。
这个问题我只想到了这个公式。
第2个问题
首先找出指定顾客对应的购买项目,不对应的位置使用行号填充,然后查找这些项目所在的位置,查找到的相同位置最多的就是最常购买的项目位置,最后将其取出来,公式如下:
=INDEX(C4:C270,MODE(MATCH(IF(B4:B270=G4,C4:C270,ROW(INDIRECT("1:"&COUNTA(B4:B270)))),IF(B4:B270=G4,C4:C270,ROW(INDIRECT("1:"&COUNTA(B4:B270)))),0)))
还有一些其它的公式,其原理类似。
=INDEX(C4:C270,MODE(IF(B4:B270=G5,MATCH(C4:C270,C4:C270,0))))
或者:
=INDEX(C4:C270,MATCH(TRUE,COUNTIFS(C4:C270,C4:C270,B4:B270,G6)=MAX(COUNTIFS(C4:C270,C4:C270,B4:B270,G6)),0))
或者:
=INDEX(C4:C270,MATCH(MAX(COUNTIFS(B4:B270,G7,C4:C270,C4:C270)),COUNTIFS(B4:B270,G7,C4:C270,C4:C270),0))
这些都是数组公式,输入完成后要按Ctrl+Shift+Enter组合键。
你还有更好的方法吗?欢迎留言分享。
注:可以在知识星球完美Excel社群中下载本文配套示例工作簿。