7 примеров использования формулы суммы чисел в Excel с несколькими условиями
В электронных таблицах Excel можно не только найти сумму чисел, но и сделать это в соответствии с заранее заданными критериями отбора. Мы рассмотрим, как правильно использовать функцию суммы (Sumif) в электронных таблицах Excel. Мы начнем с самых простых случаев и обсудим, как использовать подстановочные знаки, назначать диапазон для суммирования и работать с числами, текстом и датами. Мы сосредоточимся на том, как использовать несколько условий одновременно. И, конечно же, мы применим полученные знания на практике, рассмотрев несколько простых примеров.
Доступно
- Как использовать SUMMARY в Excel - синтаксис
- Примеры использования функции СУММЕСЛИ в Excel
- Сумма, если больше, меньше или равно
- Критерии для текста.
- Замены для частичных совпадений.
- Точная дата или диапазон дат.
- Сумма значений, соответствующих пустым или непустым ячейкам.
- Сумма более чем одного условия.
- Почему моя функция AMMESLY не работает?
Хорошей новостью является то, что функция SUMMESLY одинакова во всех версиях MS Excel, от 2016 до 2003 г. Другая хорошая новость заключается в том, что если вы потратите некоторое время на ее изучение, вам не потребуется много времени, чтобы понять другие функции "ЕСЛИ", такие как SUMMESLIMN, ACCOUNTESLIMN и т.д.
Как пользоваться СУММЕСЛИ в Excel – синтаксис
Его цель - найти сумму значений, удовлетворяющих определенным требованиям.
Синтаксис функции следующий:
=SUMMARY(range, criterion, [range_values])
Диапазон - это область, которую мы исследуем для данного значения.
Критерий - это значение или формула, по которой мы выбираем числа для суммирования.
Значение критерия может быть записано непосредственно в самой формуле. В этом случае обратите внимание, что текст должен быть заключен в двойные инвертированные запятые.
Его также можно представить как ссылку на ячейку таблицы, в которой будет установлено требуемое ограничение. Второй метод явно более эффективен, так как позволяет гибко изменять вычисления без редактирования выражения.
Range_amount - это третий параметр, который является необязательным, но очень полезным. Это позволяет нам смотреть в одной области и суммировать значения из другой в соответствующих строках.
Поэтому, если он указан, то расчет производится на основе его данных. Если нет, то суммируются значения из той же искомой области.
Чтобы лучше понять это описание, давайте рассмотрим несколько простых задач. Надеюсь, они будут понятны не только "продвинутым" пользователям, но и подойдут для "чайников".
Примеры использования функции СУММЕСЛИ в Excel
Сумма если больше чем, меньше, или равно
Начнем с самого простого из них. Предположим, у нас есть данные о продажах шоколада. Давайте рассчитаем различные варианты продаж.
В I3 говорится:
=SUM(D2:D21;I2)
D2:D21 - это координаты, по которым мы ищем значения.
I2 - ссылка на критерий выбора. Другими словами, мы ищем ячейки со значением 144 и суммируем их.
Поскольку третий параметр функции не задан, мы сразу суммируем выбранные числа. Область поиска также будет диапазоном суммирования.
Кроме того, текстовое выражение, состоящее из >, , = и числа, может быть указано в качестве задания на выбор нужных значений.
Вы можете указать его непосредственно в формуле, как в I13
=СУММЕСЛИ(D2:D21;"
То есть, мы суммируем все заказы, в которых количество меньше 144.
Но согласитесь, это не очень удобно, потому что приходится корректировать саму формулу, а также не забывать ставить условие в инвертированных запятых.
В будущем мы постараемся использовать только критериальный эталон, так как это значительно упрощает возможные исправления.
Критерии для текста.
Чаще всего необходимо провести поиск в одном месте и обобщить данные, соответствующие найденным в другом.
Чаще всего это необходимо, если вы хотите использовать выделения для определенных слов. В конце концов, текстовые значения нельзя суммировать, а соответствующие им числа - можно.
В качестве простого метода использования формулы суммы в электронных таблицах Excel, давайте рассчитаем сумму выполненных заказов.
В I3 запишите выражение:
=СУММЕСЛИ(F2:F21;I2;E2:Е21)
F2:F21 - это область, где мы выбираем соответствующие значения.
I2 - здесь мы делаем выбор.
E2:E21 - суммируем числа, соответствующие найденным совпадениям.
Конечно, вы можете указать параметр выбора непосредственно в выражении:
=SUMMESLY(F2:F21; "Yes";E2:E21)
Но мы уже согласились, что это не совсем рационально.
Это важный момент. Не забудьте, что все текстовые значения должны быть заключены в инвертированные запятые.
Подстановочные знаки для частичного совпадения.
При работе с текстовыми данными часто возникает необходимость поиска части слова или фразы.
Давайте вернемся к нашему случаю. Определим, сколько всего было заказов на темный шоколад. В результате у нас есть 2 подходящих предмета. Как учесть и то, и другое? Для этого существует понятие неточного соответствия.
Мы можем искать и подсчитывать значения, указывая только часть ячейки, а не всю ячейку. Таким образом, мы можем расширить наш поиск, используя символы подстановки "?", "*".
Символ "?" позволяет заменить один символ на любой другой.
Символ "*" позволяет нам заменить любое количество символов (включая ноль).
Эти символы могут быть использованы в нашем случае двумя способами. Вы можете ввести их непосредственно в таблицу -.
=SUMMESLI(C2:C21;I2;E2:E21), где E2 содержит *[слово]*.
или
=СУММЕСЛИ(C2:C21;"*"&I2&"*";E2:E21)
где * вставляется непосредственно в выражение и "склеивается" с нужным текстом.
Давайте потренируемся:
- "*черный*" - мы ищем выражение, в котором встречается это слово, а также все буквы, символы и цифры до и после него. В нашем случае это соответствует "Черному шоколаду" и "Супер черному шоколаду".
- "Д?" - требуется слово из 2 букв, первая из которых - "D", а вторая - любая буква. В нашем случае достаточно "Да".
- "???" - найти слово из любых 3 букв
=SUM(F2:F21;"???";E2:E21)
Этому требованию соответствует "Нет".
- "???????*" - это текст из 7 или более произвольных букв.
=SUM(B2:B21;"???????*";E8:E28)
"Зеленый", "оранжевый", "серебряный", "голубой", "коричневый", "золотой", "розовый" будет достаточно.
- "Z*" - выбирает фразу, первой буквой которой является "Z", за которой следуют любые буквы, символы и цифры. Это "золото" и "зелень".
- "Черный*" - соответствует фразе, начинающейся с этого слова, за которым следуют любые буквы, символы и цифры. Соответствующее слово - "черный шоколад".
Внимание. Если вам нужно использовать в качестве поискового запроса текст, содержащий * и ?, используйте тильду (~) перед этими символами. Тогда * и ? будут рассматриваться как обычные символы, а не как подстановочные знаки:
=СУММЕСЛИ(B2:B21;“*~?*”;E8:E28)
Важный комментарий. Если в тексте поиска имеется несколько * и ?, перед каждым из них следует поставить тильду (~). Например, если мы ищем текст с тремя звездочками, мы можем написать его следующим образом:
=СУММЕСЛИ(B2:B21;“~*~*~*”;E8:E28)
Если же текст содержит только 3 звездочки, мы можем переписать наше выражение следующим образом
=СУММЕСЛИ(B2:B21;“*~*~*~**”;E8:E28)
Точная дата либо диапазон дат.
Если вы хотите найти сумму чисел, которые соответствуют определенной дате, самый простой способ - использовать саму дату в качестве критерия.
Внимание. Обратите внимание, что введенный вами формат даты должен соответствовать региональным настройкам в электронной таблице!
Обратите внимание, что мы также можем вставить его непосредственно в формулу здесь или использовать ссылку.
Рассчитываем итог продаж на сегодня - 04.02.2020.
=СУММЕСЛИ(A2:A21;I1;E2:E21)
или
=SUMMED(A2:A21;TODAY();E2:E21)
Давайте посчитаем за вчерашний день.
=SUMMED(A2:A21;TODAY()-1;E2:E21)
TODAY()-1 - это именно "вчера".
Мы подводим итоги по датам, которые предшествовали 1 февраля.
=СУММЕСЛИ(A2:A21;"
После 1 февраля включительно:
=СУММЕСЛИ(A2:A21;">="&"01.02.2020";E2:E21)
Что если нас интересует временной интервал "от до"?
Мы можем подсчитать итоговые показатели за определенный период времени. Для этого мы используем небольшой трюк: разность функции SUMMESLY. Предположим, нам нужны продажи с 1 по 4 февраля включительно. Из продаж после 1 февраля вычтите все, что было продано до 4 февраля.
=СУММЕСЛИ(A2:A21;">="&"01.02.2020";E2:E21) - СУММЕСЛИ(A2:A21;"
Сумма значений, соответствующих пустым либо непустым ячейкам
Иногда в качестве условия суммирования следует использовать все непустые ячейки, содержащие хотя бы одну букву, число или символ.
Рассмотрим другое использование формулы СУММЕСЛИ в электронной таблице Excel, где нам нужно подсчитать заказы, не имеющие отметки о завершении, и сколько заказов вообще было выполнено.
Если критерием является только "*", мы считаем непустые ячейки, содержащие хотя бы одну букву или символ (кроме пустых ячеек).
=СУММЕСЛИ(F2:F21;"*";E2:E21)
Использование пары символов "больше" и "меньше" вместо звездочки - - дает точно такой же результат.
=СУММЕСЛИ(F2:F21;"";E2:E21)
Теперь посмотрим, как можно найти сумму, соответствующую пустым клеткам.
Чтобы найти пустые ячейки, не содержащие букв или цифр, поставьте пару одинарных кавычек '' в качестве критерия, если значение критерия задано в ячейке и формула ссылается на него.
Если ссылаются только на пустые ячейки в самой формуле, напишите двойные перевернутые запятые.
=СУММЕСЛИ(F2:F21;"";E2:E21)
Сумма по нескольким условиям.
Функция суммирования может работать только с одним условием, как мы это делали ранее. Однако очень часто бывает так, что нам необходимо найти набор данных, удовлетворяющих одновременно нескольким требованиям. Это можно сделать с помощью некоторых приемов, а также с помощью других функций. Давайте проанализируем этот процесс пошагово.
Еще раз вернемся к нашему случаю с приказами. Рассмотрим два условия и найдем, сколько заказов темного и молочного шоколада сделано вместе.
1. СУММЕСЛИ + СУММЕСЛИ
Все просто:
=СУММЕСЛИ($C$2:$C$21;"*"&H3&"*";$E$2:$E$21)+СУММЕСЛИ($C$2:$C$21;"*"&H4&"*";$E$2:$E$21)
Мы находим сумму заказов для каждого типа товара, а затем просто складываем их. Думаю, вы уже научились работать с этим :).
Это самое простое решение, но не самое универсальное и далеко не единственное.
2. СУММ и СУММЕСЛИ с аргументами массива.
Приведенное выше решение очень простое и позволяет быстро выполнить работу, если критериев немного. Но если вы хотите работать с несколькими, то она становится огромной. В этом случае лучшим подходом будет использование массива критериев в качестве аргумента. Давайте рассмотрим этот подход.
Вы можете начать с перечисления всех условий, разделяя их запятыми, а затем заключить окончательный список, разделенный точками с запятой, в скобки {figure}, что технически называется массивом.
Если вы хотите найти покупки этих двух товаров, ваши критерии в виде массива будут выглядеть следующим образом
AMOUNT($C$2:$C$21;{"*black*"; "*dairy*"};$E$2:$E$21)
Поскольку здесь мы используем массив критериев, результатом также будет массив из двух значений.
Теперь воспользуемся функцией SUMM, которая умеет работать с массивами, складывая их содержимое.
=SUM(SUMM($C$2:$C$21;{"*black*";*milk*"};$E$2:$E$21))
Важно, чтобы результаты расчетов в первом и втором случаях были одинаковыми.
3. СУММПРОИЗВ и СУММЕСЛИ.
Что если вы предпочитаете перечислять критерии в определенной части таблицы? Вы можете использовать SUMMESLY в сочетании с функцией SUMMPROIZE, которая перемножает компоненты в заданных таблицах и возвращает сумму произведений.
Это будет выглядеть следующим образом:
=SUMPROV(SUMMULTIPLY(C2:C21;H3:H4;E2:E21))
В H3 и H4 мы напишем критерии отбора.
Но, конечно, ничто не мешает нам записать значения в виде массива критериев:
=SUMPROV(SUMMESLY(C2:C21;{"*black*"; "*dairy*"};E2:E21))
Результат, полученный в обоих случаях, будет идентичен тому, что вы видите на скриншоте.
Важное замечание: Обратите внимание, что все три перечисленных выше метода выполняют вычисления по принципу логического ИЛИ. То есть, мы хотим, чтобы продаваемый шоколад был либо темным, либо молочным.
Почему СУММЕСЛИ у меня не работает?
Этому может быть несколько причин. Иногда ваше выражение возвращает не то, что вы ожидаете, просто потому, что тип данных в ячейке или какой-то аргумент не соответствует ему. Итак, вот что нужно проверить.
1-й "диапазон данных" и "диапазон сумм" должны быть указаны по ссылке, а не в виде массива.
Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например, A1: A10. Если вы попытаетесь передать что-нибудь другое, например, массив {1,2,3}, Excel выдаст сообщение об ошибке.
Справа: =SUMA(A1:A3, 'цвет', C1:C3)
Неправильно : =SUMA({1,2,3}, 'цвет', C1:C3)
2: Ошибка при суммировании значений из других рабочих листов или рабочих книг
Как и любая функция Excel, SUMMESLY может ссылаться на другие рабочие листы и рабочие книги, если они открыты в данный момент.
Найдем сумму значений в полях F2: F9 на листе 1, поскольку соответствующие данные хранятся в столбце A, и если среди них есть "яблоки":
=SUMMEDI([Book1.xlsx]Sheet1!$A$2:$A$9, "яблоки",[Book1.xlsx]Sheet1!$F$2:$F$9)
Однако это перестанет работать, как только Book1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся как таковые в текущей книге. А поскольку массивы не допускаются в аргументах 1 и 3, формула выдает ошибку #HOLE!
3) Чтобы избежать проблем, убедитесь, что диапазоны данных и поиск имеют одинаковый размер.
Как уже упоминалось в начале этого учебника, в современных версиях Microsoft Excel они не обязательно должны быть одинакового размера. Но в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако даже в последних версиях Excel 2010 и Excel 2016 сложные выражения, в которых диапазон добавления имеет меньше строк и/или столбцов, чем диапазон поиска, капризны. Поэтому рекомендуется, чтобы они всегда были одинакового размера и формы.
Примеры расчета суммы:
Функция СУММПРОИЗВ с примерами формул - В этой статье объясняется базовое и расширенное использование функции СУММПРОИЗВ в Excel. Вы найдете ряд примеров формул для сравнения массивов, условного суммирования и подсчета ячеек по нескольким условиям, вычисления средневзвешенного значения.....
Сумма по цвету и подсчет по цвету в Excel - В этой статье вы узнаете, как подсчитывать ячейки по цвету и получать сумму по цвету ячеек в Excel. Эти решения работают как для ручного, так и для условного форматирования. Если.
Формула PROMOTION TOGETHER - основные функции с примерами. - В этой статье объясняются свойства функции СУММЕСЛИ в Excel и показывается, как использовать формулы промежуточных итогов для обобщения данных в видимых ячейках. В предыдущей статье мы рассмотрели, как автоматически вставлять промежуточные итоги.....
Промежуточные итоги в Excel - В этом уроке объясняется, как использовать инструмент "Промежуточные итоги" в Excel для автоматического суммирования, подсчета или усреднения различных групп ячеек. Вы также узнаете, как отображать или скрывать детали промежуточных итогов, копировать только строки.....
Как подсчитать количество пустых и непустых ячеек в Excel - Если ваша задача - заставить Excel подсчитать пустые ячейки на рабочем листе, прочитайте эту статью, чтобы найти 3 способа сделать это. Узнайте, как искать и выбирать среди них, используя стандартные...
Как сравнить два столбца на совпадения и различия - Вам понадобится около 10 минут, чтобы прочитать эту статью, и уже через 5 минут (или даже быстрее) вы сможете легко сравнить два столбца Excel на наличие дубликатов и выделить те, которые найдены.....
Формула суммы в Excel - несколько полезных советов и примеров - Как быстро и легко вычислить сумму в электронной таблице Excel? Попробуйте разные способы: посмотрите на сумму выделенных ячеек в строке состояния, используйте автосумму для добавления всех или только нескольких отдельных ячеек,...
Функция countslime в Excel с несколькими условиями - объяснение с примерами. - В этом уроке объясняется, как использовать в Excel многоусловную функцию countslime, основанную на логике AND и OR. Вы найдете примеры для различных типов данных - чисел, дат, текста,...
КАЛЬКУЛЯЦИЯ в Excel - примеры функций с одним и несколькими условиями - В этой статье мы рассмотрим функцию Excel КАЛЬКУЛЯЦИЯ (COUNTIF на английском языке), которая используется для подсчета ячеек с определенным условием. Сначала мы кратко рассмотрим синтаксис и общее использование, а затем....
Функция SUMMESLIMN - как суммировать ячейки в Excel при наличии нескольких условий? - Этот учебник объясняет разницу между функциями SUMMESLY и SUMIFS с точки зрения их синтаксиса и использования, а также приводит примеры формул для суммирования значений с несколькими критериями.....
By Александр Трифунтов2020-08-31T16:06:50+03:006 февраля, 20201 Комментарии
