CC BY
124
Беляева Светлана Владимировна Челак Евгения Николаевна
Excel: от простого к сложному
Вычислительная техника все шире и шире проникает во все уголки человеческой деятельности. Кое-кто из наших учеников в будущем будет использовать компьютеры1 для сложные вычислений в научной деятельности, в поисково-информационные системах, но скорее всего нашим ученикам предстоит работа с ними в качестве оргтехники. Таким образом, весьма важныш в подготовке учеников к реалиям современной жизни и становится изучение так называемых офисных программных продуктов, к которым относится семейство электронные таблиц.
Наиболее распространенной в мнре на сей день является электронная таблица фирмы Microsoft Excel - мощный профессиональный пакет с огромными возможностями. Электронная таблица Excel - это прямоугольная сетка в 256 столбцов и 16 384 строк. Основной единицей рабочей таблицы является ячейка, пересечение строки и столбца, в которой Вы храните данные. Столбцы помечаются слева направо, начиная от А до Z. После Z отметка продолжается от AA до AZ, затем от BA до BZ, и т.д. до столбца IV - в сумме 256. Строки нумеруются вниз от 1 до 16 384. При обучении учеников важны следующие аспекты:
1. Каким образом можно заполнять рабочую таблицу введением текста, чисел и формул в ячейки.
2. Каким образом можно изменить способ, которым информация выводится в рабочей таблице, меняя размер, стиль и цвет данных внутри ячеек.
3. Каким образом можно добавить графические объекты для улучшения внешнего вида рабочей таблицы.
4. Каким образом происходят вычисления в рабочей таблице при добавлении или изменении данных.
Наша цель в преподавании этой темы - на конкретных практических примерах познакомить учащихся с определенным классом задач и дать навыки практической работы. Подборка задач ориентирована на общеобразовательный уровень старшеклассников.
ввод и редактирование данных
Основные правила ввода чисел или текста следующие:
1. Укажите ячейку, в которую необходимо ввести данные, и дважды нажмите кнопку мыши.
2. Если ячейка содержит данные, укажите в ней ту позицию, начиная с которой будет осуществлен ввод.
сценарии уроков
39
3. Наберите число или текст и нажмите клавишу ENTER.
На этом этапе важно акцентировать внимание учащихся на двух моментах. Первое: содержимое ячейки и изображение содержимого ячейки - не одно и то же. Несмотря на то, что часть изображения ячейки может быть закрыта другой ячейкой, информация не теряется и не пропадает. Второе: ввод данных в ячейку необходимо зафиксировать одним из трех способов:
• Нажать клавишу ENTER (или щелкнуть мышью по галочке в строке формул).
• Щелкнуть мышью по другой ячейке.
• Нажать одну из клавиш управления курсором.
В ранее изучаемых текстовых редакторах подобных особенностей не было.
Рассмотрим некоторые классы задач, решение которых поможет освоению EXCEL.
I. ЗАДАЧИ НА РАБОТУ С ДАННЫМИ РАЗНОГО ТИПА. ПРИЕМЫ ОФОРМЛЕНИЯ ТАБЛИЦЫ, ФОРМАТИРОВАНИЕ.
Задача 1: Произвести расчет семейного бюджета.
II. ЗАДАЧИ НА ИСПОЛЬЗОВАНИЕ БУФЕРА ОБМЕНА.
Задача 2: Составить график дежурства по кухне в коммунальной квартире (таблица 1).
На этом примере происходит отработка следующих приемов Excel:
1. Работа с буфером обмена (повторяющиеся фамилии).
2. Работа с маркером заполнения (дни недели).
3. Форматирование ячейки по выделенному блоку ячеек (заголовок таблицы, шрифты).
Предлагается выполнить задачу 2 еще раз, но с использованием буфера обмена и маркера заполнения. Сравнив трудозатраты на выполнение одного и того же примера с использованием различных технических приемов, учащиеся раз и навсегда запомнят преимущества, привносимые ими, и будут использовать их, а также искать новые приемы, облегчающие рутинную работу.
Дежурство по кухне
Дни недели Утро Вечер
Понедельник Иванов Лисин
Вторник Катков Лотов
Среда Страхов Молотков
Четверг Петров Иванов
Пятница Лотов Страхов
Суббота Лисин Катков
Воскресенье Молотков Петров
дни недели февраль зима декабрь январь февраль
понедельник 1 8 15 22 май
16 23 весна март апрель
вторник 2 9
среда 3 10 17 24 лето июнь июль август
четверг 4 11 18 25
пятница 5 12 19 26 осень сентябрь октябрь ноябрь
суббота 6 13 20 27
воскресенье 7 14 21 28 Таблицы 2, 3
маркер заполнения
Этот пример - легкий этюд на использование маркера заполнения. На этом примере демонстрируется возможность заполнения блоков таблицы (а не только столбцов или строк).
Задача 3: Используя маркер заполнения, составить календарь на месяц (таблица 2).
Задача 4: Распределить месяцы по временам года (таблица 3).
III. ЗАДАЧИ НА ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ.
Наиболее часто употребляемой функцией Excel является функция СУММ. Эта функция возвращает сумму всех чисел, входящих в список аргументов.
Задача 5: Заполнить сводную ведомость (таблица 4).
При использовании функций учащиеся должны освоить аппарат "Мастер функций", который упрощает процесс вставки формул в строку формул.
Задача 6: Произвести расчет затрат на наши покупки.
Задача 7: Расчет стоимости завтрака.
Задача 8: Произвести необходимые расчеты роста учеников в различных единицах измерения (таблица 5).
№ п/п Фамилия,имя рост (см) рост (дюйм) рост (аршин) рост (вершки) рост (Фут)
1
2
3
4
средний рост ДЮЙМ - 2,54 см
максимальный рост АРШИН - 71,12 см. ВЕРШОК - 4,45 см.
минимальный рост ФУТ - 30,48 см.
Таблица 5
Класс 5 4 3 2 неявка количество
5-а 4 3 5 3 4 19
5-6 6 6 3 5 9 29
5-в 6 7 6 7 8 34
6-а 3 4 9 7 2 25
6-6 4 7 5 3 3 21
6-в 3 7 3 5 5 24
7-а 5 4 7 5 3 24
7-6 6 8 7 4 8 33
7-в 6 6 4 5 7 27
Всего отлично 41
Всего хорошо 52
Всего посредственж 49
Всего неуд. 44
Всего неявок 49
Таблица 4
IV. ЗАДАЧИ НА ИСПОЛЬЗОВАНИЕ АБСОЛЮТНОЙ И ОТНОСИТЕЛЬНОЙ АДРЕСАЦИИ.
Задача 9: Вычисление я-ого члена и суммы арифметической прогрессии.
Вычисление n-ого члена и суммы арифметической прогрессии
d п a п S п
1,5 1 -3,5 =(C$3+C3)*B3/2
1,5 2 =C$3+A4*(B4-1) =(C$3+C4)*B4/2
1,5 3 =C$3+A5*(B5-1) =(C$3+C5)*B5/2
1,5 10 =C$3+A12*(B12-1) =(C$3+C12)*B12/2
Вычисление n-ого члена и суммы арифметической прогрессии
d п a п S п
1,5 1 -3,5 -3,5
1,5 2 -2 -5,5
1,5 3 -0,5 -6
1,5 10 10 32,5
Таблица Пифагора
1 2 3 4 5
1 1 2 3 4 5
2 2 4 6 8 10
3 3 6 9 12 15
4 4 8 12 16 20
5 5 10 15 20 25
В этом примере вводятся понятия абсолютного и относительного адреса, кроме того, учащиеся уясняют, что маркер заполнения используется не только для числовых данных, но и для формул. Здесь же отрабатывается форматирование символов ячейки - нижние индексы.
Задача 10: Заполнить таблицу умножения (Пифагора), в каждой клеточке которой стоит произведение номера строки и номера столбца таблицы (таблица 7).
Таблица 7.
V. ЗАДАЧИ НА ИСПОЛЬЗОВАНИЕ МАСТЕРА ДИАГРАММ.
Электронные таблицы - не единственный тип документов, создаваемых в Excel. Дополнительно к таблицам можно получить диаграммы. Задача 11: Построение графика функции по точкам. Задача 12: Календарь погоды на первую декаду месяца.
Календарь погоды
число 1
ясно 1
пасмурно
осадки
15
за декаду
ясно 5
пасмурно 2
осадки 3
максимальная 25
минимальная 10
средняя 17,1
6
8
9
10
10
11
25
18
17
20
15
23
17
-Ряд1
123456789 10
осадки 30%
ясно 50%
пасмурно 20%
VI. ЗАДАЧИ НА ИСПОЛЬЗОВАНИЕ УСЛОВИЙ.
Задача 13: Решение квадратного уравнения: ах2+Ьх+с=0
I вариант (облегченный)
a b C D Решение
4 6 5 =C4*C4-4*B4*D4 =ЕСЛИ(Е4>=0;»есть»;»нет»)
3 9 -4 =C5*C5-4*B5*D5 =ЕСЛИ(Е5>=0;»есть»;»нет»)
8 12 1 =C6*C6-4*B6*D6 =ЕСЛИ(Е6>=0;»есть»;»нет»)
I I вариант (усложненный)
a b c d X1 X2
1 2 1 =C7*C7- =ECm($E7>=0;(-$C7+ =ECm($E7>=0;(-$C7-
4*B7*D7 КОРЕНЬ($Е7))/ (2*$В7);»НЕТ») КОРЕНЬ( $E7))/(2 * $B 7) ; »НЕТ »)
Законченный вариант:
a b c d Решение X1 X2
4 6 5 -44 нет НЕТ НЕТ
3 9 -4 129 есть 0,392969 -3,39297
8 12 1 112 есть -0,08856 -1,41144
12 1 0 есть -1 -1
Задача 13 демонстрирует использование EXCEL для проведения единичных вычислений. Кроме того, на этом примере отрабатывается навык работы с функцией ЕСЛИ. Попутно используются: цвет фона, цвет ячейки, вставка спецсимволов. Это повышает наглядность представленной в таблице информации. В качестве коэффициентов здесь можно было бы использовать целые случайные числа, получаемые формулой: ЦЕЛОЕ(СЛЧИС()*(В-А)+А), где[А,В] - интервал, из которого выбираются случайные числа.
VII. ЗАДАЧИ С ИСПОЛЬЗОВАНИЕМ СЛУЧАЙНЫХ ЧИСЕЛ.
Задача 14: Заполнить рейтинговую оценку в зависимости от количества ошибок учащегося, сравнивая его со средним значением.
Результаты диктанта
Количество ошибок Оценка
Иванов 1 4
Петров 4 3
Сидоров 2 4
Среднее 2,33 3,66
Результаты диктанта
Количество ошибок Оценка
Иванов =ЦЕЛ0Е(СЛЧИС()*10) =ЕСЛИ(B3=0;5;ЕСЛИ(B3<$B$15;4;3))
Петров =ЦЕЛОЕ(СЛЧИС()*10) =ЕСЛИ(B4=0;5;ЕСЛИ(B4<$B$15;4;3)
Сидоров =ЦЕЛ0Е(СЛЧИС()*10) =ЕСЛИ(B5=0;5;ЕСЛИ(B5<$B$15;4;3))
Среднее
VIII. ЗАДАЧА НА ИСПОЛЬЗОВАНИЕ ДЕНЕЖНОГО СТИЛЯ.
Задача 15: Построить график изменения курса доллара.
Курс долла ра
Числа 01.11 02.11 03.11 04.11 05.11 06.11 07.11 08.11 09.11 10.11
Амер. доллар 6,13р. 6,04р. 6,10р. 6,02р. 6,05р. 6,17р. 6,15р. 6,16р. 6,11р. 6,06р.
Среднее
6,10р.
Максимальное 6,17р.
Минимальное 6,02р.
6,20 6,10 -6,00
5,90 -1
-Ряд1
10 11 12 13 14 15 16
Задача 16: Составить ведомость начисления зарплаты.
IX. ИСПОЛЬЗОВАНИЕ ССЫЛОК И ОФОРМЛЕНИЕ ТАБЛИЦЫ НА НЕСКОЛЬКИХ ЛИСТАХ.
MS Excel работает не с одной таблицей, а с "книгой", которая содержит 16 листов.
Задача 17: На основании экзаменационной ведомости заполнить ведомость начисления стипендии.
На этом примере отрабатывается навык работы с данными, расположенными на разных листах таблицы, использование ссылок между листами, вложенную функцию ЕСЛИ. Разбиение таблицы на два листа в данном случае логически оправдано: Лист 1 (экзаменационная ведомость) содержит исходную информацию, а на Листе 2 (ведомость начисления стипендии) происходит ее обработка. На этом примере удобно отрабатывать приемы сортировки данных по разным столбцам (фамилия, средний балл, стипендия).
Содержание Листа 1:
Отсортированная по фамилиям:
Экзаменационная ведомость
Фамилия Физ. Матем. Химия Истор.
Иванов 3 4 5 3
Петров 4 4 5 5
Сидоров 5 5 5 5
Кузнецов 3 3 5 4
Харламов 4 4 3 5
Экзаменационная ведомость
Фамилия Физ. Матем. Химия Истор.
Иванов 3 4 5 3
Кузнецов 3 3 5 4
Петров 4 4 5 5
Сидоров 5 5 5 5
Харламов 4 4 3 5
Содержание Листа 2:
Ведомость начисления стипендии
=Лист1 !A2 средний балл стипендия
=Лист1 !A3 =СРЗНАЧ(Лист1 !B3:E3) =ЕСЛИ(В3=5;»120 руб»; ЕСЛИ(В3>=$В$10;»80 руб»;»нет»))
=Лист1 !A4 =СРЗНАЧ(Лист 1 ! B4:E4) =ЕСЛИ(В4=5 ; » 120 руб»; ЕСЛИ(В4>=$В$1 0-»80 руб»'»нет»))
=Лист1 !A5 Ведомость начисления стипендии
Фамилия средний балл стипендий)
=Лист1!А6 Кузнецов 3,75 н ет
Харламов 4 нет
=Лист1 !A7 =Лист1 !A8 Смирнов 4 н ет
Иванов 4,25 830 руб
Петров 4С 830 руб
средний балл по курсу Сидоров 5 120 руб
средний балл по курсу 4,25
Данные Листа 2 могут быть графически проиллюстрированы:
Средний балл
4,25
3,75
□ Кузнецов
□ Харламов
□ Смирнов
□ Иванов
□ Петров
□ Сидоров
Тщательная проработка указанных примеров даст учащимся необходимые навыки для использования EXCEL.
НАШИ АВТОРЫ
Беляева Светлана Владимировна, учитель информатики школы № 518, г. Санкт-Петербург. Челак Евгения Николаевна, учитель информатики школы1 № 483, г. Санкт-Петербург.
