Как в экселе работать с условиями

Как в экселе работать с условиями

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

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

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

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

* «Вложенность» означает объединение нескольких функций в одной формуле.

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

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

Имя аргумента

лог_выражение

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

значение_если_истина

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

значение_если_ложь

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

Примечания

Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?

Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного.

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

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

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

Примеры

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

Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.

Если тестовых баллов больше 79, учащийся получает оценку B.

Если тестовых баллов больше 69, учащийся получает оценку C.

Если тестовых баллов больше 59, учащийся получает оценку D.

В противном случае учащийся получает оценку F.

Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:

Она по-прежнему работает правильно и работает правильно, но на написание и проверку нужно много времени, чтобы убедиться, что она работает правильно. Еще одна наиболее взглялая проблема в том, что вам приходилось вручную вводить оценки и эквивалентные буквы оценок. Какова вероятность случайного опечатки? Теперь представьте, что вы пытаетесь сделать это 64 раза с более сложными условиями! Конечно, это возможно, но действительно ли вы хотите обучебиться с такого рода усилиями и возможными ошибками, которые будет трудно обнаружить?

Совет: Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). При редактировании Excel попытается помочь вам понять, что куда идет, окрашивая разными цветами части формулы. Например, во время редактирования показанной выше формулы при перемещении курсора за каждую закрывающую скобку «)» тем же цветом будет окрашиваться соответствующая открывающая скобка. Это особенно удобно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли в них парных скобок.

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

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

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

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

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

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

Видите, что не так? Сравните порядок сравнения доходов с предыдущим примером. Как это будет происходить? Правильно, она будет снизу вверх (от 5 000 до 15 000 рублей), а не наоборот. Но почему это так важно? Это очень важно, так как формула не может пройти первую оценку для любого значения стоимостью более 5 000 рублей. Предположим, что вы получили доход в размере 12 500 долларов США— если вы получили 10 %, так как она больше 5 000 рублей, и она остановится на этом. Это может быть чрезвычайно проблемным, так как во многих ситуациях такие типы ошибок остаются незамеченными до тех пор, пока они не оказывают отрицательного влияния. Так что же можно сделать, зная о том, что при сложных вложенных заявлениях ЕСЛИ существуют серьезные недостатки? В большинстве случаев вместо создания сложной формулы с помощью функции ЕСЛИ можно использовать функцию ВЛОП. С помощью ВLOOKUPсначала нужно создать таблицу для справки:

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

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

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

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

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

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

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

Вы знали?

Теперь есть функция УСЛОВИЯ, которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:

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

Функция ЕСЛИМН — просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках.

Примечание: Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, проверьте, установлена ли у вас последняя версия Office.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как Использовать Выражение ЕСЛИ в Excel.

Excel — один из самых мощных и простых в использовании инструментов для работы с данными. Электронные таблицы — это инструмент для организации, расчета, и просмотра ваших данных.

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

Представьте, что у нас есть таблица со списком оценок, по нашим школьным курсам. Я хочу напомнить себе, сдал я или провалил экзамен, отобразив это текстом «passing» (сдал) или «failing» (провалил). Я могу написать формулу с выражением ЕСЛИ, которая будет смотреть на окончательную оценку, и в зависимости от количества балов выдавать текст «passing» или «failing».

Это простой вариант функции ЕСЛИ которая оценивает количество балов в столбце B и напоминает мне, на основании набранных балов, сдал я или провалил курс.

Формула ЕСЛИ в Excel делает ваши электронные таблицы намного «умнее». Мы можем создать для ячейки разные варианты, которые будут отображаться в зависимости от условий. Формула ЕСЛИ позволяет нам добавить логические операции в нашу электронную таблицу. В этом уроке, я покажу вам, как ее использовать.

Как Использовать Оператор ЕСЛИ в Excel (ВидеоУрок)

В этом видеоуроке, я покажу вам как я использую оператор ЕСЛИ в Excel, что бы сделать электронную таблицу «умнее». Посмотрите это видео, что бы проследить за процессом создания условных выражений с ЕСЛИ, и затем посмотрите напечатанные ниже инструкции урока.

Прежде, чем мы приступим к нашему уроку, я рекомендую вам загрузить бесплатную книгу Excel с примерами, которую я создал специально для этого урока.

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

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

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

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

=ЕСЛИ(логическое выражение;[значение если истинна];[значение если ложь])

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

=ЕСЛИ(что нужно проверять; что показывать если правда; что показывать если ложь)

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

Создание Вашей Первой ЕСЛИ — функции

Для этого примера, используйте Лист с названием «Check for Blank», из Книги с Примерами .

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

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

Хотя я и так вижу, что некоторые ячейки пусты, давайте напишем ЕСЛИ формулу, что бы напечатать в стоблце D, кому мы должны напомнить принести еду. Моя формула ЕСЛИ должна проверить является ли ячейка в столбце С пустой, и напечатать напоминание, тому кто еще не подписался.

Вот, что я написал:

=ЕСЛИ(C2=»»;»напомнить им!»;»они уже оставили запись»)

Давайте разделим эту формулу на четыре части:

1. Каждая формула ЕСЛИ начинается с =ЕСЛИ(
2. Дальше, я собираюсь проверить, являются ли значения в столбце С пустыми, поэтому я написал С2 =»» , написанные рядом знаки кавычки, без текста между ними, позволяют проверить, является ли ячейка пустой. Точка с запятой в конце, позволяют нам перейти к следующей части формулы.
3. Следующая часть формулы — это то, что мы будем показывать каждый раз, когда выражение окажется истинным. Так как мы проверяем является ли ячейка пустой, мы собираемся вывести надпись»напомнить им», если она пустая.
4. Если С2 содержит какой-то текст, мы будет писать, что этот человек уже оставил запись.

И наконец, вот результаты, после того, как мы записали формулу и применили ее к другим ячейкам ниже:

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

Отлично! Обратите внимание, что рядом с пустой ячейкой в столбце C, Excel печатает, что мы должны напомнить этому человеку оставить запись. Теперь мы можем, используя колонку D, отобрать кого нужно, и отправить им напоминание по почте.

Это простой пример, того, как с помощью функции ЕСЛИ в Excel, мы можем добавить логики в наши электронные таблицы. Давайте рассмотрим другие примеры.

Проверка Значений

Для этой части урока используйте Лист Test Values из скачанного файла с примерами .

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

В первом примере, мы проверяли, содержит ли ячейка в столбце С элемент текста. Заметьте, что мы использовали знак равно, что бы проверить, является ли содержание ячейки пустым. Мы можем использовать любые математические операторы, чтобы проверить значение величин, такие как:

• = — чтобы проверить равенство содержимого ячейки определенной величине.
• <> — чтобы проверить, что содержимое ячейки неРавно определенной величине
• > — чтобы проверить, что содержимое ячейки больше величины в формуле
• < — чтобы проверить, что содержимое ячейки меньше величины в формуле
• >= — чтобы проверить, что содержимое ячейки больше или равно величине в формуле
• <= — чтобы проверить, что содержимое ячейки меньше или равно величине в формуле

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

В данных ниже, я выписал запас продукции на складе. Мне нужно написать формулу в столбце Е, что бы проверить и убедиться, что для каждого продукта, количество соответствует 10. Если это не так, то мы должны напечатать предупреждающее сообщение.

В столбце Е, я написал формулу:

=ЕСЛИ(D4<>10;»необходимо обратить внимание»;»»)

Давайте разберем ее по частям:

• Во-первых, я использую оператор <>, что бы проверить что значение в ячейке D4 не равно 10, что указывает на проблему.
• Далее, я добавил сообщение в кавычках «необходимо обратить внимание», которая будет появляться каждый раз, если в соседней ячейке значение не равно 10.
• И, наконец, после точки с запятой я добавил пару кавычек без содержимого между ними, поэтому если число равно 10, формула ничего не напечатает.

На скриншоте выше, вы можете увидеть, как я использовал формулу, что бы добавить предупреждающее сообщение для для складских позиций. Если значение в столбце D не равно 10, Excel напечатает сообщение «необходимо обратить внимание» в столбце Е.

Как Использовать ЕСЛИОШИБКА

Есть особый оператор для функции ЕСЛИ в Excel, что бы работать с ошибками в ваших формулах. Формула ЕСЛИОШИБКА, подобна оператору ЕСЛИ, но она позволяет отследить широкий набор всевозможных ошибок, и заменить их более полезными сообщениями.

Вот набор ошибок, которые может отследить функция ЕСЛИОШИБКА, согласно документации Microsoft:

• #N/A
• #VALUE!
• #REF!
• #DIV/0!
• #NUM!
• #NAME?
• #NULL!

Чтобы использовать ЕСЛИОШИБКА, поместите существующую формулу в скобки функции ЕСЛИОШИБКА, и предложите величину, которую функция будет возвращать.

Вот как я собираюсь использовать ЕСЛИОШИБКА:

=ЕСЛИОШИБКА([ваша формула];[чем заменить сообщение об ошибке])

Давайте рассмотрим очень подходящий случай для использования ЕСЛИОШИБКА.

Пример функции ЕСЛИОШИБКА

Используйте лист IFERROR в Книге с примерами , для этого раздела.

Один из случаев, который может привести к ошибке при использовании формулы ВПР. Если вы используете ВПР, чтобы найти совпадения, есть шанс, что в просматриваемой вами таблице совпадений не будет. Когда такое случается, вы получаете ошибку «N/A» в своей таблице.

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

Эта таблица имеет некоторые ошибки, N/A, потому что в ней есть несколько отсутствующих совпадений.

Я уже написал ВПР функцию которая выдает информацию, какую работу выполняет фрилансер, в зависимости от совпадения с названием компании, которые перечислены в столбце B моей таблицы:

Но тут есть проблема: несколько компаний отсутствуют в моей справочной таблице, и поэтому у нас возникают такие ошибки — N/A. Давайте заменим их чем-то более приличным. ЕСЛИОШИБКА, которую мы собираемся использовать, имеет следующую структуру:

= ЕСЛИОШИБКА(существующая формула, чем заменить ошибку)

В моем случае, я собираюсь заменить ошибку, напоминанием связаться с моим HR менеджером, что бы проверить чем занимается этот фрилансер. Вот моя формула:

Эта формула, может показаться сложной, но запомните: все, что мы делаем это помещаем существующую формулу ВПР, с замещающим сообщением в скобки. Мы просто окружаем нашу формулу функцией ЕСЛИОШИБКА и даем Excel величину для возвращения.

Я заменил ошибку N/A на более полезный и читаемый текст»Contact HR» (Связаться с HR).

Короче говоря, ЕСЛИОШИБКА является особым вариантом функции ЕСЛИ, которую корпорация Майкрософт разработала для нашего удобства. Она автоматически вылавливает ошибки в формулах и выводит вместо этого заданную нами величину (или сообщение).

Резюмируем и Продолжаем Обучение

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

Не важно как давно я использую Excel, я все время нахожу что-то чему еще можно научиться. Если вы хотите продолжить обучение и узнать больше о том, как использовать электронные таблицы, я предлагаю вам еще несколько уроков:

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

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

Основы Excel: как использовать функцию ЕСЛИ

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

Иллюстрация: Meery Mary для Skillbox Media

ЕСЛИ — логическая функция Excel. Она проверяет, выполняются ли заданные условия в выбранном диапазоне таблицы. Пользователь указывает критерий, который нужно проверить, — функция сравнивает этот критерий с данными в ячейках таблицы и выдаёт результат.

В статье разберёмся:

функция ЕСЛИ в Excel;
• как запустить функцию ЕСЛИ с одним условием;
• как запустить функцию с несколькими условиями.

Как работает и для чего нужна функция ЕСЛИ

Функцию ЕСЛИ используют, когда нужно сравнить данные таблицы с критериями пользователя. У функции есть два результата: ИСТИНА и ЛОЖЬ. Первый результат функция выдаёт, когда данные ячейки полностью совпадают с заданным условием, второй — когда данные ячейки условию не соответствуют.

Например, если нужно определить в таблице значения меньше 500, то значение 265 будет отмечено функцией как истинное, а значение 3426 — как ложное.

Можно задавать несколько условий одновременно. Например, найти значения меньше 500, но больше 300. В этом случае функция определит значение 265 как ложное, а 402 — как истинное. Так можно проверять не только числовые значения, но и текст.

Часто функцию ЕСЛИ используют при работе с другими функциями Excel для расширения их возможностей. Например, в случае с ВПР функция ЕСЛИ позволяет настроить поиск сразу по двум критериям.

Рассмотрим, как работает функция ЕСЛИ в классическом виде на примере.

Представим, что в автосалон обратился покупатель с просьбой подобрать ему автомобиль. Его запрос — автомобили чёрного или красного цвета, с объёмом двигателя больше 1,5 л, стоимостью до 2,5 млн рублей. Есть каталог автомобилей, но все характеристики и цены расположены в нём вразброс.

Нужно выяснить, какие из представленных в салоне авто подходят покупателю. Разберёмся пошагово, как это можно сделать с помощью функции ЕСЛИ в Excel.

Шаг №1

Готовимся к запуску функции ЕСЛИ

Для начала рассмотрим, как функция ЕСЛИ работает в классическом виде — для проверки одного условия пользователя. Определим автомобили стоимостью до 2,5 млн рублей.

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

Функция ЕСЛИ приносит результаты в отдельную ячейку, поэтому создадим отдельный столбец для них. Лучше сразу назвать его так, чтобы было понятно, о чём речь. В нашем случае сделаем столбец «До 2,5 млн руб.».

Выделим первую ячейку нового столбца и откроем окно для поиска функций. Это можно сделать двумя способами:

1. Перейти во вкладку «Формулы» и нажать «Вставить функцию».

2. Нажать на «fx» в строке ссылок на любой вкладке Excel.

На экране справа появится окно «Построитель формул». В нём через поиск находим функцию ЕСЛИ и нажимаем «Вставить функцию».

Появляется окно для заполнения аргументов функции: «Лог_выражение», «Значение_если_истина», «Значение_если_ложь». Ниже разберёмся, как их заполнить.

Шаг №2

Заполняем аргументы функции

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

«Лог_выражение» (логическое выражение) — запрос пользователя, который функция будет проверять. В нашем примере это стоимость автомобилей.

Нужно, чтобы функция определила автомобили стоимостью менее или равной 2 500 000 рублей. Порядок действий следующий:

1. Ставим курсор в окно «Лог_выражение» в построителе формул.

2. Выбираем первое значение столбца «Цена, руб.» — ячейку Е2. Обозначение ячейки переносится в окно «Лог_выражение» построителя формул и одновременно с этим появляется в строке ссылок.

3. Дополняем значение E2 запросом пользователя: <=2500000. Одновременно с этим формула в строке ссылок принимает вид:fx=ЕСЛИ(E2<=2500000).

«Значение_если_истина» — результат, который функция принесёт в ячейку, если значение совпадёт с запросом пользователя. В случае с примером — что напишет функция, если проверяемая стоимость авто будет меньше либо равна 2 500 000 рублей.

Наша задача — определить, подходят автомобили каталога под запрос клиента или нет. Поэтому в окне аргумента истины введём значение «Подходит» — можно вводить его без кавычек, Excel добавит их самостоятельно.

«Значение_если_ложь» — результат, который функция принесёт в ячейку, если значение не совпадёт с запросом пользователя. В нашем примере — что напишет функция, если проверяемая стоимость авто будет больше 2 500 000 рублей.

Введём в качестве аргумента ЛОЖЬ значение «Не подходит».

Итоговая формула в строке ссылок примет вид:
fx=ЕСЛИ(E2<=2500000;»Подходит»;»Не подходит»).

Шаг №3

Получаем результат работы функции

Чтобы получить результат функции, нажимаем кнопку «Готово» в построителе формул.

В выбранной ячейке появится результат работы функции: «Подходит». Это значит, что первый автомобиль каталога подходит под запрос пользователя, — его цена 1 910 000 рублей.

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

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

Чтобы настроить функцию ЕСЛИ с несколькими условиями, нужно добавить к ней дополнительные операторы Excel — «И», «ИЛИ».

Они связывают несколько критериев и, в зависимости от того, совпадают они с данными таблицы или нет, выдают результат. Схематично это будет выглядеть так: «ЕСЛИ условие a = 1, И/ИЛИ условие b = 2, И/ИЛИ условие c = 3, то — ИСТИНА, иначе — ЛОЖЬ».

В нашем примере у покупателя было три условия: цвет автомобиля, цена и объём двигателя. Разберёмся, как настроить функцию ЕСЛИ в этом случае.

Создадим ещё один столбец для новых результатов работы функции. Назовём его в соответствии с запросом покупателя: «До 2,5 млн руб., чёрный или красный, больше 1,5 л».

Вызываем окно построителя формул, как делали выше, и заполняем аргументы функции.

Отличия от классического варианта использования функции ЕСЛИ будут при заполнении первого аргумента «Лог_выражение»:

• Ставим курсор в окно «Лог_выражение» в построителе формул, вводим дополнительный аргумент «И» и открываем скобку.
• Первым условием будем вводить два цвета автомобилей, которые выбрал покупатель, поэтому вводим второй дополнительный аргумент «ИЛИ» и открываем ещё одну скобку.
• Выбираем первое значение столбца «Цвет» — ячейку B2. Ставим знак равенства и находим в этом же столбце значение из запроса пользователя: «чёрный» — ячейку B5. Ставим знак точки с запятой.
• Снова выбираем первое значение столбца «Цвет», ставим знак равенства и находим второй цвет из запроса пользователя: «красный» — ячейку B8. Закрываем скобку и ставим знак точки с запятой.
• Закрепляем выбранные ячейки с цветами. На Windows для этого поочерёдно выбираем значения ячеек в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значения ячеек в строке ссылок и нажимаем клавиши Cmd + T. Закрепить эти ячейки нужно, чтобы в дальнейшем можно было протянуть формулу вниз и она сработала корректно для всех остальных строк.
• Выбираем первое значение столбца «Объём двигателя, л» — ячейку D2. Дополняем его запросом пользователя: >1,5. Также ставим знак точки с запятой.
• Выбираем первое значение столбца «Цена, руб.» — ячейку Е2. Дополняем его запросом пользователя: <=2500000, как делали в первом примере. Закрываем скобку.

Формула в строке ссылок принимает вид:
fx=ЕСЛИ(И(ИЛИ(B2=$B$5;B2=$B$8);D2>1,5;E2<=2500000)). Она объединит три запроса покупателя оператором «И», а в первом запросе предоставит возможность выбора с помощью оператора «ИЛИ».

Аргументы «Значение_если_истина» и «Значение_если_ложь» оставляем такими же, как в первом случае: «Подходит» и «Не подходит».

Итоговая формула в строке ссылок принимает вид:
fx=ЕСЛИ(И(ИЛИ(B2=$B$5;B2=$B$8);D2>1,5;E2<=2500000);Подходит»;»Не подходит»). Нажимаем кнопку «Готово».

В выбранной ячейке появляется результат: «Не подходит». Это значит, что первый автомобиль каталога не подходит под запрос покупателя — у него жёлтый цвет, а объём двигателя равен 1,5 л.

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

Как пользоваться функцией ЕСЛИ в «Google Таблицах»? В них тоже есть функция ЕСЛИ, но нет окна построителя формул. Поэтому нужно прописывать её вручную, добавляя все пробелы и кавычки самостоятельно. Готовая формула будет выглядеть так:
fx=ЕСЛИ(E2<=»2500000″;»Подходит»;»Не подходит»).

Функция ЕСЛИ в Excel

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

Результат функции

Результатом функции будет указанное вами значение, указать это самое значение можно для двух исходов(истина или ложь)

Формула

Аргументы функции

• проверяемый аргумент — аргумент, который, в результате выполнения функции, будет проверен. Результатом будет ИСТИНА либо ЛОЖЬ;

• значение_если_истина — значение, которое вернет функция ЕСЛИ в случае, если проверяемый аргумент оказался истиной.

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

Важная информация

• Максимум проверяемых аргументов может быть 64;

• В случае, когда вы используете функцию для проверки каких-либо условий относительно массива, будет проверено каждое значение этого самого массива;

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

На картинке ниже, мы оставили пустое место для значения, которое будет результатом, если проверяемый аргумент оказался ложью:

Тоже самое, но для аргумента «Истины»:

Примеры

Итак, давайте рассмотрим различные ситуации.

Проверяем соответствует ли число заданным критериям с помощью функции ЕСЛИ в Excel

В проверяемом аргументе функции, при работе с обычными числами, вы указываете оператор(или операторы) чтобы проверить, соответствует ли число каким-либо критериям. Вот список этих операторов:

Сразу же рассмотрим такую ситуацию:

Если число в столбце A больше либо равно 35, то результатом выполнения функции будет «Сдал», если же нет, то «Не сдал».

Проверяем сразу несколько критериев

Итак, давайте рассмотрим ситуацию, когда вам нужно проверить, соответствует ли число сразу нескольким критериям. Мы помним, что максимальное число проверяемых аргументов — 64. Давайте попробуем проверить хотя бы 2 критерия.

В приведенном ниже примере мы проверяем два условия.

• Меньше ли значение в ячейке чем число 35;

• В случае, когда в результате первой проверки возвращается ЛОЖЬ, проверяется больше или равно значение в ячейке чем число 75.

Вычисляем комиссию

Итак, с помощью этой функции мы можем даже посчитать комиссию, которую забирает себе конкретный продавец.

В ситуации описанной ниже, продавец не получает комиссию, если у него меньше 50-ти продаж. Если первое проверочное условие он прошел, тогда проверяем второе. Если у продавца меньше 100 продаж, его комиссия будет продажи*2%, а если больше, то — продажи*4%

Пример 4: Использование логических операторов (AND/OR) в функции IF в Excel

Также, мы можем использовать функции И и ИЛИ для проверки по сразу нескольким критериям.

Допустим, как указано на картинке ниже, мы имеем такую табличку:

Наша задача — рассчитать у кого из студентов будет стипендия. Данные для выдачи стипендии будут сразу же в формуле:

Как убрать ошибки при использовании функции ЕСЛИ в Excel

Теперь давайте разберемся как мы можем фильтровать ошибки при использовании функции.

Теперь, если в результате выполнения функции мы получим ошибку, она будет отфильтрована и превращена в 0. А если ошибки не произойдет — мы просто получим значение.