我们公司最近也正在招聘新员工,我也顺便去网上溜达了下。
看到很多岗位的招聘要求上都写着:「熟练使用 Excel 等办公软件」,而且在面试的时候还都有上机操作的题目。
然后就去网上搜了搜,看看 Excel 面试题都是什么内容、什么水准的题目,测试下自己能不能搞定。
在这个过程中,我发现了一道面试题,挺有意思的,今天分享给大家。
下表记录的是公司客户的每一次来店日期,要求:用函数提取出客户最后一次来店日期。
乍一看好像不难,但是对于小白而言,可能还是需要下一番功夫。
在简历上写着熟练或者精通 Excel 的人,估计有相当一部分是做不出来的
分析问题
我们先来分析下这道题!
这个题目是一个双条件查找引用,而且是查找最后一次的【来店日期】。
比如:客户【张三】有很多重名的,【客户编号】也有重复的。
要求:提取出姓名为【张三】,并且编号为【MD003】的最后一次【来店日期】。
在图中:【张三】【MD003】的【来店日期】一共有两次。
第一次:2022-1-4
第二次(也就是最后一次):2022-1-8
思考一下,如果是你,你会用什么办法呢?
对于查找,我最先想到的是用 Vlookup 函数(可能有很多人的第一反应也是这个),因为但凡学习 Excel 的人都会接触到它,它也是最常用的查找函数。
这个思路是对的,Vlookup 还真能解决这个问题,下面我们就一起来看看~
解决问题
▋方法 1
Vlookup 通常用于单条件查找,对于双条件或者更多条件的查找时,它自己无法单独完成……
那该怎么办呢?
我们可以结合 IF 函数来实现双条件查找。
如下图:
公式如下:
=VLOOKUP1,IF{1,0},0/E2&F2=$A$2:$A$15&$B$2:$B$15),$C$2:$C$15),2,1)
公式大概的意思是:
利用二分法查找的原理,匹配最后一个符合条件的值。
用 if {1,0} 组成一个查找区域和返回区域,
条件区域是:
$A$2:$A$15&$B$2:$B$15
把两个条件 E2 与 F2 用连接符(&)连在一起,然后与 A2:A15 与 B2:B15 的连接起来条件区域相比较,
E2&F2=$A$2:$A$15&$B$2:$B$15
如果相同就返回 TRUE, 否则就返回 FALSE,
结果如下:
{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
然后用 0 除以这个数组,得到一个由 0 和错误值组成的内存数组。
{#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
其中:有两个 0 就是跟查找条件一样,最后用比 0 大的任何一个值 ,这里使用 1 去查找最后一个 0 出现的位置,并返回在 $C$2:$C$15 对应的单元格内容。
需要注意的是:
此公式为数组公式需要按三键【Ctrl+Shift+Enter】结束公式输入。
如果是 Office 365 可以直接按【Enter】。
▋方法 2
对于大部分小伙伴们来说,如此复杂的公式不一定会用……
那该怎么办呢?
别急!
面试时一定要稳住!
如果只看题目要求的话,我们可以变通下,让 V 函数使用起来更简单点。
这里我们可以将两个条件变成一个条件,将返回最后一个值,变成返回第一个值。
需要做的是:增加一个辅助列,并且对数据源做个排序。
具体是什么意思呢?往下看 ↓↓↓
❶ 添加辅助列
这一步的目的是将两个条件变成一个条件。
在【A】列增加一个辅助列:
在 A2 单元格中输入如下公式:
=B2&C2
将 B2 的客户名称和 C2 的客户编号用连接符(&)连接在一起,组成一个条件。
并将公式下拉填充到【A15】。
❷ 排序
这一步的目的是将最大的日期排到最上面。以便 V 函数查找第一个值。
在【数据选项卡】中调出【排序】对话框:
并在【排序】对话框中设置三个排序。
第一关键字:客户名称,升序
第二关键字:客户编号,升序
第三关键字:来店日期,降序
最后单击【确定】,排序结果就出来了。
如下图:
❸ 输入公式
这一步就可以和平常使用 V 函数做一样的操作啦!
如下图:
公式如下:
=VLOOKUPF2&G2,$A$1:$D$15,4,0)
公式的意思是:
将 F2 和 G2 两个单元格的内容连接在一起,组成一个条件,然后在以辅助列开始的【A1:D15】这个区域查找第一次出现的日期(也就是最后一次来店日期),并返回第 4 列对应的值。
PS:这个方法是采取了变通的方式。有些时候使用辅助列来解决问题也是一个不错的思路。
▋方法 3
另外,这道题是求最后一次来店日期。
那么最后一次的来店日期也就是最大的日期。
因此我们也可以用 MAX+IF 组合来完成。
所以我们可以写成下面这样:
=MAXIF$A$2:$A$15&$B$2:$B$15=E2&F2,$C$2:$C$15))
公式解析:
先将 A 列与 B 列两个条件区域连接成一个条件区域,再和 E 列与 F 列中的条件相比较,如果条件相同,就返回 C 列的区域。最后用 MAX 返回其中最大一个日期值(也就是最后一次来店日期)。
如果你的版本是 Office 2016 版以上,还可以使用 MAXIFS 函数。
=MAXIFSC:C,A:A,E2,B:B,F2)
Maxifs 函数的用法跟 Sumifs 的函数用法一样。
第一参数是:返回的区域
第二参数是:条件区域
第三参数是:条件
基本套路为:
=MAXIFS返回的区域,条件区域 1,条件 1,条件区域 2,条件 2……)
条件区域和条件对,最多可输入 126 对。
知识拓展
如果题目没有一定要求用函数解决的话,用透视表可能更加简单。
在【插入选项卡】中点击【数据透视表】,调出【数据透视表】对话框。
选中【A1:C5】,并选择【现有工作表】中的【E7】单元格,最后点【确定】。
然后将【客户名称】和【客户编号】拖到【行区域】,【来店日期】拖到【值区域】
对客户编号进行筛选,
选中【计数项: 来店日期】右键设置为:最大值。
最终效果如下图:
好了,我们的面试题这就做完了!
但是,问题来了,你怎么知道结果是否正确呢?
我们在做表的时候,千万要牢记一点,就是要有核对机制。
比如:我们使用 V 函数得出的结果如下:
有的应聘者可能会在 H 列用其他函数(比如:MAX+IF 函数)再校验一次,
最后用两个不同的函数得出的结果值进行比较,并将比较结果放在 I 列。
如果为 true,就是两次结果相同。
如果为 false,就是不同,应进一步查明问题所在。
当然也可以用其他的方法来校验,比如透视表等。方法可以自己选择。
总结一下
今天介绍了如下方法进行多条件查找引用:
❶ VLOOKUP 函数
此函数在多条件查找时需要结合 IF 函数进行数据重新构造,比较复杂。
❷ VLOOKUP 函数 + 辅助列
这种方法适应性比较强,小白也容易上手。
❸ MAX+IF 函数组合
采用的数组判断的方法,适合有一定数组基础的人使用。
❹ MAXIFS 函数
使用方法简单,但只能在 OFFICE 2016 以上的版本中使用。
❺ 透视表
方法最为简单,适用范围广。
另外,在职场中,千万记得做完一件事之后,一定要有核对机制。
并且要有据可查!
职场中有时拼的不仅仅是技能,更重要的是经验!
本文来自微信公众号:秋叶 Excel (ID:excel100),作者:明镜在心,审核:小爽,编辑:竺兰