Работа с графами в excel - Word и Excel - помощь в работе с программами

Ориентированный граф на диаграмме MS EXCEL построить можно, но это не просто для новичков. Рассмотрим процесс построения.

Граф — это математический объект, представляющий собой множество

вершин

графа, соединенных

рёбрами

. У ориентированного графа ребра имеют направление и изображаются стрелочками.

Для построения ориентированного графа на диаграмме MS EXCEL сделаем следующие шаги:

1. Построим на

Точечной диаграмме

вершины (или узлы) графа;

Присвоим каждой вершине индивидуальную подпись

(в 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. Не ставилось целью сделать «удобную программу для пользователей» при построении графов. Это означает, что при изменении пользователем количества вершин / ребер графа в

файле примера,

переименовании листов и других изменений, макрос может потребовать дополнительной настройки.

Источник

Ранее было рассказано, как нарисовать граф с помощью пакета Graphviz. А что делать, если имеются некоторые данные в электронной таблице и необходимо построить граф?

Рассмотрим две задачи. Первая задача будет построение иерархии для последующего анализа. В файле примеров это будет вкладка test1:

Данными у нас заполнены первые три колонки. Дальше мы делаем три колонки с данными без пропусков, для чего используем функции ЕСЛИ и ЕПУСТО. Дальше собираем текст, используя функцию СЦЕПИТЬ. Обратите внимание, чтобы в результирующей строке получилась кавычка, необходимо её дублировать:

=СЦЕПИТЬ(«»»»;E2;»»»->»»»;F2;»»»»)

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

digraph test{
   node [shape = box];
"Некая цель"->"Альтернатива 1"
"Некая цель"->"Альтернатива 2"
"Некая цель"->"Альтернатива 3"
"Некая цель"->"Альтернатива 4"
"Альтернатива 1"->"Критерий 1"
"Альтернатива 1"->"Критерий 2"
"Альтернатива 1"->"Критерий 3"
"Альтернатива 1"->"Критерий 4"
"Альтернатива 1"->"Критерий 5"
"Альтернатива 2"->"Критерий 1"
"Альтернатива 2"->"Критерий 2"
"Альтернатива 2"->"Критерий 3"
"Альтернатива 2"->"Критерий 4"
"Альтернатива 2"->"Критерий 5"
"Альтернатива 3"->"Критерий 1"
"Альтернатива 3"->"Критерий 2"
"Альтернатива 3"->"Критерий 3"
"Альтернатива 3"->"Критерий 4"
"Альтернатива 3"->"Критерий 5"
"Альтернатива 4"->"Критерий 1"
"Альтернатива 4"->"Критерий 2"
"Альтернатива 4"->"Критерий 3"
"Альтернатива 4"->"Критерий 4"
"Альтернатива 4"->"Критерий 5"
}

test1

Второй задачей будет построение графа взаимных долгов на основании табличных данных

В результате получаем граф:

digraph test2{
"Вася"->"Петя"[label=100]
"Вася"->"Коля"[label=200]
"Коля"->"Петя"[label=300]
}

test2

В данной заметке рассмотрены самые простые примеры, однако, используя эту идею можно при формировании команд на языке dot в excel использовать условное форматирование, например, задавая форму узлов, стрелок, цвета и т.д.

Источник

history 21 ноября 2017 г.

Диаграммы и графики

Граф — это математический объект, представляющий собой множество вершин графа, соединенных рёбрами . У ориентированного графа ребра имеют направление и изображаются стрелочками.

Для построения ориентированного графа на диаграмме MS EXCEL сделаем следующие шаги:

1. Построим на Точечной диаграмме вершины (или узлы) графа;

2. Присвоим каждой вершине индивидуальную подпись (в MS EXCEL это придется сделать с помощью макроса);

4. Присвоим каждому ребру свою подпись на диаграмме.

Файл примера с ориентированным графом можно скачать внизу статьи.

Построение вершин

Выделите 2 столбца с координатами и создайте точечную диаграмму с маркерами (см. статью Основы построения диаграмм в MS EXCEL ), т.е. без соединительных линий. Путем настройки свойств маркеров их можно превратить в кружки без заливки.

Присваиваем вершинам подписи

В MS EXCEL 2013 индивидуальные подписи для точек можно присвоить стандартными способом. В MS EXCEL 2010 и более ранних — придется использовать макрос. Подробнее см. статью Подписи для точечной диаграммы в MS EXCEL . Для того чтобы иметь возможность переименовывать вершины, в файле примера создана кнопка Изменить подписи вершин .

Соединение вершин ребрами (отдельный ряд)

Создадим таблицу для построения ребер.

Для этого зададим названия для каждого ребра (пусть это будут буквы латинского алфавита), начальную и конечную вершину, которую соединяет ребро. С помощью простых формул =ВПР(G8;$A$8:$C$23;2;0) вычислим начальные и конечные координаты каждого ребра.

На точечной диаграмме для каждого ребра создадим отдельный ряд (см. Лист Граф1).

Чтобы не создавать вручную множество рядов, этот процесс можно автоматизировать через макрос. В файле примера создана кнопка Нарисовать ребра . Макрос работает, если нет ни одного ряда с ребрами.

Подписи ребер

Соединение вершин ребрами (пути)

Путям для удобства присвоена произвольная последовательная нумерация. Четные и нечетные пути выделены цветом с помощью Условного форматирования .

Координаты начальных точек ребер вычисляются с помощью формулы = ВПР($F8;$A$8:$C$23;H$6;0)

ВНИМАНИЕ!

Построение ориентированного графа в этой статье приведено лишь с целью демонстрации такой возможности в MS EXCEL. Не ставилось целью сделать «удобную программу для пользователей» при построении графов. Это означает, что при изменении пользователем количества вершин / ребер графа в файле примера, переименовании листов и других изменений, макрос может потребовать дополнительной настройки.

Построение графов для чайников: пошаговый гайд

Ранее мы публиковали пост, где с помощью графов проводили анализ сообществ в Точках кипения из разных городов России. Теперь хотим рассказать, как строить такие графы и проводить их анализ.

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

1. Выбор гипотезы

Если попытаться визуализировать хотя бы что-то, бездумно загрузив данные в программу построения графов, результат вас не порадует. Поэтому сначала сформулируйте для себя, что хотите узнать с помощью графов, и придумайте жизнеспособную гипотезу.

Для этого разберитесь, какие данные у вас уже есть, что из них можно представить «объектами», а что – «связями» между ними. Обычно объектов значительно меньше, чем связей — можно таким образом проверять себя.

Наш тестовый пример мы готовили совместно с командой Точки кипения из Томска. Соответственно, все данные для анализа по мероприятиям и их участникам у нас будут именно оттуда. Нам стало интересно, сформировалось ли из участников этих мероприятий сообщество и как оно выглядит с точки зрения принадлежности участников к бизнесу, университетам и власти.

Мы предположили, что люди, которые посетили одно и то же мероприятие, связаны друг с другом. Причем чем чаще они присутствовали на мероприятиях совместно, тем сильнее связь.
Во втором случае мы решили узнать, как соотносится принадлежность участников к одному из «нетов» (наших ключевых направлений) с интересующими их сквозными технологиями. Равномерно ли распределение, есть ли «горячие темы»? Для этого анализа мы взяли данные по участникам мероприятий из 200 томских технологических компаний.

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

2. Подготовка данных

Теперь, когда вы определились с тем, что хотите узнать, возьмите весь массив данных, посмотрите, какая информация об «объектах» хранится, выкиньте все лишнее и добавьте недостающее. Если данные распределены по нескольким источникам, предварительно соберите все в одну кучу, убрав дубли.

Поясню на примере. У нас были данные об участниках 650 мероприятий. Это, условно говоря, 650 эксель-таблиц с

23000 записей в них, содержащих поля «Leader ID», «Должность», «Организация». Для постройки графа достаточно одного уникального идентификатора (тут, к счастью, такой есть – это Leader ID) и признака, привязывающего каждого участника к одной из трех рассматриваемых сфер: власти, бизнесу или университетам. И этой информации у нас еще нет.

Чтобы получить ее, можно пойти напролом: в каждом из 650 файлов убрать лишние столбцы и добавить новое поле, заполнить его значениями для каждой строки, например: «1» для власти, «2» для бизнеса и «3» для образования и науки. А можно сначала объединить все 650 файлов в один большой список, убрать дубли и только после этого добавлять новые значения. В первом случае такая работа займет 1-2 месяца. Во втором — 1-2 недели.

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

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

Файл вершин в нашем случае содержал два столбца: Id — номер вершины и Label — тип. Файл ребер содержал также два столбца: Source — id начальной вершины, Target — id конечной вершины.

Как превратить данные о том, что участники 1, 2, 5 и 23 посетили одно мероприятие, в ребра? Необходимо создать шесть строк и отметить связь каждого участника с каждым: 1 и 2, 1 и 5, 1 и 23, 2 и 5, 2 и 23, 5 и 23.

Во втором нашем примере таблицы выглядели так:

В качестве вершин перечислены как рынки, так и сквозные технологии. Если, скажем, представитель компании, относящейся к рынку «Технет» (ID=4), посетил мероприятие по теме «Большие данные и ИИ» (ID=17), в таблицу ребер заносим ребро (строку), соединяющее эти вершины (Source=4, Target=17).

Этап подготовки данных – это самая трудоемкая часть процесса, но наберитесь терпения.

3. Визуализация графа

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

Скриншоты я буду делать со второго проекта, в котором было небольшое число вершин и связей, чтобы все было максимально понятно.

Первым делом нам надо загрузить таблицы с вершинами и ребрами. Для этого выбираем пункт «Импортировать из CSV» из меню раздела «Лаборатория данных».

Сначала грузим файл с вершинами. На первом экране формы указываем, что импортируем именно вершины, и проверяем, чтобы программа правильно определила кодировку подписей.

На третьей форме «Отчет об импорте» важно указать тип графа. У нас он не ориентирован.

Похожим образом грузим ребра. В первом окне указываем, что это файл с ребрами, и также проверяем кодировку.

Важный момент ждет нас в третьем окне «Отчет об импорте». Тут важно указать не только то, что граф не ориентирован, но и подгрузить ребра в то же рабочее пространство, что и вершины. Поэтому выбираем пункт «Append to existing workplace».

В результате перед нами предстанет граф примерно вот в таком виде (закладка «Обработка»):

Итак, ребра имеют разную толщину в зависимости от количества связей между вершинами. Посмотреть, какой вес стал у каждого ребра, можно на закладке «Лаборатория данных» в свойствах ребер в столбце Weight.

Что здесь плохо: все вершины имеют один размер и расположены абсолютно произвольно. На закладке «Обработка» мы это исправим. Сначала в верхнем левом окне выбираем Nodes и жмем на пиктограммку с кругами («Размер»). Далее выбираем пункт Ranking — он позволяет задать размер вершины в зависимости от какого-либо параметра. У нас есть возможность выбрать только один параметр — Degree (степень), который показывает, сколько ребер выходят из вершины. Выбираем минимальный и максимальный размер кружочка и жмем кнопку «Применить». Здесь же, если выбрать другие пиктограммки, можно настроить цвет маркера вершины и цвет ребер. Теперь граф уже более нагляден.

Следующее, что нужно сделать, — распутать граф. Это можно сделать вручную, двигая вершины, а можно использовать алгоритмы укладки, которые реализованы в Gephi.

Чего мы добиваемся правильной укладкой? Максимальной наглядности. Чем меньше на графе наложений вершин и ребер, чем меньше пересечений ребер, тем лучше. Также неплохо было бы, чтобы смежные вершины были расположены поближе друг к другу, а несмежные —подальше друг от друга. Ну и все было распределено по видимой области, а не сжато в одну кучу.

Как это сделать в Gephi? Левое нижнее окно «Укладка» содержит самые популярные алгоритмы укладки, построенные на силовых аналогиях. Представьте, что вершины — это заряженные шарики, который отталкиваются друг от друга, но при этом некоторые скреплены чем-то, похожим на пружинки. Если задать соответствующие силы и «отпустить» граф, вершины разбегутся на максимально допустимые пружинками расстояния.

Наиболее равномерную картину дает алгоритм Фрюхтермана и Рейнгольда. Выберите Fruchterman Reingold из выпадающего меню и задайте размер области построения. Нажмите кнопку «Исполнить». Получится что-то вроде этого:

Можно помочь алгоритму и, не останавливая его, поперетаскивать некоторые вершины, стараясь распутать граф. Но помните, что здесь нет кнопки «Отменить», вернуться к прежнему расположению вершин уже не удастся. Поэтому сохраняйте новые версии проекта перед каждым рискованным изменением.

Еще один полезный алгоритм — Force Atlas 2. Он представляет граф в виде металлических колец, связанных между собой пружинами. Деформированные пружины приводят систему в движение, она колеблется и в конце концов принимает устойчивое положение. Этот алгоритм хорош для визуализаций, подчеркивающих структуру группы и выделяющих подмножества с высокой степенью взаимодействия.

Этот алгоритм имеет большое количество настроек. Рассмотрим наиболее важные. «Запрет перекрытия» запрещает вершинам перекрывать друг друга. Разреженность увеличивает расстояние между вершинами, делая граф более читаемым. Также более воздушным граф делает уменьшение влияния весов ребер на взаимное расположение вершин.

Поигравшись с настройками, получим такой граф:

Получив граф в том виде, который вас устраивает, переходите к финальной обработке. Это закладка «Просмотр». Здесь мы можем задать, например, отрисовку графа кривыми ребрами, которая минимизирует наложение вершин на чужие ребра. Можем включить подписи вершин, задав размер и цвет шрифта. Наконец, поменять фон подложки. Например, так:

Для того чтобы сохранить получившийся рисунок, нажмите на надпись «Экспорт SVG/PDF/PNG в левом нижнем углу окна. Также отдельно не забудьте сохранить сам проект через верхнее меню «Файл» — «Сохранить проект».

В нашем случае принципиально было выделить взаимосвязь сквозных технологий с рынками НТИ, для чего мы вручную выстроили все рынки в одну линию в центре и разместили все остальное сверху и снизу. Получился вот такой граф. Все-таки для решения конкретных задач без ручной расстановки вершин обойтись не удалось.

Вы, наверное, думаете, как нам удалось раскрасить вершины в разный цвет? Есть одна хитрость. Можно перейти в закладку «Лаборатория данных», создать там новый столбец в вершинах, назвав его «Market». И заполнить для каждой вершины значениями: 1 если это рынок НТИ, 0 — если сквозная технология. Затем достаточно перейти в «Обработку», выбрать пиктограммку в виде палитры, Nodes — Partition, а в качестве разделителя — наш новый атрибут Market.

Для более сложных построений, когда требуется выделить кластеры и закрасить их разными цветами, в Gephi используется богатый арсенал статистических расчетов, результаты которых можно использовать для раздельной окраски. Находятся эти расчеты в правом столбце вкладки «Обработка».

Например, нажав кнопку «Запуск» возле расчета «Модулярность», вы узнаете оценку уровня кластеризации вашего графа. Если после этого выставить цвет вершин в зависимости от Modularity Class, появится симпатичная картинка наподобие такой:

Если вы хотите еще больше узнать о возможностях Gephi, стоит почитать руководство по работе с программой от Мартина Гранджина http://www.martingrandjean.ch/gephi-introduction/.

4. Анализ результата

Итак, вы получили итоговую визуализацию графа. Что она вам дает? Во-первых, это красиво, ее можно вставить в презентацию, показать знакомым или сделать заставкой на рабочем столе. Во-вторых, по ней вы можете понять, насколько сложной и многокластерной структурой является рассматриваемая вами предметная область. В-третьих, обратите внимание на самые крупные вершины и на самые жирные связи. Это особенные элементы, на которых все держится.
Так, построив граф экспертного сообщества, посещающего мероприятия в Точке кипения, мы сразу обнаружили участников, которые с наибольшей вероятностью выполняют роль суперконнекторов. Они являлись «вершинами», через которые кластеры объединялись в единое целое. А во втором случае мы увидели, как выглядит концентрация специалистов из томских компаний с точки зрения их принадлежности к рынку и сквозной цифровой технологии, на которую они делают ставку. Это косвенно говорит об уровне технологических компетенций и экспертизы региона.

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

Источник

3 Axis Graphs in Excel are the graphs that have three axis. The need for a three-axis arises when the scale of the values is very different. For example, you are given an atom and you want to make a graph between its diameter, melting point, and colloidal nature. If they are plotted on the same scale then the diameter values will be represented as a single line as the melting point will be very value than its diameter. To resolve, this problem you need to have 3 axis for plotting all three different scale values. In this article, we will learn how to create a three-axis graph in excel.

Creating a 3 axis graph

By default, excel can make at most two axis in the graph. There is no way to make a three-axis graph in excel. The three axis graph which we will make is by generating a fake third axis from another graph. Given a data set, of date and corresponding three values Temperature, Pressure, and Volume. Make a three-axis graph in excel.

To create a 3 axis graph follow the following steps:

Step 1: Select table B3:E12. Then go to Insert Tab, and select the Scatter with Chart Lines and Marker Chart.

Step 2: A Line chart with a primary axis will be created.

Step 3: The primary axis of the chart will be Temperature, the secondary axis will be Pressure and the third axis will be Volume. So, to create the third axis duplicate this chart by pressing Ctrl + D while selecting graph1. Let’s name chart1 as graph1 and chart2 as graph2.Graph1 will contain the Primary and the secondary axis. The third axis will be created by graph2.

Step 4: As graph1 contains Volume which will be the third axis, you need to delete Volume from graph1. Double click on the red line and press Delete.

Step 5: You have to make Pressure as the secondary axis in graph1. Double click, on the blue line. A Format Data Series dialogue box appears. In the series Option, select the blue line as the Secondary Axis.

Step 6: Now, you need to remove the Chart Title of graph1. Double click on the chart title of graph1. Format Chart Title dialogue box appears. Go to Text options. In the Text Fill, select No Fill.

Step 7: You need to make graph1 transparent and with no border so that the overlapping could be done efficiently. Double click on the chart area of graph1. Format Chart Area dialogue box appears. In Chart Options, under the Fill section select No fill, and under the Border section select No line. The design of graph1 is over now.

Step 8: Now, you need to remove all the gridlines of the entire worksheet. Go to View Tab, and uncheck the box Gridlines.

Step 9: You need to repeat the same steps with graph2 also. In graph2, we need only the third axis i.e. Volume. So, we will remove the rest of the two lines from graph2. Double click on the Temp line and press Delete. Again, double-click on the Pressure line and press Delete.

Step 10: You, need to remove the Chart title from graph2 also. Double click on the Chart title in graph2.

Step 11: Format Chart Title dialogue box appears. In the Text Options, under Text Fill select No Fill.

Step 12: You need to make graph2 transparent and with no border so that the overlapping could be done efficiently. Double click on the chart area of graph2. Format Chart Area dialogue box appears. In Chart Options, under the Fill section select No fill, and under the Border section select No line. The design of graph2 is over now.

Step 13: Both graphs look like this now.

Step 14: You need to add an axis title to every axis. Select graph1, and click on the plus button. Check the box, Axis Titles.

Step 15: Axis title will appear in both the axis of graph1.

Step 16: Now, you have to edit and design the data labels and axis titles on each axis. Double click, the Axis title on the secondary axis. Rename it to Pressure, color to blue, and size as per your comfortability.

Step 17: Double click on the data labels in graph1. Set color to blue and size accordingly.

Step 18: Again, double click on the data label of the secondary axis in graph1. Format Axis dialogue box appears. In Axis Options, under Line sections select the color of the axis line and its width. For example, color to blue and width to 2.5pt.

Step 19: Repeat steps 16, 17, and 18 to design the primary axis of graph1. Set color to grey and width accordingly.

Step 20: We see a problem that tick marks are not appearing in the primary axis of graph1.

Step 21: Double click on the data label of the primary axis of graph1. Format Axis dialogue box appears. In Axis Options, under Tick Marks, select Major Type as Outside.

Step 22: Repeat steps 16, 17, and 18 to design the primary axis of graph2. Set color to red and width accordingly.

Step 23: Both the graphs are ready. Now, you need to remove the dates from one of the graphs, so that they do not overlap. Removing the dates of graph2.

Step 24: We know that dates can be represented in number format in excel. We see that the largest date in the given data set is 19-Mar whose number represented in excel is 44639. The dates value of graph2 are to be set such that they align with the dates of graph1, this could be achieved by the hit and trial method, checking the different maximum and minimum values, and seeing the best-suited value. Double click on the date in graph2. Format Axis dialogue box appears. In the Axis options, change the minimum from 44630 to 44628 and the maximum from 44640 to 44639.

Step 25: By decreasing the minimum value, a space will be created between the data line and the start of the graph, this is necessary to avoid overlapping when placing graph2 over graph1. The maximum value is set as the largest date in the given data set, for example, 19-march in this case, this is necessary so that the graph lines of graph1 are aligned with the graph lines of graph2.

Step 26: You, can see the horizontal and vertical lines in graph2. These need to be removed for better clarity, after overlapping the graph. Double click on the vertical lines or horizontal lines and press Delete.

Step 27: The only work left in graph2 is to remove the dates in the major axis of graph2. Double click on the date axis, Format Axis dialogue box appears, Go to Text Options, under Text Fill, select No fill and Text Outline as No fill.

Step 28: Both the graphs are ready now, to overlap each other. Placing graph2 over graph1. The 3 axis graph is ready.

Step 29: The 3 axis graph is ready, but we see that the lines are overlapping each other which does not give a clear look at the data. To avoid this, you can change the minimum and maximum of the data labels, so that the lines get separated. This can be achieved with hit and trial, try putting different values of minimum and maximum in each axis label and take the best suited. Double click on the data label of graph2.

Step 30: A Format Axis dialogue box appears. Under the Axis Options, set the minimum and maximum with hit and trial. For example, set the minimum to 0 and maximum to 20.

Step 31: Similarly Double click, on the secondary axis of graph2. Format Axis dialogue box appears. Under the Axis Options, set the minimum and maximum with hit and trial. For example, set the minimum to 100 and maximum to 260.

Step 32: Select both the charts with Ctrl + mouse click. Now, go to Shape Format Tab, and under Arrange section select Group. This helps selecting both graphs as a single entity.

Step 33: Your three-axis graph is ready.

Источник

Содержание

Процедура построения сетевого графика
- Этап 1: построение структуры таблицы
- Этап 2: создание шкалы времени
- Этап 3: заполнение данными
- Этап 4: Условное форматирование
Вопросы и ответы

Сетевой график – это таблица, предназначенная для составления плана проекта и контроля за его выполнением. Для её профессионального построения существуют специализированные приложения, например MS Project. Но для небольших предприятий и тем более личных хозяйственных нужд нет смысла покупать специализированное программное обеспечение и тратить море времени на обучение тонкостям работы в нем. С построением сетевого графика вполне успешно справляется табличный процессор Excel, который установлен у большинства пользователей. Давайте выясним, как выполнить в этой программе указанную выше задачу.

Читайте также: Как сделать диаграмму Ганта в Экселе

Процедура построения сетевого графика

Построить сетевой график в Экселе можно при помощи диаграммы Ганта. Имея необходимые знания можно составить таблицу любой сложности, начиная от графика дежурства сторожей и заканчивая сложными многоуровневыми проектами. Взглянем на алгоритм выполнения данной задачи, составив простой сетевой график.

Этап 1: построение структуры таблицы

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

Итак, вписываем наименования столбцов в будущую шапку таблицы. В нашем примере названия колонок будут следующими:
- № п/п;
- Название мероприятия;
- Ответственное лицо;
- Дата начала;
- Продолжительность в днях;
- Примечание.
Если названия не вместятся в ячейку, то раздвигаем её границы.

Отмечаем элементы шапки и клацаем по области выделения. В списке отмечаем значение «Формат ячеек…».

В новом окне передвигаемся в раздел «Выравнивание». В области «По горизонтали» ставим переключатель в положение «По центру». В группе «Отображение» ставим галочку около пункта «Переносить по словам». Это нам пригодится позже, когда мы будет оптимизировать таблицу в целях экономии места на листе, сдвигая границы его элементов.

Перемещаемся во вкладку окна форматирования «Шрифт». В блоке настроек «Начертание» устанавливаем флажок около параметра «Полужирный». Это нужно сделать, чтобы наименования столбцов выделялись среди другой информации. Теперь жмем по кнопке «OK», чтобы сохранить введенные изменения форматирования.

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

Расположившись во вкладке «Главная», клацаем по треугольнику справа от пиктограммы «Границы» в блоке «Шрифт» на ленте. Открывается перечень выбора типа границ. Останавливаем свой выбор на позиции «Все границы».

На этом создание заготовки таблицы можно считать оконченным.

Урок: Форматирование таблиц в Экселе

Этап 2: создание шкалы времени

Теперь нужно создать основную часть нашего сетевого графика – шкалу времени. Она будет представлять собой набор столбцов, каждый из которых соответствует одному периоду проекта. Чаще всего один период равен одному дню, но бывают случаи, когда величину периода исчисляют в неделях, месяцах, кварталах и даже годах.

В нашем примере используем вариант, когда один период равен одному дню. Сделаем шкалу времени на 30 дней.

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

После этого клацаем по пиктограмме «Граница» в режиме «Все границы».

Вслед за тем, как границы очерчены, внесем даты в шкалу времени. Допустим, мы будем контролировать проект с периодом действия с 1 по 30 июня 2017 года. В этом случае наименование колонок шкалы времени нужно установить в соответствии с указанным промежутком времени. Конечно, вписывать вручную все даты довольно утомительно, поэтому воспользуемся инструментом автозаполнения, который называется «Прогрессия».
В первый объект шапки шакалы времени вставляем дату «01.06.2017». Передвигаемся во вкладку «Главная» и клацаем по значку «Заполнить». Открывается дополнительное меню, где нужно выбрать пункт «Прогрессия…».

Происходит активация окна «Прогрессия». В группе «Расположение» должно быть отмечено значение «По строкам», так как мы будем заполнять шапку, представленную в виде строки. В группе «Тип» должен быть отмечен параметр «Даты». В блоке «Единицы» следует поставить переключатель около позиции «День». В области «Шаг» должно находиться цифровое выражение «1». В области «Предельное значение» указываем дату 30.06.2017. Жмем на «OK».

Массив шапки будет заполнен последовательными датами в пределе от 1 по 30 июня 2017 года. Но для сетевого графика мы имеем слишком широкие ячейки, что негативно влияет на компактность таблицы, а, значит, и на её наглядность. Поэтому проведем ряд манипуляций для оптимизации таблицы.
Выделяем шапку шкалы времени. Клацаем по выделенному фрагменту. В списке останавливаемся на пункте «Формат ячеек».

В открывшемся окне форматирования передвигаемся в раздел «Выравнивание». В области «Ориентация» устанавливаем значение «90 градусов», либо передвигаем курсором элемент «Надпись» вверх. Клацаем по кнопке «OK».

После этого наименования столбцов в виде дат изменили свою ориентацию с горизонтальной на вертикальную. Но из-за того, что ячейки свой размер не поменяли, названия стали нечитаемыми, так как по вертикали не вписываются в обозначенные элементы листа. Чтобы изменить это положение вещей, опять выделяем содержимое шапки. Клацаем по пиктограмме «Формат», находящейся в блоке «Ячейки». В перечне останавливаемся на варианте «Автоподбор высоты строки».

После описанного действия наименования столбцов по высоте вписываются в границы ячеек, но по ширине ячейки не стали компактнее. Снова выделяем диапазон шапки шкалы времени и клацаем по кнопке «Формат». На этот раз в списке выбираем вариант «Автоподбор ширины столбца».

Теперь таблица приобрела компактность, а элементы сетки приняли квадратную форму.

Этап 3: заполнение данными

Далее нужно заполнить таблицу данными.

Возвращаемся к началу таблицы и заполняем колонку «Название мероприятия» наименованиями задач, которые планируется выполнить в ходе реализации проекта. А в следующей колонке вносим фамилии ответственных лиц, которые будут отвечать за выполнение работы по конкретному мероприятию.

После этого следует заполнить колонку «№ п/п». Если мероприятий немного, то это можно сделать, вручную вбив числа. Но если планируется выполнение многих задач, то рациональнее будет прибегнуть к автозаполнению. Для этого ставим в первый элемент столбца число «1». Курсор направляем на нижний правый край элемента, дождавшись момента, когда он преобразуется в крестик. Одномоментно зажимаем клавишу Ctrl и левую кнопку мышки, тянем крестик вниз до нижней границы таблицы.

Весь столбец при этом будет заполнен значениями по порядку.

Далее переходим к столбцу «Дата начала». Тут следует указать дату начала каждого конкретного мероприятия. Делаем это. В столбце «Продолжительность в днях» указываем количество дней, которое придется потратить для решения указанной задачи.

В колонке «Примечания» можно заполнять данные по мере необходимости, указывая особенности конкретного задания. Внесение информации в этот столбец не является обязательным для всех мероприятий.

Затем выделяем все ячейки нашей таблицы, кроме шапки и сетки с датами. Клацаем по иконке «Формат» на ленте, к которой мы уже ранее обращались, жмем в открывшемся списке по позиции «Автоподбор ширины столбца».

После этого ширина столбцов выделенных элементов сужается до размеров ячейки, в которой длина данных больше всего в сравнении с остальными элементами колонки. Таким образом, экономится место на листе. При этом в шапке таблицы производится перенос наименований по словам в тех элементах листа, в которых они не умещаются в ширину. Это получилось сделать благодаря тому, что мы ранее в формате ячеек шапки поставили галочку около параметра «Переносить по словам».

Этап 4: Условное форматирование

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

Отмечаем весь массив пустых ячеек на шкале времени, который представлен в виде сетки элементов квадратной формы.

Щелкаем по значку «Условное форматирование». Он расположен в блоке «Стили» После этого откроется список. В нем следует выбрать вариант «Создать правило».

Происходит запуск окна, в котором требуется сформировать правило. В области выбора типа правила отмечаем пункт, который подразумевает использование формулы для обозначения форматируемых элементов. В поле «Форматировать значения» нам требуется задать правило выделения, представленное в виде формулы. Для конкретно нашего случая она будет иметь следующий вид:
=И(G$1>=$D2;G$1<=($D2+$E2-1))

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

«И» — это встроенная функция Excel, которая проверяет, все ли значения, внесенные как её аргументы, являются истиной. Синтаксис таков:

=И(логическое_значение1;логическое_значение2;…)

Всего в виде аргументов используется до 255 логических значений, но нам требуется всего два.

Первый аргумент записан в виде выражения «G$1>=$D2». Он проверяет, чтобы значение в шкале времени было больше или равно соответствующему значению даты начала определенного мероприятия. Соответственно первая ссылка в данном выражении ссылается на первую ячейку строки на шкале времени, а вторая — на первый элемент столбца даты начала мероприятия. Знак доллара ($) установлен специально, чтобы координаты формулы, у которых стоит данный символ, не изменялись, а оставались абсолютными. И вы для своего случая должны расставить значки доллара в соответствующих местах.

Второй аргумент представлен выражением «G$1<=($D2+$E2-1)». Он проверяет, чтобы показатель на шкале времени (G$1) был меньше или равен дате завершения проекта ($D2+$E2-1). Показатель на шкале времени рассчитывается, как и в предыдущем выражении, а дата завершения проекта вычисляется путем сложения даты начала проекта ($D2) и продолжительности его в днях ($E2). Для того, чтобы в количество дней был включен и первый день проекта, от данной суммы отнимается единица. Знак доллара играет ту же роль, что и в предыдущем выражении.

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

Чтобы выбрать определенный цвет заливки, клацаем по кнопке «Формат…».

В новом окне передвигаемся в раздел «Заливка». В группе «Цвета фона» представлены различные варианты закраски. Отмечаем тот цвет, которым желаем, чтобы выделялись ячейки дней, соответствующих периоду выполнения конкретной задачи. Например, выберем зеленый цвет. После того, как оттенок отразился в поле «Образец», клацаем по «OK».

После возвращения в окно создания правила тоже клацаем по кнопке «OK».

После выполнения последнего действия, массивы сетки сетевого графика, соответствующие периоду выполнения конкретного мероприятия, были окрашены в зеленый цвет.

На этом создание сетевого графика можно считать оконченным.

Урок: Условное форматирование в Майкрософт Эксель

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

Построение вершин

Присваиваем вершинам подписи

Соединение вершин ребрами (отдельный ряд)

Подписи ребер

Соединение вершин ребрами (пути)

history 21 ноября 2017 г.

Построение вершин

Присваиваем вершинам подписи

Соединение вершин ребрами (отдельный ряд)

Подписи ребер

Соединение вершин ребрами (пути)

Построение графов для чайников: пошаговый гайд

1. Выбор гипотезы

2. Подготовка данных

3. Визуализация графа

4. Анализ результата

Creating a 3 axis graph

Процедура построения сетевого графика

Этап 1: построение структуры таблицы

Этап 2: создание шкалы времени

Этап 3: заполнение данными

Этап 4: Условное форматирование

Еще статьи по данной теме:

Помогла ли Вам статья?

А вот еще интересные статьи: