Excel中多组行数据转矩阵式列排布的自动化实现问询

问题场景 #

我有10组总计约70000行的数据（每组约7000行），目前数据按纵向堆叠排列：

• Column 1为时间值（x轴）
• Column 2为不同参数的测量值（如temperature、pressure、displacement等，格式为「参数名+空格+数值」）
  所有参数的测量时间点完全一致，现在需要将数据转换为宽格式矩阵：Column 1保留时间，后续每列对应一个参数的测量值，要求避免手动筛选复制，实现自动化处理。

原始数据结构 #

Column 1      Column 2

time 1        temperature 1
time 2        temperature 2
time 3        temperature 3
...           ...
time 1        pressure 1
time 2        pressure 2
time 3        pressure 3
...           ...
time 1        displacement 1
time 2        displacement 2
time 3        displacement 3
...           ...

目标矩阵形式 #

Column 1      Column 2            Column 3          Column 4            ...

time 1        temperature 1       pressure 1        displacement 1      ...
time 2        temperature 2       pressure 2        displacement 2      ...
time 3        temperature 3       pressure 3        displacement 3      ...
...           ...                 ...               ...                 ...

自动化实现方法 #

方法1：Power Query（推荐，适配大数据量） #

Power Query是Excel处理批量数据转换的高效工具，步骤如下：

1. 选中原始数据区域（包含表头），点击「数据」选项卡 → 「从表格/区域」（Excel 2016及以后版本；旧版需找「获取和转换」组的对应按钮）
2. 在Power Query编辑器中，确认第一行被识别为表头。选中Column 2列，点击「转换」选项卡 → 「拆分列」→ 「按分隔符」，选择空格作为分隔符，拆分到「最左侧的分隔符」（确保参数名完整拆分，比如"temperature 123"拆成"temperature"和"123"）
3. 此时数据会新增一列（如「Column 2.1」），分别对应参数名和数值。选中「Column 1（时间）」和拆分后的参数名列，点击「转换」选项卡 → 「透视列」
4. 在弹出窗口中，「值列」选择拆分后的数值列，「聚合函数」选择「不要聚合」（每个时间+参数的组合唯一），点击确定
5. 点击「关闭并上载」，转换好的宽格式数据会自动生成在新工作表中

方法2：数据透视表 #

适合快速转换，步骤如下：

1. 先拆分Column 2：选中Column 2列，点击「数据」选项卡 → 「分列」，选择「分隔符号」→ 勾选「空格」，完成拆分后得到参数名列和数值列
2. 选中所有数据（包含时间、参数、数值三列），点击「插入」选项卡 → 「数据透视表」，选择放置位置后确认
3. 透视表字段设置：
   • 将「时间」拖到「行」区域
   • 将「参数」拖到「列」区域
   • 将「数值」拖到「值」区域
4. 点击值区域的字段，选择「值字段设置」，把汇总方式改为「求和」（唯一值的求和结果就是自身），调整格式后即可得到目标矩阵

方法3：函数公式（适合小数据验证，大数据慎用） #

仅支持Excel 365/2021及以上版本（需动态数组函数）：

1. 新建工作表，在A2单元格输入=UNIQUE(原始数据!A:A)，提取所有唯一时间值作为第一列
2. 在B1单元格输入=UNIQUE(LEFT(原始数据!B:B,FIND(" ",原始数据!B:B)-1))，右拉填充提取所有唯一参数名作为表头
3. 在B2单元格输入公式：

   =XLOOKUP($A2&" "&B$1,原始数据!$A:$A&" "&LEFT(原始数据!$B:$B,FIND(" ",原始数据!$B:$B)-1),原始数据!$B:$B)
   

   下拉并右拉填充，即可匹配对应时间和参数的测量值

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