Alocar valores por mês e por categoria de gastos

Question

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

Observe que o mês inicial e o mês final são mostrados como mm-aaaa.

Para esclarecer os cálculos:

• A segunda tabela possui uma coluna para cada mês contida em qualquer um dos intervalos de eventos.
• Cada despesa de evento é dividida igualmente entre os meses desse evento.

• Embora o exemplo não mostre nenhuma categoria de despesas sendo usada para mais de um evento, isso pode acontecer. Nesse caso, as despesas para uma determinada categoria de custos seriam agregadas todos os meses.

  Então, por exemplo, digamos que ambos os eventos tiveram despesas com publicidade e o Evento A foi de US$ 600. A alocação mensal do Evento A seria de US$ 200 porque é um evento de três meses. Portanto, o valor da publicidade de abril na tabela 2 seria de US$ 200 para o Evento A e 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 cálculo ou estatística por computador. Para obter a tabela 2, o ideal é simples - obter a divididapara cada mês para cada evento para cada categoria de despesa(e armazene-os em algumas células auxiliares) e, em seguida, resumi-los de acordo. Para obter a despesa dividida, precisamos primeiro obter um sinalizador se aquele determinado mês estiver dentro do intervalo de tempo.

Presumo que você precise de doze meses. Presumo também que a célula para o mês inicial tenha a data do primeiro dia desse mês (por exemplo, 01/04/2017 para B2, 04/2017) e, consequentemente, a célula para o mês final tenha a data do último dia desse mês (por exemplo, 2017- 30/06 para C2, 06/2017).

1. Bandeira: usa H1como S1data para cada mês, ou seja, 01/2017 (01/01/2017), 02/2017 (01/02/2017), ..., 12/2017 (01/12/2017) e usa a esquerda células da coluna Hcomo Ssinalizador se aquele mês estiver dentro do intervalo. Em H2, usa fórmula =AND($B2<=H$1,H$1<=$C2).Advertência: observe o uso diferente de $na fórmula para ter a referência absoluta às células para permitir a cópia facilmente.Significa que se o mês atual (na linha superior da coluna atual) estiver entre o mês inicial e final, retornará verdadeiro, caso contrário, retornará falso. Em seguida, copie esta fórmula para a direita até a coluna Se copie até o último dos eventos.

2. Contar: Utiliza coluna Tcomo contador de meses entre os quais a despesa será dividida. A fórmula T2é =COUNTIF(H2:S2,TRUE)e copie a fórmula para baixo.

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

   3.1 1ª categoria: Utiliza a coluna Uto AF(1º grupo de 12 colunas) como despesa dividida para a 1ª categoria, ou seja, “Publicidade”; A fórmula de U2is =IF(H2,$E2/$T2,0), significa que se o sinalizador daquele mês for verdadeiro (ou seja, aquele mês está no intervalo), retorna a despesa dividida (despesa avançada/contagem de meses), caso contrário, zero. Em seguida, copie para a direita (até a coluna AF) e copie para baixo.

   3.2 2ª categoria: Utiliza a coluna AGpara 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 para baixo.

   3.3 3ª categoria: Utiliza a coluna ASa 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 para baixo.

4. Resuma-os: Na tabela dois, para cada mês, some as despesas correspondentes para todos os eventos.