Tenemos el modelo que suelo usar, habitualmente, que es ventas de productos en diferentes centros comerciales, provincias, y, diferentes fechas.
Lo primero que queremos calcular, es el total para cada último mes de cada trimestre, es decir, un año tiene cuatro trimestres, marzo, junio, septiembre, y, diciembre, pues el total, para cada uno de estos meses.
Este modelo está en formato de tabla, y, se llama datos.
Pues, empecemos…
En una celda, cualquiera, voy a usar la función MES, de forma matricial, para extraer el mes de cada fecha, para ello, escribimos el signo igual, seguido de la función, y, abrimos un paréntesis.
=MES(
Seleccionamos la columna fecha, cerramos paréntesis, y, aceptamos.
=MES(Datos[Fecha])
Tenemos una columna con el número de mes.
Disponemos de una función, llamada RESIDUO, que nos calcula el resto de una división, donde, nos devuelve cero si el resto es exacto, y, uno, si no lo es.
Por ejemplo, si en una celda, escribo =RESIDUO(1;3), me devuelve el valor 1, porque la división no es exacta, pero, si ponga la siguiente expresión =RESIDUO(3;3), vemos que nos devuelve cero, porque la división es exacta.
Quiere decir, que puedo usar la función RESIDUO, para calcular el último trimestre, antes, en una columna hemos usado la función MES, para extraer el numero de mes, la función RESIDUO, tiene dos argumentos, que son número, y, numero divisor, el primer argumento es el numero a dividir, y, el segundo argumento, es por el numero que vamos a dividir, pues, el argumento número, va a ser la función MES, y, el argumento numero divisor, va a ser 3.
Es decir, va a dividir cada numero de mes, entre 3, solo nos va a devolver el valor cero, cuando la división sea exacta, y, será exacta, cuando se divida 3 entre 3, o, 6 entre 3, o, 9 entre 3, o, 12 entre 3, es decir, el mes de cada trimestre.
Probemos.
=RESIDUO(MES(Datos[Fecha]);3)
Aceptamos, y, vemos que donde el mes es tres (marzo), la función RESIDUO, devuelve cero.
Lo mismo pasa para el mes de junio.
El mes de septiembre.
Y, el mes de diciembre.
Estamos trabajando con el año 2017.
Lo siguiente que vamos a hacer, es filtrar la columna de total, siempre, que el resultado de RESIDUO sea cero, para ello, vamos a usar la función matricial FILTRAR.
Bien, pues, escribimos el signo igual, seguido de la función FILTRAR, y, abrimos un paréntesis.
=FILTRAR(
Como argumento, array, seleccionamos la columna de total.
=FILTRAR(Datos[Total];
Como argumento include, vamos a abrir un paréntesis, donde ponemos la función RESIDUO, pero lo igualamos a cero, para que nos devuelva solo los totales, del ultimo mes de cada trimestre, cerramos paréntesis de la condición, cerramos paréntesis de la función FILTRAR, y, aceptamos.
=FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0))
Tenemos una matriz desbordada con los totales del ultimo mes para cada trimestre.
Pero, queremos el ultimo mes del primer trimestre, es decir, del mes de marzo.
Para ello, vamos a modificar la función FILTRAR, y, después del cierre de paréntesis de la primera condición, ponemos el símbolo de asterisco, que es igual que operador lógico Y, es decir, nos va a devolver VERDADERO, si todas las condiciones se cumplen.
=FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0)*
Abrimos un paréntesis, y, vamos a poner la segunda condición, donde usamos la función MES en la columna de fecha, y, la igualamos a 3, para que nos devuelva solo los totales para el mes de marzo.
Cerramos paréntesis, y, aceptamos.
=FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0)*(MES(Datos[Fecha])=3))
Tenemos una matriz desbordada, solo con los valores del mes de marzo, pero, queremos el total, por lo que después del signo igual, usamos la función SUMA.
=SUMA(FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0)*(MES(Datos[Fecha])=3)))
Ahora debemos de copiar hacia la derecha, por lo que voy a seleccionar la función, pulso CTRL más C, para copiar, selecciono las celdas H2, I2, y, J2, botón alternativo del ratón, y, seleccionamos pegar como fórmula, dentro de pegado especial.
En cada función, cambiamos el número de mes, por el que corresponda.
Ya tenemos el total del ultimo mes de cada trimestre.
Mi pregunta es, ¿puedo hacerlo de otra forma?
Pues vamos a verlo.
Si en una celda, abro un paréntesis, uso la función MES en la columna de fecha, cierro paréntesis de la función MES, y, del primer paréntesis, y, aceptamos.
=(MES(Datos[Fecha]))
Obtenemos una matriz desbordada con el numero de mes de cada fecha.
Pero, quiero que sea el mes 3, es decir, el ultimo mes del primer trimestre, pues al final de la función, escribo el signo igual, y, el número 3.
=(MES(Datos[Fecha])=3)
Obtenemos una matriz desbordada con VERDADEROS, y, FALSOS, VERDADERO donde el numero de mes es 3, y, FALSO, donde no lo es.
Y, si, después del cierre del último paréntesis, usamos el símbolo de asterisco, que ya sabemos que nos devolverá VERDADERO, si todas las condiciones se cumplen, donde selecciono la columna de total, y, cierro paréntesis.
=(MES(Datos[Fecha])=3)*(Datos[Total])
Pues, obtenemos una matriz desbordada con los valores del ultimo mes del primer trimestre, y, el valor cera donde no lo es.
Igual que antes, como queremos el total, vamos a hacer uso de la función SUMA.
=SUMA((MES(Datos[Fecha])=3)*(Datos[Total]))
Podemos ver que obtenemos el mismo resultado.
Copiamos hacia la derecha, y, vemos que todos los resultados son iguales, lo único que debemos de cambiar, igual que antes, es el número de mes.
Esta fue la primera función que usamos:
=SUMA(FILTRAR(Datos[Total];(RESIDUO(MES(Datos[Fecha]);3)=0)*(MES(Datos[Fecha])=3)))
Esta la segunda:
=SUMA((MES(Datos[Fecha])=3)*(Datos[Total]))
Entre las dos, la segunda expresión es mucho mas corta, y, tenemos los mismos resultados, por lo que volvemos a recordar, que en Excel, lo mismo se puede hacer de diferentes maneras.
Pero, todavía podemos hacerlo, de otra manera, seria con el condicional SI, donde voy a preguntar que si el mes de la columna fecha, es igual a 3, que me devuelva el resultado de la columna de total, en caso contrario, que me devuelva un texto en blanco.
=SI(MES(Datos[Fecha])=3;Datos[Total];"")
Obtenemos una matriz desbordada con los totales, solo para el mes de marzo.
Pero, como queremos el total, usamos, de nuevo, la función SUMA.
=SUMA(SI(MES(Datos[Fecha])=3;Datos[Total];""))
Vemos que obtenemos el mismo resultado del ultimo mes del primer trimestre.
Volvemos a copiar hacia la derecha, y, seguimos teniendo los mismos resultados.
Comentários