怎么用EXCEL跨表格查詢成本價格并計(jì)算移動平均成本

?

作者：EXCEL應(yīng)用之家來源：部落窩教育發(fā)布時間：2021-03-25 11:42:04點(diǎn)擊：7692

分享到:

收藏收藏人氣：0人

版權(quán)說明：原創(chuàng)作品，禁止轉(zhuǎn)載。

編按：

日常工作中，入庫清單和訂購清單往往是兩個獨(dú)立的EXCEL表格。當(dāng)需要從貨物的入庫數(shù)量跨表格查詢到訂購清單中的成本價格，并計(jì)算出移動平均成本時，必不可少的會用到各種公式。接下來，就讓我們一起看看大神們是用什么樣的操作實(shí)現(xiàn)這種查詢匹配并統(tǒng)計(jì)的工作吧！

在采購實(shí)物中，有些原材料的價格受到供需關(guān)系的影響，具有非常強(qiáng)的“彈性”，不同時間段所購買的原材料的單價是不同的。比如說，有色金屬、石油等商品的價格永遠(yuǎn)都處在一個波動的過程中。因此，財務(wù)在操作中為了準(zhǔn)確界定庫存的采購成本，會使用“移動平均價”這個概念。簡單來說，就是庫存中儲存的原材料的采購成本應(yīng)該是多次采購價格的平均值。

今天向大家介紹的這個例子，就體現(xiàn)了上面所介紹的概念。

對于某幾款原料，有不同的采購日期和數(shù)量，也有不同的入庫時間和數(shù)量。

現(xiàn)在要根據(jù)“入庫數(shù)量”來匹配到相對應(yīng)的訂單表單中的“成本價格”。如果一次的“入庫數(shù)量”大于一個及以上的“訂單數(shù)量”，那么需要自動計(jì)算該原料的移動平均價格。

問題：如何計(jì)算D列中的移動平均價格？

邏輯思路：

① 在11月15日，入庫產(chǎn)品A，數(shù)量是1000件。在F和G列中尋找相應(yīng)的A產(chǎn)品訂單后，確認(rèn)入庫產(chǎn)品來自于11月6日的A產(chǎn)品的訂單，其成本單價為50元。因此在11月15日入庫時的移動平均成本是50元。

② 在11月18日，再次入庫產(chǎn)品A，數(shù)量是1500件。在F和G列中尋找相應(yīng)的A產(chǎn)品訂單，并結(jié)合①后，確認(rèn)這批入庫量是來自于兩個訂單。其中1000件來自于11月6日的訂單，成本單價為50元；500件來自于11月20日的訂單，成本單價為45元。這時候，產(chǎn)品A的移動平均成本是（1000*50+500*45）/1500=48.33元。

相同的邏輯，其它產(chǎn)品的移動平均成本的計(jì)算過程是相同的，這是題目的數(shù)學(xué)邏輯和思路。

那么，這個問題用EXCEL又該怎樣實(shí)現(xiàn)呢？

在單元格D2中輸入公式：

“=ROUND((SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9)-IFERROR(SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$1:$B1,B2,$C$1:C1))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9),))/C2,2)”，按三鍵（SHIFT+CTRL+ENTER）并向下拖曳即可。

大家可千萬不要被看起來這么長的公式給嚇到了。刨除最外層的ROUND函數(shù)，其實(shí)這么長的一段可以分為三部分：

第一部分：SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9)

第二部分：IFERROR(SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$1:$B1,B2,$C$1:C1))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9),)

第三部分：((…)-(…))/C2

公式雖然長，但大家仔細(xì)看可以發(fā)現(xiàn)，它主要運(yùn)用的就是幾個簡單函數(shù)：SUM函數(shù)、FREQUENCY函數(shù)、INDIRECT函數(shù)和SUMIF(S)函數(shù)。

下面，筆者將利用庖丁解牛的方法給大家一層一層來分析這個公式！

公式詳解↓↓

第一部分公式思路：

① 根據(jù)入庫的數(shù)量來匹配的相應(yīng)的訂單。以訂單數(shù)量為依據(jù)，然后根據(jù)入庫的數(shù)量來定位并獲取訂單數(shù)量。要實(shí)現(xiàn)這個目的，使用FREQUENCY函數(shù)是最合適的了。

② 用FREQUENCY函數(shù)實(shí)現(xiàn)“入庫數(shù)量超過一個訂單數(shù)量時，超出部分自動匹配到下一個訂單”的目的，不能使用簡單一個數(shù)字（入庫數(shù)量）作為參數(shù)，這樣會讓FREQUENCY函數(shù)只能定位并計(jì)頻在一個點(diǎn)上。因此，這里，大家需要用一個自然數(shù)序列——從“1”到入庫數(shù)量的自然數(shù)序列。

最后得出：FREQUENCY函數(shù)的第一個參數(shù)應(yīng)該用ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2)))

③ SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)部分，依據(jù)F列中的時間段，對單元格B2(產(chǎn)品A)匯總，其結(jié)果是{2000;2000;2000;2500;2500;2500;3500}；SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2)部分，刨除掉不是A產(chǎn)品的訂單，其結(jié)果是{2000;0;0;2500;0;0;3500}。

④ 利用FREQUENCY函數(shù)來計(jì)頻。FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))部分的結(jié)果是{1000;0;0;0;0;0;0;0}，表示入庫的1000件A產(chǎn)品都來自于11月6日的訂單，數(shù)量是1000件。

⑤ SUM函數(shù)。SUM(FREQUENCY(ROW(INDIRECT("1:"&SUMIF($B$2:$B2,B2,$C$2:C2))),SUMIFS(I:I,G:G,B2,F:F,"<="&$F$2:$F$8)*($G$2:$G$8=B2))*$H$2:$H$9)部分，先用{1000;0;0;0;0;0;0;0}乘以采購成本{50;20;35;45;30;25;40;0}，再用SUM函數(shù)求和，得到了總的A產(chǎn)品的入庫金額。

到這里為止，公式的第一部分我們拆解分析完成了。

第二部分公式思路：

這部分公式的目的是要把當(dāng)次入庫之前的同類產(chǎn)品的入庫金額計(jì)算出來。

例如，當(dāng)大家把鼠標(biāo)定位在單元格D4上時，D4單元格內(nèi)的公式，第一部分得到的結(jié)果是122500，第二部分計(jì)算出本次入庫前A產(chǎn)品已經(jīng)有過的所有入庫貨品的總成本。

計(jì)算如下：

A產(chǎn)品之前有一次入庫記錄，入庫數(shù)量是1000件，成本50元，成本總金額是50000元。用第一部分得出的122500減去第一次入庫的50000元后，再除以本次的入庫數(shù)量1500件，移動平均成本就是48.33元。

注意：

這里和第一部分有少許差異，兩段動態(tài)的單元格區(qū)域的起始部分不一樣，分別是$B$1:$B1和$C$1:C1。

為什么要做這樣的變動呢？

因?yàn)楣街械膯卧駞^(qū)域$B$1:$B1對單元格B2條件求和時，在$B$1:$B1中找不到“A”這個數(shù)值后，會返回了錯誤值。這時，再利用IFERROR函數(shù)將錯誤值轉(zhuǎn)換為“0”即可。

第三部分公式思路：

當(dāng)次入庫的A產(chǎn)品的總金額除以當(dāng)次入庫數(shù)量，就得到了移動平均成本。最后，利用ROUND函數(shù)對結(jié)果進(jìn)行四舍五入修正。