Кластерний аналіз в Excel

Як зробити кластерний аналіз в Excel без готової команди: нормування ознак, ієрархічний метод через матрицю відстаней, k-середні формулами, Python in Excel і відкриті Orange та scikit-learn.

Кластерний аналіз розбиває об'єкти (клієнтів, магазини, регіони) на групи схожих за кількома ознаками. Готової команди «Кластерний аналіз» в Excel немає — ні на стрічці, ні в надбудові «Пакет аналізу». Невелику вибірку можна розкластеризувати формулами: побудувати матрицю відстаней (ієрархічний метод) або виконати кілька ітерацій методу k-середніх. Для великих даних зручніше Python in Excel або відкриті програми Orange і scikit-learn.

Що таке кластерний аналіз і коли він потрібен

Кожен об'єкт описується числовими ознаками (наприклад, середній чек і частота покупок). Об'єкти, що лежать «близько» в просторі ознак, потрапляють в один кластер. Відстань найчастіше рахують евклідовою: для двох ознак — √((x₂ − x₁)² + (y₂ − y₁)²). Застосування — сегментація клієнтів в економіці й маркетингу, класифікація в біології, медицині, соціології.

Перед розрахунком ознаки треба нормувати, інакше ознака з великими числами (гривні) «переважить» ознаку з малими (кількість покупок). В Excel це робить функція STANDARDIZE: =STANDARDIZE(B2,AVERAGE(B$2:B$21),STDEV.S(B$2:B$21)).

Спосіб 1: ієрархічний метод (матриця відстаней)

Підходить для 5–30 об'єктів і показує сам принцип.

  1. Розмістіть об'єкти в рядках, нормовані ознаки — у стовпцях (наприклад, x у B, y у C).
  2. Побудуйте квадратну матрицю відстаней: у клітинці перетину об'єктів i та j — =SQRT((INDEX($B$2:$B$6,i)-INDEX($B$2:$B$6,j))^2+(INDEX($C$2:$C$6,i)-INDEX($C$2:$C$6,j))^2), де i, j — номери рядків (їх зручно взяти з заголовків матриці).
  3. Знайдіть найменшу ненульову відстань (MIN по матриці без діагоналі) — ці два об'єкти утворюють перший кластер.
  4. Побудуйте нову матрицю, де пара — один елемент. Відстань від кластера до інших об'єктів беруть за обраним правилом: найменшу з двох (метод найближчого сусіда), найбільшу (далекого сусіда) або середню.
  5. Повторюйте, доки не лишиться потрібна кількість груп.

Порядок об'єднання показує дендрограма — її в Excel доводиться малювати вручну, тому для звіту зручніше відкриті інструменти нижче.

Спосіб 2: метод k-середніх формулами

Метод k-середніх (k-means) працює з будь-якою кількістю об'єктів, але кількість кластерів k задаєте ви.

  1. Виберіть k (наприклад, 3) і задайте початкові центри: значення трьох довільних об'єктів.
  2. Для кожного об'єкта порахуйте відстань до кожного центру в окремих стовпцях (=SQRT((B2-$H$2)^2+(C2-$I$2)^2) і т. д.).
  3. Номер кластера — центр з найменшою відстанню: =MATCH(MIN(E2:G2),E2:G2,0).
  4. Перерахуйте центри як середні об'єктів кожного кластера: =AVERAGEIF($J$2:$J$21,1,B$2:B$21) для x першого кластера і аналогічно для решти.
  5. Скопіюйте нові центри на місце старих (як значення) і повторіть кроки 2–4, доки номери кластерів перестануть змінюватися — зазвичай 3–10 ітерацій.

Результат залежить від початкових центрів: запустіть розрахунок кілька разів з різними стартовими об'єктами і порівняйте суму відстаней до центрів. Опис алгоритму і його обмежень — у документації scikit-learn «Clustering».

Спосіб 3: Python in Excel

У Microsoft 365 у клітинку можна ввести =PY( або вибрати «Формули» → «Вставити Python»: код виконується в хмарі Microsoft, а дані аркуша читаються функцією xl(), наприклад xl("B1:C21", headers=True). Функція доступна в Excel для Windows, Mac і вебу за відповідної передплати Microsoft 365 — умови в довідці Microsoft «Introduction to Python in Excel». Python in Excel використовує бібліотеки дистрибутиву Anaconda; якщо в наборі є scikit-learn, кластеризація k-середніх займає кілька рядків:

from sklearn.cluster import KMeans
df = xl("B1:C21", headers=True)
KMeans(n_clusters=3, n_init=10, random_state=0).fit_predict(df)

Не завантажуйте в хмарні обчислення персональні дані клієнтів без дозволу організації — на робочому ПК такі рішення приймає адміністратор.

Відкриті програми для кластерного аналізу

  • Orange Data Mining — відкрита програма з візуальними схемами: віджети «File» (відкриває XLSX), «Distances», «Hierarchical Clustering» (з дендрограмою) і «k-Means» без коду.
  • scikit-learn — бібліотека Python з k-середніми, ієрархічною кластеризацією, DBSCAN та іншими методами.
  • LibreOffice Calc — ті самі формули, що й в Excel (матриця відстаней, k-середні), якщо Microsoft Office немає.

Часті питання

Чи є кластерний аналіз у «Пакеті аналізу» Excel? (кластерный анализ в excel)

Ні. Надбудова «Пакет аналізу» містить описову статистику, кореляцію, регресію, дисперсійний аналіз, але не кластеризацію. Кластери рахують формулами, через Python in Excel або сторонніми інструментами.

Скільки кластерів вибрати?

Орієнтуйтеся на задачу (скільки сегментів реально обслуговувати) і на «метод ліктя»: порахуйте суму квадратів відстаней до центрів для k = 2…8 і виберіть точку, після якої вона зменшується повільно.

Чим ієрархічний метод відрізняється від k-середніх?

Ієрархічний не потребує заздалегідь заданого k і дає дендрограму, але повільний на великих даних. K-середні швидкі, проте кількість кластерів і стартові центри задаєте ви.

Чому результати змінюються від запуску до запуску?

K-середні залежать від початкових центрів. Зафіксуйте старт (у scikit-learn — random_state) і запускайте кілька разів, вибираючи варіант з найменшою сумою відстаней.

Схожі інструкції