Функция если в excel. примеры (с несколькими условиями)

Функция ЕСЛИ

Написать формулу в Excel можно двумя способами — вручную, введя данные в строку функции или прямо в ячейку, и обратившись к меню. Попробуем разобраться, как пользоваться функцией ЕСЛИ на примере. Алгоритм действий довольно простой — указывается определенное условие и варианты, что следует делать в случае истины или лжи (то есть условие либо выполняется, либо нет):

Открываем Эксель, найдя программу в меню «Пуск» или на рабочем столе.

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

Добавляем столбец «Премия» — в него выводим результаты функции ЕСЛИ. Курсор ставим в ячейку G4.

Кликаем по значку функции, расположенному слева от строки ввода оператора, которая находится над рабочим полем. Также формулу в Эксель можно вставить, обратившись к пункту меню «Формулы» и выбрав там «Логические».

В «Категории» находим «Логические», а в появившемся списке — функцию ЕСЛИ. Кликаем по «Ок».

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

Предположим, премия менеджеров составляет 30%. Заполняем аргументы, начиная с логического выражения — пишем там адрес ячейки и нужное значение. В нашем случае это выглядит следующим образом: Лог_выражение = D4=«менеджер». Затем указываем размер премии (30), если выражение истинно, и 0, если оно ложно. Нажимаем «Ок».

В результате видим, что премия первого сотрудника составляет 0%, так как он не является менеджером. Условие выполнено!

Таблица содержит множество строк, необходимо заполнить их все. Конечно, не требуется каждый раз вбивать функцию заново, ее надо просто скопировать. Сделать это очень просто — кликаем мышкой по ячейке G4 (там уже получен результат), подводим курсор к левому нижнему углу и смотрим, когда он превратится в плюс.

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

Вот и все — менеджеры получают премию. Воспользовавшись формулой ЕСЛИ, можно быстро сделать выборку нужных сотрудников.

Важно: конечно, посмотрев на пример, кажется, что и вручную несложно совершить подобные действия, однако все зависит от объема информации — чем больше данных в таблице, тем сложнее их анализировать визуально. Риск упустить что-то слишком велик, а Excel не только сэкономит время, но и поможет избежать ошибок

Функция ЕСЛИ с условием И

Часто одним условием дело не ограничивается — например, нужно начислить премию только менеджерам, которые работают в Южном филиале компании. Действуем следующим образом:

Выделяем мышкой первую ячейку (G4) в столбце с премиями. Кликаем по значку Fx, находящемуся слева от строки ввода формул.

Появится окно с уже заполненными аргументами функции.

Изменяем логическое выражение, добавив туда еще одно условие и объединив их с помощью оператора И (условия берем в скобки). В нашем случае получится: Лог_выражение = И(D4=«менеджер»;E4=«Южный»). Нажимаем «Ок».

Растягиваем формулу на все ячейки, выделив первую и потянув мышкой вниз при нажатой левой клавише.

Функции программы Microsoft Excel: оператор «ЕСЛИ»

​ это, в противном​ только женщинам, которые​

Общее определение и задачи

​ кнопку мышки, и​Среди многих функций, с​: Спасибо, очень Вам​Казанский​ с другом увязывать.​Код200?’200px’:”+(this.scrollHeight+5)+’px’);”>=ВЫБОР(B5-11;МИН(C5;C4);МИН(C5;C3);МИН(C5;C2))​Вопрос, какой результат​ сопоставить цены на​ цифровой тип данных,​ словом яблоки. Или,​ одного условия)​ который означает, что​Совет:​

​Она по-прежнему точна и​ сработать в 75 %​ случае сделать что-то​ являются основным персоналом,​ проводим курсором до​

Пример использования

​ которыми работает программа​ благодарен .​: Может так?​Fireman92​

​ArkaIIIa​ должен быть при​ товар в разные​ а текстовый. Поэтому​ если в качестве​Функция СУММЕСЛИМН (суммирует​ мы хотим найти​ Чтобы сложные формулы было​ будет правильно работать,​ случаев, но вернуть​ еще)​ а мужчины, и​ самого низа таблицы.​ Microsoft Excel, следует​Valinur​А помогите мне​: вот ваш список​:​ пустой ячейке?​ привозы, сравнить балансы​ мы прописали в​ критерия задать =”яблоки*”,​ значения с учетом​ близкое совпадение. Иначе​

​ проще читать, вы​ но вы потратите​ непредвиденные результаты в​Поэтому у функции ЕСЛИ​ представительницы женского пола,​

​Таким образом, мы получили​ выделить функцию «ЕСЛИ».​: Здравствуйте!​ тоже, пожалуйста! Как​Pelena​Pelena​Libershaft​ (бухгалтерские отчеты) за​ формуле В2= «зач.».​ то это позволит​ нескольких условий)​

​ говоря, будут сопоставляться​ можете вставить разрывы​ много времени, чтобы​ остальных 25 %. К​

Пример функции с несколькими условиями

​ возможны два результата.​ числящиеся вспомогательным персоналом,​ таблицу со столбцом,​ Это один из​Подскажите, пожалуйста, как​ создать условие типа​:​Большое спасибо! Вариант​: Помогите, пожалуйста, разобраться,​ несколько месяцев, успеваемость​ В кавычки берем,​ выбрать все значения​Функция И​

​ точные значения в​ строк в строке​ написать ее, а​ сожалению, шансов отыскать​ Первый результат возвращается​ не получают ничего.​ заполненным при помощи​ тех операторов, к​ выполнить двойное условие​Z2AL2>AM2 (TRUEFALSE) то​200?’200px’:”+(this.scrollHeight+5)+’px’);”>=ЕСЛИ(C7​ с ЕСЛИ прост​ как написать функцию​ учеников (студентов) разных​ чтобы программа правильно​ начинающиеся со слова​Функция ИЛИ​ таблице подстановки, а​

​ формул. Просто нажмите​ потом протестировать. Еще​ эти 25 % немного.​ в случае, если​ Таким образом, чтобы​ функции «ЕСЛИ».​ которым пользователи прибегают​ на суммирование.​ есть три колонки​Код200?’200px’:”+(this.scrollHeight+5)+’px’);”>=ЕСЛИ(C7​ и логичен, а​ =Если с двойным​ классов, в разные​ распознала текст.​ яблоки (яблоки свежие;​Функция ВПР​ также все значения,​ клавиши ALT+ВВОД перед​ одна очевидная проблема​Работа с множественными операторами​ сравнение истинно, второй —​

​ значение в ячейках​В функцию «ЕСЛИ» можно​ чаще всего, при​У меня есть​

​ чисел, правдой будет​ArkaIIIa​ вот ВЫБОР пойду​

Пример с выполнением двух условий одновременно

​ условием (​ четверти и т.д.​​ яблоки).​Общие сведения о​ попадающие между ними.​

​ текстом, который хотите​ состоит в том,​ ЕСЛИ может оказаться​ если сравнение ложно.​ колонки «Премия к​ также вводить несколько​ выполнении задач в​ большая таблица,которая будет​ тогда, когда число​:​ осмысливать)​По заданию из ВЛОЖЕНИЯ​Чтобы сравнить 2 таблицы​Часто на практике одного​Разбор подобных критериев​ формулах в Exce​ В этом случае​ перенести на другую​ что вам придется​ чрезвычайно трудоемкой, особенно​Операторы ЕСЛИ чрезвычайно надежны​ 8 марта» было​ условий. В этом​ приложении. Давайте разберемся,​ расти дальше и​ из столбца Z2​Fireman92, Pelena​

​ArkaIIIa​),​ в Excel, можно​ условия для логической​

Пример использования оператора «ИЛИ»

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

​ 1000, нужно соблюдение​ случае, применяется вложение​ что представляет собой​ в которой 4​ будет больше или​Большое вам спасибо!​:​не используя​ воспользоваться оператором СЧЕТЕСЛИ.​ функции мало. Когда​ в статье Сложение​ появления неработающих формул​ сортировать по возрастанию,​Перед вами пример сценария​ и эквивалентные буквенные​ к ним через​ частью многих моделей​ двух условий: пол​ одного оператора «ЕСЛИ»​ функция «ЕСЛИ», и​

​ поля :​

​ меньше обоих двух​Fireman92​Pelena​при этом AND,​ Рассмотрим порядок применения​ нужно учесть несколько​ чисел с единственным​Поиск ошибок в​ от меньшего к​ для расчета комиссионных​ оценки. Каковы шансы,​ какое-то время и​

​ электронных таблиц. Но​

lumpics.ru>

Дополнительные примеры

Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода.

=ЕСЛИ(C9>15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0)))))

Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д…

Хотя она выглядит примерно так же, как в примере с более ранними оценками, эта формула является отличным примером того, насколько сложно использовать крупные выписки ЕСЛИ. Что делать, если ваша организация решила добавить новые уровни компенсаций и, возможно, даже изменить существующие значения в рублях или процентах? У вас будет много работы на руках!

Совет: Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку.

Перед вами пример сценария для расчета комиссионных с неправильной логикой:

Видите, что не так? Сравните порядок сравнения доходов с предыдущим примером. Как это будет происходить? Правильно, она будет снизу вверх (от 5 000 до 15 000 рублей), а не наоборот

Но почему это так важно? Это очень важно, так как формула не может пройти первую оценку для любого значения стоимостью более 5 000 рублей. Предположим, что вы получили доход в размере 12 500 долларов США— если вы получили 10 %, так как она больше 5 000 рублей, и она остановится на этом

Это может быть чрезвычайно проблемным, так как во многих ситуациях такие типы ошибок остаются незамеченными до тех пор, пока они не оказывают отрицательного влияния. Так что же можно сделать, зная о том, что при сложных вложенных заявлениях ЕСЛИ существуют серьезные недостатки? В большинстве случаев вместо создания сложной формулы с помощью функции ЕСЛИ можно использовать функцию ВЛОП. С помощью ВLOOKUPсначала нужно создать таблицу для справки:

=ВПР(C2;C5:D17;2;ИСТИНА)

В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.

=ВПР(B9;B2:C6;2;ИСТИНА)

Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.

Примечание: В обеих функциях ВПР в конце формулы используется аргумент ИСТИНА, который означает, что мы хотим найти близкое совпадение. Иначе говоря, будут сопоставляться точные значения в таблице подстановки, а также все значения, попадающие между ними. В этом случае таблицы подстановки нужно сортировать по возрастанию, от меньшего к большему.

В этой области в этой области вложена более подробная информация,но это намного проще, чем 12-уровневая сложная вложенная если-выписка! Есть и другие, менее очевидные, преимущества:

• Таблицы ссылок функции ВПР открыты и их легко увидеть.

• Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся.

• Если вы не хотите, чтобы люди видели вашу таблицу ссылок или вмешивались в нее, просто поместите ее на другой лист.

Базовый ЕСЛИ

Прежде чем говорить о вложенном ЕСЛИ, давайте быстро рассмотрим базовую структуру:

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

Обратите внимание на квадратные скобки … это означает, что аргументы необязательны. Однако вы должны указать либо значение ИСТИНА, либо значение ЛОЖЬ

Чтобы проиллюстрировать это, мы используем ЕСЛИ, чтобы проверить результаты и вернуть «Зачтено» для баллов не менее 65:

Базовая функция ЕСЛИ — вернуть «Зачтено» для баллов не менее 65

Ячейка D4 в примере содержит эту формулу:

Что можно прочитать так: если количество баллов в ячейке C4 составляет не менее 65, вернуть «Зачтено».

Однако обратите внимание, что если оценка меньше 65, ЕСЛИ возвращает ЛОЖЬ, так как мы не указали «значение_если_ложь». Чтобы отобразить «Не зачтено» для непроходных оценок, мы можем добавить «Не зачтено» в качестве ложного аргумента следующим образом:. Базовая функция ЕСЛИ — с добавленным значением_если_ложь

Базовая функция ЕСЛИ — с добавленным значением_если_ложь

Логическая функция И

Ну а что делать, если нам нужно еще сильнее сегментировать данные. Например, дополним предыдущую задачу еще одним условием — для значений более 100 выводить слово «много», а менее 100 — «мало». При этом предыдущие условия сохраняются.

Итак, если значение больше 10, но меньше 100, то мы должны вывести слово «мало», а ведь значение должно сразу удовлетворят двум условиям, поэтому воспользуемся еще одной функцией И совместно с функцией ЕСЛИ.

Создаем формулу с функцией ЕСЛИ, но перед тем, как задать условие вводим функцию И.

У этой функции в качестве аргументов выступают условия, которые перечисляются через точку с запятой. У нас два условия и мы их указываем — значение должно быть и больше 10, и меньше 100.

Не забываем, что у функции И есть свои скобки и нужно внимательно следить за синтаксисом, чтобы не возникло ошибки.

Если условие выполняется, то указываем «мало» (ссылка на ячейку G4), а если не выполняется, то нам нужно проверить значение еще на ряд условий. То есть вложенная функция ЕСЛИ будет выполняться в том случае, если значение либо больше 100, либо меньше 10, поэтому нам нужно ввести дополнительную проверку на любое из последующих условий.

Например, воспользуемся еще одной функцией И и проверим, что значение меньше 10, но больше 0. В этом случае мы должны вывести фразу «пополнить запас» (G3). Но вот если значение не удовлетворяет и этому условию, то оно либо больше 100, либо равно нулю. Поэтому последняя функция ЕСЛИ окончательно решит задачу — если значение равно 0, то вводим «нет в наличии» (G2) иначе мы однозначно понимаем, что значение больше 100 и выводим слово «много» (G5).

Функция ЕСЛИ() в MS EXCEL

​ считать истинной только​Таким образом, мы получили​ как с ней​ отклонением.​ Более корректный пример​ из способов. Но​​ могут проверить до​​ а текстовый. Поэтому​Дополнительные сведения об использовании​ противном случае —​

​ было понять, скопируйте​6​

Синтаксис функции

​значение_если_ложь: значение, возвращаемое,​

​ условия результатов данных​​ условий. Ниже приведены​Для функции ВПР() необходимо​ Microsoft Excel. Она​​ выполнение двух или​​ таблицу со столбцом,​ работать.​​Например:​​ прилагаю. Спасибо!​ с именем проще.​ 30 условий.​ мы прописали в​ этих функций см.​ «Неверно» (ОК).​

​ его на пустой​7​ если условие ложно.​​ в двух ячейках​​ иллюстрирующие примеры.​ создать в диапазоне​​ позволяет отобразить результаты​​ нескольких условий одновременно.​ заполненным при помощи​Скачать последнюю версию​​- если отклонение​​_Boroda_​Jul​​Пример использования оператора И:​​ формуле В2= «зач.».​

Вложенные ЕСЛИ

​ в разделах Функция​=ЕСЛИ(A215; «ОК»; «Неверно»)​​ лист.​​8​Дополнительные сведения о создании​ (не в одной).​Пусть данные находятся в​A3:B6​​ соответствующие определенным условиям.​Например, в нашем случае,​

​ функции «ЕСЛИ».​ Excel​ больше или равно​

​: Тогда можно так:​: Всем привет! Необходимо​

​Пример использования функции ИЛИ:​

​ В кавычки берем,​ ЕСЛИ, Функция И​​Если значение в ячейке​​Копирование примера​

​9​ формул см. в​У нас такая​​ диапазоне​​таблицу значений:​ Ничего особенно сложного​ премия к 8​В функцию «ЕСЛИ» можно​

​«ЕСЛИ» является стандартной функцией​

​ -50% и меньше,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС((B$4:C$7;B$10:D$18);ПОИСКПОЗ(G3;A$10:A$18;-1);ЕСЛИОШИБКА(ПОИСКПОЗ(F3;B$3:C$3;);ПОИСКПОЗ(F3;B$9:D$9;));1+ЕЧИСЛО(ПОИСКПОЗ(F3;B$9:D$9;)))​ прописать условия для​Пользователям часто приходится сравнить​ чтобы программа правильно​

Опущен третий аргумент

​ A2 не равно​Выделите пример, приведенный в​10​ разделе Создание и​ таблица с данными.​A6:A11​​Если требуется вывести разный​​ в овладении принципами​​ марта в размере​​ также вводить несколько​ программы Microsoft Excel.​ или равно +50%,​

Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

​Jul​ каждого параметра в​​ две таблицы в​​ распознала текст.​К началу страницы​​ 15, возвращается «ОК»,​​ этой статье.​​11​​ удаление формул.​

​Нам нужно в ячейке​​(см. файл примера)​​ текст в случае​ использования данной функцией​ 1000 рублей выдаётся​ условий. В этом​ В её задачи​ то это можно​: Вставила формулу в​

Связь функции ЕСЛИ() с другими функциями использующие условия

​ одной ячейке, файл​ Excel на совпадения.​​Логический оператор ЕСЛИ в​ в противном случае​Важно:​12​Создание условной формулы с​ Е27 написать формулу,​Подсчитаем сумму значений, которые​ наличия в ячейке​ нет.​

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

​Часто на практике одного​ Excel применяется для​​ — «Неверно» (Неверно).​​ Не выделяйте заголовки строк​

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

​Функция ЕСЛИ(), английский вариант​ а мужчины, и​ в другой. При​ случае, когда условие​ отклонение выше планового;​ рабочего файла, подскажите​ формула уже большая,​ товар в разные​​ функции мало. Когда​

Использование ЕСЛИ с функциями И, ИЛИ и НЕ

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)

Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ ( И), только одно условие должно иметь такое значение ( ИЛИ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.

Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.

ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; ; …), значение_если_истина; ))

ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; ; …), значение_если_истина; ))

ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; ))

Условие, которое нужно проверить.

Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

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

И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Примеры

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

Ниже приведены формулы с расшифровкой их логики.

=ЕСЛИ(И(A2>0;B2 0;B4 50);ИСТИНА;ЛОЖЬ)

Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.

Если значение A7 («синий») НЕ равно «красный», возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.

Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ

Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.

Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.

Ниже приведены формулы с расшифровкой их логики.

Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.

=ЕСЛИ(И(A3>B2;A3 B2;A4 B2);ИСТИНА;ЛОЖЬ)

Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.

Использование операторов И, ИЛИ и НЕ с условным форматированием

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

На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.

«Изменить правило» с параметром «Формула»» xmlns:AntiXSS=»urn:AntiXSSExtensions» />

Вот как будут выглядеть формулы для примеров с датами:

Если A2 больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий.

=И(A3>B2;A3 B2;A4 B2)

Если A5 НЕ больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ. Если изменить формулу на =НЕ(B2>A5), она вернет значение ИСТИНА, а ячейка будет отформатирована.

Примечание: Распространенная ошибка — ввод формулы в условное форматирование без знака «равно» (=). При этом вы увидите, что в диалоговом оке Условное форматирование в формулу будут добавлены знаки «равно» и кавычка: =»ИЛИ(A4>B2;A4

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Функция СУММЕСЛИМН

Данный оператор в Excel предназначен для вычисления суммы с использованием нескольких условий. К примеру, нужно определить заработную плату менеджеров, работающих в Южном филиале:

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

Кликаем по значку функции. В появившемся окне в поле поиска вводим «суммеслимн», нажимаем «Найти» и выбираем нужный оператор из списка.

В открывшемся окне необходимо заполнить аргументы функции. В «Диапазон_суммирования» указываем ячейки с заработной платой. «Диапазон_условия1» — ячейки с должностями сотрудников. «Условие1» = «менеджер», так как мы суммируем зарплату менеджеров. Теперь нужно учесть второе условие — взять менеджеров из Южного филиала. В «Диапазон_условия2» вводим ячейки с филиалами, «Условие2» = «Южный». Все аргументы определены, нажимаем «Ок».

В результате будет рассчитана общая зарплата всех менеджеров, работающих в Южном филиале.

Используйте ЕСЛИМН вместо вложенных ЕСЛИ

Если вы используете Excel 2016, у Office 365 есть новая функция, которую вы можете использовать вместо вложенных ЕСЛИ: функция ЕСЛИМН (IFS). Функция ЕСЛИМН (IFS) предоставляет специальную структуру для оценки нескольких условий без вложенности

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

Обратите внимание, в формуле всего одна пара скобок!

Что происходит, когда вы открываете электронную таблицу, которая использует функцию ЕСЛИМН (IFS) в более старой версии Excel? В Excel 2013 и 2010 (и я верю в Excel 2007, но не могу проверить) вы увидите «_xlfn» в ячейке. Ранее вычисленное значение все еще будет там, но если формула пересчитается, вы увидите ошибку #ИМЯ?.

Как Работает Оператор ЕСЛИ в Excel?

Для успешного использования оператора ЕСЛИ надо задать три аргумента:

1. Что Проверять — Собственно, что будет проверяться на условие ЕСЛИ? Мы можем, например, проверить, равняется ли значение в ячейке определенному числу, или содержит ли она определенный текст.
2. Что Показывать Если утверждение Истинно — Если условие которое мы проверяем выполняется то, что мы должны отобразить в ячейке?
3. Что Показывать Если утверждение Ложно — Если выражение не содержит, того чего мы ищем, что нужно отобразить в таком случае?

Оператор Excel ЕСЛИ начинается с =ЕСЛИ(. В официальной документации к Excel, показана структура оператора ЕСЛИ:

На мой взгляд на простом русском правило использования оператора ЕСЛИ можно записать так:

Достаточно просто? Если кажется, что пока плохо понятно, давайте перейдем к самому первому примеру, в котором показано, как использовать оператор ЕСЛИ, и посмотрим как он работает.

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

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

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

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

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

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

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные

Здесь только нужно обратить внимание на относительность ссылок. Диапазон с критериями и диапазон суммирования должны быть абсолютным ссылками, чтобы при протягивании формулы они не «поехали вниз», а сам критерий, во-первых нужно указать мышкой (а не прописать вручную), во-вторых, должен быть относительной ссылкой, так как каждая сумма имеет свой критерий суммирования

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

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.