不修改表头合并不同表头学生数据文件并完成数据逆透视

针对你描述的Excel文件结构（合并单元格表头为学生姓名、第2行是科目、A列为测试/总分），可以通过Power Query（Excel/Power BI内置工具）完成批量处理，最终得到姓名、科目、各测试得分及总分横向排列的结构，具体步骤如下：

一、单文件处理步骤 #

1. 导入数据到Power Query
   选中数据区域，点击「数据」→「从表格/区域」，不要勾选「我的表格有标题」，让Power Query自动生成Column1~ColumnN的表头。

2. 填充学生姓名到对应列
   导入后第一行是学生姓名（含合并单元格导致的空值），选中第一行中已有的学生姓名单元格（比如ALEX），再选中右侧对应空值的列，点击「转换」→「填充」→「向右」，把学生姓名填充到该生所有科目对应的列表头行。

3. 合并学生姓名与科目作为列名

   • 选中除Column1（原A列）外的所有列，点击「转换」→「转置」。
   • 选中转置后的前两行（学生姓名+科目），点击「转换」→「合并列」，用「-」作为分隔符，新列名设为「Student-Subject」。
   • 再次点击「转换」→「转置」，然后点击「转换」→「将第一行用作标题」，最后删除原来的前两行数据。

4. 逆透视与重新透视整理结构

   • 选中Column1（测试名称/总分列），点击「转换」→「逆透视其他列」，得到三列：测试名称、学生-科目、得分。
   • 选中「学生-科目」列，点击「转换」→「拆分列」→「按分隔符」，用「-」拆分出「StudentName」和「Subject」列。
   • 选中「StudentName」和「Subject」列，点击「转换」→「透视列」，值列选择「得分」，聚合函数选「不要聚合」，最终得到每行对应一个学生+科目，各测试及总分横向排列的结构。

二、多文件批量处理 #

1. 批量导入文件夹内所有Excel文件
   点击「数据」→「获取数据」→「从文件」→「从文件夹」，选择目标文件夹后点击「编辑」进入Power Query。

2. 批量应用单文件处理逻辑

   • 点击「添加列」→「自定义列」，输入公式获取每个文件的数据（替换Sheet1为你的工作表名称）：

     Excel.Workbook([Content]){[Item="Sheet1",Kind="Sheet"]}[Data]
     

   • 点击自定义列右侧的「展开」按钮，选择「仅创建加载」，然后对展开后的表格应用上述单文件的所有处理步骤（可通过Power Query的「高级编辑器」直接粘贴单文件处理的M代码，调整对应参数）。

3. 合并所有结果
   完成处理后，所有文件的学生数据会自动合并到一个表格中，直接加载回Excel即可。

单文件处理的M代码示例 #

如果手动操作麻烦，可直接在Power Query的「高级编辑器」中替换以下代码（注意替换Table1为你的表名）：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillStudentNames = Table.FillRight(Source,{"Column3", "Column4", "Column5", "Column6"}),
    TransposeColumns = Table.Transpose(FillStudentNames),
    CombineStudentSubject = Table.CombineColumns(TransposeColumns,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Student-Subject"),
    TransposeBack = Table.Transpose(CombineStudentSubject),
    SetHeaders = Table.PromoteHeaders(TransposeBack, [PromoteAllScalars=true]),
    RemoveTopRows = Table.Skip(SetHeaders,2),
    UnpivotOtherColumns = Table.UnpivotOtherColumns(RemoveTopRows, {"Column1"}, "Attribute", "Value"),
    SplitAttribute = Table.SplitColumn(UnpivotOtherColumns, "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"StudentName", "Subject"}),
    PivotTestNames = Table.Pivot(SplitAttribute, List.Distinct(SplitAttribute[Column1]), "Column1", "Value")
in
    PivotTestNames

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