如何在Excel中实现区间匹配并自动生成对应百分比结果

核心思路 #

通过匹配当前行的类别，再定位金额对应的区间，返回对应的百分比。以下是两种适配不同Excel版本的实用方案：

方案1：适配所有Excel版本（INDEX+MATCH数组公式） #

假设你的规则表结构为：

• 类别列：D2:D5
• 金额下限列：E2:E5（需按升序排列）
• 百分比列：F2:F5

在需要填充百分比的单元格（比如C2）输入以下公式：

=INDEX($F$2:$F$5, MATCH(1, ($A2=$D$2:$D$5)*($B2>=$E$2:$E$5), 1))

旧版本Excel需按Ctrl+Shift+Enter确认，365/2021版本直接回车即可

公式说明：

• ($A2=$D$2:$D$5)：匹配当前行的类别，返回一组TRUE/FALSE值
• ($B2>=$E$2:$E$5)：判断当前金额是否大于等于区间下限，返回一组TRUE/FALSE值
• 两者相乘后，符合双条件的位置会得到1，MATCH(1,...,1)会找到最大的符合条件的位置（即金额对应的最高区间）
• INDEX根据这个位置返回对应的百分比

方案2：适配Excel 365/2021（XLOOKUP简洁版） #

同样基于上述规则表结构，在目标单元格输入：

=XLOOKUP($B2, FILTER($E$2:$E$5, $D$2:$D$5=$A2), FILTER($F$2:$F$5, $D$2:$D$5=$A2),,1)

公式说明：

• FILTER($E$2:$E$5, $D$2:$D$5=$A2)：先过滤出当前类别对应的所有金额下限
• FILTER($F$2:$F$5, $D$2:$D$5=$A2)：同时过滤出对应类别的所有百分比
• XLOOKUP($B2,...,1)：在过滤后的金额下限中，找到最接近且不超过当前金额的项，返回对应百分比

关键注意事项 #

• 规则表的金额下限必须按升序排列，否则近似匹配会失效
• 公式中的区域引用（如$D$2:$D$5）要加绝对引用符号$，避免下拉公式时引用区域偏移
• 若需处理“金额为0”或特殊区间，可在规则表中补充对应条目

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