Buat bagan peta panas di Excel

Di Excel, bagan peta panas tampak seperti tabel yang merupakan representasi visual yang menunjukkan tampilan komparatif dari kumpulan data. Jika ada kumpulan data besar di lembar kerja Anda, sangat sulit bagi Anda untuk mengidentifikasi sekilas nilai yang lebih rendah atau lebih tinggi, tetapi, dalam peta panas, nilai sel ditampilkan dalam pola warna yang berbeda sehingga kita dapat melihat data yang lebih besar atau data yang lebih kecil dengan cepat dan mudah seperti gambar di bawah ini.

• Buat bagan peta panas sederhana dengan Pemformatan Bersyarat
• Buat bagan peta panas dinamis di Excel
• Contoh 1: Buat peta panas dinamis dengan menggunakan Scroll Bar
• Contoh 2: Buat peta panas dinamis dengan menggunakan Tombol Radio
• Contoh 3: Buat peta panas dinamis dengan menggunakan Kotak Centang
• Unduh file contoh Diagram Peta Panas
• Video: Buat bagan peta panas di Excel

Buat bagan peta panas sederhana dengan Pemformatan Bersyarat

Tidak ada utilitas bagan peta panas langsung di Excel, tetapi, dengan ekstensi Format Bersyarat fitur, Anda dapat dengan cepat membuat peta panas, lakukan dengan langkah-langkah berikut:

1. Pilih rentang data yang ingin Anda terapkan Format Bersyarat.

2. Dan kemudian klik Beranda > Format Bersyarat > Timbangan Warna, lalu pilih satu gaya yang Anda perlukan dari drop-down yang diluaskan di kanan, (dalam hal ini, saya akan memilih Hijau - Kuning - Skala Warna Merah) lihat tangkapan layar:

3. Sekarang, peta panas dibuat yang menyorot sel berdasarkan nilainya, warna hijau mewakili nilai tertinggi dan warna merah mewakili nilai terendah dan nilai yang tersisa menunjukkan warna gradien antara hijau dan merah. Lihat tangkapan layar:

4. Jika Anda ingin menyembunyikan nomor dan hanya meninggalkan warna, pilih rentang data, dan tekan Ctrl + 1 kunci untuk membuka Format Cells kotak dialog.

5. di Format Cells kotak dialog, di bawah Jumlah tab, klik Kustom pilihan di kiri Kategori daftar kotak, dan kemudian enter ;;; ke dalam Tipe kotak teks, lihat tangkapan layar:

6. Lalu klik OK tombol, dan semua nomor disembunyikan seperti gambar di bawah ini yang ditunjukkan:

Note: Untuk menyorot sel dengan warna lain yang Anda suka, pilih rentang data, lalu klik Beranda > Format Bersyarat > Kelola Aturan untuk pergi ke Manajer Aturan Pemformatan Bersyarat kotak dialog.

Kemudian, klik dua kali aturan yang ada untuk membuka Edit Aturan Pemformatan kotak dialog, dan kemudian atur ulang aturan sesuai kebutuhan Anda, lihat tangkapan layar:

Buat bagan peta panas dinamis di Excel

Contoh 1: Buat peta panas dinamis dengan menggunakan Scroll Bar

Jika ada beberapa kolom data di lembar kerja Anda, tetapi Anda ingin menampilkannya dalam ruang terbatas, dalam hal ini, Anda dapat memasukkan bilah gulir ke lembar kerja untuk membuat peta panas secara dinamis seperti yang diperlihatkan demo di bawah ini.

Untuk membuat bagan peta panas dinamis jenis ini, lakukan dengan langkah-langkah berikut:

1. Sisipkan lembar kerja baru, lalu salin bulan kolom pertama dari lembar asli ke lembar baru ini.

2. Lalu klik Pengembang > Menyisipkan > Scroll bar, lihat tangkapan layar:

3. Kemudian, seret mouse untuk menggambar bilah gulir di bawah data yang disalin, dan klik kanan bilah gulir, lalu pilih Kontrol Format, lihat tangkapan layar:

4. di Format Objek kotak dialog, di bawah kontrol tab, atur nilai minimum, nilai maksimum, perubahan tambahan, perubahan halaman dan sel yang ditautkan berdasarkan rentang data Anda seperti gambar di bawah ini yang ditampilkan:

5. Lalu klik OK untuk menutup kotak dialog ini.

6. Sekarang, di sel B1 lembar baru ini, masukkan rumus berikut, dan tekan Enter kunci untuk mendapatkan hasil pertama:

Note: Dalam rumus di atas, data1! $ B $ 1: $ I $ 13 adalah lembar asli dengan rentang data tidak termasuk tajuk baris (bulan), $ I $ 1 adalah sel yang ditautkan oleh bilah gulir, $ B $ 1: B1 adalah sel tempat Anda mengeluarkan rumus.

7. Kemudian, seret sel formula ini ke sel lainnya, jika Anda hanya ingin menampilkan 3 tahun di lembar kerja, silakan seret rumus dari B1 ke D13, lihat tangkapan layar:

8. Dan kemudian, terapkan Skala warna dari Format Bersyarat fitur ke rentang data baru untuk membuat peta panas, sekarang, saat Anda menyeret bilah gulir, peta panas akan dipindahkan secara dinamis, lihat tangkapan layar:

Contoh 2: Buat peta panas dinamis dengan menggunakan Tombol Radio

Anda juga dapat membuat peta panas dinamis dengan menggunakan tombol radio, memilih satu tombol radio akan menyorot nilai n terbesar, dan memilih tombol radio lain akan menyorot nilai n terkecil seperti yang ditunjukkan demo di bawah ini:

Untuk menyelesaikan peta panas dinamis jenis ini, lakukan seperti ini:

1. Klik Pengembang > Menyisipkan > Tombol Opsi (Kontrol Formulir), lalu, seret mouse untuk menggambar dua tombol radio, dan edit teks sesuai kebutuhan Anda, lihat tangkapan layar:

2. Setelah memasukkan tombol radio, klik kanan yang pertama, dan pilih Kontrol Format, Dalam Kontrol Format kotak dialog, di bawah kontrol tab, pilih sel yang berbaris ke tombol radio, lihat tangkapan layar:

3. Klik OK untuk menutup kotak dialog, lalu ulangi langkah di atas (langkah 2) untuk menautkan tombol radio kedua ke sel yang sama (sel M1) juga.

4. Dan kemudian, Anda harus menerapkan pemformatan bersyarat untuk rentang data, pilih rentang data, dan klik Beranda > Format Bersyarat > Aturan baru, lihat tangkapan layar:

5. di Aturan Pemformatan Baru kotak dialog, pilih Gunakan Rumus untuk menentukan sel mana yang akan diformat dari Pilih Jenis Aturan daftar kotak, lalu masukkan rumus ini: =IF($M$1=1,IF(B2>=LARGE($B$2:$I$13,15),TRUE,FALSE)) ke dalam Memformat nilai yang rumus ini benar kotak teks dan kemudian klik dibentuk tombol untuk memilih warna. Lihat tangkapan layar:

6. Klik OK tombol, ini akan menyorot 15 nilai terbesar dengan warna merah saat Anda memilih tombol radio pertama.

7. Untuk menyorot 15 nilai terkecil, simpan data yang dipilih dan masuk ke Aturan Pemformatan Baru kotak dialog, lalu ketikkan rumus ini: =IF($M$1=2,IF(B2<=SMALL($B$2:$I$13,15),TRUE,FALSE)) ke dalam Memformat nilai yang rumus ini benar kotak teks, dan klik dibentuk tombol untuk memilih warna lain yang Anda butuhkan. Lihat tangkapan layar:

Note: Dalam rumus di atas, $ M $ 1 adalah sel yang ditautkan ke tombol radio, $ B $ 2: $ I $ 13 adalah rentang data yang ingin Anda terapkan pemformatan bersyarat, B2 adalah sel pertama dari rentang data, yaitu angka 15 adalah nomor spesifik yang ingin Anda sorot.

8. Klik OK untuk menutup kotak dialog, sekarang, saat memilih tombol radio pertama, 15 nilai terbesar akan disorot, dan memilih tombol radio kedua, 15 nilai terkecil akan disorot seperti yang ditunjukkan pada demo di bawah ini:

Contoh 3: Buat peta panas dinamis dengan menggunakan Kotak Centang

Pada bagian ini, saya akan memperkenalkan grafik peta panas dinamis dengan menggunakan kotak centang yang dapat membantu Anda untuk menampilkan atau menyembunyikan peta panas berdasarkan kebutuhan Anda. Jika Anda mencentang kotak centang, peta panas akan ditampilkan, jika tidak dicentang, kotak itu akan disembunyikan sekaligus, lihat demo di bawah ini:

1. Pertama, Anda harus mengonversi rentang data Anda ke format tabel yang dapat membantu Anda menerapkan pemformatan bersyarat secara otomatis saat memasukkan baris data baru. Pilih rentang data, lalu tekan Ctrl + T kunci bersama untuk membuka Buat tabel kotak dialog, lihat tangkapan layar:

2. Klik OK untuk menutup kotak dialog, lalu klik Pengembang > Menyisipkan > Kotak Centang (Kontrol Formulir), lalu, seret mouse untuk menggambar kotak centang dan edit teks sesuai kebutuhan Anda seperti gambar di bawah ini yang ditampilkan:

3. Kemudian, klik kanan kotak centang, dan pilih Kontrol Format, Dalam Format Objek kotak dialog, di bawah kontrol tab, pilih sel yang berbaris ke kotak centang, lihat tangkapan layar:

4. Klik OK untuk menutup kotak dialog, lalu, pilih rentang data yang ingin Anda buat peta panas, dan klik Beranda > Format Bersyarat > Aturan baru untuk pergi ke Aturan Pemformatan Baru kotak dialog.

5. di Aturan Pemformatan Baru kotak dialog, lakukan operasi berikut:

• Pilih Memformat semua sel berdasarkan nilainya pilihan dari Pilih Jenis Aturan kotak daftar;
• Pilih Skala 3 Warna dari Gaya Format daftar drop-down;
• Pilih Rumus dalam Tipe kotak di bawah Minimum, Titik tengah dan Maksimum daftar drop-down secara terpisah;
• Dan kemudian, masukkan rumus berikut ke dalam ketiganya Nilai kotak teks:
• Minimum: = JIKA ($ M $ 1 = TRUE, MIN ($ B $ 2: $ I $ 13), FALSE)
• Titik tengah: = JIKA ($ M $ 1 = BENAR, RATA-RATA ($ B $ 2: $ I $ 13), SALAH)
• Maksimum: = JIKA ($ M $ 1 = BENAR, MAKS ($ B $ 2: $ I $ 13), SALAH)
• Kemudian, tentukan warna sorotan dari Warna bagian untuk kebutuhan Anda.

Note: Dalam rumus di atas, $ M $ 1 adalah sel yang ditautkan ke kotak centang, $ B $ 2: $ I $ 13 adalah rentang data yang ingin Anda terapkan pemformatan bersyarat.

6. Setelah menyelesaikan pengaturan, klik OK untuk menutup kotak dialog, sekarang, saat Anda mencentang kotak, peta panas akan ditampilkan, jika tidak, akan disembunyikan. Lihat demo di bawah ini:

Unduh file contoh Diagram Peta Panas

Video: Membuat bagan Peta Panas di Excel