Как записать and в excel

Функция

И(

)

, английский вариант AND(),

проверяет на истинность условия и возвращает ИСТИНА если все условия истинны или ЛОЖЬ если хотя бы одно ложно.

Синтаксис функции

И(логическое_значение1; [логическое_значение2]; …)

логическое_значение

— любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.

Например,

=И(A1>100;A2>100)

Т.е. если в

обеих

ячейках

A1

и

A2

содержатся значения больше 100 (т.е. выражение

A1>100

— ИСТИНА

и

выражение

A2>100

— ИСТИНА), то формула вернет

ИСТИНА,

а если хотя бы в одной ячейке значение <=100, то формула вернет

ЛОЖЬ

.

Другими словами, формула

=И(ИСТИНА;ИСТИНА)

вернет ИСТИНА, а формулы

=И(ИСТИНА;ЛОЖЬ)

или

=И(ЛОЖЬ;ИСТИНА)

или

=И(ЛОЖЬ;ЛОЖЬ)

или

=И(ЛОЖЬ;ИСТИНА;ИСТИНА)

вернут ЛОЖЬ.

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

ЕСЛИ()

. Чаще всего функцией

И()

на истинность проверяется 2-5 условий.

Совместное использование с функцией

ЕСЛИ()

Сама по себе функция

И()

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

ЕСЛИ()

:

=ЕСЛИ(И(A1>100;A2>100);»Бюджет превышен»;»В рамках бюджета»)

Т.е. если в

обеих

ячейках

A1

и

A2

содержатся значения больше 100, то выводится

Бюджет превышен

, если хотя бы в одной ячейке значение <=100, то

В рамках бюджета

.

Сравнение с функцией

ИЛИ()

Функция

ИЛИ()

также может вернуть только значения ИСТИНА или ЛОЖЬ, но, в отличие от

И()

, она возвращает ЛОЖЬ, только если все ее условия ложны. Чтобы сравнить эти функции составим, так называемую таблицу истинности для

И()

и

ИЛИ()

.

Эквивалентность функции

И()

операции умножения *

В математических вычислениях EXCEL интерпретирует значение ЛОЖЬ как 0, а ИСТИНА как 1. В этом легко убедиться записав формулы

=ИСТИНА+0

и

=ЛОЖЬ+0

Следствием этого является возможность альтернативной записи формулы

=И(A1>100;A2>100)

в виде

=(A1>100)*(A2>100)

Значение второй формулы будет =1 (ИСТИНА), только если оба аргумента истинны, т.е. равны 1. Только произведение 2-х единиц даст 1 (ИСТИНА), что совпадает с определением функции

И()

.

Эквивалентность функции

И()

операции умножения * часто используется в формулах с Условием И, например, для того чтобы сложить только те значения, которые больше 5

И

меньше 10:

=СУММПРОИЗВ((A1:A10>5)*(A1:A10<10)*(A1:A10))

Проверка множества однотипных условий

Предположим, что необходимо проверить все значения в диапазоне

A6:A9

на превышение некоторого граничного значения, например 100. Можно, конечно записать формулу

=И(A6>100;A7>100;A8>100;A9>100)

но существует более компактная формула, правда которую нужно ввести как

формулу массива

(см.

файл примера

):

=И(A6:A9>100)

(для ввода формулы в ячейку вместо

ENTER

нужно нажать

CTRL+SHIFT+ENTER

)

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

попарное сравнение списков

с помощью

формулы массива

:

=И(A18:A21>B18:B21)

Вместо диапазона с границами можно также использовать

константу массива

:

=И(A18:A21>{9:25:29:39})

Функция И в Excel — это логическая функция, которая требует одновременного выполнения нескольких условий. И возвращает ИСТИНА если все аргументы имеют значение ИСТИНА или ЛОЖЬ если хотябы один из аргументов имеет значение ЛОЖЬ. Чтобы проверить, является ли число в A1 больше нуля и меньше 10, используйте
=И(A1> 0, A1 <10). Функцию И можно использовать в качестве логической проверки внутри функции ЕСЛИ, чтобы избежать дополнительных вложенных ЕСЛИ, и ее можно комбинировать с функцией ИЛИ.

Синтаксис

Функция AND(И) имеет 1 обязательный аргумент и может использоваться для одновременной проверки до 255 условий, передаваемых в качестве аргументов, в Excel 2003 функция может обрабатывать не более 30 аргументов. Каждый аргумент ( логическое_значение1 , логическое_значение2 и т. Д.) Должен быть выражением, возвращающим ИСТИНА или ЛОЖЬ, или значением, которое может быть оценено как ИСТИНА или ЛОЖЬ. Аргументы, предоставляемые функции И, могут быть константами, ссылками на ячейки, массивами или логическими выражениями.

Назначение функции И — оценить более одного логического теста одновременно и вернуть ИСТИНА, только если все результаты ИСТИНА. Например, если B3 содержит число 40, то:

=И(B3 > 10; B3 > 20; B3 < 80) // возвращает ИСТИНА

=И(B3 > 10;B3 > 20;B3 < 35) // возвращает ЛОЖЬ

Функция И будет оценивать все предоставленные значения и возвращать ИСТИНА, только если все значения оцениваются как ИСТИНА. Если какое-либо значение оценивается как ЛОЖЬ, функция И вернет ЛОЖЬ. Примечание. Excel оценит любое число, кроме нуля (0), как ИСТИННОЕ.

Как и функция И функция ИЛИ будет объединять результаты в одном значении. Это означает, что их нельзя использовать в операциях с массивами, которые должны выдавать массив результатов. Чтобы обойти это ограничение, вы можете использовать логическую логику. Для получения дополнительной информации см .:  Формулы массивов с логикой И и ИЛИ.

Примеры использования функции И

Пример 1

В следующей таблице показаны три примера функции И в Excel:

Обратите внимание, что в приведенном выше примере электронной таблицы:

• функция в ячейке Е2 оценивается как ИСТИНА, поскольку ОБА из поставленных условий ИСТИНА;
• функция в ячейке Е3 оценивается как ЛОЖЬ, поскольку третье условие, С3> 22 , ЛОЖЬ;
• функция в ячейке Е4 оценивается как ЛОЖЬ, поскольку ВСЕ предоставленные условия — ЛОЖЬ.

Пример 2

Чтобы проверить, является ли значение в B3 больше 1 и меньше 6, вы можете использовать AND следующим образом:

 (B3 > 1 , B3 < 6 )

Пример 3

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

=ЕСЛИ(И( B3 > 0; B3 < 5 );   "Верно";  "Не верно" )

Эта формула вернет «Верно», только если значение в B3 больше 0 и меньше 5.

Пример 4

Вы можете комбинировать функцию И с функцией ИЛИ. Приведенная ниже формула возвращает ИСТИНА, если B3> 100 и B1 является «выполнено» или «в работе»:

=И(B3>100;ИЛИ(B2="выполнено";B2="в работе"))

Nesting functions in Excel refers to placing one function inside another. The nested function acts as one of the main function’s arguments. The AND, OR, and IF functions are some of Excel’s better known logical functions that are commonly used together.

Build the Excel IF Statement

When using the IF, AND, and OR functions, one or all of the conditions must be true for the function to return a TRUE response. If not, the function returns FALSE as a value.

For the OR function (see row 2 in the image below), if one of these conditions is true, the function returns a value of TRUE. For the AND function (see row 3), all three conditions must be true for the function to returns a value of TRUE.

In the image below, rows 4 to 6 contain formulas where the AND and OR functions are nested inside the IF function.

In this example, three conditions are tested by the formulas in rows 2 and 3:

• Is the value in cell A2 less than 50?
• Is the value in cell A3 not equal to 75?
• Is the value in cell A4 greater than or equal to 100?

Also, in all of the examples, the nested function acts as the IF function’s first argument. This first element is known as the Logical_test argument.

=IF(OR(A2<50,A3<>75,A4>=100),"Data Correct","Data Error")

=IF(AND(A2<50,A3<>75,A4>=100),1000,TODAY())

Change the Formula’s Output

In all formulas in rows 4 to 6, the AND and OR functions are identical to their counterparts in rows 2 and 3 in that they test the data in cells A2 to A4 to see if it meets the required condition.

The IF function is used to control the formula’s output based on what is entered for the function’s second and third arguments. Examples of this output can be text as seen in row 4, a number as seen in row 5, the output from the formula, or a blank cell.

In the case of the IF/AND formula in cell B5, since not all three cells in the range A2 to A4 are true — the value in cell A4 is not greater than or equal to 100 — the AND function returns a FALSE value. The IF function uses this value and returns its Value_if_false argument — the current date supplied by the TODAY function.

On the other hand, the IF/OR formula in row four returns the text statement Data Correct for one of two reasons:

1. The OR value has returned a TRUE value — the value in cell A3 does not equal 75.

2. The IF function then used this result to return its Value_if_false argument: Data Correct.

Use the IF Statement in Excel

The next steps cover how to enter the IF/OR formula located in cell B4 from the example. These same steps can be used to enter any of the IF formulas in these examples.

The steps used to enter the IF/OR formula in cell B4 are as follows:

1. Select cell B4 to make it the active cell.

2. On the ribbon, go to Formulas.

3. Select Logical to open the function dropdown list.

4. Choose IF in the list to open the Function Arguments dialog box.

5. Place the cursor in the Logical_test text box.

6. Enter the complete OR function:

   OR(A2<50,A3<>75,A4>=100)
   

7. Place the cursor in the Value_if_true text box.

8. Type Data Correct.

9. Place the cursor in the Value_if_false text box.

10. Type Data Error.

11. Select OK to complete the function.

12. The formula displays the Value_if_true argument of Data Correct.

13. Select cell B4 to see the complete function in the formula bar above the worksheet.

Thanks for letting us know!

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

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

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

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

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

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

• логический_тест: это условие для функции для проверки.
• value_if_true: действие, которое выполняется, если условие выполнено или является истинным.
• value_if_false: действие, которое нужно выполнить, если условие не выполнено или имеет значение false.

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

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

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

Пример функции 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. Есть много способов сделать это.

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

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

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

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

  = ЕСЛИ (В2 <СЕГОДНЯ (), "Просроченные", В2-СЕГОДНЯ ()) 

Что такое вложенные формулы IF?

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

Одна функция IF способна выполнять два действия ( value_if_true и value_if_false ). Но если мы вставим (или вложим) другую функцию IF в раздел value_if_false , то мы можем выполнить другое действие.

Возьмите этот пример, где мы хотим отобразить слово «Отлично», если значение в ячейке B2 больше или равно 90, отобразить «Хорошо», если значение больше или равно 75, и отобразить «Плохо», если что-либо еще ,

  = ЕСЛИ (В2> = 90, "Отлично", ЕСЛИ (В2> = 75, "Хорошо", "Плохо")) 

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

Обратите внимание на две закрывающие скобки в конце формулы — по одной для каждой функции 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 возвращает:

• ИСТИНА, если любое условие оценивается как ИСТИНА.
• FALSE, если оба условия TRUE или ни одно из условий TRUE.

Это отличается от функции ИЛИ, потому что она вернула бы ИСТИНА, если оба условия были ИСТИНА.

Эта функция становится немного более запутанной, когда добавляется больше условий. Затем функция XOR возвращает:

• TRUE, если нечетное число условий возвращает TRUE.
• ЛОЖЬ, если четное число условий приводит к ИСТИНА, или если все условия ЛОЖЬ.

Давайте посмотрим на простой пример функции XOR.

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

Но если они продают 3000 фунтов или более в любой половине, мы хотим присвоить им Серебряный статус. Если они не продают 3000 и более фунтов стерлингов в обоих случаях, то ничего.

Функция XOR идеально подходит для этой логики. Приведенная ниже формула вводится в столбец E и показывает функцию XOR с IF для отображения «Да» или «Нет», только если выполняется любое из условий.

  = IF (XOR (В2> = 3000, С2> = 3000), "Да", "Нет") 

Функция НЕ

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

Функция NOT меняет значение своего аргумента. Так что, если логическое значение ИСТИНА, тогда оно возвращает ЛОЖЬ. И если логическое значение ЛОЖЬ, оно вернет ИСТИНА.

Это будет легче объяснить на некоторых примерах.

Структура функции НЕ имеет вид;

  = НЕ (логическое) 

НЕ Функциональный Пример 1

В этом примере представьте, что у нас есть головной офис в Лондоне, а затем много других региональных сайтов. Мы хотим отобразить слово «Да», если на сайте есть что-то, кроме Лондона, и «Нет», если это Лондон.

Функция NOT была вложена в логический тест функции IF ниже, чтобы сторнировать ИСТИННЫЙ результат.

  = ЕСЛИ (НЕ (B2 = "London"), "Да", "Нет") 

Это также может быть достигнуто с помощью логического оператора NOT <>. Ниже приведен пример.

  = ЕСЛИ (В2 <> "Лондон", "Да", "Нет") 

НЕ Функциональный Пример 2

Функция NOT полезна при работе с информационными функциями в Excel. Это группа функций в Excel, которые что-то проверяют и возвращают TRUE, если проверка прошла успешно, и FALSE, если это не так.

Например, функция ISTEXT проверит, содержит ли ячейка текст, и вернет TRUE, если она есть, и FALSE, если нет. Функция NOT полезна, потому что она может отменить результат этих функций.

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

Функция ISTEXT используется для проверки наличия текста. Это возвращает TRUE, если текст есть, поэтому функция NOT переворачивает это на FALSE. И если ИФ выполняет свой расчет.

  = ЕСЛИ (НЕ (ISTEXT (В2)), В2 * 5%, 0) 

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

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

Перейти к содержанию

=И(первое_условие; [второе_условие];...)

=ЕСЛИ(И(A1>0;A1<100); "Да"; "Нет")