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

Excel кластерный анализ

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

​Смотрите также​ буден меньше либо​ 2) более одного​ «вручную» кластерный анализ​ про нейронные сети,​ основных средств и​ PEST-анализа предприятия. Определение​Коэффициент трудового участия: применение​ максимально близки и​Из новой матрицы видно,​ исследования).​ способам терапии.​

​ рынка, анализируются сельские​ сложной процедурой, но​

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

​ есть, ищем самые​. Расстояние между ними​ в биологии (для​Одним из инструментов для​ равно семи, и​ объекта в каждом​ с нуля по​ но не нашёл​ уставного капитала. Скачать​ внешних факторов, влияющих​ и расчет в​ где динамика наиболее​ что можно объединить​Дельта-кластерный анализ имеет и​В психологии – для​

​ хозяйства для сравнения​ на самом деле​ меньшие значения. Таким​ составляет 4,123106, что​ классификации животных), психологии,​ решения экономических задач​ при этом в​ кластере.​ 10 параметрам фактически​ достойной реализации. Есть​ трансформационную таблицу МСФО.​ на продажи и​ Excel.​

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

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

​ меньше, чем между​ медицине и во​ является кластерный анализ.​ каждом кластере будет​

​ невозможно. Используйте статпакеты.​ одно обстоятельство, которое​Расчет среднего заработка работника​ прибыль. Пример применения​Коэффициент трудового участия​

​ к примеру, товарной​ объекты [4, 5]​состав и количество кластеров​ личности в определенных​​ конъюнктура рынка отдельных​​ данного метода не​​ что нашу совокупность​​ любыми другими элементами​ многих других сферах​ С его помощью​ более одного объекта.​Изначально количество кластеров​

​ Если такой возможности​ сильно усложняет процесс​ в Excel при​ маркетингового инструмента в​​ чаще всего применяется​​ и общехозяйственной конъюнктуры​ и 6 (как​ зависит от заданного​ ситуациях.​ продуктов и т.д.​ так уж тяжело.​ данных можно разбить​ данной совокупности.​ деятельности человека. Кластерный​​ кластеры и другие​​ В итоге должна​​ = количеству точек,​​ нет, я вам​​ — нельзя использовать​​ сокращении штата.​​ Excel (исследование магазина)​​ при начислении зарплаты​

​ этот метод отлично​ наиболее близкие друг​ критерия разбиения;​В экономическом анализе –​По сути, кластерный анализ​ Главное понять основную​ на два кластера.​Объединяем эти данные в​ анализ можно применять,​ объекты массива данных​ получиться точечная диаграмма​ то есть каждая​ сочувствую.​ никакие надстройки и​Как рассчитать средний​Матрица БКГ: построение и​​ работникам-сдельщикам. Как рассчитать​​ подходит.​​ к другу по​​при преобразовании исходного набора​​ при изучении и​​ – это совокупность​​ закономерность объединения в​​ В первом кластере​ группу и формируем​ используя для этих​ классифицируются по группам.​​ на которой точки​​ точка в своем​Как это все​ расширения, используется стандартный​ заработок при сокращении​ анализ в Excel​

​ КТУ: формула, таблица​Выполнения анализа данных​ значениям). Оставляем наименьшее​

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

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

​ работника в связи​ на примере предприятия.​ с повышающими и​ в таблицах с​ значение и формируем​ группы исходная информация​ исследовании конъюнктуры.​ многомерных объектов. Метод​Автор: Максим Тютюшев​ между собой элементы​ которой значения​ инструментов Эксель.​

​ применять в программе​ кластеру окрашены в​ масс» кластеров (Mi=((сумма​ на хабре. Там​Влад​ с сокращением численности​Матрица БКГ -​ понижающими критериями. ​ использованием функций, формул​ новую матрицу:​ может искажаться, отдельные​В разнообразных маркетинговых исследованиях.​ подразумевает определение расстояния​Кластерный анализ объединяет кластеры​ –​1,2​Имеем пять объектов, которые​ Excel. Посмотрим, как​

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

​ какой-нибудь свой цвет.​ Хi )/Nx; (сумма​ есть отличные статьи​: Что это за​ или штата для​ великолепный инструмент портфельного​Расчет коэффициента финансовой активности​ и встроенных стандартных​

​Объекты 1 и 2​ объекты могут терять​Когда нужно преобразовать «горы»​ между переменными (дельты)​ и переменные (объекты),​1​выступают отдельным элементом.​ характеризуются по двум​ это делается на​

​ В добавок ко​ Уi)/Ny) на данном​ по алгоритмам.​ группировка в Вашем​ начисления выходного пособия​

​ анализа. Рассмотрим на​

1. ​ в Excel: формула​ инструментов, а также​ можно объединить в​
2. ​ свою индивидуальность;​ информации в пригодные​ и последующее выделение​ похожие друг на​
3. ​,​ При составлении матрицы​ изучаемым параметрам –​ практике.​
4. ​ всему, весь процесс​ этапе это -​stylecolor​ понимании? Если это​
5. ​ за первый и​

​ примере в Excel​ по балансу.​ практическое применение расширяемых​ один кластер (как​часто игнорируется отсутствие в​

​ для дальнейшего изучения​

• ​ групп наблюдений (кластеров).​ друга. То есть​2​
• ​ оставляем наименьшие значения​x​Скачать последнюю версию​ должен быть каким​
• ​ координаты точек, для​: Доброго времени суток,​ показатели (результаты) деятельности,​ второй месяцы. 1​
• ​ построение матрицы, выявление​Коэффициент финансовой активности​ настроек для поиска​ наиболее близкие из​ анализируемой совокупности некоторых​ группы, используют кластерный​Техника кластеризации применяется в​ классифицирует объекты. Часто​

​,​ из предыдущей таблицы​

• ​и​ Excel​ то образом заметен,​
• ​ каждого кластера. Теперь​ умным людям!​ делается обычная статистическая​ 2 3 4​ с ее помощью​ показывает, насколько предприятие​
• ​ решений.​ имеющихся). Выбираем наименьшее​ значений кластеров.​

​ анализ.​

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

​ самых разнообразных областях.​ при решении экономических​4​ для объединенного элемента.​

​y​С помощью кластерного анализа​ но это пока​

​ нужно найти расстояния​Дано:​

​ группировка, для которой​ 5 6 7​ перспективных и бесперспективных​ зависит от заемных​Коэффициент оборачиваемости дебиторской задолженности​ значение и формируем​​Преимущества метода:​ Главное задача –​

​ задач, имеющих достаточно​,​ Опять смотрим, между​.​ можно проводить выборку​ не так важно.​ между всеми центрами​А(нижний предел) =​ Вы должны иметь​MaxGol​

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

​ какими элементами расстояние​Применяем к данным значениям​ по признаку, который​

​ Мне б для​ масс, то есть​ 0; В(верхний предел)​ или определить критерии.​: Необходимо разделить имеющиеся​SWOT анализ слабые и​

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

Анализ данных в Excel с помощью функций и вычислительных инструментов

Анализ данных и поиск решений

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

​ анализа, – как​​ которых необходимо много​ других объектов. Расстояние​и группа объектов​ помещаем в матрице​ расстояние между объектами​ немного получалось. Языки,​ для каждого кластера,​ 100. Копируем только​ задачу (в плане​ нужно провести группировку;​ с помощью матриц,​ «гор» информации. Позволяет​ Excel.​
​ получено два кластера,​Самыми близкими друг к​ их, делать компактными​ организовать многомерную выборку​ характеристик. Он позволяет​ между кластерами составляет​1,2​ расстояний.​ по заданному параметру.​ как я поняла,​ опять найти все​ значения, получаем набор​ техники расчетов), то​

​ 3) несколько периодов​​ составление проблемного поля.​ объединить данные в​Что показывает коэффициент​ расстояние между которыми​ другу объектами являются​ и наглядными;​ в наглядные структуры.​ разбить выборку на​

​ 9,84.​​. Дистанция составляет 6,708204.​Смотрим, между какими значениями​ Наиболее близкие друг​ родные. Но я​ расстояния между центрами​
​ случайных пар (Х;У)​ поищите материал на​ за которые имеются​Трансформационная таблица в Excel​ группы для последующего​ абсолютной ликвидности: формула,​ – 7,07.​ объекты 4 и​

​может применяться циклически (проводится​​Примеры использования кластерного анализа:​ несколько групп по​На этом завершается процедура​Добавляем указанные элементы в​ дистанция меньше всего.​ к другу значения​ даже не знаю​ масс, определить наименьшее,​
​Задача:​ тему «Многомерные группировки»,​ данные по значениям​ с примером заполнения.​ исследования. Пример применения​

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

​ пример расчета? Нормативное​​Огромное значение имеет кластерный​ 5. Следовательно, их​
​ до тех пор,​
​В биологии – для​ исследуемому признаку, проанализировать​ разбиения совокупности на​ общий кластер. Формируем​ В нашем примере​ группируются вместе.​ с чего начать.​ объединить два соответствующих​С помощью VBA​ в частности ее​ показателей.​Как составить трансформационную​
​ кластерного анализа.​
​ значение показателя, формула​ анализ в экономическом​ можно объединить в​ пока не будет​ определения видов животных​ группы (как группируются​ группы.​ новую матрицу по​
​ — это объекты​
​Хотя чаще всего данный​ Помогите, кто чем​ кластера в один.​ произвести кластеризацию объектов(точек​ вариант на основе​Я понятия не​ таблицу МСФО: обновление​Анализ макросреды PEST-анализом в​ по балансу, пример​ анализе. Инструмент позволяет​ одну группу –​ достигнут нужный результат;​ на Земле.​ переменные), группировку объектов​Как видим, хотя в​ тому же принципу,​1​ вид анализа применяют​ может. Важен любой​ И так до​ с координатами(Х;У)). Правила​ «многомерной средней»​ имею с какой​ учетной политики, сбор​ Excel на примере​ в Excel. Анализ​ вычленять из громадной​ при формировании новой​ а после каждого​В медицине – для​ (как группируются объекты).​ целом кластерный анализ​ что и в​и​ в экономике, его​ совет.​ тех пор пока​ останова: 1) 7​Все_просто​ стороны подойти к​ информации, корректировка статей​ предприятия торговли.​ динамики с помощью​ совокупности периоды, где​ матрицы оставляем наименьшее​ цикла возможно значительное​ классификации заболеваний по​ С помощью метода​ и может показаться​ предыдущий раз. То​2​ также можно использовать​Кластеризация.xlsx​ количество кластеров не​ или менее кластеров;​: В Excel’е сделать​ этому вопросу. Читал​ баланса. Пример переоценки​Сущность и назначение​ графика, интерпретация результатов.​ значения соответствующих параметров​ значение.​ изменение направленности дальнейшего​
​ группам симптомов и​

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

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

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

Excel-кластеризатор ключевых слов по весу

Рассказывает
Роман Игошин ,
ведущий джедай

Эта статья о том, как распределить по группам 20–30 тысяч ключевых слов. Поможет сэкономить время маркетологам, которые регулярно создают рекламные кампании.

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

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

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

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

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

Удаление окончаний позволит охватить все возможные словоформы для конкретного слова, а сортировка «по важности» — игнорировать порядок слов.

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

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

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

Если система не уверена, какая лемма правильная, то покажет 2–3 варианта . Например, для слова «банку» mystem вернёт две леммы: «банк» и «банка». При проверке результатов мы выберем нужную.

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

Сортировка «по важности» позволит игнорировать порядок слов. При сортировке нормализованных значений фраз по алфавиту мы получим готовые кластеры — группы запросов, схожих по смыслу и набору слов.

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

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

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

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

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

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

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

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

1. Купить — (500+150+20)*3 = 2010
2. Бумеранг — (500+350+250+150+70+20)*6 = 8040
3. Классический — (150+20)*2 = 340
4. Москва — (250+70)*2 = 640
5. Цена — (350+70)*2 = 840
6. В — 20
7. На — 70

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

1. 8040 — бумеранг
2. 2010 — купить
3. 840 — цена
4. 640 — москва
5. 340 — классический
6. 70 — на
7. 20 — в

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

1. Купить бумеранг — бумеранг | купить
2. Бумеранги цена — бумеранг | цена
3. Бумеранги в москве — бумеранг | москва
4. Купить классический бумеранг — бумеранг | купить | классический
5. Цены на бумеранги в москве — бумеранг | цена | москва | на | в
6. Купить классический бумеранг в москве — бумеранг | купить | москва | классический | в

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

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

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

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

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

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

Собираем СЯ → собираем частотность → разбиваем запросы по словам и вычисляем доли весов → формируем таблицу-справочник с весами слов → выделяем леммы для слов → вычисляем «вес» леммы → формируем таблицу-справочник с леммами → делаем первичную кластеризацию → укрупняем полученные группы.

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

Лист «Кластеризация», таблица «Main»

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

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

   

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

   Лист «Слова — Леммы», таблица «Word»

   1. Копируем столбцы W1—W7 на новый лист.
   2. Преобразуем таблицу из формата
      [W1] [W2] [W3] [W4] [W5] [W6] [W7] [Доли весов] в формат:
      [W1] → [Доли весов]
      [W2] → [Доли весов]
      [W3] → [Доли весов]
      [W4] → [Доли весов]
      [W5] → [Доли весов]
      [W6] → [Доли весов]
      [W7] → [Доли весов]:
      

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

   Лист «Слова — Леммы», таблица «Word»

   1. Копируем полученный на прошлом шаге список слов «как есть».
   2. Обрабатываем через mystem → получаем леммы для каждого слова.
   3. Считаем кол-во упоминаний каждой леммы.
      

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

   Лист «Леммы», таблица «Lemmas»

   1. Копируем полученный список лемм на новый лист и удаляем дубли.
   2. Из справочника со словами подтягиваем VLOOKUP-ом кол -во упоминаний каждой леммы.
   3. Считаем кол-во символов в лемме.
   4. Вычисляем «вес» леммы:
      Вес Леммы= [Сумма долей весов слов, входящих в Лемму] * [ Кол-во упоминаний Леммы].
      Формула:
      =(SUMIF (Words[Lemma],[@Лемма], Words[Доли весов]))*[@[ Кол-во упоминаний ]].
   5. Сортируем леммы по столбцу «вес» — от большего к меньшему.
   6. Проставляем «Статус» для лемм — минимальный для старшей леммы (лучше начать с 1 000), дальше +1 к следующему статусу:
      

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

   Лист «Кластеризация», таблица «Main»

   Для каждого слова в столбцах W1—W7 подтягиваем VLOOKP-ом значения «Статус» → записываем их столбцы L1 – L7 :

   

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

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

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

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

   

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

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

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

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

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

   • одинокие буквы, цифры, предлоги, доменные зоны. Леммы длиной 1–3 символа ;
   • редкие леммы — кол-во упоминаний меньше среднего по списку;
   • леммы с малым весом — недостаточно «важные»;
   • в редких случаях — топонимы.

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

   

   В основной таблице ничего править не надо — результат обновится самостоятельно.

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

   

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

   Ещё раз алгоритм: собираем СЯ → собираем частотность → разбиваем запросы по словам и вычисляем доли весов → формируем таблицу-справочник с весами слов → выделяем леммы для слов → вычисляем «вес» леммы → формируем таблицу-справочник с леммами → делаем первичную кластеризацию → укрупняем полученные группы.

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

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

   Инструмент можно улучшить и превратить в автоматический сервис. Также можно дорабатывать формулы определения веса лемм. Но и в текущем виде он поможет специалистам по контексту, которые работают с большой семантикой.»

   «С помощью кластеризатора сильно удобнее и быстрее сгруппировать фразы и потом писать объявления для них. Из недостатков — первый раз кажется, что это сложновато. Но когда попробуешь, то всё довольно понятно. Но эту штуку лучше автоматизировать.»

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

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

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

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

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

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

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

   В целом, способ мне нравится тем, что механика простая и понятная, её легко внедрить и потом управлять кампаниями.»

   Что дальше

   Если у вас СЯ от 2–3 тысяч ключевых слов, используйте этот алгоритм. Прогоните алгоритм 2–3 раза , чтобы «впитать».

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

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

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

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

   Посмотреть видеоурок на Statistica

   В STATISTICA реализованы классические методы кластерного анализа, включая методы k-средних, иерархической кластеризации и двухвходового объединения.

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

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

   Постановка задачи

   Исходный файл данных содержит следующую информацию об автомобилях и их владельцах:

   марка автомобиля – первая переменная;

   стоимость автомобиля – вторая переменная;

   возраст водителя – третья переменная;

   стаж водителя – четвертая переменная;

   возраст автомобиля – пятая переменная;

   

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

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

   Масштаб измерений

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

   Поскольку различные измерения используют абсолютно различные типы шкал, данные необходимо стандартизовать (в меню Данные выберете пункт Стандартизовать), так что каждая переменная будет иметь среднее 0 и стандартное отклонение 1.

   Таблица со стандартизованными переменными приведена ниже.

   

   Шаг 1. Иерархическая классификация

   На первом этапе выясним, формируют ли автомобили «естественные» кластеры, которые могут быть осмыслены.

   Выберем Кластерный анализ в меню Анализ — Многомерный разведочный анализ для отображения стартовой панели модуля Кластерный анализ. В этом диалоге выберем Иерархическая классификация и нажмем OK.

   

   Нажмем кнопку Переменные, выберем Все, в поле Объекты выберем Наблюдения (строки). В качестве правила объединения отметим Метод полной связи, в качестве меры близости – Евклидово расстояние. Нажмем ОК.

   

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

   Мера близости, определяемая евклидовым расстоянием, является геометрическим расстоянием в n- мерном пространстве и вычисляется следующим образом:

   

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

   

   

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

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

   Шаг 2. Кластеризация методом К средних

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

   В Стартовой панели модуля Кластерный анализ выберем Кластеризация методом К средних.

   

   Нажмем кнопку Переменные и выберем Все, в поле Объекты выберем Наблюдения (строки), зададим 4 кластера разбиения.

   

   Метод K-средних заключается в следующем: вычисления начинаются с k случайно выбранных наблюдений (в нашем случае k=4), которые становятся центрами групп, после чего объектный состав кластеров меняется с целью минимизации изменчивости внутри кластеров и максимизации изменчивости между кластерами.

   Каждое следующее наблюдение (K+1) относится к той группе, мера сходства с центром тяжести которого минимальна.

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

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

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

   4 техники анализа данных в Microsoft Excel

   Если вам по работе или учёбе приходится погружаться в океан цифр и искать в них подтверждение своих гипотез, вам определённо пригодятся эти техники работы в Microsoft Excel. Как их применять — показываем с помощью гифок.

   

   Тренер Учебного центра Softline с 2008 года.

   1. Сводные таблицы

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

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

   Как работать

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

   1. Откройте файл с таблицей, данные которой надо проанализировать.
   2. Выделите диапазон данных для анализа.
   3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
   4. Должно появиться диалоговое окно «Создание сводной таблицы».
   5. Настройте отображение данных, которые есть у вас в таблице.

   

   Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.

   

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

   

   Можно её детализировать, например, по странам. Переносим «Страны».

   

   Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

   

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

   Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

   Как работать

   1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
   2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
   3. Выделите диапазон данных для анализа.
   4. На вкладке «Вставка» есть кнопка 3D-карта.

   

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

   

   

   Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

   

   3. Лист прогнозов

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

   Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).

   Как работать

   1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
   2. Выделите два ряда данных.
   3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
   4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
   5. Выберите дату окончания прогноза.

   В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

   Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.

   На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

   

   4. Быстрый анализ

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

   Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

   Как работать

   1. Откройте таблицу с данными для анализа.
   2. Выделите нужный для анализа диапазон.
   3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

   

   В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.

   

   Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.

   

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