Оптимизация маршрута с помощью K-Means кластеризации в Python и Excel
Использование Python в Excel для K-Means кластеризации открывает новые возможности для анализа и оптимизации данных, таких как сложные географические маршруты. Этот метод позволяет группировать схожие точки и строить эффективные пути.
Нужен доступ к Python в Excel по подходящей подписке Microsoft 365; поддержка зависит от платформы и канала обновления. K-Means группирует точки по расстоянию до центров, но не определяет порядок посещения и не решает задачу кратчайшего маршрута.
При планировании посещений сортировка по широте и долготе может дать длинный маршрут. Кластеризация помогает выделить группы близких точек для дальнейшего планирования; полезность разбиения нужно оценивать по итоговой длине пути, а не только по виду диаграммы.
Пошаговая инструкция по кластеризации K-Means в Excel
- Подготовьте данные: Убедитесь, что ваши географические координаты (широта и долгота) находятся в числовых столбцах Latitude и Longitude таблицы Excel с именем Locations. Уберите пропуски, проверьте диапазоны широты от −90 до 90 и долготы от −180 до 180. Нужно не менее шести различных точек для примера с шестью кластерами.
- Запустите Python в Excel: Перейдите на вкладку «Формулы» и выберите «Вставить Python». В появившейся ячейке введите код для кластеризации.
from sklearn.cluster import KMeans import numpy as np import pandas as pd df = xl("Locations[#All]", headers=True).copy() coords = df[["Latitude", "Longitude"]].apply(pd.to_numeric, errors="raise") if coords.isna().any().any(): raise ValueError("Уберите пропуски в координатах") if not (coords["Latitude"].between(-90, 90).all() and coords["Longitude"].between(-180, 180).all()): raise ValueError("Координаты вне допустимого диапазона") if len(coords.drop_duplicates()) < 6: raise ValueError("Нужно не менее шести различных точек") lat = np.radians(coords["Latitude"].to_numpy()) lon = np.radians(coords["Longitude"].to_numpy()) xyz = np.column_stack((np.cos(lat) * np.cos(lon), np.cos(lat) * np.sin(lon), np.sin(lat))) kmeans = KMeans(n_clusters=6, random_state=0, n_init=10).fit(xyz) df["Cluster"] = kmeans.labels_ df - Экспериментируйте с количеством кластеров: Ключевой параметр —
n_clusters. Начните с пробного значения (например, 6), визуализируйте результат и сравните варианты. В примере координаты переведены в трёхмерные точки единичной сферы, чтобы убрать разрыв долготы на ±180°. Это группировка по евклидовым расстояниям в 3D, а не по дорогам или времени в пути. Параметр n_init=10 задан явно, чтобы не зависеть от смены значения по умолчанию в scikit-learn.
Пример кода Python для K-Means кластеризации в Excel. - Получите и проанализируйте результат: После выполнения кода в DataFrame добавится столбец с номером кластера (от 0 до 5). Оставьте
dfпоследней строкой кода, выберите возврат «Значения Excel» и выполните ячейку. Номера кластеров — метки, а не порядок посещения.
Локации, сгруппированные в шесть кластеров.
Исходный набор данных с добавленным столбцом номера кластера. - Оптимизируйте маршрут внутри кластеров: Используйте макрос или алгоритм (например, поиск ближайшего соседа) для построения приближённого пути внутри каждой группы, затем подберите порядок групп и точки переходов. Ближайший сосед не гарантирует оптимум. Сравните длину всего маршрута с исходным вариантом по одной и той же метрике расстояния; учитывайте начало, конец и необходимость возврата.
Результат и ключевые советы
Кластеризация может помочь сократить маршрут, но фиксированного выигрыша не гарантирует. Без исходных координат и алгоритма соединения нельзя воспроизвести конкретное расстояние; проверяйте результат на собственных данных.
Профессиональный совет: Хотя код можно писать и в строке формул, для работы с Python в Excel настоятельно рекомендуется использовать встроенный Python Editor — он предлагает подсветку синтаксиса, автодополнение и лучший обзор всего кода Для Windows панель редактора можно открыть сочетанием Ctrl+Alt+Shift+F2, если она доступна в вашей сборке.
Заключение
K-Means кластеризация через Python в Excel — это мощный инструмент не только для анализа клиентов или сегментации рынка, но и для решения практических оптимизационных задач, таких как планирование маршрутов. Экспериментируйте с параметрами и визуализируйте результаты для достижения наилучшего эффекта.