Условная функция и логические выражения в Excel.
план-конспект урока по информатике и икт (11 класс) на тему

Условная функция и логические выражения в Excel

Масленникова Наталья Алексеевна, учитель информатики

Цели урока:

• образовательная:

• усвоение учащимися общего вида и правил выполнения условной функции;
• обучение применению ее при решении задач;
• повторение логических выражений и изучение особенностей записи логических выражений в электронных таблицах.

• развивающая:

• продолжить работу над формированием общеучебных умений: слушать объяснение учителя, делать необходимые записи в тетради по ходу урока, отвечать на поставленные вопросы, самостоятельно выполнять задания учителя;
• развитие познавательного интереса, любознательности, интереса к предмету “информатика” и учению в целом;
• логического мышления, способности к анализу;
• формирование информационной культуры и потребности приобретения знаний.

• воспитательная:

• воспитание терпеливого и внимательного отношения к выполняемой работе;
• привитие учащимся навыка самостоятельности в работе,
• воспитание трудолюбия, чувства уважения к науке.

Оборудование:

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

База знаний

К этому уроку учащиеся знают:

• Что такое “электронные таблицы”, их виды, функции; назначение. Интерфейс табличного процессора Microsoft Excel.
• Как использовать Мастер функций, изменять формат ячейки, решать задачи.
• Арифметические действия в ЭТ, автосуммирование числовых данных, возможности использования встроенных функций.
• Относительные и абсолютные ссылки.
• Правила копирования формул.

Основные умения:

• Умеют составлять и заполнять таблицы.
• Умеют использовать Мастер функций для ввода данных.
• Умеют изменять формат ячейки, размер, заливку, объединять ячейки.
• Умеют копировать формулы.
• Умеют использовать относительные и абсолютные ссылки.

Ход урока

I. Сообщение темы и постановка целей урока

Учитель сообщает тему и цели урока.

II. Актуализация знаний и умений учащихся

Устный опрос (презентация – Повторение.ppt):

1. Для чего предназначены электронные таблицы?
   Ответ: Для автоматизации расчетов в табличной форме.
2. Что обозначает принцип относительной адресации?
   Ответ. Адреса ячеек, которые используются в формулах, определены относительно места расположения формулы.
3. К чему приводит принцип относительной адресации при перемещений формул?
   Ответ. Этот принцип приводит к тому, что при всяком перемещении формулы в другое место таблицы изменяются имена ячеек в формуле.
4. Если копировать содержимое E20 в E25, то какая формула будет занесена в E25 (рис. 1)?
   
   Рис. 1 
   Ответ. При смещении формулы вниз на одну ячейку всякий раз в именах ячеек номер строки будет увеличиваться на единицу. Таким образом, в ячейке E25 будет формула =C25*D25.
5. Таблица та же (см. рис. 1). Если копировать содержимое E20 в F23, то какая формула будет занесена в F23?
   Ответ. При смещении формулы вправо или влево (вдоль строки) в именах ячеек изменится буквенная часть. Таким образом, в ячейке E23 будет формула =D23*E23.
6. Что обозначает принцип абсолютной адресации и какой символ используется для задания абсолютной адресации?
   Ответ. Адреса ячеек, используемые в формулах, определены абсолютно и при перемещении не изменяются. Для задания абсолютной адресации используется символ $.
7. Дан фрагмент электронной таблицы (рис. 2). Если копировать содержимое D10 в F12, то какая формула будет занесена в F12?
   Рис. 2
   Ответ. =$C$11+D12.

III. Актуализация знаний (создание проблемной ситуации)

Презентация «Условная функция»

Мы хотим с помощью электронных таблиц решить следующую задачу.

Задача (Слайд 2)

Разработать таблицу, содержащую следующую информацию об абитуриентах: фамилия, оценки за экзамены по математике, русскому и иностранному языкам, сумма баллов за три экзамена и информацию о зачислении: если сумма баллов больше или равна проходному баллу и оценка по математике – 4 или 5, то абитуриент зачислен в учебное заведение, в противном случае – нет.

Давайте обсудим, какую информацию надо занести в электронные таблицы, какие задать формулы.

После обсуждения с учениками приходим к виду электронных таблиц, показанному на рис. 3.

Решение задачи осуществляется в Приложении 1

Рис.3

В ячейке С1 будет храниться значение проходного балла – 13. Формула в ячейке E3 вычисляет сумму баллов за три экзамена (воспользуемся встроенной функцией СУММ: =СУММ (B3:D3)). Эту формулу мы скопируем в ячейки E4:E6.

А какую формулу надо задать в ячейке F3?

Создана проблемная ситуация, для преодоления которой ученикам необходимы новые знания.

IV. Ознакомление с новым материалом

Ознакомление с новым материалом ведется с использованием презентации и лазерного проектора.

Слайд 3: общий вид условной функции

ЕСЛИ (<условие>; <выражение 1>; <выражение 2>).

Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной ячейки будет равно значению выражения <выражение 1>, в противном случае – <выражение 2>.

Слайд 4: построение логических выражений (общий случай).

Логические выражения строятся с помощью операций отношения (<, >, <= (меньше или равно), >= (больше или равно), <> (не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения является логическая величина ИСТИНА или ЛОЖЬ.

Слайд 5: особенности записи логических операций в электронных таблицах.

1. Записываем имя логической операции (И, ИЛИ, НЕ)
2. В круглых скобках перечисляются логические операнды.

Пример:

ЕСЛИ (ИЛИ(B5<=25; А5>=10); "Да"; "Нет").

Вернемся к решению задачи 1:

В ячейку E3 введём формулу: СУММ (B3:D3) и скопируем эту формулу в ячейки E4:E8.

Условие, записанное с помощью логической операции И, можно расшифровать так: сумма баллов (E3) >= проходному баллу (C1) И оценка за экзамен по математике (B3) > 3. Если условие выполняется, то в клетке F3 будет отображаться текст «зачислен», в противном случае – «не зачислен».

В ячейку F3 надо занести формулу =ЕСЛИ(И (E3>=C1;B3>3); "зачислен"; "не зачислен"). Для проходного балла в формуле будет использоваться содержимое ячейки C1. Но так как значение ячейки C1 не меняется (проходной балл является одинаковым и неизменным для всех абитуриентов), то на эту ячейку необходимо сделать абсолютную ссылку. То есть наша формула будет выглядеть так:

=ЕСЛИ(И (E3>=$C$1;B3>3); "зачислен"; "не зачислен").

Далее надо скопировать эту формулу в ячейки F4:F6. Формулы в столбике F после копирования будут выглядеть так:

=ЕСЛИ(И (E4>=$C$1;B4>3); "зачислен"; "не зачислен")
=ЕСЛИ(И (E5>=$C$1;B5>3); "зачислен"; "не зачислен")

Рис. 4

V. Этап закрепления знаний

Самостоятельная работа.

Таблица содержит следующие данные об учениках школы: фамилия, возраст и рост ученика. Кто из учеников может заниматься в баскетбольной секции, если туда принимают детей с ростом не менее 160 см? Возраст не должен превышать 13 лет.

Решение задачи осуществляется в Приложении 2

Решение.

Исходной информацией являются фамилии учеников, их возраст и рост. Готовим таблицу, представленную на рис. 4.

В ячейку D2 заносим формулу для определения, кто из учеников школы может заниматься в секции, а кто – нет:

=ЕСЛИ(И(С2>=160; D2<=13) "Да"; "Нет")

Таким образом, если оба условия выполняются (т.е. если рост ученика не менее 160 см и возраст не превышает 13 лет), то в ячейке D2 будет отображаться текст «Да», в противном случае – «Нет».

Рис. 5

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. Проверка работы учащихся

Демонстрируются заранее подготовленные файлы с результатами решения.

VII. Подведение итогов урока

Подводятся итоги урока, выставляются оценки с аргументацией.

VIII. Домашнее задание

Каждому ученику выдается карточка, домашнее задание комментируется. Предлагаются задачи двух уровней сложности.

Задача (уровень 1). Каждому пушному зверьку от 1-го до 2-ч месяцев полагается дополнительный стакан молока в день, если его вес меньше 3-х кг. Количество зверьков, возраст и вес каждого известны. Выяснить, сколько литров молока в месяц необходимо для зверофермы. Один стакан молока составляет 0,2 литра.

Задача (уровень 2). Если вес пушного зверька в возрасте от 6-ти до 7-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, возраст и вес каждого известны. Выяснить на сколько килограммов в месяц снизится потребление витаминного концентрата.