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

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

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

cond_sum1.png

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

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

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

cond_sum2.png

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

cond_sum3.png

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

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

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

cond_sum4.png

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

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

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

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

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

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

cond_sum5.png

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

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

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

cond_sum6.png

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

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

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

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

cond_sum7.png



Страницы: 1  2  
13.05.2016 20:24:41
Здравствуйте, Николай Павлов.
Хочу вас поблагодарить, за прекрасный сайт с подробными комментариями и разъяснениями, очень выручили.
Ради этого и зарегистрировался.
Большое спасибо!!!
12.09.2016 21:32:41
Спасибо!
01.08.2016 21:01:16
Добрый вечер! Очень нужна Ваша помощь: Проблема заключается в следующем Напротив фамилии предпринимателя есть жёлтая ячейка.В неё надо суммировать все значения под ней (снизу),но только до следующей жёлтой ячейки.Проблема в том ,что суммированых чисел может быть и три и четыре и пять и т.д . Стандарт здесь не подходит.Ставить формулы в каждую жёлтую ячейку да ещё и разные - это не вариант, так как строчек очень много. Пробовала через ЕСЛИ, но что-то не очень получается. Буду благодарна за помощь
12.09.2016 21:33:21
С цветом обычные формулы работать не умеют. Так что такое только программированием макросов через VBA.
08.09.2016 00:35:18
Похоже тему забросили...
Но все же - может кто подскажет..

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

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

???
12.09.2016 21:32:25
Считаете все заказы Григорьева для Копейки.
Считаете все заказы Григорьева для Метро.
Складываете два числа :)
Добрый день!

Извините, если пишу не в ту тему, а вопрос следующий!
Есть база данных (наименование/группа/подгруппа/продажи).
Необходимо сделать отдельную таблицу, в которой будет сумма продаж в разрезе каждой подгруппы, только не вся, а 80% от всей суммы.
(этакий АВС анализ скажем так). И далее в другом столбце необходимо количество наименований, сумма которых составляет эти 80%.
Николай, спасибо за совет!

Только вот мне это необходимо сделать в разрезе каждой подгруппы, а не просто списка наименований.
Если я все правильно поняла, то со сводной таблицей придется делать много ручных манипуляций.  
Здравствуйте! Очень нужна помощь.
Возможно ли с помощью суммеслимн суммировать диапазон, одно из условий = за конкретный месяц.
допустим:
Условие 1=Январь (столбец дата в формате 03.01.2016)
Условие 2=Петров (столбец Ф.И.О.)
Условие 3 =ГСМ (столбец статья расхода)

Вот эта формула считает за Январь: =СУММПРОИЗВ(--(МЕСЯЦ(В1:В3)=1);С1:С3). А надо применить ещё условия отбора. Вставляю в суммеслимн (МЕСЯЦ(В1:В3)=1) как условие - не работает.
15.11.2016 19:32:17
Добрый день! Спасибо за форум!
У меня вопрос, как суммировать диапазон, исключив ячейки с ошибкой?
У меня массив постоянно обновляющихся данных компаний по разным секторам, и если я хочу суммировать данные по одному сектору я должна сначала обозначить диапазон отбора сектора, установить критерий (определить нужный сектор), потом задать диапазон суммирования, и вот на этом этапе исключить ошибки (некоторые компании данные не предоставляют)
Я так поняла это будет SUMIFS(диапазон, критерий1, диапазон суммирования, И ВОТ ТУТ его надо исключить ошибку! )
Либо  =AGGREGATE(9 (это функция суммирования, 6(исключение ошибки, диапазон)  - но в этом варианте возникает вопрос как отобрать по сектору предварительно?

Спасибо!  
04.02.2017 18:46:37
Добрый день, прошу проконсультировать по следующей теме :
есть столбец ячеек, в самом низу которого Стоит ячейка с формулой суммирования ячеек этого столбца (12 строк по числу месяцев в году),
в каждой из строк слева от этого столбца производится ряд вычислений и изначально в крайнем столбце выводится некое значение вычислений с использованием в расчётах некой константы.Это сильно загружало таблицу и она была труда в восприятии - путём составления условий (при которых цвет чернил совпадает с цветом фона) я убрал лишнюю ВИДИМУЮ загрузку в каждой из ячеек столбца, но сумма всех ячеек столбца постоянно показывает сумму всех ячеек (12 ячеек с учётом некой константы, используемой в расчётах)
Суть вопроса- как сделать так, что бы в ячейке с формулой суммы этого столбца выводился результат суммирования только тех ячеек,  в строках которых появилась хотя бы одна ячейка с внесёнными данными ?
в упрощённом виде имеем 12 строк ( по числу месяцев) в три столбца : приход + некая постоянная величина, расход, остаток - в ячейке сумма остатков надо иметь только факт по текущему месяцу! А остальные, не заполненные строки учитываться не должны.
Вот ссылка на таблицу https://yadi.sk/d/qojfWrhx3Cqpf3

Я говорю о ячейке H15 и в данном случая должно быть значения , как в январе - 8 999,00
Спасибо.
13.02.2017 21:10:50
Добрый день. Подскажите пожалуйста кто знает. У меня ситуация несколько иная, а именно, ячейки с критериями и суммируемые ячейки находятся в одной строке. Каждая строка состоит из 5-ти этапов (оплата товара, привоз, поиск покупателя, демонстрация покупателю, продажа), каждый этап оплачивается отдельно и каждый этап может выполнять разный исполнитель. Исполнитель на каждом этапе выбирается из списка. Выглядит так:
A1-исполнитель (покупка), B1-З/П (руб.), C1-З/П (долл.), D1-исполнитель (привоз), E1-З/П (руб.), F1-З/П (долл.), G1-исполнитель (поиск покупателя), H1-З/П (руб.), I1-З/П (долл.) и т.д.
Нужно просуммировать зар.платы в рублях и в долларах по каждому исполнителю.
Как это можно сделать?

P.S. Сейчас формула выглядит так: ЕСЛИ($M15="Руслан";N15;0)+ЕСЛИ($P15="Руслан";Q15;0)+ЕСЛИ($S15="Руслан";T15;0)+ЕСЛИ($V15="Руслан";W15;0). А это всего лишь часть формулы, там ещё есть проверка на "дурака".
17.02.2017 14:05:00
Добрый день.
Подскажите а как правильнее прописать формулу, если в способе № 4 будет не одно значение, а несколько в критерии F1:G4 и функцию прописывать с столбце H:
1. Копейка Григорьев -  =БДСУММ(A1:26;D1;F1:G2)
2. Рамстор Петров -    =БДСУММ(A1:26;D1;F1:G3)-H2
3. Ашан Чадов -           =БДСУММ(A1:26;D1;F1:G4)-H2-H3
11.03.2017 19:01:08
Здравствуйте! У меня такой вопрос есть по данной теме: Я планирую Бюджет Автозапчастей в инвестиционном проекте длительностью 10 лет. В частности в данном Бюджете я рассчитываю месяц в котором будет происходить замена шин на машине. Формулу расчета я привожу ниже:

=ЕСЛИ((СУММ($G$14:G$14)-СУММЕСЛИ($G$3:G$3;"*квартал";$G$14:G$14)-СУММЕСЛИ($G$3:G$3;"*месяцев";$G$14:G$14))>G$10*(СУММ($F$16:F$16)+1);1;0)

В строках $G$3:G$3 указана периодичность года (месяц, квартал, 6 месяцев, 9 месяцев, 12 месяцев), в строках $G$14:G$14 указан плановый пробег автомобиля, в строках G$10 указана эксплуатационная норма пробега шины, в строках $F$16:F$16 показывается коэффициент выполнения замены шин. Если суммарный пробег больше эксплуатационной нормы, то коэффициент равен 1, в противном случае 0.

Но не знаю как сделать так чтобы применяя формулу на следующем листе она учитывала произведенные замены шин из таблицы предыдущего листа? Таких листов у меня 10.
16.03.2017 18:39:19
Добрый день!
Подскажите пожалуйста, можно ли без макроса, просто формулой подсчитать сумму красного цвета цифр в строке таблицы
именно не отрицательных, а просто красного цвета.
Этим цветом отмечены ночные смены в графике отработанного времени.
30.03.2017 11:24:16
обнаружил, что СУММЕСЛИМН не понимает значения "<" и ">" когда ссылаешься на ячейку :(((
Т.е. нужно просуммировать по критерию, который > или < значения в определенной ячейки. (т.е. ссылаемся на саму ячейку)

допустим
sum range - A:A
criteria range1 - B:B
criteria - "<C16"

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

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


Приходится спасаться функцией SUMPRODUCT
04.04.2017 08:07:10
Надо писать не "<C16", а "<"&C16 - и все будет работать ;)
04.04.2017 09:48:36
Николай, спасибо большое :) а я голову ломал, используя менее удобную для этого функцию SUMPRODUCT
21.04.2017 13:27:38
Почему надо писать с "&" не понятно, может есть свои секреты, что мы могли открыть себе много интересного и облегчение своей работы, так моя среднестатистическая логика говорила так, пока не зашел к вам на сайт и просто выяснил, что необходимо писать с "&" при использовании функции СУММЕСЛИ, а вот в функции СУММПРОИЗ как обычно. Может вы объясните в чем проблема или разница. Буду благодарен за ваш ответ!
22.04.2017 09:59:09
У функции СУММЕСЛИ условие должно быть текстом, поэтому происходит склейка символом &
У функции СУММПРОИЗВ условие - логическое значение, поэтому склейка не нужна.
24.04.2017 21:43:43
Добрый день! Николай, хочу поблагодарить за ваши труды, очень помогают. Вопрос такой,из скачаного файла, в функции =СУММЕСЛИМН(D2:D26;B2:B26;"Григорьев";A2:A26;"Копейка"), можно добавить чтоб подчитывал  "копейка" и "Метро"., Если добавить еще один диапазон, то результат 0. В строку "условий" прописывал оба варианта не находит. Без вас не разберусь.
31.05.2017 12:27:37
Николай, извините, что возможно пишу не в той теме, но не нашла подходящую
Подскажите формулу для вычисления по строке количества ячеек начиная с ячейки где впервые появляется значение больше нуля. И мне нужно, чтобы работала данная формула для разных строк, т.е. в каждой строке может в разной ячейке появиться впервые значение.
Заранее спасибо.
январьфевральмартапрельмайиюньиюльавгустсентябрьоктябрьноябрьдекабрьянварьфевральмарт
100000000000
50000000000000
2000000000
январьфевральмартапрельмайиюньиюльавгустсентябрьоктябрьноябрьдекабрьянварьфевральмарт
100000000000
50000000000000
2000000000
10.07.2017 11:06:48
Николай, здравтсвуйте.

Столкнулся с проблемой, не могу найти на просторах сети ответ внятный.
Ситуация следующая, у меня есть 4 столбца (бренд, модель, кол-во, сумма). Речь о шинах и дисках.
1С мне выгрузил не общее количество проданных за месяц моделей, а набор данных. К примеру, одну и ту же модель он показывает несколько раз и таким образом получается список "модель1, кол-во 2", "модель1, кол-во 4" итд. У меня в файле 2000 строк. Если полностью руками делать, то проще вообще не делать.

Подскажите, пожалуйста, как посчитать количество проданных моделей?)
Файл на яндекс диске  
10.07.2017 11:29:06
Николай, добрый день!
Большое спасибо за ваши труды. У меня вопрос:
Какую формулу использовать, если нужно посчитать суммы для всех менеджеров ( но в моем случае их тысячи).
То есть
Петров - сумма всех заказов
Григорьев - сумма всех заказов
Лапин - сумма всех заказов
и т.д.

У меня есть таблица на сто тысяч строк, выглядит примерно так:
№            Значение

400344 66921,56
400344 567,35
400344 0,00
400344 0,00
400344 6779,09
400344 8661,66
400344 32878,10
400344 3650,13
400344 10062,17
400344 6746,75
400344 92813,92
400344 5905,12
400344 0,00
400344 22789,31
400344 9042,64
400344 0,00
400390 0,00
400390 30715,34
400390 31900,82
400390 11946,15
400390 67114,96
400487 72851,82
400487 2897,61
400558 30032,43
400569 0,00
400569 50454,52
400569 13684,72
400628 0,00
400682 5053,60
400733 5959,27
400733 17121,40
400733 42550,14



Нужно привести ее к сгруппированному виду, т.е.
400344 - сумма всех значений
400390 - сумма всех значений и т.д.

Подскажете?
Спасибо!
10.07.2017 12:08:27
Я выше писал, у меня по сути тот же вопрос.
22.07.2017 11:35:32
При таких размерах таблицы лучше решать проблему не формулами, а с помощью Сводной Таблицы (Pivot Table) - гораздо легче будет.
20.07.2017 07:43:05
Николай, а если у меня есть, к примеру, "Петров", "Сидоров", etc... также данные по поступлению денег на счет (ну допустим) и снятию со счета, как мне посчитать разницу по каждому человеку отдельно.
ДатаНеделяМесяцИмяПоступлениеСнятиеОстаток
19.07.2017297Петров10 000,00
19.07.2017297Сидоров20 000,00
19.07.2017297Иванов1 000,00
19.07.2017297Петров3 000,00

Спасибо
22.07.2017 11:37:49
А в чем проблема? Посчитать с помощью СУММЕСЛИ сумму по менеджеру по столбцу Поступление. Потом то же самое по столбцу Снятие. И вычесть из первого второе. Нет?
23.07.2017 16:39:02
Николай, спасибо за ваш ответ.
Возможно, я не совсем корректно изложил свою мысль: можно ли создать форму, которая будет автоматически считать остатки на счету у Иванова, Петрова, Сидорова... при условии, что я буду ежедневно вводить новые данные по поступлениям/снятию средств со счетов.
26.07.2017 09:05:55
Превратите вашу таблицу в "умную" и тогда при дописывании новых платежей все формулы будут автоматически цеплять новые данные и пересчитываться на лету.
26.09.2017 14:24:42
Подскажите, пожалуйста, как смоделировать формулу, если нужно суммировать значения следующим образом:
А = В+С, если С=D, иначе А = В+D (при условии, что D не равно 0)
27.12.2017 07:43:13
У меня есть просьба для самых умных, дело в том что я хочу сделать в ексель задачки по математике для начальной школы, проблема в чем? я все сделал, кроме одного (файл прилагается), нужно в листе деление подобрать цифры так, чтобы при делении всегда получалось целое число,а то получаются всегда дробные. как это можно сделать? (если взять из отдельного списка цифр, нужно тогда подобрать два столбика цифр, при делении первого столбика на второй, чтобы получилось целое число, не дробное, типа 2,5; 5,6; 7,8)
и кстати, в ексель и не только при передвижении мышки вместо стрелки появляется круглая штука, и экран перемещается на произвольный другой конец листа, жутко мешает
06.02.2018 00:59:31
Николай, добрый день! Подскажите, Считаю продажи за определенный период ставлю формулу : =СУММЕСЛИМН(D:D;B:B;"> 31.08.15";B:B;"< 01.10.15"    Выдает результат 0, в чем ошибка? спасибо
19.02.2018 01:15:35
У меня почему то не получается протягивать функцию СУММЕСЛИ ? В каждой ячейке выдаёт одинаковый результат. ПРиходится использовать формулу массива.
30.05.2018 17:07:44
Доброго времени суток. Николай, если не сложно подскажите пожалуйста ответ, вот на какой вопрос: как сделать выборку значений из столбца D (Стоимости продажи), таким образом, чтобы сумма этих значений в итоге составляла ну к примеру 28 208$? Заранее благодарен за ответ.
26.07.2019 14:15:44
Добрый день, подскажите как создать выборочное суммирование по нескольким критериям из одного столбца?
Например диапазон суммирования А1:А30, а несколько критериев находятся в диапазоне В1:В30

Формулы не работают
={СУММ((В1:В30="синий")*(В1:В30="красный")*А1:А30)}

=СУММЕСЛИМН(А1:А30;В1:В30;"синий";В1:В30;"красный")

Мне необходимо, что бы суммирование происходило и по критерию "красный" и по критерию "синий"      
19.09.2019 20:43:49
Добрый вечер. Помогите, пожалуйста. У меня есть таблица с которой мне нужно извлечь данные в отдельную таблицу по каждом менеджеру его выполнену работу, а именно посчитать что он делал. то есть для каждого менеджера его ячейки суммировать, только проблема в том что в ячейках не цифры а слова. не могу подобрать формулу(
Страницы: 1  2  
Наверх