私信回覆關鍵詞【UP】~
獲取VLOOKUP函數用法教程合集,一看就會!(◦˙▽˙◦)
Hello,大家好,我是小E~
每個產品都有自己的價格,價格也有一定的波動範圍。
例如雙十一雙十二的活動價格,還有平常的價格等等~
比如說,下面的同學是做汽車銷售的。
每隔一段時間,汽車的銷售價格就要做一些調整。
所以,他現在想要知道,「最近一次和上一次的銷售價格差異是多少?」
那麼問題再歸納整理一下就是這樣的:
❶ 最近一次銷售價格是多少?
❷ 上一次銷售價格是多少?
❸ 兩者之間的差值是多少?
最終的效果大致是下面這樣子的:
以上問題,本質上就是一個數據查詢的問題,查詢最近一次和上一次的數據。
在 Excel 之中,當我們要進行數據查詢時,一般我們會想到,使用 Vlookup 函數,透視表,或者 Lookup 函數等等~
那麼,大家可以先結合這幾種思路思考一下~
......思考時刻……
用 Vlookup 函數?
好像只能查找第一個出現的值。
用數據透視表?
好像只能得到最大日期的價格。
用 Lookup 函數?
雖然可以得到最近的日期的價格,但是好像不能得到倒數第二次的價格。
……
…………思考時間…………
如果是你,你會怎麼做呢?
好吧,我給大家來解釋一下,無論使用哪種方法,都會遇到困難點——倒序查找。
就是查找最後一次和倒數第 2 次的數據。
前面幾種方法,直接用一個函數或者數據透視表都很難實現「自主選擇返回第幾次的數據」。
為了滿足這個需求,我們就要費一番周折,藉助 Countif 函數和 Vlookup 函數來組合實現。
好啦,那接下來我們來看一下具體的做法。
今天,我就來給大家介紹一下函數法~
我們還是以這個數據作為例子。
下面就來看看具體操作。
01
按照日期進行升序排序
❶ 選中表格,點擊【數據】選項卡-【排序】;
【主要關鍵詞】-「價格維護時間」;
【次序】-「升序」。
「為什麼要將日期先進行排序呢?」
這裡主要是為了後面可以利用 Vlookup 函數,得到最近的一個價格。
還是不理解?沒關係,先往後繼續看~
02
添加輔助列
首先新增一列,利用拉燈式得到每個車型的出現次數。
(不理解拉燈式沒關係,下面馬上就會講到啦!)
❶ 插入列的方法:
可以選中列,右鍵插入;
也可以利用快捷鍵【Ctrl+shift+=】,直接在左邊插入一列。
❷ 利用拉燈式得到每個車型的出現次數。
A2 單元格輸入公式如下:
<code>=COUNTIF($B$2:B2,B2)/<code>
❸ 在價格列前面添加輔助列,將次數和車型連接起來。
公式如下:
<code>=A2&B2/<code>
看到這裡,肯定有小夥伴疑惑了:
第一個輔助列用 Countif 函數的目的是?
什麼是拉燈模式,拉燈模式是幹嘛的?
最後為啥將 A 列和 B 列合併?
下面我們就來解析一下~
Q1:第一個輔助列用 Countif 函數的目的是?
第一個輔助列,我們用的是 Countif 函數,主要是
得到各類車型依次出現的次數。Countif 函數,能夠對區域中滿足單個指定條件的單元格進行計數。
基本語法為:
=COUNTIF(查找區域,條件)
第一個輔助列:
A2 單元格:
<code>=COUNTIF($B$2:B2,B2)/<code>
就是在 B2 到 B2 的區域中找 B2,也就是中卡車出現 1 次。
A3 單元格:
<code>=COUNTIF($B$2:B3,B3)/<code>
就是在 B2 到 B3 的區域中找 B3,也就是大卡車出現 1 次。
<code>
A4 單元格:
<code>=COUNTIF($B$2:B4,B4)/<code>
就是在 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&中卡車」的公式,這裡我們可以查找「中卡車第二次出現」的價格。
<code>=VLOOKUP(2&"中卡車",C1:E12,2,FALSE)/<code>
▲左右滑動查看
結果為 92000。
通過上面的解釋,你是否看懂了呢?
如果明白了上面的解釋,下面編寫公式就難不倒你了~
03編寫公式
❶ 查找最近一次日期, I2 單元格中輸入公式:
<code>=VLOOKUP(COUNTIF($B$2:$B$12,$H2)&$H2,$C$1:$E$12,2,FALSE)/<code>
▲左右滑動查看
❷ 查找倒數第二次日期, J2 單元格輸入公式:
<code>=VLOOKUP((COUNTIF($B$2:$B$12,$H2)-1)&$H2,$C$1:$E$12,2,FALSE)/<code>
▲左右滑動查看
❸ 最後計算兩個的差值, K2 單元格輸入公式:
<code>=I2-J2/<code>
下面我們就來簡單解析一下公式~
前面我們知道了,可以利用 Vlookup 函數,通過「次數&車型」來查找,獲得第幾次出現的價格;
最前面,我們也已經將日期進行升序排序了。
那麼,如果想要獲得「某種車最近一次日期」的價格,我們可以利用 Vlookup 函數。
比如:通過查找「小轎車一共出現的次數&小轎車」的價格,也就是最近一次日期的價格。
那如何才能得到小轎車的總次數?
前面我們介紹過 Countif 函數,它可以得到小轎車在某個區域中出現的總次數,公式如下:
=COUNTIF(區域,「小轎車」)
所以,在 I2 單元格中輸入公式如下,得到最近一次日期:
<code>=VLOOKUP(COUNTIF($B$2:$B$12,$H2)&$H2,$C$1:$E$12,2,FALSE)/<code>
▲左右滑動查看
在 J2 單元格中輸入公式如下,得到上一次日期:
<code>=VLOOKUP((COUNTIF($B$2:$B$12,$H2)-1)&$H2,$C$1:$E$12,2,FALSE)/<code>
▲左右滑動查看
最後兩者差值,直接相減就可以得到啦~
好啦,總結一下,本文涉及到的知識點和思路有:
❶ 通過排序為後面獲取最大值做準備。
❷ 藉助 Countif 拉燈模式獲得車型依次出現的次數。
❸ 通過輔助列構造不重複值,再用 Vlookup 函數進行查找。
04
小延伸
前面,我們是將日期先進行升序排序。
後面,通過計算各車的總次數,還有次數減 1,利用 Vlookup 函數返回對應的價格。
那麼我們逆向思考一下。
如果我們事先將日期進行降序排序。
後面就可以直接用「1&車」,「2&車」,分別利用 Vlookup 函數得到最近一個日期,還有倒數第二次的價格。
這樣就少了後面 Countif 函數計數的那一步了。
這其實是一個逆向的思路,我們關鍵是理解思路背後的原理。
前面我們是用組合公式完成的,對吧?
但是組合公式還有幾個缺點:
❶ 每次都需要提前將數據按照日期進行排序;
❷需要寫很多輔助列;
❸ 數據無法自動更新。
而這些缺點,使用一個叫做 Power Query 的神器都可以通通解決掉!!
看一下演示的效果~
整個過程不需要事先對數據進行排序,不需要做輔助列。
直接在右邊的結果上面,右鍵點擊刷新,就可以完成數據的更新。
一勞永逸的感覺,有沒有!
想要知道這個是怎麼做的嗎?快快給我點贊吧!
點贊越多,我寫文章就寫得越快,可能下篇文章我們就可以來聊這個話題。
還有任何疑問,或者其他做法,歡迎在後臺留言和小E互動,有用的話可以動動手轉轉發~
私信回覆關鍵詞【UP】~
獲取VLOOKUP函數用法教程合集,一看就會!(◦˙▽˙◦)
閱讀更多 秋葉Excel 的文章