Лекция по Excel №2

Лекция по Excel №2 «Логические функции»

1.Что такое условие? 2

2.Функция ЕСЛИ 3

3.Логические функции И, ИЛИ, НЕ 4

4.Вложенные логические функции 5

1. Что такое условие?

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

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

К задачам с нелинейным алгоритмом относятся задачи: с условными разветвлениями, с циклическими повторениями, рекурсивные алгоритмы.

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

С условными разветвлениями мы встречаемся на каждом шагу:

• Малыш, если ты съешь кашу, то мы пойдем гулять, иначе – нет;

• Если ты закончишь четверть без троек, то я куплю тебе велосипед, иначе – нет;

• Если ваш рейтинг ниже 30%, то вы автоматически получаете по предмету «Информатика» оценку «два»;

• и т.п.

Условие – это логическое выражение, имеющие (как правило) знаки сравнения и возвращающее результат логического типа (ИСТИНА, ЛОЖЬ).

В условиях EXCEL используются следующие знаки сравнения:

=	Равно
<>	неравно
>	Больше
<	меньше
>=	Больше либо равно
<=	Меньше либо равно

! Основным отличием условия то обычного математического выражения является то, что условие при вычислении всегда возвращает только два варианта ответа: либо ИСТИНА, либо ЛОЖЬ.

Сравним обычное математическое выражение X+1и условие X>0:

Возьмем для Х несколько разных значений:

X	X+1	X>0
10	11	истина
5	6	истина
-5	-4	ложь
0	1	ложь

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

*Для общего сведения:

В Excel используется общепринятое правило обработки условий (такое же как, например, во многих языках программирования, таких как Pascal, C, C++, VBA и др.):

Полученный при вычислении условия 0 интерпретируется как ЛОЖЬ, все что не 0 ‑ интерпретируется как ИСТИНА.

Условие, как правило, состоит из трех частей: две сравниваемые величины и какой-либо знак сравнения. В условиях EXCEL могут сравниваться:

• значения ячеек с числовыми константами: A2>5

• значения ячеек: С1<>C2

• значения ячеек с текстовыми или логическими величинами: А2= “студент”, С4=ИСТИНА

• результаты вычисления функций: ОСТАТ(А2;2)=0, СРЗНАЧ(А1:А5)>3,5 (функция ОСТАТ возвращает целочисленный остаток от деления своего первого аргумента на второй. Например, ОСТАТ(5;2) возвратит 1. Чаще всего эта функция используется для анализа четности/нечетности, кратности/некратности числа).

Все текстовые строки в EXCEL заключаются в двойные английские кавычки

2. Функция ЕСЛИ

Используется для выбора одного из двух вариантов действий.

Функция ЕСЛИ проверяет истинность какого-либо условия, и при истинности условия возвращает результат вычисления одного выражения, а при ложности – другого.

Синтаксис:

ЕСЛИ(условие; выражение_если_истина; выражение_если_ложь)

Например,

=ЕСЛИ (А6<25; 0;1)

Эта формула возвратит ноль, если значение ячейки А6 меньше 25, иначе формула возвратит 1.

Пример:

Вычислить значение кусочной функции в заданной точке:

Вышеприведенная запись читается следующим образом: если х>0, то значение y(x) равно sin(x), иначе y(x)=x^2. То есть, на той части числовой прямой, где х>0, функция строится как синусоида, а где х<=0 – как парабола. График такой кусочной функции выглядит следующим образом:

Решение:

Легче всего построить функцию ЕСЛИ, пользуясь мастером по построению функций: Вставка/Функция/Категория: Логические.

В качестве аргументов функции ЕСЛИ могут использоваться:

• Простые математические выражения: =ЕСЛИ (А1<-3; A1+2;A1^3)

• Другие функции: =ЕСЛИ(СУММ (А1:А10)>50;ПРОИЗВЕД (А1:А10);СРЗНАЧ(А1:А10)) – если сумма значений ячеек А1:А10 больше 50, то функция ЕСЛИ возвратит произведение ячеек А1:А10, иначе среднее арифметическое ячеек А1:А10.

• Текстовые константы: =ЕСЛИ (А2<3; “экзамен не сдал”; “сдал”) – если значение ячейки А2 меньше трех, выдается сообщение “экзамен не сдал”, иначе “сдал”.

• Пустые строки: =ЕСЛИ (С8<>0;1/C8; “”) – формула вычисляет значение выражения 1/х, только в том случае, если х не равен 0, иначе возвращает пустую строку. Значение для х находится в ячейке С8.

3. Логические функции И, ИЛИ, НЕ

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

Функции И, ИЛИ используются для анализа нескольких условий и имеют следующий синтаксис:

И (условие1; условие2; условие3; …;условиеN)

ИЛИ (условие1; условие2; условие3; …;условиеN)

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

Функция ИЛИ возвращает ИСТИНУ, если истинно хотя бы одно условие.

Максимальное количество проверяемых условий у этих функций не должно превышать 30.

Пример:

=ЕСЛИ (И (A2>2;B2>2;C2>2); “сессия сдана”; ”сессия не сдана”)

‑ формула проверяет оценки, введенные в диапазон ячеек А2:С2 и, если все они больше 2, выдается сообщение “сессия сдана”.

Необходимо помнить о правиле работы функции И:

Если первое проверяемое условие функции И – ложно, все остальные условия не проверяются.

Функция НЕ имеет один аргумент и следующий синтаксис:

НЕ (логическое_выражение)

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

Функция НЕ возвращает истину, если аргумент функции имеет значение ЛОЖЬ, и наоборот.

Пример:

=ЕСЛИ (НЕ (А2=2);”сдал”;’”не сдал”)

‑ функция возвращает текстовую строку ”сдал”, если значение ячейки А2 не 2.

Пример:

Вычислить значение функции, зависящей от двух аргументов:

‑ значение функции z(x,y) вычисляется как , если ли х или y является четным числом. Во всех остальных случаях .

Для определения четности /нечетности (кратности /некратности) числа используем функцию ОСТАТ.

ОСТАТ (делимое; делитель) – возвращает целочисленный остаток от деления делимого на делитель. И первый, и второй аргумент этой функции, и результат вычисления функции – являются целыми числами.

Решение:

4. Вложенные логические функции

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

1. ВЛОЖЕННЫЕ ФУНКЦИИ ЕСЛИ.

В этих задачах предлагается несколько условий, на каждое из которых имеется свой отклик (свое действие).

Пример:

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

Решение:

Обратите внимание, что в данной задаче очень важен порядок следования условий. Если условия поменять местами, например, так:

=ЕСЛИ(A2>=6; "пешком"; ЕСЛИ(A2>=10; маршрутка"; ЕСЛИ(A2>=50; "такси"; трамвай"))) то, формула вычисляться будет, но ответ будет неверным. Например, при количестве денег равном 46 руб., формула выдаст ответ "пешком", т.к. самое первое анализируемое условие A2>=6 дает при вычислении ИСТИНУ.

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

Третий аргумент функции ЕСЛИ получает управление только тогда, когда условие ложно.

Максимальное число вложений функции ЕСЛИ не должно превышать 7.

2. ВЛОЖЕННЫЕ ФУНКЦИИ И, ИЛИ.

В таких задачах на несколько проверяемых условий имеется один отклик.

Пример:

Выдать премию в размере 500 руб. женщинам, имеющим более 2-х детей.

Решение:

В таких задачах несколько проверяемых условий объединяются с помощью функций И, ИЛИ.

3. КОМБИНАЦИЯ I и II ВАРИАНТОВ.

Пример:

Вычислить значение кусочной функции в заданной точке

В зависимости то того, к какой части числовой прямой принадлежит х, y(x)вычисляется по одной из трех формул.

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

! Запись -5<=A2<=5 является неправильной, т.к. при такой записи анализируется только первое условие -5<=A2, все остальное пропускается.

Решение: