如何在Google Sheets中基于动态列生成键值对？

方法1：兼容新旧版的FLATTEN+QUERY方案 #

直接在空白单元格输入以下公式，无需指定固定列数，会自动适配动态新增的列：

=ARRAYFORMULA(
  QUERY(
    {
      FLATTEN(IF(B2:INDEX(B:Z, COUNTA(A:A), COUNTA(1:1))<>"", A2:INDEX(A:A, COUNTA(A:A)),)),
      FLATTEN(B2:INDEX(B:Z, COUNTA(A:A), COUNTA(1:1)))
    },
    "where Col2 is not null",
    0
  )
)

公式说明：

• INDEX(B:Z, COUNTA(A:A), COUNTA(1:1))：动态定位数据区域的右下角，COUNTA(A:A)获取A列有效键的行数，COUNTA(1:1)获取第一行有内容的表头列数，自动适配新增列
• FLATTEN：把二维的数值区域转成一维数组，同时通过IF判断值非空时才保留对应键，避免空值无效行
• QUERY：过滤掉值为空的行，只保留有效的键值对

方法2：新版Google Sheets专属的TOCOL方案 #

如果你的Google Sheets是新版（支持TOCOL函数），可以用更简洁的公式：

=ARRAYFORMULA(
  {
    TOCOL(INDEX(A:A, SEQUENCE(COUNTA(A:A)-1, COUNTA(1:1)-1, 2)), 1),
    TOCOL(B2:INDEX(B:Z, COUNTA(A:A), COUNTA(1:1)), 1)
  }
)

公式说明：

• SEQUENCE(COUNTA(A:A)-1, COUNTA(1:1)-1, 2)：生成对应维度的序列，让每个键重复对应的值列次数
• INDEX(A:A, ...)：基于序列生成重复的键数组，TOCOL转成一维
• TOCOL(B2:...)：把值区域转成一维数组，和键数组直接配对生成键值对

注意事项 #

• 保持A列键数据连续，避免大量空行影响COUNTA的计数准确性
• 公式会自动过滤值为空的行，若需要保留空值，可去掉QUERY中的where Col2 is not null或TOCOL的第二个参数1

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