你有一份面试题要查收

这份面试题，有8道题目，文末给出完整的数据下载。先来看看你会做几道题目？

【题目1】将以下左表和右表信息合并到一个表里

如图所示，两张表的表头完全一样，但有部份内容缺失，需要合并到一张表里让内容更加完整。

可以用选择性粘贴功能，并勾选“跳过空单元”选项，确定即可。具体操作如下：

【题目2】将下表抵押权人的内容包含"长安"的单元格置换为"长安保险公司"

如果我们用替换功能把“长安”替换成“长安保险公司”，则得到的结果如下：

原来的“长安”两字替换成“长安保险公司”，但后面的“保险公司（宁波分公司）”还是存在。

题目的要求是包含“长安”这两个关键字的整个单元格替换成“长安保险公司”，而不是部份文字变动。而且“长安”这两字可能位于开头，也可以是中间或者是结尾，这两个关键字的位置不确定。

此时我们应该用通配符来进行模型查找包含“长安”关键字的单元格。通配符有两个，它们分别是：

（1）星号(*)，可以代替任意字符，可以单个，多个或者是没有字符。

（2）问号(?)，可代替任意单个字符。

本题我们用星号通配符比较合适，选中抵押权人这一列里的内容，Ctrl+F 快捷键打开【查找和替换】对话框。

在“查找内容”框里输入“*长安*”，在“替换为”对话框里输入“长安保险公司”，再点击【全部替换】。

得到最终结果如下：

【题目3】将下表按照加盟商、省份、地区以及还款日期顺序进行升序排序

需求是按四个条件排序，依次优先排序加盟商、省份、地区，最后还款日期。

单击表格内任意一单元格，然后点击【开始】-【排序与筛选】-【自定义排序】，然后依次添加加盟商、省份、地区，最后还款日期。具体操作如下：

四个条件排序后，结果如下图所示。先升序排列加盟商；相同加盟商的按省份升序排列；相同加盟商，省份的，按地区升序排列；相同加盟商，省份，地区的，按还款日期升序排序。

【题目4】将下表空缺信息批量填充，并把加盟商为蚂蚁**的客户金额乘以   10000

第一个要求是不连续没有规律的空白单元格批量填充，首先用到定位功能把所有空白单元格选中。

选中整张表格。在【查找和选择】下拉列表中选择【定位条件】，也可以使用快捷键F5，弹出【定位】对话框。

点击左下角的“定位条件”按钮，弹出的【定位条件】窗口。然后对定位条件进行一系列的设置，在本次需求中我们要定位出空白单元格，所以选“空值”。

定位出空白单元格后，在编辑栏里也就是写公式处输入“未知”，同时按下“ctrl+enter”，具体操作如下：

如果希望在同列中空白的单元格是按照上一行的值填充，在编辑栏里也就是写公式处输入“=E45”，同时按下“ctrl+enter”，得到需要的结果。

第二个要求为“把加盟商为蚂蚁**的客户金额乘以10000”，解题的思路是先把符合要求的加盟商找出来，用IF逻辑判断函数，如果符合要求就客户金额乘以10000。其语法是：

在本例中，公式的第一个参数为加盟商=“蚂蚁**”，判断是否满足这一条件，如果满足就是TRUE，则第二参数为“客户金额乘以10000”，如果不满足为FALSE则第三参数为原来的客户金额值。

最终公式为：

=IF(B45="蚂蚁**",C45*10000,C45)

得到的结果如下：

【题目5】根据身份证号提取性别和出生年月并计算年龄

这道题有三个要求，分别是提取性别，出生年月和计算年龄，先看第一个需求。

需求一：解题思路为身份证倒数第二位是偶数为女性，是奇数为男性。

第一步：身份证一般是18位，用MID函数提取身份证的倒数第二位也就是顺数的第17位，第一参数要提取字符的文本字符串，第二参数是从文本中要提取的第一个字符的位置，第三参数是从文本中返回字符的个数，语法和具体应用如下：

在本例中的公式为：

=MID(C64,17,1)

表示在C64填写身份证这一单元格中，从第17位开始提取，提取出1个字符，就是我们所需要的倒数第二位。

第二步：用ISEVEN判断MID函数提取出来的数据是否为偶数，如果参数 number 为偶数，返回 TRUE，否则返回 FALSE，语法和具体应用如下：

在本例中的和MID一起组合成的公式为：

=ISEVEN(MID(C64,17,1))

第三步：使用逻辑函数 IF判断ISEVEN返回的值，逻辑函数 IF的用法参考上一题。最后三个公式嵌套使用，具体如下：

需求二：解题思路为身份证第7至12位是表示年月。

第一步：同样可以用MID函数提取出年月，但提取出来是文本格式，再用text函数设定显示的格式，最后转换为数值格式以方便后续的日期运算。

MID函数的应用和第一个需求一样，写上MID函数从身份证里提取出年月，得到的结果是文本“196512”，具体应用如下：

=MID(C63,7,6）

第二步：用TEXT函数，第一参数是要更改的内容，第二参数是要更改内容的显示格式，得到的结果由原来的“196512”变成“1965-12”，注意此时的“1965-12”仍然是文本格式，只是有了日期的样子，还不是真正的日期格式。

本例公式如下：

TEXT(MID(C63,7,6),"0-00")

第三步：在TEXT函数前加两个短横线，这种形式叫做减负运算，负负得正，将文本格式变成真正的日期格式。此外如果将两个短横线 “--”  换成“1*”，也可以实现文本模式向数值格式的转换，具体公式如下：

=--TEXT(MID(C63,7,6),"0-00")=1*TEXT(MID(C63,7,6),"0-00")

需求三：解题思路为现在的日期减去出生日期为年龄。

从第二步可以提取得到出生日期，用到以下两个函数可以得到年龄，一个是TODAY函数，括号里没有参数，返回的是当前日期，如在单元格里输入公式=TODAY()，则得到“2020/9/19”。DATEDIF的第一参数是开始时期，第二参数是结束日期，第三参数是返回的一段时期内的整年、月或天数，具体的是语法和应用如下：

本例的公式为：

=DATEDIF(E63,TODAY(),"Y")

最终得到的结果：

【题目6】利用员工姓名查找标黄部分相关信息，不能使用辅助列。

根据员工姓名，得到相对应的工作部门，住宅电话，公司电话，手机等信息，由于VLOOKUP只能从左到右查找，要求不能使用辅助列，因此我们用MATCH和INDEX函数组合查找，这两个函数的语法如下：

MATCH函数的第一参数是要查找的内容，本例中要找的内容是姓名所在的单元格即I75，第二参数是查找的区域也就是所有姓名所在区域，即C72：C88，第三参数是精确查找一般填0。MATCH函数最终返回的值是查找的内容也就是姓名在表格里第几行，最终的值为1，2，3……

INDEX函数的第一参数单元格区域或数组常量,本例中就是整张表格的区域，第二参数是选择数组中的某行，如姓名 “李项”在整张表的第1行。第三参数是选择数组中的某列，如工作部门在整张表的第1列，最终得到当姓名是李项时，工作部门会显示“运营部”。

本例的最终公式为两个函数嵌套使用，具体如下：

当姓名固定不变时，住宅电话在整张表的第3列，所以INDEX函数的第三参数变成3表示在整个表格中，第1行第3列的值就是李项的住宅电话，同样的道理依次把INDEX函数的第三参数变成4，5，就可得到公司电话，手机，具体如下：

=INDEX(B72:F88,MATCH(I75,C72:C88,0),3)
=INDEX(B72:F88,MATCH(I75,C72:C88,0),4)
=INDEX(B72:F88,MATCH(I75,C72:C88,0),5)

最终的结果如下图所示：

【题目7】将以下数据有错误值的单元格标为红色、如何判断身份证、手机号是否准确，对于该类问题出错如何避免？

以上的错误值分别为#DIV/0!和#N/A，可以使用条件格式标出来，选中要进行条件格式设置的区域，【开始】-【条件格式】-【新建规则】，然后具体操作如下：

由于excel里最多只能录入15个字符，超过15个字符会使用科学记数法，而身份证大多数情况下是18位，因此在输入身份证时先要把单元格设置成文本格式。

选中需要输入身份证的单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”，然后选择文本，点击确定。

也可以先在单元格打上英文状态下的一撇“  '  ”，然后再接着输入身份证，设置成功后左上角会有一个绿色的小三角形，如下所示：

身份证一般是15位或18位，电话号码是11位，不能有重复值，当输入的信息位数不够或超出或有重复时会弹出一个对话框警告提示，可以避免输错，此时用到数据验证功能。

具体的设置如下，选中要输入身份证的区域，【数据验证】，打开以下对话框并像以下这样设置好设置好内容：

输入的公式具体为：

当身份证的长度是是15位或者18位并且没有重复值时为TRUE，以上的两个条件有其中的一条不满足时，则会弹出警告窗口。

最终的效果如下：

同样的道理，手机号码只有11位，公式修改为：

=AND(LEN(D102)=11,COUNTIF($D$102:$D$110,D102)=1)

则可得到同样的结果。

【题目8】根据表一中的信息，将表二中的产品名称补充完整

按照某一条件查找匹配其他内容，通常用到 VLOOKUP公式，其语法如下：

 第一参数你想要查找的内容，第二参数是要查找的位置，第三参数包含要返回的值的区域中的列号，第四参数返回近似或精确匹配-表示为 1/TRUE 或 0/假）。而在本例中借款人可能有相同的名字，如有两个人的名字都叫白涛，但车牌是唯一的，因此公式可以写为：

=VLOOKUP(F154,$D$144:$F$151,2,0)

最终结果为：

【总结】

这一套面试题，在考察面试者熟练应用Excel函数、条件格式等解决实际工作中的问题。

用选择性粘贴合并表格，使用通配符进行模糊查找替换，进行多个关键字排序，利用ctrl+enter批量填充。

用条件格式，数据验证可以快速显示异常值及避免输入错误信息。

各个函数的应用：