инр. Лабораторная работа 3excel. Лабораторная работа 3 Использование логических функций Microsoft Excel
 Скачать 0.6 Mb.
|
Лабораторная работа 3Использование логических функций Microsoft ExcelЦель работы – научиться использовать в расчетах логические функции. Выполнив эту работу, вы узнаете назначение и возможности встроенных логических фун- кций Excel; научитесь: использовать для расчетов встроенные функции Excel из категории Логические; устанавливать защиту от изменений рабочего листа таблицы. Порядок выполнения работы Ознакомиться с теоретическими сведениями по теме работы. Выполнить практическое задание 5.1, пользуясь методическими указаниями. Самостоятельно выполнить практическое задание 5.2. Ответить на вопросы для самоконтроля. Краткие теоретические сведения Встроенные функции Excel категории «Логические»Результатом вычисления логической функции является одно из двух возможных значений: ИСТИНА (TRUE) или ЛОЖЬ (FALSE). К категории логических функций в Excel относятся функции И, ИЛИ, ЕСЛИ, НЕ. Аргументами этих функций обычно являются условные выражения. Примеры таких выражений приведены в табл. 5.1. Таблица 5.1 Примеры вычисления значений логических выражений
Функция И(логическое выражение1; логическое выражение2; …) принимает значение ИСТИНА, если все выражения в скобках имеют значение ИСТИНА. Например, функция И(А2>50; В2>20) будет иметь значение ИСТИНА, если оба условия в скобках выполняются. Если хотя бы одно условие не выполняется, то функция имеет значение ЛОЖЬ. Функция ИЛИ(логическое выражение1; логическое выражение2; …) принимает значение ИСТИНА, если хотя бы одно выражение в скобках имеет значение ИСТИНА. Например, функция ИЛИ(А2>50; В2>20) будет иметь значение ИСТИНА, если выполнено хотя бы одно из условий: A2>50 или B2>50. Функция имеет значение ЛОЖЬ только в одном случае, когда оба условия не выполняются. Функция ЕСЛИ(логическое выражение; значение_1; значение_2) принимает значение_1, если логическое выражение равно ИСТИНА, принимает значение_2, если логическое выражение равно ЛОЖЬ. Функция НЕ(логическое выражение) меняет значение логического выражения, указанного в скобках, на противоположное. Примеры использования логических функций приведены в табл. 5.2. Таблица 5.2 Примеры использования функций ЕСЛИ, И, ИЛИ, НЕ
Дополнительные учебные материалы по теме работы можно найти в источниках [1]–[6]; [8] из библиографического списка. Практические задания Задание 5.1. Подготовка теста оценки знаний учащихся Создать тест, состоящий из заданий с выбором одного правильного ответа из нескольких предложенных. В тесте должно автоматически подсчитываться количество правильных ответов и выставляться оценка за тест. Окончательный вид таблицы показан на рис. 5.1. Технология выполнения заданияСоздайте новую рабочую книгу (Файл – Создать). Сохраните файл с именем Задание_5.1_Фамилия.xlsx.  Заполните ячейки диапазона A2:B6 в соответствии с рис. 5.1. Отформатируйте указанные ячейки (шрифт, цвет, границы). Разместите варианты ответов напротив соответствующих вопросов в ячейках столбцов F, G, H, I (рис. 5.2).  Рис. 5.2. Таблица с вариантами ответов на вопросы теста В ячейке С3 установите выбор ответа на первый вопрос. Для этого активируйте ячейку и выполните команду Проверка данных на вкладке Данные. В открывшемся окне (рис. 5.3) выберите Тип данных – Список. Установите курсор в поле Источник и выделите мышью в таблице диапазон ячеек F3:I3. Нажмите на кнопку ОК. Около ячейки С3 появится кнопка выбора, нажав на которую, мы увидим заданные варианты ответов для выбора. Установленная проверка ввода данных не позволит в дальнейшем ввести в ячейку какие-либо иные значения, кроме тех, которые перечислены в раскрывающемся списке для данной ячейки.  Рис. 5.3. Формирование списка для выбора ответа на вопрос Аналогично оформите выбор ответа в ячейках С4, C5, С6. Введите в ячейку Е3 формулу для проверки правильности выбранного ответа =ЕСЛИ(C3=””;””;. Смысл этой формулы следующий: если ответ ещё не выбран (C3=””), то ячейка Е3 пустая, в противном случае, если выбран ответ, совпадающий с правильным ответом, находящимся в ячейке G3 (т. е. если C3=G3), в ячейку E3 помещается текст «верно», иначе – текст «неверно!». Самостоятельно введите формулы для проверки правильности выбранного ответа в ячейки Е4, Е5, Е6. Для подсчета количества правильных ответов введите в ячейку С8 функцию =СЧЁТЕСЛИ(E3:E6;”верно”).Эта функция посчитывает количество значений «верно» в диапазоне ячеек Е3:Е6. Результат прохождения теста будет находиться в ячейке С9. Введите в эту ячейку формулу =ЕСЛИ (C9=5;”отлично”;ЕСЛИ(C9=4;”хорошо”;ЕСЛИ(C9=3; ”удовлетворительно”;”неудовлетворительно”))) Эта формула реализует следующее правило определения оценки за тест: если количество правильных ответов равно 4, то оценка – «отлично», если правильных ответов 3 – оценка «хорошо», если правильных ответов 2 – оценка «удовлетворительно», иначе (правильных ответов 1 или 0) – оценка «неудовлетворительно». Чтобы скрыть варианты ответов, выделите столбцы E:I, откройте контекстное меню щелчком правой кнопки в области выделения и выберите Скрыть. Установите защиту таблицы, скрыв формулы и запретив ввод данных во все ячейки таблицы, кроме ячеек выбора ответов: скройте формулы в ячейках C8, C9. Для этого выделите ячейки, откройте диалоговое окно Формат ячеек (вкладка Главная – Формат – Формат ячеек). Выберите вкладку Защита и установите опцию Скрыть формулы. Формулы станут невидимыми только после того, как будет установлена защита листа; ячейки C3:С6, в которые вводятся ответы, должны остаться доступными для ввода ответов после того, как рабочий лист будет защищен от изменений. Для установки этого свойства выполните команду Формат – Ячейки – Защита – сбросьте флажок Защищаемая ячейка; установите защиту на лист, выполнив Формат – Защитить лист. При этом в открывшемся окне нужно разрешить выделение незаблокированных ячеек. В этом же окне можно ввести пароль для отключения защиты листа. 12. Проверьте работоспособность теста. Сохраните документ. Примечание. Назначение созданного теста – в основном обучающее, а не контролирующее, так как правильный ответ легко можно узнать, выполнив перебор всех возможных ответов. Задание 5.2. Создание теста по теме КУММ (для самостоятельного выполнения) Создать тест по теме КУММ, состоящий из 8–10 вопросов, на каждый из которых должно быть не менее трех вариантов ответа. Сохранить тест в файле Тест.xlsx. Для схемы вывода оценки за тест из 10 вопросов, представленной в табл. 5.3, формула определения оценки в ячейке С14 может иметь следующий вид: =ЕСЛИ(C14<4;”неудовлетворительно”;ЕСЛИ(C14<7; ”удовлетворительно”;ЕСЛИ(С14<9;”хорошо”;”отлично”))). Таблица 5.3 Пример схемы вывода оценки за тест
Вопросы и упражнения для самоконтроля Каким может быть результат вычисления значения логической функции? Опишите формат и механизм действия логических функций И, ИЛИ, ЕСЛИ, НЕ. Каким будет результат расчета по формуле =ЕСЛИ(A1=A2; A1-2*A2;A1+A2), если в ячейке A1 записано число 10, а в ячейке A2 – число 20? Каким будет результат расчета по формуле =ЕСЛИ(A1=A2;A12*A2;A1+A2), если в ячейках A1 и A2 записано число 10? Каким будет результат расчета по формуле =ЕСЛИ(И(А1>A2; A2>0); А1+А2; ”красный”), если в ячейке A1 записано число 20, а в ячейке A2 – число 10? Каким будет результат расчета по формуле =ЕСЛИ(И (А1>A2; A2>0); А1+А2; ”красный”), если в ячейке A1 записано число 10, а в ячейке A2 – число 20? Как защитить рабочий лист таблицы от внесения каких-либо изменений? Как определить ячейки, доступные для редактирования их содержимого, на листе, защищенном от изменений? Как скрыть отдельные столбцы, строки, листы электронной таблицы? Как в последующем отобразить скрытые объекты? Как сделать формулы в ячейках недоступными для просмотра? | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||