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

Добавил пользователь Евгений Кузнецов
Обновлено: 10.09.2024

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

Инструкция . Укажите количество данных, нажмите Далее . Полученное решение сохраняется в файле Word .

Выбор конкретного метода кластерного анализа зависит от цели классификации.
Обычной формой представления исходных данных в задачах кластерного анализа служит матрица:

каждая строка которой, представляет результат измерений k , рассматриваемых признаков на одном из обследованных объектов.
Наиболее трудным считается определение однородности объектов, которые задаются введением расстояния между объектами х_i и х_j (p(x_i, x_j)).
Объекты будут однородными в случае p(x_i, x_j)? p_пор,
где p_пор- заданное пороговое значение.
Выбор расстояния (р) является основным моментом исследования, от которого зависят окончательные варианты разбиения. Наиболее распространенными считаются принципы “ближайшего соседа” или “дальнего соседа”. В первом случае за расстояние между кластерами принимают расстояние между ближайшими элементами этих кластеров, а во втором - между наиболее удаленными друг от друга.
В задачах кластерного анализа часто используют Евклидово и Хемингово расстояния.
Евклидово расстояние определяется по формуле:
;
сравнивается близость двух объектов по большому числу признаков.
Хемингово расстояние:
;
используется как мера различия объектов, задаваемых атрибутивными признаками.

Вручную группировать запросы не всегда эффективно: перебрать 200–300 запросов можно за час, на 20–30 тысячуйдет неделя. Автоматическим сервисам группировку я не доверю, так как она определяет структуру и управляемость кампании.

Поэтому придумал свой метод, который ускоряет кластеризацию и даёт осознанный результат. Облегчает жизнь при работе с СЯ от 2–3 тысяч ключевых слов. Пробовал работать с 45 000 — Excel начинал умирать. Список из 200–300 запросов быстрее перебрать руками.

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

Метод

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

Используем для этого нормализованную форму запроса — уберём окончания и выстроим слова в порядке важности:

Убираем окончания

Слово без окончания — это признак, который объединяет разные словоформы:

Чтобы убирать окончания я использую mystem . Это лемматизатор от Яндекса. Он обрабатывает список слов и возвращает нормализованные значения — леммы.

Определяем важность

Важность слова — вычисляемый параметр для конкретного списка ключевых слов. Он не определяет важность слова в общей картине мира.

Важность слова рассчитывается из частотности и количества упоминаний слов в списке. Рассмотрим на примере.

Берём список запросов с частотностью

Купить бумеранг — 1000
Бумеранги цена — 700
Бумеранги в москве — 750
Купить классический бумеранг — 450
Цены на бумеранги в москве — 350
Купить классический бумеранг в москве — 100

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

Считаем доли частотностей

Купить бумеранг — 1000 = 1000/2 = 500
Бумеранги цена — 700 = 700/2 = 350
Бумеранги в москве — 750 = 750/3 = 250
Купить классический бумеранг — 450 = 450/3 = 150
Цены на бумеранги в москве — 350 = 350/5 = 70
Купить классический бумеранг в москве — 100 = 100/5 = 20

Считаем вес слов

Сортируем по важности

8040 — бумеранг
2010 — купить
840 — цена
640 — москва
340 — классический
70 - на
20 - в

Располагаем запросы по важности

Купить бумеранг — бумеранг | купить
Бумеранги цена — бумеранг | цена
Бумеранги в москве — бумеранг | москва
Купить классический бумеранг — бумеранг | купить | классический
Цены на бумеранги в москве — бумеранг | цена | москва | на | в
Купить классический бумеранг в москве — бумеранг | купить | москва | классический | в

Упорядочиваем и чистим

Бумеранг | купить: купить бумеранг — 1000
Бумеранг | купить | классический: купить классический бумеранг — 450
Бумеранг | купить | москва | классический: купить классический бумеранг в москве — 100
Бумеранг | москва: бумеранги в москве — 750
Бумеранг | цена: бумеранги цена — 700
Бумеранг | цена | москва: цены на бумеранги в москве — 350

В итоге получили первые группы объявлений, с которыми можно работать дальше: укрупнять, объединять, кросс-минусовать. Для этого используем Excel.

Реализация в Excel

Выполняем последовательность действий в таблице (XLS, 537 КБ) с формулами. Кластеризация 1000 запросов займет 30 минут.

Алгоритм одной строкой

Шаг 1. Вычисляем доли весов и разбиваем запросы по словам

Чтобы избежать правки формул называйте все листы и таблицы аналогично таблице-примеру

Вычисляем доли весов:
Доли весов = Частотность / Кол-во слов.
Кол-во слов =LEN ([@Ключ])-LEN (SUBSTITUTE ([@Ключ]," ",""))+1.

Шаг 2. Формируем таблицу-справочник с весами слов

Преобразуем таблицу из формата [W1] [W2] [W3] [W4] [W5] [W6] [W7] [Доли весов] в формат: [W1] -> [Доли весов] [W2] -> [Доли весов] [W3] -> [Доли весов] [W4] -> [Доли весов] [W5] -> [Доли весов] [W6] -> [Доли весов] [W7] -> [Доли весов]: Формирование справочника со словами

Удаляем пустые ячейки и считаем кол-во упоминаний каждого слова.

Шаг 3. Выделяем леммы и дорабатываем справочник со словами

Шаг 4. Формируем таблицу-справочник с леммами

Шаг 5. Делаем первичную кластеризацию

Итак, что мы сделали. Разбили запросы по словам. Для каждого слова выделили лемму — можем объединить запросы по общим словам. Для каждой леммы посчитали вес. Остаётся выстроить слова в запросе в порядке важности. Тогда при сортировке по алфавиту запросы сами объединятся в группы объявлений.

Выстраиваем слова в порядке важности функцией SMALL. В диапазоне статусов L1 – L7 ищем самый маленький статус — это самое важное слово во фразе. Затем, ищем второй самый маленький статус — это второе по важности слово во фразе. И так еще пять раз — проверяем оставшиеся столбцы L3 – L7.

Получаем последовательность статусов. Например, 37 -> 100 -> 200 -> 700. Для каждого статуса подтягиваем VLOOKP-ом соответствующую Лемму из справочника Лемм. Соединяем Леммы CONCATENATE-ом и получаемнормализованное значение фразы. Я использую его как название группы объявлений.

Сортируем по алфавиту:

Полная рабочая формула в файле-примере.

Шаг 6. Укрупняем полученные группы

Игнорируя окончания и порядок слов, мы объединили запросы с одинаковым набором слов. Количество групп стремится к количеству слов — это 100 % точность инструмента. Можно использовать, если вы предпочитаете работать с запросами в точном соответствии.

Что можно удалить:

До какой степени укрупнять: я стремлюсь к среднему показателю 2–3 запроса в одной группе объявлений и слежу за максимальным количеством фраз (помним про ограничения систем контекстной рекламы).

Резюме

Полученный список групп удобно кросс-минусовать и двигать между кампаниями. Название группы поможет писать объявления — вы сами определяете важные слова в названии группы.

Отзывы джедаев о кластеризаторе

«Я помогал Роме с созданием инструмента на ранних этапах. Всем рекомендую попробовать кластеризатор для ядра от 2000 ключевых слов -> сэкономит время.

Егор Холов, ведущий джедай:

«Методику пробовал, но не использую в работе, потому что нечасто собираю контекст в больших объемах.

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

«Кластеризация от Ромы просто находка! Методом пользуюсь каждый раз когда работаю с семантикой — собираю или корректирую кампании.

Больше всего мне нравятся три вещи:

Из минусов: все формулы я копирую из готового шаблона и переключаться между окнами одной программы неудобно. Я бы хотела иметь формулы под рукой, а может сделать в будущем какой-нибудь шаблон, чтобы сократить количество копирований. Ещё хотелось бы сократить время группировки, но пока не нашла способ.

Что дальше

Если у вас список из 200–300 запросов, переберите руками — так быстрее.

Если хотите готовое решение — попросите программистов написать скрипт.

Я постоянно дорабатываю кластеризатор. В следующих итерациях хочу проработать кросс-минусовку групп, добавить справочники минус-слов и максимально автоматизировать кластеризатор на Power Query. Следите за обновлениями!

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

Полученный график можно редактировать и распечатать непосредственно из Excel или

перенести, воспользовавшись буфером обмена, в какой-либо графический редактор,

например, в CorelDraw. Векторный формат изображения удобен для редактирования при

подготовке иллюстрационной графики. Основным преимуществом данного подхода является

возможность избежать утомительной процедуры экспорта данных из Excel в программу,

выполняющую статистические вычисления, что существенно экономит время.

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Кластерный анализ

Шаг №1
Шаг №2
Видеоинструкция
Оформление Word

каждая строка которой, представляет результат измерений k , рассматриваемых признаков на одном из обследованных объектов.
Наиболее трудным считается определение однородности объектов, которые задаются введением расстояния между объектами х_i и х_j (p(x_i, x_j)).
Объекты будут однородными в случае p(x_i, x_j)? p_пор,
где p_пор— заданное пороговое значение.
Выбор расстояния (р) является основным моментом исследования, от которого зависят окончательные варианты разбиения. Наиболее распространенными считаются принципы “ближайшего соседа” или “дальнего соседа”. В первом случае за расстояние между кластерами принимают расстояние между ближайшими элементами этих кластеров, а во втором — между наиболее удаленными друг от друга.
В задачах кластерного анализа часто используют Евклидово и Хемингово расстояния.
Евклидово расстояние определяется по формуле:
;
сравнивается близость двух объектов по большому числу признаков.
Хемингово расстояние:
;
используется как мера различия объектов, задаваемых атрибутивными признаками.

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

№ п/п	1	2	3	4	5	6
x₁	2	4	5	12	14	15
x₂	8	10	7	6	6	4

2. Полученные данные помещаем в таблицу (матрицу расстояний).

№ п/п	1	2	3	4	5	6
1	0	2.83	3.16	10.2	12.17	13.6
2	2.83	0	3.16	8.94	10.77	12.53
3	3.16	3.16	0	7.07	9.06	10.44
4	10.2	8.94	7.07	0	2	3.61
5	12.17	10.77	9.06	2	0	2.24
6	13.6	12.53	10.44	3.61	2.24	0

3. Поиск наименьшего расстояния.
Из матрицы расстояний следует, что объекты 4 и 5 наиболее близки P_4;5 = 2 и поэтому объединяются в один кластер.

№ п/п	1	2	3	[4]	[5]	6
1	0	2.83	3.16	10.2	12.17	13.6
2	2.83	0	3.16	8.94	10.77	12.53
3	3.16	3.16	0	7.07	9.06	10.44
[4]	10.2	8.94	7.07	0	2	3.61
[5]	12.17	10.77	9.06	2	0	2.24
6	13.6	12.53	10.44	3.61	2.24	0

При формировании новой матрицы расстояний, выбираем наименьшее значение из значений объектов №4 и №5.
В результате имеем 5 кластера: S₍₁₎, S₍₂₎, S₍₃₎, S_(4,5), S₍₆₎
Из матрицы расстояний следует, что объекты 4,5 и 6 наиболее близки P_4,5;6 = 2.24 и поэтому объединяются в один кластер.

№ п/п	1	2	3	[4,5]	[6]
1	0	2.83	3.16	10.2	13.6
2	2.83	0	3.16	8.94	12.53
3	3.16	3.16	0	7.07	10.44
[4,5]	10.2	8.94	7.07	0	2.24
[6]	13.6	12.53	10.44	2.24	0

При формировании новой матрицы расстояний, выбираем наименьшее значение из значений объектов №4,5 и №6.
В результате имеем 4 кластера: S₍₁₎, S₍₂₎, S₍₃₎, S_(4,5,6)
Из матрицы расстояний следует, что объекты 1 и 2 наиболее близки P_1;2 = 2.83 и поэтому объединяются в один кластер.

№ п/п	[1]	[2]	3	4,5,6
[1]	0	2.83	3.16	10.2
[2]	2.83	0	3.16	8.94
3	3.16	3.16	0	7.07
4,5,6	10.2	8.94	7.07	0

При формировании новой матрицы расстояний, выбираем наименьшее значение из значений объектов №1 и №2.
В результате имеем 3 кластера: S_(1,2), S₍₃₎, S_(4,5,6)
Из матрицы расстояний следует, что объекты 1,2 и 3 наиболее близки P_1,2;3 = 3.16 и поэтому объединяются в один кластер.

№ п/п	[1,2]	[3]	4,5,6
[1,2]	0	3.16	8.94
[3]	3.16	0	7.07
4,5,6	8.94	7.07	0

При формировании новой матрицы расстояний, выбираем наименьшее значение из значений объектов №1,2 и №3.
В результате имеем 2 кластера: S_(1,2,3), S_(4,5,6)

№ п/п	1,2,3	4,5,6
1,2,3	0	7.07
4,5,6	7.07	0

Таким образом, при проведении кластерного анализа по принципу “ближнего соседа” получили два кластера, расстояние между которыми равно P=7.07
Результаты иерархической классификации объектов представлены на рис. в виде дендрограммы.

Эта цепочка заблокирована. Вы можете просмотреть вопрос или оставить свой голос, если сведения окажутся полезными, но вы не можете написать ответ в этой цепочке.

Оскорбление — это любое поведение, которое беспокоит или расстраивает человека или группу лиц. К угрозам относятся любые угрозы самоубийством, насилием, нанесением ущерба и др. Любое содержимое для взрослых или недопустимое на веб-сайте сообщества. Любое изображение, обсуждение наготы или ссылка на подобные материалы. Оскорбительное, грубое или вульгарное поведение и другие проявления неуважения. Любое поведение, нарушающее лицензионные соглашения, в том числе предоставление ключей продуктов или ссылок на пиратское ПО. Незатребованная массовая рассылка или реклама. Любые ссылки или пропаганда сайтов с вирусным, шпионским, вредоносным или фишинговым ПО. Любое другое неуместное содержимое или поведение в соответствии с правилами использования и кодексом поведения. Любое изображение, ссылка или обсуждение, связанные с детской порнографией, детской наготой или другими вариантами оскорбления или эксплуатации детей.

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

Создание столбчатой диаграммы с накоплением и кластеризацией в Excel

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

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

1. После упорядочивания данных выберите диапазон данных, на основе которого вы хотите создать диаграмму, а затем щелкните Вставить > Вставить столбец или гистограмму > Столбец с накоплением, см. снимок экрана:

2. Щелкните правой кнопкой мыши панель ряда данных и выберите Форматировать ряд данных, см. снимок экрана:

3. В Форматировать ряд данных панель, под Варианты серий раздел, изменить Ширина зазора в 0, см. снимок экрана:

4. После этого сложенная кластерная столбчатая диаграмма была успешно создана, теперь вы можете переместить нижнюю легенду вправо или в другое положение по вашему желанию. Щелкните легенду правой кнопкой мыши и выберите Легенда формата, см. снимок экрана:

5. В Легенда формата панели выберите нужную позицию из Параметры легенды вкладку, см. снимок экрана:

Читайте также: