Функция СУММЕСЛИ в Excel: примеры внедрения

Функция СУММЕСЛИ в Excel: примеры применения

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

Суть функции

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

Аргументы данной для нас функции:

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

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

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

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

Поставить курсор в ячейку, куда нужно вывести итог
На ленте избрать «Формулы»-«Математические»-СУММЕСЛИ. Раскроется окно «Аргументы функции».

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

Менеджер Щербаков А. К. за прошедший месяц воплотил продукции на 54302 рубля (поле «Значение» в диалоговом указывает итог еще до нажатия клавиши ОК). На рассмотренном выше примере видно, что в Excel функция СУММЕСЛИ дозволяет оперативно суммировать нужные характеристики.

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

Функция СУММЕСЛИ в Excel: пример с несколькими условиями

В рассмотренном выше примере нужно отыскать сумму по заказам, реализованным менеджером Воробьевым И. И. заказчику ИП Васькову А. П. Таковым образом, критериев отбора два: сотрудник и заказчик. Как и в прошлом примере, ставим курсор в ячейку, где будет итог. «Формулы»-«Математические»-СУММЕСЛИмн. Возникает окно «Аргументы». Заполняется оно несколько непревзойденно от предшествующего примера.

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

СУММЕСЛИ с логическим аспектом (больше, меньше, равно)

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

В таковых вариантах при указании в аспекты данного условия его заключают в двойные кавычки.

На рисунке показано, как посчитать сумму всех заказов стоимостью больше 10000 рулей.

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

< – меньше;
= – равно (можно не указывать);
< > – не равно;
>= – больше либо равно;
<= – меньше либо равно.

В Excel функция СУММЕСЛИ с аспектом в виде логического выражения применяется довольно нередко.

СУММЕСЛИ с текстовым аспектом

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

При работе с текстовыми аспектами нет необходимости верно соблюдать регистр.

Функция СУММЕСЛИ с датой в качестве аспекта

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

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

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

Формула суммеслимн в excel примеры несколько критерий

Если нужно найти, сколько ячеек попадает под определенный аспект, употребляется функция СЧЕТЕСЛИ. Допустим, нам нужно осознать, сколько продавцов работает в данной для нас организации:

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

Интересно почитать: Excel функция совпад

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

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

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

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

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

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

Для наглядности я перенес данную таблицу на один лист с начальными данными.

Синтаксис функции СУММЕСЛИМН:

СУММЕСЛИМН( диапазон_суммирования ; диапазон_условий1 ; условия1 ;;. ) диапазон_суммирования — В нашем случае нам нужно просуммировать количество присоединенных услуг, потому это столбец Количество и спектр Е2:E646

Дальше указываются условия по которым нужно просуммировать услуги. У нас три условия:

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

диапазон_условий1 — 1-ое условие у нас сотрудники и спектр критерий это столбец с именами ФИО служащих A2:A646

условия1 — это сам сотрудник, потому что мы начинаем прописывать формулу напротив сотрудника Апанасенко Е.П то и условия1 у нас будет ссылка на его ячейку G3

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

Продолжим, последующая условие это услуга

диапазон_условий2 — это столбец с услугами D2:D646

условия2 — это ссылка на услугу 1, другими словами H2

Ах так обязана смотреться наша формула на текущий момент:

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2

Добавляем третье условие по городкам

диапазон_условий3 — спектр критерий по городкам это столбец «Город клиента» и спектр B2:B646

условия3 — это ссылка на город в раскрывающемся перечне G1

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

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2; B2:B646; G1 )

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

Во-1-х все спектры критерий у нас не двигаются и постоянны потому закрепим их с помощью знака бакса (выделить данный спектр в формуле и надавить кнопку F4):

Спектр суммирования у нас так же неизменный E2:E646 → $E$2:$E$646

Так же условия3 по городку G1 y нас постоянно находится лишь в ячейке G1 и не должен сдвигаться при протягивании, потому так же фиксируем данную ячейку

Услуги (условия2) при протягивании на право должны изменяться по столбцам, а вот строчка при протягивании вниз не обязана изменяться, потому фиксируем лишь строчку

Ссылка на фамилии напротив обязана изменяться при протягивании вниз, но не обязана изменяться при протягивании формулы на право, потому фиксируем лишь столбец

Итоговая формула будет смотреться последующим образом

=СУММЕСЛИМН( $E$2:$E$646 ; $A$2:$A$646 ; $G3 ; $D$2:$D$646 ; H$2; $B$2:$B$646 ;$ G$1 )

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

Примеры использования

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

Вид

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

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

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

C3:C12 – тип одежки;
«одежка» – аспект;
F3:F12 – интервал суммирования.

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

С несколькими условиями

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

F3:F12 – спектр суммирования;
«одежка» и 50 – аспекты;
C3:C12 и E3:E12 – спектры типов продукта и цены единицы соответственно.

Интересно почитать: Excel функция строка

С динамическим условием

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

Для вставки новейшей строчки необходимо надавить ПКМ на интересующей ячейке и избрать «Вставить» – «Строчку».

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

На этом я заканчиваю. Вы узрели главные примеры использования функции СУММЕСЛИ в Excel. Если есть какие-то советы либо вопросцы – милости прошу в комменты.

Задача3 (2 аспекта Дата)

Иной задачей быть может нахождение суммарных продаж за период (см. файл примера Лист “2 Даты” ). Используем другую начальную таблицу со столбцами Дата реализации и Размер продаж .

Формулы строятся аналогично задачке 2: = СУММЕСЛИМН(B6:B17;A6:A17;”>=”&D6;A6:A17;”

Примечание . Даты могут быть представлены в числовой форме см. статью Как Excel хранит дату и время

По мере необходимости даты могут быть введены конкретно в формулу = СУММЕСЛИМН(B6:B17;A6:A17;”>=15.01.2010″;A6:A17;”

Чтоб вывести условия отбора в текстовой строке используейте формулу =”Размер продаж за период с “&ТЕКСТ(D6;”дд.ММ.гг”)&” по “&ТЕКСТ(E6;”дд.ММ.гг”)

В крайней формуле применен Пользовательский формат .

Примеры

Чтоб употреблять эти примеры в Excel, выделите нужные данные в таблице, щелкните их правой клавишей мыши и изберите команду Копировать. На новеньком листе щелкните правой клавишей мыши ячейку A1 и в разделе Характеристики вставки изберите команду Применять форматы конечных ячеек.

=СУММЕСЛИМН(A2:A9; B2:B9; “=Я*”; C2:C9; “Артем”)

Суммирует количество товаров, наименования которых начинаются с Я и которые были проданы торговцем Артем. Подстановочный символ (*) в аргументе Условие1 ( “=Я*”) употребляется для поиска соответственных заглавий товаров в спектре ячеек, данных аргументом Диапазон_условия1 (B2:B9). Не считая того, функция делает поиск имени “Артем” в спектре ячеек, данных аргументом Диапазон_условия2 (C2:C9). Потом функция суммирует надлежащие обоим условиям значения в спектре ячеек, данном аргументом Диапазон_суммирования (A2:A9). Итог — 20.

=СУММЕСЛИМН(A2:A9; B2:B9; “Бананы”; C2:C9; “Артем”)

Суммирует количество товаров, которые не являются бананами и которые были проданы торговцем по имени Артем. При помощи оператора в аргументе Условие1 из поиска исключаются бананы ( “Бананы”). Не считая того, функция делает поиск имени “Артем” в спектре ячеек, данных аргументом Диапазон_условия2 (C2:C9). Потом функция суммирует надлежащие обоим условиям значения в спектре ячеек, данном аргументом Диапазон_суммирования (A2:A9). Итог — 30.

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

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

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

Начните запись функции с ее обозначения буквально так же, как это уже было показано выше.

Сначала введите спектр надписей, поставьте ; и задайте условие. Тогда это выражение в синтаксическом формате обретет приблизительно таковой вид: A2:A25;«Сентябрь»;.

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

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

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

Пример функции суммесли для суммирования в excel по условию

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому аспекту

Функция СУММЕСЛИ (SUMIF) в Excel похожа на СУММ (SUM), которую мы лишь что разбирали, так как она тоже суммирует значения. Разница только в том, что СУММЕСЛИ суммирует лишь те значения, которые удовлетворяют данному Вами аспекту. К примеру, простая формула с СУММЕСЛИ:

– суммирует все значения ячеек в спектре A2:A10, которые больше 10.

Весьма просто, правда? А сейчас давайте разглядим незначительно наиболее непростой пример. Представим, что у нас есть таблица, в которой перечислены имена продавцов и их номера ID (Lookup table). Не считая этого, есть ещё одна таблица, в которой те же ID соединены с данными о продажах (Main table). Наша задачка – отыскать сумму продаж для данного торговца. Тут есть 2 отягчающих происшествия:

Основная таблица (Main table) содержит огромное количество записей для 1-го ID в случайном порядке.
Вы не сможете добавить столбец с именами продавцов к главный таблице.

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

Перед тем, как мы начнём, разрешите напомнить Для вас синтаксис функции СУММЕСЛИ (SUMIF):

range (спектр) – аргумент гласит сам за себя. Это просто спектр ячеек, которые Вы желаете оценить данным аспектом.
criteria (аспект) – условие, которое гласит формуле, какие значения суммировать. Быть может числом, ссылкой на ячейку, выражением либо иной функцией Excel.
sum_range (диапазон_суммирования) – необязательный, но весьма принципиальный для нас аргумент. Он описывает спектр связанных ячеек, которые будут суммироваться. Если он не указан, Excel суммирует значения ячеек, в первом аргументе функции.

Интересно почитать: Сложные функции в excel

Собрав все воедино, давайте определим 3-ий аргумент для нашей функции СУММЕСЛИ. Как Вы помните, мы желаем суммировать все реализации, совершённые определённым торговцем, чьё имя задано в ячейке F2 (смотрите набросок, приведённый выше).

range (спектр) – потому что мы отыскиваем по ID торговца, значениями этого аргумента будут значения в столбце B главный таблицы (Main table). Можно задать спектр B:B (весь столбец) либо, преобразовав данные в таблицу, употреблять имя столбца Main_table.

criteria (аспект) – потому что имена продавцов записаны в просматриваемой таблице (Lookup table), используем функцию ВПР для поиска ID, соответственного данному торговцу. Имя записано в ячейке F2, потому для поиска используем формулу:

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

Всё, что Для вас осталось создать, это соединить части в одно целое, и формула СУММЕСЛИ+ВПР будет готова:

Примеры использования

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

Вид

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

C3:C12 – тип одежки;
«одежка» – аспект;
F3:F12 – интервал суммирования.

С несколькими условиями

F3:F12 – спектр суммирования;
«одежка» и 50 – аспекты;
C3:C12 и E3:E12 – спектры типов продукта и цены единицы соответственно.

Внимание! В функции СУММЕСЛИМН аргумент спектра суммирования стоит в начале формулы. Будьте внимательны!

С динамическим условием

Примеры использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ целенаправлено употреблять в вариантах, когда таблица содержит огромное количество данных, которые могут быть отфильтрованы по одному либо нескольким аспектам. При всем этом в итоге внедрения фильтров будет отображена лишь часть таблицы, данные в которой соответствуют установленному аспекту. Но операции с внедрением обыденных функций, таковых как СУММ, СРЗНАЧ и др. будут выполняться над всей изначальной таблицей (другими словами с учетом укрытых строк). Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает лишь с отфильтрованными данными.

Суммирование лишь видимых ячеек в фильтре Excel

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

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

Используем фильтр для отбора данных, которые относятся к гитарам марки Ibanez. Для этого выделим всю таблицу либо просто перейдите курсором на всякую ячейку таблицы и воспользуйтесь инвентарем «ДАННЫЕ»-«Фильтр». Сейчас начальная таблица имеет последующий вид:

Нажмем на раскрывающийся перечень в столбце B («Марка продукта») и установим флаг лишь напротив наименования «Ibanez»:

Опосля нажатия на клавишу «ОК» таблица воспримет последующий вид:

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

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

9 – числовое значение, соответственное использованию функции СУММ для получения промежных итогов;
C4:C20 – спектр ячеек, содержащих данные о цены гитар (при всем этом все остальные гитары, не считая марки Ibanez, в расчете не учитываются).

Аналогично выполним расчет для количества проданных гитар и общей выручки («Сумма). В итоге получим: