Выборочные вычисления по одному или нескольким критериям
Постановка задачи
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 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)
=ЕСЛИ((СУММ($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.
Подскажите пожалуйста, можно ли без макроса, просто формулой подсчитать сумму красного цвета цифр в строке таблицы
именно не отрицательных, а просто красного цвета.
Этим цветом отмечены ночные смены в графике отработанного времени.
Т.е. нужно просуммировать по критерию, который > или < значения в определенной ячейки. (т.е. ссылаемся на саму ячейку)
допустим
sum range - A:A
criteria range1 - B:B
criteria - "<C16"
Т.е. просуммировать значения в столбце А, для показателей в B, которые меньше чем значение в ячейке C16.....
При такой операции выдает всегда 0, но если подставить вместо С16, конкретное число, то результат выдает правильный.
Приходится спасаться функцией SUMPRODUCT
У функции СУММПРОИЗВ условие - логическое значение, поэтому склейка не нужна.
Подскажите формулу для вычисления по строке количества ячеек начиная с ячейки где впервые появляется значение больше нуля. И мне нужно, чтобы работала данная формула для разных строк, т.е. в каждой строке может в разной ячейке появиться впервые значение.
Заранее спасибо.
Столкнулся с проблемой, не могу найти на просторах сети ответ внятный.
Ситуация следующая, у меня есть 4 столбца (бренд, модель, кол-во, сумма). Речь о шинах и дисках.
1С мне выгрузил не общее количество проданных за месяц моделей, а набор данных. К примеру, одну и ту же модель он показывает несколько раз и таким образом получается список "модель1, кол-во 2", "модель1, кол-во 4" итд. У меня в файле 2000 строк. Если полностью руками делать, то проще вообще не делать.
Подскажите, пожалуйста, как посчитать количество проданных моделей?)
Большое спасибо за ваши труды. У меня вопрос:
Какую формулу использовать, если нужно посчитать суммы для всех менеджеров ( но в моем случае их тысячи).
То есть
Петров - сумма всех заказов
Григорьев - сумма всех заказов
Лапин - сумма всех заказов
и т.д.
У меня есть таблица на сто тысяч строк, выглядит примерно так:
№ Значение
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 - сумма всех значений и т.д.
Подскажете?
Спасибо!
Спасибо
Возможно, я не совсем корректно изложил свою мысль: можно ли создать форму, которая будет автоматически считать остатки на счету у Иванова, Петрова, Сидорова... при условии, что я буду ежедневно вводить новые данные по поступлениям/снятию средств со счетов.
у меня проблемы с формулой СУММЕСЛИ:
мне необходимо суммировать по критерию с одного диапазона, но если там пусто взять данные с другого диапазона. Думаю правильно изложил суть вопроса)
Как прописать формулу в моем случае?
Заранее благодарен!!!
А = В+С, если С=D, иначе А = В+D (при условии, что D не равно 0)
и кстати, в ексель и не только при передвижении мышки вместо стрелки появляется круглая штука, и экран перемещается на произвольный другой конец листа, жутко мешает
Например диапазон суммирования А1:А30, а несколько критериев находятся в диапазоне В1:В30
Формулы не работают
={СУММ((В1:В30="синий")*(В1:В30="красный")*А1:А30)}
=СУММЕСЛИМН(А1:А30;В1:В30;"синий";В1:В30;"красный")
Мне необходимо, что бы суммирование происходило и по критерию "красный" и по критерию "синий"
Возможно повторюсь, но всё же. Не могу тут найти ответ на свой вопрос.
Есть таблица с именами и датами (см. рисунок). В каждой дате есть несколько показателей (количество столбцов и показатели по каждой дате одинаковые). Задачка такая. Нужно найти для каждой фамилии или сумму или среднее значение или просто количество показателей за все числа.
То есть. Например есть показатель - КТУ. Нужно сделать выборку по всем числам и вывести средний показатель. По каждому человеку (то есть по каждой строчке). Есть показатель - Отработано часов. Нужно найти общую сумму и количество ячеек, в которых есть цифры. И тд по всем показателям.
Простите, если я был невнимателен и такая задачка тут уже описана. Но я так и не нашел.
В Exel 2010 это можно сделать судя по видео в ютуб. Заранее благодарю!)
А есть ли в Экселе аналог суммесли для произведения? Я что-то не нашёл. И как можно перемножать ячейки из выделенного столбца, попадающие под заданное условие?
можно ли посчитать количество строк, где есть вхождения слова "ааа" или слова "ббб" или слова "ввв" и одновременно в которых нет слова "ггг" или слова "ддд" или слова "еее"?
как сделать по И понятно, а как по или - нет.
Относительно выборочных вычислений удается найти решение только для одного конкретного вычисления при заданном или нескольких заданных критериев.
В моей задаче необходимо найти решение для более одного вычисления (результат каждого из которых будет записываться в разные ячейки), где каждый новый критерий отвечает за каждое новое вычисление - что-то похожее на промежуточные итоги:
В столбце А критерии, в столбце В - непрерывный список чисел, по одному на каждую ячейку. Нужно, чтобы из столбца В суммировались только те ячейки, которые соответствуют заданному критерию в столбце А, т.е. как только критерий меняется, происходит следующее суммирование, не связанное с первым.
Необходимо это для того, чтобы завернуть все в одну формулу с указанием полных диапазонов А:А и В:В и размножить одну формулу по ячейкам D, где в каждой следующей ячейке, будет отображаться только тот результат вычислений, который соответствует определенному критерию из А.
Формулы, которые сработали, но полностью задачу не решили:
=суммесли(А:А;"к1";В:В) - при подстановке формулы в D1 считает корректно, но в D2 я уже не могу просто протянуть ее, придется менять критерий на "к2".
=если(или(А:А;"к1";(А:А;"к2"));сумм(В:В);(" ")) - результатом получился подсчет всех ячеек В в одну сумму.
Подскажите, возможно ли это сделать?
Есть файл с формулой
=СУММЕСЛИМН(КолПоз;Группа;B4;Статья;A4). Группа и Статья это наименования столбцов.
Как в данной формуле прописать другие данные? Заменить столбцы? Не могу понять, помогите, пожалуйста.