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

где:SS – сумма квадратов;df – число степеней свободы; MS – дисперсия;F – расчетное значение критерия Фишера;P-значение – расчетное значение минимальной значимости;F-критическое – критическое (табличное) значение критерия Фишера.Полученное значение F сравнивается с критическим. Если вычисленное значение F больше критического, то делается вывод о статистически значимом влиянии фактора на изучаемый объект. В данном случае 50,24 > 3,24. Поэтому можно сделать вывод, что разные типы краски существенно отличаются друг от друга по качеству.7.2.3. Методы, применяемые после дисперсионного анализаДисперсионный анализ показывает, существует ли статистически существенное влияние изучаемого фактора на свойства объекта. Экспериментатора же помимо этого интересует также вопрос – а каково конкретное влияние фактора и как меняются свойства объекта при переходе от одного уровня фактора к другому? Другими словами – экспериментатору важно выяснить – существует ли статистически существенное различие в средних значениях по уровням фактора. Заметим, что в случае фактора с двумя уровнями этот вопрос не стоит. В самом деле, если дисперсионный анализ показал, что имеется статистически существенное влияние фактора, то автоматически существенно различаются и средние по этим двум уровням. А как быть, если число уровней фактора больше двух? Например, в рассмотренном выше примере, экспериментатора может заинтересовать вопрос: а имеется ли разница между красками B и D? Для них средние значения вроде бы близки (11,8 и 9,8 соответственно).Для этих целей наиболее часто используется ранговый критерий ДунканаОбщую схему применения этого критерия рассмотрим на вышеприведенном примере. Она состоит из следующих этапов.

1. 
   Упорядочить k средних по возрастанию.
   

В нашем примере k=4 и упорядоченные средние представляются рядом:

Средние	9,8	11,8	20,6	29,8
Тип краски	D	B	A	C

2. Из таблицы дисперсионного анализа берется внутригрупповая дисперсия с соответствующим числом степеней свободы.В нашем случае: s²_e = 8,35 при f = 16.3. Вычисляется нормированная ошибка для среднего по испытанию: , (7.4)где m – число опытов в одном варианте испытаний.В нашем случае: . 4. Из таблицы критерия Дункана (табл. 7.3) выписываются (k-1) рангов при выбранном уровне значимости и числе степеней свободы, соответствующем ошибке.Таблица 7.3

F	Ранг
	2	3	4	5	6	7	8		9	10	12	14	16	18
1	18,0	18,0	18,0	18,0	18,0	18,0	18,0	18,0		18,0	18,0	18,0	18,0	18,0
2	6,09	6,09	6,09	6,09	6,09	6,09	6,09	6,09		6,09	6,09	6,09	6,09	6,09
3	4,50	4,50	4,50	4,50	4,50	4,50	4,50	4,50		4,50	4,50	4,50	4,50	4,50
4	3,93	4,01	4,02	4,02	4,02	4,02	4,02	4,02		4,02	4,02	4,02	4,02	4,02
5	3,64	3,74	3,79	3,83	3,83	3,83	3,83	3,83		3,83	3,83	3,83	3,83	3,83
6	3,46	3,58	3,64	3,68	3,68	3,68	3,68	3,68		3,68	3,68	3,68	3,68	3,68
7	3,35	3,47	3,54	3,58	3,60	3,61	3,61	3,61		3,61	3,61	3,61	3,61	3,61
8	3,26	3,39	3,47	3,52	3,55	3,56	3,56	3,56		3,56	3,56	3,56	3,56	3,56
9	3,20	3,34	3,41	3,47	3,50	3,52	3,52	3,52		3,52	3,52	3,52	3,52	3,52
10	3,15	3,30	3,37	3,43	3,46	3,47	3,47	3,47		3,47	3,47	3,47	3,47	3,47
11	3,11	3,27	3,35	3,39	3,43	3,44	3,45	3,46		3,46	3,46	3,46	3,46	3,46
12	3,08	3,23	3,33	3,36	3,40	3,42	3,44	3,44		3,46	3,46	3,46	3,46	3,46
13	3,06	3,21	3,30	3,35	3,38	3,41	3,42	3,44		3,45	3,45	3,46	3,47	3,47
14	3,03	3,18	3,27	3,33	3,37	3,39	3,41	3,42		3,44	3,45	3,46	3,47	3,47
15	3,01	3,16	3,25	3,31	3,36	3,38	3,40	3,42		3,43	3,44	3,45	3,46	3,47
16	3,00	3,15	3,23	3,30	3,34	3,37	3,39	3,41		3,43	3,44	3,45	3,46	3,47
17	2,98	3,13	3,22	3,28	3,33	3,36	3,38	3,40		3,42	3,44	3,45	3,46	3,47
18	2,97	3,12	3,21	3,27	3,32	3,35	3,37	3,39		3,41	3,43	3,45	3,46	3,47
19	2,96	3,11	3,19	3,26	3,31	3,35	3,37	3,39		3,41	3,43	3,44	3,46	3,47
20	2,95	3,10	3,18	3,25	3,30	3,34	3,36	3,38		3,40	3,43	3,44	3,46	3,47
22	2,93	3,08	3,17	3,24	3,29	3,32	3,35	3,37		3,39	3,42	3,44	3,45	3,46
…
100	2,80	2,95	3,05	3,12	3,18	3,22	3,26	3,29		3,32	3,36	3,40	3,42	3,45
…
∞	2,77	2,92	3,02	3,09	3,15	3,19	3,23	3,26		3,29	3,34	3,38	3,41	3,44

В нашем примере для f = 16 и =0,05 выписанные ранги выглядят следующим образом:

№	2	3	4
Ранги	3,00	3,15	3,23

5. Получить наименьшие значимые ранги путем умножения выписанных рангов на нормированную ошибку.В нашем случае:

№	2	3	4
НЗР	3,876	4,070	4,173

6. Произвести сравнение наблюдаемых разностей между средними с вычисленными НЗР по схеме:разница между рядом стоящими средними сравнивается с минимальным НЗР (при № = 2);разница между средними через одно сравнивается с НЗР при № = 3;разница между средними через два сравнивается с НЗР при № = 4 и т. д.Если НЗР больше наблюдаемых разностей, то сравниваемые средние отличаются несущественно. Иначе различие между средними признается статистически значимым.В нашем случае:

1. 
   11,8 – 9,8 = 2 <3,876
   
2. 
   20,6 – 9,8 = 10,8 > 4,070
   
3. 
   29,8 – 9,8 = 20 > 4,173
   
4. 
   20,6 – 11,8 = 8,8 >3,876
   
5. 
   29,8 – 11,8 = 18 > 4,070
   
6. 
   29,8 – 20,6 = 9,2 > 3,876
   

В результате сравнения обнаружено, что первое и второе среднее отличаются несущественно, а разница между остальными средними статистически значима.Результаты сравнения можно наглядно представить на одномерной шкале.D B A C9 ,8 11,8 20,6 29,8Здесь средние, отличающиеся несущественно, имеют одну общую черту.Таким образом по результатам испытаний защитных красок можно сказать следующее:краски D и B обладают наилучшими защитными свойствами и примерно одинаковы. Наихудшими защитными свойствами обладает краска C. Краска А является промежуточной по защитным свойствам.

7.2.4. Варианты заданийВарианты заданий взять из Приложения 2 «Районы и города Чувашии в цифрах».Во всех заданиях методом дисперсионного анализа выяснить имеется ли различие в средних значениях показателя по уровням изучаемого фактора и дать экономическую интерпретацию полученных результатов. 7.3. Регрессионный анализ7.3.1.Общие сведенияВ тех случаях, когда математическая модель процесса неизвестна, для аппроксимации имеющихся данных используются полиномиальные зависимости вида [3]: , (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. Последовательно рассчитать столбцы значений X², X³ и X⁴.

	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 вызывается функция