Curso de Excel parte 10 – Cálculos Trimestrales

Espero que este curso de excel les este ayudando a mejorar su desempeño en el trabajo y a estar mas relajados, se que el trabajo en la oficina llega a ser duro, sobre todo cuando hacemos cosas tediosas y deseamos poder hacerlas mas rápido.

Descargar MegaPack Contable en Excel

Cuando se manejan formatos contables en excel, es común realizar reportes que están basados en la fecha de los registros, en este caso veremos el caso de los cálculos o análisis trimestrales, básicamente se trata de obtener información separada por cada tres meses, regularmente esto se hace iniciando en el mes de enero y termina el mes de diciembre, ( “enero, febrero, marzo ) ( abril, mayo, junio ).

1.- Determinar a que trimestre corresponde un registro de ventas según su fecha.

En la clase pasada explicamos como trabajar con bimestres, esas mismas formulas nos sirven de base para este caso, solo con un pequeño cambio, en lugar de dividir entre 2, en este caso se divide entre tres.

Recordemos que en este caso la unidad serian los tres meses completos, así que al dividir el numero del mes de la fecha entre 3 nos dará un decimal en tanto no sean trimestres completos.

Ejemplo 1: si la fecha es de marzo, vamos a obtener un tres en el mes, eso se divide entre tres y el resultado seria 1, eso significa que esa fecha corresponde al trimestre 1, pero si el mes es febrero o marzo, al dividir entre tres nos dará un decimal en ambos casos, eso significa que también pertenecen al primer trimestre, pero es necesario redondear al entero superior para que el resultado sea 1.

Ejemplo 2: Si la fecha es del mes de junio, al dividir entre 3 nos dará 2, eso quiere decir que esa fecha es del trimestre 2, pero si el mes es abril o mayo, el resultado sera un 1 con decimal, el echo de que el numero sea superior a 1 y menor que dos, significa que esa fecha corresponde al segundo trimestre, asi que solo debemos redondear al entero hacia arriba para obtener el numero de trimestre.

=REDONDEAR.MAS(MES(A1)/3,0)

Con esa simple formula se obtiene el numero de trimestre al que corresponde una fecha.

 2.- Obtener el numero de ventas realizadas en cada trimestre.

Al generar estadísticas, es común hacerlo por lapsos de tiempo largos, de ese modo la muestra sera mas completa y es posible tener un análisis mas acertado, un ejemplo es hacer acumulados trimestrales, para ello usaremos algo de la formula anterior y obtendremos la suma de las ventas según el trimestre del año deseado, esto tomando en cuenta un año completo iniciando en enero.

=SUMAPRODUCTO((REDONDEAR.MAS(MES($A$1:$A$365)/3,0)=$F2)*1)

Lo que hace esa formula es simplemente obtener el numero de trimestre al que corresponde cada fecha, luego se compara con el numero de trimestre deseado, en este caso esta la lista de trimestres en la columna F , en caso de que sean iguales, la formula devuelve un verdadero, de lo contrario da un falso, en codigo binario el verdadero corresponde a 1 y el falso seria un 0, solo es necesario multiplicar por 1 para que ese verdadero se vea como 1 en el resultado de la formula, si es falso el resultado sera cero, de ese modo sumaproducto sumara todos los 1 que devuelve la formula.

3.- Obtener el porcentaje del numero de ventas realizada en el trimestre.

Un buena estadística es obtener porcentajes, en este caso se requiere conocer que porcentaje representa el numero de ventas de cada trimestre, esto en relación al numero total de ventas que se han registrado en la base de datos, para esto usaremos la misma formula anterior, pero esta vez se dividirá el resultado entre el numero total de ventas.

=SUMAPRODUCTO((REDONDEAR.MAS(MES($A$1:$A$365)/3,0)=$F2)*1)/CONTARA($A$1:$A$365)

Al ves esa formula es fácil identificar que es casi igual que la anterior, pero en este caso solo es necesario dividir entre el numero total de ventas registradas, eso se logra con la función CONTARA, que cuenta todas las celdas no vacías en el rango.

 4.- Sumar el importe de las ventas trimestrales.

En este caso lo que se busca es obtener el total de lo que se ha vendido cada trimestre, para esto se usa la misma formula del caso 2, solo con un pequeño cambio, en lugar de multiplicar por 1 para convertir en 1 los verdaderos y cero los falsos, solo sera necesario hacer referencia a la columna donde están los importes de cada venta, de ese modo cada vez que el trimestre de la fecha corresponde al trimestre buscado, sumaproducto sumara el importe de ese registro.

=SUMAPRODUCTO((REDONDEAR.MAS(MES($A$1:$A$365)/3,0)=$F2)*$C$1:$C$365)

Si observan esa formula, básicamente esta convirtiendo cada cada fecha del registro de ventas en un numero que representa el trimestre al que corresponde esa fecha, cada vez que un registro coincide con el trimestre buscado, la formula devuelve un verdadero, entonces suma el importe que esta registrado en esa misma fila.

5.- Conocer el porcentaje que representa la suma de ventas del trimestre.

En este caso la idea es conocer que porcentaje representa cada trimestre en relación al total vendido, para ello simplemente se usa la formula anterior y se divide su resultado entre el importe total de ventas registradas.

=SUMAPRODUCTO((REDONDEAR.MAS(MES($A$1:$A$365)/3,0)=$F2)*$C$1:$C$365)/SUMA($C$1:$C$365)

Si ven esta formula y las anteriores, no es una formula muy larga pero realmente esta haciendo un calculo interesante, esta realizando una comparación interna de todas las fechas, pero antes las convierte en numero de trimestre para poder hacer esa comparación con el numero que se pone en la columna F, que finalmente representa un trimestre del año, ademas, esa formula esta realizando una suma interna de de todos los productos de la matriz cuando el trimestre coincide con el buscado, esa es precisamente la gran ventaja de que sumaproducto sea una función matricial.

Recuerden compartir en las redes sociales con sus amigos.