Корреляция входной интервал содержит нечисловые данные что делать

0

Решение проблемы «нечисловых» чисел в таблицах Excel

При импортировании данных из других источников вы, возможно, уже успели обнаружить, что Excel иногда некорректно импортирует значения. В частности, он может принять ваши числа за текст. И тогда, например, при суммировании диапазона значений формула СУММ возвращает 0 — хотя диапазон, по всей видимости, содержит числовые значения.

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

1. Активизируйте любую пустую ячейку на листе.
2. Нажмите Ctrl+C, чтобы скопировать пустую ячейку.
3. Выберите диапазон, содержащий проблематичные значения.
4. Выберите Главная ► Буфер обмена ► Вставить ► Специальная вставка для открытия диалогового окна Специальная вставка.
5. В окне Специальная вставка установите переключатель Операция в положение сложить.
6. Нажмите кнопку ОК.

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

Числовые характеристики результатов наблюдения

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

Для характеристики положения используются показатели центра распределения данных наблюдения– средняя арифметическая, мода и медиана.

Средняя арифметическая для дискретного ряда распределения рассчитывается по формуле:

–варианты значений признака;

– частота повторения данного варианта.

В интервальном вариационном ряду средняя арифметическая определяется по формуле:

– середина соответствующего интервала;

Мода распределения– это наиболее часто встречающееся значение признака в совокупности. В дискретном ряду определение моды не требует специальных расчётов. Мода соответствует варианту с наибольшей частотой. В интервальном вариационном ряду в отличие от дискретного ряда определение моды требует определённых расчётов на основе специальной формулы.

Модальный интервал (то есть содержащий моду) при интервальном распределении с равными интервалами определяется по наибольшей частоте, а с неравными интервалами– по наибольшей плотности. В первом случае мода рассчитывается по следующей формуле:

– нижняя граница модального интервала;

– величина модального интервала;

– частота модального интервала;

– частота интервала, предшествующего модальному;

– частота интервала, следующего за модальным.

Во втором случае в формуле моды вместо частот используется соответствующая плотность .

Медиана – это значение признака, расположенное в середине (в центре) ранжированного ряда. Медиана делит совокупность на две равные части– со значениями признака меньше медианы и со значениями признака больше медианы.

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

– число единиц совокупности.

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

В интервальном вариационном ряду медиана определяется по формуле:

– нижняя граница медианного интервала;

– величина медианного интервала;

– сумма всех частот ряда;

– накопленная частота интервала, предшествующего медианному;

– частота медианного интервала.

Медианным является интервал, в котором сумма накопленных частот равна или превышает полусумму частот ряда.

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

Дисперсия рассчитывается по формуле:

Среднее квадратическое (стандартное) отклонение равно корню квадратному из дисперсии.

Коэффициент вариации равен:

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

Стандартизированный коэффициент асимметрии имеет приближённое стандартное нормальное распределение.

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

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

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

В Excel числовые характеристики вычисляются с помощью процедуры Описательная статистика, входящей в Пакет анализа, и соответствующих встроенных статистических функций СРЗНАЧ, МЕДИАНА, МОДА, ДИСП, ДИСПР, СТАНДОТКЛОН, СТАНДОТКЛОНП, СРОТКЛ, КВАДРОТКЛ, СКОС и ЭКСЦЕСС.

Для доступа к процедуре Описательная статистика необходимо:

В меню Сервис выделить строку Анализ данных.

В открывшемся окне Анализ данных выделить процедуру Описательная статистика и щёлкнуть на кнопке ОК. На экране появится диалоговое окно Описательная статистика, которое содержит следующие элементы управления:

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

флажок Итоговая статистика. Если этот флажок установлен, процедура вычисляет и помещает в таблицу результатов решения следующие числовые характеристики: среднюю, стандартную ошибку средней, медиану, моду, стандартное отклонение, дисперсию, эксцесс, асимметрию, размах вариации, минимальное и максимальное значение изучаемого признака, сумму всех значений признака и объём совокупности. Если совокупность не имеет повторяющихся значений признака, в строке Мода появляется сообщение # Н/Д!– неопределённые данные;

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

флажки К-й наименьший/К-й наибольший. Если эти флажки установлены. то в таблице результатов решения появляются -й и -й элементы упорядоченной совокупности (то есть единицы совокупности, расположенные на -м месте от её начала и от конца).

Назначение переключателей Группирование по столбцам/по строкам, флажка Метки в первой строке/Метки в первом столбце и группы переключателей Выходной интервал/Новый рабочий лист/Новая книга рассмотрено на стр. 8-9.

Результаты решения выводятся на экран в виде набора таблиц– по одной таблице на каждый столбец входного интервала (на каждую обработанную совокупность). Каждая выходная таблица состоит из двух столбцов. В первом столбце указывается названия числовых характеристик, во втором– их значения. В заголовке указывается номер совокупности, к которой относится данная таблица (например, Столбец 1).

Свой наибольший размер (18×2) таблица принимает при установке всех четырёх флажков, расположенных в нижней части диалогового окна процедуры. В случае возникновения опасности того, что таблица результатов наложится на уже заполненные ячейки, на экран выводится сообщение о такой опасности. В ответ на это сообщение пользователь должен разрешить удаление старых данных и вывод на их место новых (для этого надо щёлкнуть на кнопке ОК).

Формирование выборки

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

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

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

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

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

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

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

типическая (стратифицированная, расслоенная, районированная);

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

Формирование выборки в Excel осуществляется следующим образом:

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

Порядковые номера единиц исходной совокупности вводятся в диапазон ячеек (входной диапазон). Эти номера могут находиться в одном столбце или группе смежных столбцов одинаковой «высоты». При этом число всех ячеек входного диапазона должно равняться числу единиц исходной совокупности. Если среди элементов входного интервала имеются нечисловые данные, то отбор не состоится, а на экране появится сообщение «Выборка– входной интервал содержит нечисловые данные».

В меню Сервис выделяется строка Анализ данных.

В открывшемся диалоговом окне Анализ данных выделяется процедура Выборка и нажимается кнопка ОК. На экране появится диалоговое окно Выборка, которое содержит следующие элементы управления:

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

Метод выборки устанавливается с помощью переключателей Периодический и Случайный. При активизации переключателя Случайный процедура «настраивается» на выполнение случайной выборки с повторением. Нужный объём выборки вводится в поле Число выборок. Единицы генеральной совокупности отбираются случайным образом. Каждая единица исходной совокупности имеет равную со всеми остальными единицами возможность быть включённой в выборку. Любая единица генеральной совокупности может попасть в выборку более одного раза.

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

При формировании случайной выборки выходной интервал представляет собой столбец с числом ячеек, равным заданному объёму выборки. В случае механической выборки число ячеек выходного интервала равно целой части результата деления объёма исходной совокупности на шаг выборки.

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

Корреляционный анализ

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

2) стохастическая, частным случаем которой является корреляционная.

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

Функциональной называется связь, при которой каждому значению факторного признака соответствует вполне определённое значение результативного признака. Функциональная связь является строгой, точной, полной зависимостью; проявляется и для каждой единицы совокупности, и во всех случаях наблюдения. Характерной особенностью функциональной связи является то, что в каждом отдельном случае известен полный перечень факторов, влияющих на результативный признак, а также механизм этого влияния, выраженный определённым уравнением.

Стохастическая (вероятностная) связь не проявляется в каждом отдельном случае, а лишь в общем, среднем, при большом числе наблюдений.

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

Корреляционные связи имеют ряд характеристик:

По форме (аналитическому выражению) корреляционные связи между признаками могут быть линейными (прямолинейными) и нелинейными (криволинейными). При линейной форме равномерное изменение значений одного признака сопровождается более или менее равномерным изменением значений другого признака. Математически она выражается уравнением прямой у_х = а + вх, графически — прямой линией. При нелинейной форме равномерному изменению значений одного признака соответствует неравномерное изменение значений другого. Выражается уравнением какой- либо кривой линии: параболы, гиперболы, показательной, степенной, логарифмической, логической функции и др.

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

Степень тесноты корреляционной связи оценивается по специальным шкалам, например, по шкале Чеддока.

Количественный критерий оценки тесноты связи по шкале Чеддока

Величина показателя для измерения тесноты связи

Существуют и другие менее детальные шкалы.

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

1) парная корреляция – связь между двумя признаками (результативным и факторным);

2) частная корреляция – зависимость между результативным и одним факторным признаком при фиксированном значении других факторных признаков;

3) множественная корреляция – зависимость результативного от двух или более факторных признаков.

В практике статистических исследований выделяют:

корреляционный анализ, который имеет своей задачей количественное измерение тесноты связи между признаками;

регрессионный анализ, который заключается в определении формы связи, построении одно- или многофакторных моделей (уравнений) регрессии;

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

Следует также различать собственно-корреляционные (параметрические) и непараметрические методы изучения взаимосвязей между признаками. Основу применения собственно-корреляционных методов составляют однородность и необходимость подчинения распределения совокупности по факторным и результативному признаку закону нормального распределения вероятностей. Несоблюдение этих условий обуславливает необходимость применения при изучении взаимосвязей непараметрических методов.

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

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

Для проверки гипотезы о законе распределения изучаемого признака используются также специальные статистические критерии. При этом выдвигается гипотеза о том, что истинной функцией распределения признака является некоторая заданная функция (для нашей задачи– функция нормального распределения). Если гипотеза верна (то есть, если значения признака действительно имеют функцию распределения ), то найденная по данным наблюдения эмпирическая функция распределения не должна сильно отличаться от гипотетической функции распределения , и с увеличением объёма совокупности различие между ними должно уменьшаться. В связи с этим вопрос о принятии или отклонении проверяемой гипотезы решается в зависимости от того, насколько хорошо согласуются эмпирическая и гипотетическая функции распределения. Статистические критерии, базирующиеся на таком подходе, называются критериями согласия или соответствия. В основе этих критериев лежит выбранная статистика, которая служит мерой расхождения между эмпирическим и гипотетическим законами распределения исследуемого признака. Известны критерии К. Пирсона (хи- квадрат), В.И. Романовского, А.Н. Колмогорова, Б.С. Ястремского, омега-квадрат, Крамера-Мизеса-Смирнова и др.

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

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

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

вычисление выборочных коэффициентов корреляции и корреляционных отношений;

проверка статистических гипотез о значимости корреляционной зависимости.

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

В Excel построение поля корреляции (диаграммы рассеивания) между изучаемыми признаками осуществляется при помощи специального средства, служащего для графического изображения статистических данных– Мастера диаграмм (см. 19). Для построения корреляционного поля используется тип Точечная. На палитре Вид выделяется диаграмма в виде изолированных точек, находящаяся в левом верхнем углу палитры.

Расположение точек на графике позволяет в ряде случаев сделать предположение о наличии, направлении и форме взаимосвязи между изучаемыми признаками. Так, линейное расположение точек даёт серьёзное основание для выбора линейной модели, сравнительно небольшой разброс точек относительно воображаемой кривой, проходящей «наилучшим образом» через эти точки, говорит о довольно сильной зависимости между признаками, и наоборот. Расположение точек слева на право свидетельствует о прямой корреляции, а справа налево– об обратной корреляции.

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

Построение корреляционной таблицы начинают с группировки значений факторного и результативного признаков. В Excel для группировки данных способом равных интервалов используются процедура Гистограмма, входящая в Пакет анализа (см стр.14).

– середина -го интервала группировки по факторному признаку;

– середина -го интервала группировки по результативному признаку;

– групповая частота «клетки», находящейся на пересечении строки и столбца корреляционной таблицы;

– групповая частота -го интервала группировки по факторному признаку (число наблюдений в -й строке);

–групповая частота -го интервала группировки по результативному признаку (число наблюдений в -м столбце);

–объём изучаемой совокупности (общее число наблюдений).

Заполнение корреляционной таблицы даёт довольно наглядное представление о характере зависимости между изучаемыми признаками.

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

Линейный коэффициент корреляции рассчитывается для определения тесноты и направления связи между двумя корреляционными признаками в случае наличия между ними линейной зависимости и распределения значений признаков близкого к нормальному. Линейный коэффициент корреляции может принимать значение от -1 до +1. Чем ближе коэффициент корреляции к 1, тем сильнее (теснее) связь между признаками. Для определения характера связи используют шкалу Чеддока.

В теории разработаны и на практике применяются различные модификации формулы расчёта данного коэффициента:

где –ковариация факторного и результативного признаков;

, – среднее квадратическое (стандартное) отклонение соответственно факторного и результативного признака;

n – число наблюдений.

Квадрат коэффициента корреляции (r 2 ) носит название коэффициента детерминации. Он показывает долю вариации результативного признака, обусловленную влиянием вариации факторного признака.

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

Расчет эмпирического корреляционного отношения осуществляется по сгруппированным данным наблюдения и основан на использовании теоремы (правила) сложения дисперсий:

Эмпирическое корреляционное отношение определяется по формуле:

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

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

-дисперсия результативного признака в соответствующей группе.

Общая дисперсия характеризует вариацию результативного признака, обусловленную влиянием всех факторов:

Расчёт теоретического корреляционного отношения в Excel осуществляется в рамках регрессионного анализа, поэтому будет рассмотрен в следующем разделе.

В Excel вычисление выборочного коэффициента корреляции осуществляется с помощью процедуры Корреляция, входящей в Пакет анализа, и встроенных статистических функций КОРРЕЛ, ПИРСОН и КВПИРСОН.

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

Назначение переключателя Группирование, флажка Метки и группы переключателей Выходной интервал/Новый рабочий лист/ Новая книга рассмотрено в первом разделе на стр.8-9.

Статистические функции КОРРЕЛ и ПИРСОН вычисляют выборочную оценку линейного коэффициента корреляции по первой формуле, представленной на стр. 34, и дублируют друг друга. Синтаксис функции КОРРЕЛ (массив 1; массив 2), где массив 1– диапазон ячеек, в который введены значения факторного признака (например, А1:А25), а массив 2– диапазон ячеек, в который введены значения результативного признака (например, В1:В25). Статистическая функция КВПИРСОН вычисляет квадрат выборочного коэффициента корреляции.

Для вычисление эмпирического корреляционного отношения в Excel не предусмотрено специальных статистических процедур и встроенных функций. Вычисление корреляционного отношения осуществляется по представленным выше формулам и требует предварительного построения корреляционной таблицы и ряда вспомогательных расчётов.

Значимость линейного коэффициента корреляции проверяется на основе t – критерия Стьюдента. При этом выдвигается и проверяется гипотеза ( ) о равенстве коэффициента корреляции в генеральной совокупности нулю (то есть в действительности связь между изучаемыми признаками отсутствует, а эмпирическое значение выборочного коэффициента корреляции обусловлено только случайными совпадениями и в выборке).

Фактическое значение t — критерия рассчитывается по формуле — для совокупностей n<50 по формуле:

при большом числе наблюдений (n>100):

Вычисленное значение t – критерия сравнивается с критическим его значением при принятом уровне занятости α и числе степеней свободы k = n-2. В социально-экономических исследованиях уровень значимости α обычно принимается равным 0,05.

При «ручной» проверке гипотезы критические значения t находятся по таблице распределения Стьюдента. Если расчётное значение t – критерия больше критического, то гипотеза о том, что линейный коэффициент корреляции в генеральной совокупности равен нулю и лишь в силу случайных обстоятельств оказался равен проверяемому значению, отклоняется, то есть коэффициент корреляции признаётся значимым, а связь между признаками – статистически существенной. Если расчётное значение t – критерия меньше критического, то нулевая гипотеза принимается, что означает, что коэффициент корреляции в генеральной совокупности в действительности равен нулю и соответственно эмпирический коэффициент корреляции существенно не отличается от нуля.

В Excel проверка гипотезы об отсутствии корреляции между изучаемыми признаками осуществляется следующим образом:

В ячейку (например, В1) вводится значение выборочного коэффициента корреляции ;

В ячейку В2 для определения расчётного значения t – критерия вводится формула (*): = В1*КОРЕНЬ (115/(1-В1^2)) ( = 117);

В ячейку В3 для нахождения критического значения t – критерия Стьюдента при уровне значимости α= 0,05 и числе степеней свободы k =115 вводится формула: = СТЬЮДРАСПОБР (0.05;115);

Полученные расчётное и критическое значения t – критерия Стьюдента сравниваются, и делается вывод об отклонении или принятии нулевой гипотезы на уровне значимости =0,025 . Если гипотеза противоречит реальным данным наблюдения (отклоняется), то выборочный коэффициент корреляции признаётся значимым и между изучаемыми признаками существует соответствующая по степени тесноты корреляционная зависимость. Если гипотеза принимается, коэффициент корреляции признаётся незначимым.

Для оценки значимости корреляционного отношения используется F – критерий Фишера–Снедекора, вычисленный по формуле:

где n — число наблюдений; m – число интервалов группировки или параметров в уравнении регрессии.

При этом проверяется гипотеза об отсутствии корреляционной зависимости между изучаемыми признаками. Проверяемая гипотеза отклоняется на уровне значимости , если расчётное значение F – критерия превышает его критическое значение для принятого уровня значимости и чисел степеней свободы k₁=m-1 и k₂=m-n. В этом случае величина корреляционного отношения признаётся значимой, а связь между признаками существенной.

При «ручной» проверке гипотезы используются специальные таблицы F – распределения. В них указывается предельные (критические) значения F – критерия для различных степеней свободы k₁ и k₂, которые могут быть превзойдены с вероятностью α = 0,05.

В Excel проверка гипотезы об отсутствии корреляции между изучаемыми признаками осуществляется следующим образом:

В ячейку В1 вводится объём совокупности (например, 132) в ячейку В2– число интервалов группировки или параметров в уравнении регрессии (например, 12); в ячейку В3– значение выборочного корреляционного отношения;

В ячейку Е1 для нахождения расчётного значения F – критерий Фишера вводится формула (**): = В3^2*120/(1-В3^2)*11;

В ячейку Е2 для определения критического значения F – критерий Фишера для принятого уровня значимости =0,05 и чисел степеней свободы k₁=m-1 (11) и k₂=m-n (120) вводится формула: = FРАСПОБР (0.05;11;120).

Полученные расчётное и критическое значения F – критерий Фишера сравниваются, и делается вывод об отклонении или принятии нулевой гипотезы и соответственно о значимости или незначимости корреляционного отношения.

Множественный коэффициент корреляции вычисляется статистической процедурой Регрессия (см. следующий раздел).

Рассмотренные выше вычисления относятся к собственно-корреляционным, параметрическим методам изучения связей.

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

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

Для определения рангов в Excel предусмотрены статистическая процедура Ранг и персентиль и статистическая функция РАНГ.

Использование процедуры Ранг и персентиль заключается в следующем:

В меню Сервис выделяется строка Анализ данных.

В открывшемся окне Анализ данных выделяется процедура Ранг и персентиль, нажимается кнопка ОК. На экране появляется диалоговое окно Ранг и персентиль.

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

Устанавливается переключатель Группирование в нужное положение (по столбцам или строкам).

Флажок Метки устанавливается, если первая строка (столбец) входного диапазона содержит заголовки. Если такие заголовки отсутствуют, флажок не устанавливается.

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

Статистическая функция РАНГ имеет следующий синтаксис: РАНГ (число; массив; порядок):

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

массив– массив или диапазон ячеек, содержащий единицы исследуемой совокупности (неупорядоченные данные наблюдения);

порядок– величина, определяющая, как упорядочивать (ранжировать) массив:

– если порядок равен 0 или пропущен, массив упорядочивается в порядке убывания;

– если порядок– любое число, не равное нулю, то массив упорядочивается по возрастанию.

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

Коэффициент корреляции рангов (Спирмена) определяется по формуле:

d – разность между рангами соответствующих величин двух признаков;

n – число единиц в ряду (число пар рангов).

Коэффициент корреляции рангов принимает любые значения от -1 до +1. Если все ранги строго изменяются в одном и том же порядке, то d=0, а r=1. Если же ранги изменяются строго в противоположных направлениях, то r= -1. Значение r=0 характеризует отсутствие связи.

В Excel вычисление коэффициента ранговой корреляции Спирмена осуществляется следующим образом:

1. Вводятся заголовки исходных и расчётных данных, необходимых для расчёта коэффициента корреляции рангов: в ячейку А1– названия единиц изучаемой совокупности, в ячейку В1– название факторного признака, в ячейку С1– названия результативного признака, в ячейку D1– символ , обозначающий ранг по факторному признаку, в ячейку Е1– символ , обозначающий ранг по результативному признаку, в ячейку– F– символ , обозначающий квадрат разности между рангами соответствующих величин двух признаков.

2. Производится ввод исходных данных: в диапазон ячеек столбца А вводятся названия или номера единиц изучаемой совокупности; в диапазон ячеек столбца В (например, В2:В11)– значения факторного признака, в диапазон ячеек столбца С (С2:С11)– значения результативного признака.

3. В диапазонах ячеек D2:D11 и Е2:Е11 определяются соответственно ранги по факторному и результативному признаку с помощью описанной выше процедуры Ранг и персентиль или функции РАНГ, для чего вводятся формулы массива = РАНГ (В2:В11; В2:В11;1) и = РАНГ (С2:С11; С2:С11;1).

4. В диапазоне F2:F11 вычислить квадраты разности рангов с помощью формулы массива: = (D2:D11-E2:E11)^2.

5. В ячейках D12, E12 и F12 с помощью кнопки Автосуммирование определить суммы рангов по факторному и результативному признакам и сумму квадрата разности рангов.

6. По формуле рассчитывается выборочная оценка коэффициента ранговой корреляции Спирмена.

Значимость коэффициента корреляции рангов для совокупностей небольшого объёма (n£30) проверяется по таблице предельных значений коэффициента корреляции рангов Спирмена при заданном уровне значимости a и определённом объёме совокупности.

Значимость r может быть проверена также на основе t – критерия Стьюдента. Расчётное значение критерия определяется по формуле:

Значение коэффициента корреляции считается статистически существенным, если расчётное значение t – критерия Стьюдента превосходит его критическое значение при заданном уровне значимости a и числе степеней свободы k=n-2. Критическое значение t – критерия может быть определено по таблице распределения Стьюдента или в Excel по представленному выше в данном разделе порядку.

Коэффициент корреляции рангов Кендалла рассчитывается по формуле:

n – число наблюдений;

S – сумма разностей между числом последовательностей и числом инверсий по результативному признаку.

Расчёт данного коэффициента выполняется в следующей последовательности:

ранги факторного признака располагаются в порядке возрастания;

ранги результативного признака располагаются в порядке, соответствующем рангам признака х;

для каждого ранга результативного признака определяется сколько чисел, находящихся справа от него (следующих за ним) имеют величину ранга, превышающую его величину. Суммируя полученные таким образом числа, получаем слагаемое P, которое можно рассматривать как меру соответствия последовательностей рангов по x и y, и которое учитывается со знаком «+»;

для каждого ранга y определяется число, следующих за ним рангов, меньших его величины. Суммарная величина обозначается через Q и фиксируется со знаком «-»;

определяется сумма баллов S=P+Q

Коэффициент Кендалла также изменяется в пределах от -1 до +1. При достаточно большом числе наблюдений между коэффициентами корреляции рангов Спирмена и Кендалла существует следующее соотношение: r» .

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

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

При таком представлении ранжировки формула коэффициента корреляции рангов Кендалла имеет вид:

где –число единиц совокупности, для которых и одновременно . На практике вычисляют по формуле , где – – число рангов в ранжировке (***), для которых для которых и одновременно .

В Excel вычисление коэффициента ранговой корреляции Кендалла осуществляется по формуле (****) следующим образом:

1. Вводятся заголовки исходных и расчётных данных, необходимых для расчёта коэффициента корреляции рангов: в ячейку А1– названия единиц изучаемой совокупности, в ячейку В1– название факторного признака, в ячейку С1– названия результативного признака, в ячейку D1– символ , обозначающий ранг по факторному признаку, в ячейку Е1– символ , обозначающий ранг по результативному признаку, в ячейку– F– символ , обозначающий квадрат разности между рангами соответствующих величин двух признаков.

2. Производится ввод исходных данных: в диапазон ячеек столбца А вводятся названия или номера единиц изучаемой совокупности; в диапазон ячеек столбца В (например, В2:В11)– значения факторного признака, в диапазон ячеек столбца С (С2:С11)– значения результативного признака.

3. В диапазонах ячеек D2:D11 и Е2:Е11 определяются соответственно ранги по факторному и результативному признаку с помощью описанной выше процедуры Ранг и персентиль или функции РАНГ, для чего вводятся формулы массива = РАНГ (В2:В11; В2:В11;1) и = РАНГ (С2:С11; С2:С11;1).

4. Выделяется диапазон D1:E11, в котором находятся ранги по факторному и результативному признакам, нажимается кнопка Копировать на панели инструментов Стандартная.

5. Выделяется ячейка F1. В меню Правка выделяется команда Специальная вставка.

6. В открывшемся диалоговом окне Специальная вставка в группе переключателей Вставить установливается переключатель Значения и нажимается кнопка ОК. В диапазоне F2:G11 появятся «копии» рангов.

7. Выделяется диапазон F1:G11. В меню Данные выделяется команда Сортировка.

8. В открывшемся окне Сортировка диапазона в раскрывшемся списке Сортировать по выбирается поле , по которому надо выполнить сортировку, и установливается переключатель по возрастанию; в группе переключателей Идентифицировать поля по установливатся переключатель подписям (первая строка диапазона) и нажимается кнопка ОК.

В диапазоне F2:G11 появятся ранги по факторному и результативному признакам, отсортированные в порядке возрастания рангов факторного признака.

9. В ячейку Н2 вводится формула массива = СУММ (ЕСЛИ ($G3:$G11>G2;1;0)), нажимаются клавиши Ctrl+Shift+ Enter и затем эта формула копируется в ячейки Н3:Н11. В диапазоне Н2:Н11 появятся числа .

10. Суммируя эти числа в ячейке Н12, находится выборочное значение .

11. Используя формулу = 4* Н12/(10^2-10)-1 (машинный аналог формулы (****)), находится выборочное значение .

Существенность коэффициента корреляции рангов Кендалла проверяется

–при малом объёме совокупности ( ) с помощью таблиц точного распределения статистики ;

– при больших n для заданного уровня значимости a по формуле:

t_a – коэффициент, определяемый по таблице нормального распределения.

Регрессионный анализ

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

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

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

Регрессионный анализ включает в себя следующие основные этапы:

выбор модели регрессии;

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

проверка значимости параметров модели регрессии и их интерпретация;

проверка адекватности построенной модели регрессии.

Выбор аналитической формы связи осуществляется на основе:

логического теоретического анализа;

графического изображения зависимости в виде эмпирической линии регрессии;

опыта предыдущих исследований, где выбранные формы связи давали удовлетворительные результаты;

различных статистико-математических критериев адекватности конкурирующих уравнений регрессии (остаточных дисперсий, ошибок аппроксимации и др.).

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

При анализе линейной связи применяется прямолинейная функция, математическим выражением которой является уравнение прямой линии:

При анализе нелинейных связей используются следующие функции:

параболическая y_x=a+bx+cx 2

показательная y_x=ab x

логистическая y_x= и др.

Решение математических уравнений связи предполагает вычисление по исходным данным их параметров a и b. Это осуществляется способом выравнивания эмпирических (фактических) данных методом наименьших квадратов (МНК). В основу этого метода положено требование минимальности суммы разности квадрата отклонений эмпирических значений результативного признака от его выровненных (теоретических) значений y_xi, полученных по выбранному уравнению регрессии:

Параметры b₁,… b_n в уравнении регрессии называют коэффициентами регрессии. Если связь по направлению прямая – он имеет положительное значение, если обратная – отрицательное. При линейной связи коэффициент регрессии показывает на сколько единиц своего измерения в среднем изменяется величина результативного признака при изменении факторного признака на единицу своего измерения.

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

общая сумма квадратов = – сумма квадратов отклонений фактических(эмпирических) значений результативного признака от его среднего значения ;

сумма квадратов, обусловленная регрессией = –сумма квадратов отклонений теоретических (расчётных, выровненных) значений результативного признака от его среднего значения ;

сумма квадратов остатков = –сумма квадратов отклонений фактических значений результативного признака от его теоретических значений ;

числа степеней свободы этих сумм .

средний квадрат регрессии или факторная (систематическая) дисперсия– – характеризует колеблемость результативного признака под влиянием только фактора х, входящего в уравнение регрессии;

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

Эти дисперсии связаны между собой равенством, носящим название «правило сложения дисперсий»– ; ;

множественный коэффициент (индекс) корреляции ; в случае парной линейной регрессии этот показатель совпадает с коэффициентом корреляции , а в случае парной нелинейной регрессии носит название теоретического корреляционного отношения;

коэффициент детерминации– ; показывает вариацию результативного признака, обусловленную вариацией факторов, входящих в регрессионную модель;

нормированный (скорректированный) коэффициент детерминации – . где –число факторов, включённых в регрессионную модель. Корректировка не производится при условии, если ;

стандартная ошибка аппроксимации (средняя квадратическая ошибка) уравнения регрессии:

где -число параметров в уравнении регрессии.

стандартное отклонение параметров регрессии– . Наиболее точно эта величина может быть определена по формуле:

где – среднее квадратическое отклонение результативного признака (корень квадратный из общей дисперсии); –среднее квадратическое отклонение — го факторного признака; –величина множественного коэффициента корреляции по фактору с остальными факторами.

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

При проверке значимости параметра регрессии , выдвигается гипотеза о том, что фактор не оказывает заметного влияния на результативный признак. Значимость параметров регрессии проверяется на основе t – критерия Стьюдента:

Параметр признаётся статистически значимым, если расчётное значение t – критерия Стьюдента превосходит его критическое значение, определяемое при заданном уровне значимости α и числе степеней свободы . Критическое значение t – критерия может быть определено по таблице распределения Стьюдента или в Excel по представленному в предыдущем разделе порядку.

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

Расчётное значение критерия Фишера–Снедекора, вычисляется по формуле:

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

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

Если , то точность модели регрессии высокая, если 10-20% – точность модели регрессии хорошая (то есть уравнение достаточно хорошо описывает взаимосвязь между изучаемыми признаками), если 20-50% – точность модели регрессии удовлетворительная.

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

Парная линейная регрессия в Excel осуществляется следующим образом:

Осуществляется ввод исходных данных, т.е. значений факторного и результативного признака.

В меню Сервис выделяется строка Анализ Данных.

В открывшемся окне Анализ данных выделяется процедура Регрессия и нажимается кнопка ОК. Откроется диалоговое окно Регрессия с пульсирующим курсором в поле ввода Входной интервал Y.

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

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

Устанавливается флажок в группе флажков Остатки. В данную группу входят следующие флажки:

– флажок Остатки. При его установке на экран выводится таблица ВЫВОД ОСТАТКОВ, в состав которой входит столбец Остатки;

– флажок График остатков. При активизации этого флажка на экран выводятся графики зависимости остатков от регрессионных переменных (по одному графику на каждую переменную);

– флажок Стандартизированные остатки. При установке данного флажка в таблицу ВЫВОД ОСТАТКОВ добавляется столбец центрированных нормированных (стандартизированных), которые получаются из остатков делением их на ;

– флажок График подбора. При установке этого флажка на рабочий лист выводятся точечных графиков (по числу контролируемых переменных). На графике, связанном с -й контролируемой переменной , =1, 2…., , каждому значению этой переменной поставлены в соответствие две точки и ;

– флажок График нормальной вероятности. При активизации этого флажка на экран выводятся таблица ВЫВОД ВЕРОЯТНОСТИ и график функции, обратной эмпирической функции распределения результативного признака, выполненный на «вероятностной нормальной бумаге».

Щелчком на кнопке ОК запускается процедура Регрессия.

Помимо этого процедура содержит также следующие элементы управления:

Флажок Константа-ноль. Устанавливается в том случае, когда необходимо, чтобы линия регрессии проходила через начало координат. При этом параметр равен нулю и число параметров регрессии равно числу факторов.

флажок Уровень надёжности. Устанавливается в том случае, когда помимо доверительных интервалов для параметров регрессии, соответствующих используемой по умолчанию «стандартной» доверительной вероятности 95%, необходимо вычислить доверительные интервалы, доверительная вероятность которых отличается от «стандартной». «Нестандартная» вероятность, выраженная в процентах, вводится в поле, расположенное справа от рассматриваемого флажка. Если этот флажок не установлен, то выходной таблице параметров регрессии будут одинаковые пары столбцов, содержащие доверительные границы для параметров регрессии, соответствующие одной и той же доверительной вероятности 95% (при редактировании таблицы их можно убрать).

Назначение флажка Метки и переключателей Выходной интервал/Новый рабочий лист/ Новая книга рассмотрено в 1 разделе.

После запуска процедуры Регрессия на рабочем листе появляются три таблицы результатов этой процедуры. В первой таблице «Регрессионная статистика» содержатся значения множественного коэффициента корреляции, коэффициента детерминации, нормированного коэффициента детерминации, стандартная ошибка уравнения регрессии и число наблюдений. Во второй таблице «Дисперсионный анализ» содержатся значения сумм квадратов и среднего квадрата регрессии, остатков и общие., а также расчётное значение критерия Фишера–Снедекора. В третьей таблице в графе «Коэффициенты» по строке «Y- пересечение» находится значение свободного члена уравнения регрессии , а по строке Х – значение параметра . Далее по графам расположены стандартная ошибка, расчётное значение t – критерия Стьюдента, доверительные интервалы для этих параметров.

Полиноминальная (параболическая) регрессия в Excel осуществляется следующим образом:

1. В ячейки А1, В1 и С1 вводятся метки Y, X и X 2 .

2. В диапазон А2 и далее (например, А2: А15) вводятся значения результативного признака, в диапазон В2 и далее (соответственно В2:В15)– значения факторного признака.

3.В диапазон С2 и далее (С2: С15) вводится формула массива = В2:В15^2 и нажимается комбинация клавиш Ctrl+Shift+ Enter. В диапазоне С2:С15 появится столбец квадратов значений факторного признака.

4. В открывшемся окне Анализ данных выделяется процедура Регрессия и нажимается кнопка ОК. Откроется диалоговое окно Регрессия с пульсирующим курсором в поле ввода Входной интервал Y.

5. С помощью мыши выделяется диапазон ячеек, в котором находятся эмпирические значения результативного признака Y. В поле ввода Входной интервал Y появится соответствующая ссылка.

6. Осуществляется переход в поле ввода Входной интервал Х. С помощью мыши выделяется диапазон ячеек, в котором находятся эмпирические значения факторного признака. В поле ввода Входной интервал Х появится соответствующая ссылка.

7. Устанавливается флажок в группе флажков Остатки.

8. Щелчком на кнопке ОК запускается процедура Регрессия.

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

Множественная линейная регрессия в Excel осуществляется аналогичным образом. При этом в качестве исходных данных вводятся значения результативного и нескольких ( ) факторных признаков.

К статистическим функциям, предназначенным для регрессионного анализа в Excel, относятся ЛИНЕЙН, НАКЛОН, ОТРЕЗОК, ТЕНДЕНЦИЯ, ПРЕДСКАЗ, СТОШYХ, ЛГРФПРИБЛ, РОСТ.

Из этих функций интерес представляют функции ЛГРФПРИБЛ, ТЕНДЕНЦИЯ и РОСТ, так как другие функции вычисляют некоторые характеристики, определяемые статистической процедурой РЕГРЕССИЯ, а также дублируют друг друга. Эти же три функции производят вычисления, не предусмотренные статистической процедурой РЕГРЕССИЯ.

Функция ЛГРФПРИБЛ вычисляет выборочные оценки параметров показательной (экспоненциальной) регрессии.

Синтаксис данной функции: ЛГРФПРИБЛ (известные значения у; известные значения х;, конст; стат):

известные значения у– множество значений результативного признака. Данный массив представляет собой вектор-столбец размером ;

известные значения х–множество значений факторных признаков.

– Если в случае парной регрессии этот аргумент опущен, то при вычислениях в качестве массива известные значения х используется массив натуральных чисел 1,2…и т.д. такого же размера, как и массив известные значения у;

– В случае множественной регрессии, если массив известные значения у представляет собой вектор-столбец, то массив известные значения х должен иметь строк и столбцов. При этом каждый столбец этого массива содержит значений определённого факторного признака;

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

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

– Если конст=1 (ИСТИНА) или опущен, то вычисляются и коэффициенты регрессии, и свободный член.

– Если конст= 0 (ЛОЖЬ), то предполагается, что свободный член равен единице.

стат– логическая переменная, определяющая объём выходной информации.

– Если аргумент стат =0 (ЛОЖЬ) или опущен, то функция выдаёт только параметры уравнения регрессии. При этом для вывода результатов решения надо заранее выделить диапазон ячеек размером , где – число факторов, включённых анализ.

– Если аргумент стат =1 (ИСТИНА), то помимо функция выдаёт дополнительную информацию об исследуемой регрессионной зависимости. В этом случае для вывода результатов решения надо выделить диапазон ячеек размером . В первом столбце выделенного диапазона находятся следующие характеристики коэффициенты регрессии, стандартная ошибка коэффициента регрессии, коэффициент детерминации, расчётное значение F- критерия Фишера, сумма квадратов, обусловленная регрессией. Во втором столбце находятся значения свободного члена, его стандартная ошибка, стандартная ошибка уравнения регрессии, число степеней свободы, сумма квадратов остатков.

Так как результатом реализации функции является массив чисел, содержащий выборочные характеристики исследуемой регрессионной зависимости, то функция вводится как формула массива Ctrl+Shift+ Enter. Например, = ЛГРФПРИБЛ (А1:А6;В1:В6;1;1).

Функции ТЕНДЕНЦИЯ и РОСТ используются для вычисления расчётных значений результативного признака, соответствующих заданным пользователем значениям факторных признаков, хранящимся в массиве новые значения х. При этом функция ТЕНДЕНЦИЯ вычисляет параметры линейной и других видов регрессии, линейных относительно входящих в них коэффициентов, таких, например, как полиноминальная (параболическая) регрессия , а функция РОСТ– параметры экспоненциальной регрессии.

Функции вводится как формула массива Ctrl+Shift+ Enter.

Синтаксис данных функций идентичен: ТЕНДЕНЦИЯ (известные значения у; известные значения х;, новые значения х,; конст) и РОСТ (известные значения у; известные значения х;, новые значения х,; конст):

известные значения у– множество значений результативного признака. Данный массив представляет собой вектор-столбец размером ;

известные значения х–множество значений факторных признаков.

– Если в случае парной регрессии этот аргумент опущен, то при вычислениях в качестве массива известные значения х используется массив натуральных чисел 1,2…и т.д. такого же размера, как и массив известные значения у;

– В случае множественной регрессии, если массив известные значения у представляет собой вектор-столбец, то массив известные значения х должен иметь строк и столбцов. При этом каждый столбец этого массива содержит значений определённого факторного признака;

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

новые значения х– новые значения факторных признаков, для которых функция должна вычислить расчётные значения результативного признака;

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

–Массив новые значения х, так же как и массив известные значения х, должен содержать столбец для каждого факторного признака. Число столбцов этих массивов должно быть одинаково.

– Если аргумент новые значения х опущен, то предполагается, что он совпадает с аргументом известные значения х.

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

– Если конст=1 (ИСТИНА) или опущен, то вычисляются и коэффициенты регрессии, и свободный член.

– Если конст= 0 (ЛОЖЬ), то предполагается, что свободный член равен нулю (в случае линейной регрессии) и единице (в случае экспоненциальной регрессии).

Ряды динамики

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

Ряд динамики состоит из двух элементов:

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

периодов (или моментов) времени, к которым относятся данные уровни – .

Одной из основных задач в процессе анализа уровней динамического ряда является определение основной закономерности (тенденции) их изменений во времени.

При этом выделяются следующие основные компоненты динамического ряда:

основная тенденция (тренд) (Т);

Первые три компоненты формируют систематическую составляющую динамического ряда.

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

Сезонная компонента– это колебания, периодически повторяющиеся в некоторое определённое время каждого года, дня месяца или часа дня.

Циклическая (периодическая) компонента проявляется в том, что значение изучаемого показателя в течение какого-то времени возрастает, достигает определённого максимума, затем понижается, достигает определённого минимума, вновь возрастает до прежнего значения и т.д.

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

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

С этой целью проводят выравнивание динамических рядов. Различают два вида выравнивания: механическое (или сглаживание) и аналитическое.

К приёмам механического выравнивания относятся:

усреднение левой и правой половины ряда;

скользящая средняя: простая, взвешенная;

Выбор приема выравнивания зависит от исходной информации и задач исследования.

В среде Excel для выравнивания динамических рядов используются процедуры Скользящее среднее и Экспоненциальное сглаживание, входящие в Пакет анализа.

Сущность метода скользящей средней заключается в том, что вычисляется средний уровень из определенного числа первых по порядку уровней ряда, затем – средний уровень из такого же числа уровней, начиная со второго, затем, начиная с третьего и т.д. Таким образом, при расчётах среднего уровня как бы «скользят» по ряду динамики от его начала к концу, каждый раз отбрасывая один уровень вначале и добавляя один следующий. Этим объясняется название – скользящая средняя.

Следует отметить, что при использовании метода скользящей средней «теряются» членов в начале и в конце динамического ряда (где –размер интервала (окна) сглаживания). Для восстановления «потерянных» уровней в начале и в конце сглаженного ряда для =3 и =5 могут быть использованы следующие формулы:

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

Аналитическое выравнивание производится в следующей последовательности:

1) выделяется этап развития явления и устанавливается характер динамики на этом этапе. Этап развития явления– это период, в течение которого формирование уровней динамического уровня осуществляется под воздействием определённого набора постоянных, периодических и разовых факторов. Решение этой задачи осуществляется не только с помощью статистических методов, а в основном – на базе анализа сущности, природы явлений и общих законов его развития.

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

Основанием для выбора уравнения тренда могут служить:

качественный анализ сущности развития данного явления;

результаты предыдущих исследований в данной области;

графическое изображение эмпирических или скользящих уровней ряда динамики;

статистико-математических критериев адекватности.

При анализе рядов динамики используются следующие математические модели:

линейная y_t = a₀ + a₁t,

где и – параметры уравнения;

– начальный уровень тренда в момент или период, принятый за начало отсчёта времени;

– среднее абсолютное изменение за единицу времени;

Параметр определяет направление развития: если , то уровни ряда равномерно возрастают в среднем за единицу времени на величину , если , то происходит их равномерное снижение.

полиноминальная(параболическая) , где –степень полинома. Наиболее применяемой в практике статистических расчётов является уравнение параболы второго порядка y_t = a₀ + a₁t + a₂t 2 .

Значение параметров и идентично предыдущему уравнению.

Параметр характеризует изменение интенсивности развития в единицу времени. При происходит ускорение развития, при – замедление развития.

Соответственно при параболической форме тренда возможны следующие варианты развития:

если ; – ускорение роста;

если ; – замедление роста;

если ; – замедление снижения;

если ; – ускорение снижения.

экспоненциальная ,

где – константа ряда, –темп изменения в разах. При >1 экспоненциальный тренд выражает тенденцию ускоренного и всё более ускоряющегося возрастания уровней, при <1 экспоненциальный тренд означает всё более замедляющегося снижения уровней динамического ряда.

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

гиперболическая y_t = a₀ + a₁ – применяется для отображения тенденции процессов, ограниченных предельным значением уровня;

степенная – применяется для отображения тенденции явлений с разной мерой пропорциональности изменений во времени;

логистическая и др.

Наиболее точным способом выбора формы тренда является применение статистико-математических критериев, в качестве которых могут выступать остаточное среднее квадратическое отклонение, средняя ошибка аппроксимации ( ), стандартизированная ошибка аппроксимации ( ), относительная ошибка аппроксимации (модифицированный коэффициент вариации):

y и — соответственно фактические и теоретические значения ряда динамики;

n – число уровней ряда;

m – количество параметров в уравнении тренда.

Предпочтение отдаётся той функции, которая имеет наименьшую величину критерия.

Если , то точность модели тренда высокая, если = 10-20% – точность модели тренда хорошая (то есть уравнение достаточно хорошо описывает основную тенденцию развития изучаемого явления), если =20-50% – точность модели тренда удовлетворительная.

3) Вычисляются параметры уравнения тренда, и по ним производится синтезирование трендовой модели.

Расчёт параметров уравнений тренда может быть произведён различными способами:

методом средних значений (или линейных отклонений);

методом конечных разностей;

методом наименьших квадратов.

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

å (y – y_t) 2 = min.

4) На основе синтезированной модели тренда вычисляются теоретические уровни.

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

База прогнозирования – длина или продолжительность базисного периода, закономерность которого будет распространяться на будущее.

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

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

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

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

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

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

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

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

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

1. В диапазон ячеек вводятся уровни ряда динамики (числовые значения изучаемого статистического показателя).

2.В меню Сервис выделяется строка Анализ данных.

3. В открывшемся окне Анализ данных выделяется процедура Скользящее среднее и нажимается кнопка ОК. На экране появится диалоговое окно Скользящее среднее.

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

5. В поле Интервал вводится размер окна сглаживания (по умолчанию =3).

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

7. Устанавливается флажок Вывод графика. Флажок Стандартные погрешности устанавливается при необходимости получения стандартных погрешностей сглаживания. Назначение флажка Метки рассмотрено в 1 разделе.

8. Нажимается кнопка ОК.

Следует иметь в виду, что процедур Скользящее среднее выдаёт сглаженный ряд так называемых адаптивных скользящих средних. Этот ряд сдвинут на шагов вправо относительно «канонического» ряда скользящих средних. Для сравнения простого и адаптивного скользящих средних в диапазоне ячеек, число которых на -1 меньше числа уровней исходного ряда динамики, свободного столбца, рассчитываются значения скользящих средних, вычисленные по канонической формуле = СРЗНАЧ по диапазону из первых уровней динамического ряда (например, при =3 А1:А3). Данная формула вводится в следующую после по счёту ячейку столбца, предназначенного для расчёта канонических средних (например, при =3–во вторую (С2), при =5 (С3) и т.д.). Затем данная формула копируется в оставшийся диапазон ячеек этого столбца. Адаптивные скользящие средние могут быть вычислены также с помощью статистической процедуры Добавить линию тренда (см. ниже).

При проведении экспоненциального сглаживания использование одноимённой процедуры аналогично выше рассмотренному порядку. Вместо поля Интервал диалогового окна Скользящее среднее в процедуре Экспоненциальное сглаживание заполняется поле Фактор затухания. В это поле вводится фактор затухания , где – параметр сглаживания (вес текущего значения при вычислении экспоненциального среднего, ). Параметр характеризует скорость реакции экспоненциального среднего на изменение текущего значения динамического ряда и одновременно определяет его способность сглаживать случайные колебания. Чем больше , тем быстрее реакция экспоненциального среднего на изменение динамического ряда и тем меньше его сглаживающие возможности. В качестве приемлемого компромисса рекомендуется брать в пределах от 0,1 до 0,3. Следовательно, приемлемыми значениями фактора затухания являются значения из интервала от 0,7 до 0,9. В статистической процедуре Экспоненциальное сглаживание по умолчанию , что противоречит рекомендациям.

При аналитическом выравнивании в Excel используются статистическая процедура Регрессия и статистические функции регрессионного анализа ЛИНЕЙН, ПРЕДСКАЗ, ЛГРФПРИБЛ, ТЕНДЕНЦИЯ и РОСТ, рассмотренные в предыдущем разделе. В этом случае при использовании статистической процедуры Регрессия вместо значений факторного признака вводятся натуральные числа 1,2,…. , обозначающие порядковые номера периодов или моментов времени. При использовании статистических функций натуральные числа можно не вводить, а оставить пропущеным аргумент известные значения х. Тогда при вычислениях в качестве массива известные значения х используется массив натуральных чисел 1,2…и т.д. такого же размера, как и массив известные значения у.

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

Для доступа к процедуре Добавить линию тренда необходимо:

1. В диапазон ячеек определённого столбца ввести уровни исследуемого динамического ряда.

2. С помощью Мастера Функций построить диаграмму (график) ряда динамики.

3. Щелчком на диаграмме активизировать её. На панели меню на месте пункта Данные появится пункт Диаграмма.

4. В пункте меню Диаграмма выбрать команду Добавить линию тренда. Откроется диалоговое окно Линия тренда.

5. В открывшемся окне Линия тренда раскрыть вкладку Тип.

6. На этой вкладке в разделе Построение линии тренда (аппроксимация и сглаживание) выбрать тип (вид) функции тренда.

7. В списке Построен на ряде выделить ряд данных, для которых строится линия тренда.

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

Эта вкладка содержит следующие элементы управления:

группу переключателей Название аппроксимирующей (глаженной) кривой, состоящую из двух переключателей. При установке переключателя автоматическое Excel автоматически присваивает линии тренда имя, связанное с типом этой линии и названием данных наблюдения, по которым строится линия тренда, например, Линейный (Урожайность зерновых). При установке переключателя другое пользователь сам устанавливает имя линии регрессии и вводит это имя в поле Линейный (Ряд 1), расположенное справа от переключателя (максимальная длина имени 256 символов);

группу счётчиков Прогноз, в которую входят два счётчика: вперёд на…единиц и назад на…единиц. С помощью этих счётчиков устанавливается срок прогноза и производится экстраполяция и интерполяция ряда динамики. Счётчики недоступны в режиме Скользящее среднее;

флажок пересечение кривой с осью Y в точке. Если этот флажок не установлен, ордината точки пересечения линии тренда с осью Y вычисляется по данным наблюдения. Как правило, этот флажок не устанавливается. Используя этот флажок и расположенное справа от него поле ввода, можно задать нужную ординату точки пересечения (при активном флажке и нуле в поле ввода линия тренда пройдет через начало координат);

флажок показывать уравнение на диаграмме. При установке этого флажка в область построения диаграммы выводится аналитическое выражение (формула) функции тренда;

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

9. Установить нужные переключатели, счётчики и флажки. Щёлкнуть на кнопке ОК.

Список рекомендуемой литературы

1. Вадзинский Р. Статистические вычисления в среде Еxcel. –СПб.: Питер,2008.

2. Макарова Н.В. Трофимец В.Я. Статистика в Еxcel.– М.: Финансы и статитсика, 2006.

3. Берк К. Кэйри П. Анализ данных с помощью MS Еxcel.–М.: Вильямс, 2005.

4. Васильев А.Н. Научные вычисления в Microsoft Excel.–М.; Спб.; Киев: Диалектика, 2004.

5.Вуколов Э.А. Основы статистического анализа: практикум по статистическим методам и исследованию операций с использованием пакетов STATISTICA и Еxcel.– М.: Форум; Инфра–М, 2004.

6. Минько А.А. Статистический анализ в среде Еxcel.–М., СПб., Киев: Диалектика, 2004.

7. Гайдышев И. Анализ и обработка данных.–СПб; М.: Питер, 2001.

8. Елисеева И.И., Юзбашев М.М. Общая теория статистики: Учебник. М: Финансы и статистика, 2005

9. Ефимова М.Р., Петрова Е.В., Румянцев В.Н. Общая теория статистики: Учебник. – М.: ИНФРА- М, 2006.

10. Теория статистики: Учебник / Под ред. Р.А. Шмойловой .4-е изд., доп. и перераб. — М.: Финансы и статистика, 2005.

Работа с инструментом «Регрессия» в Microsoft Excel

Диалоговое окно «Регрессия». В первое окно «Входной интервал Y» вводим данные объясняемой переменной — у, диапазон должен состоять из одного столбца. Во второе окно «Входной интервал X» вводим данные объясняющих переменных — х. На рис. П.1 представлены у. $С$2:$С$13, х: $В$2:$В$13. Длины интервалов должны быть одинаковы. Если строится уравнение множественной регрессии, то данные объясняющих переменных вводятся в окно «Входной интерват X» соответствующим образом. На рис. П.2 представлены у: $D$2:$D$13, x_t—x₂: $В2:$С$13. Максималь- ное число независимых объясняющих переменных равно 16.

Рис. П. 1. Задание парной регрессии

Ставим «галочку» в окно «Метки», если в отчете Microsoft Excel требуется знать, к какой из объясняющих переменных относятся результирующие данные.

Если исследователю не требуется константа Ь₀, то ставим «галочку» в окно «Константа — ноль». Линия регрессии пройдет через начало координат.

Рис. П.2. Задание множественной регрессии

«Уровень надежности». По умолчанию программа строит уравнение регрессии для доверительной вероятности (уровень надежности) 0,95. Если требуется другая величина, ставим «галочку» в окно «Уровень надежности» и в окно, помеченное символом «%», вводим требуемую величину уровня надежности десятичной дробью.

«Параметры вывода». Указываем, куда вывести результаты регрессионного анализа: на этом листе, как указано на обоих рисунках, на другой рабочий лист или в новую рабочую книгу.

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

Заполнив диалоговое окно «Регрессия», нажимаем кнопку ОК. Программа выводит отчет «Вывод итогов» в виде трех таблиц (рис. П.З, приведено для двух объясняющих переменных).

Приведем описание таблиц (первых двух — в табл. П1.1 и П1.2 соответственно, третьей — в текстовом виде).

Таблица П1.1

Описание первой таблицы

Наименование в отчете

Множественный R

Коэффициент множественной корреляции, индекс корреляции

Коэффициент детерминации, R 2

Скорректированный К 2

Наименование в отчете

Среднее квадратическое отклонение от модели

Рис. П.З. Результаты работы программы

Описание третьей таблицы

Данные первой строки относятся к коэффициенту уравнения регрессии Ь₀, данные второй строки — к коэффициенту Ь_и третьей — к Ь₂ и далее до коэффициента Ь_т, но числу объясняющих переменных в уравнении.

Метки, если поставлена галочка в окно «Метки». У-пересечение для коэффициента />₀, далее но всем объясняющим переменным.

Значения коэффициентов уравнения регрессии Ь₀, Ь_ь . Ь_т.

Стандартная ошибка коэффициента регрессии 5^, 5_Л). S_bm.

Статистическая значимость коэффициента регрессии (^-статистика) для а = 0,05 t_w . t_bm.

P-значение — это значение уровней значимости, соответствующее вычисленным ^статистикам коэффициентов.

Нижние 95% и Верхние 95% — это нижние и верхние границы 95%-ных доверительных интервалов для коэффициентов уравнения регрессии. Если в окно «Уровень надежности» не вводилось другое значение доверительной вероятности, то последние два столбца дублируют предыдущие два столбца. Если в окно «Уровень надежности» было введено другое значение доверительной вероятности у, то последние два столбца содержат значения соответственно нижней и верхней границы у-процентных доверительных интервалов.

Корреляция входной интервал содержит нечисловые данные что делать

Входной интервал содержит нечисловые данные что делать?

Работа с инструментом «Регрессия» в Microsoft Excel

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

Рис. П. 1. Задание парной регрессии

Ставим «галочку» в окно «Метки», если в отчете Microsoft Excel требуется знать, к какой из объясняющих переменных относятся результирующие данные.

Рис. П.2. Задание множественной регрессии

«Уровень надежности». По умолчанию программа строит уравнение регрессии для доверительной вероятности (уровень надежности) 0,95. Если требуется другая величина, ставим «галочку» в окно «Уровень надежности» и в окно, помеченное символом «%», вводим требуемую величину уровня надежности десятичной дробью.

«Параметры вывода». Указываем, куда вывести результаты регрессионного анализа: на этом листе, как указано на обоих рисунках, на другой рабочий лист или в новую рабочую книгу.

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

Заполнив диалоговое окно «Регрессия», нажимаем кнопку ОК. Программа выводит отчет «Вывод итогов» в виде трех таблиц (рис. П.З, приведено для двух объясняющих переменных).

Приведем описание таблиц (первых двух — в табл. П1.1 и П1.2 соответственно, третьей — в текстовом виде).

Описание первой таблицы

Наименование в отчете

Коэффициент множественной корреляции, индекс корреляции

Коэффициент детерминации, R 2

Скорректированный К 2

Наименование в отчете

Среднее квадратическое отклонение от модели

Рис. П.З. Результаты работы программы

Описание третьей таблицы

Стандартная ошибка коэффициента регрессии 5^, 5_Л). S_bm.

P-значение — это значение уровней значимости, соответствующее вычисленным ^статистикам коэффициентов.

Нижние 95% и Верхние 95% — это нижние и верхние границы 95%-ных доверительных интервалов для коэффициентов уравнения регрессии. Если в окно «Уровень надежности» не вводилось другое значение доверительной вероятности, то последние два столбца дублируют предыдущие два столбца. Если в окно «Уровень надежности» было введено другое значение доверительной вероятности у, то последние два столбца содержат значения соответственно нижней и верхней границы у-процентных доверительных интервалов.

Описание второй таблицы

df — число степеней свободы

SS — сумма квадратов

MS = SS/df — дисперсия на одну степень свободы

Использование Пакета анализа EXCEL для построения простой линейной регрессионной модели

history 26 января 2019 г.

Проведем простой регрессионный анализ с помощью надстройки MS EXCEL Пакет анализа .

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

После выбора этого инструмента откроется окно, в котором требуется заполнить следующие поля (см. файл примера лист Надстройка ):

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

Тот же результат можно получить с помощью формул (см. файл примера лист Надстройка, столбцы I:T ):

Отчет, сформированный надстройкой, состоит из следующих разделов:

Раздел «Регрессионная статистика»:

Раздел « Дисперсионный анализ »:

Регрессия входной интервал содержит нечисловые данные

трюки • приёмы • решения

При импортировании данных из других источников вы, возможно, уже успели обнаружить, что Excel иногда некорректно импортирует значения. В частности, он может принять ваши числа за текст. И тогда, например, при суммировании диапазона значений формула СУММ возвращает 0 — хотя диапазон, по всей видимости, содержит числовые значения.

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

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

Входной интервал. Нужно ввести ссылку на интервал данных рабочего листа, подлежащих анализу. Excel также прелагает группирование входных данных по строкам или столбцам. Если во входной интервал включаются метки (заголовки строк или столбцов данных), необходимо установит флажок Метки,в противном случае Excel выдаст предупреждающее сообщение.

Метки. Если входной интервал не включает меток, снимите флажок Метки. Excel генерирует соответствующие метки данных для выходной таблицы (Строка 1, Строка 2, или Столбец 1, Столбец 2 и т.д.)

Выходные данные

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

Новый рабочий лист. Этот параметр вставляет новый лист в рабочую книгу, где располагается текущий рабочий лист, и вставляет результаты в ячейку А1 нового листа. Используйте поле ввода рядом с параметром для задания имени нового листа.

Новая рабочая книга. Этот параметр создает новую рабочую книгу, добавляет новый рабочий лист и вставляет результаты в ячейку А1 нового листа.

Генерация случайных чисел

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

Для генерации случайных величин необходимо иметь возможность получать последовательность равномерно распределенных случайных чисел, т.е. чисел, которые ведут себя как независимые реализации или выборки случайной величины R, равномерно распределенной на единичном интервале [0,1]. Такие числа получают с помощью генераторов случайных чисел.

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

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: Для студента самое главное не сдать экзамен, а вовремя вспомнить про него. 10663 — | 7824 — или читать все.

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

Функция СРЗНАЧ вычисляет среднее арифметическое значение. Она игнорирует пустые, логические и текстовые ячейки и может использоваться вместо длинных формул. Например, для вычисления среднего значения данных в диапазоне ячеек В4:В15 можно использовать формулу:

Очевидно, что проще ввести = СРЗНАЧ(B4:B15).

Функция МЕДИАНА вычисляет медиану множества числовых значений.

Функция МОДА определяет значение, которое чаще других встречается во множестве чисел.

Функция МАКС вычисляет наибольшее значение в диапазоне.

Функция МИН вычисляет наименьшее значение в диапазоне.

Функция СЧЕТ определяет количество ячеек в заданном диапазоне, которые содержат числа, в том числе, даты и формулы, возвращающие числа.

Функции ДИСП и СТАНДОТКЛОН определяют дисперсию и стандартное отклонение чисел, в предположении что они образуют выборку.

Функции ДИСПР и СТАНДОТКЛОНП определяют дисперсию и стандартное отклонение для генеральной совокупности.

Функция НАКЛОН вычисляет коэффициент наклона линии линейной регрессии.

Функция ОТРЕЗОК вычисляет отрезок, отсекаемый на оси линией линейной регрессии.

Функция ПРЕДСКАЗ вычисляет теоретические значения y по линии линейной регрессии.

Чтобы получить доступ к инструментам Пакета анализа необходимо:

· выполнить команду Сервис/Анализ данных;

· для использования инструмента анализа, выбрать его имя в списке и нажать кнопку ОК;

· заполнить открывшееся диалоговое окно (в большинстве случаев это означает задание входного диапазона с данными, которые вы собираетесь анализировать, указание верхней левой ячейки выходного диапазона, в который должны быть помещены результаты, и выбор нужных параметров. Группирование: установить переключатель в положение По столбцам или По строкам в зависимости от расположения данных во входном диапазоне. Установить переключатель в положение Метки в первой строке, если первая строка во входном диапазоне содержит названия столбцов или установить переключатель в положение Метки в первом столбце, если названия строк находятся в первом столбце входного диапазона. Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически).

Если надстройка Анализ данных отсутствует, то ее можно подключить с помощью команды Сервис/Надстройки/Пакет анализа VBA ( Analysis ToolPak VBA ).

Инструмент Описательная статистика предлагает таблицу основных статистических характеристик для одного или нескольких множеств входных значений ( Рис. 7.1 ):

Корреляция используется для количественной оценки взаимосвязи двух наборов данных, представленных в безразмерном виде. Корреляционный анализ дает возможность установить, ассоциированы ли наборы данных по величине, то есть, большие значения из одного набора данных связаны с большими значениями другого набора (положительная корреляция), или, наоборот, малые значения одного набора связаны с большими значениями другого (отрицательная корреляция), или данные двух диапазонов никак не связаны (корреляция близка к нулю). В диалоговом окне Корреляция ( REF _Ref12174106 h * MERGEFORMAT Рис. 7.2 ) указывается Входной интервал – ссылка на диапазон, содержащий анализируемые данные. Ссылка должна состоять как минимум из двух смежных диапазонов данных, организованных в виде столбцов или строк.

Регрессия используется для подбора графика линии регрессии. Параметры диалогового окна Регрессия ( Рис. 7.3 ):

Входной интервал Y – ссылка на диапазон анализируемых зависимых данных (диапазон должен состоять из одного столбца). Входной интервал X – ссылка на диапазон независимых данных, подлежащих анализу. Уровень надежности – установить флажок, чтобы включить в выходной диапазон дополнительный уровень. В соответствующее поле ввести уровень надежности, который будет использован дополнительно к уровню 95%, применяемому по умолчанию. Константа-ноль – установить флажок, чтобы линия регрессии прошла через начало координат. Остатки – установить флажок, чтобы включить остатки в выходной диапазон. Стандартизированные остатки – установить флажок, чтобы включить стандартизированные остатки в выходной диапазон. График остатков – установить флажок, чтобы построить диаграмму остатков для каждой независимой переменной. График подбора – установить флажок, чтобы построить диаграммы наблюдаемых и предсказанных значений для каждой независимой переменной. График нормальной вероятности – установить флажок, чтобы построить диаграмму нормальной вероятности.

Статистический анализ в excel Назначение и возможности пакета анализа

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

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

Скользящее среднее используется для расчета значений в прогнозируемом периоде на основе среднего значения переменной для указанного числа предшествующих периодов. Процедура может использоваться для прогноза сбыта, инвентаризации и других процессов. Мы спрогнозируем курс доллара США на основе данных за июль 1999 года.

Экспоненциальное сглаживание предназначается для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе. Использует константу сглаживания, по величине которой определяет, насколько сильно влияют на прогнозы погрешности в предыдущем прогнозе. Для константы сглаживания наиболее подходящими являются значения от 0,2 до 0,3. Эти значения показывают, что ошибка текущего прогноза установлена на уровне от 20 до 30 процентов ошибки предыдущего прогноза. Более высокие значения константы ускоряют отклик, но могут привести к непредсказуемым выбросам. Низкие значения константы могут привести к сдвигу аргумента для предсказанных значений.

Линейный регрессионный анализ заключается в подборе графика для набора наблюдений с помощью метода наименьших квадратов. Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных. Мы рассмотрим, как влиял на курс ЕВРО по отношению к рублю курс доллара США в июле 1999 года.

Установка пакета анализа.

Если в Microsoft Excel в меню Сервисотсутствует командаАнализ данных, то необходимо установить статистический пакет анализа данных.

Чтобы установить пакет анализа данных

ВменюСервисвыберите командуНадстройки. Если в списке надстроек нет пакета анализа данных, нажмите кнопкуОбзори укажите диск, папку и имя файла для надстройки пакет анализа, Analys32.xll (как правило, папка LibraryAnalysis) или запустите программу Setup, чтобы установить эту надстройку.

Установите флажок Пакет анализа,выберите кнопкуOK.

Вызов пакета анализа

Чтобы запустить пакет анализа:

В меню Сервисвыберите командуАнализ данных.

В списке Инструменты анализавыберите нужную строку.

Корреляция

При выборе строки Корреляцияв диалоговом запросеАнализ данныхпоявляется следующее окно.

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

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

Метки в первой строке/Метки в первом столбце. Установите переключатель в положениеМетки в первой строке, если первая строка во входном диапазоне содержит названия столбцов. Установите переключатель в положениеМетки в первом столбце, если названия строк находятся в первом столбце входного диапазона. Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически. (В других видах анализа этот флажок выполняет аналогичную функцию).

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

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

Новая книга. Установите переключатель, чтобы открыть новую книгу и вставить результаты анализа в ячейку A1 на первом листе в этой книге.

Смотри лист Корреляция в примере.

Вернитесь в текущий документ через Панель задач

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

Регрессионный анализ. Построение статических однофакторных моделей

Практическая работа 1. Регрессионный анализ. Построение статических однофакторных моделей.

Содержательная постановка задачи. Имеется статистическая информация по центральному федеральному округу, которая представлена в таблице1:

Таблица 1. Число гостиниц и число ночевок в гостиницах

Наименование субъекта Федерации

Число гостиниц и аналогичных средств размещения, ед.

Число ночевок в гостиницах и аналогичных средствах размещения, тыс. ночевок

* — на базе данных Федеральной службы государственной статистики.

Пусть ряд наблюдений X — число гостиниц и аналогичных средств размещения, ряд наблюдений Y — число ночевок в гостиницах и аналогичных средствах размещения, тыс.

Часть I.

Построить точечную диаграмму, предварительно отсортировав таблицу; Выдвинуть гипотезу о виде функции зависимости; Рассчитать параметры модели регрессии, построить тренды; Оценить адекватность построенного уравнения по величине достоверности аппроксимации; Рассчитать теоретические значения по модели и построить графики фактических и расчетных данных. Создать отчет в Word по всем пунктам задания, используя экранные копии Excel. Написать вывод о виде функции зависимости, наилучшим образом описывающей модель.

Пример. В исходной таблице произведем сортировку по столбцу С (число гостиниц и аналогичных средств размещения) по возрастанию числа гостиниц.

Рис.1. Сортировка по возрастанию числа гостиниц

Отсортированная таблица представлена на рисунке 2:

Рис.2. Отсортированная таблица по возрастанию числа гостиниц

По отсортированным данным, используя мастер диаграмм, построим точечную диаграмму (диапазон ячеек С1:D19) (Рис.3).

Рис. 3. Диаграмма по отсортированной таблице

После построения диаграммы, вызовем контекстовое меню, щелкнув правой кнопкой мыши по одной из точек диаграммы, и выберем в нем команду Добавить линию тренда…(Рис. 4):

Рис. 4. Вкладка Параметры линии тренда

Во вкладке Параметры линии тренда выберем Линейная и отметим флаги показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). Чем R2 ближе к 1, тем удачнее регрессионная модель. На диаграмме появляется линия тренда (Рис. 5).

Рис.5. Диаграмма с линейной линией тренда

Чаще всего выбор производится среди следующих функций:

у = ах + b — линейная функция;

у = ах2 + bх + с — квадратичная (полиномиальная) функция;

у = аln(х) + b — логарифмическая функция;

у = аеbх — экспоненциальная функция;

у = ахb — степенная функция.

Отобразим на диаграмме все возможные тренды (Рис. 6.).

Рис. 6. Диаграмма с построенными линиями тренда

Часть II.

Требуется: рассчитать основные характеристики случайных величин.

Для расчета основных характеристик случайных величин используются следующие функции: СРЗНАЧ() – возвращает среднее арифметическое своих аргументов, КОРЕНЬ() – возвращает значение квадратного корня, а также ДИСП() и КОРРЕЛ().

ДИСП() — Оценивает дисперсию по выборке (Рис.7).

Число1, число2. — от 1 до 255 числовых аргументов, соответствующих выборке из генеральной совокупности.

Рис. 7. Аргументы функции, оценивающей дисперсию по выборке — Дисп()

КОРРЕЛ() – возвращает коэффициент корреляции между интервалами ячеек «массив1» и «массив2». Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера (Рис. 8).

Синтаксис функции: КОРРЕЛ(массив1;массив2).

Массив1 — это интервал ячеек со значениями, Массив2 — второй интервал ячеек со значениями.

Рис.8. Аргументы функции, возвращающей коэффициент корреляции Коррел()

Получим следующие результаты (Рис. 9):

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

Можно сделать вывод о том, что линейная зависимость между числом гостиниц и аналогичных средств размещения (ряд X) и числом ночевок в гостиницах и аналогичных средствах размещения (ряд Y) существует, т. к. коэффициент корреляции равен 0,93729 и .

Коэффициент корреляции значим, т. к. расчетный критерий Стъюдента больше табличного критерия: 10,7566 > 2.1190.

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

Вызвать окно Анализ данных можно с помощью команды Анализ данных меню Данные (Рис. 10).

Рис. 10. Анализ данных

Пакет Корреляция позволяет определить коэффициенты корреляции для n-го количества рядов данных. Выбор команды Корреляция вызывает окно Корреляция (Рис. 11).

Рис. 11. Окно Корреляция

Это окно содержит две панели Входные данные и Параметры вывода. Окно Входной интервал: предназначено для ссылки на диапазон, содержащий анализируемые данные. Эта ссылка должна состоять не менее чем из двух смежных диапазонов данных, расположенных по строкам или столбцам. Флаги Группирование: зависят от расположения данных в диапазоне. Флаг Метки в первой строке (Метки в первом столбце) устанавливается в том случае, если входной интервал включал название диапазонов. Если название диапазонов были включены в интервал, а данный флаг не выставлен, после нажатия кнопки Ок, Excel выдаст сообщение об ошибке «Входной интервал содержит нечисловые данные». Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически.

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

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

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

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

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

Заполняем все необходимые поля окна Корреляция (Рис. 12).

Входной интервал – это данные, по которым необходимо провести корреляционный анализ, в данном случае это исходные данные по числу гостиниц и аналогичных средств размещения и числу ночевок в гостиницах и аналогичных средствах размещения (С2:D19). Строка 1 также указана во входном интервале, но в ней содержатся заголовки столбцов, поэтому ставим флаг Метки в первой строке.

В выходном интервале ставим Новый рабочий лист, в котором будут вынесены результаты расчета.

Рис. 12. Расчет коэффициента корреляции

Полученные данные абсолютно идентичны коэффициентам полученным с помощью функции КОРРЕЛ() (Рис. 13).

Рис. 13. Результаты расчета коэффициента корреляции

Пакет Описательная статистика предназначен для расчета основных статистических показателей. Окно Описательная статистика (Рис. 14) содержит:

Рис.14. Описательная статистика

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

Далее вызовем функцию Описательная статистика из пакета Анализ данных (Рис. 15).

Рис. 15. Описательная статистика пакета Анализ данных

Выставив все необходимые флаги, нажимаем кнопку Ок, и получаем таблицу описательных статистик (Рис. 16).

Рис. 16. Таблица описательных статистик

Полученные данные совпадают с данными, рассчитанными с помощью математических функций (математическое ожидание по x и по y, дисперсия по x и по y, среднее квадратическое отклонение по x и по y).

Варианты заданий к практической работе 1.

Содержательная постановка задачи. Исходные данные:

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

Вариант 2. Имеется статистическая информация по Северо-Западному федеральному округу*, которая представлена в таблице: