Excel. Занятие 2 – ЛОГИКА
Джордж Буль (1815-1864)
(разработал основные положения алгебры логики)
Логические выражения.
Основными формами мышления являются понятия, высказывания и умозаключение. Рассмотрим некоторые основные определения:
Понятие – это форма мышления, фиксирующая основные, существенные признаки объекта.
Высказывания – это форма мышления, в которой что либо утверждается или отрицается о свойствах реальных предметов и отношениях между ними.
Логическое высказывание — это любое повествовательное предложение, в отношении которого можно однозначно сказать, истинно оно или ложно.
Примеры высказываний:
Так, например, предложение «Трава зеленая » следует считать высказыванием, так как оно истинное. Элементарные высказывания: «Петров — врач », «Иванов — шахматист ». Предложение «Лев – птица » тоже высказывание, но оно ложное.
Высказыванием не является, например, предложение «студент 1 курса МПФ».
А предложение: «Иванов И.И. студент 1 курса МПФ » является высказыванием.
Программа Excel позволяет обрабатывать логические высказывания и функции и результаты обработки могут принимать только два значения: ИСТИНА и ЛОЖЬ.
Введем в ячейку А1 логическое выражение: =7>5 и нажмем Еntеr. Excel вернет значение ИСТИНА. Скопируем содержимое А1 в А2 и исправим это выражение в строке формул на выражение: = 3>5. Нажмем Еntеr. Excel вернет значение ЛОЖЬ.
Правые части обеих формул представляют собой высказывания, т.е. утверждения, относительно которых можно заключить, верны они или нет.
В действиях над числами широко используются Операции сравнения:
> больше
>= больше или равно
< меньше
<= меньше или равно
= равно
Обратите внимание, что символ отношения «больше или равно» изображается двумя знаками: > и =. Причина в том, что на клавиатуре нет такого знака.
Задание 1.
Самостоятельно разработать примеры по Операциям сравнения.
Высказывания обычной речи - слова и словосочетания «не», «и», «или», «если..., то», «тогда и только тогда» и другие позволяют из уже заданных высказываний строить новые высказывания. Такие слова и словосочетания называются логическими связками. Высказывания, образованные из других высказываний с помощью логических связок, называются составными.
Так, например, из элементарных высказываний «Петров — врач », «Петров — шахматист » при помощи связки «и » можно получить составное высказывание «Петров врач и шахматист »
При помощи связки «или » из этих же высказываний можно получить составное высказывание «Петров врач или шахматист », понимаемое в алгебре логики как «Петров или врач, или шахматист, или и врач и шахматист одновременно ».
Чтобы обращаться к логическим высказываниям, им назначают имена.
Пусть через А обозначено высказывание «Тимур поедет летом на море», а через В — высказывание «Тимур летом отправится в горы». Тогда составное высказывание «Тимур летом побывает на море, и в горах» можно кратко записать, как: А и В. Здесь «и» — логическая связка. А и В — логические переменные, которые могут принимать только два значения - «истина» или «ложь», обозначаемые, соответственно, «1» и «0».
В Excel также имеются логические операции, которые позволяют строить сложные логические выражения. Эти операции реализованы в Excel как функции. Ниже показан перечень логических операций и соответствующих им функций Excel, расположенных в порядке убывания приоритета.
| Название | Обозначение | Функция Excel |
| Отрицание | НЕ | |
| Конъюнкция | ^ (&) | И |
| Дизъюнкция | V | ИЛИ |
У логических функций аргументы могут принимать только два значения: ИСТИНА и ЛОЖЬ. Поэтому логические функции можно задать таблицей, где перечислены все возможные значения аргументов и соответствующие им значения функций. Такие таблицы называются таблицами истинности.
Таблица для функции НЕ:
| X | НЕ(х) |
| ложь | истина |
| истина | ложь |
Таблица для функций И и ИЛИ имеет вид
| X | У | И(х,у) | ИЛИ (х,у) |
| ложь | ложь | ложь | ложь |
| ложь | истина | ложь | истина |
| истина | ложь | ложь | истина |
| истина | истина | истина | истина |
Функция НЕ может иметь только один аргумент, а функции И и ИЛИ могут иметь два и более аргументов.
Функция ЕСЛИ используется при проверке условий для значений и формул. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Синтаксис
ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)
Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае — значение ЛОЖЬ.
Значение_если_истина — значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Аргумент «значение_если_истина» может быть значением или формулой. Например, 51,2; 37,5; А2+В3*2,71.
Значение_если_ложь — значение, которое возвращается, если «лог_выражение» имеет значение ЛОЖЬ. Аргумент «значение_если_ложь» может быть значенем или формулой. Например, 51,2; 37,5; А2+В3*2,71.
Замечания
В качестве значений аргументов «значение_если_истина» и «значение_если_ложь» можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ. Как правило, логическое выражение служит первым аргументом функции ЕСЛИ:
В Мастере функций ЕСЛИ находится в категории «Логические», так
же как функции И, ИЛИ, НЕ.
Задание 2.
Записать, решить и уяснить задачу
1. В ячейки А1-А10 ввести значения температур больных от 36,6 до 39,3 градусов (с шагом 0,3).
2. Ввести в ячейку В1 формулу, ЕСЛИ (А1>=37.2;”Обратить внимание”; “Норма”).
Если будет выполняться условие А1>=37.2, то в ячейке В1 появится надпись ”Обратить внимание”, в противном случае - “Норма”. Размножить формулу в ячейки столбца до В10 включительно. В ячейке В11 рассчитать общее количество больных, на которых требуется обратить внимание, используя статистическую функцию СЧЕТЕСЛИ.
3. Если температура больного находится в пределах от 38.5 до 39.0, то в ячейке С1 вывести сообщение «Превышено пороговое значение», в противном случае, если А1>39.0 – «Вызов врача». При несовпадении указанных условий вывести сообщение – «Наблюдение». Логическое выражение представлено ниже.
ЕСЛИ (И(А1>=38.5; А1<39.0);”Превышено пороговое значение”; ЕСЛИ(А1>=39.0;” Вызов врача ”; “Наблюдение”).
Здесь в качестве логического выражения применяется функция И, обеспечивающая проверку одновременного выполнения условия: температура находится в пределах от 38.5 до 39.0 градусов. Если условие выполняется – выдаётся сообщение «Вызов врача». В противном случае, если температура выше 39.0 градусов – выдаётся сообщение «Вызов врача».
Размножить формулу в ячейки столбца до С10 включительно. Провести анализ состояния больных. В С11 рассчитать общее количество больных, температура которых превышает 39,2 градуса.
Задание 3.
Ввести таблицу, состоящую из 7-9 строк, Результаты тестирования студентов.
| Фамилия И.О | Дисц.1 | Дисц.2 | … | Результат | Оценка |
| Муравьев А.Н | … | ||||
| Морозов А.Л | … | ||||
| Гусев Н.А. | … |
Выполнить:
1. В ячейках Дисц.1-Дисц.4 проставить баллы для каждого испытуемого от 2 до 5 и отформатировать текст.
2. Вычислить суммарное количество баллов для каждого испытуемого и проставить ему оценку в соответствии со шкалой: меньше 4 - «2», от 4 до 6 — «3», от 7 до 8 — «4», больше 8 — «5».
3. Отсортировать таблицу по убыванию оценки, а в пределах одинаковой оценки — по фамилиям. Вычислить средний балл по каждому тесту.
Расчет среднего значения количества баллов поместить в столбце Результат с применением вложенной функции СРЗНАЧ. Отформатируйте результат: одна цифра после точки.
Расчет оценки провести в столбце Оценка. Ввести формулу для вычисления оценок по указанной в условии шкале. Решение проводится на основе вложенной функции ЕСЛИ.
Постройте нужную формулу самостоятельно. Вы должны реализовать условный оператор:
если сумма < 4 то 2 иначе
если сумма < 6 то 3 иначе
если сумма < 8 то 4 иначе 5
Сортировка. Отсортируем список по убыванию оценок, а внутри одинаковых оценок — по алфавитному возрастанию фамилий. Для этого: выделим какую-нибудь ячейку внутри таблицы (всю таблицу выделять не обязательно). Выберем в меню команду Данные/Сортировка». Появится диалоговое окно. В первом выпадающем списке укажем, что сортировка ведется по полю «Оценка». Рядом выберем переключатель «по убыванию». В следующем выпадающем списке выберем поле для сортировки «Фамилия И.О», переключатель «по возрастанию». После нажатия «ОК» список будет отсортирован.
Диаграмма. Постройте столбиковую диаграмму результата и оценки. Столбики оценок окрасить в разные цвета.
Задание 4.
Задать список группы, с указанием следующих столбцов: № п/п, ФИО, Год рождения, факультет, номер группы, пол.
Заполнить список с помощью Формы (Данные – Форма): указав различные года рождения, два наименования факультета и групп.
С помощью Автофильтра научится производить различного вида выборки по нескольким критериям одновременно.