Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 08.11.2023
Просмотров: 760
Скачиваний: 3
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
Вариант 19
| Официальные сведения об основных показателях экономического развития СССР за период 1960-1986 гг. | |||||
| Наименования | 1960 | 1970 | 1980 | 1985 | 1986 |
| Валовый общественный продукт | 33 | 64 | 106 | 127 | 132 |
| Национальный доход | 36 | 71 | 115 | 137 | 143 |
| Продукция промышленности | 63 | 142 | 253 | 303 | 318 |
| Производство средств производства | 128 | 306 | 559 | 669 | 705 |
| Производство средств потребления | 24 | 49 | 82 | 99 | 103 |
| Численность населения, млн чел | 212,4 | 241,7 | 264,5 | 276,3 | 278,8 |
| Производство нефти, млн. т | 148 | 353 | 603 | 595 | 615 |
| Производство газа, млрд. куб.м | 45,3 | 198 | 435 | 643 | 686 |
7.2. Дисперсионный анализ
7.2.1. Общие сведения
Применяется для обработки результатов экспериментов с качественными факторами. Основная идея заключается в следующем:
– вычисляется общая дисперсия данных эксперимента;
– вычисляются компоненты дисперсии (для каждого фактора);
– статистическая значимость фактора определяется по критерию Фишера. Если рассчитанный критерий больше табличного, то фактор признается статически значимым (существенно влияющим на результаты эксперимента), иначе фактор признается не значимым.
Обрабатываться могут результаты как однофакторных, так и многофакторных экспериментов.
Для обработки однофакторных экспериментов в Excel в надстройке «Анализ данных» имеется инструмент «Однофакторный дисперсионный анализ».
7.2.1. Пример
Пусть поставлен эксперимент по сравнению качества четырех защитных красок. Оценкой качества служит площадь поверхности окрашенных образцов, пораженных ржавчиной после проведения испытаний (в процентах). По результатам эксперимента необходимо сделать выводы о качестве красок. Результаты опытов приведены в табл. 7.2.
Таблица 7.2
| Номер опыта | Тип краски | |||
| A | B | C | D | |
| 1 | 21 | 12 | 34 | 13 |
| 2 | 25 | 15 | 30 | 9 |
| 3 | 19 | 11 | 25 | 6 |
| 4 | 16 | 12 | 29 | 11 |
| 5 | 22 | 9 | 31 | 10 |
Для выполнения расчетов:
1. Перенести данные в Excel.;
2. Вызвать инструмент для расчета: Сервис – Анализ данных – Однофакторный дисперсионный анализ;
3. В появившемся диалоговом окне задать исходные данные для анализа:
Входной интервал – необходимо отметить таблицу, в которой размещены исходные числовые данные (левая верхняя и правая нижняя ячейки).
Группирование – необходимо указать в строках или в столбцах находятся данные (для рассматриваемого примера указать – столбцы).
Альфа – требуемый уровень значимости (обычно – 0,05).
Выходной интервал – Если переключатель установить в данное положение, то результаты расчетов будут выведены на этот же лист. При этом необходимо указать адрес ячейки, начиная с которой будут выведены данные.
Новый рабочий лист – выбирается в том случае, если вы хотите поместить результаты работы на другой лист.
Новая рабочая книга – выбирается, если вы хотите поместить результаты в новую книгу.
После указания необходимых данных и нажатия ОК появятся результаты дисперсионного анализа.
4. Для рассматриваемого примера появятся следующие результаты:
| Группы | Счет | Сумма | Среднее | Дисперсия | | |
| A | 5 | 103 | 20,6 | 11,3 | | |
| B | 5 | 59 | 11,8 | 4,7 | | |
| C | 5 | 149 | 29,8 | 10,7 | | |
| D | 5 | 49 | 9,8 | 6,7 | | |
| | | | | | | |
| | | | | | | |
| Дисперсионный анализ | | | | | ||
| Источник вариации | SS | df | MS | F | P-значение | F критическое |
| Между группами | 1258,4 | 3 | 419,4667 | 50,23553 | 2,3E-08 | 3,23887152 |
| Внутри групп | 133,6 | 16 | 8,35 | | | |
| | | | | | | |
| Итого | 1392 | 19 | | | | |
где:
SS – сумма квадратов;
df – число степеней свободы;
MS – дисперсия;
F – расчетное значение критерия Фишера;
P-значение – расчетное значение минимальной значимости;
F-критическое – критическое (табличное) значение критерия Фишера.
Полученное значение F сравнивается с критическим. Если вычисленное значение F больше критического, то делается вывод о статистически значимом влиянии фактора на изучаемый объект. В данном случае 50,24 > 3,24. Поэтому можно сделать вывод, что разные типы краски существенно отличаются друг от друга по качеству.
7.2.3. Методы, применяемые после дисперсионного анализа
Дисперсионный анализ показывает, существует ли статистически существенное влияние изучаемого фактора на свойства объекта. Экспериментатора же помимо этого интересует также вопрос – а каково конкретное влияние фактора и как меняются свойства объекта при переходе от одного уровня фактора к другому? Другими словами – экспериментатору важно выяснить – существует ли статистически существенное различие в средних значениях по уровням фактора. Заметим, что в случае фактора с двумя уровнями этот вопрос не стоит. В самом деле, если дисперсионный анализ показал, что имеется статистически существенное влияние фактора, то автоматически существенно различаются и средние по этим двум уровням. А как быть, если число уровней фактора больше двух? Например, в рассмотренном выше примере, экспериментатора может заинтересовать вопрос: а имеется ли разница между красками B и D? Для них средние значения вроде бы близки (11,8 и 9,8 соответственно).
Для этих целей наиболее часто используется ранговый критерий Дункана
Общую схему применения этого критерия рассмотрим на вышеприведенном примере. Она состоит из следующих этапов.
-
Упорядочить k средних по возрастанию.
В нашем примере k=4 и упорядоченные средние представляются рядом:
| Средние | 9,8 | 11,8 | 20,6 | 29,8 |
| Тип краски | D | B | A | C |
2. Из таблицы дисперсионного анализа берется внутригрупповая дисперсия с соответствующим числом степеней свободы.
В нашем случае: s2e = 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 | |