СУММЕСЛИМН в Excel

59337 20.02.2013 Скачать пример

Постановка задачи

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

image

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

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

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

image

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=БДСУММ(A1:D26;D1;F1:G2)

  • Гайдамакина Вероника Ивановна, учитель информатики

Разделы: Информатика

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

Упражнение 1. «Магазин»

Приложение 1

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

Комментарии:

I. Создайте две таблицы: с исходными данными и результатами.

  A B C D E F
1 Наименование Поставщик Количество, л Цена Стоимость
2 1 Молоко Поставщик № 1 76 43,00р.  
3 2 Кефир Поставщик № 2 40 26,00р.  
4 3 Варенец Поставщик № 3 86 34,00р.  
5 4 Молоко Поставщик № 3 45 26,00р.  
6 5 Молоко Поставщик № 4 87 24,00р.  
7 6 Варенец Поставщик № 4 34 41,00р.  
8 7 Кефир Поставщик № 4 67 32,00р.  
9 8 Кефир Поставщик № 3 45 39,00р.  
10 9 Молоко Поставщик № 2 43 35,00р.  
11 10 Молоко Поставщик № 5 32 33,00р.  

Таблица 1

  A B C D E

14

Наименование Кол-во поставок Суммарная стоимость Средняя цена

15

1 Молоко      

16

2 Кефир      

17

3 Варенец      

Таблица 2

II. Введите формулы в таблицы.

1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке

F2: =D2*E2.

Результат:

  A B C D E F
1 Наименование Поставщик Количество, л Цена Стоимость
2 1 Молоко Поставщик № 1 76 43,00р. 3 268,00р.
3 2 Кефир Поставщик № 2 40 26,00р. 1 040,00р.
4 3 Варенец Поставщик № 3 86 34,00р. 2 924,00р.
5 4 Молоко Поставщик № 3 45 26,00р. 1 170,00р.
6 5 Молоко Поставщик № 4 87 24,00р. 2 088,00р.
7 6 Варенец Поставщик № 4 34 41,00р. 1 394,00р.
8 7 Кефир Поставщик № 4 67 32,00р. 2 144,00р.
9 8 Кефир Поставщик № 3 45 39,00р. 1 755,00р.
10 9 Молоко Поставщик № 2 43 35,00р. 1 505,00р.
11 10 Молоко Поставщик № 5 32 33,00р. 1 056,00р.

Таблица 1.1

2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:

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

=СЧЁТЕСЛИ(B$2:B$11;B15),

где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

Результат:

  A B C D E
14 Наименование Кол-во поставок Суммарная стоимость Средняя цена
15 1 Молоко 5    
16 2 Кефир 3    
17 3 Варенец 2    

Таблица 2.1

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

=СУММЕСЛИ(B$2:B$11;B15;F$2:F$11),

где F2:F11 — диапазон стоимости продукта из первой таблицы.

Результат:

  A B C D E
14 Наименование Кол-во поставок Суммарная стоимость Средняя цена
15 1 Молоко 5 9 087,00р.  
16 2 Кефир 3 4 939,00р.  
17 3 Варенец 2 4 318,00р.  

Таблица 2.2

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:

=СУММЕСЛИ(B$2:B$11;B15;E$2:E$11)/C15,

где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.

Результат:

  A B C D E
14 Наименование Кол-во поставок Суммарная стоимость Средняя цена
15 1 Молоко 5 9 087,00р. 32,20р.
16 2 Кефир 3 4 939,00р. 32,33р.
17 3 Варенец 2 4 318,00р. 37,50р.

Таблица 2.3

Замечания:

Данное упражнение может быть дополнено следующими заданиями (и не только ими):

Упражнение 2. «Студенческие стипендии» [1]

Приложение 1

Составить таблицу экзаменационных оценок студентов и на ее основе определить средний балл и размер стипендии для каждого студента. Правила определения размера стипендии:

  • стипендия не назначается, если среди оценок есть хотя бы одна двойка;
  • 3,0 <</u> средний балл < 3,5 — 1000р.
  • 3,5 <</u> средний балл < 4,0 — 1200р.
  • 4,0 <</u> средний балл < 4,5 — 1500р.
  • 4,5 <</u> средний балл < 5,0 — 1800р.
  • средний балл = 5,0 — 2000р.

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

Замечания:

Формулировка данной задачи, с некоторыми изменениями, взята из учебника «Информатика: Практикум по технологии работы на компьютере» под ред. Н.В. Макаровой.

Комментарии:

  • Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:
Фамилия Предметы Средний балл Стипендия
Матем. Физика Информ. Химия История
1 Алексеев 5 3 2 3 3    
2 Гаврилов 4 4 3 3 3    
3 Зайцев 3 3 3 3 3    
4 Иванов 4 3 5 3 3    
5 Краснов 5 4 4 4 4    
6 Кузнецов 4 5 5 5 5    
7 Петров 5 5 5 5 5    
8 Сидоров 4 3 3 3 5    
9 Смирнов 4 4 4 4 4    
10 Солдатов 5 4 5 2 3    
  • Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:

=ЕСЛИ(СЧЁТЕСЛИ(C3:G3;2)>0;0;ЕСЛИ(H3<3,5;1000;ЕСЛИ(H3<4;1200; ЕСЛИ(H3<4,5;1500;ЕСЛИ(H3<5;1800;2000))))).</em>

Результат:

Фамилия Предметы Средний балл Стипендия
Матем. Физика Информ. Химия История
1 Алексеев 5 3 2 3 3 3,2 0р.
2 Гаврилов 4 4 3 3 3 3,4 1 000р.
3 Зайцев 3 3 3 3 3 3 1 000р.
4 Иванов 4 3 5 3 3 3,6 1 200р.
5 Краснов 5 4 4 4 4 4,2 1 500р.
6 Кузнецов 4 5 5 5 5 4,8 1 800р.
7 Петров 5 5 5 5 5 5 2 000р.
8 Сидоров 4 3 3 3 5 3,6 1 200р.
9 Смирнов 4 4 4 4 4 4 1 500р.
10 Солдатов 5 4 5 2 3 3,8 0р.
              Итого: 11 200р.

Упражнение 3. «Завод железобетонных изделий»

Приложение 1

Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.

I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.

II. Ввод формул.

1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны. 2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:              a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.              b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ. 3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.

Упражнение 4. «Прайс-лист»

Приложение 1

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

Литература

  1. Информатика: Практикум по технологии работы на компьютере/ Под ред. Н.В. Макаровой. – 3-е изд., переработ. – М.: Финансы и статистика, 2003. – 256 с.: ил.

7.07.2011

Если мы хотим быстро, не задумываясь, без ошибок рассчитать сезонность для длинных временных рядов, то рекомендую использовать 2 формулы Excel — «СУММЕСЛИ» и «СЧЕТЕСЛИ»

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

Рассмотрим использование формул «СУММЕСЛИ» и «СЧЕТЕСЛИ» на примере расчета прогноза с помощью модели «Скользящей средней к 3-м месяцам с аддитивной сезонностью».

Рассчитаем:

  1. Скользящую среднюю к 3-м месяцам;
  2. Разность между значениями ряда и средними значениями к 3-м месяцам (пункт 1);
  3. Усредним разность ряда и средней для каждого месяца получим сезонность в абсолютной величание — для этого воспользуемся функциями СУММЕСЛИ и СЧЕТЕСЛИ;
  4. Продлим значения ряда с помощью скользящей средней к 3-м месяцам и скорректируем её аддитивной сезонностью.

1. Скользящую среднюю к 3-м месяцам для каждого момента времени во временном ряду начиная с 4-го периода:

Скачать файл с примером в Excel

3. Усредним разность ряда и средней (пункт 2) для 12 месяцев — получим сезонность в абсолютной величание — аддитивную сезонность.

Для этого вначале выделим номера месяцев с помощью функции Excel =месяц(дата).

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

Получаем ряд с пронумерованными месяцами от 1 до 12 для каждого момента времени t:

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

Для этого в формулу СУММЕСЛИ передаем следующие значения:

=СУММЕСЛИ(

  • диапазон — $G$8:$AY$8 — диапазон с номера месяцев за весь период;
  • критерий — D9 — номер месяца для которого суммируем отклонения и рассчитываем сезоность;
  • диапазон для суммирования — $G$7:$AY$7 — диапазон с отклонениями рассчитанными в пункте 2).

Фиксируем ссылки на диапазон с номерами месяцев и диапазон с отклонениями (как зафиксировать ссылки описано в статье «Как зафиксировать ссылку в Excel?»), и протягиваем формулу для 12 месяцев от 1 до 12.

Получаем сумму отклонений для каждого месяца.

Далее с помощью формулы Excel =СЧЁТЕСЛИ() считаем количество месяцев во временном ряду, на которое мы разделим сумму отклонений и получим среднее абсолютное отклонение для каждого месяца — аддитивную сезонность.

И так передаем в формулу =СЧЁТЕСЛИ(

  • диапазон — $G$8:$AY$8 — фиксируем ссылку — номера месяцев во временном ряду;
  • критерий — D9 — номера месяцев для сезонности, кол-во которых нам надо рассчитать во временном ряду для того чтобы рассчитать среднее отклонение.

Скачать файл с примером в Excel

Протягиваем формулу Excel =СЧЁТЕСЛИ от 1 до 12 и получаем ряд с количеством месяцев для каждого месяца сезонности:

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

Данный расчет мы проделали в 3 шага

  1. Просуммировали отклонения для каждого месяца сезонности с помощью СУММЕСЛИ,
  2. Рассчитали количество периодов для каждого месяца сезонности с помощью СЧЁТЕСЛИ,
  3. И разделили пункт 1 на пункт 2, т.е. сумму отклонений на количество периодов, т.е. нашли среднее отклонение для каждого месяца.

Но эти 3 шага можно совместить в один и сразу получить 12 значений аддитивной сезонности. Для этого СУММЕСЛИ разделим на СЧЁТЕСЛИ, как на картинке:

Формула получается следующего вида:

=СУММЕСЛИ($G$8:$AY$8;D9;$G$7:$AY$7)/СЧЁТЕСЛИ($G$8:$AY$8;D9)

Вводим формулу, протягиваем на 12 месяцев, получаем сезонность:

Еще раз её разберем =СУММЕСЛИ($G$8:$AY$8;D9;$G$7:$AY$7)/СЧЁТЕСЛИ($G$8:$AY$8;D9)

СУММЕСЛИ($G$8:$AY$8 (зафиксированный диапазон с номерами месяцев);D9 (номер месяца, для которого мы рассчитываем сезонность); $G$7:$AY$7 (зафиксированный диапазон со значениями разности между рядом и средней — пункт 2))

СЧЁТЕСЛИ($G$8:$AY$8 (зафиксированный диапазон с номерами месяцев);D9 (номер месяца, для которого мы рассчитываем сезонность))

Теперь давайте рассчитаем прогноз.

Для этого продлим значения ряда с помощью скользящей средней к 3-м месяцам и скорректируем её аддитивной сезонностью.

Продлим скользящую среднею к 3-м месяцам в будущее на 12 месяцев:

Скорректируем скользящую сезонностью.

Для этого к скользящей средней прибавим аддитивную сезонность. Сезонность для каждого месяца подтянем с помощью функции Excel ГПР.

Подробнее об этом читайте в статье «ГПР в Excel»

Прогноз = средние продажи за последние 3 месяца + сезонность:

=СРЗНАЧ(AW4:AY4 (средние продажи за 3 последних месяца))+ГПР(AZ3 (искомый номер месяца$D$9:$O$13 (зафиксированная ссылка на таблицу с сезонностью);5 (номер строки с сезонностью);0)

Скачать файл с примером в Excel

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

Для того чтобы этой ошибки сознательно избежать и заставить думать машину используйте формулы Excel =СУММЕСЛИ и СЧЁТЕСЛИ. Комбинация данных формул поможет вам рассчитать среднее значение по заданному критерию.

А если хотите на 100% избежать ошибок в расчетах прогнозов, да еще и автоматически подобрать нужную модель для каждого временного ряда работайте с Forecast4AC PRO.

Forecast4AC PRO сэкономит вам кучу времени позволит делать более точные прогнозы.

Если есть вопросы или комментарии, пожалуйста, пишите в форме ниже!

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

  • Novo Forecast Lite — автоматический расчет прогноза в Excel.
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Суммировать в программе 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 строки.

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

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

Сейчас мы с вами рассмотрим еще одну очень часто используемую функцию Excel — СУММЕСЛИ.

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

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

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

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

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

диапазон – это диапазон сравнения, то есть тот массив, в котором будут сравниваться с критериями. В нашем пример это A5:A504, чтобы при протягивании формулы наш диапазон не сдвигался, сделаем его абсолютным, поставив знак $. Получаем – $A$5:$A$504

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

диапазон_суммирования – это тот диапазон, который необходимо просуммировать, при совпадении критериев. В нашем примере это диапазон: B5:B504, который мы также для удобства превратим в абсолютный $B$5:$B$504

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

Для решения задачи с данным примером в ячейку F5 впишем следующую формулу:

=СУММЕСЛИ($A$5:$A$504;E5;$B$5:$B$504)

Логика работы функции СУММЕСЛИ следующая: в диапазоне $A$5:$A$504 ищется критерий E5 (Санкт-Петербург), если Санкт-Петербург находится, то суммируется кол-во заказов из этой строчки то есть из диапазона $B$5:$B$504

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

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

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

Скачать пример

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

Спасибо за внимание.

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

Функция СУММ – простое суммирование ячеек в Excel

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

Но мы также можем воспользоваться функцией СУММ и записать формулу следующим образом:

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

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

В следующем примере функция СУММ складывает весь столбец А, а это 1048576 значений:

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

Функция СУММ может принимать до 255 аргументов и суммировать сразу несколько несмежных диапазонов или ячеек:

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

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

Функция СУММ достаточно универсальна и позволяет использовать в качестве своих аргументов не только ссылки на ячейки и диапазоны, но и различные математические операторы и даже другие функции Excel:

Если интересно узнать, как посчитать накопительную сумму в Excel, обратитесь к этому уроку.

СУММЕСЛИ — условная сумма в Excel

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

Например, следующая формула суммируем только положительные числа диапазона A1:A10. Обратите внимание, что условие заключено в двойные кавычки.

В качестве условия можно использовать значение ячейки. В этом случае, поменяв условие, изменится и результат:

Меняем условие, меняется и результат:

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

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

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

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

Нажав Enter получим результат:

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

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

Оцените качество статьи. Нам важно ваше мнение:

Функция СУММЕСЛИ в Excel. СЧЁТЕСЛИ

Categories: Формулы

Функция СУММЕСЛИ. Первый из трех китов Excel, можно сказать. Начинаю обзор главных инструментов любимой нами программы от Microsoft. В начале хочу рассказать о моей  часто используемой функции («формуле»): это функция СУММЕСЛИ. Если Вы не представляете, что это такое, и как это использовать — я вам завидую! Для меня она была настоящим открытием в свое время.

Вам приходилось суммировать данные по сотрудникам или клиентам из большой таблицы, выбирать сколько выручки было по той или иной номенклатуре? Вы фильтровали по фамилиям/позициям, а затем вносили числа руками в отдельные ячейки? Может быть считали на калькуляторе? А если строк больше тысячи? Как посчитать быстро результат для каждой из строк?

Вот тут и пригодится СУММЕСЛИ!

Задача1. Существует статистика по товарам, городам и по тому, какие показатели были достигнуты по этим позициям. Необходимо рассчитать «на какую сумму продано номенклатуры Товар1?»

Как работает Функция СУММЕСЛИ в Excel? Подсчет суммы по значению

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

  • Диапазон.  Диапазон, в котором содержатся условия поиска. Заполнять обязательно. Для 1ой задачи столбец Товар.
  • Критерий.  Можно заполнить числом (85), выражением («>85»), ссылкой на ячейку (B1), функцией (СЕГОДНЯ()). Определяет условие, по которому суммируются (!). Все текстовые условия заключаются в кавычки («) «>85». Заполнять обязательно. Для 1ой задачи столбец =Товар1
  • Диапазон_суммирования. Ячейки, для суммирования, если они отличаются от ячеек в Диапазоне. Для 1ой задачи столбец Выручка.

Итак запишем формулу, предварительно занеся аргумент условия в ячейку F3

Не забудьте проверить 😉 Посчиталось? Верно? Отлично!

Как работает функция СУММЕСЛИМН в Excel? Подсчет суммы по значениям

Задача2. Необходимо рассчитать: «на какую сумму продано номенклатуры Товар3 в городе Казань?»

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

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

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

Как работает функция СЧЕТЕСЛИ в Excel? Подсчет количества значений

А также для подсчета количества повторений той или иной позиции используйте функции СЧЁТЕСЛИ (СЧЁТЕСЛИМН)

Задача3. Необходимо рассчитать: «сколько раз повторялось значение=»Самара» в столбце Город

Решается она еще проще, чем предыдущие две.

Дерзайте! Желаю удачи!

Скачать пример.

Вторая сильнейшая функция в Excel — это ВПР.

А так же не забывайте про сводные таблицы.

Поделитесь нашей статьей в ваших соцсетях:

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий