首页   

夸爆！哪位Excel高人琢磨出的这个数据整理技巧，太有用了！

秋叶PPT  · PPT  · 5 天前

正文

点击关注 【秋叶 Excel】👆

发送【7】领 1000 篇 Excel 精选教程！

图片

作者：小花
编辑：卫星酱


大家好，这里是秋叶编辑部~


今天的分享，是来自一位地产营销人的提问。

「我想根据来访登记表，自动晾晒实时到访人次榜单，能做到吗？」


案例说明：

上图中，需对 C 列进行条件计数，根据结果从大到小依次获取对应置业顾问姓名。

我们姑且称这一问题为「分组统计并排序问题」。


该问题十分复杂，其难点至少包括以下三点：

❶ 必须进行条件计数，但计数的条件值需要从数据列表中获取，除非使用辅助列，否则没有现成的人员清单可供引用；

❷ 人员是多次重复的，不同置业顾问的到访次数也可能是重复，但求值结果中的人名都必须是唯一的，公式需有去重功能；

❸ 必须完成按到访次数大小排序，再索引计数值对应的置业顾问姓名文本，实现数值到文本的转换。

分组统计并排序问题在不同 Excel 版本中有不同的解题公式，其难易也不尽相同，接下来，小花就为大家逐一讲解。

如果你被数据统计逼疯，想学点技能提高工作效率？


🔥那你一定要加入《秋叶 Excel 3 天集训营》，不仅有名师教你表格排版+数据处理+工作汇报，还有助教随时答疑，助你更快掌握 Excel~


《秋叶 Excel 3 天集训营》
原价 99 元
今天 限时免费 

赶紧长按下图识别二维码
抢占学习名额
开启你的高效办公之旅吧！

👇👇👇

图片

INDEX+MOD+LARGE 法


此方法适用于 Excel 2019 及以下版本，仅使用常见的几个「老函数」组合，即可实现对复杂去重排序问题的求解。

但其理解难度颇大，需要小伙伴们沉心静气，跟随小花的拆解，慢慢消化其中的知识点。

公式：
=INDEX($C:$C,MOD(LARGE(COUNTIF($C$2:$C$300,$C$2:$C$300)*(COUNTIF(INDIRECT("C2:C"&ROW($C$2:$C$300)),$C$2:$C$300)=1)+ROW($C$2:$C$300)%%,ROW()-2),1)*10000)


公式说明：

▲ 点击查看大图

❶ COUNTIF($C$2:$C$300,$C$2:$C$300)

该片段执行一组条件计数 COUNTIF 运算，分别以 C2:C300 的每一个单元格为条件值，以其本身为计数范围，统计 C2:C300 中每一个值出现的次数，即每个置业顾问的客户到访次数。


不言而喻，此处的到访次数数组中的每个值都是多次重复的，出现频数即为重复次数。

❷ COUNTIF(INDIRECT("C2:C"&ROW($C$2:$C$300)),$C$2:$C$300)=1

同样执行一组条件计数 COUNTIF 运算，遍历 C2:C300，通过 INDIRECT 函数构建一个从 C2 到当前单元格的引用区域作为计数范围，再使用 COUNTIF 函数统计当前值在单元格范围中出现的次数。

由于计数范围总是包含当前单元格，其结果必然≥1；

如果 COUNTIF 函数的返回值为 1，则说明，当前单元格是首次出现该值的位置；

如果大于 1，则说明在该单元格之上，已经出现过该值了。

最后将 COUNTIF 函数的返回值与 1 进行对比，将数值转化为逻辑值，所有的 TRUE 值刚好标记出每个唯一值首次出现的位置。


❸ ①*(②)

由于①频数数组会多次重复，无法直接通过 LARGE 函数取排位值；

而②为逻辑数组，仅首次出现位置处为 TRUE（计算时为 1），其余为 FALSE（计算时为 0）；

于是①*②刚好实现对①的去重，实现仅首次出现位置保留有效频数，其余均为 0。


至此，LARGE 函数已经具备了发挥作用的条件，但如何将唯一的频数值与所在行号挂钩，实现第 k 大数值中包含其位置值信息呢？

❹ ③+ROW($C$2:$C$300)%%

ROW(C2:C300)返回一组行号值，两个%%等同于除以 10000，将它转化为小数，再与③相加，既不影响频数值之间的大小排序，又能指示当前值位置信息。


❺ LARGE(④，ROW()-2)

ROW()-2 返回一个 k 值，F3 单元格的 k 为 1，F4 单元格的 k 为 2，逐次增大，而 LARGE 函数依次取④中第 k 大的值。


❻ MOD(⑤,1)*10000

此处是对片段④的反运算，通过对 1 取余再乘以 10000，换算出被两个%%转化为小数的 ROW(C2:C300)的行号值。


❼ INDEX(C:C,⑥)

INDEX 函数根据片段⑥返回的行号值索引 C 列对应位置，即可得到出现频数第 k 高的置业顾问姓名，问题得解。



以上，就是 Excel 2019 及以下版本用户解决分组统计并排序问题的正解，思路大致如下：

❶ 以计数范围为计数条件，使用 COUNTIF 统计出一组重复的频数数组；
❷ 用 INDIRECT 函数构建动态扩展的计数范围，判断当前值是否为首次出现；
❸ ①和②相乘，实现去重，加上代表行号的小数，以标识文本位置；
❹ 使用 LARGE 获取第 k 大值，再用 MOD 取余获取文本位置行号，最后用 INDEX 进行索引。

本文分享的公式在 Excel 属于高难度级别，一时难以理解也无需焦虑，只要多看几次，用心理解，相信每个小伙伴都能最终将其中的原理和思路内化为自己的修行和能力。

当然了，更高级版本 Excel 中还有其他更简洁的解题公式，小花将在下一篇文章中继续分享，敬请期待吧！

真正的 Excel 高手，不是加班最晚的人，而是用对方法的人！

如果你也想告别熬夜做表，升职加薪快人一步，那么一定要参加秋叶 Excel 3 天集训营！

只需 3 天时间，每天 30 分钟，你也能掌握更多 Excel 技巧，提高效率、减轻工作压力~

《秋叶 Excel 3 天集训营》
原价 99 元
现在 限时免费 

赶紧长按下图识别二维码
抢占学习名额
开启你的高效办公之旅吧！

👇👇👇

图片

▲ 现在报名，还能免费获得更多福利：
图片


❤

点击👇关注【秋叶 Excel】
免费获取 Excel 干货、模板
↓↓↓
图片

推荐文章
终究没躲过，AI进化太快的负作用还是来了  ·  秋叶PPT  ·  22 小时前
© 2024 精读
删除内容请联系邮箱 2879853325@qq.com