Cara Membuat Peta Haba di Excel: Statik dan Dinamik

Panduan langkah demi langkah ini akan membimbing anda melalui proses membuat peta haba dalam Excel dengan contoh praktikal.

Microsoft Excel direka untuk membentangkan data dalam jadual. Tetapi dalam beberapa kes, visual adalah lebih mudah untuk difahami dan dicerna. Seperti yang anda tahu, Excel mempunyai beberapa ciri terbina untuk membuat graf. Malangnya, peta haba tidak ada di atas kapal. Nasib baik, terdapat cara yang cepat dan mudah untuk membuat peta haba dalam Excel dengan pemformatan bersyarat.

• Apakah peta haba?
• Cara membuat peta haba di excel
• Buat panas dengan skala warna tersuai
• Peta Haba Excel Tanpa Nombor
• Panas Peta dengan sel persegi
• Cara membuat peta haba di pivottable
• Buat peta haba dinamik dengan kotak semak
• Buat peta haba yang dinamik tanpa nombor

Apakah peta haba di Excel?

Peta haba (aka heatmap ) adalah tafsiran visual data angka di mana nilai yang berbeza diwakili oleh warna yang berbeza. Biasanya, skema warna hangat untuk digunakan, jadi data diwakili dalam bentuk bintik-bintik panas dan sejuk.

Berbanding dengan laporan analisis standard, Heatmaps menjadikannya lebih mudah untuk memvisualisasikan dan menganalisis data kompleks. Mereka digunakan secara meluas oleh saintis, penganalisis dan pemasar untuk analisis awal data dan menemui corak generik.

Berikut adalah beberapa contoh biasa:

• Peta haba suhu udara - digunakan untuk memvisualisasikan data suhu udara di rantau tertentu.
• Peta Haba Geografi - Memaparkan beberapa data angka di kawasan geografi menggunakan warna yang berbeza.
• Peta Haba Pengurusan Risiko - Menunjukkan risiko yang berbeza dan kesannya dengan cara visual dan ringkas.

Dalam Excel, peta haba digunakan untuk menggambarkan sel-sel individu dalam kod warna yang berbeza berdasarkan nilai mereka.

Sebagai contoh, dari haba di bawah, anda dapat melihat yang paling basah (diserlahkan dalam hijau) dan yang paling kering (diserlahkan di merah) dan beberapa dekad sekilas:

Cara membuat peta haba di excel

Sekiranya anda berfikir tentang mewarna setiap sel bergantung pada nilainya secara manual, berikan idea itu kerana itu akan menjadi pembaziran masa yang tidak perlu. Pertama, ia akan mengambil banyak usaha untuk menggunakan warna warna yang sesuai mengikut pangkat nilai. Dan kedua, anda perlu mengulang semula warna-pengekodan setiap kali nilai berubah. Format bersyarat Excel dengan berkesan mengatasi kedua -dua rintangan.

Untuk membuat peta haba di Excel, kami akan menggunakan skala warna pemformatan bersyarat. Berikut adalah langkah untuk melaksanakan:

1. Pilih dataset anda. Dalam kes kita, ia adalah B3: M5.

   

2. Pada tab Home , dalam kumpulan Styles , klik pemformatan bersyarat > skala warna , dan kemudian klik skala warna yang anda inginkan. Semasa anda melayang tetikus ke atas skala warna tertentu, Excel akan menunjukkan kepada anda pratonton langsung secara langsung dalam set data anda.

   Untuk contoh ini, kami telah memilih Red - Yellow - Skala Warna Hijau :

   

   Hasilnya, anda akan mempunyai nilai tinggi yang diserlahkan dalam warna merah, tengah kuning, dan rendah hijau. Warna akan menyesuaikan secara automatik apabila nilai sel berubah.

   

Petua. Untuk peraturan pemformatan bersyarat untuk memohon kepada data baru secara automatik, anda boleh menukar julat data anda ke jadual Excel yang berfungsi sepenuhnya.

Buat panas dengan skala warna tersuai

Apabila menggunakan skala warna pratetap, ia menggambarkan nilai terendah, menengah dan tertinggi dalam warna yang telah ditetapkan (hijau, kuning dan merah dalam kes kita). Semua nilai yang tinggal mendapat warna yang berbeza dari tiga warna utama.

Sekiranya anda ingin menyerlahkan semua sel yang lebih rendah/lebih tinggi daripada nombor tertentu dalam warna tertentu tanpa mengira nilai mereka, maka bukannya menggunakan skala warna terbina dalam membina sendiri. Inilah cara untuk melakukan ini:

1. Di tab Home , dalam kumpulan Styles , klik Pemformatan Bersyarat > Skala Warna> Lebih Banyak Peraturan.

   

2. Dalam kotak dialog Peraturan Pemformatan baru , lakukan perkara berikut:
   • Pilih skala 3-warna dari senarai drop down gaya format .
   • Untuk nilai minimum dan/atau maksimum , pilih nombor dalam jenis drop down, dan masukkan nilai yang dikehendaki dalam kotak yang sepadan.
   • Untuk titik tengah , anda boleh menetapkan nombor atau persentil (biasanya, 50%).
   • Berikan warna kepada setiap tiga nilai.

   Untuk contoh ini, kami telah mengkonfigurasi tetapan berikut:

   

   Dalam haba adat ini, semua suhu di bawah 45 ° F diserlahkan dalam warna hijau yang sama dan semua suhu di atas 70 ° F dalam warna merah yang sama:

   

Buat peta haba di Excel tanpa nombor

Peta haba yang anda buat dalam Excel adalah berdasarkan nilai sel sebenar dan memadamkannya akan memusnahkan peta haba. Untuk menyembunyikan nilai sel tanpa mengeluarkannya dari lembaran, gunakan pemformatan nombor tersuai. Berikut adalah langkah terperinci:

1. Pilih peta haba.
2. Tekan CTRL 1 untuk membuka dialog Format Sel .
3. Pada tab nombor , di bawah kategori , pilih Custom .
4. Dalam kotak jenis , jenis 3 titik koma (;;;).
5. Klik OK untuk memohon format nombor tersuai.

   

Itu sahaja! Sekarang, peta haba Excel anda hanya memaparkan kod warna tanpa nombor:

Peta haba yang cemerlang dengan sel persegi

Satu lagi peningkatan yang boleh anda buat ke heatmap anda adalah sel -sel persegi yang sempurna. Berikut adalah cara terpantas untuk melakukan ini tanpa sebarang skrip atau kod VBA:

1. Menyelaraskan tajuk lajur secara menegak . Untuk mengelakkan tajuk lajur daripada terputus, tukar penjajaran mereka ke menegak. Ini boleh dilakukan dengan bantuan butang orientasi di tab rumah , dalam kumpulan penjajaran :

   

   Untuk maklumat lanjut, sila lihat cara menyelaraskan teks dalam Excel.

2. Tetapkan lebar lajur . Pilih semua lajur dan seret mana -mana kelebihan tajuk lajur untuk menjadikannya lebih luas atau lebih sempit. Seperti yang anda lakukan ini, tooltip akan muncul menunjukkan kiraan piksel yang tepat - ingat nombor ini.

   

3. Tetapkan ketinggian baris . Pilih semua baris dan seret mana -mana kelebihan tajuk baris ke nilai piksel yang sama sebagai lajur (26 piksel dalam kes kami).

   

   Selesai! Semua sel peta topi anda kini berbentuk persegi:

   

Cara Membuat Peta Haba di Excel Pivottable

Pada asasnya, mewujudkan haba dalam jadual pivot adalah sama seperti dalam julat data biasa - dengan menggunakan skala warna pemformatan bersyarat. Walau bagaimanapun, terdapat kaveat: apabila data baru ditambah ke jadual sumber, pemformatan bersyarat tidak akan digunakan secara automatik pada data tersebut.

Sebagai contoh, kami telah menambah jualan Lui ke meja sumber, menyegarkan semula yang boleh diperolehi, dan melihat bahawa nombor Lui masih di luar peta haba:

Cara Membuat Dinamik Peta Haba Pivottable

Untuk memaksa peta haba meja pivot excel untuk memasukkan entri baru secara automatik, berikut adalah langkah -langkah untuk melaksanakan:

1. Pilih mana -mana sel dalam peta haba semasa anda.
2. Di tab Home , dalam kumpulan Styles , klik Pemformatan Bersyarat > Urus Peraturan ...
3. Dalam Pengurus Peraturan Pemformatan Bersyarat , pilih peraturan dan klik pada butang Edit Peraturan .
4. Dalam kotak dialog Peraturan Pemformatan Edit , di bawah Peraturan Terapkan , pilih pilihan ketiga. Dalam kes kami, ia berbunyi: semua sel yang menunjukkan nilai "jumlah jualan" untuk "reseller" dan "produk" .
5. Klik OK dua kali untuk menutup kedua -dua tingkap dialog.

   

Sekarang, peta haba anda dinamik dan akan dikemas kini secara automatik apabila anda menambah maklumat baru di hujung belakang. Ingatlah untuk menyegarkan semula anda :)

Cara Membuat Peta Haba Dinamik Di Excel dengan Kotak Semak

Jika anda tidak mahu peta haba berada di sana sepanjang masa, anda boleh menyembunyikan dan menunjukkannya mengikut keperluan anda. Untuk membuat peta haba yang dinamik dengan kotak semak, ini adalah langkah -langkah untuk diikuti:

1. Masukkan kotak semak . Di sebelah dataset anda, masukkan kotak semak (kawalan borang). Untuk ini, klik tab Pemaju > Masukkan > Kawalan Borang > Kotak Semak . Berikut adalah langkah terperinci untuk menambah kotak semak di Excel.
2. Pautan kotak semak ke sel . Untuk menghubungkan kotak semak ke sel tertentu, klik kanan kotak semak, klik Kawalan Format , Tukar ke tab Kawalan , masukkan alamat sel ke dalam kotak pautan sel , dan klik OK.

   Dalam kes kami, kotak semak dikaitkan dengan sel O2. Apabila kotak semak dipilih, sel yang dipautkan memaparkan benar, jika tidak - palsu.

   

3. Sediakan pemformatan bersyarat . Pilih dataset, klik Pemformatan Bersyarat > Skala Warna> Lebih banyak peraturan , dan konfigurasikan skala warna tersuai dengan cara ini:
   • Dalam senarai drop-down gaya format , pilih skala 3-warna .
   • Di bawah minimum , titik tengah dan maksimum , pilih formula dari senarai drop-down jenis .
   • Dalam kotak nilai , masukkan formula berikut:

     Untuk minimum:

     =IF($O$2=TRUE, MIN($B$3:$M$5), FALSE)

     Untuk titik tengah:

     =IF($O$2=TRUE, AVERAGE($B$3:$M$5), FALSE)

     Untuk maksimum:

     =IF($O$2=TRUE, MAX($B$3:$M$5), FALSE)

     Formula ini menggunakan fungsi min, purata dan maksimum untuk menyelesaikan nilai terendah, menengah dan tertinggi dalam dataset (B3: M5) apabila sel yang dipautkan (O2) adalah benar, iaitu apabila kotak semak dipilih.

   • Dalam kotak drop-down warna , pilih warna yang dikehendaki.
   • Klik butang OK.

     

   Sekarang, peta haba muncul hanya apabila kotak semak dipilih dan tersembunyi sepanjang masa.

   

Petua. Untuk mengalih keluar nilai sebenar / palsu dari paparan, anda boleh menghubungkan kotak semak ke beberapa sel dalam lajur kosong, dan kemudian menyembunyikan lajur itu.

Cara Membuat Peta Haba Dinamik Di Excel Tanpa Nombor

Untuk menyembunyikan nombor dalam peta haba yang dinamik, anda perlu membuat satu lagi peraturan pemformatan bersyarat yang menggunakan format nombor tersuai. Inilah Caranya:

1. Buat peta haba dinamik seperti yang dijelaskan dalam contoh di atas.
2. Pilih set data anda.
3. Pada tab Home , dalam kumpulan Styles , klik Peraturan Baru > Gunakan formula untuk menentukan sel -sel yang hendak dipenuhi .
4. Dalam nilai format di mana formula ini adalah kotak yang benar , masukkan formula ini:

   =IF($O$2=TRUE, TRUE, FALSE)

   Di mana O2 adalah sel yang dipautkan anda. Formula itu mengatakan untuk memohon peraturan hanya apabila kotak semak diperiksa (O2 adalah benar).

   

5. Klik butang Format ....
6. Dalam kotak dialog Format Format , beralih ke tab nombor , pilih Custom dalam senarai kategori , jenis 3 titik koma (;;;) dalam kotak jenis , dan klik OK.

   

7. Klik OK untuk menutup kotak dialog Peraturan Formatting baru .

Mulai sekarang, memilih kotak semak akan memaparkan peta haba dan menyembunyikan nombor:

Untuk menukar antara dua jenis haba yang berbeza (dengan dan tanpa nombor), anda boleh memasukkan tiga butang radio. Dan kemudian, konfigurasikan 3 peraturan pemformatan bersyarat berasingan: 1 Peraturan untuk peta haba dengan nombor, dan 2 peraturan untuk peta haba tanpa nombor. Atau anda boleh membuat peraturan skala warna yang sama untuk kedua -dua jenis dengan menggunakan fungsi atau fungsi (seperti yang dilakukan dalam lembaran kerja sampel kami di bawah).

Hasilnya, anda akan mendapat peta haba dinamik yang bagus ini:

Untuk lebih memahami bagaimana ini berfungsi, anda dialu -alukan untuk memuat turun lembaran sampel kami. Mudah -mudahan, ini akan membantu anda membuat templat peta haba Excel yang menakjubkan.

Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Buku Kerja Amalan untuk Muat turun

Peta Haba di Excel - Contoh (fail .xlsx)

Atas ialah kandungan terperinci Cara Membuat Peta Haba di Excel: Statik dan Dinamik. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!