关于用Excel实现先进先出计算的新思考
四、扩展应用——多商品先进先出发出成本的计算
在笔者论文发表不久，在EXCELHOME论坛上发现了多商品先进先出发出成本的计算问题，具体请参加帖子:
问题描述：1、同一天有不同商品入库、同一入库单有不同商品名称、同一出库单也包含不同商品出库
2、入库单和出库单前分别有'RK'和'CK'开始、该序号不同日期中不会重复
3、没有0出库现象
要求：黄色部分用公式表示
图4 多商品先进先出发出成本的计算
据此，笔者对原自定义函数进行了拓展，扩展自定义函数用于解决如图4所示问题，计算原理和使用方法与单产品类似，只不过增加了判断商品类别的代码，具体请参加以下代码。
Public Function FIFOS(Category As Range,InQty As Range, InPrice As Range, OutQty As Range) As Variant
Dim TotalCount As Integer '定义变量
Dim count As Integer
Dim TotalCategory() As String
Dim TmpCategy() As String
Dim TotalInQty() As Double
Dim TotalInPrice() As Double
Dim TotalOutQty() As Double
Dim TotalOM() As Double
Dim aInQty() As Double
Dim aInPrice() As Double
Dim aOutQty() As Double
Dim aOM() As Double
Dim aOutQtyS1() As Double
Dim aOutQtyS2() As Double
Dim aOutQtyS() As Double
Dim t As Integer
Dim i As Integer
Dim j As Integer
Dim isExact As Boolean
TotalCount = InQty.count '定义数组大小
ReDim TotalCategory(1 To TotalCount)
ReDim TotalInQty(1 To TotalCount)
ReDim TotalInPrice(1 To TotalCount)
ReDim TotalOutQty(1 To TotalCount)
ReDim TotalOM(1 To TotalCount)
ReDim TmpCategy(1 To TotalCount)
For i = 1 To TotalCount '读入数据
TotalCategory(i) = Category(i)
TotalInQty(i) = InQty(i)
TotalInPrice(i) = InPrice(i)
TotalOutQty(i) = OutQty(i)
TmpCategy(i) = ''
Next
t = 2 '找出产品种类中的唯一值
isExact = False
TmpCategy(1) = TotalCategory(1)
For i = 1 To TotalCount
For j = 1 To t
If TotalCategory(i) = TmpCategy(j) Then
isExact = True
End If
Next
If isExact = False Then
TmpCategy(t) = TotalCategory(i)
t = t + 1
End If
isExact = False
Next
For t = 1 To TotalCount
If TmpCategy(t) <> '' Then
count = 0
For i = 1 To TotalCount '计算某产品在总表中所占行数
If TotalCategory(i) = TmpCategy(t) Then
count = count + 1
End If
Next i
ReDim aInQty(1 To count) '定义辅助数组的大小
ReDim aInPrice(1 To count)
ReDim aOutQty(1 To count)
ReDim aOutQtyS1(0 To count, 0 To count)
ReDim aOutQtyS2(0 To count, 1 To count)
ReDim aOutQtyS(0 To count, 1 To count)
ReDim aOM(1 To count)
j = 1
For i = 1 To TotalCount '从总表中读出要计算产品的数据
If TotalCategory(i) = TmpCategy(t) Then
aInQty(j) = TotalInQty(i)
aInPrice(j) = TotalInPrice(i)
aOutQty(j) = TotalOutQty(i)
j = j + 1
End If
Next i
For i = 0 To count '辅助变量初始化
aOutQtyS1(0, i) = 0
aOutQtyS1(i, 0) = 0
Next i
For i = 1 To count
aOutQtyS2(0, i) = 0
aOutQtyS(0, i) = 0
aOM(i) = 0
Next i
For i = 1 To count '计算某产品出库数量序列
For j = 1 To count
aOutQtyS(i, j) =Application.WorksheetFunction.Min(aInQty(i) - aOutQtyS1(i, j - 1), aOutQty(j) -aOutQtyS2(i - 1, j))
aOutQtyS1(i, j) = aOutQtyS1(i, j - 1) +aOutQtyS(i, j)
aOutQtyS2(i, j) = aOutQtyS2(i - 1, j) +aOutQtyS(i, j)
Next j
Next i
For j = 1 To count '类似Sumproduct函数，根据出库数量序列计算出库金额序列
For i = 1 To count
aOM(j) = aOM(j) + aInPrice(i) * aOutQtyS(i,j)
Next i
Next j
j = 1
For i = 1 To TotalCount 'aOM写入TotalOM，根据每种产品的出库金额序列，形成总出库金额序列
If TotalCategory(i) = TmpCategy(t) Then
TotalOM(i) = aOM(j)
j = j + 1
End If
Next i
End If
Next t
FIFOS =Application.WorksheetFunction.Transpose(TotalOM)
End Function
五、小结
关于用Excel计算先进先出存货发出成本ExcelHome论坛已有人专门发帖讨论过，其大体计算思路是用EXCEL公式实现数据库记录指针函数的功能，但其中所用的公式复杂难懂，计算量大，有时竟然会造成电脑死机，如图5。
图5先进先出法—用Excel也能做到吗
本文提供的计算方法原理清晰，实现简单且灵活性较强，扩展后的自定义函数不仅可以计算单一商品的先进先出存货发出成本，还适用于多产品成本的计算，非常适合推广使用。
赞 (0)