Разработка массивов в Exсel

Размещено на http://www.allbest.ru/

Производим контроль исходной информации на наличие грубых ошибок и выбросов:

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

Б) Для каждого фактора определяем подозрительные значения, используя функцию min, max.

Определяем Х1 в ячейку В22=МАКС(В2:В21), а затем курсором протягиваем формулу до ячейки Е22.

Аналогично определяем функцию МИН для каждого фактора. Для Х1 в ячейку В23 =МИН(В2:В21), протягиваем курсором до ячейки Е23.

Подозрительными значениями будут :

для Х1 - 71,1; 30,8

для Х2 - 442,3; 14,7

для Х3 - 214,3; 0,1

для Y - 38,5; 3,5.

В) Копируем данные, которые оказались под подозрением в отдельную область таблиц и сортируем в порядке возрастания, чтобы экстремальное значение оказалось на месте первого элемента массива. Обозначим первый элемент массива - Х1.

Г)Для выборки малого размера объема (n?25) определяем расчетное значение по формуле:

М=

Где еxсel массив гистограмма регрессия

Х1- это экстремальное значение;

n- последний отсортированный элемент.

Определяем выброс для х1:

Рассчитываем Мrверхнее = 30,8-33,1/30,8-60,8=0,0766.

Расчетное значение М сравниваем с критическим при заданном уровне значимости Мkr20=0,45. Так как Мр. верхнее меньше критического, то в данном случаи выбросов нет.

Mrn=71,7-60,8/71,7-33,1=0,2711

Расчетное значение М сравниваем с критическим при заданном уровне значимости Мkr20=0,45. Так как Mrn меньше критического, то в данном случаи выбросов нет.

Определяем выброс для х2:

Рассчитываем Мr верхнее = 14,7-23,1/14,7-442,3=0,0402

Расчетное значение М сравниваем с критическим при заданном уровне значимости Мkr20=0,45. Так как Мр. верхнее меньше критического, то в данном случаи выбросов нет.

Mrn=442,3 - 223,6/442,3 - 23,1=0,5217

Расчетное значение М сравниваем с критическим при заданном уровне значимости Мkr20=0,45. Так как Mrn больше критического, то строку с этим значением в исходной таблице убираем, так как оно является выбросом.

Определяем Мr верхнее2 = 14,7-23,1/14,7-200,4=0,045

Рассчитываем Mrn2=282,2-200,4/282,2-23,1=0,316

Так как Мr верхнее2, Mrn2 меньше Мkr19=0,462, то в данном случаи выбросов нет.

Определяем выброс для х3:

Рассчитываем Мrверхнее = 0,1-4,4/0,1-104,1=0,041

Расчетное значение М сравниваем с критическим при заданном уровне значимости Мkr20=0,45. Так как Мр. верхнее меньше критического, то в данном случаи выбросов нет.

Mrn=214,3-104,1/214,3-4,4=0,525

Расчетное значение М сравниваем с критическим при заданном уровне значимости Мkr20=0,45. Так как Mrn больше критического, то строку с этим значением в исходной таблице убираем, так как оно является выбросом.

Определяем Мr верхнее2 = 0,1-4,4/0,1-97,3=0,044

Рассчитываем Mrn2=104,1-93,7/104,1-4,4=0,068

Так как Мr верхнее2, Mrn2 меньше Мkr18=0,475, то в данном случаи выбросов нет.

Д) Расчетное значение сравнили с критическим при заданном уровне значимости.

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

В данном случаи значения: 442,3, 214,3, 202 являются выбросом. Строку с этим значением в исходной таблице убираем.

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

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

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

Инструмент «Описательная статистика» позволяет получить 13 статистических характеристик для десяти и более массивов переменных. Массивы на рабочем листе ЭТ должны быть размещены в смежном диапазоне ячеек, иметь одинаковый размер и заголовки в первой строке.

Для статистической обработки массивов следует выполнить последовательность операций Сервис Анализ данныхОписательная статистика. В диалоговом окне «Описательная статистика» заполняем поля:

1. входной интервал - указываем диапазон ячеек, занимаемых элементами многомерной выборки вместе с заголовками;

2. указываем метки в первой строке;

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

4. щелкаем мышью в квадратике «Итоговая статистика».

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

Функция МОДА возвращает наиболее часто встречающееся или повторяющееся значение в массиве или интервале данных. Если множество данных не содержит одинаковых данных, то функция МОДА возвращает значение ошибки #Н/Д.

3. Для результативного признака строим гистограмму Таблица 5. с помощью инструмента «Гистограмма» пакета анализа ЭТ и убедимся, что он подчиняется нормальному закону распределения.

Гистограмма строится для одного из признаков, например, результативного, с помощью инструмента «Гистограмма». Числовые характеристики для всех исследуемых признаков определяются с помощью инструмента «Описательная статистика» пакета анализа. Диалоговое окно «Гистограмма». Для построения гистограммы следует установить курсор на свободную ячейку, войти в меню Сервис, выбрать операцию Анализ данных и в появившемся диалоговом окне выбрать инструмент пакета анализа Гистограмма.

С помощью инструмента «Корреляция» пакета анализа ЭТ получаем корреляционную матрицу многомерной выборки.

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

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

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

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

Для построения диаграммы Y от х1 выделяем диапазон ячеек х1 и Y, а затем используем Мастер диаграмм>Точечная>Ok.

Для построения диаграммы Y от х2 выделяем диапазон ячеек х2 и Y, а затем используем Мастер диаграмм>Точечная>Ok.

Для построения диаграммы Y от х3 выделяем диапазон ячеек х3 и Y, а затем используем Мастер диаграмм>Точечная>Ok.

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

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

Сервис> пакет анализа > корреляция

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

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

При анализе тесноты связи, кроме коэффициента корреляции, рассмотрим коэффициент детерминации R2

R2=r2xy

Он показывает долю изменения результативного признака под влиянием факторного

R2=0,5995

На 59,95% вариация зависит от вариации изминения х (факторный признак).

Выполняем парный регрессионный анализ, включяя в математическую модель результативный признак и наиболее значимый по тесноте связи факторный признак. Уравнение линейной регрессии получить с помощью инструмента «Регрессия» пакета анализа ЭТ. Уравнение нелинейной регрессии получить, используя метод наименьших квадратов и надстройку «Поиск решения». Оцениваем значимость коэффициентов уравнения линейной регрессии и самого уравнения по соответствующим критериям. Анализ качества уравнения нелинейной регрессии выполнить по критерию Фишера и коэффициенту парной корреляции. Выполняем точечный и интервальный прогноз на основе полученной модели.

Запишем математическую модель парной линейной регрессии в виде

Y=b0+b1x



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

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

Уравнение парной линейной регрессии будет иметь вид:

Yx=a0+a1x

Параметры регрессии определим с помощью инструмента «Регрессия» пакета анализа.

Приведена следующая информация:

ячейка В4 - множественный коэффициент корреляции (в данном случае это коэффициент парной корреляции );

ячейка В5 - коэффициент детерминации

ячейка В5 - нормированный коэффициент детерминации, определяемый по формуле

где - объем выборки, - число неизвестных параметров уравнения регрессии. Коэффициент детерминации корректируется с учетом числа факторных признаков.

ячейка В7 - стандартная ошибка ;

ячейка В8 - объем выборки ;

ячейка В12 - число степеней свободы для определения критического значения критерия Фишера;

ячейка В13 - число степеней свободы для определения критического значения критерия Фишера;

ячейка С12 - сумма квадратов разностей между расчетными значениями и средним значением результативного признака т.е. сумма квадратов, объясняемая регрессией ( );

ячейка С13 -остаточная сумма квадратов, т.е. сумма квадратов отклонений ;

ячейка D13 - остаточная дисперсия

ячейка Е12 - расчетное значение критерия Фишера;

ячейка В17 - коэффициент уравнения регрессии;

ячейка В18 - коэффициент уравнения регрессии;

ячейка С17 - стандартная ошибка коэффициента ;

ячейка С18 - стандартная ошибка коэффициента ;

ячейка D17 - расчетное значение статистики коэффициента

ячейка D18 - расчетное значение статистики коэффициента ;

ячейка Е17 - значение коэффициента ;

ячейка Е18 -значение коэффициента ;

ячейки F17:F18 - нижние границы доверительных интервалов соответствующих коэффициентов уравнения регрессии;

ячейки G17:G18 -верхние границы доверительных интервалов этих коэффициентов.

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

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

отвергнуть нулевую гипотезу, если

Следовательно, уравнение парной линейной регрессии имеет вид:

Yx = 44,2814 - 0,3766х1 - 0,0931х2+0,1563х3 (23)

Выполним анализ качества полученной модели регрессии:

R2 = 0,4079 следовательно, только 40,8% дисперсии рентабельности объясняется влиянием факторного признака «премии и вознаграждения», т.е. необходимо включить в математическую модель регрессии другие факторные признаки и выполнить многомерный регрессионный анализ;

Fрасч.=0,055. Критическое значение критерия Фишера при m1=3; m2=14; б=0,05; F= 3,2156. Следовательно, уравнение регрессии (23) в целом статистически значимо, т.е. имеется хорошее соответствие данным наблюдений;

Критическое значение - статистики. Для коэффициентов уравнения регрессии расчетные значения - статистики соответственно равны 4,3396, -1,5867, -2,1331, 1,6898 т.е. оба коэффициента регрессии статистически значимы. Об этом же свидетельствуют: - значение (<0,05) и доверительные интервалы (нижние 95% и верхние 95%) для этих коэффициентов. Следовательно, нулевая гипотеза о том, что параметры регрессии могут принимать нулевые значения отвергается.

Выполняем многомерный линейный регрессионный анализ с помощью инструмента «Регрессия» пакета анализа. В математическую модель включаем все независимые между собой факторные признаки. Оцениваем значимость каждого коэффициента уравнения регрессии и уравнения в целом. Выполняем точечный и интервальный прогноз на основе полученной модели.

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

Математическая модель многомерной линейной регрессии:



(26)

Математическая запись метода наименьших квадратов:

(27)

Коэффициенты регрессии определяем с помощью инструмента «Регрессия» пакета анализа.

Анализ результатов расчета:

Уравнение многомерной линейной регрессии

Yx = 44,2814 - 0,3766х1 - 0,0931х2+0,1563х3

Критическое значение - статистики равно 2,009. Для коэффициента регрессии при факторном признаке расчетное значение - статистики меньше критического и Р - значение больше 0,05, т.е. этот коэффициент статистически не значим и он может принимать нулевые значения.

Критическое значение критерия Фишера равно 10,2038. Расчетное значение критерия больше критического, следовательно, уравнение регрессии в целом статистически значимо и его можно использовать для прогноза.

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

Точечный прогноз: среднее значение рентабельности при и равно 19,4488 %.

Интервальный прогноз: с вероятностью 95% рентабельность предприятия будет находиться в пределах от 22,3962 до 66,1665 %.

9. Парный нелинейный регрессионный анализ

Запишем уравнение парной нелинейной регрессии

Yx=a0+a1x2+a2x22 (24)

Для этого случая математическая запись метода наименьших квадратов имеет вид:

(25)

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

Анализ результатов расчета.

Уравнение парной нелинейной регрессии

Yx = 31,5793 - 0,05522 x2- 0,00008x22

Коэффициент парной корреляции ryY=0,4667 т.е. связь между фактическими и теоретическими значениями результативного признака умеренная.

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

Точечный прогноз: среднее значение рентабельности при равно 20,147 %.

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

Полученная величина показывает, какой бы была рентабельность предприятия при премиях 1,23%, если бы данное предприятие использовало свои производственные возможности в такой степени, как в среднем все предприятия. Фактическое значение рентабельности первого предприятия Следовательно, первое предприятие использует свои возможности хуже, чем в среднем все исследуемые предприятия.

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

Запишем математическую модель регрессии

(28)

Математическую запись метода наименьших квадратов представим в виде:

(29)

Параметры регрессии определяем в среде ЭТ с помощью надстройки «Поиск решения».

Анализ результатов расчета.

Уравнение многомерной нелинейной регрессии

Yx=-0,95+0,0003х2-0,0009х3+0,0002х22+0,0111х2х3-0,0198х32

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

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

Точечный прогноз: среднее значение Y при х2=158,9 и х3=81,8 равно -0,1861%.

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

Целевая функция

Yx=-0,95+0,0003х2-0,0009х3+0,0002х22+0,0111х2х3-0,0198х32=

Ограничения:

Граничные условия:

Так как целевая функция нелинейная, то имеем задачу нелинейного программирования. Для ее решения используем надстройку «Поиск решения».

Размещено на Allbest.ru

...