Модели для анализа связей и зависимостей

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

Наличие и степень тесноты связей можно определить по различным статистическим характеристикам. Часть из этих характеристик встроена в MS Excel. К их числу относятся, в частности, коэффициент корреляции (ρ), коэффициент Пирсона (r); квадрат коэффициента Пирсона (r²).

В скобках приведены принятые в MS Excel обозначения этих функций.

Три указанных коэффициента рассчитываются по формулам:

где x, y – заданные значения показателя-фактора и результативного показателя; n – количество объектов в статистической совокупности.

Приемлемость рассматриваемого вида связей (он может быть линейным или нелинейным) оценивается с помощью показателя, называемого стандартной ошибкой у от х. В MS Excel для нее принято обозначение «СТОШУХ» (σ_y/x) и рассчитывается по формуле:

Для расчета четырех названных статистических характеристик на ПЭВМ нет необходимости вводить формулы их расчета с клавиатуры, поскольку они встроены в процедуры «Мастер функций» MS Excel.

Для выполнения соответствующих расчетов достаточно: а) активизировать процедуру «Мастер функций», б) выбрать в появившемся окне «Мастера функций» слово «статистические» + ОК; в) выбрать среди статистических функций КОРРЕЛ, КВПИРСОН и СТОШУХ соответственно.

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

Синтаксис каждого из трех рассматриваемых показателей имеет вид:

КОРРЕЛ (массив 1; массив 2);

КВПИРСОН (известные значения Y; известные значения X);

СТОШУХ (известные значения Y; известные значения X),

где массив1 - это интервал ячеек со значениями;

массив2 - второй интервал ячеек со значениями.

Наиболее простым из видов связей между двумя экономическими показателями является линейный

y = b + mx,

где y – зависимый экономический показатель, x – независимый экономический показатель (называют также показателем-фактором); b и m – параметры, численные значения которых должны быть определены.

Параметры b и m рассчитываются по формулам:

m = ; .

Для работы параметров b и m в MS Excel имеются функции «ОТРЕЗОК» и «НАКЛОН». Их синтаксисы имеют вид:

ОТРЕЗОК (известные значения Y; известные значения X).

НАКЛОН (известные значения Y; известные значения X).

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

Пример. По исходным данным регионов России за 2002-2009гг. по четырем ключевым показателям, валовой региональный продукт (ВРП), стоимость основных фондов (ОФ), численность занятых в экономике (Числ) и объему инвестиций (Инв) требуется выявить наличие линейной зависимости ВРП от каждого от трех показателей-факторов для чего рассчитать статистические функции «КОРРЕЛ», «КВПИРСОН», «СТОШУХ», «ОТРЕЗОК», «НАКЛОН».

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

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

В соответствии с 1-м вариантом:

а) создается таблица-шаблон исходных данных для одной пары показателей (таблица 1).

Таблица 1

Таблица-шаблон исходных данных для первого варианта компьютерной модели

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

Рис.1. Графики точек рассеивания для зависимости ВРП от инвестиций

в) создается таблица-шаблон для расчетных показателей (таблица 2).

Таблица 2

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

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

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

Таблица 3

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

Таблица 4

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

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

Таблица 5

Таблица-шаблон исходных данных для второго варианта компьютерной модели

Во втором варианте для всех пар зависимостей создается одна таблица-шаблон для расчетных показателей (таблица 6).

Таблица 6

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

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

Таблица 7

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

Таблица 8

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

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

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

- исходную таблицу;

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

Работа компьютерной программы иллюстрирует рис. 2. Алгоритм 1 – статистические функции из «Мастера функций» MS Excel, а алгоритм 2 – графостроитель из «Мастера диаграмм». Таблицы-шаблоны и таблица-график приведены в таблицах 1, 2 и на рис. 1, а соответствующие отчетные таблицы и графики – в таблицах 3, 4.

Рис.2. Схема функционирования компьютерной модели

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

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

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