Если больше, но меньше чем excel
Обновлено: 04.07.2024
Многие задачи, которые вы выполняете в Excel, включают сравнение данных в разных ячейках. Для этого в Microsoft Excel предусмотрено шесть логических операторов, которые также называются операторами сравнения. Это руководство призвано помочь вам понять суть логических операторов Excel и написать наиболее эффективные формулы для анализа данных.
Логические операторы Excel — обзор
Логический оператор используется в Excel для сравнения двух значений. Логические операторы иногда называют булевыми операторами, потому что результат сравнения в любом конкретном случае может быть либо ИСТИНА, либо ЛОЖЬ.
В Excel доступно шесть логических операторов. В следующей таблице объясняется, что делает каждый из них, и иллюстрируется теория примерами формул.
Однако, если вы попробуете либо =A1=12/1/2014, либо =A1="12/1/2014", в результате вы получите ЛОЖЬ. Немного неожиданно, да?
Дело в том, что Excel хранит даты как числа, начинающиеся с 1 января 1900 года, которые сохраняются как 1. Дата 1/12/2014 хранится как 41974. В приведенных выше формулах Microsoft Excel интерпретирует «12/1». /2014" как обычная текстовая строка, а поскольку "12/1/2014" не равно 41974, возвращается FALSE.
Чтобы получить правильный результат, вы всегда должны заключать дату в функцию ДАТАЗНАЧ, например: =A1=ДАТАЗНАЧ("1/12/2014")
Примечание. Функцию DATEVALUE необходимо использовать и с другим логическим оператором, как показано в следующих примерах.
Тот же подход следует применять при использовании оператора равенства Excel в логической проверке функции ЕСЛИ. Вы можете найти дополнительную информацию, а также несколько примеров формул в этом руководстве: Использование функции ЕСЛИ в Excel с датами.
Пример 2. Использование оператора "Равно" с текстовыми значениями
Использование оператора Excel Равно с текстовыми значениями не требует никаких дополнительных хитростей. Единственное, что вы должны иметь в виду, это то, что логический оператор Равно в Excel нечувствителен к регистру, а это означает, что различия в регистре игнорируются при сравнении текстовых значений.
Например, если ячейка A1 содержит слово "апельсины", а ячейка B1 содержит слово "апельсины", формула =A1=B1 вернет значение TRUE.
Если вы хотите сравнить текстовые значения с учетом различий в регистре, вам следует использовать функцию EXACT вместо оператора Равно. Синтаксис функции EXACT прост:
Где текст 1 и текст 2 — это значения, которые вы хотите сравнить. Если значения совпадают, включая регистр, Excel возвращает TRUE; в противном случае возвращается ЛОЖЬ. Вы также можете использовать функцию EXACT в формулах ЕСЛИ, когда вам нужно сравнение текстовых значений с учетом регистра, как показано на снимке экрана ниже:
Примечание. Если вы хотите сравнить длину двух текстовых значений, вы можете вместо этого использовать функцию ДЛСТР, например =ДЛСТР(A2)=ДЛСТР(B2) или =ДЛСТР(A2)>=ДЛСТР(B2) .
Пример 3. Сравнение логических значений и чисел
Распространено мнение, что в Microsoft Excel логическое значение ИСТИНА всегда равно 1, а ЛОЖЬ — 0. Однако это верно лишь отчасти, и ключевое слово здесь «всегда», точнее «не всегда». : )
При написании логического выражения "равно" для сравнения логического значения и числа необходимо специально указать для Excel, что нечисловое логическое значение должно рассматриваться как число. Вы можете сделать это, добавив двойной знак минус перед логическим значением или ссылкой на ячейку, например. грамм. =A2=--ИСТИНА или =A2=--B2 .
Первый знак минус, который технически называется унарным оператором, приводит ИСТИНА/ЛОЖЬ к -1/0 соответственно, а второй унарный инвертирует значения, превращая их в +1 и 0. Это, вероятно, будет проще понять, глядя на следующий скриншот:
Примечание. Вы должны добавить двойной унарный оператор перед логическим значением при использовании других логических операторов, таких как не равно, больше чем или меньше чем для корректного сравнения числовые и логические значения.
При использовании логических операторов в сложных формулах вам также может понадобиться добавить двойное унарное выражение перед каждым логическим выражением, которое возвращает ИСТИНА или ЛОЖЬ в качестве результата. Вот пример такой формулы: СУММПРОИЗВ и СУММЕСЛИМН в Excel.
Использование логического оператора «Не равно» в Excel
Вы используете оператор Excel Не равно (<>), когда хотите убедиться, что значение ячейки не равно указанному значению. Использование оператора Не равно очень похоже на использование оператора Равно, которое мы только что обсуждали.
Результаты, возвращаемые оператором Не равно, аналогичны результатам, полученным функцией НЕ Excel, которая инвертирует значение своего аргумента. В следующей таблице приведены несколько примеров формул.
| Не равно оператору | НЕ функция | Описание |
| =A1<>B1 | =NOT(A1=B1) | Возвращает TRUE, если значения в ячейках A1 и B1 не совпадают, в противном случае FALSE. |
| =A1<>"апельсины" | =NOT(A1="апельсины ") | Возвращает TRUE, если ячейка A1 содержит любое значение, кроме "апельсинов", FALSE, если она содержит "апельсины" или "АПЕЛЬСИНЫ" или "апельсины" и т. д. | =A1<>TRUE | =NOT(A1=TRUE) | Возвращает TRUE, если ячейка A1 содержит любое значение, отличное от TRUE, иначе FALSE. |
| =A1<>(B1/2) | =NOT(A1=B1/2) | Возвращает TRUE, если число в ячейке A1 не равно частному от деления B1 на 2, в противном случае FALSE. |
| =A1<>DATEVALUE("1/12/2014") | < td>=NOT(A1=DATEVALUE("1/12/2014"))Возвращает TRUE, если A1 содержит любое значение, кроме даты 1 декабря 2014 г., независимо от даты f ormat, FALSE в противном случае. |
Больше, Меньше, Больше или равно, Меньше или равно
Вы используете эти логические операторы в Excel, чтобы проверить, как одно число сравнивается с другим. Microsoft Excel предоставляет 4 операции сравнения, названия которых говорят сами за себя:
При сравнении текстовых значений Microsoft Excel игнорирует их регистр и сравнивает значения символ за символом, при этом "a" считается наименьшим текстовым значением, а "z" — самым высоким текстовым значением.
Итак, при сравнении значений слов "яблоки" (A1) и "бананы" (B1) Excel начинает с их первых букв "a" и "b". " соответственно, и поскольку "b" больше, чем "a", формула =A1>B1 возвращает ЛОЖЬ.
Если первые буквы совпадают, то сравниваются вторые буквы, если они тоже совпадают, то Excel получает 3-ю, 4-ю буквы и так далее. Например, если A1 содержит "яблоки", а B1 содержит "агава", формула =A1>B1 вернет значение TRUE, поскольку "p" больше, чем "g".
На первый взгляд кажется, что использование операторов сравнения с текстовыми значениями имеет очень мало практического смысла, но никогда не знаешь, что может понадобиться тебе в будущем, так что, возможно, эти знания кому-то пригодятся.
Распространенное использование логических операторов в Excel
В реальной работе логические операторы Excel редко используются сами по себе. Согласитесь, булевы значения TRUE и FALSE, которые они возвращают, хоть и очень истинны (извините за каламбур), но не очень значимы. Чтобы получить более точные результаты, вы можете использовать логические операторы в составе функций Excel или правил условного форматирования, как показано в приведенных ниже примерах.
1. Использование логических операторов в аргументах функций Excel
Что касается логических операторов, Excel очень либерален и позволяет использовать их в параметрах многих функций. Одно из наиболее распространенных применений находится в функции ЕСЛИ в Excel, где операторы сравнения могут помочь построить логический тест, а формула ЕСЛИ вернет соответствующий результат в зависимости от того, оценивается ли тест как ИСТИНА или ЛОЖЬ. Например:
=ЕСЛИ(A1>=B1, "ОК", "НЕ ОК")
Эта простая формула ЕСЛИ возвращает "ОК", если значение в ячейке A1 больше или равно значению в ячейке B1, в противном случае "Не подходит".
А вот еще один пример:
Формула сравнивает значения в ячейках A1 и B1, и если A1 не равно B1, возвращается сумма значений в ячейках A1:C1, в противном случае — пустая строка.
Логические операторы Excel также широко используются в специальных функциях ЕСЛИ, таких как СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ и их аналогах во множественном числе, которые возвращают результат на основе определенного условия или нескольких условий.
Множество примеров формул можно найти в следующих руководствах:
2. Использование логических операторов Excel в математических вычислениях
Конечно, функции Excel очень эффективны, но вам не всегда нужно использовать их для достижения желаемого результата. Например, результаты, возвращаемые следующими двумя формулами, идентичны:
Функция ЕСЛИ: =ЕСЛИ(B2>C2, B2*10, B2*5)
Формула с логическими операторами: =(B2>C2)*(B2*10)+(B2
Думаю, формулу ЕСЛИ интерпретировать легче, верно? Он указывает Excel умножить значение в ячейке B2 на 10, если B2 больше, чем C2, в противном случае значение в ячейке B1 умножается на 5.
Теперь давайте проанализируем, что делает вторая формула с логическими операторами больше чем и меньше или равно. Полезно знать, что в математических вычислениях Excel приравнивает логическое значение ИСТИНА к 1, а ЛОЖЬ к 0. Имея это в виду, давайте посмотрим, что на самом деле возвращает каждое из логических выражений.
Если значение в ячейке B2 больше значения в C2, то выражение B2>C2 равно ИСТИННО и, следовательно, равно 1. С другой стороны, B2 C2 наша формула претерпевает следующее преобразование:
Поскольку любое число, умноженное на ноль, дает ноль, мы можем отбросить вторую часть формулы после знака плюс. И поскольку любое число, умноженное на 1, является этим числом, наша сложная формула превращается в простую =B2*10, которая возвращает произведение умножения B2 на 10, что и делает приведенная выше формула ЕСЛИ : )
Очевидно, что если значение в ячейке B2 меньше, чем в C2, то выражение B2>C2 оценивается как FALSE (0) и B2 =A1
Больше (зеленый): =A1>20
Подробные пошаговые инструкции и примеры правил можно найти в следующих статьях:
Как видите, использование логических операторов в Excel интуитивно понятно и просто. В следующей статье мы изучим азы логических функций Excel, которые позволяют выполнять более одного сравнения в формуле. Пожалуйста, следите за обновлениями и спасибо за чтение!
Используйте операторы сравнения в Excel, чтобы проверить, равны ли два значения друг другу, больше ли одно значение другого, меньше ли одно значение другого и т. д.
Равно
Операция равенства (=) возвращает значение TRUE, если два значения равны друг другу.
<р>1. Например, взгляните на формулу в ячейке C1 ниже.
Объяснение: формула возвращает значение ИСТИНА, поскольку значение в ячейке A1 равно значению в ячейке B1. Всегда начинайте формулу со знака равенства (=).
<р>2. В приведенной ниже функции ЕСЛИ используется оператор равенства.
Объяснение: если два значения (числа или текстовые строки) равны друг другу, функция ЕСЛИ возвращает Да, в противном случае она возвращает Нет.
Больше, чем
Операция "больше" (>) возвращает TRUE, если первое значение больше второго.
<р>1. Например, взгляните на формулу в ячейке C1 ниже.
Объяснение: формула возвращает значение TRUE, поскольку значение в ячейке A1 больше, чем значение в ячейке B1.
<р>2. В приведенной ниже функции ИЛИ используется оператор «больше».
Объяснение: эта функция ИЛИ возвращает ИСТИНА, если хотя бы одно значение больше 50, в противном случае она возвращает ЛОЖЬ.
Меньше
Операция "меньше" (=) возвращает значение TRUE, если первое значение больше или равно второму значению.
<р>1. Например, взгляните на формулу в ячейке C1 ниже.
Объяснение: формула возвращает значение TRUE, поскольку значение в ячейке A1 больше или равно значению в ячейке B1.
<р>2. Функция СЧЁТЕСЛИ ниже использует оператор больше или равно.
Объяснение: эта функция СЧЁТЕСЛИ подсчитывает количество ячеек, которые больше или равны 10.
Меньше или равно
Операция меньше или равно ( ) возвращает значение TRUE, если два значения не равны друг другу.
<р>1. Например, взгляните на формулу в ячейке C1 ниже.
Объяснение: формула возвращает значение ИСТИНА, поскольку значение в ячейке A1 не равно значению в ячейке B1.
<р>2. В приведенной ниже функции ЕСЛИ используется оператор "не равно".
Объяснение: если два значения (числа или текстовые строки) не равны друг другу, функция ЕСЛИ возвращает Нет, в противном случае возвращает Да.
Некоторые вещи в мире конечны. Другие бесконечны, и функция ЕСЛИ кажется одной из таких вещей. В нашем блоге уже есть несколько учебных пособий по Excel IF, и мы по-прежнему каждый день находим новые способы их использования. Сегодня мы рассмотрим, как можно использовать ЕСЛИ вместе с функцией И для одновременной оценки двух или более условий.
Инструкция ЕСЛИ И в Excel
Чтобы построить оператор ЕСЛИ И, вам, очевидно, нужно объединить функции ЕСЛИ и И в одной формуле. Вот как:
В переводе на простой английский формула выглядит следующим образом: ЕСЛИ верно условие 1 И верно условие 2, выполните одно действие, в противном случае сделайте другое.
В качестве примера создадим формулу, которая проверяет, "доставлен" ли элемент B2 и не является ли C2 пустым, и в зависимости от результатов выполняет одно из следующих действий:
- Если оба условия ИСТИННЫ, пометьте заказ как "Закрытый".
- Если какое-либо из условий имеет значение ЛОЖЬ или оба условия имеют значение ЛОЖЬ, возвращается пустая строка ("").
=ЕСЛИ(И(B2="доставлено", C2<>""), "Закрыто", "")
На снимке экрана ниже показана функция ЕСЛИ И в Excel:
Если вы хотите вернуть какое-то значение в случае, если логическая проверка дает FALSE, укажите это значение в аргументе value_if_false. Например:
=ЕСЛИ(И(B2="доставлено", C2<>""), "Закрыто", "Открыто")
Измененная формула выводит "Закрыто", если столбец B "доставлен" и C содержит любую дату (непустую). Во всех остальных случаях возвращается «Открыть»:
Примечание. При использовании формулы ЕСЛИ И в Excel для оценки текстовых условий помните, что строчные и прописные буквы рассматриваются как один и тот же символ. Если вы ищете формулу ЕСЛИ И с учетом регистра, оберните один или несколько аргументов И в функцию ТОЧНОЕ, как это делается в связанном примере.
Теперь, когда вы знаете синтаксис оператора Excel IF AND, позвольте мне показать вам, какие задачи он может решать.
Excel ЕСЛИ: больше И меньше
В предыдущем примере мы проверяли два условия в двух разных ячейках. Но иногда вам может понадобиться запустить два или более тестов в одной и той же ячейке. Типичным примером является проверка того, находится ли значение ячейки между двумя числами. Функция ЕСЛИ И в Excel тоже может это легко сделать!
Допустим, у вас есть данные о продажах в столбце B, и вас просят отметить суммы, превышающие 50 долларов США, но менее 100 долларов США. Для этого вставьте эту формулу в C2, а затем скопируйте ее вниз по столбцу:
Если вам нужно включить граничные значения (50 и 100), используйте оператор меньше или равно (=):
Чтобы обработать некоторые другие граничные значения без изменения формулы, введите минимальное и максимальное значения в две отдельные ячейки и обратитесь к этим ячейкам в формуле. Чтобы формула корректно работала во всех строках, обязательно используйте абсолютные ссылки для граничных ячеек (в нашем случае $F$1 и $F$2):
Используя аналогичную формулу, вы можете проверить, попадает ли дата в указанный диапазон.
Например, давайте отметим даты с 10 сентября 2018 года по 30 сентября 2018 года включительно. Небольшое препятствие заключается в том, что даты не могут быть предоставлены логическим тестам напрямую. Чтобы Excel понимал даты, они должны быть заключены в функцию ДАТАЗНАЧ, например:
Или просто введите даты От и До в две ячейки (в этом примере $F$1 и $F$2) и "вытяните" их из этих ячеек с помощью уже знакомая формула ЕСЛИ И:
ЕСЛИ это И то, то вычислить что-нибудь
Помимо возврата предопределенных значений, функция Excel ЕСЛИ И также может выполнять различные вычисления в зависимости от того, являются ли заданные условия ИСТИНА или ЛОЖЬ.
Чтобы продемонстрировать этот подход, мы будем рассчитывать бонус в размере 5% для "закрытых" продаж на сумму, превышающую или равную 100 долларов США.
Предполагая, что сумма указана в столбце B, а статус заказа — в столбце C, формула выглядит следующим образом:
=ЕСЛИ(И(B2>=100, C2="закрыто"), B2*10%, 0)
Приведенная выше формула присваивает остальным заказам ноль (value_if_false = 0). Если вы готовы давать небольшой стимулирующий бонус, скажем, 3 %, к заказам, которые не соответствуют условиям, включите в аргумент value_if_false соответствующее уравнение:
=ЕСЛИ(И(B2>=100, C2="закрыто"), B2*10%, B2*3%)
Несколько операторов IF AND в Excel
Как вы могли заметить, во всех приведенных выше примерах мы оценили только два критерия. Но ничто не мешает вам включать три и более тестов в ваши формулы ЕСЛИ И, если они соответствуют этим общим ограничениям Excel:
- В Excel 2019–2007 в формуле можно использовать до 255 аргументов, а общая длина формулы не превышает 8 192 символов.
- В Excel 2003 и более ранних версиях допускается не более 30 аргументов общей длиной не более 1024 символов.
В качестве примера нескольких условий И рассмотрим следующие:
- Сумма (B2) должна быть больше или равна 100 долл. США.
- Статус заказа (C2): "Закрыт".
- Дата доставки (D2) находится в текущем месяце.
Теперь нам нужен оператор IF AND, чтобы определить заказы, для которых все 3 условия ИСТИННЫ. И вот оно:
=ЕСЛИ(И(B2>=100, C2="Закрыто", МЕСЯЦ(D2)=МЕСЯЦ(СЕГОДНЯ())), "x", "")
Учитывая, что «текущий месяц» на момент написания был октябрь, формула дает следующие результаты:
Вложенные операторы IF AND
При работе с большими листами может потребоваться проверка нескольких наборов различных критериев И одновременно. Для этого вы берете классическую вложенную формулу ЕСЛИ Excel и расширяете ее логические тесты операторами И, например:
Чтобы получить общее представление, посмотрите на следующий пример.
Предположим, вы хотите оценить свою услугу на основе стоимости доставки и предполагаемого времени доставки (ETD):
- Отлично: стоимость доставки менее 20 долл. США и срок доставки до 3 дней.
- Плохо: стоимость доставки превышает 30 долларов США, а срок доставки – более 5 дней.
- Среднее: что-то среднее
Для этого нужно написать два отдельных оператора IF AND:
ЕСЛИ(И(B2>30, C2>5), "Плохо", …)
…и вложить одно в другое:
=ЕСЛИ(И(B2>30, C2>5), "Плохо", ЕСЛИ(И(B2
Результат будет выглядеть примерно так:
Дополнительные примеры формул можно найти во вложенных операторах IF AND Excel.
Функция ЕСЛИ И с учетом регистра в Excel
Как упоминалось в начале этого руководства, формулы Excel ЕСЛИ И не различают прописные и строчные символы, поскольку функция И по своей природе нечувствительна к регистру.
Если вы работаете с данными, чувствительными к регистру, и хотите оценить условия И с учетом регистра текста, выполните каждую отдельную логическую проверку внутри функции EXACT и вложите эти функции в оператор AND:
В этом примере мы пометим заказы конкретного клиента (например, компании Cyberspace) на сумму, превышающую определенное число, например 100 долларов США.
Как вы можете видеть на снимке экрана ниже, названия некоторых компаний в столбце B выглядят одинаково, если не считать регистр символов, и, тем не менее, это разные компании, поэтому мы должны точно проверить названия. Суммы в столбце C являются числами, и мы запускаем для них обычную проверку "больше чем":
=ЕСЛИ(И(ТОЧНО(B2, "Киберпространство"), C2>100), "x", "")
Чтобы сделать формулу более гибкой, вы можете ввести имя и сумму целевого клиента в две отдельные ячейки и ссылаться на эти ячейки. Только не забудьте заблокировать ссылки на ячейки знаком $ (в нашем случае $G$1 и $G$2), чтобы они не менялись при копировании формулы в другие строки:
Теперь вы можете ввести любое имя и сумму в соответствующих ячейках, и формула отметит соответствующие заказы в вашей таблице:
Формула ЕСЛИ ИЛИ И в Excel
В формулах ЕСЛИ Excel вы не ограничены использованием только одной логической функции. Чтобы проверить различные комбинации нескольких условий, вы можете комбинировать IF, AND, OR и другие функции для запуска необходимых логических тестов. Вот пример формулы ЕСЛИ И ИЛИ, которая проверяет пару условий ИЛИ внутри И. А теперь я покажу вам, как можно выполнить два или более теста И в функции ИЛИ.
Предположим, вы хотите пометить заказы двух клиентов на сумму, превышающую определенное число, например 100 долларов США.
На языке Excel наши условия выражаются следующим образом:
Предполагая, что имена клиентов указаны в столбце B, суммы — в столбце C, два целевых имени — в G1 и G2, а целевая сумма — в G3, вы используете эту формулу, чтобы пометить соответствующие заказы знаком «x»:< /p>
Того же результата можно добиться, используя более компактный синтаксис:
Не уверены, что полностью понимаете логику формулы? Дополнительную информацию можно найти в Excel ЕСЛИ с несколькими условиями И/ИЛИ.
Вот как вы используете функции ЕСЛИ и И вместе в Excel. Чтобы лучше понять формулы, я предлагаю вам загрузить образец книги ЕСЛИ И Excel. Спасибо за внимание и увидимся на следующей неделе!
Чтобы сделать что-то, когда в двух ячейках есть определенные значения (например, это И это), вы можете использовать функцию ЕСЛИ вместе с функцией И для запуска теста. В ячейке D6 формула выглядит так:
который возвращает "x", когда B6 "красный" И C6 "маленький", и возвращает пустую строку (""), если нет.
Чтобы сделать что-то конкретное, когда два или более условий ИСТИННЫ, вы можете использовать функцию ЕСЛИ в сочетании с функцией И для оценки условий с проверкой, затем выполнить одно действие, если результат ИСТИНА, и (необязательно) выполнить возьмите еще один, если результат теста ЛОЖЬ.
В показанном примере мы просто хотим "пометить" записи с красным цветом И маленьким размером. Другими словами, мы хотим проверить ячейки в столбце B на «красный» цвет И проверить ячейки в столбце C, чтобы увидеть, является ли размер «маленьким». Затем, если оба условия ИСТИННЫ, мы помечаем строку знаком «x». В D6 используется следующая формула:
В этой формуле логическим тестом является этот бит:
Этот фрагмент вернет TRUE, только если значение в ячейке B6 "красное" И значение в ячейке C6 "маленькое". Если одно из условий неверно, тест вернет FALSE.
Далее нам нужно выполнить действие, когда результат теста будет ИСТИННЫМ. В данном случае мы делаем это, добавляя «x» в столбец D. Если тест FALSE, мы просто добавляем пустую строку («»). Это приводит к тому, что в столбце D появляется "x", когда оба условия истинны, и ничего не отображается, если нет.
Примечание: если мы не добавим пустую строку при FALSE, формула фактически будет отображать FALSE, если цвет не красный.
Тестирование той же ячейки
В приведенном выше примере мы проверяем две разные ячейки, но ничто не мешает вам запустить два теста в одной и той же ячейке. Например, допустим, вы хотите проверить значения в столбце A, а затем сделать что-то, когда значение не менее 100, но меньше 200. В этом случае вы можете использовать этот код для логического теста:
Читайте также: