Компьютерное моделирование – построение экономико-статистических моделей с помощью электронных таблиц

Конспект урока

Учитель: Домрачева Ирина Николаевна

Предмет: Информатика и ИКТ

Тема урока. Компьютерное моделирование – построение экономико-статистических моделей с помощью электронных таблиц (2 часа)

Цели урока

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

• практическое применение изученного материала;

• закрепление знаний общих принципов работы табличного процессора и умения составлять таблицу для решения конкретной задачи;

• приобретение навыков в составлении таблиц разного типа, особенно имеющих профессиональную направленность;

• познакомиться с основными приемами построения экономико-статистических моделей;

• знакомство с методами прогнозирования процессов, происходящих в природе и обществе.

• знакомство с корреляционными методами вычислений и анализа;

• формирование представления о вычислениях в электронной таблице как наиболее важных в изучении информатики и широко применяемых на практике.

Развивающие

• развитие познавательного интереса, речи и внимания учащихся;

• развитие навыков индивидуальной и групповой практической деятельности;

• развитие коммуникационной компетентности у учащихся;

• развитие способности логически рассуждать, делать эвристические выводы;

• развитие умения применять знания для решения задач различного рода;

• формирование информационной культуры и потребности приобретения знаний;

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

• воспитание творческого подхода к работе, желания экспериментировать;

• воспитание трудолюбия, чувства уважения к науке;

• профессиональная ориентация и подготовка к трудовой деятельности.

Тип урока: комбинированный.

Форма проведения урока: беседа, работа в группах, ролевая игра.

Место проведения урока: кабинет информатики.

Технологии: программа электронная таблица MS Excel, дидактический раздаточный материал, принтер, мультимедийный проектор.

Краткие рекомендации к использованию: Создание и применение на уроках электронных презентаций на сегодняшний день весьма актуально, как и разработка общих методических принципов для них. Презентации можно рассматривать как дидактическое средство обучения. Электронную презентацию можно отнести к электронным учебным пособиям, но только с оговоркой: электронные учебные пособия рассматриваются как самостоятельные средства обучения, а презентация – вспомогательное, используемое учителем на уроке и требующее его комментариев и дополнений.

План урока

Организационный момент – 2 минуты
Вводное слово — 5 минут
Контроль выполнения домашнего задания – 8 минут
Презентация нового материала – 30 минут
Закрепление пройденного материала – 25 минут
Защита выполненной работы – 15 минут
Заключение – 2 минуты

Рефлексия – 2 минуты
Задание на дом – 1 минута

Ход урока

1. Приветствие.

2. Актуализация знаний.

Вопросы классу. Что такое модель, моделирование, математическая модель, что лежит в основе математической модели, каковы цели моделирования?

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

Модель нужна для того, чтобы научиться управлять объектом (или процессом) и определять наилучшие способы управления при заданных целях и критериях (управление).

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

1. Предлагаю вам решить задачу с неполным условием. Вы помните, что недостающие для ответа на вопрос задачи данные, можно получить, задавая вопросы. Количество вопросов неограниченно, однако ответ на вопрос может быть только «Да» или «Нет».

Известный гипнотезер Иван Данилин мог предсказать счет любого матча до его начала. Как ему это удавалось? (Ответ: счет любого матча до его начала 0:0).

Эта шуточная задача подводит нас к теме урока. Как вы думаете, о чем мы сегодня будем говорить?

Вопросы классу. Может ли человек прогнозировать события, будущее? Как осуществляется прогноз численности населения какой-либо страны, прогноз урожайности, прибыли, развития? На чем основаны данные прогнозы и насколько точны?

1. Существует специальная наука – математическая статистика, которая позволяет людям решать подобные проблемы.

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

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

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

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

Математическая статистика прежде всего занимается описанием картины случайного рассеивания (распределения) значений случайной величины и определяет такие параметры распределения, как средние значения, характеристики вариации и т.д., и решает такую важную задачу, как статистическая проверка достоверности заранее высказанных гипотез.

Основы теории корреляции

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

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

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

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

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

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

Корреляция (от лат. correlatio), корреляционная зависимость — взаимозависимость  двух или нескольких случайных величин. Суть ее заключается в том, что при изменении значения одной переменной происходит закономерное изменение (уменьшению или увеличению) другой(-их) переменной(-ых).

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

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

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

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

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

Алгоритм построения линии регрессии

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

2. Отметить точки на плоскости с соответствующими координатами.

3. Провести между этими точками плавную кривую таким образом, чтобы все точки были по возможности либо на линии, либо близко от нее.

4. По расположению точек на плоскости можно приблизительно определить вид линии, а следовательно, и вид математической зависимости (уравнение), соответствующей изучаемому явлению.

5. Если линия прямая, то уравнение ее в общем виде будет y=ax+b, если она параболического типа, то ее уравнение в общем виде будет выглядеть следующим образом: y=ax²+bx+c. Когда по линии регрессии видно, что зависимость между у и х обратная, то уравнение регрессии в общем виде можно представить так: у=a+b/x и т.д.

Построение корреляционных моделей

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

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

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

Здесь суммирование производится по всем N экспериментальным точкам.  - измеренные в i-м опыте значения зависимой переменной, - рассчитанное по математической модели при подстановке условий проведения  i-го опыта значение функции

Какой вид имеет уравнение регрессии, решается на основании расположения экспериментальных данных на координатной плоскости.

1. Практическая работа «Вычисление коэффициентов уравнения регрессии в электронных таблицах MS Excel"

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

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

Для проведения корреляционно-регрессивного анализа используется функция ЛИНЕЙН, которая использует метод наименьших квадратов для вычисления коэффициентов в уравнении прямой, задающей корреляционную зависимость. Эта прямая наилучшим образом аппроксимирует имеющиеся данные.

Точность аппроксимации с помощью прямой, вычисленной с помощью функции ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель, используемая функцией ЛИНЕЙН.

Аппроксима́ция (от лат. proxima – ближайшая) или приближе́ние — научный метод, состоящий в замене одних объектов другими, в каком-то смысле близкими к исходным, но более простыми.

Функция ЛГРФПРИБЛ задает экспоненциальную кривую. Если в задаче не указан тип линии регрессии, то пользователю предстоит самостоятельно решать, какая из этих двух функций в наибольшей степени подходит к данным задачи.

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

Задача №1

В результате измерения ряда сосен была установлена следующая зависимость между диаметром в сантиметрах и высотой в метрах.

Требуется найти уравнение регрессии, выражающей высоту дерева как функцию диаметра.

Построим в координатной плоскости график по точкам (D,H) координаты заданы в таблице. Из расположения точек на графике видно, что с увеличением диаметра высота деревьев тоже возрастает, т.е. существует линейная зависимость между независимой переменной D (см) и зависимой переменной H(м), которую можно представить в виде y=a*x+b.

Для нахождения коэффициентов a,b методом наименьших квадратов обратимся к электронным таблицам.

1. Занесите исходные данные в ячейки A1:B9.

1. Выделите область C2:D2 для выводов результатов расчета.

2. Вставить функцию – категория Статистические – ЛИНЕЙН – ОК.

3. Для выбора аргументов функции в поле ввода изв_знач_y выделите или наберите B2:B9, в поле изв_знач_x выделите ячейки А2:А9.

4. Для расчета дополнительной статистики в полях константа и стат наберите ИСТИНА (этот пункт можно опустить). ОК

5. После вычислений в ячейке С2 появится результат. Для получения остальных результатов необходимо щелкнуть мышью в строке ввода в конце формулы: ЛИНЕЙН(В2:В9;А2:А9;ИСТИНА;ИСТИНА) и нажать комбинацию клавиш ctrl+shift+enter.

1. В ячейках С2:D2 появятся необходимые значения a=0,519345, b=7,434524.

Уравнение регрессии будет иметь вид: y=0,519345*x + 7,434524

Продолжим решение задачи. Оценить возможную высоту дерева, если его диаметр равен 45 см.

Для этого в ячейку А10 запишем число 45. В ячейку В10 запишем формулу =С2+А10 + D2 и прочитаем результат в этой ячейке.

Задачи для самостоятельной работы

Задача 2. При медосмотре девяти мальчиков из детского сада было установлено, что они имели следующий рост и вес.

Определите уравнение регрессии и оцените возможный вес мальчика, имеющего рост 120 см.

Задача 3. Имеются данные относительно урожайности яровой пшеницы в хозяйстве и ее себестоимость за последние 9 лет (тыс.руб.)

Постройте соответствующую модель данной задачи. Определите возможную себестоимость 1ц пшеницы в 2018 году, если ожидаемая урожайность, по прогнозу специалистов, должна составить 40 ц/га.

Задача 4

Дана зависимость эффективности откорма телят от их возраста.

Эффективность откорма молодняка изменяется в зависимости от его возраста. В первый период эффективность возрастает, затем начинает снижаться, поэтому зависимость является нелинейной и может быть выражена уравнением параболы второго порядка: y=ax²+bx+c.

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

Задача 5

Оцените рост численности городского населения (млн чел.)

Проверить правильность прогноза на 2020 год, учитывая, что рост населения города осуществляется по следующей зависимости: F(t)=a*t + b.

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

1. Подведение итогов урока.

2. Рефлексия

3. Домашнее задание.

8