Формулы и функции Excel, используемые при корреляционном анализе
Формула для вычислений
|
Функция или инструмент Анализа данных в Excel
|
Результат вычислений/
Примечания
|
Среднее значение
|
СРЗНАЧ
(число1; число2;..)
|
Возвращает среднее значение (среднее арифметическое) аргументов.
|
Дисперсия
|
ДИСП
(число1; число2;..)
|
Оценивает дисперсию по выборке
|
Стандартное отклонение
|
СТАНДОТКЛОН
(число1; число2;..)
|
Оценивает стандартное отклонение по выборке. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.
|
Сумма квадратов отклонений
|
КВАДРОТКЛ (число1;число2;..)
|
Возвращает сумму квадратов отклонений точек данных от их среднего.
|
Коэффициент парной корреляции
|
КОРРЕЛ (массив1; массив2)
|
Возвращает коэффициент корреляции между интервалами ячеек массив1 и массив2.
|
t - критерий Стьюдента для проверки значимости коэффициента парной корреляции
|
СТЬЮДРАСПОБР (вероятность; степени_свободы)
|
Вычисленное по этой формуле значение tнабл сравнивается с критическим значением t-критерия, которое берется из таблицы значений t Стьюдента с учетом заданного уровня значимости и числа степеней свободы (n-2) или определяется с помощью функции СТЬЮДРАСПОБР(…).
|
Матрица коэффициентов парной корреляции
|
Обращение к средствам анализа данных. Для вычисления матрицы коэффициентов парной корреляции R следует воспользоваться инструментом Корреляция из пакета «Анализ данных».
|
Инструмент Корреляция применяется, если имеется более двух переменных измерений для каждого объекта. В результате выдается таблица, корреляционная матрица, показывающая значение функции КОРРЕЛ (…) для каждой возможной пары переменных измерений. Любое значение коэффициента корреляции должно находиться в диапазоне от -1 до +1 включительно.
|
Некоторые формулы и функции Excel, используемые при регрессионном анализе
Формула для вычислений
|
Функция EXCEL или инструмент Анализа данных
|
Результат вычислений /Примечания
|
Оценка параметров модели парной и множественной линейной регрессии по методу МНК.
|
Для вычисления параметров уравнения регрессии следует воспользоваться инструментом Регрессия из пакета «Анализ данных»
|
Возвращает подробную информацию о параметрах модели, качестве модели, расчетных значениях и остатках в виде четырех таблиц: Регрессионная статистика, Дисперсионный анализ, Коэффициенты, ВЫВОД ОСТАТКА.
Там же может быть получен график подбора и график остатков.
|
Оценка качества модели регрессии
|
F-критерий Фишера для проверки значимости модели регрессии
|
=FРАСПОБР(вероятность;степени_
свободы1;степени_свободы2)
Степени_свободы 1 — это степени свободы числителя k.
Степени_свободы 2 — это степени свободы знаменателя (n - k - 1),
где k – количество факторов, включенных в модель, n– длина данных.
|
Возвращает обратное значение для F-распределения вероятностей.
FРАСПОБР(…) можно использовать, чтобы определить критические значения F-распределения.
Чтобы определить критическое значение F, нужно использовать уровень значимости  как аргумент «вероятность» для FРАСПОБР(…).
|
Коэффициент детерминации
|
Коэффициент детерминации показывает долю вариации результативного признака, находящегося под воздействием изучаемых факторов, т. е. определяет, какая доля вариации признака Y учтена в модели и обусловлена влиянием на него факторов.
Чем ближе  к 1, тем выше качество модели.
|
Коэффициент множественной корреляции (индекс корреляции) R
R =  = 
|
Данный коэффициент является универсальным, так как он отражает тесноту связи и точность модели, а также может использоваться при любой форме связи переменных.
Чем ближе R к 1, тем выше качество модели.
|
t - критерий Стьюдента для оценки значимости параметров модели линейной регрессии
|
Вычисленное значение  сравнивается с критическим значением t-критерия, которое берется из таблицы значений t Стьюдента с учетом заданного уровня значимости и числа степеней свободы (n-k-1) В Excel критическое значение t-критерия можно получить с помощью функции
СТЬЮДРАСПОБР (вероятность; степени свободы)
Вероятность — вероятность, соответствующая двустороннему распределению Стьюдента.
Степени_свободы — число степеней свободы, характеризующее распределение.
|
Средняя относительная ошибка аппроксимации
|
Средняя относительная ошибка аппроксимации – оценка уровня точности модели
|
Оценка влияния отдельных факторов на зависимую переменную на основе модели
|
Коэффициенты эластичности 
|
Эластичность показывает: на сколько процентов изменится значение исследуемой величины при изменении соответствующего фактора на 1 %.
|
Бета-коэффициенты
|
Бета-коэффициент показывает: на какую часть своего среднеквадратического отклонения (СКО) изменится значение исследуемой переменной при изменении соответствующего фактора на 1 СКО.
|
Дельта коэффициенты 
|
Дельта-коэффициент показывает среднюю долю влияния соответствующего фактора в совокупном влиянии всех факторов, включенных в модель.
|
Построение интервальных прогнозов по модели регрессии
|
 – ошибка прогнозирования, которая позволяет определить доверительный интервал прогноза,
где  – стандартная ошибка модели (СКО).
Ошибка прогнозирования для модели парной регрессии
|
Регрессионная статистика в отчете Excel
Наименование в отчете Excel
|
Принятые наименования
|
Формула
|
Множественный R
|
Коэффициент множественной корреляции, индекс корреляции
|
|
R-квадрат
|
Коэффициент детерминации, R2
|
|
Нормированный R-квадрат
|
Скорректированный R2
|
|
Стандартная ошибка
|
Среднеквадратическое отклонение от модели
|
|
Дисперсионный анализ в отчете Excel
Наименование в отчете Excel
|
Df – число степеней свободы
|
SS – сумма квадратов
|
MS – дисперсия на одну степень свободы
|
F – критерий Фишера
|
Регрессия
|
k
|
|
|
|
Остаток
|
n-k-1
|
|
|
|
Итого
|
n-1
|
|
|
Названия некоторых функций в Excel 2010 были изменены по сравнению с более ранними версиями.
Чтобы повысить точность работы функций Excel, обеспечить их согласованность и привести имена функций в соответствие с их назначением, корпорация Майкрософт изменила, переименовала и добавила несколько функций в библиотеку Microsoft Excel 2010.
Для обеспечения обратной совместимости переименованные функции доступны также и по их старым именам.
Название функции в Excel более ранних версий
|
Название функции в Excel 2010
|
Примечания
|
ДИСП (число1,[число2],...])
|
ДИСП.В (число1,[число2],...])
|
Оценивает дисперсию по выборке
|
СТЬЮДРАСПОБР (вероятность; степени_свободы)
|
СТЬДЕНТ.ОБР.2Х (вероятность, степени_свободы)
|
Возвращает двустороннее обратное t-распределение Стьюдента.
|
FРАСПОБР (вероятность;степени_свободы1;степени_свободы2)
|
F.ОБР.ПХ (вероятность,степени_свободы1, степени_свободы2)
|
Возвращает значение, обратное (правостороннему) F-распределению вероятностей
|
ХИ2ОБР (вероятность,степени_ свободы)
|
ХИ2.ОБР.ПХ (вероятность,степе-ни_свободы)
|
Возвращает обратное значение односторонней вероятности распределения хи-квадрат
|
4. Комплексный пример исследования экономических данных с использованием корреляционно-регрессионного анализа
Эконометрический анализ финансовых показателей деятельности компаний-эмитентов3 отрасли «Связь»
Поставим задачу провести количественную оценку взаимосвязей показателей с целью получения обоснованных экономических выводов о результатах финансовой деятельности компаний и их перспективах. Расчеты могут быть выполнены с помощью Excel. Для ускорения получения результатов, для более комфортной работы при выполнении анализа могут быть использованы специальные пакеты программ, например, VSTAT, SPSSи другие.
Результирующей (эндогенной) переменной будем считать чистую прибыль (убыток). Исходными данными для анализа являлись данные за 2009 г. по основным показателям баланса компаний-эмитентов отрасли «Связь»4 (всего 122 компании).
В силу неоднородности объектов (компаний) была проведена их классификация на однородные группы методами кластерного анализа [3]. В результате получены три кластера следующих профилей5:
1. Первый кластер самый большой – 109 компаний. Среди них много убыточных. Все показатели баланса предприятий этой группы на несколько порядков ниже, чем у других.
2. Второй кластер – 10 крупных прибыльных компаний.
3. Третий кластер – наименьший по количеству компаний, их всего три – Вымпел-Коммуникации (Билайн), Мобильные ТелеСистемы (МТС), Мегафон, но это самые крупные и самые прибыльные компании.
Объективную картину количественных взаимосвязей финансовых показателей предприятий данной отрасли можно получить, используя данные только о компаниях самого крупного первого кластера (таблица 1).
Краткая экономическая характеристика данных таблицы 1:
- валюта баланса (ВБ) – это итог по всем счетам бухгалтерского баланса, сумма всех активов6 или всех пассивов7 компании;
- выручка (нетто) от продаж (ВП), дебиторская задолженность (краткосрочная) (ДЗ), запасы готовой продукции и товаров для перепродажи (ЗП), оборотные активы (ОА), основные средства (ОС), прибыль (убыток) от продаж (ПП), чистая прибыль (убыток) (ЧП) – это представители группы активов компании (здесь присутствуют как оборотные8, так и внеоборотные9 активы);
- долгосрочные обязательства (ДО), краткосрочные обязательства (КО) – представители группы пассивов.
|
Скачать 4.57 Mb.