EXCEL и пример решения множественной регрессионной задачи

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

Статистические функции (80 функций), вызываются через окно Мастер функций. Функция КОРРЕЛ (расчет корреляции между двумя множествами данных) запрашивает два исходных множества (массива) данных и выдает коэффициент корреляции в ту клетку, куда был установлен курсор перед обращением к функции. Можно использовать эту функцию в простом (однофакторном) регрессионном анализе.

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

Множественный корреляционно-регрессионный анализ в основном ориентирован на средства дополнительного пакета Анализ данных. Активизация команды Сервис -> Анализ данных открывает окно Инструменты анализа, предоставляющее 19 статистических инструментальных средств. Среди них - Корреляция и Регрессия, непосредственно и эффективно поддерживающие простой и множественный корреляционно-регрессионный анализ.

Эти сложные инструменты доступны в том случае, если они предварительно загружены через команду Сервис {Надстройки (Tools\Add-Ins). В открывшемся окне дополнений следует пометить флажок слева от позиции Analysys Tool-Pack VBA, и затем щелкнуть по кнопке ОК. После повторного обращения к команде Сервис, позиция Анализ данных появится в конце меню.

С помощью инструмента Корреляция, можно получить корреляционную матрицу парных коэффициентов за один прием, что было проблемой в электронных таблицах других типов. Для этого, после выбора Сервис -> Анализ данных -> Корреляция, следует определить в качестве входного все поле имеющихся исходных данных, корреляционные связи которых изучают. Затем следует уточнить с помощью флажков, по столбцам или по строкам размещены переменные. Если поле содержит заголовочную строку (или столбец), то в диалоге активизируют графический флажок Метки (Labels). После выбора графической кнопки выполнения (ОК), корреляционная матрица автоматически выводится на новый лист той же электронной таблицы, начиная с клетки А1. Если матрицу желают вывести на какой-либо конкретный лист и начиная с клетки, определяемой пользователем, то делают соответствующие установки в окне «Выходной интервал» диалогового окна инструмента Корреляция.



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

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

XI Х2 X3 Х4 Х5
XI
Х2 0,22857947
ХЗ -0,826407471 -0,127454877
Х4 -0,245445107 -0,972954999 0,01903653
Х5 0,730717472 0,81625257 -0,525376875 -0,821305037


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

Столбец 1 Столбец 2 Столбец 3 Столбец 4 Столбец 5
Столбец
Столбец 0,22857947
Столбец -0,826407471 -0,127454877
Столбец -0,245445107 -0,972954999 0,01903653
Столбец 0,730717472 0,81625257 -0,525376875 -0,821305037

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

XI Х2 ХЗ Х4 Х5
XI
Х2 Слабая
ХЗ Сильная Слабая
Х4 Слабая Весьма сильная Нет связи
Х5 Сильная Сильная Заметная Сильная

Вспомогательная диагностирующая формула:



=ЕСЛИ(АВ8(ВЗ)<0,1 ;"Нет связи";ЕСЛИ(АВ8(ВЗ)<0,3;"Слабая";

ЕСЛИ(АВ8(ВЗ)<0,5;"Умеренная";ЕСЛИ(АВ8(ВЗ)<0,7;"Заметаая";

ЕСЛИ(АВ8(ВЗ)<0,9;"Сильная";ЕСЛИ(АВ8(ВЗ)<=0,99;

"Весьма сильная"; "Полная".

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

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

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

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

4. Перейти в окно той книги и на тот ее лист, где создана и скопирована оригинальная корреляционная матрица.

5. Установить курсор в ту клетку матрицы-копии, куда скопировано значение клетки ВЗ из оригинала матрицы, (в нашем примере это клетка матрицы-копии, выделенная серым цветом, где был размещен коэффициент корреляции XI, Х2=0,22857947). Выполнить вставку из буфера (щелкнуть по пиктограмме вставки).

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

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

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

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

• Среди независимых переменных имеется пара XI, ХЗ с сильной обратной связью (с коэффициентом корреляции R(X1, X3) = -0,826407471). Следовательно, одновременное участие XI и ХЗ в уравнении нецелесообразно.

• Имеется еще одна пара независимых переменных Х2, Х4 с весьма сильной обратной связью (R(X2, Х4) = -0,972954999). Следовательно, одновременное участие Х2 и Х4 в уравнении еще более нецелесообразно.

• Зависимые переменные XI, Х2, Х4 имеют с независимой Х5 связь одного типа (сильная}. Поэтому XI, Х2, Х4 являются кандидатами на участие в уравнении, но Х2 и Х4 вместе быть не должны.

• Зависимая переменная ХЗ имеет с независимой Х5 менее выраженную (заметную) связь, наименьшую по модулю из всех независимых. Поэтому ХЗ - явный кандидат на исключение из четырехфакторной модели. Другой аргумент к исключению ХЗ - ее сильная связь с XI.

• Поиск наилучшего уравнения будем выполнять методом исключения, планируя получить и исследовать уравнения:

(1) с факторами Х1,Х2,ХЗ,Х4;

(2) с факторами XI, Х2, Х4 или XI, Х2, ХЗ;

(3) с факторами XI, Х2;

(4) с факторами XI, Х4.

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

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

• Приступая к расчету, предполагаем наличие линейной связи между исследуемой Х5 и независимыми переменными. Поэтому воспользуемся инструментом Регрессия из пакета Анализ данных (Excel ).


7388718385297097.html
7388791845073641.html
    PR.RU™