Dividir el importe en columnas mensuales

Digamos que necesita tomar una cantidad y dividirla en partes iguales en columnas mensuales. Por ejemplo, quizás necesite reconocer los ingresos a lo largo del tiempo. O tal vez haya gastado algo de dinero y necesite asignar el gasto a lo largo del tiempo. Hay otros ejemplos, pero la idea básica es que tienes una cantidad total que debes distribuir uniformemente en columnas mensuales. Como se trata de Excel, existen muchos enfoques posibles. En esta publicación, presentaré una solución que utiliza matrices dinámicas. La ventaja de esta solución es que puede ajustar rápida y fácilmente la cantidad de columnas que se muestran en el informe. He preparado un vídeo y una narrativa completa a continuación.

Video

Objetivo

Para ilustrar esta idea, digamos que recaudamos dinero por adelantado por las suscripciones que vendemos. Por ejemplo, un cliente puede pagarnos 240 dólares por una suscripción anual. Básicamente necesitamos reconocer estos ingresos a lo largo del tiempo a medida que se obtienen. Dado que $240/12 meses = $20, nos gustaría que nuestra hoja de trabajo muestre doce columnas mensuales con $20 cada una. Los primeros meses se muestran a continuación:

Nuestra hoja de trabajo debe incluir una fila por cada suscripción vendida a nuestros clientes. Cada suscripción podría tener una fecha de inicio, plazo y monto diferente. Quizás algo como esto:

Nuestro objetivo es escribir una fórmula y completarla, y queremos que funcione para todas las suscripciones. Además, queremos poder cambiar las columnas que se muestran en el informe en cualquier momento, incluida la fecha de inicio y la cantidad de meses. Y cuando lo hagamos, queremos que todas las fórmulas se actualicen en consecuencia.

Para abordar esto, aprovecharemos las matrices dinámicas y las funciones SEQUENCE y EOMONTH.

Detalles

Calcularemos nuestra hoja de trabajo siguiendo los siguientes pasos:

• Etiquetas de mes dinámicas
• Mensual verdadero/falso
• Cantidad mensual

Hagámoslo.

Nota: esta solución utiliza matrices dinámicas y la función SECUENCIA. Dependiendo de cuándo lea esta publicación, su versión de Excel puede admitirlos o no. Al momento de escribir este artículo, estas funciones solo están disponibles en la versión O365 de Excel. Si tiene una versión de Excel que no admite matrices dinámicas o la función SECUENCIA, consulte esta publicación anterior .

Etiquetas de mes dinámicas

Primero, abordemos las etiquetas de las columnas mensuales. Queremos que se generen dinámicamente en función de la fecha de inicio y los meses ingresados ​​por el usuario. Proporcionaremos dos celdas de entrada, como esta:

Según los valores ingresados, queremos que Excel cree automáticamente las etiquetas de las columnas mensuales.

Para lograr esto, necesitamos comprender cómo funcionan dos funciones: EOMONTH y SEQUENCE. Tomémoslos uno a la vez y luego combinémoslos.

EOMES

EOMONTH calcula el último día del mes, dada una fecha de inicio y el número de meses a compensar. Por ejemplo:

=EOMES(fecha_inicio, meses)

Entonces, si la fecha_inicio es 1/1/2030 y los meses son 0, entonces EOMONTH devuelve 31/01/2030. Si la fecha de inicio es 1/1/2030 y el mes es 1, entonces EOMONTH devuelve 28/02/2030, y así sucesivamente.

SECUENCIA

SECUENCIA devuelve una secuencia numérica, dado un valor inicial.

=SECUENCIA(filas, [columnas], [inicio], [paso])

Podemos especificar el número de filas y columnas que nos gustaría que se devuelvan, así como definir el valor inicial. Por ejemplo, podríamos solicitar 12 filas comenzando en 1 para devolver una matriz de valores del 1 al 12. El valor del paso le indica a Excel el valor del incremento y el valor predeterminado es 1 si se omite.

EOMES/SECUENCIA

Ahora, juntemos estas dos funciones. Si queremos que Excel genere automáticamente el número solicitado de columnas mensuales (C6 en la captura de pantalla anterior), comenzando con el mes ingresado por el usuario (C5 en la captura de pantalla anterior), usamos SECUENCIA como argumento de meses de EOMONTH, así:

=EOMES(C5,SECUENCIA(1,C6,0))

A medida que el usuario ingresa varios valores para la fecha de inicio y la cantidad de meses, las etiquetas de las columnas se actualizan en consecuencia.

¡Muy guay!

Nota: querrás seleccionar toda la fila (11) y aplicar el formato de fecha deseado. En la captura de pantalla anterior, utilicé el formato de fecha personalizado mmm-aaaa. Pero puede elegir cualquier formato de fecha que desee.

Mensual VERDADERO/FALSO

Con las etiquetas de las columnas mensuales funcionando, debemos identificar qué columnas deben recibir los montos mensuales, según la fecha de inicio de la suscripción, la fecha de finalización calculada y las etiquetas de las columnas mensuales.

Básicamente, si la etiqueta de la columna mensual se encuentra entre la fecha de inicio de la suscripción y la fecha de finalización de la suscripción calculada, entonces debemos insertar el monto mensual (el total de la suscripción dividido por los meses de suscripción). Sin embargo, si el encabezado de la columna es anterior a la fecha de inicio o posterior a la fecha de finalización de la suscripción, la columna obtiene 0.

Para identificar qué columnas mensuales deben obtener importes mensuales, utilizaremos la lógica booleana de verdadero/falso.

Para empezar, determinemos si la fecha de inicio de la suscripción (D12) es menor que la etiqueta de la columna (F11). Podemos usar un operador de comparación básico menor o igual que, como este:

=D12=F11

Si escribiéramos la fórmula de esta manera, no se expandiría dinámicamente junto con los encabezados de las columnas. Entonces, necesitamos usar el operador de rango de derrame #, así:

=D12=F11#

Ahora, la fórmula devuelve múltiples resultados, y esos resultados se extienden junto con las etiquetas de las columnas. Necesitamos un ajuste más para asegurarnos de que cuando completemos la fórmula seguirá funcionando, por lo que actualizamos F11 a una referencia absoluta, como esta:

=D12=$F$11#

Ahora debemos abordar la fecha de finalización de la suscripción. Nuestro objetivo es ver si la etiqueta de la columna es anterior a la fecha de finalización de la suscripción. Podemos usar EOMONTH para calcular la fecha de finalización de la suscripción. Entonces, podemos usar la siguiente expresión para comparar la etiqueta de la columna con la fecha de finalización de la suscripción calculada de esta manera:

$F$11#=EOMES(D12, E12-1)

Para agregar esta expresión a nuestra fórmula original, necesitamos usar el operador de multiplicación para la lógica AND (ambas expresiones deben ser verdaderas para que la columna obtenga el monto mensual). Entonces, nuestra fórmula actualizada se ve así:

=(D12=$F$11#)*($F$11#=EOMES(D12,E12-1))

Esta fórmula devuelve 1 en las celdas que necesitan la cantidad mensual y 0 en las celdas que no la necesitan. Podemos completar la fórmula para todas las suscripciones y los resultados se verán así:

Ya sólo queda calcular el importe mensual de cada suscripción.

Cantidad mensual

La expresión básica para calcular el monto mensual es la venta total dividida por el número de meses de suscripción. Por ejemplo, para el Cliente A se vería así:

=C12/E12

Como ya tenemos 1 y 0 en las columnas correctas, todo lo que tenemos que hacer es multiplicar esos resultados por la cantidad mensual. Entonces, actualizamos nuestra fórmula de esta manera:

=(D12=$F$11#)*($F$11#=EOMES(D12,E12-1))*(C12/E12)

Cuando completamos la versión actualizada, confirmamos que los resultados se ven bien:

Y lo que me encanta de esta solución es lo dinámica que es. Podemos cambiar los valores de entrada de Fecha de inicio o Meses, y las etiquetas de las columnas del informe y los montos se actualizan inmediatamente.

Si tiene otros consejos interesantes sobre matrices dinámicas o SECUENCIAS, compártalos publicando un comentario a continuación… ¡gracias!

Archivo de muestra: RevRecDA.xlsx

Nota: si descarga el archivo de muestra pero su versión de Excel no admite matrices dinámicas o la función SECUENCIA, es posible que reciba errores de fórmula y las columnas no se actualizarán dinámicamente como se describe en el artículo anterior.