Бюджет в Excel: шаблон, формулы и где он ломается

Бюджет в Excel держится на трёх листах: транзакции, справочник категорий и сводка на СУММЕСЛИМН. Собрать всё это — минут двадцать. Считает такая таблица ровно то же, что платные шаблоны с маркетплейса, и работает, пока вы готовы вносить траты руками.

Дальше — колонки, формулы целиком (копируйте прямо из текста), разметка под правило 50/30/20 и список мест, где таблица подводит. Последний раздел стоит прочитать даже тем, кому формулы не нужны. Таблицу редко убивает арифметика — обычно её бросают на третьем месяце.

Какие листы нужны в таблице бюджета

Хватает трёх. «Транзакции» — одна строка на каждую трату или поступление. «Категории» — справочник: название, группа 50/30/20, месячный лимит. «Сводка» — категории по строкам, месяцы по столбцам, суммы считает СУММЕСЛИМН. Четвёртый лист на старте заводить незачем.

Одно правило соблюдайте с первого дня: цифры вводятся только на листе «Транзакции». Сводка ничего не хранит, она считает. Начнёте править суммы прямо в сводке — через месяц перестанете понимать, какому числу верить.

Что записывать на листе «Транзакции»

Пять колонок закрывают почти любой домашний бюджет: Дата, Категория, Сумма, Счёт, Комментарий. Расходы пишем положительными числами, доходы — отдельной категорией «Доход». Можно и минусом, но тогда сводку придётся переворачивать. Чем больше колонок, тем меньше шансов, что вы будете их заполнять.

• Дата — формат даты, а не текст. От неё зависят все месячные срезы.

• Категория — выбираем из выпадающего списка, руками не набираем. Именно тут в самодельных бюджетах начинается бардак.

• Сумма — число. Без пробелов, знака рубля и слов внутри ячейки.

• Счёт — карта, наличные, накопительный. Потом по нему сверяют остаток и находят пропущенные траты.

• Комментарий — свободный текст. Через полгода только он и объяснит, что это была за трата на 14 000 ₽.

Сразу сделайте из диапазона «умную таблицу»: встаньте на шапку с первой строкой данных и нажмите Ctrl+T. На вкладке «Конструктор таблицы» дайте ей имя Транзакции. Такая таблица растёт вниз сама, и формулы ниже будут ссылаться на весь столбец, а не на «диапазон до 500-й строки». Дальше это сэкономит вам несколько вечеров.

Как сделать выпадающий список категорий?

Выделите столбец «Категория» → вкладка Данные → Проверка данных → тип «Список». В поле «Источник» вставьте:

=ДВССЫЛ("Категории[Название]")

Просто =Категории[Название] проверка данных не принимает, отсюда и обёртка ДВССЫЛ (в английской версии INDIRECT). Зато добавили категорию в справочник — она сразу появилась в списке во всех строках, править само правило не надо.

От опечаток спасает ещё и служебный столбец. В первую свободную колонку таблицы «Транзакции»:

=ЕСЛИ(СЧЁТЕСЛИ(Категории!$A:$A;[@Категория])=0;"нет в справочнике";"")

СЧЁТЕСЛИ (COUNTIF) считает, сколько раз значение встречается в справочнике. Ноль — категория написана не так, как в справочнике, и сводка её потеряет. Формула ловит и «Продукты » с лишним пробелом, и «продукты» из чужого файла.

Сводка на одной формуле СУММЕСЛИМН

В сводке строки — категории, столбцы — месяцы. В столбце A названия категорий, в B месячный лимит, в C1:N1 первые числа месяцев (01.01.2026, 01.02.2026 и дальше) с форматом «ммм гггг». Весь прямоугольник заполняет одна формула.

Ячейка C2, дальше тянем вправо и вниз:

=СУММЕСЛИМН(Транзакции[Сумма];Транзакции[Категория];$A2;Транзакции[Дата];">="&C$1;Транзакции[Дата];"<="&КОНМЕСЯЦА(C$1;0))

Та же формула в английском Excel и в Google Таблицах:

=SUMIFS(Транзакции[Сумма];Транзакции[Категория];$A2;Транзакции[Дата];">="&C$1;Транзакции[Дата];"<="&EOMONTH(C$1;0))

Что тут написано: сначала — что суммируем, потом пары «где искать / что искать». $A2 — категория из строки, доллар держит столбец. C$1 — месяц из шапки, доллар держит строку. Из-за этих долларов формулу и можно протянуть на всю сетку. КОНМЕСЯЦА (EOMONTH) сама вернёт последний день месяца, границы вбивать не придётся.

Доход считается той же формулой, просто в A стоит категория дохода. А вот доля категории в доходе месяца (пусть доход лежит в строке 20):

=ЕСЛИОШИБКА(C2/C$20;"—")

Без ЕСЛИОШИБКА (IFERROR) вся правая часть сводки покроется ошибками деления: в месяцах, которые ещё не наступили, доход равен нулю. С ней там будет аккуратное тире.

Тянуть формулы вообще не обязательно — тот же срез соберёт сводная таблица: выделите таблицу «Транзакции» → «Вставка» → «Сводная таблица», в строки перетащите «Категория», в столбцы «Дата» (Excel сам сгруппирует по месяцам), в значения «Сумма». Одна беда: сама она не пересчитывается, после новых строк надо жать «Обновить».

Почему СУММЕСЛИМН перестала считать после вставки строки?

Обычно виноват жёсткий диапазон вроде $C$2:$C$500, а данные уехали за 500-ю строку. Excel такие диапазоны не растягивает и ничего не говорит: цифры просто перестают расти, и замечаешь это через месяц. Лечится ссылкой на умную таблицу (Транзакции[Сумма]) или на весь столбец ($C:$C). Вторая частая причина — даты, которые Excel считает текстом: тогда условие «больше или равно» не срабатывает совсем. Проверить легко: настоящая дата прижимается к правому краю ячейки, текст — к левому.

Разметка 50/30/20 прямо в сводке

Чтобы таблица отвечала не только на «сколько ушло на кафе», добавьте в справочник «Категории» столбец Группа с тремя значениями: нужды, желания, сбережения. Это и есть правило 50/30/20 — 50% дохода на обязательное, 30% на желания, 20% на накопления.

В «Транзакции» добавьте столбец «Группа», он подтянет значение из справочника:

=ЕСЛИОШИБКА(ВПР([@Категория];Категории!$A:$B;2;ЛОЖЬ);"не задана")

Внизу сводки заведите те же три строки и посчитайте знакомой формулой — меняется только столбец условия:

=СУММЕСЛИМН(Транзакции[Сумма];Транзакции[Группа];$A25;Транзакции[Дата];">="&C$1;Транзакции[Дата];"<="&КОНМЕСЯЦА(C$1;0))

Теперь каждый месяц видно, как ваши траты лежат относительно 50/30/20, и пересобирать для этого ничего не нужно. Пропорция тут ориентир. В городе с дорогой арендой «нужды» спокойно перевалят за 50%, и переписывать из-за этого бюджет не надо — лучше посмотреть, что остаётся на всё остальное.

Как подсветить перерасход условным форматированием

Лимит работает, только если таблица сама о нём напоминает. Выделите сетку месяцев (C2:N30) → «Главная» → Условное форматирование → «Создать правило» → «Использовать формулу». Формула пишется по левой верхней ячейке выделения:

=И($B2>0;C2>$B2)

Заливка красная. Условие $B2>0 нужно, чтобы категории без лимита не краснели все разом. Доллары обязательны: без них правило поедет по столбцам и начнёт сравнивать март с февралём. Второе правило на ту же сетку — жёлтая заливка, когда лимит вот-вот кончится:

=И($B2>0;C2>$B2*0,8;C2<=$B2)

Где таблица ломается

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

• Ручной ввод отваливается на втором-третьем месяце — от этого таблицы умирают чаще всего. Первые недели вы вносите каждый чек, потом копится долг в сорок непрочитанных трат, и открывать файл уже неприятно. Формулы тут ни при чём.

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

• Категории расползаются — без выпадающих списков за год набегает «Продукты», «продукты», «Еда» и «Продукты дом». Считается всё как надо, а сводка врёт, и найти это можно только глазами.

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

• Нет курсов валют и котировок — валютные счета и инвестиции придётся оценивать руками. ГУГЛФИНАНСЫ в Google Таблицах и тип данных «Акции» в Excel помогают частично и по российским бумагам регулярно отваливаются.

• Соредакторы ломают файл — открыли доступ семье, и рано или поздно кто-то вставит строку поверх формулы или отсортирует сводку. Частично спасает «Рецензирование» → «Защитить лист»: вводить можно только в колонки транзакций.

• К третьему году файл тормозит — 15–20 тысяч строк, сотни СУММЕСЛИМН на весь столбец, условное форматирование на десяток диапазонов, и каждое нажатие пересчитывает всё. Помогает архив: закрытые годы уносим на отдельный лист или в отдельный файл.

Что делать, если таблица разваливается

Если вы ведёте таблицу больше полугода и не бросаете, дело в гигиене файла. Это чинится за час:

1. Всё в умные таблицы — Ctrl+T на каждом листе с данными. Половина поломок с диапазонами уходит сразу.

2. Именованные диапазоны — «Формулы» → «Диспетчер имён». Формулу с именем вы через год прочитаете, формулу с $C$2:$C$500 — нет.

3. Отдельный лист-справочник — категории, группы 50/30/20, лимиты и счета лежат в одном месте, а в транзакции попадают только выбором из списка.

4. Архив по годам — закрытый год уезжает на отдельный лист, в рабочем остаются 12–24 месяца.

5. Защита листов — от соредакторов и от себя будущего: редактируется только область ввода.

А если таблица брошена третий раз подряд, менять шаблон бесполезно. Вносить каждую трату руками — это дисциплина, которой у большинства людей просто нет, и формулы её не заменят. Тогда стоит посмотреть на сервис, где остатки по счетам вносят раз в месяц, а категории и аналитика считаются сами. Так устроен модуль «Личные финансы» в Lavina Finance — с разбором трат по категориям и периодам. Что вы теряете и что выигрываете при переходе — в сравнении таблицы и сервиса, а как перенести историю без потерь — в инструкции по переезду из таблицы.

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

Частые вопросы

Как посчитать расходы по категории за месяц в Excel?

Формулой СУММЕСЛИМН с тремя условиями: категория, дата не раньше первого числа месяца и не позже последнего. Готовый вид: =СУММЕСЛИМН(Транзакции[Сумма];Транзакции[Категория];$A2;Транзакции[Дата];">="&C$1;Транзакции[Дата];"<="&КОНМЕСЯЦА(C$1;0)), где в $A2 стоит название категории, а в C$1 — первое число месяца. Одна формула, растянутая на сетку «категории × месяцы», закрывает всю сводку.

Чем СУММЕСЛИМН отличается от СУММЕСЛИ?

СУММЕСЛИ берёт одно условие и ждёт диапазон суммирования последним аргументом. СУММЕСЛИМН берёт несколько условий и ставит диапазон суммирования первым. Для бюджета почти всегда нужна СУММЕСЛИМН: условий тут минимум три — категория и две границы месяца. Порядок аргументов у функций разный, так что переделать одну в другую копированием не выйдет.

Как сделать выпадающий список без ошибок в категориях?

Через «Данные» → «Проверка данных» → «Список» с источником =ДВССЫЛ("Категории[Название]"). Список сам подхватит новые категории из справочника. Для страховки добавьте служебный столбец с формулой =ЕСЛИ(СЧЁТЕСЛИ(Категории!$A:$A;[@Категория])=0;"нет в справочнике";"") — она подсветит записи, попавшие в таблицу мимо списка, например при вставке данных из другого файла.

Excel или Google Таблицы для личного бюджета?

Формулы почти совпадают: СУММЕСЛИМН, СЧЁТЕСЛИ и ЕСЛИОШИБКА работают одинаково, вместо КОНМЕСЯЦА в англоязычной версии пишем EOMONTH. Google Таблицы удобнее вести вдвоём и умеют подтягивать курсы через ГУГЛФИНАНСЫ, Excel быстрее на больших файлах и лучше работает офлайн. Ломаются оба в одном и том же месте — на ручном вводе.

Как понять, что таблицу пора менять на сервис?

Три сигнала: вы пропускаете ввод дольше недели и копите «долг» по тратам, у вас больше трёх-четырёх счетов и валютные операции, файл открывается с задержкой. Если при этом вы всё равно возвращаетесь к таблице и ведёте её годами — менять ничего не нужно, хватит умных таблиц и справочника.