gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 43 гостей онлайн
Диапазон таблицы Excel. Примеры функции "СУММЕСЛИМН" в Excel.

Примеры функции «СУММЕСЛИМН» в Excel.

      Рассмотрим примеры функции «СУММЕСЛИМН» в Excel. Как написать условия для различных способов выбора данных для суммирования, в т. ч. с датами. Как установить функцию «СУММЕСЛИМН» в Excel, какие можно указать условия, смотрите в статье «Суммирование ячеек в Excel по условию».
      Пример 1.
Посчитаем сумму, если в ячейке наименования товара написаны не одним словом, а несколькими. Здесь нужно вставить в условие функции специальные символы.
Таблица такая.
Нам нужно посчитать, сколько менеджер Васечкин продал яблок красных, зеленых, других покупателю Иванову.
      В ячейку Н4 вставляем функцию «СУММЕСЛИМН». В условии функции напишем "*яблоки*". По этому условию Excel будет искать все яблоки. Диалоговое окно функции заполнили так.
Формула получится такая.
=СУММЕСЛИМН(E2:E8;B2:B8;"Васечкин";A2:A8;"Иванов";C2:C8;"*яблоки*")
Получилось, что всяких яблок Васечкин продал Иванову на сумму 7897 рублей.
      Пример 2.
Нужно посчитать, сколько продано всех фруктов на букву «Я». В условиях функции «СУММЕСЛИМН» напишем так. "=я*"      Вся формула будет такая. =СУММЕСЛИМН(D2:D8;C2:C8;"=я*")
Этой формулой посчитаются все слова в столбце, которые начинаются на букву «Я».Если нужно посчитать, что продал именно Васечкин и именно Иванову, то добавим эти условия в функцию, как рассмотрели это выше.
     Но, у нас в таблице, есть строка «Красные яблоки». Они по этой формуле не посчитаются, п.ч. первое слово в ячейке начинается не с буквы «Я».
      Чтобы посчитать и эти, «красные яблоки», напишем такое условие функции.  "*я*"
Формула будет такой.  =СУММЕСЛИМН(D2:D8;C2:C8;"*я*")
Если нужно, то добавляем условия в функцию СУММЕСЛИМН по менеджерам и покупателям.
      Пример 3.
Нам нужно посчитать сумму продаж всех фруктов, кроме яблок. В условии функции напишем так. “<>яблоки” Примеры функции СУММЕСЛИМН в Excel.Но, в нашей таблице есть разные наименования – яблоки зеленые, красные яблоки. Чтобы и их исключить из суммы всех фруктов, условие в функции напишем такое.  "<>*яблоки*"
      Символы «<>» означают – не равно. О других символах, которые можно вставить в формулу и что они означают, смотрите в статье «Символы в формулах Excel».
Формула будет такая.  =СУММЕСЛИМН(D2:D8;C2:C8;"<>*яблоки*")
      Если нужно посчитать не сумму в выбранных ячейках, а количество ячеек по определенному условию, то воспользуйтесь функцией «СЧЕТЕСЛИ». Подробнее об этой функции читайте в статье «Функция «СЧЕТЕСЛИ» в Excel».
      Пример 4.
      Функция «СУММЕСЛИМН» в Excel с датами.
Нужно сложить данные из таблицы по нескольким условиям, в т. ч. по датам.
Удобно писать даты в отдельных ячейках. Так сможем посчитать данные любого периода, изменив даты в этих отдельных ячейках.
В ячейке С11 напишем дату начала периода.
В ячейке С12 напишем дату окончания периода, данные в котором мы хотим посчитать.
В диалоговом окне условия функции «СУММЕСЛИМН» по датам написали так.Получилась такая формула.
=СУММЕСЛИМН(E2:E8;B2:B8;"Васечкин";A2:A8;"Иванов";D2:D8;">="&C11;D2:D8;"<="&C12)
Здесь мы два раза указали один и тот же диапазон дат, но указали разные условия (от даты от той, что в ячейке С11 и больше, до даты, что в ячейке С12 и меньше).
Получится так.
Поменяем в формуле фамилию менеджера «Сергеева» на «Васечкин». Проверяем формулу. Получилось так.Формула работает. Можно менять даты периода в ячейках С11 и С12, т.д.
      Пример 5.
Можно в условиях функции «СУММЕСЛИМН» указать и символы «>» и «<». Например, посчитаем, на какую сумму были продажи у менеджера Васечкина, если считать только суммы продажи больше 500 рублей.
Получится такая формула.
=СУММЕСЛИМН(E2:E8;B2:B8;"Васечкин";E2:E8;">500")
Здесь последний критерий написали так – «>500».
      Пример 6.
Если посчитать в непустых ячейках, то ставим последний критерий» - «>0». Формула получится такая.
=СУММЕСЛИМН(E2:E8;B2:B8;"Васечкин";E2:E8;">0")
      Пример 7.
Нужно посчитать продажи Васечкиным по всем филиалам конкретного магазина. Таблица такая.В условиях функции напишем так - "*Монетка*".  Формула получится такая.
=СУММЕСЛИМН(D2:D8;B2:B8;"Васечкин";A2:A8;"*Монетка*")
      Последним условием мы написали «*Монетка*». Слово «Монетка» заключили в звездочки. Это значит, что Excel будет искать все слова «Монетка» с разными номерами и буквами, разные филиалы магазина «Монетка». Смотрите статью «Подстановочные знаки в Excel» тут.
      Есть ещё одна функция в Excel, которая считает по условию. Эта функция проще. Читайте о ней в статье "Функция "СУММЕСЛИ" в Excel".
      В Excel есть еще одна функция, которая сначала умножает ячейки в столбцах, затем складывает результаты. Можно настроить функцию, чтобы считала по условию. Смотрите об этой функции в статье ""СУММПРОИЗВ" в Excel".