Добавить в избранное

Гипотетическая корреляционная зависимость. Расчет корреляционных зависимостей в Microsoft Excel. Регрессионный анализ: построение предположений

>>Информатика: Компьютерный практикум: Работа 15. Расчет корреляционных зависимостей в MS Excel

Компьютерный практикум

Работа 15. Расчет корреляционных зависимостей в MS Excel

Цели работы:

Получение представления о корреляционной зависимости величин;

Освоение способа вычисления коэффициента корреляция с помощью функции КОРРЕЛ.

Используемые программные средства: табличный процессор MS Excel.

Задание 1

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

Зависимость носит статистический характер, поскольку нельзя достоверно сказать, например, что при температуре 15°С в школе болеет 5% учащихся, а при температуре 20°С - 2%. Кроме температуры, есть и другие факторы, влияющие на простудные заболевания, различные для разных школ, и все их проконтролировать невозможно.

Последовательно выполнить следующее:

=> ввести данные в Excel так, как это представлено на рис. 2.12 (см. тему 9);

=> построить с помощью Мастера диаграмм точечную диаграмму, визуально отображающую табличную зависимость;

=> ответить на вопрос, можно ли на основании этой точечной выдвинуть гипотезу о наличии линейной корреляции между величинами;

=> если ответ очевидно отрицательный, то исправить таблицу так, чтобы гипотеза о наличии линейной корреляции стала более правдоподобна;

Задание 2

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

Примерами соответствующих связанных величин могут служить:

Уровень образования (измеренный, например, в годах обучения в целом) и уровень месячного дохода;

уровень образования и уровень занимаемой должности (для последней придумайте условную шкалу);

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

Число часов» затрачиваемых старшеклассником на выполнение домашних заданий, и средняя оценка;

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

Семакин И.Г., Хеннер Е.К., Информатика и ИКТ, 11

Отослано читателями из интернет-сайтов

Содержание урока конспект урока опорный каркас презентация урока акселеративные методы интерактивные технологии Практика задачи и упражнения самопроверка практикумы, тренинги, кейсы, квесты домашние задания дискуссионные вопросы риторические вопросы от учеников Иллюстрации аудио-, видеоклипы и мультимедиа фотографии, картинки графики, таблицы, схемы юмор, анекдоты, приколы, комиксы притчи, поговорки, кроссворды, цитаты Дополнения рефераты статьи фишки для любознательных шпаргалки учебники основные и дополнительные словарь терминов прочие Совершенствование учебников и уроков исправление ошибок в учебнике обновление фрагмента в учебнике элементы новаторства на уроке замена устаревших знаний новыми Только для учителей идеальные уроки календарный план на год методические рекомендации программы обсуждения Интегрированные уроки

Тема: Практическая работа №17

« Расчет корреляционных зависимостей в Microsoft Excel»

Тип урока: практическая работа

Цели:

Получение представления о корреляционной зависимости величин;

Освоение способа вычисления коэффициента корреляции с помощью функции КОРРЕЛ;

Формирование навыка по работе в MS Excel;

Развитие системного мышления, позволяющего выделять в окружающей действительности системы, элементы систем, адекватные поставленной задаче;

Формирование профессиональных навыков работы.

Оборудование:

Интерактивная доска;

Ход урока:

I. Организационный момент (5 мин.)

Приветствие. Сообщение темы.

II. Актуализация знаний (5 мин.)

Проверка домашнего задания.

III. Практическая работа (30 мин.)

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

Задание 1

Требуется выполнить расчеты корреляционной зависимости успевае-мости учащихся от хозяйственных расходов школы, описанные в § 38 учебника.

1. Заполнить электронную таблицу следующими данными:

А	В	С
№ п/п	Затраты (руб./чел.)	Успеваемость (средний балл)
		3,81
	345	4,13
		4,30
	100	3,96
	203	3,87
	420	4,33
	210
	137	4,21
	463	4,4
	231	3,99
	134	3,9
	100	4,07
	294	4,15
	396	4,1
		3,76
	480	4,25
	450	3,88
	496	4,50
	102	4,12
	150	4,32

2. Построить точечную диаграмму зависимости величин (ее вид показан в учебнике на рис. 6.7).

3. Выполнить статистическую функцию КОРРЕЛ, указав в диалоговом окне диапазоны значений: В2:В21 и С2:С21.

4. Выписать значение коэффициента корреляции.

Задание 2

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

	Обеспечение учебного процесса
Номер школы	Обеспеченность учебниками (%)	Успеваемость (средний балл)	Обеспеченность компьютерами (%)	Успеваемость (средний балл)
		3,81		3,98
		4,15		4,01
		4,69		4,34
		4,37		4,41
		4,53		3,94
		4,23		3,62
	100	4,73		4,6
		3,69		4,24
		4,08		4,36
		4,2		3,99
		4,32		4,5

Полученные значения коэффициентов корреляции сопоставить с приведенными в § 38 учебника.

Задание для самостоятельного выполнения по теме

«Корреляционные зависимости»

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

Примерами соответствующих связанных величин могут служить:

уровень образования (измеренный, например, в годах обучения в целом) и уровень месячного дохода;

уровень образования и уровень занимаемой должности (для последней придумайте условную шкалу);

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

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

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

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

IV . Итог урока (2 мин.) Объявляются оценки.

V . Домашнее задание (3 мин.) Повторить § 38

В первом классе их вообще нет, во 2-3 это полтора часа, в 4-5 классах - два часа, в 6-8 - два с половиной часа, а с 9-го по 11-й класс ученик должен тратить на домашнюю работу не больше 3,5 часа в день. При этом трудные учебные предметы, по которым в школе обычно много задают, не должны стоять в расписании в один день. Проще говоря, не может в один день быть химия, биология, физика, математика.

Это же самое, к слову, говорится в СанПиНах, которые вступили в силу еще в 2011 году. Зачем понадобилось снова напоминать школам правила? В минобрнауки "РГ" рассказали, что в последнее время в министерство поступает много жалоб от родителей на большой объем домашних заданий и нагрузку в школе.

Министерство вынесло на общественное обсуждение проект изменений в Порядок организации и осуществления образовательной деятельности, прописав в нем требования, утвержденные СанПиНом, чтобы обратить особое внимание школ на объем допустимых нагрузок, - пояснили "РГ" в минобрнауки.

Проблемы с "домашкой" действительно есть. Между школами идет конкуренция за гранты, субсидии, результаты ЕГЭ и место в рейтингах. В этом году обязательный мониторинг пройдет даже в начальной школе. Естественно, никому не хочется быть в двоечниках. Учителя утроили усилия на уроках и прибавили домашнее задание.

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

Иногда учитель думает: чем больше он задает, тем лучше. Домашние задания нужны, но нужна и их регламентация. Полно учителей, которые бессмысленно много нагружают детей. Не должен ребенок сидеть за уроками по шесть часов в день! - высказывает мнение заместитель директора знаменитой московской школы N57 Борис Давидович.- Но я бы определял домашнее задание не по времени, а по объему материала: сделали в классе четыре примера - на дом больше шести давать нельзя.

В 57-й физико-математической школе задачу на дом ученик может решать целый день или неделю. "А иногда и всю жизнь!" - шутит Борис Михайлович.

Мы перешли на пятидневку, чтобы разгрузить детей, - говорит директор школы N17 Брянска Ирина Голикова, - стараемся на выходные дни давать меньше заданий. Вторая смена учится до семи часов вечера, и уроки обычно дети делают утром". "Получается, без родителей? - уточняю. - Как это сказывается на успеваемости?" - "У нас одна из лучших школ!"

А так ли уж нужны домашние задания? Когда-то в XIX веке по этому поводу уже разгорался спор, но "домашка" устояла. В 1917 году ее отменили и вернулитолько в 30-х годах прошлого века. Сейчас некоторые эксперты снова задаются вопросом: если мы говорим о переходе на школу полного дня, отказываясь от жестких рамок ведения урока, может, действительно пора отменить и домашнее задание?

Ирина Ильина, мама третьеклассника, профессор РГСУ:

Сын тратит на уроки столько же времени, сколько некоторые студенты на подготовку к семинару - около двух часов. Но семинар - раз в неделю, а уроки - каждый день.

Корреляционные зависимости

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

Мы рассмотрим два типа задач – требуется определить:

1. оказывает ли фактор В какое-либо заметное регулярное влияние на фактор А;

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

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

Итак, пусть хозяйственные расходы школы выражаются количеством рублей, отнесенных к числу учеников в школе (руб/чел.), потраченных за определенный период времени (например, за последние 5 лет). Успеваемость же пусть оценивается средним баллом учеников школы по результатам окончания последнего учебного года. Еще раз обращаем ваше внимание на то, что в статистических расчетах обычно используются относительные и усредненные величины.

Итоги сбора данных по 20 школам, введенные в электронную таблицу, представлены на рис. 1. На рис. 2 приведена точечная диаграмма, построенная по этим данным.


Рис. 1 Статистические данные	Рис. 2 Точечная диаграмма

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

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

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

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

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

· если это число по модулю близко к 1, то имеет место сильная корреляция, если к 0, то слабая;

· близость ρ к +1 означает, что возрастанию одного набора значений соответствует возрастание другого набора, близость к -1 означает обратное;

· значение ρ легко найти с помощью Excel (встроенные статистические функции).

В Excel функция вычисления коэффициента корреляции называется КОРРЕЛ и входит в группу статистических функций. Покажем, как ей воспользоваться. На том же листе Excel, где находится таблица, представленная на рис. 1, надо установить курсор на любую свободную ячейку и запустить функцию КОРРЕЛ. Она запросит два диапазона значений. Укажем Затраты и Успеваемость. После их ввода выведется ответ: ρ = 0,. Эта величина говорит о среднем уровне корреляции.

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

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

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

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

Коротко о главном

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

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

Количественной мерой корреляции двух величин является коэффициент корреляции.

Значение коэффициента корреляции лежит между -1 и +1. Чем ближе его значение по модулю к 1, тем корреляция (связь) сильнее.

В MS Excel для определения коэффициента корреляции используется функция КОРРЕЛ из группы статистических функций.

Вопросы и задания

1. Что такое корреляционная зависимость?

2. Что такое корреляционный анализ?

3. Какие типы задач можно решать с помощью корреляционного анализа?

4. Какая величина является количественной мерой корреляции? Какие значения она может принимать?

5. С помощью какого средства табличного процессора можно вычислить коэффициент корреляции?

6. Для данных из таблицы, представленной на рис. 3, постройте две линейные регрессионные модели.

7. Для этих же данных вычислите коэффициент корреляции. Сравните с приведенными на рис. 3 результатами.

Компьютерный практикум «Расчет корреляционных зависимостей в MS Excel»

Цели работы: получение представления о корреляционной зависимости величин; освоение способа вычисления коэффициента корреляции с помощью функции КОРРЕЛ.

Используемые программные средства: табличный процессор MS Excel.

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

Выполнить следующее:

Þ построить с помощью точечную диаграмму, визуально отображающую табличную зависимость;

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

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

Þ используя функцию КОРРЕЛ, найти коэффициент корреляции и подтвердить или опровергнуть указанную гипотезу.

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

Примерами соответствующих связанных величин могут служить:

ü уровень образования (измеренный, например, в годах обучения в целом) и уровень месячного дохода;

ü уровень образования и уровень занимаемой должности (для последней придумайте условную шкалу);

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

ü число часов, затрачиваемых старшеклассником на выполнение домашних заданий, и средняя оценка;

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

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

КАТЕГОРИИ