Tengo una fila que quiero completar con fórmulas como
=SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)
=SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48)
=SUMPRODUCT(D91:D94,D44:D47)/SUM(D44:D47)
pero arrastrar la fórmula solo cambia la parte alfabética del índice. ¿Hay alguna forma de que Excel cambie correctamente los índices de la matriz?
Respuesta1
Según tengo entendido, tienes una celda con fórmula =SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)y quieres llenarla correctamente para que la celda de la siguiente columna pero en la misma fila tenga la fórmula =SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48). Es decir, desea que la columna aumente en 1 como de costumbre, pero también que la fila disminuya en 1.
Puedes lograr esto usando la OFFSETfunción. Esto le permite construir un rango que esté a una distancia específica de un rango conocido. Podemos usar la COLUMNfunción para especificar cuánto desplazamiento queremos.
Los rangos que queremos son B93:B96y B46:B49, compensados por -1 fila y +1 columna para cada columna después de la primera. Por lo tanto, OFFSET(B93:B96, -1, 1)devolverá una referencia a C92:C95. Sin embargo, nuestra referencia actualizará naturalmente la columna, por lo que podemos dejar la columna desplazada.
Para el rango que está B93:B96en la fórmula original, en la primera celda queremos OFFSET(B93:B96, 0, 0), en la segunda queremos OFFSET(C93:C96, -1, 0), en la tercera queremos OFFSET(D93:D96, -2, 0)y así sucesivamente. Podemos usarlo =COLUMN(A1)para obtener un resultado que es el número de la columna actual (porque la referencia se actualizará a B1 en la segunda columna, etc.). Combinando estos, podemos usar
OFFSET(B93:B96, 1 - COLUMN(A1), 0)
el cual podemos simplificar alterando la referencia:
OFFSET(B94:B97, -COLUMN(A1), 0)
En la primera celda, esto se traduce en OFFSET(B94:B97, -1, 0)cuál es B93:B96. En la segunda celda, la fórmula se completará como OFFSET(C94:C97, -COLUMN(B1), 0)está C92:C95.
Entonces las dos referencias que necesitamos son OFFSET(B94:B97, -COLUMN(A1), 0)y OFFSET(B47:B50, -COLUMN(A1), 0). Por tanto la fórmula será
=SUMPRODUCT(OFFSET(B94:B97, -COLUMN(A1), 0), OFFSET(B47:B50, -COLUMN(A1), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
Hay una última complicación. Cuando estaba probando esto en mi copia de Excel 2007, encontré que dentro de la SUMPRODUCTfórmula, COLUMN(A1)devolvía unformaciónque contiene el valor 1, en lugar de solo el valor 1. (Puede ver esto en el cuadro de diálogo Evaluar fórmula: cuando evalúa, COLUMN(A1)el resultado es {1}en lugar de solo 1, y la SUMPRODUCTexpresión obtiene dos #VALUE!argumentos y termina siendo 0). Dependiendo de su versión, es posible que esto no te suceda. Si es así, envuelva el COLUMN(A1)interior a SUM(), así:
=SUMPRODUCT(OFFSET(B94:B97, -SUM(COLUMN(A1)), 0), OFFSET(B47:B50, -SUM(COLUMN(A1)), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
Notarás que no he envuelto el final COLUMN(A1)en un SUM(); eso se debe a que éste devolvió un número como de costumbre, no una referencia de matriz. Supongo que dado que la SUMfunción no acepta referencias a matrices, Excel se dio cuenta de que no queríamos una matriz COLUMNen este caso.