Анализ данных в Excel

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

Федеральное государственное образовательное

бюджетное учреждение высшего профессионального образования

«ФИНАНСОВЫЙ УНИВЕРСИТЕТ

ПРИ ПРАВИТЕЛЬСТВЕ РОССИЙСКОЙ ФЕДЕРАЦИИ»

Kафедра «Математика и информатика»

Факультет Менеджмент

Направление подготовки Маркетинг

КОНТРОЛЬНАЯ РАБОТА

по дисциплине «Анализ данных в Excel»

Вариант № 5

Студент Устеменко У.А

Курс 1 № группы 2

Преподаватель Дюдин М.С

Краснодар 2015

СОДЕРЖАНИЕ

выборка дисперсия корреляция регрессионный дисперсионный

1. Подсчёт выборочного среднего представленной выборки значений

2. Понятие дисперсии и вычисление стандартного отклонения

3. Генерация нормально распределённой случайной величины с данным средним значением и стандартным отклонением. Построение гистограммы, иллюстрирующей плотность распределения

4. Понятие коэффициента корреляции и его возможные значения

5. Подсчёт коэффициента корреляции данных выборок случайных величин

6. Понятие регрессионного анализа, его предпосылки. Модель однофакторного регрессионного анализа

7. Проведение регрессионного анализа заданных величин

8. Проведение двухфакторного регрессионного анализа заданных величин

9. Цель применения дисперсионного анализа. Соотношение межгрупповой и внутригрупповой дисперсии при наличии влияния фактора

1. Дана выборка значений случайной величины:

Посчитайте выборочное среднее.

Решение:

Для начала откроем программу Microsoft Excel, создадим пустой лист и введём в таблицу данные в условиях параметры X. Далее, для подсчёта выборочного среднего значения, воспользуемся функцией СРЗНАЧ, которую можно найти во вкладке Формулы:

Затем, в открывшемся диалогом окне поочерёдно в поля чисел вводим заданные параметры X:

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

2. Что такое выборочная дисперсия? Как считается стандартное отклонение? Посчитайте стандартное отклонение и дисперсию для предыдущего примера.

Решение:

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

Для вычисления стандартного отклонения заданных параметров из задания №1 воспользуемся формулой «СТАНДОТКЛОН» в меню функций:

В открывшемся диалоговом окне поочерёдно в поля чисел вводим заданные параметры X:

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

Далее вычислим дисперсию выборки. В меню функций находим формулу «ДИСП» и, поочерёдно заполнив числовые поля данными значениями, вычисляем дисперсию выборки, в итоге имеем:

С помощью данных функций в Excel можно определить общие характеристики выборки случайных величин.

3. Сгенерируйте нормально распределенную случайную величину со средним значением 5 и стандартным отклонением 2,5. (N=50). Постройте гистограмму, иллюстрирующую плотность распределения.

Решение:

Для генерации нормально распределённой случайной величины используем пакет «Анализ данных» в Excel и выберем необходимый инструмент анализа, а именно «Генерация случайных чисел»:

Далее, в открывшемся окне вводим следующие параметры: в поля «среднее» и «стандартное отклонение» вводим данные значения, тип распределения выбираем «нормальный», число переменных устанавливаем равным 1, а число случайных чисел равное 50, для того, чтобы в последующем построить более подробную гистограмму плотности распределения:

В итоге получаем столбец значений из 50 случайных чисел:

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

В результате мы получаем гистограмму наших сгенерированных нормально распределённых случайных величин, которая демонстрирует плотность полученного распределения:

4. Что показывает коэффициент корреляции, какие значения он может принимать?

Решение:

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

5. Даны две выборки случайных величин. Посчитайте их к-т корреляции, сделайте вывод о степени их коррелированности.

Решение:

Для начала создаём новый лист в нашей книге Excel и заполняем его данными, указанными в задании:

Далее для вычисления коэффициента корреляции воспользуемся меню формул и выберем функцию «КОРРЕЛ»:

Для поля Массива1 выделяем значения X, а для Массива2 - значения Y:

В результате получаем значение коэффициента, равное 0,5294, что не совсем удобно для точного определения положения полученного результата в диапазоне значений коэффициента корреляции, и чтобы округлить число до сотых, меняем формат ячейки нашего результата на числовой:

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

6. Для чего применяется регрессионный анализ? При каких результатах корреляционного анализа есть смысл проводить регрессионный анализ? Перечислите 4 предпосылки регрессионного анализа, напишите модель однофакторного регрессионного анализа.

Решение:

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

Регрессионному анализу свойственны определённые предпосылки, он имеет место в тех случаях, когда:

1) в линейной парной регрессионной модели (Y_i = в₀ + в₁x + е_i) зависимая переменная Y_i является случайной величиной, а объясняющая переменная X - неслучайной;

2) математическое ожидание возмущения е_i линейной парной регрессионной модели равно 0;

3) Дисперсия возмущения е_i (или зависимой переменной Y_i ) постоянна для любого i;

4) возмущения е_i и е_j или зависимые переменные Y_i и Y_j не коррелированы;

5) возмущение е_i (или зависимая переменная Y_i) есть нормально распределённая случайная величина.

Для получения уравнения регрессии достаточно первых четырёх предпосылок.

Модель однофакторного регрессионного анализа в Excel состоит из двух выборок - выборки значений зависимой переменной Y и выборки значений независимой переменной X. В однофакторном регрессионном анализе предполагается, что переменная Y определяется только одной независимой переменной (одним фактором).

7. Проведите регрессионный анализ для данных из вопроса №5, взяв зависимой переменной Y, зависимой - X. Напишите уравнение регрессии и значение R^2. Является ли точность регрессии удовлетворительной?

Решение:

Для того, чтобы произвести регрессионный анализ для заданных значений, создадим новый лист, скопируем туда данные, откроем пакет «Анализ данных» и выберем инструмент «Регрессия»:

В появившемся окне для поля Y выделим значения зависимой переменной Y из данной выборки, а для поля X, соответственно, используем значения выборки X:

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

Как видно из таблицы, значение R² = 0,2802648, следовательно, коэффициент детерминации R² составляет 28%, а модель имеет низкую значимость.

Уравнение линейной регрессии имеет вид: y = b₀ + b₁x + b₂x, где b - коэффициенты регрессии, а x - влияющие факторы. В соответствии с этим составим уравнение для данной регрессии:

Y = 23,4655485 + 0,09670595x

8. Проведите двухфакторный регрессионный анализ взяв зависимой переменной Y.

Решение:

Затем воспользуемся инструментом «Регрессия» в пакете «Анализ данных». Заполним поля появившегося окна следующим образом:

В поле входного интервала Y вводим столбец данных Y, в поле входного интервала X вводим массив значений переменных X₁ и X₂.

В результате получаем следующую таблицу регрессионного анализа:

По итогам регрессии можно сделать вывод о высокой значимости модели, так как коэффициент детерминации равен 0,986725, т.е. 99%. Также можно отметить, что значение F > F_{критерия}, следовательно, имеет место влияние фактора.

9. Расскажите цель применения дисперсионного анализа. Как должны соотносится межгрупповая и внутригрупповая дисперсия при наличии влияния фактора? Проведите дисперсионный анализ по следующим данным:

Подтверждается ли влияние фактора с уровнем значимости 0,05? С каким уровнем значимости присутствует влияние исследуемого фактора?

Решение:

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

Проведём дисперсионный анализ имеющихся данных. Для начала создадим новый лист и перенесём туда заданные параметры:

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

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

Влияние исследуемого фактора определяется по величине значимости критерия Фишера, которая находится в таблице «Дисперсионный анализ» на пересечении строки «Между группами» и столбца «Р-Значение». Эта величина показывает, с каким минимальным уровнем значимости выполняется наличие фактора. В случаях, когда Р-Значение < 0,05, критерий Фишера значим, и влияние исследуемого фактора можно считать доказанным. В нашей же модели P-Значение равно 0,035083, а выборочное значение критерия Фишера, F = 4,486628117, что немного превышает F_крит, равное 3,885294, следовательно, влияние фактора имеет место в данной модели.

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