Note: The other languages of the website are Google-translated. Back to English

Buat bagan marimekko di Excel

Bagan Marimekko juga dikenal sebagai bagan Mosaic, yang dapat digunakan untuk memvisualisasikan data dari dua atau lebih variabel kualitatif. Dalam bagan Marimekko, lebar kolom memperlihatkan sekumpulan persentase, dan tumpukan kolom memperlihatkan kumpulan persentase lainnya.

Bagan Marimekko di bawah ini menunjukkan penjualan Minuman, Makanan dan Buah di sebuah perusahaan dari tahun 2016 hingga 2020. Seperti yang Anda lihat, lebar kolom menunjukkan besarnya segmen pasar Minuman, Makanan dan Buah dalam satu tahun, dan setiap segmen di kolom menunjukkan penjualan untuk kategori tertentu.

Tutorial ini akan mendemonstrasikan langkah-langkah untuk membuat bagan Marimekko di Excel selangkah demi selangkah.

Buat bagan Marimekko di Excel

Buat bagan Marimekko dengan mudah menggunakan alat yang luar biasa
Unduh file contoh


Buat bagan Marimekko di Excel

Misalkan Anda ingin membuat bagan Marimekko berdasarkan data seperti gambar di bawah ini, Anda dapat melakukan hal berikut untuk menurunkannya.

Part1: Membuat tabel data menengah

1. Buat tabel data perantara berdasarkan data asli sebagai berikut.

Kolom pertama dari tabel data perantara

Seperti gambar di bawah ini yang ditunjukkan, nilai di kolom pembantu pertama mewakili posisi di mana setiap kolom berakhir pada sumbu X. Di sini kami menetapkan minimum sumbu X sebagai 0 dan maksimum 100, sehingga kolom dimulai dari 0 dan diakhiri dengan 100. Anda dapat melakukan hal berikut untuk mendapatkan data antara minimum dan maksimum.

> Masukkan angka 0 ke dalam sel kedua (A9 dalam kasus ini) dari kolom (biarkan tajuk kolom kosong);
> Masukkan rumus di bawah ini ke sel A10 dan tekan Enter kunci. Ulangi rumus yang sama di A11;
=SUM($B$2:$D$2)/SUM($B$2:$D$6)*100

> Masukkan rumus di bawah ini ke A12 dan tekan Enter kunci. Ulangi rumus ini di A13 untuk mendapatkan hasil yang sama.
=SUM($B$3:$D$3)/SUM($B$2:$D$6)*100+A10

> Masukkan rumus di bawah ini ke A14 lalu tekan Enter kunci. Ulangi rumus ini ke A15 untuk mendapatkan hasil yang sama.
=SUM($B$4:$D$4)/SUM($B$2:$D$6)*100+A12

> Masukkan rumus di bawah ini ke A16 lalu tekan Enter kunci. Terapkan rumus ini ke A17 untuk mendapatkan hasil yang sama.
=SUM($B$5:$D$5)/SUM($B$2:$D$6)*100+A14

> Masukkan nomor 100 ke sel terakhir (A18). Dan terakhir kolom pertama selesai.

Kolom lain dari tabel data perantara

Nilai di kolom ini mewakili tinggi rangkaian di setiap kolom yang ditumpuk. Lihat tangkapan layar:

> Masukkan tajuk yang sama dengan data asli ke dalam tiga kolom lainnya;

> Di kolom kedua, masukkan rumus di bawah ini ke dalam sel kosong pertama (B9) lalu tekan Enter kunci. Pilih sel hasil ini, seret Tuas IsiOtomatis kanan ke dua sel berikutnya dan kemudian ke baris berikutnya.
= B $ 2 / SUM ($ B $ 2: $ D $ 2)

> Masukkan rumus di bawah ini ke dalam B11 lalu tekan Enter kunci. Pilih sel hasil ini, seret Tuas IsiOtomatis kanan ke dua sel berikutnya dan kemudian ke baris berikutnya.
= B $ 3 / SUM ($ B $ 3: $ D $ 3)

> Lanjutkan dengan memasukkan rumus di bawah ini ke B13 dan kemudian tekan Enter kunci. Pilih sel hasil ini, seret Tuas IsiOtomatis kanan ke dua sel berikutnya dan kemudian ke baris berikutnya.
= B $ 4 / SUM ($ B $ 4: $ D $ 4)

> Masukkan rumus di bawah ini ke dalam B15 lalu tekan Enter kunci. Pilih sel hasil ini, seret Tuas IsiOtomatis kanan ke dua sel berikutnya dan kemudian ke baris berikutnya.
= B $ 5 / SUM ($ B $ 5: $ D $ 5)

> Masukkan rumus di bawah ini ke dalam B17 lalu tekan Enter kunci. Pilih sel hasil ini, seret Tuas IsiOtomatis kanan ke dua sel berikutnya dan kemudian ke baris berikutnya.
= B $ 6 / SUM ($ B $ 6: $ D $ 6)

Part2: Menyisipkan bagan area bertumpuk berdasarkan data antara dan memformatnya

2. Pilih seluruh tabel data antara, klik Menyisipkan > Bagan Garis atau Bagan Area > Area Bertumpuk.

3. Klik kanan sumbu X pada grafik dan pilih Format Sumbu dari menu klik kanan.

4. Dalam Format Sumbu panel, pilih Sumbu tanggal pilihan di bawah Opsi Sumbu Tab.

5. Tetap pilih sumbu X dan kemudian tekan Delete kunci untuk menghapusnya dari grafik.

Kemudian grafik ditampilkan sebagai berikut.

6. Klik kanan sumbu Y dan pilih Format Sumbu dari menu konteks

7. Dalam Format Sumbu panel, konfigurasikan sebagai berikut.

7.1) Di Opsi Sumbu bagian, ubah Maksimum kotak untuk 1;
7.2) Di Jumlah bagian, pilih Persentase dari Kategori daftar drop-down, dan kemudian ubah Tempat desimal untuk 0.

Sekarang grafik tersebut ditampilkan sebagai berikut.

8. Sekarang kita perlu menambahkan perbatasan untuk menunjukkan pekerjaan setiap data dalam satu rangkaian. Silakan lakukan sebagai berikut.

8.1) Pilih area abu-abu atas (seri Minuman dalam hal ini), klik Elemen Bagan tombol, lalu periksa Bilah Kesalahan kotak.

8.2) Buka Format Seri Data pane, klik Opsi Seri panah drop-down, lalu pilih Seri "Minuman" Y Error Bars dari daftar untuk memilih bilah kesalahan yang baru saja Anda tambahkan.

8.3) Di bawah Opsi Bilah Kesalahan tab, Anda perlu:
  • Pilih Minus dalam Kepemimpinan bagian;
  • Pilih Tidak ada topi dalam Gaya Akhir bagian;
  • Pilih Persentase opsi dan masuk 100 ke dalam kotak teks di Jumlah Kesalahan bagian.
8.4) Pilih bilah abu-abu (seri Minuman), lalu pergi ke Format Seri Data panel untuk mengkonfigurasi sebagai berikut.
  • klik Isi & Garis ikon;
  • Dalam majalah Batas bagian, pilih Garis utuh;
  • Pilih warna hitam di file Warna daftar drop-down;
  • Mengubah Lebar untuk 1pt.

Sekarang grafik ditampilkan seperti gambar di bawah ini.

9. Ulangi operasi pada langkah 8 untuk menambahkan pemisah ke segmen lain. Dan akhirnya grafiknya ditampilkan seperti di bawah ini.

Bagian3: Tentukan nilai sumbu X dari bagan Marimekko

10. Sekarang Anda perlu menghitung nilai tengah untuk setiap kolom dan menampilkan nilai subkategori (data kolom pertama dari rentang data asli) sebagai nilai sumbu X.

Dua baris pembantu diperlukan di bagian ini, harap terapkan rumus di bawah ini untuk menanganinya.

10.1) Di sel pertama (di sini saya memilih A22) dari baris pertama, masukkan rumus di bawah ini ke dalamnya dan tekan Enter kunci untuk mendapatkan hasil.
= A11 / 2

10.2) Di sel kedua dari baris pertama, terapkan rumus di bawah ini.
= (A13-A11) / 2 + A11
10.3) Di sel ketiga dari baris pertama, terapkan rumus di bawah ini.
= (A15-A13) / 2 + A13
10.4) Di sel keempat dari baris pertama, terapkan rumus di bawah ini.
= (A17-A15) / 2 + A15
10.5) Di sel kelima dari baris pertama, terapkan rumus di bawah ini.
= (A18-A17) / 2 + A17
10.6) Masukkan angka 0 ke dalam setiap sel pada baris kedua. Dan akhirnya kisaran penolong ini ditampilkan seperti gambar di bawah ini.

11. Klik kanan grafik dan klik Pilih Data di menu klik kanan.

12. Dalam pembukaan Pilih Sumber Data kotak dialog, klik Add .

13. Kemudian Edit Seri kotak dialog muncul, pilih sel yang berisi nilai 0 di Nilai seri kotak, dan kemudian klik OK .

14. Ketika kembali ke Pilih Sumber Data kotak dialog, Anda dapat melihat seri baru (Seri4) dibuat, klik OK Tombol untuk menyimpan perubahan.

15. Klik kanan grafik dan pilih Ubah Jenis Bagan Seri.

16. Dalam Ubah Jenis Bagan kotak dialog, pilih jenis bagan "Sebarkan dengan Garis Lurus dan Spidol”Untuk Series4 di Pilih tipe bagan dan sumbu untuk seri data Anda kotak. Dan kemudian klik OK.

17. Klik kanan grafik dan pilih Pilih Data.

18. Dalam Pilih Sumber Data kotak dialog, pilih Series4 (nama seri yang Anda buat pada langkah 14) dan klik Edit tombol di Entri Legenda (Seri) kotak.

19. Dalam Edit Seri kotak dialog, pilih sel baris pertama di the Nilai Seri X. kotak, lalu klik OK.

20. klik OK untuk menyimpan perubahan saat kembali ke Pilih Sumber Data kotak dialog.

Sekarang seri baru ditambahkan di bagian bawah area plot seperti gambar di bawah ini.

21. Sekarang Anda perlu menyembunyikan garis dan penanda. Pilih seri ini, buka Format Seri Data panel lalu lakukan hal berikut.

21.1) Klik Isi & Garis ikon;
21.2) Pilih Tanpa garis bawah baris Tab;

21.3) Klik Marker tab, luaskan Opsi Penanda, Dan kemudian pilih Tidak ada.

22. Biarkan rangkaian tetap dipilih, klik Elemen Bagan tombol, lalu periksa Label Data kotak.

23. Pilih label yang ditambahkan, pergi ke Format Label Data panel dan konfigurasikan sebagai berikut.

23.1) Periksa Nilai Dari Sel kotak;

23.2) Di Rentang Label Data kotak dialog, pilih sel yang ingin Anda tampilkan sebagai nilai sumbu X, lalu klik OK. Di sini saya memilih sel kolom pertama dari rentang data asli.

23.3) Kembali ke Format Label Data panel, hapus centang Nilai Y kotak di Label Berisi bagian, dan di Label posisi bagian, pilih Di bawah .

Sekarang grafik tersebut ditampilkan sebagai berikut.

Part4: Menampilkan satu set persentase di atas lebar kolom

Sekarang kita perlu menampilkan satu set persentase di atas lebar kolom. Pertama, kita perlu menghitung persentase setiap kolom.

24. Karena ada lima kolom dalam bagan, Anda perlu menghitung lima persentase sebagai berikut.

24.1) Pilih sel kosong di baris baru (di sini saya pilih A27), masukkan rumus di bawah ini ke dalamnya dan tekan Enter kunci untuk mendapatkan persentase dari kolom pertama.
= $ A $ 11/100

24.2) Untuk persentase kedua, mohon gunakan rumus di bawah ini.
= (A13-A11) / 100
24.3) Untuk persentase ketiga, terapkan rumus di bawah ini.
= (A15-A13) / 100
24.4) Untuk persentase keempat, terapkan rumus di bawah ini.
= (A17-A15) / 100
24.5) Untuk persentase kelima, terapkan rumus di bawah ini.
= (A18-A17) / 100

25. Di baris persentase berikutnya, masukkan angka 1 ke dalam setiap sel. Kemudian Anda akan mendapatkan rentang helper baru sebagai berikut.

26. Klik kanan grafik dan pilih Pilih Data dari menu klik kanan.

27. Dalam Pilih Sumber Data kotak dialog, klik Add .

28. Dalam pembukaan Edit Seri kotak dialog, Anda perlu melakukan hal berikut.

28.1) Di Nilai seri X. kotak, pilih sel yang berisi nilai tengah untuk setiap kolom yang telah Anda hitung di langkah 10.
Catatan: Di sini nilai tengah digunakan untuk menampilkan persentase di tengah lebar kolom.
28.2) Di Nilai seri Y. kotak, pilih sel yang berisi nomor 1.
Catatan: Karena sumbu Y minimum dan maksimum adalah 0 dan 1, dan kita perlu menampilkan persentase di atas area plot, di sini nilai Y harus 1.
28.3) Klik OK.

29. Ketika kembali ke Pilih Sumber Data kotak dialog, seri baru (Seri5) dibuat, klik OK Tombol untuk menyimpan perubahan.

30. Klik kanan grafik dan pilih Ubah Jenis Bagan Seri.

31. Dalam Ubah Jenis Bagan kotak dialog, pilih jenis bagan "Sebarkan dengan Garis Lurus dan Spidol”Untuk Series5 di Pilih tipe bagan dan sumbu untuk seri data Anda kotak. Dan kemudian klik OK.

Sekarang grafik tersebut ditampilkan sebagai berikut.

32. Anda perlu menyembunyikan garis dan penanda rangkaian (Klik untuk melihat caranya).

33. Tambahkan label data ke seri ini (Klik untuk melihat caranya). Tentukan posisi label ini ke Di Atas.

Catatan: Di bagian ini, setelah memeriksa Nilai Dari Sel kotak, Anda perlu memilih sel yang berisi persentase yang dihitung di Rentang Label Data kotak dialog.

Sekarang persentase ditampilkan di atas lebar kolom seperti gambar di bawah ini.

Part5: Tampilkan nama seri di sebelah kanan area plot

Seperti gambar di bawah ini yang ditampilkan, untuk menampilkan nama seri di sebelah kanan area plot pada grafik, Anda perlu menghitung nilai tengah untuk setiap seri kolom terakhir terlebih dahulu, tambahkan seri baru berdasarkan nilai ini dan terakhir tambahkan nama seri sebagai label data dari seri baru ini.

34. Untuk menghitung nilai tengah untuk setiap rangkaian kolom terakhir, harap gunakan rumus di bawah ini.

34.1) Pilih sel kosong (di sini saya pilih A31) di baris baru, masukkan rumus di bawah ini ke dalamnya dan tekan tombol Enter kunci.
= B6 / SUM (B6: D6) / 2

34.2) Pilih sel berikutnya (B31), masukkan rumus di bawah ini ke dalamnya dan tekan Enter kunci. Pilih sel hasil ini lalu seret Tuas IsiOtomatis langsung ke sel C31 berikutnya.
=SUM($B$6:C6)/SUM($B$6:$D$6)-C6/SUM($B$6:$D$6)/2

35. Pada baris baru berikutnya, masukkan nomor 100 ke dalam setiap sel. Akhirnya rentang helper baru lainnya dibuat seperti gambar di bawah ini.

Catatan: Di sini angka 100 mewakili maksimum sumbu X.

36. Klik kanan grafik dan pilih Pilih Data dari menu konteks

37. Dalam Pilih Sumber Data kotak dialog, klik Add .

38. Dalam Edit Seri kotak dialog, pilih kisaran yang sesuai sebagai berikut.

38.1) Di Nilai seri X. kotak, pilih sel yang berisi nomor 100;
38.2) Di Nilai seri Y. kotak, pilih sel yang berisi nilai tengah untuk setiap rangkaian kolom terakhir yang telah Anda hitung di langkah 34.
38.3) Klik OK .

39. Ketika kembali ke Pilih Sumber Data kotak dialog, klik OK Untuk menyimpan perubahan

40. Klik kanan grafik dan pilih Ubah Jenis Bagan Seri dari menu konteks

41. Dalam Ubah Jenis Bagan kotak dialog, pilih jenis bagan "Sebarkan dengan Garis Lurus dan Spidol”Untuk Series6 di Pilih tipe bagan dan sumbu untuk seri data Anda kotak, lalu klik OK.

Kemudian seri baru ditambahkan pada grafik seperti gambar di bawah ini.

42. Anda perlu menyembunyikan garis dan penanda rangkaian (Klik untuk melihat caranya).

43. Tambahkan label data ke seri ini (Klik untuk melihat caranya). Pertahankan Posisi Label sebagai Kanan.

Catatan: Di bagian ini, setelah memeriksa Nilai Dari Sel kotak, Anda perlu memilih sel yang berisi nama seri dalam rentang data asli di Rentang Label Data kotak dialog.

Sekarang grafik tersebut ditampilkan sebagai berikut.

Bagian6: Menampilkan nilai seri pada setiap segmen dalam kolom

Bagian terakhir di sini akan menunjukkan kepada Anda bagaimana menampilkan nilai seri (label data) pada setiap segmen dalam kolom seperti gambar di bawah ini. Silakan lakukan sebagai berikut.

44. Pertama, Anda perlu menghitung nilai tengah untuk setiap segmen dalam kolom, silakan gunakan rumus di bawah ini.

44.1) Dimulai dengan baris baru, pilih sel kosong pertama (seperti A35), masukkan rumus di bawah ini ke dalamnya dan tekan Enter kunci. Pilih sel hasil ini dan seret Tuas IsiOtomatis turun ke empat sel di bawah.
Catatan: Nilai hasil pada kolom ini merepresentasikan nilai tengah untuk segmen seri Buah di setiap tahun.
= B2 / SUM (B2: D2) / 2

44.2) Pilih sel B35 berikutnya, masukkan rumus di bawah ini dan tekan Enter kunci. Lalu seret Tuas IsiOtomatis turun ke empat sel di bawah.
Catatan: Nilai hasil di kolom ini mewakili nilai tengah untuk segmen seri Makanan di setiap tahun.
=C2/SUM(B2:D2)/2+Sheet1!B2/SUM(B2:D2)

44.3) Pilih C35, masukkan rumus di bawah ini dan tekan Enter kunci. Lalu seret Tuas IsiOtomatis turun ke empat sel di bawah.
Catatan: Nilai hasil pada kolom ini merepresentasikan nilai tengah untuk segmen seri Minuman di setiap tahunnya.
=D2/SUM(B2:D2)/2+SUM(B2:C2)/SUM(B2:D2)

45. Klik kanan grafik dan klik Pilih Data dalam menu konteks.

46. Dalam Pilih Sumber Data kotak dialog, klik Add .

47. Dalam Edit Seri kotak dialog, pilih rentang yang sesuai sebagai berikut.

47.1) Di Nilai seri X. kotak, pilih sel yang berisi nilai tengah untuk setiap kolom yang telah Anda hitung di langkah 10.
Catatan: Nilai-nilai ini digunakan untuk menentukan posisi sumbu X untuk deret Buah.
47.2) Di Nilai seri Y. kotak, pilih kolom pertama dari rentang helper yang telah Anda buat di bagian ini.
Catatan: Nilai-nilai ini digunakan untuk menentukan posisi sumbu Y untuk deret Buah.
47.3) Klik OK .

48. Ulangi langkah 46 dan 47, menggunakan dua nilai kolom yang tersisa untuk menambahkan dua deretan baru. Lihat screenshot di bawah ini:

49. Ketika kembali ke Pilih Sumber Data kotak dialog, Anda dapat melihat tiga seri baru ditambahkan, klik OK Untuk menyimpan perubahan

50. Klik kanan grafik dan pilih Ubah Jenis Bagan Seri dari menu konteks

51. Dalam Ubah Jenis Bagan kotak dialog, pilih jenis bagan "Sebarkan dengan Garis Lurus dan Spidol"Untuk tiga seri baru di Pilih tipe bagan dan sumbu untuk seri data Anda kotak, lalu klik OK.

Bagan tersebut ditampilkan sebagai berikut.

52. Anda perlu menyembunyikan garis dan penanda rangkaian (Klik untuk melihat caranya).

53. Tambahkan label data ke seri (Klik untuk melihat caranya). Tentukan Posisi Label sebagai Pusat.

Catatan:

1) Untuk label data seri Minuman, setelah pengecekan Nilai Dari Sel kotak, Anda perlu memilih nilai sebenarnya dari seri Minuman dalam kisaran data asli.

2) Untuk label data seri Pangan, setelah dilakukan pengecekan Nilai Dari Sel kotak, Anda perlu memilih nilai sebenarnya dari seri Makanan di kisaran data asli.

3) Untuk label data seri Buah, setelah dilakukan pengecekan Nilai Dari Sel kotak, Anda perlu memilih nilai aktual dari seri Buah dalam kisaran data asli.

Sekarang grafik ditampilkan seperti gambar di bawah ini.

54. Hapus judul grafik dan legenda dari grafik.

55. Jaga agar grafik tetap dipilih, pergi ke Format Seri Data panel, lalu pilih Area Plot dalam Opsi Seri daftar drop-down

56. Area plot dari grafik dipilih. Harap persempit area plot dengan menyeret batas-batasnya hingga nilai atas, bawah, dan kanan sepenuhnya ditampilkan keluar dari area plot. Lihat demo di bawah ini.

Sekarang Bagan Marimekko selesai.


Buat bagan marimekko dengan mudah di Excel

The Bagan Marimekko kegunaan Kutools untuk Excel dapat membantu Anda dengan cepat membuat bagan marimekko di Excel hanya dengan beberapa klik seperti yang ditunjukkan pada demo di bawah ini.
Unduh dan coba sekarang! 30-hari bebas jejak


Unduh file contoh


Alat Produktivitas Kantor Terbaik

Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan

Apakah Anda ingin menyelesaikan pekerjaan harian Anda dengan cepat dan sempurna? Kutools for Excel menghadirkan 300 fitur canggih yang canggih (Gabungkan buku kerja, jumlahkan berdasarkan warna, pisahkan konten sel, tanggal konversi, dan seterusnya...) dan hemat 80% waktu untuk Anda.

  • Dirancang untuk 1500 skenario kerja, membantu Anda memecahkan 80% masalah Excel.
  • Kurangi ribuan klik keyboard dan mouse setiap hari, hilangkan mata dan tangan Anda yang lelah.
  • Menjadi ahli Excel dalam 3 menit. Tidak perlu lagi mengingat rumus dan kode VBA yang menyakitkan.
  • Uji coba gratis tanpa batas selama 30 hari. Jaminan uang kembali 60 hari. Upgrade dan dukungan gratis selama 2 tahun.
Ribbon of Excel (dengan Kutools for Excel diinstal)

Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)

  • Satu detik untuk beralih di antara lusinan dokumen terbuka!
  • Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
  • Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
  • Membawa Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Firefox, dan Internet Explorer Baru.
Cuplikan Layar Excel (dengan Office Tab diinstal)
Urutkan komentar berdasarkan
komentar (1)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Bonjour, je réussis faire le graphique, mais les angle ne sont pas droits.
La forme est la même que vous, tanggal format avant de passer au.
Il faut simplement faire cette manipulasi, apakah modifikasi yang perlu dilakukan?
Terima kasih banyak
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL