四张工作表存储在一个Excel工作簿中,工作表的名称分别为“人事部”、“销售部”、“财务部”、和“查询表”。目的是根据“查询表”中的“员工姓名”查询其所在的部门和月薪?查询所在的“部门”和“月薪”?
看起来还是简单的查询引用,但仔细分析会发现,“小乔”和“黄盖”在“人事部”;“曹操”和“诸葛亮”在销售部;“司马懿”、“甘夫人”、“孙尚香”在生产部,而且基础信息表中并没有“部门”列,那如何实现查询了?不急,我们先来学习两个函数。
一、Indirect函数。
功能:返回文本字符串所指定的引用。此函数立即对引用进行计算,并显示其内容。如果需要更改公式中对单元格的引用,而不更改公式本身,则可以使用函数Indirect。
语法结构:=Indirect(单元格引用,[单元格引用样式]);“单元格引用样式”分为A1-样式和R1C1-样式。
1.如果“单元格引用”是对另一个工作簿的引用(外部引用),则此工作簿必须被打开,否则返回错误值#REF!。
2.“单元格引用样式”为逻辑值,如果为TRUE或省略,则为A1-样式的引用;否则为R1C1-样式的引用。
应用技巧:
1.函数的参数:单元格名称或符合单元格地址的字符串。
2.函数的返回值:参数所指定的单元格的值。
示例,如下表:
1.在C3单元格中输入公式:=INDIRECT('a6'),则返回值为:A3。
解读:在公式中,参数给定的即是字符串,也符合单元格地址格式,所以直接对参数代表的单元格的值进行引用。
2.在C3单元格中输入公式:=INDIRECT(A6),则返回值为:1。
解读:此时的返回值为1,即A3单元格的值,因为参数指定的A6,在这里A6是一个单元格的地址,而非字符串,所以要取出单元格A6(也就是字符串“A3”)的值,然后对取出的地址(A3)进行取值得到1。
3.在C3单元格中输入公式:=INDIRECT(A3),则返回值:#REF!。
解读:
Indirect函数的功能为:返回文本字符串所指定的引用,而公式=INDIRECT(A3)中,A3单元格的值为1,没有一个地址为1的单元格,所以会报错。
4.在C3单元格中输入公式:=INDIRECT(B3&A5),则返回值为1。
解读:
虽然公式参数中有“&”连接字符,但参数仍然不是字符串,更不是单元格地址或者名称,首先要做的就是将参数转化成字符串格式的地址或者名称。B3单元格的值为“A”,A5单元格的值为3,通过&连接后的字符串为“A3”,因此同时也就转化成了=INDIRECT('A3'),所以最终的返回值为1。
二、Countif函数。
功能:计算指定区域中满足给定条件的单元格个数。
语法结构:=Countif(条件范围,条件)。
目的:计算相应“学历”的人数。
方法:
在目标单元格中输入公式:=COUNTIF(F3:F12,J3)。
三、Lookup函数。
功能:从单行或单列或数组中查找符合指定条件的值。
语法结构:=Lookup(查询值,查询值所在的区域或数组,[返回值所在的区域或数组])。
目的:根据员工姓名查询对应的“月薪”。
方法:
在目标单元格中输入公式:=LOOKUP(1,0/(B3:B12=J3),G3:G12)。
四、Vlookup函数。
功能:搜索表区域满足条件的元素,确定待检索单元格在区域中的行号后,再进一步返回选定单元格的值。
语法结构:=Vlookup(查询值,查询值所在的数据范围,返回值的相对列数,[匹配模式])。
目的:根据员工姓名查询对应的“月薪”。
方法:
在目标单元格中输入公式:=VLOOKUP(J3,B3:G12,6,0)。
五、多表查询。
(一)判断员工所属部门。
在目标单元格中输入公式:=COUNTIF(INDIRECT({'人事部';'销售部';'生产部'}&'!b:b',1),B3)。
解读:
选取公式,按F9键之后,可返回一个数组{1;0;0},其中非0数字1的位置就是员工所在工作表的位置,即“人事部”的工作表中。
(二)返回员工所属部门名称。
在目标单元格中输入公式:=LOOKUP(1,0/COUNTIF(INDIRECT({'人事部';'销售部';'生产部'}&'!b:b',1),B3),{'人事部';'销售部';'生产部'})。
解读:
此用法为Lookup函数的经典用法,其实质还是向量形式。
(三)返回员工的月薪。
在目标单元格中输入公式:=VLOOKUP(B3,INDIRECT(C3&'!b:g',1),6,0)。
解读:
此公式的重点在于INDIRECT(C3&'!b:g',1),其含义请参阅文章中Indirect部分的解读。
联系客服