Формулы в Excel: топ функций с примерами для работы
Формулы — это то, ради чего Excel вообще стоит открывать: они считают, сравнивают, ищут и подставляют данные за вас. В этом материале разберём, как устроена любая формула, чем абсолютная ссылка отличается от относительной, какие функции нужны в работе чаще всего и как быстро находить причину ошибки. Всё — на понятных примерах из офисной практики.
Как устроена формула в Excel
Любая формула в Excel начинается со знака равенства =. Именно он сообщает программе: дальше не текст, а вычисление. После знака «равно» идёт то, из чего складывается результат, — ссылки на ячейки, числа, операторы и функции.
Возьмём простую формулу =A1+B1. Здесь A1 и B1 — ссылки на ячейки, а + — оператор сложения. Excel подставит текущие значения этих ячеек и покажет сумму. Поменяете число в A1 — результат пересчитается сам, вручную ничего править не нужно.
Операторы делятся на несколько групп:
- Арифметические:
+сложение,-вычитание,*умножение,/деление,^степень,%процент. - Сравнения:
=равно,<>не равно,>больше,<меньше, а также>=и<=. Такие операторы возвращают ИСТИНА или ЛОЖЬ. - Объединение текста: амперсанд
&склеивает значения, например=A2&" "&B2соберёт фамилию и имя через пробел.
Порядок действий такой же, как в математике: сначала степень, затем умножение и деление, потом сложение и вычитание. Скобки меняют приоритет: =(A1+B1)*2 сначала сложит, а потом умножит. Чтобы увидеть в ячейке саму формулу, а не результат, нажмите F2 или загляните в строку формул над таблицей.
Абсолютные и относительные ссылки
Ссылка — это адрес ячейки. Когда вы копируете формулу в соседние ячейки, ссылки по умолчанию «сдвигаются». Это относительная ссылка. Формула =A1*2, скопированная на строку ниже, автоматически превратится в =A2*2. Чаще всего это удобно: одну формулу растягивают на весь столбец, и она подстраивается под каждую строку.
Но иногда ссылка не должна двигаться — например, когда все строки умножаются на курс валюты или ставку из одной ячейки. Тогда ставят знак доллара: $A$1. Это абсолютная ссылка, она остаётся неизменной при копировании куда угодно.
Есть и смешанные ссылки, где закреплена только часть адреса: A$1 фиксирует строку, а $A1 — столбец. Чтобы быстро перебрать варианты, поставьте курсор на ссылку в формуле и нажимайте F4 — Excel по кругу меняет A1 → $A$1 → A$1 → $A1.
$A1 — заморожен столбец A, строка свободна. A$1 — заморожена строка 1, столбец свободен. $A$1 — заморожено всё. Если формула при копировании «поехала» и выдаёт неверные числа, почти всегда виновата забытая абсолютная ссылка.Топ функций Excel по группам
Функций в Excel сотни, но в реальной офисной работе крутится примерно два десятка. Разложим главные по группам — с коротким объяснением и рабочим примером для каждой.
| Группа | Функция | Что делает | Пример |
|---|---|---|---|
| Математика и подсчёт | СУММ | Складывает числа в диапазоне | =СУММ(B2:B10) |
| СРЗНАЧ | Считает среднее арифметическое | =СРЗНАЧ(B2:B10) | |
| СЧЁТ | Считает, в скольких ячейках есть числа | =СЧЁТ(B2:B10) | |
| Логика | ЕСЛИ | Проверяет условие и выдаёт один из двух результатов | =ЕСЛИ(B2>=100;"Опт";"Розница") |
| ЕСЛИМН | Проверяет несколько условий подряд без вложенности | =ЕСЛИМН(B2>90;"A";B2>75;"B";ИСТИНА;"C") | |
| Поиск | ВПР | Ищет значение в первом столбце таблицы и возвращает данные из нужного столбца | =ВПР("Иванов";A2:D50;3;ЛОЖЬ) |
| ГПР | То же, но ищет в первой строке — по горизонтали | =ГПР("Март";A1:M5;3;ЛОЖЬ) | |
| ПРОСМОТРX | Современная замена ВПР и ГПР: ищет в любом направлении | =ПРОСМОТРX("Иванов";A2:A50;D2:D50) | |
| Подсчёт по условию | СУММЕСЛИ | Суммирует только ячейки, подходящие под условие | =СУММЕСЛИ(A2:A50;"Москва";B2:B50) |
| СЧЁТЕСЛИ | Считает количество ячеек, отвечающих условию | =СЧЁТЕСЛИ(A2:A50;"Оплачено") | |
| Текст | СЦЕПИТЬ | Склеивает текст из нескольких ячеек | =СЦЕПИТЬ(A2;" ";B2) |
| ЛЕВСИМВ | Берёт заданное число символов слева | =ЛЕВСИМВ(A2;3) | |
| Даты | ДАТА | Собирает дату из года, месяца и дня | =ДАТА(2026;7;24) |
| СЕГОДНЯ | Подставляет текущую дату, обновляется сама | =СЕГОДНЯ() |
Несколько уточнений, которые экономят часы. В ВПР четвёртый аргумент почти всегда должен быть ЛОЖЬ — это точное совпадение; иначе функция подставит «похожее» значение и тихо ошибётся. Функция ПРОСМОТРX удобнее и не ломается при вставке новых столбцов, но доступна начиная с Excel 2021 и в подписке Microsoft 365. В версиях 2016 и 2019 её нет — там выручают ВПР и связка ИНДЕКС с ПОИСКПОЗ.
Вместо устаревшей СЦЕПИТЬ в новых версиях лучше применять СЦЕП — она умеет склеивать сразу целый диапазон, — и ОБЪЕДИНИТЬ, которая автоматически вставляет разделитель между значениями. Сама СЦЕПИТЬ пока работает для совместимости со старыми файлами.
Вложенные формулы
Вложенная формула — это когда результат одной функции сразу становится аргументом другой. Так задачи решают «в один присест», без лишних вспомогательных столбцов. Классический пример — проверка внутри проверки:
=ЕСЛИ(B2>=90;"Отлично";ЕСЛИ(B2>=75;"Хорошо";"Нужно подтянуть"))
Здесь второй ЕСЛИ срабатывает только тогда, когда первое условие не выполнилось. Читать такие конструкции удобнее изнутри наружу. Если вложенных ЕСЛИ становится больше трёх, разумнее перейти на ЕСЛИМН — она короче и понятнее.
Очень частый и полезный приём — обернуть поиск в ЕСЛИОШИБКА, чтобы вместо пугающего #Н/Д показывался понятный текст:
=ЕСЛИОШИБКА(ВПР(A2;Прайс;2;ЛОЖЬ);"Нет в прайсе")
По такому же принципу комбинируют текстовые функции: например, =ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1) вытащит из полного имени только фамилию, а =ОКРУГЛ(СРЗНАЧ(B2:B10);2) посчитает среднее и сразу округлит его до двух знаков.
Именованные диапазоны
Именованные диапазоны делают формулы читаемыми. Вместо =СУММ(B2:B100) можно написать =СУММ(Продажи) — и через месяц не придётся вспоминать, что скрывалось за адресами. Особенно это ценно в длинных формулах со ставками, коэффициентами и справочниками.
Чтобы задать имя, выделите диапазон, щёлкните в поле «Имя» слева от строки формул, введите название без пробелов (например, Ставка_НДФЛ) и нажмите Enter. Все имена книги собраны в «Формулы → Диспетчер имён» — там их удобно править и удалять.
У имени есть приятный бонус: оно работает как абсолютная ссылка и не сдвигается при копировании формулы. Поэтому именованный диапазон — хорошая замена конструкциям с множеством знаков доллара: и надёжнее, и глазами читается легче.
Частые ошибки в формулах и как их исправить
Excel не «ломается» — он честно сообщает, что пошло не так. Разберём коды ошибок, которые встречаются чаще всего, и способы их устранить.
| Ошибка | Что означает | Как исправить |
|---|---|---|
#ДЕЛ/0! | Деление на ноль или на пустую ячейку | Проверьте делитель или оберните формулу: =ЕСЛИ(B2=0;0;A2/B2) |
#Н/Д | Значение не найдено — обычно в ВПР или ПРОСМОТРX | Проверьте, есть ли искомое в таблице и нет ли лишних пробелов; оберните в ЕСЛИОШИБКА |
#ЗНАЧ! | В формуле текст там, где ожидалось число | Найдите ячейку с текстом вместо числа, уберите пробелы и посторонние символы |
#ИМЯ? | Опечатка в названии функции или имени диапазона | Проверьте написание функции и существование именованного диапазона |
#ССЫЛКА! | Формула ссылается на удалённую ячейку | Задайте ссылку заново; не удаляйте строки и столбцы, на которые опираются формулы |
#ЧИСЛО! | Недопустимый аргумент или слишком большое число | Проверьте аргументы — например, корень из отрицательного числа не считается |
Подход всегда один: не паниковать, а прочитать код ошибки — он прямо указывает на причину. Навести порядок помогают инструменты проверки на вкладке «Формулы», о которых ниже.
Хотите работать в таблицах уверенно, а не искать формулы в интернете?
На курсе «Excel + Google Таблицы» вы разберёте формулы, функции, сводные таблицы и диаграммы на реальных рабочих задачах — с домашними заданиями и обратной связью преподавателя. Занятия можно проходить очно, в прямом эфире или онлайн, а полученные навыки применимы в работе с первого дня.
Отладка формул и горячие клавиши
Когда формула большая, а результат подозрительный, её нужно «разобрать по косточкам». В Excel для этого есть удобные инструменты.
- Вычислить формулу («Формулы → Вычислить формулу») — прогоняет расчёт по шагам и показывает промежуточные значения. Лучший способ понять, где именно ломается длинная вложенная формула.
- Клавиша F9 внутри формулы — выделите в строке формул любой фрагмент и нажмите
F9: Excel посчитает только его и покажет результат. НажмитеEsc, чтобы вернуть формулу в исходный вид. - Показать формулы (
Ctrl+`, клавиша с тильдой) — переключает лист в режим, где во всех ячейках видны формулы, а не результаты. Удобно для быстрой ревизии. - Влияющие и зависимые ячейки — стрелки на вкладке «Формулы» показывают, откуда ячейка берёт данные и куда они уходят дальше.
Набор горячих клавиш, который ускоряет работу с формулами:
F2— редактировать активную ячейку и подсветить её ссылки.F4— переключить тип ссылки: относительная, абсолютная, смешанная.F9— пересчитать книгу, а внутри формулы — вычислить выделенный фрагмент.Alt+=— вставить автосумму (СУММ) по соседнему диапазону.Ctrl+`— показать или скрыть все формулы на листе.Ctrl+Enter— ввести одну формулу сразу во все выделенные ячейки.
Пример: рабочая таблица расчёта зарплаты
Соберём всё вместе на понятной задаче — расчёте зарплаты за месяц. Допустим, в таблице есть оклад, число отработанных дней из нормы и процент премии.
Исходные данные: A2 — фамилия, B2 — оклад (60000), C2 — норма рабочих дней (20), D2 — отработано дней (19), E2 — премия в процентах (10).
Формулы по столбцам:
- Оплата за отработанные дни,
F2:=B2/C2*D2 - Премия от оклада,
G2:=B2*E2% - Начислено всего,
H2:=F2+G2 - НДФЛ 13%,
I2:=H2*13% - К выплате на руки,
J2:=H2-I2
Что получится: оплата за дни — 57 000 ₽, премия — 6 000 ₽, начислено — 63 000 ₽, удержан НДФЛ — 8 190 ₽, на руки — 54 810 ₽. Теперь строку 2 достаточно растянуть вниз: относительные ссылки сами подстроятся под каждого сотрудника. А если вынести ставку НДФЛ в отдельную ячейку и ссылаться на неё как $K$1, менять налог по всей таблице можно будет в одном месте.
Эта же логика лежит в основе любого расчётного файла — бюджета, сметы, прайса или отчёта о продажах. Меняются названия столбцов, но связка «ссылки + функции + абсолютный якорь для констант» остаётся одной и той же.
Итоги
- Любая формула начинается со знака
=и состоит из ссылок, операторов и функций; порядок действий — как в математике, а скобки меняют приоритет. - Относительные ссылки при копировании сдвигаются, абсолютные — со знаком
$— остаются на месте; переключает их клавишаF4. - Для большинства задач хватает полутора-двух десятков функций:
СУММ,ЕСЛИ,ВПРилиПРОСМОТРX,СУММЕСЛИ,СЧЁТЕСЛИи текстовых. - Коды вроде
#ДЕЛ/0!,#Н/Д,#ЗНАЧ!— не поломка, а подсказка о причине; во многих случаях спасаетЕСЛИОШИБКА. - Разбирать сложные формулы помогают «Вычислить формулу», клавиша
F9и режим показа формул, а именованные диапазоны делают их читаемее.
Вопросы и ответы
- С чего начинается любая формула в Excel?
Со знака равенства. Как только вы вводите в ячейку =, Excel понимает, что дальше идёт вычисление, а не текст. После знака равно записывают ссылки, числа, операторы и функции — например, =A1+B1 или =СУММ(A1:A10).
- Чем абсолютная ссылка отличается от относительной?
Относительная ссылка (A1) при копировании формулы сдвигается вместе с ней, а абсолютная ($A$1) остаётся на месте — знак доллара «замораживает» столбец и строку. Быстро переключить тип ссылки помогает клавиша F4.
- Какая функция заменяет ВПР в новых версиях Excel?
ПРОСМОТРX (XLOOKUP). Она ищет значение в любом направлении, не ломается при вставке столбцов и заменяет сразу ВПР, ГПР, ИНДЕКС и ПОИСКПОЗ. Доступна в Excel 2021 и Microsoft 365; в версиях 2016 и 2019 используйте ВПР или связку ИНДЕКС и ПОИСКПОЗ.
- Что делать с ошибкой #Н/Д в формуле поиска?
Ошибка #Н/Д значит, что искомое значение не найдено. Проверьте, есть ли оно в таблице, нет ли лишних пробелов и совпадает ли формат данных. Чтобы вместо кода показывался понятный текст, оберните формулу: =ЕСЛИОШИБКА(ВПР(...);"Не найдено").
- Как быстро поставить знак доллара в ссылке?
Поставьте курсор на ссылку внутри формулы и нажмите F4. Каждое нажатие меняет тип по кругу: A1, затем $A$1, затем A$1 и $A1. Это заметно быстрее, чем вводить доллары вручную с клавиатуры.
- Сколько функций нужно знать для уверенной работы?
Для большинства офисных задач достаточно 15–20 функций: СУММ, СРЗНАЧ, СЧЁТ, ЕСЛИ, ВПР или ПРОСМОТРX, СУММЕСЛИ, СЧЁТЕСЛИ, а также текстовые и функции дат. Остальное подключается по мере необходимости — важнее понимать логику ссылок и уметь читать ошибки.
Материал носит информационный характер; названия функций для актуальных версий Excel.