Выборочные вычисления по одному или нескольким критериям
Постановка задачи
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 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)
Несколько раз задавался вопросом: для чего же тогда нужна функция "СУММЕСЛИ", если "СУММЕСЛИМН" изначально позволяет задавать от 1 до 127 условий и по определению выглядит привлекательнее "СУММЕСЛИ", которая поддерживая всего 1 условие?
С какой тогда целью разработчики оставили "СУММЕСЛИ" в Экселе?
Ответ на данный вопрос получил на практике, когда в очередной раз перечитывал справку.
Дело в том, что диапазон суммирования и диапазон условий в "СУММЕСЛИ" могут не совпадать. Они могут быть разными! Что очень может выручить в ситуации, когда суммируем диапазон по критерию, который берем из другого диапазона, отличного по числу ячеек.
К сожалению, так сделать в СУММЕСЛИМН не получится. Там диапазоны условий должны совпадать по количеству ячеек с диапазоном суммирования
В любом случае, спасибо за уточнение - важный момент.
если и делать разные диапазоны в СУММЕСЛИ (к примеру на таблице выше),
то важно выбрать весь диапазон с условиями (Range) в таблице (т.е. высоту диапазона), данные которых мы хотим суммировать, а в качестве диапазона суммирования (Sum_range) достаточно выбрать первую ячейку в столбце или больше (обязательно выбирать начиная с первой ячейки).
PS. выяснил, проверяя ваши комменты.....
Столкнулся с такой необходимостью просуммировать по двум критериям, находящимся в одном и том же диапазоне.
Заранее сапасибо!
У меня 20 условий в одном диапазоне и 5 в другом, формулы получаются очень длинными. Пишу макрос, но выдаются ошибки. В basic формулы разбиваются амперсандом, кавычками и другими символами. Пыталась вручную редактировать, все равно не получается. Да и в basic не сильна(((
Приходится формулы разбивать, делать предварительный расчет в свободных ячейках, и потом только суммировать полученные значения.
Честно говоря, что только я уже не перепробовала, не получается. Может есть какой-то выход?))
Кустарный способ, который я нашел - это отфильтровать столбец по СОДЕРЖ "*слово1* ИЛИ "*слово2*", а потом суммировать с зажатым CTRL, но что делать, когда таблица из 10000 строк.
Вот вам тема к прочтению
Скажите пожалуйста, а как можно вместо диапазона внести имя присвоенное данному диапазону...или же проблема в том что диапазон постоянно расширяется и нужно чтобы диапазон формулы тоже расширялся.
Николай, подскажите, пожалуйста. Я уже давно пользуюсь суммированием через =СУММПРОИЗВ((.....=..)*(.....=..)).
Такая формула вводится обычно - не как формула массива, если использовать просто СУММ. Чем различаются эти два способа суммирования по условию??? Может из них какой-то существенно проще для "железа"??? Заранее спасибо за помощь.
диапазон суммирования - столбец с деньгами (или что вы там складываете)
диапазон условия 1 - столбец с датой
условие 1 - ">="&A1
диапазон условия 2 - столбец с датой
условие 2 - "<="&A2
диапазон суммирования - столбец с минутами
диапазон условия 1 - столбец с датой и еще 2 критерии
условие 1 - "<="120 то =0
диапазон условия 2 - столбец с датой и еще 2 критерии
условие 2 - ">"120 то сумма по условию
Примерно такая ситуация
Во-вторых, я бы просто считал суммарное количество минут за сутки и потом с помощью функции ОСТАТ вычислял остаток от деления этого количества на лимит (для обнуления).
А как, например, так сделать, чтобы функция заносила последнее значение по определенному продукту на определенную (актуальную) дату.
То есть:
Столбец "А" - код продукта
Столбец "E" - дата
Столбец "F" - количество на начало периода (сюда должно попадать последнее значение по определенному виду продукта из столбца "I" )
Столбец "I" - количество на конец периода.
Продукта много, дат тоже....Сижу, ломаю голову уже не один день, но пока найти решение не могу, вот и решила обратиться...
Буду очень признательна за помощь! Спасибо..
Какой функцией можно реализовать суммирование текстовых значений столбца в одну ячейку, так, чтобы значение (текст) каждой ячейки начинался с новой строки? Пробовал это реализовать функцие СЦЕПКА, но как-то не вышло =)
Подробнее. Есть столбец (A2:A100), принадлежащий таблице A2:K100. Начиная с A5 и заканчивая A100 редко попадаются текстовые значения. При этом в одной ячейке может быть два текстовых значения. Второе значение начинается с новой строки (alt+enter). Необходимо, чтобы в ячейку A1 выписались все текстовые значения встреченные в столбце. Каждое значение - с новой строки. Если это возможно только с помощью макроса, я был бы вам крайне признателен, хотя бы намекнуть в какую сторону двигаться в плане алгоритма. Благодарю заранее.
У меня возникла следующая проблема: необходимо просуммировать определенные значения в заданном диапазоне, сам диапазон - это некие различные числовые значения, соответствующие каждой дате с 1 января по 31 декабря. Получаем следующую картину: 1-й столбец - это даты, 2-й столбец - это соответствующий дате номер недели в году, 3-й столбец - это различные значения. Так вот, необходимо просуммировать первое и последнее значение в каждой неделе по всему году. Помогите, пжл, кто может...
я чтобы каждый раз не возиться с формулой сделал ссылки на две ячейки E1 и F1- очень удобно
{=СУММ((A1:A10=E1)*(B1:B10=F1)*C1:C10)}
Подскажите, что я неправильно делаю когда хочу просуммировать по нескольким критериям и в одном условии идет подсчет ячеек с вхождением отдельных символов.
Использую формулу массива =СУММ(ЕСЛИ((.....=..)*(.....="*"&"искомые символы"&"*")).
Но она не работает если условий не одно, а несколько.
Подскажите как решить задачку. В таблице два столбца. Первый "А" - числовые значения, второй "В" - текст (может быть одно слово, а может быть и 100 в одной ячейке). Нужно прописать формулу, чтобы она искала во втором столбце "В" совпадение текста (может быть и часть слова, как аналог возможности в автофильтре значение "содержит"), и в результате выдавала сумму значений ячеек столбца "А", когда она будет находить нужное значение в столбце "В".
Надеюсь понятно объяснил.
=СУММЕСЛИ(B:B;"*ваштекст*";A:A)
{=SUM((SUBSTITUTE(B:B;"часть слова";"")<>B:B)*IF(ISNUMBER(A:A);A:A;0))}
{=СУММ(ЕТЕКСТ(A1:A4)*B1:B4)}
=СУММЕСЛИМН(D2:D26; B2:B26; "Григорьев"; D2:D26; ">5000")
Подскажите пож-та, какой формулой нужно воспользоваться, если необходимо сложить числа (C2-C32) по 3 показателям из одного диапазона(B2-B32)...нужно это сделать одной формулой. Заранее благодарю.
Определите ячейки по вашим показателям))
Примерно так, кажется...
Спасибо.
Если требуется подсчитать количество непустых ячеек в столбце Сумма, с условием по полю Заказчик. Как это просто сделать? Сломал голову )
СчетЕсли вроде бы не дает возможности накладывать условие на другой столбец.
Заранее спасибо!
Первый вариант:
Спасибо.
чека
Есть столбец, в котором содержатся данные различного формата (в основном числовые, но также есть текстовые записи). Необходимо посчитать сумму абсолютных значений по определенным ячейкам данного столбца. В моем случае, я решил использовать управляющий столбец, в котором содержится запись "%", если значение в данной строке необходимо учитывать в расчете суммы абсолютных значений. Пример этих двух столбцов:
Приведу в пример формулу, результат которой возвращает данное значение:
=СУММ(((A2:A11)="%";)*(B2:B11)*ЗНАК(B2:B11)*НЕ(T(B2:B11)))
Не видя файла точнее сказать не смогу, но рыть надо в этом направлении.
Прошу помощи.
Нужно отбор производить по наличию числа в сумме, например:
в столбце А указаны 9-значные числа (балансовые счета)
нужно поэтому столбцу сделать отбор строк
по ячейкам содержащим например число 1411
чтоб просуммировать значения в другом столбце (суммы)
я пробовал через критерий *1411"
но как я понял
критерий на содержание по числам не работает
только текст
подскажите пожалуйста, как по числам ставить критерий на содержание числа (однозначного и выше) в другом числе со значностью выше.
например
20 в числе 1220698
при применении к номеру счета со значением - 141240000592
в ячейке показало - 1,4124E+11
как избежать такое представление?
в этом представлении условие выбора не сработало.
про символ @ в excel - в инетернете ничего не смог найти.
Соответственно, и функции СЧЁТЕСЛИ и СУММЕСЛИ потом тоже сработать нормально не могут.
Не видя вашего файла, не смогу сказать - почему.
Условие только одно - значение напротив фамилии работника. Эти значения на всех листах надо "пофамильно" просуммировать.
Формула выдает ошибку (#ЗНАЧ!)
Формулы без условий (если) - работают хорошо.
Но мне надо высчитать среднее значение по одной ячейки в разных листах, нули при этом брать в расчет не нужно (тогда выйдет не верный результат вычисления среднего значения).
Можете что-то подсказать?
Второй день не могу найти решение: требуется суммирование по нескольким условиям, но одно из условий не простое: если ячейка (строка) содержится в другой строке. Различные варианты CSE функций ПОИСК, ПОИСКПОЗ не "прошли", ошибки выдавали, пример с комментариями по ссылке. Можно решить?
Применение: из отчета по продажам, выгруженном из 1С, получить сумму продаж по наименованию, если оно содержится в списке, запихнутом в 1 ячейку в качестве строки.
Вот формула из примера =СУММЕСЛИМН(D: D;E:E;">31.1.2014";E:E;"<1.03.2014" ) так работает, но если в качестве условия задать ячейки : =СУММЕСЛИМН(D: D;E:E;">L1";E:E;"<L2" ) - дает 0.
диапазон условия 1 - столбец с датой
условие 1 - ">="01.01.15
диапазон условия 2 - столбец с датой
условие 2 - "<="&07.01.15
но таким способом нужно подсчитать много ячеек, а каждый раз прописывать в формуле руками даты, или создавать дополнительно ячейки с датами не совсем удобно да и долго, вот собственно и интересно, можно ли условием сделать цвет
Прошу помочь с формулой для суммирования.
условия следующие: на одном листе столбцы, которые будут заполняться по мере оформления заказа
Спасибо за прекрасный сайт, очень много полезного, реально помагает
Подскажите пожалуйста, как можно заменить функции СУММЕСЛИ и СУММЕСЛИМН на АГРЕГАТ или что-то другое, чтобы при выборке из умной таблицы и фильтрации по ней не учитывались данные из скрытых строк? с СУММЕСЛИ все работает, но вот при применении фильтра по некоторым параметрам - результат выборки не меняется, т.к. эти функции считают все подряд, видимое и скрытое... Спасибо
Хочу вас поблагодарить, за прекрасный сайт с подробными комментариями и разъяснениями, очень выручили.
Ради этого и зарегистрировался.
Большое спасибо!!!
Но все же - может кто подскажет..
Если задачу изменить:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
на:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазинов "Копейка" и "Метро"
???
Считаете все заказы Григорьева для Метро.
Складываете два числа
Извините, если пишу не в ту тему, а вопрос следующий!
Есть база данных (наименование/группа/подгруппа/продажи).
Необходимо сделать отдельную таблицу, в которой будет сумма продаж в разрезе каждой подгруппы, только не вся, а 80% от всей суммы.
(этакий АВС анализ скажем так). И далее в другом столбце необходимо количество наименований, сумма которых составляет эти 80%.
Только вот мне это необходимо сделать в разрезе каждой подгруппы, а не просто списка наименований.
Если я все правильно поняла, то со сводной таблицей придется делать много ручных манипуляций.
Возможно ли с помощью суммеслимн суммировать диапазон, одно из условий = за конкретный месяц.
допустим:
Условие 1=Январь (столбец дата в формате 03.01.2016)
Условие 2=Петров (столбец Ф.И.О.)
Условие 3 =ГСМ (столбец статья расхода)
Вот эта формула считает за Январь: =СУММПРОИЗВ(--(МЕСЯЦ(В1:В3)=1);С1:С3). А надо применить ещё условия отбора. Вставляю в суммеслимн (МЕСЯЦ(В1:В3)=1) как условие - не работает.
У меня вопрос, как суммировать диапазон, исключив ячейки с ошибкой?
У меня массив постоянно обновляющихся данных компаний по разным секторам, и если я хочу суммировать данные по одному сектору я должна сначала обозначить диапазон отбора сектора, установить критерий (определить нужный сектор), потом задать диапазон суммирования, и вот на этом этапе исключить ошибки (некоторые компании данные не предоставляют)
Я так поняла это будет SUMIFS(диапазон, критерий1, диапазон суммирования, И ВОТ ТУТ его надо исключить ошибку! )
Либо =AGGREGATE(9 (это функция суммирования, 6(исключение ошибки, диапазон) - но в этом варианте возникает вопрос как отобрать по сектору предварительно?
Спасибо!
есть столбец ячеек, в самом низу которого Стоит ячейка с формулой суммирования ячеек этого столбца (12 строк по числу месяцев в году),
в каждой из строк слева от этого столбца производится ряд вычислений и изначально в крайнем столбце выводится некое значение вычислений с использованием в расчётах некой константы.Это сильно загружало таблицу и она была труда в восприятии - путём составления условий (при которых цвет чернил совпадает с цветом фона) я убрал лишнюю ВИДИМУЮ загрузку в каждой из ячеек столбца, но сумма всех ячеек столбца постоянно показывает сумму всех ячеек (12 ячеек с учётом некой константы, используемой в расчётах)
Суть вопроса- как сделать так, что бы в ячейке с формулой суммы этого столбца выводился результат суммирования только тех ячеек, в строках которых появилась хотя бы одна ячейка с внесёнными данными ?
в упрощённом виде имеем 12 строк ( по числу месяцев) в три столбца : приход + некая постоянная величина, расход, остаток - в ячейке сумма остатков надо иметь только факт по текущему месяцу! А остальные, не заполненные строки учитываться не должны.
Вот ссылка на таблицу
Я говорю о ячейке H15 и в данном случая должно быть значения , как в январе - 8 999,00
Спасибо.
A1-исполнитель (покупка), B1-З/П (руб.), C1-З/П (долл.), D1-исполнитель (привоз), E1-З/П (руб.), F1-З/П (долл.), G1-исполнитель (поиск покупателя), H1-З/П (руб.), I1-З/П (долл.) и т.д.
Нужно просуммировать зар.платы в рублях и в долларах по каждому исполнителю.
Как это можно сделать?
P.S. Сейчас формула выглядит так: ЕСЛИ($M15="Руслан";N15;0)+ЕСЛИ($P15="Руслан";Q15;0)+ЕСЛИ($S15="Руслан";T15;0)+ЕСЛИ($V15="Руслан";W15;0). А это всего лишь часть формулы, там ещё есть проверка на "дурака".
Подскажите а как правильнее прописать формулу, если в способе № 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