Excel經典函數嵌套（INDIRECT+SUMIFS+SUMPRODUCT+VLOOKUP）解決一個大問題！
大家好
這一章我們來講一個非常複雜的案例
爲什麼說這個案例複雜
大家先可以來看一下需求
它是一個材料清單的需求
你看這邊我們總共會有一份計劃
這份計劃當中會有4個產品
4個產品它們分別所對應到的
計劃的一個產量的情況
每一個產品都會有個產品編碼
好
現在每一份產品
我們都會有一個單獨的需求表格
譬如說剛纔我們所看到的
A001所對應到的產品1對嗎
然後每生產一個產品1
我們所需要的耗材是這個樣子的
這邊是A002
這邊是A003等等
最後的需求是什麼
最後的需求是想說
我們把所有的這些子材料組建編碼
然後去做一個總的需求表格
應該如何去做
首先大家搞清楚
我在這邊會要使用到的方法是什麼
我會要使用到
以SUMIFS函數爲主體的一個方法
大家先來理一下思路
SUMIFS函數
我們需要滿足什麼條件
我們需要滿足的第一個條件
就是需要找到這4個表格當中
對吧
然後
F列
F列來作爲它的一個求和的區域
對嗎
然後我們再選擇到這4個表格當中
然後選擇組建編碼這一列
去作爲它的條件區域
然後再選擇當前的組建編碼
去作爲它的當前條件
應該是這個樣子去考慮對嗎
但是由於它是跨越了會有4個表格
所以這個位置的做法大家看好了
我給大家來做拆解
我們先來做求和區域
這個位置我們會要使用到
等於indirect函數
好
首先由於它不是純文本
有可能你們自己在做的時候
中間還會有一些符號之類的
特別是和我們的產品編碼有關係
有可能中間是一個橫槓什麼之類的
對吧
所以你最好是在做indirect函數的時候
我們選擇先來給到雙引號
現在我想要找到的是
A001到A004這個表格
對嗎
所以你可以來寫上A00
而1234它應該是變量
對不對
所以由於剛纔我給大家講了
它中間可能會出現那種特別符號
所以你最好是在前方去加上一個單引號
好
然後A00去連接row函數
然後選擇到第一行到第四行
你不要選擇到
就是選擇到整個這四行
這個樣子會加大一些計算量
所以我會選擇到A1到A4
F4絕對用它
現在它的意思就是A001到A004
對吧
這些表格當中
它們分別所對應到的F列
所以再去做一個連接雙引號
由於之前加了單引號
所以我們到這邊去做一個閉環的單引號
感嘆號代表的是前方是表格名稱
然後後面就是F列對嗎
所以有好多同學可能就會直接使用到F列
可以嗎
但是這個樣子
如果你的數據比較多
可能會比較卡頓
所以你就給到
F1到F1000
我覺得怎麼樣都夠了
由於它本來就是
出現在我們雙引號中間的一個文本
所以就沒有必要去考慮
它是否是絕對引用了
然後直接括弧回來
確定
這個大家所看到的value錯誤
其實這個位置它所代表的就是整個這一列
那第二個位置它所代表的就是整個這一列
對吧
但是它其實是個多維數座
所以你沒有辦法說我直接讓它顯示出來
所以這個樣子就是我們的求和區域
好
我再來做一個叫做條件區域
是不是跟剛纔是一樣的
所以我對它去做一個複製
然後整個把它給貼在後面
好
然後你再看
條件區域是來自於B裏對不對
求和是F
條件是B裏
所以這個位置你就可以自己去修改
這邊的這個F修改成爲是B就行了
好
我們有了求和區有了條件區有了條件
那我們可以先來做一番嘗試
等於sumifs函數
然後我會在前面去打個點
大家如果是做比較複雜的函數
可以去把它給拆分開來去進行製作
再選擇把求和區域對它去做複製
把它給貼在剛纔的sumifs函數中間的第一個函數逗號
然後我們再選擇到剛纔的條件區域
對它去做一個複製
再選擇把它給放在sumifs函數中間的條件區域逗號
對吧
然後我再把之前的單一號刪掉
它選擇條件是當前的條件
確定
你看這個樣子我們就可以得到結論
爲什麼
第一個表格我給大家做了顏色標記
就是10420-1001
你看第一個表格滿足
就是黃色我給大家塗了顏色
第二個表格沒有了
第三個表格也沒有
第四個表格它也是第一個位置
所以你可以發現
3 3出現分別是第一個表格和最後一個表格
它所在的位置
你看3
這邊也是3
好
那如果說現在我把這個位置給修改成爲是2
你可以來看一下
是不是變成了是3和2
好
那這個樣子還不夠
爲什麼
因爲我們還需要說用這個3
然後還要去它是單位號採
我們的總共的生產數量是644
當然之前我講過說等於用它
然後再去乘以644F4絕對應用
然後使用到輔助列
然後去做
剛纔的條件求和的計算可以嗎
可以了
當然也可以
或者你可以這樣子去做
我給大家來做個拆解
首先我們還是貫徹之前我們所講的
我先把這邊稍微拉長過來一些些
等於
indirect函數
好
然後你想跟之前一樣的操作
單引號
A00
然後再去連接row函數
對吧
然後A1到A4
F4絕對用它括弧回來連接雙引號
然後單引號B環
加上一個感嘆號
然後我先在前方去打個逗點
你看我們應該調取到的是
每一個表格它同樣一個位置的生產的數量
對不對
好
所以這個位置我們可以怎麼樣去做
選擇到之前的B3單元格
所以你可以輸入B3
確定
但是它調取不出來
調取不出來
爲什麼
因爲用indirect函數通過這種跨表格
這種方式去進行內容調取的時候
我們還需要在前方去加上個n函數
n函數
它可以把它轉換成爲是數值格式
對吧
這個樣子的話
我們就可以用交錯相傳的方式去做
用3去乘以644
然後用0去乘以
第二和第三個位置
最後就是用它去乘以909
對不對
好
這種方式大家可以記住
就是如果你要去跨表格引用同一個單元格
調取不出來分成兩種情況
第一種情況就是這種數值類型的
第二種情況就是是那種純文本類型的
所以如果是純文本類型
我給你舉個例子
比如說我想要調取A001
等會我們也會用到這種方法的
A001
你就可以應該是B1單元格
對吧
如果你用n函數就調取不出來
所以你可以用到一個t函數
可以把這個文本把它給調取出來
好吧
好
這一步做完了之後
其實就是之前我們的這個公式
然後再去乘以剛纔的n函數
所以把這個公式再去做一個複製
然後再選擇讓它去乘以剛纔的n函數
確定
你看
好
然後我們還需要對它去做求和
對不對
所以在前方可以去加上一個sum函數
確定
然後如果是老版本的control shift加上回車鍵
或者是直接使用到sum product函數去做
好
然後我對這個函數去做一個複製
然後再選擇把它給放在當前的表格中間
然後我們再來做一個雙擊下拉
這個就是我們所得到的一個結論
好
還有一種可能性
我正好像剛纔已經講到了這個t函數
所以給大家再去做一個延伸
然後我把這一列整個來做一個複製
然後把它給貼在旁邊
好
把這邊把它給刪掉
好
你想之前我們是用選擇多個表格
然後通過vlookup函數的這種形式
然後去查找到的同樣一個位置
然後在當前這個表格中間去進行的創造
對嗎
好
如果說我們沒有
在後面的單元格中間去輸入到生產數量
我就只有在當前的表格中間只有一個產品面板
它這邊啥都沒有
想象一下
然後我只有個產品面板
這邊也只有一個產品面板
這個樣子的話又應該如何去做
跟之前的道理一樣的
你看首先我們可以這個樣子去做
我直接把這邊來做修改了
那剛纔我不是給大家去強調了嗎
如果你用n函數是可以把這邊的數值把它給調取出來的
那如果說我要調取文本的話
就應該選擇修改成爲是b1對嗎
那同時還需要用t函數
然後讓它顯示文本
好
那有文本之後
a001一直到a004
那我在它的基礎之上
我是不是可以通過vlookup函數
然後再去查找剛纔的這個t函數
indirect函數的結果
然後在計劃表格中間的a到c
這三類中間去進行查找
返回第三類
零精確匹配
括弧回來確定
你看是不是一樣的效果
所以那我現在可以把剛纔的這段函數
對它去做一個複製
好
你把它給貼進來之後
之前我們是乘以的n函數了
對吧
那現在你其實是可以怎麼樣
可以選擇把剛纔的這段函數
對它去做一個複製
然後再選擇用它再去乘以剛纔的
這個vlookup函數也可以
好
然後我們在前方
大家注意應該是發生在這個sum函數
之前
所以應該是用這個函數
然後再去乘以
這個vlookup函數
對吧
然後再用一個sum函數
或者是sumproduct函數
把它給套取出來
然後再選擇雙擊下拉
這個樣子的話
我們就可以得到最後的結論
我整個推導的過程中間的這些函數
包括這些筆記
都放在當前的文件當中
大家自己可以去下載
然後去參考一下
好嗎