Суммесли и счетесли в excel
В предыдущей статье мы рассмотрели функцию = СУММЕСЛИ () , которая справляется с задачей, если вам необходимо произвести сложение значений соответствующих определенному одному условию. Что делать, если у вас несколько условий? Как получить общий объем продаж какого-то продукта, реализованного продавцом в определенный месяц? И вот здесь на помощь приходит функция =СУММЕСЛИМН()
В конце статьи рассмотрим один очень полезный прием использования функции =СУММЕСЛИМН(), который я уверен вам очень пригодится
Функция =СУММЕСЛИМН() расширяет возможности функции =СУММЕСЛИ() , позволяя указать от 2 до 127 критериев, а не только один.
И как обычно перед использованием функции давайте разберем ее состав и принцип работы.
=СУММЕСЛИМН() ищет определенные критерии в двух или более полях записи, и только если он находит соответствие для каждого указанного поля, данные для этой записи суммируются.
= СУММЕСЛИМН (диапазон суммирования; диапазон 1-го условия; критерий 1-го условия; диапазон 2-го условия; критерий 2-го условия; и т.д.)
Диапазон суммирования – непосредственно сам диапазон, в котором нам необходимо произвести суммирование данных на основании заданных условий;
Диапазон 1-го условия - диапазон ячеек, содержащий критерии, на основании которых нам необходимо произвести отбор ячеек для суммирования, отвечающие первому условию;
Критерий 1-го условия - непосредственно сам критерий 1-го условия. Он может быть в форме числа, выражения, ссылки на ячейку, текста или функции.
Диапазон 2-го условия - диапазон ячеек, содержащий критерии, на основании которых нам необходимо произвести отбор ячеек для суммирования, отвечающие второму условию;
Критерий 2-го условия - непосредственно сам критерий 2-го условия. Он может быть в форме числа, выражения, ссылки на ячейку, текста или функции.
В ажно. Если критерии текстовые, логические или дата вам необходимо заключить их в двойные кавычки. Например - "Картофель" или "01.01.2021" или ">01.01.2021"
Статья представляет собой подборку заданий для проведения итоговых уроков по изучению Excel. Задания снабжены пояснениями, которые с каждым упражнением становятся все менее подробными. Такой подход заставляет учащихся не просто выполнять предложенные упражнения, но и запоминать приемы работы, ведь иначе придется возвращаться к уже выполненной работе и разбираться с заданием заново; а также способствует простому и понятному для учащихся оцениванию их работы.
Упражнение 1. «Магазин»
В магазин «Молоко» каждый день завозят молочные продукты несколько поставщиков. Составить таблицу учета поставок за день для этого магазина. В отдельной таблице определить количество поставок молочных продуктов одного вида за день, их суммарную стоимость и среднюю цену.
Комментарии:
I. Создайте две таблицы: с исходными данными и результатами.
1. В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
2. Заполните таблицу исходными данными, например:
3. В таблице с результатами должны быть поля: «Наименование», «Количество поставок», «Суммарная стоимость», «Средняя цена».
4. Во второй таблице в столбце «Наименование» названия молочных продуктов из первой таблицы должны встречаться один раз, например:
II. Введите формулы в таблицы.
1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке
2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:
а) Для определения количества поставок нужно определить, сколько раз за день в магазин завозили, например, молоко. Для этого нужно использовать функцию СЧЁТЕСЛИ, которая определяет количество данных в диапазоне, равных критерию. Примерный вид формулы в ячейке С15:
где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).
b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:
где F2:F11 — диапазон стоимости продукта из первой таблицы.
c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:
где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.
Замечания:
Данное упражнение может быть дополнено следующими заданиями (и не только ими):
1. Определить количество (в литрах) каждого продукта, завезенного в магазин.
2. Составить таблицу «Поставщики», в которой определить, на какую сумму каждый поставщик завез в магазин продукции, общий вес привезенной каждым поставщиком продукции, и сколько видов продуктов привез каждый из поставщиков.
Упражнение 2. «Студенческие стипендии» [1]
- стипендия не назначается, если среди оценок есть хотя бы одна двойка;
- 3,0 средний балл < 3,5 — 1000р.
- 3,5 средний балл < 4,0 — 1200р.
- 4,0 средний балл < 4,5 — 1500р.
- 4,5 средний балл < 5,0 — 1800р.
- средний балл = 5,0 — 2000р.
Определить общую сумму назначенных стипендий.
Замечания:
Формулировка данной задачи, с некоторыми изменениями, взята из учебника «Информатика: Практикум по технологии работы на компьютере» под ред. Н.В. Макаровой.
- Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:
- Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:
Упражнение 3. «Завод железобетонных изделий»
Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.
I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.
1. Первая таблица должна содержать поля: «Марка», «Длина», «Ширина», «Высота», «Удельный вес», «Вес блока».
a) Заполните таблицу, кроме столбца «Вес блока» (не менее 10 марок).
2. Заведите отдельную ячейку для значения грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка», «Количество блоков», «Количество вагонов».
a) В столбце «Марка» повторите названия нескольких марок блоков из первой таблицы (не менее 4).
b) Столбец «Количество блоков» заполните произвольными данными.
1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны.
2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:
a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.
b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ.
3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.
Упражнение 4. «Прайс-лист»
Компания, выпускающая косметику, выдает распространителям прайс-лист, в котором указано название продукта и его цена для распространителя и для клиента. Создать «электронный калькулятор» распространителя, с помощью которого он сможет определить, какая сумма ему потребуется, чтобы выкупить заказанную покупателем продукцию у компании, сколько ему должен заплатить клиент, и прибыль, которую он получит в результате продажи.
В прошлый раз мы рассматривали функцию ВПР. Она очень хорошо комбинируется с функцией СУММЕСЛИ при необходимости получения сводной числовой информации. Далее разберемся в принципах работы этой функции.
Синтаксис очень простой:
= СУММЕСЛИ( диапазон ; критерий ; [ диапазон_суммирования ])
Для примера снова возьмем небольшую таблицу с данными:
Предположим, что у вас возникла потребность получить сводные данные по продажам каждого продавца за какой-либо период времени. Опять же можно все это сделать руками. Если данных будет не очень много, то возможно вы сможете сделать это довольно быстро. Но мы живем во времена больших объемов информации и чаще имеем дело с большими массивами данных. Поэтому автоматизация простых действий крайне необходима, чтобы экономить ваше время. Тем более, что данная функция максимально проста в использовании.
1) сначала мы переходим к таблице и выделяем ячейку в которой, хотим получить сводное значение. Пишем =СУММЕСЛИ(
2) выделяем диапазон исходной таблицы, содержащий в себе значения критерия. В данном случае мы хотим собрать данные о продажах, используя ФИО сотрудников. Т.е. ФИО сотрудника - это критерий, а продажи - искомое значение. В нашей таблице диапазон, содержащий критерии выбора - B2:B7. Ставим точку с запятой. Формула принимает вид =СУММЕСЛИ(B2:B7;
3) выбираем критерий суммирования. В предыдущем шаге мы выделяли диапазон, содержащий критерии. Теперь мы выбираем одно из значений критерия в сводной таблице. В ячейке B11 содержатся данные о ФИО одного сотрудника. Это и есть критерий. Ставим точку с запятой. Формула принимает вид =СУММЕСЛИ(B2:B7;B11;
4) Выбираем связанный диапазон суммирования, т.е. диапазон содержащий в себе данные, которые мы хотим получить. Чаще всего этот диапазон имеет туже размерность, что и диапазон, содержащий в себе критерии. В нашем случае это столбец "Продажи" и нам необходимо указать диапазон C2:C7. Формула принимает вид =СУММЕСЛИ(B2:B7;B11;С2:С7)
Мы полностью ввели формулу и теперь можем ее вставить в другие ячейки таблицы. Теперь мы будем обладать сводной информацией о продажах каждого сотрудника.
Нам нужно суммировать ячейки, выбранные по определенному условию. Или подсчитать количество ячеек, удовлетворяющих условию. Вот самый эффективный способ: используем функции СУММЕСЛИ и СЧЕТЕСЛИ.
Например, у нас есть следующая таблица:
Пусть это будут рейсы в определенные города и какие-то экономические результаты этих поездок.
Как подсчитать приход и расход по Москве и количество рейсов в Москву (т.е. фактически – сколько раз встретилась «Москва» в таблице)?
=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
- Диапазон: Город и приход
- Условие: «Москва» (можно, кстати, было и адрес ячейки написать – A2)
- Диапазон суммирования: все значения Прихода.
Последний нюанс – зафиксируем диапазон ($A2), чтобы потом просто натянуть форумулу на следующий столбец.
Я использовал очень простой пример, но функция СУММЕСЛИ гораздо более гибкая – условие ведь может быть любым, даже отдельной функцией. Например, нам нужно суммировать только сегодняшние данные (25 января 2016). Легко:
Конечно, пара предварительных итераций, и можно было бы обойтись и без СЧЕТЕСЛИ. Мы могли бы добавить столбец Счетчик со значением единица, а потом, составив сводную таблицу, где бы значения счетчика суммировались, увидели количество совпадений. Но, согласитесь, при должном контроле над таблицей, использование этих функций и быстрее и круче.
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 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) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Читайте также: