Сумма по условию эксель

Сумма ячеек по одному или нескольким условиям в Excel — как посчитать?

Как в программе Эксель (Excel) посчитать сумму ячеек, значение которых соответствует:

1) одному условию?

2) сразу нескольким условиям?

Каким образом можно сделать подобное суммирование ячеек?

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

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

  • диапазон это ячейки входящие в условие
  • условие это строка содержащая условие отбора, состоящее из логического оператора и значения, если указано только значение то это означает что значение соответствующей ячейки должно быть равно в точности. Можно использовать подстановочные знаки *(любые символы) и ?(один любой символ)
  • диапазон_суммировани­ я это ячейки которые должны суммироваться (указывается только если этот диапазон не совпадает с диапазоном для вычисления условия)

Для суммирования ячеек по многим условиям предусмотрена функция

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

  • диапазон1,диапазон2 . это ячейки входящие в условие
  • условие1, условие2 . это строка содержащая условие отбора, состоящее из логического оператора и значения, если указано только значение то это означает что значение соответствующей ячейки должно быть равно в точности. Можно использовать подстановочные знаки *(любые символы) и ? (один любой символ)
  • диапазон_суммировани­ я это ячейки которые должны суммироваться (указывается только если этот диапазон не совпадает с диапазоном для вычисления условия)

Суммирование значений с учетом нескольких условий

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

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

Вот формула, которую можно использовать для акомплиш:

= СУММЕСЛИМН (D2: D11; a2: A11; «Юг»; C2: C11; «мясо»)

Результат — значение 14 719.

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

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

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

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

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

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

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

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

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

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

Попробуйте попрактиковаться

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

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

Читать еще:  Excel зависает при открытии файла

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

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

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

Если вы хотите узнать подробную инструкцию, как создать таблицу в Excel 2013 с точными размерами в см., вы можете прочитать статью об этом на нашем портале.

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

Данной функцией можно воспользоваться при обработке больших чисел, которые просто нужно сложить, не отделяя ячейки по каким-то критериям. Буквально, как «+» в калькуляторе. Для выполнения обычного сложения необходимо:

    Выделить нужную вам ячейку, нажав на нее один раз левой кнопкой мыши.

Важно! Знайте, что программа проигнорирует логическое или текстовое значения.

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

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

Примечание! Можно вводить также математическое выражение.

Отмечаем диапазон суммирования из столбца «Сумма».

На заметку! Эту функцию также можно ввести вручную, используя базовую запись: «=СУММЕСЛИ(x), где х – диапазон, критерий и диапазон суммирования, которые перечисляются через «;». Например, «=СУММЕСЛИ(А1:А2;«Условие»;В1:В2)».

Однако, если нужно отобрать информацию по нескольким разным критериям?

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

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

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

Важно! Обратите внимание – в отличие от «СУММЕСЛИ», в данном окне сначала задается диапазон суммирования, а потом уже условия. Также можно ввести до 127 условий.

Заполните диапазоны условий и сами условия.

Примечание! Более подробную инструкцию вы можете найти в этой статье чуть выше.

Предположим, нам нужно узнать, на какую сумму Васечкин продал яблок. У нас есть всего 2 условия – продавец должен быть Васечкин, а товар – яблоки. В нашем случае, аргументы функции будут выглядеть следующим образом.

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

С наглядной инструкцией вы также можете ознакомиться в видео.

Видео — Суммирование по условию в Excel, функция «СУММЕСЛИМН»

Понравилась статья?
Сохраните, чтобы не потерять!

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

  • Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
  • Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
  • Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.
Читать еще:  Самоучитель по эксель

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3Условие3), и четвертую, и т.д. — при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

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

После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:

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

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

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

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

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

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

  • A3:A22 – диапазон ячеек, содержащих порядковые номера членов прогрессии, относительно которых задается критерий суммирования;
  • » СУММЕСЛИ(C3:C21;»Петров»;B3:B21);»Иванов»;»Петров»)’ class=’formula’>

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

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

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

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

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

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

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

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

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

В результате получим:

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

Функция СУММЕСЛИ имеет следующий синтаксис:

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

  • диапазон – обязательный для заполнения аргумент, принимающий ссылку на диапазон ячеек с данными, в отношении которых применяется определенный критерий. В ячейках данного диапазона могут содержаться имена, текстовые строки, данные ссылочного типа, числовые значения, логические ИСТИНА или ЛОЖЬ, даты в формате Excel. Если данный диапазон также является диапазоном суммирования (третий аргумент опущен), на итоговый результат не влияют пустые ячейки и ячейки, содержащие текстовые данные.
  • условие – обязательный для заполнения аргумент, который может быть указан в виде числа, текстовой строки, логического выражения, результата выполнения какой-либо функции. Переданное в качестве данного аргумента значение или выражение является критерием суммирования рассматриваемой функции.
  • [диапазон_суммирования] – необязательный для заполнения аргумент, принимающий ссылку на диапазон ячеек, содержащих числовые значения, для которых будет рассчитана сумма с учетом критерия суммирования (условие).
  1. Если третий необязательный аргумент явно не указан, диапазон ячеек, указанных в качестве первого аргумента, также является диапазоном суммирования.
  2. Условия, представленные в виде текстовой строки или выражения, содержащего символы «>», « <», «=», должны быть указаны в кавычках. Если аргумент условие представлен в виде числа, кавычки не требуются.
  3. Если аргумент условие указан в виде текстовой строки, можно использовать жесткий критерий (точное совпадение с указанной подстрокой) или выполнить поиск значений с неточным совпадением, заменив недостающие символы звездочкой «*» (любое количество символов) или вопросительным знаком «?» (один любой символ). В качестве примеров могут быть критерии «ст?л» (стол либо стул при поиске наименований мебели), «Ива*» (фамилии Иванов, Иваненко, Иванищев и другие, которые начинаются на «Ива»).
  4. Если функции ссылаются на ячейки, содержащие коды ошибок #ЗНАЧ! или текстовые строки длиной свыше 255 символов, функция СУММЕСЛИ может возвращать некорректный результат.
  5. Аргументы могут ссылаться на диапазоны с разным количеством ячеек. Функция СУММЕСЛИ рассчитывает сумму значений для такого количества ячеек из диапазона суммирования, которое соответствует количеству ячеек, содержащихся в диапазоне. Расчет выполняется с левой верхней ячейки диапазона суммирования.
  6. Функция СУММЕСЛИ позволяет использовать только один критерий суммирования. Если необходимо указать сразу несколько критериев, следует использовать функцию СУММЕСЛИМН.
  7. Критерий суммирования не обязательно должен относиться к диапазону суммирования. Например, для расчета общей зарплаты сотрудника за год в таблице, в которой содержатся данные о зарплате всех сотрудников, можно ввести формулу =СУММЕСЛИ(A1:A100;”Петренко”;B1:B100), где:
  • a. A1:A100 – диапазон ячеек, в которых хранятся фамилии сотрудников;
  • b. «Петренко» – критерий поиска (фамилия работника) для диапазона A1:A100;
  • c. B1:B100 – диапазон ячеек, в которых хранятся данные о зарплатах работников (диапазон суммирования).

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector