如果需要更多背景信息,请链接到我之前的相关问题:在满足 IF 语句条件但不合并的情况下将唯一行转置为列
我的表格标题从第 20 行 A 列开始,表格结束于第 36 行。我用红色突出显示了当前公式产生的问题。所有数据都是 rand() 生成的。
背景: 我希望过滤函数根据情况将所选转置数量列乘以或除以其各自的使用率。查看 FG 7120,三个转置组件数量分别为 4,061、4,744 和 10,396。
如果使用率 >= 1,我想除以该使用率并填充相同的输出;如果使用率 < 1,我想将数量乘以使用率并填充输出。这背后的合理逻辑是,假设一个组件材料需要 2 个组件才能制造 1 个 FG,而您有 800 个组件数量,那么在实际情况下,该数量最多只能生产 400 个 FG。因此,尝试通过在场景分析中将每个组件材料归零来查看可以制造多少个 FG。
在里面第一的过滤输出列,公式应该对与 FG 7120 相关的所有 4 行返回 [4061*.25] = 1015.25,但公式中的某些内容导致过滤输出的第三行(突出显示的红色单元格)输出错误。
这第二过滤器输出列应该返回 [4744/2] = 2372 并填充与 FG 7120 相关的所有行。理想状态表中的绿色单元格显示了这一点。其余列只是重复出现的问题一或二。
我做了什么:
=IF(AND(A21<>B21,D21>=1),TRANSPOSE(FILTER($F$21:$F24/$D$21:$D24,($A$21:$A24<>$B$21:$B24)*$A$21:$A24=$A21)),TRANSPOSE(FILTER($F$21:$F24*$D$21:$D24,($A$21:$A24<>$B$21:$B24)*$A$21:$A24=$A21)))
我在这里做错了什么?
19 A B C D E F
20 FG Component Plant UsageRatio Std.$ Qty
21 7120 7120 1220 1.00 4.71 9783
22 7120 9907062 1220 0.25 7.8 4061
23 7120 1007710 1220 2.00 5.74 4744
24 7120 6670299 1220 0.67 5.18 10396
25 5492 5492 1220 1.00 2.82 12172
26 5492 9729374 1220 2.02 5.61 11762
27 5492 9042677 1220 0.25 5.31 5697
28 9030 9030 1297 1.00 7.69 6659
29 9030 6367234 1297 2.23 5.94 14463
30 9030 3858464 1297 1.87 4.61 5853
31 9030 8613075 1297 2.44 6.6 3435
32 9030 9473775 1297 1.61 2.55 4371
33 7774 7774 1285 1.00 2.55 43
34 7774 4345 1285 2.23 2.55 5235
35 7774 324324 1285 0.33 2.55 3241
36 7774 5345 1285 1.76 2.55 5435
編輯-2隐藏部分列,但保留列字母
A B D F G H I
FG Component UsageRatio Qty
7120 7120 1.00 9783 1,015 2,372 6,965
7120 9907062 0.25 4061 [1015*.25] [2372*.25] [6965*.25]
7120 1007710 2.00 4744 [1015/2] [2372/2] [6965/2]
7120 6670299 0.67 10396 [1015*.67] [2372*.67] [6965*.67]
答案1
如果使用率都是相似的值(即除数或乘数),那最好,但如果必须采用其当前格式,则将 IF 作为乘数值的一部分应用,如下所示:
=TRANSPOSE(FILTER($F$21:$F$24*IF($D$21:$D$24>=1,1/($D$21:$D$24),$D$21:$D$24),($A$21:$A$24<>$B$21:$B$24)*$A$21:$A$24=$A21))
编辑:
要将使用率应用于现有转换后的每一行,只需将整个内容乘以 IF 语句即可:
=TRANSPOSE(FILTER($F$20:$F$23*IF($D$20:$D$23>=1,1/($D$20:$D$23),$D$20:$D$23),($A$20:$A$23<>$B$20:$B$23)*$A$20:$A$23=$A20))*IF($D$20:$D$23>=1,1/($D$20:$D$23),$D$20:$D$23)
请注意,您只需在溢出范围的左上角单元格中输入一次此公式。您无需将其拖下来。
编辑2:
在顶行输入此内容然后向下拖动:
=TRANSPOSE(FILTER($F$2:$F$13*IF($D$2:$D$13>=1,1/($D$2:$D$13),$D$2:$D$13),($A$2:$A$13<>$B$2:$B$13)*$A$2:$A$13=$A2))*IF($D2>=1,1/($D2),$D2)