Excel сумма по условию соответствия

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

Примеры формулы для суммы диапазонов с условием отбора в Excel

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

Функция СУММЕСЛИ анализирует каждое значение ячейки в диапазоне B2:B12 и проверяет соответствует ли оно заданному условию (указанному во втором аргументе функции). Если значение меньше чем 0, тогда условие выполнено и данное число учитывается в общей итоговой сумме. Числовые значения больше или равно нулю игнорируются функцией. Проигнорированы также текстовые значения и пустые ячейки.

В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.

Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки «<0» (меньше чем ноль) используется строка «>0» (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.



Пример логического выражения в формуле для суммы с условием

Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно (<1000) придется использовать знак «меньше или равно» (<=1000) иначе цены со значением ровно 1000 не будут включены в расчеты. Более того в таком случае мы уже не можем использовать в другой формуле «больше или равно» (>=1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:

Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!

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

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

Синтаксис формулы суммирования по условию в Excel

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

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

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

• сроки с текстовым значением;
• числовые значения;
• логические выражения;
• формулы с функциями;
• ссылки на ячейки.

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

Создание критериев условий для функции СУММЕСЛИ

Второй аргумент функции называется «Критерий». Данный логический аргумент используется и в других подобных логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В каждом случаи аргумент заполняется согласно одних и тех же правил составления логических условий. Другими словами, для всех этих функций второй аргумент с критерием условий является логическим выражением возвращающим результат ИСТИНА или ЛОЖЬ. Это значит, что выражение должно содержать оператор сравнения, например: больше (>) меньше (<) равно (=) неравно (<>), больше или равно (>=), меньше или равно (<=). За исключением можно не указывать оператор равно (=), если должно быть проверено точное совпадение значений.

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

Таблица правил составления критериев условий:

Во втором аргументе критериев условий можно использовать разные функции и формулы. Ниже на рисунке изображен список дат и присвоенных им значений. Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:

Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):

Суммирование по неточному совпадению в условии критерия отбора

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

Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».

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

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

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

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

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

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

Как посчитать в Excel ячейки в определенных строках.

​Смотрите также​ ​ отобразиться результат суммирования​ ​ SHIFT+F3 для вызова​​ поиска, текстовую запись​​Функция СУММЕСЛИМН используется для​Все фактуры отсортированные относительно​, которые затем и​ Excel.​Ячейки в аргументе​) используется для поиска​
​,​Сельхозпродукты​
​ формул и списка​ условиями: необходимо узнать​Первый пример.​ в статье «Символы​Как посчитать в​
​ значений ячеек всех​ мастера функций, и​ логического выражения (например,​Пример 2. В таблице​ суммирования переданных значений​ дат транзакций.​ будут просуммированы. Пусть​Для суммирования значений, удовлетворяющих​Диапазон_суммирования​ соответствующих названий продуктов​»>32″​1500​ аргументов, то в​
​ сумму продаж товара​Сумма ячеек с условием​ в формулах Excel».​Excel​ диапазонов.​ вы быстро ее​ «>=100» или «<>0»),​ указаны данные об​ с учетом нескольких​
​Чтобы автоматически экспонировать цветом​ это будет диапазон​ заданному критерию (условию),​
​, которые содержат значение​ в диапазоне ячеек,​,​Южный​ нашем примере​Мясо​Excel.​В строке «Диапазон_суммирования»​ячейки выборочно​Обратите внимание на синтаксис​ там найдете.​ проверяемый текст (например,​ отгрузках трех товаров​ критериев отбора данных,​ необходимые суммы, необходимо:​B5:B15​ используется функция СУММЕСЛИ(),​
​ ИСТИНА, возвращают 1,​ заданных аргументом​B4​Маринова​диапазон_суммирования​(из столбца C)​Нам нужно посчитать​ указываем диапазон (столбец),​, не все подряд,​
​ в параметрах функции​Использование этой функции существенно​ «Продавец_1», «Товар_1») или​ в различные страны​ которые указываются в​
​Выделите диапазон ячеек A2:C14​, см. Файл примера.​ английская версия SUMIF().​ тогда как ячейки,​Диапазон_условия1​,​Мясо​— это диапазон​ в регионе​
​ сумму всех Ашанов.​ в котором находятся​
​ а по условию?​
​ при выделении нескольких​ расширяет возможности процесса​
​ числовое значение (Например,​ на протяжении трех​ качестве аргументов функции,​ и выберите инструмент:​
​Решим задачи:​​СУММЕСЛИДиапазонУсловие​​ которые содержат значение​
​(B2:B9). Кроме того,​»яблоки»​6596​ D2:D11, представляющий собой​Южный​ В ячейке С2​
​ числа, то, что​ Если вводить формулу​ диапазонов. Они разделены​ суммирования значений ячеек​ 20), определяющие ячейки​ месяцев. Определить суммарный​ и возвращает полученное​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».​найти сумму всех чисел​; [Диапазон_суммирования])​ ЛОЖЬ, возвращают 0​ функция выполняет поиск​или​
​Формула​
​ столбец с числами,​
​(из столбца A).​ пишем такую формулу.​ нам нужно сложить.​ вручную в большой​ между собой (;).​ в программе Excel.​
​ в диапазоне условия1,​ доход от отгрузок​ суммарное значение.​В появившемся окне «Создание​ больше или равных​Диапазон​ (нуль).​ имени​

excel-office.ru

Функция «СУММЕСЛИ» в Excel, примеры.

​»32″​​Описание​ которые вы хотите​ ​Вам понадобится такая формула:​=СУММЕСЛИ(A2:A8;»*Ашан*»;B2:B8)​​ У нас -​ таблице, то это​В параметрах функции СУММ​ На практике ее​ для которых будут​ товара №2 в​В отличие от СУММЕСЛИ,​
​ правила форматирования» выберите​ 10. Ответ: 175.​
​   — диапазон ячеек,​
​Действие​ ​»Артем»​
​.​Результат​ просуммировать;​=СУММЕСЛИМН(D2:D11,A2:A11,»Южный»,C2:C11,»Мясо»)​
​В формуле мы​
​ это столбец С.​ очень долго. Нас​ могут содержаться:​ рассмотрим возможности и​ отобраны и просуммированы​ Казахстан за июнь​ в рассматриваемой функции​ опцию: «Использовать формулу​ Формула: =СУММЕСЛИ(B5:B15;»>=10″)​ в котором ищутся​Описание​в диапазоне ячеек,​Диапазон_условия2, Условие2, …​’=СУММЕСЛИМН(D2:D11,A2:A11,​
​диапазон_условия1​Результат — 14719. Вот​ поставили по обе​ Диалоговое окно заполнили​ выручит функция Excel​ссылки на отдельные ячейки;​ настройки при суммировании​
​ данные из диапазона​
​ и август.​ ​ предусмотрена возможность внесения​
​ для определения форматированных​найти сумму всех чисел​ значения соответствующие аргументу​Использование подстановочных знаков​ заданных аргументом​
​    (необязательный аргумент)​»Южный», C2:C11,»Мясо»)​
​— это диапазон​
​ как работает эта​ стороны от слова​ так. Нажимаем «ОК».​ «СУММЕСЛИ».​ссылки на диапазоны ячеек​ нескольких диапазонов.​ суммирования.​Вид таблицы данных:​ до 127 критериев​ ячеек».​ меньше или равных​Условие​Подстановочные знаки, такие как​Диапазон_условия2​
​Дополнительные диапазоны и условия​Суммируются продажи по категории​ A2.A11, представляющий собой​ формула.​ Ашан звездочки. Это​Получилась такая формула.​У нас такая​ как смежные, так​Просуммируем значение ячеек A1,​Последующие аргументы являются необязательными​

excel-office.ru

Суммирование значений с учетом нескольких условий

​Для расчета итоговой суммы​ отбора данных для​В поле ввода введите​ 10. Ответ: 42.​. Диапазон может содержать​ вопросительный знак (?)​(C2:C9). Затем функция​

​ для них. Можно​ «Мясо» из​ столбец с данными,​​=СУММЕСЛИМН — это арифметическая формула.​​ значит, что нужно​ =СУММЕСЛИ(B2:B10;»план»;C2:C10)​​ таблица.​​ и несмежные;​

​ A2 и A3​

​ для заполнения, а​​ используем формулу:​ суммирования. СУММЕСЛИМН удобна​ формулу: 5000′ class=’formula’>​

​ Формула: =СУММЕСЛИ(B5:B15;»​ числа, даты, текстовые​ или звездочка (*),​ суммирует соответствующие обоим​ ввести до 127 пар​столбца C в​ в котором находится​ Она подсчитывает числа,​

• ​ искать все слова​

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

​ условиям значения в​ диапазонов и условий.​ регионе «Южный»​условие1​ которые в этом​ «Ашан», не зависимо​В ячейке F2 пишем​ в строках «план»​В параметрах функций все​ суммирования. За одно​

• ​ аргументам диапазон_условия1; условие1.​

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

​ на другие ячейки.​Условие1, 2​ диапазоне ячеек, заданном​Чтобы использовать эти примеры​из столбца A​— “Южный”.​ случае находятся в​ от других слов,​ такую формулу. =СУММЕСЛИ(B2:B10;»выпущено»;C2:C10)​ — это первая,​​ аргументы должны быть​​ и узнаем, для​ Всего может быть​ несмотря на то,​

• ​ большое число полей​

​ заливки для ячеек,​ Формула: =СУММЕСЛИ(B5:B15;»>0″). Альтернативный​ В случае, если​можно использовать для​ аргументом​ в Excel, выделите​ (результат — 14 719).​По мере ввода вводите​

​ столбце D. Начнем​ цифр, которые написаны​Ещё примеры счета​ четвертая и восьмая​ разделены точкой с​​ чего используется функция​​ задано до 127​ что для столбца​ данных. Например, если​ например – зеленый.​ вариант с использованием​​ другой аргумент -​​ поиска сходных, но​Диапазон_суммирования​ нужные данные в​=СУММЕСЛИМН(D2:D11,A2:A11,​​ формулы функция автозавершения​​ с того, что​

​ перед или после​ с условием смотрите​ строки.​​ запятой.​​ сумм.​ условий отбора данных​ «Месяц» было использовано​ требуется рассчитать общую​​ И нажмите на​​ функции СУММПРОИЗВ() выглядит​

Попробуйте попрактиковаться

​Диапазон_суммирования​ не совпадающих значений.​(A2:A9). Результат — 20.​ таблице, щелкните их​»Южный», C2:C11,»Мясо»)​ предлагает остальные аргументы;​ укажем формуле на​ слова «Ашан» -​

support.office.com

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

​ указанной компанией в​ желаемый эффект. Все​(критерия) достаточно гибка.​также является диапазоном,​ символов. Если требуется​ не являются бананами​. На новом листе​ все аргументы, удовлетворяющие​ столбец с данными,​ чтобы формула суммировала​Подробнее о символах,​.​

​ «Математические» выбираем функцию​ тот же результат.​ «Сумма» в разделе​

Синтаксис

​ ссылка на диапазон​ можно существенно сократить​

• ​ определенную страну, удобно​ дни, в которых​

• ​ Например, в формуле​ по которому производится​

Примеры

​ содержатся данные о​ цветом.​ D8 – число​Условие​ искомым символом значок​ помощью оператора​​Параметры вставки​​ число всех розничных​Скопируйте все данные из​ Этот диапазон ячеек​ читайте в статье​​ выполнения плана по​​ диапазон (столбец), в​​ 1, 2 и​​Диапазон ячеек распознан автоматически.​

Распространенные неполадки

Рекомендации

​ заданных аргументом​

У вас есть вопрос об определенной функции?

​Яблоки​ «Артем»)​

Помогите нам улучшить Excel

​Продажи​ где расположены данные​В ячейке D4​ только «критерий» пишем​ сумму «выпушено», то​ получаем один и​Примечание. Вместо использования инструмента​

support.office.com

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

​ быть указаны в​ CTRL+SHIFT+Enter):​ шкале).​ указанном во втором​

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

​ статьей Функция СУММЕСЛИМН()​​ как 32, «яблоки»​

​ СУММЕСЛИ и СУММЕСЛИМН​​Диапазон_условия2​Ольга​=СУММЕСЛИМН(A2:A9; B2:B9; «<>Бананы»; C2:C9;​​Южный​​ (A2:A11), и название​ напишем такую формулу.​ «%» — это​ написали бы критерий​ тот же результат​ «Сумма» на главной​​ кавычках.​​Функция СУММЕСЛИМН возвращает массив​Вид таблицы данных:​​ аргументе. В третьем​​ Сложение с несколькими​ или «>32».​ различается. Например, в​(C2:C9). Затем функция​​15​​ «Артем»)​​Орехов​​ условия («Южный»). Обратите​

​=СУММЕСЛИ(A2:A8;D3&»*»;B2:B8)​​ в нашей таблице​ – «выпущено».​ вычисления – число​ панели, можно в​Максимальная длина условия, переданного​​ значений для критериев​​Для расчета суммарного балла​ аргументе указываем суммы​ критериями в MS​

​Диапазон_суммирования​​ функции СУММЕСЛИМН аргумент​ суммирует соответствующие обоим​Артишоки​Имя аргумента​Напитки​ внимание, что между​​В формуле мы​​ так написано. Получилась​​В разделе «Критерий»​​ 11. Последовательно пройдите​ ячейку A4 сразу​ в качестве аргумента​ «Китай» и «Грузия»​ согласно установленным критериям​ для суммирования. Обратите​​ EXCEL (Часть 2.Условие​​  — диапазон ячеек содержащий​

Примеры

​Диапазон_суммирования​​ условиям значения в​​Артем​Описание​3571​ аргументами ставится запятая.​ указали адрес ячейки,​​ такая формула.​​ можно писать некоторые​ курсором от B1​ ввести функцию с​ условие1 (условие2 и​ соответственно, которые суммируются​​ используем следующую формулу:​​ внимание на то,​ И).​ числа, которые будут​​является первым, а​​ диапазоне ячеек, заданном​

​3​

• ​Диапазон_суммирования​Западный​=СУММЕСЛИМН(D2:D11;A2:A11;»Южный»;​ в которой написано​
• ​=СРЗНАЧЕСЛИ(B2:B10;»%»;C2:C10)​ символы.​ и до B5.​ параметрами вручную. Результат​
• ​ т. д.) не​ функцией СУММ.​Описание аргументов:​ что в первом​В статье СУММЕСЛИ — Сложение​ просуммированы, в случае​

​ в функции СУММЕСЛИ —​​ аргументом​​Артишоки​    (обязательный аргумент)​Егоров​Кавычки вокруг слова «Южный»​ «Ашан» и с​Таблица посчитала так.​* (звездочка) означает​ В каждой ячейке​ будет одинаковый.​ должна превышать 255​Примечание: если критерии переданы​​C3:C14 – диапазон ячеек​​ и третьем аргументах​

​ по условию (один​
​ если соответствующие им​ третьим. Этот момент​Диапазон_суммирования​Ольга​Диапазон ячеек для суммирования.​Молочные продукты​ указывают на то,​

​ содержимым этой ячейки​Как посчитать наценку, скидку,​ любое количество символов​ нажмите F2, чтобы​​ символов.​ в качестве константы​ с баллами, из​ мы используем абсолютные​ Текстовый критерий) рассмотрен более​

excel2.ru

Как выбрать суммы по условию в Excel

​ ячейки, указанные в​ часто является источником​(A2:A9). Результат — 30.​22​Диапазон_условия1​3338​ что используются текстовые​ сцепили (&) знак​ умножив столбец на​ в слове. Например,​

Как выбрать значения по условию в Excel

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

​Бананы​    (обязательный аргумент)​

​Восточный​ данные.​

1. ​ «*» (звездочка). Это​ число, смотрите в​ «мор*» — будет​
2. ​ ссылок для более​ помощью кнопки на​ критериев поиска требуемых​ и более пар​ выбраны значения для​
3. ​ то время как​ критерий применяется к соседнему​
4. ​Диапазон​ данных функций.​Описание​Артем​ Диапазон, в котором проверяется​Песоцкий​Наконец, вы вводите аргументы​

​ значит, что формула​ статье «Как умножить​ искать слова на​ понятного анализа синтаксиса​ панели инструментов или​ значений можно использовать​

​ диапазон_условияN; условиеN, результат​ расчета суммы, которые​ во-втором аргументе применяется​ диапазону, содержащему текстовые​соответствуют аргументу​При копировании и изменении​Вместо ожидаемого результата отображается​12​Условие1​Напитки​ для второго условия​ будет искать в​ в Excel число​ «мор» и любое​ записи параметров функций.​ при использовании мастера​ подстановочные знаки «?»​ выполнения формулы будет​ соответствуют установленным критериям;​ смешанная ссылка на​ значения, а суммирование​УсловиеДиапазон_суммирования​ этих похожих формул​ 0 (нуль).​

​Бананы​

Как формула Excel позволяет выбрать значения по условию?

​.​5122​ — диапазон ячеек​ столбце А все​ на число, проценты».​ окончание (мор, море,​В Excel можно одновременно​ функций (SHIFT+F3). функция​ и «*», замещающие​ некорректным.​C3:C14 – первый диапазон​ ячейку.​ производится только соответствующих​- необязательный аргумент.​ нужно следить за​Если выполняется поиск текстовых​Ольга​Диапазон_условия1​Северный​ (C2:C11), которые содержат​ слова «Ашан» независимо​

exceltable.com

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

​Ещё один способ​ моряк, т.д.). Чтобы​ просуммировать несколько смежных​ СУММ() относится к​ один или несколько​Полученное значение:​ ячеек, к которому​​

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

​ ячеек из числового​ Если он опущен,​ правильным порядком аргументов.​ значений, например имени​10​и​Песоцкий​ слово «Мясо», а​ от того, что​ посчитать ячейки в​ найти все фамилии​ и несмежных столбцов.​ группе формул «Математические».​ символов соответственно.​Функция имеет следующую синтаксическую​ будет применен первый​Таким образом Excel проверяет​

Динамический диапазон суммирования по условию в Excel

​ диапазона.​ то суммирование будет​Одинаковое количество строк и​ человека, убедитесь в​Морковь​Условие1​Молочные продукты​ также само слово​ написано после слова​ строках, но по​ на букву «Р»​Заполняете столбцы, так как​ Автоматически распознанные диапазоны​Если в диапазоне ячеек,​

​ запись:​

​ критерий;​ каждую ячейку в​В таблице находятся данные​

​ производиться по диапазону​

• ​ столбцов для аргументов,​ том, что значения​Артем​составляют пару, определяющую,​6239​ (заключенное в кавычки),​
• ​ «Ашан».​ другим условиям. Смотрите​ и заканчивающиеся на​ показано на рисунке:​
• ​ не всегда являются​ переданных в качестве​=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2;​
• ​»>5″ – первое условие​ столбце A и​ по продажам отсортированные​ ячеек, указанному в​ задающих диапазоны ячеек​
• ​ аргументов​33​ к какому диапазону​Южный​

​ которое нужно найти.​

​В Excel можно​ в статье «Как​ букву «в», нужно​Выделите диапазон A1:B3 и​ подходящими для пользователя.​ аргумента диапазон_суммирования, есть​

​ условие2]; …)​

Выборочное суммирование по условию в Excel

​ отбора значений из​ берет из нее​ по возрастанию относительно​ аргументе​Аргумент​Условие1, 2​Морковь​ применяется определенное условие​Маринова​ Закончите формулу закрывающейся​

​ сортировать данные одновременно​

​ в Excel сложить​ написать критерий так​

​ удерживая клавишу CTRL​

​ Их можно при​ ячейки, содержащие логические​Описание аргументов:​ указанного выше диапазона;​ критерии для вычисления​ даты транзакции. Необходимо​Диапазон​Диапазон_условия​заключены в кавычки.​Ольга​ при поиске. Соответствующие​Сельхозпродукты​

СУММЕСЛИМН для суммирования значений по нескольким условиям в Excel

​ скобкой​ по нескольким столбцам​ каждую вторую ячейку,​ — «Р*в» (или​ так же выделите​ необходимости быстро и​ ИСТИНА или ЛОЖЬ,​диапазон_суммирования – обязательный аргумент,​

​B3:B14 – второй диапазон​ (даты транзакций). Нам​ определить в какие​.​

​должен иметь то​Неверный результат возвращается в​Формула​ значения найденных в​8677​

​)​ и строкам, по​ строку».​ «Р*В»). Найдет фамилии​ столбец D1:D3.​ легко поправить.​ они будут автоматически​

​ принимающий ссылку на​

Особенности использования функции СУММЕСЛИМН в Excel

​ ячеек (с фамилиями​ нужна сумма по​

​ дни сумма транзакции​Рассмотрим случай, когда аргумент​

​ же количество строк​

• ​ том случае, если​Описание​ этом диапазоне ячеек​Южный​, нажмите клавишу ВВОД,​ нескольким условиям. Как​Рассмотрим,​
• ​ — Рублев, Рылеев,​На панели «Главная» щелкните​Допустим, нам нужно просуммировать​ преобразованы к числовым​ ячейки с числами,​ студентов), к которому​
• ​ условию, поэтому если​ превысила 5000. Следует​Диапазон_суммирования​ и столбцов, что​ диапазон ячеек, заданный​=СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9;​ суммируются в пределах​Егоров​ и вы получите​ это сделать, смотрите​как применять символы в​ т.д.​ по инструменту «Сумма»​ несколько диапазонов ячеек,​ значениям 1 и​

​ для которых будет​ будет применен второй​ возвращаемый результат функцией​ выбрать все суммы​опущен. В этом​ и аргумент​ аргументом​ «Артем»)​

​ аргумента​

1. ​Мясо​ результат 14719.​ в статье «Сортировка​ формулах​? (знак вопроса)​ (или нажмите ALT+=).​ как показано на​
2. ​ 0 соответственно и​ вычислено суммарное значение​ критерий;​ (касается конкретного дня)​ по условию и​ случае суммирование будет​
3. ​Диапазон_суммирования​Диапазон_суммирования​Суммирует количество продуктов, названия​Диапазон_суммирования​450​=СУММЕСЛИМН(D2:D11;A2:A11,»Южный»;C2:C11,»Мясо»)​
4. ​ в Excel по​Excel, чтобы посчитать сумму​ обозначает один символ.​Под каждым столбцом автоматически​ рисунке:​ будут учтены в​
5. ​ с учетом установленных​»А*» – второе условие​ является больше чем​ выделить их цветом.​ производиться по диапазону​.​, содержит значение ИСТИНА​
6. ​ которых начинаются с​.​Южный​Если вы вводите функцию​ нескольким столбцам и​ по условию, выборочно.​ Например, критерий «ст?л»​ добавилась функция СУММ().​Перейдите в ячейку D1​ итоговой сумме.​ критериев;​
7. ​ отбора значений (все​ 5000, то целая​Вместо того чтобы тратить​ ячеек, указанному в​К началу страницы​ или ЛОЖЬ.​

exceltable.com

Функция сумм в Excel и примеры ее использования

​Я​Условие1​Егоров​ СУММЕСЛИМН и не​ строкам».​Символы в формулах​ — будет искать​

​ Теперь в ячейках​ и выберите инструмент​Число ячеек в диапазонах​диапазон_условия1 – обязательный аргумент,​ фамилии, которые начинаются​ формула возвращает значение​ время на визуальный​ первом аргументе​

Как в таблице Excel посчитать сумму столбца?

​Задать вопрос на форуме​Значения ИСТИНА и ЛОЖЬ​и которые были​    (обязательный аргумент)​Мясо​ помните аргументов, не​Если вы сложить суммировать​

1. ​ помогают выбрать из​ стул, стол, т.д.​ A4; B4 и​ «Сумма».​ суммирования и условиях​ принимающий ссылку на​ с буквы «А»).​ ИСТИНА и сразу​
2. ​ анализ данных таблицы,​Диапазон​ сообщества, посвященном Excel​ в диапазоне ячеек,​ проданы продавцом​

​Условие, определяющее, какие ячейки​7673​ волнуйтесь. После ввода​ значения с учетом​ таблицы конкретные данные.​ Можно написать несколько​

​ D4 отображается результат​Удерживая клавишу CTRL дополнительно​ должно быть одинаковым,​ ячейки, в которых​Результат расчетов:​ же присваивает новый​ будем использовать правило​

​(т.е. он должен​

Поправка автоматического распознания диапазонов

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

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

1. ​ по улучшению следующей​Диапазон_суммирования​. Подстановочный знак (*)​
2. ​Диапазон_условия1​Песоцкий​ формулой появляется список​ получить сумму продаж​
3. ​ «СУММЕСЛИ», смотрите в​ «Иван?????» (Иванов, Иванченко,​ Это самый быстрый​ A2:B2 и ячейку​ вернет код ошибки​ проверки на соответствие​

​ определила динамически изменяемый​ ячеек. Новый формат​ автоматически определит и​ нем же будет​ версии Excel? Если​

​, оцениваются по-разному, что​ в аргументе​

• ​. Например, условия могут​
• ​Сельхозпродукты​ аргументов в нужном​ продукта в определенном​
• ​ статье «Функция «СУММЕСЛИ»​

​ т.д.)​ и удобный метод.​ A3.​ #ЗНАЧ!.​

​ заданному критерию;​ диапазон в соответствии​ заранее определен пользователем​ выделит цветом все​ производиться поиск значений​ да, ознакомьтесь с​ может приводить к​Условие1​ вводится в следующем​664​ порядке.​ регионе, используйте функцию​ в Excel».​

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

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

Одновременное суммирование столбцов

​ подставляет формат ячеек​ Enter и в​ категории: «Математические». Нажмите​

​ принимающий ссылку на​ значений и просуммировала​

1. ​ условного форматирования (зеленая​ таблицы отображен ниже​Условие​ пользовательских предложений для​
2. ​ их суммировании.​»=Я*»​32​

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

​ ячейку с условием​ его.​ заливка).​

exceltable.com

​ на рисунке:​

Синтаксис и создание функции

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

Давайте посмотрим на два оставшихся аргумента: «Диапазон» и «Критерий». Первый содержит список ячеек (A1: A100), которые будут проверяться и сравниваться с условием. В поле «Критерий» пользователь вводит условие, согласно которому ячейка становится одной из сводок. Это может быть условие неравных чисел (<30,> 50) или совпадение определенного текста («Текст»). Чтобы упростить понимание того, как задавать аргументы, откройте графическое окно «Аргумент функции» и задайте все условия по очереди в отдельных полях.

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

Как суммировать значения между двумя динамическими датами

В приведенном выше примере мы видели, как суммировать данные между двумя конкретными датами. Но что, если мы хотим суммировать данные, например, за последние 7 дней до даты открытия файла? Что, если мы не хотим каждый раз вставлять в формулу определенные даты?

В этом случае нам поможет следующая формула:

= СУММ (B2: B18; A2: A18; ”<=” & СЕГОДНЯ (); A2: A18; ”> =» & СЕГОДНЯ () — 6)

СУММЕСЛИМН для суммирования значений по нескольким условиям в Excel

Пример 3. Используя таблицу из второго примера, определите общую прибыль от поставки товаров №1. 1 в Китай и Грузию за весь период (три месяца).

Чтобы найти нужное значение, воспользуйтесь формулой массива (для ввода нажмите CTRL + SHIFT + Enter):

Функция СУММПИИФ возвращает массив значений для критериев Китая и Грузии, соответственно, которые суммируются функцией СУММ.

Примечание. Если критерии передаются как константа массива для двух или более пар критериев_диапазонN; conditionN, результат формулы будет неверным.

Суммирование с множеством условий.

Есть данные о заказах и продажах шоколада. Подсчитываем сумму реализованных продаж молочного шоколада. То есть у нас есть два требования: название продукта должно совпадать и в столбце «Завершено» должно быть указано «Да».

В качестве первого аргумента мы указываем интервал суммирования E2: E21, затем попарно интервал условия и само условие.

= СУММЕСЛИ (E2: E21; C2: C21; I2; F2: F21; I3)

В C2: C21 мы будем искать слово «молоко» в любом его появлении. То есть до и после него могут быть другие символы.

В F2: F21 мы ищем «Да», что является признаком того, что заказ выполнен.

При соблюдении ОБЕИХ требований такой заказ нам подходит и мы учтем его стоимость.

Как видите, мы нашли 2 матча, где продавался молочный шоколад.

Рекомендации

Необходимые действия

Описание

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

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

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



2. Начните запись, указав это в поле ввода, написав СУММЕСЛИ.



3. Создайте открывающую и закрывающую круглые скобки для вставки диапазона выбранных ячеек, например C2: C25. После этого обязательно поставить знак;, что означает окончание темы.



4. Откройте котировки и укажите в них условие, которое в нашем случае будет> 300000.



5. Как только вы нажмете клавишу Enter, функция активируется. На снимке экрана ниже показано, что только две ячейки соответствуют условию> 300000, поэтому формула суммирует их числа и отображает их в отдельном блоке.

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

Задача4 (Месяц)

Немного изменим условие предыдущего действия: найдем общий объем продаж за месяц (см. Пример файла Месячный лист).

Формулы строятся аналогично задаче 3, но пользователь вводит не 2 даты, а название месяца (предполагается, что данные в таблице находятся в пределах 1 года).

Месяц вводится с помощью раскрывающегося списка, список месяцев формируется с использованием динамического диапазона (для исключения лишних месяцев).

Как СУММЕСЛИМН работает с датами?

Если вы хотите выбрать и добавить некоторые индикаторы в определенный временной диапазон на основе текущей даты, используйте функцию СЕГОДНЯ () в ваших ограничениях, как показано ниже.

Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце A приходится на последние 7 дней, включая сегодняшний день (при условии, что сегодня 7 февраля):

= СУММ (G2: D21; A2: A21; «<=» & СЕГОДНЯ (); A2: A21; «> =» & СЕГОДНЯ () — 6)

Комментарий. При использовании другой функции Excel в сочетании с логическим оператором для создания ограничения необходимо использовать амперсанд (&) для объединения всего выражения в виде текста, например «<=» & TODAY().

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

= СУММЕСЛИ (A2: A21, «> =» & СЕГОДНЯ () — 6; D2: D21) — СУММЕСЛИ (A2: A21; «<=» & СЕГОДНЯ (); D2: D21)

Однако СУММЕСЛИМН делает добавление намного проще и понятнее, не так ли?

Выборочное суммирование по условию в Эксель

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

Сама таблица выглядит так.

5

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

= (СУММЕСЛИ (G2: D14; A2: A14; «= июнь»; B2: B14; «Продукт_2»; C2: C14; «Казахстан») + (СУММЕСЛИ (G2: D14; A2: A14; «= август»; B2: B14; «Товар_2»; C2: C14; «Казахстан»)))

В результате расчетов по этой формуле получаем следующий результат.

4

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

Альтернативный вариант

Альтернативный вариант для всех 4 задач — использовать автоматический фильтр .

Для решения 3-й задачи таблица с настроенным автоматическим фильтром выглядит так (см. Пример файла Лист 2 Дата).

Во-первых, таблица должна быть преобразована в формат таблицы MS EXCEL 2007 и включать строку итогов.

Описание функции

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

В языке программирования VBA и в английской версии редактора электронных таблиц синтаксис написан латинскими буквами. В отечественном аналоге — русский.

Синтаксис функции следующий: = summw (диапазон_суммы; диапазон_условий1; условие1; диапазон_условий2; условие2; …)

Диапазон суммирования — это массив, ячейки которого будут добавлены, если они соответствуют следующим условиям. Другой блок аргументов в синтаксисе — диапазон_критериев1; условие1. Они позволяют выбрать нужные ячейки в определенном массиве на основе первого фактора, которые затем суммируются в пределах начального диапазона. Дополнительными критериями являются следующие критерии_диапазона10; условие 10.

Как работает эта формула

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

Давайте проанализируем формулу, чтобы понять, как она работает:

• Первым делом указываем диапазон с данными о продажах (B2: B28), между которыми мы должны выбрать, какие значения мы будем добавлять
• Далее мы указываем диапазон данных, к которому будет применяться элемент управления. В нашем случае это диапазон с датами (A2: A28)
• На следующем шаге мы устанавливаем условие относительно диапазона дат, в соответствии с которым формула должна определять, какие данные нужно суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
• На заключительном этапе мы устанавливаем второе условие для диапазона с датами (A2: A28), согласно которому формула должна суммировать данные за период меньше или равный 15.06.2018

В результате функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.

Распространенные неполадки

Проблема

Описание

Особенности применения функции СУММЕСЛИМН

Есть несколько особенностей использования этой функции, на которые нужно обратить внимание. Прежде всего, эта функция игнорирует диапазоны с текстовыми строками или пустыми значениями, поскольку эти типы данных не могут быть добавлены арифметически, а только объединены как строки. Эта функция не может этого сделать. Также необходимо обратить внимание на следующие условия:

1. вы можете использовать эти типы значений в качестве условий для выбора ячеек для дальнейшего добавления содержащихся в них значений: числовые значения, логические выражения, ссылки на ячейки и т.д.
2. Если отмечены текст, логические выражения или математические знаки, эти критерии указываются в кавычках.
3. Условия, длина которых превышает 255 символов, использовать нельзя.
4. вы можете использовать грубые критерии для выбора значений с использованием подстановочных знаков. Знак вопроса используется для замены символа, а знак умножения (звездочка) используется для замены нескольких символов.
5. Логические значения, находящиеся в диапазоне суммирования, автоматически преобразуются в числовые значения в зависимости от их типа. Следовательно, значение «ИСТИНА» становится единицей, а «ЛОЖЬ» — нулем.
6. Если в ячейке появляется ошибка #VALUE!, Это означает, что количество ячеек в диапазоне условий и сумм различается. Вам нужно убедиться, что размеры этих аргументов совпадают.

Использование СУММЕСЛИМН и СУММЕСЛИ в Excel — что нужно запомнить?

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

Хотя общая часть понятна — схожее назначение и параметры — различия не так заметны, хотя и весьма существенны.

1. Порядок аргументов

Аргументы применяются по-разному. В частности, диапазон_суммирования — это первый параметр в СУММЕСЛИ, но это третий параметр в СУММЕСЛИ.

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

Однако в SUMIFN это очень важно и обязательно, поэтому оно стоит на первом месте. Ребята из Microsoft, наверное, подумали, что после добавления десятого или сотого интервала / пары критериев кто-то может забыть указать интервал для добавления:)

2. Диапазон суммирования и область критериев должны быть одинакового размера

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

Выражение = СУММЕСЛИ (E2: E21; C2: C21; I2; F2: F22; I3) вернет #VALUE! Сообщение об ошибке, потому что второй параметр поиска (F2: F22) не того же размера, что и остаток (E2: E21) и (C2: C21).

Хорошо, хватит стратегии (т.е теории), перейдем к тактике (примерам).

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

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

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



2. Начните писать функцию, назовите ее так, как было показано выше.



3. Первым делом введите диапазон меток, поставьте; и установите условие. Итак, это синтаксическое выражение примет что-то вроде этого: A2: A25; «Сентябрь»;.



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



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

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

Задача2 (2 числовых критерия)

Другой задачей может быть поиск сумм коробок только для тех партий товаров, у которых количество коробок попадает в определенный диапазон, например от 5 до 20 (см. Пример файла Sheet 2Numbers).

Формулы построены аналогично задаче 1: = СУММЕСЛИ (B2: B13; B2: B13; «> =» & D2; B2: B13; «<=» & E2)

Примечание: для удобства строки, участвующие в суммировании, выделены условным форматированием с правилом = И ($ B2> = $ D $ 2; $ B2 <= $ E $ 2)

Задача3 (2 критерия Дата)

Другой задачей может быть определение общих продаж за период (см. Пример файла Листа «2 даты»). Давайте воспользуемся другой исходной таблицей со столбцами «Дата продажи» и «Сумма продаж .

Формулы строятся аналогично задаче 2: = СУММЕСЛИ (B6: B17; A6: A17; «> =» & D6; A6: A17; «<=» & E6)

Примечание. Даты могут быть представлены в числовом формате, см. Статью Как Excel сохраняет дату и время

При необходимости даты можно ввести непосредственно в формулу = СУММЕСЛИ (B6: B17; A6: A17; «> = 15.01.2010»; A6: A17; «<= 06.03.2010»)

Чтобы отобразить условия выбора в текстовой строке, используйте формулу = «Объем продаж за период от» & ТЕКСТ (D6; «дд.ММ.гг») & «до» & ТЕКСТ (E6; «дд.ММ .я»)

Последняя формула использует собственный формат .

Использование операторов сравнения.

Рассчитываем стоимость заказов для «Красного» покупателя, в которых было более 100 единиц товара. Как видите, здесь нужно использовать как текстовые, так и числовые критерии.

Критерии можно записать в самой формуле, и это будет выглядеть так:

= СУММЕСЛИ (MI2: MI21; SI2: SI21; «Красный»; RE2: RE21; «> 100”)

Но рациональнее использовать ссылки, как это сделано на рисунке:

= СУММЕСЛИ (MI2: MI21; SI2: SI21; I2; RE2: RE21; I4)

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

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

Как еще мы можем решить нашу проблему?

Способ 2. Используем функцию СУММПРОИЗВ.

Давайте подробнее рассмотрим, как работает SUMPRODUCT():

= СУММПРОИЗВ (- (B2: B21 = $ I $ 12), — (D2: D21> I13), E2: E21)

Результат вычисления B2: B21 = $ I $ 12 — это массив

{ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ.: ЛОЖЬ

ИСТИНА означает, что код покупателя соответствует условию, например, слово Красный. Этот массив можно увидеть, выбрав в строке формул B2: B21 = $ I $ 12, а затем нажав F9.

А что это за странные минусовые знаки перед этими выражениями? Дело в том, что нам нужны не эти логические выражения, а числа, чтобы затем их можно было умножить и сложить. Если Excel выполняет математическую операцию с логическим выражением, он автоматически преобразует его в число. А знак минус означает умножение на -1. А если умножить на -1 дважды, результат не изменится. Мы помним это из школьной математики

И в результате логический массив превратится в массив чисел {0: 1: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 1: 0: 0: 0: 1: 0: 0: 0}.

Результат вычисления D2: D21> I13 — это массив

{ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ}.

ИСТИНА соответствует пределу «количество больше 100». Здесь мы также используем двойное отрицание для преобразования логических значений в числа.

И, наконец, результатом вычисления B2: B13 является массив {11250: 23210: 12960: 3150: 5280: 9750: 3690: 18300: 5720: 6150: 8400: 2160: 7200: 1890: 17050: 3450: 15840: 2250 : 7200: 8250}, т.е только числа из столбца E.

Результатом умножения этих трех массивов на элемент будет {0: 23210: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 15840: 0: 0: 0 }. Складываем эти продукты и получаем 39050.

Способ 3. Формула массива.

И еще один вариант расчета — применяем формулу массива. В I14 пишем:

= СУММ ((L2: L21 = I12) * (L2: L21> I13) * (L2: E21))

Не забудьте нажать CTRL + SHIFT + ENTER в конце, чтобы обозначить это выражение как формулу массива. Программа автоматически добавит фигурные скобки в начало и в конец. Опять получаем результат 39050.

Способ 4. Автофильтр.

Другой альтернативой является использование автоматического фильтра. Для этого преобразуйте диапазон данных A1: F21 в интеллектуальную таблицу. Напоминаю, что для этого в меню «Главная» выберите «Форматировать как таблицу». Далее добавляем итоговую строку (вкладка «Дизайн») и устанавливаем необходимые фильтры.

Без формул будет определено общее количество отфильтрованных строк.

Просмотров: 303

Суммируем в Excel.

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

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

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

Обычное суммирование.

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

Ее синтаксис несложен.

=СУММ(параметр1; параметр 2…)

При работе с функцией СУММ учитывайте следующие тонкости.

1. Параметрами могут выступать как отдельные числа и иные значения, так и отдельные ячейки, и диапазоны с ними.
2. В качестве ЗНАЧЕНИЙ параметров могут быть любые данные – числа, текст, даты, – но суммировать Excel будет только числа.
3. Если хотя бы одно значение в параметрах содержит ошибку, то и результат всей функции СУММ выдаст ошибку.
4. Всего можно указать до 255 параметров. Этим сама функция СУММ выгодно отличается от ее разновидности АВТОСУММА, часто применяемой новичками. АВТОСУММА находит только сумму значений в ячейках, расположенных рядом. При этом, если в диапазоне есть пустые ячейки или ячейки с текстом, то АВТОСУММА уже не складывает значения, находящиеся за ними.

Рисунок 1

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

Рисунок 2

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

Рисунок 3

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

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

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

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

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

=СУММЕСЛИ(B3:B70;”молоток”;C3:C70).

Обратите внимание, так как слово «Молоток» это текст, то мы взяли его в кавычки.

Данную формулу можно прочитать следующим образом: «Необходимо сложить значения всех ячеек, расположенных в диапазоне C3:C70 на позициях ячеек в диапазоне B3:B70, содержащих слово МОЛОТОК»

Или – так: «Если в ячейках диапазона B3:B70 находится слово МОЛОТОК, то соответствующие им по расположению ячейки диапазона C3:C70 просуммировать».

Что произойдет после того, как мы запустим на выполнение эту формулу, нажав Enter?

Excel начнет просматривать каждую ячейку в диапазоне B3:B70. Если он находит в очередной проверяемой ячейке слово «Молоток», то соответствующая ей по порядку ячейка из диапазона C3:C70 будет просуммирована. Если в очередной проверяемой ячейке в диапазоне B3:B70 слово «Молоток» не найдено, то ячейка из С3:С70 будет пропущена.

Рисунок 4

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

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

Рисунок 5

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

=СУММЕСЛИ(C3:C82;”>0″)

3. При указании критерия допускается применение подстановочных знаков. Так, знак звездочки «*» соответствует наличию или отсутствию любого количества произвольных символов. Если указать сочетание *ст, то Excel поймет, что нас интересуют любой текст, который заканчивается на сочетание «СТ», например – пост, помост, тост и прочие. Если указать «СТ*», то Excel будет искать слова, которые начинаются с СТ – студент, стол, стена. НУ а если ввести сочетание «*ст*», то будут найдены все ячейки, где вообще сочетание «СТ» есть, неважно – в начале, в середине или в конце. Вопросительный же знак говорит о возможном наличии или отсутствии одного произвольного символа. В частности, если указать такой вариант «СТ??», то из списка стол, стул, стена, студент, стекло, стеллаж программа отберет только слова Стол и Стул (так как было указано, что первые два знака должны быть СТ, но после них может быть НЕ БОЛЕЕ двух символов). Это удобно, если вы не знаете точное значение критерия.

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

=СУММЕСЛИ(B3:B82;”*внесение наличных*”;C3:C82)

Рисунок 6

 4. Если требуется вывести итоговую сумму на основе значений ячеек, где непосредственно используются подстановочные знаки, то указывая эти знак в качестве критерия, перед ними уазываем знак тильды (~)

=СУММЕСЛИ(I3:I18;”~*”;J3:J18)

Рисунок 7

5. Ну и наконец – не забываем, что если в качестве критерия указываем значение из списка в диапазоне, то диапазон проверки и диапазон суммирования в формуле закрепляют, используя абсолютную или смешанную адресацию. Изначально отсутствие закрепления на расчет не повлияет, но в дальнейшем при сортировке или ином изменении в порядке исходных данных будут проблемы с искажением результатов. Для закрепления достаточно выделит нужные диапазоны в формуле и нажать клавишу F4 в верхнем ряду клавиатуры. В примере ниже найден объем реализации в литрах для каждой номенклатурной группе ГСМ.

Рисунок 8

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

Рисунок 9

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

В следующем примере выполнялось суммирование по значениям из внешнего файла traning.xlsx

=СУММЕСЛИ([traning.xlsx]склад!$A$2:$A$1119;”*”&$A3&”*”;[traning.xlsx]склад!D$2:D$1119)

Рисунок 10

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

Рисунок 11

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

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

Синтаксис данной функции выглядит так

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

Приведем пример работы СУММЕСЛИМН. Предположим следующее. Наименования товаров заданы в диапазоне B3:B50, количество упаковок каждого товара находится в графе С3:С50, а в диапазоне D3:D50 указаны соответствующие заданным позициям товара расценки. Наша задача – найти общее количество упаковок рубашек с ценой ниже 3000. Получаем следующую задачу:

«Найти общую сумму в диапазоне С3:С50, но при этом в диапазоне B3:B50 должно содержаться слово «рубашка», а в диапазоне D3:D50 значение должно быть меньше 3000». Итоговая формула будет выглядеть следующим образом.

=СУММЕСЛИМН(C3:C50;B3:B50;”рубашка”;D3:D50;”<3000″)

Рисунок 12

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

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

1. В отличии от функции СУММЕСЛИ равенство диапазонов суммирования и поиска соответствия условию является не рекомендательным, а обязательным. Если хотя бы один из указанных диапазонов не совпадет по размеру с остальными, получите не просто неверные данные, а значение ошибки #ЗНАЧ!
2. В отличии от функции СУММЕСЛИ, где третий параметр не обязателен и иногда допускается его пропуск, для функции СУММЕСЛИМН указание каждого параметра обязательно. Нельзя задать диапазон проверки условия и при этом не указать само условие или наоборот. Тем более вы не можете не указать диапазон суммирования, так как таким диапазоном автоматически будет считаться самый первый диапазон, указанный при введении данной функции. Заданные для проверки условия проверяются по содержимому разных диапазонов. Нельзя проверить на соответствие разным критериям ячеек одного и того же диапазона. Вы можете найти общую сумму затрат по выбранному филиалу, группе затрат и периоду, так как наименование филиала, группа затрат и период будут указаны в разных графах. Если же вы попытаетесь с помощью функции СУММЕСЛИМН найти общую сумм затрат по нескольким филиалам из общего списка, то у вас ничего не получится, так как наименования филиалов расположены в одной колонке таблицы.

Подведем итоги.

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

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

В таких случаях используются формулы массива, а также функции БДСУММ, СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ. Функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ подробно рассмотрены на прошлых занятиях, а вот о работе с функциями БДСУММ, СУММПРОИЗВ и применении формул массива для суммирования в соответствии с условиями   поговорим в наших следующих материалах.

Наше же текущее занятие подошло к концу, спасибо за внимание и успеха в работе.  Надеемся, что каждый нашел в нашем уроке для себя что-то новое, интересное и необходимое для работы.

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

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

Простое сложение в Excel

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

Суммирование ячеек по условию

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

• Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
• Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как ">5". Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие "Иванов";
• Диапазон суммирования – массив значений, которые нужно сложить.

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

Функция «СУММЕСЛИ»

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

1. По свободной ячейке кликните левой клавишей мыши, чтобы выбрать место, где будет отображаться результат операции.
2. Кликните по иконке, расположенной рядом со строкой ввода формул, чтобы вызвать меню с функциями.
3. Выберите категорию «Математические» и найдите в списке функций «СУММЕСЛИ».
4. Кликните по кнопке «ОК», чтобы подтвердить свой выбор.
5. В появившемся окне «Аргументы функции» нужно заполнить обязательные поля: «Диапазон» и «Критерий». Например, нужно узнать, сколько килограмм фруктов продал продавец Иванов. Для этого выделите диапазон столбца «Продавец» полностью.
6. В строке «Критерий» пропишите фамилию продавца: «Иванов». Кавычки можно не указывать, программа вставляет их автоматически.
7. Поставьте курсор мыши в поле диапазона суммирования и выделите столбец «Количество (кг)» в таблице.
8. Нажмите на кнопку «ОК» внизу, чтобы произвести суммирование с условием, или из данной таблицы узнать, сколько килограмм фруктов продал Иванов.

В выделенной ячейке отобразится результат сложения, а в верхней строке – функция. Вычислить значение можно без вызова окна «Аргументы функции», если прописать все вручную. При этом базовая запись должна выглядеть так: =СУММЕСЛИ(X; «Условие»; Y), где X – это диапазон, а Y – диапазон суммирования. Значения не обязательно вписывать, их достаточно выделить с помощью мышки в таблице. Все элементы функции разделены знаком «;».

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

Функция «СУММЕСЛИМН»

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

Чтобы суммировать ячейки с несколькими условиями, действуйте согласно следующей инструкции:

1. Выделите пустую ячейку, в которой будет отображаться конечный результат, затем нажмите на кнопку fx, которая находится рядом со строкой функций.
2. В разделе «Математические» в окне «Вставка функций» нажмите «СУММЕСЛИМН», затем подтвердите выбор, нажав на кнопку «ОК».
3. В появившемся окне в строке «Диапазон суммирования» введите ячейки, который находятся в столбце «Количество».
4. В «Диапазон условия» выделите все ячейки в столбце «Товар».
5. В качестве первого условия пропишите значение «Яблоки».
6. После этого необходимо задать второе условие и диапазон для него. В данной таблице столбец «Покупатели» является значением для диапазона. Выделите его в строку, затем в втором условии пропишите фамилию Евдокимов.
7. Нажмите на кнопку «ОК», чтобы программа посчитала, сколько яблок купил Евдокимов.

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

Итог

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