如何创建带计算字段的单数据透视表实现工时合规统计并支持钻取

核心逻辑 #

完全可以将所有计算逻辑整合到同一张基础表中，无需拆分多表。核心思路是先在单表内完成行级的年度累计工时标记，再基于标记做聚合统计，同时保留所有原始明细字段，天然支持下钻/上钻分析。

具体实现步骤（通用逻辑，适配Excel/BI工具/SQL场景） #

• 第一步：新增年度计算列，从日期字段提取所属年份，公式示例：
  Excel/DAX场景：YEAR([日期列])
  SQL场景：EXTRACT(YEAR FROM 日期列) AS 年度
• 第二步：新增个人年度累计工时计算列，按人员+年度分组求和所有工时，公式示例：
  Excel Power Pivot/DAX场景：CALCULATE(SUM([工时]), ALLEXCEPT(表名, 表名[人员姓名], 表名[年度]))
  SQL场景：SUM(工时) OVER (PARTITION BY 人员姓名, 年度) AS 个人年度累计工时
• 第三步：新增合规标记计算列，判断累计工时是否符合要求：IF([个人年度累计工时]<=1800, "合规", "不合规")

数据透视表配置 #

直接用处理后的单表创建透视表即可：

• 行/列区域放置「年度」「合规标记」
• 值区域放置「人员姓名」，汇总方式选择非重复计数

最终输出结果就是每年度对应的合规、不合规人数，因为所有原始明细字段都保留在基础表中，可直接下钻查看某类人群的月度/每日工时明细，上钻也可直接查看全年度整体合规率。

优化提示 #

如果使用Power BI/Tableau这类BI工具，可将上述三个计算逻辑直接做成度量值，无需固化到基础表，查询性能更好，钻取逻辑完全一致。

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