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

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

Argument ‘Topic id’ is null or empty

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

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Самыми близкими друг к другу объектами являются объекты 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.

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

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

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

Кластеризация с помощью надстройки SEO-Excel

215

• Подготовительные работы
• Использование надстройки для кластеризации
• Ручная группировка
• Сортировка кластеров
• Заключение

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

Выполнив кластеризацию любым из удобных сервисов для кластеризации, семантика в файле делится на сгруппированные запросы и запросы без группы. С помощью надстройки SEO-Excel можно ускорить процесс объединения подобных запросов и групп. Перед началом работы необходимо скачать надстройку на сайте https://seo-excel.ru/.

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

Подготовительные работы

• Вкладка «Группы» — запросы, сгруппированные в кластеры.
• Вкладка «Без группы» — некластеризованные запросы.
• «Эталон» — название кластера.

Первым делом необходимо выполнить нумерацию групп запросов перед работой с надстройкой. Для этого на листе с кластерами создаём пустой столбец «Группа» и напротив первого запроса ставим 1. На слайде это ячейка C2.

Для чего делаем?

Для дальнейшего объединения групп и запросов без группы между собой. Ориентироваться на нумерацию группы куда проще, чем искать кластеры по «Эталонам» или же запросам. В ячейке C3 прописываем формулу =ЕСЛИ(A3=A2;C2;1+C2), она позволит пронумеровать значения по группам.

Почему используются значения A3 и A2 в качестве условий?

Логическое выражение, в котором мы сравниваем два одинаковых значения в столбце (A3 и A2), позволит получить одинаковые значения порядкового номера в столбце «Группа». Таким образом мы выполним быструю нумерацию ячеек. Можно выполнить нумерацию руками ��

Дублируем формулу для всего столбца «Группа».

Необходимо также пронумеровать запросы «Без групп». В моём файле они находятся на отдельном листе.

Важно: нумерацию необходимо продолжить. Если на листе «Группа» последняя группа была 89, то первому запросу из «Без группы» присваиваем номер группы 90, второму 91 и так далее.

Для чего выполняем нумерацию запросов «Без группы»?

Для дальнейшего объединения кластеризованных запросов и запросов «Без группы».

Теперь нужно создать новый лист в файле Excel, где будет происходить первый этап работы с надстройкой — «Лист1».

После вновь переходим на вкладку «Группы» и добавляем фильтр.

И выполняем сортировку запросов по цвету.

Для чего выполняем фильтрацию по цвету?

Чтобы сравнить «Эталоны» (первый запрос) каждой группы между собой. Это подготовительные работы для выполнения группировки.

Отсортированные запросы копируем вместе с номером группы.

И добавляем на «Лист1» эти запросы. Ту же операцию делаем с запросами «Без группы»: копируем и добавляем на «Лист1».

В конечном результате на «Листе1» у нас будет заполнено 2 столбца (A и B) запросами из «Группы» и «Без группы».

Для чего делаем?

Всё для той же группировки запросов ��

После чего на «Листе1» необходимо запросы из столбца A скопировать на столбец C.

Это последний этап подготовительных работ, теперь переходим непосредственно к самой группировке.

Использование надстройки для кластеризации

Теперь воспользуемся надстройкой SEO-Excel для самой кластеризации.

Переходим на вкладку надстройки.

Выбираем инструмент под названием «Выжимка».

«Выжимка» позволяет удалить слова, которые не влияют на интент. Пример таких слов: фото, купить, стоимость, дёшево. Выжимка состоит из двух полей: в левом поле пишем слова, которые подлежат удалению, дописывать их до конца необязательно, а в правом пишем слова, которые нужно полностью удалить (чаще всего это предлоги, союзы и сокращения).

Кроме этого, есть 3 галочки, которые позволяют:

• Привести каждое слово к лексеме (помогает избавиться от словоформ).
• Перевести в транслит (помогает в случае с «адидас» и «adidas», но лучше не использовать для всего кластера).
• Расставить слова в алфавитном порядке (избавляемся от перестановок в запросах).

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

Дописываем слова, которые считаем ненужными (не влияющими на интент). В моём случае в левую колонку добавляю «недорог», «скольк», «ключ», в правую — «где», «под».

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

После выполнения макроса запросов столбца «А» получаем:

• Удаление однокоренных слов и предлогов, которые были указаны в «Слова» и «Предлоги».
• Слова из фраз приведены к лексеме.
• Слова в лексеме будут отсортированы по алфавиту (готовые бани → баня готовый).

Для чего это нужно делать?

Чтобы удалить запросы, которые не влияют на интент.

Далее выполняем сортировку запросов из столбца A (я беру стандарт — от А до Я). Для этого возвращаемся на главную настройку Excel.

И выбираем «Сортировка и фильтр».

Автоматически расширяем диапазон, чтобы выполнить сортировку для всех данных «Лист1».

Ручная группировка

После сортировки мы пришли к этапу ручной кластеризации групп.

На «Лист1» определяем одинаковые запросы столбца A и объединяем между собой в одну группу. Пример:

Для чего делаем и почему эти группы необходимо объединить?

На этом этапе мы объединяем кластеры с одним интентом.

Если есть сомнения, группировать кластеры между собой или нет, то можно проверить выдачу. Воспользуемся https://arsenkin.ru/tools/check-top/ для проверки выдачи. Для этого запросы из столбца «C» добавляем в инструмент и проверяем выдачу.

Чтобы объединить кластеры на листе «Группы», я ищу нумерацию запросов с одинаковым интентом (в моём примере это группы 1 и 2) и присваиваю меньший порядковый номер для этих групп. Группы 1 и 2 будут находиться в одном кластере под номером 1.

Переходим во вкладку «Группа» и изменяем вручную нумерацию для группы 2 на 1.

Для чего это делаем?

Таким образом объединяем кластеры между собой.

Почему присваиваю группам нумерацию «1», а не «2»?

Можно присвоить и «2», лишь бы нумерация была одинаковой для всех групп с одинаковым интентом.

После возвращаемся на вкладку «Лист1» и прорабатываем оставшиеся запросы. Объединяем группы, которые имеют одинаковые интенты. Например, группы 16 и 36 объединяем между собой.

Так же объединяем подобные запросы с вкладки «Группы» (в серой заливке) и «Без группы» (без заливки).

Находим запрос во вкладке «Без группы» под номером 103.

И добавляем его в кластер под номером 83, которая находится во вкладке «Группы».

Необходимо добавленному запросу с вкладки «Без группы» присвоить порядковый номер кластера, в который он был перемещён во вкладку «Группы».

После того как все одинаковые запросы из вкладки «Лист1» были распределены по кластерам во вкладку «Группы», необходимо сделать сортировку по номеру группы.

В итоге получаем кластеры, имеющие одинаковый номер группы.

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

Нужно оставить только один Эталон в серой заливке, с остальных эталонов убираем заливку. Пример реализации:

Ручная группировка выполнена.

Стоит обратить внимание на неявные кластеры, которые необходимо сгруппировать. Например, «игрушки звёздные войны» и «игрушки star wars» могут быть разными кластерами, но по логике мы понимаем, что это одно и то же. Проверяем ТОП-10:

Видим, что в ТОПе по двум запросам одинаковые страницы, значит, кластеры необходимо сгруппировать. В таком случае «Выжимка» никак не сработает. Такие кластеры и запросы необходимо пересматривать вручную.

Обращайте внимание на особенности тематики, возможные нюансы, которые могут возникнуть при ручной группировке.

Сортировка кластеров

Для удобства использования сгруппированной семантики кластеры можно отсортировать. Я использую сортировку по точной частоте. Для её выполнения вернёмся в надстройку SEO-Excel, выбираем «Сортировка».

Сортировка строк в кластере систематизирует содержимое кластеров. Необходимо, чтобы первая строка в кластере имела заливку, потому что инструмент ориентируется на неё. Сортировка может выполняться как по столбцу с текстом, так и по столбцу, содержащему цифры.

Инструмент может сортировать кластеры по убыванию частотности. Для каждого кластера считается его суммарная частотность по выбранному столбцу, затем создаётся новый лист, который содержит все данные, и кластеры на нём расположены по убыванию частотности. Каждому кластеру присваивается ярлык (столбец A), который является суммарной частотностью кластера.

Выбираем сортировку по «Строки в кластере».

Необходимо выполнить сортировку по точной частоте.

Что мы получим от этого?

Внутри кластера запросы будут отсортированы по точной частоте по убыванию. Самый частотный запрос будет «Эталоном». Это очень удобно для определения самых частотных и трафиконесущих запросов.

После чего вновь нажимаем на «Сортировка» и теперь выбираем «Кластеры на листе».

И тоже сортируем по точной частоте.

Что от этого получаем?

Мы получим новый «Лист» в документе Excel, на котором кластеры будут отсортированы между собой по суммарной частотности по убыванию. В итоге получим вкладку с данными.

Из таблицы видно, что появился новый столбец A, закрашенный в заливку. В ячейках столбца присвоен ярлык каждому кластеру.

Заключение

Надстройка SEO-Excel — это удобный сервис для seo-специалиста. Применение надстройки в группировании ключей даёт возможность быстро и качественно объединить кластеры с одним интентом между собой. Сортировка кластеров позволяет установить рейтинг частотности кластеров и быстро определить спрос на товар или услугу.

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

Если Вы хотите грамотно оптимизировать страницы под конверсионные запросы, обращайтесь к нам!

Подписаться на рассылку

• 11 полезных расширений для SEO-специалиста в Google Chrome Подготовили подборку из 11 SEO-расширений в Chrome, которые используем в повседневной работе, со скриншотами и описанием плюсов/минусов каждого. Такие плагины будут полезны в работе и SEO-специалисту.
• Аудит сайта при помощи плагина SeoTools для Excel — подробная инструкция Как спарсить метаданные с помощью Excel, проверить битые ссылки, получить данные из поисковых систем? В сегодняшней статье мы рассмотрим возможности плагина SeoTools – одного из.
• 9 бесплатных SEO-инструментов при работе с сайтом Бесплатные инструменты для технического анализа Парсинг Website auditor от SEO Powersuite Перелинковка PageWeight Lite Free Version Скорость gtmetrix.com batchspeed.com Бесплатные инструменты для анализа структуры сайта.
• Какие есть программы для анализа и оптимизации внутренней перелинковки? У меня вопрос про внутреннюю перелинковку. Со времен PageWeight появились ли более продвинутые сервисы/софты, сфокусированные именно на отслеживании показателей, связанных с иннерлинками? Как вы измеряете.
• Инструменты для SEO-Junior, как ускорить выполнение задач — советы Работа с семантикой Сбор маркеров Serpstat Системы Метрик Сбор облака запросов Несколько потоков в Key Collector Антикапча Чистка запросов Лемматизатор «Анализ групп» в Key Collector.

В SEO пришла постепенно: прошла путь от контент-менеджера до оптимизатора. Всегда хотела работать головой.

Увлекаюсь спортом, всегда держу тело и ум в тонусе.

Девиз: Чтобы чего-нибудь добиться, надо прежде всего начать действовать.