Перейти к содержимому
Учи.Онлайн Выбрать школу
Статья

VBA для финансиста: какие расчёты в модели и отчётах стоит автоматизировать макросом

Финансисту макрос VBA нужен не для того, чтобы считать, а для того, чтобы гонять готовую модель: подставлять наборы допущений и записывать результаты сценариев, проверять чувствительность ЧПС к каждому драйверу, запускать подбор параметра сразу для десятков строк, собирать план-факт из выгрузок учётной системы по одному шаблону и сохранять отчёт без формул. Сами расчёты остаются в ячейках, где их видит проверяющий. Для работы хватит объектной модели Excel, циклов, именованных диапазонов, понимания режима пересчёта и аккуратной защиты листов.

Автор: редакция Учи.ОнлайнОтветственный редактор: Дмитрий Игнатьев
Содержание
Иллюстрация к статье: VBA для финансиста: какие расчёты в модели и отчётах стоит автоматизировать макросом

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

Что отдать макросу, а что оставить формулам и другим инструментам

VBA среди инструментов финансиста не всегда лучший. Таблица данных уже меняет одну-две вводные и показывает итог, Power Query без кода загружает и чистит выгрузки. Макрос оправдан, когда нужно менять много вводных разом, сохранять несколько показателей или целый отчёт либо работать с файлами.

Задача Чем удобнее решать
Итог при изменении одной-двух вводных Таблица данных
Пять сценариев с сохранением ЧПС и остатка денег Цикл VBA
Загрузка и очистка выгрузки Power Query
Цена безубыточности для 30 позиций Подбор параметра в цикле VBA
Запуск по расписанию без открытия Excel Office Scripts и Power Automate

По сравнению Microsoft, VBA рассчитан на настольный Excel и не подключается к Power Automate, поэтому макрос запускает человек. Office Scripts работают и в браузере, могут запускаться по расписанию, но требуют корпоративной или образовательной лицензии. Для модели на своём компьютере VBA остаётся самым простым вариантом.

Учебный пример: сценарии и чувствительность циклом

Ситуация учебная. Компания решает, строить ли склад. Модель считает ЧПС проекта и минимальный остаток денег. Пять допущений собраны в именованный диапазон Inputs: объём, тариф, доля переменных затрат, ставка, сдвиг запуска. Комитет просит пять сценариев, от пессимистичного до задержки стройки. Вручную это пять раз перебить пять ячеек и скопировать результаты, рискуя забыть вернуть базу. Макрос берёт сценарии из таблицы на листе Scenarios:

Sub RunScenarios()
    Dim r As Long, i As Long, base As Variant
    base = Range("Inputs").Value
    On Error GoTo Restore
    For r = 2 To 6
        For i = 1 To 5
            Range("Inputs").Cells(i, 1).Value = Sheets("Scenarios").Cells(r, i + 1).Value
        Next i
        Application.Calculate
        Sheets("Scenarios").Cells(r, 8).Value = Range("NPV_Project").Value
        Sheets("Scenarios").Cells(r, 9).Value = Range("Min_Cash").Value
    Next r
Restore:
    Range("Inputs").Value = base
    Application.Calculate
End Sub

Имена вместо адресов вроде C7 не дают макросу сломаться, когда кто-то вставит строку на лист вводных. Базовые значения возвращаются даже при ошибке. А явный пересчёт после подстановки нужен на случай ручного режима: без него в таблицу запишутся старые цифры.

Чувствительность считают так же: макрос меняет одно допущение на ±10 % при остальных базовых и записывает ЧПС. Получается таблица для диаграммы «торнадо», где сверху допущение, сильнее всего двигающее результат.

Подбор параметра из кода

Подбор параметра меняет одну ячейку, пока формула в другой не достигнет нужного значения. Для одной строки его запускают мышкой, а цену безубыточности для 30 позиций ассортимента проще найти методом GoalSeek в цикле. Метод возвращает True или False, и это стоит использовать:

For r = 2 To 31
    ok = Sheets("Products").Cells(r, 6).GoalSeek(Goal:=0, ChangingCell:=Sheets("Products").Cells(r, 3))
    If Not ok Then Sheets("Products").Cells(r, 7).Value = "не найдено"
Next r

В столбце F — прибыль по позиции, в C — подбираемая цена. Результат приближённый: выведите рядом прибыль после подбора и проверьте, что она близка к нулю. Найденное значение остаётся в ячейке, поэтому исходные цены заранее сохраните на отдельный лист.

Пользовательские функции: тихий сбой в модели

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

В справке Microsoft по методу Application.Volatile сказано: обычная, неволатильная функция пересчитывается только при изменении её входных переменных. Если функция читает ставку прямо из кода, например через Sheets(«Вводные»).Range(«Ставка»), а не получает её аргументом, Excel не знает об этой связи. Вы меняете ставку, остальная модель пересчитывается, а ячейки с функцией показывают прежний результат — без ошибки и предупреждения. В отчёт уходят цифры двух разных версий допущений.

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

Сборка отчёта из выгрузок

Ежемесячный план-факт удобно делить на две части. Power Query забирает выгрузку оборотов из учётной системы и приводит статьи к справочнику. Макрос обновляет запросы, сверяет контрольную сумму и при расхождении останавливается, а если всё сошлось, копирует отчёт в новую книгу значениями и сохраняет с месяцем в имени. Руководитель получает файл без формул и связей, модель остаётся у финансиста. Фоновое обновление запросов при этом отключите, иначе макрос пойдёт дальше, не дождавшись данных.

Как не сломать модель макросом

Макрос, который пишет в модель, опаснее формулы: его действие нельзя отменить сочетанием Ctrl+Z. Поэтому вводные и расчётные листы защищают, а макросу разрешают писать на них через параметр UserInterfaceOnly метода Protect. В справке Microsoft по Worksheet.Protect предупреждают, что как минимум в прежних версиях Excel после сохранения и повторного открытия книги такая защита становилась полной и для макросов, и метод приходилось вызывать заново. Поэтому надёжнее ставить защиту при каждом открытии книги, в процедуре Workbook_Open.

Режим пересчёта — свойство Application.Calculation, то есть настройка всего приложения, а не одной книги. Макрос, который ради скорости включил ручной режим и упал, оставит в нём и остальные открытые файлы, поэтому возвращайте автоматический режим в обработчике ошибок. Почему макрос из почтового вложения не запускается, разобрано в соседней статье о макросах VBA в Excel.

С чего начать и в каком порядке осваивать

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

  1. Включить вкладку «Разработчик», записать макрос, который меняет одну вводную, и прочитать код.
  2. Объекты Range и Worksheet, именованные диапазоны, чтение и запись значений.
  3. Циклы For и переменные, массив для хранения базовых вводных.
  4. Явный пересчёт, режимы вычислений и обработка ошибок.
  5. Цикл по сценариям, затем анализ чувствительности и подбор параметра в цикле.
  6. Пользовательские функции с аргументами, защита листов и сборка отчёта после Power Query.

Первые три шага можно пройти по встроенной справке за несколько вечеров. Изучать VBA вместе с построением самой модели можно на курсе Skillbox «Финансовое моделирование»: месяц, 11 практических заданий и итоговая модель по отраслевому кейсу со сценариями. Отдельный модуль там знакомит с VBA для подбора параметров и автоматизации повторяющихся задач; темы модуля не раскрыты, их стоит уточнить у школы.

Открыть курс «Финансовое моделирование»

Упражнения и типичные ошибки

Возьмите собственную модель — бюджет отдела или расчёт кредита — и напишите макрос на три сценария, а потом впишите в одну строку сценариев текст вместо числа. Правильный макрос остановится и вернёт базовые вводные. Затем добавьте чувствительность и сверьте одну строку с ручным расчётом.

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

Если нужна длинная программа, где VBA соседствует с Power Query и финансами, посмотрите Mini-MBA «Аналитик 2.0» от SF Education. В ней модуль VBA на 29 уроков и 31,5 часа, Power Query на 17 уроков, финансовый учёт и анализ, корпоративные финансы и базовое финансовое моделирование со сквозным проектом. Сведения о сроке обучения расходятся: девять или десять месяцев, так что его лучше уточнить.

Открыть курс «Mini-MBA: Аналитик 2.0»

Как навык влияет на доход

VBA — не отдельная должность, а один из инструментов экономиста и финансового аналитика. Открытых сопоставимых данных о надбавке именно за него, отделённой от опыта и отрасли, найти не удалось, поэтому чисел здесь не будет.

Деньги приходят через задачи: финансист, который за минуту прогоняет десять сценариев, прямо на заседании комитета отвечает на вопросы «а если», и это приближает его к работе финансового аналитика. Рынок оцените сами: сравните медианы вакансий своей должности и региона с VBA и без него, помня, что это предложения работодателей, а не фактические зарплаты.

Если VBA нужен как часть широкой профессии, его можно освоить на курсе Skillbox «Финансовый менеджер»: восемь месяцев, корпоративные финансы, бюджетирование и финансовая модель, а макросы VBA входят в инструментальный блок вместе с продвинутым Excel и Google Таблицами. Насколько глубоко изучают программирование, стоит уточнить у школы.

Открыть курс «Профессия Финансовый менеджер»

Как выбрать обучение

Перед оплатой проверьте три вещи: есть ли задания, где макрос работает с вашей или учебной моделью, а не только с таблицей продаж; разбирают ли обработку ошибок и режимы пересчёта; проверяет ли код человек. Курс, где VBA — пара уроков в конце, научит записывать макросы, но не управлять моделью.

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