Формулы в Excel: 10 базовых + 16 формул для digital-специалистов
Поговорим о работе с таблицами и формулами. Собрали базу о работе с Excel для тех, кто чувствует себя здесь неуверенно + мастхэв для digital-специалистов.
Если с основами вы уже знакомы, переходите сразу к нужному разделу:
-
Формулы Excel: из чего состоят, описание и как их прописывать
-
Основные формулы Excel: 10 базовых + 16 формул для digital-специалистов
Какие функции могут выполнять формулы Excel и чем они упростят вам жизнь
Пройдемся здесь коротко. Формула — это автоматизация, и как любая автоматизация она ускоряет рутинные процессы и исключает ошибки. Например, с помощью формул Excel можно:
-
выполнять простые и сложные арифметические действия;
-
анализировать данные (поиск значений, фильтрация, сортировка);
-
обрабатывать текст (объединение, поиск, извлечение частей строки);
-
работать с датами и временем (вычисление разницы, добавление дней);
-
автоматизировать расчеты с условиями.
Например, вместо ручного сложения сотни чисел можно использовать =СУММ(A1:A100), и получить результат мгновенно.
Формулы Excel: из чего состоят, описание и как их прописывать
Формула в Excel состоит из нескольких основных элементов.
|
Элемент |
Зачем нужен |
Примеры |
|
Знак равенства = |
С этого символа начинается любая формула. Это — сигнал Excel, что в ячейке содержится вычисление, а не просто текст |
=A1+B1 Результат вычисления — сумма данных в этих ячейках |
|
Аргументы |
Величины, используемые при вычислениях. Ими могут быть числа, текст, логические значения (TRUE или FALSE), массивы или ссылки на ячейки |
число (10, 3.14) |
|
дата (01.01.2024) |
||
|
текст («Отчет») |
||
|
ссылки на ячейки (A1, B2:B10) |
||
|
Массивы {} |
Данные из смежных ячеек таблицы, которые используют в расчетах как единую группу значений |
{=СУММ(A1:A3 * B1:B3)} Формула перемножает элементы массивов A1:A3 и B1:B3, а затем суммирует результаты |
|
Операторы |
Выполняют арифметические, логические и текстовые действия с аргументами |
арифметические (+, -, *, /, ^) |
|
сравнения (=, >, <, >=, <=, <>) |
||
|
текстовые (& для объединения строк) |
||
|
ссылочные (: — диапазон, ; — разделитель в списке аргументов) |
||
|
Функции |
Встроенные формулы Excel, выполняющие сложные вычисления |
СУММ(A1:A10) суммирует значения |
|
СРЗНАЧ(A1:A10) вычисляет среднее |
||
|
ЕСЛИ(A1>10, "Большое", "Маленькое") — условие |
Применить формулу Excel можно несколькими способами: прописать ее вручную, использовать встроенную формулу или протянуть ее.
Как прописать формулу в Excel вручную
Чтобы вручную создать формулу в таблице:
-
Кликните на ячейку, в которой хотите получить результат.
-
Введите знак = (с этого символа начинается любая формула, иначе Excel воспримет ее как текст).
-
Введите нужные элементы формулы (атрибуты, операторы и функции).
-
Нажмите Enter.
Если вы хотите отредактировать формулу, дважды кликните по ячейке с ней, внесите изменения и нажмите Enter.
Как пользоваться встроенными формулами
Excel содержит множество встроенных формул, которые упрощают вычисления. Чтобы ими воспользоваться, нужно:
-
Выбрать ячейку, в которой нужно что-то посчитать.
-
Ввести = и начать писать название функции (например, =СУММ).
-
Выбрать нужную функцию в подсказках.
-
Ввести аргументы (например, диапазон A1:A10).
-
Нажать Enter.
Так у нас получилась формула =СУММ(A1:A10). Если вы не помните синтаксис функции, нажмите fx (рядом со строкой формул), а затем выберите нужную (можно искать по алфавиту, по типу функции и по краткому описанию действия, которое вам нужно).
Введите аргументы и нажмите ОК.
Как протянуть формулу
Так вы сможете быстро копировать ее в другие ячейки, изменяя ссылки на данные автоматически:
-
Введите формулу в первую ячейку (например, =A1*2).
-
Наведите курсор на правый нижний угол этой ячейки. А когда он превратится в черный крестик (маркер заполнения), зажмите левую кнопку мыши и протяните:
-
вниз — если формулу надо протянуть в столбце;
-
вбок — если формулу надо протянуть в строке.
-
Когда отпустите кнопку, Excel автоматически заполнит ячейки формулами с обновленными ссылками.
Основные формулы Excel: 10 базовых + 16 формул для digital-специалистов
Формулы в Excel могут быть как простыми (=A1+A2), так и сложными: содержащими несколько функций, логических условий и ссылок на другие листы или файлы.
Кстати, в одной формуле можно использовать несколько функций. Представим, что мы хотим найти среднее значение всех положительных чисел в диапазоне A1:A10. Для этого используется формула =СРЗНАЧ(ЕСЛИ(A1:A10>0, A1:A10)).
Мы поговорим о разных уровнях сложности:
-
База: МИН, МАКС, СРЗНАЧ, СЦЕПИТЬ, ОКРУГЛ, ЕСЛИ, СЧЁТЕСЛИ, СЕГОДНЯ, ДНИ, КОРРЕЛ;
-
Мастхэв для digital-специалистов: ДЛСТР, ЕСЛИМН, СЖПРОБЕЛЫ, СУММЕСЛИМН, СЧЁТЕСЛИМН, ВПР и другие.
База: функции МИН, МАКС, СРЗНАЧ, СЦЕПИТЬ, ОКРУГЛ, ЕСЛИ, СЧЁТЕСЛИ, СЕГОДНЯ, ДНИ, КОРРЕЛ
Разберем десять простых функций, которые пригодятся каждому.
Функция МИН
=МИН(число1;[число2];...)
Находит наименьшее число в выбранном диапазоне.
Функция МАКС
=МАКС(число1;[число2];...)
Находит наибольшее значение в рамках выбранного диапазона.
Функция СРЗНАЧ
=СРЗНАЧ(число1;[число2];...)
Вычисляет среднее арифметическое значений в указанном диапазоне (суммирует все числа и делит на их количество). Например, пригодится, если вы хотите найти среднюю стоимость клика по рекламе за какой-то период.
Функция СЦЕПИТЬ
=СЦЕПИТЬ(текст1;[текст2];...)
Объединяет несколько текстовых ячеек в одну. Например, пригодится, если вам нужно объединить артикулы товаров с их названиями (не забудьте добавить между ними пробел).
Функция ОКРУГЛ
=ОКРУГЛ(число;количество разрядов)
Функция округляет число до указанного количества десятичных разрядов. Если указать отрицательное число разрядов, будет округлена целая часть; если ноль — число будет округлено до ближайшего целого числа.
Функция ЕСЛИ
=ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])
Выполняет логическую проверку: возвращает одно значение, если условие истинно, и другое — если оно ложно.
Например, на скриншоте выше выполняется проверка длины заголовков. Если длина заголовка превышает 56 символов, то его нужно переписать.
Функция СЧЁТЕСЛИ
=СЧЁТЕСЛИ(диапазон;критерий)
Подсчитывает количество ячеек в диапазоне, которые удовлетворяют заданному условию.
СЧЁТЕСЛИ считает, сколько раз что-то встречается. Часто нужен простой подсчет: сколько раз в списке встречается конкретное значение — например, товар или статус.
В нашем примере мы узнали, сколько товаров было продано 1 марта.
Функция СЕГОДНЯ
=СЕГОДНЯ()
Функция СЕГОДНЯ() в Excel и Google Sheets возвращает текущую дату (системную дату компьютера), без времени. Ее, например, удобно использовать в графиках, расписаниях, ежедневных отчетах или при расчете дней, оставшихся до дедлайна. Значение обновляется каждый день автоматически.
Функция ДНИ
=ДНИ(конечная дата; начальная дата)
Считает, сколько дней между двумя датами. Обратите внимание, что первой вводится конечная дата. Функция пригодится, например, маркетологам для мониторинга рекламных кампаний — чтобы не менять количество прошедших дней вручную.
Функция КОРРЕЛ
=КОРРЕЛ(массив1;массив2)
Функцию КОРРЕЛ используют для вычисления коэффициента корреляции между двумя наборами чисел — то есть она показывает в процентах, насколько сильно связаны два набора данных.
Функция полезна, например, маркетологам для анализа эффективности рекламы: на скриншоте выше видно, что количество продаж слабо коррелирует с расходами на их продвижение.
Формула помогает проверить гипотезу о наличии и силе корреляции. Например, между макроконверсиями и микроконверсиями.
Мастхэв для digital-специалистов: ДЛСТР, ЕСЛИМН, СЖПРОБЕЛЫ, СУММЕСЛИМН, СЧЁТЕСЛИМН, ВПР и другие
Узнали у digital-специалистов, какие функции они используют чаще всего. КОРРЕЛ ДНИ в них тоже вошли, но о них говорили выше.
Функция ДЛСТР
=ДЛСТР(ячейка с текстом)
Функция определяет длину текста: считает число символов в текстовой строке.
Функция пригодится, когда нужно посчитать, сколько символов в тексте. Например, для расчета символов в различных элементах объявлений, а также для тегов Sales_notes в фиде. В общем, везде, где существуют лимиты по символам.
Функция ЕСЛИМН
=ЕСЛИМН(условие1; значение1; [условие2; значение2]; [условиe3; значение3])
Функция проверяет соответствие одному или нескольким условиям.
Функция пригодится, если нужно найти и обозначить ячейки, которые содержат определенный текст. Часто она нужна при построении сводных таблиц с кластеризацией по какому-либо признаку.
Кстати, то же самое можно сделать с помощью поочередного перебора фильтров по содержимому ячеек, но с функцией быстрее и удобнее. В примере на скриншоте выше мы кластеризуем группы по ценовым диапазонам, чтобы потом построить сводную таблицу по ним же.
Функция СЖПРОБЕЛЫ
=СЖПРОБЕЛЫ(текст)
Функция убирает лишние пробелы в тексте.
Функция поможет избавиться от лишних пробелов после массового редактирования ячеек с текстом, например, заголовков.
Функция СУММЕСЛИМН
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2]; [условие2]; ...)
Функция суммирует ячейки, удовлетворяющие одному или нескольким условиям.
Например, функция =СУММЕСЛИМН(C1:C10; B1:B10; «>100»; A1:A10; «Яблоки») суммирует значения в C1:C10, где B1:B10 > 100 и A1:A10 = «Яблоки».
Обратите внимание, что если вы используете СУММЕСЛИМН для данных из внешнего закрытого файла Excel, функция вернет ошибку #ЗНАЧ! Это происходит, так как СУММЕСЛИМН требует прямого доступа к исходным данным в реальном времени. Можно использовать СУММПРОИЗВ — она работает, даже если файл-источник закрыт.
Функция СЧЁТЕСЛИМН
=СЧЁТЕСЛИМН(диапазон1; условие1; [диапазон2]; [условие2]; ...)
Функция считает ячейки, соответствующие нескольким критериям.
Функция полезна при анализе данных с комбинированными условиями (например, продажи в определенном регионе и периоде).
Функция ВПР
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Функция находит совпадения по строкам в таблице или диапазоне.
Функция пригодится, когда нужно найти совпадения в двух массивах данных и подтянуть значения из одной таблицу в другую. Также существует очень полезная вариация этой функции: по двум или более столбцам.
Функция ИНДЕКС/ПОИСКПОЗ
=ИНДЕКС(диапазон_результатов; ПОИСКПОЗ(искомое_значение; диапазон_поиска; 0))
Функция — более гибкая альтернатива ВПР.
Функция полезна при поиске данных в таблицах с нестандартной структурой.
Функция СУММ
=СУММ(число1;[число2];...)
Функция суммирует значения в ячейках.
Функция пригодится, когда нужно посчитать сумму значений в ячейках.
Функция СУММПРОИЗВ
=СУММПРОИЗВ(массив1; [массив2]; ...)
Функция умножает элементы массивов и возвращает сумму произведений. Может использоваться для условных расчетов.
Функцию удобно применять для сложных расчетов без использования SUMIFS/COUNTIFS.
Функция СОРТ
=СОРТ(диапазон; [столбец_сортировки]; [по_возрастанию])
Функция динамически сортирует диапазон по указанному столбцу.
Например, функция =СОРТ(A1:C10; 2; −1) сортирует данные A1:C10 по второму столбцу (B) в порядке убывания.
Функция ФИЛЬТР
=ФИЛЬТР(диапазон; условие; [значение_при_ошибке])
Функция динамически фильтрует данные по заданному условию.
Функция полезна для создания автоматических отчетов без ручной фильтрации.
Функция УНИК
=УНИК(диапазон)
Функция возвращает уникальные значения из диапазона.
Функция полезна для анализа повторяющихся данных, создания выпадающих списков.
Функция ЕСЛИОШИБКА
=ЕСЛИОШИБКА(формула; «значение_при_ошибке»)
Функция заменяет ошибки на заданное значение.
Например, =ЕСЛИОШИБКА(A1/B1; 0) возвращает 0, если деление на ноль.
Функция ТРАНСП
=ТРАНСП(массив)
Функция преобразовывает вертикальные списки в горизонтальные.
Функция меняет ориентацию таблицы: строки становятся столбцами и наоборот. Например, =ТРАНСП(A1:B4) преобразует вертикальную таблицу 2×4 в горизонтальную 4×2.
Что с формулами в Google Sheet — основы работы
Google Sheets — бесплатный облачный аналог Excel, где можно работать с данными онлайн. Главная фича — возможность совместной работы. Что еще:
-
автоматическое сохранение;
-
Google Apps Script для настройки автоматизации (аналог VBA в Excel);
-
импорт данных: =IMPORTXML(), =GOOGLEFINANCE();
-
встроенный перевод содержимого ячеек при помощи =TRANSLATE;
-
импортирование данных из одной таблицы или книги в другую (при этом если данные в исходном файле изменятся, они автоматически обновятся в других документах).
В целом Google Sheets повторяет функционал Excel, но я использую его, только если нужно создать документ с совместным доступом в режиме онлайн.
Из минусов: в некоторых случаях при копировании данных из Excel сбивается формат данных в ячейках — Google Sheets воспринимает их иначе. Чтобы избавиться от этого, придется найти все такие ячейки и поменять формат. Если этого не сделать, то формулы просто не будут работать.
Основные ошибки в использовании формул
Их четыре.
|
Ошибка |
Пример |
|
Некорректное написание формулы |
В формуле =СУММ(A1:A5) + СУММ(B1:B5 не хватает закрывающей скобки, из-за этого Excel выдаст ошибку |
|
Неправильные ссылки на ячейки, участвующие в формуле |
Неверные результаты можно получить, если написать формулу без учета разницы между $A$1 (абсолютная) и A1 (относительная). Появится ошибка #REF! |
|
Неправильный формат данных |
Числа, записанные в виде текста, не участвуют в расчетах (формула =A1+A2), если хотя бы в одной ячейке число написано текстом. Выдаст ошибку или 0 |
|
Ошибка в логике вычислений |
Неправильный порядок операций (например, =A1+B1*C1 без скобок) даст другой результат, чем =(A1+B1)*C1) |
Возможно, вам будет интересно:
Последние комментарии