Sumproduct com multiplicação por primeiro * last em vez de first * first

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 

Eu quero fazer algo semelhante ao que o sumproduct faz, que é multiplicar as entradas em cada coluna e adicionar os totais. No entanto, quero multiplicar a primeira entrada em uma das linhas pela última entrada na outra linha, e depois a segunda pela segunda 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 quero 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, em seguida, faça seu SUMPRODUCT com a primeira e a terceira linha.

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

    

Answer 2

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 3

Supondo que seus exemplos sejam colocados em A1:E1 e A2:E2 , o seguinte fará o que você precisa em uma única fórmula, simplesmente modificando como o segundo intervalo é alimentado para SUMPRODUCT() :
( quebra 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 efetivamente itera (porque é usado como uma matriz por SUMPRODUCT() ) no intervalo A2:E2 . Ele faz isso 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:E2 less um (isto é, a entrada final) e reduzirá para zero (ou seja, a primeira entrada) e, assim, quando lida como uma matriz, ela será lida na ordem inversa.

Isso é agrupado em uma chamada N() para garantir que as células vazias, o texto, etc. sejam lidos como zero e evita um erro #VALUE! nesses casos. Se você quiser isso gerar erros, então não use o N() .

Isso é simplesmente descartado no SUMPRODUCT() como o segundo array a ser usado.

    

Answer 4

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

Suponha que o seu intervalo de células seja assim. Eu nomeei os intervalos "top" e "bottom".

1
Realcequaisquer2célulasembranco.Nomeucaso,euselecioneiascélulasB4:C4,assim:

2
Insira isso na barra de fórmulas como uma fórmula de matriz (pressione Ctrl + Deslocamento + Enter ):

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

Ou isso (sem usar intervalos nomeados):

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

Vocêteráalgocomooresultadoacima;SUMPRODUCTregulargera220.

Adesvantageméquevocêsóprecisaocultaracélulaextraalterandoacordafonteouocultandosuacolunaoulinha.Acélulaextraénecessáriaparaforçarasegundapartedafórmula(aparteINDEX)a"mover" ou retornar uma matriz de valores.