Power Query：如何将逆透视数据转为各列独立的列表式表格？

你的需求本质是将每个科目下的姓名作为独立列表，按行对齐后展示，常规透视因为是聚合操作无法直接实现，核心思路是先给每个科目内的姓名分配组内行号，再基于行号进行透视，就能得到目标结构。以下是几种常用工具的实现方案：

Python Pandas 实现 #

通过分组生成组内行号，再透视对齐数据：

import pandas as pd

# 构造原始数据
raw_data = {
    "姓名（Names）": ["John", "Sam", "Fred", "John", "Emma", "Mike"],
    "科目（Subjects）": ["English", "Art", "Biology", "Biology", "Art", "Biology"]
}
df = pd.DataFrame(raw_data)

# 按科目分组，为每个组内的姓名生成连续行号
df["行号"] = df.groupby("科目（Subjects）").cumcount()

# 基于行号透视，将科目转为列，姓名作为值，缺失值填充为null
result_df = df.pivot(
    index="行号",
    columns="科目（Subjects）",
    values="姓名（Names）"
).reset_index(drop=True)

# 调整列顺序匹配目标表格
result_df = result_df[["Art", "Biology", "English"]]

print(result_df)

运行后输出的结果会和你目标表格一致，缺失值显示为NaN，对应需求中的null。

SQL 实现 #

利用窗口函数生成组内行号，再通过条件聚合实现列转行：

WITH ranked_records AS (
    SELECT 
        "姓名（Names）",
        "科目（Subjects）",
        -- 按科目分区，为每个科目下的姓名生成行号（可按需调整ORDER BY字段）
        ROW_NUMBER() OVER (PARTITION BY "科目（Subjects）" ORDER BY "姓名（Names）") AS row_num
    FROM your_table_name
)
SELECT
    MAX(CASE WHEN "科目（Subjects）" = 'Art' THEN "姓名（Names）" END) AS Art,
    MAX(CASE WHEN "科目（Subjects）" = 'Biology' THEN "姓名（Names）" END) AS Biology,
    MAX(CASE WHEN "科目（Subjects）" = 'English' THEN "姓名（Names）" END) AS English
FROM ranked_records
GROUP BY row_num
ORDER BY row_num;

注：不同SQL方言的语法略有差异，比如MySQL 8.0+才支持窗口函数，需根据实际数据库调整。

Excel 实现 #

通过数组公式提取各科目对应的姓名列表：

1. 先将原始数据按「科目」列排序，确保同科目姓名连续
2. 在目标表格的A2单元格（对应Art列第一行）输入数组公式：

   =INDEX($A$2:$A$7, SMALL(IF($B$2:$B$7="Art", ROW($B$2:$B$7)-ROW($B$1), ""), ROW(A1)))
   

   输入后按Ctrl+Shift+Enter确认（Excel 365可直接回车），然后下拉填充直到出现#NUM!，将这些错误值替换为null
3. 同理，在Biology列和English列分别替换公式中的科目名称，重复上述操作

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