Санкт-Петербургский центр детского технического творчества

Отдел компьютерных технологий

Методическая разработка  темы:

Использование формул  при вычислениях в программе MS Excel

(для учащихся 8-10 классов)

Составитель: Хрулева Галина Владимировна

педагог дополнительного образования

Санкт-Петербург

        2012        

Цель методической разработки:

Научить детей свободно и эффективно использовать приложение MS Excel для решения научных, учебных и бытовых задач.

Задачи:

1. Образовательные

1. Получение знаний по структуре, возможностям и сферам применения программы MS Excel.
2. Обучение способам эффективного использования программы MS Excel при проведении расчетов.

1. Показать особенности и преимущества программы.
2. Научить использовать абсолютные, относительные ссылки и имена ячеек при расчетах по формулам.

2. Развивающие

3. Формирование навыков работы в среде MS Excel
4. Приобретение навыков алгоритмического мышления
5. Развитие ассоциативного мышления и эстетического чувства

3. Воспитательные

1. Воспитание трудолюбия, дисциплины и организованности
2. Воспитание инициативности и настойчивости в достижении поставленной цели

Предложенная тема имеет особое значение в связи с широкой сферой применения и одновременно, как показывает практика, представляет собой некоторые трудности для усвоения  учащимися. Формулы и функции применяются при изучении многих школьных предметов. Это, казалось бы должно облегчить их использование в программе MS Excel.  Однако, к сожалению, как только возникает связь компьютерных программ с предметами школьного курса, у большинства учащихся пропадает интерес к занятиям. Кроме того, знания учащихся по точным и техническим дисциплинам (математике, физике, биологии) оставляют желать лучшего. Эти обстоятельства вызывают необходимость разработать обширный и разнообразный комплекс задач для изучения и освоения данной темы. При этом следует строить занятия так, чтобы вызвать у детей интерес к данным возможностям офисной программы MS Excel.

План:

1. Понятия формулы и функции. Расчет стоимости обоев для оклейки комнаты.
2. Различные категории функций. Логические функции. Тест «Вы витаете в облаках?»
3. Копирование формул. Относительные и абсолютные ссылки на ячейки. Таблица квадратов.
4. Присвоение имени ячейке. Расчет стоимости электроэнергии.
5. Задания для проведения контрольной работы по теме.

Тематический план

Итого        :                5                11

Всего: 16 часов

Определение 1. Формула представляет собой вычислительную процедуру, выполняемую для определения значения в ячейке, использующую данные других ячеек. Формула вводится в строку формул и начинается со знака «=» и включает в себя адреса ячеек, знаки арифметических действий, скобки, имена функций.

Определение 2. Функция – стандартная вычислительная операция, осуществляемая программой и вызываемая по имени.

Список всех функций осуществляется щелчком по кнопке fx  в строке формул. Функции сгруппированы в категории: математические, логические, Дата и Время и т.д. Перед тем, как ввести формулу в ячейку, необходимо задать этой ячейке соответствующий формат.

Задание 1. Определить время в пути, если известно время прибытия и отправления поезда.

Следует обратить  внимание на то, что при замене данных, меняется результат. То есть, мы получили нечто вроде программы, считающей время в пути для любых исходных данных. Что получится, если время отправления, больше времени прибытия?

Все  арифметические действия выполняются в формуле по следующим правилам: вначале умножение и деление, затем сложение и вычитание, для изменения порядка действий необходимо использовать круглые скобки.

Если в ячейке находится результат вычисления по формуле, то при выделении ячейки в строке формул появляется сама эта формула. Для ее редактирования нудно дважды щелкнуть по ячейке.

Задание 2. Вычислить стоимость обоев, необходимых для оклейки комнаты. Для простоты окна и двери в расчет не принимать. Даны параметры комнаты, параметры обоев, стоимость рулона обоев. Внимание: купить можно только целый рулон. Использовать функцию ОКРВВЕРХ.

Задание 3. Тест «Вы витаете в облаках». При ответе на вопрос ставим 1 в графе «да» или «нет». Считаем сумму ответов «да». Используя функции ЕСЛИ, И, ВЫБОР, сообщаем результат.

Для автоматизации создания таблиц в MS Excel служит маркер заполнения. Введем в две соседние ячейки числа 1, 3. Выделим эти ячейки и поставим курсор в нижний правый угол выделенного фрагмента. Курсор примет вид черного крестика. Это и есть маркер заполнения. Если протянуть его на соседние ячейки, мы получим 5, 7 и т.д. Таким образом, происходит автозаполнение.

Сделаем еще 2 примера: 1.Вводим в ячейку слово среда. Автозаполнение соседних ячеек даст все дни недели. 2. Вводим слово май. Получаем месяцы. Если этого не происходит, выбираем в горизонтальном меню Сервис < Параметры, вкладка «Списки» и дополняем существующий набор списков. Введем в поле Элементы списка соответствующую последовательность и нажмем кнопку . Таким образом, списки можно обогащать.  

Пользуясь маркером заполнения, можно копировать формулы.  Рассмотрим это на примере «Продажа мороженого по округам».

Задание 4. Заполним таблицу и  вычислим сумму продаж за лето в центральном районе. Используем функцию «Автосумма» (Σ). Затем, пользуясь маркером заполнения, получаем суммы продаж в трех других округах. То есть при копировании формулы вниз или вверх автоматически меняется номер строки. Точно также при копировании влево (вправо) увеличивается (уменьшается) номер столбца. Вычислите таким же способом суммы продажи мороженого во всех округах по месяцам. Это свойство называется относительной адресацией ячеек или относительной ссылкой. В данном случае относительная адресация помогла нам быстро решить задачу.  Но так бывает не всегда.

Задание 5. Составить таблицу умножения. Ввести в строку и в столбец числа от 1 до 9. На пересечении должно стоять произведение номера в строке на номер в столбце. Максимально автоматизировать заполнение таблицы. То есть, ввести формулу в одну ячейку, а затем маркером заполнить всю таблицу. При попытке действовать как в предыдущем примере, мы получим результат не соответствующий действительности. Это произошло потому, что при перемещении формулы переместилась и ссылка на ячейку. Но нам нужно ее зафиксировать. Для фиксирования какой-то позиции адреса (строки или  столбца) перед ней ставят знак $. Скорректировав формулу в первой ячейке таблицы, мы получим верный результат. При этом мы две ссылки сделали абсолютными, в одном сомножителе позиция строки, в другом – позиция столбца. Такой способ называют абсолютной адресацией или абсолютной ссылкой

Задание 6 (самостоятельно). Заполнить таблицу квадратов, пользуясь абсолютными ссылками. В ячейке должен стоять квадрат числа, полученного умножением номера строки на десять и добавлением номера столбца.

Осуществить абсолютную адресацию можно другим способом, присвоить ячейке имя. При этом фиксируется как позиция ячейки в строке, так и в столбце. Обращение к ячейке происходит по имени. Имя должно соответствовать следующим требованиям:        

1. Имя должно начинаться с буквы.
2. Если используется цифра, то она должна следовать за буквой или символом подчеркивания
3. Для разделения слов используется символ подчеркивания
4. Имя не может совпадать со ссылкой на ячейку
5. В качестве имени можно использовать одиночные буквы за исключением букв R и C

Существуют два способа именования ячеек.

1. Присвоение имени ячейке с помощью горизонтального меню. Вставка <  Имя <  Присвоить. Вводим имя в область ввода и нажимаем кнопку «Ok»
2. Вводим имя ячейки в поле «Имя» и нажимаем клавишу “Enter”

Выполняем Задание 7, используя оба способа именования ячеек. При этом если мы щелкнем на кнопке списка «Имя», то будет отображен перечень всех присвоенных имен.

Для усвоения способа адресации с именованием ячеек выполним Задание 8 »Расчет расхода и стоимости электроэнергии». Примечания: столбец «месяц» получаем автозаполнением, заголовки столбцов выравниваем по центру по вертикали и горизонтали с флажком «перенос по словам», столбцы «дата» и «показания счетчика» проставляем произвольно, расход считаем по формуле: показания месяца – показания предыдущего месяца, распространяем на весь столбец автозаполнением

Для тех учащихся, кто справился ранее других с предложенным заданием, можно предложить выполнить Задание 9. «Угадывание задуманного числа». Задумать четное число, утроить, полученное произведение разделить на 2 и частное утроить. После объявления результата вы называете задуманное число. (Формула: задуманное число = результат/9*2)

S

c

Приведем пример того как с помощью программы MS Excel можно находить экстремальные значения.

a

b

        Задание 10. Найти при каком значении b, если длина квадрата a задана, объем коробки с основанием площадью S  и высотой b будет наибольшим. Выполним задание, меняя b от 2 до 14 с шагом 2, а затем уточним наибольшее значение, выбрав шаг 0,5.

Для закрепления материала рекомендуем провести проверочную работу, состоящую из двух вариантов

Ожидаемый результат:

Предлагаемая последовательность изложения материала и набор заданий позволяют выработать у учащихся навыки работы с формулами и различными видами данных в приложении MS Excel. Учащиеся  должны научиться:

1. Использовать маркер заполнения
2. Форматировать ячейки в соответствии с типом данных
3. Находить функции при помощи мастера функций
4. Копировать функции
5. Использовать абсолютную или относительную адресацию в зависимости от ситуации
6. Присваивать имена ячейкам и использовать их в формулах
7. Находить ошибки в построении выражений и исправлять их, пользуясь подсказками программы
8. Решать несложные задачи на экстремум
9. Использовать логические функции для построения тестов

Техническое оснащение занятий :

1. Расходные материалы
2. Оборудование: компьютерный класс ПЭВМ типа IBM (Intel Seleron, 120 МБ ОЗУ)
3. Операционная система MS Windows 7
4. Пакет программ Office XP

Список используемой литературы:

1. Ю. Шафрин «Основы компьютерной технологии», Москва изд-во АБФ, 1997
2. О. Ефимова, Ю.Шафрин «Практикум по компьютерной технологии», Москва, изд-во АБФ, 1997
3. Г. Дабижа «Работа на компьютере», СПб, Питер, 2010
4. Ю. Стоцкий «Office XP”, СПб, Питер, 2003
5. Microsoft Word 2000 Шаг за шагом, М, ЭКОМ, 2002
6. Microsoft Exel 2000 Шаг за шагом, М, ЭКОМ, 2002
7. А.А.Бобцов и др. «Пособие по программам Microsoft Office 2007/2010», СПб, ИТМО, 2010