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 列合并?
![f28b16d3a27ae47dda62581429ce6310.png](https://i-blog.csdnimg.cn/blog_migrate/451c8650c6d049c28ed17e200129e7d3.jpeg)
=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 次。
![4deac2a695822c0d854af0d99c111961.png](https://i-blog.csdnimg.cn/blog_migrate/fe69e67cac894024c5da3c542276f3ff.png)
=VLOOKUP(2&"中卡车",C1:E12,2,FALSE)
▲左右滑动查看
结果为 92000。![454d0ae21c5fb4f78f70d442474930dc.png](https://i-blog.csdnimg.cn/blog_migrate/c66716d8efd198d6d71c23b750c518a4.png)
![9f9599e830e714a408bcc4410444bdd6.gif](https://i-blog.csdnimg.cn/blog_migrate/6069f1cbbcd440e1a7f976bf19b172be.gif)
03
编写公式
❶ 查找最近一次日期, I2 单元格中输入公式:=VLOOKUP(COUNTIF($B$2:$B$12,$H2)&$H2,$C$1:$E$12,2,FALSE)
▲左右滑动查看
![2d15b06ac31c9acaaf9f5a8a2b67136b.png](https://i-blog.csdnimg.cn/blog_migrate/bcae0722a1c0ca98aa61dfafe5f8cc2e.png)
=VLOOKUP((COUNTIF($B$2:$B$12,$H2)-1)&$H2,$C$1:$E$12,2,FALSE)
▲左右滑动查看
![68d8a31587e8821efbe7106f4c7370cd.png](https://i-blog.csdnimg.cn/blog_migrate/f107d190974cb959bdef55f531bc10e4.png)
=I2-J2
![7d1ce67f2a02d5cbfc608713e93ad771.png](https://i-blog.csdnimg.cn/blog_migrate/087d59b04852603fcc4b0b885bb6a2cb.png)
=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)
▲左右滑动查看
最后两者差值,直接相减就可以得到啦~![ead1837f98103ac42dfd0b903056b8ba.gif](https://i-blog.csdnimg.cn/blog_migrate/58d5ad5d2f0bdb9e87f3d09396f3e869.gif)
04
小延伸
前面,我们是将日期先进行升序排序。 后面,通过计算各车的总次数,还有次数减 1,利用 Vlookup 函数返回对应的价格。 那么我们逆向思考一下。 如果我们事先将日期进行降序排序。 后面就可以直接用「1&车」,「2&车」,分别利用 Vlookup 函数得到最近一个日期,还有倒数第二次的价格。 这样就少了后面 Countif 函数计数的那一步了。 这其实是一个逆向的思路,我们关键是理解思路背后的原理。 前面我们是用组合公式完成的,对吧? 但是组合公式还有几个缺点: ❶ 每次都需要提前将数据按照日期进行排序; ❷需要写很多辅助列; ❸ 数据无法自动更新。 而这些缺点,使用一个叫做 Power Query 的神器都可以通通解决掉!! 看一下演示的效果~![09aa552d582a7475c23faca662c8978a.gif](https://i-blog.csdnimg.cn/blog_migrate/8fb6babb2608ce2eb9b68692f6cfdd21.gif)
?学习班里,不仅教 Excel 数据处理的思路方法、教表格设计的内功心法,也教透视表、常用函数、图表的使用技巧!
?上课还有老师在线直播讲解,不用看一个人孤独看录播!
?有学员群、小组群,有助教、小组长督促你一起学习!
扫码咨询入群,一起成为效率达人!
↓↓↓![5142e7ed06e849cf30727c7fad5fd176.png](https://i-blog.csdnimg.cn/blog_migrate/43dedf2967adbecfcc15971a012ae404.jpeg)
![40460e913c8766730691a086ef802d4f.png](https://i-blog.csdnimg.cn/blog_migrate/0687a3b1ca9f1726ac136c4c89e57401.png)
![fcbc87ff5f2963dccb0081a9b59cc8be.png](https://i-blog.csdnimg.cn/blog_migrate/4bd59888ca17754300a3a1513c9dcf66.jpeg)
![04a67188967d99e25c470af0cd506441.png](https://i-blog.csdnimg.cn/blog_migrate/073dd88dc1ab8ff18216e0f2229b71bb.png)
![57e03f3d462d43a6c8b15c6b330161c2.png](https://i-blog.csdnimg.cn/blog_migrate/615cd980ac4d3de6370bb10934e3c9c4.png)
![4d11a3e24144d18f5bb0444363456a05.png](https://i-blog.csdnimg.cn/blog_migrate/b95c2a71d6bea46bc6d64000c5f3fcb5.png)
![cdbc55a01f68e0e06d5b2fdbc2164562.png](https://i-blog.csdnimg.cn/blog_migrate/4144470986d761bc96017d528ee5d035.jpeg)
![cb7f99138d49fa660dda90d4a4c0acc4.png](https://i-blog.csdnimg.cn/blog_migrate/80b0d8f10372bc05e532753d8096d247.jpeg)