Использование статистических функций MS Excel

МГУПП

Контрольная работа

по статистической обработке данных

Использование статистических функций MS Excel.

Содержание.

I.                    Исследование одномерного набора данных

II.                 Исследование двумерного набора данных

Уравнение регрессии

1.      Общий подход к построению уравнения регрессии на примере линейной модели

2.      Функции рабочего листа для уравнения линейной регрессии

3.      Экспоненциальная модель

I I I. Проверка статистических гипотез
Правила оформления контрольной работы.

1.      Описание постановки задачи, математической модели и хода решения (заполнение ячеек таблицы и формул, последовательность операций) представляются в распечатанном виде (образец титульного листа в Приложении 1) . Электронные таблицы (файлы с именем Фамилия+№группы и расширением .xls, например , Иванова_ЭУЗ_503.xls) должны содержать листы решенных задач, названные соответствующим образом, например «Транспортная задача») прилагаются к контрольной работе на CD-RW.

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

3.      Контрольная работа выполняется с помощью программы MS Exel.

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

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

6.      Контрольная работа с заполненным студентом титульным листом представляется в установленный деканатом срок для рецензирования.

7.      Контрольная работа с удовлетворительной рецензией обязательно представляется на экзамене (зачете).

Контрольная работа

«Статистические исследования данных»

I. Исследование одномерных наборов данных

(средствами MS Excel).

Для заданного одномерного набора данных

(задать САМОСТОЯТЕЛЬНО набор из 40 случайно выбранных чисел

в интервале от 0 до 100)

1) Построить гистограмму относительных частот с шагом 10

2) Построить полигон относительных частот

3) Вычислить

выборочное среднее (),

медиану ( mx ),

моду ( Mx ),

экстремальные значения  ( min xi, max xi),

нижний квартиль (Q25),

верхний квартиль (Q75),

выборочную дисперсию (Dв),

исправленную выборочную дисперсию,

среднеквадратичное отклонение (s) .

I I .  Исследование двумерных наборов данных

средствами электронных таблиц (MS Excel)

Уравнение регрессии

В данной работе будет рассмотрена задача построения регрессионной модели. С помощью средства «Поиска решений» (MS Exel) будет решена за­дача нахождения уравнения регрессии для одной зависимой и одной независимой переменных. Хотя рассмотренная модель имеет очень специфический вид, описанный подход позволяет исследовать любое уравнение регрес­сии. В разделе 2 приведены функции рабочего листа, непосредственно вычисляющие различные характеристики линейного уравнения регрессии, а в разделе 3 — экспоненциального уравнения регрессии, которые позво­ляют значительно упростить процедуру регрессионного анализа для этих наиболее часто встречающихся на практике моделей.

1 Общий подход к построению уравнения регрессии на примере линейной модели

Рассмотрим, как решается задача нелинейной оптимизации с помощью средства поиска решений на примере построения линейного уравнения регрессии. Имеются две наблюдаемые величины х и у, например, объем реализации фирмы, торгующей подержанными автомобилями, за шесть недель ее работы. Значения этих наблюдаемых величин приведены на рис. 1, где х — отчетная неделя, а у — объем реализации за эту неделю.

Рис. 1  Исходные данные для построения линейной модели

Необходимо построить линейную модель у =mx+b, наилучшим образом описывающую наблюдаемые значения. Обычно m и b подбираются так, чтобы минимизировать сумму квадратов разностей между наблюдаемыми и теоретическими значениями зависимой переменной у, т. е. минимизировать

, где n — число наблюдений (в данном случае n = 6).

Для решения этой задачи отведем под переменные m и b ячейки D3 и ЕЗ, соответственно, а в ячейку F3 введем минимизируемую функцию

{=СУММКВРАЗН(В2:В7;ЕЗ+DЗ*А2:А7)}

Функция СУММКВРАЗН (sumsq) вычисляет сумму квадратов разностей для элементов указанных массивов.

Теперь выберем команду Сервис, Поиск решения (Tools, Solver) и заполним открывшееся диалоговое окно Поиск решения (Solver), как показано на рис. 2.

Отметим, что на переменные m и b ограничения не налагаются. В результате вычислений средство поиска решений найдет: m = 1,88571 и  b = 5,400.

Рис. 2  Диалоговое окно Поиск решения для расчета уравнения регрессии

2 Функции рабочего листа для уравнения линейной регрессии

Параметры m и b линейной модели у = mх +b из предыдущего раздела можно определить с помощью функций НАКЛОН (slope) и ОТРЕЗОК (intercept).

Функция НАКЛОН (slope) определяет коэффициент наклона линейного тренда.

Синтаксис:

НАКЛОН (известные_значения_у; известные_значения_х)              ■

Функция отрезок (intercept) определяет точку пересечения линии линейного тренда с осью ординат.

Синтаксис:

ОТРЕЗОК (известные_значения_х; известные_значения_у)

Аргументы функций НАКЛОН (slope) и отрезок (intercept):

известные_значения_у -  Массив известных значений зависимой наблюдаемой величины

известные_значения_х  -  Массив известных значений независимой наблюдаемой величины.   Если  аргумент  известные_значения_х  опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и аргумент известные_значения_у

Функции наклон и отрезок вычисляются по следующим формулам:

: где

В ячейках D2 и Е2 (рис. 1) найдены m и b, соответственно, по формулам:

=НАКЛОН (В2:В7;А2:А7)

=ОТРЕЗОК(В2:В7;А2:А7)

Коэффициенты m и b можно найти и другим способом. Постройте точечный график по диапазону ячеек А2:В7, выделите точки графика двойным щелчком, а затем щелкните их правой кнопкой мыши. В раскрывшемся контекстном меню выберите команду Линии тренда (Trendline) (рис. 3).

Рис. 3  Начало построения линий тренда

В диалоговом окне Линия тренда (Trendline) на вкладке Тип (Туре) в группе Построение линии тренда (аппроксимация и сглаживание) (Trend/Regression type) выберите параметр Линейная (Linear) (рис. 4), а на вкладке Параметры (Options) установите флажки Показывать уравнение на диаграмме (Display Equation on Chart) и Поместить на диаграмму величину достоверности аппроксимации (R^2) (Display R-squared) (т.е. на диаграмму необходимо поместить значение квадрата коэффициента корреляции)

(рис. 5).

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

Флажок Пересечение кривой с осью Y в точке (Set Intercept) (рис.5) устанавливается только в случае, если эта точка известна. Например, если этот флажок установлен и в его поле введен о, это означает, что ищется модель

у = mх.

Результат   выполнения   команды  Линии тренда (Trendline) приведен на рисунке 14.

Рис. 4 Вкладка Тип диалогового окна Линия тренда

Рис. 5 Вкладка Параметры диалогового окна Линия тренда

Рис. 6 -  График линии тренда

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

На основе найденных коэффициентов уравнения регрессии можно опреде­лить теоретическое значение наблюдаемой величины у. Вычислим теорети­ческое значение у в ячейке С2 (рис. 1) при х из А2 по формуле =$D$2*A2+$E$2

Однако теоретическое значение у в фиксированной точке можно вычислить и без предварительного определения коэффициентов линейной модели с помощью функции ПРЕДСКАЗ (FORECAST).

Синтаксис:

ПРЕДСКАЗ(t; известные _ значения _ у; известные_ значения _ х)

Аргументы:

t - Точка данных, для которой предсказывается значение

известные _ значения _ у - Массив известных значений зависимой наблюдаемой величины.

известные _ значения _ х  - Массив известных значений независимой наблюдаемой величины. Если аргумент известные _ значения _ х опущен, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и массив известные _ значения _ у.

Например, теоретическое значение в ячейке С2 (рис. 1) можно также определить по формуле =ПРЕДСКАЗ(А2;$В$2:$В$7;$А$2:$А$7)

Функция тенденция (trend) вычисляет значения уравнения линейной регрессии для целого диапазона значений независимой переменной как для одномерного, так и для многомерного уравнения регрессии. Многомерная линейная модель регрессии имеет вид:

у = m1x1 + … + mnxn + b.

Синтаксис:

ТЕНДЕНЦИЯ(известные _ значения _ у; известные _ значения _ х; новые _ значения _ х; конст)

Аргументы:

известные _ значения _ у  -  Массив известных значений зависимой наблюдаемой величины

известные _ значения _ х  -  Массив известных значений независимой наблюдаемой величины. Если аргумент известные _ значения _ х опущен, то предполагается, что это массив {1; 2; 3;...} такого же размера, как и массив известные _ значения _ у.

новые _ значения _ х - Новые значения х, для которых функция тенденция возвращает соответствующие значения у.

конст - Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение истина или опущен, то b вычисляется обычным образом. Если конст имеет значение ложь, то b полагается равным 0.

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

Функция линейн (linest) возвращает массив {mn, …,m1, b} значений параметров уравнения многомерной линейной регрессии.

Синтаксис:

ЛИНЕЙН(известные _ значения _ у; известные _ значения _ х; конст; статистика)

Аргументы:

известные _ значения _ у - Массив известных значений зависимой наблюдаемой величины

известные _ значениях _ x - Массив известных значений независимой наблюдаемой величины. Если аргумент известные _ значения _ х опущен, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные _ значения _ у.

конст - Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение истина или опущен, то b вычисляется обычным образом. Если конст имеет значение ложь, то b полагается равным 0.

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

3 Экспоненциальная модель

Другой часто встречающейся на практике регрессионной моделью является экспоненциальная модель, которая описывается уравнением у = bтх

Значения экспоненциального тренда можно предсказывать с помощью функции РОСТ (GROWTH).

Синтаксис:

РОСТ (известные _ значения _ у; известные _ значения _ х; новые _ значения _ х; конст)

Аргументы:

Значения параметров экспоненциальной модели определяются с помощью функции ЛГРФПРИБЛ (LOGEST).

Синтаксис:

ЛГРФПРИБЛ (известные _ значения _ у; известные _ значения _ х; конст; статистика)

Аргументы:

Кроме того, одномерную экспоненциальную модель можно построить графически (рис. 7).

Рис. 7 Экспоненциальная линия тренда

На рис. 7 приведены результаты построения экспоненциального уравнения тренда продажи подержанных автомобилей за 7, 8 и 9-ю недели торговли.

В диапазоне ячеек В8:В10 введена формула построения линейного тренда {=ТЕНДЕНЦИЯ(В2:В7;А2:А7;А8:А10) }

В диапазоне ячеек С2:С10 введена формула построения экспоненциального тренда

{=РОСТ (132: В"7;А2:А7;А2:А10) }

Линейный и экспоненциальный тренды тесно связаны между собой. В диапазон ячеек D2:D10 введена формула

1-ЕХР(ТЕНДЕНЦИЯ(LN(В2:В7);А2:А7;А2:А10))}

Как видно из рисунка, значения в диапазонах С2:С10 и D2:D10 совпадают. В диапазоны ячеек F2:G2 и F3:G3 введены формулы

{=ЛИНЕЙН(В2:В7;А2:А7)} {=ЛГРФПРИБЛ(В2:В7;А2:А7)}

для определения параметров линейной и экспоненциальной моделей.

Квадрат коэффициента корреляции экспоненциальной модели равен 0,947 (рис. 15) и меньше квадрата коэффициента корреляции линейной модели (.= 0,9923) (рис. 6) таким образом, в данном примере линейная модель более достоверно описывает зависимость между наблюдаемыми величинами.

Варианты выборок для контрольной работы

Вариант 1

Вариант 2

Вариант 3

Вариант 4