Расширенный курс Excel 2007

Основы корреляционного анализа

Графическое представление рядов распределения

Пример дискретного ряда распределения

Число на игровой кости

1

Количество

2

выпадений

50

3

43

4

51

5

47

6

39

53

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

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

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

Пример: Заболеваемость астмой

Концентрация угарного газа, мг/м3

Количество хронически больных астмой на 1000 жителей

1,20

20

2,40

2,56

35

42

3,10

3,50

48

51

4,20

4,80

59

63

Графический анализ корреляционной связи

Линия тренда

Диаграмма рассеивания

Факторный признак

Результативный признак

Пример прямой корреляционной связи

Пример обратной корреляционной связи

Отсутствие корреляционной связи

Коэффициент корреляции

Качество связи между двумя величинами можно выразить с помощью коэффициента корреляции . Это число k из интервала [-1;1]. Если k близко к -1, то корреляционная связь между величинами является обратной , а если k близко к 1 – прямой . Чем ближе k к нулю, тем корреляционная связь слабее. Если говорить более основательно, то прочность линейной корреляционной связи оценивается так:

|k|  0,8 – сильная корреляционная связь;

0,4  |k|

|k|

В МS Excel для вычисления коэффициента корреляции используется функция КОРРЕЛ : КОРРЕЛ(диапазон_1; диапазон_2) , где диапазон_1 диапазон_2 содержат наборы значений, между которыми определяется зависимость.

Решение задач на подбор параметра. Понятие о численных методах решения задач

Основные группы методов решения математических задач:

• аналитические (с помощью формул);
• графические (решение находится путем геометрических построений);
• Численные (позволяют свести решение задачи к выполнению конечного числа арифметических действий над числами)

Численные методы решения задач

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

Численные методы решения задач

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

Численные методы решения задач

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

• В табличном процессоре Excel есть специальные средства, которые автоматически подбирают нужные значения в ячейках параметров. Они называются Подбор параметра ( Данные – Работа с данными - Анализ «что-если» – Подбор параметра ) и Поиск решения .

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

Подбор параметра Задача: Решить уравнение x – 5 = 0 .

В одну из ячеек ЭТ введите формулу целевой функции.

Х

Данные – Анализ «что-если» – Подбор параметра

Задание на дом

• Стр. 178-181, № 3, стр. 188

Регрессионный анализ

Вопрос: Каким будет уровень заболеваемости астмой, если концентрация угарного газа будет составлять 10 мг/м 3 ?

Основная задача регрессионного анализа – прогнозирование .

Основной метод прогнозирования – построение на основе выборочных данных уравнения регрессии вида у = f(x) , связывающего факторный и результативный признаки.

Линия тренда – это и есть график уравнения регрессии .

Автоматическое построение линии тренда

1. Выделить диаграмму рассеивания.

2. Работа с диаграммами – Макет – Анализ - Линия тренда – Дополнительные параметры линии тренда.

3. В окне Формат линии тренда выбрать тип зависимости – линейная .

4. Установить флажки «показывать уравнение на диаграмме» и «поместить на диаграмму величину достоверности апроксимации (R^2);

5. Задать величину прогноза.

При концентрации угарного газа 10 мг/м 3 уровень заболеваемости  120 людей на 1000 жителей.

Коэффициент детерминации

Близость уравнения регрессии и линии тренда к выборочным данным характеризуется величиной коэффициента детерминации R 2

(0 ≤ R 2 ≤ 1).

Уравнение регрессии наиболее отвечает действительности, когда R 2 приближается к своему максимальному значению.

Точность полученных результатов и разрядность представления результатов

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

9 цифр

Пример .

Прочитать условие примера из конспекта.

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

Не путайте округленное значение с отформатированным.

Цена указана в рублях без копеек.

Цена на товар стоимостью $2 составила 56,16 руб (2 х 28,06 = 56,16).

Цена на товар стоимостью $3 составила 84,24 руб (3 х 28,06 = 84,24).

Панель функции ОКРУГЛ

Синтаксис:

ОКРУГЛ(число; количество_цифр)

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

конспект