В этом руководстве показано, как использовать Отлэль СУММЕСЛИ и СУММЕСЛИМН Funcтионы в Excel и Google Таблицах для суммирования данных, соответствующих определенным критериям.
Обзор функции СУММЕСЛИ
Вы можете использовать функцию СУММЕСЛИ в Excel для суммирования ячеек, содержащих определенное значение, суммирования ячеек, которые больше или равны значению, и т. Д.
(Обратите внимание, как появляются входные данные формулы)
Синтаксис и аргументы функции СУММЕСЛИ:
| 1 | = СУММЕСЛИ (диапазон; критерий; [диапазон_суммы]) |
диапазон - Диапазон ячеек, к которым вы хотите применить критерии.
критерии - Критерии, используемые для определения, какие ячейки нужно добавить.
диапазон_суммы - [необязательно] Ячейки для сложения. Если сумма_диапазона опущена, вместо этого ячейки в диапазоне суммируются.
Что такое функция СУММЕСЛИ?
Функция СУММЕСЛИ - одна из старых функций, используемых в электронных таблицах. Он используется для сканирования диапазона ячеек, проверяя определенный критерий, а затем складывает значения в диапазоне, который соответствует этим значениям. Исходная функция СУММЕСЛИ ограничивалась одним критерием. После 2007 года была создана функция СУММЕСЛИМН, которая позволяет использовать множество критериев. В основном они используются одинаково, но есть некоторые важные различия в синтаксисе, которые мы обсудим в этой статье.
Если вы еще этого не сделали, вы можете просмотреть большую часть аналогичной структуры и примеры в статье СЧЁТЕСЛИМН.
Базовый пример
Давайте рассмотрим этот список зарегистрированных продаж, и мы хотим знать общий доход.
Поскольку у нас были расходы, отрицательное значение, мы не можем просто вычислить базовую сумму. Вместо этого мы хотим суммировать только те значения, которые больше 0. «Больше 0» - это то, что будет нашим критерием в функции СУММЕСЛИ. Наша формула для утверждения, что это
| 1 | = СУММЕСЛИ (A2: A7; «> 0») |
Пример с двумя столбцами
Хотя исходная функция СУММЕСЛИ была разработана, чтобы вы могли применить критерий к диапазону чисел, которые вы хотите суммировать, большую часть времени вам потребуется применить один или несколько критериев к другим столбцам. Рассмотрим эту таблицу:
Теперь, если мы воспользуемся исходной функцией СУММЕСЛИ, чтобы узнать, сколько у нас бананов (перечислено в ячейке D1), нам нужно будет указать диапазон, который мы хотим сумма в качестве последнего аргумента, и поэтому наша формула будет
| 1 | = СУММЕСЛИ (A2: A7; D1; B2: B7) |
Однако, когда программисты в конце концов осознали, что пользователи хотят давать более одного критерия, была создана функция СУММЕСЛИМН. Чтобы создать одну структуру, которая будет работать для любого количества критериев, СУММЕСЛИМН требует, чтобы диапазон суммы был указан первым. В нашем примере это означает, что формула должна быть
| 1 | = СУММЕСЛИМН (B2: B7; A2: A7; D1) |
ПРИМЕЧАНИЕ. Эти две формулы дают одинаковый результат и могут выглядеть примерно одинаково, поэтому обратите особое внимание на то, какая функция используется, чтобы убедиться, что вы перечислили все аргументы в правильном порядке.
Работа с датами, несколько критериев
При работе с датами в электронной таблице, хотя можно ввести дату непосредственно в формулу, лучше всего иметь дату в ячейке, чтобы вы могли просто ссылаться на ячейку в формуле. Например, это помогает компьютеру понять, что вы хотите использовать дату 27.05.2020, а не число 5, разделенное на 27, разделенное на 2022 год.
Давайте посмотрим на нашу следующую таблицу, в которой регистрируется количество посетителей сайта каждые две недели.
Мы можем указать начальную и конечную точки диапазона, который мы хотим посмотреть в D2 и E2. Наша формула для суммирования количества посетителей в этом диапазоне может быть следующей:
| 1 | = СУММЕСЛИМН (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2) |
Обратите внимание, как мы смогли объединить сравнения «=» со ссылками на ячейки для создания критериев. Кроме того, хотя оба критерия применялись к одному и тому же диапазону ячеек (A2: A7), вам необходимо записать диапазон дважды, по одному разу для каждого критерия.
Несколько столбцов
При использовании нескольких критериев вы можете применить их к тому же диапазону, что и в предыдущем примере, или вы можете применить их к разным диапазонам. Давайте объединим наши образцы данных в эту таблицу:
Мы настроили несколько ячеек, чтобы пользователь мог вводить то, что он хочет искать, в ячейках с E2 по G2. Таким образом, нам нужна формула, которая суммирует общее количество яблок, собранных в феврале. Наша формула выглядит так:
| 1 | = СУММЕСЛИМН (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2) |
СУММЕСЛИМН с логикой типа ИЛИ
До этого момента все примеры, которые мы использовали, основывались на сравнении на основе И, когда мы ищем строки, соответствующие всем нашим критериям. Теперь рассмотрим случай, когда вы хотите найти возможность строки, соответствующей тому или иному критерию.
Давайте посмотрим на этот список продаж:
Мы хотели бы сложить общие продажи Адама и Боба. Для этого у вас есть несколько вариантов. Самый простой - сложить две СУММЕСЛИМН вместе, например:
| 1 | = СУММЕСЛИМН (B2: B7; A2: A7; «Адам») + СУММЕСЛИМН (B2: B7; A2: A7; «Боб») |
Здесь компьютер подсчитывает наши индивидуальные баллы, а затем мы складываем их.
Наш следующий вариант подходит, когда у вас больше диапазонов критериев, так что вам не нужно повторно переписывать всю формулу. В предыдущей формуле мы вручную приказали компьютеру сложить две разные СУММЕСЛИМН. Однако вы также можете сделать это, записав свои критерии внутри массива, например:
| 1 | = СУММ (СУММЕСЛИМН (B2: B7; A2: A7; {"Адам", "Боб"})) |
Посмотрите, как построен массив в фигурных скобках. Когда компьютер вычисляет эту формулу, он будет знать, что мы хотим вычислить функцию СУММЕСЛИМН для каждого элемента в нашем массиве, создав таким образом массив чисел. Затем внешняя функция SUM возьмет этот массив чисел и превратит его в одно число. Пошаговая оценка формулы будет выглядеть так:
| 123 | = СУММ (СУММЕСЛИМН (B2: B7; A2: A7; {"Адам", "Боб"}))= СУММ (27401, 43470)= 70871 |
Получаем тот же результат, но формулу удалось записать более лаконично.
Работа с пробелами
Иногда в вашем наборе данных есть пустые ячейки, которые вам нужно либо найти, либо избежать. Настройка критериев для них может быть немного сложной, поэтому давайте рассмотрим другой пример.
Обратите внимание, что ячейка A3 действительно пуста, а в ячейке A5 есть формула, возвращающая строку нулевой длины «». Если мы хотим найти общую сумму действительно пустые ячейки, мы использовали бы критерий «=», и наша формула выглядела бы так:
| 1 | = СУММЕСЛИМН (B2: B7; A2: A7; "=") |
С другой стороны, если мы хотим получить сумму для всех ячеек, которые визуально выглядят пустыми, мы изменим критерии на «», и формула будет выглядеть так:
| 1 | = СУММЕСЛИМН (B2: B7; A2: A7; "") |
Давайте перевернем: что, если вы хотите найти сумму непустых ячеек? К сожалению, текущий дизайн не позволяет избежать строки нулевой длины. Вы можете использовать критерий «», но, как вы можете видеть в примере, он по-прежнему включает значение из строки 5.
| 1 | = СУММЕСЛИМН (B2: B7; A2: A7; "") |
Если вам нужно не подсчитывать ячейки, содержащие строки нулевой длины, вы можете рассмотреть возможность использования функции LEN внутри СУММПРОИЗВ.
СУММЕСЛИ в Google Таблицах
Функция СУММЕСЛИ работает в Google Таблицах точно так же, как и в Excel:
