Формулы и функции
44 вопросов
Формулы и встроенные функции Excel для вычислений и обработки данных
Любая формула в Excel начинается со знака =. Затем пишите функцию или выражение: =A1+B1, =СУММ(A1:A10), =ЕСЛИ(A1>0;"Да";"Нет"). Для расчёта нажмите Enter.
Выберите пустую ячейку под столбцом чисел и нажмите «Σ» на вкладке «Главная» (в Windows — Alt+=). Проверьте предложенный диапазон и нажмите Enter. Можно ввести формулу вручную: =СУММ(A1:A10).
Добавьте знак доллара $ перед буквой столбца и/или цифрой строки: $A$1 — полная фиксация, $A1 — только столбец, A$1 — только строка. Выделите ссылку и нажмите F4 для быстрого переключения типа.
Процент от числа: =число*процент/100 или =A1*B1, если B1 содержит долю, например 0,15 (15%). Пример: =5000*15% = 750. Доля от суммы: =часть/целое с форматом «Процентный».
СУММЕСЛИ(диапазон_условия; критерий; диапазон_суммирования) — суммирует только те строки, где выполняется условие. Пример: =СУММЕСЛИ(A:A;"Яблоко";B:B) сложит все продажи яблок из столбца B.
СУММЕСЛИ(диапазон_условия; условие; диапазон_суммирования) суммирует ячейки, удовлетворяющие условию. Например: =СУММЕСЛИ(A2:A100;"Яблоки";B2:B100) — сумма продаж яблок.
СУММЕСЛИ(диапазон; критерий; диапазон_суммирования) суммирует по одному условию. СУММЕСЛИМН в Excel — по нескольким условиям сразу. Например: =СУММЕСЛИ(B:B;"Москва";C:C) сложит продажи только по Москве.
Функция ЕСЛИ проверяет условие и возвращает одно из двух значений. Синтаксис: =ЕСЛИ(условие; значение_если_истина; значение_если_ложь). Пример: =ЕСЛИ(A1>100;"Много";"Мало").
Оберните ВПР в ЕСЛИОШИБКА: =ЕСЛИОШИБКА(ВПР(A2;B:C;2;0);"Не найдено"). Если ВПР не находит значение и возвращает #Н/Д, ЕСЛИОШИБКА подставляет ваш текст вместо ошибки.
Абсолютная ссылка в Excel ($A$1) не меняется при копировании формулы. Относительная (A1) смещается. Знак доллара фиксирует строку (A$1), столбец ($A1) или оба сразу ($A$1). Переключение — клавиша F4.
ВПР ищет значение в первом столбце таблицы и возвращает данные из заданного столбца той же строки. Синтаксис: =ВПР(что_ищем; таблица; номер_столбца; 0).
Процент от числа: =A1*B1/100, если в B1 введено число 20 для 20%; если же B1 содержит значение 20% (то есть 0,2) — =A1*B1. Для доли используйте =часть/целое с форматом ячейки «Процентный».
Введите в C2 формулу =СУММ($B$2:B2) и скопируйте вниз. Начало диапазона $B$2 зафиксировано, а его конец при копировании смещается: B3, B4 и далее.
Используйте функцию =ОКРУГЛ(число; количество_знаков). Например, =ОКРУГЛ(3,7856; 2) даст 3,79. Для округления вверх — =ОКРУГЛВВЕРХ(), вниз — =ОКРУГЛВНИЗ().
Оберните любую формулу в ЕСЛИОШИБКА: =ЕСЛИОШИБКА(формула; значение_при_ошибке). Если формула вернёт ошибку, Excel подставит ваше значение. Пример: =ЕСЛИОШИБКА(ВПР(A1;B:C;2;0);"Не найдено").
Как прибавить процент к числу в Excel: =A1*(1+10%) или =A1+A1*10%. Если процент хранится в ячейке B1 (например, 10% как число 0,1): =A1*(1+B1). Чтобы прибавить процент к столбцу — скопируйте формулу вниз.
Создайте вспомогательный ключ из нескольких полей и ищите его через ВПР. Без вспомогательного столбца используйте ИНДЕКС и ПОИСКПОЗ с произведением условий. В Microsoft 365, Excel 2021 и 2024 подходит также ПРОСМОТРX.
СЧЁТЕСЛИМН считает ячейки, где выполняются ВСЕ условия одновременно: =СЧЁТЕСЛИМН(A:A;"Москва";B:B;">1000"). Для подсчёта по одному условию используйте СЧЁТЕСЛИ.
Выделите диапазон, в поле имени слева от строки формул введите понятное имя и нажмите Enter. Для выбора области действия используйте Формулы → Присвоить имя. Имена делают формулы понятнее.
Используйте синтаксис Лист!Ячейка: =Лист2!A1 или ='Название листа'!B5. Для диапазона: =СУММ(Лист2!A1:A10). Если имя листа содержит пробелы или спецсимволы, заключите его в одинарные кавычки.
При ненулевом старом значении процентное изменение вычисляют как =(B2-A2)/A2, где A2 — старое, B2 — новое. Примените процентный формат: результат 0,2 отобразится как 20%. Дополнительно умножать на 100 при таком формате не нужно.
=СЧЁТ() считает числовые ячейки, =СЧЁТЗ() — все непустые, =СЧЁТЕСЛИ() — ячейки по условию. Пример: =СЧЁТЕСЛИ(A1:A100;"Да") посчитает, сколько раз встречается «Да».
ИНДЕКС(массив; номер_строки; номер_столбца) возвращает значение по координатам. ПОИСКПОЗ(искомое; диапазон; 0) находит позицию. Вместе ИНДЕКС+ПОИСКПОЗ — альтернатива ВПР, позволяющая искать в обе стороны.
Используйте функцию =СРЗНАЧ(A1:A100). Среднее значение в Excel можно увидеть и без формулы — просто выделите диапазон, и в строке состояния внизу отобразится среднее в Excel.
Вычитание в Excel: =A1-B1 или =A1-500. Как вычесть процент: =A1*(1-10%) или =A1-A1*10%. Разница между двумя числами — обычный знак минус в формуле.
СЧЁТЕСЛИМН считает ячейки, удовлетворяющие сразу нескольким условиям. Синтаксис: =СЧЁТЕСЛИМН(диап1;крит1;диап2;крит2;...). Пример: =СЧЁТЕСЛИМН(A:A;"Москва";B:B;">1000") — сколько строк Москва И >1000.
И(условие1;условие2) вернёт ИСТИНА только когда ВСЕ условия истинны. ИЛИ(условие1;условие2) — когда хотя бы одно. Обычно используются внутри ЕСЛИ: =ЕСЛИ(И(A1>0;B1>0);"Оба положительны";"").
В Microsoft 365, Excel 2021 и 2024 для диапазона без пустых ячеек используйте =ЧСТРОК(УНИК(A2:A100)). Если есть пустые ячейки, предварительно исключите их через ФИЛЬТР. В старых версиях подойдут расширенный фильтр или сводная таблица.
Ошибка #ЗНАЧ! возникает при несовместимости типов данных: формула ожидает число, но получает текст. Проверьте ячейки-аргументы — там может быть текст, пробелы или специальные символы.
Оберните формулу в ЕСЛИОШИБКА или добавьте проверку через ЕСЛИ: =ЕСЛИ(B2=0;"";A2/B2). Или используйте =ЕСЛИОШИБКА(A2/B2;0) для замены ошибки на ноль.
Ссылка в Excel на другую ячейку: просто введите =A1 или =Лист2!B3. Гиперссылка на сайт или файл вставляется через Ctrl+K или «Вставка» → «Ссылка».
Формула массива в Excel обрабатывает диапазон ячеек как единое целое. В старых версиях вводится через Ctrl+Shift+Enter, в Microsoft 365, Excel 2021 и новее — обычным Enter. Пример: =СУММ(A1:A10*B1:B10) — перемножит элементы и сложит результаты.
ЕСЛИМН проверяет несколько условий по очереди и возвращает результат первого выполненного: =ЕСЛИМН(A1>90;"Отл";A1>70;"Хор";ИСТИНА;"Удовл"). ПЕРЕКЛЮЧ сравнивает выражение с набором значений.
#ССЫЛКА! возникает при удалении ячеек, на которые ссылается формула. #ИМЯ? — при опечатке в имени функции или именованного диапазона. Проверьте формулу и восстановите удалённые ссылки.
Степень: оператор ^ или функция =СТЕПЕНЬ(основание; показатель). Квадратный корень: =КОРЕНЬ(число); для неотрицательного числа и натурального n корень n-й степени: =СТЕПЕНЬ(число; 1/n).
Для ставки НДС 22%: начислить налог на цену без НДС — =A1*22%; выделить налог из цены с НДС — =A1*22/122. Основная ставка в РФ повышена до 22% с 1 января 2026 года. Ставку для конкретной операции вынесите в отдельную ячейку.
Функция ТЕКСТ преобразует число или дату в текстовую строку с нужным форматом: =ТЕКСТ(A1;"ДД.ММ.ГГГГ") или =ТЕКСТ(B1;"# ##0,00 ₽"). Результат — текст, а не число.
СМЕЩ возвращает ячейку или диапазон, сдвинутый от начальной точки на заданное количество строк и столбцов: =СМЕЩ(A1;3;2) вернёт ссылку на C4; в ячейке отобразится её значение. Размер диапазона меняется, если его высота или ширина вычисляются формулой.
ГПР ищет значение в первой строке таблицы и возвращает значение из нужной строки. Синтаксис: =ГПР(искомое; таблица; номер_строки; 0). Это горизонтальный аналог функции ВПР.
ПРОСМОТРX (XLOOKUP) в Microsoft 365, Excel 2021 и новее, а также в веб-версии — современная замена ВПР. Синтаксис: =ПРОСМОТРX(искомое; диапазон_поиска; диапазон_результата). Ищет в любом направлении, возвращает несколько столбцов, использует прямой диапазон результата вместо числового номера столбца.
Выделите диапазон, кликните в поле имени (левее строки формул) и введите имя. В формулах вместо A1:A100 пишите =СУММ(Продажи) — именованный диапазон удобнее и нагляднее.
Функция МАКС в Excel: =МАКС(A1:A100) вернёт наибольшее число. Функция МИН: =МИН(A1:A100) — наименьшее. Для поиска максимума по условию используйте МАКСЕСЛИМН (Excel 2019+).
Медиана в Excel: =МЕДИАНА(A1:A100). Мода в Excel: =МОДА(A1:A100) или =МОДА.НСК(A1:A100) для нескольких мод. Стандартное отклонение: =СТАНДОТКЛОН.В(A1:A100) (выборка) или =СТАНДОТКЛОН.Г(A1:A100) (генеральная совокупность).
Используйте функцию ПРЕОБР (CONVERT): =ПРЕОБР(A1;"km";"mi") переведёт километры в мили. Поддерживает длину, вес, температуру, давление, скорость, энергию и другие единицы.