Excel多條件合併單元格情境交叉查找,這個案例真的好經典(INDEX MATCH MAX)

Urin的自习室
19粉絲
大家好
這一張呢回覆一個粉絲同學的問題
啊
他的問題呢是這個樣子的
這邊呢有一個銷售明細表格啊
中間呢
他是一個一維的統計啊
一些日期當中一些訂單號
然後呢
他們分別所對應到的數量和送單號
那現在呢
我需要把這種格式通過函數的方式
把它給轉換成是這個樣子的
那首先呢
這個問題呢
他比較麻煩的點呢
就在於他會有多個條件
同時呢
還有一個問題啊
就是他會有合併單元格啊
比如說這邊是7月1號
其實呢
合併單元格
他只有第一個位置呢
會有內容
所以這個位置應該如何去進行一個製作
好
首先我們先來理一下思路啊
我們可以去找到該訂單號
該日期
他所對應到的函數
對嗎
比如說呃這個12340001
然後呢
在7月1號的時候
他應該是出現在哪一行當中呢
7月1號該單號
他應該是出現在第一行
那如果是7月3號啊
這個004的這個訂單的話呢
他就應該會出現在第四行
所以我們需要通過兩個條件
然後呢
去確定
這個送單號
然後他會出現在這個表格當中的哪一行
然後呢
在這一行當中
對吧
在這一列當中
我們選擇給到第四行
那自然就可以把這個內容
把它給擺放在那這個送貨單號
他所在的位置啊
所以這個就是我們的基本思路
首先我們需要去解決的第一個問題呢
就是說如何把這個合併單元格來做處理啊
注意
因爲他是日期啊
日期呢
其實他的本質就是數值類型啊
所以呢
你可以使用到max函數
然後呢
選擇到這個單元格
冒號到這個單元格
那並且呢
我們把第一個單元格可以去做一個F4的絕對引用
第二個位置呢
注意
因爲你在往下拉的時候
他應該還是鎖定在第一行當中
所以我們應該是鎖行而不鎖列
而括弧回來確定
那這個時候呢
當你在往後拉的時候
你都會發現說他都可以分解在每一個單元格當中
這邊是7月1號
7月1號
7月2號
因爲此時此刻
如果我們從這個位置拉扯到這個位置來的話呢
他比較大的值呢
就是7月2號了
對嗎
好
所以這個樣子的話呢
就是我們的第一步啊
好
那接下來啊
我們通過先來找函數嘛
所以呢
可以來做一個match函數
match哪個的match剛纔的max函數
然後呢
再去連接連接這個訂單號訂單號往右邊拉列
不會發生變化
但是我下面拉的時候呢
他的行會發生變化啊
所以呢
我們應該是鎖列而不鎖行
好
然後呢
我們再繼續往後走啊
兩個條件對吧
max
match函數
他的一個數組形式啊
所以呢
選回到銷售明細表格當中的出貨日期
F4絕對用他
然後呢
再去連接連接這個訂單號當中
F4絕對用他零精確匹配擴合回來確定
那這個時候其實我們就可以找到啊
他所在的行爲
好
你再看啊
找到行爲之後呢
那我們需要去找的是什麼呀
找的返回的是這個送貨單號
如果我們來到了後面這個單元格
他所對應到的就是數量對吧
所以呢
其實我們應該說
index函數
然後呢
選擇到那這張表格當中的送貨單號呢
和數量這兩列當中
然後F4絕對用他在這一兩列當中
那兩列嗎
我現在已經知道了行爲
已經知道了行爲在第一行
但是我要的是這個送貨單號呀
所以呢
在這兩例當中
我們再去確認一下他所在的例數啊
所以呢
我們可以給到match函數來match哪個呢
來match我們當前的表頭
鎖行而不鎖利啊
往右邊拉力會發生變化
往下面拉行
他不會發生變化
在哪個位置match呢
在剛纔的這個表頭的數量和送貨單號
這兩列去做match
F4絕對用他零精確匹配擴弧回來
確定確定
那這個時候呢
我們就可以得出最後的結論
好
你看啊
我們其實這個思路很簡單
在這兩列當中去做匹配
那具體是哪一列呢
對吧
那就取決於剛纔我們所寫的函數當中的最後一個參數
那由於我們會有雙條件的匹配啊
所以我們需要去找到日期和對應的單號
然後呢
在這一兩列當中去確認它的行爲啊
所以在這兩列當中
比如說我們找到是這一列當中去做匹配
然後呢
通過這兩個條件又可以索引到我們最後所需要的這個函數的話呢
就可以交出錯
最後的結果啊
就這樣一個道理啊
所以我們可以把這個往後面拉
你看數量他就過來了
對吧
好
當你在往後拉呢
後面會出現很多的NA錯誤啊
因爲他找不到了啊
因爲在7月2號
然後他沒有這個12340001啊
所以他會出現NA錯誤
所以呢
你可以在他的前方給上一個if error函數
當他出現錯誤
我們則會給到空值的顯示括弧回來確定
然後呢
再把它給往右邊拉
然後再把它給往下面來做個拉扯啊
這個就是我們最後的結論
