Планирование бюджета с Microsoft Excel
У меня есть лист Excel, который регулярно заполняется ежедневными расходами:
Затем, чтобы извлечь отчет о сумме, потраченной на каждую категорию, я просто использую сводную таблицу, как показано ниже:
Я не уверен, как интегрировать запланированный бюджет в этот отчет. Предположим, у меня запланированный бюджет хранится в отдельной таблице:
Как я могу легко интегрировать этот план в свой отчет, чтобы я мог знать, сколько было потрачено в каждой категории в соответствии с планом? т.е. как лучше всего получить такой отчет (все в одной таблице):
Можно ли манипулировать полями сводной таблицы, чтобы получить такой отчет?
1 ответ
Используйте vlookup, чтобы дополнить таблицу ссылкой на категорию.
создать новую таблицу [лист] под названием "Статус", которая ссылается на запланированный бюджет (необязательно, но проще)
создать первый столбец с чем-то вроде формулы ячейки A1
=Planned!A1до А7 [может быть больше])- создать второй столбец с:
- 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+ и условное форматирование
- D:
- добавить сумму в нижней ячейке
Planed а также PivotSpend это имя, которое я даю листу, который содержит таблицу с данными (но может быть таким же и также на том же листе, что и тот, который будет содержать этот новый массив). Я предполагаю, что массивы внутри расположены с ячейки A1 (если есть ссылка, используйте ссылку)
Вы можете сделать это с помощью сводной таблицы, чтобы она была полностью динамичной, но, безусловно, необходимо создать временную таблицу хранения данных с исходной таблицей.