Суммапроизведение с умножением на first * last вместо first * first

Question

Я ищу функцию или формулу в Excel 2007, которая позволяет сделать следующее:

У меня есть два ряда чисел:

1 -1  2 5 10 
1  2 -1 2  5 

Я хочу сделать что-то похожее на то, что делает sumproduct, то есть умножить записи в каждом столбце вместе, а затем сложить итоги. Однако я хочу умножить первую запись в одной из строк на последнюю запись в другой строке, а затем вторую на вторую последнюю и так далее.

Так:

1 * 5 + -1 * 2 + 2 * -1 + 5 * 2 + 10 * 1

вместо того, чтобы просто

1 * 1 + -1 * 2 + ...

Есть ли какой-то разумный способ выполнить этот тип расчета суммы произведения в обратном порядке?

Я бы предпочел не создавать дополнительную строку в обратном порядке для каждого из этих суммовых произведений, которые я хочу вычислить.

Answer 1

Вы можете добавить еще одну строку (например, строку 3) с помощью этой формулы:

=INDEX($A$2:$J$2,COUNTA($A$2:$J$2)-COLUMN()+1)

И затем проделайте то же самое SUMPRODUCTс первым и третьим рядом.

Я не вижу способа сделать это одним утверждением.

Answer 2

Предполагая, что ваши примеры размещены в A1:E1и A2:E2, следующий код сделает то, что вам нужно, в одной формуле, просто изменив способ подачи второго диапазона в SUMPRODUCT():
(разрывы строк для ясности и во избежание полос прокрутки)

=SUMPRODUCT(A1:E1,
            N(OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1))
           )

---

Краткий обзор компонентов:

OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1)

Это эффективно выполняет итерацию (потому что используется как массив SUMPRODUCT()) по диапазону A2:E2. Это происходит в той же строке ( 0), но затем с использованием горизонтального смещения, вычисленного COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1, которое начнется с количества столбцов за A2:E2вычетом одного (т. е. последняя запись) и уменьшится до нуля (т. е. первая запись), и, таким образом, при чтении как массива он будет прочитан в обратном порядке.

Это заключено в N()вызов, чтобы гарантировать, что любые пустые ячейки, текст и т. д. считываются как ноль, и предотвращает #VALUE!ошибку в этих случаях. Если выхотетьэто приведет к возникновению ошибок, то не используйте N().

Затем он просто добавляется во SUMPRODUCT()второй массив для использования.

Answer 3

Вот UDF, с которого можно начать.

Function SUMPRODREV(rForward As Range, rBackward As Range) As Double

    Dim i As Long, j As Long
    Dim vaForw As Variant
    Dim vaBack As Variant
    Dim dReturn As Double

    'put range values into arrays
    vaForw = rForward.Value: vaBack = rBackward.Value

    'if only 1 row, multiply columns
    If UBound(vaForw, 1) = 1 Then
        For i = LBound(vaForw, 2) To UBound(vaForw, 2)
            dReturn = dReturn + (vaForw(1, i) * vaBack(1, UBound(vaForw, 2) - (i - 1)))
        Next i
    Else 'if only 1 column, multiply rows
        For i = LBound(vaForw, 1) To UBound(vaForw, 1)
            dReturn = dReturn + (vaForw(i, 1) * vaBack(UBound(vaForw, 1) - (i - 1), 1))
        Next i
    End If

    SUMPRODREV = dReturn

End Function

Answer 4

Вот простая формула, которая делает то, что вам нужно, но есть одна хитрость при ее вводе.

Предположим, что ваш диапазон ячеек выглядит так. Я назвал диапазоны «верхний» и «нижний».

1
Выделите любые 2 пустые ячейки. В моем случае я выделил ячейки B4:C4, вот так:

2
Введите это впанель формулкак формула массива (нажмите Ctrl+ Shift+ Enter):

=SUM(top*INDEX(bottom,1,LARGE(COLUMN(bottom),COLUMN(top))))

Или это (без использования именованных диапазонов):

=SUM(A1:J1*INDEX(A2:J2,1,LARGE(COLUMN(A2:J2),COLUMN(A1:J1))))

Вы получите что-то вроде результата, приведенного выше; обычная SUMPRODUCTдоходность составляет 220.

Недостаток в том, что вам просто придется скрыть лишнюю ячейку, изменив цвет ее шрифта или скрыв ее столбец или строку. Дополнительная ячейка необходима, чтобы заставить вторую часть формулы (часть INDEX) «переместиться» или вернуть массив значений.