Excel成绩等级,销售奖金的公式模板,可以直接套用!

621 次浏览

提问加Q群341401932 加微信EXCEL880B领取免费教程

判断数字所在的区间并返回对应的值,在学生成绩等级判定,计算销售提成,员工等级评定等场景中有广泛的应用。

以根据学生成绩判断等级为例介绍3个类型5个解决方案,其中首推XLOOKUP。

Excel成绩等级,销售奖金的公式模板,可以直接套用!插图

IFS/IF 逻辑判定

<span class="code-snippet_outer">=IFS(B2<<span class="code-snippet__number">60</span>,<span class="code-snippet__string">"不及格"</span>,B2<<span class="code-snippet__number">80</span>,<span class="code-snippet__string">"中等"</span>,B2<<span class="code-snippet__number">90</span>,<span class="code-snippet__string">"良好"</span>,B2<<span class="code-snippet__number">100</span>,<span class="code-snippet__string">"优秀"</span>)</span>

IFS中根据条件设置对应等级即可。

优点是逻辑清晰,便于书写。

缺点是条件数量较多时公式长,耗时长。

Excel成绩等级,销售奖金的公式模板,可以直接套用!插图1

<span class="code-snippet_outer">=<span class="code-snippet__keyword">IF</span>(B2<<span class="code-snippet__number">60</span>,<span class="code-snippet__string">"不及格"</span>,<span class="code-snippet__keyword">IF</span>(B2<<span class="code-snippet__number">80</span>,<span class="code-snippet__string">"中等"</span>,<span class="code-snippet__keyword">IF</span>(B2<<span class="code-snippet__number">90</span>,<span class="code-snippet__string">"良好"</span>,<span class="code-snippet__string">"优秀"</span>)))</span>

IF嵌套需要对IF函数有一定的理解,逻辑上不如IFS清晰。

 

Excel成绩等级,销售奖金的公式模板,可以直接套用!插图2

VLOOKUP/XLOOKUP查询

<span class="code-snippet_outer"><span class="code-snippet__section">=VLOOKUP(B2,G:H,2,TRUE)</span></span>

VLOOKUP模糊匹配是过去几十年中解决这类问题的中坚力量,需要在辅助列按升序列出区间下限,同时将第四参数要设置为TRUE或数字”1”.

模糊匹配的关键逻辑是:如果找不到查找值,则返回小于查找值的最大值。如在{0,60,80,90}找不到79时,则返回小于79的最大值60对应的“中等”。

<span class="code-snippet_outer"><span class="code-snippet__section">=XLOOKUP(B2,G:G,H:H,,-1)</span></span>

XLOOKUP第五参数设置为”-1“表示精确匹配下一个较小的项,如在{60,90,0,80}中找不到79时,则返回比79小的60对应的“中等”。

Excel成绩等级,销售奖金的公式模板,可以直接套用!插图3

逻辑上与VLOOKUP类似,优点是辅助列数据无需按升序排列。

  • =XLOOKUP(B2,G:G,H:H,,1)

也可以将区间上限提取出来作为辅助列,但同时需要XLOOKUP第五参数设置为”1”,表示“精确匹配下一个较大的项“。如在{60,90,0,80}中找不到79时,则返回比79大的80对应的“中等”。

Excel成绩等级,销售奖金的公式模板,可以直接套用!插图4

INDEX+MATCH 定位引用

<span class="code-snippet_outer">=INDEX(<span class="code-snippet__variable">$H</span><span class="code-snippet__variable">$2</span>:<span class="code-snippet__variable">$H</span><span class="code-snippet__variable">$5</span>,MATCH(B2,<span class="code-snippet__variable">$G</span><span class="code-snippet__variable">$2</span>:<span class="code-snippet__variable">$G</span><span class="code-snippet__variable">$5</span>,1))</span>

同样需要按升序排列辅助列,MATCH第三参数设置为”1”时查找小于或等于查找值的最大值。

Excel成绩等级,销售奖金的公式模板,可以直接套用!插图5

好了,今天的Excel小课堂就到此结束,大家赶紧实际操作联系一下吧,有不懂的问题可以留言问我!感谢关注Excel880,还请多多转发,持续关注我们呀!

郑广学Excel实战教程

课程咨询加微信EXCEL880B