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

Для решения вопроса о статистической значимости компонент уравнения производится сравнение вычисленных значений критерия Фишера с критическими. Если вычисленное значение больше критического, компонента признается статистически значимой при выбранном уровне надежности. В противном случае компонента признается статистически не значимой.В данном примере статистически существенными является компоненты первой и второй степени. Компоненты более высоких степеней не существенны. Поэтому для адекватного описания наших данных достаточно использовать уравнение второй степени следующего вида: . (7.13)7.3.4. Использование уравнения для прогнозаПусть нам необходимо найти прогнозируемое значение Y при X=3,1.Для получения прогнозного значения необходимо подставить нужное значение X в полученное уравнение и вычислить его. В рассматриваемом случае достаточно скопировать формулу из G16 в G17:

	B	C	D	E	F	G
6		Х (время)	X2	Y (показатель)		Y2
7	1	0,1	0,01	1		2,8
8	2	0,4	0,16	5		5,375758
9	3	0,7	0,49	10		7,360606
10	4	1	1	11		8,754545
11	5	1,3	1,69	10		9,557576
12	6	1,6	2,56	8		9,769697
13	7	1,9	3,61	7		9,390909
14	8	2,2	4,84	8		8,421212
15	9	2,5	6,25	7		6,860606
16	10	2,8	7,84	6		4,709091
17		3,1	9,61			1,966667

Таким образом, прогнозируемое значение Y для X=3,1 равно 1,97.Для оценки точности прогноза сначала рассчитывается стандартное отклонение в точке прогноза: , (7.14)где n – число измерений;x_p– значение x, для которого осуществляется прогноз; – среднее значение x, для имеющихся данных; – стандартное отклонение остаточной суммы квадратов, рассчитываемое по формуле: . (7.15)Суммирование в формуле производится по имеющимся значениям y.Далее рассчитывается доверительный интервал прогноза – dy: , (7.16)где t_табл – табличное значение критерия Стьюдента при выбранном уровне значимости и числе степеней свободы, равном n-1.Результирующий прогноз имеет вид: . (7.17)Непосредственно для расчетов:– в D19 вычислить среднее X =СРЗНАЧ(C7:C16);– в D20 вычислить общую дисперсию для Х =ДИСПА(C7:C16)*9;– в D21 вычислить общую дисперсию для реальных данных =ДИСПА(E7:E16)*9;– в D22 вычислить остаточную дисперсию для уравнения прогноза =СУММКВРАЗН(E7:E16;G7:G16);– в D23 вычислить стандартное отклонение для Y=(D22/8)^(1/2);– в D24 вычислить точность прогноза=D23*(1+1/10+(3,1-D19)^2/D20)^(1/2);– в D25 вычислить критерий Стьюдента=СТЬЮДРАСПОБР(0,05;9),где 0,05 – уровень значимости предсказания, что соответствует надежности 1 – 0,05 = 0,95 (95%);9 – количество реальных данных минус 1.– в D26 вычислить доверительный интервал=D25*D24.В результате должно получиться следующее:

	E	D
19	Среднее X	1,45
20	Дисперсия X	7,425
21	Дисперсия Y	76,1
22	Остаточная дисперсия	26,29697
23	Стандартное отклонение	1,813042
24	Точность прогноза	2,195703
25	Критерий Стьюдента	2,262157
26	Доверительный интервал	4,967025

Таким образом, прогнозируемое значение равно: 1,97± 4,97.7.4. Кластерный анализ7.4.1. Общие положения.Применяется в задачах классификации. Имеется два варианта задач классификации:а) Имеется набор групп объектов (классов или кластеров) и известны параметры групп. Необходимо вновь появившийся объект отнести к одной из групп.б) Не известно ни количество групп, ни их характеристики. Имеется набор объектов, которые необходимо расклассифицировать по группам. Т.е. классификация производится с нуля.Основные идеи классификации можно пояснить на следующих примерах.7.4.2. ПримерыПример 1.Пусть в качестве объектов классификации выступают регионы РФ. И пусть каждый из них характеризуется одним параметром – численностью населения. Если этот показатель нанести на числовую ось, то получится примерно следующее:Р ис.7.2. Распределение регионов РФ по численности населения.Из рис.7.2 следует, что среди регионов Росси имеются объекты с низкой численностью населения (Чукотка, Магаданская и Еврейская области). Они группируются в отдельную группу. Во вторую и самую многочисленную группу группируются регионы типа республик поволжского региона. Эту группу можно назвать регионами со средней численностью. В третью группу входят области Черноземья (Краснодарский и Ставропольский край) и промышленные регионы (Свердловская область, Нижегородская область и т.д.). Эту группу можно назвать регионами с высокой численностью населения.Особняком стоят регионы со сверхвысокой численностью населения Ленинградская и Московская области.Приведенная классификация основана на чисто визуальном восприятии данных рисунка. Т.е. мы по плотности расположения данных на числовой оси уверенно провели классификацию и выделили четыре группы или кластера. При этом интуитивно возникают такие понятия как плотность кластеров. Регионы со средней численностью представляют собой кластер с высокой плотностью объектов. Регионы с высокой численностью представляют собой более разреженный кластер. В отношении Московской и Ленинградской области трудно сказать, что это такое – или это один очень сильно разреженный кластер, состоящий из двух объектов, или это два разных кластера, состоящих из одного объекта.

Пример 2.Пусть в качестве объектов классификации выступают те же регионы РФ. И пусть каждый из них характеризуется двумя параметрами – объемом сельскохозяйственного производства и объемом промышленного производства. Если эти объекты нанести на график, у которого в качестве осей служат указанные параметры, то получится примерно следующее (рис. 7.3).Рис. 7.3. Графическое представление регионов РФ в зависимости от объемов промышленного и с/хозяйственного производстваЗдесь также видны ясно различимые кластеры. Их можно охарактеризовать следующим образом. Регионы с высоким уровнем сельскохозяйственного производства – это в основном регионы Черноземья.Регионы с высоким уровнем промышленного производства – это в основном Уральские регионы и некоторые области Сибири Регионы со средним уровнем развития как промышленного, так и сельскохозяйственного производства – сюда относятся большинство регионов России.Регионы соотносительно высоким уровнем как промышленного, так и сельскохозяйственного производства – Московская и Ленинградская области.Особняком стоит кластер с низким уровнем как промышленного, так и сельскохозяйственного производства – состоит из одного объекта (Чукотка).И снова мы уверенно провели классификацию на основе визуального восприятия.Аналогично можно произвести классификацию объектов, характеризующихся тремя показателями. Для этого правда придется строить трехмерные графики Однако здесь следует иметь в виду, что форма кластеров может быть очень далека от шарообразной. Они могут иметь весьма причудливую форму, взаимно переплетаться в пространстве, проникать друг в друга и т.д. Пример показан на рисунке 7.4.Рис.7.4. Пример объемного кластераК сожалению возможности визуальной классификации исчерпываются когда размерность кластера больше трех. Кроме того, приведенные примеры носят модельный характер. При работе же с реальными данными (даже если их можно представить графически) чаще всего невозможно четко разделить объекты на отдельные группы. 7.4.3. Формализация процесса кластеризацииДля того чтобы произвести кластеризацию, основываясь не на визуальных впечатлениях, а более объективно, необходимо формализовать задачу.

Если проанализировать все то, что было описано выше, то можно сформулировать следующие признаки, на которых основана визуальная кластеризация:- к одному кластеру относятся те объекты, которые расположены достаточно близко друг к другу, - или объекты в одном кластере расположены примерно на одинаковых расстояниях друг от друга,- при этом один кластер отделен от другого расстоянием значительно большим, чем типичное расстояние между объектами внутри кластера.Приведенные признаки, по-прежнему, еще слишком общие. Обратите внимание на слова «типичное расстояние», «достаточно близки» и т.д. Но уже становится ясным, какой параметр, характерный для набора объектов, должен подвергнуться формализации – это расстояния между объектами.Этот параметр служит основой практически для всех методов кластеризации. Одним из них является метод цепочечной кластеризации. Его достоинством является то, что к полученным с его помощью результатам легко применить статистические методы проверки гипотез.Суть метода заключается в следующем.Пусть имеется N объектов, которые необходимо подвергнуть разделению на группы.

1. 
   Для всех возможных пар объектов вычисляются расстояния между всеми возможными парами объектов и среди них такая пара, расстояние между которыми минимально. Эта пара может служить ядром будущего кластера (или стать отдельным кластером). Кроме того, данная пара помечается как уже сгруппированная.
   
2. 
   Среди оставшихся объектов находится такой, расстояние, от которого до любого из уже сгруппированных объектов минимально. Этот объект также помечается как сгруппированный.
   
3. 
   Операция 2 выполняется до тех пор, пока все объекты не станут сгруппированными.
   
4. 
   В результате выполнения п.п. 2, 3 получается последовательность (цепочка) расстояний с указанием объектов, для которых эти расстояния вычислены.
   
5. 
   К полученной последовательности применяется технология выявления промахов в ряду наблюдений (см. работу «Случайные величины»).
   
6. 
   Если в ряду расстояний обнаружено расстояние, резко отличающееся от других (что подтверждается статистической проверкой), то это расстояние признается как граница между кластерами и соответствующие объекты до этого значения относятся к одному кластеру, а оставшиеся к другому (другим).