Hello,大家好,我是小爽~
每个产品都有自己的价格,价格也有一定的波动范围。
例如双十一双十二的活动价格,还有平常的价格等等~
比如说,下面的同学是做汽车销售的。
每隔一段时间,汽车的销售价格就要做一些调整。
所以,他现在想要知道,「最近一次和上一次的销售价格差异是多少?」
那么问题再归纳整理一下就是这样的:
❶ 最近一次销售价格是多少? ❷ 上一次销售价格是多少? ❸ 两者之间的差值是多少?
最终的效果大致是下面这样子的:
以上问题,本质上就是一个数据查询的问题,查询最近一次和上一次的数据。
在 Excel 之中,当我们要进行数据查询时,一般我们会想到,使用 Vlookup 函数,透视表,或者 Lookup 函数等等~
那么,大家可以先结合这几种思路思考一下~
......思考时刻……
用 Vlookup 函数? 好像只能查找第一个出现的值。 用数据透视表? 好像只能得到最大日期的价格。 用 Lookup 函数? 虽然可以得到最近的日期的价格,但是好像不能得到倒数第二次的价格。 ………………思考时间…………
如果是你,你会怎么做呢?
好吧,我给大家来解释一下,无论使用哪种方法,都会遇到困难点——倒序查找。
就是查找最后一次和倒数第 2 次的数据。
前面几种方法,直接用一个函数或者数据透视表都很难实现「自主选择返回第几次的数据」。
为了满足这个需求,我们就要费一番周折,借助 Countif 函数和 Vlookup 函数来组合实现。
好啦,那接下来我们来看一下具体的做法。
今天,我就来给大家介绍一下函数法~
我们还是以这个数据作为例子。
下面就来看看具体操作。
01
按照日期进行升序排序
❶ 选中表格,点击【数据】选项卡-【排序】;【主要关键词】-「价格维护时间」;
【次序】-「升序」。
「为什么要将日期先进行排序呢?」
这里主要是为了后面可以利用 Vlookup 函数,得到最近的一个价格。
还是不理解?没关系,先往后继续看~
02
添加辅助列
首先新增一列,利用拉灯式得到每个车型的出现次数。
(不理解拉灯式没关系,下面马上就会讲到啦!)
❶ 插入列的方法: 可以选中列,右键插入; 也可以利用快捷键【Ctrl+shift+=】,直接在左边插入一列。❷ 利用拉灯式得到每个车型的出现次数。
A2 单元格输入公式如下:
=COUNTIF($B$2:B2,B2)
❸ 在价格列前面添加辅助列,将次数和车型连接起来。
公式如下:
=A2&B2
看到这里,肯定有小伙伴疑惑了:
下面我们就来解析一下~ Q1: 第一个辅助列用 Countif 函数的目的是? 第一个辅助列,我们用的是 Countif 函数,主要是得到各类车型依次出现的次数。 Countif 函数,能够对区域中满足单个指定条件的单元格进行计数。 基本语法为: =COUNTIF(查找区域,条件) 第一个辅助列: A2 单元格:第一个辅助列用 Countif 函数的目的是?
什么是拉灯模式,拉灯模式是干嘛的?
最后为啥将 A 列和 B 列合并?
=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 数据处理的思路方法、教表格设计的内功心法,也教透视表、常用函数、图表的使用技巧!
?上课还有老师在线直播讲解,不用看一个人孤独看录播!
?有学员群、小组群,有助教、小组长督促你一起学习!
扫码咨询入群,一起成为效率达人!
↓↓↓ 学习打卡~~