5. Логические функции

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

 

5.1.Функция и()

Функция И принимает значение ИСТИНА когда все ее аргументы - истинны. В случаях, когда один или несколько элементов имеют значение ЛОЖЬ функция И тоже имеет значение ЛОЖЬ. Например, запись =И("сентябрь"; 1; 2+2=4), содержащая три аргумента, будет иметь значение ИСТИНА в сентябре и значение ЛОЖЬ в другие месяцы года, так как два других аргумента этой функции - 1 и 2+2=4 всегда истинны.

Формат: И (логическое_выражение1; логическое_выражение2;....)

 

5.2. Функция или()

Функция ИЛИ принимает значение ИСТИНА, если хотя бы один из ее аргументов имеет значение ИСТИНА. Например, результатом выражения =ИЛИ (х=5; у=7; z>=25) будет ИСТИНА, когда хотя бы один из аргументов х,у,z будет соответствовать заданным отношениям.

Формат: ИЛИ (логическое_выражение1; логическое_выражение2;...)

 

5.3. Функция не()

Функция НЕ просто возвращает противоположное логическое значение аргумента. Например, функция =НЕ (2+2=4) будет всегда иметь значение ЛОЖЬ, а функция = НЕ (0) будет всегда ИСТИНА.

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

 

Таким образом, логические функции служат для выполнения вычислений в зависимости от выполнения некоторого условия. В условиях могут использоваться операции сравнения =, >, <, <>(не равно), >= (больше или равно), <= (меньше или равно).

 

5.4. Функция если()

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

логического выражения,

значение_1 если логическое выражение ИСТИННО

значении_2, если логическое выражение ЛОЖНО.

ЕCЛИ (логическое_выражение; значение_1; значение_2)

Пример: в ячейке A1 набрано число 30000, а в ячейке B1 формула =ЕСЛИ(A1<20000; 12; 15). Результатом будет число 15, т.к. условие не выполняется.

 

Пример:

=ЕСЛИ (И(A1>=2;A1<4);5;-1) - вычисленное значение равно 5 при величине A1 от 2до 4 и равно -1 в противном случае.

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

=ЕСЛИ(СУММ(D1:D6)>0; СУММ(D1:D6); 0)

Результатом выполнения этой функции будет сумма значений содержимого ячеек D1:D10, если эта сумма положительная, и нулю в противном случае, то есть если эта сумма отрицательная или равна нулю.

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

Например,

=ЕСЛИ(В5>100; “Принять”;”Отказать”)

Если содержимое ячейки больше 100, то результатом выполнения функции будет значение “Принять”, в противном случае - ”Отказать”.

Текстовые значения могут быть и в логическом выражении:

=ЕСЛИ(В10=”отлично”;”100%”;”70%”)

 

Вложение функций ЕСЛИ()

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

=ЕСЛИ(В10=25; “Отлично”; ЕСЛИ(И(В10<25;В10>22); “Хорошо”; ЕСЛИ(И(В10<=22;B10>19); “Удовлетворительно”; “Неудовлетворительно”)))

Выполняется функция следующим образом: если число, находящееся в ячейке В10, равно 25, то значением функции будет “Отлично”; иначе – если число, находящееся в ячейке В10 меньше 25, но больше 22, то функция примет значение “Хорошо”, иначе – если В10 меньше или равно 22 и больше 19, функция примет значение “Удовлетворительно”, иначе “Неудовлетворительно” .

Следует иметь в виду, что вложенных функций ЕСЛИ() должно быть на единицу меньше, чем возможных вариантов принимаемых значений.

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

=ЕСЛИ(И(А4>=B2;B4>=B2;C4>=B2;D4>=B2;E4>=B2;F4>=B2); “Да”; “Нет”)

Условие можно записать короче, если в аргументе функции использовать блок ячеек:

=ЕСЛИ(И(А4:F4>=B2); “Да”; “Нет”)

 

Задания по лабораторной работе

 

Задание № 1

Задание 1.1 Форматирование данных.

Используя основные приемы форматирования создать на листе 1 (название листа 'Бюджет командировки', цвет ярлыка - розовый) таблицу “Бюджет командировки”, на основе представленного на рисунке образца.

 

 

Задание 1.2. Условное форматирование и создание таблицы значений функции двух переменных. Построение графика поверхности.

1. 1.     Используя смешанные ссылки, построить на листе 2 (название листа 'Функция f(x,y)', цвет ярлыка - синий) рабочей книге таблицу значений функции двух переменных f(x,y) в виде:

	x1	…	xm
y1	f(x1, y1)	…	f(x1, ym)
…	…	…	…
yn	f(xn, y1)	…	f(xn, ym)

Функцию двух переменных, границы и шаг изменения аргументов взять из таблицы № 1. Диапазоны изменения аргументов x и y совпадают. С помощью функций MS Excel определить: максимальное, среднее, минимальное среди найденных табличных значений функции. C помощью средства «Условное форматирование» среди табличных значений функции красным цветом выделить значения превышающее среднее значение и синим – значения, ниже среднего. На этом же листе текущей рабочей книги построить график поверхности f(x,y).

 

Пример выполнения задания:

Построить таблицу квадратов двухзначных чисел, по принципу , где x и y принимают значения от 1 до 9. (см. рис. 1.)

Рисунок 1. Таблица квадратов двухзначных чисел

Для этого:

1. В столбце A и строке 5 ввели числа от 1 до 9 и выдели эти области желтым цветом;

1. В ячейку B6, используя смешанные ссылки ввели функцию: =($A6*10+B$5)^2 и используя средства автозаполнения получили все значения функции f(x,y)

Замечание: При использовании смешанных ссылок необходимо учитывать:

•      абсолютная ссылка по столбцу – фиксирует только столбец (при копировании изменяется только строка, столбец не изменяется, если копирование происходит в пределах одной строки (в разные столбцы), то адрес ячейки не изменится);

•      абсолютная ссылка по строке – фиксирует только строку (при копировании изменяется только столбец, строка не изменяется, если копирование происходит в пределах одного столбца (в разные строки), то адрес ячейки не изменится).

2. Применяя функции: MAКС(), МИН(), СРЗНАЧ() для полученных табличных значений находим максимальное, среднее, минимальное среди найденных значений функции;

3. С помощью команды Формат - Условное форматирование задаем условия и требуемые форматы (см. рисунок 2).

Рисунок 2. Окно ”Условное форматирование”

4. Для построения графика поверхности выполняются шаги:

•      Выделить найденный диапазон табличных значений;

•      Вызов мастера диаграмм;

•      Выбор типа диаграммы: Поверхность;

•      Просмотр отображаемого результата, и размещение легенды снизу;

•      Для изменения количества цветовых градаций на графике поверхности необходимо вызвать контекстно-зависимое меню для легенды “Формат легенды” и во вкладке Шкала поменять цену основных делений так, чтобы градаций стало 5;

•      Остальные параметры диаграммы установить, как показано на примере.

 

Таблица № 1 Индивидуальные задания

№	Функция f(x,y)	Диапазон изменения аргументов функции	Шаг изменения аргументов hx, hy
1		[1, 6]	0.2
2		[1, 5]	0.25
3		[0, π/2]	π/20
4		[0, π]	π/10
5		[0, π/2]	π/20
6		[π/2, 3π/2]	π/12
7		[0, π]	π/10
8		[1,2]	0.1
9		[-π, π]	π/10
10		[-5,5]	0.25
11		[-5, 5]	0.5
12		[0,1]	0.1
13		[0,1]	0.1
14		[-π, π]	π/8
15		[-1,1]	0.25