Planejamento orçamentário com Microsoft Excel

Question

Eu tenho uma planilha do Excel que é preenchida regularmente com despesas diárias:

Então, para extrair um relatório sobre a quantidade de dinheiro gasto em cada categoria, simplesmente uso uma tabela dinâmica como abaixo:

Não sei bem como integrar o orçamento previsto neste relatório. Suponha que eu tenha o orçamento planejado armazenado em uma tabela separada:

Como posso integrar perfeitamente este plano no meu relatório para saber quanto foi gasto em cada categoria de acordo com o plano? ou seja, qual é a melhor maneira de obter um relatório como este (tudo em uma tabela):

É possível manipular os campos da tabela dinâmica para obter esse relatório?

Answer 1

use o vlookup para complementar a tabela com o link da categoria.

• crie uma nova tabela [planilha] chamada 'Status' que faça referência ao orçamento planejado (opcional, mas mais fácil)

• criar a primeira coluna com algo como a fórmula da célula A1 é =Planned!A1até A7 [poderia ser maior])

• crie a segunda coluna com:
  • B1 = Gasto
  • B2 a B7, algo como a fórmula da célula b2 é =vlookup(A2;PivotSpent!$A$2:$b$7;false). Normalmente, a tabela dinâmica deve ser classificada por categoria (primeira coluna) para que um vlookup funcione.
• crie a terceira coluna com:
  • C1 = planejado
  • fórmula c2 a C7 como=Planned!b7
• as colunas D e E são base de cálculo na coluna B
  • D: =b2/C2(formato de célula em%) + formatação condicional
  • E: =c2-b2+ e formatação condicional
• adicionar soma na célula inferior

Planede PivotSpendsão os nomes que dou à planilha que contém a tabela com os dados (mas pode ser a mesma e também na mesma planilha que conterá esse novo array). Presumo que a matriz interna esteja localizada desde a célula A1 (adaptar a referência se estiver em outro lugar)

Você pode fazer isso com a tabela dinâmica para ser totalmente dinâmico, mas certamente precisa criar uma planilha temporária de armazenamento de dados com a tabela de origem