Как создать диаграмму Mekko / Marimekko в Excel

В этом руководстве будет показано, как создать диаграмму Marimekko во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

Диаграмма Mekko (также известная как диаграмма Marimekko или мозаичный график) - это двухмерная диаграмма с накоплением, где ширина столбца диаграммы обычно представляет сумму в долларах или относительный размер сегмента рынка, а высота столбца диаграммы разбивает каждый сегмент, показывая ключевых игроков, а также акций их соответствующих компаний.

На графике представлен подробный обзор целевого рынка в одном месте, поэтому консультанты по стратегии десятилетиями использовали его.

Однако в Excel по-прежнему нет встроенного типа диаграммы Marimekko, а это означает, что вам придется создать ее вручную. По этой причине обратите внимание на нашу надстройку для создания диаграмм, инструмент, который позволяет создавать расширенные диаграммы Excel за секунды.

В этом пошаговом руководстве вы узнаете, как с нуля создать эту полностью настраиваемую диаграмму Marimekko в Excel, даже если вы совершенно не знакомы с электронными таблицами и дизайном диаграмм:

Начиная

В целях иллюстрации предположим, что вы планируете запустить новую автомобильную марку и проникнуть в ключевые сегменты рынка со своими проектами. При этом вы решили построить мозаичный график, используя следующие данные:

Прежде чем мы начнем, полное раскрытие: для построения диаграммы Mekko требуется множество шагов, поэтому мы настоятельно рекомендуем вам загрузить шаблон, чтобы вам было легче следовать руководству. Только не забывайте, что, поскольку ваши фактические данные будут отличаться от примера, вам придется внести соответствующие изменения.

Шаг № 1: Настройте вспомогательную таблицу.

Старая воинская мантра гласит: «Чем больше вы потеете на тренировках, тем меньше истекаете кровью в бою». Чтобы построить диаграмму Mekko, вы должны создать отдельную вспомогательную таблицу, в которой все данные диаграммы будут вычисляться и храниться.

Начнем с копирования заголовков столбцов, содержащих названия компаний (C2: F2) в пустые ячейки на пару столбцов от ваших фактических данных (I2: L2). Также введите “0%” в каждую ячейку под строкой заголовка (I3: L3), чтобы обозначить начало каждого столбца диаграммы.

Шаг № 2: Добавьте данные сегмента рынка во вспомогательную таблицу.

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

Важно различать сегменты с пустыми буферными строками, которые будут разделять столбцы диаграммы, удерживая всю структуру вместе.

Шаг № 3: Заполните строки буфера.

Поскольку строки буфера действуют как разделители и не представляют фактических данных, вы хотите, чтобы они были скрыты.

Для этого заполните строки буфера нулевыми процентными значениями, как показано на скриншоте ниже. Включите дополнительную строку нулей в конце таблицы:

Шаг №4: Установите собственный числовой формат для столбца данных горизонтальной оси.

Чтобы волшебство произошло с вашей диаграммой Mekko, вам нужно вручную построить шкалу горизонтальной оси с нуля в столбце слева от вспомогательной таблицы (столбец H).

Во-первых, заложите основу, установив собственный числовой формат для столбца оси.

Выделите ячейки слева от вспомогательной таблицы, опуская строку заголовка (H3: H23), щелкните их правой кнопкой мыши и выберите «Формат ячеек.”

Теперь установите собственный числовой формат для диапазона ячеек:

• в Число вкладку, перейдите к Обычай категория.
• Входить 0”%” в «Тип" поле.

ЗАМЕТКА: Не вводите ничего в заголовок столбца оси! Это испортит график.

Шаг № 5: Вычислите и добавьте проценты сегментов в столбец оси.

Наш следующий шаг - составить карту ширины столбца для каждого сегмента, чтобы проиллюстрировать соответствующие доли рынка. Чем больше будет долей, тем шире будет столбец, и наоборот.

Для этого необходимо заполнить ячейки в столбце оси (столбец H), расположенный рядом с первой и последней строкой каждого набора данных сегмента во вспомогательной таблице.

Вот упражнение: вы начинаете отсчет с нуля (H4) и складывайте рыночные доли каждого последующего сегмента по мере продвижения.

Например, первая группа сегментов (I4: L6), выделенный светло-зеленым цветом, обозначает сегмент кроссовера. По логике, нам нужно добавить его долю на рынке (B3, который показывает 25%) к тому, что у нас уже есть (0%) и введите эту сумму в ячейку H6.

Точно так же добавьте процент второго сегмента (B4, который показывает 30%) к общему проценту, который у нас есть на данный момент (25%), чтобы получить ваши значения для сегмента хэтчбека, выделенного голубым шрифтом «25%" в H8 а также "55%" в H10.

Продолжайте этот процесс до конца списка.

Шаг № 6: Найдите значения горизонтальной оси для строк буфера.

Чтобы создать эффект «шагов» диаграммы Mekko, строки буфера, хотя и невидимые, должны отображаться на горизонтальной оси, чтобы столбцы оставались разделенными.

Для этого в столбце оси (столбец H), выберите ячейки слева от данных строки буфера и заполните их значениями, которые предшествуют или следуют за ними. Вот как это выглядит:

Шаг № 7: Рассчитайте средние точки для оставшихся ячеек в столбце горизонтальной оси.

Остальные пустые ячейки в столбце предназначены для средних точек - средних значений для каждой группы сегментов - чтобы помочь вам правильно расположить метки данных.

Чтобы вычислить средние точки, просто просуммируйте значения в столбце оси, расположенном рядом с первой и последней строкой каждой группы сегментов, и разделите это число на 2.

Чтобы найти середину первого сегмента (H5) используйте эту простую формулу: введите = (H6 + H4) / 2 в камеру H5.

Найдя первую среднюю точку, скопируйте формулу в оставшиеся пустые ячейки.

Шаг № 8: Настройте данные метки сегмента.

Пришло время разработать метки сегментов для диаграммы Mekko. Чтобы разместить метки наверху каждого столбца сегмента, нам нужны значения X и Y. Вы уже вычислили значения X (H3: H23). К счастью, найти значения Y намного проще.

Справа от данных диаграммы создайте новый столбец с именем «Маркер метки сегмента.”Затем введите “100%” только в ячейки, расположенные в тех же строках, что и средние точки, оставив оставшиеся ячейки пустыми.

Оказавшись там, приступайте к созданию меток сегментов. Добавьте еще один столбец справа от вспомогательной таблицы и назовите его «Метка сегмента.”

Опять же, нам нужны только ячейки, разделяющие одни и те же строки со средними точками. Выбирать ячейка N5 и введите в нее эту формулу:

1	= A3 & ТЕКСТ (B3; "(#%)")

Формула связывает воедино название сегмента и соответствующую долю рынка, заключенную в круглые скобки.

Таким же образом установите метки для остальных сегментов. Вот еще один снимок экрана для навигации по процессу:

Шаг № 9: Настройте ярлыки для акций компании.

График Mekko, на котором отсутствуют ярлыки, показывающие акции компании в каждом сегменте, будет трудно читать. К счастью, на их создание уходит всего несколько секунд.

Скопируйте один набор данных сегмента из каждой группы вспомогательной таблицы и вставьте его в ячейки справа от данных диаграммы, оставив остальные пустыми. Опять же, клетки должны соответствовать средним точкам. Таким образом, первый столбец (столбец O) будет отвечать за отображение этикеток для первой компании (BMW), и так далее.

Кроме того, вы можете назвать столбцы соответствующим образом, чтобы избежать путаницы (O2: R2). Прелесть этого метода в том, что вы получаете полный контроль над данными этикетки.

Шаг № 10: Постройте диаграмму с областями с накоплением.

Наконец, после всей этой подготовки пора переходить к построению диаграммы.

1. Выделять Только столбцы, содержащие значения горизонтальной оси (столбец H), данные сегмента (столбцы I: L), а маркеры меток сегментов (столбец M), включая строку заголовка (H2: M23).
2. Перейти к Вставлять таб.
3. Щелкните значок «Вставить линейную диаграмму или диаграмму с областями" кнопка.
4. Выбирать "Сложенная область.”

Шаг № 11: Заложите основу для маркировки сегментов.

Пришло время постепенно преобразовать диаграмму с областями в диаграмму Мекко. Давайте сначала разберемся с ярлыками сегментов.

Щелкните правой кнопкой мыши на Серия «Маркер метки сегмента» (треугольник на самом верху) и выберите «Изменить тип диаграммы серии.”

Далее для Серия «Маркер метки сегмента», изменение "Тип диаграммы" к "Линия с маркерами ».

Затем щелкните правой кнопкой мыши маркеры, представляющие Серия «Маркер метки сегмента» и нажмите «Добавьте метки данных.”

Шаг № 12: Добавьте и разместите метки пользовательских сегментов.

Теперь замените ярлыки по умолчанию на ранее подготовленные.

Щелкните правой кнопкой мыши любую метку данных, изображающую Серия «Маркер метки сегмента» и выберите «Форматировать метки данных».

Далее делаем следующее:

1. Перейти к Этикетка Вкладка Параметры.
2. Проверить "Значение из ячеек" коробка.
3. Выделите все ячейки из Метка сегмента столбца (N3: N23) - только не забудьте пустые внизу!
4. Нажмите «OK.”
5. Под "Этикетка содержит,"Снимите флажок"Ценить" коробка.
6. Под "Положение ярлыка," Выбрать "Выше.”

Шаг № 13: Сделайте вспомогательную линейную диаграмму невидимой.

Каким-то чудом метки сегментов появляются в центре над каждым сегментом рынка. Базовый линейный график нам больше не нужен, так что давайте скроем его.

Щелкните правой кнопкой мыши любой маркер метки сегмента и выберите «Форматировать ряд данных.”

Во всплывающей панели задач в Заливка и линия вкладка под "Линия," Выбрать "Нет линии"И переключитесь на Маркер таб.

Под «Параметры маркера», выбирать "Никто" чтобы исчезла вся линейная диаграмма.

Кроме того, дважды щелкните элемент легенды с именем «Маркер метки сегмента» выберите его и нажмите "Удалить" Ключ для его удаления - проделайте то же самое с линиями сетки.

Шаг № 14: Измените горизонтальную ось.

В этот момент вы, возможно, балансируете на грани мятежа, задаваясь вопросом: «Где же обещанная карта Мекко?» Что ж, наконец-то пришло время пожинать плоды своего труда!

Щелкните правой кнопкой мыши горизонтальную ось и выберите «Ось формата.”

В появившейся панели задач измените тип оси, чтобы увидеть преобразование графика в диаграмму Marimekko.

1. Перейдите к «Параметры оси”Таб.
2. Под "Тип оси," выбирать "Ось даты.”
3. Установите оба «Основные подразделения» а также «Незначительные единицы» значения к «10.”

Шаг №15: Измените вертикальную ось.

Не закрывая панель задач, перейдите к вертикальной оси, выбрав столбец чисел слева и установив Максимальные границы ценность для “1.”

Шаг №16: Отрегулируйте размер области графика.

Неизбежно вы обнаружите, что метки сегментов перекрывают заголовок диаграммы. Вот как можно обойти эту проблему.

• Выберите график диаграммы.
• Перейти к Формат таб.
• в Текущий выбор группы, щелкните раскрывающееся меню и выберите «Площадь участка.”

После того, как вы выполнили эти шаги, Excel предоставит вам ручки для настройки области построения, как вы сочтете нужным. Потяните ручки вниз, чтобы переместить данные диаграммы подальше от заголовка диаграммы.

Шаг № 17: Вставьте метки акций компании в диаграмму.

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

Во-первых, добавьте метки по умолчанию для каждой серии данных, выполнив те же шаги, что и в шаге № 11 (щелкните правой кнопкой мыши любую серию данных> Добавить метки данных).

Затем откройте Форматирование меток данных панель задач, следуя процессу, показанному на шаге № 12 (щелкните правой кнопкой мыши любую метку данных> Форматировать метки данных).

Оказавшись там, замените метки данных по умолчанию на собственные так же, как на шаге № 12 (Параметры метки> Значение из ячеек> Выбрать диапазон меток данных> ОК> Значение), за исключением диапазонов меток данных - выберите данные меток для каждой компании из соответствующих столбцов, которые вы предварительно настроили (столбцы O: R).

Опять же, не забудьте указать пустые ячейки внизу каждого столбца, иначе уловка не сработает.

Вот как выглядит процесс:

Продолжайте и добавьте ярлыки для каждой компании. Вы также можете сделать их жирными и изменить размер шрифта, чтобы они выделялись.

Шаг № 18: Добавьте границы, разделяющие блоки диаграммы.

В качестве заключительной настройки давайте сделаем небольшую настройку, чтобы диаграмма выглядела великолепно. Начнем с добавления границ для разделения блоков диаграммы.

1. Щелкните правой кнопкой мыши любой блок диаграммы.
2. Щелкните значок «Контур" кнопка.
3. Выберите белый цвет.
4. Прокрутите вниз до Масса.
5. Установите ширину границы на «2 ¼ пт.»

Промыть и повторить для остальных компаний.

Шаг №19: Перекрасить блоки диаграммы.

Вы в одном шаге от финиша! Все, что вам нужно сделать, это перекрасить блоки диаграммы, выполнив несколько простых шагов.

1. Щелкните правой кнопкой мыши любой блок диаграммы.
2. Щелкните значок «Наполнять" значок.
3. Выберите желаемый цвет из палитры.

Измените или удалите заголовок диаграммы, и ваша диаграмма Mekko готова.

И вот как вы это делаете! Теперь вы можете создавать полностью настраиваемые, умопомрачительные диаграммы Marimekko с нуля, используя базовые встроенные инструменты и функции Excel, чтобы оставаться на вершине своей игры по визуализации данных.