ifempty

    Урок 3. Функция СУММЕСЛИ

    Оглавление

    Введение

    Функция СУММЕСЛИ  - пример решения задачи №1

    Функция СУММЕСЛИ  - пример решения задачи №2

    Файлы для скачивания

     

    Введение

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

    Функция имеет три параметра:

    1. Диапазон - в данное поле указывается диапазон ячеек, среди которых программа будет искать критерий;
    2. Критерий - в данное поле указывается значение или ссылка на ячейку со значением, которое программа будет искать внутри вышеуказанного диапазона;
    3. Диапазон_суммирования - в данное поле указывается диапазон ячеек, которые нужно суммировать.

     СуммЕсли рис1

    Рисунок 1. Аргументы функции СУММЕСЛИ

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

     СуммЕсли рис2

     Рисунок 2. Пример работы функции СУММЕСЛИ 

    Теперь посмотрите на рисунок 3, на котором обозначены заполненные аргументы функции. На рисунке Диапазон отмечен красным цветом, Критерий -  зеленым, а Диапазон суммирования - синим. 

     СуммЕсли рис3

    Рисунок 3. Аргументы функции СУММЕСЛИ

     Таким образом, получается, что функция ищет Критерий внутри Диапазона и, если находит его, то суммирует значения ячеек внутри Диапазона суммирования

     

    Функция СУММЕСЛИ  - пример решения задачи №1

    Скачайте файл Функция СУММЕСЛИ список сотрудников.xlsx по ссылке ниже.

    Откройте лист Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем углу окна, как показано на рисунке 4.

     СуммЕсли рис4

     Рисунок 4. Кнопка Разрешить редактирование.

    Задача:

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

     СуммЕсли рис5

    Рисунок 5. Задача по практикуму 1

    Предварительный разбор:

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

    Критерием для первой строки будет ячейка F3, которая содержит название отдела. Этот критерий нужно искать внутри диапазона, а значит диапазоном будут ячейки B3:B45 (от B3 до B45 включительно), то есть список отделов в Таблице1. Суммировать нужно оклады, поэтому диапазоном суммирования будут ячейки D3:D45 (от D3 до D45 включительно), то есть список окладов в Таблице1.

    На рисунке 6 разными цветами выделены соответствующие диапазоны.

     СуммЕсли рис6

    Рисунок 6. Обозначение аргументов функции разными цветами

    Вызов функции СУММЕСЛИ:

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

     У вас откроется окно Аргументы функции, в котором нужно заполнить поля следующими данными:

    1. В поле Диапазон указываем: $B$3:$B$45
    2. В поле Критерий указываем: F3
    3. В поле Диапазон_суммирования указываем: $D$3:$D$45

     СуммЕсли рис7

    Рисунок 7. Заполненные аргументы функции СУММЕСЛИ

    После того, как закончите ввод данных, нажмите на кнопку ОК. У вас должно получиться как на рисунке 8.

     СуммЕсли рис8

    Рисунок 8. Результат вызова функции СУММЕСЛИ

    Далее с помощью маркера автозаполнения протяните формулу вниз до ячейки G9  включительно. У вас должно получиться как на рисунке 9.

     СуммЕсли рис9

    Рисунок 9. Графа Сумма окладов после копирования формулы с помощью маркера автозаполнеия

    Разбор проделанной работы:

    Теперь давайте попробуем разобраться с написанной функцией. Откройте окно с настройками функции первой ячейки - для этого встаньте на ячейку G3 и нажмите кнопку ƒx, как показано на рисунке 10:

     СуммЕсли рис10

    Рисунок 10. Открытие настроек написанной функции

    У вас должно открыться окно Аргументы функции как на рисунке 11:

     СуммЕсли рис11

     Рисунок 11. Аргументы функции СУММЕСЛИ

    Посмотрим внимательно на указанные аргументы функции.  Словами настройки функции можно было бы выразить так:

    Если значение ячейки F3 будет найдено среди ячеек от B3 до B45, то параллельные им ячейки из диапазона от D3 до D45 нужно суммировать.

    Значение ячейки F3 (отдел из списка в Таблице2)  - это критерий. Ячейки от B43 до В45 (весь список отделов в таблице 1) - это диапазон. Ячейки от D3 до D45 (весь список окладов в таблице 1) - это диапазон_суммирования.

    Обратите внимание, что ссылки в полях Диапазон и Диапазон_суммирования зафиксированы с помощью знаков $. Диапазоны обязательно нужно фиксировать, так как они не должны меняться при протягивании формулы вниз.  Диапазоны являются постоянными, потому что для каждого последующего отдела из таблицы 2 (Критерия) будет заново запущен поиск внутри Диапазона сверху вниз и заново будут суммироваться значения внутри Диапазона_суммирования.

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

     

    Функция ЕСЛИ  - пример решения задачи №2

    Скачайте файл Функция СУММЕСЛИ табель учета рабочего времени.xlsx по ссылке ниже.

    Откройте скачанный файл, лист Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем углу окна.

    Задача:

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

     СуммЕсли рис12

    Рисунок 12. Задача по подсчету количества часов

    Предварительный разбор:

    Функцию СУММЕСЛИ  будем вызывать дважды. Один раз для графы Кол-во часов в праздничные дни и один раз для графы Кол-во часов в будние дни.

    Для начала функцию нужно будет вызывать в ячейке J6 - это первая ячейка графы Кол-во часов в праздничные дни.  У нас есть критерий для опредления того, что день праздничный  - это буква П. Диапазоном, внутри которого функция будет искать критерий, будут ячейки от B3 до I3. Диапазоном суммирования будут ячейки параллельные диапазону  - ячейки от B6 до I6

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

    Аналогично нужно будет вызвать функцию для графы Кол-во часов в будние дни. Функцию нужно будет вызывать в ячейку K6. Параметры функции останутся такие же, кроме критерия - в качестве критерия будет пустая ячейка  - "". Далее также нужно будет протянуть функцию вниз для остальных строк.

    Вызов функции СУММЕСЛИ:

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

     У вас откроется окно Аргументы функции, в котором нужно заполнить поля следующими данными:

    1. В поле Диапазон указываем: $B$3:$I$3
    2. В поле Критерий указываем: "п"
    3. В поле Диапазон_суммирования указываем: B6:I6

     СуммЕсли рис13

    Рисунок 13. Заполненные аргументы функции СУММЕСЛИ

    После того, как закончите ввод данных, нажмите на кнопку ОК. У вас должно получиться как на рисунке 14.

     СуммЕсли рис14

    Рисунок 14. Результат вызова функции СУММЕСЛИ

    Далее с помощью маркера автозаполнения протяните формулу вниз до ячейки J15  включительно. У вас должно получиться как на рисунке 15.

     СуммЕсли рис15

    Рисунок 15. Графа Кол-во часов в праздничные дни

     Теперь осталось заполнить столбец Кол - во часов в будние дни. Для этого нужно встать на ячейку K6, перейти на вкладу Формулы, в разделе Математические выбрать СУММЕСЛИ (вы также можете вызвать функцию через мастер функций с помощью кнопки ƒx или написать ее вручную).

     У вас откроется окно Аргументы функции, в котором нужно заполнить поля следующими данными:

    1. В поле Диапазон указываем: $B$3:$I$3
    2. В поле Критерий указываем: ""
    3. В поле Диапазон_суммирования указываем: B6:I6

    Как видно, поля заполняются почти также, кроме критерия  -  тут нужно поставить обычные кавычки - "", потому что будние дни в диапазоне  - это пустые ячейки.

     СуммЕсли рис16

    Рисунок 16. Аругменты функции СУММЕСЛИ

    После заполнения полей, нажмите на кнопку ОК. У вас должно получиться как на рисунке 17.

     СуммЕсли рис17

     Рисунок 17. Результат вызова функции СУММЕСЛИ

    Далее протяните формулу вниз с помощью маркера автозаполнения, как показано на рисунке 18.

     СуммЕсли рис18

     Рисунок 18. Графа Кол-во часов в будние дни после заполнения

     Разбор проделанной работы:

    Откройте настройки функции в ячейке J6. Для этого встаньте на ячейку J6  и нажмите на кнопку нажмите кнопку ƒx, как показано на рисунке 19:

     СуммЕсли рис19

     Рисунок 19. Открытие настроек написанной функции 

     У вас должно открыться окно Аргументы функции как на рисунке 20:

      СуммЕсли рис20

    Рисунок 20. Аргументы функции СУММЕСЛИ

    В качестве диапазона указаны ячейки от B3 до I3 - это ячейки, по которым определяются праздничные или будние дни. В качестве критерия мы просто указали букву П в кавычках. Кавычки очень важны, так как буквы -  это строковый тип данных, а строковые типы данных в формулах/функциях берутся в кавычки. В качестве диапазона суммирования указаны ячейки параллельные ячейкам указанным в диапазоне - от B6 до I6.

    Таким образом, можно сказать, что если буква П будет найдена среди ячеек от B3 до I3, то параллельные ячейки в дипазоне от B6 до I6 должны будут суммироваться.

    Обратите внимание, что ссылки в диапазоне зафиксированы с помощью знаков $. Это сделано потому что диапазон при копировании формулы вниз с помощью маркера автозаполнения от фамилии к фамилии меняться не должен. Иначе бы при копировании, диапазон начал бы сползать вниз и мы бы получили некорректный результат. 

    Также обратите внимание, что диапазон суммирования не зафиксирован, ссылки не имеют знаков $. Диапазон суммирования  - это часы, которые отработал человек и при копировании формулы вниз, эти часы должны меняться от фамилии к фамилии.

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

    Для столбца Кол-во часов в будни дни все было сделано аналогично, как и для столбца Кол-во часов в праздничные дни. Разница только в том, что в качестве критерия стоит не буква П, а пустое значение в кавычках  - "". Так как будние дни никакими буквами не обозначаются.

    Будние дни можно было бы обозначать буквой Б - тогда в качестве критерия нужно было бы указать букву Б в кавычках - "Б".

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.