先进先出法—用Excel也能做到吗？—类似数据库记录指针的Excel函数E.xlsVIP

解法E

解法D

解法C

解法B

解法A

解析

问题

DOWNROW

DOWNROW

INCM

0

INCMOFF

0

INCMOFFA

0

UPROW

梧桐

gdliyy

日期

入库

出库

结余

数量

金额

单价

chenjun

apolloh

解法C

解法A

解法B

解法D

解法E

公式

结果

50*1.2+1*1.3

10*1.3

1*1.3+11*1.4

20*1.4

问题

B1数量

=累计出库-最后一次完整出库的累计数量

上次累计出库金额

本次出库金额

B1单价

=最后一批出库单价

截至最后一次完整被出库的批次的金额累计，即全部出库A的金额

本次累计出库金额A+B1

=最后一批部分出库的金额

为B1，部分出库的金额

小结：

INCMOFF

B2部分结存批次

C全部结存批次

本次留存金额B2+C

其中SUM(E$6:E17)INCM和SUM(E$6:E17)INCMOFF段（深蓝色区域）实现的是类似于数据库游标（指针）的功能。

其中SUM(E$6:E17)INCMOFF段（深蓝色区域）实现的是类似于数据库游标（指针）的功能。

实际上是通过未在上批次完全出库的剩余数量去乘下次入库的单价，来求得B1部分的金额。

=LOOKUP(A35,B26:B37,G26:G37)

A全部出库批次累计

B1部分出库批次

DOWNROW

INCM

INCMOFFA

UPROW

先进先出核算法，出库价值先计出库时库存中最先入库批次的价值，不足部分再计下批次入库的货物价值，以此类推。

出库金额演算

解析

问题

解析

解法A

解法B

解法C

解法D

解法E

此次主题重点讨论如何在数据表中指定记录的位置，实现类似数据库记录指针(游标)的功能。

A

完全出库

B

部分出库

C

完全留存

两个解题方向

方向一

求得本次累计出库金额后扣除上次累计出库即是本次出库金额

方向二

三种记录定位技术

方法一

方法二

方法三

Match定位小于累计出库的最大累计入库，返回最后一次完整出库的位置。

名称

用途

公式

本次入库累计数量

上次入库累计数量

上次入库累计金额

最后一个完整出库行次

最早一次完整留存行次

其中“!”表示当前表，是名称中对工作表相对引用的表示方法。

关于名称中用到的SUMIF+OFFSET，测试题2中有讨论，这里不再重复。

已知入库的时间、数量和单价及出库的时间和数量

求黄色区域的计算公式

日期

入库

出库

结余

数量

金额

单价

=SUMPRODUCT((SUM(E$6:E17)INCMOFF)*(SUM(E$6:E17)-INCMOFF)*(C$6:C17-C$5:C16))-SUM(G$5:G16)

本次累计出库

数量

每次的

累计入库

TRUE为

有出库

的批次

累计数量中未在上一次入库中扣除的出库部分

下批次

入库单价

D

本批次

入库单价

E

本批次中

出库的金

额明细D和

E错行相减

B1金额

=SUMPRODUCT((((INCM-SUM(E$6:E17))*(SUM(E$6:E17)INCM))-(SUM(E$6:E17)INCMOFF)*(INCMOFF-SUM(E$6:E17)))*C$6:C17)

本次累计出货

截至本次每次累计入库数量

True为本次为止留存的批次

本次留存数量累计

截至上次每次累计入库数量

True上次为止留存的批次

上次留存数量累计

本次留存批次数量明细

每批次入库单价

本次留存批次金额明细

=LOOKUP(SUM($E$6:$E17),INCMOFF,INCMOFFA+(SUM($E$6:$E17)-INCMOFF)*$C$6:$C17)-SUM($G$5:$G16)

本次累计出库数量

上次累计入库数量

上次累计出库金额

出库累计数量中未在上一次入库中扣除的部分

每次进货单价

上次未扣除累计出库数量乘本次的单价

上次完全出库和本次部分出库相加

使用LOOKUP直接返回符合条件记录所在的位置以此来实现类似于数据库游标（指针）的功能。

=SUMPRODUCT(OFFSET(B$6,,,DOWNROW),OFFSET(B$6,,1,DOWNROW))+OFFSET(C$6,DOWNROW-1,0)*(SUM(E$6:E17)-SUM(OFFSET(B$6,,,DOWNROW)))-SUM($G$5:$G16)

上次入库数量累计

Match定位

所有出库批次的数量

所有出库批次的单价

所有出库批次的金额

最后一次出库单价

本次累计的出库数量

最后一次出库批次留存数量

最后一次出库批次留存金额

所有有出库的批次A+B

B2部分出库批次的留存部分

此解法先求得A+B所有出库批次的总金额再扣除最后一批次留存的金额来达到A+B-B2=A+B1的目的。

其中MATCH(SUM($E$6:$E1