做工作学函数(06)


一、将日期统一到当月末

F2:=EOMONTH(D5,0)

函数说明

1、eomonth

函数表达式=eomonth(start_date,months)

用途:计算本日期指定间隔月份的月末日期,比如参数用0就是当月末,用1是下月末。




二、生成按月采购入库报表

E40=EOMONTH(D40,-1)

公式说明:D40=E38,E40用eomonth依次减少1个月。

思路:库龄分析是基于FIFO(先进先出)假设,所以结余库存数是最近采购的,依次是近1个月、2个月、3个月采购

所以,日期要从近到远排列,先统计个月采购数量。


三、计算库龄分段库存数

D49=MIN($C49,VLOOKUP($B49,$B$41: $AA$46,3,0))

函数说明

1、min

函数表达式=min(number1,number2,...)

用途:从多个数或者一个与区域里找出最小值。

2、嵌套公式用途:

比较【一个月内采购入库数】和【结存数】,取小的那一个数。


E49=MIN($C49-SUM($D49: $D49),VLOOKUP($B49,$B$41: $AA$46,4,0))

1、嵌套公式用途:

比较【前上月(6月)采购入库数】和【结存数扣除库龄1个月的数量】,取小的那一个数。


F49=MIN($C49-SUM($D49: $E49),VLOOKUP($B49,$B$41: $AA$46,5,0))

1、嵌套公式用途:

比较【5月采购入库数】和【结存数扣除库龄1~2月的数量】,取小的那一个数。


G49=MIN($C49-SUM($D49:F49),SUMPRODUCT(($D$40: $AA$40>EOMONTH($E$38,-6))*($D$40: $AA$40<=EOMONTH($E$38,-3))*($B$41: $B$46=$B49),$D$41: $AA$46))

1、sumproduct

函数表达式=sumproduct(array1,array2,array3,...)

用途:同时满足多个条件的数据求和。

2、嵌套公式说明

2.1SUMPRODUCT(($D$40: $AA$40>EOMONTH($E$38,-6))*($D$40: $AA$40<=EOMONTH($E$38,-3))*($B$41: $B$46=$B49),$D$41: $AA$46)

日期大于前6个月,且小于等于前3个月,本案例是指2018年2至4月,且物料编码符合时,将采购入库数相加。

2.2、 min($C49-SUM($D49:F49),公式2.1)

从【结存数-3个月内库存数】和【本区间采购入库数】中取较小者。


H49、I49、J49的公式说明

将G49公式右拉,到J49,依次修改三个公式中eomonth的参数

区间

参数1

参数2

>月份1

<=月份2

4-6

-6

-3

2018年1月31日

2018年4月30日

7-9

-9

-6

2017年10月31日

2018年1月31日

9-12

-12

-9

2017年7月31日

2017年10月31日

12~24

-12

-24

2017年7月31日

2016年7月31日


K49=MAX($C49-SUM($D49:J49),0)

比较【结存数-不超过2年库存数】和【0】,取较大的数。

也就是说若果已有库龄统计的数量之和还比结存数少,那多余的就是2年以上的库存。

如果(万一)算出来是负数,那就显示0。其实前面已经设置了公式判断,2年以上的库存不会为负数的。           

附件: 您需要登录才可以下载或查看附件。没有帐号?注册  -- >