Формулы для Excel: как не тратить часы на поиски и составление

Формулы для Excel: нейросети пишут за вас

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

Руководство для тех, кто хочет результат без лишних часов копания в справке.

Содержание

1. Важно про локаль Excel
2. Пример 1: Процент скидки с округлением
3. Пример 2: SUMIFS по менеджеру и дате
4. Пример 3: XLOOKUP с fallback
5. Пример 4: Вытянуть домен из URL
6. Пример 5: Топ-5 товаров без сводной (365)
7. FAQ

Генерация формул через нейросети: пошаговые примеры

В русской локали Excel аргументы функций разделяются ;, а в английской ,. Ниже формулы даны в английском формате.

Важно про локаль Excel

Названия функций и разделители зависят от локали. Если используете русскую версию, просите нейросеть сгенерировать русскую версию с точками с запятой или заменяйте разделители вручную.

Пример 1. Процент скидки с округлением и защитой от деления на ноль

Задача: из старой цены B2 и новой C2 получить процент скидки, округлить до 2 знаков, вернуть пусто при пустом/нулевом значении.

Промпт для ChatGPT:

Сгенерируй формулу Excel: в D2 посчитай процент скидки как (B2−C2)/B2, округли до 2 знаков, если B2 пустая или равна 0 — верни пустую строку. Дай англ. имена функций и запятые.

Формула:

=IF(OR(B2="",B2=0),"",ROUND((B2-C2)/B2,2))

Версия без отрицательной скидки:

=IF(OR(B2="",B2=0),"",ROUND(MAX(0,(B2-C2)/B2),2))

Пример 2. Сумма по нескольким условиям: менеджер + диапазон дат

Задача: сложить продажи в D:D, если менеджер в B:B — Иванов, а дата в C:C в январе 2025.

Промпт:

SUMIFS по D:D, менеджер B:B="Иванов", дата C:C между 01.01.2025 и 31.01.2025 включительно. Используй DATE и EOMONTH, англ. функции.

Формула:

=SUMIFS(D:D,B:B,"Иванов",C:C,">="&DATE(2025,1,1),C:C,"<="&EOMONTH(DATE(2025,1,1),0))

Параметризация через ячейки:

=SUMIFS($D:$D,$B:$B,$H$2,$C:$C,">="&DATE($H$3,$H$4,1),$C:$C,"<="&EOMONTH(DATE($H$3,$H$4,1),0))

Пример 3. Поиск значения с запасным вариантом и частичным совпадением

Задача: найти цену по артикулу из A2 в справочнике F:G; допускается частичное совпадение; при отсутствии — Не найдено.

Промпт:

Сделай XLOOKUP с подстановочными символами, ищи A2 в F:F по частичному совпадению, верни G:G; если не найдено — «Не найдено».

Формула:

=IFERROR(XLOOKUP("*"&A2&"*",F:F,G:G,"",0),"Не найдено")

Жестче по началу строки:

=IFERROR(XLOOKUP(A2&"*",F:F,G:G,"",2),"Не найдено")

Пример 4. Разбор текста: вытащить домен из URL и нормализовать регистр

Задача: из https://www.shop.example.com/path?x=1 получить shop.example.com без Power Query.

Промпт:

Дай формулу на TEXTAFTER/TEXTBEFORE, удали «www.» если есть, верни в нижнем регистре, учти, что протокола может не быть.

Формула (365):

=LOWER(SUBSTITUTE(TEXTBEFORE(TEXTAFTER(IF(ISNUMBER(SEARCH("://",A2)),A2,"http://"&A2),"//"),"/"),"www.",""))

Пример 5. Топ-5 товаров по выручке без сводной: UNIQUE + SORTBY + MAP

Дано: товары в B2:B100, выручка в D2:D100. Нужен список топ-5 товаров по суммарной выручке.

Промпт:

Одна формула для Excel 365: UNIQUE, MAP/LAMBDA, SUMIF и сортировка по убыванию, верни только 5 позиций.

Формула:

=TAKE(SORTBY(UNIQUE(B2:B100),MAP(UNIQUE(B2:B100),LAMBDA(t,SUMIF(B2:B100,t,D2:D100))),-1),5)