Как построить матрицу расстояний в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Как сделать кластерный анализ в Excel: сфера применения и инструкция

Кластерный анализ объединяет кластеры и переменные (объекты), похожие друг на друга. То есть классифицирует объекты. Часто при решении экономических задач, имеющих достаточно большое число данных, нужна многомерность описания. Один из простых методов многомерного анализа – кластерный анализ.

Кластерный анализ является количественным инструментом исследования социально-экономических процессов, для описания которых необходимо много характеристик. Он позволяет разбить выборку на несколько групп по исследуемому признаку, проанализировать группы (как группируются переменные), группировку объектов (как группируются объекты). С помощью метода решаются задачи сегментирования рынка, анализируются сельские хозяйства для сравнения производительности, например, прогнозируется конъюнктура рынка отдельных продуктов и т.д.

Многомерный кластерный анализ

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

Техника кластеризации применяется в самых разнообразных областях. Главное задача – разбить многомерный ряд исследуемых значений (объектов, переменных, признаков) на однородные группы, кластеры. То есть данные классифицируются и структурируются.

Вопрос, который задает исследователь при использовании кластерного анализа, – как организовать многомерную выборку в наглядные структуры.

Примеры использования кластерного анализа:

В биологии – для определения видов животных на Земле.
В медицине – для классификации заболеваний по группам симптомов и способам терапии.
В психологии – для определения типов поведения личности в определенных ситуациях.
В экономическом анализе – при изучении и прогнозировании экономической депрессии, исследовании конъюнктуры.
В разнообразных маркетинговых исследованиях.

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

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

Дельта-кластерный анализ имеет и свои недостатки:

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

Как сделать кластерный анализ в Excel

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

XY.

В качестве расстояния между объектами возьмем евклидовое расстояние. Формула расчета:

КОРЕНЬ.

Рассчитанные данные размещаем в матрице расстояний.

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

Группа.

Из новой матрицы видно, что можно объединить в один кластер объекты [4, 5] и 6 (как наиболее близкие друг к другу по значениям). Оставляем наименьшее значение и формируем новую матрицу:

Матрица.

Объекты 1 и 2 можно объединить в один кластер (как наиболее близкие из имеющихся). Выбираем наименьшее значение и формируем новую матрицу расстояний. В результате получаем три кластера:

Кластеры.

Самые близкие объекты – 1, 2 и 3. Объединим их.

Пример.

Мы провели кластерный анализ по методу «ближайшего соседа». В результате получено два кластера, расстояние между которыми – 7,07.

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

Excel Formula Examples
Создать таблицу
Форматирование
Функции Excel
Формулы и диапазоны
Фильтр и сортировка
Диаграммы и графики
Сводные таблицы
Печать документов
Базы данных и XML
Возможности Excel
Настройки параметры
Уроки Excel
Макросы VBA
Скачать примеры

Применение кластерного анализа в Microsoft Excel

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

Использование кластерного анализа

С помощью кластерного анализа можно проводить выборку по признаку, который исследуется. Его основная задача – разбиение многомерного массива на однородные группы. В качестве критерия группировки применяется парный коэффициент корреляции или эвклидово расстояние между объектами по заданному параметру. Наиболее близкие друг к другу значения группируются вместе.

Хотя чаще всего данный вид анализа применяют в экономике, его также можно использовать в биологии (для классификации животных), психологии, медицине и во многих других сферах деятельности человека. Кластерный анализ можно применять, используя для этих целей стандартный набор инструментов Эксель.

Пример использования

Имеем пять объектов, которые характеризуются по двум изучаемым параметрам – x и y.

Применяем к данным значениям формулу эвклидового расстояния, которое вычисляется по шаблону: =КОРЕНЬ((x2-x1)^2+(y2-y1)^2)
Данное значение вычисляем между каждым из пяти объектов. Результаты расчета помещаем в матрице расстояний.
Смотрим, между какими значениями дистанция меньше всего. В нашем примере — это объекты 1 и 2. Расстояние между ними составляет 4,123106, что меньше, чем между любыми другими элементами данной совокупности.
Объединяем эти данные в группу и формируем новую матрицу, в которой значения 1,2 выступают отдельным элементом. При составлении матрицы оставляем наименьшие значения из предыдущей таблицы для объединенного элемента. Опять смотрим, между какими элементами расстояние минимально. На этот раз – это 4 и 5, а также объект 5 и группа объектов 1,2. Дистанция составляет 6,708204.
Добавляем указанные элементы в общий кластер. Формируем новую матрицу по тому же принципу, что и в предыдущий раз. То есть, ищем самые меньшие значения. Таким образом мы видим, что нашу совокупность данных можно разбить на два кластера. В первом кластере находятся наиболее близкие между собой элементы – 1,2,4,5. Во втором кластере в нашем случае представлен только один элемент — 3. Он находится сравнительно в отдалении от других объектов. Расстояние между кластерами составляет 9,84.

Итоговое значение в Microsoft Excel

На этом завершается процедура разбиения совокупности на группы.

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

Построить матрицу пространственных весов (Пространственная статистика)

Создает файл с матрицей пространственных весов ( .swm ) для отображения пространственных отношений между объектами в наборе данных.

Иллюстрация

Использование

Выходные данные этого инструмента – файл матрицы пространственных весов ( .swm ). Для таких инструментов, как Анализ горячих точек, для которых требуется указывать параметр Определение пространственных взаимоотношений , может использоваться файл матрицы пространственных весов. Выберите опцию Получить пространственные веса из файла для параметра Определение пространственных взаимоотношений , а для параметра Файл матрицы весов укажите полный путь к файлу пространственных весов, который вы создали с помощью этого инструмента.
Данный инструмент также выводит характеристики полученного файла матрицы пространственных весов: число объектов, связность, а также минимальное, максимальное и среднее число соседних объектов. Эта информация отображается в виде сообщений, которые появляются в нижней части панели Геообработка во время выполнения этого инструмента. Чтобы получить доступ к сообщениям, поместите курсор мыши над индикатором выполнения и щелкните всплывающую кнопку или разверните раздел сообщений в панели Геообработка . Вы можете получить доступ к сообщениям для выполненного ранее инструмента через историю геообработки. В этих сводках должно быть указано, у всех ли объектов имеется хотя бы один сосед. В целом, особенно в случае больших наборов данных, каждому объекту желательно иметь, по меньшей мере, восемь соседей. Также желательно низкое значение связности объектов.
Для анализа пространства/времени выберите опцию Пространственно-временное окно для параметра Определение пространственных взаимоотношений . Пространство задается значением Порогового расстояния ; время задается значением Поле даты/времени , а также двумя значениями: Тип даты/времени (часы или дни) и Значение интервала даты/времени . Значение параметра Значение интервала даты/времени должно быть целым числом. Например, если ввести пороговое расстояние в 1000 футов, выбрать опцию Часы и задать для Значение интервала даты/времени значение 3 , то соседями будут считаться объекты, расположенные один от другого в пределах 1000 футов и 3-х часов.
Файл матрицы пространственных весов ( .swm ) позволяет создавать, хранить, повторно использовать и передавать другим концептуальное представление взаимоотношений между объектами в наборе объектов. Для повышения быстродействия системы файл создается в двоичном формате. Отношения между пространственными объектами хранятся в виде разреженной матрицы, при этом в файл .swm записываются только ненулевые отношения. В общем, инструмент показывает высокую производительность даже тогда, когда файл .swm содержит более 15 миллионов ненулевых отношений. Тем не менее, если вдруг при работе с файлом .swm возникнет ошибка оперативной памяти, следует пересмотреть метод задания отношений между объектами. Лучше всего стремиться к матрице пространственных весов, в которой у каждого объекта есть как минимум 1 сосед, у большинства — 8 соседей, и ни один объект не имеет более 1000 соседей.
Совпадающие точки не используются в вычислении значения параметра Пороговое расстояние по умолчанию.
При использовании данных с координатами, включающими z-значения, значение параметра Пороговое расстояние будет 3D-расстоянием.
При использовании данных с координатами, включающими z-значения, единственными опциями, поддерживаемыми параметром Определение пространственных взаимоотношений , являются Обратное расстояние , Фиксированное расстояние , Ближайшая окрестность K и Пространственно-временное окно .
Если значение параметра Входной класс пространственных объектов содержит z-значения, линейные единицы измерения вертикальной системы координат (VCS) должны совпадать с линейными единицами измерения горизонтальный системы координат. Если значение параметра Входной класс пространственных объектов не имеет вертикальной системы координат, предполагается, что линейные вертикальные единицы измерения совпадают с горизонтальными.
Когда значение параметра Входной класс объектов не имеет проекции (т.е. когда координаты заданы в градусах, минутах и секундах), или когда в качестве выходной системы координат используется Географическая система координат, расстояния будут рассчитываться с помощью хордовых измерений. Измерения хордовых расстояний применяются постольку, поскольку они могут быть быстро вычислены и дают хорошие оценки истинных геодезических расстояний, по крайней мере для точек, расстояние между которыми в пределах порядка тридцати градусов. Хордовые расстояния основаны на эллипосиде вращения. Если взять две любые точки на поверхности Земли, то хордовым расстоянием между ними будет длина прямой линии, проходящей через трехмерное тело Земли и соединяющей эти две точки. Хордовые расстояния выражаются в метрах.

Внимание:

Проецируйте данные, если экстент области исследования превышает 30 градусов. Хордовые расстояния не обеспечивают точных оценок геодезических расстояний, превышающих 30 градусов.

Внимание:

Если таблица содержит веса для собственного потенциала, они не будут включены в полученный файл .swm , и при анализе будет использоваться значение собственного потенциала по умолчанию. Значение собственного потенциала по умолчанию для инструмента Анализ горячих точек равно единице, но это значение может быть заменено параметром Поле собственного потенциала . Для всех других инструментов значение собственного потенциала по умолчанию равно нулю.

Внимание:

При использовании шейп-файлов, помните, что в них нельзя хранить нулевые (null) значения. Инструменты или другие процедуры, создающие шейп-файлы из прочих входных данных, могут хранить значения NULL в виде 0 или оперировать ими как нулем. В некоторых случаях нули в шейп-файлах хранятся как очень маленькие отрицательные числа. Это может привести к неожиданным результатам. Дополнительные сведения см. в разделе Рекомендации по геообработке выходных данных шейп-файла.

Параметры

Входной класс объектов

Класс пространственных объектов, для которого пространственные отношения объектов будут оценены.

Поле уникального ID

Целочисленное поле, содержащее по уникальному значению для каждого объекта во входном классе объектов. Если у вас нет поля Уникальный ID, вы можете создать его путем добавления нового целого поля в вашу таблицу классов объектов и вычислив значения полей, которые были бы равны полям FID или OBJECTID .

Выходной файл матрицы пространственных весов

Полный путь к выходному файлу матрицы пространственных весов ( .swm ).

Определение пространственных взаимоотношений

Определяет, как будут формироваться пространственные отношения между объектами.

Обратное расстояние — Влияние одного объекта на другой будет уменьшаться с увеличением расстояния.

Метод расстояний
(Дополнительный)

Определяет, как рассчитываются расстояния от одного объекта до соседнего объекта.

Евклидово — Расстояние между двумя точками будет вычислено по прямой линии (как ворона летает). Это значение по умолчанию

(Дополнительный)

Значение для расчета обратного расстояния. Типичные значения – 1 или 2.

Пороговое расстояние
(Дополнительный)

Пороговое расстояние для опций параметра Определение пространственных взаимоотношений Обратное расстояние и Фиксированное расстояние . Введите это значение, используя единицы, определенные во входящей системе координат. Задает размер пространственного окна для опции Пространственно-временное окно .

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

Число соседей
(Дополнительный)

Целое число, показывающее или минимально или точное количество соседей. Когда параметр Определение пространственных взаимоотношений установлен на Ближайшая окрестность K , каждый объект будет иметь точно указанное количество соседей. Для опций Обратное расстояние или Фиксированное расстояние каждый объект будет иметь как минимум это количество соседей (чтобы обеспечить это количество соседей, пороговое расстояние будет временно увеличено при необходимости). Когда выбрана опция Только совпадающие ребра или Совпадающие ребра и углы , каждому полигону будет назначено это минимальное количество соседей. Для полигонов с меньшим числом соседей, чем число соседей со смежными границами, дополнительное количество соседей определяется по принципу близости центроидов объектов.

Стандартизация строк
(Дополнительный)

Указывает, будут ли пространственные веса стандартизированы по строкам. Нормализация ряда рекомендуется, независимо от того, распределены ли объекты потенциально предвзято в зависимости от дизайна примера или от установленной схемы агрегации.

Включено – Пространственные веса будут нормализованы по ряду. Каждый вес делится на сумму его ряда. Это значение по умолчанию
Выключено – Нормализация ряда пространственных весов не будет применяться.

Входная таблица
(Дополнительный)

Таблица, содержащая числовые веса, связывающие объекты друг с другом во Входном классе объектов. Обязательными полями для таблицы являются значение параметра Поле уникального идентификатора , NID (идентификатор соседа) и WEIGHT .

Поле даты/времени
(Дополнительный)

Поле даты с отметкой времени для каждого объекта.

Тип интервала даты/времени
(Дополнительный)

Задает единицы измерения времени.

Секунды — Единицами измерения будут секунды.

Значение интервала даты/времени
(Дополнительный)

Целочисленное значение количества единиц измерения времени, составляющее временной диапазон.

Например, если для параметра Тип интервала даты/времени выбрано Часы , а параметр Значение интервала даты/времени задан равным 3, временное окно составит 3 часа. Объекты, попадающие в указанное временное окно и в указанное пространственное окно, будут считаться соседями.

Использовать Z-значения

Определяет, будут ли использоваться z-координаты при построении матрицы пространственных весов, если входные объекты имеют координату z.

Отмечено – z-значения будут использоваться в построении матрицы пространственных весов.
Не отмечено — Z-значения не будут использоваться. Они будут игнорироваться, а в построении матрицы пространственных весов используются только координаты x и y. Это значение по умолчанию

arcpy.stats.GenerateSpatialWeightsMatrix(Input_Feature_Class, Unique_ID_Field, Output_Spatial_Weights_Matrix_File, Conceptualization_of_Spatial_Relationships, , , , , , , , , , Use_Z_values)

Input_Feature_Class

Класс пространственных объектов, для которого пространственные отношения объектов будут оценены.

Unique_ID_Field

Output_Spatial_Weights_Matrix_File

Полный путь к выходному файлу матрицы пространственных весов ( .swm ).

Conceptualization_of_Spatial_Relationships

Определяет, как будут формироваться пространственные отношения между объектами.

INVERSE_DISTANCE — Влияние одного объекта на другой будет уменьшаться с увеличением расстояния.

Distance_Method
(Дополнительный)

Определяет, как рассчитываются расстояния от одного объекта до соседнего объекта.

EUCLIDEAN — Расстояние между двумя точками будет вычислено по прямой линии (как ворона летает). Это значение по умолчанию

(Дополнительный)

Значение для расчета обратного расстояния. Типичные значения – 1 или 2.

Threshold_Distance
(Дополнительный)

Пороговое расстояние для опций INVERSE_DISTANCE и FIXED_DISTANCE параметра Conceptualization_of_Spatial_Relationships . Введите это значение, используя единицы, определенные во входящей системе координат. Задает размер пространственного окна для опции SPACE_TIME_WINDOW .

Number_of_Neighbors
(Дополнительный)

Целое число, показывающее или минимально или точное количество соседей. Когда параметр Conceptualization_of_Spatial_Relationships задан как K_NEAREST_NEIGHBORS , каждый объект будет иметь это точно указанное количество соседей. Для опций INVERSE_DISTANCE или FIXED_DISTANCE каждый объект будет иметь по крайней мере это заданное количество соседей, (для этого, если потребуется, пороговое расстояние будет временно увеличено). Когда выбрана опция CONTIGUITY_EDGES_ONLY или CONTIGUITY_EDGES_CORNERS , каждому полигону будет назначено это минимальное количество соседей. Для полигонов с меньшим числом соседей, чем число соседей со смежными границами, дополнительное количество соседей определяется по принципу близости центроидов объектов.

Row_Standardization
(Дополнительный)

ROW_STANDARDIZATION — Пространственные веса будут нормализованы по ряду. Каждый вес делится на сумму его ряда. Это значение по умолчанию

Input_Table
(Дополнительный)

Date_Time_Field
(Дополнительный)

Поле даты с отметкой времени для каждого объекта.

Date_Time_Interval_Type
(Дополнительный)

Задает единицы измерения времени.

SECONDS — Единицами измерения будут секунды.

Date_Time_Interval_Value
(Дополнительный)

Целочисленное значение количества единиц измерения времени, составляющее временной диапазон.

Например, если для параметра Date_Time_Interval_Type выбрано HOURS , а параметр Date_Time_Interval_Value задан равным 3, временное окно составит 3 часа. Объекты, попадающие в указанное временное окно и в указанное пространственное окно, будут считаться соседями.

Use_Z_values

USE_Z_VALUES — Z-значения будут использоваться в построении матрицы пространственных весов.

Пример кода

GenerateSpatialWeightsMatrix – пример 1 (окно Python)

Пример скрипта в окне Python для использования функции GenerateSpatialWeightsMatrix .

import arcpy arcpy.env.workspace = "C:/data" arcpy.GenerateSpatialWeightsMatrix_stats("911Count.shp", "MYID", "euclidean6Neighs.swm", "K_NEAREST_NEIGHBORS", "#", "#", "#", 6, "NO_STANDARDIZATION")

GenerateSpatialWeightsMatrix, пример 2 (автономный скрипт Python)

Следующий автономный Python скрипт демонстрирует, как использовать функцию GenerateSpatialWeightsMatrix .

 # Analyze the spatial distribution of 911 calls in a metropolitan area # using the Hot-Spot Analysis Tool (Local Gi*) # Import system modules import arcpy # Set property to overwrite existing output, by default arcpy.env.overwriteOutput = True # Local variables. workspace = "C:/Data" try: # Set the current workspace (to avoid having to specify the full path to the feature classes each time) arcpy.env.workspace = workspace # Copy the input feature class and integrate the points to snap # together at 500 feet # Process: Copy Features and Integrate cf = arcpy.CopyFeatures_management("911Calls.shp", "911Copied.shp", "#", 0, 0, 0) integrate = arcpy.Integrate_management("911Copied.shp #", "500 Feet") # Use Collect Events to count the number of calls at each location # Process: Collect Events ce = arcpy.CollectEvents_stats("911Copied.shp", "911Count.shp", "Count", "#") # Add a unique ID field to the count feature class # Process: Add Field and Calculate Field af = arcpy.AddField_management("911Count.shp", "MyID", "LONG", "#", "#", "#", "#", "NON_NULLABLE", "NON_REQUIRED", "#", "911Count.shp") cf = arcpy.CalculateField_management("911Count.shp", "MyID", "[FID]", "VB") # Create Spatial Weights Matrix for Calculations # Process: Generate Spatial Weights Matrix. swm = arcpy.GenerateSpatialWeightsMatrix_stats("911Count.shp", "MYID", "euclidean6Neighs.swm", "K_NEAREST_NEIGHBORS", "#", "#", "#", 6, "NO_STANDARDIZATION") # Hot Spot Analysis of 911 Calls # Process: Hot Spot Analysis (Getis-Ord Gi*) hs = arcpy.HotSpots_stats("911Count.shp", "ICOUNT", "911HotSpots.shp", "GET_SPATIAL_WEIGHTS_FROM_FILE", "EUCLIDEAN_DISTANCE", "NONE", "#", "#", "euclidean6Neighs.swm") except arcpy.ExecuteError: # If an error occurred when running the tool, print the error message print(arcpy.GetMessages())

Параметры среды

Особые случаи

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

Информация о лицензиях

Basic: Да
Standard: Да
Advanced: Да

Связанные разделы

Обзор группы инструментов Моделирование пространственных отношений
Как работает Географически взвешенная регрессия (ГВР)
Пространственная автокорреляция (Глобальный индекс Морана I)
Кластеризация с высокими/низкими значениями (Глобальный индекс Getis-Ord G)
Анализ кластеров и выбросов (Anselin Локальный индекс Морана I)
Анализ горячих точек
Анализ группирования
Пространственные веса
Моделирование пространственных отношений
Как работает инструмент Построить матрицу пространственных весов для сети
Поиск инструмента геообработки

Как построить матрицу расстояний в excel