94 全自动《计划排程物料需求运算报表》-02

需要找出排程编码对应BOM的最大项

怎么理解呢？因为需要计算排程中的物料编码的物料需求，要知道物料需求就需要展开，也就是包含多少个物料，理解为生产A，需要多少个零件?用函数来实现的思路就是筛选出指定母件对应的子件数，再嵌套统计函数统计：

录入函数：=COUNTA(FILTER('1.BOM'!D220000,'1.BOM'!B2:B20000='2.需求 '!D2))，下拉填充，可以得到最大展开项数。

这个公式就是传统的公式，需要变成动态数组才能实现一键填充。

为了实现全自动，需要把这个公式变更一下。变更成动态数组就需要用到比较高级的函数了：REDUCE函数和LAMBDA函数的组合。

这个函数的难点是，如何理解LAMBDA函数定义的参数 X 和 Y，能够理解X和Y就基本理解了这个函数组合。

录入函数，一键动填充得到全自动公式：

=DROP(REDUCE("",FILTER(D22000,D22000<>""),LAMBDA(X,Y,VSTACK(X,COUNTA(FILTER('1.BOM'!D220000,'1.BOM'!B2:B20000=Y))))),1)

通过下图看出，这个是动态数组，一键填充，只数据有更新（范围内），都可以自动填充。

也就是写好后，算法没有问题，可以一劳永逸了。范围排程这里预留了2000，BOM表中预留了20000，当然可以增大，只需要更新范围就可以了；

其实还有另外一个思路，就是在BOM表中想办法。因为BOM表中是每个都有展开项，有些BOM的话是自动带出零件数，如下图，有些是没有的，如果有的话，就不需要用比较复杂的公式了，只需要更改公式：

=MAXIFS('1.BOM'!C2:C20000,'1.BOM'!A2:A20000,FILTER(D22000,D22000<>""))

但是有些时候，BOM中没有这个零件的顺序号，应该如何自动判断呢？如果是填充的公式就是相对简单，有两种方法

录入函数：=COUNTIFS($A$2:A2,A2)下拉填充，这个方法数据量超过1万行，会非常卡。

录入函数：=IF(A1<>A2,1,L1+1),这个函数就快多了，属于高效的函数。

上面的公式需要转成动态数组才能实现真正意义上的一键填充。所以需要继续更改为动态数组函数。录入函数：

=TAKE(SCAN(0,A2:A20000,LAMBDA(X,Y,(Y=OFFSET(Y,-1,))*X+1)),COUNTA(A2:A20000))，就实现了一键填充，注意这里的范围是A2:A20000,超过这个数据只需要变更范围即可；

方法2：REDUCE 的方法，刚刚用的是SCAN的方法，因为这两个函数非常相似，一个是运算过程，一个是运算结果。所以如果更正为REDUCE的方法，函数更改为：

=DROP(REDUCE("",DROP(UNIQUE(A2:A20000),-1),LAMBDA(X,Y,VSTACK(X,SEQUENCE(COUNTA(FILTER(A2:A20000,A2:A20000=Y)))))),1)

从结构上来说，SCAN函数比较短，但是难以理解，REDUCE虽然看起来长，但是理解起来就简单了，就是筛选出母件编码，再统计每个母件编码对应的零件数，再用生成序号的函数生成连续数据，再合并起来。

今天分享的知识点，也是Excel 365版本的重大更新，实现了不用VBA的简易化编程。解决了一些以前需要VBA实现的功能，用这个版本的编程函数即可快速搞定。

经典组合：SCAN、REDUCE、LAMBDA 铁三角函数；

未完待续……