Excel 多条件查找引用技巧

我们公司最近也正在招聘新员工,我也顺便去网上溜达了下。

看到很多岗位的招聘要求上都写着:「熟练使用 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),作者:明镜在心,审核:小爽,编辑:竺兰

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注