Возможность проведения логических проверок в ячейках является мощным инструментом. Вы найдете бесконечное количество применений для ЕСЛИ() в ваших повседневных документах. Проблема заключается в том, что часто необходимые проверки выходят за рамки возможностей простой функции ЕСЛИ() . Это происходит, когда необходимо сделать проверку на два и более условия до принятия результата.
Синтаксис функции. Где аргументы таковы. Шаг 6: для ложного состояния вашего первого логического теста напишите свой второй логический тест для категории 2, и это соответствует истинному условию. Обратите внимание, что при написании второго логического теста вам нужно вернуться к исходному эталонному значению, но начните сравнивать его со второй категорией.
Поэтому просто введите категорию 3. Шаг 8: Закройте логический тест 1 и логический тест 2, поместив две скобки в конце формулы. Шаг 9: Ссылка заблокирует соответствующие значения. Вы еще не закончили редактирование формулы. Чтобы упростить этот процесс, вы должны помнить о том, чтобы привязать соответствующие ячейки, прежде чем копировать формулу.
Для таких случаев в Excel предусмотрено несколько вариантов: использование ЕСЛИ() внутри другого ЕСЛИ() , функции И() и ИЛИ() . Далее мы познакомимся с этими способами.
Использование ЕСЛИ() внутри другой функции ЕСЛИ()
Давайте рассмотрим вариант на основе изученной ранее функции =ЕСЛИ(А1>1000;"много"; "мало") . Что если вам необходимо вывести другую строку, когда число в А1 является, например, большим, чем 10.000? Другими словами, если выражение А1>1000 верно, вы захотите запустить другую проверку и посмотреть, верно ли, что А1>10000. Такой вариант вы можете создать, применив вторую функцию ЕСЛИ() внутри первой в качестве аргумента значение _если_истина: =ЕСЛИ(А1>1000;ЕСЛИ(А1>10000;"очень много"; "много");"мало") .
Чтобы определить ячейки, вам нужно привязать блокировку, просто подумайте о том, какие ячейки вам нужно плавать, и какие ячейки вам нужно оставаться одинаковыми для каждой формулы. Для значений, которые нам нужно заблокировать, это в основном все, что связано с порогами категорий и категорий, которые мы идентифицировали.
Поэтому идите и привяжите все ячейки, относящиеся к таблице категорий, вправо, оставив значение ячейки данных разблокированным. Шаг 10: Скопируйте формулу вниз, чтобы применить ее ко всем наборам данных. Опять же, убедитесь, что вы завершили Шаг 7, прежде чем перейти к этому заключительному этапу.
Если А1>1000 является истинным, запускается другая функция ЕСЛИ() , возвращающая значение «очень много», когда А1>10000. Если же при этом А1 меньше или равно 10000, возвращается значение «много». Если же при самой первой проверке число А1 будет меньше 1000, выведется значение «мало».
Обратите внимание, что с таким же успехом вы можете запустить вторую проверку, в случае если первая будет ложной (то есть в аргументе значение_если_ложь функции еслио). Вот небольшой пример, возвращающий значение «очень мало», когда число в А1 меньше 100: =ЕСЛИ(А1>1000;"много";ЕСЛИ(А1<100;"очень мало"; "мало")) .
Если ваше значение набора данных меньше 20, оно будет присвоено категории «Низкий». Если ваше значение набора данных меньше 40, оно будет присвоено категории «Средний». Если ваши значения данных не соответствуют ни одному из этих условий, он будет присвоен категории «Высокий». Хотя они все еще несколько подвержены ошибкам, они гораздо более масштабируемы и легче проверяются на наличие ошибок.
Многие инструкции, если им требуется большая сумма денег, и убедитесь, что они могут быть правильно рассчитаны для каждого условия. Если вы не использовали формулу 100% именно таким образом, тогда она могла работать 75% времени, но возвращать неожиданный результат в 25% случаев. Многие инструкции, если это может стать очень трудным, особенно когда вы вернетесь позже, и некоторое время, когда вы пытаетесь узнать, кто или какой другой человек пытался это сделать.
- Подходит к концу.
- Маловероятно: поддерживается захват 25%.
Расчет бонуса с продаж
Хорошим примером использования одной проверки внутри другой проверки является расчет бонуса с продаж персоналу. который работает в Клуб — отель Гелиопарк Талассо, Звенигород . В данном случае, если значение равно X, вы хотите получить один результат, если У — другой, если Z
— третий. Например, в случае вычисления бонуса за успешные продажи возможны три варианта:
Ниже описывается, как будет выглядеть формула. Он по-прежнему функционирует точно и работает так, как ожидалось, но требуется много времени, чтобы сохранить и дольше проверить, что у него есть. Еще одна вопиющая проблема заключается в том, что они просматривали вручную, чтобы ввести оценки и эквивалентные оценки. Каковы шансы, что вы случайно его назовете? Давайте теперь попытаемся реализовать более сложные критерии, которые 64 раза! Несомненно, это возможно, но вы уверены, что хотите подвергнуть себя такого рода усилиям и вероятным ошибкам, которые будет очень трудно сделать?
- Продавец не достиг планового значения, бонус равен 0.
- Продавец превысил плановое значение менее чем на 10%, бонус равен 1 000 рублей.
- Продавец превысил плановое значение более чем на 10%, бонус равен 10 000 рублей.
Вот формула для расчета такого примера: =ЕСЛИ(Е3>0;ЕСЛИ(Е3>0.1;10000;1000);0) . Если значение в Е3 является отрицательным, то возвращается 0 (нет бонуса). В случае когда результат положительный, проверяется, больше ли он 10%, и в зависимости от этого выдается 1 000 или 10 000. Рис. 4.17 показывает пример работы формулы.
Это может быть полезно в сложных вложенных формулах, когда вы пытаетесь определить, есть ли у вас слишком мало скобок. Ниже приведен довольно типичный пример расчета комиссии продаж на основе уровней доходов. 
Хотя это очень похоже на предыдущий пример, эта формула является отличным примером того, насколько сложно справиться с большими инструкциями, если: что вы хотите сделать, если ваша организация решила добавить новые уровни компенсации и даже изменить существующую сумму или процент? У вас много задач на руках!
Функция И()
Часто бывает необходимо провести проверку только на верность одновременно двум условиям. Например, выплата бонуса осуществляется, только когда продажи определенного товара превысили план и также общие продажи превысили план. Если одно из данных условий не выполняется (или оба условия), премия не выплачивается. В булевой логике это называется логическим И, поскольку оба условия должны быть истинны для истинности всего выражения.
На панели формул вы можете вставлять разрывы строк, чтобы упростить длинную формулу. Вот пример сценария комиссии с неправильной логикой. 
Вы видите, в чем проблема? Сравните порядок сравнения прибыли с предыдущим примером. Каким образом это первый запуск? Это прямо вверх дном, а не наоборот. Но почему ты занимаешься большими? Это может быть чрезвычайно проблематичным, потому что во многих ситуациях незаметно проходят типы ошибок, пока они не пострадали. Чтобы узнать, какие из серьезных проблем возникают в сложных вложенных операциях, можете ли вы это сделать?
В Excel выражения логического И обрабатываются с помощью функции И() : И(логическое_значение1;логическое_значение2;…). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо.
Еще раз отметим работу функции:
- Если все выражения возвращают ИСТИНА (или любое положительное число), И() возвращает ИСТИНА.
- Если один или более аргументов возвращают ЛОЖЬ (или 0), И() возвращает ЛОЖЬ.
Чаще всего И() применяется внутри функции ЕСЛИ() . В таком случае, когда все аргументы внутри И() вернут ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение если истина. Если одно или более из выражений в И() вернет ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь.
В большинстве случаев вместо создания сложных формул с помощью функции вы можете использовать функцию поиска. Используя функцию поиска, вы должны сначала создать таблицу апелляции. 
Другими словами, точные значения в таблице реферера, а также значения, которые идут между ними. В этом случае справочные таблицы должны быть упорядочены от наименьшего до самого большого порядка по возрастанию.
Вот небольшой пример: =ЕСЛИ(И(С2>0;В2>0);1000;"нет бонуса") . Если значение в В2 будет больше нуля и значение в С2 будет больше нуля, формула вернет 1000, в противном случае выведется строка «нет бонуса».
Разделение значений по категориям
Полезным применением функции и () является разделение по категориям в зависимости от значения. Например, у вас имеется таблица с результатами какого-то опроса или голосования, и вы хотите разделить все голоса на категории в соответствии со следующими возрастными рамками: 18-34,35-49, 50-64,65 и более. Предполагая, что возраст респондента находится в ячейке В9, следующие аргументы функции и () проводят логическую проверку на принадлежность возраста диапазону: =И(В9>=18;В9
Существуют и другие менее очевидные преимущества. Значения в таблице могут быть легко обновлены, и вам не нужно изменять формулу при изменении условий. Если вы не хотите, чтобы кто-то видел справочную таблицу или что-то менял, просто положите ее на другой лист.
В большинстве случаев вместо создания сложных формул с помощью функции вы можете использовать функцию поиска. Используя функцию поиска, вы должны сначала создать таблицу апелляции.
Другими словами, точные значения в таблице реферера, а также значения, которые идут между ними. В этом случае справочные таблицы должны быть упорядочены от наименьшего до самого большого порядка по возрастанию.
Если ответ человека находится в ячейке С9, следующая формула выведет результат голосования человека, если срабатывает проверка на соответствие возрастной группе 18-34: =ЕСЛИ(И(В9>=18;В9
- 35-49: =ЕСЛИ(И(В9>=35;В9
- 50-64: =ЕСЛИ(И(В9>=50;В9
- 65+: =ЕСЛИ(В9>=65;С9;"")
Функция ИЛИ()
Существуют ситуации, когда вам необходимо решение, только когда одно из условий принимает положительное значение (ИСТИНА). Например, вы можете решить выплатить бонус сотрудникам, когда общие продажи превышают план или когда продажи отдельного товара превысили прогноз. В булевой логике это называется логическим ИЛИ .
Такие условия проверяются в Excel с помощью функции ИЛИ() : ИЛИ(логическое_значение1; логическое_значение2;...). Каждый аргумент представляет собой логическое значение для проверки. Вы можете ввести столько аргументов, сколько вам необходимо. Результат работы ИЛИ() зависит от следующих условий:
- Если один аргумент или более возвращает ИСТИНУ (любое положительное число), ИЛИ() возвращает ИСТИНУ.
- Если все аргументы возвращают ЛОЖЬ (нулевое значение), результатом работы ИЛИ() будет ЛОЖЬ.
Так же как и И() , чаще всего функция ИЛИ() используется внутри проверки ЕСЛИ() . В таком случае, когда один из аргументов внутри ИЛИ() вернет ИСТИНА, функция ЕСЛИ() пойдет по своей ветке значение_если_истина. Если все выражения в ИЛИ() вернут ЛОЖЬ, функция ЕСЛИ() пойдет по ветке значение_если_ложь . Вот небольшой пример: = ЕСЛИ(ИЛИ(С2>0;В2>0);1000;"нет бонуса") .
В случае когда в одной из ячеек (С2 или В2) будет положительное число, функция вернет 1000. Только когда оба значения будут отрицательны (или равны нулю), функция вернет строку "нет бонуса".
В статье речь пойдет о функции табличного редактора Excel «Если». Она связана с логическими возможностями приложения. Данную функцию можно отнести к наиболее распространенной, которая зачастую используется во время работы.
Ключевые возможности
В программе Excel формула «Если» позволяет различного рода задачи, которые требуют сравнить определенные значения и получить результат. Такое решение дает возможность использовать ветвящиеся алгоритмы, создавать дерево решений.
Примеры применения
Функция имеет вид: =ЕСЛИ (задача; истина; ложь). Первой составной частью является логическое выражение. Оно способно выступать в виде фразы или числа. Например, «10» или «без НДС». Такой параметр обязательно следует заполнить. Истина представляет собой значение, отображающееся в качестве результата, если выражение верно. Ложь представляет собой данные, которые будут выданы, когда задача неверна.
Равенство параметров двух ячеек
Чтобы лучше понять возможности функции «Если» в Excel, необходимо привести примеры. Поэтому дальше стоит приступить к их рассмотрению. Нужно ввести в клетку C1 значение 8. После этого в поле по адресу D1 необходимо вписать формулу вида: =ЕСЛИ(C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.
Можно рассмотреть еще один пример. Существует несколько студентов и их отметки, которые заработаны за экзамен. Данные следующие: 5, 4, а также 3 и 2. По условию задания предусматривается создание для каждого из учащегося текстового комментария «сдал» или «не сдал». Таким образом, если студент получил отметку три и выше, считается, что он сдал экзамен. Если его оценка ниже 3, студент не справился с заланием. Для решения подобной задачи следует написать следующую формулу: =ЕСЛИ(C1<3; «не справился»; «сдал»).
Программа начинает сравнивать результаты каждого студента. Если показатель меньше трех, в соответствующей клетке появится надпись «не справился». Когда отметка 3 и выше, в требуемой графе можно увидеть комментарий о том, что студент не сдал экзамен. Стоит отметить, что текстовые комментарии следует всегда писать в кавычках. Во время использования данной функции можно применять следующие операторы сравнения: < >, =, >, <, >=, <=.
Примеры с использованием условий «ИЛИ», «И»
Необходимо продолжить рассмотрение логических возможностей данного приложения. Существует возможность соединить функцию «Если» с операторами сравнения. Это следующие параметры:
«ИЛИ»;
«И».
Необходимо указать важное условие в Excel: когда оценка студента равна или меньше 5, но больше 3. При этом следует отобразить комментарий: «проходит» или «нет». Таким образом, проходят только те студенты, которые заработали пятерки и четверки. Чтобы записать данную задачу в табличном редакторе следует применить специальную формулу. Она имеет следующий вид: =ЕСЛИ(И(A1<=5; A1>3); «проходит», «нет»).
Если рассмотреть более сложный пример, понадобится использовать «ИЛИ» либо «И». Таким образом, можно ознакомиться с применением формулы в Excel, если существует несколько условий в задании. Например: =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; 0). В данном случае можно сделать вывод, когда значение в клетке А1 равно 5 или 10, программа отображает результат 100. В противном случае он составляет 0. Существует возможность применить эти операторы, чтобы найти решение более сложным задачам.
Например, в базе требуется вычислить должников, которым требуется заплатить более 10000 рублей. Можно задать условие, что заем не погашался более полугода, то есть шести месяцев. Благодаря функции табличного редактора Excel «Если» позволяется в автоматическом режиме получить около соответствующих имен отметку «проблемный клиент». Допустим, в клетке A1 размещены данные, которые указывают на срок задолженности (месяцы). Поле B1 содержит сумму.
В данном случае формула представлена в следующем виде: =ЕСЛИ(И(A1>=6; B1>10000); «проблемный клиент»; «»). Таким образом, если выявлен человек, соответствующий указанным условиям, программа отображает напротив его имени соответствующий комментарий. Для других участников списка эта клетка останется пустой.
Также можно ознакомиться с примером, когда ситуация является критической. Необходимо ввести соответствующий комментарий. Как результат, формула получится следующей: =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая ситуация»; «»). Однако если программа определяет совпадения по одному из параметров как минимум, отображается соответствующее примечание.
Сложные задачи
Функция Excel «Если» применяется для того, чтобы избежать встроенных ошибок при делении на ноль. Кроме того, она используется и в некоторых других ситуациях. Первый случай обозначается, как «ДЕЛ/0». Его можно встретить достаточно часто. Обычно это происходит тогда, когда формула «A/B» требует копирования. В это же время показатель B в отдельных ячейках составляет 0. Чтобы избежать подобной ситуации, стоит использовать возможности рассматриваемого оператора. Таким образом, требуемая формула имеет следующий вид: =ЕСЛИ(B1=0; 0; A1/B1). Итак, если клетка B1 заполнена значением «ноль», редактор отобразит «0». В противном случае программа поделит показатель A1 на данные B1 и выдаст требуемый результат.
Скидка
Как показывает практика, зачастую возникают ситуации, которые рассматриваются далее. Потребуется рассчитать скидки, учитывая общую сумму средств, затраченных на покупку конкретного товара. Матрица, которая используется, выглядит следующим образом: менее 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; более 5001 - 7%. Можно ознакомиться с ситуацией, когда в Excel имеется база данных посетителей и сведения о сумме, потраченной на покупку. Далее следует рассчитать скидку для каждого клиента. Для этого понадобится применить следующее выражение: =ЕСЛИ(A1>=5001; B1*0,93; ЕСЛИ(А1>=3001; B1*0,95;..).
Система выполняет проверку общей суммы покупок. Если она превышает значение в 5001 рублей, выполняется умножение на 93 процента цены товара. Если превышена отметка в 3001 единиц, происходит аналогичное действие, однако уже учитывается 95%.
