Как сделать диаграмму в экселе по таблице с разными столбцами

Диаграммы

Ещё один наглядный вариант визуализации выполнения плана с помощью столбчатой гистограммы с накоплением и «усов» погрешностей в Microsoft Excel.

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

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

2 способа построить в Excel вафельную диаграмму (waffle-chart) для наглядной визуализации прогресса по проекту или любых других KPI.

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

Подробный разбор особенностей использования двух новых типов данных География (Geography) и Акции (Stocks), появившихся в Microsoft Excel 2016.

Как с помощью Power Query быстро «размазать» бюджет по дням в списке проектов, построив упрощенный вариант диаграммы Ганта в Excel.

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

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

Как выводить нестандартные символы и пиктограммы с помощью функции СИМВОЛ (CHAR) для визуализации количества проданного товара, динамики роста-падения и т.п.

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

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

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

Как построить в Excel диаграмму-торнадо для наглядного сравнения двух наборов данных по нескольким параметрам.

Обзор различных способов для отображения числовых данных из Excel на карте: надстройки Microsoft Power View, Microsoft Power Map, Bing Maps, пузырьковая диаграмма и др.

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

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

Как опустить линии проекции от каждой точки данных на оси X и Y на диаграмме в Microsoft Excel.

Как в Excel построить диаграмму-шкалу или диаграмму-термометр (bullet chart) для наглядного отображения достигнутых значений по сравнению с запланированными. Будет полезно для визуализации ключевых параметров (KPI) в бизнес-отчетах и презентациях.

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

5 способов построить диаграмму Парето (принцип 20/80) для наглядного отображения ключевых факторов, влияющих на ваши бизнес-процессы.

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

Как в Excel построить очень популярную в финансовых отчетах каскадную диаграмму отклонений, известную также под названиями «водопад» (waterfall) или «мост» (bridge) для наглядного отображения динамики изменений какого-либо параметра.

Что нового появилось в построении диаграмм в Microsoft Excel 2013. Новый мастер диаграмм и рекомендуемые диаграммы. Настройка параметров и форматирования диаграмм, фильтрация данных в диаграмме. Подписи данных из ячеек. Анимация диаграмм.

Обучающий видеоролик о том, как создать проектную диаграмму Ганта в новых версиях Excel 2007-2010. Можно использовать для создания графиков отпусков, дежурств, тренингов и т.п. в Excel.

Как создать один из основных типов диаграмм в управлении проектами — диаграмму Ганта — в Excel 2003 и старше.

Как при помощи условного форматирования быстро создать в Excel проектную диаграмму Ганта. Удобно для визуализации этапов любого проекта, графиков отпусков, тренингов и т.д.

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

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

Что такое пузырьковая диаграмма (Bubble chart) и с чем ее едят. Как ее «читать», как должны быть подготовлены исходные данные для ее построения. Как ее создать, настроить и как сделать подписи к пузырькам.

Как быстро добавить к имеющейся диаграмме новые данные или настроить это добавление полностью автоматически.

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Выбор данных для диаграмм

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

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

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

Упорядочение данных для диаграмм

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

Тип диаграммы

Упорядочение данных

Гистограмма, линейчатая, график, с областями, поверхностная или лепестковая

По столбцам или по строкам.

Круговая диаграмма

Эта диаграмма использует один набор значений («ряд данных»).

Один столбец или строка и один столбец или строка меток данных.

Кольцевая диаграмма

На этой диаграмме может отображаться один или несколько рядов данных.

Один или несколько столбцов или строк данных и один столбец или строка меток данных.

Точечная или пузырьковая диаграмма

Данные расположены по столбцам, причем значения x — в первом столбце, а значения y — в следующем.

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

Биржевая диаграмма

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

См. также

Чтобы создать диаграмму в Excel в Интернете, необходимо выбрать хотя бы одну ячейку в диапазоне данных (наборе ячеек). Диаграмма будет включать все данные из этого диапазона.

Упорядочение данных для диаграмм

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

Тип диаграммы

Упорядочение данных

Г столбец, ли линия, диаграмма с областью или диаграмма с областью

По столбцам или по строкам, например:

Эта диаграмма использует один набор значений («ряд данных»).

Один столбец или строка и один столбец или строка меток данных, например:

Эта диаграмма может использовать один или несколько рядов данных

Несколько столбцов или строк данных и один столбец или строка меток данных, например:

По столбцам, значения x — в первом столбце, а значения y — в следующем, например:

Дополнительные сведения об этих диаграммах см. в таблице Типы диаграмм.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

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

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

Составим для учебных целей простую табличку:

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

1. Выделяем таблицу с данными. Переходим на вкладку «Вставка» — «Диаграммы». Выбираем тип «Круговая».
2. Как только мы нажимаем на подходящее нам изображение, появляется готовая диаграмма.

Одновременно становится доступной вкладка «Работа с диаграммами» — «Конструктор». Ее инструментарий выглядит так:

Что мы можем сделать с имеющейся диаграммой:

Изменить тип. При нажатии на одноименную кнопку раскрывается список с изображениями типов диаграмм.

Попробуем, например, объемную разрезанную круговую.

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

Использовать различные макеты и шаблоны оформления.

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

Построенный график можно переместить на отдельный лист. Нажимаем соответствующую кнопку на вкладке «Конструктор» и заполняем открывшееся меню.

Создать круговую диаграмму в Excel можно от обратного порядка действий:

1. Сначала вставить на лист макет («Вставка» — «Диаграммы» — «Круговая»). В результате появится пустое окно.
2. Затем присвоить необходимые значения данных. Можно нажать на панели инструментов кнопку «Выбрать данные». А можно щелкнуть по макету правой кнопкой мыши и нажать «Выбрать данные».
3. В открывшемся окне «Выбор источника данных» заполняем поля. Диапазон – ссылка на ячейки с данными, на основании которых будет строиться круговая диаграмма. Элементы легенды (ряды) – числовые данные, части целого. Это поле заполнится автоматически, как только мы укажем диапазон.

Если выбор программы не совпадает с задуманным нами вариантом, то выделяем элемент легенды и нажимаем «Изменить». Откроется окно «Изменение ряда», где «Имя ряда» и «Значения» — ссылки на ячейки (ставим те, которые нужны) и жмем ОК.

Как изменить диаграмму в Excel

Все основные моменты показаны выше. Резюмируем:

1. Выделить диаграмму – перейти на вкладку «Конструктор», «Макет» или «Формат» (в зависимости от целей).
2. Выделить диаграмму либо ее часть (оси, ряды) – щелкнуть правой кнопкой мыши.
3. Вкладка «Выбрать данные» — для изменения названий элементов, диапазонов.

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

Круговая диаграмма в процентах в Excel

Простейший вариант изображения данных в процентах:

1. Создаем круговую диаграмму по таблице с данными (см. выше).
2. Щелкаем левой кнопкой по готовому изображению. Становится активной вкладка «Конструктор».
3. Выбираем из предлагаемых программой макетов варианты с процентами.

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

Второй способ отображения данных в процентах:

1. Щелкаем левой кнопкой по готовой круговой диаграмме.
2. Переходим на вкладку «Макет». Нам нужна кнопка «Подписи данных».
3. В раскрывшемся списке выбираем место для подписей.
4. Теперь на диаграмме отображаются числовые значения.
5. Щелкаем по любому из них левой кнопкой. Потом правой. В раскрывшемся меню выбираем «Формат подписей данных».
6. Откроется окно для назначения параметров подписи. Так как значения нужно отобразить в процентах, выберем доли.
7. Чтобы получить проценты с десятичными знаками, необходимо перейти по ссылке «Число», выбрать процентный формат и установить нужное количество цифр после запятой.

Результат проделанной работы:

Как построить диаграмму Парето в Excel

Вильфредо Парето открыл принцип 80/20. Открытие прижилось и стало правилом, применимым ко многим областям человеческой деятельности.

Согласно принципу 80/20, 20% усилий дают 80% результата (только 20% причин объяснят 80% проблем и т.д.). Диаграмма Парето отражает данную зависимость в виде гистограммы.

Построим кривую Парето в Excel. Существует какое-то событие. На него воздействует 6 причин. Оценим, какая из причин оказывает большее влияние на событие.

1. Создадим в Excel таблицу с данными. 1 столбец – причины. 2 столбец – количество фактов, при котором были обнаружены данные причины (числовые значения). Обязательно – итог.
2. Теперь посчитаем в процентах воздействие каждой причины на общую ситуацию. Создаем третий столбец. Вводим формулу: количество фактов по данной причине / общее количество фактов (=В3/В9). Нажимаем ВВОД. Устанавливаем процентный формат для данной ячейки – Excel автоматически преобразует числовое значение в проценты.
3. Отсортируем проценты в порядке убывание. Выделим диапазон: C3:C8 (кроме итога) – правая кнопка мыши – сортировка – «от максимального к минимальному».
4. Находим суммарное влияние каждой причины и всех предыдущих. Для причины 2 – причина 1 + причина 2.
5. Столбец «Факты» вспомогательный. Скроем его. Выделить столбец – правая кнопка мыши – скрыть (или нажимаем комбинацию горячих клавиш CTRL+0).
6. Выделяем три столбца. Переходим на вкладку «Диаграммы» — нажимаем «Гистограмма».
7. Выделяем вертикальную ось левой кнопкой мышки. Затем нажимаем правую клавишу и выбираем «Формат оси». Устанавливаем максимальное значение 1 (т.е. 100%).
8. Добавляем для каждого ряда подписи данных (выделить – правая кнопка – «Добавить подписи данных»).
9. Выделяем ряд «Сум.влиян.» (на рис. – зеленый). Правая кнопка мыши – «Изменить тип диаграммы для ряда». «График» — линия.

Получилась диаграмма Парето, которая показывает: наибольшее влияние на результат оказали причина 3, 5 и 1.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Построение диаграмм с несколькими рядами данных в EXCEL

Построение диаграмм с одним рядом данных рассмотрено в статье Основы построения диаграмм в MS EXCEL . Начинающим пользователям также целесообразно перед прочтением ознакомиться со статьей Основные типы диаграмм .

ГИСТОГРАММА

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

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

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

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

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

Если не настроить ширину столбцов, то эту диаграмму можно спутать с диаграммой Гистограмма с пополнением (столбцы, относящие к одной категории «ставятся» друг на друга).

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

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

Теперь изменим подписи по горизонтальной оси (категории).

В окне Выбор источника данных видно, что для обоих рядов подписи горизонтальной оси одинаковы, т.к. категории одинаковы для обоих рядов (столбец Месяц).

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

При желании это также можно изменить. В окне Выбор источника данных выделите ряд Прибыль, нажмите кнопку Изменить расположенную справа, удалите ссылку на ячейки. Теперь у ряда Прибыль вместо названия категорий будут просто порядковые числа 1, 2, 3,… Однако, они не будут отображаться на диаграмме, т.к. отображается пока только Основная горизонтальная ось .

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

Хотя техническая возможность отображения 2-х различных категорий существует, конечно, таких диаграмм нужно избегать, т.к. их сложно воспринимать. Категории должны быть одинаковыми для всех рядов на диаграмме. Естественно, такой трюк сработает только для двух групп рядов, т.к. имеется всего 2 типа оси: основная и вспомогательная.

ГРАФИК

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

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

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

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

ТОЧЕЧНАЯ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

КРУГОВАЯ

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

Совмещение разных типов диаграмм

Про совмещение Графика и Точечной диаграммы рассказано выше (см. раздел Точечная).

Совмещением Линейчатой и Гистограммы не стоит заниматься, т.к. выглядит это достаточно странно, почти также странно, как и совмещение Гистограммы с Круговой (см. также статью Основные типы диаграмм, раздел Круговая ).

Совет . Также можно посмотреть статью Совмещаем разные типы диаграмм .

Оригинальностью совмещения могут также похвастаться Гистограмма и Нормированная линейчатая с накоплением .

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

Для такой диаграммы постройте сначала Гистограмму с группировкой с двумя рядами (см. раздел Гистограмма в начале этой статьи). Затем выделите нужный ряд и нажмите кнопку Изменить тип диаграммы (вкладка Конструктор ). Выбрав График или График с Маркерами нажмите ОК.

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