在满足 IF 语句条件但不合并的情况下将唯一行转置为列

在满足 IF 语句条件但不合并的情况下将唯一行转置为列

当前状态:

 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?) 

编辑表添加:

更新表

答案1

您可以使用以下公式:

=TRANSPOSE(FILTER($E$2:$E$13,($A$2:$A$13<>$B$2:$B$13)*$A$2:$A$13=$A2))

即,过滤 Qty 列中 FG <>Component 和 FG=(当前行的 FG) 的行,然后转置结果数组。

在此处输入图片描述

编辑:

您可以在 filter 的第一个参数中使用大小相似的数组进行数学运算。因此,在您的示例中,您需要将 F 乘以 D,因此公式变为:

=TRANSPOSE(FILTER($F$21:$F$24*$D$21:$D$24,($A$21:$A$24<>$B$21:$B$24)*$A$21:$A$24=$A21))

相关内容