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

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

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

• прогресс по проекту
• различные 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 :)

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

• Что умеет условное форматирование в Excel
• Диаграммы "план-факт" в Microsoft Excel
• Имитация гистограмм значками с функциями ПОВТОР и СИМВОЛ