先进先出分配法

题目:


先进先出分配法是财务与企业管理中经常遇到的老问题,根据期初库存分配出库数。
在工作表函数、VBA、DAX中都已经有了对应的解法,今天来看看在Power Query又是怎样的思路?

解法1:

let
    源 = Excel.CurrentWorkbook(){[Name="先进先出"]}[Content],
    结果 = Table.AddColumn(源, "分配", each [a=Table.SelectRows(源,(x)=>x[产品]=[产品] and x[入库年月]<=[入库年月]),b=a[库存]{0}-List.Sum(a[入库数]),c=if b>0 then [入库数] else if b+[入库数] >0 then b+[入库数] else 0][c])
in
    结果

虽然是一步出结果,但整个过程分为三步,也就是中间构建的record的abc。
过程a是筛选每一行中日期<=当前行的所有记录,返回一张每行都不一样的筛选表:
过程b是用筛选表a中的[库存]列减去累计入库数,比如上图中就是用200-(50+60+100)。
过程c再判断如果b>0就返回当前行[入库数]的值,否则就加上[入库数],也就是剩余的库存数,最后再对负数进行归0处理。

实际上就是累计求和题型的变体,之前也介绍过很多次。
同样之前也说过,这种方法因为是逐行扫描筛选,算法的复杂度是行数的平方,所以如果数据量一大效率就会比较低,再想想有没有别的解法?

解法2:

let
    源 = Excel.CurrentWorkbook(){[Name="先进先出"]}[Content],
    分组 = Table.Group(源, {"产品"}, {"a", each Table.AddColumn(Table.AddIndexColumn(_,"索引",1,1),"分配",(x)=>[a=Table.FirstN(_,x[索引]),b=x[库存]-List.Sum(a[入库数]),c=if b>0 then x[入库数] else if b+x[入库数] >0 then b+x[入库数] else 0][c])}),
    展开 = Table.ExpandTableColumn(分组, "a", List.Skip(Table.ColumnNames(源))&{"分配"})
in
    展开

此方法为第一种方法的改进,同样分为三个过程,和前面的方法只在第一步的累计求和有区别。
此处使用分组加索引,然后Table.FirstN,每一行获得筛选表中前N行的记录,复杂度大大减少,效率有显著提升。
本题中[产品]列中的A和B本身就分开的,但如果是AABBAABB这种交替的排序,分组后就会打乱原来的顺序,如果要还原原来的顺序,只要在源数据中再加一个索引列,最后按索引列排序即可还原。
 

解法3:

let
    源 = Excel.CurrentWorkbook(){[Name="先进先出"]}[Content],
    分组 = Table.Group(源,"产品",{"a",each _}),
    fx = (l,t)=>List.Accumulate(l,
		[a={},b=0] ,
		(s,c)=>if t<c+s[b]
			then [a=s[a]&{List.Max({t-s[b],0})},b=s[b]+c]
			else [a=s[a]&{c},b=s[b]+c]
	  )[a],
    结果 = Table.Combine(List.Transform(分组[a],
		each Table.FromColumns(
			Table.ToColumns(_)&{fx([入库数],[库存]{0})},
			Table.ColumnNames(_)&{"分配"})
	  ))
in 
    结果

基于第二种方法,对中间的算法使用List.Accumulate进一步优化。
实测2W行数据秒出结果,效率最高,但如果数据过大可能会因为堆栈溢出引发报错。
这种方法较难,在《List.Accumulate》中有讲解。
 

总结:

如果数据量不大,以上三种方法均可。
如果数据量很大,用第三种方法,如果挂了,再换第二种方法。

附件

8 Replies to “先进先出分配法”

  1. 老师好高深啊,看不懂最后的那种方法里面的function,自己需要分解步骤再仔细研究,谢谢老师的分享

  2. 施阳你好,非常感谢你创建的网站和QQ群,受益很多。
    对于FIFO,请问如果是这种情况,该怎么处理呢?
    比如:A产品每天都有进出,我需要知道每一条卖出的数量是来自于哪一条采购的呢?
    和您的例子有一点不一样的地方是:某一天出去的量可能来自于不同的采购,或者一条采购会被用于很多条卖出。
    请问您会怎么处理?
    非常感谢!

  3. 你好博主,你看第一个表和第二个表。结果是错的。

    A库存结余200个,怎么可能1月份还分配的到。

  4. 收益匪浅,非常感谢老师。
    List.Accumulate确实高效,一举解决了我工作上的一些Query运行速度巨慢的问题(因为数据量很大)。
    譬如参考解法三,我写了“累计求和”的一个通用函数,在此分享,以共同提高。

    * 应用背景:通过排序分组就能解决累计求和的商业分析情况(非常常见,通常大家在Excel知道如何写公式,这里借鉴这个思路)
    * 应用举例(按照SKU和Week两列对Production列求累计):= fxRunningTotal(mytable, {"SKU"}, {"SKU","Week"}, {"Production","Running Production Qty"})
    * 后续改善:如何适用多列?基本想法是完善fx以便对第四个参数循环,譬如):= fxRunningTotal(mytable, {"SKU"}, {"SKU","Week"},
    { {"Production","Running Production Qty"},
    {"Capacity","Running Capacity Qty"},
    {"Order","Running Order Qty"},
    {"Shipment","Running Shipment Qty"}
    })

    这个改善没想出来,希望老师指点。

    * 函数如下:

    fxRunningTotal = (table as table, gc as list, sc as list, rc as list) as table =>

    /*
    gc: column names as group list, e.g. {"SKU"}
    sc: column names as sort list, e.g. {"SKU","Week"}
    rc: column names as running list, e.g. {"Qty","Running Qty"},
    where rc{0} as running input, rc{1} as running output
    */

    let
    sort = List.Transform(sc, each {_, Order.Ascending}),
    group = Table.Group(table, gc, {"a",each Table.Sort(_,sort)}),
    fx = (col as list) => List.Accumulate(col, {}, (x,y)=> x & {List.Sum({List.Last(x),y})} ),
    final = Table.Combine(List.Transform(group[a],
    each Table.FromColumns(
    Table.ToColumns(_)&{fx(Table.Column(_,rc{0}))},
    Table.ColumnNames(_)&{rc{1}})
    ))
    in
    final

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注