如何将行数据转列？Excel表头不匹配及公式失效问题求解

原公式问题分析 #

你当前使用的数组公式逻辑方向正确，但可能因数组输入方式错误或范围计算冗余导致未达到预期效果。原公式中ROW($B$1:$B$18)-MIN(ROW($B$1:$B$18))+1的写法容易在范围变动时出错，且若未以数组方式触发计算，会导致缺失表头时无法正确返回空值。

修正后的公式方案 #

根据你的需求（提取同一表头对应的多行数据，无匹配时留空），分两种Excel版本提供可靠写法：

方案1：兼容所有Excel版本（需数组输入）

在目标单元格（如D2）输入以下公式，按Ctrl+Shift+Enter触发数组计算后，下拉填充至所需行数：

=IFERROR(INDEX($B:$B,SMALL(IF($A:$A=D$1,ROW($A:$A)),ROWS(D$2:D2))),"")

• 逻辑说明：
  1. IF($A:$A=D$1,ROW($A:$A))：筛选出A列与当前表头（D1）匹配的所有行号
  2. SMALL(...,ROWS(D$2:D2))：按顺序提取第1、2、3...个匹配行号
  3. INDEX($B:$B,...)：根据行号提取B列对应数据
  4. IFERROR(...)：无匹配项时返回空值

方案2：适用于Excel 365/2021（动态数组更简洁）

利用FILTER函数简化逻辑，直接在D2输入公式后下拉：

=IFERROR(INDEX(FILTER($B:$B,$A:$A=D$1),ROWS(D$2:D2)),"")

• 逻辑说明：
  1. FILTER($B:$B,$A:$A=D$1)：先筛选出A列匹配当前表头的所有B列数据
  2. INDEX(...,ROWS(D$2:D2))：按顺序提取第N个匹配值
  3. IFERROR(...)：无匹配时返回空值

关键注意事项 #

• 确保目标列的表头（如D1、E1等）与你需要的指定列完全一致
• 下拉填充时，ROWS(D$2:D2)会自动递增，依次提取第1、2...个匹配数据
• 若数据范围固定，可将$A:$A、$B:$B改为具体范围（如$A$1:$A$18）以提升计算性能

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