EXCEL：查询系统的制作与跨表引用

一、薪资查询系统

（一）前期资料

1、人事部门中的一些薪资情况的资料（作为基础数据库）

2、根据基础数据库建立以下查询表【该查询表的具体类目可以依据自己想要提取的信息进行制作】，根据年、月、员工编号、部门提取出以下信息。

（二）查询系统的制作

1、方法1：VLOOKUP

（1）年、月和部门部分可以使用数据验证的形式，使其变成下拉列表的模式

①设置数据验证

② 数据验证的序列来自于参数表中年份的数据

③月份和部门同理

（2）根据数据库的内容，构造唯一编号 

①从这个数据库可以看到，我们需要同时确定年、月、工号和部门才能确定提取某一行的数据

②构造唯一值，这里的话尽量把文字放在前面，数字放在前面可能会出错【E2&B2&C2&D2】

 ③同时也在查询系统上，构建唯一编号，构造方式与在数据库中构造方式一致

④可以将唯一编号隐藏起来：设置自定义数值格式。选中想要隐藏内容的单元格，右键点击选择“设置单元格格式”，在弹出的对话框中选择“数字”选项卡，然后选择“自定义”，在类型栏中输入“;;;”（三个分号），点击“确定”，这样单元格内容将被隐藏，但仍然存在于单元格中。

（3）使用VLOOKUP进行查找

①VLOOKUP配合MATCH进行查找【=IFERROR(VLOOKUP($A$2,工资表!$A:$S,MATCH(查询系统!J7,工资表!$A$1:$S$1,0),0),"")】

2、方法2：INDIRECT

（1）INDIRECT可以提取行和列的交叉处的值，根据唯一编号确定行、根据需要提取的信息确定列

①首先定义名称，先定义列的名称，再定义行的名称

（2）使用INDIRECT函数交叉提取信息

---

• INDIRECT函数是一个引用函数，它返回由文本字符串指定的引用。
• 该函数可以用于动态引用单元格、工作表、或命名区域，使得公式中的引用可以根据需要变化。
• 用法：

 1、基本语法：=INDIRECT(ref_text, [a1])

• [a1]是可选项，用于指定ref_text参数中的地址类型。
• ref_text是必选项，代表要引用的单元格、工作表或命名区域的地址

2、引用类型：

• 使用A1引用样式时，可以直接引用单元格、工作表或命名区域。
• 使用R1C1引用样式时，可以通过行号和列号来引用单元格

  ---

  ①此处利用的是INDIRECT可以直接引用命名区域的所有单元格的内容进行交叉引用

【=INDIRECT($A$2,TRUE) INDIRECT(J7,TRUE)】，其中，两个INDIRECT之间的空格表示取两个区域的交集。这两个区域的所包含的单元格是由上一步中名称的定义所确定的。空格为交集引用符。

  

②此处基本工资的是由：

INDIRECT($A$2,TRUE) ={2021,1,11003,"财务部",2000,4000,1450,300,27,1,2,3,7783,-20,33,594.27,37.14,7098.59}

INDIRECT(J7,TRUE) ={2000;2000;2000;2000;2000;2000;3000;3000;3000;3000;3000;3000;3000;3500;3500;3500;3500;3500;3500;3500;2000;2000;2000;2000;2000;2000;3000;3000;3000;3000;3000;3000;3000;3500;3500;3500;3500;3500;3500;3500} 

的交集 2000 确定

  ③ 其余同理

二、动态薪资查询系统

---

应用场景：

1、现有一个多公司的薪资数据库，需要根据公司名称提取相应公司的员工薪资

2、提取到以下表格中，且要实施动态提取，集团分公司处为下拉菜单形式

---

（一）方法一：数据验证

1、首先可以对需要提取的分公司进行排序

【IF('查询-数据验证'!$D$1=数据源1!C2,N(数据源1!A1)+1,N(数据源1!A1))】

（其逻辑是：当需要提取的分公司名字等于数据源中分公司的名字时，则返回上一个单元格的数字+1，否则则返回上一个数字，这样的操作可以对所用名字时该分公司名字的数据进行排序）

（这个和之前所学的动态提取唯一值有一点相似，但也有不同之处，需要鉴别）

2、使用vlookup进行查找即可（即使出现了很多次8，但是vlookup只会返回第一次出现8的那一行数据）

【=IFERROR(VLOOKUP(ROW(A1),数据源1!$A:$K,COLUMN(B1),FALSE),"")】

3、可以在【视图】-【冻结窗格】进行设置，使其能够把表头固定住

4、还通过【条件格式】设置成有值的部分的显示边框

（1）【开始】-【条件格式】-【管理规则】

（2）【新建格式规则】-【使用公式确定要设置格式的单元格】-【=$A4<>""】(从A4开始，不等于空值-【格式】

（3）【边框】-【外边框】

（二）方法二：开发工具

---

【菜单栏】-【开发工具】（如果找不到的话就在【菜单栏】-【文件】-【选项】-【自定义功能区】-【开发工具】勾选上-确定）

---

1、查询：组合框

（1）【菜单栏】-【开发工具】-【插入】-【表单控件】-【组合框】（第二个图标），然后在表中拉出一个组合框即可。（如果想要删除时觉得不好删的话，可以按住Ctrl+选中+delete键删除）

（2）选中后（如果不好选中，可以按住Ctrl键再进行选择），右键-【设置控件格式】

数据源区域：【组合框中数据源区域必须是竖着的】

单元格链接：是显示你选择的是第一个参数

（3）此时依旧对数据源中我所要提取的分公司的数据进行排序

【IF(C2=INDEX(参数表!$A$2:$A$7,'查询-组合框'!$B$1),N(数据源2!A1)+1,N(数据源2!A1))】

（上面公式的逻辑是，如果数据源中分公司的名=INDEX返回的值（参数表中的分公司的排序，该值组合框控件返回的单元格链接的结果），那么就记一个，然后一直累记排序）

（4）通过vlookup进行查找

2、查询：单选按钮

---

可以分别通过【集团分公司】、【部门】、【职位】来进行分表

---

（1）【菜单栏】-【开发工具】-【插入】-【表单控件】-【选项按钮（窗体控件）】（第六个图标），在每一个选项前设置单选按钮

单元格链接显示是你选择的是哪一选项

（2）在选项的右侧设置成数据验证的格式

（3）在数据源中进行排序

【IF(CHOOSE('查询-单选按钮'!$A$1,'查询-单选按钮'!$D$1,'查询-单选按钮'!$D$2,'查询-单选按钮'!$D$3)=CHOOSE('查询-单选按钮'!$A$1,数据源3!C2,数据源3!D2,数据源3!F2),N(数据源3!A1)+1,N(数据源3!A1))】

（以上公式的逻辑是根据控件格式中单元格链接返回的数字即可以知道你选择的是分公司、部门还是职位，如果选择与数据源中的值匹配，即记为1个，然后累记计数，进行排序）

（4）通过vlookup进行查找

【=IFERROR(VLOOKUP(ROW(A1),数据源3!$A:$K,COLUMN(B1),FALSE),"")】

3、查询：复选按钮

---

任务背景：当你需要多条件分表时，如选择多个公司

---

（1）【菜单栏】-【开发工具】-【插入】-【表单控件】-【复选框（窗体控件）】（第三个图标），在每一个选项前设置复选按钮

（2）设置控件时，返回的不再是数字，而是TRUE OR FALSE

（3）再构建一列，使得选中的显示名称

【=IF(B2,D2,"")】（IF函数第一个参数就是逻辑参数，判断TRUE和FALSE的）

（4）在数据源中排序

【IF(MATCH(C2,'查询-复选按钮'!$A$2:$A$7,0),N(A1)+1,N(A1))】

（以上公式的逻辑是，如果分公司的名字，在以下区域中出现，则加1）

但是如果找不到的话，会报错，此时我们可以使用ISNUMBER进行判断，因为MATCH返回的是数据在数据源区域的第几个，返回的是数值，所以正确的公式应该是：

【=IF(ISNUMBER(MATCH(C3,'查询-复选按钮'!$A$2:$A$7,0)),N(A2)+1,N(A2))】

（5）使用vlookup进行查找

三、跨表引用

1、单人业绩汇总

---

任务背景：将某个人的不同月份的业绩引用过来

---

（1）首先用VLOOKUP找1月份的业绩

【=VLOOKUP($A$1,'1月'!$A:$G,7,0)】

（2）但是不能通过下拉来进行填充，换一个思路：找一下规律，我们可以观察到，其实只有工作簿变了，那么工作簿的变化可以表示为INDIRECT（C2&"!$A:$G），即公式变换为以下：

【=VLOOKUP($A$1,INDIRECT(C2&"!$A:$G"),7,0)】，然后下拉即可填充

2、【复杂】员工业绩汇总

---

任务背景：现有一个1月、2月、3月、4月的数据分别在不同的工作表里，是不同员工每一天的销售记录：现在需要汇总到汇总表中：

---

（1）可以用SUM求和，前提是这几个表的表头和汇总表的表头是一一对应的

【=SUM('1月'!C:C)】

（2）此时使用1中的INDIRECT的方法进行跨表引用：【=SUM(INDIRECT($A4&"!C:C"))】

，但是出现了问题，就是一直都是引用C：C列

（3）如何让C:C列发生变化呢？这里我们可以使用到一个新的函数：CHAR（），其作用是【根据本机的字符集，返回由代码数字指定的字符】，其中CHAR（65）返回的就是A

（4）要让其随着列变化，可以配合column使用：【=CHAR(65+COLUMN(B2))】

此时就是C了

（5）然后再配合INDIRECT，进行查找，最终的公式为：【=SUM(INDIRECT($A3&"!"&CHAR(65+COLUMN(C2))&":"&CHAR(65+COLUMN(C2))))】

（感叹号是区分工作表的）