tetapi jika belum ada tanggal yang disisipkan, area bagan juga diisi tetapi alih-alih durasi tanggal tertentu, isian biru ada di semua sel baris
Saya mencoba kosong tetapi karena kolom J memiliki formula, itu tidak akan merespons
jadi bagaimana saya bisa membuat area grafik (L6. CQ110, CR7. CR15,CT7. CT15,CV7. CV15,CU9. CU15, CS10. CS15, CR17. CV110)
diisi hanya jika kolom tanggal akhir memiliki teks di dalamnya
atau bagaimana mencegahnya menerapkan pemformatan bersyarat saat kolom tanggal akhir tidak memiliki teks
aku benar-benar terjebak di sini
Terima kasih sebelumnya
Balaskata Mike
20 November 2020 pukul 7. 52 pagi
Halo, terima kasih untuk artikel yang bagus, dan terutama untuk bertahun-tahun menjawab pertanyaan orang di bagian komentar
Saya memiliki Lembar Google yang saya gunakan untuk melacak harga saham. Saya ingin dua kolom - satu untuk melacak %-nilai dari perubahan harga saham, dan yang lainnya untuk melacak $-nilai dari perubahan harga saham. Saya telah menyiapkan pemformatan bersyarat untuk % sel dari +3% (hijau) menjadi 0% (putih) hingga -3% (merah). Saya ingin mentransfer pemformatan bersyarat itu ke sel $change, alih-alih menyetel rentang langsung untuk sel tersebut. (Perubahan $10 sangat berarti ketika harga saham adalah $50, tetapi hampir tidak masalah sama sekali jika harga saham adalah $3.000)
Dalam contoh di atas, Anda mengajarkan cara melakukan ini dengan aturan warna tunggal, tetapi saya tidak tahu cara melakukannya dengan skala warna, atau bahkan mungkin
Dalam panduan ini, Anda akan mempelajari cara membuat dan menggunakan formula khusus pemformatan bersyarat apa pun di Google sheets
Aturan pemformatan bersyarat prasetel sangat berguna dan serbaguna
Namun, terkadang itu tidak terlalu berguna dan cukup serbaguna
Untungnya, Anda dapat memperluas keserbagunaannya dengan menulis formula khusus pemformatan bersyarat Google Sheets
Mari kita masuk ke dalamnya
Daftar isi
Mengapa menggunakan rumus khusus untuk pemformatan bersyarat?
Alasan mengapa Anda mungkin perlu menggunakan rumus kustom pemformatan bersyarat adalah untuk memiliki kontrol penuh atas ketentuan yang memicu aturan dan menerapkan logika tepat yang Anda perlukan
Meskipun Google Spreadsheet memiliki banyak preset, namun tidak cukup rumit untuk memenuhi kebutuhan setiap pengguna
Itulah mengapa rumus khusus tersedia bagi Anda untuk membuat aturan yang lebih canggih
Cara membuat formula khusus pemformatan bersyarat di Google Sheets
Mari kita lihat beberapa langkah yang dapat Anda ikuti untuk memasukkan rumus dan fungsi pemformatan bersyarat kustom Anda sendiri
Kemudian, kita akan melihat beberapa contoh dunia nyata yang akan membantu Anda memahami cara kerja rumus khusus
Untuk mengakses dan menggunakan formula khusus pemformatan bersyarat Google Sheets, pertama-tama, pilih sel yang akan diperiksa dan diformat. Selanjutnya, buka Format > Pemformatan bersyarat. Di bawah Aturan format, pilih "Formula kustom adalah" dari daftar. Tentukan rumus khusus Anda di kotak yang diberikan dan atur pemformatan Anda. Lalu klik Selesai
Di atas hanyalah ringkasan dari keseluruhan proses
Patuhi panduan langkah demi langkah di bawah ini
Langkah 1. Pilih Sel atau Sel yang akan diformat
Langkah ini mudah
Dengan menggunakan penunjuk tetikus Anda, cukup klik dan seret ke semua sel yang ingin Anda pengaruhi pemformatan bersyarat
Langkah 2. Buka Format > Pemformatan bersyarat
Langkah sederhana lainnya di sini
Pindahkan kursor Anda ke bagian Menu lembar Google Anda dan klik pada menu Format
Tindakan ini akan mengungkapkan semua item di bawah menu Format termasuk Pemformatan bersyarat. Cukup klik untuk membuka panel di sebelah kanan
Langkah 3. Di bawah aturan Format, klik drop-down dan pilih "Rumus khusus adalah" dari daftar
Langkah 4. Masukkan rumus Kustom Anda
Pada tahap ini, Anda akan disajikan kotak teks tempat Anda dapat memasukkan aturan sebagai rumus
Kami akan segera menjelaskan beberapa rumus dalam contoh
Langkah 5. Setel pemformatan Anda dan klik Selesai
Setelah Anda memasukkan rumus, buat pemformatan yang diperlukan dan klik tombol Selesai untuk menyelesaikan penyiapan
Ini adalah langkah-langkah yang diperlukan yang Anda perlukan untuk menggunakan Google Sheets
Mari kita lihat beberapa contoh
Contoh pemformatan bersyarat Rumus khusus di
Contoh yang akan kami bahas akan menjelaskan cara membuat formula khusus pemformatan bersyarat untuk melakukan hal-hal yang tidak mungkin dilakukan dengan aturan yang telah ditetapkan
Kami akan melihat formula yang akan
- Sorot entri teks dalam sel (Contoh 1)
- Sorot baris berdasarkan nilai (Contoh 2)
- Sorot tanggal yang jatuh pada akhir pekan (Contoh 3)
- Sorot Tanggal dalam 15 hari ke depan (Contoh 4)
- Soroti nilai yang hilang dalam dua set data (Contoh 5)
- Soroti Laptop dengan setidaknya 16GB RAM di bawah $1000 (Contoh 6)
Saya harap beberapa formula ini terbukti bermanfaat bagi Anda. Jika tidak, saya yakin mereka akan menginspirasi Anda untuk membuat formula pemformatan bersyarat kustom Anda sendiri di Google Sheets
Semua rumus yang digunakan dalam contoh ini dapat diakses di bawah ini
Dapatkan Akses ke File Contoh
Contoh 1. Rumus pemformatan bersyarat untuk menyorot sel dengan entri teks
Dalam contoh ini, kami akan membuat formula khusus pemformatan bersyarat yang menyoroti sel dalam rentang B3. D14 hanya jika sel berisi entri teks
Seperti yang terlihat pada tangkapan layar di bawah, aturan disetel untuk menyorot sel yang berisi entri teks dan membiarkan sel dengan angka tidak terformat
=ISTEXT(B3)
Tak satu pun dari preset pemformatan bersyarat di Google Sheets dapat melakukan tugas ini. Dan di sinilah formula khusus berguna
Dengan rumus pemformatan bersyarat khusus di Google Sheets, Anda dapat membuat rumus yang akan mengembalikan BENAR jika sel berisi teks dan mengembalikan SALAH jika sebaliknya
Catatan. Rumus kustom pemformatan bersyarat harus logis, dengan TRUE atau FALSE sebagai nilai kembalian. Jika rumus mengembalikan BENAR, kondisi terpenuhi, dan pemformatan bersyarat diterapkan. Pemformatan bersyarat tidak diterapkan jika rumus mengembalikan FALSE. Jika rumus Anda tidak mengembalikan TRUE atau FALSE, rumus tersebut dapat berfungsi sebagai pemicu untuk pemformatan bersyarat
Patuhi langkah-langkah di bawah ini
Langkah 1. Pilih Sel atau Sel yang akan diformat
Dengan menggunakan penunjuk tetikus Anda, cukup klik dan seret ke semua sel yang ingin Anda pengaruhi pemformatan bersyarat
Dalam contoh ini, kami akan memilih B3. D14
Langkah 2. Buka Format > Pemformatan bersyarat
Pindahkan kursor Anda ke bagian Menu lembar Google Anda dan klik pada menu Format
Tindakan ini akan mengungkapkan semua item di bawah menu Format termasuk Pemformatan bersyarat
Cukup klik dan itu akan segera terbuka
Langkah 3. Di bawah aturan Format, klik drop-down dan pilih "Rumus khusus adalah" dari daftar
Langkah 4. Masukkan rumus ini. =ISTEXT(B3)
Anda akan disajikan dengan kotak teks untuk memasukkan rumus khusus Anda, masukkan =ISTEXT(B3)
Langkah 5. Atur pemformatan
Ada banyak gaya pemformatan yang dapat Anda pilih untuk sel saat memenuhi kondisi yang Anda tetapkan
Ini dapat dilakukan dengan menggunakan kontrol font di bawah gaya Pemformatan
Lihat tangkapan layar di bawah ini
Ada beberapa pemformatan prasetel yang dapat Anda pilih (termasuk latar belakang hijau, latar belakang kuning, latar belakang merah, teks hijau, teks kuning, dan teks merah)
Klik tombol Default di bawah Formatting style untuk menampilkan preset
Jika tidak, Anda dapat menentukan gaya format Anda sendiri menggunakan kontrol font
Langkah 6. Klik Selesai
Setelah Anda menekan tombol selesai, semua sel yang berisi teks akan disorot
Memahami referensi Relatif dan Absolut saat membuat rumus pemformatan bersyarat di Google Sheets
Saya ingin Anda memiliki pemahaman yang baik tentang referensi sel Relatif dan Mutlak sebelum kita melanjutkan ke contoh lainnya
Jika Anda sudah terbiasa dengan kedua konsep ini, Anda dapat melanjutkan ke contoh berikutnya
Jika tidak, Anda harus memiliki pengetahuan tentang hal itu untuk dapat memahaminya dengan lebih baik dan membuat pemformatan bersyarat formula khusus Google Sheets Anda sendiri
Referensi sel akan sering muncul dalam rumus pemformatan bersyarat. Bergantung pada aturan yang ingin Anda tetapkan, referensi sel ini bisa bersifat relatif atau absolut
Rumus yang dimasukkan dalam Contoh 1 berisi referensi relatif ke sel kiri atas dalam rentang yang dipilih
Saat memasukkan rumus pemformatan bersyarat untuk rentang sel, biasanya Anda merujuk ke sel aktif, yang biasanya merupakan sel kiri atas dalam rentang yang dipilih, kecuali Anda merujuk ke satu sel tertentu
Misalnya, katakanlah Anda memilih rentang B3. D14, dan Anda ingin menerapkan pemformatan bersyarat ke semua sel dalam rentang yang melebihi nilai di sel lain, misalnya sel A1. Anda harus memasukkan rumus pemformatan bersyarat ini
=B3>$A$1
Referensi ke sel A1, dalam hal ini, adalah referensi absolut yang tidak akan disesuaikan untuk sel dalam rentang yang dipilih. Dengan kata lain, rumus kustom pemformatan bersyarat di sel B4 akan terlihat seperti ini
=B4>$A$1
Referensi sel relatif akan terus menyesuaikan untuk setiap sel dalam rentang, tetapi referensi sel absolut tidak
Contoh 2. Rumus pemformatan bersyarat untuk menyorot baris berdasarkan nilai
Saat bekerja dengan spreadsheet, Anda hampir pasti akan menghadapi situasi di mana Anda perlu menyorot seluruh baris atau kolom data jika kondisi tertentu terpenuhi
Spreadsheet Google Sheets dengan formula khusus pemformatan bersyarat ditunjukkan pada tangkapan layar di bawah. Jika salah satu bulan yang dipilih di Sel C2 muncul di kolom pertama, seluruh baris untuk bulan tersebut akan disorot
Klik di sini untuk mempelajari cara membuat daftar drop-down di Google Sheets. Ini adalah panduan lengkap yang akan membantu Anda meningkatkan keterampilan spreadsheet Anda
=$A5=$C$2
Ikuti langkah-langkah di bawah ini untuk membuat rumus pemformatan bersyarat yang menyorot baris berdasarkan nilai yang Anda masukkan
Langkah 1. Pilih rentang sel untuk Pemformatan Bersyarat
Langkah 2. Buka Format > Pemformatan bersyarat
Langkah 3. Masukkan Rumus Pemformatan Bersyarat ini. =$A5=$C$2
Untuk memasukkan rumus, pertama-tama, di bagian Aturan format, klik tarik-turun. Kemudian pilih "Rumus khusus adalah" dari daftar
Anda kemudian akan disajikan dengan kotak teks untuk memasukkan rumus khusus Anda, masukkan =$A5=$C$2 atau =$A5=”Bulan” jika Anda tidak menggunakan referensi sel
Kemudian atur pemformatan yang ingin Anda terapkan ke sel yang memenuhi kondisi ini
Langkah 4. Klik Selesai
Saat Anda telah menyelesaikan semua langkah dengan benar, spreadsheet Anda seharusnya dapat menyorot baris berdasarkan nilai yang Anda pilih
Untuk sel A5, referensi campuran digunakan untuk membuat bagian kolom referensi menjadi absolut;
Contoh 3. Rumus pemformatan bersyarat untuk Menyoroti tanggal yang jatuh pada akhir pekan
Ada beberapa aturan pemformatan bersyarat yang berhubungan dengan tanggal di lembar Google
Tapi tidak satu pun dari aturan ini yang memungkinkan Anda mengidentifikasi tanggal yang jatuh pada akhir pekan
Contoh ini akan menunjukkan kepada Anda rumus yang menyoroti tanggal akhir pekan
=ATAU(HARI KERJA(A3)=7,HARI KERJA(A3)=1)
Langkah 1. Pilih rentang sel untuk Pemformatan Bersyarat
Dalam contoh ini, Anda harus memilih semua sel yang berisi tanggal
Langkah 2. Buka Format > Pemformatan bersyarat
Langkah 3. Masukkan Formula Kustom Pemformatan Bersyarat ini. =ATAU(HARI KERJA(A3)=7,HARI KERJA(A3)=1)
Untuk memasukkan rumus, pertama-tama, di bagian Aturan format, klik tarik-turun. Kemudian pilih "Rumus khusus adalah" dari daftar
Anda kemudian akan disajikan dengan kotak teks untuk memasukkan formula khusus Anda, masukkan =OR(WEEKDAY(A3)=7,WEEKDAY(A3)=1)
Kemudian atur pemformatan yang ingin Anda terapkan pada tanggal yang jatuh pada akhir pekan
Langkah 4. Klik Selesai
Setelah Anda menyelesaikan semua langkah dengan benar, spreadsheet Anda seharusnya dapat menyorot sel dengan tanggal akhir pekan
Contoh 4. Sorot Tanggal dalam 15 hari ke depan
Contoh ini memiliki banyak tanggal untuk acara yang tercampur dalam rentang sel
Kami ingin membuat formula khusus pemformatan bersyarat yang menyoroti semua tanggal yang terjadi dalam 15 hari ke depan
Rumusnya harus cukup pintar untuk memastikan bahwa tanggal di masa depan dan 15 hari atau kurang dari tanggal hari ini
Seperti yang ditunjukkan di bawah ini, salah satu cara paling sederhana untuk melakukan ini adalah dengan menggunakan fungsi AND dan fungsi NOW
=AND(A4>SEKARANG(),A4 Pemformatan bersyarat
Langkah 3. Pilih "Rumus khusus adalah" di bawah Aturan format
Langkah 4. Masukkan Rumus Pemformatan Bersyarat ini. =AND(A4>SEKARANG(),A4 Pemformatan bersyarat
Langkah 3. Pilih "Rumus khusus adalah" di bawah Aturan format
Langkah 4. Masukkan Rumus Pemformatan Bersyarat ini. =COUNTIF(D2. E15,A2)=0
Langkah 5. Atur pemformatan di bawah Gaya Pemformatan
Langkah 6. Klik Selesai
Contoh 6. Formula untuk Menyoroti Laptop dengan setidaknya 16GB RAM di bawah $1000
Dengan menggunakan spreadsheet di bawah ini, Anda dapat menulis formula khusus pemformatan bersyarat untuk menemukan Laptop yang memiliki setidaknya 16GB RAM di bawah $1000
Gunakan rumus di bawah ini untuk membuat aturan ini
=AND($B4=16)
Dalam contoh ini, tanda dolar ($) mengunci referensi ke kolom B dan C, sedangkan fungsi AND memastikan kedua kondisi BENAR. Di baris tempat rumus mengembalikan BENAR, pemformatan dipicu
Langkah langkah
Langkah 1. Pilih rentang sel untuk Pemformatan Bersyarat
Langkah 2. Buka Format > Pemformatan bersyarat
Langkah 3. Pilih "Rumus khusus adalah" di bawah Aturan format
Step 4: Enter this Conditional Formatting Formula: =AND($B4=16)
Langkah 5. Atur pemformatan di bawah Gaya Pemformatan
Langkah 6. Klik Selesai
Contoh 7. Pemformatan Bersyarat Kustom Google Sheets untuk Menyoroti nilai Maksimum atau nilai Minimum dalam rentang
Ini adalah contoh yang terinspirasi dari pembaca untuk menulis rumus yang menyoroti nilai tertinggi dalam suatu rentang
Dengan menggunakan aturan pemformatan bersyarat kustom dan prasetel, Anda dapat dengan mudah menyorot nilai maksimum atau minimum dalam rentang tertentu
Dalam contoh ini, kita akan memeriksa dua cara untuk mencapai hasil ini
Pilihan 1. Menggunakan Lebih dari atau sama dengan aturan pemformatan bersyarat prasetel untuk menyorot nilai tertinggi dalam rentang
Seperti yang Anda lihat pada ilustrasi di atas, kolom penyimpanan memiliki semua nilai tertinggi yang dipilih. Ini dilakukan dengan menggunakan lebih besar dari atau sama dengan 'aturan. ’
Patuhi petunjuk berikut untuk mempelajari cara kami melakukannya
Langkah 1. Pilih rentang sel untuk Pemformatan Bersyarat
Dalam contoh ini, kita akan memilih sel D4. D16, itu adalah kolom yang memiliki nilai tertinggi yang ingin kita soroti
Langkah 2. Buka Format > Pemformatan bersyarat
Langkah 3. Pilih “Lebih besar dari atau sama dengan” di bawah Aturan format
CATATAN. Harap gunakan prasetel “Kurang dari atau sama dengan” jika Anda ingin menyorot nilai minimum dalam pemilihan
Langkah 4. Masukkan Rumus Pemformatan Bersyarat ini. =MAX(D4. D16)
Rumus ini menggunakan fungsi MAX untuk mengembalikan 'Benar' dari semua sel yang berisi nilai maksimum dalam rentang, karenanya memicu aturan pemformatan bersyarat untuk menyoroti nilai tertinggi
CATATAN. Harap ganti fungsi MAX dengan fungsi MIN jika Anda ingin menyorot nilai minimum dalam pemilihan
Langkah 5. Atur pemformatan di bawah Gaya Pemformatan
Langkah 6. Klik Selesai
pilihan 2. Menggunakan Pemformatan Bersyarat Rumus Kustom untuk menyorot baris dengan nilai tertinggi di Google Sheet
Di bawah ini adalah rumus yang digunakan untuk tugas ini
=$D4=MAX($D$4. $D$16)
Selain menggunakan aturan prasetel, Anda juga dapat menggunakan rumus pemformatan bersyarat khusus untuk mencapai fungsi ini. Ini sangat berguna jika Anda ingin rumus pemformatan bersyarat menyorot seluruh baris berdasarkan nilai tertinggi dalam kolom tertentu
Patuhi petunjuk di bawah tentang cara memilih atau menyorot baris berdasarkan nilai tertinggi di kolom tertentu
Langkah 1. Pilih rentang sel untuk Pemformatan Bersyarat
CATATAN. Jika Anda ingin agar pemformatan bersyarat diterapkan ke seluruh baris atau kolom, pilih untuk menyertakan semua baris dan kolom dalam kumpulan data
Langkah 2. Buka Format > Pemformatan bersyarat
Langkah 3. Pilih "Rumus khusus adalah" di bawah Aturan format
Langkah 4. Masukkan Rumus Pemformatan Bersyarat ini. =$D4=MAX($D$4. $D$16)
CATATAN. Harap ganti fungsi MAX dengan fungsi MIN jika Anda ingin menyorot nilai minimum dalam pemilihan
Di sini Anda harus menyesuaikan rumus sesuai dengan data Anda
Jika Anda ingin menerapkan ini pada data Anda, cukup ubah bagian $D4 dari rumus untuk mereferensikan sel yang memulai kolom yang berisi nilai yang Anda gunakan untuk kondisi tersebut. Kemudian ubah $D$4. bagian $D$16 untuk mencocokkan rentang seluruh kolom
Ingatlah untuk menyertakan tanda dolar di referensi sel Anda untuk surat kolom. Ini akan mengunci kolom, mencegahnya mereferensikan kolom lain
Langkah 5. Di bawah Gaya Pemformatan, Atur pemformatan
Langkah 6. Klik Selesai
Dan begitulah, panduan langkah demi langkah untuk memilih atau menyorot nilai tertinggi dalam rentang menggunakan aturan prasetel dan formula pemformatan bersyarat khusus di Google Sheets
Memecahkan Masalah Formula Pemformatan Bersyarat Kustom di Google Sheets
Saat bekerja dengan formula khusus pemformatan bersyarat, kemungkinan besar Anda akan selesai menulisnya hanya untuk menemukan bahwa itu tidak berpengaruh pada data Anda. Kemungkinan besar, ada kesalahan dalam rumus Anda
Solusinya mungkin sesederhana memulai rumus dengan tanda sama dengan (=)
Tanpa tanda sama dengan (=) sebelum formula Anda, Google Sheets akan memperlakukannya sebagai teks secara diam-diam, menjadikannya tidak efektif
Untuk memperbaikinya, mulailah rumus dengan tanda sama dengan (=).
Namun, jika tampaknya tidak ada yang salah dengan rumus Anda, tetapi masih gagal memicu aturan pemformatan bersyarat, Anda mungkin perlu menggali lebih dalam dengan menguji rumus langsung di dalam spreadsheet. Ini memungkinkan Anda untuk melihat dengan tepat apa yang sedang terjadi
Ketika Anda langsung memasukkan rumus ke dalam spreadsheet Anda, dan itu mengembalikan BENAR atau SALAH, itu berarti mereka berfungsi dengan baik, tetapi aturan yang Anda tetapkan tidak dipicu
Namun, jika Anda mendapatkan beberapa kesalahan atau tidak mengembalikan BENAR atau SALAH setelah memasukkan rumus Anda, itu merupakan indikasi bahwa ada yang salah dengan rumus tersebut, dan Anda harus mempertimbangkan untuk menulis ulang rumus tersebut.
Keterbatasan Rumus Pemformatan Bersyarat
Sejauh ini, saya telah mengidentifikasi satu batasan dengan pemformatan bersyarat berbasis rumus di Google Sheets yang berkaitan dengan skala warna
Pemformatan bersyarat skala warna belum memungkinkan. Anda hanya dapat menggunakan fitur pemformatan sel standar seperti warna isian, opsi batas, dan format angka
Bagaimanapun, rumus pemformatan bersyarat sangat bagus untuk digunakan di Google Sheets
Kesimpulan
Ada banyak hal yang dapat Anda lakukan dengan formula khusus pemformatan bersyarat Google Sheets
Saya harap contoh dalam panduan ini akan menginspirasi Anda untuk membuat formula Anda sendiri yang memenuhi kebutuhan spesifik Anda