Рутинные задачи при работе с таблицами Excel можно автоматизировать с помощью специальных формул. Они позволят выполнить сложные вычисления, значительно ускорят работу с большими объемами данных и помогут избежать ошибок.
Как использовать формулы в Excel
В Excel формулы вводят в строке формул, которая находится в верхней части окна Excel, под лентой инструментов.
Чтобы начать ввод:
- Выберите ячейку таблицы, в которую хотите вставить формулу.
- Введите формулу в строке формул. Формула всегда начинается со знака «=».
- Нажмите Enter для завершения ввода и отображения результата расчета в выбранной ячейке.
Какие операторы используют в формулах
В формулах Excel используют операторы для выполнения математических, логических и текстовых операций. Вот основные виды операторов:
Арифметические операторы
Операторы сравнения
В зависимости от результата формулы значением целевой ячейки будет ИСТИНА или ЛОЖЬ.
Текстовый оператор
Оператор конкатенации & соединяет («склеивает») текстовые строки.
Пример:
Ячейка A1 содержит значение «Skill», ячейка B1 содержит значение «Factory».
Результат формулы «=A1 & B1» будет соединенной строкой «SkillFactory».
Обратите внимание, что при соединении значений в конечной строке пробел отсутствует. Чтобы добавить пробел, нужно включить его в формулу: «=A1 & » » & B1».
Логические операторы
Ссылочные операторы
Оператор ссылки
Оператор абсолютной ссылки $ фиксирует ссылку на конкретную ячейку или диапазон.
Пример:
Если в формуле сослаться на ячейку A1 таким образом — $A$1, то при копировании формулы (например, при «растягивании» ячейки на нужный диапазон) ссылка на ячейку A1 не изменится, так как она зафиксирована.
Какие основные формулы есть в Excel и как их использовать
Расчет суммы
Формула для расчета суммы используется для сложения числовых значений в заданном диапазоне ячеек. Она помогает рассчитать общую сумму значений в столбце, строке или любом другом диапазоне ячеек. Для сложения можно использовать оператор + или формулу СУММ.
Пример
Допустим, у вас есть таблица с продажами товаров за несколько дней. Вам нужно рассчитать общую сумму продаж каждого товара.
В ячейке B7 рассчитываем сумму продаж товара A по формуле «=СУММ(B2:B6)». Это эквивалентно формуле «=B2 + B3 + B4 + B5 + B6», но гораздо короче.
В ячейке C7 проделаем то же самое для товара B с помощью формулы «=СУММ(C2:C6)». Можно просто растянуть ячейку B7 до C7.
Умножение
Используется для перемножения числовых значений из двух или более ячеек. Можно выполнить при помощи оператора * или формулы ПРОИЗВЕД.
Пример
У вас есть таблица, где указано количество проданных единиц товара и цена за единицу. Вы хотите рассчитать общую выручку для каждого дня.
В ячейке D2 указываем формулу расчета общей выручки, умножив цену одной единицы товара на количество проданного товара. Полученную формулу «=C2*B2» применяем ко всему столбцу выручки.
Произведение ячеек C2 и B2, которое мы оформили как «=C2*B2», можно также записать как «=ПРОИЗВЕД(C2:B2)», указав диапазон ячеек для перемножения. Этот способ будет актуален для большого количества множителей.
Например, используя формулу «=ПРОИЗВЕД(A14:D14)», мы получили произведение всего диапазона ячеек.
Объединение (конкатенация)
Кроме оператора & существует формула СЦЕПИТЬ, которая также нужна для объединения (сцепления) текста из нескольких ячеек в одну. Эти два способа выполняют одинаковые функции, но формула СЦЕПИТЬ удобнее, когда нужно объединить текст из большого количества ячеек.
Пример
Например, есть таблица со столбцами имен и фамилий, которые вы хотите объединить в одну строку.
Воспользуемся формулой СЦЕПИТЬ. Вставим ее в ячейку C1: «=СЦЕПИТЬ(A2; » «; B2)», а затем применим ко всему столбцу «Имя и фамилия». Указав в формуле пробел, мы избежим слитного написания имени и фамилии.
Округление
Формула ОКРУГЛ в Excel округляет числовые значения до заданного количества десятичных знаков. Это полезно для сокращения длинных дробей или для того, чтобы добиться точности до определенного знака после запятой.
Пример
Есть таблица с результатами расчетов, которые вам нужно округлить до сотых долей.
В ячейку B2 вводим формулу «=ОКРУГЛ(A2; 2)». Она округлит значение в ячейке A2 до двух знаков после запятой. Растянем ее на весь столбец «Округленное значение» и получим округленные дроби для остальных исходных значений.
Также можно контролировать, в какую сторону будет округлена дробь: вверх — ОКРУГЛВВЕРХ или вниз — ОКРУГЛВНИЗ.
Нахождение корня
Квадратный корень
Для нахождения квадратного корня в Excel используется формула КОРЕНЬ.
Пример
У вас есть таблица с числами, и вам нужно найти квадратный корень для каждого из этих чисел.
В ячейке B2 пишем формулу квадратного корня — «=КОРЕНЬ(A2)» и растягиваем ее на весь столбец.
Корень любой степени
Для извлечения корня любой степени (в том числе и квадратного) нужно воспользоваться оператором возведения в степень ^. В качестве степени нужно взять обратную степень корня. Например, для кубического корня — 1/3, для корня четвертой степени — 1/4 и так далее.
Пример
Нужно рассчитать кубический корень каждого числа в столбце «Число».
Запишем формулу «=A2^(1/3)» в ячейке B2 и растянем значение по всему столбцу. Получим корень третьей степени для столбца «Число».
Поиск значения по диапазону или таблице (ВПР)
ВПР (или VLOOKUP) помогает найти значение в первом столбце определенного диапазона и вернуть соответствующее значение из другого столбца в той же строке. Эта функция очень полезна в работе с большими таблицами.
В этой статье подробно рассказали о возможностях функции ВПР и приложили пошаговую инструкцию.
Изменение регистра
В Excel есть две функции для изменения регистра текста: ПРОПИСН и СТРОЧН.
ПРОПИСН
Преобразует все буквы в тексте в прописные (заглавные).
Пример
Текст из столбца «Исходный текст» нужно перевести в заглавные буквы.
В ячейке B2 пишем формулу «=ПРОПИСН(A2)» и растягиваем ее на весь столбец.
СТРОЧН
Нужно преобразовать все буквы в тексте в строчные (маленькие).
Пример
Можно также перевести текст из столбца «Заглавные буквы» в строчные.
В ячейку C2 введем формулу «=СТРОЧН(B2)» и растянем ее на весь столбец.
Убрать пробелы
Чтобы убрать лишние пробелы, есть формула СЖПРОБЕЛЫ. Эта функция оставляет только один пробел между словами.
Пример
Представим, что при копировании информации в таблицу форматирование текста было нарушено и вместо одного пробела между словами образовалась ненужная пустота. Нужно убрать лишние пробелы из исходного текста.
С помощью формулы «=СЖПРОБЕЛЫ(A2)» получим отформатированную строку в ячейке B2. Применим формулу ко всему столбцу.
Условные формулы
Чтобы использовать функции условий в Excel, применяют ЕСЛИ и его производные — ЕСЛИМН и ЕСЛИОШИБКА.
- ЕСЛИ
Выполняет логическую проверку условий, которые возвращают значение ИСТИНА или ЛОЖЬ.
Пример
У вас есть таблица с результатами экзаменов студентов. Вам нужно определить, сдал студент экзамен или нет, исходя из его баллов за экзамен. Проходной балл — 50.
В ячейку B2 вставим формулу «=ЕСЛИ(A2>=50; «Сдал»; «Не сдал»)» и применим ее ко всему столбцу. Из формулы следует, что мы получим значение «Сдал», если результат проверки условия — ИСТИНА, и «Не сдал», если ЛОЖЬ.
- ЕСЛИМН
Применяется тогда, когда условий несколько. Работает по такому принципу:
Функция последовательно проверяет условия, перечисленные в формуле, и, как только находит первое условие со значением ИСТИНА, возвращает соответствующее этому условию значение. Другие условия не проверяются, даже если они тоже истинны.
Пример
В той же таблице с баллами студентов за экзамен теперь нужно дать оценку согласно полученным баллам. Всего оценок четыре: «Отлично», «Хорошо», «Удовлетворительно» и «Неудовлетворительно».
Добавим формулу «=ЕСЛИМН(A2>=80; «Отлично»; A2>=60; «Хорошо»; A2>=50; «Удовлетворительно»; A2<50; «Неудовлетворительно»)» в ячейку C3 и растянем ее на весь столбец.
Обратите внимание, что оценки в условии нужно расположить в порядке убывания. Например, если сделать по возрастанию, то все оценки разделятся на «Неудовлетворительно» (<50) и «Удовлетворительно» (>=50).
- ЕСЛИОШИБКА
Эта функция нужна, чтобы обрабатывать возникающие при расчетах ошибки с помощью сообщения об ошибке. В сообщении можно указать любой текст.
Пример
Обработайте классический пример ошибки при делении на ноль. Пусть сообщение об ошибке звучит как «Делить на ноль нельзя».
В ячейку C2 ставим формулу «=ЕСЛИОШИБКА(A2/B2; «Делить на ноль нельзя»)» и растягиваем значение на весь столбец.
- Как применять условия для функции СУММ
Функция ЕСЛИ применяется в связке с другими функциями, например с СУММ. Рассмотрим на примерах:
— СУММЕСЛИ, чтобы посчитать сумму ячеек по определенному критерию.
«=СУММЕСЛИ(A2:A4; «Продажи»; B2:B4)» покажет итоговую сумму по показателю «Продажи».
— СУММЕСЛИМН, чтобы посчитать сумму значений в диапазоне, которые соответствуют нескольким условиям.
«=СУММЕСЛИМН(B2:B4; A2:A4; «Продажи»; C2:C4; «Январь»)» суммирует значения ячеек, где в столбце A указано «Продажи», а в столбце C — «Январь». В данном случае есть только одна такая ячейка.
- Где еще применяют ЕСЛИ
— СЧЁТЕСЛИ, чтобы посчитать количество ячеек, соответствующих одному критерию.
«=СЧЁТЕСЛИ(A1:A3; «Продажи»)» — эта формула подсчитает количество ячеек в диапазоне A1:A3, где указано слово «Продажи».
— СЧЁТЕСЛИМН, чтобы посчитать количество ячеек, которые соответствуют нескольким критериям одновременно.
«=СЧЁТЕСЛИМН(A1:A4; «Продажи»; B1:B4; «Январь»)» посчитает количество ячеек, где в столбце A указано «Продажи», а в столбце B — «Январь». Если оба условия выполняются для одной строки, то она будет включена в подсчет.
- Извлечение текста (ПСТР)
Функция ПСТР нужна для извлечения определенного количества символов из строки текста или числового ряда, начиная с заданной позиции.
Пример
Вам нужно извлечь из исходника три символа, начиная с четвертого.
C помощью формулы «=ПСТР(A2; 4; 3)» извлекаем символы из строк столбца A в столбец B.
Обратите внимание, что даже если функция не получит необходимое по условию количество символов в строке, она все равно выполнит операцию с тем количеством символов, которые есть. Если подходящие символы в строке отсутствуют, то в ячейке результата получится пустая строка.
- Замена текста
С помощью функции ЗАМЕНИТЬ можно заменить часть строки текста на другую, начиная с указанной позиции и на определенное количество символов.
Пример
Например, вам нужно скрыть последние цифры кода или номера из первого столбца, начиная с 5 символа, на 6 символов. Сделать это можно с помощью замены на звездочки *.
В ячейку B2 вставим формулу «=ЗАМЕНИТЬ(A2; 5; 6; «******»)» и применим ее ко всему столбцу. Количество новых символов для замены не обязательно должно совпадать с количеством заменяемых старых символов.
Подведем итог
- Для работы с формулами в Excel используют специальные операторы: арифметические, логические сравнения и другие.
- Математические операции, такие как сложение и умножение, можно провести двумя способами: через операторы + и * либо с помощью функций СУММ и ПРОИЗВЕД соответственно.
- В Excel можно округлять дроби с помощью функции ОКРУГЛ. В большую сторону — ОКРУГЛВВЕРХ, в меньшую — ОКРУГЛВНИЗ.
- Строки можно «скреплять» в одну с помощью оператора & или формулы СЦЕПИТЬ. При этом нужно вручную поставить пробел между ними при необходимости.
- Для работы с регистром символов в строке есть формулы ПРОПИСН и СТРОЧН.
- Формулы условий используют функцию ЕСЛИ и проверяют условия на значение ИСТИНА или ЛОЖЬ. С их помощью можно проверять диапазоны на соответствие требованиям, обрабатывать ошибки, выбирать конкретные ячейки для суммирования и т. д.
- Из ячеек можно извлекать установленное количество символов с помощью функции ПСТР, заменять символы через ЗАМЕНИТЬ, убирать пробелы с помощью СЖПРОБЕЛЫ.
