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

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

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

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

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

Порядок проведения корреляционного анализа средствами Excel

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

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

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

Рисунок 1.20 — Исходная таблица для анализа.

2. Выполните команду Сервис Анализ данных Подробнее о работе с Пакетом анализа см. раздел Работа со статистическим пакетом анализа данных Excel.

3. Выберите инструмент анализа Корреляция из списка Инструменты анализа и нажмите кнопку ОК.

Рисунок 1.22 — Инструменты анализа данных из Пакета анализа.

Рисунок 1.23 — Диалоговое окно Корреляция.

4. В появившемся диалоговом окне Корреляция укажите ссылки на входной и выходной интервалы.

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

Установите переключатель (рис. 1.23) в положение «по столбцам» или «по строкам» в зависимости от расположения данных во входном диапазоне. В нашем примере его следует установить в положение «по столбцам».

Рисунок 1.24. Процедура указания входного интервала.

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

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

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

Нажмите кнопку ОК.

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

Рисунок 1.25 — Матрица коэффициентов парной линейной корреляции.

Для вычисления коэффициента корреляции между двумя наборами данных на листе можно также использовать статистическую функцию КОРРЕЛ. Данная функция возвращает коэффициент корреляции меду интервалами ячеек Массив1 и Массив2.

Функция записывается в виде КОРРЕЛ(массив1;массив2), где массив1 — это ссылка интервал ячеек со значениями первого показателя, а массив2 — ссылка интервал со значениями второго показателя.

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

=КОРРЕЛ (В2:В13;С2:С13).

После нажатия клавиши Ввод в выбранной ячейке появится результат вычислений 0,57 082 488 938 163.

Если массив1 и массив2 имеют различное количество точек данных, то функция КОРРЕЛ возвращает значение ошибки #Н/Д.

Если массив1 либо массив2 пуст, или если у (стандартное отклонение) их значений равно нулю, то функция КОРРЕЛ возвращает значение ошибки #ДЕЛ/0!