Continuamos con el curso de excel, esta es la parte 8 del curso, en la clase anterior vimos como hacer cálculos con semanas, esta ves veremos como hacer cálculos basándonos en meses, esto es algo muy usado en Formatos de Contabilidad entre otros controles.
1.- Como obtener el mes al que corresponde una fecha.
Con al función MES podemos obtener el numero de mes de una fecha =MES(A1) , eso nos puede servir para los cálculos que siguen, pero si se desea obtener el nombre del mes, se debe usar la siguiente formula.
=TEXTO(A1,"mmmm")
La función texto la hemos estado usando en varios ejemplos de este curso, básicamente con esa función le damos el formato deseado a la fecha que esta en la celda A1, el formato que ponemos es para indicar que nos de el nombre del mes, si solo ponemos dos veces la m de mes, la formula nos devuelve el numero del mes.
2.- Contar el numero de ventas realizadas en un mes.
Partiendo de la idea de que cada venta tiene un registro en una base de datos y cada registro tiene su fecha, podemos hacer un conteo del numero de ventas realizadas en un mes determinado.
=SUMAPRODUCTO((TEXTO($A$1:$A$365,"mmmm")=$F2)*1)
Como ya vimos en clases anteriores, sumaproducto suma los productos de matrices o rangos, en este caso los productos serian los meses, así que cada vez que un mes de la base de datos coincide con el mes del que deseamos contar sus ventas, la función realizara el conteo de cada uno.
TEXTO($A$1:$A$365,"mmmm")
En esa parte de la formula estamos obteniendo todos los nombres de mes de la base de datos completa en su columna de mes.
TEXTO($A$1:$A$365,"mmmm")=$F2
En esta parte se hace la comparación de cada mes de los registros de la base de datos con el mes que se pone en la celda F2, que seria el mes del que deseamos obtener el numero de ventas.
Al hacer la comparacion se obtienen falsos y verdaderos, los verdaderos se dan cuando el mes de la fecha coincide con el mes que indicamos en F2, los falsos se dan cuando no coincide.
((TEXTO($A$1:$A$365,"mmmm")=$F2)*1)
En esa parte de la formula se multiplica por 1 para convertir los verdaderos en 1 y los falsos en 0, de ese modo sumaproducto podrá realizar la suma.
En esa imagen se observa como al multiplicar por 1, todos los verdaderos se convierten en uno y los falsos en cero, de ese modo se logra realizar el conteo.
3.- Obtener el porcentaje del numero de ventas realizadas cada mes.
Para obtener el porcentaje que ocupa el numero de ventas realizados cada mes, solo se usa la misma formula anterior y el resultado se divide entre el total de registros de la base de datos.
=SUMAPRODUCTO((TEXTO($A$1:$A$365,"mmmm")=$F2)*1)/CONTARA($A$1:$A$365)
La función sumaproducto cuenta el numero de ventas del mes, la función contara cuenta el numero total de ventas que hay en al base de datos, después solo se aplica formato de porcentaje en esa celda.
4.- Sumar las ventas realizadas en un mes.
En este caso lo que se necesita es sumar el importe de las ventas de un mes.
=SUMAPRODUCTO((TEXTO($A$1:$A$365,"mmmm")=$F2)*$C$1:$C$365)
La formula es casi igual que la usada para contar las ventas de un mes, pero en este caso como se desea sumar el importe en dinero, en lugar de multiplicar por 1 se debe multiplicar haciendo referencia al rango donde están las cantidades en dinero de cada registro de ventas.
En esa imagen se puede observar que cuando el mes de la la columna de fecha en la base de datos concide con el mes que ponemos en F2, nos devuelve un numero, ese numero es el importe de la venta que corresponde a ese registro donde el mes si concide con el deseado.
5.- Obtener el porcentaje de ventas de un mes en relación al total.
En este caso solo se desea obtener que porcentaje corresponde a cierto mes en relacion al total de ventas en dinero.
=SUMAPRODUCTO((TEXTO($A$1:$A$365,"mmmm")=$F2)*$C$1:$C$365)/SUMA($C$1:$C$365)
La formula es casi igual a la usada para obtener la suma de ventas del mes, simplemente se divide ese resultado del mes entre la suma total de ventas.
Solo sera necesario cambiar el formato de esa celda a porcentaje.
Posdata: Recuerda que lo mas importante es practicar estos ejemplos, no basta con solo ver la información, la practica es realmente lo que te ayudara a comprender mejor los casos y adaptarlos a tus necesidades.
edu dice
gracias por el enorme trabajo y la voluntad de difusión, nos facilitas la tarea a muchos. Pero una pregunta, como debo realizar la formula para que al margen del mes, tenga en cuenta también, y a la vez, el día de la semana? No se si me explico, quiero poder saber, por ejemplo, la facturación total de los lunes de agosto, y así con todos los días de la semana y los meses del año. Como muchos, busco patrón de comportamiento del cliente
Nolberto Lopez dice
Saludos Edu.
En la siguiente pagina encontrara un ejemplo sobre como trabajar con dias de la semana para la obtención de estadísticas.
https://formulasexcel.com/curso-de-excel-calculos-con-dias-de-la-semana/
edu dice
Gracias Nolberto,
mis sospechas eran ciertas, me explique fatal. Por si te sirve para futuro, preguntaba sobre como hacer para que mas allá de distinguir entre día o mes a partir de una fecha, consiguiera, con una fórmula, tener en cuenta los dos factores. Es decir, tengo una columna A con fechas dd/mm/yy y otra, columna B, con facturación…lo que buscaba (ya encontré solución) era qué formula usar para que me diera el resultado de los lunes (e igual con otros días de la semana) de los distintos meses (pero no agrupados, separado por meses)… Creo que sigo sin explicarme, en fin, que muchas gracias y te animo a seguir con tu trabajo en el blog
Nolberto Lopez dice
Si observa en los ejemplos tanto de meses como dias, las formulas son prácticamente iguales, lo único que cambia es el formato en la función TEXTO “mmmm” “dddd”, esto con el fin de determinar si la comparación de la fecha es en base al nombre del mes o el día de la semana.
Así que lo único que se debe hacer es ingresar las dos comparaciones ( meses y dias ) en la función SUMAPRODUCTO, de la siguiente manera.
=SUMAPRODUCTO((TEXTO($A$1:$A$365,”mmmm”)=G$16)*(TEXTO($A$1:$A$365,”dddd”)=$F17)*$C$1:$C$365)
En esa formula los dias están a partir de la celda F17 hacia abajo, los meses están a partir de G16 hacia la derecha.
Suerte.
danilo efren dice
Como siempre excelente ejemplo.
Abra que trabajar en el.
Saludos.
Danilo.
danilo efren dice
Como siempre excelente ejemplo.
Saludos!!!
Danilo