Материал: _индив анализ данных

2

Министерство образования и науки ДНР

ГОУ ВПО «Донецкая академия управления и государственной службы при Главе ДНР»

Кафедра информационных технологий

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

к выполнению индивидуальной работы

«Анализ данных в MS EXCEL»

Донецк

20_____

Цель работы: усвоить принципы работы с пакетом «Анализ данных» в MS Excel и его основными элементами.

Методические указания к выполнению индивидуальной работы

1. Описательная статистика в ms Excel

Задача 1. Товарная биржа в августе текущего года проводит фьючерсные торги^** на сахар, который поступит с сахарных заводов в ноябре текущего года. По результатам торгов установлена цена 1850 грн/т. Компании «Альфа» необходимо принять решение о приобретении партии сахара, если известна динамика цен на сахар за последние 8 месяцев.

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

Для расчета числовых характеристик нужно с помощью команды Сервис – Анализ данных^* вызвать окно, отображенное на рис.1, в котором выбирается команда Описательная статистика.

Рис. 2.

Результат расчета приведен на рис. 3.

Рис. 3.

2. Корреляционный и регрессионный анализ в ms Excel

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

Вычисления коэффициента корреляции

Коэффициент корреляции r характеризует степень приближения зависимости между случайными величинами к линейной функциональной зависимости. Коэффициент корреляции принимает значения –1  r  1. Коэффициент корреляции может быть равен –1 или 1, только если величины линейно зависят друг от друга. Величина r, близкая к –1 или 1, указывает, что зависимость между данными величинами почти линейная. Значения r, близкие к нулю, означают, что связь между данными величинами либо слабая, либо не носит линейного характера. Если r>0, то связь между переменными прямая. При r<0 связь обратная.

Для примера рассмотрим задачу 2.

Задача 2. Дана информация о динамике некоторых экономических показателей за 8 лет. Необходимо оценить взаимосвязь и взаимное влияние этих показателей.

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

Таблица входных данных имеет вид, представленный на рис. 4.

Рис. 4.

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

Рис. 5.

Рис. 6.

Наиболее информативной для решения задачи 2. является квадратная матрица . Из нее следует:

• величина платы за электроэнергию прямо пропорциональна тарифу на элктроэнергию (коэффициент корреляции равен 1)^*;

• цены на уголь и сталь в значительно степени связаны с величиной тарифа на электроэнергию (коэффициенты корреляции соответственно равны 0,93 и 0,91);

• цена на рожь связана с величиной тарифа на электроэнергию в значительно меньшей степени (коэффициент корреляции 0,49);

• высока степень связи цен на уголь и сталь (коэффициент корреляции равен 0,93);

• практически не связаны цены на рожь с ценами на сталь и уголь (коэффициенты соответственно равны 0,34 и 0, 24).

Парная линейная регрессия

Возвратимся к задаче 1.

Найдём линейную зависимость цены от месяца.

Для решения задачи 1 средствами MS Excel составляем таблицу исходных данных (рис.7.) и вызовем окно Анализ данных (рис. 1.), где выбираем раздел Регрессия (рис. 8.). Параметры Входной интервал Y и Входной интервал X представляют собою зависимую и независимую переменные уравнения линейной регрессии .

Для записи уравнения линейной регрессии в виде y=ax+b выберем параметры a и b из рис. 9: коэффициент при неизвестной a – на пересечении столбца Коэффициенты и строки с наименованием № месяца, свободный член b – на пересечении столбца Коэффициенты и строки Y-пересечение. Таким образом, уравнения регрессии для задачи 3 запишется в таком виде:

Цена на сахар = 11,714№ месяца + 1727,54.

или

y = 11,714 x + 1727,54

Рис. 9

Для анализа адекватности полученного уравнения линейной регрессии в MS Excel используются параметры (см. рис. 9): коэффициент множественной корреляции (множественный R); коэффициент детерминации (R-квадрат); критерий Фишера (F-статистика); критерий Стьюдента (t-статистика).

Коэффициент множественной корреляции R позволяет оценить тесноту вероятностной связи между зависимой и независимой переменными. Высокое значение этой величины свидетельствует о сильной связи между переменными (при равенстве R=1 – связь функциональная). Таким образом, величина R=0,921 для задачи 1 говорит о высокой степени связи переменных МЕСЯЦ и САХАР. Однако, характер этой связи пока неясен.

Величина квадрата коэффициента множественной корреляции или коэффициент детерминации R²(RI) показывает долю общего разброса (относительно выборочного среднего зависимой переменной), которая объясняется построенной регрессией. Иными словами данная величина показывает, разброс какой части полученных экспериментальных данных (зависимая переменная) соответствует полученному уравнению линейной регрессии. В задаче 1 эта величина составляет 84,8%, что говорит о том, что имеющиеся статистические данные с достаточно высокой степенью точности могут быть описаны полученным уравнением регрессии.

F-статистика (критерий Фишера) используется для оценки значимости полученной линейной зависимости, иными словами он подтверждает или опровергает гипотезу о существовании линейной зависиомсти. Если полученное значение F-критерия выше критического F_кр, то гипотеза о незначимости линейной зависимости отвергается. Величина F_кр должна быть получена из специальных таблиц в соответствии с числом степеней свободы df. Однако в окне рис. 9 приведено значение величины p – уровня значимости, показывающего вероятность непринятия верной гипотезы. Иными словами, если выдвинута гипотеза о незначимости линейной зависимости, то если уровень значимости p стремится к единице, то между переменными (зависимой и независимой) не существует линейной зависимости (гипотеза принимается), и, наоборот, если p стремится к нулю, то гипотеза отвергается (между переменными существует линейная зависимость). Для задачи 1 F=33,571; df=1,6; p=0,00116, то есть вероятность отвергнуть верную гипотезу о незначимости коэффициента при независимой переменной составляет всего 0,116%.

Значение t-статистики (критерий Стьюдента) используется для оценки значимостей коэффициента при неизвестной и свободного члена полученной линейной зависимости. Если полученное значение t-критерия выше критического t_кр, то гипотеза о незначимости свободного члена линейной зависимости отвергается. Аналогично как уровню значимости F-критерия, для t-критерия также приведено значение величины p – уровня значимости, показывающего вероятность непринятия верной гипотезы. В задаче 1 для свободного члена t=169,20903; p=2,89Е-12, то есть вероятность отвергнуть верную гипотезу о незначимости cвободного члена практически равна нулю. Для коэффициента при неизвестной t=5,79405; p=0,001158, то есть вероятность отвергнуть верную гипотезу о незначимости коэффициента при неизвестной составляет 0,12%.

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

Множественная линейная регрессия в ms Excel

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

Рассмотрим следующую задачу.

Задача 3. Инвестиционная компания «Аргон-Инвест» рассматривает инвестиционный проект, связанный с покупкой 20%-ного пакета акций АО «N-ский металлургический комбинат». Стоимость пакета C_П составляет 70 млн. USD. Менеджерами компании собрана информация об аналогичных сделках. Для оценки стоимости пакета акций ими выбраны следующие параметры:

• стоимость основных фондов предприятия C_ОФ, млн. USD;

• объем годового оборота предприятия V_О, млн. USD;

• кредиторская задолжненность предприятия V_К, млн. USD;

• дебиторская задолженность предприятия V_Д, млн. USD;

• задолженность предприятия по заработной плате V_ЗП, тыс. USD.

Для решения задачи 3 средствами MS Excel составляем таблицу исходных данных (рис. 10) и вызовем окно Анализ данных (рис. 1), где выбираем раздел Регрессия (рис. 11). Параметры Входной интервал Y и Входной интервал X представляют собою зависимую и независимые переменные уравнения множественной линейной регрессии. Результаты расчетов приведены на рис. 12.

Рис. 10

Рис. 11

Рис. 12

По этим результатам может быть построено следующее уравнение регрессии:

CP = 0,103CO + 0,541VO – 0,031VK +0,405VD +0,691VZP – 265,844

или

y = 0,103x1 + 0,541x2 – 0,031x3 +0,405x4 +0,691x5 – 265,844

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

Подставив соответствующие данные для N-го металлургического комбината (таблица 1) в полученное уравнение регрессии получаем искомое значение стоимости пакета акций.

Таблица 1