Excel中基于读书量自动匹配下一奖励层级的高效方法咨询

核心需求 #

跟踪学生读书数量，根据非连续的奖励规则（比如读3、4本无奖励，读5本得奖励），输入读书数后自动显示下一奖励层级和对应奖品。原有的IF/嵌套VLOOKUP方案在奖励层级增至35个时操作繁琐，需要更高效的方法。

前提准备 #

确保你的奖励规则表（Books列和Prize列）是升序排列的，示例规则表如下：

假设规则表数据范围为$F$2:$G$6（Books在F列，Prize在G列），学生读书数在单元格B2（对应单条学生数据的读书数量）。

方法1：XLOOKUP函数（Excel 365/2021及以上推荐） #

这个函数直接支持近似匹配，写法简洁，适合大量奖励层级的场景：

计算下一奖励层级（Next Tier）

在学生行的Next Tier单元格输入：

=XLOOKUP(B2+1, $F$2:$F$6, $F$2:$F$6, "已解锁所有奖励", 1)

• B2+1：跳过当前已达到的层级（比如读了2本，要找比2大的下一层级）
• 1：启用近似匹配，自动查找大于等于B2+1的最小层级
• 第四个参数：如果学生读书数已超过最高奖励，返回自定义提示

计算对应奖品（Prize）

在Prize单元格输入：

=XLOOKUP(B2+1, $F$2:$F$6, $G$2:$G$6, "已解锁所有奖励", 1)

原理和层级公式一致，只是返回Prize列的对应值。

方法2：INDEX+MATCH（兼容旧版Excel） #

如果使用旧版Excel没有XLOOKUP功能，用INDEX+MATCH组合也能实现需求：

计算下一奖励层级（Next Tier）

=IFERROR(INDEX($F$2:$F$6, MATCH(B2+1, $F$2:$F$6, 1)+1), "已解锁所有奖励")

• MATCH(B2+1, $F$2:$F$6, 1)：找到小于等于B2+1的最大层级的位置
• +1：定位到下一个更高的奖励层级
• IFERROR：处理学生读书数超过最高奖励的情况

计算对应奖品（Prize）

=IFERROR(INDEX($G$2:$G$6, MATCH(B2+1, $F$2:$F$6, 1)+1), "已解锁所有奖励")

示例验证 #

以你提供的学生数据为例：

• Sally读书数=4：公式返回Next Tier=5，Prize=Candy Store
• Luke读书数=1：公式返回Next Tier=2，Prize=Extra coloring time
• Jane读书数=8：公式返回Next Tier=10，Prize=10 Extra minutes on the playground
  完全符合预期结果。

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