В папке «Лабораторная работа № 9» должны присутствовать файлы:
Ø Задание 1.xclx.
Ø Задание 2.xclx.
Ø Задание 3.xclx.
Лабораторная работа № 9
ЛОГИЧЕСКИЕ ФОРМУЛЫ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ
Цель работы – изучение возможностей применения в электронных таблицах логических данных и выражений.
К логическим функциям относятся такие функции, которые позволяют выбрать то или иное решение в зависимости от того, выполняется или нет одно или несколько условий.
С помощью этих функций в Excel можно предпринять одно действие, если условие выполняется, и другое — если условие не выполняется.
Под условием в Excel понимается следующая запись:
выражение # 1 условный оператор выражение # 2
К условным операторам относятся:
< — меньше, чем, например В1 < С4;
<= — меньше или равно, например В1<=С4;
> —больше, чем, например В1>С4;
>= — больше или равно, например В1>=С4;
= — равно, например В1=С4;
<> — неравно, например В 1<>С4.
Логические функции электронных таблиц предназначены для проверки истинности логических высказываний или построения таблиц истинности логических операций.
Рис. 9.1. Окно вставки логических функций
Аргументами логических функций являются логические значения ИСТИНА и ЛОЖЬ. Логические значения, в свою очередь могут быть получены как результат определения логических выражений. Например, для логического выражения 10>5 результатом будет логическое выражение ИСТИНА, а для логического выражении А1<А2 (где в ячейке А1 хранится число 10, а в ячейке А2 – число 5) – значение ЛОЖЬ.
ПРОСТАЯ ФУНКЦИЯ ЕСЛИ
Формат записи: =ЕСЛИ (условие; выражение В; выражение С)
Эта запись означает:
1) если условие выполняется, то происходит действие, определенное в выражении В;
2) если условие не выполняется, то происходит действие, определенное в выражении С.
Выражениями В и С могут быть числовое выражение, функция, ссылка на клетку таблицы или ее имя, заключенный в кавычки текст.
Пример. Пусть у ряда работников имеется задолженность по потребительскому кредиту, которая отражена в диапазоне СЗ:С7 (рис.10.2). Нужно найти в списке таких работников и удержать с них в счет погашения кредита 10% от начисленной им суммы.
| А | В | С | D | Е | |
| 1 | Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | |
| 2 | Потреби-тельский | Жилищ-ный | |||
| 3 | Иванов | 4000 | 2400 | =ЕСЛИ(С3>0;В3*0,1;””) | |
| 4 | Петров | 1600 | =ЕСЛИ(С4>0;В4*0,1;””) | ||
| 5 | Кузьмин | 4500 | 3000 | 25000 | =ЕСЛИ(С5>0;В5*0,1;””) |
| 6 | Сухов | 2800 | 6000 | =ЕСЛИ(С6>0;В6*0,1;””) | |
| 7 | Николаев | 3200 | 18000 | =ЕСЛИ(С7>0;В7*0,1;””) | |
Рис. 9.2.Пример простой логической функции ЕСЛИ
Для нашего примера логическая функция будет иметь следующий ВИД:
=ЕСЛИ (С3>0; ВЗ *0,1; " ").
Данная логическая функция означает следующее: если задолженность по потребительскому кредиту больше нуля, то необходимо удержать 10% с начисленной суммы, в противном случае необходимо вывести пробелы.
Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7.
По диапазону СЗ:С7 отыскиваются работники, у которых есть задолженность по потребительскому кредиту, а в диапазоне ЕЗ:Е7 указывается размер удержанной суммы. В случаях когда задолженности по потребительскому кредиту нет, напротив фамилий соответствующих работников в диапазоне ЕЗ:Е7 выводятся пробелы.
В результате в диапазоне ЕЗ:Е7 (рис.10.3) получим размер удержанной суммы с работников, у которых имеются задолженности по потребительскому кредиту.
| А | В | С | D | Е | |
| 1 | Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | |
| 2 | Потребительский | Жилищный | |||
| 3 | Иванов | 4000 | 2400 | 400 | |
| 4 | Петров | 1600 | |||
| 5 | Кузьмин | 4500 | 3000 | 25000 | 450 |
| 6 | Сухов | 2800 | 6000 | 280 | |
| 7 | Николаев | 3200 | 18000 | ||
Рис.9 3. Результат выполнения простой логической функции ЕСЛИ
ЛОГИЧЕСКАЯ ФУНКЦИЯ И
Логическая функция И обеспечивает проверку одновременного выполнения связанных этой функцией условий. При выполнении всех условий функция принимает значение ИСТИНА, а если хотя бы одно из условий не выполняется, функция принимает значение ЛОЖЬ.
Формат записи: И (условие #1; условие #2)
Результат является истинным, если два условия истинны. В противном случае результат ложен:
| Условие #1 | Условие #2 | Результат |
| Истина | Истина | Истина |
| Истина | Ложь | Ложь |
| Ложь | Истина | Ложь |
| Ложь | Ложь | Ложь |
Функция И входит в состав функции ЕСЛИ, которая в этом случае имеет следующий формат:
=ЕСЛИ(И(усл.#1;усл.#2); выражение В; выражение С)
Пример. Найти работников, у которых одновременно имеются задолженности по потребительскому кредиту и кредиту на жилищное строительство, и удержать от начисленной им суммы 20% (рис.9.4)
| А | В | С | D | Е | |
| 1 | Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | |
| 2 | Потребит | Жилищн. | |||
| 3 | Иванов | 4000 | 2400 | =ЕСЛИ(И(С3>0;D3>0); В3*0,2;””) | |
| 4 | Петров | 1600 | =ЕСЛИ(И(С4>0;D4>0); В4*0,2;””) | ||
| 5 | Кузьмин | 4500 | 3000 | 25000 | =ЕСЛИ(И(С5>0;D5>0); В5*0,2;””) |
| 6 | Сухов | 2800 | 6000 | =ЕСЛИ(И(С6>0;D6>0); В6*0,2;””) | |
| 7 | Николаев | 3200 | 18000 | =ЕСЛИ(И(С7>0;D7>0); В7*0,2;””) | |
Рис. 9.4. Логическая функция И в сочетании с функцией ЕСЛИ
Для нашего примера логическая функция будет иметь следующий вид:
=ЕСЛИ (И (C 3>0; D 3>0); В3*0,2; " ")
Данная логическая функция означает следующее: если одновременно задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 20% с начисленной суммы, в противном случае необходимо вывести пробелы.
Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7.
В столбцах С и D (рис. 9.5) будет найден только один работник, у которого есть задолженность по двум видам кредита. В столбце Е у него будет удержано 20% от начисленной суммы. Напротив остальных работников в столбце Е будут выведены пробелы.
| А | В | С | D | Е | |
| 1 | Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | |
| 2 | Потребит. | Жилищн. | |||
| 3 | Иванов | 4000 | 2400 | ||
| 4 | Петров | 1600 | |||
| 5 | Кузьмин | 4500 | 3000 | 25000 | 900 |
| 6 | Сухов | 2800 | 6000 | ||
| 7 | Николаев | 3200 | 18000 | ||
Рис.9.5. Результат выполнения логической функции И в сочетании с функцией ЕСЛИ
ЛОГИЧЕСКАЯ ФУНКЦИЯ ИЛИ
Логическая функция ИЛИ обеспечивает проверку выполнения хотя бы одного условия, из всех условий связанных этой функцией. В этой ситуации функция принимает значение ИСТИНА, а если ни одно условие не выполняется, функция принимает значение ЛОЖЬ.
Данная функция истинна, если истинно хотя бы одно из двух входящих в нее условий. Формат записи: = ИЛИ (условие #1; условие #2)
Лишь в случае, когда оба условия ложны, функция ИЛИ также ложна:
| Условие #1 | Условие #2 | Результат |
| Истина | Истина | Истина |
| Истина | Ложь | Истина |
| Ложь | Истина | Истина |
| Ложь | Ложь | Ложь |
Данная функция используется вместе с логической функцией ЕСЛИ, которая в этом случае имеет следующий формат:
=ЕСЛИ(ИЛИ(усл.#1;усл.#2); выражение В; выражение С)
Пример. Найти работников, у которых имеется задолженность либо по потребительскому кредиту, либо по кредиту на жилищное строительство, либо по обоим видам кредита сразу, и удержать с них в счет погашения кредита 10% от начисленной им суммы (рис. 9.6).
| А | В | С | D | Е | |
| 1 | Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | |
| 2 | Потребит. | Жилищн. | |||
| 3 | Иванов | 4000 | 2400 | =ЕСЛИ(ИЛИ(С3>0;D3>0); В3*0,1;””) | |
| 4 | Петров | 1600 | =ЕСЛИ(ИЛИ(С4>0;D4>0); В4*0,1;””) | ||
| 5 | Кузьмин | 4500 | 3000 | 25000 | =ЕСЛИ(ИЛИ(С5>0;D5>0); В5*0,1;””) |
| 6 | Сухов | 2800 | 6000 | =ЕСЛИ(ИЛИ(С6>0;D6>0); В6*0,1;””) | |
| 7 | Николаев | 3200 | 18000 | =ЕСЛИ(ИЛИ(С7>0;D7>0); В7*0,1;””) | |
Рис. 9.6. Логическая функция ИЛИ в сочетании с функцией ЕСЛИ
Логическая функция в нашем примере будет иметь вид:
=ЕСЛИ (ИЛИ (С3>0; D 3>0); В3*0,1; " ")
Данная логическая функция означает следующее: если одновременно или по отдельности задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 10% с начисленной суммы, в противном случае необходимо вывести пробелы.
Запишем ее в ячейку ЕЗ, а затем скопируем в ячейки Е4:Е7.
В столбцах С и D (рис. 9.7) будут найдены работники, у которых есть задолженность хотя бы по одному виду кредита, и в столбце Е с них будет удержано 10% от начисленной им суммы. Один работник не имеет задолженности по кредиту, и в столбце Е напротив его фамилии будут выведены пробелы.
| А | В | С | D | Е | |
| 1 | Ф.И.О. | Начислено, тыс. руб. | Задолженность по кредитам | Удержано, тыс. руб. | |
| 2 | Потребит. | Жилищн. | |||
| 3 | Иванов | 4000 | 2400 | 400 | |
| 4 | Петров | 1600 | |||
| 5 | Кузьмин | 4500 | 3000 | 25000 | 450 |
| 6 | Сухов | 2800 | 6000 | 280 | |
| 7 | Николаев | 3200 | 18000 | 320 | |
Рис.9.7. Результат выполнения логической функции ИЛИ в сочетании с функцией ЕСЛИ
