Как использовать СУММЕСЛИ, СУММЕСЛИМН в Google Таблицах

Функции СУММЕСЛИ и СУММЕСЛИМН в Google Таблицах помогают анализировать, упорядочивать и суммировать данные на основе определенных критериев.

Эти формулы суммируют значения ячеек, когда они соответствуют установленным вами требованиям.

Недавно я сделал это для онлайн-бизнеса, который хотел углубиться в свои отчеты о трафике.

Я экспортировал ежедневный трафик сайта компании по каналам из ее аналитического программного обеспечения и импортировал эти данные в таблицу Google, которую я сохранил как «Ежедневный трафик».

Затем я агрегировал данные: по одной строке на канал в день. Компания получает трафик примерно из девяти источников. Таким образом, с 1 января по 28 марта (87 дней) в электронной таблице было 748 строк общего трафика канала. Это было слишком много информации, чтобы отсортировать ее вручную.

В результате импорта данных было получено 748 строк общего трафика канала.

Используя примеры данных, давайте посмотрим, как формулы СУММЕСЛИ и СУММЕСЛИМН могут помочь в анализе трафика сайта, а именно:

• Сколько трафика генерировал каждый канал за весь 87-дневный период?
• Сколько ежемесячно генерировал каждый канал?

СУММЕСЛИ

Импортированная информация о трафике состоит из трех столбцов: дата, канал и количество уникальных посещений для каждого канала в этот день.

Исходные данные имеют три столбца на листе: Дата, Канал и Трафик сайта.

Моя первая задача — выяснить, сколько трафика сгенерировал каждый канал за все 87 дней.

Для начала я создал лист под названием «Общий трафик по каналам» с двумя столбцами: «Канал» и «Общий трафик».

Новый лист Total Traffic by Channel содержит итоговые данные по каждому каналу за весь период.

Я использовал формулу СУММЕСЛИ, чтобы получить общий трафик с каждого канала за весь диапазон дат. Формула принимает два или три параметра.

Версия с двумя параметрами включает «диапазон» и «критерий».

 =СУММЕСЛИ(диапазон, критерий)

В этом случае диапазон — это диапазон, в котором формула будет искать критерий и ячейки для суммирования.

Для этого примера нам нужна версия с тремя параметрами, которая добавляет «диапазон суммы». Этот диапазон — это то, где Google Таблицы будут искать, когда он соответствует критерию. Диапазон суммы представляет ячейки, которые необходимо суммировать.

 =СУММЕСЛИ(диапазон, критерий, диапазон суммы)

Теперь я буду использовать формулу для получения трафика из партнерского канала.

Когда я начинаю вводить «=СУММЕСЛИ» в строку формул, Google Таблицы предоставляют возможность выбрать формулу СУММЕСЛИ.

Google Sheets предлагает SUMIF еще до того, как набор текста завершен.

Поскольку я выбрал предложенную формулу СУММЕСЛИ, Google Таблицы показали мне руководство. Я могу перемещаться по рабочей книге и выбирать столбцы из таблицы Daily Traffic по мере необходимости.

Выбор предложенной функции СУММЕСЛИ упрощает процесс создания формулы.

Я перехожу к листу «Ежедневный трафик» и выбираю столбец B.

Далее мне нужно определить мой критерий, которым является слово «Партнер». Поскольку это слово уже есть на моем новом листе «Общий трафик по каналам», я могу использовать ссылку на ячейку A3.

Щелчок по ячейке A3 добавляет ее значение «Партнер» в формулу СУММЕСЛИ в качестве критерия.

Наконец, я возвращаюсь к листу Daily Traffic и выбираю столбец C, диапазон суммы. Если столбец B соответствует моему критерию «Партнер», значение в столбце C той же строки будет добавлено к сумме.

Если столбец B соответствует моему критерию «Партнер», значение в столбце C той же строки будет добавлено к сумме.

Вот и готовая формула.

 =СУММЕСЛИ('Ежедневный трафик'!B:B,A3,'Ежедневный трафик'!C:C)

Таким образом, мы знаем, что партнерский канал привлек 53 875 посещений сайта с 1 января по 28 марта.

Формула СУММЕСЛИ сработала. С 1 января по 28 марта на партнерском канале было 53 875 посещений.

Я могу взяться за нижний угол этой ячейки и перетащить формулу вниз, чтобы получить итоговые значения для каждого из оставшихся каналов.

Перетащите формулу ячейки вниз по столбцу, чтобы заполнить итоговые данные по трафику для всех остальных каналов.

«Платный» (то есть рекламный) канал генерировал львиную долю трафика (3 038 521 посещение), что характерно для сайтов электронной коммерции.

СУММЕСЛИМН

Функция СУММЕСЛИМН аналогична СУММЕСЛИ, за исключением того, что она допускает использование нескольких критериев. Эта функция помогает ответить на второй вопрос: «Сколько трафика ежемесячно генерирует каждый канал?»

Параметры для СУММЕСЛИМН находятся в несколько ином порядке.

 =СУММЕСЛИМН(диапазон суммы, диапазон критерия, критерий)

Можно иметь почти неограниченные критерии, добавляя пары диапазона критерия и критерия.

 =СУММЕСЛИМН(суммарный диапазон, диапазон критериев 1, критерий 1, диапазон критериев 2, критерий 2)

Я создал еще один лист «Ежемесячный трафик по каналам» со строками каналов и столбцами месяцев.

На листе «Ежемесячный трафик по каналам» функция СУММЕСЛИМН получает месячные итоги трафика.

Я работал с функцией СУММЕСЛИМН так же, как и с функцией СУММЕСЛИ. Поскольку формула СУММЕСЛИ может включать много критериев, она может быть длинной. В конце концов, у меня есть готовая формула для партнерского канала в январе 2022 года.

 =СУММЕСЛИМН('Ежедневный трафик'!C:C,'Ежедневный трафик'!B:B,A2,'Ежедневный трафик'!A:A,">=2022-01-01",'Ежедневный трафик'!A:A ,"<=2022-01-31") 

Формула СУММЕСЛИ может быть длинной из-за многих критериев.

Давайте раскроем эту формулу.

Первый параметр — это диапазон суммы, столбец C в таблице Daily Traffic. Он содержит фактический объем трафика.

 =СУММЕСЛИМН('Ежедневный трафик'!C:C,

Следующие два параметра, разделенные запятыми, — это диапазон первого критерия и первый критерий.

 'Ежедневный трафик'!B:B,A2

Столбец B в таблице Daily Traffic ( «Ежедневный трафик»!B:B в формуле) — это список каналов. Ячейка A2 содержит название канала «Партнер». Я мог бы ввести «партнер», но использование ссылки на ячейку позволяет перетащить формулу вниз и заполнить другие каналы.

Следующие две пары диапазона критерия и критерия создают диапазон дат. Столбец Daily Traffic A содержит даты. Критерий «>=2022-01-01» указывает, что дата больше или равна 1 января 2022 года.

 'Ежедневный трафик'!A:A,">=2022-01-01"

Я включил дату в виде текста «>=2022-01-01», потому что знал, что Google Таблицы распознают этот формат. Другой способ написания дат: «>=»&date(2022,1,1).

Оба будут работать.

Я могу скопировать и вставить формулу для каждого диапазона дат, но в остальном задача выполнена. Функция SUMIF делает процесс относительно простым. Теперь я знаю общий ежемесячный трафик, генерируемый каждым каналом.

Функция СУММЕСЛИ была проста для получения месячных итогов по каналам.