Главная → Как сделать в Excel → Формулы Excel

Формулы Excel: шпаргалка с примерами

Как устроена формула, четырнадцать формул на каждый день с рабочими примерами и разбор ошибок: #Н/Д, #ЗНАЧ!, #ДЕЛ/0!, #ИМЯ?, #####. Отдельно — почему формула показывается текстом и не считается.

Коротко

Формула — это то, что начинается со знака равно. Пока в ячейке стоит =, Excel считает; без него там просто текст. Дальше идут ссылки на ячейки (B2), диапазоны (K6:K13 — все ячейки с шестой строки по тринадцатую), числа, операторы + − * / и имена функций. =B2*C2 перемножит две ячейки, =СУММ(K6:K13) сложит весь диапазон.

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

Знак доллара закрепляет ссылку. Обычная ссылка B2 относительная: протянули формулу на строку ниже — она сама стала B3. Для расчёта по строкам это правильно: каждая строка считает свою. Но когда формула смотрит на одну общую ячейку — курс, ставку, коэффициент, справочник, — при протяжке ссылка уедет на пустое место, и столбец покажет нули или #ДЕЛ/0!. Ссылку закрепляют долларами: $B$2. Ставят их не руками, а клавишей F4: поставили курсор на ссылку внутри формулы, нажали — появились оба доллара; ещё раз — закреплена только строка (B$2); ещё — только столбец ($B2). Диапазон справочника в ВПР закрепляют всегда.

Формулы ниже считают на маленькой таблице продаж: столбцы Дата, Менеджер, Товар, Количество, Цена, Сумма в диапазоне F6:K13 — восемь строк. Рядом справочник цен M9:N12, жёлтая ячейка N5 для названия товара и N14 с наценкой. Ровно эти данные лежат в файле-шпаргалке, и там каждая формула не только показана, но и работает.

Про совместимость — сразу, чтобы не было сюрприза. Всё, что ниже, работает в Excel 2016 и новее и в Google Таблицах. ПРОСМОТРX, УНИК, ФИЛЬТР, СОРТ, ЛЕТ и динамические массивы в набор не вошли намеренно: в Excel 2016 их не существует, и файл с ними откроется у коллеги ошибкой #ИМЯ?. Если вся команда на Microsoft 365 — пользуйтесь, ПРОСМОТРX действительно удобнее ВПР. Если хоть у кого-то 2016-й или файл уходит наружу — берите то, что здесь.

То же самое формулой в Excel

=СУММ($K$6:$K$13) — сложить столбец: текст и пустые ячейки внутри диапазона пропускаются молча

=СУММЕСЛИМН($K$6:$K$13;$G$6:$G$13;"Иванов") — сумма по условию: сначала что складываем, потом пара «где искать — что искать»

=СУММЕСЛИМН($K$6:$K$13;$G$6:$G$13;"Петрова";$H$6:$H$13;"Стол Loft") — второе условие дописывается такой же парой — считаются строки, подходящие под оба

=СЧЁТЕСЛИМН($H$6:$H$13;"Стол Loft") — то же самое, но считает не сумму, а количество подходящих строк

=ЕСЛИ($K$6>50000;"крупная";"обычная") — развилка: условие, что вернуть если верно, что вернуть если нет

=ЕСЛИОШИБКА(ВПР("Шкаф Kvadrat";$M$9:$N$12;2;ЛОЖЬ);"нет в справочнике") — любая ошибка внутри превращается в понятный текст вместо #Н/Д

=ВПР($N$5;$M$9:$N$12;2;ЛОЖЬ) — подтянуть цену по названию из справочника; ЛОЖЬ — точное совпадение

=ИНДЕКС($M$9:$M$12;ПОИСКПОЗ(6200;$N$9:$N$12;0)) — поиск влево: ПОИСКПОЗ находит строку по цене, ИНДЕКС отдаёт название

=ОКРУГЛ($K$6*22/122;2) — НДС 22 % внутри суммы, до копеек; второй аргумент — сколько знаков оставить

=$J$6*(1+$N$14/100) — цена с наценкой из N14: доллары держат ссылку на месте при протяжке

=ДАТА(2026;12;31)-$F$13 — разница между датами в днях: даты в Excel — обычные числа, их вычитают

=СЕГОДНЯ() — текущая дата, скобки пустые; обновляется при каждом открытии файла

=$H$6&" — "&$G$6 — склеить текст из двух ячеек; то же делает СЦЕПИТЬ, но & короче

=ТЕКСТ($F$6;"ДД.ММ.ГГГГ")&", "&$H$6 — без ТЕКСТ дата при склейке превратится в число 46 239 — ТЕКСТ задаёт вид

Шпаргалка: четырнадцать формул на одном листе

Все формулы отсюда на живых данных: восемь строк продаж, справочник цен и две жёлтые ячейки для ввода. Каждая формула стоит дважды — текстом, чтобы её было видно и можно было скопировать, и рядом в работе, чтобы видеть результат. Меняете товар в N5 или наценку в N14 — примеры пересчитываются.

Забрать в Telegram Забрать в MAX

Файл formuly-shpargalka.xlsx · формулы работают в Excel 2016 и новее и в Google Таблицах · без макросов · формулы проходят автопроверку перед публикацией

Лист файла «Формулы Excel: шпаргалка с примерами»: Дата, Менеджер, Товар, Количество, Цена, Сумма, Товар:, Полка Nord — Так выглядит файл. Картинка собрана из него же при сборке сайта: числа в ней те, что посчитает Excel.

Пошагово

Встаньте в пустую ячейку и нажмите =. Пока знака равно нет, Excel считает содержимое ячейки текстом и ничего не вычисляет.
Кликните мышью ячейку, из которой берёте число, — её адрес подставится в формулу сам.
Поставьте оператор: +, −, *, /. Скобки работают как в арифметике: =(B2+C2)*D2 и =B2+C2*D2 дадут разное.
Нужна функция — наберите её имя сразу после знака равно: =СУММ. Excel покажет список подходящих; выберите стрелками и нажмите Tab — имя и открывающая скобка подставятся сами, и опечатки в имени не будет.
Выделите диапазон мышью. Если формулу будете протягивать, а диапазон должен остаться на месте, нажмите F4 — появятся доллары.
Аргументы функции разделяются точкой с запятой: =СУММЕСЛИМН(K6:K13;G6:G13;"Иванов"). Запятая вместо точки с запятой — самая частая причина, по которой русский Excel отказывается принимать формулу.
Закройте скобку и нажмите Enter.
Наведите курсор на правый нижний угол ячейки — появится тонкий чёрный крестик. Потяните вниз, и формула размножится по столбцу. Двойной клик по крестику протянет её сразу до конца соседнего заполненного столбца.

Где обычно ломается

Что значат ошибки в ячейке

#Н/Д — «не нашёл». Так отвечают поисковые функции: ВПР, ПОИСКПОЗ, ГПР. Причина почти всегда в ключе: лишний пробел, число против текста или искомое лежит не в первом столбце диапазона. Разобрано подробно на странице про ВПР.
#ЗНАЧ! — в формулу попал текст там, где ждали число. Обычно виноват не тот столбец в диапазоне или ячейка, где вместо числа стоит прочерк, «нет» или пробел. Проверьте аргументы по одному: встаньте в формулу, выделите кусок и нажмите F9 — Excel покажет, во что он превращается.
#ДЕЛ/0! — деление на ноль или на пустую ячейку: для Excel пустая ячейка — это ноль. Лечится не удалением формулы, а оболочкой: =ЕСЛИОШИБКА(A2/B2;0) или =ЕСЛИ(B2=0;"";A2/B2).
#ИМЯ? — Excel не узнал имя. Варианты: опечатка (СУМА вместо СУММ), английское имя в русской версии (SUM вместо СУММ), текст в формуле без кавычек — или функции нет в вашей версии Excel. Так ведут себя ПРОСМОТРX, УНИК, ФИЛЬТР и ЛЕТ, если файл сделали в Microsoft 365, а открыли в 2016-м.
##### — вообще не ошибка: число не помещается по ширине столбца. Потяните границу столбца вправо или дважды кликните по ней — ширина подберётся сама. Если столбец широкий, а решётки остались, в ячейке отрицательная дата или отрицательное время.
#ССЫЛКА! — формула ссылалась на ячейку, а её строку или столбец удалили. Отменить удаление (Ctrl + Z) сразу — единственный простой способ: восстановить адрес по тексту формулы уже нельзя, он затёрт.

Формула показывается текстом и не считается

Массовая история: в ячейке видно =СУММ(A1:A10), а не число. Четыре причины, по частоте:

У ячейки формат «Текстовый». Excel видит формат и даже не пытается считать. Поменяйте формат на «Общий» (Главная → раскрывающийся список форматов), а затем обязательно перевведите формулу: встаньте в ячейку, F2, Enter. Без переввода она останется текстом — смена формата задним числом сама ничего не пересчитывает. Для целого столбца быстрее: Данные → Текст по столбцам → Готово.
Включён показ формул. Сочетание Ctrl + ` (клавиша с буквой Ё) переключает лист в режим, где во всех ячейках видны формулы, а столбцы стали заметно шире. Если формулами покрылся весь лист разом — это оно. Нажмите ещё раз.
Пересчёт переключен на ручной. Формулы → Параметры вычислений → Вручную. Тогда результат обновляется только по F9, а при вводе новой формулы ячейка может показывать ноль или старое значение. Верните «Автоматически». Режим переезжает вместе с файлом: достаточно, чтобы его включил тот, кто прислал вам книгу.
Перед знаком равно стоит апостроф или пробел. Апостроф в самой ячейке не виден — его видно в строке формул. Уберите его и нажмите Enter.

Отдельный случай — формула считается, но результат не тот: например СУММ даёт ноль. Значит, сами числа лежат текстом. Такие числа прижаты к левому краю ячейки и часто помечены зелёным уголком; СУММ их не складывает. Лечится тем же «Текст по столбцам» или умножением на единицу через специальную вставку.

Русские и английские имена функций

Excel переводит имена функций на язык интерфейса, а внутри книги хранит их одинаково. Поэтому файл, собранный в русской версии, в английской откроется правильно — но формулу, скопированную с англоязычного сайта или из Google Таблиц, русский Excel не поймёт: ему нужно СУММ, а не SUM. Ниже 64 функций, которые встречаются в наших разборах и файлах, — с обоими именами.

Отдельно про то, что имена переводятся, а коды формата — нет. Внутри ТЕКСТ код пишется буквально: ДД.ММ.ГГГГ в русском Excel и DD.MM.YYYY в Google Таблицах, и книга хранит то, что вы набрали. Это и есть самая частая причина, по которой формула, работавшая у вас, у коллеги показывает мусор. Подробный разбор — на странице про функцию ТЕКСТ.

По-русски	По-английски	Что делает
`АГРЕГАТ`	`AGGREGATE`	итог с пропуском скрытого и ошибок
`БС`	`FV`	будущая сумма вклада
`ВПР`	`VLOOKUP`	поиск по первому столбцу вправо
`ГОД`	`YEAR`	год
`ГПР`	`HLOOKUP`	поиск по первой строке вниз
`ДАТА`	`DATE`	собрать дату из года, месяца, дня
`ДАТАМЕС`	`EDATE`	дата через n месяцев
`ДЕНЬ`	`DAY`	день месяца
`ДЕНЬНЕД`	`WEEKDAY`	день недели числом
`ДЛСТР`	`LEN`	сколько знаков
`ЕПУСТО`	`ISBLANK`	пуста ли ячейка
`ЕСЛИ`	`IF`	условие и два ответа
`ЕСЛИМН`	`IFS`	несколько условий подряд (2019+)
`ЕСЛИОШИБКА`	`IFERROR`	подменить ошибку своим текстом
`ЕЧИСЛО`	`ISNUMBER`	число ли это
`ЗНАЧЕН`	`VALUE`	строку с числом — в число
`И`	`AND`	все условия сразу
`ИЛИ`	`OR`	хотя бы одно условие
`ИНДЕКС`	`INDEX`	значение по номеру строки и столбца
`КОНМЕСЯЦА`	`EOMONTH`	последний день месяца
`ЛЕВСИМВ`	`LEFT`	знаки слева
`МАКС`	`MAX`	наибольшее
`МАКСЕСЛИ`	`MAXIFS`	максимум по условию (2019+)
`МЕСЯЦ`	`MONTH`	номер месяца
`МИН`	`MIN`	наименьшее
`НАИБОЛЬШИЙ`	`LARGE`	n-е по величине
`НАИМЕНЬШИЙ`	`SMALL`	n-е с конца
`НАЙТИ`	`FIND`	позиция текста, с регистром
`НЕ`	`NOT`	перевернуть условие
`ОБЪЕДИНИТЬ`	`TEXTJOIN`	склеить с разделителем (2019+)
`ОКРУГЛ`	`ROUND`	округлить до знаков
`ПЛТ`	`PMT`	платёж по кредиту
`ПОДСТАВИТЬ`	`SUBSTITUTE`	заменить текст на другой
`ПОИСК`	`SEARCH`	позиция текста, без регистра
`ПОИСКПОЗ`	`MATCH`	номер совпадения в диапазоне
`ПРАВСИМВ`	`RIGHT`	знаки справа
`ПРОМЕЖУТОЧНЫЕ.ИТОГИ`	`SUBTOTAL`	итог по видимым строкам
`ПРОПИСН`	`UPPER`	ВСЁ ПРОПИСНЫМИ
`ПРОПНАЧ`	`PROPER`	Каждое Слово С Заглавной
`ПРОСМОТР`	`LOOKUP`	старый поиск, требует сортировки
`ПРОСМОТРX`	`XLOOKUP`	поиск в любую сторону (2021+, в 2016 нет)
`ПСТР`	`MID`	знаки из середины
`РАЗНДАТ`	`DATEDIF`	разница дат; «md» не использовать
`СЕГОДНЯ`	`TODAY`	сегодняшняя дата
`СЖПРОБЕЛЫ`	`TRIM`	убрать лишние пробелы
`СИМВОЛ`	`CHAR`	знак по коду; 10 — перенос строки
`СРЗНАЧ`	`AVERAGE`	среднее по диапазону
`СРЗНАЧЕСЛИ`	`AVERAGEIF`	среднее по условию
`СТРОЧН`	`LOWER`	всё строчными
`СУММ`	`SUM`	сложить диапазон
`СУММЕСЛИ`	`SUMIF`	сумма по одному условию
`СУММЕСЛИМН`	`SUMIFS`	сумма по нескольким условиям
`СУММПРОИЗВ`	`SUMPRODUCT`	сумма произведений; заменяет формулы массива
`СЦЕП`	`CONCAT`	склеить диапазон (2019+)
`СЦЕПИТЬ`	`CONCATENATE`	склеить значения
`СЧЁТ`	`COUNT`	сколько чисел
`СЧЁТЕСЛИ`	`COUNTIF`	счёт по условию
`СЧЁТЕСЛИМН`	`COUNTIFS`	счёт по нескольким условиям
`СЧЁТЗ`	`COUNTA`	сколько непустых
`ТЕКСТ`	`TEXT`	число или дата строкой в нужном виде
`УНИК`	`UNIQUE`	без повторов (2021+, в 2016 нет)
`ФИЛЬТР`	`FILTER`	все подходящие строки (2021+, в 2016 нет)
`ЦЕЛОЕ`	`INT`	целая часть
`ЧИСТРАБДНИ`	`NETWORKDAYS`	рабочих дней между датами

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

Частые вопросы

Какие самые базовые формулы в Excel?

Их примерно десяток, и они закрывают большую часть работы: СУММ — сложить диапазон; СУММЕСЛИМН и СЧЁТЕСЛИМН — сложить и посчитать по условиям; ЕСЛИ — развилка; ЕСЛИОШИБКА — подменить ошибку понятным текстом; ВПР и связка ИНДЕКС с ПОИСКПОЗ — подтянуть данные из справочника; ОКРУГЛ — до копеек или до рубля; СЕГОДНЯ и ДАТА — работа с датами; амперсанд & и ТЕКСТ — склеить текст и задать, как показывать число. Все они есть выше с рабочими примерами и лежат в файле-шпаргалке.

Как в Excel задать формулу с условием?

Через ЕСЛИ: =ЕСЛИ(условие; что вернуть если верно; что вернуть если нет). Например =ЕСЛИ(K6>50000;"крупная";"обычная"). Вместо текста в любой ветке может стоять другая формула, в том числе ещё одно ЕСЛИ — так делают лесенку условий. Но если условий больше трёх, вложенные ЕСЛИ читать невозможно: лучше вынести пороги в отдельную табличку и брать ответ оттуда через ВПР с приблизительным поиском. Когда нужно не выбрать текст, а сложить или посчитать по условию, ЕСЛИ не нужен вовсе — берите СУММЕСЛИМН и СЧЁТЕСЛИМН.

Как сделать формулу в Excel, чтобы считал автоматически?

Автоматический пересчёт включён по умолчанию: достаточно поставить знак равно и Enter. Если формула введена, а число не меняется при правке исходных ячеек, пересчёт переключен на ручной: Формулы → Параметры вычислений → Автоматически. До этого пересчитать можно клавишей F9. Режим хранится в файле, поэтому чаще всего его включил не пользователь, а тот, кто прислал книгу.

Как в Excel показать все формулы?

Сочетание Ctrl + ` — клавиша с буквой Ё, слева от единицы. Лист переключается в режим, где вместо результатов видны сами формулы, а столбцы становятся шире. Это же есть в меню: Формулы → Показать формулы. Повторное нажатие возвращает нормальный вид. Режим ничего не портит и не сохраняется в значения — им удобно проверять чужой файл целиком, а не кликать по ячейкам.

Как прописать две формулы в одной ячейке?

Две формулы в одной ячейке не живут — знак равно в ячейке один. Но одна формула может содержать другую: =ЕСЛИОШИБКА(ВПР(…);"нет") — это ВПР внутри ЕСЛИОШИБКА, и таких вложений допускается до 64 уровней. Если нужно показать в ячейке два разных результата, их склеивают в текст амперсандом: =СУММ(A1:A5)&" из "&СЧЁТ(A1:A5). Учтите, что после склейки в ячейке лежит текст, и считать по нему дальше уже нельзя.

Как в Excel быстро протянуть формулу?

Двойной клик по чёрному крестику в правом нижнем углу ячейки: формула заполнит столбец до конца соседнего заполненного столбца, тянуть мышью не нужно. Второй способ — выделить весь диапазон вместе с ячейкой-источником и нажать Ctrl + D (вниз) или Ctrl + R (вправо). Перед протяжкой проверьте доллары: ссылки на общие ячейки и справочники должны быть закреплены через F4, иначе они сползут вместе с формулой.