Суммирование по нескольким условиям в excel

Выборочное суммирование

Как в экселе посчитать сумму выборочных ячеек, решает функция «СУММ» раздела «Формулы» панели управлений.

Все операции выполняются наведением курсора на разделы панели управления и ячейки с данными, щелканьем («клик») левой кнопкой мыши, в следующей последовательности:

• любая пустая ячейка;
• раздел «ФОРМУЛЫ» панели управлений;
• раздел «Вставить функцию»;
• раздел «Категория», выбрать «Математические»;
• раздел «Выберите функцию» выбрать «СУММ», перейти к разделу «Аргументы функции»;
• ячейка таблицы с первым слагаемым;
• раздел «Аргументы функции», строка «Число 2»;
• ячейка таблицы со вторым слагаемым.

После того как все слагаемые введены, нажать «ОК» окна «Аргументы функции», получив окончательный результат.

Программа позволяет находить более сложные суммы данных, объединенных между собой условием (СУММ ЕСЛИ).

Такими условиями могут быть отрицательная величина, экстремальные значения, т.е. сложению будут подвергаться только отрицательные значения или отличающиеся от экстремумов.

Можно складывать результаты промежуточных расчетов: квадраты, произведения, квадраты разностей и разность квадратов — необходимые при статистическом анализе.

Электронные таблицы могут содержать тысячи исходных данных, но простая операция в эксель гарантирует быстрый подсчет их суммы.

Как в Excel посчитать сумму в столбце: все способы

Шаг 3 — Примените формулу ко всему столбцу. Вы можете использовать маркер Fill, чтобы выбрать и перетащить его, или просто скопировать и вставить ячейку C3 во все оставшиеся ячейки (что автоматически настроит ссылку и даст правильный результат).

Синтаксис метода WorksheetFunction.SumIfs

• ДСумм – диапазон суммирования, представляющий из себя часть таблицы, в ячейках которого, соответствующих условиям (критериям), суммируются значения.
• ДУсл – диапазон условия, представляющий из себя часть обрабатываемой таблицы, в ячейках которого ищется совпадение с условием (критерием) суммирования.
• Усл – это условие, которое применимо к диапазону, указанному перед ним, и определяющее критерий суммирования.

Первые три аргумента являются обязательными, остальные – необязательные. Всего выражение может содержать один диапазон суммирования (ДСумм) и четырнадцать пар диапазон+условие (ДУсл+Усл).

Диапазоны условий не должны повторяться – повторение приведет к ошибке. Диапазон суммирования можно один раз использовать как диапазон условия.

В параметре «Усл» метода WorksheetFunction.SumIfs можно использовать знаки подстановки:

• вопросительный знак (?) – заменяет один любой символ;
• звездочка (*) – заменяет любую последовательность символов (в том числе ни одного символа);
• тильда (~) – ставится перед вопросительным знаком или звездочкой, чтобы они обозначали сами себя.

Как задать простое логическое условие в Excel

​ с функцией​ все ли проверяемые​ в противном случае.​ Excel ячейки в​ С1 слово «Да».​ «6». Можно написать​ с помощью функции​Нужно очень крепко подумать,​ приоритеты.​=ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)​Имя аргумента​​ В9 – условие.​​ или продукт хранится​​ «ЛОЖЬ».​​ Как так сделать?​ значение ЛОЖЬ.​ убедиться, что одно​ЕСЛИ​

• ​ условия принимают значение​
• ​ Задавая такое условие,​ определенных строках».​
• ​ Если число будет​

Операторы сравнения в Excel

​ любое слово в​ проверки ошибок​ чтобы выстроить последовательность​Иногда требуется проверить, пуста​В примере выше функция​Описание​ Третий аргумент –​

Как задать условие в Excel

​ дольше 10 месяцев,​=И (Лог_знач. 1; Лог_знач.​И скажить как​=ЕСЛИ(ИЛИ(НЕ(A350)); A3; «Значение вне​ значение не равно​.​

​ ИСТИНА.​​ можно сравнивать текстовые​Можно в Excel​ меньше 2, то​ этом условии, т.д.​Логические функции​ из множества операторов​ ли ячейка. Обычно​ ЕСЛИ в ячейке​лог_выражение​ $C$2:$C$7 – диапазон​ его списывают.​ 2;…)​ написать чтоб EXCEL​

​ интервала»)​​ другому.​Формула​Функция​ строки без учета​ суммировать данные из​ ячейка должна оставаться​Третье условие –​Функции Excel (по алфавиту)​ ЕСЛИ и обеспечить​ это делается, чтобы​

​ D2 означает:​ (обязательно)​ усреднения; числовые значения,​Для решения используем логические​Принимает до 255 аргументов​ понял что надо​100 не меньше 0​Функция​Описание​И​

​ регистра. К примеру,​ таблицы по многим​ пустой. Скопировали формулу​ «Значение_если_ложь» — здесь​Функции Excel (по категориям)​ их правильную отработку​ формула не выводила​

​ЕСЛИ(C2 больше B2, то​​Условие, которое нужно проверить.​ которые берутся для​ функции ЕСЛИ и​ в виде условий​ «Больше или равно»,»Меньше​ (ЛОЖЬ) и больше​

​НЕ​=И(A2>1;A2​​возвращает значение ИСТИНА,​​ сравнивая «ЯНВАРЬ» и​​ разным условиям. Например,​​ по столбцу С.​ мы напишем, что​Часто применяемая,​ по каждому условию​ результат при отсутствии​ вернуть текст «Превышение​значение_если_истина​ расчета среднего арифметического.​ ИЛИ: =10);»списан»;»»)’ class=’formula’>.​ или ссылок. Обязательным​ или равно», «Не​ чем 50 (ИСТИНА),​меняет значение своего​

​Возвращает значение ИСТИНА, если​ если в результате​ «январь» формула возвратит​ в функции «ЕСЛИ»​ Получилось так.​ нужно писать, если​логическая функция в Excel​ на протяжении всей​ входного значения.​ бюджета», в противном​​ (обязательно)​​Функция СРЗНАЧЕСЛИ сопоставляет значение​​ Условие, записанное с​​ является первый.​

​ ровняется». Так для​

Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в EXCEL

history 3 апреля 2013 г.

Функция СУММПРОИЗВ() , английская версия SUMPRODUCT(), не так проста, как кажется с первого взгляда: помимо собственно нахождения суммы произведений, эта функция может использоваться для подсчета и суммирования значений на основе критериев, а также, в некоторых случаях, избавить от необходимости применений формул массива.

Существует несколько вариантов применения функции СУММПРОИЗВ() :

• нахождение суммы произведений элементов списка (массива);
• суммирование и подсчет значений, удовлетворяющих определенным критериям;
• замена формул массива (в некоторых случаях).

Использование операторов сравнения.

Рассчитываем стоимость заказов для «Красного» покупателя, в которых было более 100 единиц товара. Как видите, здесь нужно использовать как текстовые, так и числовые критерии.

Критерии можно записать в самой формуле, и это будет выглядеть так:

= СУММЕСЛИ (MI2: MI21; SI2: SI21; «Красный»; RE2: RE21; «> 100”)

Но рациональнее использовать ссылки, как это сделано на рисунке:

= СУММЕСЛИ (MI2: MI21; SI2: SI21; I2; RE2: RE21; I4)

Примечание

Обратите внимание, что логические выражения с операторами сравнения всегда должны заключаться в кавычки («») в формулах («»)

Синтаксис этой функции, помимо работы с числами, текстом и датами, точно такой же, как и для СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу об условной сумме.

Как еще мы можем решить нашу проблему?

Способ 2. Используем функцию СУММПРОИЗВ.

Давайте подробнее рассмотрим, как работает SUMPRODUCT():

= СУММПРОИЗВ (- (B2: B21 = $ I $ 12), — (D2: D21> I13), E2: E21)

Результат вычисления B2: B21 = $ I $ 12 — это массив

{ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ.: ЛОЖЬ

ИСТИНА означает, что код покупателя соответствует условию, например, слово Красный. Этот массив можно увидеть, выбрав в строке формул B2: B21 = $ I $ 12, а затем нажав F9.

А что это за странные минусовые знаки перед этими выражениями? Дело в том, что нам нужны не эти логические выражения, а числа, чтобы затем их можно было умножить и сложить. Если Excel выполняет математическую операцию с логическим выражением, он автоматически преобразует его в число. А знак минус означает умножение на -1. А если умножить на -1 дважды, результат не изменится. Мы помним это из школьной математики

И в результате логический массив превратится в массив чисел {0: 1: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 1: 0: 0: 0: 1: 0: 0: 0}.

Результат вычисления D2: D21> I13 — это массив

{ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ}.

ИСТИНА соответствует пределу «количество больше 100». Здесь мы также используем двойное отрицание для преобразования логических значений в числа.

И, наконец, результатом вычисления B2: B13 является массив {11250: 23210: 12960: 3150: 5280: 9750: 3690: 18300: 5720: 6150: 8400: 2160: 7200: 1890: 17050: 3450: 15840: 2250 : 7200: 8250}, т.е только числа из столбца E.

Результатом умножения этих трех массивов на элемент будет {0: 23210: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 15840: 0: 0: 0 }. Складываем эти продукты и получаем 39050.

Способ 3. Формула массива.

И еще один вариант расчета — применяем формулу массива. В I14 пишем:

= СУММ ((L2: L21 = I12) * (L2: L21> I13) * (L2: E21))

Не забудьте нажать CTRL + SHIFT + ENTER в конце, чтобы обозначить это выражение как формулу массива. Программа автоматически добавит фигурные скобки в начало и в конец. Опять получаем результат 39050.

Способ 4. Автофильтр.

Другой альтернативой является использование автоматического фильтра. Для этого преобразуйте диапазон данных A1: F21 в интеллектуальную таблицу. Напоминаю, что для этого в меню «Главная» выберите «Форматировать как таблицу». Далее добавляем итоговую строку (вкладка «Дизайн») и устанавливаем необходимые фильтры.

Без формул будет определено общее количество отфильтрованных строк.

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

​ F9, то мы​ в ячейке G2.​

​ десятков, то такой​

​ сталкивались с такой​СУММЕСЛИ (SUMIF)​ все заказы в​

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

​ «*» (любое количество​ в году соответственно​»​Фрукты​ листа Excel. Чтобы​можно использовать подстановочные​суммирует только те​ окно заполняем, как​ написали бы критерий​ на замечание​​ (слово «​

​ увидим массив результатов​​ «Читать» эту формулу​​ способ начинает нагонять​ замечательной возможностью Excel​не поможет, т.к.​​ «Копейку», например), то​​ символов) или вопросительным​ составим следующую таблицу:​​B3:B22 – диапазон ячеек,​​Апельсины​

​ отобразить результаты формул,​​ знаки: вопросительный знак​​ значения из диапазона​

• ​ в предыдущей формуле,​​ – «выпущено».​ant6729​Условие​​ работы функции​​ лучше из середины​ тоску, да и​ как формулы массива,​ не умеет проверять​
• ​ задача решалась бы​​ знаком «?» (один​Для расчетов используем следующую​ содержащих значения членов​8 000 ₽​ выделите их и​ (​ C2:C5, для которых​ только «критерий» пишем​В разделе «Критерий»​: Там, где 1​» в E1), лишь​ОСТАТ​ наружу:​ ошибиться при вводе​ то советую почитать​ больше одного критерия.​ достаточно легко при​ любой символ). В​​ формулу:​​ прогрессии.​Масло​ нажмите клавишу F2,​?​ соответствующие значения из​ «%» — это​​ можно писать некоторые​​ — вставляете количество​ бы он не​
• ​, т.е. остатки от​​Функция​ формулы можно запросто.​ предварительно про них​ Поэтому начиная с​ помощи встроенной функции​

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

​ качестве примеров могут​В результате получим:​Полученный результат:​4 000 ₽​ а затем — клавишу​​) и звездочку (​​ диапазона B2:B5 равны​ в нашей таблице​ символы.​ ячеек для суммирования​ совпадал с заголовками​ деления номеров строк​СТРОКА (ROW)​​ Как же поступить?​​ много хорошего здесь.​ версии Excel 2007​ Excel​ быть критерии «ст?л»​Функция СУММЕСЛИ имеет следующий​Доля в процентах первых​​Овощи​​ ВВОД. При необходимости​*​ «Иван».​

​ так написано. Получилась​* (звездочка) означает​ (количество первых ячеек​ из таблицы с​ на 2:​​выдает номер строки​​ Есть способы сделать​​ Ну, а в​​ в набор функций​СУММЕСЛИ (SUMIF)​ (стол либо стул​

​ синтаксис:​ 15-ти значений (75%​Морковь​ измените ширину столбцов,​). Вопросительный знак соответствует​Если необходимо выполнить суммирование​ такая формула.​ любое количество символов​

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

​ диапазона)​ данными. После ввода​И, наконец, если выделить​ для каждой по​ это изящнее.​ нашем случае задача​ была добавлена функция​ из категории​ при поиске наименований​=СУММЕСЛИ(диапазон; условие; )​ — от количества​42 000 ₽​ чтобы видеть все​

​ одному любому символу,​

​ ячеек в соответствии​=СРЗНАЧЕСЛИ(B2:B10;»%»;C2:C10)​ в слове. Например,​Там, где 2​ условия в ячейку​ фрагмент​ очереди ячейки из​Если в таблице есть​ решается одной формулой:​СУММЕСЛИМН (SUMIFS)​Математические (Math&Trig)​ мебели), «Ива*» (фамилии​Описание аргументов:​ всех 20-ти значений)​Фрукты​ данные.​

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

​ а звездочка — любой​ с несколькими условиями,​Таблица посчитала так.​ «мор*» — будет​ — суммируется​ E2 появится слово​ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15)​ диапазона B2:B15.​ столбец с признаком,​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​

​- в ней​

​. Выделяем пустую ячейку​ Иванов, Иваненко, Иванищев​диапазон – обязательный для​ данной геометрической прогрессии​​Яблоки​Стоимость имущества​

​ последовательности символов. Если​ используйте функцию СУММЕСЛИМН.​Как посчитать наценку, скидку,​ искать слова на​Как-то так​ИСТИНА (TRUE)​и нажать на​Функция​ по которому можно​После ввода этой формулы​ количество условий проверки​ для результата, жмем​ и другие, которые​

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

​ заполнения аргумент, принимающий​​ составляет всего лишь​

​12 000 ₽​​Комиссионные​ ​ требуется найти непосредственно​СУММЕСЛИ(диапазон; условие; )​ умножив столбец на​ «мор» и любое​ant6729​или​F9​ОСТАТ (MOD)​ произвести выборочное суммирование​ необходимо нажать не​ увеличено аж до​ кнопку​

​ начинаются на «Ива»).​

planetaexcel.ru>

Поиск минимального или максимального значения по условию

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?

Предположим, нам нужно найти минимальную цену для каждого товара в базе данных по поставщикам:

Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки — столбец с ценами.

Для будущего удобства, конвертируем исходный диапазон с ценами в «умную таблицу». Для этого выделите его и выберите на вкладке Главная — Форматировать как таблицу (Home — Format as Table) или нажмите Ctrl+T. Наша «поумневшая» таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1 или Таблица1. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей «умной» таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу — это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS) . Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS) :

=МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки1 ; Условие1 ; Диапазон_проверки2 ; Условие2 . )

• Диапазон_чисел — диапазон с числами, из которых выбирается минимальное или максимальное
• Диапазон_проверки — диапазон, который проверяется на выполнение условия
• Условие — критерий отбора

Например, в нашем случае:

Просто, красиво, изящно. Одна проблема — функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.

Способ 2. Формула массива

В английской версии это будет, соответственно =MIN(IF(Table1=F4;Table1))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

. и нажать на клавиатуре F9, чтобы наглядно увидеть тот самый результирующий массив, из которого потом функция МИН и выбирает минимальное значение:

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

• База_данных — вся наша таблица вместе с заголовками.
• Поле — название столбца из шапки таблицы, из которого выбирается минимальное значение.
• Критерий — таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

Если в исходной таблице очень много строк, но данные меняются не часто, то удобнее будет использовать сводную таблицу, т.к. формула массива и функция ДМИН могут сильно тормозить Excel.

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка — Сводная таблица (Insert — Pivot Table) . В появившемся окне нажмите ОК:

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по — Минимум:

Примеры использования функции СУММЕСЛИ в Microsoft Excel

Функция СУММЕСЛИ популярна, поскольку почти в каждой таблице нужно посчитать сумму чисел в ячейках, игнорируя значения, не попадающие под основное условие. Благодаря этой формуле подсчет не становится чем-то сложным и длительным. Стандартное оформление функции выглядит как =СУММЕСЛИ(Диапазон;Критерий;Диапазон_суммирования), а «Диапазон суммирования» указывается только при условии, когда есть фактические ячейки, сложение которых выполняется при любых обстоятельствах. Если данные в «Диапазоне суммирования» отсутствуют, проверяться будут все ячейки, входящие в «Диапазон».

Существует не так много разных примеров, особенности заполнения которых стоит учитывать при оформлении функции СУММЕСЛИ, и далее разберемся с основными и самыми популярными.

Функция СУММЕСЛИ при условии неравенства

Первый пример — использование функции СУММЕСЛИ при условии, когда число для попадания должно быть больше, меньше или не равняться заданному числу. При таком синтаксисе функция проверяет все ячейки указанного диапазона и считает только подходящие. Ручное ее написание через поле ввода состоит из нескольких частей:

1. Определитесь с диапазоном ячеек, попадающих под рассмотрение формулой, у нас это будет прибыль за месяц.

Выше был разобран только один из примеров со случайно взятыми условиями. Ничего не помешает подставить другие значения, расширить или сузить диапазон — формула все равно нормально посчитает значение, если правила синтаксиса были соблюдены.

Функция СУММЕСЛИ при условии соответствия тексту

Возьмем второй пример, когда СУММЕСЛИ используется для подсчета суммы в ячейках, подпадающих под соответствие надписями в другом диапазоне блоков. Это пригодится, например, когда выполняется подсчет общей цены всех товаров, находящихся в одной категории, или рассчитываются затраты компании на зарплаты сотрудникам на конкретных должностях. Пример оформления в этой ситуации еще интересен тем, что синтаксис немного меняется, поскольку в условие подпадает второй диапазон ячеек.

1. В этот раз помимо диапазона суммируемых ячеек определите и те, где присутствуют надписи, попадающие в условие.

Заменяйте слово или вписывайте целую фразу, учитывая регистр символов, чтобы создавать СУММЕСЛИ при подсчете требуемых значений.

Функция СУММЕСЛИМН с несколькими условиями

Завершим разбор примеров третьим вариантом, когда условий для сложения сразу несколько. При таких расчетах рассматриваемая функция заменяется на видоизмененную СУММЕСЛИМН, которая позволяет задавать более одного аргумента, чего нельзя реализовать в первом варианте. Один из самых простых примеров с полным синтаксическим соответствием создается так:

1. Возьмем таблицу, в которой есть три разных значения как по находящимся там данным, так и по их типам. Это месяц, общая прибыль и количество проданных единиц товара. В формуле с несколькими условиями нужно сделать так, чтобы в итоговую сумму попали только те результаты прибыли, которые были получены в сентябре при продажах выше 29 единиц.

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12345 инструкций. Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

Функция СУММЕСЛИ
(SUMIF) в Excel похожа на СУММ
(SUM), которую мы только что разбирали, поскольку она тоже суммирует значения. Разница лишь в том, что СУММЕСЛИ
суммирует только те значения, которые удовлетворяют заданному Вами критерию. Например, простейшая формула с СУММЕСЛИ
:

SUMIF(A2:A10,»>10″)
=СУММЕСЛИ(A2:A10;»>10″)

– суммирует все значения ячеек в диапазоне A2:A10
, которые больше 10
.

Очень просто, правда? А теперь давайте рассмотрим немного более сложный пример. Предположим, что у нас есть таблица, в которой перечислены имена продавцов и их номера ID
(Lookup table). Кроме этого, есть ещё одна таблица, в которой те же ID
связаны с данными о продажах (Main table). Наша задача – найти сумму продаж для заданного продавца. Здесь есть 2 отягчающих обстоятельства:

• Основная таблица (Main table) содержит множество записей для одного ID
  в случайном порядке.
• Вы не можете добавить столбец с именами продавцов к основной таблице.

Давайте запишем формулу, которая найдет все продажи, сделанные заданным продавцом, а также просуммирует найденные значения.

Перед тем, как мы начнём, позвольте напомнить Вам синтаксис функции СУММЕСЛИ
(SUMIF):

SUMIF(range,criteria,)
СУММЕСЛИ(диапазон;критерий;)

• range
  (диапазон) – аргумент говорит сам за себя. Это просто диапазон ячеек, которые Вы хотите оценить заданным критерием.
• criteria
  (критерий) – условие, которое говорит формуле, какие значения суммировать. Может быть числом, ссылкой на ячейку, выражением или другой функцией Excel.
• sum_range
  (диапазон_суммирования) – необязательный, но очень важный для нас аргумент. Он определяет диапазон связанных ячеек, которые будут суммироваться. Если он не указан, Excel суммирует значения ячеек, в первом аргументе функции.

Собрав все воедино, давайте определим третий аргумент для нашей функции СУММЕСЛИ
. Как Вы помните, мы хотим суммировать все продажи, совершённые определённым продавцом, чьё имя задано в ячейке F2 (смотрите рисунок, приведённый выше).

1. range
   (диапазон) – так как мы ищем по ID
   продавца, значениями этого аргумента будут значения в столбце B основной таблицы (Main table). Можно задать диапазон B:B
   (весь столбец) или, преобразовав данные в таблицу, использовать имя столбца Main_table
   .

criteria
(критерий) – так как имена продавцов записаны в просматриваемой таблице (Lookup table), используем функцию ВПР
для поиска ID
, соответствующего заданному продавцу. Имя записано в ячейке F2, поэтому для поиска используем формулу:

VLOOKUP($F$2,Lookup_table,2,FALSE)
ВПР($F$2;Lookup_table;2;ЛОЖЬ)

Конечно, Вы могли бы ввести имя как искомое значение напрямую в функцию ВПР
, но лучше использовать абсолютную ссылку на ячейку, поскольку так мы создаём универсальную формулу, которая будет работать для любого значения, введённого в эту ячейку.