关于用Excel实现先进先出计算的新思考

cf7315752

关于用Excel实现先进先出计算的新思考

                      ——基于ExcelVBA的先进先出存货发出模型设计

一、引言

先进先出法是确定发出存货成本时采用的一种方法，但采用该方法计算时，对同一存货用两个或两个以上的成本计价，计算量大，容易出错，特别是对于存货进出量大且频繁的企业更是如此。

本文首先建立单一商品先进先出成本计算模型，然后给出用EXCEL公式实现计算的方法，最后编写自定义求解函数。借助该函数，在已知各批入库数量、单价及出库数量的条件下，可快速实现存货发出成本的计算。

二、单一商品先进先出成本计算模型

1. 先进先出法计算存货发出成本模拟图

在已知入库数量、单价和出库数量的前提下，需先对出库数量进行分解，用分解后的数量分别乘以对应单价并加和，即可得到存货发出成本，先进先出法计算存货发出成本的过程，可用图1表示。

图1 先进先出法计算存货发出成本模拟图

2. 建立库存发出队列计算模型

根据以上思路，使用先进先出法计算存货发出成本的关键是对发出数量的分解，即计算图1中的库存发出队列，建立库存发出队列的模型如下：

图2 库存发出队列计算模型

设a(i)、b(i)、c(i) (i≥1)分别表示第i期入库数量、出库数量、库存数量，X(i,j) (i≥0，j≥0)表示第j期发出的b(i)件存货中来自于第i期入库存货a(i)的数量，对a(i)、b(i)、c(i)、X(i,j)做如下假定：

(1)对任意i均有a(i)≥0，b(i)≥0，c(i)≥0；

(2)对任意i、j，若i*j=0，则X(i,j)=0。

3.数学归纳法求解X(i,j)

三、单一商品先进先出成本计算EXCEL实现

1.公式法

用Excel实现先进先出存货发出成本计算所用到的函数仅有Sum函数、Min函数和Sumproduct函数。

图3 先进先出法EXCEL模型

A列：入库时间，B列：入库数量，C列：入库单价，D列：入库金额，E列：出库数量，F列：出库单价，G列：出库金额，H列：库存数量，I列：库存单价，J列：库存金额

根据图1有：

K4：K6值均设置为0，L3：N3值均设置为0；

G4：G6依次引用区域L7：N7 ，L2：N2依次引用区域E4：E6；

L4公式为“=MIN(L$2-SUM(L$3:L3),$B4-SUM($K4:K4))”，向下拖动L4至L6，再向右拖动L6至N6，即可成功设置L4：N6的公式；

L7公式为“=SUMPRODUCT($C4:$C6,L4:L6)”，向右拖动L7至N7，即可成功设置M7：N7的公式。

使用该方法计算过程中用到的关键公式只有两个，这两个公式中所用到的函数都是非常常见且基础的函数，通过精准地设置绝对引用和相对引用，这两个公式又可以实现快速复制填充，应用到其他单元格，因此，用该方法计算先进先出存货发出成本具有一定的可操作性。

2.VBA自定义函数法

所编写自定义函数名称为FIFO(First In First Out)，函数参数为3个对象变量InQty、InPrice和OutQty，其中，InQty为入库数量，InPrice为入库单价，OutQty为出库数量；函数返回值为一组数据，即出库金额序列。该函数设计要点是，将用户选定区域的入库数量、入库单价和出库数量读入三个数组中，然后再设置辅助数组模拟图1中“辅助计算表”进行计算，并将计算的结果返回给函数输出，具体代码如下：

Public Function FIFO(InQty As Range,InPrice As Range, OutQty As Range) As Variant

Dim aInQty()

Dim aInPrice()

Dim aOutQty()

Dim count As Integer

Dim aOM()

Dim aOutQtyS1()

Dim aOutQtyS2()

Dim aOutQtyS()

count = InQty.count

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)

For i = 1 To count

aInQty(i) = InQty(i)

aInPrice(i) = InPrice(i)

aOutQty(i) = OutQty(i)

aOutQtyS2(0, i) = 0

aOutQtyS(0, i) = 0

aOM(i) = 0

aOutQtyS1(0, i) = 0

aOutQtyS1(i, 0) = 0

Next

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

For i = 1 To count

aOM(j) = aOM(j) + aInPrice(i) * aOutQtyS(i,j)

Next i

Next j

FIFO =Application.WorksheetFunction.Transpose(aOM)

End Function

选中“G4：G6”单元格区域，删除其中的数据，在编辑栏中输入“=FIFO(B4:B6,C4:C6,E4:E6)”，同时按下Ctrl键、Shift键和Enter键，即可得到计算结果。

本文提供的计算方法原理清晰，实现简单且灵活性较强，扩展后的自定义函数不仅可以计算单一商品的先进先出存货发出成本，还适用于多产品成本的计算，非常适合推广使用。

如有疑问请联系QQ：529039284

附件1 先进先出法公式实现模板

附件1 先进先出示例文件.rar (37.43 KB, 下载次数: 1904)

2014-11-5 21:51 上传

点击文件名下载附件

---

请参见论文《基于Excel VBA的先进先出存货发出模型设计》,《财会月刊》,2014年第11期

cf7315752

四、扩展应用——多商品先进先出发出成本的计算

在笔者论文发表不久，在EXCELHOME论坛上发现了多商品先进先出发出成本的计算问题，具体请参加帖子:

http://club.excelhome.net/forum.php?mod=viewthread&tid=888985

问题描述：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也能做到吗

本文提供的计算方法原理清晰，实现简单且灵活性较强，扩展后的自定义函数不仅可以计算单一商品的先进先出存货发出成本，还适用于多产品成本的计算，非常适合推广使用。

szjiaxb

好东西，学习

lexx

牛逼啊,我正准备用excel记录股票交易,考虑用先进先出计算成本.

licy0208

计算速度有点慢

sz1437

cf7315752 发表于 2014-11-5 21:55
四、扩展应用——多商品先进先出发出成本的计算在笔者论文发表不久，在EXCELHOME论坛上发现了多商品先进先 ...

保留研究研究

cbxxgwf

真是高人，把代码打印下来慢慢看才行

yuran

看不懂代码{:soso_e109:}

liuyizhong

楼主真是牛人一个，下载学习了。

yamasedekong

牛逼。看不懂