Наиболее распространенной мерой изменчивости является отклонение di значения наблюдения хi, от среднего х. Одни отклонения могут иметь отрицательные значения (для наблюдений, значения которых меньше среднего), а другие — положительные (для наблюдений, значения которых больше среднего). Простое суммирование отклонений ничего не дает, поскольку они могут взаимно компенсировать друг друга, т.е. среднее отклонение всегда равно нулю. Поэтому каждое отклонение возводят в квадрат, суммируют и делят на количество наблюдений минус 1. Данная мера изменчивости называется дисперсией (variance) и обозначается s2.
Вычисление дисперсии производится при помощи функции ДИСП(массив). Чем больше ее величина, тем соответственно больше разброс значений вокруг среднего.
Для измерения изменчивости также вычисляется стандартное отклонение, обозначаемое символом s и равное квадратному корню из дисперсии, для вычисления которого используется функция СТАНДОТКЛОН. Эта характеристика представляет «типичное» отклонение значений от среднего, более высокое значение которой свидетельствует о более высокой степени изменчивости данных. При выборе функций для вычисления стандартного отклонения нужно обращать внимание на их описание, поскольку предлагаются функции как для генеральной совокупности, так и для выборки данных, а также с учетом или без учета текстовых и логических значений.
В ряде случаев возникает необходимость определения диапазона значений, в который попадает средняя величина исследуемой характеристики при заданной вероятности. Для этого используется функция ДОВЕРИТ, при вычислении которой должны быть указаны следующие данные: уровень значимости альфа, используемый для вычисления уровня надежности (уровень надежности равняется 100*(1 - альфа) процентам, т.е. альфа равное 0,05 означает 95-процентный уровень надежности), размер выборки – число наблюдений и предварительно рассчитанное стандартное отклонение.
В целом, следует отметить, что Ехсе1 предоставляет большое количество функций для проведения статистического анализа. В данном разделе рассмотрены функции, которые находят наиболее частое применение при анализе данных, проводимых товароведами-экспертами.
В случае отсутствия необходимых функций, можно произвести расчет требуемых показателей путем создания формулы.
Рассмотрим процедуру проведения корреляционного анализа. Величина наклона в уравнении регрессии зависит от единицы измерения данных. При использовании другой шкалы наклон изменится. Поэтому иногда бывает удобнее выражать взаимосвязь между одной переменной и другой в безразмерном виде, для чего и предназначена корреляция, выражающая силу взаимосвязи по безразмерной шкале ( -1; 1).
Положительная корреляция означает сильную положительную взаимосвязь, т.е. увеличение одной переменной вызывает увеличение другой переменной. Например, такая корреляция наблюдается между содержанием золота в сплаве и ценой на изделие, изготовленное из этого сплава. Отрицательная корреляция означает сильную отрицательную взаимосвязь, т.е. увеличение одной переменной вызывает уменьшение другой переменной например, увеличение цены товара может сопровождаться уменьшением объема продаж.
Близкая к нулю корреляция означает, что между двумя переменными нет никакой взаимосвязи. Если между переменными существует нелинейная взаимосвязь, она будет характеризоваться нулевой корреляцией.
Чаще всего для измерения корреляции используется коэффициент корреляции Пирсона, который обычно обозначается символом r.
При нулевой корреляции наклон равен нулю, а знак наклона всегда соответствует знаку корреляции. Наклон может выражаться любым действительным числом, но корреляция всегда должна быть в промежутке от - 1 до +1. Корреляция +1 означает, что все точки данных падают точно на одну линию с положительным наклоном. В таком случае все остатки равны
нулю, а подогнанная линия регрессии точно проходит через все точки.
Корреляция означает взаимосвязь между двумя переменными без предположения о том, что изменение одной переменной вызывает изменения другой, поэтому следует крайне осторожно интерпретировать полученные результаты и не путать корреляцию с причинно-следственной связью.
Для вычисления коэффициента корреляции служит функция КОРРЕЛ(х, у).
Для применения этого инструмента необходимо построить таблицу, как это требуется при регрессионном анализе. После установления курсора внутри таблицы следует обратиться к меню Сервис→Анализ данных для определения матрицы коэффициентов корреляции. В появившемся окне отметить опцию Корреляция. В следующем диалоговом окне ввести в указанных строках диапазон для переменной Х и У. Нажатием ОК будут произведены расчеты. При этом на листе появится матрица, элементами которой будут являться коэффициенты корреляции между всеми выбранными показателями.
Рассмотрим основы регрессионного анализа.
При отображении на диаграмме зависимости двух переменных друг от друга полученные значения обычно не укладываются точно на прямую линию. Выполняя анализ линейной регрессии, пытаются найти такую линию, которая наилучшим образом оценивает взаимосвязь между двумя переменными (зависимой переменной у и независимой переменной х). Такая линия называется подогнанной линией регрессии, а описывающее ее уравнение — уравнением регрессии.
В Excel предусмотрена подгонка линии регрессии с помощью уравнения у= а + bх, где у — зависимая переменная, значения которой нужно предсказать; х — независимая переменная, или предиктор, на основе которой нужно сделать предсказание; а и b— коэффициенты.
Короткие вертикальные отрезки между подогнанной линией и точками представляют собой ошибки или остатки. Остаток — это разность между наблюдаемыми и предсказываемыми значениями. Поскольку а является значением переменной у в месте пересечения вертикальной оси и подогнанной линии регрессии, этот коэффициент иногда называют пересечением или постоянным членом. Поскольку bхарактеризует наклон подогнанной линии регрессии, этот коэффициент называют наклоном. Коэффициент bвыражает отношение вертикального и горизонтального приращений вдоль линии. Например, если переменная у возрастает от 10 до 30, а переменная х при этом увеличивается от 0 до 10, то наклон подогнанной линии регрессии выражается формулой, в числителе которой будет разница (30-10), в знаменателе – (10-0). Отсюда b =2.
Предположим, что переменная х обозначает крутку текстильных нитей, а переменная у — их прочность. В таком случае пересечение регрессии (т.е. значение переменной у при х = 0) обозначает прочность нитей без крутки. Кроме того, наклон регрессии обозначает приращение прочности нитей при увеличении крутки на определенную величину, которая определяется шагом измерений, например, десять витков.
У нитей с прочностью выше подогнанной линии регрессии наблюдается положительный остаток, а у нитей с прочностью ниже подогнанной линии регрессии — отрицательный остаток.
Если линия направлена вниз так, что при возрастании значений переменной х уменьшаются значения переменной у, то линия регрессии имеет отрицательный наклон. Например, если переменная х обозначает срок эксплуатации компьютера в годах, а переменная у — его цену, то в таком случае наклон обозначает ежегодное снижение цены. В этом примере пересечение регрессии обозначает цену нового компьютера.
При подгонке линии к данным используются такие а и b, для которых сумма квадратов остатков принимает наименьшее значение. Эта процедура называется методом наименьших квадратов. Для определенияэтих членовслужат функции ОТРЕЗОК (у,х) и НАКЛОН (у,х).
Для применения инструмента Регрессия необходимо построить таблицу, указав в ней значения анализируемых показателей. Результирующим показателем должен быть только один, а в качестве объясняющих переменных может быть использовано несколько показателей.
Для активизации инструмента обращаются к меню Сервис→Пакет анализа→Регрессия.
В появившемся окне указываются следующие параметры:
- диапазон для У (результативного показателя);
- диапазон для Х (объясняющих показателей);
- флажок в позиции Уровень надежности – 0,95.
- флажок в позиции Метки переменных (только в тех случаях, когда перед выполнением анализа была выделена вся таблица или при задании диапазонов У и Х указывались не только сами значения, но и наименования столбцов).
- флажок в позиции остатки и стандартизированные остатки.
- опция в позиции выводить результат на новом листе.
В результате на новом листе появятся результативные таблицы.
В качестве примера интерпретации результативных таблиц рассмотрим регрессионный анализ зависимости двух переменных. В качестве зависимой переменной будет выступать уровень реализации, в качестве независимой переменной - затраты на рекламу. Таблица на основе которой будет проведен анализ состоит из 3 столбцов: вид изделия, затраты на рекламу, уровень реализации. После использования инструмента «Регрессия» на экране появляется несколько таблиц.
Первая - область статистических параметров регрессии. В ней рассмотрим основные параметры.
Величина достоверности аппроксимацииR2измеряет процентную долю изменчивости значений зависимой переменной, которая может объясняться изменениями независимой переменной. При построении однофакторной корреляционной модели (как в данном примере) коэффициент множественной корреляции равен коэффициенту парной корреляции.Величина R2может принимать значения от 0 до 1. Например, значение 0,7654 говорит о том, что изменчивость уровня реализации, составляющая 76,54%, может объясняться изменениями затрат на рекламу. Оставшаяся доля (23,46%) изменчивости уровня реализацииможет объясняться случайной изменчивостью.