2. 4 Научный инструментарий системного анализа Вопросы для самотестирования (стр. 10 из 13)

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,6991 30392,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 - Окно диалога

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