if嵌套vlookup函数使用方法
在 Excel 中 IF 和 VLOOKUP 都是很常用的函数,它们都可以在指定的条件下返回需要的结果。当你不确定使用哪一个时,告诉你一个好的方法—看完全文。
本文通过两个示例说明在哪种情况下用嵌套 IF 或是 VLOOKUP。
示例 1
例如,我们要根据卖家的销售额来找出对应的佣金比例,为此单独创建了一个佣金比例表(见下图)。
其中,第一列是按销售额划分的区间,每个区间对应的不同的佣金比例。第二列销售额是每个区间的最低下限,也就是说,只有销售额大于最低下限才能享受对应的佣金比例。还有一点比较重要,就是设置的区间参数要有顺序,要按从低到高排序(如下图)。
使用嵌套 IF 公式如下:
=IF(C3>200,10%,IF(C3>100,7%,IF(C3>50,5%,IF(C3>1,3%,""))))
公式中并没有引用佣金比例表中的数据,而是直接输入了销售额及佣金比例。如果要直接引用应把公式写成:
=IF(C3>$G$6,$H$6,IF(C3>$G$5,$H$5,IF(C3>$G$4,$H$4,IF(C3>$G$3,$H$3,""))))
这样编写公式的好处是当调整销售额区间或佣金比例时不用再修改公式了。
这里你会发现上面公式用 IF 函数是从高到低来判断的,即先判断大于 200的销售额然后逐级递减,而不是像佣金比例表中的由低到高的顺序。如果按与比例表中相同顺序编写公式,结果会出现错误,具体原因请阅读:Excel中的嵌套 IF – 具有多个条件的公式
使用 VLOOPUP 函数公式如下:
=VLOOKUP(C3,$G$3:$H$6,2,TRUE )
其中:
- C3 是要查找的值,也就是示例中的销售额
- $G H$6 是查找的返回值所在区域,是佣金比例表的第二和第三列
- 2 是查找区域的第二列,也就是返回佣金比例所在的列
- 最后一个参数设置为 TRUE(可省略),是让 VLOOKUP 查找近似匹配
这里要注意,使用近似匹配查找,查找区域的第一列必须按升序(从低到高)排列。这也是为什么开始设置佣金比例参数是要按从低到高排序了。
示例 1 的结论
首先,当只有一个条件时(如只根据销售额),使用 VLOOKUP 更容易编写公式,且比嵌套 IF 公式短很多,也更便于阅读。其次,如果在佣金结构中添加或删除一个层级,虽然这是表中的简单操作,但对于嵌套的 IF 公式来说,它要复杂得多。因此,这里使用 VLOOKUP 的优势很明显。
示例 2
例如,要根据测量的血压情况来确定血压水平的级别。下图中,上边的表是测量血压的数据,下面的表是分级表,其中后两列是对血压值的定义及分级。前三列是为了编写计算公式而特别添加的参数。
计算血压分级的逻辑是,“正常”级别是同时满足高压(收缩压)<140,低压(舒张压)<90;其余 3 个级别都是满足其中一项条件,就可确定为对应的级别。如高压 145,低压 80,高压值 145 在 1 级的收缩压 140~159 范围内,就确定为 1 级。
因此根据上图的条件,用嵌套 IF 编写的公式如下:
=IF(OR(E3>=180,F3>=110),"3级",IF(OR(E3>=160,F3>=100),"2级",IF(OR(E3>=140,F3>=90),"1级",IF(OR(E3>=120,F3>=80),"正常高值","正常"))))
通过使用 OR 函数,可以在每个 IF 函数的判断两个或多个不同的条件,如果其中一个 OR 参数的计算结果为 TRUE,则返回 TRUE。
从高到低的顺序检查 3-1 级,剩下的就都是正常级别。
再来看下用 VLOOKUP 的公式
=VLOOKUP(MAX(VLOOKUP(E3,$J$3:$L$7,3),VLOOKUP(F3,$K$3:$L$7,2)),$L$3:$M$7,2,FALSE)
先用两个 VLOOKUP 分别查找近似匹配高、低压对应的编号,借助 MAX 函数返回两个编号中最大数值的编号,最外层 VLOOKUP 的查找精确匹配编号,并返回对应的分级。
示例 2 的结论
首先,当有两个条件时,在编写公式的难易程度上用嵌套 IF 要比 VLOOKUP 更容易些,且更符合逻辑。其次,嵌套 IF 语句不需要辅助参数就可完成公式,而 VLOOKUP 需要提前设置好辅助参数。因此,这里使用嵌套的 IF 语句要更灵活。
总结
当仅一个条件时,应当使用 VLOOKUP
在两个或更多条件时,可考虑嵌套 IF 语句(在没有更好的替代方案时)
这个是血压跟踪表的全貌,其中状态一列是使用的嵌套 IF 语句。如果对这方面有需要的可以做下参考。(里面的血压数据是函数随机生成的,别当真的哦)
最新推荐
-
华硕笔记本的移动热点怎么开启 win10笔记本开热点
华硕笔记本的移动热点怎么开启?通过将电脑设置为移动热点,其他设备可以通过连接到您的电脑热点来共享网络连接。 […]
-
wps全部删除页眉页脚怎么删除
WPS是一款非常好用的文档编辑和存储的软件,我们平时编辑的一些文档也都会有不同的格式要求,最近有朋友问wps页眉页脚怎么删除?为了解决这个疑惑。下面小编就来给大家介绍一下具体的操作方法,希望能对大家有所帮助。 WPS Office V11.1.0.12763 官方电脑版 [电脑软件] 大小:5.15 MB 类别:办公管理 立即下载
-
win11默认浏览器怎么更换 win11更换默认浏览器
win11默认浏览器怎么更换?在最新的win11系统中,微软进行了大范围的功能调整,比如用户常用的默认应用 […]
-
剪映自动续费怎么关 剪映app怎么取消自动续费支付宝
剪映自动续费怎么关?剪映APP是一款强大的视频编辑工具,用户通过使用付费功能,可以创造出很有个性的视频,那 […]
-
google浏览器怎么添加信任站点 谷歌浏览器给网址添加信任
我们在使用谷歌浏览器的过程中,遇到了不信任的站点,通常都会直接将其拦截屏蔽无法访问,如果我们想要继续浏览,那么就需要将这个站点添加为信任站点方可访问,但是很多人都不知道应该要如何进行操作,针对这个问题,本期小编就来和广大用户们分享具体的操作步骤,接下来就让我们来了解看看吧。 谷歌浏览器 V111.0.5563.65 官方版 [电脑软件] 大小:1.27 MB 类别:网络软件 立即下载
-
win10在哪里设置默认打印机 win10设置默认打印机的方法
win10在哪里设置默认打印机?在Win10系统中,通过设置好默认打印机,可以方便用户每次打印文档时都使用 […]