excel - 总 Material list 数量计算

Question

我目前正在处理工程 Material list 的 Excel 电子表格。请引用以下示例:

1 main assembly Qty - 1 Total Qty - 1;
1.1 sub assembly Qty - 2 Total Qty - Qty of 1.1 * Total Qty of 1 = 2;
1.1.1 parts involved in the sub assembly Qty - 4 Total Qty - 8;
1.1.2 part Qty - 2 Total Qty - Qty of 1.1.2 * Total Qty of 1.1 = 4;
1.1.3 part Qty - 4 Total Qty - Qty of 1.1.3 * Total Qty of 1.1 = 8;

是否有函数或公式来计算该子组件和特定组件的每个零件的总量。由于电子表格中有超过 1000 行，如果除了手动操作之外还有其他方法，它将节省大量时间并防止错误。我附上了我想要实现的目标的图像。

Answer 1

尝试:

=LET(x, LEN($A2)-LEN(SUBSTITUTE($A2,".","")),
y, LEFT($A2, FIND("!", SUBSTITUTE($A2, ".", "!", x), 1)-1),
IF(x=0, $B2,
$B2*INDEX($A$2:$C$14, MATCH(y, TEXT($A$2:$A$14, "General"),0),3)))

步骤:

1. 我们使用 LET 函数来命名变量。
2. 我们使用 LEN($A2)-LEN(SUBSTITUTE($A2,".","")) 计算小数出现的次数(表示父子关系)，并且称此计数为“x”。
3. 我们使用 LEFT($A2, FIND("!", SUBSTITUTE($A2, ".", "!", x), 1)-1) 找到最后一个 child 的父级。请注意，等式的右边部分找到了小数的最后一个实例，并且字符“!”是随机选择的；它可以是通常不会出现在字符串中的任何内容。
4. 我们最终使用了一个IF 语句。如果它是主要父级，则总数量就是数量，否则就是数量乘以父级的总数量。请注意，我们将在 INDEX\MATCH 中查找的范围包装为文本字符串并使用“3”作为固定引用，只是因为我们知道“Total QTY”列的位置并且不'预计它会移动。

编辑图像以显示对新数据的作用: