Somaproduto com multiplicação por primeiro * último em vez de primeiro * primeiro

Question

Estou procurando alguma função ou fórmula no Excel 2007 para fazer o seguinte:

Eu tenho duas linhas de números:

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

Quero fazer algo semelhante ao que sumproduct faz, que é multiplicar as entradas em cada coluna e depois somar os totais. No entanto, quero multiplicar a primeira entrada em uma das linhas pela última entrada na outra linha e, em seguida, a segunda pela penúltima e assim por diante.

Então:

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

em vez de apenas

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

Existe alguma maneira razoável de fazer esse tipo de cálculo de produto de soma de ordem reversa?

Eu preferiria não ter que criar uma linha extra na ordem inversa para cada um desses produtos de soma que desejo fazer.

Answer 1

Você pode adicionar outra linha (digamos, na linha 3) com esta fórmula:

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

E então faça o seu SUMPRODUCTcom a primeira e terceira linha.

Não consigo ver uma maneira de fazer isso em apenas uma afirmação

Answer 2

Supondo que seus exemplos sejam colocados em A1:E1e A2:E2, o seguinte fará o que você precisa em uma única fórmula, simplesmente modificando como o segundo intervalo é alimentado SUMPRODUCT():
(quebras de linha para maior clareza e para evitar barras de rolagem)

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

---

Uma rápida olhada nos componentes:

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

Isso itera efetivamente (porque é usado como uma matriz por SUMPRODUCT()) no intervalo A2:E2. Isso é feito na mesma linha (o 0), mas usando o deslocamento horizontal calculado por COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1, que começará no número de colunas em A2:E2menos de um (ou seja, a entrada final) e reduzirá a zero (ou seja, a primeira entrada), e assim, quando lido como um array, será lido na ordem inversa.

Isso é encapsulado em uma N()chamada para garantir que quaisquer células vazias, texto, etc. sejam lidas como zero e evitam #VALUE!erros nesses casos. Se vocêquererisso para gerar erros, então não use o N().

Isso é simplesmente colocado no SUMPRODUCT()segundo array a ser usado.

Answer 3

Aqui está um UDF para você começar.

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

Aqui está uma fórmula única que faz o que você precisa, mas há um truque para inseri-la.

Suponha que seu intervalo de células seja assim. Chamei os intervalos de "superior" e "inferior".

1
Destaque quaisquer 2 células em branco. No meu caso, selecionei as células B4:C4, assim:

2
Insira isso noBarra de Fórmulacomo uma fórmula de matriz (pressione Ctrl+ Shift+ Enter):

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

Ou isto (sem usar intervalos nomeados):

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

Você obterá algo parecido com o resultado acima; rendimentos regulares SUMPRODUCT220.

A desvantagem é que você apenas terá que ocultar a célula extra alterando a cor da fonte ou ocultando sua coluna ou linha. A célula extra é necessária para forçar a 2ª parte da fórmula (a parte INDEX) a "mover" ou para retornar uma matriz de valores.