В Excel около 500 функций, но для 90% задач хватит 15-20 основных. Ниже шпаргалка по самым нужным формулам: от простого СУММ и СРЗНАЧ до ВПР и ЕСЛИ. Каждая с примером, объяснением и типичными ошибками, которые совершают новички.
Как работают формулы в Excel
Любая формула начинается со знака равно. Это закон. Без «=» Excel считает, что вы ввели просто текст.
# Правильно:
=A1+B1
=СУММ(A1:A10)
# Неправильно (Excel воспримет как текст):
СУММ(A1:A10)
A1+B1Короче, запомните: знак равно, потом формула. Всё остальное Excel подскажет сам.
Кстати, есть один неочевидный момент. Если вы случайно ввели формулу как текст (без знака =), просто нажмите F2 для редактирования ячейки, добавьте = в начало и нажмите Enter. Не нужно удалять и набирать заново.
Математические формулы
СУММ (SUM)
Суммирует числа. Самая используемая функция в Excel. По статистике Microsoft, СУММ используется в 90% всех книг Excel.
# Сумма диапазона ячеек
=СУММ(A1:A100)
# Сумма нескольких диапазонов
=СУММ(A1:A10; C1:C10; E1:E10)
# Сумма с отдельными ячейками
=СУММ(A1; A5; A10)
# Быстрый способ: выделите диапазон и нажмите Alt + =
# Excel автоматически вставит формулу СУМММой знакомый Тимур, начинающий бухгалтер, первый месяц в новой компании складывал числа калькулятором и вручную вбивал результат в ячейку. На своём Acer Aspire 3 (не самом быстром, скажем так). Пока старший коллега не показал ему СУММ. «Ты серьёзно?» сказал Тимур. Да, серьёзно. Одна формула заменяет калькулятор.
СРЗНАЧ (AVERAGE)
Считает среднее арифметическое. Пустые ячейки и текст игнорирует.
# Средняя зарплата по отделу
=СРЗНАЧ(B2:B50)
# Среднее из нескольких ячеек
=СРЗНАЧ(B2; B5; B10)
# Среднее без учёта нулей (полезно для рейтингов)
=СРЗНАЧЕСЛИ(B2:B50; "<>0")Знаете, какая частая ошибка? Люди путают среднее и медиану. СРЗНАЧ считает среднее арифметическое (сумма / количество). Если у вас зарплаты: 30000, 40000, 50000, 50000, 500000, среднее будет 134000. Но это не «типичная» зарплата. Для более реалистичной картины используйте МЕДИАНА (MEDIAN), которая покажет 50000.
МИН и МАКС (MIN, MAX)
Находят минимальное и максимальное значение. Просто и полезно.
# Минимальная цена из списка
=МИН(C2:C100)
# Максимальная продажа за месяц
=МАКС(D2:D31)
# Разница между максимумом и минимумом (размах)
=МАКС(C2:C100)-МИН(C2:C100)СЧЁТ и СЧЁТЗ (COUNT, COUNTA)
СЧЁТ считает ячейки с числами. СЧЁТЗ считает все непустые ячейки (включая текст).
# Сколько чисел в столбце (пустые и текст не считает)
=СЧЁТ(A1:A100)
# Сколько непустых ячеек (считает всё кроме пустых)
=СЧЁТЗ(A1:A100)
# Сколько ПУСТЫХ ячеек
=СЧИТАТЬПУСТОТЫ(A1:A100)На самом деле разница между ними критична. Если в столбце есть текст «н/д» или пробелы, СЧЁТ их пропустит, а СЧЁТЗ посчитает. Тут такое дело: выбирайте функцию под задачу.
ОКРУГЛ (ROUND)
Округляет числа до нужного количества знаков. Незаменимая функция для финансовых расчётов.
# Округлить до двух знаков после запятой (копейки)
=ОКРУГЛ(A1; 2)
# Округлить до целого
=ОКРУГЛ(A1; 0)
# Округлить до тысяч
=ОКРУГЛ(A1; -3)
# Округление вверх и вниз
=ОКРУГЛВВЕРХ(A1; 0) # всегда вверх
=ОКРУГЛВНИЗ(A1; 0) # всегда внизПо факту, для бухгалтерских расчётов всегда округляйте итоговые суммы. Иначе получите красивые 1234.5678 рублей, которые ни в один отчёт не влезут.
Логические формулы
ЕСЛИ (IF)
Царь логических функций. Проверяет условие и возвращает разные значения в зависимости от результата.
# Если продажа больше 100000, то "Выполнен", иначе "Не выполнен"
=ЕСЛИ(B2>100000; "Выполнен"; "Не выполнен")
# Вложенный ЕСЛИ (не злоупотребляйте, максимум 3-4 уровня)
=ЕСЛИ(B2>100000; "Отлично"; ЕСЛИ(B2>50000; "Хорошо"; "Плохо"))
# ЕСЛИ с проверкой на пустую ячейку
=ЕСЛИ(A2=""; "Не заполнено"; A2*1.2)Знаете что? Вложенные ЕСЛИ это кошмар. Больше трёх уровней и формулу невозможно прочитать. Для множества условий используйте ЕСЛИМН (IFS) или ВПР.
ЕСЛИМН (IFS)
Проверяет несколько условий без вложенности. Появилась в Excel 2019 и Microsoft 365.
# Оценка на основе баллов
=ЕСЛИМН(A2>=90; "Отлично"; A2>=70; "Хорошо"; A2>=50; "Удовл."; A2<50; "Неуд.")По факту ЕСЛИМН делает то же что и вложенные ЕСЛИ, но читается в сто раз легче. Если у вас Excel 2016 или старше, функция недоступна.
И, ИЛИ, НЕ (AND, OR, NOT)
Комбинируются с ЕСЛИ для сложных условий.
# Бонус если продажи > 100000 И опыт > 2 лет
=ЕСЛИ(И(B2>100000; C2>2); "Бонус"; "Нет бонуса")
# Скидка если клиент VIP ИЛИ сумма > 50000
=ЕСЛИ(ИЛИ(D2="VIP"; E2>50000); "Скидка 10%"; "Без скидки")
# НЕ инвертирует логику
=ЕСЛИ(НЕ(A2=""); "Заполнено"; "Пусто")Формулы поиска
ВПР (VLOOKUP)
Ищет значение в первом столбце таблицы и возвращает значение из другого столбца. Самая полезная функция после СУММ. И самая пугающая для новичков.
# Найти цену товара по его коду
# ВПР(что_ищем; где_ищем; номер_столбца; точное_совпадение)
=ВПР(A2; Прайс!A:C; 3; ЛОЖЬ)
# A2 - код товара, который ищем
# Прайс!A:C - таблица с прайсом на другом листе
# 3 - третий столбец таблицы (цена)
# ЛОЖЬ - ищем точное совпадение (почти всегда ЛОЖЬ)Грубо говоря, ВПР работает как поиск в телефонной книге: вы знаете имя (код товара) и хотите найти номер (цену). Функция ищет имя в первом столбце и возвращает значение из нужного столбца той же строки.
Кстати, ВПР ищет только вправо. Если нужное значение находится левее искомого, ВПР не справится. Для этого используйте ИНДЕКС+ПОИСКПОЗ или XLOOKUP.
А ещё частая проблема: ВПР возвращает #Н/Д, хотя значение точно есть в таблице. В 80% случаев причина в скрытых пробелах. Число "123 " (с пробелом) и "123" (без) для ВПР это разные значения. Используйте СЖПРОБЕЛЫ (TRIM) для очистки: =ВПР(СЖПРОБЕЛЫ(A2); Прайс!A:C; 3; ЛОЖЬ).
XLOOKUP (ПРОСМОТРX)
Замена ВПР в современных версиях Excel (Microsoft 365, Excel 2021). Ищет в любом направлении.
# ПРОСМОТРX(что_ищем; где_ищем; что_возвращаем)
=ПРОСМОТРX(A2; Прайс!B:B; Прайс!D:D)
# Проще чем ВПР, не нужно считать номер столбца
# Ищет в любом направлении (влево, вправо)
# С обработкой ошибки (если не найдено)
=ПРОСМОТРX(A2; Прайс!B:B; Прайс!D:D; "Не найдено")Если честно, ПРОСМОТРX лучше ВПР во всём. Но ВПР знают все, а ПРОСМОТРX пока нет. Учите оба.
ИНДЕКС + ПОИСКПОЗ (INDEX + MATCH)
Связка для продвинутых. Заменяет ВПР и работает гибче.
# Находим цену товара
# ПОИСКПОЗ находит номер строки
# ИНДЕКС возвращает значение из этой строки
=ИНДЕКС(C2:C100; ПОИСКПОЗ(A2; B2:B100; 0))Моя подруга Оля, аналитик на своём Surface Pro 9, перешла с ВПР на ИНДЕКС+ПОИСКПОЗ год назад. Говорит, привыкла за неделю и теперь не понимает, как жила без этой связки. Работает быстрее на больших данных и не ломается при добавлении столбцов.
Текстовые формулы
СЦЕПИТЬ / ОБЪЕДИНИТЬ (CONCATENATE / CONCAT)
# Объединить имя и фамилию
=СЦЕПИТЬ(A2; " "; B2)
# Или через амперсанд (короче и удобнее)
=A2&" "&B2
# ОБЪЕДИНИТЬ работает с диапазонами (Excel 2019+)
=ОБЪЕДИНИТЬ(A2:C2)ЛЕВСИМВ, ПРАВСИМВ, ПСТР (LEFT, RIGHT, MID)
# Первые 3 символа (например, код региона)
=ЛЕВСИМВ(A2; 3)
# Последние 4 символа (например, последние цифры телефона)
=ПРАВСИМВ(A2; 4)
# Символы с 5 по 8 (извлечь фрагмент из середины)
=ПСТР(A2; 5; 4)ПОДСТАВИТЬ (SUBSTITUTE)
Вот в чём прикол: ПОДСТАВИТЬ заменяет текст внутри ячейки без необходимости редактировать её вручную.
# Заменить "ООО" на "Общество с ограниченной ответственностью"
=ПОДСТАВИТЬ(A2; "ООО"; "Общество с ограниченной ответственностью")
# Убрать все пробелы из строки
=ПОДСТАВИТЬ(A2; " "; "")
# Заменить запятую на точку (полезно для импортированных данных)
=ПОДСТАВИТЬ(A2; ","; ".")Формулы с условиями
СУММЕСЛИ и СУММЕСЛИМН (SUMIF, SUMIFS)
# Сумма продаж только по Москве
=СУММЕСЛИ(B2:B100; "Москва"; C2:C100)
# Сумма продаж по Москве за январь (два условия)
=СУММЕСЛИМН(D2:D100; B2:B100; "Москва"; C2:C100; "Январь")
# Сумма продаж больше 50000
=СУММЕСЛИ(C2:C100; ">50000")Вот в чём прикол с СУММЕСЛИ: порядок аргументов отличается от СУММЕСЛИМН. В СУММЕСЛИ сначала диапазон условия, потом условие, потом диапазон суммирования. В СУММЕСЛИМН наоборот: сначала диапазон суммирования. Путаница гарантирована.
СЧЁТЕСЛИ (COUNTIF)
# Сколько раз встречается "Москва" в столбце
=СЧЁТЕСЛИ(B2:B100; "Москва")
# Сколько значений больше 1000
=СЧЁТЕСЛИ(C2:C100; ">1000")
# Сколько уникальных значений (хитрый приём)
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(B2:B100;B2:B100))Работа с датами
# Сегодняшняя дата (обновляется автоматически)
=СЕГОДНЯ()
# Текущая дата и время
=ТДАТА()
# Разница между датами в днях
=B2-A2
# Добавить 30 дней к дате
=A2+30
# Вычислить возраст человека по дате рождения
=ЦЕЛОЕ((СЕГОДНЯ()-A2)/365.25)
# Определить день недели (1 = понедельник, 7 = воскресенье)
=ДЕНЬНЕД(A2; 2)Тут такое дело с датами: Excel хранит их как числа. 1 января 1900 года это число 1, 2 января 1900 это 2, и так далее. Поэтому разница между датами считается простым вычитанием. Если формула с датами даёт странный результат, проверьте формат ячеек. Может быть, Excel отображает число, а не дату.
Шпаргалка: 15 формул на каждый день
| Формула | Что делает | Пример |
|---|---|---|
| СУММ | Суммирует | =СУММ(A1:A10) |
| СРЗНАЧ | Среднее | =СРЗНАЧ(A1:A10) |
| МИН / МАКС | Мин / Макс | =МИН(A1:A10) |
| СЧЁТ | Количество чисел | =СЧЁТ(A1:A10) |
| ЕСЛИ | Условие | =ЕСЛИ(A1>10;"Да";"Нет") |
| ВПР | Поиск по таблице | =ВПР(A1;B:C;2;ЛОЖЬ) |
| СУММЕСЛИ | Сумма с условием | =СУММЕСЛИ(A:A;"Да";B:B) |
| СЧЁТЕСЛИ | Подсчёт с условием | =СЧЁТЕСЛИ(A:A;"Да") |
| СЦЕПИТЬ | Объединить текст | =A1&" "&B1 |
| ЛЕВСИМВ | Первые N символов | =ЛЕВСИМВ(A1;3) |
| ОКРУГЛ | Округление | =ОКРУГЛ(A1;2) |
| СЕГОДНЯ | Текущая дата | =СЕГОДНЯ() |
| И / ИЛИ | Логические операторы | =И(A1>0;B1>0) |
| ABS | Модуль числа | =ABS(A1) |
| ПРОСМОТРX | Улучшенный ВПР | =ПРОСМОТРX(A1;B:B;C:C) |
Горячие клавиши для работы с формулами
Если честно, горячие клавиши экономят кучу времени. Вот самые полезные:
| Клавиша | Действие |
|---|---|
| F2 | Редактирование ячейки (перейти внутрь формулы) |
| F4 | Переключение абсолютной/относительной ссылки ($A$1, A$1, $A1, A1) |
| F9 | Вычислить выделенную часть формулы (для отладки) |
| Ctrl + ` | Показать все формулы вместо значений |
| Alt + = | Автосумма |
| Ctrl + Shift + Enter | Ввод формулы массива (для старых версий Excel) |
| Tab | Принять подсказку автодополнения функции |
Особенно полезна клавиша F9. Выделяете часть формулы в строке формул и нажимаете F9. Excel вычислит только выделенный фрагмент и покажет результат. Так можно пошагово проверить сложную формулу. Только не забудьте нажать Esc после проверки, иначе Excel заменит формулу на вычисленное значение.
Типичные ошибки новичков
#ЗНАЧ! означает неправильный тип данных. Пытаетесь сложить число с текстом.
#ССЫЛ! означает что ячейка, на которую ссылается формула, была удалена.
#ДЕЛ/0! означает деление на ноль. Оберните формулу в ЕСЛИОШИБКА: =ЕСЛИОШИБКА(A1/B1; 0)
#Н/Д означает что ВПР не нашёл искомое значение. Проверьте что данные совпадают точно (пробелы!), или оберните в ЕСЛИОШИБКА.
#ИМЯ? означает, что Excel не распознал имя функции. Обычно это опечатка в названии функции или попытка использовать функцию из новой версии Excel в старой.
А ещё новички часто забывают фиксировать ссылки знаком $. Когда копируете формулу вниз, ссылки смещаются. Чтобы зафиксировать: $A$1 фиксирует и столбец и строку. F4 переключает режимы фиксации.
Мой коллега Максим, менеджер по продажам, работает на Dell Latitude 5530 и каждый месяц делает отчёт. Полгода его формулы ВПР ломались при копировании, потому что он не фиксировал диапазон поиска. Каждый раз заново всё переделывал. Потом узнал про F4 и $ и сэкономил себе полдня в месяц. Ну и нервов прилично.
Формулы для обработки ошибок
В реальных таблицах ошибки неизбежны. Пустые ячейки, отсутствующие данные, деление на ноль. Вот как с ними бороться.
# Если формула выдаёт ошибку, показать 0 вместо #Н/Д или #ДЕЛ/0!
=ЕСЛИОШИБКА(ВПР(A2; B:C; 2; ЛОЖЬ); 0)
# Если формула выдаёт ошибку, показать пустую строку
=ЕСЛИОШИБКА(A1/B1; "")
# Проверить, является ли значение числом
=ЕЧИСЛО(A1)
# Проверить, пуста ли ячейка
=ЕПУСТО(A1)Тут такое дело: ЕСЛИОШИБКА это ваш спасательный круг. Оборачивайте в неё любую формулу, которая может выдать ошибку. Особенно ВПР. Особенно деление. Без ЕСЛИОШИБКА таблица с тысячей строк может выглядеть как поле с минами #Н/Д.
Моя знакомая Лена, экономист в торговой компании, работает на HP ProBook 450 G10 и каждый день строит отчёты на 50 000+ строк. Говорит, первое что она делает в любой формуле ВПР, оборачивает в ЕСЛИОШИБКА. "Отчёт с кучей #Н/Д показать руководству невозможно, даже если ошибки в двух строках из тысячи".
Полезные комбинации формул
Вот несколько комбинаций, которые решают реальные задачи:
# Извлечь домен из email ([email protected] -> company.ru)
=ПРАВСИМВ(A2; ДЛСТР(A2)-НАЙТИ("@"; A2))
# Первая буква заглавная, остальные строчные
=ПРОПИСН(ЛЕВСИМВ(A2;1))&СТРОЧН(ПСТР(A2;2;255))
# Убрать все нечисловые символы из строки (оставить только цифры)
# К сожалению, в одну формулу не получится, используйте ПОДСТАВИТЬ
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;"+";""");"(";""");")";"")
# Найти второе по величине значение
=НАИБОЛЬШИЙ(A1:A100; 2)
# Сумма каждой второй строки (нечётные строки)
=СУММПРОИЗВ((ОСТАТ(СТРОКА(A1:A100);2)=1)*A1:A100)Ну и если честно, такие комбинации лучше сохранять в отдельный файл-шпаргалку. Потому что через месяц вы забудете, как извлечь домен из email, и будете гуглить заново.
Когда формул не хватает
Если вы обнаружили, что строите формулы из 10 вложенных функций, остановитесь. Скорее всего вам нужны сводные таблицы или Power Query. Формулы прекрасны для простых задач, но для серьёзной аналитики есть инструменты получше.
А когда именно пора переходить от формул к другим инструментам? Грубо говоря, если ваша формула занимает больше одной строки в строке формул, если вы используете больше трёх вложенных функций, или если файл Excel начинает тормозить при пересчёте, значит пора осваивать сводные таблицы, Power Query или даже Python.
Для полноценной работы с формулами нужен десктопный Excel из Microsoft 365 или Office 2021. Онлайн-версия поддерживает не все функции (ПРОСМОТРX, ФИЛЬТР, СОРТ работают только в десктопной версии). Лицензию Microsoft 365 или Office 2021 можно приобрести в keytrust24.store.
Практический пример: таблица расчёта зарплаты
Давайте соберём всё в один практический пример. Допустим, у вас список сотрудников, и нужно рассчитать зарплату с надбавками.
# Столбец A: ФИО
# Столбец B: Оклад
# Столбец C: Стаж (лет)
# Столбец D: Отработано дней
# Столбец E: Надбавка за стаж
# Формула надбавки (столбец E):
=ЕСЛИ(C2>=10; B2*0.15; ЕСЛИ(C2>=5; B2*0.10; ЕСЛИ(C2>=3; B2*0.05; 0)))
# Итоговая зарплата (столбец F):
=ОКРУГЛ((B2/22)*D2 + E2; 2)
# Итого по всем сотрудникам (внизу таблицы):
=СУММ(F2:F50)
# Средняя зарплата:
=СРЗНАЧ(F2:F50)
# Максимальная зарплата:
=МАКС(F2:F50)Вот в чём прикол: все формулы из этой статьи здесь работают вместе. ЕСЛИ для надбавки, ОКРУГЛ для копеек, СУММ для итога, СРЗНАЧ для средней. Один файл, десяток формул, и расчёт зарплаты для всего отдела готов. Ну разве не красота?
