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

СУММЕСЛИ в «Эксель»: описание и пример использования функции

Работа в почти всех проф областях на нынешний денек не обходится без использования программки «Эксель». Эта программка, как хоть какое орудие труда, становится полезной лишь в опытных руках. Для удачной деятельности в сфере экономики, финансирования и почти всех остальных нужны базисные познания по Excel. На обширных просторах веба представители профессий, относящихся к вышеуказанным областям, сформировали перечень функций и способностей, остро нужных для решения проф задач. В этот перечень, кроме умения работы со сводными таблицами, вошли функции:

СУММЕСЛИ.
ЕСЛИ.
СУММПРОИЗВ.
ВПР.
СУММЕСЛИМН.

Как следует, функция СУММЕСЛИ в «Эксель» является одной из самых нужных.

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

Функция СУММЕСЛИ: диалоговое окно аргументов функции

Если при анализе таблицы данных в «Эксель» задачка стоит в необходимости суммирования характеристик, которые соответствуют определенным условиям, означает, настало время внедрения функции СУММЕСЛИ. Курсор мыши ставится в ячейку, куда необходимо вывести итог. Дальше или функция «Эксель» СУММЕСЛИ выбирается из ленты (Формула – математические – СУММЕСЛИ), или вводится через жаркую кнопку =, которая значит готовность ввести в ячейку формулу либо функцию. Если опосля знака = начать вводить 1-ые буковкы наименования функции, «Эксель» сам выдаст несколько пригодных, остается лишь избрать подходящую. Бывалые юзеры прописывают формулу СУММЕСЛИ в «Эксель» прямо в строке формул, вписывая аргументы через точку с запятой. Новенькому же лучше пользоваться значком fx, который находится слева от строчки формул. Возникает окно «Аргументы функции». Грамотное наполнение всех окон – залог удачной работы с функцией СУММЕСЛИ в «Эксель».

Обычно три поля:

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

Могут заполняться 2 поля – спектр и аспект, такое происходит, если 1-ый совпадает с спектром суммирования. В диалоговом окне аргументы функции, в самой нижней строке показывается получившееся значение. Если с спектрами все просто: необходимо просто обозначить область анализируемых ячеек, то для наполнения поля «Аспект» необходимо знать некие тонкости и индивидуальности программки.

Аспекты для функции СУММЕСЛИ в «Эксель»

Аспектами для функции могут служить:

Логические выражения.
Текст.
Четкие даты.
Временной интервал.

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

Логическое значение в качестве аспекта

Самый нередкий вариант использования функции СУММЕСЛИ в «Эксель» – это внедрение логического значения (больше, меньше, равно) в качестве условия. Для вышеуказанной таблицы сумма заказов, равных 2000 рублей, опишется так.

Как видно из примера, спектр и спектр суммирования совпадают, заполняется лишь 1-ая строчка. Условие прописывается в кавычках (это справедливо постоянно для этого поля!). Сходу видно вычисленное значение: 4000. Возвращаемся к исходной таблице: вправду, находятся два заказа в сумме 2000 рублей. Совместно они составляют 4000 рублей. Не используя диалоговое окно, можно записать в строке формул: =СУММЕСЛИ(E2:E15;»=2000″), итог будет этим же.

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

«>2000» – функция посчитает сумму всех заказов больше 2000 рублей.
«<2000» – возвратится сумма заказов меньше 2000 рублей
«>=2000» – возвратится сумма заказов которые больше и равны 2000 рублей.
«<=2000» – сумма заказов меньше и равных 2000 рублям.

Текст в качестве аспекта

Пример СУММЕСЛИ в «Эксель» текстовым аспектом: подсчитать, на какую сумму продал заказов менеджер Иванов И. И. Так смотрится окно аргументов в этом случае.

Функция возвратила значение 13000 рублей. На эту сумму продал продуктов менеджер Иванов И. И.

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

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

В поле аспект будет запись «*миксер*».

Задать условие с текстом, которое будет иметь значение «не равно», можно композицией символов <>. К примеру, для подсчета суммы заказов не от реализации миксеров, окно аргументов функции будет иметь последующий вид.

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

Дата и спектр дат в качестве аспекта

Если в функции СУММЕСЛИ в «Эксель» в качестве аспекта для условия обязана употребляться определенная дата, то к оформлению поля используются те же аспекты, что и в случае с числами и с текстом. Этим же знакам =,>,<,<=,>=,<>, соответствуют те же значения, лишь применимо это к датам. К примеру, в начальной таблице нужно суммировать заказы за 1-ый квартал 2018 года. В качестве спектра выбирается дата реализации, аспект, основываясь на задании, смотрится как «»>=31.03.2018″, в итоге получится последующее.

Означает, 19600 рублей – размер продаж по нареченной номенклатуре за 1-ый квартал.

Ссылка в поле аспекта

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

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

Аналогично ссылки можно вставить в хоть какое выражение в поле «Аспект» в аргументах функции СУММЕСЛИ в «Эксель».

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

СУММЕСЛИ в Excel

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

В примере показано, что ячейка G5 содержит эту формулу:

Эта формула суммирует суммы в столбце D, когда значение в столбце C начинается с «футболка». Направьте внимание, что СУММЕСЛИ нечувствительна к регистру.

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

Эти шаблоны разрешают создавать такие аспекты, как «начинается с», «кончается на», «содержит 3 знака» и так дальше.

Чтоб соответствовали все предметы, которые начинаются на «футболка», необходимы аспекты «футболка*». Направьте внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).

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

Направьте внимание, что спектр суммирования постоянно стоит первым в функции СУММЕСЛИМН.

Сумма значений меж определенными числами

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

В показанном примере, ячейка F7 содержит эту формулу:

Эта формула суммирует суммы в столбце D, когда они больше 500 и меньше 1000.

Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы сможете употреблять их, как для вас нравится в ваших аспектах.

В этом случае, мы желаем просуммировать значения в столбце D, которые соответствуют двум аспектам. Потому спектр суммы — «сумма» (Д4:Д11), а потом два ряда/пары критериев:

С этими аспектами, функция СУММЕСЛИМН суммирует все суммы больше 500 и меньше 1000.

Отметим, что оба оператора (>, <) и пороговых значения заключены в двойные кавычки («»).

Если вы желаете включить пороговое значение чисел в сумме (включая суммы, которые равны 500 и 1000), больше либо равно (>=) и меньше либо равно (<=), вот так:

Если вы желаете выставить порог суммы на листе, так что они могут быть просто изменены, используйте эту формулу:

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

где A1-это ссылка на нижний порог и B1-это ссылка на верхний порог

Сумма, если за год

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

В показанном примере, формула в Н6 является:

В итоге выходит общая сумма за 2011 год. При копировании вниз формулы создается общая сумма для 2012 и 2013 годов.

1-ый аргумент для функции СУММЕСЛИМН постоянно суммируемый спектр («ранг_суммы»), и аспекты поставляются в виде 1-го либо нескольких спектра/пары критериев.

Спектр суммы в этом примере — это именованный спектр, именуемый «сумма» (Е3:E2931), и аспекты поставляются в виде 2-ух пар, как при помощи именованного спектра под заглавием «Дата» (Б3:B2931).

Всякий раз функция ДАТА употребляется в аспектах для построения 2-ух допустимых дат, как при помощи такого же года:

В 1-ый денек 2011 года
В крайний денек 2011 года

Эти даты возникают в виде формулы:

В итоге, формула рассчитывает общую сумму всех сумм лишь в 2011 году.

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

Сумма, если ячейка содержит текст в иной ячейке

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

В примере показано, ячейка G7 содержит эту формулу:

Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит текст из ячейки F7.

Эти шаблоны разрешают создавать такие аспекты, как «начинается с», «кончается на», «содержит 3 знаков» и так дальше.

Так, к примеру, можно употреблять такие аспекты, как «*шапка*» для поиска текста в любом месте ячейки.

Когда Excel вычисляет этот аргумент снутри функции СУММЕСЛИ, он будет «созидать» это: «*кофточка*».

Направьте внимание, что СУММЕСЛИ нечувствительна к регистру.

Кандидатура с СУММЕСЛИМН

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

Направьте внимание, что спектр суммы постоянно стоит на первом месте в функции СУММЕСЛИМН.

Сумма, если ячейки равны

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

В показанном примере, мы подсчитываем все реализации в Западном регионе. Формула в ячейке H4 является:

Формула в ячейке Н5 является:

Обе формулы относятся к нареченной области диапазонов (С5:C14) и сумм (Е5:Е14).

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

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

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

Сумма, если ячейки не равны

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

В примере показано, ячейка Н5 содержит эту формулу:

Эта формула суммирует суммы в колонке » Е » лишь тогда, когда регион в столбце C — не «Западный».

Функция СУММЕСЛИ поддерживает все обычные операторы Excel, включая «не равно», который вводится в качестве <>.

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

Кандидатура с СУММЕСЛИМН

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

Направьте внимание, что спектр суммы постоянно стоит на первом месте в функции СУММЕСЛИМН.

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

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

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

Интересно почитать: Функция впр в excel для чайников примеры

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

СУММЕСЛИ и ее синтаксис

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

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

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

Как работает функция СУММЕСЛИ в Excel?

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

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

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

Спектром в этом случае будет являться перечень всех должностей служащих, поэтому что нам необходимо будет найти сумму заработных плат. Потому проставляем E2:E14.
Аспект выбора в нашем случае – торговец. Заключаем слово в кавычки и ставим вторым аргументом.
Спектр суммирования – это заработные платы, поэтому что нам необходимо выяснить сумму зарплат всех продавцов. Потому F2:F14.

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

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

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

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

Синтаксис с внедрением функции по нескольким аспектам

Аргументов у СУММЕСЛИМН быть может сколько угодно, но минимум – это 5.

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

И так дальше. Зависимо от количества критериев, число аргументов может возрастать в арифметической прогрессии с шагом 2. Т.е. 5, 7, 9.

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

Представим, нам необходимо подсчитать сумму заработных плат за январь всех продавцов-женщин. У нас есть два условия. Сотрудник должен быть:

торговцем;
дамой.

Означает, будем использовать команду СУММЕСЛИМН.

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

Результат: все продавцы-женщины в январе получили в сумме 51100 рублей.

СУММЕСЛИ в Excel с динамическим условием

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

У нас возникла доборная строка. Сходу обращаем внимание, что спектр критерий и суммирования автоматом расширился до 15 строчки.

Копируем данные сотрудника и вставляем их в общий список. Суммы в итоговых ячейках поменялись. Функции среагировали на возникновение в спектре еще 1-го продавца-женщины.

Аналогично можно не только лишь добавлять, да и удалять какие-либо строчки (к примеру, при увольнении сотрудника), изменять значения (поменять «январь» на «февраль» и подставить новейшие заработные платы) и т.п.