Как сделать таблицу доходов и расходов для семейного бюджета в Excel

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

Какой результат будет в итоге?

Анимированная инструкция: как работает таблица доходов и расходов для семейного бюджета в Excel

Содержание

1. Введение
2. Алгоритм создания таблицы
3. Как вести учет доходов и расходов в этой таблице Excel: советы и нюансы
4. Заключение

Введение

У нас уже есть готовое решение для учета доходов и расходов, которое можно использовать сразу. Однако, если вы хотите глубже разобраться в процессе и создать таблицу самостоятельно, эта инструкция станет вашим надежным помощником. Мы пошагово расскажем, как настроить таблицу в Excel, чтобы она была максимально удобной и функциональной. Следуя нашим рекомендациям, вы сможете легко и быстро создать инструмент для управления личным или семейным бюджетом, который будет соответствовать именно вашим потребностям.

Алгоритм создания таблицы

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

Шаг 1. Создаем excel файл и формируем вспомогательный лист

В самом начале после открытия пустого файла нужно создать вспомогательный лист (обычно его называют «Support»), на котором будут располагаться справочники категорий, подкатегорий и иные данные.

1. 1. Создаем новый лист и называем его «Support» или как вам удобно.
  2. Формируем таблицу категорий. В нее вписываем все потенциальные категории, которые могут быть у вас (в последствии их можно будет изменить).
  3. Делаем вторую таблицу подкатегорий, но с уточнениями к каким категориям они относятся. Это необходимо для зависимых выпадающих списков. Можете сделать по аналогии как на картинке ниже, а после изменить.
  4. Создаем третью таблицу с заголовком направление, у которой будет два значения: доходы и расходы. Так как мы предполагаем именно учет движения денег, то логичнее было бы написать «Приход ДС» и «Уход ДС», но для простоты пользования оставим так, как есть сейчас.
  5. После того, как вы сделали таблицы, необходимо их превратить в динамические. Для этого перейдите на любое место таблицы и нажмите сочетание клавиш ctrl + t. В появившемся окне укажите галочку «Таблица с заголовками» и выберите «Ок». Обратите внимание, что они изменят свой цвет на синий – это означает, что таблица стала динамической.
  6. Задаем названия для каждой таблицы. Для этого нажимаем на любое место таблицы и вверху слева должно появиться поле для ввода. Первую таблицу называем «Справочник_Категории», вторую «Справочник_Подкатегории», третью «Справочник_Направления» или как вам удобно.

Шаг 2. Формируем лист для отражения плана

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

1. 1. Создаем новый лист и называем его «Бюджет» или «План» или как вам удобно.
  2. На листе формируем таблицу с заголовками «Дата», «Сумма», «Направление», «Категория», «Подкатегория».
  3. Вносим в таблицу несколько тестовых записей, например, на картинке ниже это три строки плановым доходом по заработной плате и расходами по автомобилю.
  4. После этого делаем таблицу динамической путем нажатия клавиш ctrl + t и задаем ей имя «Бюджет» (по аналогии с пунктом 5 из предыдущего шага).
  5. Создаем выпадающий Список для столбца «Направление».
    - Выделяем весь столбец без заголовка как показано на изображении.
    - Во вкладке «Данные» вверху ищем пункт «Проверка данных» и выбираем его.
    - В открывшемся окне после текста «Разрешить:» в выпадающем списке выбираем «Список».
    - В поле «Источник» вписываем вот эту формулу =ДВССЫЛ("Справочник_Направления[Направление]"). В ней мы ссылаемся на список наших значений, при этом если впоследствии он будет обновляться и дополняться (речь о категориях), то он автоматически подтянет новые значения. Если же вы задавали другие имена, то измените формулу по аналогии в соответствии с ними.
    - Нажимаем на ОК и получаем обновляемый выпадающий список.
  6. По аналогии с пунктом 5 делаем то же самое и со столбцом «Категория». Обязательно выделяем весь столбец без заголовка. Формула в проверке данных будет следующая: =ДВССЫЛ("Справочник_Категории[Категория]")
  7. Делаем выпадающий список для подкатегорий. Здесь отличной идеей будет создать зависимый выпадающий список, то есть чтобы он формировался на основе категории, выбранной слева, чтобы вам удобно было ориентироваться внутри нее. Для этого сначала отсортируем таблицу подкатегорий на вкладке «Support» по категории, а затем по подкатегории (нажимаем на любое место таблицы, выбираем вкладку данные и сортировка). Далее возвращаемся на лист с бюджетом и по аналогии с пунктами 5 и 6 переходим в проверку данных для подкатегорий и вставляем вот эту формулу: =СМЕЩ(Support!$D$1;ПОИСКПОЗ(D2;Support!D:D;0)-1;1;СЧЁТЕСЛИ(Support!D:D;D2);1). Если ваша таблица подкатегорий находится в других столбцах, то измените формулу для них.

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

Шаг 3. Создаем таблицу для учета фактических операций

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

1. 1. Создаем новый лист и называем его «Факт».
  2. Выделяем всю таблицу с листа «План», копируем ее и вставляем на созданный лист. Меняем заголовок «Сумма, план» на «Сумма, факт». Добавляем столбец справа «Комментарий». Вместе с копированием предыдущей таблицы мы получили все настроенные выпадающие списки.
  3. Переназываем таблицу на «Факт».
  4. Делаем тестовые записи фактических операций.

Шаг 4. Создаем подключения к таблицам доходов и расходов

Для того чтобы анализировать план факт доходов и расходов необходимо соединить таблицы между собой. Для этого мы будем использовать функционал Power Query.

1. 1. Нажимаем на любое место таблицы плана, вверху на вкладке «Данные» в самом начале выбираем «Из таблицы/диапазона». Сразу после этого откроется редактор Power Query.
  2. Меняем тип данных столбцов «Дата» и «Сумма, план». Для этого нажимаем на значок слева от заголовка столбца и выбираем в выпадающем списке для Даты «Дата», а для суммы «Десятичное число».
  3. Далее вверху слева нажимаем на «Закрыть и загрузить» (не на саму иконку, а именно на стрелочку) и в выпадающем списке выбираем «Закрыть и загрузить в».
  4. В появившемся окне указываем «Только создать подключение» и нажимаем на ок.
  5. То же самое проделываем с таблицей факта.

Шаг 5. Объединяем таблицы друг с другом

Теперь необходимо объединить таблицы между собой. Для этого используем следующий алгоритм:

1. 1. На вкладке «Данные» открываем выпадающий список из значка «Получить данные», нажимаем на «Объединить запросы» и выбираем «Добавить»
  2. В открывшемся окне указываем, что у нас 2 таблицы и из выпадающих списков выбираем по одной в любой последовательности и нажимаем на ок.
  3. У нас открылся редактор Power Query, в нем ничего делать не нужно, просто нажимаем на выпадающий список из значка «Закрыть и загрузить» и выбираем «Закрыть и загрузить в».
  4. В появившемся окне указываем «Отчет сводной таблицы» и «Новый лист», нажимаем на ок.

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

Шаг 6. Настраиваем поля сводной таблицы

Из предыдущего шага мы получили пустую сводную таблицу, которую необходимо настроить:

1. 1. Первым делом после нажатия на любое место сводной таблицы должны появиться поля для настройки. Если они не появились, то вверху выберите вкладку «Анализ сводной таблицы» и «Список полей».
  2. В открывшейся справа окне будет находиться список всех полей, которые доступны для вывода и анализа.
  3. Перетаскиваем текстовые поля Направление, Категория и Подкатегория (по желанию) в блок «Строки», тем самым создавая иерархию сводной таблицы.
  4. Перетаскиваем поля плана и факта в блок «Значения». Меняем названия и форматы чисел на удобные для вас.

На выходе получили настроенную сводную таблицу для анализа. Однако необходимо обработать поле с датой, чтобы анализировать определенные периоды – это мы сделаем на шаге с визуализацией.

Шаг 7. Визуализируем данные

Для хорошего восприятия и анализа необходимо визуализировать данные из таблиц.

1. 1. Нажимаем на любое место сводной таблицы и открываем вкладку конструктор. Там выбираем любой стиль для сводной, который вам понравится.
  2. Во вкладке анализ сводной таблицы нажимаем на кнопку «Вставить временную шкалу», указываем галочку напротив поля с датой и кликаем на ок. После этого у вас добавится фильтр по дате в виде временных промежутков, с помощью которых довольно удобно выбирает периоды. Вы также можете изменить стиль шкалы, нажав на нее и выбрав вкладку вверху «Временная шкала», далее стиль.
  3. Теперь создаем диаграммы доходов и расходов. Для каждой диаграммы нам потребуется «Своя» сводная таблица. Для этого выделяем всю таблицу и копируем ее на лист «Support» где-нибудь справа от справочников. В новой таблице перетаскиваем поле «Направление» в блок с фильтрами и в них выбираем только «Доходы». Далее нажимаем на любое место сводной и наверху выбираем вкладку «Вставка» и вставляем диаграмму, после чего вырезаем ее на основной лист. Настраиваем стили (заливку, контур, цвета) под свой вкус. Также делаем и для расходов (можно скопировать просто предыдущую сводную таблицу для доходов и выбрать там только расходы).
  4. Располагаем элементы так, как вам удобно.

Готово. На выходе получаем полноценную таблицу для учета доходов и расходов (бюджетных и фактических). После внесения данных в исходные таблицы на вкладке «Данные» в Excel нажимаем на кнопку «Обновить все» и значения в сводных и диаграммах обновятся.

Как вести учет доходов и расходов в этой таблице Excel, советы и нюансы

Несколько слов о том, как заполнять таблицы и вести учет.

1. 1. В начале ведения учета хорошей идей будет где-нибудь справа от таблицы факта в любой ячейке указать сумму входных остатков денежных средств, а также в соседней ячейке сколько денег осталось согласно данным из таблицы (для этого в формуле просто введите сложение ячейки входных остатков и сумму по полю «Сумма, факт». После этого вам легко будет проводить сверку остатков. Например, вы заполнили таблицу на вечер текущего дня, фактически денег у вас в наличии 122 000 рублей. А по расчетам 125 000 рублей, то есть возникает вопрос что что-то не записали в таблицу и нужно искать что именно.
  2. Когда вы делаете бюджет (план) движения денег, то в поле с датами можно указывать первое число каждого месяца, например, 01.02.2025, так как аналитика в основном всегда ведется по периодам минимум месяц и дни не так важны + не всегда точно можно знать в какой из дней вы понесете тот или иной расход.
  3. Планы можно заполнить как на месяц, так и на год вперед, чтобы потом их просто корректировать, если были какие-то существенные изменения.
  4. В категории и подкатегории можно добавить «Долги» и «Инвестиции», чтобы видеть аналитику по ним.
  5. Поле «Комментарий» можно использовать в таблице фактических операций для дополнительной аналитики и выводить потом это в иерархию сводных. Например, кому вы давали в долг или на что инвестировали.
  6. Через вычисляемое поле сводной таблицы можно добавить поля с абсолютным и процентными отклонениями факта от плана. Нажимаем на любое место сводной, вверху переходим во вкладку «Анализ сводной таблицы», далее «Поля, элементы и наборы», «Вычисляемое поле» и в строке с формулой пишем ='Сумма, факт'- 'Сумма, план', тем самым добавляя новое поле в таблицу. Перетаскиваем его в значения и настраиваем формат. По той же схеме можно рассчитать процентное отклонение =('Сумма, факт'- 'Сумма, план')/ 'Сумма, план'.

Заключение

Теперь вы знаете, как всего за несколько простых шагов создать готовый файл в Excel для учета доходов и расходов. Следуя данной инструкции, вы сможете легко контролировать свои финансы, анализировать траты и планировать бюджет.

Если у вас возникнут вопросы на любом этапе, не стесняйтесь обращаться к нам в Telegram — мы с радостью поможем вам бесплатно!

' />

8 февраля, 2025
Нет комментариев