Диаграмма с отрицательными и положительными значениями
Идея этой диаграммы заключается в том, чтобы поместить слева от оси Y значения со знаком минус, а справа – со знаком плюс. Данные для диаграммы представлены на рис. 1.
Рис. 1. Исходные данные для диаграммы
с отрицательными и положительными значениями
1. Запустите приложение Microsoft Excel и сохраните файл Книга1 в своей рабочей папке под именем Диаграммы.xlsx.
2. Выделите диапазон A1:C12 и вставьте линейчатую диаграмму с группировкой. У вас должна получиться диаграмма, аналогичная диаграмме, представленной на рис. 2. Она похожа на то, что мы хотим получить, надо только её немного подкорректировать.
Рис. 2. Первый вариант диаграммы
3. Далее надо изменить параметры вертикальной оси. Выделите вертикальную ось и откройте диалог Формат оси. На вкладке Параметры оси установите флажок Обратный порядок категорий и в выпадающем списке Подписи оси выберите значение внизу.
4. После изменения порядка категорий горизонтальная ось оказывается сверху. Чтобы переместить её вниз надо выполнить две операции. Во-первых, откройте диалог форматирования вертикальной оси и для параметра Горизонтальная ось пересекает установите значение в максимальной категории. Во-вторых, откройте диалог форматирования горизонтальной оси и в выпадающем списке Подписи оси выберите значение вверху.
5. Также надо изменить параметры рядов данных так, чтобы они были расположены напротив друг друга. Выберите любой рад данных и откройте диалог Формат ряда данных. На вкладке Параметры ряда установите значение 100% для параметра Перекрытие рядов. Также можно изменить значение параметра Боковой зазор.
6. Можно также добавить подписи данных, изменить стиль диаграммы, изменить положение легенды. В итоге должна получиться диаграмма, аналогичная изображённой на рис. 3.
|
|
Рис. 3. Окончательный вариант диаграммы
Диаграмма с разорванной осью
Как показать маленькие и большие величины на одном графике? Если на диаграмме большинство элементов ряда данных близки по значению, но в массиве данных также присутствует пара значений, которые на порядок или два отличаются от всех остальных, то в таком случае построенная диаграмма будет не читабельной. Выйти из ситуации можно, разбив ось Y на две составляющие, где по одной части будут построены основные элементы ряда данных, а по другой – пиковые значения.
Конечный результат представлен на рис. 4. Здесь, нижняя часть, отображающая маленькие значения, построена по основной оси (с левой стороны), а большие значения построены по вспомогательной оси (с правой стороны). Обе оси неразрывны и масштабируются от нуля. Таким образом, данную диаграмму можно воспринимать как два графика, расположенных друг под другом.
1. Прежде всего, надо подготовить данные. Основной фокус будет заключаться в том, что значения, превышающие определённую границу, будут умножаться не некоторый коэффициент, который будет приближать большие значения к основному массиву. Для этого воспользуемся формулой =ЕСЛИ(B2>100;B2/10+100;B2), которая будет делить значения, превышающие 100 на 10 и прибавлять к ним 100. Формулу надо скопировать из ячейки C2 в ячейки C3:C16. Также добавим столбец для построения ряда данных, который будет визуально разграничивать график с маленькими и большими значениями. На рис. 4 – это серая пунктирная линия, проходящая посередине диаграммы. Этот график будет строиться по нулевой отметке вспомогательной оси, поэтому он заполнен нулями. Исходные данные для диаграммы представлены на рис. 5.
|
|
Рис. 4. Диаграмма с разорванной осью
Рис. 5. Исходные данные для графика с разорванной осью
2. Выделите столбцы с заголовками «Дата», «Пересчёт» и «Разделитель», т.е. несмежный диапазон A1:A16;C1:D16 и постройте график.
3. Выберите ряд данных «Разделитель» и настройте его отображение по вспомогательной вертикальной оси.
4. Настройте параметры осей. Для основной вертикальной оси надо установить минимальное значение равным 0, максимальное значение – равным 200, цену основных делений – равной 20.
5. Для того чтобы числа больше 100 не отображались, перейдите на вкладку Число, снимите флажок Связь с источником, в поле Код формата введите [=0]0;[<=100]0; и нажмите кнопку Добавить. Этот формат как раз и означает, что будут отображаться только числа, меньшие или равные 100. Если понадобится использовать добавленный формат повторно, его можно найти в диалоге Формат оси на вкладке Число в поле Тип, предварительно выбрав все форматы в поле Числовые форматы.
6. Для вспомогательной вертикальной оси надо установить минимальное значение равным
-1000, максимальное значение – равным 1000, цену основных делений – равной 200.
7. Для того чтобы на вспомогательной оси не отображались числа, меньшие или равные 0, надо задать код формата 0;; (см. пункт 5). Это значит, что для всех положительных значений будет применён общий формат, а для 0 и отрицательных значений никакой формат применён не будет. Нижняя часть вспомогательной оси должна исчезнуть, как показано на рис. 6. При этом ряд данных Разделитель переместится к центру.
|
|
Рис. 6. Вид графика после редактирования осей
8. Далее нужно визуально отделить верхнюю часть графика от нижней. Для этого выделите ряд данных Разделитель и задайте для линии ширину 20, белый цвет и прозрачность 20%.
9. Добавьте ещё один ряд данных со значениями из столбца Разделитель, чтобы иметь чёткую границу между малыми значениями и большими. Постройте его по вспомогательной оси и назовите Разделитель2. Отформатируйте его так, чтобы он представлял собой серую прерывистую линию.
10. Добавьте маркеры к ряду данных Пересчёт.
11. Желательно задать подписи данных для ряда Пересчёт. Однако тут есть проблема, которая состоит в том, что автоматически добавляются значения, взятые из столбца с модифицированными данными, а нам нужны значения из столбца с исходными данными. Поэтому поступим следующим образом. Выделите диаграмму, щёлкните правой кнопкой мыши и из меню выберите пункт Выбрать данные… В появившемся диалоге измените подписи горизонтальной оси на данные из столбца Данные. Добавьте подписи данных над маркерами. Откройте диалог Формат подписей данных и в поле Включить в подписи снимите флажок значения и установите флажок имена категорий.
12. Отформатируйте подписи данных.
13. Чтобы теперь вернуть даты как подписи оси, удалите основную горизонтальную ось, нажмите кнопку Оси, которая находится на вкладке Макет в группе Оси, и в выпадающем списке для вспомогательной горизонтальной оси выберите Слева направо.
14. Переместите вспомогательную горизонтальную ось и подписи оси вниз. Чтобы переместить вспомогательную горизонтальную ось надо открыть диалог форматирования вспомогательной вертикальной оси и установить, что горизонтальная ось пересекает вертикальную в точке -1000. В результате должен получиться график, аналогичный показанному на рис. 4.
Пулевая диаграмма
Пулевая диаграмма – вид гистограммы, разработанная экспертом по визуализации Stephen Few, которая служит в качестве замены датчиков. Целью пулевой диаграммы является отображение нескольких слоёв информации на небольшом участке. На рис. 7 отображена пулевая диаграмма с одним показателем эффективности в сравнении с целевым значением и качественной оценкой достижения цели: плохо, средне, хорошо, отлично.
Рис. 7. Пулевая диаграмма
Существует три основных части пулевой диаграммы. На заднем фоне показаны зоны различных уровней достижения, красный маркер в виде прямой полоски отображает целевое значение показателя, тёмный столбец внутри всей диаграммы показывает текущее значение индикатора эффективности. На рис. 8 изображены данные для пулевой диаграммы. Первые четыре значения (плохо, средне, хорошо, отлично) необходимы для построения столбца качественной оценки. Для удобства берутся процентные значения, которые в сумме дают 100%. Пятое значение необходимо для создания колонки с текущим значением, шестое – для создания маркера с целевым значением.
Рис. 8. Данные для пулевой диаграммы
1. Введите исходные данные для пулевой диаграммы.
2. Выделите диапазон A1:B6 и вставьте гистограмму с накоплением.
3. MS Excel построит диаграмму с одним рядом данных. Чтобы превратить её в пулевую диаграмму, необходимо поменять строки и столбы данных местами, при этом каждое значение превратится в отдельный ряд данных. Для того чтобы поменять местами строки и столбцы нажмите кнопку Строка/столбец, которая находится в группе Данные на вкладке Конструктор.
4. Выберите ряд данных Цель и измените для него тип диаграммы на график с маркерами, а также установите отображение этого ряда данных по вспомогательной оси.
5. Выберите ряд данных Показатель и установите отображение этого ряда данных по вспомогательной оси. Также задайте для ряда данных боковой зазор так, чтобы этот ряд был у́же остальных.
6. Удалите вспомогательную ось, чтобы масштаб обоих осей совпал для всех рядов данных. Также подкорректируйте параметры основной оси, чтобы диапазон значений был от 0% до 100%.
7. Удалите горизонтальную ось. Можно также удалить основную вертикальную ось. Измените цвета диаграммы.
8. Для ряда Цель установите тип маркера в виде линии размером 20-25, измените заливку маркера на соответствующий целевому цвет.
9. Диаграмма получилась вертикальная. Можно оставить её в таком виде, но можно и превратить в горизонтальную. Если вы не удалили основную вертикальную ось, то измените направление текста на оси. Для этого надо открыть диалог Формат оси и на вкладке Выравнивание в выпадающем списке Направление текста выберите повернуть весь текст на 270°.
10. Далее нам потребуется воспользоваться инструментом Камера, который не доступен в стандартном наборе инструментов ленты. Нажмите кнопку, которая находится справа от панели быстрого доступа, выберите Другие команды…, в появившемся диалоговом окне в поле Выбрать команды из выберите Все команды, найдите инструмент Камера и добавьте его на панель быстрого доступа.
11. Выделите ячейки, на переднем плане которых находится пулевая диаграмма (чтобы результат получился лучше, желательно изменить размер диаграммы так, чтобы он полностью перекрывал несколько ячеек по вертикали и по горизонтали).
12. Щёлкните по иконке Камера в панели быстрого доступа, при этом изображение указателя мышки сменится на небольшой крест. Щёлкните на то место на листе, куда вы хотите поместить горизонтальную диаграмму. MS Excel вставит снимок диапазона ячеек, который был выделен. Щёлкните по нему, по краям появятся маркеры редактирования картинок. Наведите указатель мыши на иконку в виде закручивающейся стрелки и поверните диаграмму. Обратите внимание, что вставленный рисунок изменяется вместе с исходной диаграммой при изменении данных диаграммы.