Asignar valores por mes y por categoría de gasto

Question

Tengo la siguiente primera tabla como datos. ¿Hay alguna forma de obtener resultados como en la segunda tabla?

Tenga en cuenta que el mes de inicio y el mes de finalización se muestran como mm-aaaa.

Para aclarar los cálculos:

• La segunda tabla tiene una columna para cada mes contenido en cualquiera de los intervalos de eventos.
• Los gastos de cada evento se dividen uniformemente entre los meses de ese evento.

• Aunque el ejemplo muestra que no se utiliza ninguna categoría de gastos para más de un evento, eso puede suceder. En ese caso, los gastos de una determinada categoría de costos se agregarían cada mes.

  Entonces, por ejemplo, supongamos que ambos eventos tuvieron un gasto de publicidad y el del evento A fue de $600. La asignación mensual del evento A sería de $200 porque es un evento de tres meses. Por lo tanto, el monto de publicidad de abril en la tabla 2 sería $200 para el Evento A y $500 para el Evento B, o $700.

Answer 1

Hablando francamente, la primera tabla puede ser adecuada para que la lean los humanos, pero no es adecuada para que la computadora calcule o haga algunas estadísticas. Para obtener la tabla 2, lo ideal es simple: obtener la tabla dividida.para cada mes para cada evento para cada categoría de gasto(y almacenarlos en algunas celdas auxiliares), luego resumirlos en consecuencia. Para obtener el gasto dividido, primero debemos obtener una marca si ese mes determinado se encuentra dentro del rango de tiempo.

Supongo que necesitas doce meses. También asumo que la celda del mes de inicio tiene la fecha del primer día de ese mes (por ejemplo, 2017-04-01 para B2, 04/2017) y, en consecuencia, la celda del mes de finalización tiene la fecha del último día de ese mes (por ejemplo, 2017- 30-06 de C2, 06/2017).

1. Bandera: Utiliza H1hasta S1como fecha para cada mes, es decir, 01/2017 (2017-01-01), 02/2017 (2017-02-01), ..., 12/2017 (2017-12-01), y utiliza izquierda celdas de la columna Hcomo Sbandera de si ese mes está dentro del rango. En H2, utiliza fórmula =AND($B2<=H$1,H$1<=$C2).Advertencia: tenga en cuenta el uso diferente de $en la fórmula para tener la referencia absoluta a las celdas para permitir la copia fácilmente.Significa que si el mes actual (en la fila superior de la columna actual) está entre el mes inicial y el final, devuelve verdadero; de lo contrario, devuelve falso. Luego copie esta fórmula a la derecha hasta la columna Sy cópiela hasta el último de los eventos.

2. Contar: Utiliza la columna Tcomo contador de meses entre los cuales se dividirá el gasto. La fórmula en T2es =COUNTIF(H2:S2,TRUE), luego copie la fórmula.

3. Gasto dividido para cada categoría:Nuevamente, tenga en cuenta el uso de $en la siguiente fórmula.

   3.1 1ra categoría: Utiliza la columna Upara AF(primer grupo de 12 columnas) como gasto dividido para la primera categoría, es decir, "Publicidad"; La fórmula de U2is =IF(H2,$E2/$T2,0)significa que si el indicador de ese mes es verdadero (es decir, ese mes está dentro del rango), devuelve el gasto dividido (gasto anticipado/recuento de meses); de lo contrario, cero. Luego copie a la derecha (hasta la columna AF), copie hacia abajo.

   3.2 2da categoría: Utiliza la columna AGpara AR(segundo grupo de 12 columnas) como gasto dividido para la segunda categoría, es decir, "Ferias y seminarios"; Fórmula de AG2es =IF(H2,$F2/$T2,0). Luego copie hacia la derecha (hasta AR) y copie hacia abajo.

   3.3 3ra categoría: Utiliza la columna ASpara BD(tercer grupo de 12 columnas) como gasto dividido para la tercera categoría, es decir, "Alojamiento"; Fórmula de AS2es =IF(H2,$G2/$T2,0). Luego copie hacia la derecha (hasta BD) y copie hacia abajo.

4. resumirlos: En la tabla dos, para cada mes, sume los gastos correspondientes a todos los eventos.