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

В арсенале Excel существует набор логических функций, которые вы можете найти на вкладке Формулы в группе Библиотека функций в выпадающем меню Логические. Все логические функции возвращают ИСТИНА или ЛОЖЬ в зависимости от оценки. Ниже список функций с синтаксисом и кратким описанием.

ЕСЛИ(логическое_выражение; [значение_если_истина]; [значение_если_ложь]) – Проверяет логическое_выражение и возвращает значение_если_истина в ячейку если оно равно ИСТИНА, иначе возвращает значение_если_ложь.

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

Для правильной работы функция должна быть написана определенным образом, который называется синтаксисом. Основной синтаксис для функции - знак равенства, имя функции и один или несколько аргументов. Аргументы содержат информацию, которую вы хотите вычислить.

ЕСЛИОШИБКА(значение; значение_если_ошибка) – Возвращает значение аргумента другой логической функции не содержащей ошибку, иначе возвращает значение_если_ошибка.

ЕСНД(значение; значение_если_нд) – Возвращает указанное значение_если_нд, если выражение дает результат #Н/Д; в противном случае возвращает результат выажения.

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

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

Если вы уже знаете имя функции, вы можете легко ввести ее самостоятельно.

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

И(логическое_значение1; [логическое_значение2]; …) – Проверяет логические_значения аргументов и возвращает ИСТИНА, если все они имеют значение ИСТИНА. Если хотя бы одно значение ложно, возвращает ЛОЖЬ.

ИЛИ(логическое_значение1; [логическое_значение2]; …) — Проверяет логические_значения аргументов и возвращает ИСТИНА, если хотя бы одно имеет значение ИСТИНА. Если все значения равны ЛОЖЬ, функция ИЛИ возвращает ЛОЖЬ.

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

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

ИСКЛИЛИ(логическое_значение1; [логическое_значение2]; …) – Проверяет логические_значения аргументов и возвращает ИСТИНА, если количество истинных значений четно, в противном случае возвращает ЛОЖЬ

ИСТИНА() – аргументов нет и возвращает значение ИСТИНА.

ЛОЖЬ() – аргументов нет и возвращает значение ЛОЖЬ.

НЕ(логическое_значение) – меняет значение ИСТИНА на ЛОЖЬ, и ЛОЖЬ на ИСТИНА.

Чтобы вставить функцию из библиотеки функций

Дополнительные функции содержат дополнительные функции по категориям для статистики, проектирования, куба, информации и совместимости.

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

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

Логические выражения и логические значения, используемые в функциях, обычно используют операторы сравнения (=, <, >, <=, >= или <>), которые возвращают ИСТИНА и ЛОЖЬ. Предположим, вы ввели следующую формулу:

И(A1=B1; C1>=500)

Он работает в ситуациях, когда нам нужно сравнить два или более значений друг с другом, чтобы определить, является ли условие истинным. Условия выполняются только для первой строки, где значение равно 1 = 1, а значение равно 2 = 1. Конечно, обе функции могут быть объединены. Предположим, нам нужно выяснить все записи, где.

I V. Ознакомление с новым материалом

Значение 1 = 1 и в то же время Значение 2 = 0 или Значение 2 = 1. . Другими словами, значение 1 всегда должно быть 1, а значение 2 может быть 1 или. Итак, чтобы развеять все сомнения, давайте рассмотрим небольшой пример. Наша задача - проверить, было ли введено значение в двух ячейках.

В этой формуле Excel определяет, равны ли друг другу значения ячеек A1 и B1. Если равны, возвращает ИСТИНА, если нет – ЛОЖЬ. Затем Excel вычисляет второе логическое значение и возвращает ИСТИНА, если содержимое ячейки С1 больше или равно 500, иначе возвращает ЛОЖЬ.

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

Разбив полученные решения на простые множители, получим. После подстановки мы получаем действие: 1 раз 1 или результат равен 1. Пойдем дальше и поставим нашу пробную задачу еще более сложной и построим формулу, которая в дополнение к написанию, было ли условие удовлетворена или нет, также поместит информацию о том, какая ячейка появилась в 0.

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

(часто используемая в программировании) дает Excel первые задатки разумного существа, которое может выбирать на основании полученных данных. В функции ЕСЛИ используется, так называемое, логическое выражение, от результата которого будет зависеть выбор Excel в пользу значение_если_истина или значение_если_ложь . Логическое выражение чаще всего использует операторы сравнения, которое возвращает либо ИСТИНА, либо ЛОЖЬ. Соответственно при логическом выражении равным ИСТИНА, Excel вернет значение, которое было установлено в аргументе значение_если_истина, в противном случае — значение_если_ложь.

Сравнение с функцией ИЛИ()

Примерный лист показан на рисунке ниже. Эти требования необходимы кандидату для перехода на следующий этап. Чтобы облегчить задачу, был создан лист, чтобы помочь в выборе кандидатов. В противном случае сообщение: отбрасывается. Как вы можете видеть, все проверенные строки помещены в кавычки, и размер используемых букв не имеет значения. Предположим, у нас есть список имен и фамилий, импортированных из другой программы, но, к сожалению, перед каждым именем у нас есть добавленная фраза любезности. Наша цель - удалить эту фразу, чтобы осталось только первое и последнее имя.

Рассмотрим следующую формулу, которая в зависимости от значения в ячейке с условием, определяет по какой формуле выдавать результат:

ЕСЛИ(E5=»Да»;D5+D5*7.5%;D5)

Если значение ячейки E5 равняется Да, значение_если_истина, которое говорит Excel, чтобы он увеличил Значение на 7,5%. Если ячейка E5 будет пустой или будет содержать что-нибудь отличное от Да, функция ЕСЛИ будет использовать аргумент значение_если_ложь, которое вернет значение ячейки D5 без изменений.

Таким образом, мы не имеем ничего другого, кроме как разбить эту формулу на простые факторы. Эта функция возвращает самые левые символы из текстового значения. Задача состоит в том, чтобы указать, какая из указанных дат - суббота и воскресенье. Функция возвращает значения от 1 до 7 - значение 1 - воскресенье, значение 2 - понедельник, значение 3 - вторник и т.д. поэтому наша задача - поиск значений, соответствующих субботам и воскресеньям.

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

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

Функция И() , английский вариант AND(), проверяет на истинность условия и возвращает ИСТИНА если все условия истинны или ЛОЖЬ если хотя бы одно ложно.

Проницательный читатель обязательно заметит, что один из приведенных аргументов никогда не будет выполнен. Можно ли добиться того же результата с другой функцией? Формула использует тот факт, что, когда число не меньше или равно 600, оно должно быть больше 600.

Наконец, блок-диаграмма, в которой суммируется используемая формула. Где, конечно, на месте понедельника вы поместите формулу. Вставьте сейчас, с техникой, которую вы видите выше типов 2, 3 и 4 в формуле.

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

И(логическое_значение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 )