Excel SUMPRODUCT公式计算时段月均客量慢 优化方案咨询

问题背景 #

• 已搭建可按日期、时段维度整理原始数据的客户量统计看板，基础统计功能运行正常
• 待实现需求：按时段维度统计月均客户量，目前已在daily_pax_sheet工作表搭建对应基础数据表：以1月10:00-10:30时段E类客户数据为例，该时段总到访量为35，共覆盖8个有有效客流记录的日期，计算得该时段日均客户量为4
• 当前实现逻辑：在daily_pax_details工作表第107行使用SUMPRODUCT公式，计算逻辑为「对应时段总客户量 ÷ 存在对应客流记录的有效天数」
• 现存问题：当原始数据行数超过2万行时，SUMPRODUCT公式运算等待时间过长，需要更高效的实现路径与公式调整方法

可复用的高效实现方案 #

经实测，基于数据模型的数据透视表方案可大幅降低大行数下的计算耗时，操作步骤如下：

• 创建数据透视表（Pivot Table）导入目标数据源时，勾选*将此数据添加到数据模型（Data Model）*选项
• 调整值字段设置为非重复值计数（Distinct value count），即可快速统计各时段下的唯一有效日期数，配合总客户量汇总字段即可快速完成日均、月均客户量计算

该方案计算效率远高于大行数场景下的SUMPRODUCT等数组类公式，可适配10万行级以上的原始数据量，有同类统计需求的用户可直接复用。

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