Профессиональная диаграмма в excel

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

Принципы построение презентабельных графиков и диаграмм в Excel при формировании аналитических или статистических отчетов.

Построение графиков и диаграмм

Возрастная половая пирамида населения в Excel.
Пример построения возрастной пирамиды с разделением статистики по полу населения на мужчин и женщин. Возрастная гистограмма по статистическим данным в таблице.

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

Интерполяция графика и табличных данных в Excel.
Примеры интерполяции графиков и табличных данных в Excel. Практическое применение и использование функции НД. Что такое интерполяция и зачем она нужна?

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

Построение линии тренда в Excel для анализа графика.
Создание и практическое использование линии тренда в анализе графиков. Особенности трендовой линии как аппроксимирующего инструмента. Сравнение и настройка прогноза по линии тренда на разных типах графиков.

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

Как построить лепестковую диаграмму в Excel.
Пример построения и практического применения лепестковой диаграммы. Как следует читать лепестковую диаграмму при анализе данных или сравнении показателей?

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



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

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

• Часть 1. Как сделать организационную диаграмму в Microsoft Excel
• Часть 2. Лучший альтернативный онлайн-конструктор организационных диаграмм в Excel
• Часть 3. Часто задаваемые вопросы о создании организационных диаграмм

Часть 1. Как сделать организационную диаграмму в Microsoft Excel

Excel — один из лучших офисных пакетов Microsoft. Хотя эта ведущая программа для работы с электронными таблицами помогает визуализировать и анализировать данные, она также является подходящим устройством для создания диаграмм, диаграмм и даже ментальных карт. В Excel, как и в других пакетах Microsoft, есть функция SmartArt, которая позволяет пользователям легко создавать иллюстративные диаграммы. Между тем, сам Excel также может помочь вам в создании диаграмм без использования указанной функции. Это потому, что он поставляется с фантастическими элементами, такими как фигуры, изображения и 3D-модели, которые много значат при создании диаграмм.

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

Способ 1. Создайте организационную диаграмму с помощью SmartArt

Способ 1. Создайте организационную диаграмму в Excel с помощью фигур

Часть 2. Лучший альтернативный онлайн-конструктор организационных диаграмм в Excel

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

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

Как создать организационную диаграмму с помощью MinOnMap

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

Часть 3. Часто задаваемые вопросы о создании организационных диаграмм

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

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

Преимущества диаграмм типа «Шкала»:

• трехцветная фоновая заливка показывает зоны риска;
• горизонтальная красная линия показывает плановый (целевой, нормативный) уровень;
• черный центральный прямоугольник-уровень показывает достигнутый текущий уровень (факт).

Недостаток диаграммы в том, что она не сравнивает показатели за несколько лет. Этот недочет легко устранить, если расположить рядом «шкалы» за несколько отчетных периодов.

Построение диаграммы «Шкала»

Рассмотрим на примере табл. 5 и рис. 9, как построить диаграмму со «шкалой». Действуют последовательно:

Шаг 1. Готовят таблицу с исходными данными, где определяют уровни показателей (табл. 5).

Шаг 2. По заготовленному диапазону строят гистограмму с накоплением. На вкладке Конструктор выбирают Данные, а в Строка/Столбец выбирают расположение столбцов друг над другом. Настраивают поочередно цветовую заливку секторов по их смыслу: вкладка Формат → Заливка фигуры (рис. 10). Убирают легенду и название диаграммы. Изменяют размер — делают столбик узким.

Шаг 3. Настраивают черный «бегунок». Выделяют черный сектор (фактический остаток запасов). Вызывают через контекстное меню: Формат ряда данных → Параметры ряда → Построить ряд → По вспомогательной оси. Черный прямоугольник временно закроет другие секторы диаграммы. Далее в Параметрах ряда Боковой зазор устанавливают до максимума → Большой зазор (рис. 11).

Шаг 4. Форматируют нужный шрифт. Удаляют правую ось.

Шаг 5. Устанавливают плановый уровень. Чтобы получить в итоге красную планку, выделяют верхний красный сектор: Изменить тип диаграммы для ряда → Точечная. Сектор превратится в точку или знак «Ж» (рис. 12). Далее выполняют команду: Макет → Планки погрешностей → Планки погрешностей с относительными ошибками. Через контекстное меню вызывают Формат планок погрешностей. У точки появятся четыре планки. Вертикальные планки удаляют, а горизонтальные настраивают, чтобы получить черту-уровень.

В свойствах Горизонтальных планок погрешностей (диалоговое окно «Формат планок погрешностей») в разделе «Величина погрешности» выбирают «Фиксированное значение» и задают положительное и отрицательное значение ошибки («Все»), равное 0,2–0,5 (рис. 13). Здесь же увеличивают толщину линии и устанавливают красную заливку (Цвет линии и Тип линии). Маркер отключают через Формат ряда данных → Параметры маркера — Нет.

Другой способ настроить «уровень». Маркер не удаляют, а придают ему форму линии. Вызывают контекстное меню – Формат точки данных – Параметры маркера – Тип Линия. Размер подбирают так, чтобы перекрыть шкалу (толщину оставшихся секторов гистограммы). В результате получают диаграммы, как на рис. 7–9.

ЕЖЕДНЕВНАЯ ДИНАМИКА ПРОДАЖ

Производственные показатели и прибыль — достаточно узкие показатели. Руководителей больше интересуют объемы продаж («живые» деньги). При подведении итогов года по продажам суммы годовой выручки часто недостаточно. Требуется более подробная аналитика:

1) за каждый день года. Для анализа, привязанного к выборочным датам и определенным событиям: предпраздничный сезон, период скидок, агрессивная рекламная кампания, действия конкурентов, новая система мотивации менеджеров, открытие новых торговых точек, передача «активных» продаж на аутсорсинг и др. Если знать, как на интересующие события реагировали продажи в прошлом году, можно эффективно спланировать политику продаж и маркетинговый план на следующий год и таким образом увеличить выручку;

2) по регионам, торговым точкам, брендам, видам продукции, ценовым сегментам.

Сгруппировать все перечисленное в одной информативной диаграмме и не допустить перегруженности помогут интерактивные графики. Интерактивный график (рис. 14) каждый специалист может подстраивать под свои задачи:

• флажками включать и выключать отображение отдельных графиков. На выбор: все три графика, только две торговые марки, одну торговую марку или только общий объем продаж;
• подробнее рассмотреть показатели за укрупненный или короткий интервал времени (полгода или десять дней);
• выбрать любой период времени за прошлый год (январь, март или октябрь).

Создание интерактивного графика динамики продаж

Чтобы создать интерактивный график (см. рис. 14) на базе исходной таблицы, действуют поэтапно.

Этап 1. Строят вспомогательную таблицу на основании исходной (в нашем случае — на основании табл. 6). Структура и показатели обеих таблиц одинаковые. Отличие в следующем: во вспомогательную таблицу берут только те показатели (столбцы), которые выбраны «флажками».

Чтобы подключить флажки, используют вкладку «Разработчик». Если ее нет (в стандартных настройках отсутствует), открывают вкладку Файл → Параметры → Настройка ленты → Разработчик.

Далее выполняют команду: вкладка Разработчик → Вставить → Элементы управления формы → Флажок. Вставляют три флажка по количеству отображаемых на графике показателей (рис. 15). С помощью правой кнопки мыши выбирают команду «Изменить текст», подписывают флажки идентично столбцам в табл. 6.

Далее для каждого флажка определяют связанную ячейку. Нужно каждый флажок привязать к соответствующей зеленой ячейке над столбцом с выручкой (рис. 15). При включении флажка в связанную ячейку будет выводиться ИСТИНА, при выключении — ЛОЖЬ.

С помощью формул проверяют связанные ячейки и выводят во вспомогательную таблицу показатели выручки из исходной таблицы для построения графика или ошибку #Н/Д, чтобы график не визуализировался. Чтобы установить связь с ячейкой, вызывают контекстное меню → Формат объекта — Формат элемента управления → Связь с ячейкой (рис. 15).

После установки флажков настраивают автозаполнение формулой для вспомогательной таблицы из исходной при условии, что флажок включен и в связанной ячейке ИСТИНА. Формула для общего объема продаж (рис. 16):

=ЕСЛИ(H$1;ПродажиИ[@[Объем продаж — всего, тыс.руб.]];#Н/Д).

Копируем формулы на другие столбцы:

=ЕСЛИ(I$1;ПродажиИ[@[Объем продаж ТМ «Омега», тыс.руб.]];#Н/Д);

=ЕСЛИ(J$1;ПродажиИ[@[Объем продаж ТМ «Дельта», тыс.руб.]];#Н/Д).

Такой вид аргументов в формуле «ЕСЛИ» связан с тем, что обе таблицы отформатированы как «умные», им присвоены имена:

• исходная таблица «ПродажиИ»;
• вспомогательная таблица «ПродажиВ».

В формулах также выполнено частичное закрепление ссылки на зеленую ячейку (H$1). Она должна при копировании смещаться вправо, но не вниз (рис. 16).

Этап 2. Настраивают полосы прокрутки для масштабирования.

По аналогии с флажками добавляют две полосы прокрутки: Разработчик → Вставить → Элементы управления формы → Полоса прокрутки. Полосы прокрутки связывают каждую со своей ячейкой, куда будет выводиться числовое значение положения ползунка. Это нужно для определения масштаба и сдвига. Чтобы связать с ячейкой, действуют через Формат объекта (аналогично с флажками). Для первой полосы прокрутки назначают ячейку W16, для второй — W18 (рис. 17). При перемещении ползунков значения в связанных ячейках должны меняться в интервале от 1 до 366. Максимальное и минимальное значение интервала зависит от того, за какой период нужно визуализировать график.

Этап 3. Создают динамические именованные диапазоны.

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

• отступом от начала таблицы вниз на заданное количество строк. Это отступ по временной шкале начало-конец года (верхняя полоса прокрутки);
• количеством ячеек по высоте, то есть размером интервала (нижняя полоса прокрутки).

Для создания динамического диапазона используют функцию «СМЕЩ» из библиотеки функций «Ссылки и массивы». Константы в аргументах этой функции заменяют ссылками на ячейки с переменным содержимым — на значения в связанных ячейках W16 и W18 (рис. 18).

В первую очередь присваивают имя ячейкам W16 и W18: вкладка Формулы → Диспетчер имен  → Создать. Имена Период и Масштаб соответственно (рис. 18).

Далее создают диапазон с именем ВОбщ (общий объем продаж). Этот диапазон ссылается с помощью функции «СМЕЩ» на показатели общего объема продаж за выбранный отрезок времени, используя диапазоны Период и Масштаб, а также ячейку G4 (шапка вспомогательной таблицы) в качестве точки отсчета (рис. 19). Перед именем диапазона проставляют имя текущего листа (в нашем случае имя листа — Динамика). Именованный диапазон доступен только в пределах текущего листа, а не всей книги. Это важно для построения графиков на следующем этапе.

Аналогично создают именованные диапазоны для продаж по отдельным торговым маркам и для столбца дат.

Все созданные диапазоны можно проконтролировать через «Диспетчер имен» на вкладке Формулы (рис. 19). Формулы диапазонов следующие:

• Дата (ВДата):=СМЕЩ($G$4;Период;0;Масштаб;1);
• Общий объем продаж (ВОбщ):=СМЕЩ(Динамика!G$4;Период;1;Масштаб;1);
• Продажи ТМ «Омега» (ВОмега):=СМЕЩ(Динамика!$G$4;Период;2;Масштаб;1);
• Продажи ТМ «Дельта» (ВДельта):=СМЕЩ(Динамика!$G$4;Период;3;Масштаб;1).

Этап 4. Вставляют график продаж.

Выделяют часть вспомогательной таблицы (табл. 6, рис. 20) и строят обычный график: вкладка Вставка → Диаграммы → График.

Если выделить одну из линий графика, то в строке формул появится функция «РЯД». Она задает диапазоны данных для выделенного графика. Заменяют статические диапазоны в ее аргументах (G5:G15) на динамические, которые создали на предыдущем этапе. В строке формул меняют.

Общая выручка:

=РЯД(Динамика!$H$4;Динамика!$G$5:$G$15;Динамика!$H$5:$H$15;1)

на =РЯД(Динамика!$H$4;Динамика!ВДата;Динамика!ВОбщ;1).

Аналогично по торговым маркам «Омега» и «Дельта»:

=РЯД(Динамика!$I$4;Динамика!ВДата;Динамика!ВОмега;2);

=РЯД(Динамика!$J$4;Динамика!ВДата;Динамика!ВДельта;3).

Форматируют цвет линий графика и размер диаграммы.

В работе с интерактивными графиками часто возникает проблема, когда после нескольких манипуляций прокрутка перестает работать, а диапазон в функции «РЯД» меняется с поименованного на базовый. Чтобы настройки не сбивались, в полосах прокрутки ставят максимальную длину на единицу больше. Так, за год вместо 365 в полосах прокрутки задано максимальное значение 366 (см. рис. 17).

Рассмотрим примеры, при которых динамические графики буду полезны (рис. 21–23).

ПРИМЕР 4

Годовой объем продаж продукции ТМ «Омега» превышает объем реализации ТМ «Дельта». Нужно сравнить эти торговые марки, поэтому график общей выручки с помощью флажка отключают (см. рис. 14). Получили на диаграмме два графика (рис. 21). Чтобы получить период февраль-май, изменяют положения бегунка в полосах прокрутки.

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

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

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

ПРИМЕР 5

В сентябре при проведении следующего комплекса стимулирующих мер ранее допущенные ошибки (пример 4) проанализировали и устранили (рис. 22). В результате в августе-сентябре вышли на объем выручки ТМ «Дельта», запланированный в маркетинговом бюджете. Повышенные объемы продаж ТМ «Дельта» держались до конца октября. Это более длительный период, чем планировалось. Более того, еще около месяца продажи обеих торговых марок были на одном уровне. К концу года выручка от ТМ «Дельта» уже превышала ТМ «Омега» (рис. 22).

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

ПРИМЕР 6

Если просмотреть весь динамический график (с помощью прокруток сделать это удобно и быстро), то общий объем выручки соответствует динамике продаж ТМ «Омега» и ТМ «Дельта». Однако в отдельные периоды (рис. 14, 22 и 23), когда продажи от упомянутой продукции отсутствуют или на низком уровне, выручка в компанию продолжает поступать.

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

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

ЗАКЛЮЧЕНИЕ

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