Para obtener el més con más tráfico de la tabla con los pasajeros del aeropuerto, se podía hacer de varias formas, pero dado el formato en que venía la tabla, estas implicaban copiar y pegar de forma repetitiva o usar macros de excel. Sin embargo, hay otra forma.
Excel tiene una funcncionalidad poco documentada llamada array formula. Esta, permite (por ejemplo) aplicar una formula diseñada para un solo valor a un rango de valores uno por uno.
Por ejemplo, en el caso del ejercicio de los pasajeros, para obtener el subtotal de pasajeros de cada mes se puede usar la siguiente array formula (en la celda AA2):
{=SI(MES(A2)<MES(A3);SUMA(SI(MES($A$2:$A$366)=MES(A2);$Z$2:$Z$366));0)}
NOTA: Tras introducir la formula (y cada vez que se modifique) hay que presionar CTRL+SHIFT+ENTER (en vez de enter), así se le indica a Excel que esta es una array formula y Excel pondra la formula entre corchetes para señalizarlo. Estos corchetes NO hay que teclearlos, y si tratas de editar la formula desaparecerán porque «no están ahí», no son caracteres reales.
Donde:
- AA2: Subtotal al final del mes, celda en la que ponemos la formula.
- A2: Fecha del dia actual.
- A3:Fecha del siguiente dia.
- $A$2:$A$366: Rango de fechas para todos los dias del año.
- $Z$2:$Z$366: Rango de pasajeros totales del dia para todos los dias del año.
Explicación:
Primero comprobamos si estamos en el ultimo dia del mes, si es así, mostraremos el total del mes , si no 0:
=SI( MES(A2)<MES(A3) ; SUMA(SI(MES($A$2:$A$366)=MES(A2);$Z$2:$Z$366)); 0)
Suponiendo que estemos en una celda que esté en la fila de un día que sea final de més (y por tanto no sea 0), sumamos todos los subtotales de dia que estén en el mismo més que la fila de la celda en la que estamos.
=SI( MES(A2)<MES(A3) ; SUMA(SI(MES($A$2:$A$366)=MES(A2);$Z$2:$Z$366)); 0)
Para filtrar solo los subtotales del mismo mes usamos un SI. Como estamos en una array formula podemos operar con rangos en vez de con celdas individuales. Así pues, si el MES de la fecha de cada uno de los subtotales de dia, coincide con el mes de la fecha de nuestra celda, lo añadimos a nuestro rango, si no, se añade un FALSO, que al sumarlo computa como 0.
=SI( MES(A2)<MES(A3) ; SUMA(SI(MES($A$2:$A$366)=MES(A2);$Z$2:$Z$366)); 0)
Las array formulas, pueden constar de más de una celda de resultados. En nuestro caso, solo consta de una, lo que nos permite arrastrarla (copiarla) como si fuese una celda normal para rellenar la columna entera.
Por último, para evitar que el último dia del año (que no va seguido por otra fila con una fecha valida) no de un error (como #VALOR), habría que retocar el primer if:
=SI(SI(ESNUMERO(A3);MES(A2)<MES(A3);VERDADERO); SUMA(SI(MES($A$2:$A$366)=MES(A2);$Z$2:$Z$366));0)
Esto permite obtener una columna con los subtotales de mes el último dia de este. Con este mismo tipo de formulas, se podrían obtener subtotales, promedios, desviaciones típicas… por semanas, meses o cualquier otro criterio, sin tener que usar macros o columnas con cálculos intermedios.