Как построить кроссплот в excel
Спарклайн в Excel – это график в ячейке (можно назвать мини-графиком). Спарклайн очень удобный дополнительный инструмент для презентации тенденции общего тренда в отчетах. Он эффектно дополняет общее графическое представление о поведении конкретной ситуации. Но определенно его нельзя использовать для детального анализа данных. Почему так и какие преимущества спарклайнов перед обычными графиками? Ответим на этот вопрос конкретным примером.
Как сделать спарклайн в Excel
Разберем на конкретном примере, что такое спарклайны в Excel и как их использовать. Предприятие имеет 5 магазинов в разных районах города. Возле таблицы с показателями оборотов в месяцах первого квартала разместите мини-графики в ячейках отображающие тенденцию изменения оборотов за текущий период:
- Заполните таблицу данными так как показано ниже на рисунке:
- Выделите диапазон B2:D6 и выберите инструмент: «Вставка»-«Спарклайны»-«График».
- В появившемся диалоговом окне «Создание спарклайнов» укажите значение в полю «Диапазон расположения», выделив диапазон E2:E6. И нажмите ОК.
- Теперь если выделить диапазон ячеек E2:E6 у нас появляется дополнительная панель инструментов «Работа со спаркалнйами». В ней выберите инструмент: «Конструктор»-«Цвет маркера»-«Маркеры»-«Оранжевый 25%». Это действие сделало мини-графики более читабельными и привлекательными.
Несмотря на то, что спарклайны – это маленькие графики в ячейках их значения все равно легче читать, чем из таблицы. Мы не можем детально проанализировать ситуацию по каждому магазину. Но нам легко быстро оценить общую ситуацию по всех магазина одновременно. Которые магазины хорошо развиваются, а которые нет.
Чтобы оценить преимущества спарклайнов, создайте обычных график и сравните:
- Выделите диапазон A1:D6. Потом выберите инструмент: «Вставка»-«Диаграммы»-«График».
- Так как у нас категорий (месяцев) меньше чем рядов (магазинов) нужно поменять значения в строках и столбцах. Для этого выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Строка/столбец».
В результате у нас получилась картинка, которая не требует лишних комментариев.
Если Вы активно работаете с диаграммами в Excel, то наверняка знаете, что их в программе представлено достаточно много. Графики, гистограммы, линейчатые, круговые, лепестковые и т.д. Подходящий вариант для своих данных можно найти практически всегда. Однако, некоторые задачи визуализации решить стандартными средствами не получится.
Одна из самых распространенных проблем - отсутствие красивой и наглядной диаграммы для графического отображения единственного показателя (или, на языке диаграмм, единственной точки данных). Под это описание подходит, например, показатель KPI. Он обычно выражается в % в диапазоне от 0 до 100. Ни одна из встроенных диаграмм сходу не дает красивого и наглядного отображения такого показателя.
Но если проявить немного фантазии и навыков владения Excel, то можно создать собственные наглядные типы диаграмм на основе стандартных, которые красиво и легко решат поставленную задачу. Разберем построение трех типов таких диаграмм: Термометр , Круговая шкала и Спидометр . Если Вы не знаете как выполнить какое-то действие, указанное в статье, то можете посмотреть подробнейший пошаговый видеоурок по этой теме со всеми объяснениями . Видео найдете в конце статьи . Итоговый результат будет выглядеть вот так:
Термометр
Построение любой диаграммы начинается с подготовки исходных данных. В нашем случае у каждой диаграммы будет одна главная точка данных - показатель выполнения KPI в процентах от 0 до 100 . Для наглядности мы также создадим цветную шкалу, которая будет обозначать зоны выполнения KPI (Плохо - Нормально - Хорошо). Это будет второй набор данных.
Таким образом, для построения диаграммы Термометр нам нужны следующие небольшие таблички с данными:
Обратите внимание на таблицу для шкалы. Проценты в ней указаны по следующему принципу:
- Плохая зона - первые 30% (то есть от 0% до 30%)
- Нормальная зона - следующие 40% (то есть от 30% до 70%)
- Хорошая зона - последние 30% (то есть от 70% до 100%)
Общая сумма процентов, очевидно, должна быть равна 100%.
Построение диаграммы
Теперь по этим данным можно строить диаграмму. Выделяем таблицу со шкалой и строим " Нормированную гистограмму с накоплением ". Скорее всего, Excel построит три одинаковых столбца для каждого ряда: Плохо, Норма и Хорошо . Чтобы они приняли вид шкалы, надо выделить диаграмму и на вкладке " Конструктор " выбрать " Строка/столбец ". После выполнения этой команды шкала обретет нормальный вид.
Далее нужно убрать с нее все лишнее: название, подпись ряда данных. Также необходимо настроить цвет каждой зоны на шкале, и ширину диаграммы в целом и самой шкалы. Для изменения ширины самой шкалы, кликаем на ней правой кнопкой мыши, выбираем " Формат ряда данных " и меняем значение ползунка " Боковой зазор ". В итоге получится примерно следующее:
Теперь нужно на шкалу термометра добавить наш показатель KPI. Копируем его из нужной ячейки, выделяем диаграмму и нажимаем Ctrl+V . Скорее всего новые данные сольются с уже имеющейся шкалой и добавятся сверху зеленой зоны. Придется их донастроить вручную. Для начала меняем цвет самого верхнего сектора шкалы на черный. Затем кликаем правой кнопкой мыши на верхней части зеленой шкалы и выбираем " Изменить тип диаграммы для ряда ". В этом окне надо будет установить для нового добавленного ряда галочку " Вспомогательная ось " и тип диаграммы - " Гистограмма с группировкой ".
После нажатия кнопки OK нужно будет обязательно удалить вспомогательную ось с процентами и настроить ширину черного столбца ("ртутной полоски" нашего термометра). Останется только кликнуть на этом столбце правой кнопкой мыши и выбрать " Добавить подписи данных ". Диаграмма-термометр готова!
Круговая шкала
Для построение диаграммы с круговой шкалой нам, помимо самого показателя KPI, также понадобятся дополнительные данные для построения шкалы. В данном случае все просто. Нам нужен столбец из единичек. Количество единиц = количество делений на нашей круговой шкале. Для создания двадцати делений нам нужен будет столбец из двадцати единиц. Кроме того, понадобится не только показатель выполнения KPI, но и количество процентов, которых не хватило до 100%. Его вычислить просто: 1 - KPI . Исходные данные имеют такой вид:
Построение диаграммы
Построение начинается с создания шкалы. Выделяем столбец с единицами и строим кольцевую диаграмму. Удаляем всё лишнее: название и легенду. По умолчанию, диаграмма будет иметь очень пестрый вид. Каждый из 20 секторов будет окрашен в свой цвет.
Нужно будет вручную перекрасить шкалу в правильные цвета. Кликаете на кольцо правой кнопкой мыши и применяете нужную заливку сразу ко всем секторам. Затем придется задать вручную цвет для некоторых секторов. Итак, у нас 20 секторов. Мы знаем, что первые 30% - плохая зона. То есть первые 20 * 30% = 6 секторов будут красные. Следующие 8 секторов (40%) - будут желтые. Последние 6 секторов - зеленые.
Чтобы покрасить один сектор, кликните на кольцо, чтобы выделились все сектора. Затем кликните еще раз прямо по нужному сектору. Выделится только он. Измените его заливку. Затем, нажимая Tab , перемещайтесь на каждый следующий сектор и выбирайте нужные цвета. В итоге должно получиться вот так:
Теперь добавим на шкалу второе кольцо поверх первого. У него будет всего 2 зоны: выполненный KPI и невыполненный KPI. Копируем два числа из таблицы, выделяем диаграмму и жмем Ctrl+V . Появится второе (внешнее) кольцо на диаграмме. Кликаете на него правой кнопкой мыши и выбираете " Изменить тип диаграммы для ряда ". В окне изменения типа для Ряд2 включаете галочку " Вспомогательная ось ". Второе кольцо наложится сверху на первое.
Останется поменять заливку верхнего кольца. Для зоны выполненного KPI полностью убираете всю заливку, а для зоны невыполненного - ставите белую и задаете прозрачность в районе 20% (это делается на панели " Формат точки данных "). Также не лишним будет вставить внутрь кольца надпись, которая будет отображать процент выполнения KPI числом (для большей наглядности).
Раньше я такие видел только в специализированных ПО, типа STATISTICA, и для того, чтобы нарисовать подобную диаграмму в Excel, нужно было изрядно потрудиться. Теперь она есть в стандартном наборе Excel.
Зачем нужна такая диаграмма? Допустим, есть выборка для анализа. А еще лучше несколько выборок, которые нужно сравнить. Для этого рассчитывают различные показатели. Однако к любому расчету всегда хочется добавить наглядности, чтобы мозг перешел в режим образного представления, а не довольствовался сухими цифрами и формулами. Поэтому основные характеристики ловко изображают на рисунке. Отличным вариантом будет как раз диаграмма «ящик с усами».
На рисунке показан формат по умолчанию. Как видно, сравниваются две выборки путем изображения двух «ящиков с усами».
Что здесь что обозначает?
Крестик посередине – это среднее арифметическое по выборке.
Линия чуть выше или ниже крестика – медиана.
Нижняя и верхняя грань прямоугольника (типа ящика) соответствует первому и третьему квартилю (значениям, отделяющим ¼ и ¾ выборки). Расстояние между 1-м и 3-м квартилем – это межквартильный размах (или расстояние).
Горизонтальные черточки на конце «усов» – максимальное и минимальное значение (без учета выбросов, см. ниже).
Отдельные точки – это выбросы, которые показываются по умолчанию. Если значение выходит за пределы 1,5 межквартильных размаха от ближайшего квартиля, то оно считается аномальным. Их можно скрыть (см. ниже настройки).
Во всей красе «ящик с усами» проявляется при сравнении выборок, в которых данные делятся на категории. Допустим, провели некоторый эксперимент среди мужчин и женщин. Есть данные до и после эксперимента по обоим полам. Для анализа потребуется вычислить различные показатели. А если к этому добавить диаграмму «ящик с усами», то результат будет весьма наглядным.
Отлично видно, что после проведения эксперимента данные по мужчинам в целом уменьшились, а данные среди женщин наоборот, увеличились. Это не значит, что выборки больше не нужно анализировать (сравнивать, проверять гипотезы и т.д.). Но наглядность сильно улучшает понимание. Перейдем к настройкам.
Общий вид диаграммы настраивается стандартно. Можно менять цвет, добавлять подписи и т.д. Для этого есть две контекстные вкладки на ленте (Конструктор и Формат). Но есть настройки, предназначенные специально для этой диаграммы.
Выбираем какой-либо ряд и жмем Ctrl+1. Либо два раза кликаем по какому-нибудь «ящику». Можно через правую кнопку Формат ряда данных…. Справа вылазит панель настроек.
Рассмотрим по порядку.
Боковой зазор – регулирует ширину ящиков и расстояние между ними.
Показывать внутренние точки. Если поставить галочку, то на оси, где расположены «усы», точками будут показаны все значения. Так хорошо видно распределение внутри групп.
Показывать точки выбросов – отражать экстремальные значения.
Выбросы – это точки, выходящие за пределы 1,5 межквартильных размаха.
Показать средние метки – среднее арифметическое (крестики). Стоят по умолчанию, но можно скрыть.
Показать среднюю линию – только для различных категорий. Показывает изменения по категориям.
Если добавить линии, то изменения после эксперимента станут видны еще лучше. В справке написано, что соединяются медианы, но на графике почему-то соединяются средние. Чудеса.
Инклюзивная медиана или эксклюзивная медиана. Инклюзивная медиана включает в «ящик» квартильные значения , а эксклюзивная медиана не включает. При выборе «эксклюзивной медианы» верх и низ «ящика» соответствует средней между квартильным и следующим (от центра) значением. По умолчанию стоит «эксклюзивная». Пусть стоит дальше. Причем тут медиана, вообще не понял, – речь ведь про квартиль. Думал, криво перевели, но в английской версии те же названия. В общем, здесь лучше ничего не менять.
Как показано в ролике ниже, все делается очень быстро и просто.
Принцип построения поверхностных диаграмм в Excel можно сравнить с рельефными картами. Где положение пункта определяется не только долготой и широтой, но и третьей величиной – высотой.
Данное сравнение поможет понять, как создать на первый взгляд сложную поверхностную диаграмму в Excel и как ее использовать.
Построение поверхностной диаграммы в Excel
Практический пример применения и создания поверхностной диаграммы в Excel.
Напряжение излучения в квадратной комнате определено формулой z=[sin(x)*y] 2 . Начало осей координат расположено центру комнаты.
Визуально сложно определить место в комнате, где наиболее интенсивное излучение. Создадим графическое представление ситуации, которое будет читабельно даже для дилетантов.
Сначала выполним все необходимые расчеты и вычисления в таблице. А поверхностную диаграмму построим на основе уже полученных данных.
- Заполните таблицу как указано на рисунке.
- Выделите диапазон B2:L12 так, чтобы B2 активной ячейкой выделенного диапазона (выделяйте с верхнего левого угла B2 диапазона к нижнему правому углу до ячейки L12).
- Не снимая выделения, введите в строку формул: =(SIN(B$13)*$A2)^2 и нажмите комбинацию клавиш CTRL+Enter. Обратите внимание, как мы используем в аргументах формулы смешанные ссылки на ячейки.
- Между столбцами A и B вставьте новый столбец и заполните его вторую ячейку формулой: =" "&A2 (не забудьте поставить пробел между кавычками). Скопируйте эту формулу во все ячейки столбца до 12-ой строки (то есть заполните этой формулой диапазон ячеек B2:B12).
- Выделите диапазон: B2:M12 и выберите инструмент: «Вставка»-«Диаграммы»-«Другие»-«Поверхность».
Теперь четко видно на диаграмме что наибольшая интенсивность излучения находится в углах комнаты.
Чтобы правильно настроить горизонтальную ось X, щелкните по диаграмме, чтобы ее активировать и выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Выбрать данные».
В появившемся окне «Выбор источника данных» в правом разделе «Подписи горизонтальной оси (категории)» щелкните на кнопку «Изменить».
В окне «Подписи оси» измените значение, выделив диапазон ячеек C13:M13 и на всех диалоговых окнах нажмите ОК.
Краткое описание примера
Стоит отметить! При создании поверхностной диаграммы мы изменили числовые значения столбца A в текстовые, поместив их в столбец B с помощью формулы . Если бы мы этого не сделали, то Excel воспринял бы эти числовые значения (столбца A) как данные для построения поверхностной диаграммы, а не как подписи данных.
Если бы мы просто присвоили текстовый формат для значений столбца A (вместо дополнительного столбца с формулами), тогда мы просто получили бы ошибку при расчетах.
Вот в такой нехитрый способ мы красиво сделали подписи для осей диаграммы и не допустили ошибок при расчетах.
Правильно подобранное изображение может донести больше полезной информации, чем самая элегантная таблица. Очень эффектной является диаграмма типа спидометр, которая показывает текущее значение на некоторой ограниченной шкале. На этой диаграмме можно отображать, например, уровень KPI, выполнение плана, уровень обслуживания клиентов и другие показатели с ограниченным диапазоном изменения. Циферблат спидометра делится на зоны, стрелка показывает текущее состояние дел. Снизу можно добавить точное значение измеряемого показателя.
Такая диаграмма отлично встраивается в отчеты и дашборды, т.к. весьма информативна, не занимает много места и на вид красива. Однако этой диаграммы нет в стандартном наборе Excel (во всяком случае пока). Для ее построения, придется использовать доступные средства, скомбинировав кольцевую и круговую диаграммы, для шкалы и стрелки, соответственно.
Вначале об общем принципе. Шкала – это верхняя половина кольцевой диаграммы. Нижняя половина также есть, но она прозрачная. Стрелка – это контур видимого сектора круговой диаграммы. Там же есть еще два сектора, но они прозрачны. Местоположение стрелки определяет измеряемый показатель.
Теперь изучим, как сделать диаграмму-спидометр в Excel. Вначале подготовим данные для шкалы, для чего нужно задать 4 значения: величина нижней прозрачной части, красной, желтой и зеленой зоны (цвета и их количество, разумеется, можно выбирать самостоятельно). Т.к. прозрачная часть занимает половину диаграммы, то она должна быть равна сумме трех цветов. Для простоты пусть весь циферблат занимает 100 делений. Тогда красная зона (плохо) – 50, желтая (нормально) – 30 и зеленая (хорошо) – 20 (50+30+20=100). Чтобы получился полукруг, невидимая часть также должна быть равна 100.
Выделяем весь диапазон и создаем кольцевую диаграмму.
По умолчанию получится следующее.
В параметрах ряда делаем поворот на 90⁰.
Удаляем название и легенду.
Почти готово. Двойным нажатием заходим в каждый сектор и меняем цвет: нижний – прозрачный, остальные – красный, желтый и зеленый. Контур также убираем.
Получаем циферблат спидометра.
Теперь сделаем стрелку. Подготовим данные, по которым будут строится три сектора круговой диаграммы. Первый сектор – от нуля до стрелки (прозрачный), второй – стрелка, третий – оставшаяся часть круга (прозрачный).
На этот раз секторы должны быть подвижными и зависеть от измеряемого показателя. Результатом будет «отклонение стрелки» на соответствующую величину. Пусть показатель измеряется в процентах и его первоначальное значение равно 60%.
Как и с циферблатом, диапазон от 0 до 100% должен приходиться на верхний полукруг. Тогда весь круг – это 200%. Чтобы стрелка меняла свое положение, первый сектор (от которого строятся остальные) привяжем к значению измеряемого показателя. Стрелка имеет фиксированный размер, установим пока 2% (потом вообще уберем). Последний сектор – это разница между 200% и суммой первых двух секторов.
Теперь нужно добавить на имеющийся циферблат спидометра новый ряд данных, чтобы отобразить стрелку. Для этого нажимаем правой кнопкой мыши по диаграмме и в контекстном меню «Выбрать данные…».
Указываем источник данных (диапазон из трех значений) и ОК. Должно получиться примерно следующее.
Не нужно пугаться, вон тот оранжевый зародыш внизу – это будущая стрелка. Дело в том, что вместо нужной круговой диаграммы пока получилось второе кольцо. Поэтому в контекстном меню (через правую кнопку мыши) выбираем новый тип для этого ряда.
Меняем диаграмму на круговую.
Обязательно нужно выбрать вспомогательную ось, как на рисунке, иначе стрелка окажется под циферблатом. Осталось повернуть диаграмму на 270⁰ и сделать прозрачными ненужные секторы.
Не забываем убрать контуры секторов.
Чтобы стрелка была больше похожа на стрелку, а не на узкий кусочек пирога, зададим для нее вначале черный (или другой) контур, а затем ширину сектора вместо 2% сделаем 0%.
Сектор исчезнет, а контур превратится в черную линию.
Для более точного и отображения снизу можно добавить цифровое значение показателя. Это сделать очень просто. Вставим прямоугольник с закругленными углами.
Сделаем прозрачный фон, красный контур. Затем выделим полученную фигуру, поставим курсор в строку формул и сделаем ссылку на отображаемое значение.
Отформатируем, как нужно и получим окончательный вид спидометра.
Остался один нюанс. Дело в том, что, если значение выйдет за пределы от 0 до 100%, то стрелка окажется не известно где.
Сделать такую диаграмму также несложно. Отличие только в циферблате. В ролике ниже показана пошаговая инструкция, как в Excel сделать оба варианта спидометров.
Используйте диаграмму спидометр в Excel, чтобы удивить своих коллег, а также руководство. По кнопке ниже можно скачать файл с примерами.
Читайте также: