把宽表转换成长表或反向转换

作者: AILesson7 分钟准备已测试:ChatGPT复核日期: 2026-08-28

直接回答

在保护记录粒度、标识符、空值含义和聚合规则的前提下逆透视或透视数据. 请提供: 来源结构与样例、目标结构与用途、键、标签、空值与聚合. 预期成果: 包含 Power Query 步骤、键检查和行数对账的可逆重塑方案.

补充你的背景

你填写的内容只保留在当前浏览器中，AILesson 提示语不会将其发送给模型或服务器。

来源结构与样例目标结构与用途键、标签、空值与聚合

生成的提示语

未填写的字段会保留为占位符，你仍然可以复制后自行编辑

请根据所给结构和规则，设计 Excel 宽转长或长转宽方案。

表头、类型、粒度、键、度量、样例、空值和错误：
[source]

目标方向、列、顺序、下游用途、刷新和可逆要求：
[target]

唯一键、标签解析、空值/零、重复、单位、合计、聚合和验证：
[rules]

提出步骤前先说明来源和目标记录粒度。宽转长时，区分标识列和度量列，定义原表头如何变成属性字段；不得误逆透视合计或备注。长转宽时，证明每个行键与新列组合唯一，或只使用明确批准的聚合。保留空值与零、数据类型、单位和来源标签。可刷新转换优先使用 Power Query，并在有用时提供菜单步骤和关键 M 操作；只有所述环境需要时才使用公式。请返回：粒度与键审计；来源到目标映射；转换步骤；重复与表头解析策略；预期行列对账；验证查询；样例输出；刷新/反向说明；以及应停止转换的失败条件。

默认保护隐私提示语在浏览器本地组装。除非所在组织明确允许，否则不要把机密信息提交给任何 AI 服务。

从输入到成果

实际案例

看看具体背景如何通过这条配方转化为可使用的成果

实际输入

来源结构与样例: Excel 表 QuarterlyTargets，每行粒度为产品编号 + 地区。列：Product ID 文本、Region 文本、Owner 文本、Q1 Target 整数、Q2 Target 整数、Q3 Target 整数、Q4 Target 整数、Annual Total 公式、Notes 文本。共 120 行。Product ID + Region 唯一。Q2 可空，表示尚未设定目标；0 表示明确无目标。没有公式错误。样例：P-01、East、Ana、100、空白、0、140、240、launch delayed。
目标结构与用途: 宽转长供 Power BI 使用。目标列 Product ID、Region、Owner、Quarter、Target、Notes。每行粒度为产品-地区-季度；保留目标空白行，以分析缺失。每月从 Excel 表刷新。Annual Total 不得成为季度。可以反向恢复四个季度列，但 Annual Total 可重新计算。
键、标签、空值与聚合: 季度表头必须准确映射 Q1 Target -> Q1，直至 Q4 Target -> Q4。不得聚合。Product ID + Region + Quarter 必须唯一。Target 保持可空整数，0 保留为 0。Owner 和 Notes 在季度行重复。验证 120×4=480 行，每个来源键准确有四个季度。

示例输出

粒度审计：来源键为 Product ID + Region；目标键为 Product ID + Region + Quarter。Owner 和 Notes 是按设计重复的描述属性。Annual Total 是派生元数据，不参与逆透视。目标应准确为 480 行，包括 Target 为空的行。

Power Query 步骤：通过“数据 > 从表格/区域”加载 QuarterlyTargets。Product ID、Region、Owner、Notes 设为文本；Q1–Q4 Target 设为可空整数。选择 Product ID、Region、Owner、Annual Total、Notes 后使用“逆透视其他列”，但必须先确认其他列准确只有四个季度；为降低刷新时误纳新列风险，更安全的是明确选择 Q1 Target 至 Q4 Target 后“逆透视列”。把 Attribute 改名 Quarter Source，Value 改名 Target。只把四个允许表头映射到 Q1–Q4；出现其他值时返回错误，不随意裁剪文字。Annual Total 只用于对账后删除，再排列目标列。不得过滤 Target 空行。

验证：按 Product ID、Region、Quarter 分组，任何计数不为 1 就停止。按来源键分组并要求四行。要求总行数 480、Quarter 只含 Q1–Q4、Target 为整数或空。重新计算非空季度之和并与来源 Annual Total 比较，但空值应视为缺失，不自动当零；P-01 的长表行是 Q1=100、Q2=空、Q3=0、Q4=140，Owner Ana 和 Notes 重复。反向时仅在唯一性通过后用 Target 透视 Quarter，并选择“不聚合”；按批准的空值规则重算 Annual Total。