Стандотклонп (значение1, значение2,. )

В качестве примера рассчитаем наличие исправного тракторного парка в районах Пензенской области по состоянию на 1 марта 1998года. (Социально -

36

экономическое положение городов и районов Пензенской области в январе – феврале 1998г. Пензенский облкомстат, Пенза, Март 1998г, с. 63.)

Таблица 6 Наличие исправной сельскохозяйственной техники в с/х Пензенской области

Наименование	Наличее трактров,
района	штук
Бековский	234
Белинский	486
Колышлейский	504
Кондольский	185
М-Сердобинский	204
Томалинский	474
Башмаковский	450
Вадинский	183
Земетченский	300
Н-Ломовский	330

Считая приведенные данные выборкой из генеральной совокупности, рассчитаем среднеквадратическое отклонение с использованием функции СТАНДОТКЛОН (). В результате получим σ = 133. При этом среднее ариф-метическое числа тракторов, рассчитанное с использованием функции СРЗНАЧ () равно 335.

Анализ проведенных расчетов показывает, что почти две трети работ-ников районов области, отвечающих за исправность тракторного парка перед посевной, обеспечили наличие техники от 202 единиц (335 – 133) до 468 еди-ниц (335 + 133).

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

Доверительные интервалы, это области тех значений генеральной сово-купности средней величины признака, выход за пределы которых имеет малую вероятность.

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

37

В качестве примера рассчитаем доверительные интервалы наличия сво­бодных рабочих мест и вакантных должностей по Пензенской области. Цифро­вой материал заимствован из сборников Пензенского облкомстата «Социально - экономическое положение городов и районов Пензенской области» за 1997год.

Для проведения расчетов используем средство Excel Пакет анализа. Для этого необходимо выполнить следующие действия.

1. Выбрать команду Сервис => Надстройки.

2. В появившемся диалоговом окне Надстройки (рисунок 5) устано­вить флажок Пакет анализа.

3. Щелкнуть по кнопке ОК.

 

Рисунок 5 - Диалоговое окно Надстройки

4. Из главного меню выбрать команду Сервис => Анализ данных

Ш

Анализ данных

Инструменты анализа

Однофакгорный диверсионный анализ Двухоакгорный дисперсионный анализ: повторениями Двухоакгорный дисперсионный анализ зез повторений Корреляция Ксеариация

Энгпсненциальное сглаживание
Двухвыборо-ньй Р-тест для /;исгерсии
Анализ Фурье
Гистограмме_____________________

ОК

Отмена

Справка

 

_

Рисунок 6 - Диалоговое окно Анализ данных

38

5. Выбрать из списка инструмент анализа Описательная статисти-ка (рисунок 6).

6. Щелкнуть по кнопке ОК.

7. В появившемся диалоговом окне Описательная статистика (рисунок 7) установить входной и выходной интервалы и уровень надежности.

8. Щелкнуть по кнопке ОК.

 

Рисунок 7 – Окно диалога Описательная статистика

В результате появиться решение, показанное на рисунок 8. Здесь же при-ведено значение средней арифметической, определенной с помощью функции СРЗНАЧ () и вычисленное значение доверительных интервалов.

Н а л и ч и е с в ободных м е с т и вакант ных должностей

по Пенз енс кой облас т и в1997. г. (П о мат ериал ам Пенз енс ког о

облкомг ос т ат а)

1209 1355 1729 2008 1788 2232 2210

На л и ч и е рабочих мес т

Стол б ец 1

Уровень надежности(95,0%)                                  368

среднее             1790

Ни ж н и й предел доверительного интервала                    1422

Верхний предел доверительного интервала                2158

Рисунок 8 - Результаты решения

39

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

4.2 Диагностика проблем с использованием инструментов регресси­онного анализа

Предположим, что ставиться задача оценки влияния стоимости покупки товаров на общую величину денежных расходов населения Пензенской облас­ти. Исходные данные, по материалам облкомстата г. Пензы за 1998 года в млн. рублей, приведены в таблице 7.

Таблица 7

Расходы, связанные с покупкой товаров и

оплатой услуг

 

Покупка товаров	Оплата услуг	Всего денежных расходов
362,8	107,1	547,3
368,0	93,4	560,7
409,1	94,9	595,5
387,7	84,3	549,1
372,9	79,8	534,5
366,3	84,3	509,6
377,5	87,2	546,3
404,5	85,2	476,6
542,3	90,5	606,2
524,7	100,6	690,5
544,5	97,4	729,9
629,9	107,5	959,0

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

1. Ввести исходные данные, например, заполнить ячейки А7:В20.

2.Выбрать команду Вставка => Диаграмма, либо щелкнуть на кнопке Мастер диаграмм.

3. На первом шаге работы инструмента Мастер диаграмм необходимо выбрать тип диаграмм. Рекомендуется выбрать Точечная (рисунок 9) Щелк­нуть кнопку Далее.

4. На втором шаге работы инструмента Мастер диаграмм необходимо уточнить, что в текстовом поле Диапазон введены адреса ячеек, содержащих данные необходимые для построения диаграммы (рисунок 10). Щелкнуть кноп­ку Далее.

 

5. На третьем шаге работы инструмента Мастер диаграмм необходимо выбрать параметры диаграммы: название осей, легенду, координатную сетку и т. д. (рисунок 11). Щелкнуть кнопку Далее.

6. На четвертом шаге необходимо выбрать место размещения диаграм­мы и щелкнуть на кнопке Готово. Когда диаграмма появится на рабочем лис-

40

те, необходимо щелкнуть правой кнопкой мыши на любом из маркеров ряда. Появится контекстного меню (рисунок 12) на котором необходимо выбрать команду Добавить линию тренда. Появится диалоговое окно, показанное на рисунке 13.

Рисунок 9 - Мастер диаграмм. Шаг 1

Рисунок 10 - Мастер диаграмм Шаг 2

Рисунок 11- Мастер диаграмм Шаг 4

41

42

Рисунок 12- Контекстное меню

Рисунок 13 - Окно выбора типа аппроксимации

7. В соответствии с рисунком 13 выбрать тип аппроксимации (например Линейная) и щелкнуть на вкладке Параметры.

43

Рисунок 14 - Окно установки параметров тренда

В появившемся окне (рисунок 14) проверить, установку флажков для показа уравнения на диаграмме и численное значение достоверности аппрок­симации (R^{^} 2). Щелкнуть по кнопке ОК. Появляется окно с окончательными результатами расчетов (рисунок 15).

В регрессионном уравнении под зависимой переменной у обозначена общая величина денежных расходов населения, а под объясняющей перемен­ной х - расходы, связанные с покупкой товаров. Величина достоверности ап­проксимации R^{^2} представляет собой квадрат коэффициента корреляции между (у) и (х) и на физическом уровне показывает долю дисперсии в общем объеме денежных расходов, связанную с дисперсией денежных расходов на покупку товаров.

Вывод. Анализируя полученные результаты можно сделать вывод о том, что около 78% расходов населения Пензенской области приходится на покупку товаров. Данный вывод может оказаться полезным при принятии решения о формировании потребительской корзины.

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

Построить уравнение множественной регрессии средствами MS Excel можно двумя способами: с помощью функции ЛИНЕЙН или же с помощью пакета Анализ данных.

Результаты расчетов по первому способу приведены на рисунке 16.

44

 

Рисунок 15 - Окончательный вариант расчетов

ЛИНЕЙН(С7:С18;А7:В18;ИСТИНА;ИСТ ИНА)

-238,85036

4,271277911 1,024704

179,53751 #Н/Д #Н/Д #Н/Д

2,287451269 0,22504

0,840517526 58,11124

23,71626658           9

160175,699130392,25

Рисунок 16 - Результаты расчетов

Коэффициенты регрессии и свободный член уравнения приведены в верхней строке рисунка 16. При составлении уравнения регрессии следует иметь ввиду, что функция ЛИНЕЙН возвращает коэффициенты регрессии не в том порядке, в котором расположены изменяемые переменные в рабочем листе.

Тогда уравнение регрессии имеет вид

)

(3)

у = - 238,85 + 1,025х₁ + 4,27х₂,

где у ⁾ - общие расходы;

х₁ – расходы, связанные с покупкой товаров; х₂ – расходы, связанные с оплатой услуг.

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

45

В третьей строке рассчитан квадрат коэффициента корреляции R^{^ 2} и

приведена величина стандартной погрешности (ошибки) аппроксимации.

В четвертой строке приведено значение F - статистики, необходи­мой для проверки адекватности уравнения регрессии (условия является ли ус­тановленная связь случайной или же нет). Кроме того, здесь же показано чис­ло степеней свободы, которое необходимо для определения критического табличного значения критерия F_кр.

В пятой строке приведены регрессионная и остаточная суммы квадратов отклонений. Проводя регрессионный анализ, MS Ex cel вычисляет для каждой точки квадрат разности между прогнозируемым значением у и фактическим значением y. Сумма этих квадратов разностей называется оста­точной суммой квадратов. Затем подсчитывается сумма квадратов разностей между фактическими значениями y и средним значением у, которая называется общей суммой квадратов (регрессионная сумма квадратов плюс остаточная сумма квадратов). Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента корреляции R^².

Используя данные расчетов, оценим адекватность полученного урав­нения регрессии. Для определения F_кр можно воспользоваться либо статисти­ческой функцией FРАСПОБР () табличного процессора MS Excel, либо таб­лицей распределения Фишера - Снедекора (F - распределения), приведенной в [ 10] на с. 499. Входом в таблицу являются величины v₁ - число переменных в уравнении регрессии и v₂ - число степеней свободы. Для уровня надежности 95%, v₁ =2 и v₂ = 9 имеем F_кр = 4,26. Так как F_р = 23,71 значительно больше F_кр = 4,26 то можно сделать вывод, что полученное уравнение адекватно опи­сывает рассматриваемое явление.

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

-t _р - статистика для первого коэффициента =1,025/ 0,225 = 4,55; -t _р - статистика для второго коэффициента =4,271/ 2,287 = 1,49. Для определения t_кр можно воспользоваться либо статистической функцией СТЬЮДРАСПОБР () табличного процессора MS Excel, либо таблицей рас­пределения Стьюдента (t - распределения), приведенной в [ 10 ] на с. 493. Вхо­дом в таблицу является параметр v - число степеней свободы. Для уровня на­дежности 95% и v = 9 имеем t _кр = 2,26. Так как t_р = 4,55 для первого коэффи­циента больше t_кр = 2,26 то можно сделать вывод, что первый коэффициент является значимым. И наоборот второй коэффициент является незначи­мым и его можно исключить из уравнения.

Рассмотрим построение уравнения более удобным вторым способом с помощью пакета Анализ данных и его инструмента Регрессия

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

46

Ввести ячейки в поле Входной интервал У и поле Выходной интервал Х, установить флажок Уровень надежности 95, указать Выходной интервал и нажать кнопку ОК. Результаты расчетов показаны на рисунке 18.

 

Рисунок 17 - Окно диалога

Регрессионная статистика

Множественный R  0,916797429

R-квадрат ___________ 0,840517526

Нормированный R-кв а 0,805076976

58,11124159 12

Стандартная ошибка На бл ю д ен ия

 

Дисперсионный анализ
	<#	88	М8	Р	Значимость F
Регрессия	2	160175,6991	80087,84954	23,71626658	0,00025835
Остаток	9	30392,24759	3376,916399
Итого	11	190567,9467

 

Коэффициент Стандартная ошибка t-статистика P-Значение  Нижние 95%
Y-пересечение	-238,850358	179,5375101	-1,330364657	0,216121449	-644,9927319
Переменная X 1	1,024703982	0,225040296	4,553424439	0,001379169	0,515627077
Переменная X 2	4,271277911	2,287451269	1,867265095	0,094710635	-0,903300306

Рисунок 18 - Результаты расчета

Уравнение регрессии, полученное с помощью инструмента Регрессия, практически не отличается от полученного с помощью функции ЛИНЕЙН, однако приведенный сопутствующий статистический материал гораздо удобнее и полнее описывает рассматриваемый пример и раскрывает его физическую сущность.

47

Так в частности, приведенный в первой таблице рисунка 18 параметр множественный R определяемый как корень квадратный из R - квадрат, яв­ляется коэффициентом корреляции и определяет корреляцию между общими расходами населения и полученной комбинацией расходов, связанных с покуп­кой товаров и оплатой услуг.

Нормированный R - квадрат позволяет произвести оценку объема ис­следуемой выборки. Так в частности, при увеличении объема выборки, чис­ленное значение Нормированного R - квадрат приближалось бы к фактиче­скому значению R - квадрат.

Вторая таблица рисунка 18 показывает результаты дисперсионного анализа.

В третьей таблице помимо коэффициентов регрессии и их стандартных погрешностей (ошибок) приведены расчетные t - статистики для каждого пара­метра уравнения регрессии, а также величины их доверительных интервалов. Кроме того, в таблице приводится р - значение, называемое так же р - уро­вень. Этот показатель находится в убывающей зависимости от уровня надеж­ности формируемых статистических данных. Он показывает вероятность появ­ления ошибки, связанной с распространением полученного результата на всю статистическую совокупность в целом. Например, р - уровень = 0,05 (1/20) по­казывает, что имеется 5% вероятность, что найденная в выборке связь между переменными является лишь случайной особенностью данной выборки. С по­мощью параметр р -значение имеется возможность предварительной оценки, без использования специальных статистических таблиц, значимости коэффи­циентов уравнения регрессии. Например, из третьей таблицы рисунка 18 вид­но, что р - значение второй переменной большее 0,05, что может служить ос­нованием вывода о не значимости второй переменной. Этот вывод нами уста­новлен ранее путем сравнения расчетного и критического значений t - стати­стик.

Учитывая, что уравнение регрессии адекватно описывает изучаемое яв­ления, а также то, что t -статистика и р - значение для переменной х₂ не зна­чительно отличаются от критических значений уровня надежности, принимаем решение оставить переменную х₂ в уравнении регрессии.

Вывод. В целом, выполненные исследования позволяют сделать вывод о том, что около 92% всех денежных расходов населения Пензенской области тратится им на покупку товаров и оплату услуг, причем около 80% из них рас­ходуется на покупку товаров и лишь примерно 12 % на оплату услуг. Другие виды расходов, например покупка валюты, ценных бумаг и т.д. можно считать статистически не значимыми. Полученный вывод может быть использован при принятии управленческого решения по формированию потребительской кор­зины, расчету прожиточного минимума, оценки перспектив деятельности бан­ковских структур и рынка ценных бумаг.

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

48

пользованием функции ТЕНДЕНЦИЯ. Данная функция вычисляет уравнение регрессии так же как это делает функция ЛИНЕЙН, и при необходимости мо-жет применяться для новых прогнозных значений переменных.

На рисунке 19 в нижней его части показаны результаты прогнозирования с использованием функции ТЕНДЕНЦИЯ

 

 

 

ЩПотрвб. расходы
	А	В                      С		о
Э	409,1	Э4.Э	595,5
10	387,7	84,3	549/
11	372,9	79,8	534,5
12	366,3	84,3	509,6
13	377,5	87,2	546,3
и-	404,5	85,2	476,6
15	542,3	90,5	606,2
16	524,7	100,6	690,5
17	544,5	97/	729,9
18 19	629,9	107,5	959,0
	500,0	200,0	{=ТЕНЦЕНЦИЯ(С7:С18;А7:В18;А19:В23Н|
20                   400,0		290,0	1238,9
21                      450		450	2144,341494
22                       ЕОО		ЗОО	1657,355404
СП	5501         270		1477,931868

Рисунок 19 - Результаты прогнозирования

Изменяя соотношения переменных х ₁ (ячейка А) и х₂ (ячейка В), ре­зультат прогноза денежных расходов населения отображается в ячейках С19:С23. Приведенная в этих ячейках сумма прогнозных расходов также мо­жет служить исходной информацией для принятия соответствующего управ­ленческого решения.