Sumaproducto con multiplicación por primero * último en lugar de primero * primero

Question

Estoy buscando alguna función o fórmula en Excel 2007 para hacer lo siguiente:

Tengo dos filas de números:

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

Quiero hacer algo similar a lo que hace sumproduct, que es multiplicar las entradas de cada columna y luego sumar los totales. Sin embargo, quiero multiplicar la primera entrada en una de las filas por la última entrada en la otra fila, y luego la segunda por la penúltima, y ​​así sucesivamente.

Entonces:

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

en lugar de solo

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

¿Existe alguna forma razonable de realizar este tipo de cálculo de producto de suma de orden inverso?

Preferiría no tener que crear una fila adicional en orden inverso para cada uno de estos productos de suma que quiero hacer.

Answer 1

Puede agregar otra fila (por ejemplo, en la fila 3) con esta fórmula:

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

Y luego hazlo SUMPRODUCTcon la primera y tercera fila.

No veo la manera de hacerlo en una sola declaración.

Answer 2

Suponiendo que sus ejemplos estén ubicados en A1:E1y A2:E2, lo siguiente hará lo que necesita en una sola fórmula, simplemente modificando cómo se alimenta el segundo rango SUMPRODUCT():
(saltos de línea para mayor claridad y para evitar barras de desplazamiento)

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

---

Un vistazo rápido a los componentes:

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

Esto efectivamente itera (porque lo usa como una matriz SUMPRODUCT()) sobre el rango A2:E2. Lo hace en la misma fila (la 0), pero luego usando el desplazamiento horizontal calculado por COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1, que comenzará en el número de columnas A2:E2menos uno (es decir, la entrada final) y se reducirá a cero (es decir, la primera entrada), y, por lo tanto, cuando se lea como una matriz, se leerá en orden inverso.

Esto está incluido en una N()llamada para garantizar que cualquier celda vacía, texto, etc. se lea como cero y evita un #VALUE!error en estos casos. Si usteddesearesto genera errores, entonces no uses el archivo N().

Luego, esto simplemente se coloca en el SUMPRODUCT()como segunda matriz que se utilizará.

Answer 3

Aquí hay una UDF para comenzar.

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

Aquí hay una fórmula única que hace lo que necesita, pero hay un truco para ingresarla.

Supongamos que su rango de celdas se ve así. He llamado a los rangos "superior" e "inferior".

1
Resalte 2 celdas en blanco cualesquiera. En mi caso, seleccioné las celdas B4:C4, así:

2
Introduzca esto en elbarra de formulascomo una fórmula matricial (presione Ctrl+ Shift+ Enter):

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

O esto (sin usar rangos con nombre):

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

Obtendrá algo como el resultado anterior; rendimientos regulares SUMPRODUCT220.

La desventaja es que sólo tendrás que ocultar la celda adicional cambiando el color de fuente u ocultando su columna o fila. La celda adicional es necesaria para forzar que la segunda parte de la fórmula (la parte ÍNDICE) se "mueva" o devuelva una matriz de valores.