7 примеров использования формулы суммесли в excel с несколькими условиями

Содержание:

Как работает функция СУММЕСЛИМН?

Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.

Синтаксис функции выглядит так:

  • диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
  • диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
  • , условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;

в Excel 2003 нет функции суммеслимн и как быть? Помогите!Отчет!

​ и выберите команду​​ не может одновременно​=СЧЁТЕСЛИ(D6:D25;I6)​ меня научил еще​условие 2 – женский​Диапазон суммирования – это​ преобразованы к числовым​Примечание: если критерии переданы​ университете. Определить итоговую​ в ячейке напишем​

​ отдела, код, т.​​ функции СУММПРОИЗВ() выглядит​

​ используется функция СУММЕСЛИ(),​​ диапазоны условий, нужно​

​ ежедневно. В конце​​ правильным порядком аргументов.​​в диапазоне ячеек,​​Копировать​

​ быть и 5​​ikki​ бы так формулы​

​ (ж).​​ заработные платы, потому​

​ значениям 1 и​​ в качестве константы​ оценку для студентов​ такую. =СУММЕСЛИ(A2:A7;B10;C2:C7)+СУММЕСЛИ(A2:A7;B10;D2:D7)​

​ д. Какие символы​​ так : =СУММПРОИЗВ((B5:B15)*(B5:B15>0))​ английская версия SUMIF().​​ выделить столбцы, которые​​ месяца для расчета​Одинаковое количество строк и​ заданных аргументом​. На новом листе​​ и 6.​

​: meloman, пример иметь​​ писать.​​Итог: все продавцы-женщины в​

​ что нам нужно​​ 0 соответственно и​ массива для двух​ с фамилией, начинающейся​Этой формулой мы​ можно использовать в​

​Форма задания​​СУММЕСЛИДиапазонУсловие​ их содержат. Сами​ премии Щербакову А.​

​ столбцов для аргументов,​​Диапазон_условия2​ щелкните правой кнопкой​Igor67​ недостаточно. нужно иметь​

​vikttur​​ январе получили в​ узнать сумму зарплат​​ будут учтены в​ и более пар​

​ на букву «А»​​ считаем выборочные данные​ формуле, что они​условия​​; )​ условия прописываются как​ К. нужно узнать,​

​ задающих диапазоны ячеек​​(C2:C9). Затем функция​ мыши ячейку A1​:​ еще кое-что.​: GIG_ant, Владимир, Михаил,​ сумме 51100 рублей.​ всех продавцов. Поэтому​ итоговой сумме.​ диапазон_условияN; условиеN, результат​ при условии, что​ из столбцов C​ означают, смотрите в​(критерия) достаточно гибка.​

​Диапазон​​ текстовые, аналогично предыдущему​ какова общая сумма​​Аргумент​ суммирует соответствующие обоим​

​ и в разделе​​Михаил С.,​​в вашем примере​ ztn3t1 благодарности не​Функции СУММЕСЛИ и СУММЕСЛИМН​ F2:F14.​Число ячеек в диапазонах​ выполнения формулы будет​ минимальный балл должен​ и D по​ статье «Символы в​ Например, в формуле​   — диапазон ячеек,​ примеру.​ заказов, реализованных этим​Диапазон_условия​

​ условиям значения в​​Параметры вставки​ну да, что​ в функции СУММЕСЛИМН​​ заслуживают?​ хороши тем, что​​Получилось 92900. Т.е. функция​ суммирования и условиях​ некорректным.​ быть не менее​​ первому отделу.​ формулах Excel» тут.​ =СУММЕСЛИ(B5:B15;D7&D8) критерий D7​ в котором ищутся​

​Часто возникает необходимость использовать​​ сотрудником. В таких​должен иметь то​ диапазоне ячеек, заданном​выберите команду​ то не досмотрел​ вообще нет никакой​

​kim​​ они автоматически подстраиваются​ автоматически проработала список​ должно быть одинаковым,​​Полученное значение:​ 5 (успеваемость студентов​

​Функцией «СУММЕСЛИ» в​​В строке «Диапазон​​ содержится текстовое значение​

​ значения соответствующие аргументу​​ в качестве критерия​ случаях в Excel​ же количество строк​​ аргументом​Использовать форматы конечных ячеек​ ведь там 1​ необходимости.​

​: Чтоб не сильно​​ под изменение условий.​ должностей, выбрала из​ иначе функция СУММЕСЛИМН​Функция имеет следующую синтаксическую​

​ оценивается по 10-бальной​​Excel​ суммирования» мы указываем​ D8 – число​Условие​ не определенное значение,​ функция СУММЕСЛИ и​​ и столбцов, что​

​Диапазон_суммирования​​.​ диапазон суммирования и​Guest​

​ тормозило при большом​​ Т.е. мы можем​ них только продавцов​ вернет код ошибки​​ запись:​ шкале).​

​можно посчитать, только,​​ диапазон столбца с​ 56 (см. рисунок​​. Диапазон может содержать​ а область значений,​

​ используется. Для вычисления​​ и аргумент​(A2:A9). Результат — 30.​Проданное количество​ несколько диапазонов условий​: а как можно​

planetaexcel.ru>

СУММЕСЛИ

Аргументы

Рассмотрим аргументы для СУММЕСЛИ.

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

Диапазон – здесь необходимо указать, тот столбец, данные в котором будут сравниваться с заданным Критерием. В примере, это D2:D26.

Критерий – здесь нужно выбрать одно значение из ранее указанного Диапазона. Сначала будем указывать мальчиков, затем девочек.

Диапазон суммирования – это столбец, значения в котором нужно просуммировать, если они относятся к выбранному Критерию. Например, если для выбранного Диапазона D2:D26 указан Критерий «мальчик», значит в столбце «Средний бал»: С2:С26, будут суммироваться все значения, которые относятся к мальчикам.

Пример

Выделяем ячейку G4 и ставим «=». Пишем в ней СУММЕСЛИ и открываем скобку «(». В качестве Диапазона, из которого будут выбираться значения, выделяем D2:D26. В качестве Критерия, нужно выбрать одно из значений в ранее указанном Диапазоне. У нас это «мальчик», поэтому выделяем ячейку D3. Диапазон суммирования – это столбец, в котором указан «Средний бал». Выделяем С2:С26. Между аргументами ставьте «;», в конце, закройте скобку «)». Нажмите «Enter».

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

Теперь давайте растянем формулу по столбцу. Конечно, можно также прописать ее и для девочек, но если у Вас много разных критериев – это займет немало времени. Закрепим диапазоны для столбцов С и D, чтобы при копировании формулы они не ползли вниз.

Для этого выделяем G4 и в «Строке формул» после D2, D26, C2, C26 нажимаем клавишу «F4». В адресах на ячейки, появятся знаки «$». Таким образом, мы делаем ссылки абсолютными. Чтобы растянуть формулу по столбцу, потяните за правый нижний угол ячейки, курсор изменит при этом вид на черный плюсик.

Если Вам интересно, как сделать ссылки в Эксель: абсолютные, относительные, на другой лист или книгу, прочтите статью по этой теме.

Дальше смотрим на «Строку формул». В качестве аргументов для Диапазона и Диапазона суммирования берутся правильные значения: D2:D26, C2:C26. В качестве Критерия укажите адрес нужной ячейки. Поскольку, нужно посчитать средний балл для всех девочек, в столбце D выбираем адрес любой ячейки, в которой указано «девочка»: D4.

Использование СУММЕСЛИМН и СУММЕСЛИ в Excel — что нужно запомнить?

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

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

1. Порядок аргументов

Аргументы применяются по-разному. В частности, диапазон_сумирования является 1-м параметром в СУММЕСЛИ, но является третьим в СУММЕСЛИМН.

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

В СУММЕСЛИМН он, напротив, очень важен и обязателен, и поэтому и стоит первым. Вероятно, ребята из Microsoft подумали, что после добавления 10- й или 100- й пары диапазон/критерий кто-то может забыть указать диапазон для суммирования:)

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

В функции СУММЕСЛИ эти аргументы не обязательно должны иметь одинаковую размерность. Достаточно указать начальную точку. В СУММЕСЛИМН они должны содержать одинаковое количество строк и столбцов.

Выражение =СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F22;I3) вернет сообщение об ошибке #ЗНАЧ!, так как второй параметр поиска (F2:F22) не совпадает по размеру с остальными (E2:E21) и (C2:C21).

Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (к примерам).

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

​ условие, указав для​ можно оперативнее обеспечивать​ (например, содержащие «green»​Пример 7.​ воспользуйтесь функцией «СЧЕТЕСЛИ».​Пример 2.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН(J2:K51;C2:D51;»Орск»;H2:I51;»Маты МП 50 мм»)​так​ замечательной возможностью Excel​ Григорьева для «Копейки»),​

Синтаксис СУММЕСЛИМН и распространенные ошибки

​ для магазина «Копейка».​

​ клавишу TAB.​ формат, текстовый; «воспринимает»​ каждому менеджеру.​В строке «Диапазон​
​Суммирует продаж мяса в​ с функцией СУММЕСЛИМН,​ функции расположение данных​ вас актуальными справочными​ и больше 9),​Нужно посчитать продажи​ Подробнее об этой​Нужно посчитать, сколько​К сожалению, нет​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН($K$2:$K$51;$C$2:$C$51;A58;$I$2:$I$51;B58)+СУММЕСЛИМН($J$2:$J$51;$C$2:$C$51;A58;$H$2:$H$51;B58)​ как формулы массива,​ то функция​Если бы в нашей​В нашем примере диапазон​ математические операторы

Например,​Внимание!​ суммирования» пишем диапазон​Столбец C в​ то некоторые образцы​
​ (a2: a11), а​ материалами на вашем​ применяйте функцию​ Васечкиным по всем​ функции читайте в​ продано всех фруктов​ способа превращения массива​или такКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(($C$2:$C$51=A58)*($H$2:$H$51=B58)*$J$2:$J$51+($C$2:$C$51=A58)*($I$2:$I$51=B58)*$K$2:$K$51)​ то советую почитать​. ​СУММЕСЛИ (SUMIF)​ задаче было только​

​СУММЕСЛИ (SUMIF)​ задаче было только​

  1. ​ суммирования – это​ 45; «​В условиях этой​ D2-D8 – это​ южном​ данных и формулы,​
  2. ​ также какие условием​ языке. Эта страница​СЧЁТЕСЛИМН​ филиалам конкретного магазина.​ статье «Функция «СЧЕТЕСЛИ»​ на букву «Я».​ в диапазон, подходящий​
  3. ​ZORRO2005​ предварительно про них​не поможет, т.к.​ одно условие (все​ диапазон ячеек с​Диапазон ячеек для проверки​ функции «СУММЕСЛИМН» количество​ столбец с суммами​

​область в столбце​ использующей функцию.​

  • ​ является — наилучшим​ переведена автоматически, поэтому​(COUNTIFS).​ Таблица такая.​ в Excel».​
  • ​ В условиях функции​ для таких функций​: или так:​

Примеры функции СУММЕСЛИМН в Excel

​ не умеет проверять​ заказы Петрова или​ количеством оказанных услуг.​ условия 2; условие​ ячеек диапазона суммирования​

​ покупок.​ A (результат —​Вы можете работать с​ образом «Южный». Обратите​

​ ее текст может​=СЧЁТЕСЛИМН(A1:A5;»green»;B1:B5;»>9″)​

​В условиях функции напишем​Пример 4.​ «СУММЕСЛИМН» напишем так.​ как СУММЕСЛИ, СУММЕСЛИМН…​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((C$2:C$51=»Орск»)*(H$2:I$51=»Маты МП 50 мм»)*J$2:K$51)​ Ну, а в​ больше одного критерия.​ все заказы в​ В качестве первого​ 2; … необязательные​ и количество ячеек​В строке «Диапазон​ 14 719).​ демонстрационными данными непосредственно​ внимание, запятые между​ содержать неточности и​

​=COUNTIFS(A1:A5,»green»,B1:B5,»>9″)​ так — «*Монетка*».​Функция «СУММЕСЛИМН» в​ «=я*» Вся формула​Но это уже​Perfect2You​ нашем случае задача​ Поэтому начиная с​ «Копейку», например), то​
​ аргумента выбираем столбец​ аргументы для назначения​ диапазона условий должно​ условия1» пишем диапазон​= SUMIFS(D2:D11,A2:A11,​ в этой книге​ отдельные аргументы:​ грамматические ошибки. Для​Для суммирования диапазона ячеек​ Формула получится такая.​Excel с датами​ будет такая
=СУММЕСЛИМН(D2:D8;C2:C8;»=я*»)​ другая тема​: Да, СУММЕСЛИМН работает​ решается одной формулой:​
​ версии Excel 2007​ задача решалась бы​ «Количество» (Е2:Е11). Название​ дополнительных диапазонов и​ быть одинаковое

Например,​ столбца с менеджерами.​«Южно» C2: C11,​ веб-приложения Excel Online.​
​= SUMIFS(D2:D11,A2:A11,»South»,​ нас важно, чтобы​ используйте функцию​

​=СУММЕСЛИМН(D2:D8;B2:B8;»Васечкин»;A2:A8;»*Монетка*»)​

​.​Этой формулой посчитаются​Belka​

​ только по одномерному​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​ в набор функций​ достаточно легко при​ столбца не нужно​ условий для них.​ все диапазоны с​

​ У нас -​ «Мясо»)​ Изменяйте значения и​Кавычки вокруг слова «Южный»​ эта статья была​СУММ​Последним условием мы​Нужно сложить данные​ все слова в​: Добрый день. Задача​ массиву (столбцу, либо​После ввода этой формулы​

​ была добавлена функция​ помощи встроенной функции​ включать.​

​ Excel может проработать​ 3 по 8​

​ B2-B8.​Примечания:​ формулы или добавляйте​

exceltable.com>

Примеры

Чтобы использовать эти примеры в Excel, выделите нужные данные в таблице, щелкните их правой кнопкой мыши и выберите команду Копировать. На новом листе щелкните правой кнопкой мыши ячейку A1 и в разделе Параметры вставки выберите команду Использовать форматы конечных ячеек.

=СУММЕСЛИМН(A2:A9; B2:B9; “=Я*”; C2:C9; “Артем”)

Суммирует количество продуктов, названия которых начинаются с Я и которые были проданы продавцом Артем. Подстановочный знак (*) в аргументе Условие1 ( “=Я*”) используется для поиска соответствующих названий продуктов в диапазоне ячеек, заданных аргументом Диапазон_условия1 (B2:B9). Кроме того, функция выполняет поиск имени “Артем” в диапазоне ячеек, заданных аргументом Диапазон_условия2 (C2:C9). Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 20.

=СУММЕСЛИМН(A2:A9; B2:B9; “Бананы”; C2:C9; “Артем”)

Суммирует количество продуктов, которые не являются бананами и которые были проданы продавцом по имени Артем. С помощью оператора в аргументе Условие1 из поиска исключаются бананы ( “Бананы”). Кроме того, функция выполняет поиск имени “Артем” в диапазоне ячеек, заданных аргументом Диапазон_условия2 (C2:C9). Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 30.

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

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

Динамический диапазон суммирования по условию в Excel

Пример 1. В таблице содержатся данные о успеваемости студентов по некоторому предмету в университете. Определить итоговую оценку для студентов с фамилией, начинающейся на букву «А» при условии, что минимальный балл должен быть не менее 5 (успеваемость студентов оценивается по 10-бальной шкале).

Вид таблицы данных:

Для расчета суммарного балла согласно установленным критериям используем следующую формулу:

  • C3:C14 – диапазон ячеек с баллами, из которых будут автоматически выбраны значения для расчета суммы, которые соответствуют установленным критериям;
  • C3:C14 – первый диапазон ячеек, к которому будет применен первый критерий;
  • «>5» – первое условие отбора значений из указанного выше диапазона;
  • B3:B14 – второй диапазон ячеек (с фамилиями студентов), к которому будет применен второй критерий;
  • «А*» – второе условие отбора значений (все фамилии, которые начинаются с буквы «А»).

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

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

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

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

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

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

Теперь давайте рассмотрим, пример, когда критерий не совпадает с диапазоном суммирования. Такая ситуация встречается гораздо чаще. Рассмотрим те же условные данные. Пусть нам нужно узнать сумму не больше или меньше какого-то значения, а сумму конкретной группы товаров, допустим, группы Г.

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные

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

Заполненные поля Мастера функций при подобном расчете будут выглядеть примерно так.

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

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

Постановка задачи

​ Чтобы отобразить результаты​ аргументу​

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

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

​Михаил С.​ всему столбцу C​В категории​ образом, но имеет​ Excel​В нашем примере диапазон​ (первого аргумента).​ д.​ особенностей применения следует​ что в Excel​​ вычислить сумму только​​ формул, выделите их​​диапазон​​ формате Excel (см.​ автофильтром выглядит так​ таблицы. Размещение условий​​ на ячейку или​​: формулы массива =сумм(суммеслимн(a1:a5;b1:b5;{5:6})​ — получите циклическую​​Базы данных (Database)​​ больше аргументов:​

​СУММЕСЛИ (SUMIF)​​ суммирования – это​​Условие 1. Обязательный аргумент,​

  • ​Подробнее о символах,​​ отметить, что совпадение​ функция СУММЕСЛИ позволяет​ по элементам, соответствующим​​ и нажмите клавишу​​. Пример:​ примеры ниже).​ (см. файл примера​ в одной строке​
  • ​ текста, определяющих, какие​​ =сумм(суммесли(b1:b5;{5:6};a1:a5)или простой ввод​ ссылку!!!​ можно найти функцию​При помощи полосы прокрутки​ из категории​ диапазон ячеек с​ составляющий пару предыдущему.​ которые можно написать​ по тексту может​ оперативно суммировать нужные​ определенным критериям.​ F2, а затем —​Диапазон​Условие​ Лист 2 Даты).​ соответствует Условию И​ ячейки будут просуммированы.​ =суммпроизв(суммеслимн(a1:a5;b1:b5;{5:6}) =суммпроизв(суммесли(b1:b5;{5:6};a1:a5)​​китин​​БДСУММ (DSUM)​ в правой части​Математические (Math&Trig)​ количеством оказанных услуг.​ Критерий, по которому​ в формуле и​​ быть не точным.​​ показатели.​Аргументы этой функции:​
  • ​ клавишу ВВОД. Кроме​​Диапазон суммирования​   . Обязательный аргумент. Условие​Предварительно таблицу нужно преобразовать​ (см. диапазон​Порядок аргументов различен в​

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

​Igor67,​: СУММЕСЛИМН считает только​, которая тоже способна​ окна можно задать​. Выделяем пустую ячейку​​ В качестве первого​​ определяются ячейки для​ что они означают,​ Для нахождения суммы​Бывают случаи, когда суммирование​Диапазон – все ячейки,​ того, вы можете​Фактические ячейки​​ в форме числа,​​ в формат таблиц​D14:E15​ функциях СУММЕСЛИМН() и​ошибаешься. Так выдаст​ в диапазоне 1​ решить нашу задачу.​​ и третью пару​​ для результата, жмем​ аргумента выбираем столбец​ суммирования в диапазоне​

​ читайте в статье​ по заказам в​ необходимо провести не​ в которых следует​ настроить ширину столбцов​​A1:A5​​ выражения, ссылки на​​ MS EXCEL 2007​​).​ СУММЕСЛИ(). В СУММЕСЛИМН()​ ноль — критерий​

​ строки или1 столбца.​ Нюанс состоит в​ (​ кнопку​ «Количество» (Е2:Е11). Название​ условия 1. Условие​ «Символы в формулах​ Москве и Московской​

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

​ по одному, а​ искать заданный критерий​ в соответствии с​B1:B5​ ячейку, текста или​ и включить строку​Примечание​ аргумент​ не может одновременно​ если хотите использовать​ том, что для​Диапазон_условия3​fx​

​ столбца не нужно​

​ может иметь числовой​ Excel» тут.​ области в поле​ по нескольким условиям.​Собственно критерий – условие,​ содержащимися в них​B1:B5​ функции, определяющее, какие​ Итогов.​: для удобства, строки,​интервал_суммирования​ быть и 5​ СУММЕСЛИМН то так​ работы этой функции​-​в строке формул,​

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

​ включать.​ формат, текстовый; «воспринимает»​Второй пример.​ критерия вводится значение​ Начиная с 2007​ по которому происходит​ данными.​A1:A5​ ячейки необходимо суммировать.​Примечание:​

​ участвующие в суммировании,​

​является первым аргументом,​ и 6.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН(K1:K50;C1:C50;»Орск»;H1:H50;»Маты МП 50 мм»;I1:I50;»Маты​ необходимо создать на​​Условие3​ находим функцию​​Первое условие, которое нужно​ математические операторы. Например,​Сумма выборочных ячеек​ «*моск*». Выделение звездочками​ года, суммировать с​ суммирование (это может​Категория​B1:B3​ Например, условие может​ Мы стараемся как можно​ выделены Условным форматированием​ а в СУММЕСЛИ()​Igor67​

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

​ МП 50 мм»)+СУММЕСЛИМН(J1:J50;C1:C50;»Орск»;H1:H50;»Маты​​ листе специальный диапазон​​), и четвертую, и​​СУММЕСЛИ​​ соблюсти при нахождении​ 45; «​Excel.​ предполагает частичное совпадение​ указанием нескольких условий​ быть текст, логические​Продукты​B1:B5​ быть представлено в​ оперативнее обеспечивать вас​ с правилом =И($A2=$D$2;$B2>=$E$2)​ – третьим. При​

​:​

planetaexcel.ru>

Как суммировать каждые n строк в Excel?

Предположим, у вас есть длинный список данных на вашем листе, и вам нужно просуммировать каждые следующие 5 строк следующим образом: = СУММ (B2: B6), = СУММ (B7: B11) и так далее, а затем поместить результат в другой столбец, как показано на следующем снимке экрана. В этой статье я расскажу о некоторых хороших формулах для решения этой проблемы.

Суммируйте каждые n строк в Excel с помощью формул

В этом примере я суммирую каждые 5 строк данных, пожалуйста, примените следующую формулу:

1. Введите эту формулу в пустую ячейку, в которую вы хотите поместить результат: = СУММ (СМЕЩЕНИЕ ($ 2 млрд; (СТРОКА () — СТРОКА ($ 2 млрд)) * 5,0,5,1))

Чаевые: В приведенной выше формуле B2 указывает начальный номер строки, которую вы хотите суммировать, и 5 обозначает увеличивающиеся номера строк. Если вы хотите суммировать каждые 10 строк в столбце B, вам просто нужно изменить 5 на 10.

2. Затем перетащите маркер заполнения к ячейкам, пока не отобразится 0. И вы получите нужный результат. Смотрите скриншот:

Ноты:

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

В этом случае введите эту формулу: = ЕСЛИ (MOD (СТРОКА (); 5); «»; СУММ (СМЕЩЕНИЕ (B1 ,,, — 5))) (B1 — первое значение ячейки, которое вы хотите просуммировать в столбце B, и 5 — порядковые номера строк) в ячейку C1, а затем перетащите маркер заполнения в ячейки, к которым вы хотите применить эту формулу.

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

2. Если у вас есть список значений в строке, вы также можете использовать формулу для суммирования всех n столбцов, =SUM(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5)) (A1 ссылка на ячейку, в которой находится ваше первое значение, A3 обозначает ячейку, в которую вы вводите эту формулу, и 5 — порядковые номера столбцов), а затем перетащите маркер заполнения в правые ячейки, пока не отобразится 0, см. снимки экрана:

Суммируйте каждые n строк в Excel с помощью Kutools for Excel

Если у вас есть Kutools for Excel, вы можете вставлять разрывы страниц через каждые n строк, а затем вычислять промежуточные итоги разбиения по страницам для получения результата.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней. 
Перейти к загрузкеБесплатная пробная версия 30 днейпокупкаPayPal / MyCommerce

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Kutools Plus > печать > Вставить разрыв страницы в каждую строку, см. снимок экрана:

2. В Вставить разрыв страницы в каждую строку В диалоговом окне укажите интервал строк, в который вы хотите вставить разрывы страниц, и разрывы страниц были вставлены в данные каждые 5 строк, см. снимок экрана:

3, Затем нажмите Kutools Plus > печать > Промежуточные итоги по страницам, см. снимок экрана:

4. В Промежуточные итоги по страницам В диалоговом окне проверьте заголовок столбца, в котором вы хотите выполнить некоторые вычисления, и выберите нужную функцию, см. снимок экрана:

5. Затем нажмите Ok, все данные каждые 5 строк суммированы, см. снимок экрана:

Демо: сумма / среднее значение каждые n строк в Excel с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

1. = СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;”>=”&E2)

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

  • B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
  • A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
  • D2 и “>=”&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

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

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

  • Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2:A13=D2 , а затем нажав F9 >;
  • Результатом вычисления B2:B13 является массив {3:5:11:98:4:8:56:2:4:6:10:11}, т.е. просто значения из столбца B >;
  • Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
  • Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив {0:0:1:1:0:1:1:0:0:1:1:1}. Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
  • Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector