Кластерный анализ данных средствами Microsoft Excel

Очевидно, что какая бы совокупность данных не рассматривалась в исследовании, она всегда подразделяется на группы. Группировка – это распределение единиц по группам в соответствии со следующим принципом: различия между единицами, отнесенными к одной группе, должны быть меньше, чем между единицами, отнесенными к разным группам.

Группировка лежит в основе всей дальнейшей работы с собранной информацией. На основе группировки рассчитываются сводные показатели по группам, появляется возможность их сравнения, анализа причин различия между группами, изучения взаимосвязей между признаками.

Группировка называется простой (монотетической), если для её построения используется один группировочный признак. Если группировка выполняется по нескольким признакам, она называется сложной (политетической). Этот вид группировки достаточно ненадежен ввиду того, что группы становятся малочисленными, данные – труднообозримыми. Альтернативой сложной группировке является многомерная группировка, проводимая на основе многомерной классификации. Простейшим видом многомерной классификации является группировка на основе многомерных средних. Более обоснованным методом многомерной классификации является кластерный анализ.

Каждая единица совокупности данных в кластерном анализе рассматривается как точка в заданном признаковом пространстве. Значение каждого из признаков у данной единицы служит её координатой в этом пространстве по аналогии с координатами точки в нашем реальном трехмерном пространстве. Таким образом, признаковое пространство – это область варьирования всех признаков совокупности изучаемых явлений. Ели мы уподобим это пространство обычному пространству, имеющему евклидову метрику, то тем самым получим возможность измерить «расстояния» между точками признакового пространства. Эти расстояния называют евклидовыми. Их вычисляют по тем же правилам, что и в обычной евклидовой геометрии. На плоскости, т.е. в двумерном пространстве, расстояние между точками A и B равно корню квадратному из суммы квадратов разностей координат этих точек по оси абсцисс и по оси ординат:

Необходимо сначала выразить различия между единицами совокупности по каждому признаку в каком-то относительно безразмерном показателе для исключения «эффекта масштаба». В качестве такого показателя часто применяют «нормированную разность», т.е. величину $d_{pq}=\frac{x_{jp}-x_{jq}}{\sigma_{xj}}$, где $x_{jp} − x_{jq}$ - абсолютная разность значений j-го признака у единиц совокупности с номерами p и q; $\sigma_{xj}$ - среднее квадратическое отклонение признака $x_j$. Знаки нормированных разностей не имеют значения, так как «расстояние» в признаковом пространстве – скалярная, а не векторная величина.

Пример. Рассмотрим использование многомерных средних для анализа данных по сельхозпредприятиям (см. таблицу 1). По каждому предприятию приведены четыре признака:

среднемесячная оплата труда работника за определяемый вид работ, руб., x₁;
валовой доход на 1 га сельхозугодий, тыс. руб./га, x₂;
среднегодовая стоимость основных производственных фондов на 1 га сельхозугодий, тыс. руб./га, x₃;
отношение дебиторской задолженности к кредиторской задолженности, %, x₄.

Предприятие	x₁	x₂	x₃	x₄
«Ручьи»	597	390	20,6	72
«Бугры»	353	96	12,1	30
«Пригородное»	403	84	20,6	26
«Авлога»	231	71	15,1
«Владимирское»	330	114	14,8	159
«Выгорское»	540	235	24,0	26
«Принеманское»	372	461	33,2	85
«Щеглово»	393	113	15,0	62

Технология выполнения процедуры кластеризации для рассматриваемого примера с использованием Microsoft Excel

Проведем первоначальный анализ исходных данных, выполнив расчет средних величин признаков, среднеквадратических отклонений (СКО) для каждого признака, а также процентных отношения значений признака к средней величине и значений многомерной средней для каждого признака. Для этого создадим на одном из листов Рабочей книги таблицу, в которой заданы исходные значения признаков, а расчетная часть выполнена с использованием функций СРЗНАЧ и СТАНДОТКЛ.
В результате выполненных расчетов должны быть получены сл. значения:

Первоначальный анализ
Предприятия	Значения признаков				В % к средней				Многомерная средняя
Предприятия	x₁	x₂	x₃	x₄	x₁	x₂	x₃	x₄	Многомерная средняя
«Ручьи»	597	390	20,6	72	148	199	106	108	140
«Бугры»	353	96	12,1	30	88	49	62	45	61
«Пригородное»	403	84	20,6	26	100	43	106	39	72
«Авлога»	231	71	15,1	74	57	36	78	111	71
«Владимирское»	330	114	14,8	159	82	58	76	238	114
«Выгорское»	540	235	24	26	134	120	124	39	104
«Принеманское»	372	461	33,2	85	92	236	171	127	157
«Щеглово»	393	113	15	62	98	58	77	93	81
Средние величины	402,4	195,5	19,4	66,8	100	100	100	100	100
СКО	108,9	141,9	6,4	41,3	-	-	-	-

Рассчитаем нормированные разности между признаками по предприятиям. Основная расчетная формула имеет вид: =ABS(($B3-D$2)/‘Расчет средних’!$B$13), где в ячейках B3:B10 и C2:J2 размещены значения соответствующего признака, а в ячейках B13:E13 на листе «Расчет средних»– значения СКО для рассматриваемых признаков. В результате проведенных расчетов должны быть получены сл. таблицы:

Матрица нормированных разностей между предприятиями по признаку x₁:
D₁	x ₁	«Ручьи»	«Бугры»	«Пригородное»	«Авлога»	«Владимирское»	«Выгорское»	«Принеманское»	«Щеглово»
x ₁		597	353	403	231	330	540	372	393
«Ручьи»	597	0,000	2,241	1,781	3,361	2,452	0,523	2,066	1,873
«Бугры»	353	2,241	0,000	0,459	1,120	0,211	1,717	0,174	0,367
«Пригородное»	403	1,781	0,459	0,000	1,579	0,670	1,258	0,285	0,092
«Авлога»	231	3,361	1,120	1,579	0,000	0,909	2,837	1,295	1,488
«Владимирское»	330	2,452	0,211	0,670	0,909	0,000	1,928	0,386	0,579
«Выгорское»	540	0,523	1,717	1,258	2,837	1,928	0,000	1,543	1,350
«Принеманское»	372	2,066	0,174	0,285	1,295	0,386	1,543	0,000	0,193
«Щеглово»	393	1,873	0,367	0,092	1,488	0,579	1,350	0,193	0,000
Средняя нормированная разность			1,241

Матрица нормированных разностей между предприятиями по признаку x₂:
D₂	x₂	«Ручьи»	«Бугры»	«Пригородное»	«Авлога»	«Владимирское»	«Выгорское»	«Принеманское»	«Щеглово»
x₂		390	96	84	71	114	235	461	113
«Ручьи»	390	0,000	2,071	2,156	2,248	1,945	1,092	0,500	1,952
«Бугры»	96	2,071	0,000	0,085	0,176	0,127	0,979	2,572	0,120
«Пригородное»	84	2,156	0,085	0,000	0,092	0,211	1,064	2,656	0,204
«Авлога»	71	2,248	0,176	0,092	0,000	0,303	1,155	2,748	0,296
«Владимирское»	114	1,945	0,127	0,211	0,303	0,000	0,853	2,445	0,007
«Выгорское»	235	1,092	0,979	1,064	1,155	0,853	0,000	1,592	0,860
«Принеманское»	461	0,500	2,572	2,656	2,748	2,445	1,592	0,000	2,452
«Щеглово»	113	1,952	0,120	0,204	0,296	0,007	0,860	2,452	0,000

Средняя нормированная разность 1,177

Матрица нормированных разностей между предприятиями по признаку x₃:
D₃	x₃	«Ручьи»	«Бугры»	«Пригородное»	«Авлога»	«Владимирское»	«Выгорское»	«Принеманское»	«Щеглово»
x₃		20,6	12,1	20,6	15,1	14,8	24	33,2	15

«Ручьи»	20,6	0,000	1,330	0,000	0,861	0,908	0,532	1,971	0,876
«Бугры»	12,1	1,330	0,000	1,330	0,469	0,422	1,862	3,301	0,454
«Пригородное»	20,6	0,000	1,330	0,000	0,861	0,908	0,532	1,971	0,876
«Авлога»	15,1	0,861	0,469	0,861	0,000	0,047	1,393	2,832	0,016
«Владимирское»	14,8	0,908	0,422	0,908	0,047	0,000	1,439	2,879	0,031
«Выгорское»	24	0,532	1,862	0,532	1,393	1,439	0,000	1,439	1,408
«Принеманское»	33,2	1,971	3,301	1,971	2,832	2,879	1,439	0,000	2,848
«Щеглово»	15	0,876	0,454	0,876	0,016	0,031	1,408	2,848	0,000

Средняя нормированная разность 1,207

Матрица нормированных разностей между предприятиями по признаку x₄:
D₄	x₄	«Ручьи»	«Бугры»	«Пригородное»	«Авлога»	«Владимирское»	«Выгорское»	«Принеманское»	«Щеглово»
x₄		72	30	26	74	159	26	85	62
«Ручьи»	72	0,000	1,018	1,115	0,048	2,109	1,115	0,315	0,242
«Бугры»	30	1,018	0,000	0,097	1,066	3,127	0,097	1,333	0,776
«Пригородное»	26	1,115	0,097	0,000	1,163	3,224	0,000	1,430	0,873
«Авлога»	74	0,048	1,066	1,163	0,000	2,060	1,163	0,267	0,291
«Владимирское»	159	2,109	3,127	3,224	2,060	0,000	3,224	1,794	2,351
«Выгорское»	26	1,115	0,097	0,000	1,163	3,224	0,000	1,430	0,873
«Принеманское»	85	0,315	1,333	1,430	0,267	1,794	1,430	0,000	0,557
«Щеглово»	62	0,242	0,776	0,873	0,291	2,351	0,873	0,557	0,000

Средняя нормированная разность 1,184

В результате проведенных вычислений получим матрицу нормализованных (нормированных) евклидовых расстояний между предприятиями:

Расстояние	«Ручьи»	«Бугры»	«Пригородное»	«Авлога»	«Владимирское»	«Выгорское»	«Принеманское»	«Щеглово»
«Ручьи»	0,000
«Бугры»	3,481	0,000
«Пригородное»	3,011	1,413	0,000
«Авлога»	4,134	1,626	2,144	0,000
«Владимирское»	3,881	3,165	3,422	2,273	0,000
«Выгорское»	1,730	2,717	1,731	3,561	4,112	0,000

«Принеманское»	2,916	4,395	3,615	4,161	4,199	3,005	0,000
«Щеглово»	2,854	0,978	1,257	1,544	2,421	2,303	3,804	0,000

В результате проведенных вычислений получим матрицу нормализованных (нормированных) евклидовых расстояний между предприятиями:

Расстояние	«Ручьи»	«Бугры»	«Пригородное»	«Авлога»	«Владимирское»	«Выгорское»	«Принеманское»	«Щеглово»
«Ручьи»	0,000
«Бугры»	3,481	0,000
«Пригородное»	3,011	1,413	0,000
«Авлога»	4,134	1,626	2,144	0,000
«Владимирское»	3,881	3,165	3,422	2,273	0,000
«Выгорское»	1,730	2,717	1,731	3,561	4,112	0,000
«Принеманское»	2,916	4,395	3,615	4,161	4,199	3,005	0,000
«Щеглово»	2,854	0,978	1,257	1,544	2,421	2,303	3,804	0,000

Для получения этой таблицы воспользуемся формулой =(‘Признак 1’!C4^2+‘Признак 2’!C4^2+‘Признак 3’!C4^2+‘Признак 4’!C4²⁾0,5. Эта формула заносится в ячейку B3 на листе «Евклидовы расстояния» нашей рабочей книги.

Матрица евклидовых расстояний D (между всеми точками совокупности) служит основой агломеративно-иерархического метода классификации, который заключается в последовательном объединении группируемых объектов – сначала самых близких, а затем все более удаленных друг от друга. Процедура классификации состоит из последовательных шагов, на каждом из которых проводится объединение двух ближайших групп объектов (единиц объектов и кластеров), у которых евклидово расстояние меньше 2. На нулевом шаге каждый объект рассматривается как отдельный кластер.

На первом шаге в кластер 1 объединяются предприятия с наименьшим евклидовым расстоянием. В нашем случае - это предприятия «Бугры» и «Щеглово». Для определения наименьших расстояний можно воспользоваться функцией МИН. Выполним расчет средних по всем признакам для этого кластера евклидовых расстояний от кластера до других предприятий. В результате выполненных расчетов должна быть получена сл. таблица:

	x₁	x₂	x₃	x₄	Расстояние
Кластер 1	373	104,5	13,55	46
«Ручьи»	2,057	2,011	1,103	0,630	3,145
«Пригородное»	0,275	0,144	1,103	0,485	1,244
«Авлога»	1,304	0,236	0,243	0,679	1,508
«Владимирское»	0,395	0,067	0,196	2,739	2,775
«Выгорское»	1,533	0,919	1,635	0,485	2,471
«Принеманское»	0,009	2,512	3,075	0,945	4,081

Новые евклидовы расстояния (с учетом образования кластера 1) рассчитываются в сл. таблице, из которой следует, что в кластер 1 должно быть добавлено предприятие «Пригородное»

Расстояние	Кластер 1(“Б”+“Щ”)	«Ручьи»	«Пригородное»	«Авлога»	«Владимирское»	«Выгорское»	«Принеманское»
Кластер 1(“Б”+“Щ”)	0,000
«Ручьи»	3,145	0,000
«Пригородное»	1,244	3,011	0,000
«Авлога»	1,508	4,134	2,144	0,000
«Владимирское»	2,775	3,881	3,422	2,273	0,000
«Выгорское»	2,471	1,730	1,731	3,561	4,112	0,000
«Принеманское»	4,081	2,916	3,615	4,161	4,199	3,005	0,000

Далее по аналогичной схеме рассчитаем нормированные разности и евклидовы расстояния между предприятиями и обновленным кластером 1

	x₁	x₂	x₃	x₄	Расстояние
Кластер 1	383	97,67	15,90	39,33
«Ручьи»	1,965	2,060	0,735	0,792	3,045
«Авлога»	1,396	0,188	0,125	0,840	1,645
«Владимирское»	0,487	0,115	0,172	2,900	2,948
«Выгорское»	1,442	0,968	1,267	0,323	2,174
«Принеманское»	0,101	2,560	2,707	1,107	3,888

Матрица евклидовых расстояний после образования кластера 1 («Бугры» + Щеглово»+«Пригородное»)

Расстояние	Кластер 1(“Б”+“Щ”+“П”)	«Ручьи»	«Авлога»	«Владимирское»	Выгорское»	«Принеманское»
Кластер 1(“Б”+“Щ”+“П”)	0,000
«Ручьи»	3,045	0,000
«Авлога»	1,645	4,134	0,000
«Владимирское»	2,948	3,881	2,273	0,000
«Выгорское»	2,174	1,730	3,561	4,112	0,000
«Принеманское»	3,888	2,916	4,161	4,199	3,005	0,000

Объединяются кластер 1 и «Авлога». Получаем ни расстояния для кластера 1 (после добавления «Авлоги»)

Расстояние	Кластер 1(“Б”+“Щ”+“П”+“А”)	«Ручьи»	«Владимирское»	«Выгорское»	«Принеманское»
Кластер 1(“Б”+“Щ”+“П”+“А”)	0,000
«Ручьи»	3,274	0,000
«Владимирское»	2,702	3,881	0,000
«Выгорское»	2,491	1,730	3,561	0,000
«Принеманское»	3,893	2,916	4,161	4,199	0,000

Объединяются в кластер 2 «Ручьи» и «Выгорское», так как только для этих предприятий евклидово расстояние меньше 2.

Вычисляем нормированные разности и расстояния для кластеров 1 и 2:

	x1	x2	x3	x4	Расстояние
Кластер 2	568,5	49	312,5	22,3
Кластер 1	2,052	1,561	1,033	0,024	2,777
«Владимирское»	2,190	1,399	1,173	2,666	3,904
«Принеманское»	1,804	1,046	1,705	0,873	2,832

Матрица евклидовых расстояний после образования кластеров 1 и 2 имеет вид:

Расстояние	Кластер 1(“Б”+“Щ”+“П”+“А”)	Кластер 2(“Р”+“В”)	Кластер 3(«Владимирское»)	Кластер 4(«Принеманское»)
Кластер 1(“Б”+“Щ”+“П”+“А”)	0,000
Кластер 2(“Р”+“В”)	2,777	0,000
Кластер 3(«Владимирское»)	2,702	3,904	0,000
Кластер 4(«Принеманское»)	3,893	2,832	4,199	0,000

Процедура классификации на этом шаге заканчивается, так как нет двух единиц или кластеров, расстояние между которыми меньше 2.

Обобщая рассмотренную процедуру кластерного анализа, запишем последовательность выполнения действий как алгоритм:

вычисление средних величин для каждого из классификационных признаков в целом по совокупности;
вычисление средних квадратических отклонений для каждого из признаков по совокупности ;
вычисление матрицы нормированных разностей по каждому из группировочных признаков
вычисление евклидовых расстояний между каждой парой сочетаний единиц совокупности ;
выбор наименьшего из евклидовых расстояний;
объединение единиц совокупности с наименьшим евклидовым расстоянием между ними в один кластер;
вычисление средних значений всех признаков для единиц, объединенных в кластер;
вычисление новых нормированных расстояний между объединенным кластером и остальными единицами;
вычисление новых евклидовых расстояний между объединенным кластером и остальными единицами (или кластерами);
выбор наименьшего из евклидовых расстояний;
повторение операций п.п.6)-10) до тех пор, пока все евклидовы расстояний не превысят некоторую заданную критическую величину (обычно – 2).