Як створити діаграму Mekko/Marimekko в Excel

Цей підручник покаже, як створити діаграму Марімекко у всіх версіях Excel: 2007, 2010, 2013, 2016 та 2022 роки.

Діаграма Мекко (також відома як діаграма Марімекко або графік мозаїки)-це двовимірна діаграма з накладанням, де ширина стовпця діаграми зазвичай представляє суму долара або відносний розмір сегмента ринку, тоді як висота стовпця діаграми розбиває кожен сегмент, виявляючи ключових гравців, а також їх відповідних акцій компанії.

Графік містить детальний огляд цільового ринку, все в одному місці-саме тому він використовується десятиліттями консультантами зі стратегії.

Однак у Excel досі немає вбудованого типу діаграми Marimekko, що означає, що вам доведеться будувати його вручну. З цієї причини ознайомтеся з нашою надбудовою Chart Creator-інструментом, який дозволяє складати розширені діаграми Excel за лічені секунди.

У цьому покроковому посібнику ви дізнаєтесь, як створити цю повністю настроювану діаграму Marimekko в Excel з нуля, навіть якщо ви зовсім не знайомі з електронними таблицями та дизайном діаграм:

Починаємо

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

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

Крок №1: Налаштуйте таблицю помічників.

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

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

Крок 2: Додайте дані сегмента ринку до допоміжної таблиці.

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

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

Крок №3: Заповніть буферні рядки.

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

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

Крок #4: Встановіть спеціальний формат числа для стовпця даних по горизонтальній осі.

Щоб здійснити магію для вашої діаграми Mekko, вам потрібно вручну побудувати масштаб горизонтальної осі з нуля у стовпці ліворуч від допоміжної таблиці (стовпець Н).

По -перше, закладіть основу, встановивши формат власного числа для стовпця осі.

Виділіть клітинки зліва від допоміжної таблиці, залишивши рядок заголовка (H3: H23), клацніть на них правою кнопкою миші та виберіть "Формат комірок.”

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

• В Номер перейдіть до На замовлення категорія.
• Введіть 0”%” в "Тип”Поле.

ПРИМІТКА: Не вводьте нічого в заголовок стовпця осі! Це зіпсує діаграму.

Крок 5: Обчисліть і додайте відсотки сегмента до стовпця осі.

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

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

Ось вправа: ви починаєте відлік з нуля (Н4) і додайте ринкові частки кожного послідовного сегмента по ходу.

Наприклад, перша група сегментів (I4: L6), виділений світло -зеленим кольором, означає сегмент кросовера. Логічно, нам потрібно додати його частку на ринку (В3, що показує 25%) до того, що ми вже маємо (0%) і введіть цю суму в клітинку H6.

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

Продовжуйте цей процес до кінця списку.

Крок #6: Знайдіть значення горизонтальної осі для рядків буфера.

Для створення ефекту «кроків» діаграми Мекко буферні рядки, хоча і невидимі, повинні відображатися на горизонтальній осі, щоб колонки були розділені.

Для цього у стовпці осі (стовпець Н), виділіть клітинки ліворуч від даних рядка буфера та заповніть їх значеннями, які або передують, або слідують за ними. Ось як це виглядає:

Крок №7: Обчисліть середні точки для інших клітинок у стовпці горизонтальної осі.

Решта порожніх клітинок у стовпці призначені для середніх точок-середніх показників для кожної групи сегментів-щоб допомогти вам правильно розташувати мітки даних.

Щоб обчислити середні точки, просто підсумуйте значення у стовпці осі, розташованому біля першого та останнього рядка кожної групи сегментів, і поділіть це число на 2.

Щоб знайти середину для першого відрізка (H5), використовуйте цю просту формулу: тип = (H6+H4)/2 в клітинку H5.

Після того, як ви знайшли свою першу середню точку, скопіюйте формулу в інші порожні клітинки.

Крок #8: Налаштуйте дані етикетки сегмента.

Настав час розробити мітки сегментів для діаграми Mekko. Щоб розмістити мітки над кожним стовпцем сегмента, нам потрібні значення X і Y. Ви вже визначили значення X (H3: H23). На щастя, знайти значення Y набагато простіше.

Праворуч від даних діаграми створіть новий стовпець з назвою “Маркер міток сегмента.”Потім введіть “100%” виключно в клітинки, розташовані в тих самих рядках, що і середні точки, залишаючи інші клітинки порожніми.

Опинившись там, приступайте до створення міток сегментів. Додайте ще один стовпець праворуч від допоміжної таблиці і назвіть його "Мітка сегмента.”

Знову ж таки, нам потрібні лише клітинки, які мають однакові рядки з серединами. Виберіть комірка N5 і введіть цю формулу:

1	= A3 & TEXT (B3, "(#%)")

Формула пов'язує назву сегмента та відповідну частку ринку в дужках.

Точно так само встановіть мітки для решти сегментів. Ось ще один знімок екрана, де ви можете орієнтуватися в процесі:

Крок 9: Налаштуйте мітки спільного доступу компанії.

Діаграму Mekko, де відсутні етикетки, що ілюструють акції компанії в кожному сегменті, було б важко прочитати. На щастя, їх розробка займає всього кілька секунд.

Скопіюйте один набір сегментних даних з кожної групи в допоміжній таблиці та вставте їх у клітинки праворуч від даних діаграми, залишивши решту порожніми. Знову ж таки, клітинки повинні відповідати серединам. Таким чином, перша колонка (стовпець О) буде відповідати за розміщення етикетки для першої компанії (БМВ), і так далі.

Крім того, ви можете відповідно назвати стовпці, щоб уникнути будь-яких переплутань (O2: R2). Краса цього методу в тому, що ви отримуєте повний контроль над даними етикетки.

Крок №10: Нанесіть діаграму складених областей.

Нарешті, після всієї цієї підготовки, час переходити до побудови діаграми.

1. Виділити тільки стовпці, що містять значення горизонтальної осі (стовпець Н), дані сегмента (колонки I: L) та маркери міток сегментів (стовпець М), включаючи рядок заголовка (Н2: М23).
2. Перейдіть до Вставити вкладка.
3. Натисніть кнопку «Вставити лінійну або діаграму області”.
4. Виберіть "Укладена площа.”

Крок #11: Покладіть основи для етикеток сегментів.

Настав час поступово перетворювати діаграму площ на графік Мекко. Спочатку розглянемо мітки сегментів.

Клацніть правою кнопкою миші Серія "Маркер сегментних міток" (трикутник у верхній частині) і виберіть “Змінити тип діаграми серії.”

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

Потім клацніть правою кнопкою миші маркери, що представляють Серія "Маркер сегментних міток" і натисніть «Додати мітки даних.”

Крок #12: Додайте та розташуйте мітки користувацького сегмента.

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

Клацніть правою кнопкою миші на будь-якій етикетці даних, що ілюструє Серія "Маркер сегментних міток" і виберіть "Форматувати мітки даних".

Далі зробіть наступне:

1. Перейдіть до Мітка Вкладка "Параметри".
2. Перевірте "Значення з клітин”Коробка.
3. Виділіть усі клітинки від стовпець Мітка сегмента (N3: N23)-просто не забудьте порожні внизу!
4. Натисніть «В ПОРЯДКУ.”
5. Під "Мітка містить,"Зніміть прапорець"Цінність”Коробка.
6. Під "Позиція етикетки,"Виберіть"Вище.”

Крок 13: Зробіть помітну лінійну діаграму невидимою.

Дивом, мітки сегментів з’являються по центру над кожним сегментом ринку. Основна лінійна діаграма нам більше не потрібна, тому приховаймо її.

Клацніть правою кнопкою миші будь-який маркер мітки сегмента та виберіть "Форматувати серію даних.”

У спливаючій панелі завдань у Заповнити & Лінія вкладка, під "Лінія", виберіть "Без лінії”Та перейдіть до Маркер вкладка.

Під "Параметри маркерів", вибирати “Жодного” щоб зникнути вся лінійна діаграма.

Крім того, двічі клацніть на іменному елементі легенди "Маркер міток сегмента" щоб вибрати його та натиснути "Видалити" ключ для його видалення-зробіть те ж саме з лініями сітки.

Крок 14: Змініть горизонтальну вісь.

У цей момент ви, можливо, стоїте на межі заколоту, дивуючись: "Де обіцяна діаграма Мекко?" Ну ось нарешті настав час нарешті зібрати плоди вашої праці!

Клацніть правою кнопкою миші по горизонтальній осі і виберіть "Формат осі.”

У вікні завдань, що з’явиться, змініть тип осі, щоб графік перетворився на діаграму Марімекко.

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

Крок 15: Змініть вертикальну вісь.

Не закриваючи панель завдань, перейдіть до вертикальної осі, вибравши стовпець чисел зліва та встановивши Максимальні межі значення до “1.”

Крок #16: Відрегулюйте розмір ділянки.

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

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

Після того, як ви виконали ці кроки, Excel надає вам ручки для регулювання площі сюжету так, як вам зручно. Потягніть ручки вниз, щоб перемістити дані діаграми від назви діаграми.

Крок 17: Вставте мітки акцій компанії в діаграму.

Нарешті, додайте мітки даних, що представляють акції компанії в кожному сегменті ринку.

По -перше, додайте мітки за замовчуванням для кожної серії даних, виконуючи ті ж кроки, описані у кроці №11 (клацніть правою кнопкою миші будь-який ряд даних> Додати мітки даних).

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

Потрапивши туди, замініть стандартні мітки даних на власні, так само, як ви це зробили на кроці 12 (Параметри мітки> Значення з клітинок> Виберіть діапазон міток даних> OK> Значення), окрім діапазонів міток даних-виберіть дані етикетки для кожної компанії з відповідних стовпців, які ви раніше налаштували (стовпці O: R).

Знову ж таки, не забудьте включити порожні клітинки внизу кожного стовпця-інакше трюк не спрацює.

Ось як виглядає процес:

Додайте мітки для кожної компанії. Ви також можете зробити їх жирними та змінити розмір шрифту, щоб вони виділялися.

Крок 18: Додайте межі, що розділяють блоки діаграми.

Нарешті, давайте зробимо деяку тонку настройку, щоб діаграма виглядала чудово. Ми почнемо з додавання меж для розділення блоків діаграм.

1. Клацніть правою кнопкою миші будь-який блок діаграми.
2. Натисніть кнопку «Контур”.
3. Виберіть білий.
4. Прокрутіть униз до Вага.
5. Встановіть ширину кордону на "2 ¼ пт."

Промийте та повторіть для решти компаній.

Крок 19: Перефарбуйте блоки діаграми.

Ви за крок від фінішу! Все, що вам потрібно зробити, це перефарбувати блоки діаграми, виконавши кілька простих кроків.

1. Клацніть правою кнопкою миші будь-який блок діаграми.
2. Натисніть кнопку «Заповнити”.
3. Виберіть потрібний колір з палітри.

Змініть або видаліть назву діаграми, і ваша діаграма Mekko буде готова.

І ось як ти це робиш! Тепер ви можете створювати повністю настроювані, вражаючі діаграми Marimekko з нуля, використовуючи основні вбудовані інструменти та функції Excel, щоб залишатися на висоті своєї гри у візуалізації даних.