Как написать формулу Excel с помощью нейросети: три примера с проверкой

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

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

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

Что сообщить нейросети, чтобы получить работающую формулу

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

Укажите версию Excel, язык имён функций и разделитель аргументов. Ниже используем русские имена с ;, а рядом даём английские варианты с ,. Разделитель зависит от настроек: язык функций сам по себе его не определяет. Microsoft отдельно разбирает такие проблемы в руководстве по исправлению формул.

Скачать учебную таблицу XLSX. На листе «Заказы» находятся исходные данные и расчёты, на листе «Формулы» показаны варианты для копирования. Есть и CSV с исходными данными. CSV не хранит формулы и типы ячеек; после импорта проверьте числовые значения и даты. Для повторения примера удобнее XLSX.

В файле данные занимают строки 2–21. Столбцы A–G содержат ID заказа, сумму, скидку, возврат, срок оплаты, дату оплаты и сегмент. В H будем рассчитывать стоимость заказа после скидки и возврата, в I отмечать просрочку. Дата проверки находится в L2: 01.11.2026.

Для всех примеров действуют одинаковые правила:

• В C скидка хранится обычным числом: 10 означает 10 процентов. Это не ячейка со значением 10%.
• B и D содержат рубли. Сумма заказа обязательна; пустые C и D считаем нулём.
• E всегда содержит дату. Пустая F означает отсутствие полной оплаты. Частичные платежи в этом примере не учитываем.
• В F записаны только фактические оплаты не позже даты проверки. Будущую плановую дату туда не ставим.
• Каждый заказ относится ровно к одному сегменту. Итоговая стоимость заказа не может быть отрицательной.

H показывает стоимость заказа, а не остаток долга. Поэтому общая сумма H включает и оплаченные заказы. Их статус учитываем отдельно.

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

Пример 1. Рассчитываем итоговую сумму заказа

Начнём с суммы после скидки и возврата. Для B2 = 1 000, C2 = 10 и D2 = 100 ручной расчёт даёт 800 ₽: скидка составляет 100 ₽, затем вычитаем ещё 100 ₽ возврата.

Помоги написать формулу для Excel 2021.
Имена функций русские, разделитель аргументов ;.

B2: сумма заказа в рублях.
C2: скидка обычным числом. Например, 10 означает 10%.
D2: возврат в рублях.
Пустые C2 и D2 считаем нулём. B2 всегда заполнена числом.

Нужна формула для H2: применить скидку к B2,
затем вычесть D2. Отрицательный итог заменить на 0.

Для B2=1000, C2=10, D2=100 ожидаю 800.
Дай формулу, объяснение и случаи для проверки.
Объясни, как скопировать её до H21.

Формула для H2:

=МАКС(0;B2*(1-C2/100)-D2)

Английский вариант с запятыми:

=MAX(0,B2*(1-C2/100)-D2)

C2/100 превращает число 10 в долю 0,1. Умножение применяет скидку, затем вычитается возврат. МАКС выбирает большее из двух значений: полученный итог или ноль.

Вставьте формулу в H2 и скопируйте до H21. Адреса B2, C2 и D2 будут меняться вместе со строкой. Перед копированием на рабочую таблицу проверьте крайние случаи:

Заказ	Сумма, ₽	Скидка	Возврат, ₽	Ожидаемая H
Z-001	1 000	10	100	800
Z-002	1 000	0	0	1 000
Z-003	1 000	пусто	пусто	1 000
Z-004	500	20	500	0

В последней строке после скидки остаётся 400 ₽, а возврат равен 500 ₽. По нашему правилу результат равен нулю. Для вашей компании правило может быть другим, поэтому его нужно явно указать в запросе.

Если C2 уже содержит 10%, Excel хранит долю 0,1. В таком случае нужна формула =МАКС(0;B2*(1-C2)-D2) без деления на 100. Замена только внешнего формата ячейки не равна изменению исходного числа: сообщайте модели фактическое значение.

Пример 2. Находим просроченные оплаты

Теперь попросите нейросеть отметить заказы, по которым на дату L2 ещё нужна оплата. В продолжении того же чата уточните:

Добавь формулу для I2 и объясни её.
H2 содержит стоимость заказа после скидки и возврата.
E2 содержит срок оплаты, F2 дату полной оплаты или пустую ячейку.
Дата проверки находится в L2: 01.11.2026.

Ответ «Да» нужен, если H2 больше 0, F2 пуста
и срок E2 строго раньше L2. Во всех остальных случаях «Нет».
В день срока оплаты просрочки ещё нет.
Закрепи ссылку на L2 для копирования до I21.

Формула для I2:

=ЕСЛИ(И(H2>0;F2="";E2<$L$2);"Да";"Нет")

Английский вариант:

=IF(AND(H2>0,F2="",E2<$L$2),"Да","Нет")

И проверяет три условия одновременно. Символы $ закрепляют L2: при копировании вниз дата проверки останется в той же ячейке. Ссылки H2, F2 и E2 будут меняться.

На контрольную дату получаем:

• Z-001: «Да». Срок был 31 октября, полной оплаты нет, сумма положительная.
• Z-002: «Нет». Срок 1 ноября совпадает с датой проверки. Условие строгого сравнения ещё не выполнено.
• Z-003: «Нет». В F есть дата полной оплаты.
• Z-004: «Нет». Стоимость после возврата равна нулю.

Во всех 20 строках просрочены три заказа: Z-001, Z-007 и Z-015. Их сумма составляет 9 100 ₽. Оплата после срока, уже полученная к дате проверки, здесь не считается текущей задолженностью. Для истории задержек понадобится отдельный расчёт.

Используем фиксированную дату, чтобы проверка повторялась с тем же результатом. Если вашей задаче нужен ежедневно обновляемый статус, можно обсудить с моделью замену L2 на СЕГОДНЯ(), но контрольные значения тогда будут зависеть от дня открытия файла.

Пример 3. Считаем сумму по клиентскому сегменту

Попросите сложить H только для сегмента, указанного в K6. В учебном файле K6 содержит «Розница», K7 «Малый бизнес», K8 «Корпоративный».

В L6 используйте:

=СУММЕСЛИМН($H$2:$H$21;$G$2:$G$21;K6)

Английский вариант:

=SUMIFS($H$2:$H$21,$G$2:$G$21,K6)

Первый диапазон задаёт суммы, второй содержит сегменты, а K6 задаёт условие отбора. Диапазоны закреплены и охватывают одинаковые строки. Когда копируете формулу из L6 в L7 и L8, меняется только ссылка на сегмент.

СУММЕСЛИМН подходит и для одного условия; при необходимости можно добавить второе. Синтаксис и требования к диапазонам описаны в справке Microsoft.

Сегмент	Сумма H, ₽
Розница	7 940
Малый бизнес	13 965
Корпоративный	20 675
Всего	42 580

Проверка: =СУММ(L6:L8) должна совпасть с =СУММ(H2:H21). В учебном файле оба результата равны 42 580 ₽. Это стоимость заказов после скидок и возвратов, включая оплаченные заказы.

Такое равенство работает, пока у каждого заказа есть один из трёх сегментов. Опечатки и пустые сегменты приведут к пропуску сумм. Если добавите 21-й заказ в строку 22, расширьте все связанные диапазоны до строки 22.

Что делать, если формула не работает

Excel не принимает выражение или показывает #ИМЯ?. Проверьте имя функции, парные скобки, обычные прямые кавычки и разделитель аргументов. Вместе с запросом на исправление передайте точный текст формулы и ошибки, а также язык функций. Чек-лист Microsoft помогает найти синтаксическую причину.

Вместо результата виден текст формулы. Проверьте, не включён ли показ формул. Если остальные ячейки считают нормально, посмотрите, нет ли апострофа перед = и не задан ли формат «Текстовый». После смены формата на «Общий» введите формулу заново.

Формула считается, но сумма неверная. Сравните C2 со своим правилом процентов: число 10 и доля 10% требуют разных выражений. Проверьте порядок скидки и возврата. Для первого заказа контрольным значением остаётся 800 ₽.

Просрочка отмечена неправильно. Убедитесь, что E, F и L2 содержат настоящие даты, а пустая F не заполнена пробелом. Учитывайте, что < и <= по-разному обрабатывают день срока. Отдельно проверьте заказ с нулевой стоимостью.

Итоги по сегментам не сходятся. Проверьте окончания диапазонов, пустые значения и лишние пробелы в названиях. Если появляется #ССЫЛКА!, восстановите удалённый адрес. Маскировка любой ошибки через ЕСЛИОШИБКА(...;0) может скрыть проблему за правдоподобным нулём.

Готовый промпт для своих таблиц

Замените поля в квадратных скобках. Передавайте только разрешённые и обезличенные примеры строк.

Помоги написать формулу для электронной таблицы.

Программа и версия: [например, Excel 2021].
Язык имён функций: [русский / английский].
Разделитель аргументов: [; или ,].
Лист и адрес результата: [например, Заказы!H2].
Диапазон данных: [например, A2:G21].

Столбцы и типы значений:
[буква, назначение, число / процент / дата / текст].
Примеры 2–3 строк:
[обезличенные данные].

Задача: [точное правило расчёта].
Пустые значения означают: [правило].
Нули и отрицательные результаты: [правило].
Граница срока или диапазона: [что включать].
Контрольный пример: [входные значения и известный ответ].

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

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

Опишите столбцы и нужный расчёт в BebrGPT, попросите формулу и начните с контрольных строк. После проверки перенесите её в копию своей таблицы и сравните общий итог.