Advanced Excel – вафельная диаграмма
Вафельная диаграмма добавляет красоты к вашей визуализации данных, если вы хотите отобразить прогресс в работе в процентах от выполненной задачи, достигнутой цели по сравнению с целью и т. Д. Она дает быстрый визуальный отчет о том, что вы хотите изобразить.
Вафельный график также известен как квадратный круговой график или матричный график.
Что такое вафельная карта?
Вафельная диаграмма – это ячейка 10 × 10 с ячейками, окрашенными в соответствии с условным форматированием. Сетка представляет значения в диапазоне 1% – 100%, и ячейки будут выделены с условным форматированием, примененным к значениям%, которые они содержат. Например, если процент завершения работы составляет 85%, он изображается путем форматирования всех ячеек, содержащих значения
Вафельный график выглядит так, как показано ниже.
Преимущества вафельной диаграммы
Вафельный график имеет следующие преимущества –
- Это визуально интересно.
- Это очень читабельно.
- Это можно обнаружить.
- Это не искажает данные.
- Это обеспечивает визуальное общение помимо простой визуализации данных.
Использование вафельной диаграммы
Вафельная диаграмма используется для абсолютно плоских данных, которые складываются до 100%. Процент переменной подсвечивается, чтобы дать изображение по количеству выделенных ячеек. Он может быть использован для различных целей, включая следующие –
- Для отображения процента выполненных работ.
- Для отображения процента достигнутого прогресса.
- Изобразить расходы, понесенные по сравнению с бюджетом.
- Для отображения прибыли%.
- Чтобы изобразить фактическое значение, достигнутое в сравнении с поставленной целью, скажем, в продажах.
- Визуализировать прогресс компании в сравнении с поставленными целями.
- Чтобы отобразить процент сдачи на экзамене в колледже / городе / штате.
Создание сетки вафельных диаграмм
Для вафельной диаграммы вам нужно сначала создать сетку 10 × 10 из квадратных ячеек, чтобы сама сетка была квадратной.
Шаг 1 – Создайте квадратную сетку 10 × 10 на листе Excel, отрегулировав ширину ячейки.
Шаг 2 – Заполните ячейки значениями%, начиная с 1% в левой нижней ячейке и заканчивая 100% в правой верхней ячейке.
Шаг 3 – Уменьшите размер шрифта так, чтобы все значения были видны, но не меняли форму сетки.
Это сетка, которую вы будете использовать для вафельной диаграммы.
Создание вафельной диаграммы
Предположим, у вас есть следующие данные –
Шаг 1 – Создайте вафельную диаграмму, которая отображает% прибыли для региона Восток, применяя условное форматирование к созданной вами сетке следующим образом:
- Выберите Сетка.
- Нажмите Условное форматирование на ленте.
- Выберите Новое правило из выпадающего списка.
- Определите правило для форматирования значений
Нажмите Условное форматирование на ленте.
Выберите Новое правило из выпадающего списка.
Шаг 2 – Определите другое правило для форматирования значений> 85% (укажите ссылку на ячейку% прибыли) с цветом заливки и цветом шрифта, как светло-зеленым.
Шаг 3 – Дайте название диаграммы, указав ссылку на ячейку B3.
Как видите, выбор одного цвета для заливки и шрифта позволяет не отображать значения в%.
Шаг 4 – Присвойте ярлык диаграмме следующим образом.
- Вставьте текстовое поле в диаграмму.
- Дайте ссылку на ячейку C3 в текстовом поле.
Шаг 5 – Окрасьте границы ячейки белым.
Ваш вафельный график для региона Восток готов.
Создайте вафельные графики для регионов, т. Е. Севера, юга и запада, следующим образом:
- Создайте сетки для севера, юга и запада, как указано в предыдущем разделе.
- Для каждой таблицы примените условное форматирование, как указано выше, на основе соответствующего значения% прибыли.
Создайте сетки для севера, юга и запада, как указано в предыдущем разделе.
Для каждой таблицы примените условное форматирование, как указано выше, на основе соответствующего значения% прибыли.
Вы также можете создать вафельные диаграммы для разных регионов, выбрав разные цвета для условного форматирования.
Как вы можете видеть, цвета, выбранные для вафельных диаграмм справа, отличаются от цветов, выбранных для вафельных диаграмм слева.
Вафельная диаграмма в Excel
В открывшемся окне выбираем тип правила Форматировать только ячейки, которые содержат (Format only cells than contains) , чуть ниже в выпадающем списке выбираем вариант Меньше или равно (Less or equal) и указываем рядом ссылку на ячейку с исходным значением ($B$2). Задаём цвет заливки, нажав на кнопку Формат (Format) :
После нажатия на ОК получаем почти готовую диаграмму:
Для пущей красоты можно скрыть значения процентов в ячейках таблицы, задав для неё в окне Формат ячеек (Format cells) на вкладке Число (Number) пользовательский формат, состоящий из трёх подряд точек с запятой:
Затем на вкладке Вставка (Insert) жмём кнопку WordArt и, выбрав приглянувшийся дизайн надписи и вставив её поверх нашей таблицы, вводим в строку формул знак «равно» и делаем ссылку на ячейку с исходным значением:
Получаем надпись, текст которой автоматически обновляется из ячейки B2, отображая поверх нашей «вафли» текущее значение нашего параметра.
Способ 2. Вафельная диаграмма из линейчатой
Этот способ создания вафельной диаграммы основан на выпиливании её из стандартной линейчатой диаграммы, встроенной в Excel (горизонтальная гистограмма). Однако, сначала нам придется подготовить таблицу — источник данных для будущей диаграммы.
Начинаем с создания процентного ряда от 0% до 100% с шагом 10% (диапазон A4:A13). Затем добавляем к нему столбец с вычислением разности между значениями ряда и нашим исходным значением, которое нужно визуализировать из ячейки B2:
Затем добавляем столбец с вложенными друг в друга функциями ЕСЛИ (IF) , чтобы реализовать следующую логику:
- отрицательные значения заменяем на 0
- значения больше 10% на 10
- оставшиеся выводим как есть, но добавляем умножение на 100 (т.к. проценты в Excel представляют из себя числовые значения от 0 до 1, а нам нужно на выходе получить числа от 0 до 10)
Выделяем последний вычисленный столбец (диапазон C4:C13) и строим по нему линейчатую диаграмму на вкладке Вставка (Insert) :
. и получаем вот такую картину:
Осталось сделать эту диаграмму более похожей на «вафлю». Для этого:
-
Щёлкаем правой кнопкой мыши по синим столбцам, выбираем опцию Формат ряда данных (Format data series) и убираем Боковой зазор (Gap width) до нуля. Столбики становятся максимально широкими и сливаются в единое целое:
Дополнительно и при желании, можно настроить ещё цвет линий сетки, сделав их более яркими.
Вот и всё — вафля готова 🙂
Пара нюансов и лайфхаков вдогон:
- Если после создания диаграммы захочется скрыть вспомогательную таблицу A4:C13, оставив только ячейку B2 с исходным значением, то лучше щёлкнуть по диаграмме правой, затем команда Выбрать данные — Скрытые и пустые ячейки (Select data source — Hidden and Empty cells) и включить флажок Показывать данные в скрытых строках и столбцах (Show data from hidden rows and columns) . Иначе после скрытия исходных данных пропадет и диаграмма.
- Если такой вид диаграммы вам придётся делать ещё неоднократно, то имеет смысл сохранить созданную диаграмму как шаблон, щёлкнув по ней правой кнопкой мыши и выбрав команду Сохранить как шаблон (Save as Template) :
После этого вафельную диаграмму (по подготовленной таблице!) можно будет создать через выбор в стандартном окне типов диаграмм Excel, найдя её в разделе Шаблоны (Templates) :
Вот и всё. Теперь вы сможете делать вафли не только на кухне, но и в Excel 🙂
Ссылки по теме
- Что умеет условное форматирование в Excel
- Диаграммы «план-факт» в Microsoft Excel
- Имитация гистограмм значками с функциями ПОВТОР и СИМВОЛ
Вафельная диаграмма в Excel
Вафельная диаграмма (Waffle Chart) — один из типов диаграмм, которые обычно используют для визуализации прогресса. Логика тут предельно простая и очевидная — чем больше залитых квадратиков, тем ближе к цели:
С ходу можно придумать кучу ситуаций, где такая диаграмма была бы «в тему». Например, с её помощью удобно визуализировать:
- прогресс по проекту;
- различные KPI в любом бизнесе;
- заполняемость площадей или объемов (склады, строительство.);
Одна проблема: в Microsoft Excel нет такого типа диаграммы среди встроенных возможностей. Тем не менее, обойти это ограничение можно достаточно легко.
Вафельная диаграмма условным форматированием
Размечаем обрамлением табличку 10х10 квадратных ячеек и заполняем её снизу вверх возрастающими значениями от 1% до 100%.
Затем выделяем весь размеченный диапазон и выбираем Главная — Условное форматирование — Создать правило (Home — Conditional Formatting — Create Rule).
В открывшемся окне выбираем тип правила Форматировать только ячейки, которые содержат (Format only cells than contains), чуть ниже в выпадающем списке выбираем вариант Меньше или равно (Less or equal) и указываем рядом ссылку на ячейку с исходным значением ( $B$2 ). Задаём цвет заливки, нажав на кнопку Формат (Format):
После нажатия на ОК получаем почти готовую диаграмму:
Для пущей красоты можно скрыть значения процентов в ячейках таблицы, задав для неё в окне Формат ячеек (Format cells) на вкладке Число (Number) пользовательский формат, состоящий из трёх подряд точек с запятой:
Затем на вкладке Вставка (Insert) жмём кнопку WordArt и, выбрав приглянувшийся дизайн надписи и вставив её поверх нашей таблицы, вводим в строку формул знак «равно» и делаем ссылку на ячейку с исходным значением:
Получаем надпись, текст которой автоматически обновляется из ячейки B2 , отображая поверх нашей «вафли» текущее значение нашего параметра.
Doconomist — учет в электронных таблицах
Докономист — блог, посвященный бизнесу, финансам и аналитике. Этот блог идеален для частных предпринимателей, экономистов, финансистов, менеджеров и всех, кто работает в электронных таблицах Excel и Google Docs.
Вафельная диаграмма 2. Теперь условным форматированием
- Получить ссылку
- Электронная почта
- Другие приложения
Для тех, кто не знает, вафельная диаграмма — это интересная визуализация, которая дает процентное значение по отношению к цели. Это квадрат, разделенный на клетки сеткой 10х10, каждая клетка отображает 1% от цели — 100%. Количество клеток, окрашенных твоим любимым цветом зависит от метрики. Такая диаграмма — один из полезных способов для добавления интересной визуализации в твой отчет без искажения данных и не занимающая много места.
Пример диаграммы предлагаю скачать заранее, так как принцип построения ее несложный.
Несколько дней назад я писал о том, как сделать такую диаграмму при помощи графика в Экселе. Сегодня будем делать то же самое намного более легким способом, используя технику условного форматирования + еще немного хитрости. При помощи этой техники ты можешь расширить свои возможности и использовать более одного цвета, отображая прогресс и текущую цель. Например, желтый цвет в этой вафельной диаграмме отражает фактическую эффективность, а голубой цвет отражает целевую эффективность, которая должна быть достигнута на данный момент (месячная цель, квартальная цель и проч.)
Давай сделаем все пошагово:
Шаг 1. Создай все элементы диаграммы
Создай ячейку с метрикой, в нашем примере это клетка [В5].
Затем, если желаешь, создай целевую ячейку [В9], в случае необходимости добавить дополнительный уровень раскраски для текущей месячной, квартальной и т.д. цели.
Наконец, создай сетку 10х10 с диапазоном процентов от 1% до 100%.
Шаг 2. Создай раскраску цели
В случае, если ты добавляешь в диаграмму цель, то важно раскрасить ее клетки первыми. Выдели свою сетку 10х10 и иди в меню Главная > Условное форматирование > Создать правило.
Для сетки 10х10 создай привило так, чтобы раскрасить клетки со значением менее или равняющихся значению целевой клетки [В9]. Цвет установи как для фона, так и для шрифта — это визуально спрячет процентные значения из ячеек. Нажми ок, чтобы подтвердить свой выбор.
Шаг 3. Создай раскраску метрики
Оставляя сетку 10х10 выделенной, повтори предыдущее действие для метрики. Иди в меню Главная > Условное форматирование > Создать правило.
Для сетки 10х10 создай привило так, чтобы раскрасить клетки со значением менее или равняющихся значению клетки с метрикой [В5]. Так же цвет установи как для фона, так и для шрифта — это визуально спрячет процентные значения из ячеек. Нажми ок, чтобы подтвердить свой выбор.
Шаг 4. Поправь форматирование
Выдели всю свою сетку 10х10 и установи серый цвет по умолчанию для фона и шрифта. Так же установи белый цвет для границ.
На этом этапе, твоя сетка должна выглядеть примерно как на картинке ниже. Если ты поменяешь значения для метрики или цели, то твоя сетка должна автоматически перестроится под новые значения.
Шаг 5. Создай связанное изображение.
Скопируй все клетки в сетке 10х10 и затем нажми на стрелку вставки с выпадающим меню на вкладке Главная и выбери иконку: вставить Связанный рисунок.
Эксель вставит рисунок твоей сетки на лист. Подгони его размер под свои нужды. Связанный рисунок обновляется автоматом, так же как и ячейки сетки. Ты можешь его вставить в свой отчет.
Шаг 6. Добавь интерактивную надпись
Чтобы добавить наддпись к своей вафельной диаграмме, нажми в меню Вставка > Фигуры > Надпись
Выдели Надпись и на панели формул введи ссылку на свою метрику, после нажатия знака равно (=) стань на ячейку с метрикой [В5] и введи формулу, нажав Enter.
Поставь Надпись сверху вафельной диаграммы.
***
Наградой за твои мучения будет красивая визуализация метрики в сравнении с целью.
- Работа с текстом (всяческие задачи по сверке / сравнению / разбитию текста на куски)
- Работа по вводу данных (многоуровневая проверка данных при вводе)