Примеры нескольких критерий в формуле «если» в excel
Базу хоть какого логического выражения составляют две величины: правда (TRUE) и ересь (FALSE). При помощи логических выражений строятся бессчетные деревья решений. Самое обычное – вопросец с ответом «ДА» либо «НЕТ». В случае «ДА» производится одно действие, в случае «НЕТ» – другое. Для реализации критерий существует программный оператор IF (ЕСЛИ).
В программке EXCEL есть функция IF, которая дозволяет создать самые неописуемые логические конструкции, вкладывая операторы IF друг в друга. В старенькых версиях программки вложенность была ограничена 7 уровнями, ЕСЛИ в EXCEL 2010 не имеет ограничений, а в версии 2016 возникла новенькая функция ЕСЛИМН, учитывающая множественность вложений.
Познакомимся с многозначной функцией IF.
Функция IF
ЕСЛИ – обычная функция программки EXCEL, которая делает проверку определенного условия. Условие представляет собой вопросец, имеющий два ответа: правда и ересь.
Условие записывается в виде логического выражения, состоящего из правой и левой части, связанного логическими операторами сопоставления: больше (>), меньше (<), равно (=), неравно (<>), также >= и <=. Итог сопоставления указывает, производится либо нет это условие. В случае выполнения условия отрабатывает метод ИСТИНА, в неприятном случае – ЛОЖЬ.
Синтаксис функции смотрится последующим образом:
ЕСЛИ (логическое_выражение; значение_если_истина; значение_если_ложь).
Функции ЕСЛИ в EXCEL, примеры
Обычным примером реализации функции IF может служить необходимость присвоения высококачественного признака ряду значений. К примеру, нужно выделить в списочном кадровом составе компании всех работников молодее 35 лет. Для этого довольно в новейший столбец напротив каждой фамилии подставить формулу вида: =ЕСЛИ(ячейка_с_возрастом<35; 1;0). Таковым образом, всем юным людям будет присвоен высококачественный признак =1, который даст возможность работать лишь с подходящей частью перечня.
Увлекателен вариант работы с формулой IF, когда употребляется не цифровые данные, а текст.
Текст в формуле необходимо выделять кавычками для корректной отработки функции.
Всем инженерам 2 группы премия составляет 30%, остальным работникам – 50%. Для реализации этого условия вставляем формулу: =ЕСЛИ(ячейка_профессия=“инженер 2 группы”; “премия 30%”; “премия 50%”). Все инженеры 2 группы промаркируются данными по премии в 30%, остальным работникам будет введено значение «премия 50%».
Когда условия наиболее сложные, то употребляют композиции вложенных функций.
Применение вложенных IF
На предприятии при ведении табеля учёта рабочего времени возникает необходимость поставить месячную норму времени для расчёта заработной платы. У различных профессионалов эта величина различается. При помощи функции IF можно просто решить эту задачку.
Пусть у дневного персонала месячная норма равна 162 часа, у сменного при 8-часовом рабочем деньке она составит 148 часов, а при рабочем деньке 7,2 часа норма будет равна 136 часов.
Тогда необходимо проверить дневную норму работника (8 либо 7,2 часа) и категорию работающего (смена либо денек). Воплотить такую проверку можно различными методами.
- Вариант 1
Когда в качестве первой проверки берём дневную норму и вторую проверку делаем по правде, то формула будет последующая:
- Вариант 2
Первую проверку оставляем прежней, вторую проверяем по ереси, то выражение будет смотреться так:
- Вариант 3
Когда первую проверку делаем по группы, то имеем: =ЕСЛИ(ячейка_категория=“смена”;ЕСЛИ(ячейка_дневнаянорма =8;148;136);162)
Итог во всех вариантах будет однообразный.
Допустим, что есть ещё смена по 12 часов с нормой в 144 часа. Тогда для реализации такового метода будет нужно ещё одно вложение функции IF. Это уже 3-ий уровень.
Последующее выражение даёт решение и в этом случае:
=ЕСЛИ(ячейка_категория=“смена”;ЕСЛИ(ячейка_дневнаянорма =8;148;ЕСЛИ(ячейка_дневнаянорма=12;144; 136));162)
Освоив принцип проверок, можно создать сложные древовидные структуры. Хотя на практике наиболее 4 уровней вложенности фактически не применяется, потому что наиболее сложные композиции можно воплотить проще через остальные функции программки, работающие с выбором данных из массивов по аспектам.
Одновременное применение 2-ух критерий
Многоликость функции IF выражается в том, что её можно использовать сразу с иными функциями. В EXCEL есть ряд нужных интегрированных конструкций, использующих способ IF. Это такие формулы, как СУММЕСЛИ, СЧЕТЕСЛИ, СРЗНАЧЕСЛИ. С помощью их просто делать расчёты по определенному условию. Да и саму систему IF можно применить сразу с иными функциями. Разглядим её одновременное внедрение с оператором И и ИЛИ.
Функция IF с формулой И
Если требуется чтоб было выполнено сразу несколько критерий, то поможет система И, имеющая синтаксис:
Она инспектирует истинность каждой из логических структур и возвращает ИСТИНУ лишь при истинности всех проверяемых выражений.
Где это можно применять? К примеру, нужно выделить из общего перечня работников лишь слесарей 6 разряда, имеющих стаж работы больше 10 лет, то поможет система вида:
Эта формула выделит в общем перечне подходящую категорию работников по установленному высококачественному признаку =1.
Функция IF с формулой ИЛИ
Вариант использования функции ИЛИ с IF быть может последующий. Управление компании в течение года поквартально поощряло работников. Нужно избрать всех, кто получал поквартальные премии больше 5000 р.
Система ИЛИ имеет синтаксис аналогичный формуле И. Но ИСТИНА ворачивается при условии истинности хотя бы 1-го логического выражения.
В нашем примере нужна формула:
=ЕСЛИ(ИЛИ(ячейка_премия1квартал>=5000; ячейка_премия2квартал>=5000; ячейка_премия3квартал>=5000; ячейка_премия4квартал>=5000);1;0)
Следующая фильтрация данных по признаку 1 даст создать подходящий перечень трудящихся.
Таковым образом, явна полезность функции IF. В новейших версиях программки возникают усовершенствованные формулы, объединяющие более нужные старенькые приёмы и деяния, что даёт неограниченные способности для исследования и внедрения на практике программки эксель.
Функция Эксель ЕСЛИ: обучаемся ставить условия
Здрасти, друзья. Нередко нужно вывести значение в зависимости от содержания иной ячейки. Когда употребляется формула ЕСЛИ, Эксель может гибко решать подобные задачки, в чем вы на данный момент удостоверьтесь.
Предназначение и синтаксис функции
Вы нередко спрашиваете меня, как смотрится формула Эксель «Если то», сейчас отвечаю на ваши вопросцы. Деяния с условием производятся функцией ЕСЛИ:
=ЕСЛИ(условие ; значение если правда ; значение если ересь)
Разбираемся с аргументами:
- Условие – логическое выражение, которое будет инспектировать функция. Тут можно применять операторы:
- = — равно
- <> — не равно
- < — меньше
- > — больше
- <= — меньше либо равно
- >= — больше либо равно
Не считая того, сможете использовать функции И() и ИЛИ().
- Значение если правда – значение, которое возвратит формула, если выполнится условие
- Значение если ересь – значение, которое возвратит формула, если условие не выполнится
К примеру, формула =ЕСЛИ(A1=100;«Готово»;«В процессе») выведет в ячейку слово «Готово», если в А1 содержится число 100. Либо выведет «В процессе», когда в А1 не 100.
Функция И() нужна для проверки 2-ух критерий сразу, перечисленных через точку с запятой. К примеру, формула =И(A1>10;B1<30) возвратит значение ИСТИНА лишь тогда, когда выполнятся оба условия: значение в ячейке А1 будет больше 10, а в ячейке B1 – меньше 30.
Функция ИЛИ() возвратит правду, когда выполнится хотя бы одно из критерий. Другими словами, формула =ИЛИ(A1>10;B1<30) даст правду, когда выполнится либо 1-ое условие, либо 2-ое, либо оба.Сейчас практикуемся. На базе одной таблицы разглядим несколько примеров. Пусть у нас есть список студентов и их оценки за экзамен.
Функция ЕСЛИ с одним условием
Это самый обычной пример. Задачка стоит: тем студентам, у каких оценка выше сорока баллов – поставить отметку «Зачет», остальным – не ставить никаких отметок. Напишем формулу: =ЕСЛИ(B3>40;»Зачёт»;»») . Скопируем её во все строчки таблицы:
Функция ЕСЛИ с несколькими условиями
Расширяем задачку. Некие студенты получили «автомат» по итогам семестра. Сейчас зачет необходимо поставить к тому же тем студентам, у каких в поле «Автомат» стоит плюс. Воспользуемся описанной чуть повыше функцией ИЛИ: =ЕСЛИ(ИЛИ(B3>40;C3=»+»);»Зачёт»;»») . Итог такой:
ЕСЛИ значение принадлежит спектру
Сейчас таковой пример. Нужна формула в Эксель, если значение лежит в спектре, то выслать студента на пересдачу. Пусть это будет 20-40 баллов. Пользуемся уже знакомой функцией И(), чтоб задать несколько критерий.
Тут я применил условное форматирование, чтоб выделить отобранные строки цветом.
Функция ЕСЛИ со вложенным условием
Крайний пример. Необходимо студентам проставить или «Зачет», когда они получили наиболее 40 баллов, или «Отчисление», когда заработано наименее 20 баллов. Мы уже писали функцию, где проставляли «Зачет». Сейчас ее модифицируем.
Другими словами, когда студент не получил зачет, необходимо опять проверить, если его балл наименее 20 – отчислить. Как следует, в аргумент «Значение если ересь» запишем еще одну, вложенную функцию ЕСЛИ. Вот так: =ЕСЛИ(ИЛИ(B3>40;C3=»+»);»Зачёт»;ЕСЛИ(B3<20;»Отчисление»;»»)) .
Мы разглядели довольно примеров, чтоб вы соображали, как работает функция, сейчас применяйте ее на собственных проектах.
Так же, будут весьма полезны статьи о логических функциях в Экселе и проверках на тип содержимого.
А если что-то не получится – задавайте вопросцы в комментах. До встречи!
Поиск и подстановка по нескольким условиям
Если вы продвинутый юзер Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР либо VLOOKUP (если еще нет, то поначалу почитайте эту статью, чтоб им стать). Для тех, кто осознает, рекламировать ее не надо 🙂 — без нее не обходится ни один непростой расчет в Excel. Есть, но, одна неувязка: эта функция умеет находить данные лишь по совпадению 1-го параметра. А если у нас их несколько?
Представим, что у нас есть база данных по ценам продуктов за различные месяцы:
Необходимо отыскать и вынуть стоимость данного продукта (Нектарин) в определенном месяце (Январь), т.е. получить на выходе152, но автоматом, т.е. с помощью формулы. ВПР в чистом виде здесь не поможет, но есть несколько остальных методов решить эту задачку.
Метод 1. Доп столбец с ключом поиска
Это самый тривиальный и обычной (хотя и не самый удачный) метод. Так как штатная функция ВПР (VLOOKUP) умеет находить лишь по одному столбцу, а не по нескольким, то нам необходимо из нескольких создать один!
Добавим рядом с нашей таблицей очередной столбец, где склеим заглавие продукта и месяц в единое целое с помощью оператора сцепки (&), чтоб получить неповторимый столбец-ключ для поиска:
Сейчас можно применять знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в сделанном главном столбце:
Плюсы : Обычный метод, знакомая функция, работает с хоть какими данными.
Минусы : Нужно созодать доп столбец и позже, может быть, к тому же прятать его от юзера. При изменении числа строк в таблице — допротягивать формулу сцепки на новейшие строчки (хотя это можно упростить применением умной таблицы).
Метод 2. Функция СУММЕСЛИМН
Если необходимо отыскать конкретно число (в нашем случае стоимость как раз число), то заместо ВПР можно применять функцию СУММЕСЛИМН (SUMIFS) , показавшуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем перечне нет циклических продуктов снутри 1-го месяца, то она просто выведет значение цены для данного продукта и месяца:
Плюсы : Не нужен доп столбец, решение просто масштабируется на большее количество критерий (до 127), стремительно считает.
Минусы : Работает лишь с числовыми данными на выходе, не применима для поиска текста, не работает в старенькых версиях Excel (2003 и ранее).
Метод 3. Формула массива
О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве наиболее сильной кандидатуры ВПР я уже тщательно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:
- Выделите пустую зеленоватую ячейку, где должен быть итог.
- Введите в строке формул в нее последующую формулу:
Как это по сути работает:
Функция ИНДЕКС выдает из спектра цен C2:C161 содержимое N-ой ячейки по порядку. При всем этом порядковый номер подходящей ячейки нам находит функция ПОИСКПОЗ. Она отыскивает связку наименования продукта и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из 2-ух столбцов спектра A2:A161&B2:B161 и выдает порядковый номер ячейки, где отыскала четкое совпадение. На самом деле, это 1-ый метод, но главный столбец создается виртуально прямо снутри формулы, а не в ячейках листа.
Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.
Минусы : Осязаемо тормозит на огромных таблицах (как и все формулы массива, вообщем), в особенности если указывать спектры «с припасом» либо сходу целые столбцы (т.е. заместо A2:A161 вводить A:A и т.д.) Почти всем непривычны формулы массива в принципе (тогда для вас сюда).
