5 лайфхаков в Excel перед защитой бюджета 📌 Понимаем, что Excel для… — Финансист

Финансист@finctrl · 8.1K subscribers Open in TelegramOpen

5 лайфхаков в Excel перед защитой бюджета

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

Собрали пять формул и контрольных приёмов, которые помогут быстро проверить текущую модель в Excel.

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

Сверить детализацию с итогом

Допустим, в B5:B48 находится детализация, в B50 стоит итог по этому же блоку. В свободную ячейку B51 вставьте:

=СУММ(B5:B48)-B50

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

Учитывайте скрытые или отфильтрованные строки. Функция СУММ включает их в расчёт. ПРОМЕЖУТОЧНЫЕ.ИТОГИ при некоторых настройках может исключать.

Найти повторяющиеся ключи

Этот пункт подходит для выгрузок, где ключевые признаки вынесены в отдельные колонки. Допустим, в A:E находятся юрлицо, ЦФО, статья, месяц и версия бюджета. В свободную ячейку F2 рядом с первой строкой данных вставьте:

=СЧЁТЕСЛИМН($A$2:$A$5000;A2;$B$2:$B$5000;B2;$C$2:$C$5000;C2;$D$2:$D$5000;D2;$E$2:$E$5000;E2)

Протяните формулу вниз. Значение больше единицы показывает повтор сочетания юрлица, ЦФО, статьи, месяца и версии.

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

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

Поймать пустые драйверы и ошибки

Допустим, в строке расчёта цена стоит в C2, объём в D2, себестоимость единицы в E2. В свободную ячейку F2 вставьте:

=ЕСЛИОШИБКА(ЕСЛИ(ИЛИ(C2="";D2="";E2="");"ПРОВЕРИТЬ";"");"ПРОВЕРИТЬ")

Протяните формулу по нужным строкам. Метка «ПРОВЕРИТЬ» появится, если драйвер не заполнен или в одной из ячеек есть ошибка.

Нулевое значение проверка не считает проблемой. Это важно для сценариев с нулевым объёмом или ценой.

Сравнить текущую и предыдущую версии

Допустим, в C2 стоит сумма из предыдущей версии, в D2 из текущей. В свободную ячейку E2 вставьте:

=ЕСЛИОШИБКА(ЕСЛИ(ИЛИ(C2="";D2="");"ПРОВЕРИТЬ";ЕСЛИ(C2=0;"НЕТ БАЗЫ";D2/C2-1));"ПРОВЕРИТЬ")

Формула покажет:

• «ПРОВЕРИТЬ», если нет текущего или предыдущего значения;
• «НЕТ БАЗЫ», если предыдущая версия равна нулю;
• отклонение текущей версии от предыдущей, если обе суммы заполнены.

Выделите результаты, например E2:E1000. Затем настройте два правила через «Главная» → «Условное форматирование» → «Правила выделения ячеек»:

1. «Больше» 5%;
2. «Меньше» -5%.

Так вы сразу увидите строки с заметным ростом или снижением, для которых потребуется объяснение.

Пересчитать валовую маржу

Допустим, в B5:B48 указана выручка по направлениям или категориям, в C5:C48 себестоимость. В свободную ячейку рядом с итогом вставьте:

=ЕСЛИОШИБКА((СУММ(B5:B48)-СУММ(C5:C48))/СУММ(B5:B48);"ПРОВЕРИТЬ")

Отформатируйте ячейку как процент. Формула покажет валовую маржу выбранного блока на основе общей выручки и себестоимости.

Сравните результат с маржой в своде. Если себестоимость в модели хранится со знаком минус, замените вычитание перед второй функцией СУММ на сложение.