Как сделать вафельную диаграмму в Excel для сетки дашборда
Представляем вашему вниманию 3 метода создания Grid Chart в Excel. По шагам от простого к сложному. Первый пример создание вафельной диаграммы по принципу маски.
Первый метод МАСКА для создания сеточного графика в Excel
D2 – это ячейка с исходным значением. В ячейке E2 – формула остатка от 100% после вычитания исходного значения.
Создаем таблицу для построения логической конструкции Waffle Chart из линейной горизонтальной гистограммы Excel. Первый столбец – шаги. Второй столбец содержит формулу с логикой для преобразования горизонтальной гистограммы в Grid Chart.
Третий столбец – формула вычисления остатка незаполненной части Grid Chart, от величины исходного значения до 100% заполнения. Для построения горизонтальной гистограммы используем только последние два столбца Value и Remainder – диапазон ячеек I1:J11. Устанавливаем фиксированные минимальные и максимальные значения для горизонтальной оси X в диапазоне от 0% до 100%.
Открываем приложение PowerPoint для создания сложных векторах фигур. Возможности редактора фигур в PowerPoint более расширены по сравнению с MS Excel.
Делаем иконку в форме человечка собирая его из прямоугольников как конструктор. Выделяем все фигуры и выполняем слияние в одну целостную фигуру. Создаем целую армию копий человечков в один легион 100 человечков 10 на 10. Все копии человечков объединяем в одну фигуру.
Создаем еще одну фигуру в форме прямоугольника со скругленными углами. Накладываем фигуру с человечками на фигуру прямоугольника. Выделяем сначала фигуру прямоугольника, а потом фигуру с человечками, чтобы вычесть из прямоугольника формы человечков. В результате в PowerPoint получим перфорированную фигуру, которую невозможно сделать в Excel. Этот объект будет выполнять функцию маски.
Окрашиваем маску в стильные цвета градиента. Переносим фигуру перфорированного прямоугольника на новый лист Excel. Там же перенесем ранее созданную гистограмму.
Настраиваем размер и украшаем цветами бары горизонтальной гистограммы.
Накладываем прямоугольник с отверстиями в форме человечков на гистограмму и получаем популярный эффект «маска». Это очень популярный метод в графическом дизайне его можно применять и в Excel благодаря приложению PowerPoint.
Добавляем элемент управления для Waffle Chart. Сначала выделим ячейку под исходные значения счетчика. Подключаемся к исходному значению счетчика через формулу преобразования величины значения в обратном порядке.
На вкладке разработчик из раздела «Вставка» выбираем инструмент управления формами – полоса прокрутки. В настройках параметров устанавливаем минимальные и максимальные значения, а также указываем ссылку на исходные значения счетчика.
Проверяем работоспособность Grid Chart.
Добавляем фигуру текстовой подписи TextBox, а в строке формул указываем для нее ссылку на ячейку с исходными значениями для чарта. На основе исходных значений чарта создаем вертикальную столбчатую гистограмму для информативности композиции презентации визуализации данных Grid Chart.
Метод пиксельная графика для построения вафельного чарта в Excel
Создания вафельной диаграммы по методу пиксельной графики существенно сложнее. Но он обладает более широкими возможностями. Например, метод пиксельная графика предоставляет возможность создания вафельной диаграммы в форме овала, треугольника и даже звезды.
Копируем в Excel из приложения PowerPoint ранее созданную фигуру иконки человечка и настраиваем размеры пропорций высоты, ширины. Добавляем прозрачную фигуру прямоугольника с соответственными пропорциями и группируем все в одну группу.
В ячейке С2 указываем текстовый спецсимвол прямоугольника с помощью альткода введенного комбинацией горячих клавиш Alt+219 (число вводим на бухгалтерской цифровой клавиатуре по правилам ввода альткодов).
Создаем объект TextBox и в строке формул указываем ссылку на ячейку C2, там находится текстовый спецсимвол прямоугольника. Он будет использоваться в качестве источника значений для TextBox на этапе настройки и тестирования.
Копируем группу фигур с человечком, чтобы использовать ее в качестве фонового рисунка для текстового спецсимвола в TextBox. Для этого в настройках форматирования TextBox в разделе Опции Текста используем в качестве заливки рисунок из буфера обмена. И настраиваем размер шрифта. Теперь если ячейка C2 содержит спецсимвол – это будет содержимое для объекта TextBox, а если ячейка C2 пуста – TextBox также будет пуст. Проверяем.
Подготавливаем диапазон исходных значений A1:B100 со спецсимволами для будущих 200 копий TextBox. 100 копий для позитивных значений и 100 – для отрицательных.
При каждом изменении ссылки на источник для элемента TextBox нужно заново форматировать текст. Хорошо, что имеется возможность сразу выделить 100 объектов и применить одновременно для всех форматирование текста заливкой картинкой из буфера обмена. А также размер шрифта.
Переносим TextBox на новый лист, проверяем в строке формул ссылку на ячейку, должна быть внешняя ссылка на другой лист «Processing». При необходимости изменить ссылку, выполняем форматирования текста в TextBox - соответственно. Выстраиваем армию 100 копий TextBox и выравниваем в прямоугольник с количеством 10 на 10.
Создаем прямоугольник со скругленными углами и с размерами как в предыдущем примере. А также из предыдущего примера копируем формат фигуры скругленного прямоугольника.
Подкладываем прямоугольник на задний план под 100 копий TextBox с фигурами человечков. Теперь каждому элементу TextBox нужно изменить ссылку на свою персональную ячейку сохраняя последовательность. Для самого нижнего TextBox в левом углу легиона указываем ссылку на ячейку A1, а для самого верхнего объекта в правом углу указываем ссылку на ячейку A100.
Группируем весь легион TextBox в одну группу и копируем ее. Делаем копию фигур и изменяем цвет человечка для новой заливки текста спецсимвола через буфер обмена.
Копию группы разгруппируем чтобы для ее элементов TextBox изменить ссылки на исходные значения и отформатировать. Аналогично в ссылках сохраняем правильную последовательность. Для самого нижнего TextBox в левом углу легиона указываем ссылку на ячейку B1, а для самого верхнего в правом углу указываем ссылку на ячейку B100.
Совмещаем два легиона TextBox с фигурками человечек и накладываем один поверх второго. В диапазоне A1:B100 исходных значений для двух легионов TextBox изменяем значения спецсимвола на формулы с логикой подстановки спецсимвола при условии.
Из предыдущего примера копируем элемент управления, инфографику и подпись текущего значения. На первый взгляд результат метода пиксельной графики ничем не отличается от предыдущего примера – «маски». Но сейчас пришло время презентовать преимущества метода пиксельной графики для создания вафельных чартов со сложными формами.
Скопируйте группу легиона TextBox для создания будущего альтернативного вафельного чарта. И нам понадобится снова заполнить диапазон исходных значений спецсимволом прямоугольником.
Разгруппируйте копию группы и подложите под нее фигуру овала. Переложите все объекты TextBox с человечками по контору овала, так чтобы их групповое положение было похоже на овал. При этом постарайтесь сильно не исказить последовательность, внизу начальные в верху конечные ссылки на исходные значения.
После сгруппируйте легион TextBox в форме овала и временно отключите новую группу из видимости, чтобы она не мешала создать аналогичный овал с другими цветами человечков.
Снова копируем группу, но с другим цветом и повторяем аналогичные действия, чтобы создать еще один овал из объектов TextBox. Две группы следует наложить соответственно и центрировать между собой.
Подкладываем копию прямоугольника на задний план под круглые группы. Проверяем две формы вафельного чата в действии.
Таким способом можно создавать вафельные чарты разных форм разных сложностей: треугольник, звезда, многоугольник и другие. Но в следящем примере мы рассмотрим, как еще можно эффективно использовать метод пикселя не для вариаций с формами, а для способов заполнения вафельного чарта.
Разные методы заполнения сетки вафельного чарта
Копируем одновременно два листа. Для этого выделяем их с зажатой клавишей Control на клавиатуре и перетаскиваем их ярлыки в новое местоположение. Далее на листе с исходными данными «Processing 2» создаем новую таблицу исходных значений.
Нам понадобится последовательность чисел натуральных чисел целочисленный квадратный корень: 1, 4, 9, 16, 25, 36, 49, 64, 81 и 100. Обратите внимание что в примере в ячейке H4 было намерено сделано ошибку – указано число 3 вместо 4, чтобы показать, что работоспособность чарта не нарушается если одно из чисел будет не целочисленный квадратный корень.
Добавляем столбец с формулами в диапазоне C1 : C100. Формула используется как промежуточная в логической цепочке вычисления всех формул для наглядности примера.
Диапазон ячеек исходных данных для всех легионов TextBox теперь будут содержать другие формулы. Заполняем диапазон A1:B100 новыми формулами.
Изменяем в настройках параметров полосы прокрутки минимальные и максимальные значения, проверяем ссылку.
Скачать Вафельный график для сетки дашборда в Excel 
Проверяем функционал нового метода! Теперь область вафельного чарта заполняется особым образом. В левом примере заполнение идет от угла, а в правом примере рандомно. Такие стили заполнений можно настраивать с помощью формулы разными способами. Например, в виде сердца или даже текстом. Метод пикселя более сложный, но открывает широкие возможности для полета вашего воображения.
