Analisis Data Excel - Pemformatan Bersyarat

Di Microsoft Excel, Anda dapat menggunakan Conditional Formattinguntuk visualisasi data. Anda harus menentukan pemformatan untuk rentang sel berdasarkan konten rentang sel. Sel-sel yang memenuhi kondisi yang ditentukan akan diformat seperti yang Anda tentukan.

Contoh

Dalam rentang yang berisi angka penjualan kuartal terakhir untuk sekumpulan staf penjualan, Anda dapat menyorot sel yang mewakili siapa yang telah memenuhi target yang ditentukan, misalnya, $ 2.500.

Anda dapat mengatur kondisi sebagai total penjualan orang> = $2500dan tentukan kode warna hijau. Excel memeriksa setiap sel dalam rentang tersebut untuk menentukan apakah kondisi yang Anda tentukan, yaitu, total penjualan orang> = $ 2500 terpenuhi.

Excel menerapkan format yang Anda pilih, yaitu warna hijau ke semua sel yang memenuhi syarat. Jika konten sel tidak memenuhi kondisi tersebut, format sel tetap tidak berubah. Hasilnya seperti yang diharapkan, hanya untuk tenaga penjualan yang telah memenuhi target, sel disorot dengan warna hijau - visualisasi cepat dari hasil analisis.

Anda dapat menentukan sejumlah kondisi untuk pemformatan dengan menentukan Rules. Anda dapat mengambil aturan yang sesuai dengan kondisi Anda

  • Sorot aturan sel
  • Aturan Atas / Bawah

Anda juga dapat menentukan aturan Anda sendiri. Anda bisa -

  • Tambahkan aturan
  • Hapus aturan yang ada
  • Kelola aturan yang ditentukan

Selanjutnya, Anda memiliki beberapa opsi pemformatan di Excel untuk memilih salah satu yang sesuai untuk Visualisasi Data Anda -

  • Bilah Data
  • Timbangan Warna
  • Set Ikon

Pemformatan bersyarat telah dipromosikan ke versi Excel 2007, Excel 2010, Excel 2013. Contoh yang Anda temukan di bab ini berasal dari Excel 2013.

Di bagian berikut, Anda akan memahami aturan pemformatan bersyarat, opsi pemformatan, dan cara bekerja dengan aturan.

Sorot Aturan Sel

Kamu dapat memakai Highlight Cells aturan untuk menetapkan format ke sel yang isinya memenuhi salah satu kriteria berikut -

  • Angka dalam rentang numerik tertentu -
    • Lebih besar dari
    • Kurang dari
    • Between
    • Sama dengan
  • Teks yang berisi string teks tertentu.
  • Tanggal terjadi dalam rentang tanggal tertentu relatif terhadap tanggal saat ini -
    • Yesterday
    • Today
    • Tomorrow
    • Dalam 7 hari terakhir
    • Minggu lalu
    • Minggu ini
    • Minggu depan
    • Bulan lalu
    • Bulan ini
    • Bulan depan
  • Nilai yang duplikat atau unik.

Ikuti langkah-langkah untuk memformat sel secara bersyarat -

  • Pilih rentang yang akan diformat secara bersyarat.

  • Klik Conditional Formatting dalam Styles kelompok di bawah Home tab.

  • Klik Highlight Cells Rules dari menu tarik-turun.

  • Klik Greater Thandan tentukan> 750. Pilih warna hijau.

  • Klik Less Than dan tentukan <500. Pilih warna merah.

  • Klik Between dan tentukan 500 dan 750. Pilih warna kuning.

Data akan disorot berdasarkan kondisi yang diberikan dan format yang sesuai.

Aturan Atas / Bawah

Kamu dapat memakai Top / Bottom Rules untuk menetapkan format ke sel yang isinya memenuhi salah satu kriteria berikut -

  • Top 10 items - Sel yang menempati peringkat N teratas, di mana 1 <= N <= 1000.

  • Top 10% - Sel yang menempati peringkat n% teratas, di mana 1 <= n <= 100.

  • Bottom 10 items - Sel yang menempati urutan N terbawah, dimana 1 <= N <= 1000.

  • Bottom 10% - Sel yang menempati urutan n% terbawah, dimana 1 <= n <= 100.

  • Above average - Sel yang berada di atas rata-rata untuk rentang yang dipilih.

  • Below average - Sel yang berada di bawah rata-rata untuk rentang yang dipilih.

Ikuti langkah-langkah yang diberikan di bawah ini untuk menetapkan aturan Atas / Bawah.

  • Pilih rentang yang akan diformat secara bersyarat.

  • Klik Conditional Formatting dalam Styles kelompok di bawah Home tab.

  • Klik Top/Bottom Rulesdari menu tarik-turun. Opsi aturan Atas / Bawah muncul.

  • Klik Top Ten Items dan tentukan 5. Pilih warna hijau.

  • Klik Bottom Ten Items dan tentukan 5. Pilih warna merah.

Data akan disorot berdasarkan kondisi yang diberikan dan format yang sesuai.

  • Ulangi tiga langkah pertama yang diberikan di atas.

  • Klik Top Ten% dan tentukan 5. Pilih warna hijau.

  • Klik Bottom Ten% dan tentukan 5. Pilih warna merah.

Data akan disorot berdasarkan kondisi yang diberikan dan format yang sesuai.

  • Ulangi tiga langkah pertama yang diberikan di atas.

  • Klik Above Average. Pilih warna hijau.

  • Klik Below Average. Pilih warna merah.

Data akan disorot berdasarkan kondisi yang diberikan dan format yang sesuai.

Bilah Data

Anda bisa menggunakan berwarna Data Barsuntuk melihat nilai dalam sel yang berhubungan dengan nilai di sel lain. Panjang bilah data mewakili nilai dalam sel. Bilah yang lebih panjang menunjukkan nilai yang lebih tinggi, dan bilah yang lebih pendek menunjukkan nilai yang lebih rendah. Anda memiliki enam warna solid untuk dipilih untuk bilah data - biru, hijau, merah, kuning, biru muda dan ungu.

Bilah data membantu dalam memvisualisasikan nilai yang lebih tinggi, lebih rendah, dan menengah saat Anda memiliki data dalam jumlah besar. Contoh - Suhu hari di seluruh wilayah dalam bulan tertentu. Anda dapat menggunakan bilah warna isian gradien untuk memvisualisasikan nilai dalam sel relatif terhadap nilai di sel lain. Anda punya enamGradient Colors untuk memilih dari Bilah Data - Biru, Hijau, Merah, Kuning, Biru Muda dan Ungu.

  • Pilih rentang yang akan diformat secara bersyarat.

  • Klik Conditional Formatting dalam Styles kelompok di bawah Home tab.

  • Klik Data Barsdari menu tarik-turun. ItuGradient Fill opsi dan Fill pilihan muncul.

Klik bilah data biru di Gradient Fill pilihan.

  • Ulangi tiga langkah pertama.

  • Klik bilah data biru di Solid Fill pilihan.

Anda juga dapat memformat bilah data sedemikian rupa sehingga bilah data dimulai di tengah sel, dan membentang ke kiri untuk nilai negatif dan membentang ke kanan untuk nilai positif.

Timbangan Warna

Kamu dapat memakai Color Scalesuntuk melihat nilai dalam sel yang berhubungan dengan nilai di sel lain dalam rentang tertentu. Seperti dalam kasusHighlight Cells Rules, Sebuah Color Scalemenggunakan bayangan sel untuk menampilkan perbedaan nilai sel. Gradien warna akan diterapkan ke berbagai sel. Warna menunjukkan di mana setiap nilai sel berada dalam kisaran itu.

Anda dapat memilih dari -

  • Tiga - Skala Warna -
    • Hijau - Kuning - Skala Warna Merah
    • Merah - Kuning - Skala Warna Hijau
    • Hijau - Putih - Skala Warna Merah
    • Merah - Putih - Skala Warna Hijau
    • Biru - Putih - Skala Warna Merah
    • Merah - Putih - Skala Warna Biru
  • Skala Dua Warna -
    • Putih - Skala Warna Merah
    • Merah - Skala Warna Putih
    • Hijau - Skala Warna Putih
    • Putih - Skala Warna Hijau
    • Hijau - Skala Warna Kuning
    • Kuning - Skala Warna Hijau

Ikuti langkah-langkah yang diberikan di bawah ini -

  • Pilih Rentang yang akan diformat secara bersyarat.

  • Klik Conditional Formatting dalam Styles kelompok di bawah Home tab.

  • Klik Color Scalesdari menu tarik-turun. ItuColor Scale pilihan muncul.

  • Klik Skala Warna Hijau - Kuning - Merah.

Data akan disorot berdasarkan skala warna Hijau - Kuning - Merah dalam rentang yang dipilih.

  • Ulangi tiga langkah pertama.
  • Klik skala warna Hijau - Putih.

Data akan disorot berdasarkan skala warna Hijau - Putih dalam rentang yang dipilih.

Set Ikon

Anda dapat menggunakan kumpulan ikon untuk memvisualisasikan perbedaan numerik. Set ikon berikut tersedia -

Seperti yang Anda amati, satu set ikon terdiri dari tiga hingga lima simbol. Anda dapat menentukan kriteria untuk mengaitkan ikon dengan setiap nilai dalam rentang sel. Misalnya, panah bawah merah untuk angka kecil, panah atas hijau untuk angka besar, dan panah horizontal kuning untuk nilai tengah.

  • Pilih rentang yang akan diformat secara bersyarat.

  • Klik Conditional Formatting dalam Styles kelompok di bawah Home tab.

  • Klik Icon Setsdari menu tarik-turun. ItuIcon Sets pilihan muncul.

  • Klik tiga panah berwarna.

Panah Berwarna muncul di samping Data berdasarkan Nilai dalam rentang yang dipilih.

  • Ulangi tiga langkah pertama. ItuIcon Sets pilihan muncul.

  • Pilih 5 Rating. Ikon Peringkat muncul di samping data berdasarkan nilai dalam rentang yang dipilih.

Aturan baru

Kamu dapat memakai New Rule untuk membuat rumus Anda sendiri sebagai syarat untuk memformat sel seperti yang Anda tentukan.

Ada dua cara untuk menggunakan Aturan Baru -

  • Dengan New Rule opsi dari menu drop-down

  • Dengan New Rule tombol masuk Manage Rules kotak dialog

Dengan opsi Aturan Baru dari Menu Drop-Down

  • Pilih Rentang yang akan diformat secara bersyarat.

  • Klik Conditional Formatting dalam Styles kelompok di bawah Home tab.

  • Klik New Rule dari menu tarik-turun.

Itu New Formatting Rule kotak dialog muncul.

  • Dari kotak pilih tipe aturan, pilih gunakan rumus untuk menentukan sel mana yang akan diformat. Edit the Rule Description kotak muncul.

  • Dalam format nilai di mana rumus ini benar: ketikkan rumus.

  • Klik tombol format dan klik OK.

Sel yang berisi nilai dengan rumus TRUE, diformat seperti yang ditentukan.

Dengan Tombol Aturan Baru di kotak dialog Kelola Aturan

  • Pilih rentang yang akan diformat secara bersyarat.

  • Klik Conditional Formatting dalam Styles kelompok di bawah Home tab.

  • Klik Manage Rules dari menu tarik-turun.

Itu Conditional Formatting Rules Manager kotak dialog muncul.

Klik New Rule tombol.

Itu New Formatting Rule kotak dialog muncul.

Ulangi Langkah-langkah yang diberikan di atas untuk menentukan rumus dan format Anda.

Itu Conditional Formatting Rules Manager kotak dialog muncul dengan ditentukan New Ruledisorot. KlikApply tombol.

Sel yang berisi nilai dengan rumus TRUE, diformat seperti yang ditentukan.

Aturan yang Jelas

Anda dapat Menghapus Aturan untuk menghapus semua format bersyarat yang telah Anda buat

  • Sel yang dipilih
  • Lembar Kerja Saat Ini
  • Tabel yang Dipilih
  • PivotTable yang Dipilih

Ikuti langkah-langkah yang diberikan -

  • Pilih Rentang / Klik pada Lembar Kerja / Klik tabel> PivotTable di mana aturan pemformatan bersyarat perlu dihapus.

  • Klik Conditional Formatting dalam Styles kelompok di bawah Home tab.

  • Klik Clear Rulesdari menu tarik-turun. Opsi aturan yang jelas muncul.

Pilih opsi yang sesuai. Pemformatan bersyarat dihapus dari Rentang / Lembar Kerja / Tabel / PivotTable.

Kelola Aturan

Kamu bisa Manage Rulesdari Conditional Formatting Rules Managerjendela. Anda bisa melihat aturan pemformatan untuk pilihan saat ini, untuk seluruh lembar kerja saat ini, untuk lembar kerja lain di buku kerja atau tabel atau PivotTable di buku kerja.

  • Klik Conditional Formatting dalam Styles kelompok di bawah Home tab.

  • Klik Manage Rules dari menu tarik-turun.

Itu Conditional Formatting Rules Manager kotak dialog muncul.

Klik panah di Kotak Daftar di sebelah Show formatting rules for Pilihan Saat Ini, Lembar Kerja Ini dan Lembar lainnya, Tabel, PivotTable jika ada dengan Aturan Pemformatan Bersyarat, muncul.

Pilih This Worksheetdari daftar drop-down. Aturan Pemformatan pada Lembar Kerja saat ini muncul sesuai urutan penerapannya. Anda dapat mengubah urutan ini dengan menggunakan panah atas dan bawah.

Anda dapat menambahkan Aturan Baru, Mengedit Aturan, dan Menghapus Aturan.

  • Anda sudah melihat New Ruledi bagian sebelumnya. Anda dapat menghapus aturan dengan memilih Aturan dan mengklikDelete Rule. Aturan yang disorot dihapus.

  • Untuk mengedit Aturan, pilih ATURAN dan klik Edit Rule. Edit Formatting Rule kotak dialog muncul.

  • Kamu bisa

    • Pilih Jenis Aturan

    • Edit Deskripsi Aturan

    • Edit Pemformatan

  • Setelah Anda selesai dengan perubahan, klik OK.

  • Perubahan Aturan akan tercermin di Conditional Formatting Rules Managerkotak dialog. KlikApply.

  • Data akan disorot berdasarkan yang dimodifikasi Conditional Formatting Rules.