Планирование бюджета с Microsoft Excel

485
Meysam

У меня есть лист Excel, который регулярно заполняется ежедневными расходами:

Планирование бюджета с Microsoft Excel

Затем, чтобы извлечь отчет о сумме, потраченной на каждую категорию, я просто использую сводную таблицу, как показано ниже:

Планирование бюджета с Microsoft Excel

Я не уверен, как интегрировать запланированный бюджет в этот отчет. Предположим, у меня запланированный бюджет хранится в отдельной таблице:

Планирование бюджета с Microsoft Excel

Как я могу легко интегрировать этот план в свой отчет, чтобы я мог знать, сколько было потрачено в каждой категории в соответствии с планом? т.е. как лучше всего получить такой отчет (все в одной таблице):

Планирование бюджета с Microsoft Excel

Можно ли манипулировать полями сводной таблицы, чтобы получить такой отчет?

1

1 ответ на вопрос

1
NeronLeVelu

используйте vlookup, чтобы дополнить таблицу ссылкой на категорию.

  • создайте новую таблицу [лист] под названием «Статус», которая ссылается на запланированный бюджет (необязательно, но проще)

  • создать первый столбец с чем-то вроде формулы ячейки A1 =Planned!A1до A7 [может быть больше])

  • создать второй столбец с:
    • B1 = потрачено
    • B2-B7 что-то вроде формулы ячейки b2 =vlookup(A2;PivotSpent!$A$2:$b$7;false). Обычно сводная таблица должна быть отсортирована по категории (первый столбец), чтобы vlookup работал.
  • создать третий столбец с:
    • C1 = запланировано
    • C2 по C7 формула, как =Planned!b7
  • столбцы D и E являются расчетной базой для столбца B
    • D: =b2/C2(формат ячейки в%) + условное форматирование
    • E: =c2-b2+ и условное форматирование
  • добавить сумму в нижней ячейке

Planedи PivotSpendэто имя, которое я даю листу, который содержит таблицу с данными (но может быть таким же и также на том же листе, что и тот, который будет содержать этот новый массив). Я предполагаю, что внутри массива расположены, так как ячейка A1 (адаптировать ссылку, если где-то еще)

Вы можете сделать это с помощью сводной таблицы, чтобы она была полностью динамичной, но, безусловно, необходимо создать временную таблицу хранения данных с исходной таблицей.

Похожие вопросы