不管查找第几次出现的数据，用Vlookup函数这样做，超简单！

本文作者：小爽

本文编辑：小叮、竺兰

Hello，大家好，我是小爽~

每个产品都有自己的价格，价格也有一定的波动范围。

例如双十一双十二的活动价格，还有平常的价格等等~

比如说，下面的同学是做汽车销售的。

每隔一段时间，汽车的销售价格就要做一些调整。

所以，他现在想要知道，「最近一次和上一次的销售价格差异是多少？」

那么问题再归纳整理一下就是这样的：

❶ 最近一次销售价格是多少？❷ 上一次销售价格是多少？❸ 两者之间的差值是多少？

最终的效果大致是下面这样子的：

以上问题，本质上就是一个数据查询的问题，查询最近一次和上一次的数据。

在 Excel 之中，当我们要进行数据查询时，一般我们会想到，使用 Vlookup 函数，透视表，或者 Lookup 函数等等~

那么，大家可以先结合这几种思路思考一下~

......思考时刻……

用 Vlookup 函数？好像只能查找第一个出现的值。

用数据透视表？好像只能得到最大日期的价格。

用 Lookup 函数？虽然可以得到最近的日期的价格，但是好像不能得到倒数第二次的价格。

……

…………思考时间…………

如果是你，你会怎么做呢？

好吧，我给大家来解释一下，无论使用哪种方法，都会遇到困难点——倒序查找。

就是查找最后一次和倒数第 2 次的数据。

前面几种方法，直接用一个函数或者数据透视表都很难实现「自主选择返回第几次的数据」。

为了满足这个需求，我们就要费一番周折，借助 Countif 函数和 Vlookup 函数来组合实现。

好啦，那接下来我们来看一下具体的做法。

今天，我就来给大家介绍一下函数法~

我们还是以这个数据作为例子。

下面就来看看具体操作。

01

按照日期进行升序排序

❶ 选中表格，点击【数据】选项卡-【排序】；

【主要关键词】-「价格维护时间」；

【次序】-「升序」。

「为什么要将日期先进行排序呢？」

这里主要是为了后面可以利用 Vlookup 函数，得到最近的一个价格。

还是不理解？没关系，先往后继续看~

02

添加辅助列

首先新增一列，利用拉灯式得到每个车型的出现次数。

（不理解拉灯式没关系，下面马上就会讲到啦！）

❶ 插入列的方法：

可以选中列，右键插入；

也可以利用快捷键【Ctrl+shift+=】，直接在左边插入一列。

❷ 利用拉灯式得到每个车型的出现次数。

A2 单元格输入公式如下：

=COUNTIF($B$2:B2,B2)

❸ 在价格列前面添加辅助列，将次数和车型连接起来。

公式如下：

=A2&B2

看到这里，肯定有小伙伴疑惑了：

第一个辅助列用 Countif 函数的目的是？

什么是拉灯模式，拉灯模式是干嘛的？

最后为啥将 A 列和 B 列合并？

下面我们就来解析一下~

Q1：第一个辅助列用 Countif 函数的目的是？

第一个辅助列，我们用的是 Countif 函数，主要是得到各类车型依次出现的次数。

Countif 函数，能够对区域中满足单个指定条件的单元格进行计数。

基本语法为：

=COUNTIF（查找区域，条件）

第一个辅助列：

A2 单元格：

=COUNTIF($B$2:B2,B2)

就是在 B2 到 B2 的区域中找 B2，也就是中卡车出现 1 次。

A3 单元格：

=COUNTIF($B$2:B3,B3)

就是在 B2 到 B3 的区域中找 B3，也就是大卡车出现 1 次。




A4 单元格：

=COUNTIF($B$2:B4,B4)

就是在 B2 到 B4 的区域中找 B4，也就是中卡车出现 2 次。

……

如此循环下去，这里我们看到中卡车一共出现了 5 次。

Q2：什么是拉灯模式，拉灯模式是干嘛的？

拉灯模式：单列区域中，起始单元格绝对引用，结尾单元格相对引用。

比如「$A$1:A1」，像拉灯一样头端固定，向下拉长度变化的动态区域。

下面我做了一个简单的图示，可以简单了解一下：

Q3：最后，为啥将 A 列和 B 列合并？

C 列中我们将 A 列和 B 列进行合并是由于：

后面我们需要用到 Vlookup 函数进行查询。

而 Vlookup 函数只能查找第一次出现的值，对于重复出现的值没办法进行查找。

Vlookup 基本语法：

=VLOOKUP（找啥，在哪里找，返回的在第几列，匹配方式）

PS：公众号后台回复【UP】即可获取 Vlookup 函数的相关文章。

A 列是出现的次数，B 列是车型。

两列合并不仅可以构造不重复的值，而且「次数&车型」，我们也可以得到第几次出现的值。

比如下面：

「2&中卡车」的公式，这里我们可以查找「中卡车第二次出现」的价格。

=VLOOKUP(2&"中卡车",C1:E12,2,FALSE)

▲左右滑动查看

结果为 92000。

通过上面的解释，你是否看懂了呢？

如果明白了上面的解释，下面编写公式就难不倒你了~

03

编写公式

❶ 查找最近一次日期， I2 单元格中输入公式：

=VLOOKUP(COUNTIF($B$2:$B$12,$H2)&$H2,$C$1:$E$12,2,FALSE)

▲左右滑动查看

❷ 查找倒数第二次日期， J2 单元格输入公式：

=VLOOKUP((COUNTIF($B$2:$B$12,$H2)-1)&$H2,$C$1:$E$12,2,FALSE)

▲左右滑动查看

❸ 最后计算两个的差值， K2 单元格输入公式：

=I2-J2

下面我们就来简单解析一下公式~

前面我们知道了，可以利用 Vlookup 函数，通过「次数&车型」来查找，获得第几次出现的价格；

最前面，我们也已经将日期进行升序排序了。

那么，如果想要获得「某种车最近一次日期」的价格，我们可以利用 Vlookup 函数。

比如：通过查找「小轿车一共出现的次数&小轿车」的价格，也就是最近一次日期的价格。

那如何才能得到小轿车的总次数？

前面我们介绍过 Countif 函数，它可以得到小轿车在某个区域中出现的总次数，公式如下：

=COUNTIF(区域,「小轿车」)

所以，在 I2 单元格中输入公式如下，得到最近一次日期：

=VLOOKUP(COUNTIF($B$2:$B$12,$H2)&$H2,$C$1:$E$12,2,FALSE)

▲左右滑动查看

在 J2 单元格中输入公式如下，得到上一次日期：

=VLOOKUP((COUNTIF($B$2:$B$12,$H2)-1)&$H2,$C$1:$E$12,2,FALSE)

▲左右滑动查看

最后两者差值，直接相减就可以得到啦~

好啦，总结一下，本文涉及到的知识点和思路有：

❶ 通过排序为后面获取最大值做准备。

❷ 借助 Countif 拉灯模式获得车型依次出现的次数。

❸ 通过辅助列构造不重复值，再用 Vlookup 函数进行查找。

04

小延伸

前面，我们是将日期先进行升序排序。

后面，通过计算各车的总次数，还有次数减 1，利用 Vlookup 函数返回对应的价格。

那么我们逆向思考一下。

如果我们事先将日期进行降序排序。

后面就可以直接用「1&车」，「2&车」，分别利用 Vlookup 函数得到最近一个日期，还有倒数第二次的价格。

这样就少了后面 Countif 函数计数的那一步了。

这其实是一个逆向的思路，我们关键是理解思路背后的原理。

前面我们是用组合公式完成的，对吧？

但是组合公式还有几个缺点：

❶ 每次都需要提前将数据按照日期进行排序；

❷需要写很多辅助列；

❸ 数据无法自动更新。

而这些缺点，使用一个叫做 Power Query 的神器都可以通通解决掉！！

看一下演示的效果~

整个过程不需要事先对数据进行排序，不需要做辅助列。

直接在右边的结果上面，右键点击刷新，就可以完成数据的更新。

一劳永逸的感觉，有没有！

想要知道这个是怎么做的吗？快快给我点赞吧！

点赞越多，我写文章就写得越快，可能下篇文章我们就可以来聊这个话题。

还有任何疑问，或者其他做法，欢迎在后台留言和小爽互动，有用的话可以动动手转转发~

如果你也想遇到问题，现场提问、现场解答，再也不用一个人苦苦挠破头，欢迎加入【秋叶 Excel 数据处理学习班】：

👉学习班里，不仅教 Excel 数据处理的思路方法、教表格设计的内功心法，也教透视表、常用函数、图表的使用技巧！

👉上课还有老师在线直播讲解，不用看一个人孤独看录播！

👉有学员群、小组群，有助教、小组长督促你一起学习！

扫码咨询入群，一起成为效率达人！

↓↓↓

学习打卡~~