10 формул в Яндекс Таблицах: как с их помощью считать выручку, KPI сотрудников и решать другие задачи

Не всегда специалистам нужны платные программы для аналитики или финансового учёта. Чтобы вести бухгалтерию или отслеживать показатели эффективности, достаточно изучить основные формулы в Яндекс Таблицах. Рассказываем на примерах, как этот инструмент упростит работу сотрудникам компаний из разных сфер.

СРЗНАЧ — находит среднее арифметическое данных в столбце или строке

Поможет спланировать затраты на следующий период

Пример. Сметчик в строительной компании готовит документы для подачи на конкурс. В одном из пунктов нужно указать примерную стоимость отделочных работ в помещении площадью 300 квадратных метров. Заранее назвать точную сумму невозможно, зато можно посмотреть, сколько в среднем стоила отделка объектов такой же площади. Для этого нужно занести данные в таблицу и использовать функцию СРЗНАЧ. Она посчитает среднее арифметическое значение данных в столбце или строке.

Как использовать. Выберите ячейку, в которой нужно показать итоговый результат, нажмите знак «=» на клавиатуре. Напечатайте СРЗНАЧ, а затем мышкой выделите столбец или строку с данными, на основе которых надо посчитать среднее. Нажмите Enter, если у вас компьютер на Windows, или Return, если на MacOS.

В строке с функцией будет =СРЗНАЧ(B2:B8), где:

• =СРЗНАЧ — название формулы
• B2 — первое значение диапазона, которое учитывает формула
• B8 — заключительное значение диапазона, которое учитывает формула

Вы можете вручную менять эти данные — например, начинать расчёт не с ячейки B2, а с B3.

МИН и МАКС — находят самое маленькое и самое большое значения в диапазоне

Покажут минимальную и максимальную выручку за месяц

Пример. Владелец салона красоты хочет узнать минимальную и максимальную выручку за день, чтобы использовать эти данные для дальнейшего анализа. Например, так можно выяснить, почему всё отлично сработало в самый успешный день месяца и как масштабировать этот опыт.

Чтобы найти минимальную и максимальную сумму, не надо просматривать каждую строчку таблицы. Можно воспользоваться функциями МИН и МАКС.

Как использовать. Поставьте знак «=» в ячейке, куда надо вывести итоговый результат. Затем напечатайте МИН и мышкой выберите нужный столбец или строку. Если массив слишком большой, введите через точку с запятой координаты первой ячейки и последней.

В строке с функцией появится синтаксис =МИН(B2:B15), где:

• =МИН — название функции
• B2 — первая ячейка массива
• B15 — последняя ячейка массива

Функция МАКС работает по аналогичной схеме.

ЕСЛИ — оставит в таблице только нужные данные

Поможет найти лучшие рекламные кампании

Пример. Таргетолог хочет собрать все объявления с CRT выше 5%, чтобы в преддверии большой распродажи понять, какие креативы больше всего нравятся аудитории. Эту задачу можно решить разными способами. Один из них — использовать функцию ЕСЛИ. Потом с помощью фильтра можно оставить только нужные данные.

Как использовать. Эта формула работает с каждой ячейкой в отдельности. В нашем примере она будет сравнивать данные из столбца CTR с 5. Если число больше или равно 5, то рядом появится 1, если меньше — 0. На языке логики это означает, что первое выражение — истина, второе — ложь, поэтому столбец для нулей и единиц мы назвали «Истина или ложь». Вы можете назвать его по-другому или оставить безымянным.

Чтобы запустить формулу, выберите ячейку справа от той, что идёт первой в массиве. Затем нажмите «=» и введите ЕСЛИ. Дальше напечатайте условие и через точку с запятой допишите 1 и 0. Нажмите Enter или Return. Теперь нужно распространить формулу на весь столбец. Для этого выделите ячейку с формулой, наведите курсор на её нижний правый угол — появится тонкий крестик. Зажмите его и растяните мышкой на весь массив.

Синтаксис функции =ЕСЛИ(B2>5;1;0), где:

• =ЕСЛИ — название формулы.
• B2 — координаты ячейки, данные которой мы сравниваем с условием. В примере это 5.
• >5 — условие, которое должны выполнить данные из указанной ячейки.
• 1 — значение, если число из ячейки отвечает условию, то есть больше 5.
• 0 — значение, если число из ячейки не прошло проверку, то есть оказалось меньше 5.

СУММЕСЛИ — складывает данные только в избранных ячейках

Рассчитает выручку, которую принёс конкретный товар

Пример. Владелица ателье хочет узнать, сколько денег приносит пошив юбок. Для этого можно вручную отсортировать нужные позиции, а потом сложить суммы. Это легко, когда в таблице 10 строк. А если их сотни, то подойдёт вариант с функцией СУММЕСЛИ.

Как использовать. Выделите ячейку, в которую хотите вывести данные, нажмите «=». Затем введите СУММЕСЛИ и выберите диапазон, из которого нужно брать данные. В нашем примере это столбец «Позиция». Дальше надо указать условие, по которому функция отберёт подходящие данные. Для этого внутри кавычек пишут фразу или число, которое будет искать программа. Осталось после точки с запятой вписать массив, из которого программа возьмёт сумму, и нажать Enter или Return.

В строке с функцией получится =СУММЕСЛИ(A2:A11;"=Юбка*";B2:B11), где:

• =СУММЕСЛИ — название функции.
• A2:A11 — массив, из которого программа возьмёт данные, чтобы сравнить их с условием.
• "=Юбка*" — условие, которому должны соответствовать данные. Так как в столбце «Позиция» нет продукта под названием «Юбка», зато есть «Юбка мини хлопок», «Юбка миди» и т. п. Чтобы алгоритм посчитал их как одинаковые товары, мы с помощью знака «*» обозначили общую часть всех наименований — "=Юбка*". Звёздочка заменяет все знаки после неё, то есть позиции со словом «Юбка» выглядят для алгоритмов одинаково: «Юбка*».
• B2:B11 — массив, из которого формула возьмёт сумму, если данные в соответствующей строке из столбца «Позиция» соответствуют условию.

СУММЕСЛИ — очень полезная функция для бизнеса. Читайте, как ещё можно её использовать.

СУММЕСЛИМН — находит сумму данных в ячейках, которые отвечают двум и более условиям

Посчитает, сколько выручки принесли товары, которые изготовил конкретный сотрудник

Пример. Продолжим пример с юбками и ателье. Представим, что теперь владелица хочет посчитать, сколько выручки принесли юбки, которые сшила сотрудница Иванова. Для этого данные должны одновременно отвечать двум условиям:

• Это юбка
• Её сшила Иванова

Перевести это на язык алгоритмов поможет функция СУММЕСЛИМН.

Как использовать. Выберите ячейку, в которую надо вывести результат, нажмите «=» и напечатайте СУММЕСЛИМН. Укажите диапазон, из которого надо брать данные для суммирования. После этого через точку с запятой укажите условия: сначала выберите диапазон, потом через запятую укажите условие. Пары «диапазон – условие» отделяют друг от друга точкой с запятой.

В строке с функцией получится =СУММЕСЛИМН(C2:C11;A2:A11;"Юбка*";B2:B11;"Иванова"), где:

• =СУММЕСЛИМН — название функции.
• C2:C11 — диапазон, откуда нужно взять стоимость юбок.
• A2:A1 — диапазон, для которого мы задаём первое условие. По нему формула будет учитывать только юбки.
• "Юбка*" — фраза, с помощью которой мы переводим условие на язык алгоритма. По ней он будет учитывать только те ячейки, в которых встречается слово «Юбка».
• B2:B11 — диапазон с данными для второго условия, то есть с фамилиями швей.
• "Иванова" — значение, которое покажет, что условие выполнено.

СУММПРОИЗВ — перемножает данные из двух столбцов, а потом складывает их

Посчитает, на какую сумму менеджеры продали товар в этом месяце

Пример. Весь месяц менеджеры продавали пылесосы конкретной марки. Теперь руководитель хочет узнать, сколько всего выручки принесли все менеджеры. В этом ему поможет формула СУММПРОИЗВ.

Как использовать. Выберите ячейку, где будет показываться результат, нажмите «=» и введите СУММПРОИЗВ. Дальше через точку с запятой укажите массивы, данные из которых нужно попарно перемножить, а затем сложить. Нажмите Enter или Return.

В строке с функцией будет =СУММПРОИЗВ(B2:B14;C2:C14), где:

• =СУММПРОИЗВ — название формулы.
• B2:B14 — столбец, из которого алгоритмы возьмут первое число, то есть количество пылесосов, проданных менеджером.
• C2:C14 — столбец со вторым множителем или с ценой пылесоса.

СЧЁТ — показывает количество ячеек, в которых есть числовые данные

Посчитает, сколько обращений решила служба гарантии в этом месяце

Пример. Сотрудники отдела гарантии заносят все обращения клиентов в таблицу. Когда обращение закрывают, в ячейке рядом с номером обращения ставят плюсик. Если с помощью горячих клавиш заменить плюсы на единицы, можно посчитать количество закрытых обращений через функцию СЧËТ.

Как использовать. Выберите ячейку, куда нужно вывести результат, нажмите «=» и введите СЧËТ. Затем выберите диапазон, в котором нужно посчитать заполненные ячейки, нажмите Enter или Return.

В строке с функцией появится формула =СЧЁТ(A2:A13), где:

• =СЧЁТ — название функции
• A2:A13 — столбец, в котором формула считает ячейки с данными, то есть закрытые заявки

Обратите внимание: эта формула учитывает только числовые значения в ячейках, поэтому если вместо «1» написать «да», то формула не посчитает обращение в этой строке.

СЧËТЕСЛИ — учитывает данные только в тех ячейках, которые отвечают конкретному условию

Поможет узнать, сколько сотрудников выполнили план в этом месяце

Пример. Начальник отдела продаж получил отчёт от сотрудников и хочет посмотреть, сколько менеджеров в итоге выполнили KPI. Так как у каждого сотрудника индивидуальный план, нужно будет сравнивать фактический показатель с эталоном. Это можно посмотреть за полминуты с помощью функции СЧËТЕСЛИ.

Как использовать. Выберите ячейку, поставьте там знак «=» и напечатайте СЧËТЕСЛИ. Затем укажите массив, в котором компьютер будет искать данные для сравнения с условием. Добавьте условие и нажмите Enter или Return.

В нашем примере получится формула =СЧЁТЕСЛИ(C2:C14;">"&B2:B14), где:

• =СЧЁТЕСЛИ — название формулы.
• C2:C14 — массив с данными, которые формула будет сравнивать с заданным условием. В нашем случае это сумма, которую менеджер заработал.
• ">" — условие, которому должны соответствовать данные из предыдущего пункта, то есть фактическая сумма должна быть больше плана или второго параметра.
• &B2:B14 — данные, с которыми алгоритм сравнивает содержимое первого массива. В примере это запланированная сумма.

ВПР — быстро находит данные в большой таблице

Покажет, сколько выручки принесла продажа корма для собак в прошлом сентябре

Пример. Зоомагазин ведёт статистику по продажам всех товаров в течение многих месяцев. В какой-то момент он столкнулся с падением спроса на корм для собак. Чтобы разобраться, с чем это связано, отдел продаж решил посмотреть, была ли похожая ситуация год назад. Это удобно делать с помощью функции ВПР.

Как использовать. Выберите ячейку для результата, нажмите в ней «=» и наберите ВПР. Затем укажите значение ячейки, для которого будете искать данные. После этого обозначьте таблицу для поиска и номер столбца. В конце поставьте 0, если не сортировали данные в исходной таблице, и 1 — если сортировали.

В итоге в строке функции будет =ВПР("Корм для собак";A1:E9;4;0), где:

• =ВПР — название функции
• "Корм для собак" — название ячейки, для которой мы ищем данные
• A1:E9 — таблица, в которой будет проходить поиск
• 4 — номер столбца, где нужно искать нужные данные
• 0 — означает, что мы не сортировали данные в исходной таблице

СЦЕПИТЬ — объединяет данные в разных ячейках

Поможет сгенерировать ссылку с UTM-метками

Пример. Маркетологи размечают ссылки, чтобы отслеживать источники переходов. Для этого используют UTM-метки. Иногда между адресом страницы и её «хвостом» попадает пробел. Из-за него метки перестают работать. Чтобы не терять данных, лучше присоединять переменные с помощью функции СЦЕПИТЬ.

Как использовать. Выберите ячейку, где будет результат, нажмите «=» и напечатайте СЦЕПИТЬ. Затем кликните на ячейки, информацию из которых хотите «склеить», и нажмите клавишу Enter или Return.

Популярные формулы в Яндекс Таблицах удобно использовать для быстрого решения рабочих задач. Подробнее о других функциях читайте в Справке внутри документа: Файл → Справка → Вставка функций.