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

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

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

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

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

Функция Суммеслимн - исходные данные таблицы

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

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

Функция Суммеслимн - исходные данные с таблицей

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

СУММЕСЛИМН(диапазон_суммирования;диапазон_условий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):

A2:A646 → $A$2:$A$646

D2:D646 → $D$2:$D$646 

B2:B646 → $B$2:$B$646

Диапазон суммирования у нас так же постоянный 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)

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

Пример файла: Скачать

 

Поделиться:
14 Комментариев
  • Андрей:

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

    • excel:

      То что, у вас таблицы одни вертикальные, а другие горизонтальные не очень удобно.

      Я вы сделал вспомогательный на листе «Налет ЛС» и уже оттуда подтягивал бы в график. (см. вложение)

  • Мария:

    вложение  report.xlsx

    Добрый день! Подскажите, пожалуйста, как подсчитать сумму продаж за определенный период по определенному артикулу в шутках и деньгах? В исходниках есть продажи по артикулам за каждый день (пример во вложении)

    Спасибо заранее!

  • Сергей:

    Есть у меня проблема, а где и кому задать вопрос, не знаю.

    Есть два листа Excel. На первом — табель, в котором, в столбце В список сотрудников. На втором — расчёт зарплаты, где в столбце В аналогичный список. При изменении списка на первом листе, при удалении или добавлении ФИО, автоматически изменяется список на втором листе (ячейки связаны) Если сделать сортировку (от А до Я) на первом листе, то автоматически сортируется и список на втором листе. Вопрос! Как сделать так, чтобы диапазон ячеек правее столбца В на втором листе был привязан к соответствующей ячейке в столбце В и перемещался в соответствии с автоматической сортировкой. Пример: В ячейке В3 указано «Иванов», в ячейке С3 — «79»; в ячейке D3 — «0,75» и т.д. При сортировке на первом листе «Иванов» оказывается на 7 строке, то же самое происходит и на втором листе, а данные в ячейках на втором листе остаются на 3 строке. Мне нужно, чтобы они перемещались вместе с «Ивановым». Если кто знает как это сделать, помогите!

    • excel:

      Вам поможет ВПР

      Во второй таблице данные значений по расчету зарплаты должны быть не числа а формулы привязанные к ФИО. Если значения по ЗП берутся из первой таблицы, то используйте ВПР.

      Если вы это рассчитываете по-другому или вставляете только значения, то сделайте отдельную таблицу (служебную, ее можно будет скрыть) куда необходимо снова ставить ФИО и данные по ЗП и из этой таблицы подтягивать значения ЗП с помощью все той же функции ВПР

  • Алла:

    Как создать фильтр городов ? Как вы создали фильтр на диапазоне G1 ?

  • Надежда:

    вложение 1

    А как быть, если в таблице есть ещё и заголовки и критерии меняются в зависимости от заголоков. В данном примере нужно вычленить личные звонки/смс и прочее из всех (личные+рабочие). Если заголовок «звонки на велком» или «звонки на мтс», то учитываются только те, что сделаны в выходной день (к примеру, 03.02.15) и до 8.00 или после 19.00. Причём, если оба условия выполняются, как к примеру в 5й строчке (и выходной — 03.02.15 и после 19.00), учитывать только один раз. Если заголовок «СМС» или «роуминг», то всё относить в личное. Т.е. решением должно быть: 100 (оба критерия выполняются) + 10 (выходной) + 20 (после 19.00) + 200 (смс всегда личные) + 2000 (роуминг всегда личные). Это возможно сделать с помощью формулы?

    • excel:

      В таком виде трудновато будет сделать, тем более если кол-во дней все время меняется.

      Я бы добавил вспомогательные столбец слева и присвоил бы каждой строчке тип услуги. В ячейке A3 написать формулу с ЕСЛИ, если B3="", то =B4, иначе =В3

      то есть мы привязываемся к пустой строке, которая отделяет типы звонков.

      Далее можно сделать еще один вспомогательный столбец — где вернуть день недели (выходной)

      После этого думаю уже можно посчитать

      • Надежда:

        А можно как-нибудь автоматически «добавить каждой строчке тип услуги»? Писать вручную — не вариант: таблица более 5000 строк. Можно как-нибудь скопировать столбец и вставить в пустой столбец, но так, чтобы там были только надписи «звонки на велком», «звонки на мтс», «смс» и т.д. Т.е. вставился только текст, без цифр.

        • excel:

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

          Либо если вам нужно «Можно как-нибудь скопировать столбец и вставить в пустой столбец, но так, чтобы там были только надписи», то сделать доп столбце и сделать проверку каждой строки с помощью функции ЕТЕКСТ

          Идея в следующем. Делаем проверку соседней ячейки. Если это текст, то возвращаем эту ячейку с текстом, в другом случае — возвращаем пустую строчку. Протягиваем и получаем нужный вам вариант

  • Владимир:

    вложение  -.xls

    Здравствуйте.

    Нужно написать формулу для переноса данных в зависимости от Ф.И.О., даты и марки автомобиля в табель учета рабочего времени. Сам пробовал, не получилось.

    Заранее благодарен.

    • excel:

      вложение  -1.xls

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

      Остальных людей подтягивайте по аналогии.

      При обновлении данных, нужно обязательно обновить сводную таблицу, правой кнопкой по таблицы и выбрать «Обновить».

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

      Сейчас понимаю, что можно вообще без этого обойтись, после объединения делаем доп. столбец, объединяем дату и фамилию, а далее через ВПР подтягиваем данные по этому столбцу. Искомое значение будет ФИО&Дата

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

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.