利用Excel内置数据透视表计算字段按日期区间统计采购量

操作步骤： #

1. 设置动态报告截止日

   • 在工作表空白单元格（例如G1）输入初始报告截止日（如2023/11/30），后续可直接修改为2023/12/31实现切换。
   • 选中该单元格，在顶部名称框输入ReportEndDate并回车，将其设为工作簿级命名单元格，方便后续公式引用。

2. 插入基础数据透视表

   • 选中包含客户、采购日期、采购商品的数据源区域，点击「插入」选项卡→「数据透视表」，按向导完成透视表创建。
   • 将「客户」字段拖至数据透视表的行区域，作为分组统计维度。

3. 添加4个日期区间的计算字段

   • 点击数据透视表任意单元格，在「数据透视表分析」（或「选项」）选项卡中选择「字段、项目和集」→「计算字段」。
   • 依次创建以下4个计算字段：
     • <30天：名称输入<30天，公式输入：

       =IF(AND(采购日期<=ReportEndDate, DATEDIF(采购日期, ReportEndDate, "d")<30), 1, 0)
       

     • 30-60天：名称输入30-60天，公式输入：

       =IF(AND(采购日期<=ReportEndDate, DATEDIF(采购日期, ReportEndDate, "d")>=30, DATEDIF(采购日期, ReportEndDate, "d")<60), 1, 0)
       

     • 60-90天：名称输入60-90天，公式输入：

       =IF(AND(采购日期<=ReportEndDate, DATEDIF(采购日期, ReportEndDate, "d")>=60, DATEDIF(采购日期, ReportEndDate, "d")<90), 1, 0)
       

     • 90+天：名称输入90+天，公式输入：

       =IF(AND(采购日期<=ReportEndDate, DATEDIF(采购日期, ReportEndDate, "d")>=90), 1, 0)
       

   • 每个字段创建完成后点击「添加」，全部添加完毕后点击「确定」。

4. 配置值区域汇总方式

   • 将刚创建的4个计算字段依次拖至数据透视表的值区域。
   • 对每个值字段右键→「值字段设置」，将汇总方式改为「求和」（因为符合条件的记录会返回1，求和结果即为对应区间的采购数量）。

动态切换说明 #

修改ReportEndDate单元格的日期后，点击数据透视表任意单元格→「数据透视表分析」→「刷新」，即可自动更新各日期区间的采购量统计结果。

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