2 Cara Mengubah Latar Belakang di Excel

Dalam artikel ini, Anda akan mempelajari dua cara sederhana untuk mengubah latar belakang sel berdasarkan kontennya di versi Excel terbaru. Anda juga akan memahami rumus mana yang digunakan untuk mengubah bayangan sel atau sel di mana rumus ditulis dengan tidak benar, atau di mana tidak ada informasi sama sekali.

Semua orang tahu bahwa mengedit latar belakang sel sederhana adalah prosedur sederhana. Klik pada "warna latar belakang". Tetapi bagaimana jika Anda membutuhkan koreksi warna berdasarkan konten sel tertentu? Bagaimana saya bisa membuat ini terjadi secara otomatis? Berikut ini adalah serangkaian informasi berguna yang akan memungkinkan Anda menemukan cara yang tepat untuk menyelesaikan semua tugas ini.

Perubahan warna latar belakang sel dinamis

tugas: Anda memiliki tabel atau kumpulan nilai, dan Anda perlu mengedit warna latar belakang sel berdasarkan angka yang dimasukkan di sana. Anda juga perlu memastikan bahwa rona merespons perubahan nilai.

Solusi: Untuk tugas ini, fungsi "pemformatan bersyarat" Excel disediakan untuk sel warna dengan angka lebih besar dari X, kurang dari Y, atau antara X dan Y.

Katakanlah Anda memiliki satu set produk di negara bagian yang berbeda dengan harganya, dan Anda perlu tahu mana yang harganya lebih dari $3,7. Oleh karena itu, kami memutuskan untuk menyoroti produk-produk yang berada di atas nilai ini dengan warna merah. Dan sel-sel yang memiliki nilai yang sama atau lebih besar, diputuskan untuk diwarnai dengan warna hijau.

Catatan: Tangkapan layar diambil dalam versi program 2010. Tetapi ini tidak memengaruhi apa pun, karena urutan tindakannya sama, terlepas dari versi mana - terbaru atau tidak - yang digunakan orang tersebut.

Jadi, inilah yang perlu Anda lakukan (langkah demi langkah):

1. Pilih sel di mana rona harus diedit. Misalnya, rentang $B$2:$H$10 (nama kolom dan kolom pertama, yang mencantumkan nama negara bagian, dikecualikan dari sampel).

2. Klik "Rumah" dalam kelompok "gaya". Akan ada item “Pemformatan Bersyarat”. Di sana Anda juga perlu memilih "Aturan baru". Dalam versi bahasa Inggris Excel, urutan langkah-langkahnya adalah sebagai berikut: “Beranda”, “Grup gaya”, “Pemformatan Bersyarat > Aturan Baru».

3. Di jendela yang terbuka, centang kotak “Format hanya sel yang mengandung” (“Format hanya sel yang mengandung” dalam versi bahasa Inggris).

4. Di bagian bawah jendela ini di bawah tulisan “Format hanya sel yang memenuhi kondisi berikut” (Hanya format sel dengan) Anda dapat menetapkan aturan dengan mana pemformatan akan dilakukan. Kami telah memilih format untuk nilai yang ditentukan dalam sel, yang harus lebih besar dari 3.7, seperti yang Anda lihat dari tangkapan layar: 

5. Selanjutnya, klik tombol "Format". Sebuah jendela akan muncul dengan area pemilihan warna latar di sebelah kiri. Tetapi sebelum itu, Anda perlu membuka tab "Mengisi" ("Mengisi"). Dalam hal ini, warnanya merah. Setelah itu, klik tombol “OK”.

6. Kemudian Anda akan kembali ke jendela “Aturan Pemformatan Baru”, tetapi sudah di bagian bawah jendela ini Anda dapat melihat bagaimana tampilan sel ini. Jika semuanya baik-baik saja, Anda perlu mengklik tombol "OK".

Akibatnya, Anda mendapatkan sesuatu seperti ini:

Selanjutnya, kita perlu menambahkan satu syarat lagi, yaitu mengubah latar belakang sel dengan nilai kurang dari 3.45 menjadi hijau. Untuk melakukan tugas ini, Anda harus mengklik “Aturan Pemformatan Baru” dan ulangi langkah di atas, hanya kondisi yang perlu disetel sebagai “kurang dari, atau setara” (dalam versi bahasa Inggris "kurang dari atau sama dengan", dan kemudian tulis nilainya. Pada akhirnya, Anda perlu mengklik tombol "OK".

Sekarang tabel diformat dengan cara ini.

Ini menampilkan harga bahan bakar tertinggi dan terendah di berbagai negara bagian, dan Anda dapat segera menentukan di mana situasinya paling optimis (di Texas, tentu saja).

Rekomendasi: Jika perlu, Anda dapat menggunakan metode pemformatan serupa, mengedit bukan latar belakang, tetapi font. Untuk melakukan ini, di jendela pemformatan yang muncul di tahap kelima, Anda harus memilih tab «font» dan ikuti petunjuk yang diberikan di jendela. Semuanya jelas secara intuitif, dan bahkan seorang pemula pun dapat mengetahuinya.

Hasilnya, Anda akan mendapatkan tabel seperti ini:

Bagaimana cara menjaga warna sel tetap sama meskipun nilainya berubah?

tugas: Anda perlu mewarnai latar belakang agar tidak pernah berubah, bahkan jika latar belakang berubah di masa mendatang.

Solusi: temukan semua sel dengan nomor tertentu menggunakan fungsi excel "Temukan semua" "Temukan Semua" atau add-on “Pilih sel khusus” (“Pilih Sel Khusus”), lalu edit format sel menggunakan fungsi “Memformat sel” («Memformat Sel»).

Ini adalah salah satu situasi yang jarang terjadi yang tidak tercakup dalam manual Excel, dan bahkan di Internet, solusi untuk masalah ini sangat jarang ditemukan. Yang tidak mengherankan, karena tugas ini tidak standar. Jika Anda ingin mengedit latar belakang secara permanen agar tidak pernah berubah hingga disesuaikan secara manual oleh pengguna program, Anda harus mengikuti petunjuk di atas.

Pilih semua sel yang berisi kondisi tertentu

Ada beberapa metode yang mungkin, tergantung pada jenis nilai tertentu yang akan ditemukan.

Jika Anda perlu menunjuk sel dengan nilai tertentu dengan latar belakang khusus, Anda harus pergi ke tab "Rumah" Dan pilihlah “Temukan dan pilih” – “Temukan”.

Masukkan nilai yang diperlukan dan klik "Temukan semua".

Bantuan: Anda dapat mengklik tombol "Pilihan" di sebelah kanan untuk memanfaatkan beberapa pengaturan tambahan: tempat mencari, cara melihat, apakah menghormati huruf besar dan kecil, dan seterusnya. Anda juga dapat menulis karakter tambahan, seperti tanda bintang (*), untuk menemukan semua baris yang berisi nilai-nilai ini. Jika Anda menggunakan tanda tanya, Anda dapat menemukan karakter tunggal apa pun.

Dalam contoh kami sebelumnya, jika kami ingin menemukan semua harga bahan bakar antara $3,7 dan $3,799, kami dapat mempersempit kueri penelusuran kami.

Sekarang pilih salah satu nilai yang ditemukan program di bagian bawah kotak dialog dan klik salah satunya. Setelah itu, tekan kombinasi tombol “Ctrl-A” untuk memilih semua hasil. Selanjutnya, klik tombol "Tutup". 

Berikut cara memilih semua sel dengan nilai tertentu menggunakan fitur Temukan Semua. Dalam contoh kami, kami perlu menemukan semua harga bahan bakar di atas $3,7, dan sayangnya Excel tidak mengizinkan Anda melakukan ini menggunakan fungsi Temukan dan Ganti.

"Tong madu" terungkap di sini karena ada alat lain yang akan membantu tugas-tugas rumit seperti itu. Ini disebut Pilih Sel Khusus. Add-on ini (yang perlu diinstal ke Excel secara terpisah) akan membantu:

• temukan semua nilai dalam rentang tertentu, misalnya antara -1 dan 45,
• mendapatkan nilai maksimum atau minimum dalam kolom,
• temukan string atau rentang,
• temukan sel berdasarkan warna latar belakang dan banyak lagi.

Setelah menginstal add-on, cukup klik tombol “Pilih berdasarkan nilai” (“Pilih berdasarkan Nilai”) dan kemudian perbaiki kueri penelusuran di jendela addon. Dalam contoh kami, kami mencari angka yang lebih besar dari 3,7. Tekan "Memilih" (“Pilih”), dan dalam sekejap Anda akan mendapatkan hasil seperti ini:

Jika Anda tertarik dengan add-on, Anda dapat mengunduh versi uji coba di link.

Mengubah latar belakang sel yang dipilih melalui jendela "Format Sel"

Sekarang, setelah semua sel dengan nilai tertentu dipilih menggunakan salah satu metode yang dijelaskan di atas, tetap menentukan warna latar belakangnya.

Untuk melakukan ini, Anda perlu membuka jendela “Format sel”dengan menekan tombol Ctrl + 1 (Anda juga dapat mengklik kanan pada sel yang dipilih dan mengklik kiri pada item "pemformatan sel") dan menyesuaikan pemformatan yang Anda butuhkan.

Kami akan memilih warna oranye, tetapi Anda dapat memilih yang lain.

Jika Anda perlu mengedit warna latar belakang tanpa mengubah parameter tampilan lainnya, Anda cukup mengklik “isi warna” dan pilih warna yang cocok untuk Anda dengan sempurna.

Hasilnya adalah tabel seperti ini:

Berbeda dengan teknik sebelumnya, di sini warna sel tidak akan berubah meskipun nilainya diedit. Ini dapat digunakan, misalnya, untuk melacak dinamika barang dalam kelompok harga tertentu. Nilainya telah berubah, tetapi warnanya tetap sama.

Mengedit warna latar belakang untuk sel khusus (kosong atau dengan kesalahan saat menulis rumus)

Mirip dengan contoh sebelumnya, pengguna memiliki kemampuan untuk mengedit warna latar belakang sel khusus dengan dua cara. Ada opsi statis dan dinamis.

Menerapkan Rumus untuk Mengedit Latar Belakang

Di sini warna sel akan diedit secara otomatis berdasarkan nilainya. Metode ini sangat membantu pengguna dan diminati di 99% situasi.

Sebagai contoh, Anda dapat menggunakan tabel sebelumnya, tetapi sekarang beberapa sel akan kosong. Kita perlu menentukan mana yang tidak mengandung bacaan dan mengedit warna latar belakang.

1. Di tab "Rumah" Anda perlu mengklik “Pemformatan Bersyarat” ->  "Aturan baru" (sama seperti pada langkah 2 dari bagian pertama “Ubah warna latar belakang secara dinamis”.

2. Selanjutnya, Anda harus memilih item “Gunakan rumus untuk menentukan…”.

3. Masukkan rumus =Kosong() (ISBLANK in the version), if you want to edit the background of an empty cell, or =ApakahError() (ISERROR in the version), if you need to find a cell where there is an erroneously written formula. Since in this case we need to edit empty cells, we enter the formula =Kosong(), lalu tempatkan kursor di antara tanda kurung dan klik tombol di sebelah kolom input rumus. Setelah manipulasi ini, rentang sel harus dipilih secara manual. Selain itu, Anda dapat menentukan rentang sendiri, misalnya, =IsKosong(B2:H12).

4. Klik tombol “Format” dan pilih warna latar belakang yang sesuai dan lakukan semuanya seperti yang dijelaskan dalam paragraf 5 dari bagian “Perubahan warna latar belakang sel dinamis”, lalu klik “OK”. Di sana Anda juga dapat melihat seperti apa warna selnya. Jendela akan terlihat seperti ini.

5. Jika Anda menyukai latar belakang sel, Anda harus mengklik tombol "OK", dan perubahan akan segera dilakukan pada tabel.

Perubahan statis warna latar belakang sel khusus

Dalam situasi ini, setelah ditetapkan, warna latar belakang akan tetap seperti itu, tidak peduli bagaimana sel berubah.

Jika Anda perlu mengubah sel khusus secara permanen (kosong atau berisi kesalahan), ikuti petunjuk berikut:

1. Pilih dokumen Anda atau beberapa sel dan tekan F5 untuk membuka jendela Go To, lalu tekan tombol "Menyorot".
2. In the dialog box that opens, select the “Blanks” or “Empty cells” button (depending on the version of the program – or English) to select empty cells.
3. Jika Anda perlu menyorot sel yang memiliki rumus dengan kesalahan, Anda harus memilih item "Formula" dan tinggalkan satu kotak centang di sebelah kata "Kesalahan". Sebagai berikut dari tangkapan layar di atas, sel dapat dipilih sesuai dengan parameter apa pun, dan setiap pengaturan yang dijelaskan tersedia jika perlu.
4. Terakhir, Anda perlu mengubah warna latar belakang sel yang dipilih atau menyesuaikannya dengan cara lain. Untuk melakukan ini, gunakan metode yang dijelaskan di atas.

Ingatlah bahwa perubahan pemformatan yang dibuat dengan cara ini akan tetap ada meskipun Anda mengisi celah atau mengubah jenis sel khusus. Tentu saja, tidak mungkin seseorang ingin menggunakan metode ini, tetapi dalam praktiknya, apa pun bisa terjadi.

Bagaimana cara memaksimalkan Excel?

Sebagai pengguna berat Microsoft Excel, Anda harus tahu bahwa itu berisi banyak fitur. Beberapa dari mereka kita kenal dan cintai, sementara yang lain tetap misterius bagi pengguna rata-rata, dan sejumlah besar blogger mencoba menjelaskan setidaknya sedikit tentang mereka. Tetapi ada tugas umum yang harus kita lakukan masing-masing, dan Excel tidak memperkenalkan beberapa fitur atau alat untuk mengotomatiskan beberapa tindakan kompleks.

Dan solusi dari masalah ini adalah add-ons (tambahan). Beberapa dari mereka didistribusikan secara gratis, yang lain - untuk uang. Ada banyak alat serupa yang dapat melakukan fungsi yang berbeda. Misalnya, temukan duplikat dalam dua file tanpa rumus atau makro samar.

Jika Anda menggabungkan alat-alat ini dengan fungsionalitas inti Excel, Anda dapat mencapai hasil yang sangat bagus. Misalnya, Anda dapat mengetahui harga bahan bakar mana yang telah berubah, dan kemudian menemukan duplikat dalam file selama setahun terakhir.

Kami melihat bahwa pemformatan bersyarat adalah alat praktis yang memungkinkan Anda mengotomatiskan pekerjaan di tabel tanpa keahlian khusus. Anda sekarang tahu cara mengisi sel dengan beberapa cara, berdasarkan isinya. Sekarang tinggal mempraktekkannya. Semoga beruntung!