以1敵5的XLOOKUP函數

Urin的自习室
19粉絲
大家好x look up函數大家可能聽聞已久
這個函數只有在office 365版本當中
才能夠被使用
很多人都說x look up函數的到來
意味着例如index加上match函數
look up函數
v look up函數已死
其實不然
它只是在某種程度上
可以替代這些函數而已
當然了功能上呢
會有一些提升
今天我們會要講到8個例子
讓大家來了解一下這個函數
在此之前呢
我們先來看一下這個函數的格式
我們都知道look up函數之前呢也講過
大家可以翻閱一下之前的內容
look up函數中間它會有三個參數
查找值查找範圍
結果範圍
x look up函數呢
則在look up函數的基礎之上
多出了兩個隱數
匹配類型和查找類型
那這些引數都應該怎麼樣去使用呢
我們一步一步來分解
我們先來對比三個函數
v Lookup look up以及x Lookup
大家可以看到
這邊會有一張產品的表單
產品系列
型號系列
進價以及零售價格
好現在我想通過精確匹配的方式
找到可口可樂它的零售價格
如果我們使用VLOOKUP函數呢
這邊直接呢就可以寫的等於VL type
然後選擇到
可口可樂所在的這樣一個單元格
範圍呢在整個這張表格中間
我們需要找到的是零售價格
所以它應該是在第四列啊
0精確匹配
確定
這個呢就是用we look up函數來做
如果我們想要用
look up函數來做呢
我們可以選擇到
首先我們要找可口可樂
然後呢哎
找到這個可口可樂
它所在的範圍應該是在產品系列當中
最後的輸出結果呢
在我們的零售價格系列當中確定
結果呢也都是一樣的
好如果我們使用到x look up函數呢
在這種情況下面
其實它和look up函數是一模一樣的
我們只需要用到
前面的三個參數就行了
等於x l type
然後選擇到可口可樂在哪個範圍呢
在我們的產品系列範圍
然後我們的這個結果呢
在零售價格系列當中確定
近似匹配的第一個例子
那譬如啊
今天呢我們到這邊銷售了一些產品
這個呢是我們的銷量好
然後呢
上方是我們的這樣一個打折表格了
那譬如銷量如果是0-4的話
那我們是沒有折扣
當他是5-15的話
小型批發
那他是0.9的這樣一個折扣
等等
那和之前我們所講過的VLOOKUP函數
VLOOKUP函數的近似匹配都很相似啊
都一樣的
那我們呢
通過比對的方式
來比較一下這樣一些函數了
譬如說我們要用到VLOOKUP函數
這邊呢等於VL
然後呢
我們可以選擇到銷量在哪個範圍
中間呢在我們的這個範圍表格中間
對吧然後我們需要找的是第幾列啊
是第四列
也就是我們的折扣列
最後呢我們會要做到近似匹配
所以說呢
我們可以到這邊來寫個一
或者呢寫two
或者呢你這邊不寫啊
也都可以確定
那這邊我們
就可以算出來了
那呃 55的銷量的話呢
就介於48到143之間
所以它的折扣是0.6折
好如果我們用look up函數去做呢
這邊等於look up
然後選擇到我們的銷量在哪個範圍呢
首先啊
我們需要來找到是在銷量系列當中
然後呢反饋出來的結果呢
在折扣系列當中
確定也是0.6折
對吧一樣的
那之前呢
我們所講過的look up
它還會有個屬性了
譬如我們再來找到我們的銷量是55
然後呢在哪個範圍中間呢
在整個這個範圍中間
我們直接框選這個範圍就行了
因爲我們的折扣
在整個表格的最右邊這一列啊
所以也就符合了
我們look up函數的其中一種用法
確定你看也是0.6
好我們再來看一下x look up函數
這邊呢我們可以寫到哈呃
x l Tab然後呢
選擇到我們的銷量
然後在哪個範圍呢
首先是在銷量系列當中
然後呢我們需要反饋的結果呢
在折扣系列當中
好如果我們一旦要用到近似匹配
我們或許就需要來用到
我們x look up函數當中的第四個參數
也就是匹配類型
那這個匹配類型呢
你如果不輸入默認精確匹配
好我們可以直接呢
先給到確定
你看找出來是錯的
精確匹配他是沒有辦法去找到的
所以我們就需要來找到什麼呢
找到中間合適的這樣一個檔
也就是你是選擇-1
還是選擇一
還是選擇2啊
當然2呢
在這種情況下面可以排除了
我們是選擇一還是選擇-1
那-1它的意思是什麼呢
就是精確匹配
或者找到下一個更小的值
所以它中間是個或的關係了
那我們可以到這邊來寫到什麼呢
來寫到逗號哈
把第四個參數呢
我們來寫到-1
確定
大家可以看到最後的輸出結果呢
你看也是0.6對吧
因爲我們首先啊是48這樣一個範圍
中間我們銷量是55嘛
他介於48到144之間
所以在這個之間呢
我們會默認選擇到
這樣一個比較小的值了
所以48呢
它所對應到的呢折扣呢就是0.6啊
它的原理也就這樣的
如果呢
我們到這邊再來寫到XL look up函數
然後呢選擇到銷量
選擇到我們的銷量區域
然後再選擇到我們的折扣區域
然後呢
如果我們一旦在這個地方輸入一
的話呢你猜它是等於多少
它等於啊0.5
也就是說55
它趨於48到144之間
然後呢我們選擇了一模式
那也就意味着說在這兩個數之間
我們會挑選一個比較大的值
那也就144144
它所對應到的折扣呢是0.5
並且x look up函數
還會有一個比較出衆的一個點
也就在於大家記不記得
我們在說we look up和look up的時候呢
我們都要求在近似匹配的情況下面
我們這一列哈必須來做到升序排列
而在x look up函數當中呢
無所謂
它跟你的升序和降序都沒有關係
大家呢也都可以去試一下
近似匹配的第二個例子
例如我們想要根據收益的
閥值來找到合適的投入成本
這邊呢是我們的收益啊
這邊是我們的成本
那現在此時此刻呢
我們的收益是250
我們想算一下
對應的合適的這樣一個成本
應該是多少
那首先呢
我們就應該把VLOOKUP函數
把它給排除出去
那比如我們想用VLOOKUP函數來做一下
選擇250在整個這張表格中間
然後找到第二列成本
然後一近似匹配
確定是錯誤值
爲什麼呢
因爲我們這邊是降序排列
所以我們用VLOOKUP函數呢
是沒有辦法去做的
那當然了
其實比較合適的理解呢
我們這邊應該使用到trend函數
因爲它符合一個線性規則
呃但是呢
我們這邊所強調的閥值是什麼意思呢
就比如說我現在是250
我沒有到300
我想按照200
來進行我們的成本的這樣一個投入
如果你到達了300
我則會給到120
但是呢你哪怕是399
我也都會給到120的成本
除非你達到了400
所以基於這樣一個考慮的話呢
我們啊可以到這邊
先來嘗試使用一下index函數
那這個是我們需要反饋的結果
然後呢行
這塊呢我們可以用到match函數
match哪個呢
match 250 在收益系列當中
由於這邊是做的降序
並且呢我們無法來做到精確匹配
所以這塊我們需要來輸入-1
括弧括弧確定
你可以看到250呢
它所對應到的成本投入是120
所以
跟剛纔我們所描述的這樣一段邏輯啊
不太相似
好有可能大家會考慮
我們把這個-1是否能改
成一呢這是不行的哈
因爲這個-1還是一呢
它取決於你是降序還是升序了
所以實質上呢
我們還是找不到合適的值
那我們如果是250的收益
他就應該是在200到300之間
他應該歸結到80啊
這樣一個成本投入
所以在這種情況下面呢
我們當然還會有其他的方法了
但是呢x look up函數呢
直接呢就可以把它給算出來
我們用到x look up函數
然後選擇到250
選擇到收益所對應到的這樣一個區域
結果反饋的區域
然後第三個參數啊
第四個參數啊
匹配類型呢
這塊呢我們可以寫到一個-1
確定這樣的話我們就可以找到80
爲什麼是80
我跟大家解釋一下啊
首先呢是250250
他趨於200到300之間
所以我們就可以找到200和300
這兩個數字
好正因爲剛纔呢
我們在第四個參數這邊
我們輸入的是-1
對吧輸入的是-1
所以如果他達不到精確匹配
我們則會啊
找到下一個更小的值
那在這兩個值當中呢
更小的值呢
也就是200了
200它所對應到的成本呢
那也就是80
那如果我們使用到一的話呢
找到下一個更大的值的話呢
你就可以發現這一邊他所找到的呢
就是120啊
所以在這樣一種模式
跟這樣一個邏輯下面呢
我們使用x look up函數啊
確實是比較好使的
如何找到最後一次的統計
例如在這張表格中間呢
日期他所統計的這樣一個邏輯呢
是從前到後
然後產品呢中間
呢都會出現很多的重複值
我們怎麼樣找到
最後一次統計的銷量呢
例如百事可樂最後一次呢
是出現在這個位置
可口可樂最後出現呢在這個位置
那我們可以用到look up函數
之前呢我們已經講過了
大家可以去找一下那一章
然後呢我們可以把它給往下拉啊
這個呢就是我們最後的結果
大家可以看一下了
呃可口可樂42看到沒有
然後冰紅茶呢是15
然後鮮橙多呢是19
確實啊都是最後一次
單個產品的統計
如果是在x look up函數中間呢
就相對會比較容易理解
等於x l tap
然後我們選擇到我們需要查詢的值是
百事可樂在哪個範圍呢
在產品系列當中
然後當然了這邊呢因爲等下會往下拉
所以我們這邊得做到絕對引用好
然後呢
呃我們返回的值呢
輸出的值呢
在銷量系列當中啊
銷量系列當中
同樣需要來做到一個絕對引用
好然後我們這邊匹配類型呢
你默認它就精確匹配
所以我們要找的
當然是一個精準的值了
所以我們可以把這個匹配類型
直接跳過去
再按一個逗號
在搜索模式就是查找類型這塊呢
我們有從前往後去找
也有從後往前去找
從後往前呢我們可以寫到-1
也就是說我們整個把它給倒過來了
這樣的話
我們就可以找到最後一個值了
很好理解了
確定
然後呢我們可以把它給下拉
你看我們得出的結果呢
也都是一樣的
x look up函數來替代index加上match函數
之前呢我們講過反向查找
那v look up函數的反向查找呢
這個功能呢確實需要來搭配choose函數
那通常情況下面
我們都會使用index函數加上match函數
去進行反向查找
那譬如在這種情況下面啊
這個是我們的產品表格
然後呢我們這邊會有個進價
進價是兩塊5
我們通過進價來找到對應的產品名稱
那這邊呢我們可以寫到哈index函數
我們需要反饋出來的呢
是我們的產品嘛
對吧然後呢我們來match一下
具體是在第多少行match
match哪個呢
match我們的2.5
然後在哪個系列當中呢
在我們的進價系列當中
然後0精確匹配
括弧回來
括弧回來
因爲列哈就在第一列嘛
所以我們不需要給列位了
確定找出來呢就是可口可樂好
如果我們用x look up函數呢
就會也比較簡單哈
XL然後呢我們來找到哪個呢
找到我們的這樣一個進價了
在哪個系列當中呢
在我們的這個系列當中
最後我們需要反饋的結果呢
在我們的產品系列當中
確定
你看這樣的話呢
其實我們反倒使用x LOOKUP函數
它會比較清晰
當然你用LOOKUP函數呢
也是一樣的對不對
x look up取代h look up函數
那之前呢我們已經講過了
在這樣一個縱向的表格中間呢
我們可以用x來取代v look up
那在這樣一張橫向的表單當中呢
我們同樣可以這樣來做
那譬如我們先用h LOOKUP函數
來找一下可口可樂
它的零售價格
那這邊呢我們可以寫到等於h LOOKUP
然後呢選擇到可口可樂
然後所在的範圍呢
在整個這張表單中間
我們現在所反饋的呢是
第幾行是我們的零售價格
我們這邊呢可以輸入40精確匹配確定
如果我們使用
h LOOKUP函數呢
同樣的道理
我們選擇到可口可樂
可口可樂它所在的區域呢
是在產品系列當中
然後呢輸出結果呢
是在零售價格系列當中確定
我們再來看一個混合應用
這個呢是一張貸款的表格
例如你貸了300萬
然後呢這一邊會有一個貸款年限
這上面呢是一個貸款的利率
那譬如你貸款40年
然後你的利率呢是5%的話
那你每個月呢就需要還14,465塊錢
如果是7%的利率貸十年的話呢
就3萬多塊錢
好那現在呢我們可以擬定兩個條件
一個條件呢是我們貸款年限
一個條件呢是我們的貸款利率
好我們就可以做一個縱橫交錯
最後呢就可以鎖定一個值
在這種情況下面
我們可以使用到這樣三個函數
index加上match
VLOOKUP函數加上match函數
包括最後呢
單獨的來使用一個XLOOKUP函數
我們先來看第一個index加上match
這邊直接可以寫到等於index函數
在哪個系列當中呢
在月環系列當中啊
不包括那表頭
然後呢match行位
match我們的貸款年限
在哪個系列當中呢在
年限系列當中
0精確匹配
括弧回來
然後呢我們還需要一個列位
所以我們再來match match 4%的利率啊
在我們的利率系列當中
0精確匹配
括弧回來
括弧回來
確定這樣的話呢
我們就可以找到4%的利率
30年啊大家可以看一下
4%利率30年在這個位置了
就是14,000多塊錢
如果使用we LOOKUP函數加上match函數呢
這邊可以寫到we LOOKUP函數
我們需要查找的值是什麼呢
查找的值是30年
好吧所以我們可以選擇到30年
並且呢選擇到這個系列當中
好吧好
然後呢所以呢
這個地方就是你需要給到第多少列
那當然這邊我們不能用到column
當然用column也可以了
譬如說你表格在正中間的話呢
可能還需要加上一個常數
那用match呢
就會相對比較簡單
我們可以用match函數
match哪個呢
match 4% 在哪個系列當中呢
並不是在這個系列當中
而是在包括我們的表頭啊
整個的系列當中
包括年限和利率這樣一個表頭
因爲我們表格上方和下方
需要做到一個完全的對應啊
0 括弧回來
然後我們要做到精確匹配啊
0確定那我們得出的結果呢
也都是一樣的
關於雙條件的x look up函數
如果大家不熟悉的話
我們可以把它給拆開來做
我們會有兩個條件
對吧
我們先把一個條件把它給做出來
我們先來看一下
到這邊呢
我們可以先寫到XL look up函數
我們來找到我們
的利率在哪個範圍中間呢
在我們的利率系列當中
並且呢我們最後的
這樣一個返回的數據範圍呢
我們可以選擇到下方
月供所有的數據範圍
確定
這樣我們可以得出一列數據
那這一列數據呢
正好就是4%啊
滿足條件的這樣一個數據範圍
好是否我們就已經會有一個
滿足條件的區域了呢
在這樣一個區域當中
我們再給他第二個條件
也就是正好是30年
你看他這個中間交錯呢
也就正好是14322.5
所以關於這個函數呢
我們首先呢可以分步驟來做啊
先把它給進行一個複製啊
寫完了以後複製一下它的公式
然後呢我們可以到下方來寫到等於x l
我們這一次來找30年在哪個區域呢
在我們的年限區域當中
最後
我們需要來找到返回數據的範圍呢
就是滿足4%利率它所在的數據範圍
所以這一次呢
我們可以把剛纔所書寫的x look up函數
把它給直接複製過來確定
你看哈這個公式呢
其實也比較好理解了
我們先呢可以把它給拆開來做
還是建議大家拆開做啊
我們先呢要知道
我們滿足條件的某一個範圍
然後呢在這個範圍當中
我們再給他一個條件
來鎖定他的行或者列
這樣的話我們就可以得出最後的結果
好譬如現在
我們可以把它給換到5%的利率
換到呃40年
那所有的輸出結果呢
也都是一樣的
最後我們再來講一下關於通配符
譬如在這張表格中間
呢
那這邊是百事可樂
可口可樂等等
那我想查找的產品呢
你看它的名稱呢
並不是很完整
這邊是百事
那如果我們如果要用精確匹配
或者是其他的查找方式
是查找不到任任何內容的
所以我們這邊呢
必須得用到通配服務
例如我們用VLOOKUP函數來試一下
我們來找百事
在此之前和之後呢
我們都需要來加上連接符
並且在前方呢
我們需要來加上通配符
這邊呢雙引號夾雜通配符
因爲通配符呢屬於文本格式
然後在哪個範圍
中間呢在我們的這個系列當中
然後返回是2
然後是0精確匹配
然後我們可以在這個位置呢
來做到一個絕對引用了
確定好你可以看到百事呢
找出來就是兩點五升
然後再往下拉一下
當然如果你直接呢查找這個值
並且呢在我們的we look up函數中間
我們沒有這樣一段的話呢
你是查找不出來的
好吧譬如我把這邊呢把它給刪除
我相信這個大家應該知道啊
你看這樣就不行對吧
所以我們只能這樣去做
如果使用x look up函數呢
我們中間就需要在匹配類型這邊呢
來做一個強調
譬如我們到這邊來寫到x l look up函數
當然我們還是需要來加上通配符連接
然後呢這邊得寫上啊通配符
後方呢還得再加一個通配符
然後我們需要查找的區域呢
是在產品系列當中
反饋出來的結果呢
是在我們的產品類型系列當中
當然了這邊呢
我們最好呢也做一個混合引用啊
然後呢我們的匹配類型呢
這邊我們可以寫到2
好吧確定
你看找出來是2點五升
那這邊呢
也都是一樣的啊
6升好
如果我們這邊沒有寫2的話呢
你看這邊就是錯誤啊
所以大家要記住這樣一點
今天的例子呢
也就說到這個位置
總結一下
其實沒有哪個函數是無敵的
還得取決於使用情景
