Sumproduct с умножением на первый * последний вместо первого * первый

Question

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

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

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

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

Так:

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

Вот 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 3

Предполагая, что ваши примеры помещены в 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 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.

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