规模化构建Excel每周仪表盘

目的

设计可刷新的Excel仪表盘（使用Power Query + 结构化表格 + 数据验证 + 数据透视表报告）。

适用场景

• 触发条件：
  • 请为我为此文件构建一个Power Query流程，以便它能每周自动刷新，无需手动操作。
  • 将此转换为带有验证列表和清晰数据录入规则的结构化表格。
  • 创建一个基于数据透视表的每周仪表盘，并带有用于筛选年份和ISO周数的切片器。
  • 修复此Excel模型，使其在出现新列时刷新不会中断。
  • 设计一个可从CSV文件夹更新的可重用KPI包。
• 不适用场景...
  • 您需要进行高级预测/估值建模（本技能适用于可重复的报告流程）。
  • 您需要构建的是BI工具（如Power BI/Tableau）而非Excel。
  • 您的主要数据获取方式是网络爬虫。

输入

• 必需项：
  • 源数据文件：CSV、XLSX、DOCX导出的表格或PDF导出的表格（由用户提供）。
  • “周”的定义（优先采用ISO周）以及所需的KPI字段。
• 可选内容：
  • 数据字典 / 列定义。
  • 已知的需要验证的“不良数据”模式（例如，空白的PayNumber，无效的日期）。
  • 待重构的现有工作簿。
• 示例：
  • 每周CSV导出的文件夹：exports/2026-W02/*.csv
  • 每月列结构变化的单个XLSX转储文件

输出

• 如果要求仅提供计划（默认）：一份分步构建计划 + Power Query步骤 + 工作表布局 + 验证规则。
• 如果明确要求生成工件：
  • workbook_spec.md（工作簿结构和命名表）
  • power_query_steps.pq（M代码模板）
  • refresh-checklist.md（来自资产/) 成功标准 = 在添加新一周的文件后，刷新操作无需手动编辑即可正常工作，并且验证能够捕获错误行。

工作流程

1. 识别源文件类型（CSV/XLSX/DOCX/PDF导出）以及稳定的业务键（例如，支付编号）。
2. 定义规范化的表结构：
   • 必需的列、数据类型、允许的值以及“未知”值的处理方式。
3. 使用 Power Query 设计数据摄取流程：
   • 优先采用文件夹摄取+ 合并的方式，并包含防御性的“缺失列”处理。
   • 规范化列名（修剪空格、统一大小写、合并空格）。
4. 设计数据清洗与验证：
   • 创建一个数据_暂存查询（原始-规范化）和一个数据_清洗查询（已验证）。
   • 添加验证列（例如，支付编号是否有效、日期是否有效,问题原因).
5. 构建报告层：
   • 基于以下内容创建数据透视表Data_Clean
   • 切片器：年份，ISO周；以及运营维度
6. 添加一个“刷新状态”工作表：
   • 最后刷新时间戳、行数、查询错误标志、存在的最新周
7. 在以下情况下，停止并询问用户：
   • 所需的KPI/列未指定，
   • 源文件不包含任何稳定的键，
   • 周定义/时区规则不明确，
   • 如果没有提供导出文件，PDF/DOCX表格无法可靠提取。

输出格式

在生成计划时，请使用此模板：

WORKBOOK PLAN
- Sheets:
  - Data_Staging (query output)
  - Data_Clean (query output + validation flags)
  - Dashboard (pivots/charts)
  - Refresh_Status (counts + health checks)
- Canonical Schema:
  - <Column>: <Type> | Required? | Validation
- Power Query:
  - Query 1: Ingest_<name> (Folder/File)
  - Query 2: Clean_<name>
  - Key transforms: <bullets>
- Validation rules:
  - <rule> -> <action>
- Pivot design:
  - Rows/Columns/Values
  - Slicers

如果要求提供工件，也输出：

• assets/power-query-folder-ingest-template.pq（已适配）
• assets/refresh-checklist.md

安全性与边界情况

• 默认只读模式：除非用户明确要求生成文件，否则仅提供方案与代码片段。
• 绝不删除或覆盖用户文件；为输出内容建议新文件名。
• 倾向“禁止静默失败”：包含行数检查与可见的错误标识。
• 处理PDF/DOCX源文件时，要求用户提供已导出的表格（CSV/XLSX格式）或明确标注提取风险。

示例

• 输入：“包含PayNumber/Name/Date的每周CSV文件夹。”
  输出：文件夹导入Power Query模板 + 数据架构 + 刷新状态检查 + 数据透视仪表板方案。

• 输入：“当出现新列时刷新中断。”
  输出：防御性缺失列处理逻辑 + 列标准化方案 + 类型化架构计划。