Как сделать кластерный анализ в excel
Кластерный анализ - это совокупность методов классификации многомерных наблюдений или объектов, основанных на определении понятия расстояния между объектами с последующим выделением из них групп наблюдений (кластеров, таксонов).
Инструкция . Укажите количество данных, нажмите Далее . Полученное решение сохраняется в файле Word .
Выбор конкретного метода кластерного анализа зависит от цели классификации.
Обычной формой представления исходных данных в задачах кластерного анализа служит матрица:
каждая строка которой, представляет результат измерений k , рассматриваемых признаков на одном из обследованных объектов.
Наиболее трудным считается определение однородности объектов, которые задаются введением расстояния между объектами хi и хj (p(xi, xj)).
Объекты будут однородными в случае p(xi, xj)£ 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] → [Доли весов]: Формирование справочника со словами
Преобразуем таблицу из формата [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(xi, xj)).
Объекты будут однородными в случае p(xi, xj)£ pпор,
где pпор— заданное пороговое значение.
Выбор расстояния (р) является основным моментом исследования, от которого зависят окончательные варианты разбиения. Наиболее распространенными считаются принципы “ближайшего соседа” или “дальнего соседа”. В первом случае за расстояние между кластерами принимают расстояние между ближайшими элементами этих кластеров, а во втором — между наиболее удаленными друг от друга.
В задачах кластерного анализа часто используют Евклидово и Хемингово расстояния.
Евклидово расстояние определяется по формуле:
;
сравнивается близость двух объектов по большому числу признаков.
Хемингово расстояние:
;
используется как мера различия объектов, задаваемых атрибутивными признаками.
Пример . Провести классификацию шести объектов, каждый из которых характеризуется двумя признаками (табл.9). В качестве расстояния между объектами принять , расстояние между кластерами исчислить по принципам: 1) “ближайшего соседа” и 2) “дальнего соседа”.
| № п/п | 1 | 2 | 3 | 4 | 5 | 6 |
| x1 | 2 | 4 | 5 | 12 | 14 | 15 |
| x2 | 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 наиболее близки P4;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(1), S(2), S(3), S(4,5), S(6)
Из матрицы расстояний следует, что объекты 4,5 и 6 наиболее близки P4,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(1), S(2), S(3), S(4,5,6)
Из матрицы расстояний следует, что объекты 1 и 2 наиболее близки P1;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(3), S(4,5,6)
Из матрицы расстояний следует, что объекты 1,2 и 3 наиболее близки P1,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. В Легенда формата панели выберите нужную позицию из Параметры легенды вкладку, см. снимок экрана:
Читайте также: