excel跨多个表查数据
一、需求:分班后名单全部打乱需要找原班级数据
要点:
1、跨工作表查
2、跨多个工作表查
3、要使用多个函数嵌套配合
二、思路
1、INDIRECT查找数据表和对应列
=INDIRECT(单元格引用,[引用样式])
这里我们直接用
=INDIRECT(“表名”&“列名”)
=INDIRECT(“806”&"!C:C")
# “806” 是表的名称、 “!C:C” 是列数据
这里查出来的数据就是“807”数据表的C列名字
看看“807” 数据
最后查出来下拉
查到的就是“807” 表格C列上的数据。
2、INDIRECT多表格也可以查
=INDIRECT({"806";"807";"808"}&"!C:C")
# 这里数组{"806";"807";"808"}
# "里面是表名",用 ; 隔开。后面一样是表示C列
# !!!⚠️注意这里只能查第一个表的数据,所以需要配合下一个函数
3、COUNTIF函数判断
# COUNTIF(区域,条件)
COUNTIF(INDIRECT({"807";"808"}&"!C:C"),B3)
// 这里区域就是上面第二点的INDIRECT查到的C列,条件就是B3姓名,李四1,明显807 没有李四,所以得到的结果是0 对比C列的和现在表格的B列能不能对上姓名,对上1,对不上0
但是如果把B3换成D3的张三1,那么数据就变成了1。
4、LOOKUP配合前两个确定表名
最后配合LOOKUP(1/({0,1}),{"1";"2"}) 这里找对应的1(他在第二个),所以返回的是数组{"1";"2"}里第二个 把数组换成表名就可以找到表名了 把{0,1}换成前面的COUNTIF(INDIRECT({"701";"702"}&"!C:C"),B3) 就可以确定哪个表的数据和B3相等,返回1 !!!⚠️ 这里{0, 1} 是COUNITIF配合INDIRECT查的数组{"701";"702";.....;"801"} 这个数组都是表名 /(0;0;0;1;0;0;0) 这个是查出来的结果 !!! 后面的 {"1";"2"}数组是对应前面结果的,一一对应,这样取出来的结果就是所对应的第二个数组 总之前后数组对应起来。
5、全部连起来
!!!注意这里的806不是直接查的表名,而是查出姓名所在哪个表,然后对应后面数组的位置,如果后面数组“806”改成aaa
那么806班的查出来就是aaa
为啥要对应表名呢,因为后面需要用到这个。
三、点睛之笔,VLOOKUP
VLOOKUP(查找值,数据表,序列数,[匹配条件])
1、这里查找值很明显就是B3对应的姓名。
数据表就是上面全部连用所查出来的数据表
这里“803”&“!C:I” 是去803表找C:I区域的数据
最后VLOOKUP,每次只改变一个数字就可以查不同列的数据
这把7改成1就是对应姓名,改成2就是成绩