7.2.4. Варианты заданий
Варианты заданий взять из Приложения 2 «Районы и города Чувашии в цифрах».
Во всех заданиях методом дисперсионного анализа выяснить имеется ли различие в средних значениях показателя по уровням изучаемого фактора и дать экономическую интерпретацию полученных результатов.
7.3. Регрессионный анализ
7.3.1.Общие сведения
В тех случаях, когда математическая модель процесса неизвестна, для аппроксимации имеющихся данных используются полиномиальные зависимости вида:
, (7.5)
где
y – выходной параметр;
x – фактор;
а – коэффициенты уравнения;
n – степень полинома.
Полином первой
степени имеет вид:
(7.6)
второй:
(7.7)
третьей:
(7.8)
и т.д.
Основной задачей регрессионного анализа является получение адекватного описания полученных данных полиномом минимальной степени.
7.3.2. Порядок выполнения работы
1. Взять данные своего варианта из Приложения 4 «Транспорт и связь» или открыть файл Транспорт и связь.doc.
2. Скопировать данные своего варианта в новую рабочую книгу. Пусть это будут следующие данные:
|
Y (показатель) |
X (время) |
|
1 |
0,1 |
|
5 |
0,4 |
|
10 |
0,7 |
|
11 |
1 |
|
10 |
1,3 |
|
8 |
1,6 |
|
7 |
1,9 |
|
8 |
2,2 |
|
7 |
2,5 |
|
6 |
2,8 |
3. Последовательно рассчитать столбцы значений X2, X3 и X4.
|
|
A |
B |
C |
D |
E |
|
1 |
Y |
X |
X2 |
X3 |
X4 |
|
2 |
1 |
0,1 |
0,01 |
0,001 |
0,0001 |
|
3 |
5 |
0,4 |
0,16 |
0,064 |
0,0256 |
|
4 |
10 |
0,7 |
0,49 |
0,343 |
0,2401 |
|
5 |
11 |
1 |
1 |
1 |
1 |
|
6 |
10 |
1,3 |
1,69 |
2,197 |
2,8561 |
|
7 |
8 |
1,6 |
2,56 |
4,096 |
6,5536 |
|
8 |
7 |
1,9 |
3,61 |
6,859 |
13,0321 |
|
9 |
8 |
2,2 |
4,84 |
10,648 |
23,4256 |
|
10 |
7 |
2,5 |
6,25 |
15,625 |
39,0625 |
|
11 |
6 |
2,8 |
7,84 |
21,952 |
61,4656 |
4. В строке 13 получить уравнение регрессии первой степени. Для этого в B13 вызывается функция ЛИНЕЙН и в качестве ее аргументов указывается:
известные значения Y – A2:A11;
известные значения X – B2:B11.
В результате выполнения функции в B13 появится число, соответствующее коэффициенту а1 в уравнении (1). Для того, чтобы увидеть второй коэффициент необходимо выделить ячейки B13:C13, затем нажать F2 и затем выполнить тройное нажатие Ctrl+Shift+Enter.
Для обрабатываемых данных получатся следующие значения:
|
|
A |
B |
C |
|
13 |
Y1= |
0,707071 |
6,274747 |
5. В строке 14 получить уравнение регрессии второй степени. Для этого в B14 вызывается функция ЛИНЕЙН и в качестве ее аргументов указывается:
известные значения Y – A2:A11;
известные значения X – B2:С11.
В результате выполнения функции в B14 появится число, соответствующее коэффициенту а2 в уравнении (2). Для того, чтобы увидеть остальные коэффициенты необходимо выделить ячейки В14:C14, затем нажать F2 и затем выполнить тройное нажатие Ctrl+Shift+Enter.
Для обрабатываемых данных получатся следующие значения:
|
|
A |
B |
C |
D |
|
14 |
Y2= |
-3,28283 |
10,22727 |
1,810101 |
6. Аналогично в строках 15 и 16 получить коэффициенты полиномов 3 и 4-ой степени.
7. Используя полученные уравнения регрессии рассчитать предсказываемые с его помощью значения выходного параметра.
Пример расчета по уравнению первой степени:
В G2 вводится формула =$C$13+$B$13*B2, которая затем копируется на весь столбец G. При этом должны получиться следующие значения:
|
|
A |
B |
C |
D |
E |
F |
G |
|
1 |
Y |
X |
X2 |
X3 |
X4 |
|
Y1 |
|
2 |
1 |
0,1 |
0,01 |
0,001 |
0,0001 |
|
6,345455 |
|
3 |
5 |
0,4 |
0,16 |
0,064 |
0,0256 |
|
6,557576 |
|
4 |
10 |
0,7 |
0,49 |
0,343 |
0,2401 |
|
6,769697 |
|
5 |
11 |
1 |
1 |
1 |
1 |
|
6,981818 |
|
6 |
10 |
1,3 |
1,69 |
2,197 |
2,8561 |
|
7,193939 |
|
7 |
8 |
1,6 |
2,56 |
4,096 |
6,5536 |
|
7,406061 |
|
8 |
7 |
1,9 |
3,61 |
6,859 |
13,0321 |
|
7,618182 |
|
9 |
8 |
2,2 |
4,84 |
10,648 |
23,4256 |
|
7,830303 |
|
10 |
7 |
2,5 |
6,25 |
15,625 |
39,0625 |
|
8,042424 |
|
11 |
6 |
2,8 |
7,84 |
21,952 |
61,4656 |
|
8,254545 |
По данным столбцов А и G построить совместный график, общий вид которого показан на рисунке. При этом «экспериментальные» данные (столбец А) представлены точками, а рассчитанные – сплошной линией (рис. 7.1).
Рис. 7.1. Результат аппроксимации реальных данных линейной зависимостью
И без статистической проверки очевидно, что соответствие между экспериментальными и расчетными данными отсутствует.
В столбцах I, J произвести аналогичные расчеты и построения для полиномов третьей и четвертой степени. При этом при расчетах уравнения третьей степени в качестве параметра известные значения X указать – B2:D11, а уравнения четвертой степени – B2:E11.