如何在Google Sheets中按A列分隔组转置多列数据

一、公式方案（适合中小数据集） #

利用Google Sheets原生函数实现自动分组处理，无需手动逐个编写重复公式：

=LET(
  data, A1:P9, // 替换为实际源数据范围，比如A1:P1000
  groupCol, INDEX(data,,1),
  bCol, INDEX(data,,2),
  cpCols, INDEX(data,,3):INDEX(data,,16),
  // 自动为每行标记所属分组（A列非空值为组标识）
  groups, SCAN("", groupCol, LAMBDA(acc, curr, IF(curr<>"", curr, acc))),
  // 过滤无分组的无效行
  filtered, FILTER(HSTACK(groups, bCol, cpCols), groups<>""),
  // 分组批量处理：转置B列、合并C-P列内容
  groupedData, GROUPBY(
    INDEX(filtered,,1),
    // 转置组内B列值，用空格分隔（可替换为其他分隔符）
    LAMBDA(grp, TEXTJOIN(" ",, TRANSPOSE(FILTER(INDEX(filtered,,2), INDEX(filtered,,1)=grp)))),
    // 合并组内每行C-P列内容，再用竖线分隔所有行
    LAMBDA(grp, TEXTJOIN("|",, BYROW(FILTER(INDEX(filtered,,3):INDEX(filtered,,18), INDEX(filtered,,1)=grp), LAMBDA(row, TEXTJOIN("",, row))))),
    0
  ),
  // 组合成需求格式：转置B列 + ": " + 合并的C-P列内容
  HSTACK(INDEX(groupedData,,1), BYROW(INDEX(groupedData,,2):INDEX(groupedData,,3), LAMBDA(row, TEXTJOIN(": ",, row))))
)

公式说明： #

1. SCAN函数自动填充每行的分组归属，解决A列仅组首行有值的分组问题
2. GROUPBY按分组批量处理，避免手动重复设置公式
3. 只需修改data变量的范围，即可适配不同大小的数据集

二、Apps Script方案（适合超大数据集） #

当数据集达到数百/数千行时，公式可能出现性能瓶颈，脚本可高效完成批量处理：

function processGroupedData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  
  // 可根据实际需求修改以下参数
  const sourceRange = sheet.getRange("A1:P9"); // 源数据区域
  const outputStartRow = 1; // 结果输出起始行
  const outputStartCol = 17; // 结果输出起始列（Q列对应17）
  
  const values = sourceRange.getValues();
  const groups = {};
  let currentGroup = null;

  // 遍历数据，按A列分组收集内容
  values.forEach(row => {
    const groupId = row[0].toString().trim();
    // 遇到新分组时初始化容器
    if (groupId !== "") {
      currentGroup = groupId;
      groups[currentGroup] = { bValues: [], cpContent: [] };
    }
    // 跳过无分组的行
    if (!currentGroup) return;
    
    // 收集B列非空值
    const bVal = row[1].toString().trim();
    if (bVal) groups[currentGroup].bValues.push(bVal);
    
    // 收集当前行C-P列的合并内容
    const cpStr = row.slice(2, 16).map(cell => cell.toString().trim()).join("");
    groups[currentGroup].cpContent.push(cpStr);
  });

  // 生成最终结果数组
  const result = [];
  Object.keys(groups).forEach(groupId => {
    const bTransposed = groups[groupId].bValues.join(" "); // B列转置后的分隔符
    const cpJoined = groups[groupId].cpContent.join("|"); // C-P列行之间的分隔符
    result.push([groupId, `${bTransposed}: ${cpJoined}`]);
  });

  // 将结果写入表格
  if (result.length === 0) return;
  const outputRange = sheet.getRange(outputStartRow, outputStartCol, result.length, result[0].length);
  outputRange.setValues(result);
}

使用方法： #

1. 打开Google Sheets，点击「扩展程序」→「Apps Script」
2. 粘贴上述代码，保存并运行（首次运行需完成授权）
3. 可在表格中添加自定义按钮，绑定该函数实现一键执行

脚本优势： #

• 处理数千行数据仅需几秒，性能远优于公式
• 可灵活修改分隔符、源数据范围、输出位置等参数

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