如何从7000+志愿调研数据中筛选前20%志愿者并生成频率图？

工具选择建议 #

7000+条数据用PowerQuery完全够用，比手动透视表更高效，且无需代码基础；宏（VBA）适合需要重复自动化操作的场景，但PowerQuery的可视化操作对非技术用户更友好，优先推荐PowerQuery。

Step 1：用PowerQuery计算志愿者核心指标 #

1. 导入数据到PowerQuery：选中原始数据区域，点击数据选项卡→自表格/区域，确认加载进入PowerQuery编辑器。
2. 分组统计核心数据：点击转换选项卡→分组依据，设置：
   • 分组列：选择能唯一标识志愿者的字段（如志愿者ID/姓名）
   • 添加第一个分组项：新列名探访次数，操作选行数
   • 添加第二个分组项：新列名总志愿时长，操作选求和，列选择陪伴时长
3. 导出处理后的数据：点击关闭上载→关闭上载至，选择加载到新工作表（保留原始数据不受影响）。

Step 2：筛选前20%志愿者 #

1. 计算阈值：在新工作表的空白单元格，分别输入：
   • 探访次数阈值：=PERCENTILE.EXC([探访次数],0.8)（返回前20%志愿者的最低探访次数）
   • 总志愿时长阈值：=PERCENTILE.EXC([总志愿时长],0.8)（返回前20%志愿者的最低总时长）
2. 筛选目标志愿者：
   • 如果要求同时满足两个维度都进入前20%：用数据选项卡→高级筛选，设置条件为探访次数≥[阈值单元格]且总志愿时长≥[阈值单元格]
   • 如果需要分别生成两个维度的前20%榜单：直接用表格的筛选功能，分别对两列设置“大于等于对应阈值”即可。

Step 3：生成累积频率图 #

探访次数累积频率图 #

1. 准备累积数据：
   • 对处理后的表格按探访次数降序排序
   • 新增累积人数列，第2行（表头行下一行）输入=ROW()-1，下拉填充（自动统计当前行及以上的志愿者数量）
   • 新增累积频率列，输入=[@累积人数]/COUNTA([志愿者ID])，下拉填充（得到0~1的累积比例，可设置单元格格式为百分比）
2. 插入图表：选中探访次数和累积频率列数据，点击插入选项卡→散点图→带平滑线的散点图。调整图表细节：
   • Y轴设置为百分比格式，标题改为“累积频率”
   • X轴标题改为“探访次数”
   • 可添加一条Y=0.8的水平参考线，直观标识前20%的分界点

总志愿时长累积频率图 #

重复上述步骤，仅将X轴数据替换为总志愿时长列即可。

宏（VBA）替代方案 #

如果需要定期重复这套操作，可以录制宏：

1. 开启开发工具选项卡，点击录制宏
2. 依次执行PowerQuery分组导出、阈值计算、筛选、作图的全流程
3. 停止录制后，下次点击宏按钮即可一键完成所有操作。注意宏仅适配固定格式的数据，若数据结构变动需重新录制。

内容的提问来源于stack exchange，提问作者Kitty