Resumen por año usando Matrices dinámicas en Excel

En este tutorial analizaremos el caso de una Constructora, que solicitan hacer un resumen por año de un cronograma de actividades.

Lo resolvimos mediante solución muy creativa, usando fórmulas y matrices dinámicas.

Acompáñanos a ver esta bonita historia de “Excel, casos de la vida real”.

Ver video Resumen por año en Excel

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Excel, caso de la vida real

Resumen por año de un cronograma de actividades - exceleinfo
Figura 1. Resumen por año de un cronograma de actividades – exceleinfo

Una constructora tiene un cronograma de actividades en formato Diagrama de Gantt. Lo que se solicita es obtener una suma de los ingresos por actividad con base en la selección de un año.

Una manera de resolverlo fue usando funciones de Matrices dinámicas, las cuales enumeramos a continuación.

TRANSPONER el rango

TRANSPONEMOS un rango para convertir filas en columnas - exceleinfo
Figura 2. TRANSPONEMOS un rango para convertir filas en columnas – exceleinfo

Si nos fijamos el dato de los meses y años y el de los Totales, están en fila. Lo que tenemos que hacer primero en convertir a columna para posteriormente filtrar por año.

=ELEGIRCOLS(TRANSPONER(A1:AM21),1,CONTARA(B:B)-1)

Con TRANSPONER rotamos el rango y con ELEGIRCOLS mostramos sólo las columnas de mes_año y los totales.

FILTRAR por año

FILTRAR para motrar sólo el año especificado - exceleinfo
Figura 3. FILTRAR para motrar sólo el año especificado – exceleinfo

Una vez que convertimos las filas en columnas, usaremos la función FILTRO para mostrar sólo las filas que coincidan con un año especificado.

=FILTRAR(AP1#,ESNUMERO(HALLAR(--(DERECHA(AO1,4)),ELEGIRCOLS(TRANSPONER(A1:AM21),1))))

SUMAR sólo los totales

SUMAR totales del cronograma de actividades - exceleinfo
Figura 4. SUMAR totales del cronograma de actividades – exceleinfo

El siguiente paso es sumar sólo la columna de totales.

=SUMA(ELEGIRCOLS(AS1#,2))

Al final, armamos una mega fórmula

Como último paso, unimos las 3 fórmulas en una.+

=SI.ERROR(SUMA(ELEGIRCOLS(FILTRAR(ELEGIRCOLS(TRANSPONER(A1:AM21),1,CONTARA(B:B)-1),ESNUMERO(HALLAR(--(DERECHA(AO1,4)),ELEGIRCOLS(TRANSPONER(A1:AM21),1)))),2)),0)

Descarga el archivo de ejemplo

Resumen por año – EXCELeINFO.zip

👇

⭐ Si te gustó este tutorial, por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top