Excel какая формула лучше бдсумм или суммеслимн
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 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) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Одной из самых популярных задач в Excel является суммирование по условию, для этого используются функции СУММЕСЛИ (в случае если условие одно) и СУММЕСЛИМН (если условий несколько). В этом уроке рассмотрим использование этих функций более подробно.
СУММЕСЛИ в Эксель
Функция СУММЕСЛИ применяется в случае, если вам необходимо просуммировать числа по одному определенному условию. Синтаксис функции следующий.
Синтаксис функции для нашего примера будет:
Первым аргументом (A2:A13) мы задаем область, в которой будем проверять условие, в нашем случае это фамилии менеджеров, вторым аргументом (E2) мы задаем ячейку, в которой указано условие, третьи аргументом (C2:C13) мы задаем область с выручкой, которую должны просуммировать.
Что будет делать формула? Она сопоставит в каждой строке с фамилиями менеджеров фамилию из условия и фамилию в ячейке и если они совпали, то из соответствующей строки с выручкой возьмет значение и добавит к результату. Если вы все сделали правильно, то сумма выручки по менеджеру Петров должно составить 3 850 рублей.
СУММЕСЛИМН в Эксель
СУММЕСЛИМН является более универсальной формулой, она позволяет суммировать по нескольким условиям, в том числе по одному, т.е. заменяя функцию СУММЕСЛИ. Синтаксис у функции следующий:
В нашем случае мы хотим узнать, какую выручку нам принес менеджер Петров, работая в городе Москва. Синтаксис функции:
По итогам работы этой функции, мы получим размер выручки, которую принес нам менеджер Петров, работая в городе Москва. Если вы все сделали правильно, то результат должен получиться 1 775 рублей.
Функция СУММЕСЛИМН — одна из математических и тригонометрических функций, которая суммирует все аргументы, удовлетворяющие нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех розничных продавцов, (1) проживающих в одном регионе, (2) чей доход превышает установленный уровень.
Это видео — часть учебного курса Усложненные функции ЕСЛИ.
Синтаксис
СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
=СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")
=СУММЕСЛИМН(A2:A9; B2:B9; "<>Бананы"; C2:C9; "Артем")
Имя аргумента
Диапазон_суммирования (обязательный аргумент)
Диапазон ячеек для суммирования.
Диапазон_условия1 (обязательный аргумент)
Диапазон, в котором проверяется Условие1.
Диапазон_условия1 и Условие1 составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента Диапазон_суммирования.
Условие1 (обязательный аргумент)
Условие, определяющее, какие ячейки суммируются в аргументе Диапазон_условия1. Например, условия могут вводится в следующем виде: 32, ">32", B4, "яблоки" или "32".
Диапазон_условия2, Условие2, … (необязательный аргумент)
Дополнительные диапазоны и условия для них. Можно ввести до 127 пар диапазонов и условий.
Примеры
Чтобы использовать эти примеры в Excel, выделите нужные данные в таблице, щелкните их правой кнопкой мыши и выберите команду Копировать. На новом листе щелкните правой кнопкой мыши ячейку A1 и в разделе Параметры вставки выберите команду Использовать форматы конечных ячеек.
Проданное количество
=СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")
Суммирует количество продуктов, названия которых начинаются с Я и которые были проданы продавцом Артем. Для поиска совпадающих названий продуктов в Criteria_range1 B2:B Criteria_range2 9 в группе Условия1(=A*) используется подп. Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 20.
=СУММЕСЛИМН(A2:A9; B2:B9; "<>Бананы"; C2:C9; "Артем")
Суммирует количество продуктов, которые не являются бананами и которые были проданы продавцом по имени Артем. Он исключает бананы, используя<>в критерии1, "<>Бананы"и ищет имя "Том" в Criteria_range2 C2:C9. Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 30.
Распространенные неполадки
Вместо ожидаемого результата отображается 0 (нуль).
Если выполняется поиск текстовых значений, например имени человека, убедитесь в том, что значения аргументов Условие1, 2 заключены в кавычки.
Неверный результат возвращается в том случае, если диапазон ячеек, заданный аргументом Диапазон_суммирования, содержит значение ИСТИНА или ЛОЖЬ.
Значения ИСТИНА и ЛОЖЬ в диапазоне ячеек, заданных аргументом Диапазон_суммирования, оцениваются по-разному, что может приводить к непредвиденным результатам при их суммировании.
Ячейки в аргументе Диапазон_суммирования, которым присвоено значение ИСТИНА, оцениваются как 1. Ячейки, которым присвоено значение ЛОЖЬ, оцениваются как 0 (ноль).
Рекомендации
Необходимые действия
Использование подстановочных знаков
Подстановочные знаки, такие как вопросительный знак (?) или звездочка (*), в аргументах Условие1, 2 можно использовать для поиска сходных, но не совпадающих значений.
Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (
) перед вопросительным знаком.
Например, формула =СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Арте?") будет суммировать все значения с именем, начинающимся на "Арте" и оканчивающимся любой буквой.
Различия между функциями СУММЕСЛИ и СУММЕСЛИМН
Порядок аргументов в функциях СУММЕСЛИ и СУММЕСЛИМН различается. Например, в функции СУММЕСЛИМН аргумент Диапазон_суммирования является первым, а в функции СУММЕСЛИ — третьим. Этот момент часто является источником проблем при использовании данных функций.
При копировании и изменении этих похожих формул нужно следить за правильным порядком аргументов.
Одинаковое количество строк и столбцов для аргументов, задающих диапазоны ячеек
Аргумент Диапазон_условия должен иметь то же количество строк и столбцов, что и аргумент Диапазон_суммирования.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
В приложении к статье файл с простой задачей суммирования диапазона по различным условиям.
Как ни странно, подобные задачи вызывают серьезные трудности даже у опытных пользователей Excel, работающих с большими объемами данных. Попробуйте решить самостоятельно (лист ЗАДАЧА) – вычислите значения в желтых ячейках, формулы должны копироваться. Подразумевается также, что даты в исходной таблице и в условиях могут быть любыми.
Если сходу не получилось, то давайте разбираться. Некоторые пользователи указывают на «неправильность» расположения данных в таблице. Действительно, в примере данные не нормализированы – т.е. не в приведены единую таблицу со списками полей, где даты также бы располагались в столбцах, а не в заголовках, как в примере. Но на самом деле это типичный вид рабочей таблицы Excel. Обсуждать неправильность структуры данных в данной статье не будем, хотя, это, пожалуй, главная причина проблем с анализом информации. Но и говорить о том, что задача не имеет решения в Excel нельзя.
СУММЕСЛИ, СУММЕСЛИМН
Итак, рассмотрим первый вариант решения задачи, тот, на котором спотыкается большинство пользователей – лист ОШИБКИ.
Первое, что приходит в голову подавляющему большинству – это использовать формулу SUMIF для первой задачи и SUMIFS для остальных:
Функция СУММЕСЛИМН не работает, так как она более требовательна к размерам диапазонов – просто выдает ошибку, если количество ячеек в массивах значений и условий не совпадает. Причем важно не только количество ячеек, но и их расположение – по строкам или столбцам.
Можно сделать вывод, что простое использование SUMIFи SUMIFSв данном случае не подходит.
Дополнительные вычисления
В условиях задачи нет запрета на использование дополнительных диапазонов для служебных вычислений. Главное, чтобы результат попал в желтые ячейки и формулы были бы копируемыми. Воспользовавшись этой подсказкой большинство опытных пользователей без труда решают задачу с использованием дополнительных столбцов – лист ДОП.ВЫЧ.
Предварительно вычисляются суммы по периодическим условиям, затем этот результат еще раз обрабатывается через суммирование по округам. Т.е. применяется последовательно 2 функции суммирования с разными условиями, но с одинаковыми размерами диапазонов.
Это очевидный и, в принципе, верный подход к решению подобных задач. Использование дополнительных вычислений – это один из главных методов обработки сложных информационных структур.
СУММПРОИЗВ
Но можно ли обойтись без служебных столбцов? Во-первых, просто ради интереса. Во-вторых, бывают случаи, особенно в сложных экономических моделях, когда наличие дополнительных вычислений не приветствуется. Это может быть связано с алгоритмами вставки новых строк, столбцов и т.п.
Самым простым способом является не совсем стандартное использование функции SUMPRODUCT. Функция, предназначенная для суммы произведений чисел, может использоваться также для работы с условиями (см. лист СУММПРОИЗВ):
Формулы выдают правильный результат:
В качестве аргумента функции используется произведение векторов на массив, при этом в векторах записаны логически выражения, при выполнении возвращающие 1 (ИСТИНА) или 0 (ЛОЖЬ). Т.е. каждый элемент результирующего массива умножается на 0 или на 1 в зависимости от соответствующего условия вектора. Функция SUMPRODUCT в данном случае работает с массивами данных (см.далее), но в отличие от простых функций агрегирования (SUM, MIN, MAX) не требует ввода через Ctrl+Shift+Enter.
Можно сказать, что как раз этот метод работы функции SUMPRODUCT, гораздо полезнее на практике, чем вычисление суммы произведений.
Формулы обработки массивов
Если вам удалось понять принцип работы СУММПРОИЗВ, то альтернативный метод решения задачи с применением функций обработки массивов не должен вызвать больших проблем, даже, если вы никогда не сталкивались с подобной записью (см. лист МАССИВЫ).
Формулы также выдают правильный результат, но в записи видны фигурные скобки – такие формулы вводятся через нажатие Ctrl+Shift+Enter:
Принцип работы функций обработки массивов кратко можно изложить следующим образом
- Последовательно просматриваются ячейки в диапазоне строк и столбцов, при этом выбираются соответствующие им значения в прямоугольных диапазонах.
- Каждый шаг образует формулу, которая вычисляется отдельно.
- В конце результат каждого вычисления агрегируется в одно значение (чаще всего используется функция SUM).
По-моему, запись формулы с обработкой массивов, в которой явно заданы условия (функция ЕСЛИ), более наглядна, чем использование скрытых условий в SUMPRODUCT. Хотя принцип работы тот же. Сказать однозначно, что будет работать быстрее на больших объемах данных нельзя – все зависит от расположения значений в массиве данных. Главное для массивов – не забывать про Ctrl+Shift+Enter, просто вписанные вручную фигурные скобки, как пытаются делать некоторые, просто сделают из формулы текстовую строку.
Вычисляемая адресация
Использование функций вычисляемой адресации диапазонов – это одно из мощнейших средств оптимизации работы с ненормализированными структурами данных. Вычисляемая адресация позволяет создать копируемую формулу при обработке многострочных блоков информации, сборе данные из столбцов в строки и наоборот, поиске информацию в диапазонах с заранее неизвестной размерностью. Принцип копируемости формулы в свою очередь существенно сокращает время на обработку информации, снижая при этом риск ошибки.
Пример использования формул с функциями вычисляемой адресацией представлен на листе ВЫЧ.АДРЕС.
В чистом виде использование вычисляемой адресации позволило решить только вторую часть задачи – собрать данные на определенную дату:
Смысл формулы в том, что столбец для суммирования вычисляется через поиск соответствующего заголовка.
Аналогичная логика заложена и в третьей части задачи:
Функция СУММЕСЛИ собирает данные по строкам, а номера столбцов вычисляются сложным образом через вложенные условия IF. Выбранные данные суммируются. Формула требует ввод через Ctrl+Shift+Enter, так как в ней использован метод агрегации (SUM) нескольких найденных значений.
Вариант решения первой части задачи – простого итога по округам – совсем не стандартный:
В формуле использован массив констант , обеспечивающий просмотр семи столбцов. По большому счету это та же обработка массива без сложного поиска, как во второй части задачи – последовательно обрабатываются столбцы, затем происходит общее суммирование. Так как массив состоит из констант, то формула не требует обязательного ввода через Ctrl+Shift+Enter.
Не стоит злоупотреблять вычисляемой адресацией диапазонов там, где это не требуется. На больших объемах данных этот метод будет работать медленнее всех, описанных выше.
Читайте также: