Функция «суммеслимн» в Excel: примеры использования

При составлении отчетной документации время от времени нужно пользоваться суммированием ячеек, отвечающих определенным условиям. Так, функция «суммеслимн» в Excel (примеры будут приведены) непревзойденно совладевает с поставленной задачей. Если в операторе «суммесли» вероятен отбор по одному условию, то вышеназванный вариант дозволяет использовать несколько диапазонов.

Математические функции в Excel

В данной группы содержится порядка 80 функций. Тут можно отыскать операторы, способные просчитать значения электрической таблицы хоть какого формата. В Excel «суммесли» нередко встречается, а набор тригонометрических функций подступает для определенного круга юзеров. В чем все-таки выражается сущность арифметического оператора, у которого есть аналог в крайних версиях программного продукта «суммеслимн»? Его задачка суммировать значения, которые попадают под определенные аспекты.

Установка OpenVPN и настройка сервера на Windows и Linux Для вас будет любопытно: Установка OpenVPN и настройка сервера на Windows и Linux

В основном математические функции призваны заавтоматизировать работу юзера. Если появляются вопросцы по применению оператора, вызывают справку. Создать это можно в окне «Аргументы функции» нажатием на ссылку «Короткая справка» либо через F1.

Для ввода формулы нужно надавить поначалу символ «=». В неприятном случае программный продукт распознает информацию как текст либо выдает ошибку. Формулы юзер прописывает вручную, делая упор на свои познания и способности, через панель инструментов либо через «Мастер подстановки».

Описание функции

Что такое секвенсор: общее представление и азы для начинающих музыкантов Для вас будет любопытно: Что такое секвенсор: общее представление и азы для начинающих музыкантов

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

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

Синтаксис функции смотрится последующим образом: =суммеслимн(диапазон_суммирования; [диапазон_условия1; условие1]; [диапазон_условия2; условие2]; …)

функция суммеслимн в excel примеры

Под спектром суммирования понимается массив, ячейки которого будут складываться, если они удовлетворяют следующим условиям. Иной блок аргументов в синтаксисе – диапазон_условия1; условие1. Они разрешают избрать в определенном массиве по первому фактору нужные ячейки, которые в предстоящем суммируются в границах начального интервала. Доп аспектами выступают следующие диапазон_условия10; условие10.

Индивидуальности функции

  • Если ассоциировать «суммесли» и «суммеслимн», то размещение аргументов в синтаксисе обозначенных операторов различается. У первой функции спектр суммирования находится на третьей позиции, у 2-ой – на первой.
  • Если некорректно ввести данные для «суммеслимн» в Excel, примеры выявляют ошибку. Дело в том, что размерность всех строк и столбцов диапазонов схожа для данного оператора. «Суммесли» допускает различное количество ячеек в интервале суммирования и условия.
  • В случае перебора первого аргумента обнаруживаются пустые либо текстовые ячейки, они игнорируются.
  • При прописывании аспекта употребляют символ «*». Он обозначает, что находить необходимо содержащийся фрагмент и не стопроцентное совпадение с условием.
  • Функция «суммеслимн» в Excel (примеры это подтверждают) допускают длину строчки до 255 знаков.
  • Ячейки, обозначенные в спектре суммирования, только тогда складываются, когда удовлетворяют всем поставленным условиям. По другому говоря, производится еще логическая функция «И».

Пример 1

Для закрепления материала юзеру необходимо самому решать подобные задачки по «суммеслимн» в Excel. Примеры использования представлены ниже.

Даны 5 столбцов, где указаны:

  • дата;
  • цвет;
  • штат;
  • количество;
  • стоимость.

функция суммеслимн в excel примеры

Производится поиск по последующим аспектам:

  • спектр суммирования: F5:F11;
  • интервал первого условия: поиск по цвету;
  • исходный аспект отбора: содержится слово red;
  • спектр второго условия: поиск по штату;
  • иной аспект: содержится аббревиатура TX.

Пример 2

Даны 4 столбца, в которых указаны:

  • А – категория продукта;
  • В – определенные продукты;
  • С – русский город;
  • D – размер продаж.

1-ое условие в примере 1 — отобрать все ячейки, содержащие слово «овощи». 2-ой аспект — отыскать клеточки, отвечающие за город «Москва». Во 2-м примере 1-ое условие содержит поиск по фруктам. 2-ой аспект относится к Казани.

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

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

Во 2-м операторе цифрами помечены аргументы «суммеслимн»:

  • 1 – спектр суммирования (размер продаж);
  • 2 – интервал первого условия (поиск по группы);
  • 3 – аспект 1 (поиск слова «фрукты»);
  • 4 – спектр второго условия (нахождение по городку);
  • 5 – аспект 2 (поиск слова «Казань»).

Пример 3

Даны 2 столбца. В первом указаны имена служащих, во 2-м – размер продаж для всякого из их. Нужно сделать 3 функции «суммеслимн» в Excel. Примеры, как это создать, показаны на изображении.

Пример 4

Даны 3 столбца, в которых указана дата, тип расходов, сумма на определенный вид растраты. Нужно создать подборку по двум условиям:

формула суммеслимн в excel примеры

В качестве самостоятельного задания юзер может ввести условие по дате за определенный период: в месяц либо недельку. Чтоб это создать, нужно в кавычках указать период, например, «>01.01.2017». Вторым условием выступает: « Приглянулась статья? Поделись с друзьями:

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

Функция суммеслимн в excel примеры

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

Синтаксис:

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

Аргументы:

  • sum_range : Нужные. Ячейки либо спектр ячеек, которые вы желаете суммировать. Суммируются лишь ячейки с числами, пустые ячейки и текстовые значения игнорируются.
  • criteria_range1 : Нужные. 1-ый спектр, к которому вы желаете применить критерий1.
  • criteria1 : Нужные. 1-ое условие, которое будет употребляться в диапазоне1.
  • criteria_range2, criteria2, … : Необязательный. Остальные доп спектры и связанные с ними аспекты.
  • 1. Функция СУММЕСЛИМН может обрабатывать до 127 пар критериев_диапазона и аргументов критериев.
  • 2. Аргумент диапазон_критерия должен содержать то же количество строк и столбцов, что и аргумент диапазон_суммы.
  • 3. Типом аспекта быть может: число, логическое выражение, ссылка на ячейку, текст, дата либо иная функция Excel. Если аспект является текстовой строчкой либо выражением, оно обязано быть заключено в двойные кавычки.
  • 4. В функции СУММЕСЛИМН регистр не учитывается. К примеру, текстовые строчки «excel» и «EXCEL» будут считаться равными.
  • 5. Функция СУММЕСЛИМН работает с логикой И, это значит, что сумма ячеек в спектре сумм суммируется, лишь если она соответствует всем обозначенным аспектам.

Возвратить:

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

Примеры:

Пример 1: базисное внедрение функции СУММЕСЛИМН

К примеру, у меня есть таблица со столбцами «Продукт», «Имя», «Реализации» и «Стоимость», как показано на снимке экрана ниже. Сейчас я желаю выяснить сумму общей цены, для которой продукт является KTE, а размер продаж превосходит 200.

Вставьте последующую формулу в пустую ячейку:

  • Советы: В приведенной выше формуле:
  • D2: D12: спектр ячеек, которые вы желаете суммировать.
  • A2: A12, «КТЕ»: 1-ый диапазон_критерия и аспекты, по которым вы желаете суммировать значения.
  • C2: C12, «> 200»: 2-ой диапазон_критерия и аспекты, по которым вы желаете суммировать значения.

А потом нажмите Enter ключ, чтоб получить подходящий для вас итог. Смотрите снимок экрана:

Внимание: Вы также сможете употреблять ссылку на ячейку, чтоб поменять условие в приведенной выше формуле последующим образом: =SUMIFS(D2:D12, A2:A12,F2, C2:C12, G2) .

Пример 2: функция СУММЕСЛИМН с датами в Excel

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

Используйте приведенную ниже формулу в пустую ячейку:

  • Советы: В приведенной выше формуле:
  • C2: C12: спектр ячеек, которые вы желаете суммировать.
  • A2: A12, «> = 4 января 1 г.»: 1-ый диапазон_критерия и аспекты, по которым вы желаете суммировать значения.
  • A2: A12, «<= 5»: 2-ой диапазон_критерия и аспекты, по которым вы желаете суммировать значения.

А потом нажмите Enter key, общая стоимость меж 2-мя обозначенными датами была рассчитана, см. скриншот:

Внимание: Вы также сможете употреблять ссылку на ячейку, чтоб поменять условие в приведенной выше формуле последующим образом: = СУММЕСЛИМН (C2: C12, A2: A12, «> =» & E2, A2: A12, «< margin-left: 13px;»>

Пример 3: функция СУММЕСЛИМН с пустыми и непустыми ячейками

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

Для решения данной для нас задачки следует употреблять последующую формулу:

Пример 4: функция СУММЕСЛИМН с несколькими аспектами и логикой ИЛИ

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

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

Формула1: СУММЕСЛИМН () + СУММЕСЛИМН () + СУММЕСЛИМН () +…

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

А потом нажмите Enter ключ для получения правильного результата, как для вас необходимо:

Формула 2: СУММ (СУММЕСЛИМН (<>))

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

Введите либо скопируйте последующую формулу в пустую ячейку:

Потом нажмите Shift + Ctrl + Enter совместно, чтоб получить итог, как показано на снимке экрана ниже:

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

Наилучшие инструменты для работы в кабинете

Kutools for Excel — поможет для вас выделиться из толпы

Желаете стремительно и идеально делать свою ежедневную работу? Kutools for Excel дает массивные расширенные функции 300 (объединение книжек, сумма по цвету, разделение содержимого ячеек, дата преобразования и так дальше . ) и экономия 80% времени вам.

Функция СУММЕСЛИ – Служба саппорта Office

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

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

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

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

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

Синтаксис

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

Аргументы функции СУММЕСЛИ описаны ниже.

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

Условие .Неотклонимый аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста либо функции, определяющее, какие ячейки нужно суммировать. Можно употреблять поддиавные знаки: вопросии (?), которые соответствуют хоть какому отдельному символу, звездочка (*) — хоть какой последовательности знаков. Если требуется отыскать конкретно вопросительный символ (либо звездочку), нужно поставить перед ним символ “тильда” (

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

Диапазон_суммирования .Необязательный аргумент. Ячейки, значения из которых суммируются, если они различаются от ячеек, обозначенных в качестве спектра. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, обозначенные в аргументе спектр (те же ячейки, к которым применяется условие).

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

Спектр

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

Фактические суммные ячейки

Общие сведения о функции СУММЕСЛИ

Как видно из наименования, эта функция производит суммирование значений, соответственных определенному условию. Она дозволяет поменять сложную формулу одной функцией.

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

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

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

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

СУММЕСЛИ(A1:A10; “Оплачено”; B1:B10)

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

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

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

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

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

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

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

Примеры

Пример 1

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

Стоимость имущества

Комиссионные

Сумма комиссионных за имущество стоимостью больше 1 600 000 ₽.

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

Сумма по имуществу стоимостью больше 1 600 000 ₽.

Сумма комиссионных за имущество стоимостью 3 000 000 ₽.

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

Пример 2

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

Размер продаж

Размер продаж всех товаров группы “Фрукты”.

Размер продаж всех товаров группы “Овощи”.

Размер продаж всех товаров, наименования которых завершаются на “ы” (“Помидоры” и “Апельсины”).

Размер продаж всех товаров, категория для которых не указана.

К началу странички

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

Представим у нас есть данные по продажам товаров в магазине:

Таблица с данными по продажам

Наша задачка состоит в расчете суммы продаж раздельно по овощам и по фруктам (аспект №1 в нашем примере).
В качестве спектра избираем ячейки A2:A13 (категория продукта), задаем условие суммирования F3 («Овощи») и избираем в спектр суммирования ячейки D2:D13 (размер продаж в рублях):

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

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

Также посчитаем размер продаж товаров в рублях выше определенного количества килограмм (аспект №2 в нашем примере).
В качестве спектра избираем ячейки C2:C13 (размер продаж в кг), задаем условие суммирования F7 (“>500”) и избираем в спектр суммирования ячейки D2:D13 (размер продаж в рублях):

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

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

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

Фортуны для вас и до скорых встреч на страничках блога Tutorexcel.ru!

Доп сведения

Вы постоянно сможете задать вопросец спецу Excel Tech Community, попросить помощи в обществе Answers community, также предложить новейшую функцию либо улучшение на сайте Excel User Voice.

См. также:

СУММКВ: Возвращает сумму квадратов ряда чисел либо содержимого ряда ячеек.

СУММ: Находит сумму ряда чисел либо содержимого ряда ячеек.

РЯД.СУММ: Находит сумму первых членов степенного ряда, вычисленную по последующей формуле: a1xn + a2x(n+m) + … + aix(n+(i-1)m), где i – это число коэффициентов в массиве a.

ЧАСТНОЕ: Разделяет одно число на другое без остатка.

ПРОИЗВЕД: Находит произведение ряда чисел.

MULTIPLY: Находит произведение 2-ух чисел. Делает ту же функцию, что и оператор “*”.

MINUS: Находит разность 2-ух чисел. Делает ту же функцию, что и оператор “-“.

БДСУММ: Вычисляет сумму значений в табличном массиве, используя способ, аналогичный SQL-запросу.

DIVIDE: Разделяет одно число на другое. Делает ту же функцию, что и оператор “/”.

СЧЁТЕСЛИ: Подсчитывает количество ячеек, соответственных данному условию и расположенных в обозначенном спектре.

ADD: Находит сумму 2-ух чисел. Делает ту же функцию, что и оператор “+”.

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

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

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

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

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

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

Следом идет 2-ой аргумент — совпадение по наименованию в ячейке. Запись этого параметра уже рассматривалась выше и снова ее можно узреть на снимке экрана.

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

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

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

На сто процентов строчка данной для нас функции имеет вид =СУММЕСЛИМН(C2:C25;A2:A25;«Сентябрь»;B2:B25;«>29»), а для вас остается только подстроить ее под себя и получить верный итог. Может показаться, что объявлять любой аргумент и не запутаться в знаках трудно, но при раздельном подходе никаких заморочек появиться не обязано.

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

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