LAMBDA函数递归：根据BOM清单计算产成品成本

shaowu459

BOM清单（Bill of Material, BOM）也即物料清单，指产品所需要的零部件的清单及组成结构，物料清单又叫做产品结构树或产品结构表，是以数据形式来描述产品结构的技术文件，通俗点讲，就是配方或要素表，包括了原料、产品、半成品、消耗品、协作件等许多与生产息息相关的物料。

BOM实际上体现的是如下图所示的这种树形结构。例如，生成1个单位A需要5个单位B，3个单位C，2个单位D，1个单位E；BDE都是直接买来的最终原材料，而C是要由其他原材料生产而成，比如说生产一个单位C需要FGH各2个单位，FG是最终原材料，H要由若干单位的IJKL生产而成。


在Excel表中，BOM通常的存储方式为二维表，但样式可能各有不同。下图是一种BOM存储方式，同一行中左侧是母件编码（含版本号），中间是生产这个母件所需的子件（含版本号），最右侧是生产若干单位的母件需要若干单位的子件数据。若子件没有版本号，说明该产品是最终原材料，有子版本号，说明该子件还是要由其他子件生产而成。二楼会对上述内容进行详细说明。


现在知道最终原材料的采购价格，如下图：


要求根据最终原材料的采购加个和BOM档案表，求出生产若干单位母件的成本：


在根据此类BOM计算成本时，递归是常用的方式之一（根据基础资料拆分成BOM也可以用递归，另帖说明）。本帖主要介绍如何用LAMBDA函数通过自定义名称递归的方法根据BOM计算产成品成本。

shaowu459

下面对BOM档案进行详细说明：
1）A列和B列数据为一组，分别是母件的编码和版本号（为方便描述用编码+版本号表示）。红框中A1是母件编码，01是它的版本号。C列和D列数据为一组，分别是子件的编码和版本号。
紫色框中的3行数据表明，生产A1+01这个母件，需要用a01、a02+01、a03这3种子件。



2）下图中紫色框的a01没有子件版本号，说明a01是最终原材料。红色框中的b02有子件版本号01，因此b02+01不是最终的原材料，所以在A列和B列还能找到b02+01作为母件的记录，生产b02+01需要bb0002这个子件，因为bb0002没有子件版本号，所以bb0002就是最终原材料。



3）最后两列是数量关系，红框里的2和1表明：生产1个单位的A1+01需要2个单位的a03。如果基本用量（E列）是4，基础数量（F列）是3，则说明生产3个单位的母件要用4个单位的子件，所以生产1个单位的母件需要用4/3个母件。


4）如果没有子件版本号，说明该子件就是最终原材料，因此可以从采购价格表中查找单位价格。例如左侧红框中的a01是最终原材料，a01的单价是5元，因此生产1个单位的A1+01需要的a01成本是：a01单价5元*1.5/1=5*1.5/1=7.5元。


5）下图左侧上方红框表明，生产1单位的B1+01需要4/1=4单位的b02+01。左侧下方红框表明，生产1单位的b02+01需要1/4=0.25个单位的bb0002。因为bb0002是最终原材料，且单价是8元，因此生产1单位的B1+01耗用的b02+01的成本是：4*0.25*8=8元

shaowu459

基于BOM清单和原材料的采购价格表，我们就可以计算出生产若干单位的某产品的成本了。


下面以生产10个单位A1+02产品为例，先手动展示一下成本是如何计算出来的：


第一步，从BOM清单中筛选出AB列内容为A1+02的行：


第二步，因为子件中AA001+01仍然由子件构成，因此，继续筛选AB列为AA001+01的行（下图右侧红框内）：


第三步，因为aa0001也已经是最终原材料，因此可以通过单价逐层计算出生产1个单位A1+02的总成本：


第四步，生产1个单位A1+02的总成本是132元，因此生产10个的总成本就是：10*132=1320元。

shaowu459

从上面手动拆解的过程可以看出，根据BOM清单计算产品成本的过程如下：
1）从BOM清单中筛选出要求的产品作为母件的所有行，也即筛选出这个产品所有的子件所在行，假设有ABC共3行，则对ABC这3行进行逐行判断。
2）如果子件没有版本号，则查询原材料单价，然后通过折算得到耗用该子件的成本。
3）如果子件有版本号，则重复执行步骤1，筛选出该子件作为母件的所有行，针对每行进行逐行判断，看对应子件是否仍有子件，如果有，则继续重复步骤1，直到查询到最终原材料后执行步骤2。
4）执行步骤2获得最底层原材料的成本后，加总当前步骤的所有子件成本合计构成最底层原材料生产1个单位直接母件的成本。该成本返回到上一级，根据数量折算关系，得到生产1个单位更上级母件的耗用成本，然后加总当前环节所有子件成本构成直接母件的成本。该成本继续往上返回到更上一级，一直返回到到1层，最后加总第1层所有子件成本即得到生产1个单位某产品的成本。


具体举例如下：
A1+01在第1层有3个子件，其中a02+01下一层有a002+01和a005两个子件，a002+01下一层又有a0002一个子件。
a0002单价是5元，a005的单价是16元，因此第2层的子件成本合计是：a005成本+a0002成本=5*5*2+3*16=98元，也即1个单位a02+01的成本是98元。
返回至第1层，因为生产2个单位A1+01需要用3个单位a02+01，所以生产1个单位A1+01耗用的a02+01的成本=3/2*98=147元。A1+01的另外两个子件单价分别a01=5元和a03=9元，因此1个单位A1+01的成本=5*1.5+147+9*2=172.5元。

shaowu459

从上一楼的分析可以看出，根据BOM清单求成本就是要不断的去搜寻当前的子件是不是其他子件的母件，如果是的话要继续寻找，直到查找到最终原材料，然后逐层往上返回成本。这也是典型的递归模式，因此也可以使用LAMBDA函数定一个kx的名称通过递归来实现。我们可以先来分步骤看下上一楼的每个步骤大概是要如何来用公式实现。


1）筛选所有子件所在行。
我们以母件编码和编号这个数组为参数，假设筛选A3:B3单元格区域代表的母件对应的所有子件，可以用以下公式：

1. =FILTER($C$10:$F$26,$A$10:$A$26&$B$10:$B$26=CONCAT(A3:B3))

复制代码

我们可以把母件编码和版本号连接在一起，然后用FILTER函数筛选出所有子件编码的行。每行包括4个元素，前两个为子件编码和版本号（可用于继续查询），后两个为母件和子件数量关系。



2）逐行处理第1步筛选出的所有行。
因为是逐行处理，并且每行只返回一个成本数值，因此可以用BYROW函数来解决。

1. =BYROW(第1步筛选出来的数组,LAMBDA(y,INDEX(y,3)/INDEX(y,4)*IF(INDEX(y,2)=0,VLOOKUP(@y,$H$3:$I$12,2,),kx(TAKE(y,,2)))))

复制代码

其中INDEX(y,3)/INDEX(y,4)部分计算得到生产1个单位上层母件需要使用几个单位当前子件，这个数量要乘以当前子件的成本，而这个成本要用IF函数生成。
IF(INDEX(y,2)=0,VLOOKUP(@y,$H$3:$I$12,2,),kx(TAKE(y,,2)))部分判断，如果这一行数据的第2个值是0（也就是没有子件版本号），则用VLOOKUP函数获得当前子件的单价。如果这一行数据的第2个值不是0（也就是有子版本号），则要重复调用kx这个自定义函数，将当前行的前两个元素（TAKE函数获取前2列）作为参数继续查找。


3）BYROW函数处理完每一行后，会得到每一行子件的成本，这若干个成本需要用SUM函数加总起来，然后往上一级返回。

完整的kx公式如下：

1. =LAMBDA(x,SUM(BYROW(FILTER($C$10:$F$26,$A$10:$A$26&$B$10:$B$26=CONCAT(x)),LAMBDA(y,INDEX(y,3)/INDEX(y,4)*IF(INDEX(y,2)=0,VLOOKUP(@y,$H$3:$I$12,2,),kx(TAKE(y,,2)))))))

复制代码

shaowu459

定义完成kx名称后，在D3单元格输入以下公式，然后向下填充即可得到AB列产品的成本。

1. =C3*kx(A3:B3)

复制代码

最后，再将LAMBDA自定义公式部分拆分说明一下：
=LAMBDA(x,                                                                                                                 参数为编码+版本号合并的文本

                  SUM(                                                                                                          对当前所有子件成本求和
                         BYROW(                                                                                              逐行处理数组
                                     FILTER($C$10:$F$26,$A$10:$A$26&$B$10:$B$26=CONCAT(x)),   筛选当前母件对应的所有子件信息
                                     LAMBDA(y,
                                                  INDEX(y,3)/INDEX(y,4)*IF(                                           子件母件数量折算，乘以子件单位成本
                                                                                        INDEX(y,2)=0,                       如果子件版本号为0
                                                                                        VLOOKUP(@y,$H$3:$I$12,2,), 直接查找最终原材料成本
                                                                                        kx(TAKE(y,,2)))))))                 如果还有子件，则继续调用kx重复查找

柳叶飞

教程非常好，但是有一个问题，由于execl版本问题，希望注明所有版本。

shaowu459

柳叶飞 发表于 2022-11-26 08:25
教程非常好，但是有一个问题，由于execl版本问题，希望注明所有版本。

嗯，只适用于有Lambda函数的版本。目前Lambda只在365里有，我发的其他365函数的帖子标题中一般都加上了365函数的说明。

alan57

先占个坑，再慢慢学习研究

shaowu459

实用示例：根据BOM清单计算生产若干数量的各种产品需要采购原材料的数量：

定义fx：

1. =LAMBDA(z,DROP(REDUCE(0,SEQUENCE(ROWS(z)),LAMBDA(x,y,LET(s,INDEX(z,y,),t,FILTER(BOM示例!$E$2:$G$32,BOM示例!$B$2:$B$32=@s,0),VSTACK(x,IF(COUNTA(t)=1,s,fx(IF({1,1,0},t,TAKE(t,,-1)*TAKE(s,-1,-1)))))))),1))

复制代码

在单元格中输入公式：

1. =LET(s,fx(B3:D6),MAP(B14:B32,LAMBDA(x,SUM(IF(TAKE(s,,1)=x,s)))))

复制代码