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

Question

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

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

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

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

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

Answer 1

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

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

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

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

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

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