Как использовать логические функции в Excel IF, AND, OR, XOR, NOT

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

Как использовать функцию IF

Функция IF - это основная логическая функция в Excel, и поэтому она должна быть понятна первой. Это будет появляться много раз на протяжении всей этой статьи.

Давайте посмотрим на структуру функции IF, а затем посмотрим несколько примеров ее использования..

Функция IF принимает 3 бита информации:

= IF (логический_тест, [value_if_true], [value_if_false])

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

Операторы сравнения для использования с логическими функциями

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

Теперь давайте посмотрим на некоторые примеры в действии.

Пример функции IF 1: текстовые значения

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

Следующая формула используется в столбце C для отображения «Нет», если столбец B содержит текст «Завершено» и «Да», если он содержит что-либо еще.

= ЕСЛИ (B2 = "Завершено", "Нет", "Да")

Хотя функция IF не чувствительна к регистру, текст должен точно соответствовать.

Пример функции IF 2: Числовые значения

Функция IF также отлично подходит для сравнения числовых значений.

В приведенной ниже формуле мы проверяем, содержит ли ячейка B2 число, большее или равное 75. Если это так, то мы отображаем слово «Pass», а если не слово «Fail».

= ЕСЛИ (В2> = 75, "Проход", "Сбой")

Функция IF - это намного больше, чем просто отображение другого текста в результате теста. Мы также можем использовать его для запуска различных расчетов.

В этом примере мы хотим предоставить скидку 10%, если клиент тратит определенную сумму денег. Мы будем использовать £ 3000 в качестве примера.

= ЕСЛИ (В2> = 3000, В2 * 90%, В2)

Часть формулы B2 * 90% позволяет вычесть 10% из значения в ячейке B2. Есть много способов сделать это.

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

Пример функции IF 3: значения даты

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

Приведенная ниже формула используется в столбце C. Мы проверяем, меньше ли дата оплаты в ячейке B2, чем сегодняшняя дата (функция TODAY возвращает сегодняшнюю дату с часов компьютера).

= ЕСЛИ (В2 
 
Что такое вложенные формулы IF??
 
Возможно, вы слышали о термине «вложенные IF» раньше. Это означает, что мы можем написать функцию IF внутри другой функции IF. Мы можем захотеть сделать это, если нам нужно выполнить более двух действий.
 
Одна функция IF способна выполнять два действия ( значение_если_истина а также значение_если_ложь ). Но если мы вставим (или вложим) другую функцию IF в значение_если_ложь раздел, то мы можем выполнить другое действие.
 
Возьмите этот пример, где мы хотим отобразить слово «Отлично», если значение в ячейке B2 больше или равно 90, отобразить «Хорошо», если значение больше или равно 75, и отобразить «Плохо», если что-то еще.
 
= ЕСЛИ (В2> = 90, "Отлично", ЕСЛИ (В2> = 75, "Хорошо", "Плохо"))
 
 
Теперь мы расширили нашу формулу за пределы того, что может сделать только одна функция IF. И вы можете вложить больше функций IF, если это необходимо.
 
Обратите внимание на две закрывающие скобки в конце формулы-1 для каждой функции IF.
 
Существуют альтернативные формулы, которые могут быть чище, чем этот вложенный подход IF. Одна очень полезная альтернатива - это функция SWITCH в Excel..
 
Логические функции AND и OR
 
Функции AND и OR используются, когда вы хотите выполнить более одного сравнения в своей формуле. Только функция IF может обрабатывать только одно условие или сравнение.
 
Возьмите пример, где мы дисконтируем значение на 10% в зависимости от суммы, которую тратит клиент а также сколько лет они были клиентом.
 
Сами функции AND и OR возвращают значение TRUE или FALSE..
 
Функция AND возвращает TRUE, только если выполняется каждое условие, а в противном случае возвращает FALSE. Функция OR возвращает TRUE, если выполняется одно или все условия, и возвращает FALSE, только если не выполняются никакие условия..
 
Эти функции могут тестировать до 255 условий, поэтому, конечно, не ограничиваются только двумя условиями, как показано здесь.
 
Ниже приведена структура функций И ​​и ИЛИ. Они написаны одинаково. Просто замените имя И на ИЛИ. Это просто их логика, которая отличается.
 
= И (логический1, [логический2]…)
 
Давайте посмотрим на пример того, как они оба оценивают два условия.
 
Пример функции AND
 
Функция AND используется ниже для проверки того, потратил ли клиент не менее 3000 фунтов стерлингов и был ли он клиентом не менее трех лет.
 
= И (В2> = 3000, С2> = 3)
 
 
Вы можете видеть, что он возвращает FALSE для Мэтта и Терри, потому что хотя они оба соответствуют одному из критериев, они должны соответствовать обеим функциям AND.
 
Пример функции OR
 
Функция ИЛИ используется ниже, чтобы проверить, потратил ли клиент не менее 3000 фунтов стерлингов или был клиентом не менее трех лет..
 
= ИЛИ (В2> = 3000, С2> = 3)
 
 
В этом примере формула возвращает TRUE для Matt и Terry. Только Джули и Джиллиан не выполняют оба условия и возвращают значение FALSE.
 
Использование AND и OR с функцией IF
 
Поскольку функции И и ИЛИ возвращают значение ИСТИНА или ЛОЖЬ, когда используются по отдельности, они редко используются сами по себе..
 
Вместо этого вы, как правило, будете использовать их с функцией IF или внутри функции Excel, такой как условное форматирование или проверка данных, для выполнения некоторых ретроспективных действий, если формула имеет значение TRUE..
 
В приведенной ниже формуле функция AND вложена в логический тест функции IF. Если функция AND возвращает TRUE, тогда скидка 10% от суммы в столбце B; в противном случае скидка не предоставляется, а значение в столбце B повторяется в столбце D.
 
= ЕСЛИ (И (В2> = 3000, С2> = 3), В2 * 90%, В2)
 
 
Функция XOR
 
В дополнение к функции ИЛИ, есть также эксклюзивная функция ИЛИ. Это называется функцией XOR. Функция XOR была представлена ​​в версии Excel 2013.
 
Эта функция может потребовать некоторых усилий, чтобы понять, поэтому показан практический пример.
 
Структура функции XOR такая же, как функция OR.
 
= XOR (логический1, [логический2]…)
 
При оценке только двух условий функция XOR возвращает:
 
 
ИСТИНА, если любое условие оценивается как ИСТИНА.
 
ЛОЖЬ, если оба условия ИСТИНА или ни одно из условий ИСТИНА.
 
 
Это отличается от функции ИЛИ, потому что это вернет ИСТИНА, если оба условия были ИСТИНА.
 
Эта функция становится немного более запутанной, когда добавляется больше условий. Затем функция XOR возвращает:
 
 
ИСТИНА, если странный число условий возврата ИСТИНА.
 
ЛОЖЬ, если четное число условий приводит к ИСТИНА, или если все условия ЛОЖЬ.
 
 
Давайте посмотрим на простой пример функции XOR.
 
В этом примере продажи делятся на две половины года. Если продавец продает 3000 и более фунтов стерлингов в обеих половинах, ему назначается Золотой стандарт. Это достигается с помощью функции AND с IF, как ранее в статье.
 
Но если они продают 3000 фунтов или больше в любой половине, мы хотим присвоить им Серебряный статус. Если они не продают 3000 и более фунтов стерлингов в обоих случаях, то ничего.
 
Функция XOR идеально подходит для этой логики. Приведенная ниже формула вводится в столбец E и показывает функцию XOR с IF для отображения «Да» или «Нет» только в случае выполнения любого условия.
 
= IF (XOR (В2> = 3000, С2> = 3000), "Да", "Нет")
 
 
Функция НЕ
 
Последней логической функцией, которую мы обсудим в этой статье, является функция NOT, и мы оставим самую простую последнюю. Хотя иногда бывает трудно увидеть использование функции в реальном мире.
 
Функция NOT меняет значение своего аргумента. Таким образом, если логическое значение ИСТИНА, тогда оно возвращает ЛОЖЬ. И если логическое значение ЛОЖЬ, он вернет ИСТИНА.
 
Это будет легче объяснить на некоторых примерах.
 
Структура функции НЕ имеет вид;
 
= НЕ (логическое)
 
НЕ Функциональный Пример 1
 
В этом примере представьте, что у нас есть головной офис в Лондоне, а затем много других региональных сайтов. Мы хотим отобразить слово «Да», если на сайте есть что-то, кроме Лондона, и «Нет», если это Лондон..
 
Функция NOT была вложена в логический тест функции IF ниже, чтобы сторнировать ИСТИННЫЙ результат.
 
= ЕСЛИ (НЕ (B2 = "London"), "Да", "Нет")
 
 
Это также может быть достигнуто с помощью логического оператора NOT. Ниже приведен пример.
 
= ЕСЛИ (B2 "Лондон", "Да", "Нет")
 
НЕ Функциональный Пример 2
 
Функция NOT полезна при работе с информационными функциями в Excel. Это группа функций в Excel, которые что-то проверяют и возвращают TRUE, если проверка прошла успешно, и FALSE, если это не так.
 
Например, функция ISTEXT проверит, содержит ли ячейка текст, и вернет TRUE, если она есть, и FALSE, если нет. Функция NOT полезна, потому что она может отменить результат этих функций.
 
В приведенном ниже примере мы хотим заплатить продавцу 5% от суммы, которую он продает. Но если они ничего не перепродали, в ячейке есть слово «Нет», и это приведет к ошибке в формуле.
 
Функция ISTEXT используется для проверки наличия текста. Это возвращает TRUE, если текст есть, поэтому функция NOT переворачивает это на FALSE. И если ИФ выполняет свой расчет.
 
= ЕСЛИ (НЕ (ISTEXT (В2)), В2 * 5%, 0)
 
 
Овладение логическими функциями даст вам большое преимущество как пользователю Excel. Очень полезно иметь возможность проверять и сравнивать значения в ячейках и выполнять различные действия на основе этих результатов..
 
 
В этой статье рассматриваются лучшие логические функции, используемые сегодня. В последних версиях Excel появилось больше функций, добавленных в эту библиотеку, таких как функция XOR, упомянутая в этой статье. Будьте в курсе этих новых дополнений, вы будете впереди толпы.