Excel 多条件区间筛选求值几种方法

Excel 多条件求值用到的地方还是比较多的,比如给定区间查询费率,给定成绩查询合格,给定条件打分之类的,实现的方法有很多,不同的条件有不同的方法,有些方法更加实用简单点,适合自己用,有些方法说起来简单算起来复杂但机械,这种就适合远程给不懂的人讲,少废话

一般来说要熟悉多条件查询下面这几个函数一定要了解的

IF 函数:最简单的,假如A1>90取值多少,否则取值多少,否则里面可以再嵌套,简单好理解,所以基本上来说不管多少个条件求值都可以用这个算,只要你不怕烦,远程教别人时最好用,Excel 2016 可以用 IFS 函数代替,更加简单明了

LOOKUP 函数:在一个一维数组中查找某个值,返回另一个一维数组同一位置另一个值,可以通过模糊查询来实现0-10对应A这样的效果

VLOOKUP 函数:LOOKUP 增强版,在二维数组第一列查询某个值,返回这个值同一行的任意值,相同于 N 个 LOOKUP 叠起来使用,可以模糊查询,但这里的模糊查询更多的感觉像是使用通配符,有时会超出你想象,意义不大

INDEX 函数:在一个二维数组中通过行号、列号来确定一个唯一的值,参考下面例子,2×2的条件打分最好用,有时需要 MATCH 函数帮忙来确定行号列号

MATCH 函数:查找一个值在一个一维数组中的位置,就是序号,可以通过模糊查询来匹配区间值,实现查找区间任意数值在区间的位置,和 LOOKUP 差不多

AND 函数:好理解了,几个条件都符合那就对了,配合 IF 用来多条件求值用的

关于模糊查询来匹配区间值,MATCH、LOOKUP 包括其他函数中要求某一参数要顺序排列都是为了这个目的,比如这种查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须以升序排序,说起来有点拗口,但小于或等于 lookup_value 的最大值的意思就是如果有数组{0,5,10},0-5(不含5)之间任意数都视为0来计算,5-10(不含10)之间任意数都视为5来计算,10以上任意数全部视为10来计算,这就是必须升序排序的原因,降序排列的同理

平时用到条件查找筛选求值的几种方法就记下来了

=IF(A1>90,"优秀",IF(A1>80,"良好",IF(A1>60,"及格","不及格")))
好理解了,成绩大于90分优秀,大于80分良好,大于60及格

=IF(AND(A1>=60,B1>=60),"合格","不合格")
也好理解,两科都60分以上才算考试合格

=INDEX({10,8;4,2},MATCH(A1>=50,{TRUE,FALSE},0),MATCH(B1>=50,{TRUE,FALSE},0))
考评打分时很常用的一个函数,A1≥50时,如果B1≥50得10分,B1<50得8分;A1<50时,B1≥50得4分,B1<50得2分,扩展的话A1、B1可以分成更细的阶段,参考官方 MATCH 的用法

=LOOKUP(A1,{0,5,10},{10,8,9})
如果A1是0-5(不含5)之间任意数就得10分,5-10(不含10)之间任意数就得8分,10以上任意数都得9分,两个数组都可以扩展,一一对应出结果,但第一个数组必须升序,如果给的条件没办法让第一个数组升序排列,那就0-A1

=VLOOKUP(A1,{1,2,3;4,5,6;7,8,9},3,FALSE)
如果A1=7,查到的是第3行,要求返回的是该行的第3个数字,所以是9

这些都要组合起来用,比如有时感觉好像用 LOOKUP 更好,但是其实 IF 更简单,因为{}里面只允许出现常量,连%都不能出现

另外:{}括起来的表示一个数组,;分开的表示换行,没有的就是一维数组,比如{1,2,3,4,5}就表示A1:E1{1,2,3,4,5;6,7,8,9,10}就表示A1:E2

1 2 3 4 5
6 7 8 9 10

来自于https://support.office.com/zh-cn/article/Excel-函数(按类别列出)-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

发布者:最爱你是谁

悄悄的我走了,正如我悄悄的来;我挥了挥衣袖,却没留下一点精彩。

留下评论

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据