excel表格_excel函数公式大全_execl从入门到精通
如何实现EXCEL数据的灵活查找——教你用活Index与Match2024-03-23 06:04:46
员工信息查询卡的高级制作方法——MATCH,INDEX来帮忙
“顾总，VLOOKUP只能从左向右查找，但你之前让我查员工信息，只给我了员工的身份证号，如果我需要按员工身份证号查找，那还要把身份证号放到最左边吗？这样不好看啊？”
“小琪，这个问题问的好，如果出现查找内容无法在数据区域最左侧的情况，就不能再用VLOOKUP进行查找了，需要用更高级的查找函数来完成这个工作。”
“在日常工作中，更普遍的查询人员档案的方法是按身份证号进行查找，所以我先把刚刚的人员信息查询表修改一下。”顾城边说，边重新调整了一下电脑中的表格结构。（如图 358所示）图3-58“查询表设计好后，就可以根据表格结构制作查找公式了。这里需要用到INDEX和MATCH函数。”
首先设置查找员工工号的公式：
INDEX函数详解：
这个函数就像是一个索引一样，因此也被称为“索引函数”，知道了查找的区域，同时又知道了数据所在的行和列的位置，就可以找到位于那个位置的数据。
一、作用：此函数为查找引用函数，主要用于在给定的单元格区域中，返回特定行列交叉处单元格的值或引用。
二、选择此函数后，会弹出“选定参数”对话框，需要对参数的组合方式进行选择。
方式一array,row_num,column_num：进行单一表格的查询。
方式二reference, row_num,column_num,area_num：在多表格中进行查询。
此案例需要在单一表格中进行查询，因此选择方式一array,row_num,column_num，然后点击“确定”按钮。（如图 359所示）
图3-59三、 单一表格的查询方式一共包括三个参数（如图 360所示）：
参数一Array：指单元格区域或数组常量，也就是要查找的数据区域。即“在哪里找”。在本例中，由于要查找身份证号对应的员工工号，因此需要在员工工号这一列中查找数据，故此参数需要选择《员工档案常用信息》中的A列，即“人员档案常用信息!$A:$A”
参数二row_num：数组或引用中要返回值的行序号。即要查找数据所在的行号。此参数需要确定取出数值的行号，而行号要根据所查询的身份证号所在的行来确定，因此需要用MATCH函数来确定（见MATCH函数详解），可暂且空置。
参数三column_num：数组或引用中要返回值的列序号。即要查找数据所在的列号。在此例中，由于只选择了工号一列数据，因此可直接录入1。
图3-60知识扩充：如何在函数对话框中进行函数嵌套录入？
以图360为例，点击选择要进行函数嵌套的参数，然后在名称框中，点击下拉三角，选择要插入的函数，如果下拉框中没有找到目标函数，可以点击“其他函数”选项。点击后，会再次弹出“插入函数”对话框，此时再查找目标函数即可。
接下来，重点介绍一下INDEX函数中参数二的MATCH函数嵌套录入方式：
Step1： 点击选择INDEX参数二，然后按函数嵌套录入的方式，插入MATCH函数。
Step2：在弹出的MATCH函数对话框中，进行函数录入，以确定员工身份证号所在的行号。
MATCH函数详解：
一、作用：此函数为查找与引用函数，主要用于返回符合特定值特定顺序的项在数组中的相对位置，它不仅可以查找数据所在的列号，同样也可以查找行号。
二、此函数共有三个参数。
参数一Lookup_value：在数组中所要查找匹配的值，可以是数值、引用或字符串。同VLOOKUP一样，这个参数可以理解为“找什么”。在本例中，需要找身份证号，因此需要选择身份证号所在的单元格D4。
参数二Table_array：含有要查找的值的连续单元格区域，可以是一个数组，或是对某数组的引用。这个参数可以理解为“在哪里找”。在本例中，需要在《员工档案常用信息》表中的“身份证号码”所在列中查找，因此选择“人员档案常用信息!$I:$I”。
参数三Match_type：这里面的参数是数字－1、0或1。它指定了前两个参数中数值进行匹配的方式。
为1时：查找小于或等于Lookup_value的最大数值在Table_array中的位置，Table_array必须按升序排列。
为-1时：查找大于或等于Lookup_value的最小数值在Table_array中的位置，Table_array必须按降序排列。
为0时：查找等于Lookup_value的第一个数值，Table_array按任意顺序排列。
在本例中，由于身份证号是唯一的，且没有排序，所以录入“0”。图3-61此时，可以在MATCH函数参数对话框中的计算结果中看到，此身份证号在本列数据中的行号为2。最后点击“确定”按钮，完成公式的录入。（如图 361所示）图3-62完整公式为：=INDEX(人员档案常用信息!$A:$A,MATCH($D$4,人员档案常用信息!$I:$I,0),1)。（如图 362所示）
按此方法可以将员工档案查询表中的其它信息查询出来，由于公式具体相似性，因此可以通过直接修改公式中参数一的列号的方式来提高公式制作的效率。
具体公式为：
员工姓名：=INDEX(人员档案常用信息!$B:$B,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
学历：=INDEX(人员档案常用信息!$F:$F,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
所在部门：=INDEX(人员档案常用信息!$C:$C,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
职级：=INDEX(人员档案常用信息!$E:$E,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
性别：=INDEX(人员档案常用信息!$J:$J,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
出生日期：=INDEX(人员档案常用信息!$K:$K,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
婚姻状况：=INDEX(人员档案常用信息!$G:$G,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
政治面貌：=INDEX(人员档案常用信息!$H:$H,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
入职日期：=INDEX(人员档案常用信息!$N:$N,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
司龄：=INDEX(人员档案常用信息!$O:$O,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
离职时间：=INDEX(人员档案常用信息!$P:$P,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
离职原因：=INDEX(人员档案常用信息!$Q:$Q,MATCH($D$4,人员档案常用信息!$I:$I,0),1)
最终效果如图 363所示。
图3-63
特别说明：
1、 在本例中，由于员工工号设计时采用了“自定义数字格式”，因此在查询表中，员工工号格式采取相同的设置方式。
2、 在涉及日期查询时，有时会出现查询结果为数字的情况，只需将单元格格式设置为“日期”即可。
3、 在查找与引用函数中，如果涉及查找区域或查找目标的选择时，当查找区域或查找目标为固定的位置时，尽可能使用绝对引用，以避免公式设计完成并进行拖动时，由于查找区域或查找目标的自动变化，从而导致查找失败。
思考题：在本例中，由于INDEX函数中的参数一只选择了一列数据，因此只将INDEX函数中的第二个参数采用了MATCH函数自动定位的方法，如果选择了全部数据，各位读者朋友，是否可以将第三个参数也通过MATCH函数实现自动定位呢？
欢迎大家跟小编留言互动哟！
标签: excel政治面貌用什么公式