Значение, указанное, когда два условия истинны или ложны.
Чаще всего количество возможных состояний не 2 (контролируемых и чередующихся), а 3, 4 и более. В этом случае вы также можете использовать функцию ЕСЛИ, но теперь вы должны вложить их друг в друга, указав все условия по очереди. Рассмотрим следующий пример.
Разным менеджерам по продажам должны начисляться бонусы в зависимости от выполнения плана продаж. Система мотивации следующая. Если план удовлетворен менее чем на 90%, премия не подлежит уплате, если от 90% до 95% — 10% премии, от 95% до 100% — 20% премии, а если план превышен, то 30%. Как видите, здесь есть 4 варианта. Чтобы указать их в формуле, требуется следующая логическая структура. Если первое условие выполняется, то выполняется первый вариант, в противном случае, если выполняется второе условие, возникает второй вариант, иначе, если .. и т.д. Количество условий может быть довольно большим. В конце формулы указывается последняя альтернатива, для которой не выполняется ни одно из перечисленных выше условий (например, третье поле в нормальной формуле ЕСЛИ). Следовательно, формула выглядит так.
Комбинация функций SE работает таким образом, что при выполнении одного из указанных условий следующие больше не совпадают. Поэтому важно указывать их в правильной последовательности. Если бы мы начали проверку с B2 при написании формулы легко запутаться, поэтому рекомендуется посмотреть подсказку.
В конце убедитесь, что вы закрыли все скобки, иначе Excel выдаст ошибку
Вот как выглядит синтаксис этой функции и ее аргументов:
= ЕСЛИ (логическое выражение, значение, если да, значение, если нет»)
Логическое выражение — это (обязательное) условие, которое возвращает истину или ложь (да или нет»);
Значение, если «да» — действие (обязательное), которое выполняется в случае положительного ответа;
Значение, если «нет» — действие (обязательное), которое выполняется в случае отрицательного ответа;
Давайте вместе рассмотрим эти темы подробнее.
Первый аргумент — логичный вопрос. И этот ответ может быть только «да» или «нет», «правда» или «ложь».
Как правильно задать вопрос? Для этого можно составить логическое выражение, используя знаки “=”, “>”, “=”, “”.
Когда необходимо проверить несколько истинных условий, используется функция И. Суть заключается в следующем: ЕСЛИ a = 1 И a = 2, ТО значение в ELSE является значением c.
Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие выполняется, результат будет истинным. Нижняя строка: ЕСЛИ a = 1 ИЛИ a = 2, ТО значение в ELSE значение c.
С помощью функций И и ИЛИ можно проверить до 30 условий.
Пример использования оператора И:
Пример использования функции ИЛИ:
Допустим, вы работаете в компании, которая продает шоколад в разных регионах и работает с большим количеством клиентов.
Отдельно стоит выделить продажи, которые происходили в нашем регионе и за рубежом. Для этого вам нужно добавить в таблицу для каждой продажи еще одну характеристику — страну, в которой она произошла. Мы хотим, чтобы этот тег автоматически генерировался для каждой записи (то есть строки).
В этом нам поможет функция SE. Добавим в таблицу данных столбец «Страна». Западный регион — это местные продажи («Местные»), а остальные регионы — зарубежные продажи («Экспорт»).
Мы только что видели пример использования оператора IF с одним логическим выражением. Но в программе также есть возможность установить более одного условия. В этом случае первая проверка будет выполнена на первой, и в этом случае указанное значение будет отображаться сразу. И только если первое логическое выражение не выполнено, управление вторым будет действовать.
Давайте посмотрим на примере той же таблицы. Но на этот раз усложняем задачу. Теперь нужно делать скидку на женскую обувь в зависимости от вида спорта.
Первое условие — проверка пола. Если это «мужской», сразу отображается значение 0. Если «женский», проверка начинается на основе второго условия. Если спорт беговой — 20%, если теннис — 10%.
Пишем формулу этих условий в нужную нам ячейку.
= SE (B2 = «мужской»; 0; SE (C2 = «ход»; 20%; 10%))
Нажмите Enter и получите результат согласно заданным условиям.
Затем мы распространяем формулу на все оставшиеся строки таблицы.
Операторы сравнения чисел и строк представлены операторами, состоящими из одного или двух равенств и математических неравенств:
Синтаксис:
| 1 | Результат = Выражение1 Оператор Выражение2 |
Если переменная Result объявлена как Boolean (или Variant), она вернет False и True.Числовые переменные других типов вернут значения 0 (False) и -1 (True).
Операторы сравнения чисел и строк работают с двумя числами или двумя строками. При сравнении числа со строкой или строки с числом VBA Excel выдаст ошибку несоответствия типа):
| 1 2 3 4 5 6 7 8 9 10 | Sub Primer1 () При ошибке GoTo Instr Dim myRes As Boolean ‘Сравните строку с числом myRes = «five»> 3 Instr: If Err.Description «» Then MsgBox «Произошла ошибка:» & Err.Description End If Конец подзаголовка |
Сравнение строк начинается с их первых символов. Если они совпадают, сравниваются следующие символы. И так до тех пор, пока символы не станут другими или не закончится одна или обе строки.
Значения буквенных символов увеличиваются в алфавитном порядке, сначала все прописные (прописные) буквы, затем строчные. Если вам нужно сравнить длины строк, используйте функцию Len.
| 1 2 3 | myRes = «семь»> «восемь» ‘myRes = True myRes = «семь»> «восемь»‘ myRes = False myRes = Len («семь»)> Len («восемь») ‘myRes = False |
Также в Excel можно просмотреть данные об одновременном выполнении двух условий. В этом случае значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этого действия используется оператор И».
Давайте посмотрим на нашу таблицу в качестве примера. Отныне скидка 30% будет применяться только в том случае, если это женская обувь, предназначенная для бега. При соблюдении этих условий значение ячейки будет 30%, в противном случае — 0.
Для этого воспользуемся следующей формулой:
= ЕСЛИ (И (B2 = «женский»; C2 = «ход»); 30%; 0)
Нажмите клавишу Enter, чтобы просмотреть результат в ячейке.
Как и в приведенных выше примерах, растяните формулу до остальных строк.
«SE» — это стандартная функция Microsoft Excel. В его обязанности входит контроль соблюдения определенного условия. Когда условие выполняется (истина), значение возвращается в ячейку, в которой используется эта функция, а если оно не выполняется (ложь), возвращается другое значение.
Синтаксис этой функции следующий: «ЕСЛИ (логическое выражение; [функция, если истина]; [функция, если ложь])».
Установите курсор в ячейку G2 и введите знак «=». Для Excel это означает, что формула будет вставлена. Поэтому, как только буква «е» будет нажата дальше, мы получим предложение выбрать функцию, которая начинается с этой буквы. Выбираем «СЭ”.
Кроме того, все наши действия также будут сопровождаться предложениями.
Пишем в качестве первого аргумента: C2 = «Запад». Как и в других функциях Excel, здесь нет необходимости вручную вводить адрес ячейки, достаточно просто щелкнуть по ней мышью. Поэтому мы ставим «» и указываем второй аргумент.
Второй аргумент — это значение, которое примет ячейка G2, если условие, которое мы отметили, выполнено. Это будет слово «Местный”.
Далее мы снова указываем значение третьего аргумента через запятую. Это значение будет принято ячейкой G2, если условие не выполнено: «Экспорт». Не забудьте завершить ввод формулы, закрыв круглые скобки и нажав «Enter”.
Наша функция выглядит так:
= SE (C2 = Запад, Местный, Экспорт”)
Наша ячейка G2 настроена на Local».
Теперь нашу функцию можно скопировать во все остальные ячейки в столбце G.
Вот еще одна типичная проблема: цена за единицу продукта меняется в зависимости от количества. Ваша цель — написать формулу, которая вычисляет цену для любого количества предметов, введенных в определенную ячейку. Другими словами, ваша формула должна проверять разные условия и выполнять разные вычисления в зависимости от диапазона суммы, к которой относится указанное количество товаров.
Эту задачу также можно выполнить с помощью нескольких вложенных функций ЕСЛИ. Логика такая же, как в предыдущем примере, с той лишь разницей, что вы умножаете указанное количество на значение, возвращаемое вложенными условиями (то есть на соответствующую цену за единицу).
Предполагая, что количество находится в B8, формула будет выглядеть так:
= L8 * SE (La8> = 101; 12; SE (La8> = 50; 14; SE (La8> = 20; 16; SE (La8> = 11; 18; SE (La8> = 1; 22; “”)))))
И вот результат:
Как вы понимаете, этот пример демонстрирует только общий подход, и вы можете легко настроить эту вложенную функцию на основе конкретного действия.
Например, вместо того, чтобы «кодировать» цены в самой формуле, вы можете ссылаться на ячейки, в которых они появляются (ячейки с B2 по B6). Это позволит вам изменить исходные данные без обновления самой формулы:
= L8 * SE (L8> = 101; L6; SE (L8> = 50; L5; SE (L8> = 20; L4; SE (L8> = 11; L3; SE (L8> = 1; L2; “”)))))
Если вас не интересует, что произойдет, например, если интересующее вас условие не выполняется, вы можете опустить второй аргумент. Например, мы предоставляем скидку 10% при заказе более 100 наименований. Мы не указываем никаких аргументов в случае невыполнения условия.
= ЕСЛИ (MI2> 100; F2 * 0,1)
Что будет в результате?
Насколько красиво и удобно — судить вам. Я думаю, что в любом случае лучше использовать оба аргумента.
А если второе условие не выполняется, но ничего делать не нужно, введите в ячейку пустое значение.
= ЕСЛИ (MI2> 100; FA2 * 0,1,””)
Однако эту конструкцию можно использовать в случае, если значение «Истина» или «Ложь» используется другими функциями Excel в качестве логических значений.
Также обратите внимание, что результирующие логические значения в ячейке всегда центрируются. Это видно на скриншоте выше.
Кроме того, если вам действительно нужно проверить некоторые условия и получить «Истина» или «Ложь» («Да» или «Нет»), вы можете использовать следующую конструкцию –
= ЕСЛИ (MI2> 100; ИСТИНА; ЛОЖЬ)
Учтите, что здесь кавычки использовать не нужно. Если вы заключите аргументы в кавычки, функция ЕСЛИ вернет текстовые значения, а не логические значения.
Если вам нужно определить, пуста ли ячейка, вы можете использовать функцию ISBLANK, которая имеет следующий синтаксис:
= БАЛАНС (значение)
Аргумент значения может быть ячейкой или ссылкой на диапазон. Если значение относится к пустой ячейке или диапазону, функция возвращает логическое значение ИСТИНА, в противном случае — ЛОЖЬ.
Функции ИСТИНА и ЛОЖЬ предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят так:
= ИСТИНА()
= ЛОЖЬ()
Например, ячейка A1 содержит логическое выражение. Затем следующая функция вернет значение «Пройдено», если выражение в ячейке A1 ИСТИНА:
= ЕСЛИ (A1 = ИСТИНА (), «Пройден», «Стоп”)
В противном случае формула вернет «Стоп”.
Составное условие состоит из простых логических операций, связанных И () и ИЛИ().
И () — логическая операция, требующая одновременного выполнения всех связанных с ней условий.
ИЛИ () — это логическая операция, которая требует выполнения любого из связанных с ней перечисленных условий.
Для чего нужна функция SE ()? Взгляните на схему. Вот простой пример того, как работает функция при определении знака числа a.
Блок-схема «Простое состояние». Определение отрицательных и неотрицательных чисел
Условие a> = 0 определяет два возможных варианта: неотрицательное число (ноль или положительное) и отрицательное число. Ниже представлена схема написания формулы в Excel. Параметры перечислены после условия и разделены точкой с запятой. Если условие истинно, в ячейке будет отображаться «неотрицательный» текст, в противном случае — «отрицательный». То есть запись, соответствующая ветви схемы, — «Да», за которой следует «Нет».
Текстовые данные в формуле заключаются в кавычки, а формулы и числа пишутся без них.
Если результатом должны были быть данные, полученные в результате вычислений, см. Следующий пример. Увеличиваем неотрицательное число на 10, а отрицательное оставляем без изменений.
Блок-схема «Простое состояние». Расчет данных
На диаграмме показано, что при выполнении условия число увеличивается на десять и вычисленное выражение A1 + 10 (выделено зеленым цветом) записывается в формулу Excel. В противном случае число не меняется и здесь вычисляемое выражение состоит только в обозначении самого числа А1 (выделено красным).
Это было краткое введение для новичков, только начинающих знакомиться с основами Excel. Теперь давайте посмотрим на более серьезный пример с использованием условной функции.
Упражнение:
Прогрессивная ставка зависит от дохода. Если доход компании превышает определенную сумму, ставка налога выше. Используя функцию SE, рассчитайте сумму налога.
Решение:
Решение этой проблемы можно увидеть на следующем рисунке. Но давайте все же попробуем прояснить эту иллюстрацию. Основные исходные данные для решения этой проблемы можно найти в столбцах A и B. В ячейке A5 указано пороговое значение дохода, при котором изменяется ставка налога. Соответствующие ставки показаны в ячейках B5 и B6. Доход от бизнеса отображается в диапазоне ячеек B9: B14. Формула расчета налога записана в ячейке C9: = IF (B9> A $ 5; B9 * B $ 6; B9 * B $ 5). Эту формулу необходимо скопировать в ячейки ниже (выделены желтым).
В формуле расчета адреса ячеек записываются как A $ 5, B $ 6, B $ 5. Знак доллара фиксирует часть адреса, перед которой он устанавливается при копировании формулы. Запрещается менять номер строки в адресе ячейки.
В функцию ЕСЛИ также можно вставить различные условия. В этой ситуации используется вложение одного оператора IF в другой. Когда условие выполнено, указанный результат отображается в ячейке, но если условие не выполняется, отображаемый результат зависит от второго оператора.
Теперь рассмотрим конкретные примеры использования формулы ЕСЛИ».
При использовании функции ЕСЛИ в Excel вы можете использовать различные операторы для проверки статуса. Вот список операторов, которые вы можете использовать:
Если сумма баллов больше или равна «35», то формула возвращает «Пройден», в противном случае — «Неудачно”.
Одним из самых популярных и полезных инструментов в Excel является функция ЕСЛИ, которая проверяет данные на соответствие условиям, которые мы установили, и автоматически выдает результат, исключая возможность ошибок из-за человеческой ошибки. Поэтому знания и умение пользоваться этим инструментом сэкономят время не только на многих задачах, но и на поиске возможных ошибок за счет «ручного режима работы.
