jueves, 9 de febrero de 2017

Suma Acumulada Cruzada por Fila y Columna

Un lector consultaba por la manera de Acumular cantidades de acuerdo a las repeticiones de elementos por filas y columnas en Excel:
[...]como a partir de la matriz en la cual los títulos de columnas y de filas pueden repetirse en varias columnas y filas, hacer una matriz resumida que responda a la suma de cada celda con el mismo título de fila y columna?..., no se si se entiende bien la pregunta
Un ejemplo corto sería:
COL
FILA c1 c2 c1 c3 c2 c1
f1 1 1 1 1 1 1
f2 1 1 1 1 1 1
f3 1 1 1 1 1 1
f3 1 1 1 1 1 1
f1 1 1 1 1 1 1
f2 1 1 1 1 1 1
f1 1 1 1 1 1 1

Para lograr el siguiente resumen:
COL
FILA c1 c2 c3
f1 9 6 3
f2 6 4 2
f3 6 4 2 
[...]

El asunto podría parecer 'peliagudo', pero nada más lejos de la realidad.
Emplearemos nuestras fórmulas matriciales para resolver el caso.
Partiremos del siguiente ejemplo:

Suma Acumulada Cruzada por Fila y Columna



La idea es completar de manera directa y acumular, sin repetición, por los elementos dispuestos por filas y columnas, esto es, completar el siguiente cuadro:

Suma Acumulada Cruzada por Fila y Columna


¿Cuál es la fórmula matricial buscada que nos resolverá la cuestión?.
Insertamos en K2:
=SUMA(SI($A$2:$A$10=$J2;SI($B$1:$G$1=K$1;$B$2:$G$10)))
y ejecutamos matricialmente!! (presionamos Ctrl+Mayusc+Enter)


Luego podemos copiar y pegar al resto de celdas de nuestro resumen, obteniendo los datos acumulados por fila y columna.
Podemos comprobar en el total calculado en una y otra tabla que la suma acumulada se ha completado correctamente.

Fijémonos en el doble condicional aplicado matricialmente dentro de nuestra suma... en él indicamos que cuando en los encabezados:
$A$2:$A$10 y $B$1:$G$1
coincidan con los elementos de nuestra segunda tabla resumen, se devuelva la matriz de datos $B$2:$G$10 !!!.
La fórmula entiende por sí misma que los datos que interesan son los que cruza unos con otros... tal como necesitábamos.

No hay comentarios:

Publicar un comentario