AND, OR и вложенные IF
Балл 81 при посещаемости 70% проходит проверку по баллу и не проходит по посещаемости. AND нужны обе; OR достаточно одной.
Два условия сразу: AND и OR
У той же учительницы есть второе правило. Чтобы сдать, ученику нужен балл не меньше 60 и посещаемость не меньше 80%. AND принимает одну или несколько проверок (Excel допускает до 255) и возвращает TRUE, только когда каждая из них даёт TRUE: =AND(B2>=60,C2>=80). OR возвращает TRUE, когда хотя бы одна проверка даёт TRUE, поэтому =OR(B2>=90,C2=100) может отметить для награды ученика, который либо набрал очень много, либо не пропустил ни одного занятия. NOT меняет TRUE на FALSE и обратно, и это подходит правилам со словом «если только не».
Сами по себе AND и OR дают только TRUE или FALSE. Помести их внутрь IF, чтобы превратить это в слова: =IF(AND(B2>=60, C2>=80), "Yes", "No"). Проверки внутри могут ссылаться на разные столбцы, сравнивать текст или использовать вычисления. AND и OR пропускают текст и пустые ячейки в диапазонах, а если проверять совсем нечего, возвращают #VALUE!. Таблица ниже применяет две проверки, балл не меньше 60 и посещаемость не меньше 80, к четырём ученикам.
Прежде чем писать правило, скажи его простыми словами. «Сдал, если балл не меньше 60 и посещаемость не меньше 80» прямо ложится на AND; «сдал, если пройдена любая из проверок» ложится на OR. Правило, которое смешивает оба, например «не меньше 60 и либо посещаемость 80%, либо справка от врача», требует одной функции внутри другой: =AND(B2>=60, OR(C2>=80, D2="note")). Сначала напиши внутреннюю часть. Проверь, что она даёт TRUE и FALSE там, где ты ждёшь, и только потом оборачивай.
| Балл | Посещ., % | AND | OR |
|---|---|---|---|
| 93 | 95 | TRUE | TRUE |
| 81 | 70 | FALSE | TRUE |
| 66 | 88 | TRUE | TRUE |
| 55 | 70 | FALSE | FALSE |
Почему 93 из истории превратилось в D
IF даёт два исхода. Чтобы их было больше, поставь другую IF на место ответа для лжи, и шкала оценок станет такой: =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))). Таблица выполняет первую проверку и переходит к следующей, только если первая не прошла. Именно это и пошло не так в истории. Когда первой стоит B2>=60, 93 проходит самую первую проверку и останавливается на D. Более высокие проверки так и не читаются. Начинай с самого высокого порога и спускайся вниз.
Считай скобки: четырём IF нужны четыре закрывающие скобки в конце. Страница ограничений Excel допускает 64 уровня вложенности функций, но больше трёх или четырёх уровней формулу уже трудно читать и легко сломать. В новых версиях обеих программ есть IFS, где пары «проверка и ответ» идут одна за другой без вложенности, и справка Google об IF ссылается на неё. Для длинной шкалы ещё понятнее обычно таблица поиска из следующего урока, потому что пороги стоят в ячейках, где любой может их увидеть и изменить.
В практикуме ниже столбец оценок использует четыре вложенные IF, а столбец зачёта использует IF с AND. Протяни оба вниз и сравни Ben с Dev. У Ben балл лучше, но он не проходит по посещаемости. Dev ходит на занятия, но набирает меньше 60.
Перетащи, чтобы повернуть. Колёсико или щипок для масштаба.
- Начинать вложенную IF с самого низкого порога. Побеждает первая проверка, давшая TRUE. Если первой стоит B2>=60, любой балл от 60 и выше останавливается на D.
- Писать =IF(B2>=60 AND C2>=80, ...). AND является функцией, которая принимает проверки как аргументы, а не словом между ними. Пиши AND(B2>=60, C2>=80).
- Использовать OR, когда правило требует обоих условий. Ученик с 81 баллом и посещаемостью 70% проходит с OR и не проходит с AND. Произнеси правило вслух: союз «и» в предложении означает AND.
- Сбиваться со счёта закрывающих скобок. Четырём вложенным IF нужны четыре ) в конце. Если одной не хватает, формула не принимается.
- В B2 стоит 81, в C2 стоит 70. Что возвращает =AND(B2>=60,C2>=80)?
- FALSE
- TRUE
- Те же ячейки. Что возвращает =OR(B2>=60,C2>=80)?
- TRUE
- FALSE
- Что возвращает =NOT(5>3)?
- FALSE
- TRUE
- В B2 стоит 93. Что возвращает =IF(B2>=90, "A", IF(B2>=80, "B", "C"))?
- В B2 стоит 85. Та же формула. Что она возвращает?
- В B2 стоит 93. Что возвращает формула с неправильным порядком =IF(B2>=60, "D", IF(B2>=90, "A", "F"))?
- Сколько закрывающих скобок завершает формулу из четырёх вложенных IF и больше ничего?
- Сколько уровней вложенности функций допускает Excel?
- В B2 стоит 66, в C2 стоит 88. Что возвращает =IF(AND(B2>=60, C2>=80), "Yes", "No")?
- Какие баллы лучше всего проверяют формулу «60 и больше сдаёт»?
- 60 и 59.9
- 100 и 0
- 75 и 76
- 30 и 90
- AND: все проверки TRUE. OR: хотя бы одна TRUE. NOT: противоположное.
- Используй их внутри IF, чтобы превратить TRUE или FALSE в слова.
- Вложенная IF: ставь следующую IF на место ответа для лжи, начиная с самого высокого порога.
- Проверяй границы. Для длинных шкал понятнее таблица поиска.
Источники курса (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
Проверено: 2026-10-19