Google Sheets数据转换咨询：寻求将表头映射至每行的适用公式

适用场景 #

针对原始数据为一级行标题+一级列标题的宽表结构，需要转换为每行均携带列标题、行标题和对应数值的长表结构，可直接使用以下公式：

方案1：自动溢出公式（适配2022年后更新的Google Sheet版本） #

无需手动下拉，输入公式后自动生成全部转换后数据，空值行自动过滤：

=LET(
  col_header, Sheet1!B1:D1,
  row_header, Sheet1!A2:A10,
  data_range, Sheet1!B2:D10,
  col_list, TOCOL(IF(SEQUENCE(ROWS(data_range)), col_header)),
  row_list, TOCOL(IF(SEQUENCE(COLUMNS(data_range)), TRANSPOSE(row_header))),
  val_list, TOCOL(data_range),
  FILTER(HSTACK(col_list, row_list, val_list), val_list<>"")
)

• 替换Sheet1!B1:D1为你自己的列标题范围（即需要填充到每行的表头范围）
• 替换Sheet1!A2:A10为你自己的行标题范围
• 替换Sheet1!B2:D10为你自己的数值区域范围

方案2：兼容旧版本的下拉公式 #

如果你的Google Sheet版本不支持LAMBDA系列函数，可使用以下公式手动下拉生成：

1. 先在空白区域的第一行设置新表头，比如F1填列标题、G1填行标题、H1填对应数值
2. F2输入公式，下拉即可自动填充原列表头：
   =INDEX(Sheet1!$B$1:$D$1, MOD(ROW(A1)-1, COLUMNS(Sheet1!$B$1:$D$1))+1)
3. G2输入公式，下拉即可自动填充原行标题：
   =INDEX(Sheet1!$A$2:$A$10, INT((ROW(A1)-1)/COLUMNS(Sheet1!$B$1:$D$1))+1)
4. H2输入公式，下拉即可自动填充对应数值：
   =INDEX(Sheet1!$B$2:$D$10, INT((ROW(A1)-1)/COLUMNS(Sheet1!$B$1:$D$1))+1, MOD(ROW(A1)-1, COLUMNS(Sheet1!$B$1:$D$1))+1)
5. 下拉到出现#REF!错误时停止，删除最后一行错误内容即可。

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