Формула в эксель суммеслимн

Формула в эксель суммеслимн

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

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

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

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

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

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

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

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

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

Выборочное суммирование по условию в Excel

Пример 2. В таблице указаны данные об отгрузках трех товаров в различные страны на протяжении трех месяцев. Определить суммарный доход от отгрузок товара №2 в Казахстан за июнь и август.

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

Для расчета итоговой суммы используем формулу:

Примечание: формула выглядит громоздкой несмотря на то, что для столбца «Месяц» было использовано всего 2 критерия отбора. Формулы с несколькими критериями для одного диапазона данных можно существенно сократить способом, который будет показан в следующем примере.

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

Пример 3. Используя таблицу из второго примера определить суммарной прибыли от поставок товара №1 в Китай и Грузию на протяжении всего периода (трех месяцев).

Для нахождения искомого значения используем формулу массива (для ввода жмем CTRL+SHIFT+Enter):

Функция СУММЕСЛИМН возвращает массив значений для критериев «Китай» и «Грузия» соответственно, которые суммируются функцией СУММ.

Примечание: если критерии переданы в качестве константы массива для двух и более пар диапазон_условияN; условиеN, результат выполнения формулы будет некорректным.

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

Функция имеет следующую синтаксическую запись:

=СУММЕСЛИМН( диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

  • диапазон_суммирования – обязательный аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
  • диапазон_условия1 – обязательный аргумент, принимающий ссылку на ячейки, в которых содержатся данные для проверки на соответствие заданному критерию;
  • условие1 – обязательный аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (например, «>=100» или «<>0»), проверяемый текст (например, «Продавец_1», «Товар_1») или числовое значение (Например, 20), определяющие ячейки в диапазоне условия1, для которых будут отобраны и просуммированы данные из диапазона суммирования.
Читайте также:  Psychotoxic gateway to hell

Последующие аргументы являются необязательными для заполнения, а их смысл соответствует аргументам диапазон_условия1; условие1. Всего может быть задано до 127 условий отбора данных для суммирования.

  1. Если в качестве аргумента диапазон_суммирования была передана ссылка на диапазон ячеек, содержащие текстовые строки или пустые значения, функция СУММЕСЛИМН будет их игнорировать.
  2. В качестве условий суммирования данных могут выступать числовые значения, логические выражения, текстовые строки, формулы, ссылки на ячейки, содержащие условия.
  3. Условия проверки в виде текста или выражений с логическими и математическими символами должны быть указаны в кавычках.
  4. Максимальная длина условия, переданного в качестве аргумента условие1 (условие2 и т. д.) не должна превышать 255 символов.
  5. Для создания неточных текстовых критериев поиска требуемых значений можно использовать подстановочные знаки «?» и «*», замещающие один или несколько символов соответственно.
  6. Если в диапазоне ячеек, переданных в качестве аргумента диапазон_суммирования, есть ячейки, содержащие логические ИСТИНА или ЛОЖЬ, они будут автоматически преобразованы к числовым значениям 1 и 0 соответственно и будут учтены в итоговой сумме.
  7. Число ячеек в диапазонах суммирования и условиях должно быть одинаковым, иначе функция СУММЕСЛИМН вернет код ошибки #ЗНАЧ!.

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

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

Аргументы функции СУММЕСЛИМН:

  1. Диапазон ячеек для нахождения суммы. Обязательный аргумент, где указаны данные для суммирования.
  2. Диапазон ячеек для проверки условия 1. Обязательный аргумент, к которому применяется заданное условие поиска. Найденные в этом массиве данные суммируются в пределах диапазона для суммирования (первого аргумента).
  3. Условие 1. Обязательный аргумент, составляющий пару предыдущему. Критерий, по которому определяются ячейки для суммирования в диапазоне условия 1. Условие может иметь числовой формат, текстовый; «воспринимает» математические операторы. Например, 45; « , = и др.).
  4. 

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

У нас есть таблица с данными об оказанных услугах клиентам из разных городов с номерами договоров.

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

Как использовать функцию СУММЕСЛИМН в Excel:

  1. Вызываем «Мастер функций». В категории «Математические» находим СУММЕСЛИМН. Можно поставить в ячейке знак «равно» и начать вводить название функции. Excel покажет список функций, которые имеют в названии такое начало. Выбираем необходимую двойным щелчком мыши или просто смещаем курсор стрелкой на клавиатуре вниз по списку и жмем клавишу TAB.
  2. В нашем примере диапазон суммирования – это диапазон ячеек с количеством оказанных услуг. В качестве первого аргумента выбираем столбец «Количество» (Е2:Е11). Название столбца не нужно включать.
  3. Первое условие, которое нужно соблюсти при нахождении суммы, – определенный город. Диапазон ячеек для проверки условия 1 – столбец с названиями городов (С2:С11). Условие 1 – это название города, для которого необходимо просуммировать услуги. Допустим, «Кемерово». Условие 1 – ссылка на ячейку с названием города (С3).
  4. Для учета вида услуг задаем второй диапазон условий – столбец «Услуга» (D2:D11). Условие 2 – это ссылка на определенную услугу. В частности, услугу 2 (D5).
  5. Вот так выглядит формула с двумя условиями для суммирования: =СУММЕСЛИМН(E2:E11;C2:C11;C3;D2:D11;D5).

Результат расчета – 68.

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

Теперь можно посмотреть, сколько услуг 2 оказано в том или ином городе (а не только в Кемерово). Формулу немного видоизменим: =СУММЕСЛИМН($E$2:$E$11;$C$2:$C$11;F$2;$D$2:$D$11;$D$5).

Все диапазоны для суммирования и проверки условий нужно закрепить (кнопка F4). Условие 1 – название города – ссылка на первую ячейку выпадающего списка. Ссылку на условие 2 тоже делаем постоянной. Для проверки из списка городов выберем «Кемерово»:

Результат тот же – 68.

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

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

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

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

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

Для наглядности я перенес данную таблицу на один лист с исходными данными.

Синтаксис функции СУММЕСЛИМН:

СУММЕСЛИМН( диапазон_суммирования ; диапазон_условий1 ; условия1 ;[ диапазон_условий2 ; условия2 ];…)
диапазон_суммирования — В нашем случае нам необходимо просуммировать количество подключенных услуг, поэтому это столбец Количество и диапазон Е2:E646

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

  1. должна совпадать фамилия сотрудника;
  2. должна совпадать услуга;
  3. должен совпадать город.

диапазон_условий1 — первое условие у нас сотрудники и диапазон условий это столбец с именами ФИО сотрудников A2:A646

условия1 — это сам сотрудник, так как мы начинаем прописывать формулу напротив сотрудника Апанасенко Е.П то и условия1 у нас будет ссылка на его ячейку G3

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

Продолжим, следующая условие это услуга

диапазон_условий2 — это столбец с услугами D2:D646

условия2 — это ссылка на услугу 1, то есть H2

Вот как должна выглядеть наша формула на текущий момент:

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2

Добавляем третье условие по городам

диапазон_условий3 — диапазон условий по городам это столбец «Город клиента» и диапазон B2:B646

условия3 — это ссылка на город в раскрывающемся списке G1

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

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2; B2:B646; G1 )

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

Во-первых все диапазоны условий у нас не двигаются и постоянны поэтому закрепим их с помощью знака доллара (выделить данный диапазон в формуле и нажать клавишу F4):

Диапазон суммирования у нас так же постоянный E2:E646 → $E$2:$E$646

Так же условия3 по городу G1 y нас всегда находится только в ячейке G1 и не должен смещаться при протягивании, поэтому так же закрепляем данную ячейку

G1$G$1

Услуги (условия2) при протягивании вправо должны меняться по столбцам, а вот строка при протягивании вниз не должна меняться, поэтому закрепляем только строку

H2 → H$2

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

G3 → $G3

Итоговая формула будет выглядеть следующим образом

=СУММЕСЛИМН( $E$2:$E$646 ; $A$2:$A$646 ; $G3 ; $D$2:$D$646 ; H$2; $B$2:$B$646 ;$ G$1 )

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

Ссылка на основную публикацию
Установка mac os transmac
В сети сейчас полно копипастов, по сути одной и той же статьи, про установку MacOS X на хакинтош примерно с...
Тест для определения цвета волос
Пожалуйста, не копируйте понравившиеся вам статьи незаконно. Мы предлагаем вам разместить активную ссылку на наш сайт в случае, если вы...
Тест графики видеокарты 3dmark
Наиболее известная программа тестирования производительности, ставшая де-факто стандартом и точкой отсчета в измерениях игровых возможностей видеокарт. Основную популярность программе обеспечило...
Установка op com на windows 10
Всем привет! Очень многие вектроводы заказывают с Китая OP-COM и сталкиваются с проблемами установки драйверов самого OP-COM на различных системах...
Adblock detector