在進(jìn)銷存管理中，最難、最復(fù)雜的一個工作就是根據(jù)銷售數(shù)量，用先進(jìn)先出法計算銷售成本。這不，蘭色在設(shè)計《Excel進(jìn)銷存系統(tǒng)》時就遇到了這個棘手的難題：
如下圖所示在銷售表中根據(jù)出庫數(shù)量計算成本金額。
先跟蘭色一起了解先進(jìn)先出的計算原理。
先進(jìn)先出：就是先入庫的產(chǎn)品先出庫，計算成本價時優(yōu)先。
【例】如下圖所示，A產(chǎn)品先后入庫3個批次，每次的價格都不同。
情形一，A產(chǎn)品第一次銷售20個，因為A的第1批進(jìn)了50個，所以可以全部按該批次的價格算成本，成本應(yīng)為：
=20*2=40
情形二，A產(chǎn)品第一次銷售51個，那么第一批進(jìn)的不夠用，所以要從第二批拿出一個算成本。成本總額為：
50*2 1*5=104
情形三：A產(chǎn)品第一次銷售70個，前兩批都不夠用，所以得從第三批中拿出10個算成本。
=50*2 10*4 10*9=230
情形4：A產(chǎn)品后續(xù)又銷售了5個，因為前面已把前2批貨賣完了，所以直接從第3批（價格為9）中拿貨并計算成本。
=5*9=45
以后再銷售以此類推。
如果你沒從事過會計，估計看的有點暈了。這個公式不但要考慮入庫表的所有批次價格，而且還要考慮銷售表已賣的數(shù)量。所以你可以想象用Excel公式來設(shè)置有多么的難。如果都用手工，一個幾百行的小表也會花費你一天的時間。
為了解決這個，用VBA編寫了一個自定義函數(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)
語法：
成本（商品名稱,商品區(qū)域,銷量區(qū)域,已計算成本區(qū)域,入庫表商品名稱，入庫表數(shù)量區(qū)域,入庫表單價區(qū)域)
參數(shù)說明：
Function 成本(商品, 已銷商品rg As Range, 已銷售數(shù)量rg As Range, 已成本rg, 商品rg As Range, 入庫數(shù)量rg As Range, 單價rg As Range)
Dim 入總, 總數(shù)量, 總成本, 銷售數(shù)量 As Integer, 已計算銷售成本arr1 = 商品rgarr2 = 入庫數(shù)量rgarr3 = 單價rgarr4 = 已銷商品rgarr5 = 已銷售數(shù)量rgarr6 = 已成本rg
For M = 1 To UBound(arr4) If arr4(M, 1) = 商品 Then 銷售數(shù)量 = 銷售數(shù)量 arr5(M, 1) If M < UBound(arr4) Then 已計算銷售成本 = 已計算銷售成本 arr6(M, 1) End IfNext M
For x = 1 To UBound(arr1) If 商品 = arr1(x, 1) Then '入總 = 入總 arr2(x, 1) If 總數(shù)量 < 銷售數(shù)量 Then If 總數(shù)量 arr2(x, 1) < 銷售數(shù)量 Then 總成本 = 總成本 arr2(x, 1) * arr3(x, 1) Else 總成本 = 總成本 (銷售數(shù)量 - 總數(shù)量) * arr3(x, 1) End If End If 總數(shù)量 = 總數(shù)量 arr2(x, 1) End If Next x If 總數(shù)量 < 銷售數(shù)量 Then 成本 = '銷量大于庫存數(shù)量，請核查' Else 成本 = 總成本 - 已計算銷售成本 End IfEnd Function