可否用公式解决库龄计算的问题

mrfox

在本论坛搜索了库龄，看了一遍，没有对先进先出法的库龄分析的解决方案，在网上搜索到的一篇文章对需求讲得比较清楚http://my.oschina.net/yonge/blog/84，引用如下：
先进先出的库龄分析算法

先统计当前机构、仓库下，产品对应的库存数量。然后按照产品的入库历史去追溯，直到当前数量减去入库数量总数小于号这等于0，记录下来这个入库时间。然后把这些入库按照库龄分析要求的时间段进行归类即可。
举例说明一下：
假设产品A的库存数量是50个。假设当前时间是6月1号。
假设产品A的入库历史如下：
序号    产品  入库时间   入库数量
1       A       6月1日      20
2       A       5月3日      10
3       A       5月1日      15
4       A       3月20日     30
5       A       2月3日      25
    ......
按照我们设定的数据50，
减去序号1的20个，剩余30个，大于0，继续；
减去序号2的10个，剩余20个，大于0，继续；
减去序号3的15个，剩余5个，大于0，继续；
减去序号4的30个，结果为负数，OK，计算到这里截至。根据这个时间，我们知道，最早的入库时间是4月20日。
然后按照序号1、2、3、4去归类，序号1、2归类到1个月内，序号3归类到1-2个月，序号4归类到2-3个月；
库龄分析的结果是：
1个月内     20
1-2个月     25
2-3个月     5
其余为0

我自己的需求是这样的，附件里的表的出入记录表是已有的表

想要的结果类似这样

由于库存周转快，本想按天计算库龄，后来感觉过细不利于通用，改为用周为单位，请高手给写个公式吧，不胜感谢！

[ 本帖最后由 mrfox 于 2011-4-4 13:39 编辑 ]

mourintong

感觉用函数很复杂，期待VBA高手出现

mrfox

如果能通过多个辅助列加公式解决也行，不怕麻烦，就怕做不出来

excelhomesnake

请问一下，你的那个“当前日期”是每一个产品都有一个，还是所指的其实是现在系统的日期？

excelhomesnake

原帖由 mrfox 于 2011-4-4 13:40 发表
如果能通过多个辅助列加公式解决也行，不怕麻烦，就怕做不出来

大工程，请参考附件，一列辅助列，两个定义名称，不知符合要求不……

mrfox

当前日期就是统计当天的日期，是我加上做为示例用的，实际上源表是没有的

mrfox

非常感谢！！光看这公式就得学一阵儿才能懂，我看明白了再来请教，不过目前结果表目前还不对，是这样的

似乎还没成功，是不是我用2010的原因？这个XLS打开后出现了两个表格文件，兼容1和2，还是头一次见

excelhomesnake

原帖由 mrfox 于 2011-4-4 23:10 发表
非常感谢！！光看这公式就得学一阵儿才能懂，我看明白了再来请教，不过目前结果表目前还不对，是这样的
901071
似乎还没成功，是不是我用2010的原因？这个XLS打开后出现了两个表格文件，兼容1和2，还是头一次见
9 ...

吓？我这里是可以的，但我是用2003，其他的版本我就不清楚了
C:\Documents and Settings\Administrator\桌面\1.png
C:\Documents and Settings\Administrator\桌面\2.png

[ 本帖最后由 excelhomesnake 于 2011-4-4 23:21 编辑 ]

mrfox

谢谢，我去找个2003装上试试，辛苦了

gouweicao78

楼主，根据结存为29推测，结存数量是：入库+退货-出库
那么为何不用负数表示出库，正数表示入库和退货，这样SUMIF就可以计算结存了。