Excel суммеслимн больше или равно

ТРЕНИНГИ

   Быстрый старт
   Расширенный Excel
   Мастер Формул
   Прогнозирование
   Визуализация
   Макросы на VBA

КНИГИ

   Готовые решения
   Мастер Формул
   Скульптор данных

ВИДЕОУРОКИ

ОНЛАЙН-КУРСЫ

ФОРУМ

   Excel
   Работа
   PLEX

© Николай Павлов, Planetaexcel, 2006-2022
info@planetaexcel.ru

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

Техническая поддержка сайта

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

Я покажу, как устроен синтаксис этих функций в Microsoft Excel и для каких задач их можно применить.

Разбор синтаксиса СУММЕСЛИ

Начать стоит с правил ввода составляющих функции СУММЕСЛИ. Из ее частей формируется СУММЕСЛИМН, поэтому детальное описание второй опустим, а вернемся к ней только в завершающем разделе статьи. Стандартная запись функции выглядит как =СУММЕСЛИ(A1:A10;»>1″). 

Значит, что суммироваться будут только те ячейки, которые подпадают под указанный критерий, то есть больше 1. Это удобно, например, когда вам нужно посчитать, сколько у вас всего значений, меньше, больше или равняются конкретному. Если вызвать окно «Аргументы функции», вы увидите более понятный вариант записи и можете использовать его, указав диапазон и критерий.

Задать значение для «Диапазон_суммирования» нужно только в том случае, если вы ищете значения больше или меньше конкретного, но при этом добавлять в сумму необходимо ячейки из другого столбца (пока это звучит непонятно, но далее вы увидите такое представление функции на наглядном примере).

Запись СУММЕСЛИ при неравенстве

Еще раз пройдемся по базовому типу использования СУММЕСЛИ. После объявления функции укажите диапазон для проверки в виде A1:A20;. Обязательно поставьте точку с запятой, ведь это нужно для корректной работы функции.

После этого введите вручную сам критерий (нужно именно указывать число, а не брать его из конкретной ячейки с записью A1). Выберите знак больше, меньше, равно или <>, чтобы указать, что число не равняется конкретному. 

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

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

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

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

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

2. Затем вместо неравенства введите текст для поиска.

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

4. Это то самое значение «Диапазон_суммирования», о котором я говорил при просмотре окна «Аргументы функции».

5. На следующем скриншоте вы видите, что морковок в списке всего две, каждая запись имеет значение 12, а это значит, что в сумму попадают только эти два числа, в результате получается 24.

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

Использование функции СУММЕСЛИМН 

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

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

1. Объявите функцию СУММЕСЛИМН и сначала запишите тот диапазон, который будете считать. В моем случае это количество груш.

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

3. Второй критерий – цена, которая должна превышать 10 за единицу. Соответственно, впишите блок с неравенством A1:A10;»>10″, где A1:A10 – диапазон ячеек, а >10 – критерий.

4. Нажмите клавишу Enter и ознакомьтесь с результатом. На следующем скриншоте вы видите, что груш с ценой больше 10 довольно много, все их количество суммировалось и отображается в отдельной ячейке.

При первой записи у вас могут возникнуть трудности с правильным написанием функции, ведь она содержит много условий. Я оставлю вам ее отдельно, чтобы вы могли скопировать ее и вставить, подставив вместо текущих диапазонов ячеек свои: =СУММЕСЛИМН(B2:B25;A2:A25;»Груши»;C2:C25;»>10″). Не забывайте о том, что первый диапазон – то, что вы считаете, далее идет первый критерий – диапазон с названием столбца, потом второй – диапазон с неравенством.

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

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

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

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

Динамический диапазон суммирования по условию в Excel

Пример 1. В таблице содержатся данные о успеваемости студентов по некоторому предмету в университете. Определить итоговую оценку для студентов с фамилией, начинающейся на букву «А» при условии, что минимальный балл должен быть не менее 5 (успеваемость студентов оценивается по 10-бальной шкале).

Вид таблицы данных:

Для расчета суммарного балла согласно установленным критериям используем следующую формулу:

Описание аргументов:

• C3:C14 – диапазон ячеек с баллами, из которых будут автоматически выбраны значения для расчета суммы, которые соответствуют установленным критериям;
• C3:C14 – первый диапазон ячеек, к которому будет применен первый критерий;
• «>5» – первое условие отбора значений из указанного выше диапазона;
• B3:B14 – второй диапазон ячеек (с фамилиями студентов), к которому будет применен второй критерий;
• «А*» – второе условие отбора значений (все фамилии, которые начинаются с буквы «А»).

Результат расчетов:

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



Выборочное суммирование по условию в Excel

Пример 2. В таблице указаны данные об отгрузках трех товаров в различные страны на протяжении трех месяцев. Определить суммарный доход от отгрузок товара №2 в Казахстан за июнь и август.

Вид таблицы данных:

Для расчета итоговой суммы используем формулу:

Результат расчетов:

Примечание: формула выглядит громоздкой несмотря на то, что для столбца «Месяц» было использовано всего 2 критерия отбора. Формулы с несколькими критериями для одного диапазона данных можно существенно сократить способом, который будет показан в следующем примере.

СУММЕСЛИМН для суммирования значений по нескольким условиям в Excel

Пример 3. Используя таблицу из второго примера определить суммарной прибыли от поставок товара №1 в Китай и Грузию на протяжении всего периода (трех месяцев).

Для нахождения искомого значения используем формулу массива (для ввода жмем CTRL+SHIFT+Enter):

Функция СУММЕСЛИМН возвращает массив значений для критериев «Китай» и «Грузия» соответственно, которые суммируются функцией СУММ.

Примечание: если критерии переданы в качестве константы массива для двух и более пар диапазон_условияN; условиеN, результат выполнения формулы будет некорректным.

Полученное значение:

Особенности использования функции СУММЕСЛИМН в Excel

Функция имеет следующую синтаксическую запись:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

Описание аргументов:

• диапазон_суммирования – обязательный аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
• диапазон_условия1 – обязательный аргумент, принимающий ссылку на ячейки, в которых содержатся данные для проверки на соответствие заданному критерию;
• условие1 – обязательный аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (например, «>=100» или «<>0»), проверяемый текст (например, «Продавец_1», «Товар_1») или числовое значение (Например, 20), определяющие ячейки в диапазоне условия1, для которых будут отобраны и просуммированы данные из диапазона суммирования.

Последующие аргументы являются необязательными для заполнения, а их смысл соответствует аргументам диапазон_условия1; условие1. Всего может быть задано до 127 условий отбора данных для суммирования.

Примечания:

1. Если в качестве аргумента диапазон_суммирования была передана ссылка на диапазон ячеек, содержащие текстовые строки или пустые значения, функция СУММЕСЛИМН будет их игнорировать.
2. В качестве условий суммирования данных могут выступать числовые значения, логические выражения, текстовые строки, формулы, ссылки на ячейки, содержащие условия.
3. Условия проверки в виде текста или выражений с логическими и математическими символами должны быть указаны в кавычках.
4. Максимальная длина условия, переданного в качестве аргумента условие1 (условие2 и т. д.) не должна превышать 255 символов.
5. Для создания неточных текстовых критериев поиска требуемых значений можно использовать подстановочные знаки «?» и «*», замещающие один или несколько символов соответственно.
6. Если в диапазоне ячеек, переданных в качестве аргумента диапазон_суммирования, есть ячейки, содержащие логические ИСТИНА или ЛОЖЬ, они будут автоматически преобразованы к числовым значениям 1 и 0 соответственно и будут учтены в итоговой сумме.
7. Число ячеек в диапазонах суммирования и условиях должно быть одинаковым, иначе функция СУММЕСЛИМН вернет код ошибки #ЗНАЧ!.

Сумма, если больше чем

=СУММЕСЛИ( диапазон ; «> 1000» )

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

В показанном примере ячейка Н4 содержит следующую формулу:

=СУММЕСЛИ(D5:D11; «>1000»)

Эта формула суммирует суммы в столбце D, когда они больше, чем 1000.

Функция СУММЕСЛИ поддерживает логические операторы в Excel (то есть «=», «>», «> =» и т.д.), так что вы можете использовать их, как вам нравится в ваших критериях.

В этом случае, мы хотим, чтобы соответствующее количество было больше, чем 1000, и «критерии варьировались» так же, как «диапазон суммы», поэтому нет необходимости вводить диапазон суммы в качестве последнего аргумента.

Функция СУММЕСЛИ просто суммирует все суммы больше, чем 1000.

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

Если вы хотите включить пороговое число в сумму, используйте больше или равно (> =), например, так:

=СУММЕСЛИ( сумма ; «> = 1000» )

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

=СУММЕСЛИ( диапазон ; «>» & A1 )

Там, где А1 представляет собой ссылку на ячейку, которая содержит пороговое число.

Альтернатива с СУММЕСЛИМН

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

=СУММЕСЛИМН( количество ; сумма ; «> 1000» )

Обратите внимание на то, что диапазон суммы всегда стоит первым в функции СУММЕСЛИМН. Также обратите внимание, что критерии СУММЕСЛИМН должны быть введены в парах (диапазон / критерий), что означает имя диапазона «количество» должен быть введен дважды: один раз в пределах суммы, и один раз как ряд критериев.

Сумма, если меньше чем

=СУММЕСЛИ( диапазон ; «<1000» )

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

В показанном примере ячейка Н4 содержит следующую формулу:

=СУММЕСЛИ(D5:D11; «<1000»)

Эта формула суммирует суммы в колонке D, когда они меньше, чем 1000.

Функция СУММЕСЛИ поддерживает логические операторы в Excel (то есть «=», «>», «> =» и т.д.), так что вы можете использовать их, как вам нравится в ваших критериях.

В этом случае, мы хотим, чтобы соответствовующие суммы были меньше, чем 1000, и «критерии варьировались» так же, как «диапазон суммы», поэтому нет необходимости вводить диапазон суммы в качестве последнего аргумента.

Функция СУММЕСЛИ просто суммирует все суммы менее 1000.

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

Если вы хотите включить пороговое число в сумму, используйте меньше или равно (<=), например, так:

=СУММЕСЛИ( сумма ; «<= 1000» )

Используя ссылку на ячейку

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

=СУММЕСЛИ( диапазон ; «<» & A1 )

Там, где А1 представляет собой ссылку на ячейку, которая содержит пороговое число.

Альтернатива с СУММЕСЛИМН

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

=СУММЕСЛИМН( количество ; сумма ; «<1000» )

Обратите внимание на то, что диапазон суммы всегда стоит первым в функции СУММЕСЛИМН. Также обратите внимание, что критерии СУММЕСЛИМН должны быть введены в парах (диапазон/критерий), что означает имя диапазона «количество» должен быть введен дважды: один раз в пределах суммы, и один раз как ряд критериев.