Alocar valores por mês e por categoria de gastos

Question

Eu tenho a primeira tabela a seguir como dados. Existe alguma maneira de obter resultado como na segunda tabela.

Por favor, note que o mês inicial e final do mês são mostrados como mm-aaaa.

Para esclarecer os cálculos:

• A segunda tabela tem uma coluna para cada mês contida em qualquer um dos intervalos de eventos.
• Cada despesa do evento é dividida uniformemente ao longo dos meses para esse evento.

• Embora o exemplo mostre que nenhuma categoria de despesa está sendo usada para mais de um evento, isso pode acontecer. Nesse caso, as despesas para uma determinada categoria de custo seriam agregadas a cada mês.

  Assim, por exemplo, os dois eventos tiveram uma despesa de publicidade, e o Evento A foi de $ 600. A alocação mensal do evento A seria de US $ 200, porque é um evento de três meses. Assim, o valor de publicidade de abril na tabela 2 seria de US $ 200 para o Evento A e de US $ 500 para o Evento B, ou US $ 700.

Answer 1

Falando francamente, a primeira tabela pode ser adequada para leitura humana, mas não é adequada para o computador calcular ou fazer algumas estatísticas. Para obter a tabela 2, o ideal é simples - obter o dividido para cada mês para cada evento para cada despesa de categoria (e armazená-los em algumas células auxiliares) e, em seguida, somar de acordo. Para obter a despesa dividida, precisamos primeiro obter um sinalizador se esse determinado mês cair no intervalo de tempo.

Eu suponho que você precisa de doze meses. Eu também presumo que a célula para o mês de início tem a data do primeiro dia do mês (por exemplo, 2017-04-01 para B2 , 04/2017) e, portanto, a célula do mês final tem a data do último dia do mês ( por exemplo, 2017-06-30 para C2 , 06/2017).

1. Sinalizador : usa H1 a S1 como data para cada mês, por exemplo, 01/2017 (2017-01-01), 02/2017 (2017-02-01) ), ..., 12/2017 (2017-12-01) e usa as células à esquerda da coluna H to S como sinalizador de se esse mês está no intervalo. Em H2 , usa a fórmula =AND($B2<=H$1,H$1<=$C2) . Ressalva: observe o uso diferente de $ na fórmula para ter a referência absoluta às células para permitir a cópia facilmente. Isso significa que se o mês atual (na linha superior da coluna atual) estiver entre o início e final do mês, retorna verdadeiro, caso contrário, retorna falso. Em seguida, copie essa fórmula para a direita até a coluna S e copie até o último dos eventos.

2. Contagem : usa a coluna T como contador de meses, entre os quais a despesa a ser dividida. A fórmula em T2 é =COUNTIF(H2:S2,TRUE) e, em seguida, copie a fórmula para baixo.

3. Despesa dividida para cada categoria: Novamente, observe o uso de $ na fórmula a seguir.

   3.1 1a categoria : Usa a coluna U a AF (primeiro grupo de 12 colunas) como despesa dividida para a primeira categoria, ou seja, "Anúncio"; A fórmula de U2 é =IF(H2,$E2/$T2,0) , significa que se o sinalizador desse mês for verdadeiro (ou seja, esse mês estiver dentro do intervalo), devolve a despesa dividida (despesa adv. / Contagem de meses), caso contrário, zero. Em seguida, copie para a direita (até a coluna AF ), copie.

   3.2 2ª categoria : Usa a coluna AG a AR (2º grupo de 12 colunas) como despesa dividida para a 2ª categoria, ou seja, "Feiras e Seminários"; Fórmula de AG2 é =IF(H2,$F2/$T2,0) . Em seguida, copie para a direita (até AR ) e copie.

   3.3 3ª categoria : Usa a coluna AS a BD (3º grupo de 12 colunas) como despesa dividida para a 3ª categoria, ou seja, "Alojamento"; Fórmula de AS2 é =IF(H2,$G2/$T2,0) . Em seguida, copie para a direita (até BD ) e copie.

4. Resuma : na tabela dois, para cada mês, some a despesa correspondente para todos os eventos.