Создание двудольного графа в Excel: возможно ли это?

Двудольный граф — это инструмент для визуализации взаимосвязей между двумя различными наборами объектов или сущностей. Он широко используется, чтобы наглядно показать аффилиации или связи между разными группами данных. Часто возникает вопрос: можно ли создать такой граф прямо в Excel?

Ограничения стандартных возможностей Excel для двудольного графа

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

Многих интересует решение, при котором можно загружать данные (например, точки и связи между ними) из файла, и при этом получить граф, построенный средствами Excel — желательно без использования программирования, но допускается применение VBA-скриптов, если другого выхода нет.

Варианты реализации двудольного графа в Excel без программирования

Если говорить о нативных средствах Excel без кода, то можно рассмотреть альтернативные подходы, например создание наклонных диаграмм (slope charts), которые показывают отношения между двумя наборами данных через линии. Для этого существует множество руководств и видеоуроков.

Однако наклонная диаграмма — это не полноценный двудольный граф с визуализацией узлов и рёбер, а скорее упрощённая форма отображения связей.

Использование Python в Excel для создания двудольного графа

Современные версии Excel (например, Microsoft 365) поддерживают интеграцию с Python напрямую в ячейках. Это позволяет использовать мощные библиотеки Python — например, networkx для построения графов и matplotlib для их визуализации — прямо внутри Excel, что идеально подходит для задачи создания двудольного графа.

Пример кода для построения двудольного графа в Excel с помощью Python

1. Загружаем исходные данные в таблицу Excel, например, с двумя столбцами: UserID и GroupID — которые соответствуют двум множествам, между которыми нужно отобразить связи.
2. Создаём в Excel ячейку с формулой =PY(, которая открывает возможность вписать Python-код.
3. Вставляем следующий код (пример):

python

Загрузка данных из таблицы Excel

df = xl("Sheet1!A1:B8", headers=True)

import networkx as nx
from matplotlib.patches import Ellipse
import matplotlib.pyplot as plt

Сортируем пользователей и группы для удобства расположения

users = sorted(df[‘UserID’].unique())
groups = sorted(df[‘GroupID’].unique())

Создаём двудольный граф

B = nx.Graph()
B.add_nodes_from(users, bipartite=0)
B.add_nodes_from(groups, bipartite=1)
B.add_edges_from(df.itertuples(index=False, name=None))

Назначаем позиции: пользователи слева, группы справа

pos = {}
for i, user in enumerate(users):
pos[user] = (0, len(users)-1-i) # убывание по y от верхнего к нижнему
for i, group in enumerate(groups):
pos[group] = (1, len(groups)-1-i)

fig, ax = plt.subplots(figsize=(8,6))

Рисуем граф

nx.draw(
B, pos, with_labels=True, ax=ax,
node_color=["skyblue" if n in users else "lightgreen" for n in B.nodes()],
node_size=1200, edge_color="#888", font_size=10
)

Добавляем эллипсы вокруг узлов для выделения групп

user_y = [pos[u][1] for u in users]
group_y = [pos[g][1] for g in groups]
user_oval = Ellipse(xy=(0, sum(user_y)/len(user_y)), width=0.5, height=len(users)+0.5,
edgecolor="blue", facecolor="none", lw=2, ls="—")
group_oval = Ellipse(xy=(1, sum(group_y)/len(group_y)), width=0.5, height=len(groups)+0.5,
edgecolor="green", facecolor="none", lw=2, ls="—")
ax.add_patch(user_oval)
ax.add_patch(group_oval)

ax.set_xlim(-0.6, 1.6)
ax.set_ylim(-1, max(len(users), len(groups)))
ax.set_xticks([0,1])
ax.set_xticklabels([‘Users’,’Groups’])
ax.set_yticks([])
plt.show()

Результатом будет красивый двудольный граф с подписанными узлами, разделённый на две части — пользователей и группы.

Заключение

Хотя стандартные возможности Excel не предусматривают автоматизированное создание двудольных графов, современная интеграция с Python открывает широкие возможности для визуализации таких структур прямо в таблицах. Если вы хотите использовать только нативные средства Excel без кода, то подойдут наклонные диаграммы как частичный вариант решения. В противном случае, запуск Python кода внутри Excel с библиотеками networkx и matplotlib позволит получить удобный, наглядный и полностью управляемый двудольный граф.

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

Источник