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

Электрические таблицы в Excel разрешают делать разные вычислительные деяния, включая суммирование ячеек. При работе с впечатляющим объемом данных, время от времени требуется проделывать математические операции с ячейками, попадающими по определенные условия. Для таковых задач в Excel предусмотрены особые функции для суммирования по условию, которые упрощают работу с таблицами.

1 Обычное сложение в Excel
2 Суммирование ячеек по условию
3 Функция «СУММЕСЛИ»
4 Функция «СУММЕСЛИМН»
5 Результат

Обычное сложение в Excel

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

Функция СУММ в Excel

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

Excel также предугадывает суммирование с учетом условия. Функция применяется, когда нужно просуммировать лишь те значения, которые соответствуют данному условию. Для нее может быть задать три аргумента:

Спектр – неотклонимый аргумент, представляющий из себя массив, в каком происходит проверка данного условия;
Аспект – очередной неотклонимый аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, нужно ввести его без кавычек, в остальных вариантах нужны кавычки: к примеру, если значение больше числа 5, то его необходимо прописать, как «>5» . Также работают текстовые значения: если необходимо суммировать выручку торговца Иванова в таблице, то прописывается условие «Иванов» ;
Спектр суммирования – массив значений, которые необходимо сложить.

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

Функция «СУММЕСЛИ»

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

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

В выделенной ячейке отобразится итог сложения, а в верхней строке – функция. Вычислить значение можно без вызова окна «Аргументы функции», если прописать все вручную. При всем этом базисная запись обязана смотреться так: =СУММЕСЛИ(X; «Условие»; Y) , где X – это спектр, а Y – спектр суммирования. Значения не непременно вчеркивать, их довольно выделить при помощи мышки в таблице. Все элементы функции разбиты знаком «;».

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

Функция «СУММЕСЛИМН»

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

Чтоб суммировать ячейки с несколькими критериями, действуйте согласно последующей аннотации:

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

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

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

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

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

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

Пример 1. В таблице Excel записаны члены геометрической прогрессии. Какую часть (в процентах) составляет сумма первых 15 членов прогрессии от общей суммы ее членов.

Вид начальной таблицы данных:

Выполним расчет при помощи последующей формулы:

A3:A22 – спектр ячеек, содержащих порядковые номера членов прогрессии, относительно которых задается аспект суммирования;
«<=15» – логическое выражение, являющееся аспектом суммирования;
B3:B22 – спектр ячеек, содержащих значения членов прогрессии.

Толика в процентах первых 15-ти значений (75% — от количества всех 20-ти значений) данной геометрической прогрессии составляет всего только 0,41%.

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

Пример 2. В таблице Excel указаны данные о работе 2-ух продавцов маленького магазина. Найти, какой из сотрудника принес больший доход за 19 рабочих дней).

Начальная таблица имеет последующий вид:

Для расчета используем функцию в формуле:

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

В итоге получим последующее значение:

Как в Excel суммировать ячейки лишь с определенным значением

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

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

Вешними месяцами являются месяца с номерами 3, 4 и 5. Для расчета используем формулу:

Сумма зарплат с 6-го по 12-й месяц является подмножеством огромного количества суммы зарплат с 3-го по 12-й месяц. Разница этих сумм является разыскиваемой величиной – суммой зарплат за вешние месяцы:

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

Функция СУММЕСЛИ в MS Excel. Варианты данной функции с описанием и примерами.

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

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

Вероятные варианты данной функции:

сумма, если больше;
сумма, если меньше;
сумма, если равно;
сумма, если не равно;
сумма если больше либо равно;
сумма если меньше либо равно.

Отдельная статья обрисовывает функцию СУММЕСЛИ Excel. Четкое совпадение. Частичное совпадение. С текстовыми аспект. Содержит описание, примеры и иллюстрации. Статья находиться по данной ссылке: Функция СУММЕСЛИ в MS Excel. Четкое совпадение. Частичное совпадение. {Инструкция} и примеры.

Самый обычный и понятный метод вызвать данную функцию, это надавить в правом верхнем углу, рядом с клавишей ∑, стрелку вниз (Рис. 1).

Показаться перечень функций. Обычно, это более нередко применяемые функции. В этом перечне уже быть может подходящая Для вас функция СУММЕСЛИ. В нашем случае ее там нет. Тогда избираем пункт Остальные функции. Он находиться понизу перечня (Рис. 2).

Возникает диалоговое окно – Вставка функции. Самое верхнее, это поле для поиска подходящей функции. Вводим заглавие функции, избираем ее в поисковых результатах и жмем ОК (Рис. 3).

Возникает диалоговое окно — Аргументы функции (Рис. 4).

В нем есть три главных поля для наполнения:

Спектр — тут указываться спектр ячеек, в каких будет происходить поиск по обозначенному аспекту;
Аспект — тут будет указан сам аспект поиска;
Спектр _ суммирования, тут указывается спектр ячеек, данные в каких будут суммироваться, исходя из аспекта поиска. Применяется не во всех вариантах.

Что бы избрать подходящий спектр нажимаете на стрелочку в конце строчки спектра и выбираете нужные Для вас ячейки при помощи мыши. Можно сходу избрать подходящий спектр, не нажимая на стрелочку, но поначалу необходимо поставив курсор на подходящую строчку в диалоговом окне. Можно прописать спектр вручную. Итог от этого не зависит. Опосля того как все готово, надавливает ОК (Рис. 5).

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

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

Сумма, если больше, Сумма, если меньше. СУММЕСЛИ.

Знаки, которые употребляются: >, <.

У нас есть 25 строк с заглавием Пример и номер примера, в таблице. Напротив всякого примера стоит значение (Рис. 6).

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

Формула в ячейке, как пример, смотрится вот так:

Напротив всякого примера стоит число от 100 до 1300. Нам необходимо просуммировать, лишь значения, которые больше числа 500.

Вызываем функцию, как описано выше и избираем подходящий нам спектр в строке – Спектр (Е3;Е27). Это столбец со значениями чисел от 100 до 1300.

Вводим аспект. В строке Аспект, пишем: >500, вручную. Можно скопировать значение аспекта с самой таблицы и добавить в ручную лишь символ >. Кавычки покажутся автоматом (Рис. 7).

Жмем ОК. Напротив ячейки с словом ИТОГО, возникла сумма чисел больше 500 (Рис. 8)

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

Формула в ячейке, как пример, смотрится вот так:

Допустим, нам необходимо отыскать сумму в спектре значений (E3:E27) меньше числа 500.

Метод буквально таковой же. В аспекты показывает символ меньше <, и нужное значение. В нашем примере 500.

Сумма, если равно, Сумма, если не равно. СУММЕСЛИ.

Знаки, которые употребляются: =, <>.

Сумма, если равно.

Формулы в ячейках, как пример, смотрятся вот так:

В столбце D, есть четыре ячейки с Пример 1 (выделенные красноватым шрифтом). Наша задачка суммировать значения, которые стоят лишь напротив Пример 1. Вызываем функцию СУММЕСЛИ. В Спектре избираем подходящий спектр (столбец D) (D3:D27).

В Аспект, вручную пишем Пример 1 либо копируем с таблицы. По этому аспекту будет осуществляться поиск подходящих ячеек в спектре (D3:D27).

В Диапазон_суммирования избираем спектр ячеек с значениями (столбец Е), которые будут суммироваться (E3:E27). Суммироваться будут те ячейки, которые будут отвечать нашему аспекту и находиться напротив ячеек с значением Пример 1. Жмем ОК (Рис. 9).

Так же можно употреблять данный вариант функции, когда из перечня значений (цифр), необходимо суммировать лишь числа равные определенному значению. К примеру, в спектре значений (E3:E27), нам необходимо суммировать лишь значения равные числу 100 (выделенные красноватым шрифтом). В строке Аспект указываем нужное значений, опосля знака =. В нашем случае это цифра 100. Диапазон_суммирования не заполняем. (Рис. 10).

Получаем разыскиваемую сумму, в нашем случае это число 400.

Сумма, если не равно.

Формулы в ячейках, как пример, смотрятся вот так:

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

Избираем в Спектр ячейки, содержащие наш аспект (D3:D27) – Пример 1

В Аспект прописываем: <> Пример 1. Получаем сумму всех значений (спектр E3:E27), не считая тех, которые стоят напротив Пример 1 (спектр (D3:D27)).

В Диапазон_суммирования избираем ячейки, значения которых необходимо суммировать (E3:E27). Жмем ОК. Получаем подходящую нам сумму. (Рис. 11).

Либо нам необходимо суммировать лишь значение не равные числу 100.

Тогда в строке Спектр указываем подходящий нам спектр для суммирования (E3:E27).

В Аспект пишем <>100.

Диапазон_суммирования не заполняем.

Жмем ОК. Получаем сумму всех ячеек, не считая тех, где значение равно числу 100. (Рис. 12).

Сумма если больше либо равно, Сумма если меньше либо равно. СУММЕСЛИ.

Знаки, которые употребляются: >=, <=.

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

Формула в ячейке, как пример, смотрится вот так:

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

В строке Спектр избираем спектр ячеек, которые необходимо просуммировать (E3:E27).

В Аспект пишем:>=500.

Диапазон_суммирования не заполняем.

Жмем ОК. Получаем сумму значений равною числу 500, плюс все, что больше данной числа в данном спектре (E3:E27) (Рис. 13).

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

Формула в ячейке, как пример, смотрится вот так:

Метод буквально таковой же, как и в прошлом примере. Лишь заместо символ больше, используем символ меньше и равно <=. Получаем сумму всех чисел равную либо меньше числу 500, которые находятся в данном спектре (E3:E27).

Обращаю Ваше внимание, что выше приведенные примеры, являются вариациями использования данной функции. Метод использования данной функции должен определяться задачей, которую нужно решить.

Функция БДСУММ() — Сложение с множественными критериями в EXCEL

Разглядим сильную функцию суммирования БДСУММ() , британский вариант DSUM( database, field, criteria ). Эту функцию имеет смысл употреблять, когда нужно просуммировать значения с учетом нескольких критерий. Подробный анализ этих задач приводится в группе статей Сложение чисел с несколькими аспектами .

Как показано в вышеуказанных статьях, без функции БДСУММ() можно совершенно обойтись, заменив ее функциями СУММПРОИЗВ() , СУММЕСЛИМН() либо формулами массива . Но, время от времени, функция БДСУММ() вправду комфортна, в особенности при использовании бессчетных либо сложных критериев, к примеру, с подстановочными знаками . Поначалу разберем синтаксис функции, потом решим задачки.

Синтаксис функции БДСУММ()

Для использования данной функции требуется чтоб:

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

БДСУММ( база_данных;поле;условия ) База_данных представляет собой спектр ячеек с данными связанными логически, т.е. таблицу. Верхняя строчка таблицы обязана содержать заглавия всех столбцов. Поле — Заголовок столбца, по которому делается суммирование (т.е. столбец с числами). Аргумент Поле можно заполнить введя:

текст с заголовком столбца в двойных кавычках, к примеру «Возраст» либо «Сбор»,
число (без кавычек), задающее положение столбца в таблице (обозначенной в аргументе база_данных ): 1 — для первого столбца, 2 — для второго и т.д.
ссылку на заголовок столбца.

Условия — интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с аспектами отбора для БДСУММ() подобна структуре для Расширенного фильтра .

Задачки

Представим, что в спектре A 8:С13 имеется таблица продаж, содержащая поля (столбцы) Продукт , Торговец и Реализации (см. набросок выше и файл примера ).

Задачка 1 (с одним числовым аспектом).

Просуммируем все реализации, которые >3000.

Сделаем в спектре F2:F3 табличку с аспектом (лучше табличку располагать над начальной таблицей, чтоб она не мешала добавлению новейших данных в таблицу), состоящую из заголовка Реализации (совпадает с заглавием заголовка столбца начальной таблицы, к которому применяется аспект) и фактически аспекта (условия отбора) >3000.
запишем саму формулу =БДСУММ(C8:C13;C8;F2:F3) Предполагая, что База_данных (начальная таблица) находится в С8:C13 (столбцы А (Продукт) и В (Торговец) можно в этом случае не включать в Базу_данных, т.к. они не участвуют в аспекты отбора и по ним не делается суммирование). С8 – это ссылка на заголовок столбца по которому будет выполняться суммирование (т.е. столбец Реализации). F2 : F3 – ссылка на табличку критериев

Другое решение — = СУММЕСЛИ(C9:C13;F3) либо = СУММЕСЛИ(C9:C13;»>3000″)

Задачка 2 (с одним текстовым аспектом)

Просуммируем все значения продаж торговца Белов .

Сделаем новейшую табличку критериев, состоящую из заголовка Торговец (совпадает с заглавием заголовка столбца начальной таблицы, к которому применяется аспект) и фактически аспекта (условия отбора);

Условие отбора обязано быть записано в особом формате: =»=Белов» (будут суммироваться Реализации лишь строк, у каких в столбце Торговец содержится буквально слово Белов (либо белов , беЛОв , т.е. без учета РЕгиСТра ). Если имеются строчки с Торговцами « ИванБелов», «Белов Иван» и пр., то суммирование по ним выполняться не будет. Примечание : Если в качестве аспекта указать не , а просто Белов , то, будут суммироваться Реализации строк, у каких в столбце Торговец содержатся значения, начинающиеся со слова Белов (к примеру, « Белов Иван », Белов , белов ). Чтоб просуммировать реализации, в том числе и для торговца « Иван Белов », нужно в качестве аспекта указать =»=*Белов». Этот аспект учитывает значения, заканчивающиеся на Белов.Звездочка ( *) — это подстановочный символ .Если в качестве аспекта указать *Белов (либо =»=*Белов*») , то будут подсчитаны числа, в соответственных ячейках которых содержится слово Белов.
Сейчас можно в конце концов записать саму формулу =БДСУММ(B8:C13;C8;B2:B3) Предполагая, что База_данных (начальная таблица) находится в B8:C13 (столбец А ( Продукт ) можно в этом случае не включать в Базу_данных, т.к. он не участвует в формировании условия и по нему не делается суммирование). С8 – это ссылка на заголовок столбца по которому будет выполняться суммирование (т.е. столбец Реализации ). B2:B3 – ссылка на табличку критериев.

Другое решение — = СУММЕСЛИ(B9:B13;»белов»;C9:C13)

Задачка 3 (Два аспекта к различным столбцам строчки, Условие И)

Найдем сумму продаж >3000 лишь торговца Белов . Т.е. необходимо отобрать строчки, у каких в столбце Торговец числится Белов , а в столбце Реализации значение >3000, потом просуммировать значения продаж в отобранных строчках (см. также статью про Условие И ).

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

Формула для сложения: = БДСУММ(B8:C13;C8;F2:G3)

Другое решение — =СУММЕСЛИМН(C9:C13;B9:B13;G3;C9:C13;F3) либо =СУММЕСЛИМН(C9:C13;B9:B13;»белов»;C9:C13;»>3000″)

Задачка 4 (Два текстовых аспекта к одному столбцу, условие отбора ИЛИ)

Найдем сумму продаж продавцов Белов ИЛИ Батурин . Т.е. необходимо отобрать строчки, в каких в столбце Торговец числится Белов ИЛИ Батурин (см. также статью про Условие ИЛИ ).

В этом случае, аспекты отбора должны располагаться под заголовком, заглавие которого совпадает с заголовком столбца начальной таблицы, по которому будет выполняться поиск (столбец Торговец ). Аспекты должны размещаться друг под другом (в различных строчках), т.к. отбираются строчки, у каких в поле Торговец значение Белов ИЛИ строчки, у каких в поле Торговец значение Батурин (функция БДСУММ () вроде бы совершает 2 прохода по таблице с различными аспектами для 1-го поля).

Записать саму формулу можно так =БДСУММ(B8:C13;C8;B2:B4)

Другое решение — =СУММЕСЛИ(B9:B13;»белов»;C9:C13)+СУММЕСЛИ(B9:B13;»батурин»;C9:C13)

Задачка 5 (Два аспекта к различным столбцам, условие отбора ИЛИ)

Найдем сумму продаж Белова ИЛИ Продаж >6000 Т.е. необходимо отобрать строчки, в каких в столбце Торговец числится Белов ИЛИ в столбце Реализации имеется значение >6000 .

Аспекты должны размещаться в различных строчках и в различных столбцах, т.к. отбираются строчки, у каких в поле Торговец значение Белов ИЛИ строчки, у каких в поле Реализации значение >6000 (функция БДСУММ () вроде бы совершает 2 прохода по таблице с различными аспектами для 2-х различных полей).

Записать саму формулу можно так =БДСУММ(B8:C13;C8;G2:H4)

Другое решение — = СУММЕСЛИ(B9:B13;G3;C9:C13)+СУММЕСЛИ(C9:C13;H4)-СУММЕСЛИМН(C9:C13;B9:B13;G3;C9:C13;H4) либо = СУММЕСЛИ(B9:B13;»белов»;C9:C13)+СУММЕСЛИ(C9:C13;»>6000″)-СУММЕСЛИМН(C9:C13;B9:B13;»белов»;C9:C13;»>6000″)

Задачка 6 (Два текстовых аспекта к различным столбцам, условие отбора И)

Найдем сумму продаж продукта Фрукты торговца Белов . Т.е. необходимо отобрать строчки, в каких в столбце Торговец числится Белов И в столбце Продукт числится Фрукты .

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

Записать саму формулу можно так =БДСУММ(A8:C13;C8;A2:B3)

Другое решение — =СУММЕСЛИМН(C9:C13;A9:A13;»фрукты»;B9:B13;»белов»)

Задачка 7 (Условия отбора, сделанные в итоге внедрения формулы)

Просуммируем реализации, которые выше среднего.

В качестве условия отбора можно употреблять значение, вычисляемое с помощью формулы. Формула обязана возвращать итог ИСТИНА либо ЛОЖЬ.

Для этого введем в ячейку С3 файла примера формулу =C9>СРЗНАЧ($C$9:$C$13) , а в С2 заместо заголовка введем случайный поясняющий текст, к примеру, « Больше среднего » (заголовок не должен повторять заглавия начальной таблицы).

Направьте внимание на то, что спектр нахождения среднего значения введен с внедрением абсолютных ссылок ( $C$9:$C$13 ), а среднее значение всех продаж таблицы СРЗНАЧ($C$9:$C$13) сравнивается с первым значением спектра, ссылка на который задана относительной адресацией ( C9 ). При вычислении функции БДСУММ() EXCEL увидит, что С9 — это относительная ссылка, и будет передвигаться по спектру вниз по одной записи и возвращать значение или ИСТИНА, или ЛОЖЬ (больше среднего либо нет). Если будет возвращено значение ИСТИНА, то соответственная строчка таблицы будет учтена при суммировании. Если возвращено значение ЛОЖЬ, то строчка учтена не будет.

Записать формулу можно так =БДСУММ(C8:C13;C8;C2:C3)

Другое решение — =СУММЕСЛИ(C9:C13;»>»&СРЗНАЧ($C$9:$C$13))