Суммирование ячеек в Excel по условию

Обновлено: 24.11.2024

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

Взгляните на этот пример, в котором у нас есть два условия: нам нужна сумма продаж мяса (из столбца C) в Южном регионе (из столбца A).

Вот формула, которую вы можете использовать для достижения этой цели:

=СУММЕСЛИМН(D2:D11,A2:A11,"Юг",C2:C11,"Мясо")

Результатом является значение 14 719.

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

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

=СУММЕСЛИМН(D2:D11,

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

Далее вы хотите найти данные, которые удовлетворяют двум условиям, поэтому вы вводите свое первое условие, указав для функции расположение данных (A2:A11), а также то, что это за условие — «Юг». Обратите внимание на запятые между отдельными аргументами:

=СУММЕСЛИМН(D2:D11,A2:A11,"Юг",

Кавычки вокруг слова «Юг» указывают, что это текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазона ячеек (C2:C11), содержащих слово «мясо», плюс само слово (заключенное в кавычки), чтобы Excel мог сопоставить его. Завершите формулу закрывающей скобкой ) и нажмите Enter. Результат снова равен 14 719.

=СУММЕСЛИМН(D2:D11,A2:A11,"Юг",C2:C11,"Мясо")

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

Глядя на изображение автозаполнения формул и список аргументов, в нашем примере sum_range равен D2:D11, столбцу чисел, которые вы хотите суммировать; Критерий_диапазон1 — это A2.A11, столбец данных, в котором находится критерий 1 «Юг».

По мере ввода остальные аргументы будут отображаться в автозаполнении формул (здесь не показано); Критерий_диапазон2 — это C2:C11, столбец данных, в котором находится критерий 2 «Мясо».

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

Попробуйте

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

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

Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 на новом листе Excel. Вы можете настроить ширину столбцов, чтобы лучше видеть формулы

1. Выберите ячейку, которая будет содержать результат.

2. Выполните одно из следующих действий:

    На вкладке "Формула" в группе "Библиотека функций" нажмите кнопку "Математика и триггер":

Выберите СУММЕСЛИ в списке.

В диалоговом окне "Вставить функцию":

  • выберите Math & Trig в раскрывающемся списке Или выберите категорию,
  • выберите СУММЕСЛИ в списке Выберите функцию.

3. В диалоговом окне «Аргументы функции»:

  • Поле "Диапазон" определяет диапазон ячеек, в которых Excel будет искать для выполнения подсчета. В этом примере диапазон ячеек равен B2:B21.
  • Критерии — это условное выражение, аналогичное условному оператору в операторе IF.
  • Поле Sum_range сообщает Excel, какие ячейки следует добавить, если выполняются критерии для каждой ячейки в диапазоне. В этом примере диапазон ячеек равен D2:D21.

4. Нажмите OK.

  • Эту формулу можно ввести с помощью клавиатуры, например:
    = СУММЕСЛИ (B2:B21, "*Revay", D2:D21)
  • В критериях можно использовать подстановочные знаки, вопросительный знак (?) и звездочку (*). Знак вопроса соответствует любому одиночному символу; звездочка соответствует любой последовательности символов. Если вы хотите найти фактический вопросительный знак или звездочку, введите тильду (~) перед символом. Например:

  • Чтобы подсчитать количество вхождений строки текста или числа в диапазоне ячеек, используйте функцию СЧЁТЕСЛИ (дополнительную информацию см. в разделе Как подсчитывать ячейки по критериям).
  • Чтобы формула возвращала одно из двух значений в зависимости от условия, используйте функцию ЕСЛИ.
  • Для анализа данных в списке на основе критериев, таких как размер прибыли или типы продуктов, используйте функции управления базой данных и списками (DCOUNTA, DCOUNTA, DSUM и т. д.).

Как считать ячейки по критериям

Диаграмма с областями для инвестиций

Многие финансовые и нефинансовые компании, такие как Yahoo, предоставляют услуги по отслеживанию инвестиционного портфеля, но не позволяют изменять внешний вид диаграмм. Microsoft Excel дает вам полную гибкость, предоставляя различные типы настраиваемых диаграмм. Например, существует несколько вариантов диаграммы с областями для отслеживания стоимости и затрат вашего портфеля.

Как считать дни недели в диапазоне дат

Время от времени вам нужно считать дни недели в некотором диапазоне дат или между двумя датами (см. Как считать дни недели между двумя датами). Excel предлагает различные формулы для работы с данными. Формулу, необходимую для этой задачи, легко создать:

Аналитика, визуализация данных, бизнес-аналитика и другие актуальные темы.

Давайте общаться!

Последние записи

Категории

7 способов условно вычислить сумму значений в Excel.


Excel предлагает разные способы выполнения одной и той же задачи. Это особенно очевидно в случае использования функций Excel, где мы можем просто выбрать ту, которая предлагает наилучшее решение, или, что более реалистично, ту, которую нам удобнее использовать. В качестве примера давайте решим следующий сценарий: Мы предлагаем онлайн-курсы Excel как на нашем внутреннем сайте, так и на платформе Udemy. Udemy взимает с нас 50% комиссионных за все продажи курсов, а также предлагает рекламные тарифы для увеличения нашего объема. В результате мы продаем один и тот же контент по разным ценам. Глядя на показатели продаж на неделе благодарения, давайте посчитаем нашу общую чистую выручку для всех транзакций Udemy (выделено). Давайте используем различные функции Excel для выполнения необходимых расчетов.

Метод 1. Используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ при фильтрации строк.
Мы рассмотрели использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в нашем последнем посте. Этот метод имеет свои ограничения, так как мы должны фактически фильтровать наши данные и не можем выполнять этот расчет на лету. В противном случае мы можем пойти по этому пути.

Способ 2. Используйте функцию СУММЕСЛИ

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

Эта функция позволит нам вычислить сумму диапазона, указанного на основе нашего условия, примененного к диапазону критериев. В нашем примере нашим диапазоном критериев является веб-платформа: $C6:$C$22, нашим условием является используемая веб-платформа: «Udemy», а диапазоном, который мы хотели бы рассчитать, если совпадение найдено, является чистый объем продаж: $ G$6:$G$22. Наша формула становится следующей:

Способ 3. Используйте функцию СУММЕСЛИМН

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

Эта функция СУММЕСЛИМН, появившаяся в Excel 2007, представляет собой расширенную версию функции СУММЕСЛИ и позволяет выполнять вычисления на основе нескольких условий, а не только одного. В нашем примере давайте примем его для использования с единственным условием, которое у нас есть. Однако может не возникнуть практической необходимости использовать функцию СУММЕСЛИМН для суммы одного критерия; при необходимости мы можем просто изменить его для расчета нескольких условий без необходимости переключения используемых функций. Обратите внимание, что параметры в этой функции имеют другой порядок, чем в функции СУММЕСЛИ. Наш sum_range, как и раньше, равен: $G$6:$G$22. Наш критерий_диапазон1 — это $C$6:$C$22, а наш критерий — «Udemy». Собрав все части вместе, мы теперь имеем:

ПРИМЕЧАНИЕ. Все приведенные выше примеры принимают аргументы, которые являются диапазонами (ТОЛЬКО отдельные ячейки или диапазоны ячеек), в то время как последующие примеры принимают массивы в качестве параметров.Если указан диапазон, они просто преобразуют его в массив.

Способ 4. Используйте функцию СУММПРОИЗВ

=СУММПРОИЗВ(массив1,[массив2]…)

До Excel 2007 функция СУММПРОИЗВ использовалась для выполнения суммирования по нескольким условиям. Синтаксис этой функции позволяет нам заменять звездочку запятой, что немного упрощает работу с этой функцией. В нашем примере нам нужно «умножить» наш чистый доход на любую запись, найденную как совпадение в столбце веб-платформы для «Udemy»:

=СУММПРОИЗВ($G$6:$G$22*($C$6:$C$22="Удеми"))

Способ 5. Используйте функцию DSUM

=DSUM(база данных, поле, критерии)

Эту редко используемую функцию базы данных в Excel немного сложно настроить, но она очень полезна при работе с несколькими критериями. Наша база данных представляет собой весь спектр записей, включая заголовки столбцов: $B$5:$G$22. Параметр field принимает либо текстовую строку имени столбца, либо номер столбца в диапазоне. Оба: «Чистый доход» и 6 будут работать для нас. Наконец, критерии часто требуют от нас настройки некоторых «вспомогательных» ячеек, но в моем случае я удобно использую в качестве критерия значение из самой первой записи в нашем диапазоне:

ПРИМЕЧАНИЕ. Следующие две функции являются функциями массива, и их необходимо вводить с помощью следующей комбинации клавиш: CTRL+SHIFT+ENTER в поле формулы. Для пользователей Mac вам придется использовать COMMAND + RETURN.

Способ 6. Используйте функцию СУММ

Синтаксис очень похож на функцию SUBOTAL, но параметры меняются местами. Нам нужно сначала указать наше условие, а затем «умножить» на диапазон суммы. Не забудьте использовать CTRL+SHIFT+ENTER:

Способ 7. Используйте комбинацию функций СУММ(ЕСЛИ())

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

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

Метод 8 (БОНУС!) — Использование сводных таблиц.
Функция сводной таблицы — это еще один инструмент Excel, который мы можем использовать для вычисления требуемых результатов, однако они заслуживают отдельного поста или серии статей.

Примеры с несколькими условиями поиска:

Использование функции SUMIF для одного условия поиска может быть излишним, давайте изменим наши критерии, чтобы включать только скидки курсы Udemy. Не стесняйтесь загружать сопутствующую книгу для этого поста и найдите решения ниже. Обратите внимание на создание «вспомогательного» диапазона данных R1:S2 для настройки функции DSUM. Также обратите внимание, что версия оператора IF с массивом не позволяет использовать функцию AND, поэтому мне пришлось использовать подход с вложенной функцией IF. Кроме того, мы больше не можем использовать функцию СУММЕСЛИ, поскольку она может обрабатывать только одно условие за раз:

Что вы думаете по этой теме? Что касается условного суммирования данных, какие функции вы используете и почему?

Суммирование с помощью критерия "или" в Excel может оказаться сложной задачей. В этой статье показано несколько простых примеров.

<р>1. Начнем с простого. Например, мы хотим просуммировать ячейки, соответствующие следующим критериям: Google или Facebook (один диапазон критериев).

2а. Однако, если мы хотим просуммировать ячейки, соответствующие следующим критериям: Google или Stanford (два диапазона критериев), мы не можем просто дважды использовать функцию СУММЕСЛИ (см. рисунок ниже). Ячейки, соответствующие критериям Google и Stanford, добавляются дважды, но их следует добавлять только один раз. 10 — это ответ, который мы ищем.

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

Объяснение: ИСТИНА = 1, ЛОЖЬ = 0.Для строки 1 функция ЕСЛИ оценивается как ЕСЛИ(ИСТИНА+ИСТИНА,1,0)*3, ЕСЛИ(2,1,0)*3, 3. Таким образом, будет добавлено значение 3. Для строки 2 функция ЕСЛИ оценивается как ЕСЛИ(ЛОЖЬ+ЛОЖЬ,1,0)*5, ЕСЛИ(0,1,0)*5, 0. Таким образом, значение 5 не будет добавлено. Для строки 3 функция ЕСЛИ оценивается как ЕСЛИ(ЛОЖЬ+ИСТИНА,1,0)*2, ЕСЛИ(1,1,0)*2, 2. Таким образом, будет добавлено значение 2 и т. д.

2с. Все, что нам нужно, это функция СУММ, которая суммирует эти значения. Для этого (не переусердствуйте) мы добавляем функцию СУММ и заменяем A1 на A1:A8, B1 на B1:B8 и C1 на C1:C8.

2д. Завершите, нажав CTRL + SHIFT + ENTER.

Примечание: строка формул указывает, что это формула массива, заключая ее в фигурные скобки <>. Не вводите их самостоятельно. Они исчезнут, когда вы отредактируете формулу. Используете Excel 365 или Excel 2021? Закончите, просто нажав Enter. Вы не увидите фигурные скобки.

Объяснение: диапазон (константа массива), созданный функцией ЕСЛИ, хранится в памяти Excel, а не в диапазоне. Константа массива выглядит следующим образом:

умножив на C1:C8, получим:

Эта последняя константа массива используется в качестве аргумента для функции СУММ, что дает результат 10.

<р>3. Мы можем сделать еще один шаг вперед. Например, мы хотим просуммировать ячейки, соответствующие следующим критериям: (Google и Стэнфорд) или Колумбия.

Читайте также: