Оглавление
- Excel Sumifs Function Examples
- Функции СЧЁТ и СУММ в Excel
- Как исправить #VALUE! сообщение об ошибке в функции СУММЕСЛИ и СУММЕСЛИМН
- Примеры использования функции СУММЕСЛИМН
- SUMIFS and Wildcard
- Функция СУММЕСЛИ при условии неравенства
- Функция СУММЕСЛИМН с несколькими условиями
- Несколько критериев для СУММЕСЛИ()
- Примеры суммеслимн
- Формула суммеслимн по частичному совпадению условия (Формулы/Formulas)
- SUMIFS+SUMIFS
- Функция «СУММЕСЛИМН»
- SUM(SUMIFS({}))
- СУММЕСЛИМН для периода дат
- Пример суммирования с использованием функции СУММЕСЛИ
- Выборочные вычисления по одному или нескольким критериям
Excel Sumifs Function Examples
The spreadsheet below shows the quarterly sales figures for 3 sales representatives.
The Sumifs function can be used to find total sales figures for any combination of quarter, area and sales rep.
This is shown in the examples below.
A | B | C | D | |
---|---|---|---|---|
1 | Quarter | Area | Sales Rep. | Sales |
2 | 1 | North | Jeff | $223,000 |
3 | 1 | North | Chris | $125,000 |
4 | 1 | South | Carol | $456,000 |
5 | 2 | North | Jeff | $322,000 |
6 | 2 | North | Chris | $340,000 |
7 | 2 | South | Carol | $198,000 |
8 | 3 | North | Jeff | $310,000 |
9 | 3 | North | Chris | $250,000 |
10 | 3 | South | Carol | $460,000 |
11 | 4 | North | Jeff | $261,000 |
12 | 4 | North | Chris | $389,000 |
13 | 4 | South | Carol | $305,000 |
Example 1
To find the sum of sales in the North area during quarter 1:
=SUMIFS( D2:D13, A2:A13, 1, B2:B13, «North» )
which gives the result $348,000.
The value in column A is equal to 1
The entry in column B is equal to «North»
and calculates the sum of the corresponding values from column D.
I.e. this formula finds the sum of the values $223,000 and $125,000 (from cells D2 and D3).
Example 2
Again, using the data spreadsheet above, we can also use the Sumifs function to find the total sales for «Jeff», during quarters 3 and 4:
=SUMIFS( D2:D13, A2:A13, «>2», C2:C13, «Jeff» )
This formula returns the result $571,000.
In this example, the Excel Sumifs function identifies rows where:
The value in column A is greater than 2
The entry in column C is equal to «Jeff»
and calculates the sum of the corresponding values in column D.
I.e. this formula finds the sum of the values $310,000 and $261,000 (from cells D8 and D11).
For further examples of the Excel Sumifs function, see the Microsoft Office website.
Функции СЧЁТ и СУММ в Excel
- =СУММЕСЛИ(B2:B5; «Иван»; C2:C5)
- Допустим, «Кемерово». Условие
- математические операторы. Например,
- того, что нами
- Рассмотрим два возможных варианта:
- набежавших процентов).
такая формула. функцию СУММЕСЛИМН поСРЗНАЧЕСЛИ да, ознакомьтесь с 0 (нуль).. итог будет равен критерий – равенство
выбора ячеек, включенных490 000 ₽ строке #ЗНАЧ!.суммирует только те 1 – ссылка
СЧЕТЕСЛИ
Ежедневно на сумму просроченнойДля этого будет использоваться=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;E2:E8;»>500″) менеджерам и покупателям.(AVERAGEIF) и темами на портале
доходу по ней. поля значению «Индивидуальные». в сумму, достаточноСкопируйте образец данных изАргумент значения из диапазона на ячейку с
дебиторской задолженности начислялся оператор «СУММЕСЛИ» вЗдесь последний критерийПример 3.
значений, например имениПродуктИспользование функции СУММЕСЛИ вИ в этом случае кликнуть на нужную, приведенной ниже таблицыдиапазон_суммирования C2:C5, для которых названием города (С3). условия 2; условие отношение исключительно к 0,1 процент в
Нам нужно посчитать(AVERAGEIFS), чтобы рассчитать Excel. человека, убедитесь вПродавец Excel облегчает выполнение мы должны указать и ее расположение и вставьте егоможет не совпадать соответствующие значения изДля учета вида услуг 2; … необязательные последнему столбцу.
«>500». сумму продаж всех среднее значение ячеекСЧЁТ том, что значения5 многих задач. Ее колонку слагаемых в будет указано в
диапазона B2:B5 равны задаем второй диапазон аргументы для назначенияПосле того как заполненыИсходим из объема рефинансирования с того, чтоПример 6. фруктов, кроме яблок. на основе одногоСЧЁТЕСЛИ
аргументовЯблоки синтаксис прост и поле «Диапазон_суммирования» окна
формуле.
office-guru.ru>
Как исправить #VALUE! сообщение об ошибке в функции СУММЕСЛИ и СУММЕСЛИМН
? только значения, превышающие или звездочка (*), в аргументеиmaslow
Проблема: формула ссылается на ячейки в закрытой книге
— подумаем.Способ 3: Формула листе.ПРИМЕЧАНИЯ: Количество критериев аргументам диапазон_условия1; условие1. его. способы суммирования в
даты. В ячейку и складывает данные по улучшению следующей это приводит к=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)) и звездочку ( 5. Для этого в аргументахУсловие1Условие1: Очень интересное решение,
vkluchatel массива суммирования ограничено оперативной Всего может быть
Excel, не обязательно E10 устанавливаем функцию из таблицы по версии Excel? Если ошибке #ЗНАЧ!.Сумма комиссионных за имущество* можно использовать следующуюУсловие1, 2
Проблема: длина строки условия превышает 255 символов
(составляют пару, определяющую, но есть одно: Ок, пример упрощен.Code =СУММ(ЕСЛИ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2);F2:F16))
памятью. задано до 127Пример 2. В таблице формулами, смотрите в «СУММЕСЛИ». определенным условиям, которые да, ознакомьтесь сПримечание. стоимостью больше 1
). Вопросительный знак соответствует формулу:
можно использовать для»=Я*» к какому диапазону «но»- что еслиVlad999В английской версии:ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая условий отбора данных указаны данные об статье «Суммирование в
В ячейке B10 мы пишем в темами на порталеЭто известная проблема 600 000 ₽. одному любому символу,=СУММЕСЛИ(B2:B25;»>5″) поиска сходных, но) используется для поиска применяется определенное условие данные в объединённой: вариантCode =SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16)) версия Excel для суммирования. отгрузках трех товаров Excel». пишем нужную дату. формуле.
Это видео — часть не совпадающих значений. соответствующих названий продуктов при поиске. Соответствующие из 3 ячеекКод =СУММПРОИЗВ(((($A$1:$H$1=$K$1)*$A$2:$H$900)=—K3)*$B$2:$I$900) помоему
КАК ЭТО РАБОТАЕТ:Способ 2:Примечания: в различные страны
Помогите нам улучшить Excel
Разберем Excel. функций Excel, в=СУММЕСЛИ(A2:A5; «>160000») последовательности символов. Если учебного курса сложениеВопросительный знак соответствует любому
support.office.com>
Примеры использования функции СУММЕСЛИМН
Функция СУММЕСЛИМН не такая сложная, как кажется на первый взгляд, оказывается. Но для большей наглядности давайте рассмотрим некоторые практические примеры, как можно использовать функцию СУММЕСЛИМН. Так будет значительно проще углубиться в тему.
Динамический диапазон суммирования по условию
Итак, начнем с первого примера. Допустим, у нас есть таблица, в которой содержится информация о том, как справляются студенты с учебной программой по определенному предмету. Есть набор оценок, успеваемость оценивается по 10-балльной шкале. Стоит задача найти оценку за экзамен тех студентов, фамилия которых начинается с буквы А, а их минимальный балл составляет 5.
Таблица выглядит следующим образом.
1
Чтобы нам рассчитать суммарный балл, основываясь на описанных выше критериях, необходимо применять такую формулу.
2
Давайте более подробно опишем аргументы:
- C3:C14 – это наш диапазон суммирования. В нашем случае он совпадает с диапазоном условия. Из него будут отбираться баллы, используемые для расчета суммы, но лишь те, которые подпадают под наши критерии.
- «>5» – наше первое условие.
- B3:B14 – второй диапазон суммирования, который обрабатывается на предмет соответствия второму критерию. Видим, что здесь нет совпадения с диапазоном суммирования. Из этого делаем вывод, что диапазон суммирования и диапазон условия может быть как идентичным, так и нет.
- «A*» – второй диапазон, который задает отбор оценок лишь тех студентов, чья фамилия начинается на А. Звездочка в нашем случае означает любое количество знаков.
После расчетов мы получаем следующую таблицу.
3
Как видим, формулой было произведено суммирование значений, основываясь на динамическом диапазон и опираясь на заданные пользователем условия.
SUMIFS and Wildcard
If we are lucky, there is a uniform pattern in the labels that we can use with wildcards. For example, in the data above, all of the sales accounts happen to start with “Sales-” and that makes us really lucky. We can simply use a wildcard to tell the SUMIFS function to add up any rows where the account begins with Sales- as shown in the formula below:
=SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-*")
Here it is in the report:
Note: Alternatively, we could reference the report label in the cell, and update the third argument to B6&”*”
A similar function could populate the COS report value, as follows:
=SUMIFS($C$18:$C$28,$B$18:$B$28,"COS-*")
And then we could populate the SG&A report value by adding up all of the accounts that do not have a dash, as follows:
=SUMIFS($C$18:$C$28,$B$18:$B$28,"<>*-*")
However, in practice, we are not often lucky enough to have such a pattern. So, let’s explore other options.
Функция СУММЕСЛИ при условии неравенства
Первый пример — использование функции СУММЕСЛИ при условии, когда число для попадания должно быть больше, меньше или не равняться заданному числу. При таком синтаксисе функция проверяет все ячейки указанного диапазона и считает только подходящие. Ручное ее написание через поле ввода состоит из нескольких частей:
- Определитесь с диапазоном ячеек, попадающих под рассмотрение формулой, у нас это будет прибыль за месяц.
Начните запись с ее указания в поле ввода, написав СУММЕСЛИ.
Создайте открывающую и закрывающую скобку, где введите диапазон выбранных ячеек, например C2:C25. После этого обязательно поставьте знак ;, который означает конец аргумента.
Откройте кавычки и в них укажите условие, что в нашем случае будет >300000.
Как только произойдет нажатие по клавише Enter, функция активируется. На скриншоте ниже видно, что условию >300000 соответствуют лишь две ячейки, следовательно, формула суммирует их числа и отображает в отдельном блоке.
Выше был разобран только один из примеров со случайно взятыми условиями. Ничего не помешает подставить другие значения, расширить или сузить диапазон — формула все равно нормально посчитает значение, если правила синтаксиса были соблюдены.
Функция СУММЕСЛИМН с несколькими условиями
Завершим разбор примеров третьим вариантом, когда условий для сложения сразу несколько. При таких расчетах рассматриваемая функция заменяется на видоизмененную СУММЕСЛИМН, которая позволяет задавать более одного аргумента, чего нельзя реализовать в первом варианте. Один из самых простых примеров с полным синтаксическим соответствием создается так:
- Возьмем таблицу, в которой есть три разных значения как по находящимся там данным, так и по их типам. Это месяц, общая прибыль и количество проданных единиц товара. В формуле с несколькими условиями нужно сделать так, чтобы в итоговую сумму попали только те результаты прибыли, которые были получены в сентябре при продажах выше 29 единиц.
Начните запись со стандартного ее объявления =СУММЕСЛИМН, откройте скобки и напишите, какой диапазон ячеек будет суммироваться. Не забудьте закрыть объявление аргумента знаком ;.
Следом идет второй аргумент — совпадение по названию в ячейке. Запись этого параметра уже рассматривалась выше и еще раз ее можно увидеть на скриншоте.
Третье условие — соответствие заданному неравенству, с чем мы ознакомились еще в начале статьи. Опять в таком же формате укажите диапазон значений и само неравенство в кавычках. Не забудьте поставить закрывающую скобку, завершив тем самым написание функции.
Полностью строка этой функции имеет вид =СУММЕСЛИМН(C2:C25;A2:A25;«Сентябрь»;B2:B25;«>29»), а вам остается лишь подстроить ее под себя и получить правильный результат. Может показаться, что объявлять каждый аргумент и не запутаться в символах сложно, но при раздельном подходе никаких проблем возникнуть не должно.
Подробнее: 10 популярных математических функций Microsoft Excel
Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.
Несколько критериев для СУММЕСЛИ()
один» повторяет действие выполнялось — получимCode =DSUM(A1:G16,F1,I1:K2) Ашан звездочки. Это искомым символом значок помощью оператораПараметры вставки число всех розничных оператор конкатенации. В
В следующем примере функция использованием Динамического диапазона таблицы. Размещение условий
является первым аргументом, позволяет.
они будут автоматически ячейки с числами, будет применен второй функции СУММПРОИЗВ в
0 (1*1*0 =КАК ЭТО РАБОТАЕТ: значит, что нужно
тильды (~).<>выберите команду продавцов, (1) проживающих примере ниже формула
СУММ (для исключения лишних в одной строке а в СУММЕСЛИ()Makes
преобразованы к числовым для которых будет
критерий; нашем примере, но 0 ; 1*0*1Из указанной нами
искать все словаНапример, формула =СУММЕСЛИМН(A2:A9; B2:B9;в аргументеИспользовать форматы конечных ячеек в одном регионе, вернет сумму значений,складывает весь столбец месяцев). соответствует Условию И – третьим. При: Благодарю значениям 1 и вычислено суммарное значение»А*» – второе условие не является формулой = 0 ; базы данных A1:G16 «Ашан», не зависимо «=Я*»; C2:C9; «Арте?»)
Условие1. (2) чей доход которые больше значения А, а это
Альтернативным вариантом для всех (см. диапазон
planetaexcel.ru>
копировании и редактировании
- Счетесли в excel по двум критериям
- Суммесли в excel формула
- Как в excel работает суммесли
- Excel суммесли на английском
- Функция суммесли в excel примеры
- Функция суммесли в excel
- Как в excel отсортировать данные по нескольким критериям
- Функция суммесли в excel примеры с несколькими условиями
- Критерий фишера в excel
- Excel впр по нескольким критериям
- Excel суммесли формула
- Сумма если в excel примеры 2 критерия
Примеры суммеслимн
Для начала создаете таблицу со значениями. Можно использовать различные форматы ячеек и в зависимости от этого будет меняться синтаксис внутри формулы. Для примера будем работать с таблицей следующего вида:
Как видите, ячейки содержат и даты, и текст, и цифры. Будем задавать критерии для поиска суммы, а также рассматривать структуру оператора под поставленную задачу.
- Примеры с датами продаж:
Задача: найти количество продуктов проданных 22.02.2019г.
В свободной ячейке пишете следующее: =СУММЕСЛИМН(С2:С8;B2:B8;B2)
- Примеры с двумя условиями:
Задача: определить сумму яблок, проданных 22.02.2019г.:
Функция будет выглядеть следующим образом: =СУММЕСЛИМН(C2:C8;B2:B8;B2;A2:A8;»Яблоко»)
где B2:B8;B2 – первое условие, а A2:A8;»Яблоко» – второе. Диапазон суммирования по столбцу Количество.
- Примеры с текстом и звездочкой:
Задача: найти количество яблок, проданных Артемом.
Содержимое пустой ячейки: =СУММЕСЛИМН(C2:C8;A2:A8;»Яблоко»;D2:D8;»А*»)
Использование подстановочного знака звездочка помогает искать продавца, имя которого начинается на букву А. При таком условии считается число яблок, проданных Артемом и Ангелиной, поскольку их имена начинаются на букву А. Чтобы найти именно продажи Артема, необходимо изменить аргумент на «Ар*».
- Также в числовых столбцах можно использовать операторы сравнения. Если в условие больше меньше или равно, то можно обозначить границы поиска по цифрам.
Задача: Найти дату продажи Ангелиной продуктов больше 10 единиц.
Функция имеет следующий вид: =СУММЕСЛИМН(B2:B8;D2:D8;»Ангелина»;C2:C8;»>10″)
Как видите, оператор СУММЕСЛИМН позволяет находить сумму значений исходя из нескольких параметров, выполнение которых обязательно. При этом нужно соблюдать правила одинаковости размера массивов и написания текстовых блоков. Единственным недостатком такой функции является то, что нельзя суммировать несколько диапазонов. Для этого лучше использовать СУММПРОИЗВ.
Формула суммеслимн по частичному совпадению условия (Формулы/Formulas)
принимающий ссылку наВ результате формула автоматически использования функции «СУММЕСЛИМН», С2:С8. выбирает определенные данные(SUMIF). В данном в таблице данные функциях СУММЕСЛИМН() иAleksSid «текст заканчивается на», МП 50 мм»;I1:I50;»Маты хочу применить 2 нет: Никто ничего не документы работают с суммирования и условиях ячейки, в которых определила динамически изменяемый смотрите в статьеВ строке «Условие3» для сложения по случае для проверки в рамках 1 БДСУММ()). Заголовки этой СУММЕСЛИ(). В СУММЕСЛИМН(): Так? Код =СУММПРОИЗВ((ЦЕЛОЕ(Лист2!A2:A6)=A2)*(Лист2!C2:C6=C1)*Лист2!B2:B6) чтобы было для МП 50 мм») условия в формуле
jakim чертил, сводная строится
этой функцией довольно должно быть одинаковым, содержатся данные для
диапазон в соответствии «Примеры функции «СУММЕСЛИМН» пишем – «яблоки». нескольким условиям. Можно условия и суммирования года). таблицы должны в аргументtru-lya-lya «текст содержит», нужно
(При выборе «Орск»: Как вариант. сама в большом обьеме
иначе функция СУММЕСЛИМН проверки на соответствие с условиями отбора в Excel».Нажимаем «ОК». Получился такой указать до 127 используется один столбец,
excelworld.ru>
SUMIFS+SUMIFS
Another option is to simply string together a series of SUMIFS functions, each one designed to aggregate a specific label. For example, we could use SUMIFS to add up all rows where the account is equal to Sales-Labor, and then add to its result a SUMIFS function designed to add up all rows where account is equal to Sales-Hardware, and so on. This idea can be illustrated as follows:
=SUMIFS()+SUMIFS()+SUMIFS()
The corresponding formula to populate the sales report value is shown below.
=SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-Labor")+ SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-Hardware")+ SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-Software")
When there are only a couple of criteria values, this approach may get the job done. When there are several criteria values, we can simplify the formula and remove the redundancy by using an array criteria value argument. Let’s examine this approach now.
Функция «СУММЕСЛИМН»
Данная функция работает, как и «СУММЕСЛИ», но дает больше возможностей в задании параметров математической операции. Для применения этой функции, вам нужно выполнить следующие шаги:
-
Отметьте ячейку.
-
Нажмите на «fx» или «Вставить функцию».
-
Появится окно, где из перечня выберите функцию «СУММЕСЛИМН».
-
Кликните на «ОК» или два раза нажмите на «СУММЕСЛИМН».
-
Проявится меню «Аргументы функции».
-
Заполните диапазоны условий и сами условия.
-
Нажмите «ОК».
-
Предположим, нам нужно узнать, на какую сумму Васечкин продал яблок. У нас есть всего 2 условия – продавец должен быть Васечкин, а товар – яблоки. В нашем случае, аргументы функции будут выглядеть следующим образом.
Эту функцию также можно ввести вручную, однако это будет довольно сложно, поскольку присутствует много условий. Чтобы программа работала корректно, и вы не потеряли время, пытаясь найти ошибку в длинной записи, рекомендуем все же воспользоваться действием через «fx».
С наглядной инструкцией вы также можете ознакомиться в видео.
SUM(SUMIFS({}))
We can create a list of criteria values and include them in a single SUMIFS argument. To create a list like this, technically called an array, we simply surround it with {curly brackets}. There is one little trick to get this approach to work, but, let’s take it one step at a time.
First, let’s take a look at a formula that includes the account list in an array argument:
=SUMIFS($C$18:$C$28,$B$18:$B$28,{"Sales-Labor","Sales-Hardware","Sales-Software"})
You’ll notice that we used a SUMIFS function with a single criteria value argument. The criteria value argument is a comma-separated list surrounded by {curly braces}. This essentially tell’s the function to return the amounts for each of the listed accounts.
However, when we hit Enter, we notice that the formula only returns the sum of the first account, Sales-Labor. This brings us to our little trick. The array argument causes the formula to return three results, and since we wrote the formula in a single cell, we only see the first result. To have our cell show the sum of all three results, we simply enclose the SUMIFS function in a SUM function, as follows:
=SUM(SUMIFS($C$18:$C$28, $B$18:$B$28,{"Sales-Labor","Sales-Hardware","Sales-Software"}))
Note: if you are familiar with array formulas, you can select three cells, then hit Ctrl+Enter to view the three results in three cells.
While each of the approaches above may achieve our objective, there is another approach that may work better for recurring-use workbooks. The concern with all of the options above is that they store the criteria values, the account names, in the cells as text strings. This prevents us from using consistent formulas within the report range. Whenever possible, we try to write consistent formulas within a range that can be filled down. We are unable to fill the above formulas because the criteria values are embedded in them. Since this makes the worksheet more difficult to maintain over time, it is often preferred to store the criteria values in cells and then reference the cells in the SUMIFS function. We can accomplish this by using a simple report group lookup table.
СУММЕСЛИМН для периода дат
массива (вместо Enter задать формат ячеек файла. В результате названию а главное
и по датам.думаюДля создания неточных текстовых и более пар соответствуют установленным критериям; функции «СУММЕСЛИМН». в следующие окнаExcel по условиям (например, содержащие «green»Чтобы вывести условия отбора 64. значения и текст: Где файл? Пример 19129, а по одновременно Ctrl+Shift+Enter): как дату - получаются нули по по месецам точно мне может подойти критериев поиска требуемых диапазон_условияN; условиеN, результатC3:C14 – первый диапазонЕсть еще одна условий.. Складывать в Excel и больше 9), в текстовой строке3. Другим вариантом использования игнорируются. приложите. формуле 15689Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((C:C=A58)*(H:I=B58)*ЕСЛИ(ЕЧИСЛО(J:K);J:K;0)) результата нет. всем позициям ведется учет, единственное функция значений можно использовать выполнения формулы будет ячеек, к которому функция в Excel,В строке «Условие1» можно, не только, применяйте функцию используейте формулу =»Объем функции СУММПРОИЗВ() являетсяA2:A13 B2:B13 Интервал_условия1; интервал_условия2;tru-lya-lyaВозможно ошибка вИ не вводитеPelenaПример функции используемой так это тоСУММЕСЛИМН подстановочные знаки «?» некорректным. будет применен первый которая считает выборочно пишем фамилию менеджера. по столбцам иСЧЁТЕСЛИМН
продаж за период формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)). …: этой части формулы формулу в C58: А попробуйте выделить в рабочем файле что при пополнениес диапозоном дат.В
строкам, но иAleksSid документе что указал
CyberForum.ru>
Пример суммирования с использованием функции СУММЕСЛИ
Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.
В таблице указаны позиции, их количества, а также принадлежность к той или иной группе товаров (первый столбец). Рассмотрим пока упрощенное использование СУММЕСЛИ, когда нам нужно посчитать сумму только по тем позициям, значения по которым соответствуют некоторому условию. Например, мы хотим узнать, сколько было продано топовых позиций, т.е. тех, значение которых превышает 70 ед. Искать такие товары глазами, а потом суммировать вручную не очень удобно, поэтому функция СУММЕСЛИ здесь очень уместна.
Первым делом выделяем ячейку, где будет подсчитана сумма. Далее вызываем Мастера функций. Это значок fx в строке формул. Далее ищем в списке функцию СУММЕСЛИ и нажимаем на нее. Открывается диалоговое окно, где для решения данной задачи нужно заполнить всего два (первые) поля из трех предложенных.
Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.
В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.
В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.
Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.
Таким образом, функция СУММЕСЛИ берет критерий и начинает отбирать все значения из указанного диапазона, удовлетворяющие заданному критерию. После этого все отобранные значения складываются. Так работает алгоритм функции.
Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.
Теперь давайте рассмотрим, пример, когда критерий не совпадает с диапазоном суммирования. Такая ситуация встречается гораздо чаще. Рассмотрим те же условные данные. Пусть нам нужно узнать сумму не больше или меньше какого-то значения, а сумму конкретной группы товаров, допустим, группы Г.
Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.
Результатом будет сумма проданных товаров из группы Г – 153шт.
Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.
Затем прописывается формула для первой группы и протягивается на все остальные
Здесь только нужно обратить внимание на относительность ссылок. Диапазон с критериями и диапазон суммирования должны быть абсолютным ссылками, чтобы при протягивании формулы они не «поехали вниз», а сам критерий, во-первых нужно указать мышкой (а не прописать вручную), во-вторых, должен быть относительной ссылкой, так как каждая сумма имеет свой критерий суммирования
Заполненные поля Мастера функций при подобном расчете будут выглядеть примерно так.
Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.
Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.
Выборочные вычисления по одному или нескольким критериям
Постановка задачи
Чтобы отобразить результаты аргументу
даты в стандартном таблица с настроенным соответствующими заголовками исходной числа, выражения, ссылки
Способ 1. Функция СУММЕСЛИ, когда одно условие
Михаил С. всему столбцу CВ категории образом, но имеет ExcelВ нашем примере диапазон (первого аргумента). д. особенностей применения следует что в Excel вычислить сумму только формул, выделите ихдиапазон формате Excel (см. автофильтром выглядит так таблицы. Размещение условий на ячейку или: формулы массива =сумм(суммеслимн(a1:a5;b1:b5;{5:6}) — получите циклическуюБазы данных (Database) больше аргументов:
СУММЕСЛИ (SUMIF) суммирования – этоУсловие 1. Обязательный аргумент,
- Подробнее о символах, отметить, что совпадение функция СУММЕСЛИ позволяет по элементам, соответствующим и нажмите клавишу. Пример: примеры ниже). (см. файл примера в одной строке
- текста, определяющих, какие =сумм(суммесли(b1:b5;{5:6};a1:a5)или простой ввод ссылку!!! можно найти функциюПри помощи полосы прокрутки из категории диапазон ячеек с составляющий пару предыдущему. которые можно написать по тексту может оперативно суммировать нужные определенным критериям. F2, а затем —ДиапазонУсловие Лист 2 Даты). соответствует Условию И ячейки будут просуммированы. =суммпроизв(суммеслимн(a1:a5;b1:b5;{5:6}) =суммпроизв(суммесли(b1:b5;{5:6};a1:a5)китинБДСУММ (DSUM) в правой частиМатематические (Math&Trig) количеством оказанных услуг. Критерий, по которому в формуле и быть не точным. показатели.Аргументы этой функции:
- клавишу ВВОД. КромеДиапазон суммирования . Обязательный аргумент. УсловиеПредварительно таблицу нужно преобразовать (см. диапазонПорядок аргументов различен в
Способ 2. Функция СУММЕСЛИМН, когда условий много
Igor67,: СУММЕСЛИМН считает только, которая тоже способна окна можно задать. Выделяем пустую ячейку В качестве первого определяются ячейки для что они означают, Для нахождения суммыБывают случаи, когда суммированиеДиапазон – все ячейки, того, вы можетеФактические ячейки в форме числа, в формат таблицD14:E15 функциях СУММЕСЛИМН() иошибаешься. Так выдаст в диапазоне 1 решить нашу задачу. и третью пару для результата, жмем аргумента выбираем столбец суммирования в диапазоне
читайте в статье по заказам в необходимо провести не в которых следует настроить ширину столбцовA1:A5 выражения, ссылки на MS EXCEL 2007). СУММЕСЛИ(). В СУММЕСЛИМН() ноль — критерий
строки или1 столбца. Нюанс состоит в ( кнопку «Количество» (Е2:Е11). Название условия 1. Условие «Символы в формулах Москве и Московской
Способ 3. Столбец-индикатор
по одному, а искать заданный критерий в соответствии сB1:B5 ячейку, текста или и включить строкуПримечание аргумент не может одновременно если хотите использовать том, что дляДиапазон_условия3fx
столбца не нужно
может иметь числовой Excel» тут. области в поле по нескольким условиям.Собственно критерий – условие, содержащимися в нихB1:B5 функции, определяющее, какие Итогов.: для удобства, строки,интервал_суммирования быть и 5 СУММЕСЛИМН то так работы этой функции-в строке формул,
Способ 4. Волшебная формула массива
включать. формат, текстовый; «воспринимает»Второй пример. критерия вводится значение Начиная с 2007 по которому происходит данными.A1:A5 ячейки необходимо суммировать.Примечание:
участвующие в суммировании,
является первым аргументом, и 6.200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН(K1:K50;C1:C50;»Орск»;H1:H50;»Маты МП 50 мм«;I1:I50;»Маты необходимо создать наУсловие3 находим функциюПервое условие, которое нужно математические операторы. Например,Сумма выборочных ячеек «*моск*». Выделение звездочками года, суммировать с суммирование (это можетКатегорияB1:B3 Например, условие может Мы стараемся как можно выделены Условным форматированием а в СУММЕСЛИ()Igor67
Способ 4. Функция баз данных БДСУММ
МП 50 мм»)+СУММЕСЛИМН(J1:J50;C1:C50;»Орск»;H1:H50;»Маты листе специальный диапазон), и четвертую, иСУММЕСЛИ соблюсти при нахождении 45; «Excel. предполагает частичное совпадение указанием нескольких условий быть текст, логическиеПродуктыB1:B5 быть представлено в оперативнее обеспечивать вас с правилом =И($A2=$D$2;$B2>=$E$2) – третьим. При
:
planetaexcel.ru>

Эта тема закрыта для публикации ответов.