一、薪资查询系统一前期资料1、人事部门中的一些薪资情况的资料作为基础数据库2、根据基础数据库建立以下查询表【该查询表的具体类目可以依据自己想要提取的信息进行制作】根据年、月、员工编号、部门提取出以下信息。二查询系统的制作1、方法1VLOOKUP1年、月和部门部分可以使用数据验证的形式使其变成下拉列表的模式①设置数据验证② 数据验证的序列来自于参数表中年份的数据③月份和部门同理2根据数据库的内容构造唯一编号①从这个数据库可以看到我们需要同时确定年、月、工号和部门才能确定提取某一行的数据②构造唯一值这里的话尽量把文字放在前面数字放在前面可能会出错【E2B2C2D2】③同时也在查询系统上构建唯一编号构造方式与在数据库中构造方式一致④可以将唯一编号隐藏起来设置自定义数值格式。选中想要隐藏内容的单元格右键点击选择“设置单元格格式”在弹出的对话框中选择“数字”选项卡然后选择“自定义”在类型栏中输入“;;;”三个分号点击“确定”这样单元格内容将被隐藏但仍然存在于单元格中。3使用VLOOKUP进行查找①VLOOKUP配合MATCH进行查找【IFERROR(VLOOKUP($A$2,工资表!$A:$S,MATCH(查询系统!J7,工资表!$A$1:$S$1,0),0),)】2、方法2INDIRECT1INDIRECT可以提取行和列的交叉处的值根据唯一编号确定行、根据需要提取的信息确定列①首先定义名称先定义列的名称再定义行的名称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(C2INDEX(参数表!$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 FALSE3再构建一列使得选中的显示名称【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但是不能通过下拉来进行填充换一个思路找一下规律我们可以观察到其实只有工作簿变了那么工作簿的变化可以表示为INDIRECTC2!$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))】但是出现了问题就是一直都是引用CC列3如何让C:C列发生变化呢这里我们可以使用到一个新的函数CHAR其作用是【根据本机的字符集返回由代码数字指定的字符】其中CHAR65返回的就是A4要让其随着列变化可以配合column使用【CHAR(65COLUMN(B2))】此时就是C了5然后再配合INDIRECT进行查找最终的公式为【SUM(INDIRECT($A3!CHAR(65COLUMN(C2)):CHAR(65COLUMN(C2))))】感叹号是区分工作表的