Как использовать функцию СУММЕСЛИ в Google Таблицах? Примеры

Содержание:

Подвести итог по диапазону чисел может быть довольно легко даже для новичка в Google Таблицах. Однако когда дело доходит до суммирования чисел на основе какого-либо условия, именно тогда все начинает усложняться.

Ну не совсем.

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

Как только вы поймете основной синтаксис и посмотрите несколько примеров, вы захотите использовать функцию СУММЕСЛИ при каждой возможности.

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

Что делает функция СУММЕСЛИ?

Функция СУММЕСЛИ — это просто объединение функций СУММ и ЕСЛИ. Его применение простое.

Функция просто просматривает диапазон ячеек, чтобы найти те, которые соответствуют заданному условию.

Когда совпадение найдено, число, соответствующее ячейке, включается в группу выбранных чисел.

Как только функция завершит сканирование всех ячеек в диапазоне, она берет группу выбранных чисел и суммирует их!

Синтаксис функции СУММЕСЛИ в Google Таблицах

Синтаксис функции СУММЕСЛИ:

= СУММЕСЛИ (диапазон; условие; [диапазон_суммы])

Здесь,

диапазон — это группа ячеек, которые проверяются на наличие условия.
Условие — это критерий, которому должна соответствовать ячейка в диапазоне, чтобы считаться совпадением. Условие может быть значением (числом, текстом, датой) или ссылкой на ячейку, содержащую критерии.
sum_range (диапазон суммы) — необязательный параметр. Если включен, это диапазон ячеек, содержащий значения, которые будут добавлены, если соответствующий номер в диапазоне соответствует условию. Если параметр sum_range не включен, предполагается, что диапазон в первом параметре также является sum_range.

Как видно из синтаксиса выше, есть два способа использования функции СУММЕСЛИ.

без отдельного диапазона сумм
с отдельным диапазоном сумм.

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

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

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

Наконец, он возвращает сумму всех ячеек в диапазоне, соответствующих критериям.

Примеры использования функции СУММЕСЛИ (примеры)

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

Например, вы можете использовать его для:

Найдите сумму только положительных или отрицательных чисел в диапазоне
Найдите общий объем продаж для отдела
Найдите общие расходы до заданной даты

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

Однако в этой статье давайте просто рассмотрим эти три варианта использования. При объяснении подстановочных знаков мы включим еще один особый вариант использования.

Использование СУММЕСЛИ с числовым условием

Первый вариант использования — найти сумму только положительных или отрицательных чисел в диапазоне.

Давайте посмотрим на набор данных, который состоит из комбинации положительных и отрицательных целых чисел, как показано ниже.

Мы хотим сложить только положительные числа в диапазоне A2: A10. Вот как можно применить функцию СУММЕСЛИ в этом случае:

Выберите ячейку, в которой вы хотите отобразить результат суммы (в нашем случае C2).
Введите в ячейку следующую формулу: = СУММЕСЛИ (A2: A10, ”> = 0”)Обратите внимание, что в этом случае мы не включили третий параметр.
Нажмите клавишу возврата

Это должно отобразить сумму положительных чисел в ячейке C2.

Объяснение формулы

В этом примере функция СУММЕСЛИ проверила каждую ячейку от A2 до A10 и выбрала только те ячейки, которые имеют значения больше или равные 0.

Затем он сложил все выбранные значения и отобразил результат в ячейке C2.

Примечание. Если вместо этого вы хотите отобразить сумму всех отрицательных чисел, все, что вам нужно сделать, это изменить условие на «<0».

Использование СУММЕСЛИ с условием текста

Давайте посмотрим на другой пример.

Ниже у нас есть набор данных, в котором мы хотим подвести итоги продаж отдела упаковочных материалов.

Ниже показано, как это можно сделать с помощью формулы СУММЕСЛИ:

Выберите ячейку, в которой вы хотите отобразить результат суммы (в нашем случае D2).
Введите в ячейку следующую формулу: = СУММЕСЛИ (A2: A10, «Упаковка», B2: B10)
Нажмите клавишу возврата

В ячейке D2 должен отображаться общий объем продаж отдела упаковки.

Объяснение формулы

В этом примере функция СУММЕСЛИ проверяет каждую ячейку от A2 до A10 и ищет только те ячейки, которые содержат значение «Packaging».

Для каждой ячейки, содержащей слово «Упаковка», функция СУММЕСЛИ выбирала соответствующее значение продаж в столбце B. Затем она складывала все выбранные значения и отображала результат в ячейке D2.

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

Использование СУММЕСЛИ с условием даты

Теперь давайте посмотрим на пример, в котором мы хотим найти общие расходы до заданной даты, скажем, 1 сентября 2019 года.

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

Выберите ячейку, в которой вы хотите отобразить результат общих расходов (в нашем случае D2).
Введите в ячейку следующую формулу: = СУММЕСЛИ (A2: A10, ”<” & ДАТА (2019,9,1), B2: B10).
Нажмите клавишу возврата

Это должно отображать общие расходы до ДАТЫ (2019,9,2), что означает просто 1 сентября 2019 года. Мы использовали только функцию ДАТА для представления даты.

Примечание. Мы должны присоединить оператор («<») к дате с помощью амперсанда (&).

Объяснение формулы

В этом примере функция СУММЕСЛИ проверяет каждую ячейку от A2 до A10 и ищет только те ячейки, которые содержат даты до 1 сентября 2019 года.

Для каждой совпадающей ячейки функция СУММЕСЛИ выбирала соответствующее значение расходов из столбца B.

Затем он суммировал все выбранные значения расходов и отобразил результат в ячейке D2.

Существует ряд других способов использования функции СУММЕСЛИ.

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

Использование СУММЕСЛИ с подстановочными знаками

Один из творческих способов использования функции СУММЕСЛИ — это включение подстановочных знаков в условную часть функции.

Например, предположим, что у вас есть следующий набор данных, содержащий количество различных мобильных телефонов на складе.

Если вы хотите узнать общее количество всех моделей Samsung, вы можете использовать подстановочные знаки, такие как ‘*’ или ‘?’ в вашей функции СУММЕСЛИ следующим образом:

Выберите ячейку, в которой вы хотите отобразить результат общих продаж (в нашем случае D2).
Введите в ячейку следующую формулу: = СУММЕСЛИ (A2: A10; «Samsung *», B2: B10)
Нажмите клавишу возврата

Это должно отобразить количество телефонов Samsung в ячейке D2.

Примечание. Подстановочный знак звездочки (*) обычно сочетается с основанием слова или буквы, чтобы найти различные варианты термина.

Объяснение формулы

В этом примере условие «Samsung *» означает «найти все ячейки, содержащие слово Samsung».

Это не обязательно должно быть точное совпадение, но ячейка должна содержать слово «Samsung» вместе с любым другим символом (ами).

Как только совпадение найдено, функция СУММЕСЛИ берет значение количества, соответствующее соответствующей ячейке, и добавляет его в список выбранных значений количества.

После прохождения всех моделей функция СУММЕСЛИ суммирует выбранные значения количества и отображает результат в ячейке C2.

Вы можете использовать «?» подстановочный знак таким же образом. ‘?’ подстановочный знак используется для представления одного символа в любом месте текстовой строки.

Итак, если вы хотите найти, скажем, все модели Apple iPhone X, вы можете использовать «Apple iPhone X?» в состоянии.

Что следует помнить

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

При использовании отдельного диапазона сумм не забудьте убедиться, что и диапазон, и диапазон сумм имеют равное количество ячеек.
Функция СУММЕСЛИ не чувствительна к регистру.
Если условие содержит текстовое значение, дату или подстановочные знаки, оно должно быть заключено в двойные кавычки.
Ссылки на ячейки в условии не заключаются в кавычки.
Если условие состоит из комбинации оператора и ссылки на ячейку или другой функции (например, функции ДАТА), то они должны быть объединены амперсандом (&). Например,
- «<» & ДАТА (2019,9.1)
- «>» И B2
В функции СУММЕСЛИ можно использовать только одно условие. Если вы хотите использовать более одного условия, вам нужно будет вместо этого использовать функцию СУММЕСЛИМН.
Вы не можете заменять ссылки range и sum_range массивами.

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

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