Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Python и AI в Excel

Оптимизация маршрута с помощью K-Means кластеризации в Python и Excel

forest_paw 13.11.2025 384

Оптимизация маршрута с помощью K-Means кластеризации в Python и Excel

Использование Python в Excel для K-Means кластеризации открывает новые возможности для анализа и оптимизации данных, таких как сложные географические маршруты. Этот метод позволяет группировать схожие точки и строить эффективные пути.

Нужен доступ к Python в Excel по подходящей подписке Microsoft 365; поддержка зависит от платформы и канала обновления. K-Means группирует точки по расстоянию до центров, но не определяет порядок посещения и не решает задачу кратчайшего маршрута.

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

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

Пошаговая инструкция по кластеризации K-Means в Excel

  1. Подготовьте данные: Убедитесь, что ваши географические координаты (широта и долгота) находятся в числовых столбцах Latitude и Longitude таблицы Excel с именем Locations. Уберите пропуски, проверьте диапазоны широты от −90 до 90 и долготы от −180 до 180. Нужно не менее шести различных точек для примера с шестью кластерами.
  2. Запустите 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
  3. Экспериментируйте с количеством кластеров: Ключевой параметр — n_clusters. Начните с пробного значения (например, 6), визуализируйте результат и сравните варианты. В примере координаты переведены в трёхмерные точки единичной сферы, чтобы убрать разрыв долготы на ±180°. Это группировка по евклидовым расстояниям в 3D, а не по дорогам или времени в пути. Параметр n_init=10 задан явно, чтобы не зависеть от смены значения по умолчанию в scikit-learn.
    Пример кода Python для K-Means кластеризации в Excel.
    Пример кода Python для K-Means кластеризации в Excel.
  4. Получите и проанализируйте результат: После выполнения кода в DataFrame добавится столбец с номером кластера (от 0 до 5). Оставьте df последней строкой кода, выберите возврат «Значения Excel» и выполните ячейку. Номера кластеров — метки, а не порядок посещения.
    Локации, сгруппированные в шесть кластеров.
    Локации, сгруппированные в шесть кластеров.
    Исходный набор данных с добавленным столбцом номера кластера.
    Исходный набор данных с добавленным столбцом номера кластера.
  5. Оптимизируйте маршрут внутри кластеров: Используйте макрос или алгоритм (например, поиск ближайшего соседа) для построения приближённого пути внутри каждой группы, затем подберите порядок групп и точки переходов. Ближайший сосед не гарантирует оптимум. Сравните длину всего маршрута с исходным вариантом по одной и той же метрике расстояния; учитывайте начало, конец и необходимость возврата.

Результат и ключевые советы

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

Сравнение исходного и оптимизированного маршрута после кластеризации.
Сравнение исходного и оптимизированного маршрута после кластеризации.

Профессиональный совет: Хотя код можно писать и в строке формул, для работы с Python в Excel настоятельно рекомендуется использовать встроенный Python Editor — он предлагает подсветку синтаксиса, автодополнение и лучший обзор всего кода Для Windows панель редактора можно открыть сочетанием Ctrl+Alt+Shift+F2, если она доступна в вашей сборке.

Заключение

K-Means кластеризация через Python в Excel — это мощный инструмент не только для анализа клиентов или сегментации рынка, но и для решения практических оптимизационных задач, таких как планирование маршрутов. Экспериментируйте с параметрами и визуализируйте результаты для достижения наилучшего эффекта.

Поделиться:

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