Суммесли в excel как сделать

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

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

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

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

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

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

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

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

Способ 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)

Источник: https://www.planetaexcel.ru/techniques/2/167/

Функция СУММЕСЛИ в Excel с примерами

Сейчас мы с вами рассмотрим еще одну очень часто используемую функцию 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. Скачать пример
2. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.
3. Спасибо за внимание.

Источник: https://sirexcel.ru/priemi-excel/funkciya-summesli-v-excel-s-primerami/

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

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

Что такое СУММЕСЛИ

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

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

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

Когда можно использовать функцию «Сумма если»

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

• Если все числа больше нуля;
• Если в ячейках есть данные;
• Если число больше определенной цифры;
• И другие.

Вы можете сами задать условие этого подсчета. И это сэкономит вам очень много времени, так как Эксель буквально за вас посчитает, все что вам нужно сложить и не будет добавлять в расчёты те данные, которые вам складывать не нужно.

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

Как работает функция

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

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

Это же работает при любом произвольном условии. Рассмотрим более сложное условие – должность. Предположим, нам нужно посчитать зарплату только продавцов. Для этого прописываем диапазон и правило, по которому считать. В итоге получаем формулу: =СУММЕСЛИ(E2:E14;»продавец»;F2:F14), где:

• Первое: E2:E14 – это диапазон, откуда брать данные для условия;
• Второе: продавец – это контролируемое условие;
• Третье: F2:F14 – это диапазон, в котором мы считаем сумму.

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

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

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

• Начальная точка диапазона условий;
• Конечная точка диапазона условия;
• Условие;
• Начальная точка подсчета суммы;
• Конечная точка подсчета суммы;

Вы можете добавлять дополнительные условия – 5 является минимальным, а не максимальным значением.

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

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

(Еще не оценивали) Загрузка…

Источник: https://win10m.ru/funkciya-summesli-v-excel.html

Функция SUMIF (СУММЕСЛИ) в Excel. Как использовать?

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

Что возвращает функция

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

Синтаксис

=SUMIF(range, criteria [sum_range]) – английская версия

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

Аргументы функции

• range (диапазон) – диапазон ячеек, по которым оцениваются критерии. Аргументом могут быть числа, текст, массивы или ссылки, содержащие числа;
• criteria (условие) – критерии, которые проверяются по указанному диапазону ячеек и определяют, какие ячейки суммировать;
• sum_range (диапазон_суммирования) – (не обязательно) суммируемые ячейки. Если этот аргумент не указан, то функция использует аргумент range (диапазон) в качестве sum_range (диапазон_суммирования).

• суммирование ячеек в функции СУММЕСЛИ производится на основе одного критерия, если вам необходимо указать несколько критериев – используйте функцию SUMIFS (СУММЕСЛИМН);
• пустые и текстовые ячейки в аргументе sum_range (диапазон_суммирования) игнорируются;
• критерием для функции могут служить числа, выражения, результаты вычисления из других ячеек, текст или формула;
• если в качестве критерия указан текст или математический/логический символ (=,+,-,/,*), то такие значения должны быть указаны в двойных кавычках;
• аргумент с критерием не должен быть длиннее чем 255 символов;
• в качестве критерия могут использоваться подстановочные знаки (*);
• если количество ячеек в диапазоне критерия и аргументе sum_range (диапазон_суммирования) различаются, то размер диапазона критерия будет в приоритете.

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

Источник: https://excelhack.ru/funkciya-sumif-summesli-v-excel/

Функция СУММЕСЛИ() — Сложение с одним критерием в MS EXCEL (Часть1.ЧИСЛОвой критерий)

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

Синтаксис функции

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

Диапазон   — диапазон ячеек, в котором ищутся значения соответствующие аргументу Условие. Диапазон может содержать числа, даты, текстовые значения или ссылки на другие ячейки.

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

Условие   — критерий в форме числа, выражения или текста, определяющий, какие ячейки должны суммироваться. Например, аргумент Условие может быть выражен как 32, «яблоки» или «>32».

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

Примеры

Рассмотрим случай, когда аргумент Диапазон_суммирования опущен. В этом случае суммирование будет производиться по диапазону ячеек, указанному в первом аргументе Диапазон (т.е.

он должен содержать числа). В нем же будет производиться поиск значений соответствующих аргументу Условие, которые затем и будут просуммированы. Пусть это будет диапазон B5:B15, см.

Файл примера.

Решим задачи:

• найти сумму всех чисел больше или равных 10. Ответ: 175. Формула: =СУММЕСЛИ(B5:B15;»>=10″)
• найти сумму всех чисел меньше или равных 10. Ответ: 42. Формула: =СУММЕСЛИ(B5:B15;»0″). Альтернативный вариант с использованием функции СУММПРОИЗВ() выглядит так : =СУММПРОИЗВ((B5:B15)*(B5:B15>0))

Форма задания условия (критерия) достаточно гибка. Например, в формуле =СУММЕСЛИ(B5:B15;D7&D8) критерий

Источник: https://excel2.ru/articles/funkciya-summesli-slozhenie-s-odnim-kriteriem-v-ms-excel-chast1chislovoy-kriteriy-summesli

Функция Excel СУММЕСЛИ (SUMIF) — примеры использования

Добрый день, уважаемые подписчики и посетители блога statanaliz.info.

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

Обе эти функции умеют «связывать» данные из разных источников (таблиц) по ключевому полю и при некоторых условиях являются взаимозаменяемыми. В то же время есть и существенные отличия в их назначении и использовании.

Если назначение ВПР в том, чтобы просто «подтянуть» данные из одного места Excel в другое, то СУММЕСЛИ используют, чтобы числовые данные просуммировать по заданному критерию.

Функцию СУММЕСЛИ можно успешно приспособить для решения самых различных задач. Поэтому мы в этой статье рассмотрим не 1 (один), а 2 (два) примера. Первый связан с суммированием по заданному критерию, второй – с «подтягиванием» данных, то есть в качестве альтернативы ВПР.

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

Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.

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

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

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

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

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

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

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт.

То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все.

Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

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

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров.

Последнее окошко – это диапазон, где находятся суммируемые данные.

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе.

То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице.

Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные. Здесь только нужно обратить внимание на относительность ссылок.

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

Заполненные поля Мастера функций при подобном расчете будут выглядеть примерно так.

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

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

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

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

Нужно просто знать свойства функции СУММЕСЛИ и использовать согласно инструкции по эксплуатации.

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

Где-то в другом экселевском файле есть статистика за февраль по всему ассортименту, но нам хочется проанализировать именно эти позиции, для чего требуется из большого файла со статистикой продаж всех товаров подтянуть нужные значения в нашу табличку. Для начала давайте попробуем воспользоваться формулой ВПР. В качестве критерия будем использовать код товара.

Результат на рисунке.

Отчетливо видно, что одна позиция не подтянулась, и вместо числового значения выдается ошибка #Н/Д. Скорее всего, в феврале этот товар просто не продавался и поэтому он отсутствует в базе данных за февраль. Как следствие ошибка #Н/Д показывается и в сумме.

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

Теперь воспользуемся формулой СУММЕСЛИ вместо ВПР.

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

На сегодня все. Всех благ и до новых встреч на statanaliz.info.

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

Источник: https://statanaliz.info/excel/funktsii-i-formuly/funktsiya-excel-summesli-sumif/

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

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

Синтаксис функции и принцип работы

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

Синтаксис СУММЕСЛИ выглядит следующим образом:

=СУММЕСЛИ(диапазон; критерий; [диапазон суммирования]), где:

• диапазон – список значений, к которым будет применено ограничение;
• критерий – конкретное условие;
• диапазон суммирования – список значений, которые будут суммироваться.

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

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

Рассмотрим все возможные ситуации применения одноименной функции.

Общий вид

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

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

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

=СУММЕСЛИ(C3:C12;»одежда»;F3:F12), где:

• C3:C12 – тип одежды;
• «одежда» – критерий;
• F3:F12 – интервал суммирования.

В результате получили 180. Функция проработала все указанные нами условия и выдала корректный результат. Также можно повторить подобные манипуляции для других типов товара, сменив критерий «одежда» на какой-то другой.

С несколькими условиями

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

=СУММЕСЛИМН(F3:F12;C3:C12;»одежда»;E3:E12;50), где:

• F3:F12 – диапазон суммирования;
• «одежда» и 50 – критерии;
• C3:C12 и E3:E12 – диапазоны типов товара и стоимости единицы соответственно.

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

С динамическим условием

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

Для вставки новой строки нужно нажать ПКМ на интересующей ячейке и выбрать «Вставить» – «Строку».

Далее просто введите новые данные или скопируйте их с другого места. Итоговое значение соответственно изменится. Аналогичные трансформации происходят и при редактировании или удалении строк.

На этом я заканчиваю. Вы увидели основные примеры использования функции СУММЕСЛИ в Excel. Если есть какие-то рекомендации или вопросы – милости прошу в комментарии.

Источник: https://geekon.media/funkciya-summesli-v-excel/

3 способа подсчитать итоги по условию в Excel

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

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

Исходные данные и свод для заполнения

• Сегодня мы рассмотрим 3 способа это сделать:
• 1) Функция СУММЕСЛИМН
• 2) Функция СУММПРОИЗВ
• 3) Сводная таблица

Но сначала поделимся с Вами небольшой хитростью. В итоговом своде в ячейках с названиями месяцев на самом деле введен не текст («Январь», «Февраль» и т.д.), а даты первого дня каждого месяца (01.01.2017, 01.02.2017 и т.д.), а уже после к этим ячейкам применен пользовательский формат «ММММ».

Чтобы применить такой формат, выделите нужные ячейки, нажмите Ctrl+1, выберите вкладку Число, найдите пункт «(все форматы)» и в строку Тип введите четыре прописные буквы М. Применение пользовательского формата

Этот небольшой трюк впоследствии нам очень поможет при подсчете итоговых сумм.

Способ 1. Функция СУММЕСЛИМН

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

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

=МЕСЯЦ(A2)

и протянем ее вниз на всю высоту таблицы. Получим столбец с порядковым номером месяца (определяется по дате в первом столбце).

Дополнительный столбец

=СУММЕСЛИМН($C:$C;$D:$D;МЕСЯЦ(H$2);$B:$B;$G3)

1. где:
2. $C:$C — столбец «Сумма затрат» (первым указывается тот диапазон, итоги по которому нужно подсчитать).
3. $D:$D — столбец проверки первого условия («Номер месяца»).

МЕСЯЦ(H$2) — первое условие. Ячейка H2 это «Январь». Так как мы вначале ввели туда 01.01.2017, а потом просто применили числовой формат, мы можем обработать эту ячейку функцией МЕСЯЦ и узнать порядковый номер месяца (и тогда функция сможет сравнить порядковый номер месяца в H2 и порядковые номера в столбце «Номер месяца»).

$B:$B — столбец проверки второго условия («Статья»).

$G3 — второе условие. Ячейка с названием статьи затрат, по которой подводим итог.

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

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

Способ 2. Функция СУММПРОИЗВ

Избежать создания доп.столбца (как в первом способе) можно путем применения функции СУММПРОИЗВ. Ее особенность в том, что она может обрабатывать внутри себя массивы данных, но при этом не требует ввода через Ctrl+Shift+Enter, то есть формально не является формулой массива.

В ячейку для Статьи 1 месяца Январь (в примере это ячейка H3) введем формулу:

=СУММПРОИЗВ((МЕСЯЦ($A$2:$A$1000)=МЕСЯЦ(H$2))*($B$2:$B$1000=$G3)*$C$2:$C$1000)

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

Разберем пошагово, как эта формула работает:

• Первая часть формулы (МЕСЯЦ($A$2:$A$1000)=МЕСЯЦ($H2)) делает следующее. Она берет диапазон А2:A1000 и к каждой ячейке применяет функцию МЕСЯЦ, то есть из каждой даты получает номер месяца. Затем каждый из полученных номеров сравнивается с номером месяца в ячейке H2 (это наш Январь в итоговом своде). Результат такого сравнения — столбец из значений ИСТИНА (если номера совпали) и ЛОЖЬ (если не совпали). Все эти вычисления происходят внутри формулы и не выносятся на лист. Обратите внимание, что сравнение нужно обязательно заключать в скобки!

Работа первой части формулы

• Вторая часть формулы ($B$2:$B$1000=$G3) делает то же самое, но для колонки Статьи (здесь нам не требуется применение функции МЕСЯЦ, названия статей сравниваются напрямую). Сравнение также берется в скобки и на выходе так же дает столбец значений ИСТИНА и ЛОЖЬ.
• На третьем этапе столбцы перемножаются между собой. В Excel при умножении логических значений ИСТИНА и ЛОЖЬ на выходе получается ноль или единица.

Перемножение столбцов сравнения

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

• На завершающем этапе функция складывает значения полученного столбца и выдает итоговую сумму.

Правила работы с функцией при подобных расчетах:

• не указывать целые столбцы и строки в качестве аргументов;
• перемножаемые диапазоны-аргументы должны быть равны (А2:А1000 и B2:B1000, например);
• все выражения сравнения нужно заключать в скобки.

Способ 3. Сводная таблица

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

Выбор места вставки таблицы

Поместите поле «Дата» в область строк, поле «Статьи» в область столбцов, а поле «Сумма затрат» в область значений, как показано на рисунке ниже.

Настройка сводной

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

Сводная таблица

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

• простое копирование и вставка. Подходит, если сводная не будет обновляться и менять размеры (не будут добавляться новые статьи);
• использование функции ИНДЕКС, для извлечения данных из сводной. Про эту функцию мы недавно подробно рассказывали. Этот способ подойдет, если сводная будет обновляться, но новые статьи не будут появляться;
• использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Самый интересный способ, который позволяет гибко менять сводную и всё равно подтягивать данные. Разберем его подробнее.

Встаньте в ячейку Января и Статьи 1 итогового свода и попробуйте сослаться на соответствующую ячейку сводной таблицы. Скорее всего, Excel вместо простой ссылки, вроде =А15, вставит огромную формулу

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма затрат»;$M$2;»Дата»;1;»Статья»;»Статья 1″)

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

Включение функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

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

2) Адрес любой ячейки сводной таблицы. Указывается на случай, если на листе их несколько и Excel должен понять, с какой именно работать;

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

В нашем случае нужно указать поле «Дата», в качестве условия для него — месяц. Так как в сводной месяц представлен в виде текста «янв», «фев» и т.д., нам нужно превратить наш заголовок в такой же текст. Так как мы вводили туда даты (делали это в начале статьи), получить нужное сочетание поможет формула

=ТЕКСТ(H2;»МММ»), где H2 — ячейка с месяцем в итоговом своде

Вторая пара условий — поле «Статья» и ссылка на название статьи в итоговом своде. В результате для ячейки Января и Статьи 1 получим формулу:

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма затрат»;$M$2;»Дата»;ТЕКСТ(H$2;»МММ»);»Статья»;$G3)

Итоговый результат

Файл-пример, в котором реализованы все 3 способа подсчета, Вы можете скачать по этой ссылке.

Для закрепления материала, можете посмотреть ролик по данному уроку на нашем YouTube канале.

• Поддержать наш проект и его дальнейшее развитие можно вот здесь.
• Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
• С уважением, команда tDots.ru

Источник: https://zen.yandex.ru/media/id/59affb7afd96b11e8eadd771/5a74034d8c8be30f0667f4bf

Как Начать Использовать СЧЕТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ в Excel

Обучение работе в Microsoft Excel — это добавление все большего и большего числа формул и функций к вашему инструментарию. Комбинируйте их, и вы можете делать со своими электронными таблицами, практически все что угодно.

В этом уроке, вы узнаете, как можно использовать три весьма полезных формулы в Excel: СУММЕСЛИ, СЧЕТЕСЛИ  и СРЗНАЧЕСЛИ.

Здесь у нас список транзакций за месяц с классификацией по типам расходов.

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

С правой стороны, есть ячейка: Dining Out Expense в которой используются эти три формулы, что бы отследить мои затраты:

• СЧЕТЕСЛИ — Используется для того чтобы подсчитать количество раз, когда в списке появляется слово «Restaurant».
• СУММЕСЛИ — Рассчитывает сумму всех затрат со словом «Restaurant».
• СРЗНАЧЕСЛИ — Рассчитывает среднее значения для расходов со словом «Restaurant».

В общем виде, вот что каждая из этих формул делает для вас и как вы можете использовать их в своих интересах:

• СУММЕСЛИ — суммирует значения, удовлетворяющие заданным условиям, как например, суммирует все затраты из одной категории.
• СЧЕТЕСЛИ — Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию, например, сколько раз какое-то название повторяется в списке.
• СРЗНАЧЕСЛИ — Рассчитывает условное среднее значение; вы можете рассчитать среднюю оценку только для экзаменов.

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

СЧЁТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ в Excel (Короткий ВидеоУрок)

Скринкаст — один из лучших способов посмотреть и приобрести новый навык, включая и этот — как начать использовать эти три ключевые формулы в Microsoft Excel. Посмотрите этот видеоурок ниже, что узнать как я работаю в Excel. Загрузите бесплатно Книгу с примерами, которую я использую в этом уроке.

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

Как Использовать СУММЕСЛИ в Excel

Используйте для этой части урока, вкладку SUMIF (лист с таким названием) в закачанном вами файле примеров.

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

Вот как работает формула СУММЕСЛИ:

=СУММЕСЛИ(ячейки которые нужно проверить на условие; само условие; какие ячейки складывать при удовлетворении условию)

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

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

Я хочу знать две вещи:

1. 1.Сколько всего денег я потратил в ресторанах в этом месяце.
2. Все затраты в этом месяце, из любой категории, которые превысили значение 50$.

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

Сумма Затрат на Ресторан

• Чтобы узнать мои суммарные затраты на ресторан, я просуммирую значения всех затрат с категорией «Restaurant», которые приводятся в Столбце В.
• Вот формула, которую я использую в этом примере:
• =СУММЕСЛИ(B2:B17;»Restaurant»;C2:C17)
• Обратите внимание, что элементы разделены точкой с запятой (в онлайн версии разделителем служит запятая) . Эта формула выполняет три вещи:

• Смотрит, что находится в ячейках с В2 по В17 в категориях затрат
• Использует слово «Restaurant» в качестве критерия для выбора того, что суммировать
• Использует значения в ячейках С2-С17, что бы суммировать

В этом примере, я суммирую все величины, с категорией затрат —  «Restaurant».

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

Затраты Выше 50$

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

Давайте напишем простую формулу, что бы найти сумму всех затрат выше 50$:

=СУММЕСЛИ(C2:C17;»>50″)

В этом случае, формула чуть проще: так как мы суммируем те же величины, что мы и проверяем на условие (С2-С17), мы просто должны указать эти ячейки. Затем мы должны добавить точку с запятой и потом «>50», что бы суммировать только те значения, которые больше 50$.

Сумма всех затрат, которые превышают 50 долларов, с помощью простой формулы в Excel/

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

Как Использовать СЧЕТЕСЛИ в Excel

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

3. Вот общий формат для формулы СЧЕТЕСЛИ:
4. = СЧЕТЕСЛИ(ячейки которые надо подсчитывать, критерий по которым ячейку принимать в расчет)
5. Используя те же данные, давайте посчитаем случаи появления такой информации:

• Сколько раз в течение месяца я покупал одежду
• Количество затрат, значение которых равно или больше 100 долларов

Число Случаев Покупки Одежды

• Мой первый СЧЕТЕСЛИ будет смотреть на тип расходов и подсчитывать количество покупок с категорией «Clothing» среди моих транзакций.
• Окончательная формула будет выглядеть следующим образом:
• =СЧЕТЕСЛИ(B2:B17;»Clothing»)

Эта формула смотрит в столбец с названием «Expense Type», подсчитывает количество раз, сколько ей встретилось слово «clothing», и суммирует их. В результате получается 2.

Формула СЧЁТЕСЛИ подсчитывает количество расходов, с названием одежда «Clothing» и суммирует их.

Количество Затрат на Сумму 100$+

1. Теперь давайте рассмотрим количество транзакций в моем списке значение которых от 100 долларов и выше.

2. Вот формула, которую я буду использовать:
3. =СЧЕТЕСЛИ(C2:C17;»>100″)

Это простая формула, состоящая из двух частей: она просто указывает Excel список данных которые нужно считать, и дает правило для подсчета. В этом случае, мы просматриваем ячейки С2-С17, на предмет значений, которые выше 100 долларов.

Подсчет числа транзакции на сумму 100$+ с помощью формулы СЧЕТЕСЛИ в Excel.

Как Использовать СРЗНАЧЕСЛИ в Excel

Используйте для этой части урока, вкладку СРЗНАЧЕСЛИ (лист с таким названием) в закачанном вами файле примеров.

И последнее, давайте посмотрим как использовать формулу СРЗНАЧЕСЛИ. Я думаю, к настоящему моменту, уже понятно, что СРЗНАЧЕСЛИ, можно  использовать для расчета среднего определенных величин, которые выбираются на основании условий, которые мы зададим для Excel.

• Формат формулы СРЗНАЧЕСЛИ следующий:
• =СРЗНАЧЕСЛИ(ячейки которые нужно проверить на условие; само условие; для каких ячеек рассчитывать среднее при удовлетворении условию)
• Формат формулы СРЗНАЧЕСЛИ, такой же как у формулы СУММЕСЛИ.
• Давайте используем СРЗНАЧЕСЛИ формулу, для расчета двух статистических величин для моих затрат:

1. Средние затраты на рестораны..
2. Среднее для всех затрат, которые меньше 25 долларов.

Затраты на Рестораны в Среднем

1. Чтобы подсчитать  сколько в среднем я потратил на рестораны, я написал формулу СРЗНАЧЕСЛИ, что бы рассчитать среднее значение на основании категории.

2. =СРЗНАЧЕСЛИ(B2:B17;»Restaurant»,C2:C17)
3. В этой формуле, есть три часть, каждая из которых отделена точкой с запятой:

• В2:В17 задает диапазон ячеек для которых проверяется выполнение условия. Так как категория затрат записана в этом столбце.
• Слово «Restaurant» задает значение, которое нужно искать.
• И наконец С2-С17 — из которого берутся значения для расчета среднего.

Здесь я использую СРЗНАЧЕСЛИ, что бы усреднить мои затраты на еду в ресторанах.

И наконец, Excel усредняет все мои затраты на рестораны в путешествии. По формуле, которую я ему дал.

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

Среднее для Затрат Меньше чем 25$

• Если я слежу за своими незначительными покупками, и хочу знать сколько я потратил в среднем, я могу написать СРЗНАЧЕСЛИ для затрат, значение которых меньше некой величины.
• Вот формула, которую я использую, для того, чтобы сделать это:
• =СРЗНАЧЕСЛИ(C2:C17;»

Источник: https://business.tutsplus.com/ru/tutorials/how-to-start-using-countif-sumif-and-averageif-in-excel—cms-28086