§ 24. Логические функции и абсолютные адреса

Определения и обозначения

Логическое выражение — условие, результатом проверки которого является одно из двух значений: ИСТИНА или ЛОЖЬ. Например, сравнение A1>=3 проверяет, не меньше ли значение в ячейке A1 трёх. Такие выражения нужны, чтобы таблица могла принимать решение на основе данных.

Функция ЕСЛИ проверяет условие и возвращает одно значение, если оно истинно, и другое — если ложно. Например, по результату проверки оценки она может вывести слово «зачёт» или «незачёт». Функция нужна, когда значение в ячейке должно зависеть от выполнения условия.

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

Абсолютный адрес ячейки — адрес, который при копировании формулы не изменяется; знак $ фиксирует столбец и строку. Например, $F$1 всегда указывает на столбец F и строку 1. Такой адрес нужен, если формулы должны обращаться к одной постоянной ячейке, например к коэффициенту скидки.

Как это устроено

Логическое выражение строят, сравнивая значения при помощи знаков =, <>, <, >, <= и >=. Например, условие B2>10 проверяет, больше ли значение в B2 числа 10. Сравнение превращает данные в ответ ИСТИНА или ЛОЖЬ, поэтому таблица может использовать его для выбора дальнейшего действия.

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

Аргументы ЕСЛИ записывают в установленном порядке: условие, значение при истинности, значение при ложности. Например, формула =ЕСЛИ(A1>=3;«зачёт»;«незачёт») сначала проверяет оценку, а затем выдаёт подходящее слово. Текстовые значения заключают в кавычки, чтобы таблица поняла: это текст для вывода, а не название ячейки или формулы.

При копировании формулы относительные адреса автоматически сдвигаются на то же число строк или столбцов, что и сама формула. Например, ссылка B2 в формуле, скопированной на одну строку вниз, станет B3. Такое изменение полезно: формула продолжает обращаться к данным той же строки, а не повторяет расчёт для прежней строки.

Знак $ в адресе закрепляет столбец, строку или оба элемента, поэтому ссылка не сдвигается в закреплённой части. В абсолютном адресе, например $F$1, закреплены и столбец, и строка. Он нужен, когда множество формул должны использовать одну и ту же ячейку с постоянным значением, тогда как адреса изменяемых данных остаются относительными.

Логическая функция ЕСЛИ и виды адресации

Сначала определите, что должно происходить со ссылкой при копировании формулы. Если она должна следовать за данными, оставьте её относительной; если указывать на одну и ту же ячейку — закрепите обе части знаками $. Смешанный адрес выбирают, когда менять нужно только строку или только столбец.

Разбор задач

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

1. Определяем условие: оценка должна быть не ниже трёх, значит, записываем A1>=3.
2. Выбираем функцию ЕСЛИ, потому что результат зависит от выполнения этого условия.
3. Указываем после условия результат при ИСТИНА — «зачёт», затем при ЛОЖЬ — «незачёт».

Получаем формулу =ЕСЛИ(A1>=3;«зачёт»;«незачёт»). Если в A1 стоит 3 или больше, таблица выведет «зачёт». При меньшей оценке она выведет «незачёт».

В столбце B записано количество товара, а в C — цена одной единицы. Нужно рассчитать стоимость во второй строке и скопировать формулу вниз. Количество и цена для каждой строки свои, поэтому ссылки должны перемещаться вместе с формулой.

1. Стоимость находят умножением количества на цену.
2. Для второй строки берём ячейки B2 и C2, значит, формула будет =B2*C2.
3. При копировании вниз оставляем адреса относительными: на следующей строке они станут B3 и C3.

В ячейке D2 записываем =B2*C2 и копируем формулу вниз. Таблица сама заменяет номера строк в ссылках. Так в каждой строке стоимость рассчитывается по количеству и цене именно этой строки.

В столбце B указано количество изделий, в C — цена, а в F1 — постоянный коэффициент скидки. Нужно рассчитать стоимость со скидкой и скопировать формулу вниз. Адреса количества и цены должны меняться по строкам, но ссылка на коэффициент должна оставаться неизменной.

1. Для стоимости без скидки перемножаем количество и цену: B2*C2.
2. Чтобы применить коэффициент скидки, умножаем стоимость на него: B2*C2*F1.
3. Закрепляем адрес коэффициента полностью, заменяя F1 на $F$1, а ссылки на данные сохраняем относительными.

Формула для второй строки: =B2*C2*$F$1. При копировании вниз B2 и C2 превратятся в B3 и C3, а $F$1 останется прежним. Поэтому каждая строка будет использовать своё количество и цену, но общий коэффициент из одной ячейки.

Типичные ошибки

Первая ошибка — перепутать порядок аргументов ЕСЛИ и поставить результат проверки не на первое место. Так происходит, если воспринимать функцию как обычную фразу и не следить за её устройством. Перед вводом проверьте себя: первым должно идти условие, затем результат при ИСТИНА, последним — при ЛОЖЬ.

Вторая ошибка — забыть кавычки вокруг текстовых результатов. Таблица может принять слово без кавычек за имя или ссылку, потому что не видит, что его нужно вывести как обычный текст. Проверьте каждый текстовый аргумент: «зачёт» и «незачёт» должны быть записаны в кавычках.

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

Четвёртая ошибка — поставить знак $ не перед той частью адреса или закрепить лишь строку либо столбец вместо всей ячейки. Это случается, когда не уточняют, какая часть ссылки должна оставаться постоянной. Для абсолютного адреса поставьте $ и перед буквой столбца, и перед номером строки, например $F$1.

Связь с другими темами

Для работы с ЕСЛИ нужно уметь вводить формулы электронной таблицы, пользоваться адресами ячеек и сравнивать значения. Если эти действия знакомы, останется понять, какое условие требуется проверить и какой результат выбрать для каждого случая. Поэтому новая тема опирается на навыки обычных вычислений и чтения таблиц.

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

Что запомнить

• Логическое выражение проверяет условие и даёт результат ИСТИНА или ЛОЖЬ.
• ЕСЛИ выбирает один из двух результатов в зависимости от условия.
• Текстовые результаты функции ЕСЛИ записывают в кавычках.
• Относительный адрес изменяется при копировании формулы.
• В абсолютном адресе знаки $ закрепляют и столбец, и строку.
• Смешанный адрес закрепляет только строку или только столбец.