当前状态:
A B C D E
FG Component FieldKey Cost$ Qty
7120 7120 1220 $4.71 9,783
7120 9907062 1220 $7.80 4,061
7120 1007710 1220 $5.74 4,744
7120 6670299 1220 $5.18 10,396
5492 5492 1220 $2.82 12,172
5492 9729374 1220 $5.61 11,762
5492 9042677 1220 $5.31 5,697
9030 9030 1297 $7.69 6,659
9030 6367234 1297 $5.94 14,463
9030 3858464 1297 $4.61 5,853
9030 8613075 1297 $6.60 3,435
9030 9473775 1297 $2.55 4,371
目标状态:
logic logic logic logic logic logic
where x=0 & qty j = qtyx+1,...,...
FG Component Field Key Cost $ Qty qty1 qty2 qty3 qty4 qtyj qtyj+1
7120 7120 1220 $4.71 9,783 4,061 4,744 10,396 x x x
7120 9907062 1220 $7.80 4,061 4,061 4,744 10,396 x x x
7120 1007710 1220 $5.74 4,744 4,061 4,744 10,396 x x x
7120 6670299 1220 $5.18 10,396 4,061 4,744 10,396 x x x
5492 5492 1220 $2.82 12,172 11,762 5,697 x x x x
5492 9729374 1220 $5.61 11,762 11,762 5,697 x x x x
5492 9042677 1220 $5.31 5,697 11,762 5,697 x x x x
9030 9030 1297 $7.69 6,659 14,463 5,853 3,435 4,371 x x
9030 6367234 1297 $5.94 14,463 14,463 5,853 3,435 4,371 x x
9030 3858464 1297 $4.61 5,853 14,463 5,853 3,435 4,371 x x
9030 8613075 1297 $6.60 3,435 14,463 5,853 3,435 4,371 x x
9030 9473775 1297 $2.55 4,371 14,463 5,853 3,435 4,371 x x
我使用随机值制作了这些表格来说明。目标是构建逻辑来自动化一个非常缓慢且手动的报告,这会耗费数十个小时。我也在考虑将其移植到 powerbi 解决方案中。但是我被困在第 3 部分;下面我将进行说明。
有几件事同时发生:
第一的迭代范围:
迭代范围是 A 列 [FG] 中重复值的每个实例,即 FG 7120 在其范围内有 4 个实例,FG 5492 在其范围内有 3 个实例;每个范围在整个工作表中都会有可变数量的实例,具体取决于给定 FG 有多少个组件。
第二:
在每个迭代范围内查找不是 FG 本身的项目。我已经定义了这个逻辑。本质上是查看每个迭代范围内 A 列 + B 列的串联,并在 FG <> 组件时返回 TRUE。表格已经显示当 A 列和 B 列中出现相同的 FG 编号时,FG 就是其本身,即(第 1 行 A 列和 B 列中的 7120 - 7120)。
第三
在每个非 FG 行的迭代范围内,即 [A2 <> B2],循环并查看每个唯一的 B 列组件行,直到范围的末尾,即 [A2&B2 <> A3B3],其中范围将在 [A4 <> A5 --> 7120 <> 5492] 时结束。
到目前为止我已经完成了这部分,但是当我试图弄清楚如何将各个组件编号(E 列.数量)转置到定义该 FG 分组的迭代范围的开始的相应 FG 行级别时,我的思绪已经脱离了身体。
换句话说,查看 FG 7120 分组,然后查看所有组件编号(FG 编号除外),对于该 FG 分组范围 x、x+1、x+2 中存在的组件行数,取每个唯一行的相应数量,并将该数量转置到从该 FG 分组的 FG 标题行级别开始的新列,并重复填充相同的数量,直到范围结束。我的目标状态表可能会更好地直观地解释这一点。
我发现了什么
寻找解决方案我发现索引匹配或者 xlookup 可以帮我实现部分功能,但我不知道如何强制输出到每个唯一的 FG 行级别,如目标状态表中所示。
每个迭代范围都会有可变列,对于某些范围,当这些额外的列无法用数量填充时,我需要返回空白(在我的表中显示为 x),因为给定范围内没有足够的唯一行。这是我的障碍,我需要帮助来弄清楚这部分。我可以使用 IF THEN 条件进行索引匹配/xlookup 吗?这个解决方案是否必须是带有某种 Do While 循环的 VBA,还是仍然可以使用公式来完成?
下一个复杂层是自动将成本 $ 与每行的转置数量相乘,并在每数量旁边创建一个新列。我相信这是一个 VBA 解决方案,但一旦弄清楚了其他部分,我就会进行探索。我打算制作一个 MULTIPLY IF 语句,如果存在空白则不进行乘法运算。但我无法弄清楚上面的内容,所以这部分是没有意义的。
qty1 Impact qty2 Impact
4,061 $19,126.36 4,744 $22,343.13
4,061 $19,126.36 4,744 $22,343.13
4,061 $19,126.36 4,744 $22,343.13
4,061 $19,126.36 4,744 $22,343.13
11,762 $55,396.28 5,697 $26,831.54
11,762 $55,396.28 5,697 $26,831.54
11,762 $55,396.28 5,697 $26,831.54
14,463 $68,117.36 5,853 $27,566.26
14,463 $68,117.36 5,853 $27,566.26
14,463 $68,117.36 5,853 $27,566.26
14,463 $68,117.36 5,853 $27,566.26
14,463 $68,117.36 5,853 $27,566.26
当前逻辑(由于我仍在研究,所以比较零散)
IF(A2=A3,
[BUT condition] AND(A2 <> B2,
AND(A2&B2 <> A3B3,
[THEN] (transpose & offset?)
编辑表添加: