Excel中Index Match结合分段求和的多场景公式实现需求

输入数据表格 #

填充规则需求 #

• 红/紫色框：通过INDEX+MATCH匹配出发地（Origin）与目的地城市，获取对应里程
• 黄/蓝色框：匹配逻辑同上，但需排除Ocala相关记录（Ocala对应日期为10/20/22），求和出发地到目的地间除Ocala外的所有距离
• 空白项：若在10/21/22前未找到Cayce相关记录，返回NULL或空值
• 绿色框：从Ocala开始，求和Ocala到第一个Dacula的所有路段距离

问题背景 #

已完成红、紫色框的公式编写，但后续场景无法实现，寻求能覆盖所有需求的Excel函数方案。

Excel函数解决方案 #

假设输入数据位于A2:F5区域，以下是对应各需求的函数方案：

1. 红/紫色框（基础匹配）

=INDEX($F$2:$F$5,MATCH(1,($D$2:$D$5=目标Origin)*($C$2:$C$5=目标Destination),0))

注：旧版Excel需按Ctrl+Shift+Enter确认数组公式，新版Excel直接回车即可

2. 黄/蓝色框（排除Ocala的求和）

如果需要排除所有含Ocala的记录：

=SUMIFS($F$2:$F$5,$C$2:$C$5=目标Destination,$D$2:$D$5=目标Origin,$B$2:$B$5<>"Ocala",$D$2:$D$5<>"Ocala")

如果仅需排除10/20/22当天的Ocala记录：

=SUMIFS($F$2:$F$5,$C$2:$C$5=目标Destination,$D$2:$D$5=目标Origin,($A$2:$A$5<>"10/20/22")+($B$2:$B$5<>"Ocala")*($D$2:$D$5<>"Ocala")>0)

3. 空白项（日期前未找到Cayce返回空）

=IF(COUNTIFS($A$2:$A$5,"<10/22/22",$B$2:$E$5,"Cayce")=0,"",[你已编写的匹配公式])

将[你已编写的匹配公式]替换为对应逻辑的公式，未找到Cayce时自动返回空值

4. 绿色框（Ocala到第一个Dacula的累计求和）

适用于Excel 365及以上版本（支持LET函数）：

=LET(
  ocala_row,MATCH("Ocala",$B$2:$B$5,0)+1,
  dacula_row,XMATCH("Dacula",$C$ocala_row:$C$5,0)+ocala_row-1,
  IF(ISERROR(dacula_row),0,SUM($F$ocala_row:$F$dacula_row))
)

旧版Excel可拆分为三步：

1. 定位Ocala起始行：=MATCH("Ocala",$B$2:$B$5,0)+1
2. 定位第一个Dacula行：=MATCH("Dacula",$C$[ocala_row]:$C$5,0)+[ocala_row]-1
3. 求和区间里程：=IFERROR(SUM($F$[ocala_row]:$F$[dacula_row]),0)

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