Графики и диаграммы (5)
Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (64)
Разное (41)
Баги и глюки Excel (4)

Два в одном - как сделать?

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

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

Важно: если вы планируете в своей диаграмме использовать тип Гистограмма (Columns) , то при создании диаграммы необходимо выбрать именно этот тип диаграммы. А затем уже добавлять остальные типы диаграмм, т.к. попытки назначить тип Гистограмма (Columns) после всех остальных приведут к тому, что все ряды станут типа Гистограмма (Columns) . Притом без каких-либо предупреждений.
Так же следует помнить, что нельзя совместить объемные и плоские типы диаграмм. Но при любой попытке совмещения плоских и объемных типов Excel сам предупредит об этом, так что это не так страшно.

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

Выбираем тип и смотрим как изменилась диаграмма.
Маленький нюанс: если ваши данные имеют в одной таблице большую разницу между собой (например суммы продаж в рублях и количество проданного товара) и вы хотите сравнить эти данные совместив в одной диаграмме - то изначально у вас ничего не выйдет, т.к. разница в числовых данных между рядами будет отличаться в десятки, а то и сотни раз. Что сделает данные по количеству практически не заметными. Чтобы этого избежать можно воспользоваться интересной возможностью - построить эти данные по другой оси. Правая кнопка мыши по ряду--группа Параметры ряда (Series Option) -По вспомогательной оси (Secondary Axis) :

Так же если щелкнуть по ряду данных правой кнопкой мыши и выбрать уже Формат ряда данных (Format Data Series) , то можно побродить по другим параметрам и изменить цвет заливки для отдельного ряда, границы, добавить тени и т.п. Это дает довольно большой простор для творчества и рассказать ту больше особо нечего - надо пробовать и творить:-)

Скачать пример:

(47,0 KiB, 14 126 скачиваний)

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Гистограмма в Excel – это способ построения наглядной диаграммы, отражающей изменение нескольких видов данных за какой-то период времени.

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

Как построить обновляемую гистограмму?

Имеем данные по продажам разных видов молочной продукции по каждому месяцу за 2015 год.

Построим обновляемую гистограмму, которая будет реагировать на вносимые в таблицу изменения. Выделим весь массив вместе с шапкой и кликнем на вкладку ВСТАВКА. Найдем так ДИАГРАММЫ – ГИСТОГРАММА и выберем самый первый тип. Он называется ГИСТОГРАММА С ГРУППИРОВКОЙ.

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

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



Гистограмма с накоплением

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

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

Гистограммы в Excel можно изменять. Так, если мы кликнем правой кнопкой мыши в пустом месте диаграммы и выберем ИЗМЕНИТЬ ТИП, то сможем несколько ее видоизменить. Поменяем тип нашей гистограммы с накоплением на нормированную. Результатом будет та же самая диаграмма, но по оси Y будут отражены соотношения в процентном эквиваленте.

Аналогично можно производить и другие изменения гистограммы, что мы и сделали:

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

Как объединить гистограмму и график в Excel?

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

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

Теперь изменим существующую диаграмму. Кликнем в пустом месте правой кнопкой и выберем ВЫБРАТЬ ДАННЫЕ. Появится такое поле, на котором будет предложено выбрать другой интервал. Выделяем всю таблицу снова, но уже охватывая и строку с выручкой.

Excel автоматически расширил область значений по оси Y, поэтому данные по количеству продаж остались в самом низу в виде незаметных столбиков.

Но такая гистограмма неверна, потому что на одной диаграмме у нас значатся числа в рублевом и количественном эквиваленте (рублей и литров). Поэтому нужно произвести изменения. Перенесем данные по выручке на правую сторону. Кликнем по фиолетовым столбикам правой кнопкой, выберем ФОРМАТ РЯДА ДАННЫХ и отметим ПО ВСПОМОГАТЕЛЬНОЙ ОСИ.

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

Но это все равно не очень удобно, потому что столбики почти сливаются. Поэтому произведем еще одно дополнительное действие: кликнем правой кнопкой по фиолетовым столбцам и выберем ИЗМЕНИТЬ ТИП ДИАГРАММЫ ДЛЯ РЯДА. Появится окно, в котором выбираем график, самый первый тип.

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

Рассмотрим построение в MS EXCEL 2010 диаграмм с несколькими рядами данных, а также использование вспомогательных осей и совмещение на одной диаграмме диаграмм различных типов.

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

ГИСТОГРАММА

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ГРАФИК

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

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

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

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

ТОЧЕЧНАЯ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

КРУГОВАЯ

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

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

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

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

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

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

Диаграмма с двумя вертикальными осями

Чтобы построить диаграмму с двумя вертикальными осями для примера построим таблицу следующего вида:

Столбцы В и С имеют ряды разного типа: рубли (числовые значения) и проценты. На основе таблицы построим простой график с маркерами:

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

Как в диаграмме сделать две оси:

После нажатия кнопки ОК диаграммы приобретают следующий вид:

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

Теперь в параметрах редактирования диаграмм появилась возможность работать со вспомогательной осью:

Чтобы удалить вспомогательную вертикальную ось, можно выделить ее, щелкнуть правой кнопкой мыши – нажать «Удалить».

Еще один способ. Перейти на вкладку «Макет». Для изменения форматирования и разметки каждой оси выбрать инструмент «Оси». Далее – «Вспомогательная вертикальная» - «Не показывать».



Вспомогательная горизонтальная ось

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

Порядок добавления:

На рисунке это будет выглядеть так:

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

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

Для дальнейшего применения построенную диаграмму с двумя осями можно сохранить в виде шаблона. Для этого нужно щелкнуть по области построения. На вкладке «Конструктор» нажать кнопку «Сохранить как шаблон».

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

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

Сперва все диаграммы строем одного вида. Теперь выбираем один ряд и для него меняем тип диаграммы. Кликнув на ряде правой кнопкой мыши, выбираем «Изменить тип диаграммы для ряда» (Change Series Chart Type) и выбираем тип «График» для ряда с процентами.

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

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

Такая диаграмма очень хорошо демонстрирует отношения показателей (см рисунок 1).

Если показатели по выручке очень высокие (сколько было затрачено на 1 рубль доходов), то показатели за 12 месяц можно считать идеальными.

Закраска области диаграммы в Excel

Это интересная хитрость в Excel, уже не помню, где ей научился. Очень наглядно получается, если закрасить часть диаграммы для визуализации. Например, отделить начало нового периода, в примере начало нового года (месяц 1)

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

Нажимаем Выбор источника — Добавить — Добавляем данные с 1%, т.е. добавляем еще один ряд данных. Повторяем все действия, чтобы получилась еще одна гистограмма по вспомогательной оси.

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

Операционные системы