Цели урока:
- образовательная : усвоение учащимися общего вида и правил выполнения условной функции, обучение применению ее при решении задач; повторение логических выражений и изучение особенностей записи логических выражений в электронных таблицах;
- развивающая : развитие познавательного интереса, логического мышления, речи и внимания учащихся, формирование информационной культуры и потребности приобретения знаний;
- воспитательная : привитие учащимся навыка самостоятельности в работе, воспитание трудолюбия, чувства уважения к науке.
Оборудование: компьютеры, лазерный проектор; программное обеспечение – , индивидуальные карточки с заданиями, подготовленные демонстрационные файлы.
Ход урока
I. Сообщение темы и постановка целей урока
Учитель сообщает тему и цели урока.
II. Актуализация знаний и умений учащихся
I II. Актуализация знаний (создание проблемной ситуации)
Презентация «Условная функция»
Мы хотим с помощью электронных таблиц решить следующую задачу.
Задача (Слайд 2)
Разработать таблицу, содержащую следующую информацию об абитуриентах: фамилия, оценки за экзамены по математике, русскому и иностранному языкам, сумма баллов за три экзамена и информацию о зачислении: если сумма баллов больше или равна проходному баллу и оценка по математике – 4 или 5, то абитуриент зачислен в учебное заведение, в противном случае – нет.
Давайте обсудим, какую информацию надо занести в электронные таблицы, какие задать формулы.
После обсуждения с учениками приходим к виду электронных таблиц, показанному на рис. 3.
Приложении 1
Рис.3
В ячейке С1 будет храниться значение проходного балла – 13. Формула в ячейке E3 вычисляет сумму баллов за три экзамена (воспользуемся встроенной функцией СУММ: =СУММ (B3:D3)). Эту формулу мы скопируем в ячейки E4:E6.
А какую формулу надо задать в ячейке F3?
Создана проблемная ситуация, для преодоления которой ученикам необходимы новые знания.
I V. Ознакомление с новым материалом
Ознакомление с новым материалом ведется с использованием презентации и лазерного проектора.
Слайд 3: общий вид условной функции
ЕСЛИ (<условие>; <выражение 1>; <выражение 2>).
Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной ячейки будет равно значению выражения <выражение 1>, в противном случае – <выражение 2>.
Слайд 4: построение логических выражений (общий случай).
Логические выражения строятся с помощью операций отношения (<, >, <= (меньше или равно), >= (больше или равно), <> (не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения является логическая величина ИСТИНА или ЛОЖЬ.
Слайд 5: особенности записи логических операций в электронных таблицах.
- Записываем имя логической операции (И, ИЛИ, НЕ)
- В круглых скобках перечисляются логические операнды.
ЕСЛИ (ИЛИ(B5<=25; А5>=10); "Да"; "Нет").
Вернемся к решению задачи 1:
В ячейку E3 введём формулу: СУММ (B3:D3) и скопируем эту формулу в ячейки E4:E8.
Условие, записанное с помощью логической операции И , можно расшифровать так: сумма баллов (E3) >= проходному баллу (C1) И оценка за экзамен по математике (B3) > 3. Если условие выполняется, то в клетке F3 будет отображаться текст «зачислен», в противном случае – «не зачислен».
В ячейку F3 надо занести формулу =ЕСЛИ(И (E3>=C1;B3>. Для проходного балла в формуле будет использоваться содержимое ячейки C1. Но так как значение ячейки C1 не меняется (проходной балл является одинаковым и неизменным для всех абитуриентов), то на эту ячейку необходимо сделать абсолютную ссылку. То есть наша формула будет выглядеть так:
ЕСЛИ(И (E3>=$C$1;B3>3); "зачислен"; "не зачислен").
ЕСЛИ(И (E4>=$C$1;B4>3); "зачислен"; "не зачислен")
=ЕСЛИ(И (E5>=$C$1;B5>3); "зачислен"; "не зачислен")
Слайд 6: простой пример с подробным объяснением учителя.
Таблица содержит следующие данные об учениках школы: фамилия, возраст и рост ученика. Кто из учеников может заниматься в баскетбольной секции, если туда принимают детей с ростом не менее 160 см? Возраст не должен превышать 13 лет.
Решение задачи осуществляется в Приложении 2
Решение.
Исходной информацией являются фамилии учеников, их возраст и рост. Готовим таблицу, представленную на рис. 4.
В ячейку D2 заносим формулу для определения, кто из учеников школы может заниматься в секции, а кто – нет:
ЕСЛИ(И(С2>=160; D2<=13) "Да"; "Нет")
Таким образом, если оба условия выполняются (т.е. если рост ученика не менее 160 см и возраст не превышает 13 лет), то в ячейке D2 будет отображаться текст «Да», в противном случае – «Нет».
Рис. 4
Слайд 7: простой пример, объяснить который должны ученики.
6 спортсменов – многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определённое количество очков. Спортсмену присваивается звание мастера, если он набрал в сумме 100 и более очков. Сколько спортсменов получило звание мастера
Решение задачи осуществляется в Приложении 3
Решение.
Готовим таблицу в виде, представленном на рис. 5.
Рис. 5
В ячейки В2:F7 заносим результаты учеников по каждому виду спорта. В ячейку G2 - формулу =СУММ(B2:F2). Далее копируем эту формулу для ячеек GЗ:G7.
В ячейку H2 – формулу =ЕСЛИ(G2>=100;"мастер";"нет звания"). Таким образом, если условие, что общее количество очков больше или равно 100, выполняется для данного спортсмена, то в соответствующей ячейке столбца H будет отображаться текст «мастер», в противном случае – «нет звания».
В ячейку I2 – формулу =ЕСЛИ(H2="мастер";1;0) . Таким образом, если условие, что спортсмен является мастером выполняется, то в соответствующей ячейке столбца I будет отображаться 1, в противном случае - 0.
В ячейку I8 – формулу =СУММ(I2:I7). Таким образом можно подсчитать общее количество спортсменов, имеющих звание «Мастер спорта».
Слайд 8: более сложный пример с подробным объяснением учителя.
Покупатели магазина пользуются 10% скидками, если стоимость покупки превышает k рублей. Составить ведомость, учитывающую скидки: покупатель, стоимость покупки, скидка, стоимость покупки с учётом скидки. Составить таблицу и показать, кто из покупателей сделал покупки со скидкой, стоимость которых превышает k рублей.
Решение задачи осуществляется в Приложении 4
Решение.
Готовим таблицу в виде, представленном на рис. 6.
Рис. 6.
В ячейки A1:B7 заносим соответствующие данные.
В ячейку C2 заносим формулу =B2*0,1 (так как скидка на покупку 10%, то изначальную стоимость покупки необходимо умножить на 0,1).
В ячейку D2 заносим формулу =B2-C2 (так как стоимость мы вычисляем покупки с учётом скидки)
В ячейку E2 заносим формулу =ЕСЛИ(B2>=$B$9;D2;B2). В этой формуле необходимо обратить внимание на абсолютную ссылку на ячейку B9.
При занесении формулы в ячейку E2 мы встретились с ситуацией, когда при вставке встроенной функции ЕСЛИ требуется в качестве значения логического выражения сделать ссылку на ячейку.
V. Этап закрепления знаний
Самостоятельная работа учащихся за компьютерами
Учащимся предлагаются задания трех уровней сложности.
Задача (уровень 1).
Покупатель магазина получает скидку 3%, если у него есть дисконтная карта или если общая стоимость его покупки превышает 5000 рублей. Определить, сколько заплатили покупатели за свои покупки.
Задача (уровень 2).
В доме проживают 10 жильцов. Подсчитать, сколько каждый из них должен платить за электроэнергию и определить суммарную плату для всех жильцов. Известно, что 1 кВт/ч электроэнергии стоит m рублей, а некоторые жильцы имеют 50% скидку при оплате (например пенсионеры).
Задача (уровень 3).
Если количество баллов, полученных при тестировании, не превышает 12, то это соответствует оценке «2»; оценке «3» соответствует количество баллов от 12 до 15; оценке «4» – от 16 до 20; оценке «5» - свыше 20 баллов. Составить ведомость тестирования, содержащую сведения: фамилия, количество баллов, оценка.
VI. Проверка работы учащихся
Демонстрируются заранее подготовленные файлы с результатами решения.
VI I. Подведение итогов урока
Подводятся итоги урока, выставляются оценки с аргументацией.
VIII. Домашнее задание
Каждому ученику выдается карточка, домашнее задание комментируется. Предлагаются задачи двух уровней сложности.
Задача (уровень 1). Каждому пушному зверьку от 1-го до 2-ч месяцев полагается дополнительный стакан молока в день, если его вес меньше 3-х кг. Количество зверьков, возраст и вес каждого известны. Выяснить, сколько литров молока в месяц необходимо для зверофермы. Один стакан молока составляет 0,2 литра.
Задача (уровень 2). Если вес пушного зверька в возрасте от 6-ти до 7-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, возраст и вес каждого известны. Выяснить на сколько килограммов в месяц снизится потребление витаминного концентрата.
В арсенале Excel существует набор логических функций, которые вы можете найти на вкладке Формулы в группе Библиотека функций в выпадающем меню Логические. Все логические функции возвращают ИСТИНА или ЛОЖЬ в зависимости от оценки. Ниже список функций с синтаксисом и кратким описанием.
ЕСЛИ(логическое_выражение; [значение_если_истина]; [значение_если_ложь]) – Проверяет логическое_выражение и возвращает значение_если_истина в ячейку если оно равно ИСТИНА, иначе возвращает значение_если_ложь.
ЕСЛИОШИБКА(значение; значение_если_ошибка) – Возвращает значение аргумента другой логической функции не содержащей ошибку, иначе возвращает значение_если_ошибка.
ЕСНД(значение; значение_если_нд) – Возвращает указанное значение_если_нд, если выражение дает результат #Н/Д; в противном случае возвращает результат выажения.
И(логическое_значение1; [логическое_значение2]; …) – Проверяет логические_значения аргументов и возвращает ИСТИНА, если все они имеют значение ИСТИНА. Если хотя бы одно значение ложно, возвращает ЛОЖЬ.
ИЛИ(логическое_значение1; [логическое_значение2]; …) — Проверяет логические_значения аргументов и возвращает ИСТИНА, если хотя бы одно имеет значение ИСТИНА. Если все значения равны ЛОЖЬ, функция ИЛИ возвращает ЛОЖЬ.
ИСКЛИЛИ(логическое_значение1; [логическое_значение2]; …) – Проверяет логические_значения аргументов и возвращает ИСТИНА, если количество истинных значений четно, в противном случае возвращает ЛОЖЬ
ИСТИНА() – аргументов нет и возвращает значение ИСТИНА.
ЛОЖЬ() – аргументов нет и возвращает значение ЛОЖЬ.
НЕ(логическое_значение) – меняет значение ИСТИНА на ЛОЖЬ, и ЛОЖЬ на ИСТИНА.
Логические выражения и логические значения, используемые в функциях, обычно используют операторы сравнения (=, <, >, <=, >= или <>), которые возвращают ИСТИНА и ЛОЖЬ. Предположим, вы ввели следующую формулу:
И(A1=B1; C1>=500)
В этой формуле Excel определяет, равны ли друг другу значения ячеек A1 и B1. Если равны, возвращает ИСТИНА, если нет – ЛОЖЬ. Затем Excel вычисляет второе логическое значение и возвращает ИСТИНА, если содержимое ячейки С1 больше или равно 500, иначе возвращает ЛОЖЬ.
После оценки логических значений, функция И сравнивает их и возвращает ИСТИНА, если оба аргумента имеют значение ИСТИНА. Однако, если хотя бы один аргумент равен ЛОЖЬ, функция И возвращает ЛОЖЬ.
(часто используемая в программировании) дает Excel первые задатки разумного существа, которое может выбирать на основании полученных данных. В функции ЕСЛИ используется, так называемое, логическое выражение, от результата которого будет зависеть выбор Excel в пользу значение_если_истина или значение_если_ложь . Логическое выражение чаще всего использует операторы сравнения, которое возвращает либо ИСТИНА, либо ЛОЖЬ. Соответственно при логическом выражении равным ИСТИНА, Excel вернет значение, которое было установлено в аргументе значение_если_истина, в противном случае — значение_если_ложь.
Рассмотрим следующую формулу, которая в зависимости от значения в ячейке с условием, определяет по какой формуле выдавать результат:
ЕСЛИ(E5=»Да»;D5+D5*7.5%;D5)
Если значение ячейки E5 равняется Да, значение_если_истина, которое говорит Excel, чтобы он увеличил Значение на 7,5%. Если ячейка E5 будет пустой или будет содержать что-нибудь отличное от Да, функция ЕСЛИ будет использовать аргумент значение_если_ложь, которое вернет значение ячейки D5 без изменений.
Т.е. аргументы значение_если_истина и значение_если_ложь функции ЕСЛИ могут содержать константы или выражения, результаты вычислений которых будут возвращены в ячейку с формулой ЕСЛИ.
Функция И() , английский вариант AND(), проверяет на истинность условия и возвращает ИСТИНА если все условия истинны или ЛОЖЬ если хотя бы одно ложно.
Синтаксис функции
И(логическое_значение1; [логическое_значение2]; ...)
логическое_значение - любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.
Например, =И(A1>100;A2>100)
Т.е. если в обеих
ячейках A1
и A2
содержатся значения больше 100 (т.е. выражение A1>100
- ИСТИНА и
выражение A2>100
- ИСТИНА), то формула вернет ИСТИНА,
а если хотя бы в одной ячейке значение <=100, то формула вернет ЛОЖЬ
.
Другими словами, формула =И(ИСТИНА;ИСТИНА)
вернет ИСТИНА,
а формулы =И(ИСТИНА;ЛОЖЬ)
или =И(ЛОЖЬ;ИСТИНА)
или =И(ЛОЖЬ;ЛОЖЬ)
или =И(ЛОЖЬ;ИСТИНА;ИСТИНА)
вернут ЛОЖЬ.
Функция воспринимает от 1 до 255 проверяемых условий. Понятно, что 1 значение использовать бессмысленно, для этого есть функция ЕСЛИ() . Чаще всего функцией И() на истинность проверяется 2-5 условий.
Совместное использование с функцией ЕСЛИ()
Сама по себе функция И()
имеет ограниченное использование, т.к. она может вернуть только значения ИСТИНА или ЛОЖЬ, чаще всего ее используют вместе с функцией ЕСЛИ()
:
=ЕСЛИ(И(A1>100;A2>100);"Бюджет превышен";"В рамках бюджета")
Т.е. если в обеих ячейках A1 и A2 содержатся значения больше 100, то выводится Бюджет превышен , если хотя бы в одной ячейке значение <=100, то В рамках бюджета .
Сравнение с функцией ИЛИ()
Функция ИЛИ() также может вернуть только значения ИСТИНА или ЛОЖЬ, но, в отличие от И() , она возвращает ЛОЖЬ, только если все ее условия ложны. Чтобы сравнить эти функции составим, так называемую таблицу истинности для И() и ИЛИ() .
Эквивалентность функции И() операции умножения *
В математических вычислениях EXCEL интерпретирует значение ЛОЖЬ как 0, а ИСТИНА как 1. В этом легко убедиться записав формулы =ИСТИНА+0 и =ЛОЖЬ+0
Следствием этого является возможность альтернативной записи формулы =И(A1>100;A2>100)
в виде =(A1>100)*(A2>100)
Значение второй формулы будет =1 (ИСТИНА), только если оба аргумента истинны, т.е. равны 1. Только произведение 2-х единиц даст 1 (ИСТИНА), что совпадает с определением функции И()
.
Эквивалентность функции И()
операции умножения * часто используется в формулах с Условием И, например, для того чтобы сложить только те значения, которые больше 5 И
меньше 10:
=СУММПРОИЗВ((A1:A10>5)*(A1:A10<10)*(A1:A10))
Проверка множества однотипных условий
Предположим, что необходимо проверить все значения в диапазоне A6:A9
на превышение некоторого граничного значения, например 100. Можно, конечно записать формулу
=И(A6>100;A7>100;A8>100;A9>100)
но существует более компактная формула, правда которую нужно ввести как (см. файл примера
):
=И(A6:A9>100)
(для ввода формулы в ячейку вместо ENTER
нужно нажать CTRL+SHIFT+ENTER
)