Диаграммы в microsoft excel

Диаграмма Ганта в Excel

Диаграмма Ганта – это способ представления информации в виде столбиков для иллюстрации многоэтапного мероприятия. Красивый и несложный прием.

1. У нас есть таблица (учебная) со сроками сдачи отчетов.
2. Для диаграммы вставляем столбец, где будет указано количество дней. Заполняем его с помощью формул Excel.
3. Выделяем диапазон, где будет находиться диаграмма Ганта. То есть ячейки будут залиты определенным цветом между датами начала и конца установленных сроков.
4. Открываем меню «Условное форматирование» (на «Главной»). Выбираем задачу «Создать правило» — «Использовать формулу для определения форматируемых ячеек».
5. Вводим формулу вида: =И(E$2>=$B3;E$2

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

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

Простенькая диаграмма Ганта готова. Скачать шаблон с примером в качестве образца.

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

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

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

Перемена местами осей диаграммы

В диаграммах большинства есть ось X и Y. На них отображаются соответственно категории и значения.

Ниже отображена простая гистограмма, представляющая доходы вымышленного физического лица по месяцам. Последние являются категориями, а доходы – значениями. Цифры в левой части диаграммы ограничены числом 90, поскольку максимальный (декабрьский) доход не превышает 80 тысяч.

Иногда бывает нужно поменять оси местами. Для этого диаграмма выделяется, после чего на вкладке «Конструктор» щёлкнуть инструмент «Строка/столбец». Диаграмма примет следующий вид. Давайте разбираться, что произошло. Для лучшего понимания воспользуемся известными нам приёмами редактирования.

Активация элемента диаграммы «Легенда» «включила» цветовую адресацию месяцев.

Диаграмма стала совсем понятной после активации элемента «подписи данных».

Как построить комбинированную диаграмму в Excel

Способы построения комбинированной диаграммы в Excel:

• преобразование имеющейся диаграммы в комбинированную;
• добавление вспомогательной оси.

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

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

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

Каким образом можно комбинировать разные типы диаграмм? Щелкнем правой кнопкой мыши «К-во, шт.». В открывшемся окне выберем «Изменить тип для ряда».

Откроется меню с типами диаграмм. В разделе «Гистограмма» выберем плоскую столбчатую «Гистограмму с группировкой».

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

Выделим гистограмму, щелкнув по ней мышкой. Перейдем на вкладку «Макет». Группа «Текущий фрагмент» — инструмент «Формат выделенного фрагмента».

Откроется окно «Формат ряда данных». На вкладке «Параметры ряда» поставим галочку напротив «Построить ряд по вспомогательной оси».

Нажимаем кнопку «Закрыть».

Поработаем над внешним видом комбинированной диаграммы. Выделим область построения и перейдем на вкладку «Конструктор». Поменяем стиль. Удалим легенду (выделить – Delete). Добавим название и подписи вертикальных осей.

Для основной и вспомогательной оси выбираем вариант расположения (отдельно для каждой) и вводим подпись. Жмем Enter.

В данном примере мы использовали сразу два способа построения комбинированных диаграмм: изменяли тип для ряда и добавляли вспомогательную ось.

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



ТОЧЕЧНАЯ

Визуально Точечная диаграмма похожа на диаграмму типа График (если конечно у Точечной диаграммы точки соединены линиями).

Различие состоит в том, что для построения Графика в качестве координаты Х всегда используется последовательность 1, 2, 3, … (с подписями — категориями), а для Точечной значения по горизонтальной оси м.б. любыми числами (см. статью

График vs Точечная

).

Примечание
. Если для построения Точечной диаграммы не указана ссылка на значения Х (или ссылка указывает на текстовые значения), то в качестве координат по Х будет использована та же последовательность 1, 2, 3, …, что и для Графика.

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

Теперь о совмещении разных типов диаграмм с Точечной. Если для Точечной диаграммы не используется координата Х, то на диаграмме она выглядит как График.

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

Кроме того, График может быть только на основной оси и поменять это нельзя.

Если для Точечной диаграммы используется координата Х и оба ряда отложены по одной (основной) оси, то получается не очень красивая диаграмма.

Это связано с тем, что при совмещении с Точечной диаграммой (на одной оси и когда у Точечной указаны значения по Х), диаграмма типа График становится как бы главной:

• на горизонтальной оси отображаются подписи только для Графика;
• вертикальная сетка не отображается для отрицательных значений Х (т.к. График строится только для Х=1, 2, 3, …);
• у Графика невозможно изменить Ось с Основной на Вспомогательную (у Точечной можно).

Если Точечную построить на вспомогательной оси, то диаграмма изменится.

Подписи для Точечной (значения по Х) теперь отображаются сверху.

Совет
. Диаграмма типа Точечная используется для

построения окружностей, эллипсов

и других

замкнутых фигур на плоскости

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

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

Примечание
. Фактически на диаграмме 4 ряда данных: точка центра представляет отдельный ряд.

Дважды кликнем по бордовому эллипсу и выберем построение ряда на вспомогательной оси (сделаем то же и центра эллипса).

Теперь координаты Y для бордового эллипса откладываются по правой вертикальной оси (можно ее для наглядности выделить также бордовым цветом).

Добавим Вспомогательную горизонтальную ось (в меню Оси (вкладка
Макет
, группа
Оси
) выберите
Вспомогательная горизонтальная ось
и установите ее
По умолчанию
).

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

Дашборд помогает решать задачи менеджерам по продажам, HR-специалистам, бухгалтерам, маркетологам, руководителям

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

Основные задачи дашборда:

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

Построение дашбордов ― такой же hard skill, как владение формулами в Excel. По статистике, пользователь Excel среднего уровня может освоить этот навык за 20 часов обучения и практики.

Для специалистов, которые работают с отчётами, навык построения дашбордов стал необходимостью, а не дополнительным преимуществом.

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

Менеджерам по продажам дашборд помогает управлять продажами. HR-специалистам ― отслеживать основные метрики, связанные с трудовыми ресурсами. Для бухгалтера будет полезен дашборд о движении средств, который отражает финансовое состояние организации. Маркетологи анализируют рекламные кампании и оценивают их эффективность. Руководителю дашборд позволит быстро оценивать состояние ключевых показателей и принимать управленческие решения.

Существует большое количество сервисов для бизнес―аналитики, такие как Tableau, Power BI, Qlik, DataLens, Google Data Studio. Самым доступным можно назвать Excel.

Главное и самое интересное в дашборде ― интерактивность.

Настроить интерактивность можно с помощью следующих приёмов:

Водопад = динамика + рейтинг

В оригинале эта диаграмма называется Waÿerfall Chart, в русском переводе MS Office — «Каскадная», но самый популярный вариант названия — «Водопадная диаграмма». В некоторых компаниях о ее построении говорят что-то вроде «строить бриджи» или «висячие сады». Чаще всего этот визуальный элемент используют для факторного анализа. 

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

• зеленый — там, где показатель вырос,
• красный — там, где он снизился.

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

Мы видим, что из валовой прибыли вычли управленческие расходы, добавили проценты к получению и вычли проценты к уплате. Потом добавили 2,8 млн прочих доходов и получили пик денежного потока: 4,1 млн из него списали в прочие расходы, а уже с оставшейся небольшой базы уплатили 340 тыс. налога. 

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

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

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

Цветовая кодировка может быть инвертирована в случае анализа расходов. Здесь мы видим, что плановые расходы на оплату труда были 55 млн, а по факту составили 70,5 млн.

Основная экономия вышла за счет сдвига сроков, вывода объектов и немного за счет сокращения административно-управленческого персонала. А перерасход вышел из-за пересмотра ставок, текучести и премирования. 

На диаграмму мы вывели самые значимые факторы — но были еще и мелкие, которые в сумме сэкономили 5 млн рублей.

Построение гистограммы

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

Фотография Создаем таблицу в Excel

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

Фотография Выбираем вид гистограммы

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

Добавление данных в диаграмму в Excel

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

1. Открыть контекстное меню созданной диаграммы, щёлкнуть правой клавишей мыши на ней.
2. В раскрывшемся списке щёлкнуть «Выбрать данные».

1. В окне «Выбор источника данных», в поле «Диапазон данных для диаграммы» щёлкнуть правый инструмент с маленькой наклонно красной стрелкой.

1. В появившемся маленьком окошке выделить изменённый диапазон данных, включая новый столбец «Налог».
2. Щёлкнуть инструмент справа с маленькой красной стрелкой, направленной вниз.

Как видно, в диаграмму добавились данные нового столбца – «Налог».

Как закрепить элементы управления на диаграмме Excel?

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

1. Выделяем диапазон значений A1:C5 и на «Главной» нажимаем «Форматировать как таблицу».
2. В открывшемся меню выбираем любой стиль. Программа предлагает выбрать диапазон для таблицы – соглашаемся с его вариантом. Получаем следующий вид значений для диаграммы:
3. Как только мы начнем вводить новую информацию в таблицу, будет меняться и диаграмма. Она стала динамической:

Мы рассмотрели, как создать «умную таблицу» на основе имеющихся данных. Если перед нами чистый лист, то значения сразу заносим в таблицу: «Вставка» — «Таблица».

Правило 4. Легкое сравнение данных

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

Одна из главных целей визуализации – удобное и наглядное сравнение двух и более показателей.

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

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

Построение диаграммы в Excel

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

Вариант 1: Построение диаграммы по таблице

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

1. Прежде чем приступить к созданию какой-либо диаграммы, необходимо создать таблицу с данными, на основе которых она будет построена. Затем перейдите на вкладку «Вставка» и выберите область таблицы, которая будет отображена на диаграмме.
2. На ленте вкладки «Вставка» выберите один из шести основных типов:
   • Гистограмма;
   • Программа;
   • Круговой;
   • Управляемый;
   • С площадями;
   • Точка.
3. Кроме того, нажав кнопку «Другие», вы можете остановиться на одном из менее распространенных типов: стык, поверхность, безымянный палец, пузырь, лепесток.
4. Впоследствии, нажав на любой из типов диаграмм, появляется возможность выбрать конкретный подвид. Например, для гистограммы или столбчатой ​​диаграммы этими подтипами будут следующие элементы: регулярная, объемная, цилиндрическая, коническая, пирамидальная гистограмма.
5. После выбора конкретного подвида автоматически создается диаграмма. Например, обычная гистограмма будет выглядеть как на скриншоте ниже:
6. График в виде графика будет иметь следующий вид:
7. Вариант с регионами будет выглядеть так:

Работа с диаграммами

После создания объекта на новой вкладке «Работа с диаграммами» доступны дополнительные инструменты для редактирования и модификации.

1. Вы можете изменить тип, стиль и многие другие параметры.
2. На вкладке «Работа с диаграммами» есть три дополнительных вложенных вкладки: «Дизайн», «Макет» и «Формат», с помощью которых вы можете настроить отображение по мере необходимости. Например, чтобы дать диаграмме имя, откройте вкладку «Макет» и выберите один из вариантов размещения имени: по центру или вверху.
3. После этого появится стандартное «Графическое имя». Меняем его на любую надпись, соответствующую контексту этой таблицы.
4. Имена осей на графике подписаны точно так же, но для этого нужно нажать кнопку «Названия осей».

Вариант 2: Отображение диаграммы в процентах

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

1. Как и в предыдущем случае, давайте создадим таблицу, а затем выберем диапазон данных. Затем перейдите на вкладку «Вставка», укажите круговую диаграмму на ленте и щелкните любой тип в появившемся списке.
2. Программа самостоятельно переводит нас на одну из вкладок для работы с этим объектом: «Конструктор». Выберите любой макет на ленте, содержащий знак процента.
3. Круговая диаграмма в процентах готова.

Вариант 3: Построение диаграммы Парето

Согласно теории Вильфредо Парето, 20% наиболее эффективных действий приносят 80% общего результата. В итоге оставшиеся 80% от общего набора неэффективных действий приносят только 20% результата. Создание диаграммы Парето предназначено для расчета наиболее эффективных действий, приносящих наибольшую отдачу. Сделаем это с помощью Microsoft Excel.

1. этот объект удобнее построить в виде гистограммы, о которой мы уже говорили выше.
2. Вот пример: таблица содержит список продуктов. В одном столбце указана покупная стоимость всего объема того или иного вида товара на оптовом складе, а во втором — прибыль от его продажи. Нам необходимо определить, какие продукты приносят наибольшую «прибыль» при продаже.

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

3. Как видите, в результате предпринятых действий сформировалась диаграмма с двумя типами столбцов — синим и красным. Теперь нам нужно превратить красные столбцы в график: выделите эти столбцы курсором и во вкладке «Конструктор» нажмите кнопку «Изменить тип графика».
4. Откроется окно для изменения типа. Перейдите в раздел «Графика» и укажите тип, подходящий для наших целей.
5. Таким образом, диаграмма Парето построена. Теперь вы можете изменять его элементы (имя объекта и оси, стили и т.д.) так же, как это было описано в примере гистограммы.

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

Что такое точечная диаграмма?

Точечная диаграмма (также известная как точечная диаграмма и точечная диаграмма) — это график, на котором точки отображаются как точки данных, представляющие две разные переменные. Каждая точка данных показывает пересечение значений переменных x и y.

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

1. Изучение корреляций

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

2. Изучение дистрибутивов

Диаграммы рассеивания дают четкое представление о том, как точки данных распределяются по осям x и y. Наблюдение за распределением данных имеет решающее значение для сегментации, выявления закономерностей и статистического моделирования.

3. Сравнение наборов данных

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

4. Представление научных данных

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

Создание диаграммы с областями с накоплением

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

Предположим, у вас есть набор данных ниже, который показывает продажи трех разных товаров (в тысячах):

Вот шаги, чтобы создать диаграмму области в Excel с этими данными:

• Выберите весь набор данных (A1: D6)
• Щелкните вкладку Вставка.
• В группе «Диаграмма» щелкните значок «Вставить линию или диаграмму с областями».
• В категории «2-D Area» нажмите «Stacked Area».

Это даст вам диаграмму с областями с накоплением, как показано ниже:

На приведенной выше диаграмме вы получите следующую информацию:

• Общее значение для любого года (например, 1 из 5 в 2017 году и 130 в 2018 году).
• Общая тенденция, а также тенденция по продукту.
• Визуальное представление доли продаж каждого продукта в общем объеме продаж. Например, вы можете визуально увидеть и сделать вывод, что продажи, если проекторы внесли наибольший вклад в общий объем продаж, а белые доски — наименьший.

Выводы

Главное, представить данные так, чтобы с ними было легко работать. Как это сделать?

Первый шаг: определитесь с целью визуализации. Это для доклада руководству или схема бизнес-процесса?

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

Третий шаг: постройте представление.

Четвертый шаг: убедитесь, что ваше представление не противоречит принципам визуализации. Все ли понятно? Не слишком ли много данных на одном представлении? Привычно ли оно для вашей аудитории?

Пятый шаг: проверьте на наличие распространенных ошибок. На месте ли легенды? Все ли оси отображены корректно?

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

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

Научившись визуализировать на бытовом уровне, вы сможете быстрее понять принципы визуализации более сложных данных. А это открывает большие и интересные перспективы.

Мнение автора и редакции может не совпадать. Хотите написать колонку для «Нетологии»? Читайте наши условия публикации.