Прогнозирование и анализ экономических процессов

Тема 4. Прогнозирование и анализ экономических процессов.

Вопрос 1. Понятие ряда динамики, их разновидности и расчет элементарных показателей динамики.

Вопрос 2.  Графическое представление информации

Вопрос 3.  Способы прогнозирования рядов динамики.

Вопрос 4. Статистический анализ  

Вопрос 1 Понятие ряда динамики, их разновидности и расчет элементарных показателей динамики.

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

Уровень развития может выражаться абсолютными, относительными или средними величинами.

Рекомендуемые материалы

Различают:

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

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

Дискретные ряды различают:

- простые:

- моментные - характеризуют размеры изучаемого явления в определенные моменты времени и даты;

- интервальные - за определенные промежутки времени;

         - сложные:

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

- ряды относительных величин.

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

- уровень ряда – каждый член ряда динамики;

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

- средний уровень ряда;

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

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

- автокорреляция – связь между уровнями ряда динамики, характеризуется коэффициентом корреляции. Чем ближе коэффициент к единице, тем сильнее влияние одного уровня ряда на другой. Знак коэффициента корреляции указывает тип зависимости – прямую или обратную. Для определения этого показателя в Ехсе1 служит функция КОРРЕЛ.

Вопрос 2  Графическое представление информации. Типы диаграмм, предлагаемые Ехсе1 и использование диаграмм для прогноза.

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

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

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

Из стандартных типов в товароведно-экспертной области деятельности целесообразно использовать:

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

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

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

         Диаграмма как объект имеет ряд элементов: название диаграммы, Область диаграммы, Область построения диаграммы, Оси и названия осей: Х (категорий) и Y (значений), Маркеры данных, Легенду.

Для создания диаграммы необходимо выделить диапазон, содержащий требуемые данные. Для выделения несмежных ячеек или несмежных диапазонов - использовать клавишу Ctrl. Если нужно включить в диаграмму название строк и столбцов, то их нужно включить их при выделении диапазона (в таком случае в соответствии с наименованиями, указанными в заголовках, на оси Х могут автоматически отразиться наименования категорий, а в легенде  - соответствующие наименования рядов). Команда Вставка→Диаграмма (или кнопка Мастер диаграмм стандартной панели инструментов) открывает многошаговое диалоговое окно. Для перехода по шагам построения диаграммы выбирается кнопка Далее или Назад в соответствующем окне шага. Для того, чтобы применить диаграмму выбирают клавишу Готово.

ШАГ 1: Выбор типа и вида диаграммы. После выбора в списке, расположенном в левой части диалогового окна, типа диаграммы, необходимо определить, какой ее вид наиболее предпочтителен. Виды различают в зависимости, объемным или необъемным будет изображение, как будут отражать данные – с накоплением (прибавлять предыдущее значение) или будут нормированы, т.е. отражать долю каждой категории в общей сумме. Интерес представляют также виды круговой диаграммы, в котором значения одного из секторов могут быть вынесены в гистограмму. Информация о выбранном виде диаграммы выводится справа в нижней части окна. Выбрав кнопку Просмотр результата (внизу слева) можно увидеть эскиз диаграммы, построенной на основе выделенных данных.

ШАГ 2: Определение источника данных диаграммы. В диалоговом окне Мастер диаграмм выделенный диапазон данных отображается в поле диапазон данных. При необходимости его можно изменить, а также варьировать направление рядов данных (в строках или в столбцах).

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

ШАГ 3: Установление параметров диаграммы. В диалоговом окне:

-  Заполняются поля для подписей названий осей и всей диаграммы.

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

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

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

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

ШАГ 4: Выбор местоположения диаграммы. Позволяет указать размещение диаграммы: на имеющемся листе (на котором находится таблица с данными) или на отдельном (диаграмма займет весь лист).

После построения диаграммы в нее могут быть внесены изменения. При обращении к разным объектам в контекстном меню становятся доступны те или иные команды. Так, при щелчке в области диаграммы и области построения диаграммы к таким командам относятся: Тип диаграммы, Исходные данные, Параметры диаграммы, Размещение, Формат области диаграммы или области построения диаграммы. При щелчке по маркерам  рядов данных - это команды Тип диаграммы, Исходные данные, Формат рядов данных и Добавить линию тренда. Для остальных объектов диаграммы доступна команда Формат, например, формат оси или формат линий сетки.

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

В целом, следует отметить, что в команде Формат… можно настроить параметры шрифта, линий, заливки их цвет, цвет заливки и т. д. Для отдельных объектов имеются специфические вкладки диалогового окна данной команды. Так, для команды Формат оси будет присутствовать вкладка Шкала с указанием минимального и максимального значений по оси, цены ее основных и промежуточных делений,  в каком значении будут пересекаться оси и др., а также вкладка Число для установления формата данных и Выравнивание – для изменения ориентации подписей относительно горизонтали. Для команды Формат ряда – это может быть Порядок рядов, Параметры, где указывается Ширина перекрытий (рядами друг друга), Зазора между рядами, для круговой диаграммы – определение угла поворота для просмотра частей и установление или отмена флажка Дольки разного цвета, а в объемном виде гистограммы – еще и вкладка Фигура для указания формы  фигуры: пирамида, цилиндр и др.

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

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

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

Вопрос 3.  Способы прогнозирования рядов динамики.  

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

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

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

         К способам получения прогноза относятся:

1.Использование маркера заполнения.

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

Данный способ реализуется следующим образом:

а) выделить в таблице все имеющиеся значения экономического показателя за прошедшие периоды времени;

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

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

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

2. Использование надстроек Пакета анализа.

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

3. Использование специальных функций.

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

Формат функции ТЕНДЕНЦИЯ имеет следующий вид:

ТЕНДЕНЦИЯ (интервал значений показателя У; интервал значений показателя Х, новые значения Х, константа).

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

1. установить курсор в клетку таблицы, где должно быть получено первое прогнозируемое значение;

2. обратиться к функции ТЕНДЕНЦИЯ и задать значение показателя У (зависимой переменной) и показателя Х (новые значения Х можно опустить, в таком случае будет предполагаться, что они совпадают с известными значениями Х; константу также можно не задавать).

3. скопировать формулу на весь интервал.

Получение прогноза возможно также и другим способом: в функции ТЕНДЕНЦИЯ можно указать дополнительную переменную в виде адреса клетки, где требуется задать нужный период для прогноза (новое значение Х). В этом случае в клетке, где содержалась формула, использующая данную функцию от 3-х переменных,  высветится величина прогноза.

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

4. Использование диаграммы.

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

Применение этого способа позволяет расширить круг используемых математических зависимостей за счет полиномиальных, степенных и логарифмических функций. Наличие большого количества нелинейных функций дает возможность  выбрать вид наиболее удачной аппроксимирующей функции для экстраполяции изучаемого признака. Для этого лучше использовать диаграмму типа «График» или «Точечная»,  щёлкнуть правой клавишей мыши на линии графика или маркерах, в появившемся меню выбрать команду Добавить линию тренда и затем в открывшемся окне на вкладке Тип установить тип линии (линейная, степенная, полиномиальная, экспоненциальная, логарифмическая зависимости),  на вкладке параметры установить флажки  в позициях выводить на экран линию тренда и выводить на экран R^2. Коэффициент R² показывает уровень надёжности. Чем ближе его значение к единице, тем более верно описываются функции тенденции развития рассматриваемого показателя (ниже 0,5 – моделью пользоваться нельзя,  выше 0,5 – можно, выше 0,7 – модель очень хорошая). Появившуюся на диаграмме функцию ввести в таблицу как формулу и путем копирования с использованием относительной адресации (можно при использовании маркера заполнения) получить теоретические значения данного показателя. Чтобы получить прогноз в функцию вместо переменной x поставить нужное значение и подсчитать результирующий показатель y. Или на этапе выбора типа линии тренда на вкладке Параметры указать количество единиц прогноза вперед и/или назад. В этом случае прогноз будет показан на графике.

5.  Применение регрессионного анализа.

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

Вопрос 5. Статистический анализ.

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

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

Переменные могут быть количественными или качественными. Количественные пе­ременные должны иметь конкретные числовые значения, напри­мер штуки, вес единицы товара или его цена. Количественные переменные могут быть дис­кретными или непрерывными. Дискретная переменная может прини­мать только целые значения, например такие единицы измерения, как штуки (1, 2, 3 и т.д.). Непрерывная переменная может иметь значения в широком диапазоне величин. Например, вес товара может выражаться дробным, а не обязательно целочисленным значени­ем: 185, 185,5 или 185,562 кг.

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

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

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

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

Для получения распределения значений нужно создать таблицу частот (frequency table) для анализируемых дан­ных. Это таблица, в которой собраны сведения о том, сколько раз встречаются данные с определенной величиной. Команда для создания таблицы частот предусмотрена в подключаемом к Excel модуле StatPlus. Для создания таблицы частот необходимо выбрать команду меню StatPlus ® Описательная статистика (Descriptive Statistics) ® Таблицы частот (Frequency Tables), в появившемся диалоговом окне Создание таблицы частот (Create Frequency Table) щелкнуть на кнопке Значения данных (Data Values), затем в диалоговом окне Параметры ввода (Input Options) выбрать переключатель Использовать имена диапазонов (Use Range Names), после чего в списке диапазонов выбрать переключатель, соответствующий нужному наименованию столбца, и щелкнуть на кнопке ОК.

Важным этапом анализа данных является выявление центральных мер распределения значений (среднее, медиана). Вычисление значения каждой из них будет характеризовать весь набор данных. Это значение часто называется типичным или наиболее пред­ставительным. Медиана (median) представляет собой середину распределения, т.е. одна половина заданного набора данных имеет меньшие значения, а другая — большие значения. Точный подсчет медианы зависит от количества наблюдений в наборе данных. При нечетном количестве значений медианой является промежуточное значение, а при четном — полусумма двух центральных значений.

Еще одной распространенной характеристикой является среднее значение (average или mean), которое равно сумме значений, деленной на их количество. Одним из недостатков среднего значения является то, что оно существенно зависит от экстремальных значе­ний. Медиана в большей степени представляет "типичную" величину характеристики, поскольку игно­рирует величину экстремальных значений.

Функциями, позволяющими вычислить перечисленные центральные меры распределения, являются: СРЗНАЧ (массив) и МЕДИАНА (массив).

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

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

Наиболее распространенной мерой изменчивости является отклонение di значения наблюдения хi, от среднего х. Одни отклонения могут иметь отрицательные значения (для наблюдений, значения которых меньше среднего), а другие — положительные (для наблюдений, значения которых больше среднего). Простое суммирование отклонений ничего не дает, поскольку они могут взаимно компенсировать друг друга, т.е. среднее отклонение всегда равно нулю. Поэтому каждое отклонение возводят в квадрат, суммируют и делят на количество наблюдений минус 1. Данная мера изменчивости называется дисперсией (variance) и обозначается s².

Вычисление дисперсии производится при помощи функции ДИСП(массив). Чем больше ее величина, тем соответственно больше разброс значений вокруг среднего.

Для измерения изменчивости также вычисляется стандартное отклонение, обозначаемое символом s и равное квадратному корню из дисперсии, для вычисления которого используется функция СТАНДОТКЛОН. Эта характеристика представляет «типичное» отклонение значений от среднего, более высокое значение которой свидетельствует о более высокой степени изменчивости данных. При выборе функций для вычисления стандартного отклонения нужно обращать внимание на их описание,  поскольку предлагаются функции как для генеральной совокупности, так и для выборки данных, а также с учетом или без учета текстовых и логических значений.

В ряде случаев возникает необходимость определения диапазона значений, в который попадает средняя величина исследуемой характеристики при заданной вероятности. Для этого используется функция ДОВЕРИТ, при вычислении которой должны быть указаны следующие данные: уровень значимости альфа, используемый для вычисления уровня надежности (уровень надежности равняется 100*(1 - альфа) процентам, т.е. альфа равное 0,05 означает 95-процентный уровень надежности),  размер выборки – число наблюдений и предварительно  рассчитанное стандартное отклонение.

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

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

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

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

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

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

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

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

Для вычисления коэффициента корреляции служит функция КОРРЕЛ(х, у).

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

Рассмотрим основы регрессионного анализа.

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

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

Короткие вертикальные отрезки между подогнанной линией и точками представляют собой ошибки или остатки. Остаток — это разность между наблюдаемыми и предсказываемыми значениями. Поскольку а является значением переменной у в месте пересечения вертикальной оси и подогнанной линии регрессии, этот коэффициент иногда называют пересечением или постоянным членом. Поскольку b характеризует наклон подогнанной линии регрессии, этот коэффициент называют наклоном. Коэффициент b выражает отношение вертикального и горизонтального приращений вдоль линии. Например, если переменная у возрастает от 10 до 30, а переменная х при этом увеличивается от 0 до 10, то наклон подогнанной линии регрессии выражается формулой, в числителе которой будет разница (30-10), в знаменателе – (10-0). Отсюда b = 2.  

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

У нитей с прочностью выше подогнанной линии регрессии наблюдается положительный остаток, а у нитей  с прочностью ниже подогнанной   линии регрессии — отрицательный остаток.

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

При подгонке линии к данным используются такие а и b, для которых сумма квадратов остатков принимает наименьшее значение. Эта процедура называется методом наименьших квадратов. Для определения этих членов служат функции ОТРЕЗОК (у,х) и НАКЛОН (у,х).

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

Для активизации инструмента обращаются к меню Сервис→Пакет анализа→Регрессия.

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

- диапазон для У (результативного показателя);

- диапазон для Х (объясняющих показателей);

- флажок в позиции Уровень надежности – 0,95.

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

- флажок в позиции остатки и стандартизированные остатки.

- опция  в позиции выводить результат на новом листе.

В результате на новом листе появятся результативные таблицы.

В качестве примера интерпретации  результативных таблиц рассмотрим регрессионный анализ зависимости двух переменных. В качестве зависимой переменной будет выступать уровень реализации, в качестве независимой переменной - затраты на рекламу. Таблица на основе которой будет проведен анализ состоит из 3 столбцов: вид изделия, затраты на рекламу, уровень реализации. После использования инструмента «Регрессия» на экране появляется несколько таблиц.

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

Величина достоверности аппроксимации R² измеряет процентную долю изменчивости значений зависимой переменной, которая может объясняться изменениями независимой переменной. При построении однофакторной корреляционной модели (как в данном примере) коэффициент множественной корреляции равен коэффициенту парной корреляции. Величина R² может принимать значения от 0 до 1. Например, значение 0,7654 говорит о том, что изменчивость уровня реализации, составляющая 76,54%, может объясняться изменениями затрат на рекламу. Оставшаяся доля (23,46%) изменчивости уровня реализации может объясняться случайной изменчивостью.

Величина в строке Множественный R равна квадратному корню величины R². Она выражает абсолютную величину корреляции между зависимой переменной и предиктором.

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

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

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

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

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

В столбце ss приводятся значения суммы квадратов. Общая сумма квадратов в ячейке на пересечении со строкой Итого содержит сумму квадратов отклонений объема продаж от среднего. Общая сумма квадратов складывается из двух частей: одна определяется изменениями линии регрессии, а другая связана с хаотичными изменениями и указана в ячейке на пересечении со строкой Остаток. Первая часть указана в ячейке на пересечении со строкой Регрессия и является суммой квадратичных отклонений от среднего. Вторая часть указана в ячейке на пересечении со строкой Остаток и является суммой квадратичных отклонений от линии регрессии. Последнее из двух значений должно принимать минимальное значение в уравнении регрессии. Если сумма квадратичных отклонений равна 3 396,84, причем одно ее слагаемое 2 599,53 определяется изменениями линии регрессии, то другое (796,91) — ошибкой.

Доля общей суммы квадратичных отклонений равна R², т.е. процентной доле изменчивости, определяемой изменениями линии регрессии.

Вместе с этой лекцией читают "Перестройка в СССР".

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

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

Далее - в третьей по счету таблице  приводятся оценочные параметры. В столбце коэффициенты указаны значения пересечения и наклона. В столбце стандартная ошибка приводятся величины стандартной ошибки для пересечения и наклона.  В двух последних столбцах данной области приводятся 95%-ные доверительные интервалы для пересечения и наклона.

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

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

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