Суммесли в экселе как пользоваться
Перейти к содержимому

Суммесли в экселе как пользоваться

  • автор:

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

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

Функция СУММЕСЛИ популярна, поскольку почти в каждой таблице нужно посчитать сумму чисел в ячейках, игнорируя значения, не попадающие под основное условие. Благодаря этой формуле подсчет не становится чем-то сложным и длительным. Стандартное оформление функции выглядит как =СУММЕСЛИ(Диапазон;Критерий;Диапазон_суммирования), а «Диапазон суммирования» указывается только при условии, когда есть фактические ячейки, сложение которых выполняется при любых обстоятельствах. Если данные в «Диапазоне суммирования» отсутствуют, проверяться будут все ячейки, входящие в «Диапазон».

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

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

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

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

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

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

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

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

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

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

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

Lumpics.ru

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

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

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

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

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

СУММЕСЛИ в Excel

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

Ячейка начинается с определенного значения

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. В первый день 2011 года
  2. В последний день 2011 года

Эти даты появляются в виде формулы:

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

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

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

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

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

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

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

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

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

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

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

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

Альтернатива с СУММЕСЛИМН

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

Обратите внимание, что диапазон суммы всегда стоит на первом месте в функции СУММЕСЛИМН.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Альтернатива с СУММЕСЛИМН

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

Обратите внимание, что диапазон суммы всегда стоит на первом месте в функции СУММЕСЛИМН.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • продавцом;
  • женщиной.

Значит, будем применять команду СУММЕСЛИМН.

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

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

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

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

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

Дополнительная строчка.

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

Пример.

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

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

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

cond_sum1.png

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

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

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

cond_sum2.png

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

cond_sum3.png

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

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

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

cond_sum4.png

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

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

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

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

=(A2=»Копейка»)*(B2=»Григорьев»)

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

cond_sum5.png

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

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

=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)

cond_sum6.png

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

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *