备注:
- 本文只涉及单元格 cells range 等查询
- 不涉及 find() 等函数公式对 字符串 string的查找
1 什么是查找? inputs----f(x)----outputs
1.1 你的目的
- 你要查什么?
- 查符合条件的(1或多个)单元格在哪儿?
- 查符合条件的(1或多个)单元格的值是什么?
- 查符合条件的(1或多个)单元格有没有
- 查符合条件的(1或多个)单元格的其他
1.2 你的预期(先验预期,先验结果)
1.2.1 先要考虑用函数查询可能出现的结果
- 查符合条件的(1或多个)单元格在哪儿?
- 查符合条件的(1或多个)单元格的值是什么?
- 查符合条件的(1或多个)单元格有没有
- 查符合条件的(1或多个)单元格的其他
1.2.2 扩大思维宽度,完整的考虑问题
以上的分支,其实只是1个子集
- if 如果查不到呢?怎么办,返回什么?
- if 如果能查到,且是唯一的结果,返回所要的内容(是否有,序号,内容,等待...)
- if 如果能查到,多个符合要求的结果,你是要多个?还是要1个(如果是要1个,要哪个呢?....)
1.2.3 考虑如何处理这些不同的输出
- if 如果查不到呢?怎么办,返回什么?
- 这个函数会报错么?
- 这个函数会返回一个符合要求的最大值,以期最解决查找目标?比如lookup() ,这个是我需要的吗?这个特性可以故意利用吗?
- if 如果能查到,且是唯一的结果,返回所要的内容(是否有,序号,内容,等待...)
- 查符合条件的(1或多个)单元格在哪儿? 用match() 可以查一维数据(单行/单列)的相对或绝对位置(行序号号,列序号等)
- 查符合条件的(1或多个)单元格的值是什么? 用vlookup()等查第1个,或lookup()查最后1个
- 查符合条件的(1或多个)单元格有没有,用if() countif()
- 查符合条件的(1或多个)单元格的其他, 用if() 因为if()可以定义多种返回类型
- if 如果能查到,多个符合要求的结果,你是要多个?还是要1个(如果是要1个,要哪个呢?....)
- 如果有多个符合要求的返回值,你是要多个,还是要1个?
- 如果是要多个,那就用数组公式,数组存起来
- 如果是要1个,你用的这个函数是否可以选择要的是哪个?
- 如果是用large()等处理,可选择返回的是哪个?
- 如果用的是vlookup()等处理,默认会返回符合条件第1个,无法挑选
1.2.4 如果是查多个,需要用数组公式
下面详细再说
1.2.5 上述原理解释
- inputs:输入,原始数据
- 函数/公式:处理过程
- outputs: 输出,输出结果
1.3 哪些会影响输出的结果
1 .3.1 raw_data 和 f(x) 的关系
- 数据+查询目的+查询函数,可能有很多种不同的组合
1.3.2 会影响查询结果的重要因素,查询数据区存在几种情况:不同的排序!
排序,排序,还是排序!!!(数值 数组才考虑排序,且不在乎重复,只在乎排序)
- 升序数值(不在乎重复)
- 降序数值(不在乎重复)
- 乱序
1.3.3 使用的函数和参数不同
比如 match(1,range,0) match(1,range,1) match(1,range,-1)
1.3.4 其他
- 比如
- 原始数据错误
- 拼写错误
- 函数错误等等
2 EXCEL查找数据的基础方法:菜单查找
- crtl+F 查找
- 注意:查找数据,查找公式
- 注意:查找下一个 查找全部
- 注意:查找,查找全部
- 替换功能
- 筛选功能
3 单条件查询
3.1 if()
- if() 是最基础的判断函数,基础的基础
- IF(B9=L$5,L$5) 或 IF(B9=L$5,row(L$5))
- 最弱的地方:每次只能判断1个数据,多个数据判断,需要往下拖公式 (和数组公式组合用有奇效~)
- 最强的地方:if() 基础而强大,如果能找到,后面true的地方,些row(A1) 或 =A1 取行数,内容都可以
3.2 match()
- match() 这个函数本身只适合一维(也就是1行或1列的情况,但可以和其他函数组合使用处理2维数据)
- 只适合取行号(绝对或相对的)
- 可以取绝对行号,或者相对 行序号,看match() 内部本身怎么写
- 函数返回结果:如果有多个,只能返回第一个符合条件的值的行号
- match(,0) match(,1) match(,-1)
- match(,0) ,如果可以查到显示行序号,查不到显示为#NA
- match(,1)
需要升序排列(从小到大),如果可以查到显示行序号
如果要查的目标,比最小的还小,查不到显示为#NA
如果要查的目标,比最大的还大,则会显示最接近(最大/最末尾的)的值
- match(,-1)
需要降序排列(从大到小),如果可以查到显示行序号
如果要查的目标,比最小的还小,则会显示最接近(最小/最末尾的)的值
如果要查的目标,比最大的还大,查不到显示为#NA
3.3 match()和其他函数的套用
index() 和 match() 联合使用
- 因为 match() 可以取得行号(绝对/相对行序号)
- index() 可以匹配着用 绝对/相对行号
- INDEX(D9:D28,MATCH(J5,B9:B28,0))
- 也是自由数组偏移
- 只能查到第1个
3.4 vlookup()
- vlookup只适合查内容,尤其是偏移的其他列的内容
- 但取不了行号等
- 函数返回结果:只能查到符合条件的第1个
3.5 hlookup()
- 类vlookup()
3.6 lookup()
- 无比强大,也相当麻烦
- 最大的优势:返回的是符合条件的最后1个数据(直接使用需要升序排列)
- 原理是二分法查数据
- lookup() 中间过程会用到序号(绝对的或相对的)
- LOOKUP(H5,B9:B28,D9:D28)
- LOOKUP(H5,B:B,D:D)
lookup() 使用注意点
- 需要用lookup() 查询的时候,要查询的数组,必须是升序的。(目标数组可以不用),如果不是升序,会出现错误
- 如果lookup() 数据升序,且查不到目标值,不会报错,函数会返回最后一个值的序号。
- 这个最好的就是,允许数组自由偏移。
- 既不需要vlookup()那样从左边第1列查起,也不需要2列对齐,但2列最好数据个数相同。
lookup() 的特殊写法
- lookup()有一种特殊写法,可以无视 查询数组是否排序
- lookup(1,0/(a:a=15,b:b))
lookup() 的特殊用法---查多列
- 如果 lookup() 查找的是多列,那么会自动按区间匹配,<=的合适区间
- LOOKUP(G21,B9:C13)
3.7 数组公式{if()} 也可以用于单一条件查询
- 数组公式
- 这里通过先计算数组,中转过程,最后得到1个值
- =INDEX(C:C,IF(B9:B28=N5,ROW(B9:B28)))
- 这里得到的是1个数组,但得到数组并不是目的
- =INDEX(C:C,LARGE(IF(B9:B28=N5,ROW(B9:B28)),2))
- 得到数组只是中间过程,最终还是要得到数值
举例,用这个看计算过程
INDEX(C9:C28,IF(B9:B28=看计算给N5,ROW(B9:B28)))
4 多条件查询
4.1 EXCEL的内置单个函数,sumifs() 或 countifs(),只能返回 或 查到的个数
- 并且是按内容查找
- 并且只支持单列,单行,也是一维的!
- 比如下面得2个,多条件查询
- SUMIFS(C10:C29,A10:A29,G6,B10:B29,G7)
- COUNTIFS(A10:A29,I6,B10:B29,I7)
4.2 match()和其他函数的套用
- 虽然match() 也只支持单行,单列。选范围就报错。
- 但是match() 可以和其他函数结合,match()只查单行,单列内容
- index(match()) 查行/列 偏移
- indirect("r",match(),"c",match(),false) 用r1c1去查找行列交叉的
- match(match())
- vlookup(match())
4.3 按内容查找,按行列号查找,按行列序号查询
- 根据内容去查找 , 比如 sumifs() countifs()
- 根据行列号去查找 indirect("r",match(),"c",match(),false)
- 根据行列号偏移去查找 vlookup(match())
4.4 笨办法:就是加辅助列。从而多条件转1条件,不完美,但是好用。
- 下面得例子
- 直接生成一个辅助列,把 "条件1"& "识别特征"&" 条件2" 作为新得查询条件,就从多条件转为1条件了。
- MATCH(K6&"and"&K7,D10:D29,0)
- VLOOKUP(M6&"and"&M7,D10:D29,1,FALSE)
5 多条件查询方法:数组公式