Материал: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78

ЛИНЕЙН и в качестве ее аргументов указывается:известные значения Y – A2:A11;известные значения X – B2:B11.В результате выполнения функции в B13 появится число, соответствующее коэффициенту а₁ в уравнении (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). Для того, чтобы увидеть остальные коэффициенты необходимо выделить ячейки В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, K произвести аналогичные расчеты и построения диаграмм для полиномов второй, третьей и четвертой степени. При этом при расчетах уравнения третьей степени в качестве параметра известные значения X указать – B2:D11, а уравнения четвертой степени – B2:E11.7.3.3. Выбор оптимального уравнения регрессииПо мере увеличения степени полинома наблюдается все более лучшее соответствие между экспериментальными и расчетными данными.Отметим, что при наличии N измерений максимально возможная степень полинома равна N-1. При использовании полинома максимальной степени будет достигнуто максимальное соответствие между экспериментальными и расчетными данными, т.е. расчетная кривая пройдет по всем экспериментальным точкам.Однако, интуитивно очевидно, что описывающая кривая не должна быть очень сложной и нет необходимости в идеальном совпадении расчетов с экспериментом. Описание должно быть как можно более простым, а отклонения от него легко объясняется возможными погрешностями в исходных данных.Поэтому встает вопрос: а на какой степени полинома остановиться?Для решения этого вопроса предлагается следующая схема вычислений:

• 
  для каждого уравнения регрессии рассчитывается остаточная сумма квадратов. Для ее расчета используется функция Excel СУММКВРАЗН. Для вышеприведенного примера расчет остаточной суммы квадратов уравнения первой степени производится следующим образом:
  
• 
  курсор устанавливается в G12, вызывается функция СУММКВРАЗН и в качестве ее аргументов указываются столбцы А и G (A2:A11;G2:G11).
  
• 
  аналогично в строке 12 столбцов H, I, J производятся расчеты остаточных сумм для полиномов второй, третьей и четвертой степени.
  
• 
  в F12 отдельно рассчитывается остаточная сумма квадратов для полинома нулевой степени. Для ее расчета в указанную ячейку вводится формула =ДИСПА(A2:A11)*9 (здесь 9 это число измерений минус один);
  
• 
  дальнейшие расчеты показываются на следующем примере.
  

Пусть для обработки было представлено 10 измерений – N=10.И пусть в результате расчетов остаточных сумм квадратов для уравнений разных степеней получены следующие результаты:

Степень уравнения	0	1	2	3	4
Остаточная сумма квадратов	10000	4000	155	152	150

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

1. 
   Вычисляются суммы квадратов, приходящиеся на каждую компоненту уравнения. Вычисления производятся по формуле:
   

SS_с = SS_k-1 – SS_k , (7.9)гдеSS_с – остаточная сумма квадратов, для k-ого члена полинома;SS_k-1 – остаточная сумма квадратов для уравнения (k-1)-ой степени;SS_с – остаточная сумма квадратов для уравнения k-ой степени.Для данного примера:

Степень уравнения	0	1	2	3	4
Остаточная сумма квадратов	10000	4000	155	152	150
Сумма квадратов, приходящаяся на компоненту уравнения		6000	3845	3	2

2. 
   Определяются числа степеней свободы для компонент уравнения остаточной суммы квадратов.
   

Для каждой компоненты это число равно 1, а для остаточной суммы вычисляется по формуле:f = N – k – 1, (7.10)где N – общее число измерений;k – количество коэффициентов в уравнении.Для данного примера:

Степень уравнения	0	1	2	3	4
Число степеней свободы, для компоненты		1	1	1	1
Число степеней свободы на остаточную сумму квадратов (ошибки)		7	6	5	4

3. 
   Определяются величины дисперсий для компоненты и ошибки текущей степени уравнения.
   

Вычисления производятся по формуле:s² = SS / f. (7.11)Для данного примера:

Степень уравнения	0	1	2	3	4
Дисперсия для компоненты		6000	3845	3	2
Дисперсия для ошибки		571,42	25,83	30,4	37,5

4. 
   Для каждой компоненты вычисляется критерий Фишера.
   

Вычисления производятся по формуле:F = s²_k / s²_e , (7.12)где s²_k – дисперсия компоненты;s²_e – дисперсия ошибки.Для данного примера:

Степень уравнения	0	1	2	3	4
F-отношение		10,5	148,84	0,098	0,0533

5. 
   Для каждой компоненты определяются критические значения критерия Фишера.
   

Эти значения вычисляются с помощью встроенной в Excel функции

FРАСПОБР.Аргументами этой функции являются: а) уровень значимости. Если мы хотим сделать свои выводы с надежность 95%, то его значение должно быть равно 0,05.б) число степеней свободы для числителя.У нас при вычислении F-отношения в числителе находилась дисперсия компоненты, число степеней свободы которой всегда равно 1.в) число степеней свободы для знаменателя. Здесь указывается число степеней свободы для ошибки.В результате всех вычислений должна получиться следующая сводная таблица.

Степень уравнения	0	1	2	3	4
Остаточная сумма квадратов	10000	4000	155	152	150
Сумма квадратов, приходящаяся на компоненту уравнения		6000	3845	3	2
Число степеней свободы, для компоненты		1	1	1	1
Число степеней свободы для остаточной суммы квадратов		7	6	5	4
Дисперсия для компоненты		6000	3845	3	2
Дисперсия для ошибки		571,42	25,83	30,4	37,5
F-отношение		10,5	148,84	0,098	0,0533
F критическое		5,59	5,98	6,608	7,7086

1   ...   27   28   29   30   31   32   33   34   ...   45