在進(jìn)銷(xiāo)存管理中,，最難、最復(fù)雜的一個(gè)工作就是根據(jù)銷(xiāo)售數(shù)量,，用先進(jìn)先出法計(jì)算銷(xiāo)售成本,。
如下圖所示在銷(xiāo)售表中根據(jù)出庫(kù)數(shù)量計(jì)算成本金額。
先跟一起了解先進(jìn)先出的計(jì)算原理,。
先進(jìn)先出：就是先入庫(kù)的產(chǎn)品先出庫(kù),，計(jì)算成本價(jià)時(shí)優(yōu)先。
【例】如下圖所示,，A產(chǎn)品先后入庫(kù)3個(gè)批次,，每次的價(jià)格都不同。
情形一,，A產(chǎn)品第一次銷(xiāo)售20個(gè),，因?yàn)锳的第1批進(jìn)了50個(gè)，所以可以全部按該批次的價(jià)格算成本,，成本應(yīng)為：
=20*2=40
情形二,，A產(chǎn)品第一次銷(xiāo)售51個(gè)，那么第一批進(jìn)的不夠用,，所以要從第二批拿出一個(gè)算成本,。成本總額為：
50*2+1*5=104
情形三：A產(chǎn)品第一次銷(xiāo)售70個(gè)，前兩批都不夠用,，所以得從第三批中拿出10個(gè)算成本,。
=50*2+10*4+10*9=230
情形4：A產(chǎn)品后續(xù)又銷(xiāo)售了5個(gè)，因?yàn)榍懊嬉寻亚?批貨賣(mài)完了,，所以直接從第3批（價(jià)格為9）中拿貨并計(jì)算成本,。
=5*9=45
以后再銷(xiāo)售以此類(lèi)推。
如果你沒(méi)從事過(guò)會(huì)計(jì),，估計(jì)看的有點(diǎn)暈了,。這個(gè)公式不但要考慮入庫(kù)表的所有批次價(jià)格，而且還要考慮銷(xiāo)售表已賣(mài)的數(shù)量,。所以你可以想象用Excel公式來(lái)設(shè)置有多么的難,。如果都用手工，一個(gè)幾百行的小表也會(huì)花費(fèi)你一天的時(shí)間,。
為了解決這個(gè),，用VBA編寫(xiě)了一個(gè)自定義函數(shù)。完美解決了先進(jìn)先出算成本的難題。
=成本(H2,H$1:H2,I$1:I2,J$1:J1,B$2:B$18,C$2:C$18,D$2:D$18)
語(yǔ)法：
成本（商品名稱(chēng),商品區(qū)域,銷(xiāo)量區(qū)域,已計(jì)算成本區(qū)域,入庫(kù)表商品名稱(chēng),，入庫(kù)表數(shù)量區(qū)域,入庫(kù)表單價(jià)區(qū)域)
參數(shù)說(shuō)明：
- 商品名稱(chēng)：引用單元格中的商品名稱(chēng)
- 商品區(qū)域：銷(xiāo)售表中截止本行之前的商品列2行區(qū)域,，鎖定開(kāi)始行，如H$1:H2
- 銷(xiāo)量區(qū)域：銷(xiāo)售表中截止本行之前的銷(xiāo)量2行區(qū)域
- 已計(jì)算成本區(qū)域：輸入公式位置的上一個(gè)單元格,，要用區(qū)域的形式錄入,，如：J$1:J1,
- 入庫(kù)表商品名稱(chēng)：入庫(kù)表中商品名稱(chēng)或商品編號(hào)區(qū)域（商品的唯一標(biāo)識(shí)）
- 入庫(kù)表入庫(kù)數(shù)量區(qū)域：入庫(kù)表中商品入庫(kù)數(shù)量所在列區(qū)域
- 入庫(kù)表單價(jià)區(qū)域：入庫(kù)表中商品單價(jià)所在列區(qū)域（商品的唯一標(biāo)識(shí)）
Function 成本(商品, 已銷(xiāo)商品rg As Range, 已銷(xiāo)售數(shù)量rg As Range, 已成本rg, 商品rg As Range, 入庫(kù)數(shù)量rg As Range, 單價(jià)rg As Range)
Dim 入總, 總數(shù)量, 總成本, 銷(xiāo)售數(shù)量 As Integer, 已計(jì)算銷(xiāo)售成本arr1 = 商品rgarr2 = 入庫(kù)數(shù)量rgarr3 = 單價(jià)rgarr4 = 已銷(xiāo)商品rgarr5 = 已銷(xiāo)售數(shù)量rgarr6 = 已成本rg
For M = 1 To UBound(arr4)If arr4(M, 1) = 商品 Then銷(xiāo)售數(shù)量 = 銷(xiāo)售數(shù)量 + arr5(M, 1)If M < UBound(arr4) Then 已計(jì)算銷(xiāo)售成本 = 已計(jì)算銷(xiāo)售成本 + arr6(M, 1)End IfNext M
For x = 1 To UBound(arr1)If 商品 = arr1(x, 1) Then'入總 = 入總 + arr2(x, 1)If 總數(shù)量 < 銷(xiāo)售數(shù)量 ThenIf 總數(shù)量 + arr2(x, 1) < 銷(xiāo)售數(shù)量 Then總成本 = 總成本 + arr2(x, 1) * arr3(x, 1)Else總成本 = 總成本 + (銷(xiāo)售數(shù)量 - 總數(shù)量) * arr3(x, 1)End IfEnd If總數(shù)量 = 總數(shù)量 + arr2(x, 1)End IfNext xIf 總數(shù)量 < 銷(xiāo)售數(shù)量 Then成本 = "銷(xiāo)量大于庫(kù)存數(shù)量,，請(qǐng)核查"Else成本 = 總成本 - 已計(jì)算銷(xiāo)售成本End IfEnd Function
第2步 打開(kāi)你的Excel表格，然后通過(guò)開(kāi)發(fā)工具-Visual basic打開(kāi)編輯器,，再插入 -模塊,，然后把代碼粘到右側(cè)的窗口里。最后把文件另存為：?jiǎn)⒂煤甑膃xcel工作簿類(lèi)型,。
以后,，在這個(gè)excel文件里就可以直接使用“成本”函數(shù)了。
解決先進(jìn)先出公式是蘭色很多年的愿望,，但一直手懶沒(méi)去編寫(xiě),。這次要做進(jìn)銷(xiāo)存管理系統(tǒng)繞不過(guò)去了，嘿嘿,，只能耐下心搞定它了,。
