文章目录
说明
这篇文章记录了部分我觉得稍微有点复杂vlookup的组合函数,所有的这些总结全部都是在自学后基于我自身的理解写下的,如有错误请见谅。
1.多列批量查找(vlookup+column)
- 基本格式:vlookup(a,b,column( c ),精确匹配)
例子:一次性查找原表中的数学成绩,计算机成绩,英语成绩,以及总分。
-
参数含义
a:要查找的列,在结果表里面的第一列,这个例子中就是利空,注意这个地方是锁列不锁行,因为查找的是名字这一列,这一列是不会变的,只是行在变,比如数学,计算机。#这个地方我也不知道我理解的对不对。
b:要查找的范围,在原表里面,这个例子就是把整张表圈起来
c:需要写结果的列在原表的第几列,如果是第一列就是a1,第二列就是b1,在这个例子里面数学在原表中的第三列,所以是c1
-
2.一对多查找(vlookup+countif,filter)
2.1 vlookup+countif
-
添加辅助列
-
参数含义
a:b : 表示需要计算的单元格区域,需要查找的范围。在这个例子里面我们需要给每个男女编号,但由于我们是对于不同的性别分别计数,所以这里a和b是指向同一格,并且需要将a锁列锁行,这样你往下拉的时候就会形成一个区域,在这个区域里面分别计算男女出现的次数,比如第一格是男的话,就标男一,第二格是女的话就标女一,因为第一格是锁住的,所以它会按照第一格来对比,如果不锁住,就没有对比,永远都是一。我也不知道说清楚没有,反正以后遇到这种分类计数的都把第一个参数全锁了。
c:表示查找条件,这个例子里面就是男和女,所以这格就不锁,它会跟着下拉变条件。如果这个锁了的话就只能计算第一个格子的性别,然后一直重复1,0,1,0。
-
-
利用&将性别和计算的数量连接起来。(a & countif(a:b,c))
就像这样:
-
开始查询
-
参数含义
a&b:这里指需要查找的值,a就是性别,b就是序号,a&b就是刚在用countif所编的编号。同时注意这里a锁列不锁行,因为我们查找的a这一列是不会变的,然后b是锁行不锁列,因为查找的b也就是序号这一行也不会变。
c:查找的范围,这里指原表
d:查找的值在第几列,这个例子中想要查找姓名,在原表的第三列,所以c就是3
-
ps:如果出现空值,直接在外面套个iferror函数,这里懒得写了。
2.2 filter
-
基本语法:=filter(a,b,c)
a:要筛选的数据范围
b:筛选的条件
c:如果没有满足条件的元素,返回的值(可选)
2.1 单条件
- 基本语法:=filter(a,b,c)
例子:查找一年级同学的姓名,性别和总成绩。
ps:不好意思,写到一半才发现微信的截图还有方框功能,人傻了。。。这样就更加清晰了
-
参数含义
a:在这个例子里面,a就代表你要搜查的区域,框选姓名,性别,总成绩这三列。
b:b1:就代表条件,这里的条件就是一年级,所以框选年级,年级列=“一年级”。
c:不写
-
2.2 多条件
-
基本语法:
=filter(a,(b*b1),c) # b且b1
=filter(a,(b+b1),c) # b或b1例子:找出一年级且是男生的同学的总成绩
-
参数含义
a:这个例子里面,a就是总成绩这一列,因为我们只查找总成绩。
b*b1:这里我们需要把两个条件结合起来:(年级列=“一年级”)*(性别列=“男”)
c:不写
例子:找出一年级或者男生的同学的总成绩
-
参数含义
a:这个例子里面,a就是总成绩这一列,因为我们只查找总成绩。b+b1:这里我们需要把两个条件结合起来:(年级列=“一年级”)+(性别列=“男”)
c:不写
-
3.多列动态查找(vlookup+match)
- 基本语法:=vlookup(a,b,match(c,d,精确匹配),精确匹配)
例子:查找所有人的姓名,总成绩,性别,但是列的顺序和原表不同。
-
参数含义
a:这里指需要查找的值,也就是年级,同时需要锁列不锁行,因为等下右拉和下拉的时候,根据的都是年级这一列,所以锁定这一列。b:需要查找的范围,这里也就是框选原表。
c:由于三个列名顺序和原表不一样,所以我们需要动态查找,首先框选第一列姓名列,然后锁行不锁列,因为等下右拉还是下拉,都是根据姓名,总成绩,性别这一行去原表寻找。
d:这里指的是查找的范围,这里也就是框选原表的所有列名。
-
4.跨多表查找(indirect)
- indirect 函数的两种引用方法:
方法 | 引用方式 |
---|---|
间接引用 | 不需要打双引号 |
直接引用 | 需要打双引号 |
例如:
indirect(选中单元格,这个单元格写的B1,但是实际位置并不是B1),属于间接引用,不用打双引号。
indirect(“B1”),属于直接引用,需要打引号。
-
跨多表查询的基本语法:=vlookup(a,indirect(b&c),d,精确匹配)
例子:分别从一年级表,二年级表,三年级表中查找到总成绩。
二年级表:
三年级表:
- 参数含义
a:代表要查找的值,这里也就指的是姓名,通过姓名去查找,但是注意这里需要锁列不锁行,因为无论是向下拉还是想右拉,通过这列去查找都不会变。
b&c:由于是跨表查找,这里就需要我们去引用,其中b是代表的表的名称,因为我工作簿名称分别是一年级,二年级,三年级,和列名称一样,所以b就是间接索引,通过列名去索引到工作簿,注意这里需要锁行不锁列,因为我始终是根据年级列名这一行去查找。而c呢,就是跟在工作簿后面的所要找寻的单元格,它是固定的文本,因为在所有表中都是在这一块,所以添加引号表示静态文本。(但是我认为也可以理解成直接引用的一种,因为这就是我们的最终结果,所以就是直接指向,也就是直接索引,所以需要加双引号。)
d:代表查找结果在搜索表的第几列,这里我们需要查找的是总成绩,在各表中是第四列,所以这里是4.
总结
真的难写,写好半天。。。