Корреляционно-регрессионный анализ связи показателей коммерческой деятельности с использованием прог - Бараз В.Р.. Корреляционно-регрессионный анализ связи показателей коммерческо. В. Р. Бараз корреляционнорегрессионный анализ
 Скачать 1.37 Mb.
|
|
33 Продолжим рассмотрение нашей задачи. В диалоговом окне Ранг и персентиль заполним поле Входной интервал (рис.7). Рис. 7. Диалоговое окно Ранг и перцентиль В нем укажем данные 2-й графы табл. 3 (вместе с заголовком), отно- сящиеся к фирме «Колокольный звон» (это диапазон ячеек $B$1:$B$13). От- метим флажком позицию Метки в первой строке (поскольку нам нужно со- хранить заголовок этой графы), а затем в окне Выходной интервалукажем ячейку $I$1, в которой будет размещена таблица с рассчитанными показате- лями рангов и перцентилей. После этого – кнопка ОК. Затем аналогичным образом поступим с данными 3-й графы (сведения от фирмы «Мельхиор»). При заполнении диалогового окна Ранг и персен- тиль отметим диапазон ячеек $С$1:$С$13, а для опции Выходной интервал покажем ячейку, которая должна быть по соседству с первой половинкой нашей общей таблицы. Это ячейка $М$1. В окончательном виде наша таблица примет следующий вид (рис.8). Как видно, Excel аккуратно проранжировал результаты по каждому эпизоду, расположив студентов по местам в соответствии с их материальны- Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 34 ми успехами, а также указал их перцентильный ранг (в %). Для дальнейших рассуждений данные по перцентилям мы использовать не станем, а вот ранги окажутся совершенно необходимыми. Рис. 8. Расчетная таблица с показателями рангов и перцентилей На основании ранговых оценок организуем сводную таблицу, анало- гичную уже знакомой нам табл.3 (рис.9). Для удобства перейдем на другой рабочий лист (Лист 2). Для выполнения последующих расчетов используем итоговый результат, отражающий сумму разностей квадратов рангов, равную 105. Оформим вспомогательную таблицу (рис.9), в которой укажем значение Σd 2 = 105, размер выборки n = 12, а также предусмотрим в ней ячейку, где поместим рассчитанное значение коэффициента ранговой корреляции р (ячейка Е22). Поместим курсор в ячейку Е22, а затем в поле формулы запишем уравнение, по которому будем рассчитать коэффициент р. Выглядит оно так: = 1 − 6*(Е20)/(Е21*(Е21^2 − 1)) В ячейке появится искомый результат 0,632867. С округлением при- нимаем его равным 0,633 – коэффициент оказался именно таким, каким мы его вычислили «вручную». связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 35 Полученный результат показывает, что в данной ситуации надлежит высказать совершенно те же соображения по поводу исследуемого процесса, какие были сделаны для случая расчета коэффициента ртрадиционным спо- собом. При доверительной вероятности 0,95 студенты вполне могут гордели- во полагать, что их материальные достижения всецело определяются личным усердием и не зависят от каких-то иных привходящих факторов. Однако тре- бование более строгой оценки (с вероятностью 99 %) делает такое мнение менее очевидным и для значимого статистического вывода возникает необ- ходимость расширить выборку (привлечь для анализа большее число студен- тов) либо (при невозможности это сделать) отнестись к результату вполне философски. Рис. 9. Фрагмент рабочего листа Excel с обобщенной таблицей и данными для расчета коэффициента корреляции Спирмена. Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 36 Регрессионный метод оценки коммерческой деятельности Если мой сосед бьет жену каждый день, а я никогда, то с точки зрения статистики мы оба бьем своих жен через день. (Бернард Шоу) Подожди - и плохое само собой исчезнет..., нанеся положенный ущерб. (Расширенный закон Мэрфи) В практике статистического исследования весьма часто возникает не- обходимость определить не только корреляционное соотношение между изу- чаемыми характеристиками, но и установить определенную обусловленность между ними, представив выявленную связь в строгой аналитической форме. В этом случае результат исследования – экспериментальная зависимость воз- действия какого-либо фактора (скажем, производительности труда, уровня образования, практического стажа работы и т.д.) на изменение изучаемого параметра (например, величины прибыли фирмы) − может быть не только представлен в виде графика (что весьма наглядно), но и описан математиче- ски с использованием аппроксимирующего выражения (эмпирической фор- мулы). Исследование такой ситуации и является задачей регрессионного ана- лиза, который дает предсказание (прогнозирование) одной переменной на ос- новании другой. Регрессионный анализ четко распределяет роли между изу- чаемыми характеристиками − одна из них является аргументом, а вторая функцией. Переменная, которая прогнозируется (функция), обозначается как у, а переменная, которая используется для такого прогнозирования (аргумент или фактор), − это х. Таким образом, в случае выявления корреляции дается попытка отве- тить на вопрос: «Существует ли связь?» Целью регрессионного анализа явля- связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 37 ется поиск ответа на уже более сложный вопрос: «Каков вид этой связи? Что на что влияет?» Однако в последнем случае речь не идет о выяснении меха- низма причинности обнаруженной связи, т.е. не ставится вопрос «Почему существует связь?» Это уже считается проблемой специального исследова- ния, касающегося выявления физической (или социальной) природы изучае- мого процесса. 2.1. Аппроксимационные модели При изучении любого процесса (физического, социального) прихо- дится сталкиваться с необходимостью представлять его в качестве некоторой модели, т.е. в виде какого-то образа. Этот образ может быть заявлен в описа- тельной форме (эпистолярный жанр), может изображаться в форме матема- тического уравнения (формулы) или же показан как графическая картинка. Следовательно, сам оригинал (физический процесс, экономическое явление) заменяется некоторым аналогом, «эрзацем» (т.е. моделью). Такое создание «заместителя оригинала» и принято называть аппроксимацией. Обычно под аппроксимацией (от лат. approximatio − приближение) понимают замену одного объекта другим, более известным и более простым, однако весьма близким к исходному по своему содержанию. В этом случае связь между исходным объектом (оригиналом) F и его приближенным представлением (моделью) f соответствует приближенному равенству F ≈ f (рис.10). Рис.10. Схематическая связь между оригиналом и моделью объекта Оригинал F (в виде реального процесса) Изображение f (в виде математической модели) Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 38 Задача аппроксимации часто возникает при обработке результатов экспериментов, когда становится необходимым подобрать математическую модель изучаемого процесса, т.е. дать его аналитическое описание в виде так называемой эмпирической формулы. При подборе эмпирической формулы обычно используется феноме- нологическийподход. Этот термин означает, что изучаемому процессу придаетсячисто описательный вид, при котором довольствуются только сведениями о внешнем характере этого процесса, но игнорируется причинность проявления рассматриваемой зависимости. В этом смысле феноменологический подход можно уподобить кибернетической модели «черного ящика». Как известно, при этом анализируется комбинация «вход −выход», т.е. характер влияния воздействующего фактора (аргумента) на исследуемый параметр (отклик или функцию). Однако содержимое «черного ящика» остается вещью в себе, т.е. физическая (или экономическая) природа процесса не обсуждается. Принципиальная особенность физического подхода состоит в том, что исследуемый процесс оценивается с позиций причин его проявления. Следовательно, если при феноменологическом подходе основной вопрос ставится в формулировке «Как произошло?», то при физическом описании − «Почему произошло?» Тем самым феноменология дает чисто формальное, внешнее описание процесса, физический же подход основывается на выяснении его причин, его природы. 2.2. Выбор формул лучшего вида При изучении связи показателей коммерческой деятельности приме- няются различного вида уравнения прямолинейной и криволинейной связи. Формально могут возникать ситуации двух типов: 1. Вид функциональной зависимости неизвестен. В этом случае нуж- но решить предварительно задачу, направленную на отыскание подходящей функциональной зависимости. Это достаточно сложная задача, но она ус- связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 39 пешно решается современными средствами информационных технологий (программа Excel). 2. Вид функциональной зависимости известен и требуется только найти ее параметры (коэффициенты регрессии b 0 , b 1 , b 2 , …). Термином линейный регрессионный анализ обозначают такое прогнозирование, которое описывается линейной взаимосвязью между ис- следуемыми переменными: y = b 0 + b 1 x. В случае криволинейных зависимостей применяются математические функции следующего вида: гиперболическая y = b 0 + b 1 /x; показательная y = b 0 + b 1 x ; степенная y = b 0 x b1 ; параболическая y = b 0 + b 1 x + b 2 x 2 ; логарифмическая y = b 0 + b 1 lgx; экспоненциальная y = b 0 exp (b 1 x) и другие. Решение математических уравнений связи предполагает вычисление по исходным данным их параметров (свободного члена b 0 и коэффициентов регрессии b 1 , b 2 , …). При всем разнообразии эмпирических формул все же имеется вид аналитической зависимости, получивший широкое распространение. Им яв- ляется уравнение регрессии в виде многочленов (полинома), расположенных по восходящим степеням изучаемого фактора и одновременно линейных ко всем коэффициентам. Такая формула имеет вид: y = f(x) = b 0 + b 1 x + b 2 x 2 +…+ b m x m , где b 0 , b 1 , b 2 ,…, b m − коэффициенты, подлежащие определению. Этот ряд − сходящийся, т.к. стремится к некоторому пределу. Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 40 Эмпирические формулы (аппроксимирующие уравнения) всегда имеют ограниченную область применения, которая не должна выходить за пределы имеющихся опытных данных. Широкое применение аппроксимирующих уравнений объясняется следующими причинами: 1. Точное аналитическое выражение зависимости между исследуемы- ми величинами может оставаться неизвестным и поэтому по необходимости приходится ограничиваться приближенными формулами эмпирического ха- рактера. 2. Точная функциональная зависимость выражается формулой на- столько сложной, что ее непосредственное применение при вычислениях бы- ло бы очень затруднительным. Эмпирические формулы могут быть разнообразными, т.к. при выборе аналитической зависимости руководствуются не какими-то строгими тео- риями (физическими или экономическими), а ставят только одно условие − возможно близкое соответствие значений, вычисленных по формуле опыт- ным данным. Таким образом, формально описание одного и того же процесса можно дать разными по виду уравнениями. Их пригодность оценивается только по одному критерию − наиболее точное предсказание эксперимен- тального результата. В эмпирическую формулу можно вводить различное число постоян- ных параметров (коэффициентов), величину которых нужно определить с большой точностью. Более удачными (удобными) следует считать уравнения с небольшим числом коэффициентов (не более 2 −3). В противном случае возрастают трудности с применением таких формул. связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 41 2.3. Метод наименьших квадратов Для определения коэффициентов уравнения регрессии b применяют разные методы (графический, метод средних), однако наибольшее распро- странение получил метод наименьших квадратов (МНК). Пусть обсуждается некоторая зависимость y = f(x), которая отражает какой-то процесс, имеющий плавное течение, и поэтому все параметры сис- темы изменяются постепенно, без скачков. В этих случаях эксперименталь- ные точки, нанесенные на графике, должны бы укладываться на некоторую плавную кривую (в частном случае, прямую). Однако на практике опреде- ленный разброс экспериментальных точек всегда наблюдается, что связано с изменчивостью (ошибками) регистрируемых измерений. Понятно, что такого разброса удалось бы избежать, если бы результаты измерений оказались со- вершенно свободными от ошибок, и тогда точки, отвечающие этим результа- там, строго ложились бы на соответствующую плавную кривую, или прямую линию. Поэтому все процессы, которые имеют заведомо плавное течение, принято изображать также плавными кривыми, проводя их не через точки, а так, чтобы кривая проходила по возможности ближе ко всем точкам на гра- фике. Однако такое указание оставляет при построении кривых определен- ный произвол. Его частично можно устранить основным положением МНК: сумма квадратов отклонений ε i экспериментальных точек от кривой по вертикальному направлению, т.е. сумма квадратов величин ε i , должна быть наименьшей (Σ ε i 2 = минимум). Или иначе − сумма квадратов отклонений известных (эксперимен- тальных) значений исследуемой функции и соответствующих значений ап- проксимирующей функции (теоретическими показателями) должна быть наименьшей. Бараз В.Р. Корреляционно-регрессионный анализ ГОУ ВПО УГТУ −УПИ – 2005 42 Довольно часто при описании аппроксимирующей функции ограни- чиваются простым видом полиноминальной зависимости, полагая ее линей- ной, т.е. в виде уравнения прямой y = b 0 + b 1 x. Здесь свободный член b 0 харак- теризует сдвиг и равен тому значению у, которое получается при х = 0, а ко- эффициент b 1 определяет наклон линии. Отыскание коэффициентов b 0 и b 1 осуществляется по МНК. Пусть имеется n экспериментальных точек (n пар наблюдений): (x 1 , y 1 ); (x 2 , y 2 );… ( x n , y n ). Введем следующие обозначения: у i – это измеренные (экспериментальные) значения изучаемого параметра, а ŷ i – его теоретиче- ские (рассчитанные по уравнению) показатели. Предположим, что экспериментальные точки на графике укладывают- ся так, что по ним вполне возможно провести прямую линию (рис.11). Значе- ния функции ŷ i в этом случаеможнозаписать в виде линейного уравнения: ŷ i = b 0 + b 1 x i . Расстояние по ординате (вертикали) от точки y i до прямой со- ставит: b 0 + b 1 x i − y i = ε i , где b 0 + b 1 x i = ŷ i − рассчитанное (теоретическое) значение функции; y i − ее измеренное (опытное) значение и ε i − разница (рас- стояние) между ŷ i и y i Рис. 11.Схематическое пояснение содержания метода наименьших квадратов y i { ŷ i у i ŷ i ε связи показателей коммерческой деятельности с использованием программы Excel ГОУ ВПО УГТУ −УПИ – 2005 43 В соответствии с МНК полагаем, что искомая прямая будет наилуч- шей, если сумма квадратов всех расстояний (b 0 + b 1 x i − y i ) 2 = ε i 2 окажется наи- меньшей. Минимум этой суммы ищется по правилам дифференциального ис- числения. В результате для определения b 0 и b 1 используются следующие уравнения: ∑ ∑ ∑ ∑ ∑ ∑ = = = = = = ⎟ ⎠ ⎞ ⎜ ⎝ ⎛ − − = n i n i i i n i n i n i n i i i i i i x x n y x x y x b 1 2 1 2 1 1 1 1 2 0 ; ∑ ∑ ∑ ∑ ∑ = = = = = ⎟ ⎠ ⎞ ⎜ ⎝ ⎛ − − = n i n i i i n i n i i i i n i i x x n y x y x n b 1 2 1 2 1 1 1 1 Особенности МНК: 1. Этот метод не дает ответа на вопрос о том, какого вида функция лучше всего аппроксимирует конкретные экспериментальные точки. Вид интересующей нас функции должен быть задан на основе каких- то физических или экономических соображений (либо специальным образом отыскан). МНК позволяет лишь выбрать, какая из прямых (парабол, экспо- нент) является лучшей прямой (параболой, экспонентой) для прогнозирова- ния. 2. Вычисления по МНК являются достаточно громоздкими, поэтому основная нагрузка − на компьютерные программы. 3. МНК является достаточно точным приемом и позволяет получить вполне надежные результаты. Одновременно он является интерполяционным |