От ручного ввода к структурированным данным: создаем формулу в Excel с помощью нейросети

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

В статье разберем алгоритмы генерации формул в Excel через нейросети Алиса АI от Яндекса и ГигаЧат от Сбера (читатели могут применить нейросети, которые используют или планируют использовать), покажем примеры с цифрами и таблицами, рассмотрим типовые ошибки создания формул в Excel с помощью нейросети и способы их предотвращения.

ГЕНЕРАЦИЯ ФОРМУЛ ПЛТ, ОСПЛТ, ПРПЛТ ЧЕРЕЗ НЕЙРОСЕТЬ АЛИСА АI

Рассмотрим типовую задачу ПЭО: расчет графика погашения кредита для инвестиционного проекта. Исходные данные:

• сумма кредита — 3 000 000 руб.;

• годовая процентная ставка — 15 %;

• срок — 7 месяцев;

• условие — аннуитетный платеж;

• дата выдачи — 01.01.2027.

Цель — сформировать в Excel таблицу графика платежей с разбивкой на погашение основного долга и процентов по каждому месяцу. Для этого потребуются три ключевые функции:

• ПЛТ — ежемесячный платеж;
• ОСПЛТ — погашение основного долга за период;
• ПРПЛТ — проценты за период.

Функция ПЛТ(ставка; кпер; пс; [бс]; [тип]) — эта финансовая функция рассчитывает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки.

Аргументы функции ПЛТ:

• Ставка — обязательный аргумент. Процентная ставка по ссуде;

• Кпер — обязательный аргумент. Общее число выплат по ссуде;

• Пс — обязательный аргумент. Приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой;

• Бс — необязательный аргумент. Значение будущей стоимости, то есть желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается значение 0 (например, значение будущей стоимости для займа равно 0);

• Тип — необязательный аргумент. Число 0 или 1, обозначающее, когда должна производиться выплата.

Функция ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип]) — рассчитывает величину платежа в погашение основной суммы по кредиту за данный период на основе постоянных периодических платежей и постоянной процентной ставки.

Аргументы функции ОСПЛТ:

• Ставка — обязательный аргумент. Процентная ставка за период;

• Период — обязательный аргумент. Период: значение должно находиться в диапазоне от 1 до «кпер»;

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

• Пс — обязательный аргумент. Приведенная (текущая) стоимость, то есть общая сумма, которая на данный момент равноценна ряду будущих платежей;

• Бс — необязательный аргумент. Значение будущей стоимости, то есть желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается значение 0 (например, значение будущей стоимости для займа равно 0);

• Тип — необязательный аргумент. Число 0 или 1, обозначающее, когда должна производиться выплата.

Функция ПРПЛТ(ставка; период; кпер; пс; [бс]; [тип]) — рассчитывает сумму платежей по процентам для кредита за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.

Аргументы функции ПРПЛТ:

• Ставка — обязательный аргумент. Процентная ставка за период;

• Период — обязательный аргумент. Период, для которого требуется найти платежи по процентам; число в интервале от 1 до «кпер»;

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

• Пс — обязательный аргумент. Приведенная к текущему моменту стоимость, то есть общая сумма, которая на текущий момент равноценна ряду будущих платежей;

• Бс — необязательный аргумент. Значение будущей стоимости, то есть желаемого остатка средств после последней выплаты. Если значение «бс» опущено, предполагается, что оно равно 0 (будущее значение кредита, например, равно 0);

• Тип — необязательный аргумент. Число 0 или 1, обозначающее срок выплаты. Если аргумент «тип» опущен, предполагается значение 0.

Приступим к генерации формул.

Шаг 1. Генерация формулы ПЛТ через нейросеть

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

«Напиши формулу Excel для расчета аннуитетного платежа по кредиту. Сумма кредита в ячейке B2, годовая ставка в B3, срок в месяцах в B4. Используй функцию ПЛТ. Результат должен быть положительным числом (без минуса).

Дай пояснение аргументов и пример расчета при значениях: B2 = 3 000 000, B3 = 15 %, B4 = 7».

Нейросеть выводит формулу: =-ПЛТ(B3/12;B4;B2).

Вставим формулу из нейросети в таблицу Excel (рис. 1).

Пояснение аргументов:

• ставка B3/12 — месячная процентная ставка (годовая ставка делится на 12);

• кпер B4 — общее количество периодов (месяцев);

• пс B2 — текущая стоимость (сумма кредита);

• [бс] — не указан. Означает будущую стоимость после последнего платежа. По умолчанию принимается равной 0 (кредит полностью погашен);

• [тип] — не указан. По умолчанию равен 0 — платежи в конце месяца (обычный аннуитет). Если нужно считать платежи в начале месяца, укажите 1.

Знак «минус» перед ПЛТ необходим, чтобы результат был положительным: Excel трактует сумму кредита как отток средств, поэтому выводит отрицательное значение.

При указанных значениях формула дает ежемесячный платеж 450 266,16 руб.

Шаг 2. Генерация формул ОСПЛТ и ПРПЛТ

Для построения графика платежей по месяцам нужны функции ОСПЛТ (основной долг) и ПРПЛТ (проценты).

Промпт для нейросети:

«Дай формулы Excel для расчета погашения основного долга и процентов за конкретный месяц по кредиту. Используй функции ОСПЛТ и ПРПЛТ.

Аргументы: ставка в B3 (годовая), срок в B4 (месяцы), сумма кредита в B2. Номер текущего периода в ячейке A14. Формулы должны возвращать положительные значения.

Приведи пример расчета для первого месяца при тех же значениях».

Ответ нейросети Алиса АI приведен на рис. 2.

Формулы, которые сформировала нейросеть, копируем в таблицу Excel:

=-ОСПЛТ(B3/12;A14;B4;B2);

=-ПРПЛТ(B3/12;A14;B4;B2).

Пояснение аргументов (одинаково для обеих функций):

• ставка — B3/12. Годовая ставка из B3 делится на 12, чтобы получить месячную ставку;

• период — A14. Номер текущего месяца (1, 2, 3, …). Именно здесь вы меняете номер месяца, чтобы получить расчет для любого периода;

• кпер — B4. Общее количество месяцев кредита;

• пс — B2. Сумма кредита (приведенная стоимость);

• [бс] и [тип] не указаны. По умолчанию будущая стоимость = 0, тип платежа = 0 (в конце месяца). Если у вас платежи в начале месяца, добавьте в конец формулы 1.

Совет для управленческой отчетности и Excel-моделей: зафиксируйте ссылки на параметры кредита, чтобы формулы корректно копировались вниз по графику платежей:

=-ОСПЛТ($B$3/12; A14; $B$4; $B$2) и =-ПРПЛТ($B$3/12; A14; $B$4; $B$2).

Тогда при протягивании формул вниз будет меняться только номер периода (A14, A15 и т. д.), а параметры кредита останутся неизменными.

Шаг 3. Построение полного графика платежей в Excel

На основе полученных формул строим таблицу на 7 месяцев (таблица 1). В столбце «Период» — номера месяцев от 1 до 7. В соседних столбцах — формулы с абсолютной ссылкой на параметры кредита и относительной — на номер периода.

Итоговая сумма выплат — 3 151 863,14 руб. Переплата по процентам — 151 863,14 руб.

Практические аспекты для экономиста при создании формул в Excel с помощью нейросети:

1) контроль знаков. Если вы строите график платежей, лучше один раз зафиксировать логику: либо везде использовать минус перед функциями, либо вводить сумму кредита как отрицательное число;

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

3) валидация модели. Простой чек-лист для проверки:

• сумма всех платежей по основному долгу должна равняться сумме кредита — 3 млн руб. (см. таблицу 1);

• сумма всех процентов должна соответствовать переплате по кредиту — 151 863,14 руб.;

• остаток долга на конец срока должен быть равен 0 или очень близок к нему с учетом округления;

• формула остатка долга:

предыдущий остаток — основной долг текущего месяца.

3 000 000,00 руб. – 412 766,16 руб. = 2 587 233,84 руб.

Такой график легко встроить в Бюджет движения денежных средств (БДДС) планово-экономического отдела, а формулы — защитить от случайных изменений и снабдить комментариями.