Excel公式自动化需求：实现行列对应匹配计算

核心需求 #

• 下方表格的A列单元格，需匹配上方表格的A行单元格（例：A8列对应A8行）
• 原公式=SUM(IF($B2:$M2>$B9:$M9; $B$18:$M$18; 0)) / SUM($B$18:$M$18)复制时需手动修改单元格范围，需实现自动化适配

解决方案 #

根据行列对应的逻辑，使用动态行引用替代原公式中的固定行范围，实现复制时自动适配：

场景1：按列号匹配行号（当前列号=目标行号）

适用于下方表格的列序号直接对应上方表格的行号（例：第8列单元格对应上方第8行）

• 动态数组Excel（365/2021及以后）：

  =SUM(IF(INDEX($B:$M, COLUMN(), 0) > $B$9:$M$9, $B$18:$M$18, 0)) / SUM($B$18:$M$18)
  

• 旧版Excel（需数组公式）：
  输入公式后按Ctrl+Shift+Enter确认：

  =SUM(IF(INDEX($B:$M, COLUMN(), 0) > $B$9:$M$9, $B$18:$M$18, 0)) / SUM($B$18:$M$18)
  

场景2：按列标题匹配行号（列标题文本=目标行的A列文本）

适用于下方列标题为A文本，需匹配上方表格A列中相同文本对应的行号

假设下方列标题在第1行，公式：

=SUM(IF(INDEX($B:$M, MATCH(INDEX($1:$1, COLUMN()), $A:$A, 0), 0) > $B$9:$M$9, $B$18:$M$18, 0)) / SUM($B$18:$M$18)

公式说明 #

• COLUMN()：自动获取当前单元格的列号，直接对应上方表格的目标行号
• INDEX($B:$M, 行号, 0)：动态引用上方表格中目标行的B-M列整行数据，替代原公式中的固定行范围$B2:$M2
• MATCH(INDEX($1:$1, COLUMN()), $A:$A, 0)：通过当前列标题，匹配上方A列中对应文本的行号，适配非连续或自定义的行列对应关系
• 其余逻辑与原公式一致：对比目标行与基准行$B$9:$M$9的数值，大于则取权重行$B$18:$M$18对应值求和，再除以权重总和

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