寻求Google Sheets中列转行的自动化透视/转置方案（类Excel Power Pivot）

方案1：函数组合实现（静态/半动态场景） #

如果你的需求是快速实现行列转换或透视，可通过QUERY、ARRAYFORMULA等原生函数组合完成，无需脚本。

场景A：将长表透视为宽表（类似Power Pivot的透视功能） #

假设源数据为长表结构（列：维度、属性、值），要将属性列的唯一值转为表头，生成宽表：

源数据示例：
| 类别 | 属性 | 值 |
|------|------|----|
| A    | 销量 | 100|
| A    | 利润 | 20 |
| B    | 销量 | 150|
| B    | 利润 | 30 |

使用以下公式：

=QUERY(A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B", 1)

• 说明：QUERY的PIVOT子句会将指定列（这里是B列属性）的唯一值转为表头，结合SUM（可替换为MAX/MIN/AVG等）聚合函数生成对应行的值，实现透视效果。

场景B：将宽表转置为长表（反向透视） #

如果源数据是宽表结构，要转为长表：

源数据示例：
| 类别 | 销量 | 利润 |
|------|------|------|
| A    | 100  | 20   |
| B    | 150  | 30   |

使用以下公式：

=ARRAYFORMULA(SPLIT(FLATTEN(A2:A&"|"&B1:C1&"|"&B2:C), "|"))

• 说明：FLATTEN将多行多列的组合数据压平为单列，SPLIT用分隔符|拆分回多列，实现宽表转长表。

方案2：Apps Script实现全自动化（类Power Pivot动态刷新） #

如果需要源数据更新时自动同步透视结果，或处理更复杂的多维度透视，可通过Apps Script实现类似Power Pivot的动态功能：

步骤1：编写脚本 #

打开Google Sheets，点击「扩展程序」→「Apps Script」，粘贴以下代码：

function autoPivot() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("源数据"); // 替换为你的源数据表名
  const targetSheet = ss.getSheetByName("透视结果"); // 替换为目标表名
  const sourceData = sourceSheet.getDataRange().getValues();
  
  // 配置列索引（从0开始计数，根据你的数据结构调整）
  const dimensionCol = 0; // 维度列（如示例中的「类别」列）
  const attributeCol = 1; // 属性列（如示例中的「属性」列）
  const valueCol = 2; // 值列（如示例中的「值」列）
  
  // 构建透视映射表
  const pivotMap = new Map();
  const attributes = new Set();
  
  // 遍历源数据（跳过表头行）
  for (let i = 1; i < sourceData.length; i++) {
    const dimension = sourceData[i][dimensionCol];
    const attribute = sourceData[i][attributeCol];
    const value = sourceData[i][valueCol];
    
    if (!pivotMap.has(dimension)) pivotMap.set(dimension, new Map());
    pivotMap.get(dimension).set(attribute, value);
    attributes.add(attribute);
  }
  
  // 生成透视结果的表头和行数据
  const pivotHeaders = [sourceData[0][dimensionCol], ...Array.from(attributes)];
  const pivotRows = [pivotHeaders];
  
  pivotMap.forEach((attrMap, dimension) => {
    const row = [dimension];
    attributes.forEach(attr => row.push(attrMap.get(attr) || ""));
    pivotRows.push(row);
  });
  
  // 写入目标表
  targetSheet.clear();
  targetSheet.getRange(1, 1, pivotRows.length, pivotRows[0].length).setValues(pivotRows);
}

// 创建编辑触发器，源数据更新时自动执行透视
function createTrigger() {
  ScriptApp.newTrigger("autoPivot")
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onEdit()
    .create();
}

步骤2：配置并启用 #

1. 修改代码中的sourceSheet和targetSheet名称，匹配你的表格
2. 根据数据结构调整dimensionCol、attributeCol、valueCol的索引值
3. 点击运行createTrigger函数，完成授权后，源数据编辑时会自动更新透视结果

扩展说明 #

• 可修改脚本中的逻辑，添加求和、平均等聚合函数，支持多维度透视
• 如果需要定时刷新，可将触发器改为timeBased()类型

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