Цель урока: Ознакомится со встроенными функциями табличного процессора MS Excel для подготовки к ОГЭ.
Оборудование: компьютеры, проектор, экран, презентация, приложение 1 – архив с файлами для практикума, приложение 2 – задания для практикума.
Ход урока
1. Организационный момент
2. Проверка выполнения домашнего задания
3. Актуализация знаний, постановка цели урока
Основное назначение табличного процессора состоит в автоматизации расчёта данных. Эта возможность полезна при обработке большого массива данных. Для её реализации в ячейки таблицы вводятсяформулы, в которых могут использоваться не только стандартные арифметические операции, но и встроенные функции. Сегодня мы познакомимся со встроенными функциями табличного процессора и научимся применять их при решении задач.
3. Изучение нового материала
(Слайд 3) Использование любых функций в формулах происходит по одинаковым правилам:
- Каждая функция имеет свое уникальное имя.
- При обращении к функции после ее имени в круглых скобках указывается список аргументов, разделенных точкой с запятой.
- Ввод функции в ячейку начинается со знака равенства, а затем указывается ее имя.
На слайде приведён пример формулы, использующей функцию:
Для вычисления произведения сумм значений блоков ячеек А1:А9 и В7:С10 и сохранения результата в ячейке А10 можно использовать функцию СУММ.
Для этого необходимо ввести в ячейку А10 формулу:
= СУММ(А1:А9) * СУММ(В7:С10).
(Слайд 4) Чтобы вставить функцию в формулу необходимо выполнить следующую последовательность действий:
- выделить ячейку, в которой будет записана формула;
- (ПКМ) на панели инструментов выбрать команду вставить функцию;
- (ПКМ) в открывшемся диалоговом окне выбрать соответствующую категорию (ПКМ);
- выбрать нужную функцию.
Количество различных функций, которые можно использовать в формулах, очень велико. Поэтому, для удобства выбора и обращения к ним, все функции объединены по назначению в группы, называемые категориями: математические, статистические, текстовые, логические, финансовые, функции даты и времени и др. Рассмотрим некоторые из них.
(Слайд 5) Математические функции. Из математических функций наиболее часто используются следующие:
- Функция СУММ – для нахождения суммы значений диапазона;
- Функция ПРОИЗВЕД – для нахождения произведения значений диапазона;
- Функция ABS – для нахождения модуля числа;
- Функция КОРЕНЬ – для нахождения значения квадратного корня;
- Функция СТЕПЕНЬ – для нахождения результата возведения в степень.
(Слайд 6) Статистические функции. Из математических функций наиболее часто используются следующие:
- Функция МАКС – для нахождения максимального из значений диапазона;
- Функция МИН – для нахождения минимального из значений диапазона;
- Функция СРЗНАЧ – для нахождения среднего арифметического значения диапазона;
- Функция СЧЁТ – для подсчёта количества ячеек в диапазоне, который содержит числа.
(Слайд 7) Логические функции. Из математических функций наиболее часто используются следующие:
- Функция ЕСЛИ – для проверки условия, возвращает одно значение, если оно выполняется, и другое значение, если не выполняется;
- Функция И – для проверки истинности всех аргументов;
- Функция ИЛИ – для проверки истинности хотя бы одного аргумента;
- Функция НЕ – для изменения логического значения аргумента;
Остановимся более подробно на логических функциях, использование которых позволяет решать с помощью табличного процессора логические задачи. Для понимания и грамотного применения логических функций вспомним некоторые основные правила формальной логики.
(Слайд 8) В логической функции обязательно используется условие. Условие представляет собой величины или выражения одного типа, связанные одним из знаков отношений (ПКМ): = (равно), <> (не равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно). Например: а > 7; 3 < 5 и т.п. Такие условия называютсяпростыми. Они могут быть составлены из данных, ссылок и выражений.
(ПКМ) Любое условие может принимать одно из двух возможных значений: ИСТИНА (логическая единица) или ЛОЖЬ (логический ноль).
(ПКМ) Для проверки условия в табличном процессоре используется функция ЕСЛИ. Функция ЕСЛИ имеет следующий формат записи:
ЕСЛИ(условие;выражение_если_истина;выражение_если_ложно).
Вычисляя значение этой функции при конкретных значениях, табличный процессор проверяет истинность записанного в ней логического условия и выводит значение выражения выражение_если_истина, если проверяемое условие истинно, или значение выражения выражение_если_ложно, если условие ложно.
Сложным, или составным, условием называют несколько простых условий, связанных с помощью логических операций: И (логическое умножение), ИЛИ (логическое сложение) и др.
(ПКМ) Функции И и ИЛИ имеют следующий формат записи:
И(условие1;условие2;...)
ИЛИ(условие1 ;условие2;...)
Для вычисления результата сложного условия необходимо знать, как определяется результат соответствующей логической операции (И, ИЛИ и др.). Ответ на этот вопрос дает таблица истинности. (ПКМ)
Рассмотрим пример использования логической функции ЕСЛИ, в которую входит сложное логическое условие с функцией И.
(Слайд 9) Предположим, что электронная таблица используется для хранения и обработки среднего балла по школьному аттестату зрелости и оценок по результатам вступительных экзаменов в вуз. Пусть в ячейке А1 указан средний балл по аттестату, а в диапазоне А2:А5 хранятся экзаменационные оценки по четырем предметам, полученные на вступительных экзаменах. В ячейке А6 вычисляется сумма вступительных баллов ученика.
Требуется сравнить полученный суммарный балл с проходным баллом, который хранится в ячейке В1. В ячейке А7 должен быть выведен результат сравнения:
- если условие (суммарный балл >= проходному баллу И средний балл по аттестату >4), то выводится текст из ячейки С1: «Поздравляем, вы успешно сдали экзамены и приняты»;
- если условие не выполнено, то выводится текст из ячейки С2: «Сожалеем, но вы не прошли по конкурсу».
Для решения поставленной задачи в ячейку А7 необходимо ввести логическую функцию ЕСЛИ следующего вида (ПКМ):
=ЕСЛИ(И(А6>=В1;А1>4);С1;С2)
Если оба простых условия (А6>=В1 и А1>4) истинны, то в соответствии с таблицей истинности будет истинно и проверяемое условие в функции ЕСЛИ, и в ячейке А7 будет выведен текст из ячейки С1. Иначе (то есть, если ложно хотя бы одно из условий, А6>=В1 или А1>4) в ячейке А7 появится текст из ячейки С2.
(Слайд 10) Некоторые функции в табличном процессоре имеют разновидности. Рассмотрим их. Очень часто в задачах на обработку большого массива данных нужно определить количество записей. Например, количество учеников, количество городов, количество продуктов и т.д. Для ответа на этот вопрос можно воспользоваться функцией СЧЕТ (ПКМ). Но общее количество записей можно легко узнать и без этой функции. Обычно в задачах нужно определить не общее количество записей, а количество записей, удовлетворяющих условию. Для этого можно воспользоваться функцией СЧЕТЕСЛИ (ПКМ). Если же условий два и более, то нам поможет функция СЧЕТЕСЛИМН (ПКМ). Такие же разновидности имеют и другие функции (ПКМ): СРЗНАЧ, СУММ.
4. Решение задач
(Слайд 11) Рассмотрим пример использования этих функций. В электронную таблицу занесли данные о калорийности продуктов. На слайде приведены первые пять строк таблицы. В столбце A записан продукт; в столбце B – содержание в нём жиров; в столбце C – содержание белков; в столбце D – содержание углеводов и в столбце Е – калорийность этого продукта. Всего в электронную таблицу были занесены данные по 1000 продуктам. На основании данных, содержащихся в этой таблице, ответьте на два вопроса:
1. Сколько продуктов в таблице содержат меньше 50 г углеводов и меньше 50 г белков? Запишите число, обозначающее количество этих продуктов, в ячейку H2 таблицы.
2. Какова средняя калорийность продуктов с содержанием жиров менее 1 г? Запишите значение в ячейку H3 таблицы с точностью не менее двух знаков после запятой.
(Слайд 12) Существует несколько способов решения этой задачи.
1 способ (логические функции):
1) В ячейку F2 запишем формулу =ЕСЛИ(И(D2<50;C2<50);1;0)
Скопируем формулу во все ячейки диапазона F3:F1001.
В ячейку H2 запишем формулу =СУММ(F2:F1001)
2) В ячейку H3 запишем формулу
=СУММЕСЛИ(B2:B1001; ″<1″;E2:E1001)/СЧЁТЕСЛИ(B2:B1001;″<1″)
2 способ (статистические функции):
1) В ячейку H2 запишем формулу =СЧЕТЕСЛИМН(D:D;”<50”;C:C;”<50”)
2) В ячейку H3 запишем формулу =СРЗНАЧЕСЛИ(B:B;”<1”;E:E)
(Слайд 13) Обратите внимание на вторую половину вопроса №2: «Запишите значение в ячейку H3 таблицы с точностью не менее двух знаков после запятой». Чтобы изменить количество знаков после запятой нужно выполнить следующую последовательность действий:
- выделить нужную ячейку (ПКМ);
- нажать правую клавишу мыши (ПКМ);
- в контекстном меню выбрать команду «Формат ячеек…» (ПКМ);
- в диалоговом окне выбрать «Числовой» формат;
- с помощью стрелочек или клавиатуры указать нужное количество знаков после запятой (ПКМ).
(Слайд 14) Для закрепления материала рассмотрим следующую задачу. В электронную таблицу занесли данные о тестировании учеников. На слайде приведены первые пять строк таблицы. В столбце А записан округ, в котором учится ученик; в столбце В — фамилия; в столбце С — любимый предмет; в столбце D — тестовый балл. Всего в электронную таблицу были занесены данные по 1000 ученикам. На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
1. Сколько учеников в Восточном округе (В) выбрали в качестве любимого предмета информатику? Ответ на этот вопрос запишите в ячейку Н2 таблицы.
2. Каков средний тестовый балл у учеников Северного округа (С)? Ответ на этот вопрос запишите в ячейку НЗ таблицы с точностью не менее двух знаков после запятой.
Решение задачи (ПКМ):
1) =СЧЕТЕСЛИМН(A:A;”В”;C:C;”информатика”)
2) =СРЗНАЧЕСЛИ(A:A;”C”;D:D)
(Слайд 15) Следующая задача. В электронную таблицу занесли численность населения городов разных стран. На слайде приведены первые пять строк таблицы. В столбце А указано название города; в столбце В — численность населения (тыс. чел.); в столбце С — название страны. Всего в электронную таблицу были занесены данные по 1000 городам. Порядок записей в таблице произвольный. На основании данных, содержащихся в этой таблице, ответьте на два вопроса:
1. Сколько городов Белоруссии представлено в таблице? Ответ на этот вопрос запишите в ячейку F2 таблицы.
2. Какова средняя численность населения городов, количество жителей которых не превышает 100 тыс. человек? Ответ на этот вопрос с точностью не менее двух знаков после запятой (в тыс. чел.) запишите в ячейку F3 таблицы.
Решение задачи (ПКМ):
1) =СЧЕТЕСЛИ(C:C;”Белоруссия”)
2) =СРЗНАЧЕСЛИ(B:B;”<=100”;B:B)
5. Компьютерный практикум
(Слайд 16-17) Переходим к компьютерному практикуму и следующие задания выполним на компьютере. (Приложение 1 – архив с файлами для компьютерного практикума, Приложение 2 – текст заданий для компьютерного практикума).