Как рассчитать зарплату в excel примеры с несколькими условиями
Ниже приведены варианты функции СУММЕСЛИ Excel. Описания, пошаговый алгоритм использования в зависимости от варианта функции, с примерами использования и иллюстрациями.
СУММЕСЛИ следует использовать в тех случаях, когда Вам необходимо суммировать значения в диапазоне, которые соответствует указанному Вами критерию.
Возможные варианты данной функции:
- сумма, если больше;
- сумма, если меньше;
- сумма, если равно;
- сумма, если не равно;
- сумма если больше или равно;
- сумма если меньше или равно.
Отдельная статья описывает функцию СУММЕСЛИ Excel. Точное совпадение. Частичное совпадение. С текстовыми критерий. Содержит описание, примеры и иллюстрации. Статья находиться по этой ссылке: Функция СУММЕСЛИ в MS Excel. Точное совпадение. Частичное совпадение. Инструкция и примеры.
Самый простой и понятный способ вызвать данную функцию, это нажать в правом верхнем углу, рядом с кнопкой ∑, стрелку вниз (Рис. 1).
Появиться список функций. Как правило, это наиболее часто используемые функции. В этом списке уже может быть нужная Вам функция СУММЕСЛИ. В нашем случае ее там нет. Тогда выбираем пункт Другие функции. Он находиться внизу списка (Рис. 2).
Появляется диалоговое окно – Вставка функции. Самое верхнее, это поле для поиска нужной функции. Вводим название функции, выбираем ее в результатах поиска и нажимаем ОК (Рис. 3).
В нем есть три ключевых поля для заполнения:
Что бы выбрать нужный диапазон нажимаете на стрелочку в конце строки диапазона и выбираете нужные Вам ячейки с помощью мыши. Можно сразу выбрать нужный диапазон, не нажимая на стрелочку, но сначала нужно поставив курсор на нужную строку в диалоговом окне. Можно прописать диапазон вручную. Результат от этого не зависит. После того как все готово, нажимает ОК (Рис. 5).
Далее рассмотрим варианты использования данной функции на конкретных примерах, что бы понять, как они работают.
Напоминаю, что перед тем, как вызывать функцию, нужно выбрать ячейку, в которой должно появиться искомое значение, результат применения функции.
Сумма, если больше, Сумма, если меньше. СУММЕСЛИ.
Знаки, которые используются: >, <.
У нас есть 25 строк с названием Пример и номер примера, в таблице. Напротив каждого примера стоит значение (Рис. 6).
Сумма, если больше.
Формула в ячейке, как пример, выглядит вот так:
Напротив каждого примера стоит число от 100 до 1300. Нам нужно просуммировать, только значения, которые больше числа 500.
Вызываем функцию, как описано выше и выбираем нужный нам диапазон в строке – Диапазон (Е3;Е27). Это столбец со значениями чисел от 100 до 1300.
Вводим критерий. В строке Критерий, пишем: >500, вручную. Можно скопировать значение критерия с самой таблицы и добавить в ручную только знак >. Кавычки появятся автоматически (Рис. 7).
Нажимаем ОК. Напротив ячейки с словом ИТОГО, появилась сумма чисел больше 500 (Рис. 8)
Сумма, если меньше.
Формула в ячейке, как пример, выглядит вот так:
Допустим, нам нужно найти сумму в диапазоне значений (E3:E27) меньше числа 500.
Алгоритм точно такой же. В критерии указывает знак меньше <, и необходимое значение. В нашем примере 500.
Сумма, если равно, Сумма, если не равно. СУММЕСЛИ.
Знаки, которые используются: =, <>.
Сумма, если равно.
Формулы в ячейках, как пример, выглядят вот так:
В столбце D, есть четыре ячейки с Пример 1 (выделенные красным шрифтом). Наша задача суммировать значения, которые стоят только напротив Пример 1. Вызываем функцию СУММЕСЛИ. В Диапазоне выбираем нужный диапазон (столбец D) (D3:D27).
В Критерий, вручную пишем Пример 1 или копируем с таблицы. По этому критерию будет осуществляться поиск нужных ячеек в диапазоне (D3:D27).
В Диапазон_суммирования выбираем диапазон ячеек с значениями (столбец Е), которые будут суммироваться (E3:E27). Суммироваться будут те ячейки, которые будут отвечать нашему критерию и находиться напротив ячеек с значением Пример 1. Нажимаем ОК (Рис. 9).
Так же можно использовать данный вариант функции, когда из списка значений (цифр), нужно суммировать только цифры равные определенному значению. Например, в диапазоне значений (E3:E27), нам нужно суммировать только значения равные числу 100 (выделенные красным шрифтом). В строке Критерий указываем необходимое значений, после знака =. В нашем случае это цифра 100. Диапазон_суммирования не заполняем. (Рис. 10).
Получаем искомую сумму, в нашем случае это число 400.
Сумма, если не равно.
Формулы в ячейках, как пример, выглядят вот так:
Рассмотрим на примере, когда нам нужно суммировать все значений, кроме тех, которые находятся напротив ячейки с Пример 1.
Выбираем в Диапазон ячейки, содержащие наш критерий (D3:D27) – Пример 1
В Критерий прописываем: <> Пример 1. Получаем сумму всех значений (диапазон E3:E27), кроме тех, которые стоят напротив Пример 1 (диапазон (D3:D27)).
В Диапазон_суммирования выбираем ячейки, значения которых нужно суммировать (E3:E27). Нажимаем ОК. Получаем нужную нам сумму. (Рис. 11).
Или нам нужно суммировать только значение не равные числу 100.
Тогда в строке Диапазон указываем нужный нам диапазон для суммирования (E3:E27).
В Критерий пишем <>100.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму всех ячеек, кроме тех, где значение равно числу 100. (Рис. 12).
Сумма если больше или равно, Сумма если меньше или равно. СУММЕСЛИ.
Знаки, которые используются: >=, <=.
Сумма если больше или равно.
Формула в ячейке, как пример, выглядит вот так:
Рассмотрим на примере. Нам нужно посчитать сумму значений в столбце (E3:E27). Но суммировать нужно только те числа, которые равны или больше значения числа 500. По стандартному алгоритму вызываем функцию СУММЕСЛИ.
В строке Диапазон выбираем диапазон ячеек, которые нужно просуммировать (E3:E27).
В Критерий пишем:>=500.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму значений равною числу 500, плюс все, что больше этой цифры в заданном диапазоне (E3:E27) (Рис. 13).
Сумма если меньше или равно
Формула в ячейке, как пример, выглядит вот так:
Алгоритм точно такой же, как и в предыдущем примере. Только вместо знак больше, используем знак меньше и равно <=. Получаем сумму всех чисел равную или меньше числу 500, которые находятся в заданном диапазоне (E3:E27).
Обращаю Ваше внимание, что выше приведенные примеры, являются вариантами использования данной функции. Способ использования данной функции должен определяться задачей, которую необходимо решить.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;"> 5")
Это видео — часть учебного курса Сложение чисел в Excel.
При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; "Иван"; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны "Иван".
Если необходимо выполнить суммирование ячеек в соответствии с несколькими условиями, используйте функцию СУММЕСЛИМН.
Синтаксис
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Аргументы функции СУММЕСЛИ описаны ниже.
Диапазон — обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).
Условие .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Можно включит под вопросицу (?), которая соответствует любому одиночному символу, звездочка (*) — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак "тильда" (
Например, условия могут быть выражены как 32, ">32", B5, "3?", "яблок*", "*
Важно: Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки ("). Если условием является число, использовать кавычки не требуется.
Диапазон_суммирования .Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).
Sum_range должны быть того же размера и фигуры, что идиапазон . В этом случае производительность может понести убытки, и формула суммирует диапазон ячеек, который начинается с первой ячейки в sum_range но имеет те же измерения, что и диапазон. Например:
Функция ЕСЛИ
Написать формулу в Excel можно двумя способами — вручную, введя данные в строку функции или прямо в ячейку, и обратившись к меню. Попробуем разобраться, как пользоваться функцией ЕСЛИ на примере. Алгоритм действий довольно простой — указывается определенное условие и варианты, что следует делать в случае истины или лжи (то есть условие либо выполняется, либо нет):
- Открываем Эксель, найдя программу в меню «Пуск» или на рабочем столе.
- Вводим данные, с которыми предстоит работать. Допустим, есть информация о сотрудниках компании — ее и будем использовать в качестве материала.
- Добавляем столбец «Премия» — в него выводим результаты функции ЕСЛИ. Курсор ставим в ячейку G4.
- Кликаем по значку функции, расположенному слева от строки ввода оператора, которая находится над рабочим полем. Также формулу в Эксель можно вставить, обратившись к пункту меню «Формулы» и выбрав там «Логические».
- В «Категории» находим «Логические», а в появившемся списке — функцию ЕСЛИ. Кликаем по «Ок».
- Появится окно, где необходимо указать аргументы функции, то есть условия.
- Предположим, премия менеджеров составляет 30%. Заполняем аргументы, начиная с логического выражения — пишем там адрес ячейки и нужное значение. В нашем случае это выглядит следующим образом: Лог_выражение = D4=«менеджер». Затем указываем размер премии (30), если выражение истинно, и 0, если оно ложно. Нажимаем «Ок».
- В результате видим, что премия первого сотрудника составляет 0%, так как он не является менеджером. Условие выполнено!
- Таблица содержит множество строк, необходимо заполнить их все. Конечно, не требуется каждый раз вбивать функцию заново, ее надо просто скопировать. Сделать это очень просто — кликаем мышкой по ячейке G4 (там уже получен результат), подводим курсор к левому нижнему углу и смотрим, когда он превратится в плюс.
- Растягиваем формулу на остальные ячейки, держа левую кнопку мыши нажатой и опускаясь вниз.
Вот и все — менеджеры получают премию. Воспользовавшись формулой ЕСЛИ, можно быстро сделать выборку нужных сотрудников.
Важно: конечно, посмотрев на пример, кажется, что и вручную несложно совершить подобные действия, однако все зависит от объема информации — чем больше данных в таблице, тем сложнее их анализировать визуально. Риск упустить что-то слишком велик, а Excel не только сэкономит время, но и поможет избежать ошибок.
Функция ЕСЛИ с условием И
Часто одним условием дело не ограничивается — например, нужно начислить премию только менеджерам, которые работают в Южном филиале компании. Действуем следующим образом:
- Выделяем мышкой первую ячейку (G4) в столбце с премиями. Кликаем по значку Fx, находящемуся слева от строки ввода формул.
- Появится окно с уже заполненными аргументами функции.
- Изменяем логическое выражение, добавив туда еще одно условие и объединив их с помощью оператора И (условия берем в скобки). В нашем случае получится: Лог_выражение = И(D4=«менеджер»;E4=«Южный»). Нажимаем «Ок».
- Растягиваем формулу на все ячейки, выделив первую и потянув мышкой вниз при нажатой левой клавише.
Совет: если в таблице много строк, то становится неудобно постоянно перематывать вверх-вниз, чтобы посмотреть шапку. Выход есть — закрепить строку в Excel. Тогда названия столбцов будут всегда показаны на экране.
Функция ЕСЛИ с условием ИЛИ
В качестве примера рассмотрим, как начислить в Экселе премию в размере 40% всем сотрудникам, которые являются бухгалтерами или директорами. То есть произведем выборку по двум условиям:
- Кликаем мышью по первой ячейке в столбце премий (G4), затем нажимаем на иконку слева от строки ввода формул.
- Редактируем аргументы функции. Логическое выражение будет представлять собой: ИЛИ(D4=«бухгалтер»;D4=«директор»). В «Значение_если_истина» пишем 40, а в «Значение_если_ложь» — 0. Кликаем «Ок».
- Копируем формулу, растягивая ее на остальные ячейки. Смотрим результат — премия 40% начислена директору и двум бухгалтерам.
Функция СУММЕСЛИ
Данная формула в Экселе применяется, когда требуется суммировать значения в ячейках, попадающих под какое-либо заданное условие. Например, нужно выяснить суммарную заработную плату всех продавцов:
- Добавляем строку с общей зарплатой продавцов и кликаем по ячейке, куда будет выводится результат.
- Нажимаем на иконку Fx, которая находится слева от строки ввода функций. В открывшемся окне ищем нужную формулу через поиск — вводим в соответствующее окно «суммесли», выбираем оператор в списке, кликаем «Ок».
- Появится окно, где необходимо заполнить аргументы функции.
- Вводим аргументы — первое поле «Диапазон» определяет, какие ячейки нужно проверить. В данном случае — должности работников. Кликаем мышкой в поле «Диапазон» и указываем там D4:D18. Можно поступить еще проще — просто выделить нужные ячейки.
- В поле «Критерий» вводим «продавец». В «Диапазоне_суммирования» пишем ячейки с зарплатой сотрудников (вручную либо выделив их мышкой). Далее — «Ок».
- Смотрим на результат — общая заработная плата всех продавцов посчитана.
Совет: сделать диаграмму в Excel просто и быстро — нужно всего лишь найти соответствующую кнопку на вкладке «Вставка» в меню.
Функция СУММЕСЛИМН
Данный оператор в Excel предназначен для вычисления суммы с использованием нескольких условий. К примеру, нужно определить заработную плату менеджеров, работающих в Южном филиале:
- Добавляем строку с общей зарплатой менеджеров Южного филиала. Выделим ячейку, где будет показываться итог вычислений.
- Кликаем по значку функции. В появившемся окне в поле поиска вводим «суммеслимн», нажимаем «Найти» и выбираем нужный оператор из списка.
- В открывшемся окне необходимо заполнить аргументы функции. В «Диапазон_суммирования» указываем ячейки с заработной платой. «Диапазон_условия1» — ячейки с должностями сотрудников. «Условие1» = «менеджер», так как мы суммируем зарплату менеджеров. Теперь нужно учесть второе условие — взять менеджеров из Южного филиала. В «Диапазон_условия2» вводим ячейки с филиалами, «Условие2» = «Южный». Все аргументы определены, нажимаем «Ок».
- В результате будет рассчитана общая зарплата всех менеджеров, работающих в Южном филиале.
Функция СЧЁТЕСЛИ
Данный оператор используется в Экселе, когда нужно посчитать количество ячеек, попадающих под определенное условие. Предположим, требуется узнать, сколько продавцов работает в компании:
- Добавляем строку с количеством продавцов и выделяем ячейку, куда планируется вывести результат.
- Кликаем по кнопке «Вставить функцию», расположенной во вкладке «Формулы» в меню. В открывшемся окне в поле «Категория» выбираем «Полный алфавитный перечень». В списке формул находим по алфавиту СЧЁТЕСЛИ, нажимаем «Ок».
- Заполняем аргументы функции — в поле «Диапазон» указываем ячейки с должностями, в «Критерии» пишем «продавец». Далее — «Ок».
- В результате получаем количество продавцов, работающих в компании. Эксель просто считает ячейки, где написано «продавец».
Функция СЧЁТЕСЛИМН
Иногда возникают более сложные задачи — например, нужно определить, сколько продавцов работает в Северном филиале. Тогда следует воспользоваться формулой СЧЁТЕСЛИМН:
- Добавляем строку с количеством продавцов Северного филиала и выделяем ячейку с будущим результатом.
- Кликаем по кнопке «Вставить функцию» во вкладке «Формулы». Через алфавитный перечень находим нужную функцию и нажимаем «Ок».
- Вводим аргументы функции: «Диапазон_условия1» — это ячейки с должностями, «Условие1» = «продавец». В «Диапазон_условия2» пишем ячейки с филиалами, «Условие2» = «Северный».
- В итоге будет определено количество продавцов Северного филиала.
Подводим итоги
В Excel существует несколько функций категории ЕСЛИ — использовать их нетрудно, так как программа максимально подсказывает алгоритм действий. Формулы существенно облегчают вычисления, на которые без Экселя можно потратить уйму времени и сил. К тому же нивелируется риск ошибок и человеческий фактор. Если возникают сложности с применением операторов, то, скорее всего, дело в неправильном вводе аргументов функции — перепроверьте формулу. Чтобы уточнить синтаксис, лучше воспользоваться справкой, которая в Экселе есть по каждой функции.
Читайте также: