excel求符合条件区间 Excel-区间等级查找

区间/等级查找通常用于：成绩等级判定、销售提成计算、个税计算、业绩考核、绩效考核等等。

今天介绍几种方法，简单易上手。

一、IF函数的多级嵌套

IF函数的多级嵌套，其实很多人很难理解。实际上，把多级嵌套当成剥洋葱就好了。

IF函数的语法结构：

=IF(判断条件，满足条件时的返回值，不满足条件时的返回值)。

IF函数嵌套的结构特点：将向下个IF函数嵌套在上一个IF函数的第三参数中。

实例：判定各个同学的成绩分数等级

①输入公式：

升序：=IF(J4<60,"不及格",IF(J4<80,"及格",IF(J4<90,"良好",IF(J4<100,"优秀","满分"))))

或

②降序：=IF(J4=100,"满分",IF(J4>=90,"优秀",IF(J4>=80,"良好",IF(J4>=60,"及格","不及格"))))

注意事项：

（1）IF 函数在进行区间/等级判断时，数字一定要按顺序判断，要么升要么降。

（2）多层嵌套的解析：（与剥洋葱一致）

①嵌套，通常是将IF函数的第三参数变成一个新的IF函数返回。嵌套后很像洋葱，从外往里，每层一个IF判断，最多可以有64层。

②函数嵌套有多少个IF函数，最后结尾就需要多少个括号来闭环。一层嵌套，就有一对括号，有多少层嵌套就要写多少对括号。每对括号的颜色是不同的（在编辑状态下），最外一层括号肯定是黑色的。

二、IFS函数 (Excel2019版本才能用）

IFS函数，顾名思义，就是IF的复数形式，是多条件判断函数，是对IF函数嵌套的简化和升级版，更容易理解和操作。

实质：新的IFS函数相当于多个IF函数的组合，可以简化IF函数的多层嵌套问题。

功能：对多个条件依次进行判定

IFS函数的语法结构：

=IFS(判断条件1，返回值1，判断条件2，返回值2……判断条件N，返回值N)

实例：判定各个同学的成绩分数等级

输入公式：

=IFS(T14<60,"不及格",T14<80,"及格",T14<90,"良好",T14<100,"优秀",T14=100,"满分")

注意事项：

①对于多条件判断/多层嵌套，可以利用IF函数嵌套，但是IFS函数更简单且更容易理解。

②使用时需要注意逻辑顺序，输入数值比较型判断条件时，参照数值要么从小到大，要么从大到小，否则容易出错。

三、Vlookup函数近似/模糊查找（之前有讲过，再次补充）

Vlookup近似查询：当函数查找不到精确的结果，函数就会返回小于查找值的最大值，从而完美取代了IF函数的多层嵌套。

语法结构：

=Vlookup（查找值，查找区域，查找列数，精确匹配或模糊匹配）

精确匹配：用FALSE或0来表示，模糊匹配：用TRUE或1来表示。

实例：判定各个同学的成绩分数等级

方法一：（无需辅助查询区域）

输入公式：=VLOOKUP(T14,{0,"不及格";60,"及格";80,"良好";90,"优秀";100,"满分"},2,1)

方法二：（需要辅助查询区域）

第一步：创建辅助查找区域

①让每一个区间的最小值来对应这个区间的结果。

②辅助查找区域中查找范围（分值）必须从小到大排列。

创建的辅助查找区域

第二步：Vlookup近似查找

Vlookup进行近似查找时，查找区域的首列必须升序排序，也就是这个辅助查找区域的首列。

输入公式：=VLOOKUP(T14,$W$13:$X$18,2,1)

注意事项：

①查找区域首列必须是升序排序的，并用F4键将查找区域锁定。

②Vlookup函数的第4参数省略，默认进行模糊查找。

③当查找不到精确的结果，则返回小于查找值的最大值。

四、Lookup函数区间查找（之前有讲过，再次补充）

Lookup函数基础语法：

=Lookup（查找值，查找列（必须升序排列），结果列）

两种形式：

①向量形式：在一行或一列中查找。

=Lookup（查找值，查找区域为单行或单列，结果区域为单行或单列）

②数组形式：在数行或数列中查找。

=Lookup（查找值，查找区域为多行多列）

六个通用公式：(已验证，均可使用)

① =Lookup(查找值, 查找区间）

Lookup函数-公式1实例

②=Lookup(查找值，查找列，结果列）

Lookup函数-公式2实例

③=Lookup(查找值,{分隔点1,分隔点2,分隔点3,……},{结果1,结果2,结果3,……}）

Lookup函数-公式3实例

④=Lookup(查找值,{分隔点1;分隔点2;分隔点3;……},{结果1;结果2;结果3;……}）

Lookup函数-公式4实例

⑤=Lookup(查找值,{分隔点1,分割点2,分隔点3,....; 结果1,结果2,结果3,.....})

Lookup函数-公式5实例

⑥=Lookup(查找值,{分隔点1,结果1;分割点2,结果2;分隔点3,结果3;.....})

Lookup函数-公式6实例

注意事项：

①查找区域首列必须是升序排序的，并用F4键将查找区域锁定。

②当找不到查询值时，则返回小于查找值的最大值。

③当只有2个参数，结果列没有的时候，查找列也是结果列

五、Index Match组合函数（之前有讲过，再次补充）

（1）Index函数

Index函数：用于查找区域行列交叉的值。

套用公式= Index（查找区域，行序号，列序号，[区域序号]）

常用语法结构：=Index（查找区域，行，列）

当省略【列】值时，默认为第一列。

（2）Match函数

Match函数：用于查找数据在区域中的位置，返回代表位置的数字。即第几行第几列。

套用公式=Match（查找值，查找区域，查找方式）

其中查找方式有：1、0、-1三种。

• -1：查找小于或等于查找值的最大值；
• 0 ：查找等于查找值的第一个值；
• 1 ：查找大于或等于查找值的最小值。

（3）Index Match组合

常用的万能公式是：

=Index（单元格区域，Match函数的行位置，Match函数的列位置）

实例：判定各个同学的成绩分数等级

输入公式：

①=INDEX($X$13:$X$18,MATCH(T14,$W$13:$W$18,1))

或：

②=INDEX($X$14:$X$18,MATCH(T14,{0,60,80,90,100},1))

Index Match-公式1实例

Index Match-公式2实例

注意：

①查找区域首列必须是升序排序的，并用F4键将查找区域锁定。

②Index Match搭配使用时，Index函数的第一个参数区域（即结果列），一定要和Match的第二个参数区域（即查找列）起始行一致，否则，会出现查找错位。

六、Choose Match组合函数（后续Choose函数会讲解）

七、Offset Match组合函数（后续Offset函数会讲解）

前四种方法比较常用，后三种方法了解即可。

,