您现在的位置是：首页 > 办公软件 > Excel

excel中的查找和引用函数（一）

设·集合小编发布时间：2022-01-05 00:21:00 901次最后更新：2024-03-08 10:36:12

这篇文章分享excel中的查找和引用函数的使用方法。

1. 看这个例子，有两个表格，表2中的学号对应表1中的学号，当表2中的学号变换的时候，表2后面的信息也跟着一起变换。这是怎么做出来的呢？接下来就分享操作方法。

2. 先清除表2中的数据，在学号列可以手动输入学号，但是为了方便，我们给它设置一下使其可以展示下拉选择框。选中J4单元格，找到【数据】菜单栏的【数据工具】功能区，点击【数据验证】工具。

3. 在弹出的【数据验证】窗口设置验证条件为序列，点击来源输入框，选择表1学号列的数据，也就是A3-A19单元格。点击确定后表2的学号列就出现下拉选项了。

4. 要使表2中的学号等于表1中的一个学号后，后面的数据跟着改变。比如表2学号为001时，那么姓名就要展示张虹。那么姓名列应该怎么设置呢？这时就要用到查找函数，找到【公式】菜单栏的【函数库】功能区，点击【查找与引用】。这时会看到列出来了一堆函数，选择VLOOKUP函数。

5. 双击VLOOKUP函数，在弹出的【函数参数】窗口可以看到这个函数怎么使用。点击各个输入框也可以看到每个输入框需要怎么输入。如果还是看不懂的话可以点击左下角”有关函数的帮助“，可以看到这个函数非常详细的使用方法。

6. 那么就点击Lookup_value输入框，然后选中表2的学号单元格，也就是J4单元格；点击Table_array输入框，然后选中表1需要搜索的区域，也就是A3到D19区域；点击Col_index_num输入框，然后输入需要查找的列，姓名是第二列，所以输入2；点击Range_lookup输入框，如果需要精确匹配输入FALSE，不需要的话输入TRUE或者不输入，这里我们输入FALSE。全部输入完后可以看到【函数参数】窗口的左下角已经出现计算结果了。点击确定，表2的姓名列就展示出来姓名了。可以通过更换表2中学号单元格的值，检验姓名列是否展示正确。

7. 班级列用同样的方法操作，也可以直接在单元格中输入公式，输入“=VLOOKUP([@学号],A3:D19,3,FALSE)”。回车后就展示正确的班级了。

8. 性别列麻烦一点，需要先根据学号查找到身份证号，身份证号的倒数第二位如果是奇数，性别就是男，如果是偶数，性别就是女。那么就先写根据学号查找到身份证号的公式，为了理清楚公式，我们先不在M4单元格写，先找一个其他的空白单元格，比如M16单元格，复制表2姓名列的公式改改，改成“=VLOOKUP(表2[学号],A3:D19,4,FALSE)”，身份证号就在M16单元格显示出来了。

9. 再找另外一个空白单元格，比如M15单元格，用MID函数截取出身份证号的倒数第二位。调出这个函数的【函数参数】窗口，可以看到这个函数的使用方法，在Text输入框中输入要提取字符的文本字符串，也就是M16；在start_num中输入文本中要提取的第一个字符的位置，也就是17；在num_chars 中输入希望 MID 从文本中返回字符的个数，也就是1。点击确定，身份证号的倒数第二位就展示出来了。

10. 那么怎么通过是奇数还是偶数判断是男还是女呢？可以这样，如果这个数能被2整除，那么就是偶数，性别就是女，不能被整除就是奇数，性别就是男。接下来再找一个空白单元格，使用MOD函数。调出这个函数的【函数参数】窗口，Number和Divisor中分别输入被除数单元格和除数，也就是M15和2，点击确定后余数就在M14单元格展示出来了。

11. 再找个空白单元格，调出IF函数的【函数参数】窗口，在Logical_test中输入逻辑表达式，这里输入M14=0。在Value_if_true中输入当表达式返回值为TRUE时需要展示的内容，如果M14=0的话，就是偶数，那么这里就应该输入"女"。在Value_if_false中输入“男”。点击确定后性别就显示出来了。

12. 最后整合所有的公式填到表2性别单元格，公式展示为“=IF(MOD(MID(VLOOKUP(表2[学号],A3:D19,4,FALSE),17,1),2)=0,"女","男")”。这样性别列的单元格也能随学号的改变而改变了。

13. 出生日期列也要先使用VLOOKUP函数先查找到身份证号，再使用MID函数分别截取年月日，最后使用DATE函数。身份证号在M16单元格已经显示出来了。接下来就选中N15单元格，调出DATE函数的【函数参数】窗口，分别在Year,Month和Day中填入“MID(M16,7,4)”，“MID(M16,11,2)”,“MID(M16,13,2)”。点击确定，出生日期就显示出来了。最后将公式整合填入出生日期栏，整合后的公式为“=DATE(MID(VLOOKUP(表2[学号],A3:D19,4,FALSE),7,4),MID(VLOOKUP(表2[学号],A3:D19,4,FALSE),11,2),MID(VLOOKUP(表2[学号],A3:D19,4,FALSE),13,2))”。

14. 年龄列就是需要用当前的年份减去身份证号里的年份。先计算身份证号里的年份，在O15单元格输入“=MID(M16,7,4)”后回车。再计算当前的年份，在O14单元格输入“=NOW()”后回车，得到当前时间。再在O13单元格输入“=YEAR(O14)”后回车，得到当前年份。最后在O12单元格中输入“=O13-O15”,回车后就得到年龄了。