Excel数据透视图表动态日期计算实现方案及教程问询

前置说明 #

我尝试阐述当前待解决的问题，若表述不清恳请谅解，希望有人能理解并提供可行方案。

背景 #

需分析并汇报服务台工单系统数据，系统字段如下：

工具集 #

使用Excel 365，借助Power Query、Data Model、KPIs及Pivot Table功能进行数据汇总分析，长期计划迁移至Power BI仪表盘，但目前无相关经验。

时间维度展示需求 #

需制作仪表盘式数据透视图表，按时间轴各节点展示：

• 新增工单数量
• 关闭工单数量
• 当前活跃工单数量（未关闭或取消）
• 未关闭工单的平均时效（工作日）
• 是否达成服务水平协议目标（布尔型KPI）

时间轴动态数据要求 #

工单的运营状态需根据关闭日期动态变更，例如：7/1创建、7/5关闭的工单：

• 7/1之前不纳入统计
• 7/1至7/4统计为Active状态
• 7/5及之后统计为Closed状态

核心问题 #

1. 此类动态汇总能否通过数据透视图表实现？若不能，该如何构建此类仪表盘？
2. 能否在时间轴上动态计算工单活跃期间的时效？例如上述工单在7/1时效为1，7/4为4，7/5及之后为5。

教程需求 #

是否有相关视频或课程可指导制作此类展示？

问题1：动态汇总的实现方式 #

纯原生数据透视表无法直接实现这类基于时间轴的动态状态判断，但结合Excel 365的Power Query + 数据模型（DAX）+ 数据透视表可以完成，具体步骤：

1. 生成日期维度表：用Power Query创建覆盖工单最早创建日到最新统计日的完整日期序列，作为时间轴的基础维度。
2. 导入数据到数据模型：将工单表和日期表都添加到Excel数据模型，无需建立传统关系，通过DAX计算关联日期与工单状态。
3. 编写核心DAX度量值：
   • 新增工单数量：

     新增工单 = CALCULATE(COUNT('工单表'[Ticket Number]), FILTER('工单表', '工单表'[Date created] = MAX('日期表'[Date])))
     

   • 关闭工单数量：

     关闭工单 = CALCULATE(COUNT('工单表'[Ticket Number]), FILTER('工单表', '工单表'[Date closed] = MAX('日期表'[Date]) && NOT(ISBLANK('工单表'[Date closed]))))
     

   • 活跃工单数量：

     活跃工单 = CALCULATE(COUNT('工单表'[Ticket Number]), FILTER('工单表', '工单表'[Date created] <= MAX('日期表'[Date]) && (ISBLANK('工单表'[Date closed]) || '工单表'[Date closed] > MAX('日期表'[Date]))))
     

   • SLA达成KPI：根据你的SLA规则编写，例如要求5个工作日内关闭，可计算当日已关闭工单的平均处理时长是否达标，返回布尔值（如1代表达标，0代表未达标）。
4. 构建动态数据透视表：以日期表的日期为行标签，将上述度量值拖入值区域，即可生成随时间轴动态变化的汇总结果，再搭配透视图表制作仪表盘。

后续迁移到Power BI时，逻辑完全通用，仅操作界面略有差异。

问题2：动态时效计算 #

可以实现，通过DAX度量值结合时间智能函数完成：

• 核心思路是根据当前时间轴的日期，判断工单处于活跃期还是已关闭，分别计算对应时效：

  动态工单时效 = 
  VAR 当前日期 = MAX('日期表'[Date])
  VAR 工单创建日 = SELECTEDVALUE('工单表'[Date created])
  VAR 工单关闭日 = SELECTEDVALUE('工单表'[Date closed])
  RETURN
  IF(ISBLANK(工单关闭日),
      NETWORKDAYS(工单创建日, 当前日期),
      IF(当前日期 <= 工单关闭日,
          NETWORKDAYS(工单创建日, 当前日期),
          NETWORKDAYS(工单创建日, 工单关闭日)
      )
  )
  

• 将该度量值用于计算未关闭工单的平均时效时，只需外层嵌套AVERAGE函数并添加活跃工单的筛选条件即可。

教程资源建议 #

直接在Excel官方教程或内置帮助中搜索以下关键词，即可找到实操性内容：

• Excel Power Query 日期维度表创建
• Excel 数据模型 DAX 度量值入门
• Excel 服务台工单动态仪表盘制作
• Power BI 时间智能函数应用（提前为迁移做准备）
  Excel 365的内置帮助文档中，针对NETWORKDAYS、CALCULATE、FILTER等核心函数都有配套案例，适合边操作边学习。

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