03.05.2016      300      2
 

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


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

Для примера рассмотрим следующую таблицу. В ней указано имя и пол ученика, в каком классе учится и средний бал. Используя функцию СУММЕСЛИ, решим такую задачу: посчитаем сумму значений из столбца «Средний бал» для всех мальчиков и для всех девочек. Задача для функции СУММЕСЛИМН такая: посчитать сумму среднего бала для мальчиков и девочек, которые учатся в 8 и 9 классе отдельно.

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

СУММЕСЛИ

Аргументы

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

=СУММЕСЛИ(диапазон; критерий; [диапазон суммирования])

Диапазон – здесь необходимо указать, тот столбец, данные в котором будут сравниваться с заданным Критерием. В примере, это 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; диапазон условия2; условие2)

Диапазон суммирования – это столбец, значения из которого будут выбираться, если они соответствуют одному и второму заданному условию. В примере, это С2:С26.

Диапазон условия1 – это первый диапазон для сравнения. Нужно выбрать пол ученика, поэтому напишем D2:D26.

Условие1 – нужно указать то значение, которое будет выбираться из Диапазона условий1. Нас интересуют мальчики, поэтому укажем D3.

Диапазон условия2 – второй диапазон сравнения. Здесь будем выбирать класс, в котором учится ребенок. Соответственно укажем В2:В26.

Условие2 – конкретное значение, которое нас интересует из Диапазона условий2. Сначала нужно будет выделить ячейку, в которой указан «8 класс», затем «9 класс».

Пример

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

В «Строке формул» сразу сделайте абсолютные ссылки на столбцы: Класс, Средний бал, Мал/Дев.

Растягиваем формулу на Н8:Н11. Теперь укажем правильные ячейки в качестве Условия1 и Условия2.

В Н9 считается средний балл для мальчиков, которые учатся в 9 классе. Поэтому, Условие1 для первого диапазона «мальчик»: D3, Условие2 для второго диапазона «9»: В6.

В Н10 считаем средний балл девочек, которые учатся в 8 классе. Условие1 – «девочка», D4, Условие2 – «8», В3.

В Н11 считаем средний балл девочек, которые учатся в 9 классе. Условие1 – «девочка», D4, Условие2 – «9», В2.

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

Давайте теперь проверим, чтобы результат был правильным. Посчитаем сумму «Среднего бала» для всех значений в исходной таблице, для Задачи 1 и для Задачи 2. Сумма должна быть одинаковой.

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

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


1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
Об авторе: Олег Каминский

Вебмастер. Высшее образование по специальности "Защита информации". Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности

Обсуждение: 2 комментария
  1. Евгений:

    Отличная статья!

    Ответить
  2. Галина:

    Спасибо. очень понятно и доступно

    Ответить

Ваш комментарий

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

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

Последние комментарии