如何按区间模糊匹配?除了以前教过的 lookup 函数,今天再分享两个异曲同工的公式。

案例:

将下图 1 中的完成率转换成等级,规则如下:

  • >=100%:绿
  • >=80% 且 <100%:黄
  • <80%:红

效果如下图 2 所示。

excel中如何按区间模糊匹配?2个方法轻松实现-趣帮office教程网

excel中如何按区间模糊匹配?2个方法轻松实现-趣帮office教程网

解决方案 1:

1. 在右侧建立等级对照表。

excel中如何按区间模糊匹配?2个方法轻松实现-趣帮office教程网

2. 在 E2 单元格中输入以下公式 --> 下拉复制公式:

=VLOOKUP(D2,$G$2:$H$4,2)

公式释义:

  • 首先强调一点,对照表中的第一列必须是升序排序;
  • VLOOKUP 函数本身不用多讲,但是与以往不同,这个公式中省略了第 4 个参数,表示返回近似匹配;
  • 如果 VLOOKUP 在查找区域中找不到精确匹配值,会返回小于查询内容的的最大值,从而实现了区间匹配。

* 请注意公式中的绝对和相对引用。

excel中如何按区间模糊匹配?2个方法轻松实现-趣帮office教程网

excel中如何按区间模糊匹配?2个方法轻松实现-趣帮office教程网

excel中如何按区间模糊匹配?2个方法轻松实现-趣帮office教程网

解决方案 2:

1. 在 E2 单元格中输入以下公式 --> 下拉复制公式:

=MID("红黄绿",MATCH(D2,{0,0.8,1}),1)

公式释义:

  • MATCH(D2,{0,0.8,1}):
    • 将 D2 单元格的值与序列 {0,0.8,1} 中的值比对,返回符合结果的值所在的位置数;
    • match 函数的第 3 参数省略,默认为 1,即近似匹配;在找不到精确值的情况下,会返回小于或等于查询内容的最大值;
    • 这段公式表示在 {0,0.8,1} 查找小于或等于 D2 的最大值,返回该值在序列中的排名数;
  • MID("红黄绿",...,1):在“红黄绿”中从 match 返回的值(即代表查询结果所在位置的一个数字)开始取数,只取 1 位数

* 公式中无论 match 的第二参数还是 mid 的第一参数都要升序排序,且一一对应。

excel中如何按区间模糊匹配?2个方法轻松实现-趣帮office教程网

excel中如何按区间模糊匹配?2个方法轻松实现-趣帮office教程网

excel中如何按区间模糊匹配?2个方法轻松实现-趣帮office教程网