Fórmula de Excel para asignar una cantidad en columnas mensuales

Si alguna vez ha necesitado asignar una cantidad a lo largo del tiempo y dividir la cantidad en columnas mensuales según la cantidad de días, rápidamente se dará cuenta de que esta idea simple es difícil de implementar. Como ocurre con cualquier cosa en Excel, hay muchas formas de realizar esta tarea y esta publicación explica uno de esos métodos. Si prefiere otro método, publique un comentario con su fórmula.

La meta

El objetivo es tomar una cantidad, digamos $1,000, y asignarla en columnas mensuales entre las fechas de inicio y finalización de la asignación, digamos del 1/1 al 31/3. Esperaríamos ver alrededor de un tercio del monto en cada columna mensual de enero, febrero y marzo. Lo asignaremos en función del número de días de cada mes, por lo que no serán tercios iguales (enero tiene más días que febrero). Además de este objetivo principal, nuestra fórmula debe ser coherente, de modo que podamos completarla hacia abajo y hacia la derecha y debería seguir funcionando. Además, nuestra fórmula debe manejar montos negativos en caso de que haya saldos acreedores. Y nuestra fórmula debería arrojar un cero si el período de asignación está fuera de los meses reflejados en nuestra tabla de asignación. Finalmente, nuestra fórmula debe manejar períodos mensuales parciales y admitir períodos de asignación de, digamos, 15/01/14 al 15/04/14.

Aunque Excel tiene algunas funciones integradas que nos acercan un poco, no he descubierto ninguna que satisfaga todos los objetivos descritos anteriormente. Una de esas funciones que nos acerca es la función VDB; sin embargo, tiene la suposición incorporada de que la asignación se realiza en períodos iguales y no nos permite asignar una cantidad basada en la cantidad de días de un mes. Si su libro de trabajo no necesita esta asignación diaria y simplemente requiere períodos de meses completos, entonces la función VDB simplificará enormemente su libro de trabajo y definitivamente vale la pena dedicar tiempo a revisarlo.

Nota: los siguientes párrafos recorren la lógica de la fórmula; sin embargo, si solo quieres la respuesta, salta hasta el final de la publicación y no dudes en descargar el archivo de muestra.

El recorrido

En lugar de simplemente publicar la fórmula, pensé que sería útil construirla juntos, pieza por pieza, de modo que si deciden usarla en sus libros de trabajo puedan adaptarla y mantenerla a lo largo del tiempo.

En primer lugar, echemos un vistazo al libro de trabajo de muestra, para que nuestro objetivo quede muy claro.

La idea general es bastante simple. Tomamos una cantidad, la dividimos por la cantidad de días del período de asignación y luego multiplicamos esta cantidad diaria por la cantidad de días representada por cada columna en la tabla de asignación. Las fórmulas relacionadas en Excel también son bastante simples, excepto la parte que calcula el número de días representados por cada columna mensual.

Comencemos con las cantidades calculadas fácilmente, el número de días (Days) y la cantidad diaria (DailyAmt). El número de días se puede determinar rápidamente restando las fechas de fin de asignación y de inicio de asignación. En la celda F9, la fórmula sería:

=D9-C9

Esta fórmula calcula el número de días entre dos fechas, lo que para la mayoría de las cosas está bien. Sin embargo, en nuestro caso, crea un problema. Tomando el caso simple, si quisiéramos asignar un valor del 1/1/14 al 31/03/14, hay 90 días que debemos asignar. Es decir, 31 días para enero, 28 días para febrero y 31 días para marzo. Sin embargo, una simple resta da como resultado solo 89 días porque no incluye ambos puntos finales de fecha. Este es un problema fácil de superar, ya que simplemente podemos sumar 1 al resultado. Nuestra fórmula modificada, que se muestra a continuación, completará perfectamente la columna Días.

=D9-C9+1

La siguiente es la columna DailyAmt. Esto representa la cantidad a asignar diariamente, por lo que es solo la cantidad total dividida por la cantidad de días. La fórmula en G9 es:

=B9/F9

Podemos completar cada una de estas fórmulas y el resultado se muestra a continuación.

Hasta ahora, todo bien. Ahora, vayamos a la parte difícil: calcular el número de días representados por cada columna.

Sabemos que si podemos calcular el número de días de asignación representados en cada columna, simplemente podemos multiplicarlo por la cantidad diaria (DailyAmt). Entonces, por ahora, nos centraremos únicamente en calcular la cantidad de días representados en cada columna.

Hagamos una suposición que ayudará a lo largo de este ejercicio. Supongamos que los encabezados de columna de la tabla de asignación mensual son valores de fecha que representan el último día del mes. Entonces, el encabezado de la columna de enero es en realidad un valor de fecha que representa el 31/01/14, y así sucesivamente.

En lugar de simplemente publicar la fórmula para calcular los días, quería brindar una forma más visual de comprender la mecánica. Entonces, creé una serie de capturas de pantalla para ilustrar lo que hace cada parte de la fórmula.

Si lo piensas bien, hay varios casos que necesitamos que maneje nuestra elegante fórmula. Lo necesitamos para manejar el caso en el que el encabezado de la columna actual, lo llamaremos período actual, se encuentra entre las fechas de inicio y finalización de la asignación. Nuestra fórmula también debe manejar el caso en el que el encabezado de la columna mensual sea menor que la fecha de inicio. También debe funcionar cuando el período actual es mayor que la fecha de finalización. Entonces, iremos despacio y mejoraremos la fórmula paso a paso.

Primero tomemos un caso sencillo, el caso en el que la etiqueta de la columna actual está dentro de un mes del período de asignación. Para la serie de capturas de pantalla a continuación, todas asumen que el período de asignación es el período de tres meses desde el 1/1 (fecha de inicio) hasta el 31/03 (fecha final). Si el período actual (CurPer) es 1/31, entonces podríamos pensar en el número de días como el segmento de línea A que se muestra a continuación.

El número de días en el segmento A se calcula fácilmente restando los puntos finales de fecha.

A = actual - fecha de inicioA = 1/31 - 1/1(Nota: técnicamente esto tiene un retraso de un día; abordaremos este problema en breve)

¿Hasta ahora, todo bien? Bien, consideremos otro caso. El caso en el que la fecha de la columna mensual es anterior a la fecha de inicio de la asignación. Por ejemplo, una fecha de columna mensual actual del 30/11, como se ilustra a continuación.

En este caso, nuestra fórmula (CurPer – StartDate) dará como resultado un número de días negativo. Esta es una solución fácil. La función MAX devuelve el valor de argumento más grande. Por ejemplo, MAX(100,0) devuelve 100 y MAX(-200,0) devuelve 0. Por lo tanto, podemos actualizar fácilmente la fórmula para evitar que se devuelva un número negativo envolviendo una función MAX alrededor de ella.

A=MAX(ActualPor-Fecha de inicio,0)A=MÁX(11/30-1/1,0)A=0

Otro caso que nuestra fórmula debe manejar es el caso en el que el período actual lleva más de un mes desde el período de asignación. Por ejemplo, cuando CurPer = 2/28. Considere la captura de pantalla a continuación.

Nuestra fórmula existente (CurPer – StartDate) devuelve el número acumulado de días entre el período actual y la fecha de inicio. Como solo queremos la cantidad de días dentro de la columna mensual actual, debemos restar la cantidad de días anteriores al período actual. Esto está representado por el segmento C anterior. El segmento C se puede calcular restando el segmento B del segmento A.

C=ABA=Actual por fecha de inicio = 28/2-1/1B=Anterior por fecha de inicio = 31/1-1/1

El valor PriorPer se calcula fácilmente con la función EOMONTH. Si no ha explorado la función EOMONTH, aquí hay una publicación que la explica. Para evitar valores diarios negativos, usaremos una vez más la función MAX.

El último caso que debemos manejar es el caso en el que el período actual cae después de la fecha de finalización, como cuando CurPer = 5/31. Esto se ilustra a continuación.

Nuestra fórmula existente, que calcula el Segmento C, devolvería un valor distinto de cero aunque no debería haber ninguna asignación, ya que la fecha del período actual es posterior al último día del período de asignación. Por lo tanto, le restamos la cantidad de días en el período actual que exceden la fecha de finalización, representada arriba por el segmento F. El resultado de la fórmula deseada es CF para devolver cero días.

F=DED=Fecha de finalización actualE = Fecha anterior por finalización

Nuevamente, para evitar que se devuelvan días negativos, los envolvemos con una función MAX y, una vez más, calculamos la fecha PriorPer usando la función EOMONTH.

Si ponemos todo esto en una fórmula y luego completamos la fórmula hacia abajo y hacia la derecha, se obtiene el resultado del número de días calculados para cada columna de la tabla de asignación.

La fórmula de Excel utilizada en la celda H9 es la siguiente:

=(MAX(H$8-$C9,0)-MAX(EOMES(H$8,-1)-$C9,0))-(MAX(H$8-$D9,0)-MAX(EOMES(H$8,-1)-$D9,0))

Aunque pueda parecer confuso, se trata simplemente de calcular los pasos que hicimos anteriormente. Calculamos el segmento C (la primera línea de la fórmula anterior) y le restamos el segmento F (la segunda línea).

Las funciones MAX están ahí solo para garantizar que no devolvamos un número negativo de días, y las funciones EOMONTH están ahí solo para calcular los valores de fecha del período anterior.

Notará que todavía debemos abordar el hecho de que tenemos un día de diferencia en la primera columna del período de asignación. Es decir, la columna 1/31 refleja incorrectamente 30 días, cuando debería ser 31 días. Esto se debe a que 31/1 – 1/1 son 30 días, pero debemos realizar la asignación en función de 31 días. Necesitamos actualizar las fórmulas de nuestra tabla de asignación para agregar uno a la cantidad de días para el primer período de asignación. Hay muchas maneras de lograr esto, una es usar una fórmula de comparación que determine si el período CurPer es igual al período StartDate. Una fórmula de comparación devuelve VERDADERO y el motor de cálculo de Excel trata VERDADERO como un 1. Entonces, si agregamos lo siguiente al final de nuestra fórmula, deberíamos estar bien:

+EOMES(CurPer)=EOMES(Fecha de inicio)

Al traducir la lógica anterior a una fórmula de Excel real, se obtiene la fórmula actualizada a continuación.

=(MAX(H$8-$C9,0)-MAX(EOMES(H$8,-1)-$C9,0))-(MAX(H$8-$D9,0)-MAX(EOMES(H$8,-1)-$D9,0))+(EOMES(H$8,0)=EOMES($C9,0))

La fórmula anterior toma el segmento C menos el segmento F y luego suma 1 si el último día del período de la columna actual es igual al período de la fecha de inicio. La tabla de asignación actualizada se muestra a continuación.

Ahora que el número de días es exacto, todo lo que tenemos que hacer es multiplicarlos por el DailyAmt almacenado en la celda $G9. La hoja de trabajo resultante se muestra en la captura de pantalla siguiente.

La fórmula de Excel en H9 es la siguiente:

=$G9*((MAX(H$8-$C9,0)-MAX(EOMES(H$8,-1)-$C9,0))-(MAX(H$8-$D9,0)-MAX(EOMES(H$8,-1)-$D9,0))+(EOMES(H$8,0)=EOMES($C9,0)))

Y eso, amigo mío, es una forma de asignar una cantidad en columnas mensuales. Hay muchas otras formas, me encantaría saber cuál prefiere… publique su fórmula en el cuadro de comentarios a continuación.

Archivo de muestra

Descargue el archivo de muestra si desea comprobarlo.

• Asignación (archivo original)
• Asignación2 (esta versión actualizada tiene una hoja que excluye los fines de semana de la asignación)
• Asignación3 (esta versión actualizada tiene una hoja que tiene columnas de asignación diaria)
• Asignación4 (esta versión actualizada tiene una hoja que contiene columnas de asignación mensual)
• Asignación5 (esta versión actualizada tiene una hoja que incluye columnas de asignación anual)
• Asignación6 (esta versión actualizada tiene una hoja que calcula las horas semanales)