Вафельная диаграмма в Excel

Вафельная диаграмма (Waffle Chart) - один из типов диаграмм, которые обычно используют для визуализации прогресса. Логика тут предельно простая и очевидная - чем больше залитых квадратиков, тем ближе к цели:

Вафельная диаграмма в Excel

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

  • прогресс по проекту
  • различные KPI в любом бизнесе
  • заполненяемость площадей или объемов (склады, строительство...)
  • ... и т.д.
Одна проблема: в Microsoft Excel нет такого типа диаграммы среди встроенных возможностей. Тем не менее, обойти это ограничение можно достаточно легко и даже не одним способом.

Способ 1. Вафельная диаграмма условным форматированием

Размечаем обрамлением табличку 10х10 квадратных ячеек и заполняем её (копированием, а не вручную, само-собой) снизу вверх возрастающими значениями от 1% до 100%

Заготовка для вафельной диаграммы

Затем выделяем весь размеченный диапазон (D3:M12) и выбираем Главная - Условное форматирование - Создать правило (Home - Conditional Formatting - Create Rule).

В открывшемся окне выбираем тип правила Форматировать только ячейки, которые содержат (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):

Строим диаграмму

... и получаем вот такую картину:

Готовая диаграмма

Осталось сделать эту диаграмму более похожей на "вафлю". Для этого:

  1. Щёлкаем правой кнопкой мыши по синим столбцам, выбираем опцию Формат ряда данных (Format data series) и убираем Боковой зазор (Gap width) до нуля. Столбики становятся максимально широкими и сливаются в единое целое:

    Убираем зазор между рядами

  2. Там же на вкладке форматирования задаём для синих столбиков полупрозрачную заливку:

    Полупрозрачная заливка

  3. Щелкаем по горизонтальной оси правой кнопкой мыши, выбираем команду Формат оси (Format axis) и задаем пределы от 0 до 10 и шаг основных делений равный 1:

    Настраиваем ось

  4. Используя иконку со знаком "плюс" в правом верхнем углу диаграммы, выключаем название диаграммы, оси, названия осей и, наоборот, добавляем основные вертикальные и горизонтальные линии сетки:

    Настраиваем линии сетки
    Дополнительно и при желании, можно настроить ещё цвет линий сетки, сделав их более яркими.

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

    Готовая вафельная диаграмма

Вот и всё - вафля готова :)

Нюансы

Пара нюансов и лайфхаков вдогон:

  • Если после создания диаграммы захочется скрыть вспомогательную таблицу A4:C13, оставив только ячейку B2 с исходным значением, то лучше щёлкнуть по диаграмме правой, затем команда Выбрать данные - Скрытые и пустые ячейки (Select data source - Hidden and Empty cells) и включить флажок Показывать данные в скрытых строках и столбцах (Show data from hidden rows and columns). Иначе после скрытия исходных данных пропадет и диаграмма.
  • Если такой вид диаграммы вам придётся делать ещё неоднократно, то имеет смысл сохранить созданную диаграмму как шаблон, щёлкнув по ней правой кнопкой мыши и выбрав команду Сохранить как шаблон (Save as Template):

    Сохраняем вафельную диаграмму как шаблон
    После этого вафельную диаграмму (по подготовленной таблице!) можно будет создать через выбор в стандартном окне типов диаграмм Excel, найдя её в разделе Шаблоны (Templates):

    Готовый шаблон вафельной диаграммы

Вот и всё. Теперь вы сможете делать вафли не только на кухне, но и в Excel :)

Ссылки по теме



05.10.2020 17:27:06
Здравствуйте Николай!
Скажите, а как быть если результат 100%. У меня появляется еще одна строка в графике. Как убрать эту строку?
Спасибо
05.10.2020 18:09:16
Всё, разобралась сама :)
Была не внимательна. Надо было поставить минимальное значение оси - 0, а не автоматически
Наверх