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

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

• А) Вводим многомерную выборку своего задания в смежный диапазон ячеек рабочего листа электронной таблицы (ЭТ). Результативный признак разместить после последнего факторного признака.
• Б) Для каждого фактора определяем подозрительные значения, используя функцию 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%) для этих коэффициентов. Следовательно, нулевая гипотеза о том, что параметры регрессии могут принимать нулевые значения отвергается.

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

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

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

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

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

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

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

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). Так как параметры регрессии могут принимать любые значения, то ограничения и граничные условия в математической модели оптимизации отсутствуют.

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

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

Yx = 31,5793 — 0,5 522×2- 0,8×22.

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

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

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

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

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

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

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

(28).

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

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

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

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

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=.

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

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

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