Ориентированный граф на диаграмме EXCEL
history 21 ноября 2017 г.
- Группы статей
- Диаграммы и графики
Ориентированный граф на диаграмме MS EXCEL построить можно, но это не просто для новичков. Рассмотрим процесс построения.
Граф — это математический объект, представляющий собой множество вершин графа, соединенных рёбрами . У ориентированного графа ребра имеют направление и изображаются стрелочками.
Для построения ориентированного графа на диаграмме MS EXCEL сделаем следующие шаги:
1. Построим на Точечной диаграмме вершины (или узлы) графа;
2. Присвоим каждой вершине индивидуальную подпись (в MS EXCEL это придется сделать с помощью макроса);
3. Зададим порядок соединения вершин ребрами. Например, ребро С будет соединять вершину 1 (начало) и вершину 2 (конец). Опишем соединения двумя способами: через непосредственное описание ребер и через маршруты;
4. Присвоим каждому ребру свою подпись на диаграмме.
Файл примера с ориентированным графом можно скачать внизу статьи.
Построение вершин
Создадим исходную таблицу для вершин. С каждой вершиной сопоставим произвольную точку с условными координатами (Х и Y). Координаты точек выберем лишь из соображений удобства отображения вершин на точечной диаграмме.
Выделите 2 столбца с координатами и создайте точечную диаграмму с маркерами (см. статью Основы построения диаграмм в MS EXCEL ), т.е. без соединительных линий. Путем настройки свойств маркеров их можно превратить в кружки без заливки.
Присваиваем вершинам подписи
В MS EXCEL 2013 индивидуальные подписи для точек можно присвоить стандартными способом. В MS EXCEL 2010 и более ранних — придется использовать макрос. Подробнее см. статью Подписи для точечной диаграммы в MS EXCEL . Для того чтобы иметь возможность переименовывать вершины, в файле примера создана кнопка Изменить подписи вершин .
Соединение вершин ребрами (отдельный ряд)
Создадим таблицу для построения ребер.
Для этого зададим названия для каждого ребра (пусть это будут буквы латинского алфавита), начальную и конечную вершину, которую соединяет ребро. С помощью простых формул =ВПР(G8;$A$8:$C$23;2;0) вычислим начальные и конечные координаты каждого ребра.
На точечной диаграмме для каждого ребра создадим отдельный ряд (см. Лист Граф1).
Чтобы не создавать вручную множество рядов, этот процесс можно автоматизировать через макрос. В файле примера создана кнопка Нарисовать ребра . Макрос работает, если нет ни одного ряда с ребрами.
Подписи ребер
Чтобы подписи располагались по центру ребра, потребуется создать еще 1 ряд. Ряд состоит из точек с координатами центра ребра. Координаты центра ребра вычисляются на основе координат начала и конца ребра (см. таблицу, столбец M и N). Подписи точкам присваиваются макросом аналогично тому, как мы делали для вершин. В итоге, как видно на диаграмме, название ребра будет выведено в его середине, в центре кружка.
Соединение вершин ребрами (пути)
Построение ребер было сделано выше через использование отдельного ряда для каждого ребра. Это трудоемко и потребовало написания макроса. Существует и другой путь. Можно определить пути обхода вершин, например 1; 4; 8; 14 (см. Лист Граф2). Это путь, который располагается в нижней части графа.
Задав путь, можно вычислить координаты ребер, которые соединены последовательно. Это существенно сократит количество рядов для построения ребер. Теперь каждый ряд будет представлять собой путь. При задании путей нужно избегать включения ребра в более чем 1 путь.
Путям для удобства присвоена произвольная последовательная нумерация. Четные и нечетные пути выделены цветом с помощью Условного форматирования .
Координаты начальных точек ребер вычисляются с помощью формулы = ВПР($F8;$A$8:$C$23;H$6;0)
ВНИМАНИЕ!
Построение ориентированного графа в этой статье приведено лишь с целью демонстрации такой возможности в MS EXCEL. Не ставилось целью сделать "удобную программу для пользователей" при построении графов. Это означает, что при изменении пользователем количества вершин / ребер графа в файле примера, переименовании листов и других изменений, макрос может потребовать дополнительной настройки.
Марк Мур. Встроенные приложения Excel
Встроенные приложения Excel появились в версии 2013 года. Они представлены двумя типами. Приложения данных – это встроенные веб-страницы, на которые можно наложить данные с листа Excel. Приложения задач – это панели в Excel, который предоставляют дополнительные функции, например, переводчик. Настоящая заметка посвящена приложениям Карты Bing и Социальный граф, и является кратким переводом книги
Moore, Mark. Mastering Excel: Excel Apps
Скачать заметку в формате Word или pdf, готовые примеры или пошаговые инструкции в формате Excel
Карты Bing
Начиная с Excel 2013 можно добавить интерактивные географические карты для визуализации данных. Они доступны на вкладке Вставка (рис. 1).
Рис. 1. Приложения Excel
Если значок не отображается на ленте, его можно получить бесплатно на сайте Майкрософт. Приложение также можно добавить через магазин. Пройдите по меню Вставка –> Надстройки. Кликните на значке Магазин. Введите Карты Bing в окне поиска. Добавьте приложение (рис. 2).
Рис. 2. Добавление Карт Bing через магазин Microsoft
Для работы с картами Bing вам потребуется учетная запись Майкрософт. Если она у вас уже есть, откройте файл Марк Мур. Встроенные приложения Excel. Пошаговые инструкции.xlsx. Перейдите на лист Карты Bing. Карта будет создана на основе данных о продажах (рис. 3).
Рис. 3. Продажи по продуктам
Выберите диапазон A3:D28. Перейдите на вкладку Вставка. Нажмите Карты Bing. Появится сообщение (рис. 4). Кликните Доверять этому приложению. В открывшемся окне Карты Bing, закройте сообщение, кликнув на крестик (1 на рис. 5). Нажмите Фильтры (2 на рис. 5).
Рис. 4. Подключение карты Bing
Рис. 5. Стартовая страница приложения Карты Bing
В правом нижнем углу кликните Выбрать данные. Поскольку вы уже выбрали данные, они отразятся в окне Выбор данных. Нажмите OK. На карте появятся круговые диаграммы для каждого города (рис. 6).
Рис. 6. Данные на карте
Эта карта интерактивная. Вы можете увеличить или уменьшить масштаб с помощью кнопок минус и плюс или колесом мыши. Если кликнуть на границе карты, можно менять размеры и положение окна. Если кликнуть внутри карты, можно изменять ее видимую часть внутри окна. Если кликнуть на круговую диаграмму, всплывет окно с данными (рис. 7).
Рис. 7. Если кликнуть на круговую диаграмму, всплывет окно с данными
Приложение поддерживает некоторые настройки (но не так много, как для родной диаграммы Excel). Нажмите на шестеренку в правом верхнем углу. Откроется панель Параметры (рис. 8). Щелкните стрелку, чтобы закрыть панель.
Рис. 8. Панель Параметры
Нажав на кнопку Фильтры, вы можете (а) скрыть некоторые города, (б) изменить диапазон данных (нажав кнопку Выбрать данные в нижней части панели). Кнопка Справка не работает; она просто отображает начальную панель (см. рис. 5). А вот, если нажать на стрелку в правом верхнем углу (рис. 9), появится дополнительное меню.
Рис. 9. Дополнительное меню
Получить поддержку. Вы попадете на форум Microsoft. Выделить. Будет выбрано окно диаграммы. После чего его можно переместить или изменить размер и пропорции. Перезагрузить. График вернется в исходное состояние (не очень понятно, что происходит). Просмотр исходного кода. Должен показать XML-код (у меня не получилось). Показывать как сохраненное изображение. Превращает интерактивную карту в изображение, которое нельзя изменить. Защита листа не влияет на карту. Можно опять вернуться к интерактивной карте, если повторна нажать на эту опцию. Сведения о безопасности. Не работает. Пытается открыть страницу в Интернете, которой не существует.
На листе Excel Карты Bing 2 создана карта для продаж одного продукта. Сам продукт можно выбрать в выпадающем меню. Данные переносятся с листа Карты Bing с помощью функции ВПР. Если используется только один ряд данных каждая точка выглядит несколько иначе. Размер кругов указывает относительное количество продаж (рис. 10).
Рис. 10. Карта для продаж одного продукта; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Социальный граф
Перейдите на лист Excel Социальный граф. Вы планируете отразить на графе структуру компании (рис. 11). Перейдите на вкладку Вставка и кликните Социальный граф. Появится исходный граф. В верхней части справа нажмите на значок данных. Если значок не виден, кликните мышкой возле заголовка. Это приложение не имеет окно для выбора данных, что немного сбивает с толку. После того как вы кликните на кнопке Выбрать данные, выделите на листе диапазон А2:В13, и нажмите Создать.
Рис. 11. Исходные данные для построения социального графа.
Нажмите на значок данных еще раз. Введите название диаграммы. Щелкните стрелку, чтобы закрыть панель. Изменить размер диаграммы, чтобы она отображала все департаменты. Для этого можно кликнуть на границе окна или нажать стрелку вверху окна и выбрать опцию Выделить (выпадающее меню такое же, как и в Картах Bing). Получившийся граф визуально более привлекательный, чем стандартные гистограммы Excel (рис. 12).
Рис. 12. Социальный граф
Существует несколько дополнительных настроек. Нажмите на значок шестеренки в правом верхнем углу. Вы увидите три вкладки: тип, тема и форма.
Рис. 13. Настройки графа
Одним из недостатков приложений является то, что они не работают без доступа в Интернет. Если соединение отсутствует в левом верхнем углу будет отражаться крестик в красном кружке. Единственный способ заставить приложение работать – это подключиться к Интернету. Если вы планируете использовать приложения для презентации, убедитесь, что у вас будет подключение к Интернету.
Получение дополнительных приложений
В магазине доступно много приложений. Некоторые из них бесплатны, другие – нет. Посмотрите, может быть, вам что-то понравится.