Перейти до основного матеріалу

Створіть діаграму marimekko в Excel

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

Нижче наведена діаграма Marimekko демонструє продажі напоїв, продуктів харчування та фруктів у компанії з 2016 по 2020 рік. Як бачите, ширина стовпців показує розмір сегмента ринку напоїв, продуктів харчування та фруктів за рік, а також кожен сегмент у у стовпці відображаються продажі для певної категорії.

Цей посібник продемонструє кроки для створення діаграми Marimekko в Excel поетапно.

Створіть діаграму Марімекко в Excel

Легко створюйте діаграму Марімекко за допомогою дивовижного інструменту
Завантажте зразок файлу

Створіть діаграму Марімекко в Excel

Припустимо, ви хочете створити діаграму Marimekko на основі даних, як показано на скріншоті нижче, ви можете зробити наступне, щоб її знизити.

Частина 1: Створення проміжної таблиці даних

1. Створіть проміжну таблицю даних на основі вихідних даних наступним чином.

Перший стовпець таблиці проміжних даних

Як показано на скріншоті нижче, значення в першому допоміжному стовпці представляють позицію, де кожен стовпець закінчується на осі X. Тут ми вказуємо мінімум осі X як 0, а максимум - 100, тому стовпець починається з 0 і закінчується 100. Ви можете зробити наступне, щоб отримати дані між мінімумом та максимумом.

> Введіть номер 0 у другу комірку (у цьому випадку A9) стовпця (заголовок стовпця не пустіть);
> Введіть формулу нижче в комірку A10 і натисніть Що натомість? Створіть віртуальну версію себе у ключ. Повторіть ту ж формулу в A11;
=SUM($B$2:$D$2)/SUM($B$2:$D$6)*100

> Введіть формулу нижче в A12 і натисніть Що натомість? Створіть віртуальну версію себе у ключ. Повторіть цю формулу в A13, щоб отримати той самий результат.
=SUM($B$3:$D$3)/SUM($B$2:$D$6)*100+A10

> Введіть формулу нижче в A14, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Повторіть цю формулу до A15, щоб отримати той самий результат.
=SUM($B$4:$D$4)/SUM($B$2:$D$6)*100+A12

> Введіть формулу нижче в A16, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Застосуйте цю формулу до A17, щоб отримати той самий результат.
=SUM($B$5:$D$5)/SUM($B$2:$D$6)*100+A14

> Введіть номер 100 в останню комірку (A18). І нарешті перша колонка завершена.

Інші стовпці таблиці проміжних даних

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

> Введіть ті самі заголовки, що й вихідні дані, в інші три стовпці;

> У другому стовпці введіть формулу нижче в першу порожню клітинку (B9), а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть цю комірку результату, перетягніть Ручка автозаповнення право до наступних двох комірок, а потім вниз до наступного рядка.
= B $ 2 / SUM ($ B $ 2: $ D $ 2)

> Введіть формулу нижче в B11, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть цю комірку результату, перетягніть Ручка автозаповнення право до наступних двох комірок, а потім вниз до наступного рядка.
= B $ 3 / SUM ($ B $ 3: $ D $ 3)

> Продовжуйте вводити формулу нижче в B13, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть цю комірку результату, перетягніть Ручка автозаповнення право до наступних двох комірок, а потім вниз до наступного рядка.
= B $ 4 / SUM ($ B $ 4: $ D $ 4)

> Введіть формулу нижче в B15, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть цю комірку результату, перетягніть Ручка автозаповнення право до наступних двох комірок, а потім вниз до наступного рядка.
= B $ 5 / SUM ($ B $ 5: $ D $ 5)

> Введіть формулу нижче в B17, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть цю комірку результату, перетягніть Ручка автозаповнення право до наступних двох комірок, а потім вниз до наступного рядка.
= B $ 6 / SUM ($ B $ 6: $ D $ 6)

Частина 2: Вставте діаграму областей з накопиченням на основі проміжних даних та відформатуйте її

2. Виберіть всю таблицю проміжних даних, натисніть Insert > Лінійна діаграма або діаграма області > Площа з накопиченням.

3. Клацніть правою кнопкою миші вісь X на діаграмі та виберіть Формат осі з меню, що клацне правою кнопкою миші.

4 В Формат осі виберіть панель Вісь дати опція під Параметри осі Вкладка.

5. Тримайте вибрану вісь X, а потім натисніть кнопку видаляти , щоб видалити його з діаграми.

Потім діаграма відображається наступним чином.

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

7 В Формат осі , будь ласка, налаштуйте наступним чином.

7.1) У Параметри осі розділ, змініть Максимальний поле до 1;
7.2) У Номер розділ, виберіть Відсоток від Категорія , а потім змініть Десяткові місця до 0.

Тепер діаграма відображається наступним чином.

8. Тепер нам потрібно додати межі, щоб показати зайнятість кожного даних у серії. Будь ласка, виконайте наступне.

8.1) Виберіть верхню сіру область (у цьому випадку серія Drink), натисніть Елементи діаграми , а потім перевірте Бари помилок коробка

8.2) Перейдіть до Форматувати ряд даних панель, натисніть кнопку Варіант серії спадну стрілку, а потім виберіть Серія “Напої” Y Бари помилок зі списку, щоб вибрати панелі помилок, які ви додали щойно.

8.3) Під Параметри панелі помилок вкладку, вам потрібно:
  • Select Мінус в Керівництво розділ;
  • Select Без шапки в Кінцевий стиль розділ;
  • Виберіть Відсоток і введіть 100 в текстове поле в Сума помилки .
8.4) Виберіть сіру смужку (серія Drink), а потім перейдіть до Форматувати ряд даних для налаштування наступним чином.
  • Натисніть Заливка та рядок піктограма;
  • У Кордон розділ, виберіть Суцільна лінія;
  • Виберіть чорний колір у полі Колір випадаючий список;
  • Змінити ширина до 1pt.

Тепер діаграма відображається так, як показано на скріншоті нижче.

9. Повторіть операції на кроці 8, щоб додати дільники до інших сегментів. І нарешті, діаграма показана, як показано нижче.

Частина 3: Вкажіть значення осі X діаграми Марімекко

10. Тепер вам потрібно розрахувати середнє значення для кожного стовпця і відобразити значення підкатегорій (дані першого стовпця вихідного діапазону даних) як значення осі X.

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

10.1) У першій комірці (тут я вибираю A22) першого рядка введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.
= A11 / 2

10.2) У другій комірці першого рядка застосуйте формулу нижче.
= (A13-A11) / 2 + A11
10.3) У третій комірці першого рядка застосуйте формулу нижче.
= (A15-A13) / 2 + A13
10.4) У четвертій комірці першого рядка застосуйте формулу нижче.
= (A17-A15) / 2 + A15
10.5) У п’ятій комірці першого рядка застосуйте формулу нижче.
= (A18-A17) / 2 + A17
10.6) Введіть число 0 у кожну комірку другого рядка. І нарешті, цей допоміжний діапазон відображається, як показано на знімку екрана нижче.

11. Клацніть правою кнопкою миші на діаграмі та натисніть Вибрати дані в меню, що клацне правою кнопкою миші.

12. На відкритті Виберіть Джерело даних у діалоговому вікні натисніть додавати кнопки.

13. Потім an Редагувати серію спливаюче діалогове вікно, будь ласка, виберіть клітинки, що містять значення 0 у вікні Значення серії , а потім клацніть на OK кнопки.

14. Коли він повернеться до Виберіть Джерело даних У діалоговому вікні ви можете побачити, як створена нова серія (Series4), клацніть на OK кнопка, щоб зберегти зміни.

15. Клацніть правою кнопкою миші на діаграмі та виберіть Змінити тип діаграми серії.

16 В Змінити тип діаграми у діалоговому вікні виберіть тип діаграми “Розсіяти прямими лініями та маркерами”Для Series4 у Виберіть тип діаграми та вісь для вашого ряду даних коробці. А потім клацніть ОК.

17. Клацніть правою кнопкою миші на діаграмі та виберіть Виберіть Дані.

18 В Виберіть Джерело даних діалоговому вікні, виберіть Series4 (ім'я серії, яке ви створили на кроці 14) і клацніть на Редагувати кнопка в Записи про легенду (Серія) коробка

19 В Редагувати серію діалоговому вікні, виберіть перші комірки рядка в the Значення серії X поле, а потім натисніть кнопку ОК.

20. клацання OK щоб зберегти зміни, коли він повернеться до Виберіть Джерело даних діалогове вікно.

Тепер внизу області сюжету додано нову серію, як показано на скріншоті нижче.

21. Тепер потрібно заховати лінію та маркери. Виберіть цю серію, перейдіть до Форматувати ряд даних а потім виконайте наступне.

21.1) Клацніть на Заливка та рядок піктограма;
21.2) Виберіть Без рядка під Лінія вкладка;

21.3) Клацніть на маркер вкладку, розгорніть Параметри маркера, А потім виберіть Ні.

22. Залиште вибрану серію, клацніть на Елементи діаграми , а потім перевірте Мітки даних коробка

23. Виберіть додані мітки, перейдіть до Форматувати мітки даних панелі та налаштуйте наступним чином.

23.1) Перевірте Значення з клітин коробка;

23.2) У Діапазон міток даних діалоговому вікні, виберіть клітинки, які потрібно відобразити як значення осі X, а потім натисніть ОК. Тут я вибираю перші комірки стовпця вихідного діапазону даних.

23.3) Поверніться до Форматувати мітки даних , зніміть прапорець Значення Y коробка в Етикетка містить і в розділі Позиція мітки розділ, виберіть Нижче варіант.

Тепер діаграма відображається наступним чином.

Частина 4: Відображення одного набору відсотків над шириною стовпців

Тепер нам потрібно відобразити один набір відсотків над шириною стовпців. По-перше, нам потрібно розрахувати відсотки кожного стовпця.

24. Оскільки на діаграмі є п’ять стовпців, вам потрібно розрахувати п’ять відсотків наступним чином.

24.1) Виберіть порожню клітинку в новому рядку (тут я вибираю A27), введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у , щоб отримати відсоток першого стовпця.
= 11 австралійських доларів / 100

24.2) Для другого відсотка, будь ласка, застосуйте формулу нижче.
= (A13-A11) / 100
24.3) Для третього відсотка застосуйте формулу нижче.
= (A15-A13) / 100
24.4) Для четвертого відсотка застосуйте формулу нижче.
= (A17-A15) / 100
24.5) Для п’ятого відсотка застосуйте формулу нижче.
= (A18-A17) / 100

25. У наступному рядку відсотка введіть номер 1 у кожну клітинку. Тоді ви отримаєте новий асортимент допоміжних засобів наступним чином.

26. Клацніть правою кнопкою миші на діаграмі та виберіть Виберіть Дані з меню, що клацне правою кнопкою миші.

27 В Виберіть Джерело даних у діалоговому вікні натисніть додавати кнопки.

28. На відкритті Редагувати серію діалогове вікно, потрібно зробити наступне.

28.1) У Значення серії X виберіть клітинки, що містять середнє значення для кожного стовпця, який ви розрахували на кроці 10.
Примітка: Тут середні значення використовуються для відображення відсотків у середині ширини стовпця.
28.2) У Значення серії Y виберіть клітинки, що містять номер 1.
Примітка: Оскільки мінімум і максимум осі Y дорівнюють 0 і 1, і нам потрібно відображати відсотки над площею ділянки, тут значення Y має бути 1.
28.3) Клацніть ОК.

29. Коли він повернеться до Виберіть Джерело даних У діалоговому вікні створюється нова серія (Series5), натисніть на OK кнопка, щоб зберегти зміни.

30. Клацніть правою кнопкою миші на діаграмі та виберіть Змінити тип діаграми серії.

31 В Змінити тип діаграми у діалоговому вікні виберіть тип діаграми “Розсіяти прямими лініями та маркерами”Для Series5 у Виберіть тип діаграми та вісь для вашого ряду даних коробці. А потім клацніть ОК.

Тепер діаграма відображається наступним чином.

32. Вам потрібно приховати рядок і маркери серії (Клацніть, щоб побачити, як).

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

Примітка: У цьому розділі після перевірки Значення з клітин Вам потрібно вибрати клітинки, що містять обчислювані відсотки у полі Діапазон міток даних діалогове вікно.

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

Частина 5: Відображення назв серій праворуч від ділянки ділянки

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

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

34.1) Виділіть порожню комірку (тут я вибираю A31) у новому рядку, введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ
= B6 / SUM (B6: D6) / 2

34.2) Виберіть наступну комірку (B31), введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть цю комірку результату, а потім перетягніть її Ручка автозаповнення право до наступної комірки C31.
=SUM($B$6:C6)/SUM($B$6:$D$6)-C6/SUM($B$6:$D$6)/2

35. У наступному новому рядку введіть номер 100 у кожну клітинку. Нарешті, створюється ще один новий допоміжний асортимент, як показано на знімку екрана.

Примітка: Тут число 100 представляє максимум осі Х.

36. Клацніть правою кнопкою миші на діаграмі та виберіть Виберіть Дані з контекстного меню.

37 В Виберіть Джерело даних у діалоговому вікні натисніть додавати кнопки.

38 В Редагувати серію діалоговому вікні, будь-ласка, виберіть відповідний діапазон наступним чином.

38.1) У Значення серії X виберіть клітинки, що містять номер 100;
38.2) У Значення серії Y виберіть клітинки, що містять середні значення для кожної серії останнього стовпця, який ви розрахували на кроці 34.
38.3) Клацніть на OK кнопки.

39. Коли він повернеться до Виберіть Джерело даних діалогове вікно натисніть кнопку OK зберегти зміни.

40. Клацніть правою кнопкою миші на діаграмі та виберіть Змінити тип діаграми серії з контекстного меню.

41 В Змінити тип діаграми у діалоговому вікні виберіть тип діаграми “Розсіяти прямими лініями та маркерами”Для Series6 у Виберіть тип діаграми та вісь для вашого ряду даних поле, а потім натисніть кнопку ОК.

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

42. Вам потрібно приховати рядок і маркери серії (Клацніть, щоб побачити, як).

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

Примітка: У цьому розділі після перевірки Значення з клітин Вам потрібно вибрати клітинки, що містять назви серій у вихідному діапазоні даних у Діапазон міток даних діалогове вікно.

Тепер діаграма відображається наступним чином.

Частина 6: Відображення значень серій на кожному сегменті у стовпцях

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

44. По-перше, вам потрібно обчислити середнє значення для кожного сегмента у стовпцях, застосуйте наведені нижче формули.

44.1) Починаючи з нового рядка, виділіть першу порожню клітинку (наприклад, A35), введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть цю комірку результату та перетягніть її Ручка автозаповнення аж до чотирьох клітинок нижче.
Примітка: Значення результатів у цій колонці представляють середні значення для сегментів плодових серій у кожному році.
= B2 / SUM (B2: D2) / 2

44.2) Виберіть наступну комірку B35, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Потім перетягніть його Ручка автозаповнення аж до чотирьох клітинок нижче.
Примітка: Значення результатів у цій колонці представляють середні значення для сегментів серії продуктів харчування у кожному році.
=C2/SUM(B2:D2)/2+Sheet1!B2/SUM(B2:D2)

44.3) Виберіть C35, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Потім перетягніть його Ручка автозаповнення аж до чотирьох клітинок нижче.
Примітка: Значення результатів у цій колонці представляють середні значення для сегментів серії Drink у кожному році.
=D2/SUM(B2:D2)/2+SUM(B2:C2)/SUM(B2:D2)

45. Клацніть правою кнопкою миші на діаграмі та натисніть Виберіть Дані в контекстному меню.

46 В Виберіть Джерело даних у діалоговому вікні натисніть додавати кнопки.

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

47.1) У Значення серії X виберіть клітинки, що містять середні значення для кожного стовпця, який ви розрахували на кроці 10.
Примітка: Ці значення використовуються для визначення положення осі X для серії Fruit.
47.2) У Значення серії Y виберіть перший стовпець допоміжного діапазону, який ви створили в цьому розділі.
Примітка: Ці значення використовуються для визначення положення осі Y для серії Fruit.
47.3) Клацніть на OK кнопки.

48. Повторіть кроки 46 і 47, використовуючи решту двох значень стовпців, щоб додати дві нові серії. Дивіться наведені нижче скріншоти:

49. Коли він повернеться до Виберіть Джерело даних діалоговому вікні, ви можете побачити, як додано три нові серії, натисніть OK зберегти зміни.

50. Клацніть правою кнопкою миші на діаграмі та виберіть Змінити тип діаграми серії з контекстного меню.

51 В Змінити тип діаграми діалоговому вікні, окремо виберіть тип діаграми “Розсіяти прямими лініями та маркерамиДля цих трьох нових серій у Виберіть тип діаграми та вісь для вашого ряду даних поле, а потім натисніть кнопку ОК.

Діаграма відображається наступним чином.

52. Вам потрібно окремо приховати лінії та маркери серії (Клацніть, щоб побачити, як).

53. Додайте мітки даних до серії (Клацніть, щоб побачити, як). Вкажіть Позицію мітки як Центр.

Примітки:

1) Для ярликів даних серії Drink, після перевірки Значення з клітин Вам потрібно вибрати фактичні значення серії Drink у вихідному діапазоні даних.

2) Для ярликів даних серії продуктів харчування після перевірки Значення з клітин Вам потрібно вибрати фактичні значення серії продуктів харчування у вихідному діапазоні даних.

3) Для ярликів даних із серії Fruit після перевірки Значення з клітин Вам потрібно вибрати фактичні значення серії Fruit у вихідному діапазоні даних.

Тепер діаграма відображається так, як показано на скріншоті нижче.

54. Видаліть із діаграми заголовок та легенду.

55. Зберігайте діаграму вибраною, перейдіть до Форматувати ряд даних панелі, а потім виберіть Площа ділянки в Параметри серії випадаючий список.

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

Тепер діаграма Марімекко завершена.

Легко створюйте діаграму marimekko в Excel

Команда Діаграма Марімекко корисність Kutools для Excel може допомогти вам швидко створити діаграму marimekko в Excel за допомогою декількох клацань лише як показано нижче.
Завантажте та спробуйте зараз! 30-денний безкоштовний трейл

Завантажте зразок файлу

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів:  12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки Excel ...)  |  ... і більше

Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...

Опис

Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
Comments (1)
No ratings yet. Be the first to rate!
Louison
Louison
This comment was minimized by the moderator on the site
Bonjour, je réussis à faire le graphique, mais les angles ne sont pas droits.
La forme est la même que vous, avant de passer au format dates.
Il faut simplement faire cette manipulation, ou d'autres modifications sont nécessaires ?
Merci beaucoup
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations