Использование различных возможностей динамических (электронных) таблиц

Практическая работа №16.

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

1. Цель работы: освоить порядок проведения корреляционного и регрессионного анализа средствами электронных таблиц.

2. Литература: 1.  Информатика и ИКТ: учебник для начального и среднего профессионального образования. Цветкова Н.С., Великович Л.С. – Академия, 2011 г.

2.Информатика и ИКТ. Практикум для профессий и специальностей технического и социально-экономического профилей. Н. Е. Астафьева, С. А. Гаврилова, под ред. М.С. Цветковой, Академия, 2012г.

3.      Информатика и ИКТ. Базовый уровень: учебник для 10-11 кл. / И.Г.Семакин, Е.К.Хеннер. – 4 изд., испр. – М. – Бином. Лаборатория знаний, 2008г. – 246 с.: ил.

4.      Информатика и ИКТ. Базовый уровень: практикум для 10-11 кл. / И.Г.Семакин, Е.К.Хеннер. – 4 изд., испр. – М. – Бином. Лаборатория знаний, 2008г.

5.      Информатика и ИКТ. 10 кл. Базовый уровень под ред. Н.В.Макаровой – Спб – Лидер, 2010г.

6.      Информатика и ИКТ. 11 кл. Базовый уровень под ред. Н.В.Макаровой – Спб – Лидер, 2010г.

7.      Энциклопедия школьной информатики / под ред. И.Г.Семакина. – М.: Бином. Лаборатория знаний, 2011г.

3. Подготовка к работе:

3.1. Изучить материал и конспект лекций по курсу.

3.2. Изучить описание практической работы.

3.3. Подготовить бланк отчета, содержащий название, цель работы, задание.

4. Оборудование, приборы, аппаратура, материалы: персональный компьютер, программа MS Еxcel.

5. Задания:

Занятия в лаборатории разрешается проводить только в присутствии преподавателя.

5.1. Изучить теоретический материал

5.2. Выполнить задание 1

5.3. Ответить письменно на контрольные вопросы:

  Краткие теоретические сведения.

 Статистика – наука о сборе, измерении и анализе массовых количественных данных.

Функция, которая удовлетворяет требованиям:

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

Получение регрессионной модели происходит в два этапа:

1. подбор вида функции;
2. вычисление параметров функции.

 Чаще всего выбор производится среди следующих функций:

• y=аx+b - линейная функция;
• y=аx²+bx+c -квадратичная функция;
• y=аln(x)+b - логарифмическая функция;
• y=ae^bx - экспоненциальная функция;
• y=ax^b -степенная функция.

Во всех этих формулах x – аргумент, y – значение функции, а, b, c – параметры функций.

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

Существует метод наименьших квадратов (МНК). Его суть – искомая функция должна быть построена так, чтобы сумма квадратов отклонений y-координат всех экспериментальных точек от y-координат графика Функции была бы минимальна.

Графики регрессионной модели называются трендами. (английское слово trend переводиться как общее направление или тенденция).

Опишем алгоритм получения с помощью MS Еxcel регрессионных моделей по МНК с построением тренда.

1. вводим табличные данные;
2. строим точечную диаграмму, где в качестве подписи к оси Ox выбрать текст «линейный тренд» (остальные надписи и легенду можно игнорировать);
3. щелкнуть мышью по полю диаграммы; выполнить команду диаграмма – добавить линию тренда;
4. в открывшемся окне на закладке «тип» выбрать «линейный тренд»;
5. перейти к закладке «параметры» и установит галочки на флажках «показать уравнения на диаграмме» и «поместить на диаграмме величину достоверности аппроксимации R^2» и щелкнуть OK;
6. аналогично получаем и другие тренды.

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

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

• коэффициент корреляции (обычно обозначаемый греческой буквой r) есть число, заключенное в диапазоне от -1 до +1;
• если это число по модулю близко к 1, то имеет место сильная корреляция, если к 0, то слабая;
• близость r к +1 означает, что возрастанию одного набора значений соответствует возрастание другого набора, близость к -1 означает обратное;
• значение r легко найти с помощью Excel (встроенные статистические функции).

 В Еxcel функция вычисления коэффициента корреляции называется Коррел и входит в группу статистических функций.

 Задание 1.

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

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

Исходные данные и результаты расчетов сохраните в Файл ПР16.хls.

 Контрольные вопросы

1. В чем сущность и назначение регрессионного анализа?
2. В чем сущность и назначение корреляционного анализа?
3. Укажите порядок проведения корреляционного анализа средствами электронных таблиц.
4. Что такое тренд?
5. Как построить линию тренда?