Методическая разработка практического занятия «Электронные таблицы. Прогнозирование в Excel на основе Интернет-ресурсов»

№

пп

Структурный элемент занятия

Время

1

Организационный момент

2 минуты

2

Постановка дидактической цели

3 минуты

3

Мотивация предстоящей деятельности

2 минуты

4

Повторение изученного материала

10 минут

5

Изучение и закрепление материала на практике

15 минут

6

Закрепление практического материала

10 минут

7

Подведение итогов

3 минуты

Всего

45 минут

Структурный элемент занятия

Деятельность учителя,

краткое содержание урока

Деятельность учащихся

Организационный момент

Учитель приветствует группу и проверяет ее готовность к занятию.

Ученики приветствуют учителя. Староста группы сообщает о явке учащихся на занятие.

Постановка дидактической цели

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

При моделировании различных экономических

процессов на практике широко используются возрастающие возможности современных компьютерных технологий, а также эффективные способы прогнозирования. Так, для разработки прогнозов в пакете Exсel можно воспользоваться таким инструментом, как построение регрессии. А какие математические программы Вы еще знаете?

Учащиеся слушают рассказ учителя. Предлагают варианты ответа на вопрос учителя.

Мотивация предстоящей деятельности

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

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

Ученики слушают рассказ учителя. Воспринимают информацию в текстовой и аудиовизуальной форме.

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

Повторение изученного материала

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

Фактически между переменными ставится причинно-следственная зависимость.

В курсе математики Вы уже изучали уравнение линейной функции y = kx + b и строили ее график.

Для того, чтобы вспомнить как строится график этой функции, мы используем интерактивную модель, созданную в «Математическом конструкторе»: № 27. Решение линейного уравнения (http://obr.1c.ru/pages/read/mk_collection/).

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

Вспоминают с помощью интерактивной модели, как строится линейная функция.

программы 1С.

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

Изучение материала на практике

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

Мы видим, что температура падает, это не происходит прямолинейно, но есть тенденция к ее уменьшению. И возникает задача как эту тенденцию нам измерить, и сказать, в какой степени мы уверены, что завтра, скажем, будет вероятнее что -10 чем +2? Для такого типа задач можно применять регрессионный анализ. Причем, при решении таких задач нам нужно не только рассчитать степень уверенности в «завтрашнем дне», но и построить график линейной (или другой) функции максимально близко «объясняющей» данную связь между изменяющимися данными.

Для того чтобы понять как рассчитывается прогноз и строится соответствующий график, рассмотрим реальный пример, где в качестве исходных данных для решения задачи используем Интернет-ресурс, т.е. сайт: http://www.kurs.metrinfo.ru/kurs/, на котором представлена актуальная информация по курсы валют в табличной форме.

Условие задачи. На основе фактических данных курса валют спрогнозировать стоимость доллара (евро) по отношению к рублю на следующий день. Построить регрессию, график и указать процент достоверности прогноза.

Решение задачи.

На начальном этапе данные копируются с сайта и подготавливаются для последующей обработки (см. табл.1).

Таблица 1

Курс валют в таблице

.

Для подготовки таблицы 1 к обработке необходимо решить ряд сопутствующих задач:

Форматирование таблицы, т.е. изменение заливки, границ таблицы и т.д.

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

Применение функции «Найти и заменить» во вкладке «Заменить» для замены точек на запятые (см. рис.1).

Перекодировка даты на числовой формат.

Рис.1. Панель «Найти и заменить»

В результате этих преобразований получим таблицу 2.

Таблица 2

Курс валют в таблице

Графически изменение курса доллара по дням представлено на рисунке 2.

Рис.2. Изменение курса доллара по дням

С помощью функции «Добавить линию тренда …» (см. рис.3) добавляем к графику уравнение и величину достоверности аппроксимации (см. рис.4, 5).

Рис.3. Формат линии тренда

Уравнение регрессии и величину достоверности аппроксимации мы можем получить с помощью надстройки «Анализ данных» → «Регрессия».

Рис.6. Окно выполнения регрессии

Результат работы программы показан в таблице 3.

Таблица 3

Вывод итогов

Коэффициенты регрессии, которые показаны на рисунке 4, выделены в таблице 3 жирным шрифтом. Регрессионная модель вида Y = kX + b будет выглядеть:

Y = 0,0169*x + 39,0614 (R2 = 0,9195).

Коэффициент детерминации (R-квадрат) приблизительно составил 0,91 или 91%. Это означает, что модель хорошо объясняет изменчивость уровня цены доллара от времени. 9% – доля изменчивости уровня цены, объясняемая другими факторами.

Вычислим на основе указанной регрессии стоимость доллара по отношению к рублю на следующий день:

Y = 0,0169*x + 39,0614 = 0,0169*32 + 39,0614 = 42,4811 (руб.). Конец решения.

Ученики слушают рассказ учителя.

Приводят примеры других нефункциональных зависимостей в природе, биологии, экономике и других науках.

Вместе с учителем решают задачу.

Закрепление практического материала

Учитель сообщает, что полезным для решения задач методами математического прогнозирования, является сайт Гидрометцентра России http://www.meteoinfo.ru/ где представлены фактические данные атмосферного давления, температуры.

Учитель формулирует задачу на закрепление практического материала.

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

Таблица 4

Архив фактической погоды

Ученики самостоятельно решают задачу.

Подведение итогов

Учитель делает выводы по пройденному материалу.

Дает домашнее задание и рекомендует интернет-сайты, и литературу для более глубокого изучения материала.

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

В конце занятия учитель просит ответить на вопросы анкеты!

Что вы нового узнали сегодня на занятии?

Хотите ли узнать еще о методах прогнозирования?

Какие интернет-ресурсы можно использовать еще для применения метода регрессии?

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

Учащиеся отвечают на вопросы анкеты и сдают их учителю.