Корреляционно-регрессионный анализ связи показателей коммерческой деятельности с использованием прог - Бараз В.Р.. Корреляционно-регрессионный анализ связи показателей коммерческо. В. Р. Бараз корреляционнорегрессионный анализ
 Скачать 1.37 Mb.
|
|
множественной регрессией. В этом случае математическая модель процесса представляется в виде уравнения регрессии с несколькими переменными величинами, т.е.у = f (b 0 , …, x k ). Общий вид уравнения множественной регрессии обычно стараются представить в форме линейной зависимости: у = b 0 + b 1 x 1 + b 2 x 2 + …+ b k x k , гдеb 0 – свободный член (или сдвиг); b 1 , b 2 , …, b k − коэффициенты регрессии, которые подлежат вычислению методом наименьших квадратов. При анализе уравнения множественной регрессии (как и в случае про- стой регрессии) используется также такое понятие, как ошибка прогнозиро- вания ∆у. Последняя понимается как разность между рассчитанным (теоре- тическим) значением функции ŷ i и ее измеренным (опытным) значением y i , т.е. ∆у = ŷ i − y i связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 55 Статистический вывод о пригодности (значимости) уравнения обычно проверяется в следующей последовательности. 1. Сначала проводится общая проверка методом F-теста, целью ко- торой является выяснение, объясняют ли х-переменные значимую долю вариации у,т.е. превалирует ли влияние факторов х k на изменение функции у над ее колебаниями случайного порядка; если регрессия не является значи- мой, то говорить больше не о чем. 2. Если регрессия оказывается значимой, то можно продолжить ана- лиз, используя t-тесты для отдельных коэффициентов регрессии; в этом случае пытаются выяснить, насколько значимой является влияние той или иной переменной х на параметр у при условии, что все другие факторы х k остаются неизменными. Построение доверительных интервалов и проверка гипотез на адекватность для отдельного коэффициента регрессии основыва- ется на определении стандартной ошибки. Каждый коэффициент регрессии имеет свою стандартную ошибку S b1 , S b2 ,…, S bk Рассмотрим конкретный пример. Замечательная корова кота Матроскина радовала превосходными надоями, и поэтому он вознамерился излишки молока продавать. При этом Матроскин решил выяснить, каким образом объем ежедневной продажи молока у (литров в день) зависит от а) присутствия среди покупателей ба- бушек с внучками(их доля от общего числа покупателей х 1 , %) и б) участия в коммерции пса Шарика (относительное время х 2 , когда он помогал рабо- тать за прилавком, %). Тщательные наблюдения Матроскин вел в течение 20 рабочих дней, результаты которых представил в табличной форме (табл.7). При этом порядковые номера торговых дней были расположены в случайном порядке и никак формально не отражали какое-либо внятное из- менение объема продажи молока. Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 56 Требуется помочь коту Матроскину: − написать уравнение множественной регрессии; − оценить статистическую значимость уравнения; − определить значимость коэффициентов регрессии и пояснить ха- рактер влияния исследуемых факторов. Если поставленную задачу сформулировать в более понятных для кота категориях, то нужно выяснить, влияют ли указанные факторы на его коммерческую деятельность в области молочного бизнеса, а если это так, то насколько ощутимо. Т а б л и ц а 7 Исходные данные об эффективности продажи молока Порядковый номер дня продажи у, л/день х 1 , % х 2 ,% Порядковый номер дня продажи у, л/день х 1 , % х 2 ,% 1 2 3 4 5 6 7 8 9 10 6 4,6 4,4 4,5 5,5 4,8 5,1 5,2 7 5,3 40 20 31 32 34 35 37 32 39 35 30 33 20 25 29 20 21 20 35 30 11 12 13 14 15 16 17 18 19 20 7,5 7,7 7,3 7 6,7 5,7 6 6,4 7,1 6,3 50 37 50 38 50 35 46 49 51 45 35 30 40 42 39 35 36 38 41 34 связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 57 3.1. Расчет коэффициентов регрессии и представление уравнения множественной регрессии Итак, нам надлежит выполнить предложенную задачу. Вся прелесть исходной ситуации состоит в том, что по представленным данным решитель- но невозможно обнаружить сколько-нибудь заметную тенденцию. Постара- емся обеспечить решение задачи с использованием компьютерных программ в режиме Windows. Запускаем Excel и воспроизводим в табличной форме имеющиеся ис- ходные результаты (табл.7). В данном случае все экспериментальные данные (по каждой позиции) представляем в виде самостоятельных колонок (рис.14). Размещаем всю таблицу в ячейках от A1 до D21, при этом сами исходные данные (т.е. для у и x 1 , x 2 ) будут находиться в диапазоне B1: D21. Рис.14. Лист Excel с исходными табличными результатами Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 58 После этого получим сводную таблицу основных статистических ха- рактеристик для функции у. Для этого воспользуемся известным методом анализа данных – программой Описательная статистика. Предпримем следующие шаги: − в главном меню выбираем последовательно пункты Сервис/Анализ данных/Описательная статистика, после чего щелкаем по кнопке ОК; − заполняем диалоговое окно для ввода данных и параметров вывода. Чтобы получить их, проделаем следующие манипуляции (рис.15): а) укажем Входной интервал (в виде абсолютных ссылок $B$1:$D$21), т.е. адресуем все ячейки, в которых находятся значения функ- ции у и аргументов x 1 , x 2 ; б) отметим способ Группирования (в нашем случае по столбцам); в) откроем флажок для Метки, показывающий, что первая строка содержит название столбца; г) выделим Выходной интервал, для этого достаточно указать левую верхнюю ячейку будущего диапазона ($F$1); д) установим флажки, показывающие, что нам нужна информация в виде Итоговой статистики, а такжеУровень надежности, равный95 %;по- сле чего нажмем кнопку ОК. Рис.15. Диалоговое окно ввода параметров Описательная статистика связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 59 Полученные результаты статистического расчета показаны на рис.16 в виде соответствующего листа Excel. Из представленного комплекта статистических показателей выберем те, которые нам потребуются для последующего анализа − среднее арифме- тическое и стандартное отклонение (среднеквадратичное отклонение) S n В табл.8 приведены названные статистические показатели для функ- ции у и обеих переменных х 1 и х 2 . Отметим, что для функции у ее среднее арифметическое ⎯у составляет 6,01, а стандартное отклонение (среднеквадра- тичное отклонение) S n равно 1,06. Т а б л и ц а 8 Статистические показатели для функции у и переменных х 1 и х 2 Показатели y x 1 x 2 Среднее 6,01 39,3 31,65 Стандартное отклонение S n 1,06 8,26 7,25 Рис.16. Лист Excel с результатами расчета статистических показателей Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 60 Расчет показателей регрессии также исполняется по компьютерной программе. Для ее запуска исполним следующие команды: − в главном меню выберем пункты Сервис/Анализ данных / Рег- рессия, после чего щелкнем по кнопке ОК; − заполним диалоговое окно ввода данных для параметра у и обеих характеристик х 1 и х 2 ;для этого в каждое окно (Интервал Y и Интервал Х) поместим наши данные, выделив их предварительно в соответствующих столбцах (напомним, что для функции у ее данные «сидят» во втором столб- це В2:В21, а для переменных х 1 и х 2 – в третьем и четвертом, т.е. в диапазоне ячеек C2:D21; заметим, что при этом выделяются только те ячейки, которые содержат исключительно числовые показатели); − выделим в текстовом поле Выходной интервалту ячейку, от кото- рой будет формироваться весь блок получаемых статистических показате- лей; при этом укажем другой лист − Лист 2; − после чего − кнопка ОК. Заполненное диалоговое окно для программы Регрессия представле- но на рис.17. Рис.17. Диалоговое окно ввода параметров Регрессия связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 61 Старательный Excel выдает, как мы убеждаемся, весьма богатый на- бор разнообразных статистических материалов (рис.18). Выберем, однако, из них такие, которые нам потребуются для последующего анализа. Для этого создадим табл.9, в которой поместим расчетные значения коэффициентов регрессии, стандартную ошибку, величины t-критерия и по- казатели уровня значимости α. Укажем также (ниже таблицы) рассчитанные показатели для самой функции у. Т а б л и ц а 9 Данные регрессионной статистики Для функции Y: S ⎯y = 0,65; R-квадрат = 0,67; R-квадрат (нормир.) = 0,63. Та- ким образом, для рассматриваемого примера уравнение регрессии (или урав- нение прогнозирования) будет иметь следующий вид: ŷ (объем продажи молока, л/день)= b 0 + b 1 x 1 + b 2 x 2 = = 1,61+ 0,06 (доля среди покупателей бабушек с внучками, %) + + 0,07 (относительный вклад участия в торговле Шарика, %). Запишем полученное уравнение в окончательной редакции: ŷ= 1,61 + 0,06x 1 + 0,07 x 2 Независимая переменная Коэффициент Стандартная ошибка t p (или α) Свободный член Х 1 Х 2 1,61 0,06 0,07 0,77 0,23 0,03 2,09 2,59 2,57 0,05 0,02 0,02 Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 62 Рис.18. Лист Excel с результатами расчета статистических показателей регрессии Теперь займемся статистическим анализом этого уравнения регрес- сии. 3.2. Интерпретация коэффициентов регрессии Свободный член (сдвиг) b 0 , равный 1,61, формально надлежит пони- мать следующим образом: объем продажи молока котом Матроскиным, ко- гда среди покупателей отсутствуют бабушки с внучками, и нет компаньона Шарика (занят фотоохотой), составляет 1,61 литров в день. Однако мы пола- гаем, что в указанной совокупности исходных данных нет подобных приме- ров (всегда среди покупателей окажутся бабушки с внучками, а Шарик помо- гает ежедневно). Поэтому сдвиг b 0 следует обсуждать как вспомогательную величину, необходимую для получения оптимальных прогнозов, и не истол- ковывать ее столь буквально. связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 63 Коэффициенты регрессииb 1 и b 2 следует рассматривать как степень влияния каждой из переменных (присутствие бабушек с внучками и вклад коммерческого таланта Шарика) на размер продажи, если все другие незави- симые переменные остаются неизменными. Так, коэффициент b 1 , равный 0,06, указывает, что (при прочих равных условиях) повышение доли бабушек с внучками на 1 % приводит к возрастанию продажи молока на 0,06 литров в день. Анализируя коэффициент b 2 , можно заметить, что увеличение относи- тельного участия Шарика на 1 % приводит также к повышению продажи, этот прирост составляет почти такую же величину − 0,07 л/день. Еще раз заметим, что все названные коэффициенты регрессии отра- жают влияние на исследуемый параметр у только какой-то одной переменной х при непременном условии, что все другие переменные (факторы) не меня- ются. Например, применительно к коэффициенту b 2 это нужно понимать так: указанное влияние коммерческой помощи Шарика проявляется при условии, когда сохраняется среди покупателей неизменной доля старушек с внучками. 3.3. Ошибки прогнозирования (определение качества регрессионного анализа) Можно воспользоваться двумя приемами для оценки добротности выполненного нами регрессионного анализа. В статистике для этого исполь- зуют: − стандартную ошибку (S ⎯ у ), которая дает представление о приблизи- тельной величине ошибки прогнозирования; − коэффициент детерминации (R 2 ), указывающий, какой процент ва- риации функции у объясняется воздействием факторов х k Рассмотрим оба подхода более подробно. 1. Результаты статистического расчета показывают, что стандартная ошибка для функции составляет 0,65. Этот результат применительно к наше- Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 64 му примеру следует рассматривать следующим образом: фактическая вели- чина объема продаж молока отличается от прогнозируемых показателей не более чем на 0,65 л/день. Однако ценность этого показателя невелика, если не надежность этого утверждения. При условии сохранения нормального распределения можно полагать, что примерно 2/3 фактических данных будут находиться в пределах S ⎯ у от прогнозируемых показателей; примерно 95 % − в пределах 2S ⎯ у и т.д. Эта стандартная ошибка S ⎯ у , равная 0,65, указывает отклонение фак- тических данных от прогнозируемых на основании использования воздейст- вующих факторов х 1 и х 2 (влияние среди покупателей бабушек с внучками и высокопрофессионального вклада Шарика). В то же время мы располагаем обычным стандартным отклонением S n , равным 1,06 (см. табл.8), которое было рассчитано для одной переменной, а именно: сами текущие значения у i и величина среднего арифметического ⎯у, которое равно 6,01. Легко видеть, что S ⎯ у < S n ; следовательно, ошибки прогнозирования, как правило, оказыва- ются меньшими, если использовать уравнение регрессии (учитывается вклад факторов х 1 и х 2 ), а не ограничиваться только значением ⎯у. Сказанное можно истолковать следующим образом. Если бы нам ни- чего не было известно про переменные х 1 и х 2 , то в качестве оптимальной приблизительной величины среднего уровня продаж пришлось бы использо- вать показатель ⎯у = 6,01 л/день и полагать, что наши прогнозы дают ошибку S n , равную 1,06 л/день. Однако если нам известны такие характеристики, как влияние особой категории покупателей (бабушки с внучками) и роль высо- коквалифицированной помощи Шарика, то для прогнозирования можно вос- пользоваться уравнением регрессии. В этом случае наши предсказания будут давать ошибку уже примерно в 0,65 л/день. Такое сокращение погрешности прогнозирования с 1,06 до 0,65 и яв- ляется одним из преимуществ использования регрессионного анализа. связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 65 2. Если вновь обратиться к нашему примеру, то коэффициент детер- минации R 2 (на рис.17 славный Excel его подает как R-квадрат) равен 0,67, что составляет 67 %. Этот результат следует толковать так: все исследуемые воздействующие факторы (влияние особой категории покупателей и коммер- ческий талант Шарика) объясняют 67 % вариации анализируемой функции (объема проданного молока). Остальное же (33 %, что весьма прилично!) ос- тается необъясненным и может быть связано с влиянием других, неучтенных факторов. Для нашего примера показатель R 2 (67 %) считается умеренным, по- этому можно полагать, что именно эти два фактора в данном конкретном случае оказывают наиболее значительное влияние на y¯. Итак, нами получено уравнение множественной регрессии, коэффи- циенты которого b i формально показывают, как и в каком направлении дей- ствуют (пока лишь вероятно!) исследуемые факторы х k i и какой процент из- менчивости функции у объясняется влиянием именно этих факторов. Теперь нам надлежит определить статистическую значимость полу- ченного аналитического выражения. 3.4. Проверка значимости модели При проверке значимости модели принято придерживаться следую- щей последовательности действий: 1. Сначала выполняется общая проверка полученного уравнения на пригодность. 2. Если результат оказался положительным (уравнение значимо), то проверяют на значимость уже каждый коэффициент уравнения регрессии b i 3. Дается сравнительная оценка степени влияния каждого из анали- зируемых факторов х k |