颠倒SUMPRODUCT中数组的顺序之一 [英] Reverse one of the array's order in an SUMPRODUCT

问题描述

我正在尝试计算几个值.我要计算的第一个值是该数学公式:

I am in the process of trying to calculate a couple of values The first value I am trying to calculate is this math formula:

RL =(P ₅ * a ₁ + P ₄ * a ₂ + P ₃ * a ₃ + P ₂ * a ₄ + P ₁ * a ₅ )/L

RL = (P₅*a₁ + P₄*a₂ + P₃*a₃ + P₂*a₄ + P₁*a₅)/L

第二个公式中包含RL，但存在相同的问题.

The Second formula has RL in it but suffers from the same problem.

M = RL * x-P ₅ (xa ₁ )-P ₄ ( xa ₂ )-P ₃ (xa ₃ )-P ₂ (xa ₄ )-P ₁ *(xa ₅ )

M = RL * x - P₅(x-a₁) - P₄(x-a₂) - P₃(x-a₃) - P₂(x-a₄) - P₁*(x-a₅)

当我看到这些时，会看到一个SUMPRODUCT.

When I look at these I see a SUMPRODUCT.

我的 P ₁ ... P ₅ 值以相同的顺序布置在 $ C $ 12范围内: $ G $ 12 或命名范围 Axle_P .

My P₁...P₅ values are layed out in the same order in the range $C$12:$G$12 Or named range Axle_P.

我的 a ₁ ... a ₅ 值在数组 offset($ X $ 12，$ AL $ 13,0,1,5)，其中 X12 在 a 值的第一行上方和 a的第一列中值. AL13 保存感兴趣的行的行号.

My a₁...a₅ values are layed out in the same order in the array offset($X$12,$AL$13,0,1,5) where X12 is above the first row of a values and in the first column of a values. AL13 holds the row number for the row of interest.

L 是单元格 $ D $ 8
x 是单元格 $ M41

L is cell $D$8
x is cell $M41

所以我想使用公式

For calculating RL in say cell AA1

=SUMPRODUCT(Axle_P,offset($X$12,$AL$13,0,1,5))/$D$8
OR
=SUMPRODUCT($C$12:$G$12,offset($X$12,$AL$13,0,1,5))/$D$8

For calculating M

=$AA$1*$M41 - SUMPRODUCT($M41-offset($X$12,$AL$13,0,1,5),Axle_P)
OR
=$AA$1*$M41 - SUMPRODUCT($M41-offset($X$12,$AL$13,0,1,5),$C$12:$G$12)

问题在于这导致以下错误的顺序:

The problem is this results in the wrong sequence of:

(P ₁ * a ₁ + P ₂ * a ₂ + P ₃ * a ₃ + P ₄ * a ₄ + P ₅ * a ₅ )

(P₁*a₁ + P₂*a₂ + P₃*a₃ + P₄*a₄ + P₅*a₅)

如何反转P数组或A数组的顺序?

现在我确实计划将这些最终合并到一个单元格中.完成后，我计划将公式向下复制5行.因此，实际上有5个不同的x值，并在5个单元格中输出.

Now I do plan on combining these eventually into one cell. when I am done, I plan on copying the formula down through 5 rows. So essentially 5 different x values with output in 5 cells.

VBA不是一个有效的选择，我宁愿避免使用CSE，但如果这是唯一的方法，那我会受苦.

VBA not a valid option and I would prefer to avoid the use of CSE but if it is the only way then I will suffer.

现在，如果您想知道为什么我不将M更改为

now if you are wondering why I dont change M to

M = RL * x-(RL * L)

还有其他一些警告，如果我发现了这个问题，我会在 P _{n-i + 1} 中将其设置为0. _i 小于0或大于 L ，并且我已经计划了该部分.至少我可以在SUMPRODCUT中完成.

is that there are some other caveats I will be applying after I get this figured out that will set P_n-i+1 to 0 if a_i is less than 0 or greater than L and I have that part planned out. At least I do if this can be done in a SUMPRODCUT.

推荐答案

借助OFFSET和列偏移量数组，可以反转范围.

With help of OFFSET and an array of column offsets it is possible to reverse a range.

示例:

诀窍是要从范围的末尾开始并以偏移量{0,-1,-2,-3,-4}获取每个单元格. N函数是必需的，因为与偏移数组一起使用的OFFSET会导致{ref,ref,..., ref}，其中ref是单元格引用.此{ref,ref,..., ref}将无法正确评估. N从ref获取数字.

The trick is to get each single cell from the range beginning with the end and with offset {0,-1,-2,-3,-4}. The N function is neccessary because OFFSET used with offset arrays leads to {ref,ref,..., ref} where ref are cell references. This {ref,ref,..., ref} will not be evaluated correctly. N gets the number from the ref.

因此您的公式将是:

=SUMPRODUCT($C$12:$G$12,N(OFFSET($AB$12,$AL$13,-(COLUMN($C:$G)-COLUMN($C:$C)))))

或

=SUMPRODUCT(N(OFFSET($G$12,0,-COLUMN($C:$G)+COLUMN($C:$C))),OFFSET($X$12,$AL$13,0,1,5))

这篇关于颠倒SUMPRODUCT中数组的顺序之一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！