Excel какая формула лучше бдсумм или суммеслимн
Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.
Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.
Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20
Теперь необходимо сложить значения в ячейках, отвечающих только что установленному критерию. Неважно, какое именно форматирование применяется к этим ячейкам, однако необходимо знать критерий, согласно которому ячейки выделяются.
Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).
Рис. 2. Суммирование ячеек, отвечающих одному условию
Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).
Рис. 3. Суммирование ячеек, отвечающих нескольким условиям
Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.
Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.
В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).
Рис. 4. Использование функций баз данных
Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите
Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.
Дополнение от 29 марта 2017 г.
Для выборочного подсчета по нескольким условиям в больших таблицах можно использовать несколько способов: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИМН и т.д.
Еще одним, относительно экзотическим, но весма мощным инструментом является функция БДСУММ (DSUM) из категории Работа с базой данных (Database) . При внешней простоте, она позволяет гибко фильтровать списки по нескольким сложным и связанным между собой условиям и подсчитывает сумму найденных записей по заданному столбцу. Синтаксис функции таков:
=БДСУММ( Исходные_данные ; Столбец_результата ; Диапазон_условий )
- Исходные_данные - диапазон, включающий в себя таблицу с данными, которые мы анализируем и строкой заголовка.
- Столбец_результата - название (из шапки таблицы) или порядковый номер столбца, по которому нужно просуммировать данные.
- Диапазон_условий - диапазон, содержащий названия столбцов и условия по ним.
Чтобы удобнее было ссылаться эту таблицу в будущем, конвертируем ее в "умную" командой Форматировать как таблицу на вкладке Главная (Home - Format as Table) или сочетанием клавиш Ctrl + T . На появившейся затем вкладке Конструктор (Design) зададим ей имя - например БазаДанных.
Простая сумма по одному условию
Начнем с простого случая. Допустим, нам нужно просуммировать стоимость по всем сделкам сока Добрый. Это будет выглядеть следующим образом:
Обратите внимание на следующие моменты:
Приблизительный и точный текстовый поиск
При необходимости, можно легко реализовать не только точный, но и приблизительный текстовый поиск. Посмотрите внимательно на следующие варианты суммирования выручки по городу Абакан и разницу между ними:
- Если нужен поиск точного соответствия, то используем конструкцию '= (апостроф и знак равно).
- Если нужен поиск подстроки, т.е. всех ячеек, которые содержат нужное значение, то его надо заключить в звездочки. В нашем случае будут просуммированы все варианты Абакана (с "г.", без "г.", с пробелами перед-после и т.п.)
- Если просто ввести значение без равно и звездочек, то будут найдены и просуммированны все строки, где содержимое начинается с указанного значения, т.е. это равноценно звездочке в конце.
Несколько условий со связками "И" - "ИЛИ"
Если нужно просуммировать данные по нескольким условиям, связанным друг с другом логическим оператором И (AND), то ячейки с этими условиями должны быть в одной строке. Например, если нужно просуммировать все продажи Fanta по Абакану (в любом виде его написания), то это будет выглядеть так:
Если же нужно связать несколько условий логическим оператором ИЛИ (OR), то их нужно расположить в разных строчках. Например, если нужно просуммировать деньги по всем вариантам написания "города на Неве", коих великое множество:
И конечно же, можно комбинировать оба подхода, сочетания в одном запросе условия со связками И и ИЛИ одновременно:
В этом случае вычисляется сумма продаж Fanta в Абакане и Burn у Дубинина.
Суммирование по интервалу дат
В диапазоне условий можно легко задать интервал дат для подсчета. Если интервал ограничен с двух сторон, то нужно будет задать два условия, связанных между собой логическим "И" - поэтому придется писать условия в одной строке, т.е. добавить еще один столбец с тем же названием Дата сделки:
В данном случае вычисляется сумма продаж Fanta за 2016 год и Фруктайм до 2016 года.
Условия для чисел
Исключения "все кроме"
Если нужно при суммировании исключить записи по какому-либо параметру, то можно использовать символы "<>" обозначающие "не равно" в синтаксисе Excel. Допустим, нам нужно просуммировать все данные по Fanta кроме Самары и по Квасу кроме Пензы - это будет выглядеть так:
Заключение
Надеюсь, вы уже поняли, что функция БДСУММ является очень неплохим инструментом и, зачастую, более удобной альтернативой классическим функциям выборочного подсчета типа СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) . Кроме того, в той же категории Работа с базой данных (Database) можно найти ее "подруг", вычисляющих не только сумму:
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 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) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Диапазон — диапазон ячеек, в котором ищутся значения соответствующие аргументу Условие . Диапазон может содержать числа, даты, текстовые значения или ссылки на другие ячейки. В случае, если другой аргумент - Диапазон_суммирования - опущен, то аргумент Диапазон также является диапазоном, по которому производится суммирование значений удовлетворяющих аргументу Условие (в этом случае Диапазон должен содержать числа).
Условие — критерий в форме числа, выражения или текста, определяющий, какие ячейки должны суммироваться. Например, аргумент Условие может быть выражен как 32, "яблоки" или ">32".
Диапазон_суммирования — диапазон ячеек содержащий числа, которые будут просуммированы, в случае если соответствующие им ячейки, указанные в аргументе Диапазон соответствуют аргументу Условие . Диапазон_суммирования - необязательный аргумент. Если он опущен, то суммирование будет производиться по диапазону ячеек, указанному в аргументе Диапазон .
Примеры
Рассмотрим случай, когда аргумент Диапазон_суммирования опущен. В этом случае суммирование будет производиться по диапазону ячеек, указанному в первом аргументе Диапазон (т.е. он должен содержать числа). В нем же будет производиться поиск значений соответствующих аргументу Условие , которые затем и будут просуммированы. Пусть это будет диапазон B5:B15 , см. Файл примера .
- найти сумму всех чисел больше или равных 10. Ответ: 175. Формула: =СУММЕСЛИ(B5:B15;">=10")
- найти сумму всех чисел меньше или равных 10. Ответ: 42. Формула: =СУММЕСЛИ(B5:B15;"
- найти сумму всех положительных чисел в диапазоне. Формула: =СУММЕСЛИ(B5:B15;">0") . Альтернативный вариант с использованием функции СУММПРОИЗВ() выглядит так : =СУММПРОИЗВ((B5:B15)*(B5:B15>0))
Форма задания условия (критерия) достаточно гибка. Например, в формуле =СУММЕСЛИ(B5:B15;D7&D8) критерий D7&D8 : в D7 содержится текстовое значение =СУММЕСЛИ(B5:B15;" =СУММЕСЛИ(B5:B15;" =СУММЕСЛИ(B5:B15;" =СУММЕСЛИ(B5:B15; D7 &56) .
СОВЕТ: Для сложения с несколькими критериями воспользуйтесь статьей Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL (Часть 2.Условие И) .
В статье СУММЕСЛИ - Сложение по условию (один Текстовый критерий) рассмотрен более общий случай, когда критерий применяется к соседнему диапазону, содержащему текстовые значения, а суммирование производится только соответствующих ячеек из числового диапазона.
Один из способов использования двух или несколько условий в функции СУММЕСЛИ основан на добавлении или вычитании результатов нескольких функций СУММЕСЛИ. Если два условия касаются одного и того же диапазона значений, данный метод является весьма эффективным. Если же диапазоны разные, то данный способ существенно усложняется, так как необходимо следить за тем, чтобы в итоговых расчетах не учитывать повторно те же самые значения.
Функция СУММЕСЛИ и несколько условий в одном диапазоне Excel
Допустим нам необходимо суммировать числовые значения в границах дат от 23-го и до 29-го июня включительно. Начальные и конечные даты введены в соответствующих ячейках дополнительной таблицы для составления запросов выборки из исходной таблицы. Ниже на рисунке изображены исходные значения и условия отбора значений для суммирования:
Описанная данная техника суммирования по нескольким условиям с помощью функции СУММЕСЛИ основана на вычитании. В данной формуле первая функция СУММЕСЛИ возвращает сумму значений, соответствующих датам раньше конечной даты в ячейке E3 или равной этой дате (в данном случаи =29-е Июня). Оператор «меньше» объединен символом амперсант (&) со ссылкой на ячейку E2 (начальной даты).
Когда формула содержала бы только первую функцию =СУММЕСЛИ(A2:A12;" < первую функция формулы." src="https://exceltable.com/formuly/images/formuly111-2.jpg" >
Но нам необходимо получить сумму чисел в границах между 25-ым и 29-ым числом Июня месяца включительно, а не между 20-ым и 29-ым. Для этого необходимо вычесть из первого итогового результата 5721,00 сумму чисел раньше даты 25-го Июня – 3 726,50.
Данного результата мы добиваемся с помощью повторного использования такой же функции СУММЕСЛИ, но с немного измененным вторым аргументом СУММЕСЛИ(A2:A12;"<"&E2;B2:B12).
Теперь проверим разницу вычислений двух частей формулы с помощью вычитания:
Суммирование числовых значений, соответствующих датам раньше конечной даты (29-го Июня и равной ей) с последующим вычитанием от данного результата суммы всех числовых значений, относящихся к датам раньше от начальной (25-го Июня) позволяет получить сумму всех чисел в границах соответствующим датам заданного периода в таблице составления запроса выборки.
Пример функции СУММЕСЛИМН с несколькими условиями в Excel
Если вы пользуетесь версией Excel 2010-го года или более новой, то для данного решения задачи лучше воспользоваться специальной функцией СУММЕСЛИМН для суммирования значений с несколькими условиями. Она вычислит такой же правильный результат, но выполнит его быстрее при меньших затратах системных ресурсов. Кроме того, новая функция более интуитивно понятная, чем выше описанная техника вычитания итоговых сумм. Формула функции СУММЕСЛИМН:
Пример функции СУММЕСЛИМН." src="https://exceltable.com/formuly/images/formuly111-5.jpg" >
=E2)*(B2:B12))' >
Как видно на рисунке, мы получили аналогичный результат вычисления. Как работает СУММПРОИЗВ в данной формуле детально описано в статье: Формулы суммирования по нескольким условиям в Excel.
Данные вычисления основаны на умножении логических значений и числовых в столбце B2:B12. Результаты вычислений в первых двух логических выражений возвращают логические значения ИСТИНА=1 и ЛОЖЬ=0. Если хоть один из множителей будет равен нулю, то и результат = 0. Например, как читает данная формула первую строку:
Если же оба логические выражения возвращают значение ИСТИНА, тогда текущая ячейка столбца B с показателем продаж умножается на единицы, а затем суммируются все найденные значения. Для наглядного примера сформируем таблицу как видит формула СУММПРОИЗВ текущую ситуацию:
Несмотря на то что функция СУММПРОИЗВ старая, не такая быстрая и удобная как СУММЕСЛИМН, она будет всегда поддерживаться новыми версиями Excel для возможности открытия файлов старых версий без вычислительных ошибок в формулах. Поэтому стоит разобраться с ее принципами работы.
Читайте также: