Теперь поговорим о колоссально важных в анализе данных визуализации и комфортном восприятия информации. Очень удобно, когда на одной диаграмме можно сравнить сразу несколько данных, отследить динамику этих показателей, при этом все должно быть понятно и не сливаться в «кашу». Для этого в Excel можно создавать составные графики из разных их типов. Что же такое смешанная диаграмма, и как ее создать, рассмотрим далее.
Итак, мы создали диаграмму. Теперь нам необходимо нанести на диаграмму вторую шкалу. Например, процент затрат на логистику от стоимости продаж или какую долю занимает этот показатель в общем итоге. Т.е. это тот случай, когда необходимо совместить в диаграмме несколько типов, например Гистограмму и Линейный график.
Сперва все диаграммы строем одного вида. Теперь выбираем один ряд и для него меняем тип диаграммы. Кликнув на ряде правой кнопкой мыши, выбираем «Изменить тип диаграммы для ряда» (Change Series Chart Type) и выбираем тип «График» для ряда с процентами.
Если график получился незаметным на диаграмме, нужно добавить вспомогательную ось — правой кнопкой мыши нажимаем на линию или на название в легенде, в появившимся окошке выбираем — Формат ряда данных.
В открывшемся окне ищем Параметры ряда и меняем галочку на По вспомогательной оси.
Такая диаграмма очень хорошо демонстрирует отношения показателей (см рисунок 1).
Если показатели по выручке очень высокие (сколько было затрачено на 1 рубль доходов), то показатели за 12 месяц можно считать идеальными.
Закраска области диаграммы в Excel
Это интересная хитрость в Excel, уже не помню, где ей научился. Очень наглядно получается, если закрасить часть диаграммы для визуализации. Например, отделить начало нового периода, в примере начало нового года (месяц 1)
Необходимо добавить еще одну гистограмму с начала года с данными равными 1% (полному показателю по столбцу). Делаем отдельный столбец или строку с данными 1%, начиная с нового года.
Нажимаем Выбор источника — Добавить — Добавляем данные с 1%, т.е. добавляем еще один ряд данных. Повторяем все действия, чтобы получилась еще одна гистограмма по вспомогательной оси.
Для такой гистограммы нажимаем правой кнопкой мыши Параметры ряда — Боковой зазор значение 0, чтобы столбцы растянулись на всю область.
Построение комбинированных диаграмм
В тех случаях, когда требуется отобразить на одной диаграмме данные разного масштаба, полезно использовать комбинированные диаграммы. Типичный случай – абсолютные (рубли) и относительные (проценты) показатели. Например, размер дебиторской задолженности и её доля от реализации.
Комбинированной (или смешанной ) называется такая диаграмма, которая состоит из нескольких рядов данных и в которой используются различные типы диаграмм одновременно (например, гистограмма и график). Для построения комбинированных диаграмм нужно по меньшей мере два ряда данных. Примеры смешанных диаграмм размещены в книге Комбинированные
Существуют некоторые ограничения на построение комбинированных диаграмм:
· невозможно смешивать какие-либо типы диаграмм с объемными типами;
· некоторые комбинации типов диаграмм выглядят крайне неудовлетворительно (например, комбинация графика и лепестковой диаграммы);
· в смешанной диаграмме используется единственная область построения, следовательно, невозможно создать, например, комбинированную диаграмму, состоящую из трех круговых диаграмм;
· не поддерживается объединение графика и линейчатой диаграммы: ось категорий линейчатой диаграммы всегда направлена вертикально, а ось графика – горизонтально.
На рисунке представлена диаграмма, построенная по трем рядам данных. Причем значения температура воздуха и воды представлены в виде гистограммы, а количество осадков – графиком.
На рисунке представлена гистограмма, построенная по двум рядам данных:
Изменим тип диаграммы для второго ряда данных (Осадки ) и используем для этого ряда отдельную ось значений.
1. Выделим на диаграмме ряд данных Осадки и вызовем контекстное меню ряда.
2. Откроем окно Формат ряда данных (Format Data Series) и на вкладке Параметры ряда (Series Options) установим переключатель По вспомогательной оси (Secondary Axis) .
3. Не снимая выделения с ряда, выполним команду Работа с диаграммами – Конструктор – Тип – Изменить тип диаграммы (Chart Tools – Design – Type – Change Chart Type ) .
4. В диалоговом окне Изменение типа диаграммы выберем тип График (Line ) и щелкнем на кнопке ОК .
Данные по осадкам теперь визуализированы отрезками прямой линии, справа появилась новая Ось значений (V alue A xis) .
Важно понимать, что команда Работа с диаграммами – Конструктор – Тип – Изменить тип диаграммы (Chart Tools – Design – Type – Change Chart Type ) работает по-разному в зависимости от того, что выделено. Если выделен ряд диаграммы, то команда изменяет тип только этого ряда. Если выделен любой другой элемент диаграммы, то команда изменяет тип всей диаграммы.
Наложение диаграмм (O verlay C harts )
Под наложением диаграмм понимается размещение диаграмм как графических объектов: одна поверх другой. Диаграмма, расположенная сверху, как правило, имеет прозрачную область диаграммы и область построения диаграммы. В ней удаляются все элементы, кроме, например, маркеров и линий. Наложение диаграмм – ручная работа, требующая точного позиционирования объектов, настройки осей значений и категорий.
Для того чтобы диаграммы расположить «в стопку» в соответствующем порядке, их нужно выделять как объекты. Для выделения объекта, а не диаграммы, нажмите клавишу Ctrl и щелкните на диаграмме.
Рассмотрим простой пример (лист Наложение_1 книги Комбинированные ). Известно, что одним из ограничений на построение комбинированных диаграмм является невозможность смешивать какие-либо типы диаграмм с объемными типами. Метод наложения диаграмм позволяет совместить на листе график и объемную гистограмму. Проделаем следующие шаги:
1. Выделим в таблице данных диапазон А2:В7 и построим объемную гистограмму с группировкой. Удалим легенду диаграммы.
2. Выделим несмежные диапазоны А2:А7 и С2:С7 и построим график с маркерами. Удалим легенду диаграммы.
3. На второй диаграмме установим прозрачную заливку области диаграммы и области построения диаграммы, удалим оси. Отформатируем линии графика и маркеры.
4. Вручную совместим график и объемную гистограмму. Изменяя размеры объекта «График», добьемся совпадения маркеров графика с серединами столбцов гистограммы.
5. После заключительного форматирования получим наложенную диаграмму.
6. Если после выделения двух диаграмм как графических объектов, провести их группировку, то полученная наложенная диаграмма будет копироваться и перемещаться как один объект. Отметим, что попытка разместить диаграмму на отдельном листе приведет к разрушению наложения.
На следующем рисунке показано наложение объемной круговой диаграммы и объемной гистограммы. Общая рамка создает впечатление, что это одна диаграмма. Однако это две отдельные диаграммы. Рамка принадлежит не им, а ячейкам рабочего листа. Заголовок – свободно перемещаемая надпись. Данные для такого наложения приведены на листе Круговая _объемная книги Комбинированные .
Объемные гистограммы Excel не позволяют выводить дополнительные ряды в глубину. Наложение диаграмм позволяет обойти это ограничение. Достаточно построить три объемные гистограммы с накоплением (по каждому году в отдельности) и объединить их так, чтобы «создать» третью ось в глубину. Диаграмма позволяет визуально сравнивать результаты по трем параметрам – регионам, месяцам и годам. Исходная таблица данных находится на листе Три_объемных книги Комбинированные .
Если нужно на одной диаграмме изобразить разные ряды данных (по величине, по типу), то добавляется вспомогательная ось. Ее масштаб соответствует значениям связанного ряда. Excel позволяет добавить ось значений (вертикальную) и категорий (горизонтальную). Последний вариант используется при построении точечных и пузырьковых диаграмм.
Чтобы визуализировать разные типы данных в одной области построения, используются разные типы диаграмм. Тогда сразу видны значения, добавленные на вспомогательную ось. Рассмотрим, как сделать диаграмму с двумя осями в Excel.
Диаграмма с двумя вертикальными осями
Чтобы построить диаграмму с двумя вертикальными осями для примера построим таблицу следующего вида:
Столбцы В и С имеют ряды разного типа: рубли (числовые значения) и проценты. На основе таблицы построим простой график с маркерами:
Чтобы добавить вспомогательную вертикальную ось на данную область построения, нужно выбирать плоские виды диаграмм. На объемную диаграмму не получится добавить вспомогательную ось.
Как в диаграмме сделать две оси:
После нажатия кнопки ОК диаграммы приобретают следующий вид:
Верхние точки столбиков гистограммы и маркеры графика совпали, т.к. доля продаж за каждый месяц – это процентное выражение объема в рублях за каждый месяц. Одни и те же значения, только имеющие разное выражение.
Теперь в параметрах редактирования диаграмм появилась возможность работать со вспомогательной осью:
Чтобы удалить вспомогательную вертикальную ось, можно выделить ее, щелкнуть правой кнопкой мыши – нажать «Удалить».
Еще один способ. Перейти на вкладку «Макет». Для изменения форматирования и разметки каждой оси выбрать инструмент «Оси». Далее – «Вспомогательная вертикальная» - «Не показывать».
Вспомогательная горизонтальная ось
Чтобы добавить вторую горизонтальную ось (категорий), в области построения уже должна отображаться вспомогательная.
Порядок добавления:
На рисунке это будет выглядеть так:
Удалить дополнительную горизонталь значений можно теми же способами, что и вспомогательную вертикальную.
На любой вспомогательной оси можно показать лишь один ряд данных. Если необходимо отобразить несколько рядов, то для каждого процедуру добавления повторяют сначала.
Для дальнейшего применения построенную диаграмму с двумя осями можно сохранить в виде шаблона. Для этого нужно щелкнуть по области построения. На вкладке «Конструктор» нажать кнопку «Сохранить как шаблон».
Средствами Excel можно построить простой и объемный график, график с маркерами, цилиндрическую, коническую и столбчатую гистограммы, пузырьковую, лепестковую, точечную и линейчатую диаграммы. Все они облегчают восприятие статистических данных в той или иной сфере человеческой деятельности.
Если значения различных рядов значительно отличаются друг от друга, целесообразно отобразить их с помощью разных типов диаграмм. Excel позволяет сделать это в одной области построения. Рассмотрим комбинированные (смешанные) диаграммы в Excel.
Как построить комбинированную диаграмму в Excel
Способы построения комбинированной диаграммы в Excel:
- преобразование имеющейся диаграммы в комбинированную;
- добавление вспомогательной оси.
Создадим таблицу с данными, которые нужно отобразить на комбинированной диаграмме.
Выделим столбцы диапазона, включая заголовки. На вкладке «Вставка» в группе «Диаграммы» выберем обычный «График с маркерами».
В области построения появилось два графика, отображающих количество проданных единиц товара и объем продаж в рублях.
Каким образом можно комбинировать разные типы диаграмм? Щелкнем правой кнопкой мыши «К-во, шт.». В открывшемся окне выберем «Изменить тип для ряда».
Откроется меню с типами диаграмм. В разделе «Гистограмма» выберем плоскую столбчатую «Гистограмму с группировкой».
Нажмем ОК. По умолчанию высота столбиков соответствует вертикальной оси значений, на которую нанесены продажи. Но гистограмма должна отображать количество.
Выделим гистограмму, щелкнув по ней мышкой. Перейдем на вкладку «Макет». Группа «Текущий фрагмент» - инструмент «Формат выделенного фрагмента».
Откроется окно «Формат ряда данных». На вкладке «Параметры ряда» поставим галочку напротив «Построить ряд по вспомогательной оси».
Нажимаем кнопку «Закрыть».
Поработаем над внешним видом комбинированной диаграммы. Выделим область построения и перейдем на вкладку «Конструктор». Поменяем стиль. Удалим легенду (выделить – Delete). Добавим название и подписи вертикальных осей.
Для основной и вспомогательной оси выбираем вариант расположения (отдельно для каждой) и вводим подпись. Жмем Enter.
В данном примере мы использовали сразу два способа построения комбинированных диаграмм: изменяли тип для ряда и добавляли вспомогательную ось.
Если наборы данных значительно отличаются по масштабу, способу выражения, то без вспомогательной оси для создания смешанной диаграммы не обойтись. При использовании только одной шкалы один ряд становится практически не виден. Решение проблемы – применение для второго набора данных дополнительной оси.
Изменение для одного ряда данных типа диаграммы
Создадим смешанную диаграмму путем изменения для одного из рядов типа.
Таблица с исходными данными:
Построим обычную гистограмму на основе двух рядов данных:
Выделим столбики гистограммы, отображающие плановые показатели. На вкладке «Конструктор» в группе «Тип» нажмем кнопку «Изменить тип диаграммы». Выберем из предложенных вариантов «С областями».
Можем плановые показатели оставить в виде столбиков гистограммы, а фактические отобразить в виде графика с маркерами.
Внимание! Не все виды диаграмм можно комбинировать. Нельзя объединять некоторые объемные типы, пузырьковые с другими диаграммами. Программа Excel при невозможных комбинациях выдает ошибку.
Таким образом, смешанная диаграмма строится на основе двух и более рядов данных. В ней используются разные типы диаграмм. Или один тип (к примеру, гистограмма), но содержится вторая ось значений.
В Экселе можно результаты расчетов отобразить в виде диаграммы или графика, придавая им большую наглядность, а для сравнения иногда нужно построить два графика рядом. Как построить два графика в Excel на одном поле мы далее и рассмотрим.
Начнем с того, что не каждый тип диаграмм в Экселе сможет отобразить именно тот результат, который мы ожидаем. К примеру, имеются результаты расчетов для нескольких функций на основе одинаковых исходных данных. Если по этим данным строить обычную гистограмму или график, то исходные данные не будут учитываться при построении, а лишь их количество, между которыми будут задаваться одинаковые интервалы.
Выделяем два столбца результатов расчетов и строим обычную гистограмму.
Теперь попробуем добавить еще одну гистограмму к имеющимся с таким же количеством результатов расчетов. Для добавления графика в Экселе делаем активным имеющийся график, выделив его, и на появившейся вкладке «Конструктор» выбираем «Выбрать данные» . В появившемся окошке в разделе «Элементы легенды» нажимаем добавить, и указываем ячейки «Имя ряда:» и «Значения:» на листе, в качестве которых будут значения расчета функции «j» .
Теперь посмотрим, как будет выглядеть наша диаграмма, если мы к имеющимся гистограммам добавим еще одну, у которой количество значений почти в два раза больше. Добавим к графику значения функции «k» .
Как видно, последних добавленных значений гораздо больше, и они настолько малы, что их на гистограмме практически не видно.
Если изменить тип диаграммы с гистограммы на обычный график, результат получится в нашем случае более наглядным.
Если использовать для построения графиков в Экселе точечную диаграмму, то на полученных графиках будет учитываться не только результат расчетов, но и исходные данные, т.е. будет прослеживаться четкая зависимость между величинами.
Для создания точеного графика выделим столбец начальных значений, и пару столбцов результатов для двух разных функций. На вкладке «Вставка» выбираем точечную диаграмму с гладкими кривыми.
Для добавления еще одного графика выделяем имеющиеся, и на вкладке «Конструктор» нажимаем «Выбрать данные» .
В новом окошке в графе «Элементы легенды» нажимаем «Добавить» , и указываем ячейки для «Имя ряда:» , «Значения X:» и «Значения Y:» . Добавим таким образом функцию «j» на график.
