Розробка уроку на тему «Використання вбудованих функцій в електронних таблицях»

Использование встроенных функций average, min, max
(среднее, минимальное и максимальное значения)

Перечень заданий:

Задание на 5 баллов

1. Отформатировать текст.

Задание на 8 баллов

2. Рассчитать средний балл по предметам и по ученикам за два семестра.
3. Определить максимальный и минимальный средний балл по предметам и по ученикам за первый и второй семестр.

Задание на 10 баллов

4. Заполнить сводную таблицу: годовые оценки по предметам.
5. Определить максимальный и минимальный средний балл по предметам и по ученикам за весь учебный год.

Задание на 12 баллов

6. Определить количество отличников и двоечников за первый, второй семестр и по итогам года.

Ход выполнения

1.      Запустить файл «7кл. Сводная ведомость»
2.      Сохранить файл в свою папку на диск Д.

Задание на 5 баллов

Примечание! Для того, чтобы быстро применить форматирование текста для одного и того же диапазона ячеек на всех листах книги нужно:

- выделить все листы с помощью клавиши CTRL (выделить Лист1 (Ведомость за 1 семестр), зажать клавишу CTRL, выделить второй и третий лист поочередно, отпустить клавишу CTRL)

- применить требуемое форматирование

- снять выделение с листов, для этого щелкнуть на один из листов в книге (например, на второй или третий)

- перейти снова на первый лист и продолжить работу.

3.      Заполнить автозаполнением номер по порядку на всех трех листах.
4.      Отформатировать текст как на рисунке (кроме границ основной таблицы). См. ниже задание.

5.      Установить формат для всего текста на всех трех листах: шрифт Times New Roman, размер шрифта 12 пунктов (используйте сочетание клавиш для выделения всего текста, также можно выделить все листы в книге)
6.      Установить формат для диапазона ячеек B3:K4 и ячеек А1 и B12 на всех листах: - начертание Полужирный; - цвет черный; - выравнивание По центру
7.      Объединить диапазоны ячеек: А1:К1, D3:J3, В12:С12.
8.      Для диапазона D4:J4 и ячейки К4 установить формат написания (вертикально): Выделить ячейки, выбрать команду Формат ячеек, на вкладке Выравнивание в разделе Положение выбрать Повернуть на 90 градусов.
9.      Установить формат:

- для ячейки М8: полужирный, цвет красный, границы таблицы (как на рисунке);

- для ячейки Р8: полужирный, цвет синий, границы таблицы (как на рисунке).

- для диапазона D14:J15: обрамление таблицы; для текста: полужирный, цвета соответственно: синий, оранжевый, зеленый, красный.

10.   Сохранить книгу. Файл – Сохранить.

Задание на 8 баллов

11.   На первом листе «1 семестр» и на втором листе «2 семестр» вычислить:

Примечание!! Для быстрого выполнения задания: выделите два листа и используйте маркер автозаполнения! Не забудьте снять выделение с листов, нажав на третий лист!!

- среднее значение по каждому ученику =average(D5:J5)

- среднее значение по каждому предмету =average(D5:D11)

- максимальный и минимальный средний бал по ученику и по предмету.

=min(D12:J12) =min(K5:K11) =max(D12:J12) =max(K5:K11)

12.   Сохранить книгу. Файл – Сохранить.

Задание на 10 баллов

13.   Заполнить сводную таблицу. Рассчитать годовую оценку по каждому предмету для каждого ученика (записать формулу один раз и скопировать с остальные ячейки автозаполнением. При необходимости использовать абсолютные или смешанные ссылки), воспользовавшись одной из формул:

а) =('1семестр'!D5+'2семестр'!D5)/2 – используя известную формулу для нахождения среднего арифметического: оценку за первый семестр складываем с оценкой за второй семестр и делим на 2, используем скобки для правильной последовательности арифметических действий.

Как ввести формулу: Ставим курсор в ячейку D5 на третьем листе, в строке формул ставим «=», затем открываем скобку, переходим на первый лист, щелкаем на ячейку с оценкой по первому предмету первого ученика, нажимаем «+», переходим на второй лист, щелкаем на ячейку с оценкой по первому предмету первого ученика, закрываем скобку, нажимаем на знак деления «/», нажимаем цифру «2», нажимаем Enter для завершения ввода формулы.

б) =average('1семестр'!D5;'2семестр'!D5) – используя встроенную функцию электронных таблиц для вычисления среднего значения.

Как ввести формулу: Ставим курсор в ячейку D5 на третьем листе, в строке формул ставим «=», указываем функцию average, затем открываем скобку. Далее указываем диапазон: переходим на первый лист, щелкаем на ячейку с оценкой по первому предмету первого ученика, нажимаем «;», переходим на второй лист, щелкаем на ячейку с оценкой по первому предмету первого ученика, закрываем скобку, нажимаем Enter для завершения ввода формулы.

14.   Растянуть формулу вниз и вправо! Установить формат чисел для корректного отображения оценок. Для этого: выделить диапазон D5:J11, выбрать команду Формат ячеек, на вкладе Число установить Числовой формат и Количество знаков после запятой (десятичные знаки) установить 0.
15.   Рассчитать средний балл, определить максимальный и минимальный средний балл по предметам и по ученикам за год аналогично п.10.
16.   Согласно рисунку сделать обрамление всех таблиц.
17.   Сохранить книгу. Файл – Сохранить.

Задание на 12 баллов

18.   Определить количество отличников и двоечников за первый, второй семестр и по итогам года.

Для выполнения данного задания можно воспользоваться встроенной функцией COUNTIF (счет если). Эта функция подсчитывает количество ячеек, которые отвечают указанному условию. Например, =COUNTIF(A2:A5;'яблоки') подсчитывает количество ячеек в диапазоне A2:A5, содержащих слово 'яблоки'.

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

Для подсчета количества отличников будем использовать формулу: =COUNTIF(K5:K11;H14), где (K5:K11) – диапазон ячеек (средний балл каждого ученика), а H14 – критерий (>9)

Для подсчета количества двоечников будем использовать формулу: =COUNTIF(K5:K11;H15), где (K5:K11) – диапазон ячеек (средний балл каждого ученика), а H15 – критерий (<4)

Для быстрого заполнения таблицы можно выделить сразу все три листа, в диапазон ячеек I14 ввести формулу =COUNTIF(K$5:K$11;H14) и скопировать ее маркером автозаполнения в ячейку I15.

19.   Сохранить книгу. Файл – Сохранить.