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

是在我弄清楚后我将应用一些其他警告，如果 a 将 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屋！