如何在Excel/Google Sheets中通过公式将带行列标题的表格转为列表

Excel和Google Sheets均提供原生公式，可直接实现带行列标题的二维交叉表到表头匹配一维列表的转换，无需编写宏或自定义脚本。

转换效果参考：

• 原始带行列标题的二维交叉表：
• 转换完成的一维目标列表：

以下公式默认原始表结构为：行标题存于A列（A1为行标题字段名，A2起为具体行标题值），列标题存于第1行（B1起为具体列标题值），行列交叉的业务数据存于B2开始的矩形区域，使用时请将公式中的范围替换为你表格的实际范围。

Google Sheets 实现方案 #

支持动态数组的Google Sheets版本可直接输入单条公式，自动溢出全量转换结果，无需下拉填充。在空白区域的起始单元格输入以下公式即可：

=HSTACK(
  TOCOL(IF(B2:Z<>"",A2:A,NA())),
  TOCOL(IF(B2:Z<>"",B1:1,NA())),
  TOCOL(B2:Z,1)
)

公式逻辑说明：

• 第一段通过TOCOL搭配空值判断，逐行匹配每个非空数据单元格对应的行标题，自动跳过空单元格
• 第二段逐列匹配每个非空数据单元格对应的列标题，自动跳过空单元格
• 第三段按行顺序提取所有非空的交叉业务数据
• 三部分结果通过HSTACK横向拼接，直接生成「行标题值、列标题值、交叉数据值」三列结构化结果，手动给三列加上对应表头即可使用。
  如果使用的是旧版Google Sheets无动态数组溢出能力，可使用FLATTEN函数搭配筛选实现相同逻辑。

Excel 实现方案 #

根据Excel版本不同，可选择对应公式：

新版Excel（365/2021及以上版本，支持动态数组） #

逻辑与Google Sheets版本一致，单公式即可自动溢出全量结果，空白起始单元格输入公式后按回车即可：

=HSTACK(
  TOCOL(IF(B2:Z<>"",A2:A,"")),
  TOCOL(IF(B2:Z<>"",B1:Z1,"")),
  TOCOL(B2:Z,1)
)

旧版Excel（2019及更早版本，无动态数组支持） #

可使用INDEX+MOD+INT组合公式逐行生成结果，假设转换后的结果从D2单元格开始存放（D列存行标题、E列存列标题、F列存交叉数据），分别在对应单元格输入公式：

• D2（提取行标题）：

=INDEX($A:$A,INT((ROW(A1)-1)/COUNTA($B$1:$Z$1))+2)

• E2（提取列标题）：

=INDEX($B$1:$Z$1,MOD(ROW(A1)-1,COUNTA($B$1:$Z$1))+1)

• F2（提取交叉数据）：

=INDEX($B:$Z,INT((ROW(A1)-1)/COUNTA($B$1:$Z$1))+2,MOD(ROW(A1)-1,COUNTA($B$1:$Z$1))+1)

输入完成后选中D2:F2单元格区域，下拉填充直到出现空值，再筛选删除数据为空的无效行即可。

注意：如果你的业务数据中存在需要保留的合法空值，可将公式中的非空判断逻辑替换为适配你数据规则的判断条件，避免有效数据被误过滤。

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