多对多的数据查询技巧你掌握了吗？如果还不会，立在墙角打屁屁！

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

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

多对多的数据查询是我们应对日常工作的必备技能之一。这不，小明同学有遇到了工作中这样一个问题。

题目要求我们统计在 1月2日到1月8日间，市场专员P0867签订的订单数量；在同一天内重复项按一次计算。

多么熟悉的味道！这就是一个多条件提取不重复项及计数的问题！利用FREQUENCY函数，可以轻松地解决问题！

01

统计订单总数

在单元格N6中输入公式“=COUNT(0/FREQUENCY(ROW(A:A),MATCH($A$3:$A$60,$A$3:$A$60,)*($I$3:$I$60>=$N$2)*($I$3:$I$60<=$N$3)*($D$3:$D$60=$N$4)))-1”即可。

思路：

  • MATCH($A$3:$A$60,$A$3:$A$60,)部分，返回在单元格区域$A$3:$A$60中每个订单的位置信息。如有重复订单，则返回该订单第一次出现时的位置

  • ($I$3:$I$60>=$N$2)*($I$3:$I$60<=$N$3)*($D$3:$D$60=$N$4)部分，分别是题目中的三个条件

  • 以上整个部分的含义是，返回所有满足条件的数据的位置信息，其结果是{1;1;1;0;0;0;0;0;0;0;0;12;12;0;0;0;0;0;0;20;20;20;20;20;20;20;0;0;0;0;31;31;33;33;33;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;54;0;0;0;0}

  • 接下来FREQUENCY()部分，在上述的内存数组中对ROW(A:A)计频，实际上就是对从1开始的自然数序列计频，计频的结果是{1;0;0;0;0;0;0;0;0;0;0;11;0;0;0;0;0;0;0;8;0;0;0;0;0;0;0;0;0;0;11;0;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;21;0;0;0;0;1048522}，它说明小于等于1的数有1个；小于等于12 的数有11个…；大于58的数有1048522个。FREQUENCY函数会将大于计频区间最高值数字也统计并计频，因此FREQUENCY函数的结果永远比计频区间多1个

  • 0/ FREQUENCY()部分，其作用是将全部的0值转换为错误值，为接下啦的COUNT函数计数做准备

  • COUNT函数忽略错误值，统计结果为7。但是要注意，此时COUNT函数多统计了一个，就是那个1048522，因此这里要减去多统计的一个

02

统计客户来源

在单元格M9中输入公式“=LOOKUP(0,0/FREQUENCY(1,($I$3:$I$60>=$N$2)*($I$3:$I$60<=$N$3)*($D$3:$D$60=$N$4)-COUNTIF($M$8:M8,$H$3:$H$60)),$H$3:$H$60)&""”，并向下拖曳即可。

思路：

  • ($I$3:$I$60>=$N$2)*($I$3:$I$60<=$N$3)*($D$3:$D$60=$N$4)部分，是所有的条件

  • COUNTIF($M$8:M8,$H$3:$H$60)部分是这个公式中的精华。在动态的单元格区域$M$8:M8中查找$H$3:$H$60中的数据，查到了就返回1，查不到就返回0

  • 以上两部分相减，去除了重复值，其结果为{1;1;1;0;0;0;0;0;0;0;0;1;1;0;0;0;0;0;0;1;1;1;1;1;1;1;0;0;0;0;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0}

  • 利用FREQUENCY函数在上述的区间中对“1”计频，结果为{1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

  • 后面利用LOOKUP函数可以返回第一个来源清单

  • 当鼠标拖曳到单元格M10时，计频的结果为{0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}，最终函数返回的是第二个不重复的清单。后面的依次类推。

客户来源的公式还可以写成下面的形式：

“=LOOKUP(0,0/FREQUENCY(1,ISNA(MATCH($H$3:$H$60,$O$8:O8,))*($I$3:$I$60>=$N$2)*($I$3:$I$60<=$N$3)*($D$3:$D$60=$N$4)),$H$3:$H$60)&""”，小伙伴们如果有兴趣可以试着分解一下这个公式。有问题可以私信我哦！

03

订单数量统计

在单元格N9中输入公式“=COUNT(0/FREQUENCY(ROW(A:A),MATCH($A$3:$A$60,$A$3:$A$60,)*($I$3:$I$60>=$N$2)*($I$3:$I$60<=$N$3)*($D$3:$D$60=$N$4)*($H$3:$H$60=M9)))-1”，并向下拖曳即可。

这个公式和上面订单总量那个公式是一样的道理。只不过这里增加了一个条件$H$3:$H$60=M9，分别统计了每个客户来源的订单数量。这里就不在对公式做详细解释了。

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

本期阅读分享赠书书目为：

中文版Excel 2016宝典（第9版）

此书亦是我了解2016版的用书。内容详实充分，适用于小白进阶，也同样适用于作为高手的伴手书。

赠书规则：

  • 本公众号下文章“阅读最多”排名和“分享最多”排名各自第一名的朋友将会获赠一本

  • 截止时间：2021-4-11

我就知道你“在看”

戳原文，更有料！免费模板文档！

推荐阅读
赞 (0)

相关推荐