Rumus bersarang dan logika kondisional di Excel

Jika Anda bekerja dengan spreadsheet setiap hari, cepat atau lambat Anda akan menemui masalah ini. Rumus bersarang dan logika kondisional di ExcelHal-hal tersebut merupakan dasar dari banyak model yang ampuh: mulai dari sistem lulus/gagal sederhana hingga sistem kompleks seperti komisi, diskon, atau klasifikasi data.

Kuasai fungsi IF, AND, OR, dan NOT, beserta variasi yang lebih modern seperti Alternatif tipe IF.SET dan VLOOKUPInilah yang membuat perbedaan besar antara spreadsheet yang "kurang lebih berfungsi" dan file yang stabil, mudah dipelihara, dan bebas dari kejutan di menit-menit terakhir. Mari kita lihat dengan tenang, tetapi langsung dan dengan contoh yang jelas.

Apa itu logika bersyarat di Excel dan mengapa hal itu sangat penting?

Logika bersyarat di Excel didasarkan pada pengajuan sebuah pertanyaan benar/salah dan mengembalikan hasil yang berbeda dalam setiap kasus. Inti dari semua ini adalah fungsi IF, yang menjawab gagasan: "jika A terjadi, lakukan B; jika tidak, lakukan C".

Fungsi itu IF memeriksa suatu kondisi logis (uji logika) dan, tergantung pada apakah kondisi terpenuhi atau tidak, ia mengembalikan nilai yang berbeda: satu ketika hasilnya BENAR dan yang lain ketika hasilnya SALAH. Ini berlaku untuk angka maupun teks, tanggal, atau hasil rumus lainnya.

Dari segi sintaks, fungsi klasik ditulis seperti ini: =IF(uji_logika;nilai_jika_benar;nilai_jika_salah)Tes logika dapat memiliki operator perbandingan seperti =, <>, >, <, >= atau <= dan dapat menggunakan referensi sel, konstanta, atau bahkan fungsi lain.

Argumen nilai_jika_benar Inilah yang dikembalikan ketika kondisi terpenuhi: bisa berupa teks, angka, rumus lain, atau referensi. Argumennya nilai_jika_salah Inilah yang dikembalikan ketika kondisi tidak terpenuhi; jika Anda membiarkannya kosong, Excel akan menampilkan FALSE secara default, yang seringkali menyebabkan kebingungan jika Anda tidak merencanakannya.

Contoh sederhana pertama dengan fungsi IF

Salah satu penggunaan umum fungsi IF adalah untuk mengklasifikasikan data ke dalam dua kelompok. Misalnya, Anda mungkin ingin membedakan apakah seseorang termasuk dalam kelompok tertentu atau tidak. dewasa atau anak di bawah umur tergantung pada usia mereka:

Di dalam sel, Anda bisa menulis sesuatu seperti ini: =IF(C3>=18; "Dewasa"; "Anak di bawah umur")Di sini, uji logikanya adalah C3>=18, hasilnya jika BENAR adalah “Dewasa” dan hasilnya jika SALAH adalah “Anak di bawah umur”.

Keindahan struktur ini terletak pada kenyataan bahwa ia berfungsi sama dengan nilai teksMelanjutkan contoh yang sama, Anda dapat menanyakan apakah seseorang berhak mendapatkan diskon berdasarkan label yang telah Anda berikan kepada mereka di kolom lain: =IF(D3="Di atas 18"; "Ya"; "Tidak").

Pada dasarnya, logika di balik semua contoh ini selalu sama: Excel mengevaluasi pengujian, memeriksa apakah hasilnya benar atau salah, dan dalam setiap kasus, mengembalikan hasil yang berbeda tergantung pada apa yang telah Anda definisikan.

Gabungkan IF dengan fungsi logika AND, OR, dan NOT.

Ketika situasi mengharuskan evaluasi lebih dari satu kondisi, segalanya menjadi menarik. Excel menawarkan fungsi-fungsi logika. Dan, Atau dan Tidakyang sering dikombinasikan dengan SI untuk membangun pengujian yang lebih kompleks.

Fungsi itu Y Mengembalikan TRUE hanya jika semua kondisi benarJika dikombinasikan dengan SI, struktur tipikalnya adalah: =IF(AND(kondisi1; kondisi2); nilai_jika_benar; nilai_jika_salah)Ini sangat berguna ketika Anda perlu memenuhi beberapa persyaratan sekaligus, seperti rentang usia atau beberapa kriteria validasi.

Fungsi itu O mengembalikan TRUE jika setidaknya salah satu kondisi terpenuhiDalam rumus SI, integrasinya akan dilakukan dengan cara ini: =IF(OR(kondisi1; kondisi2); nilai_jika_benar; nilai_jika_salah)Ini sangat cocok untuk kasus-kasus di mana terdapat berbagai situasi yang memberikan hak kepada seseorang untuk mendapatkan hasil yang sama, seperti beberapa alasan yang dapat diterima untuk mengajukan permohonan tunjangan.

Fungsi itu TIDAK Fungsi ini membalikkan hasil logika: jika kondisinya benar, ia mengembalikan FALSE, dan jika salah, ia mengembalikan TRUE. Jika dikombinasikan dengan IF, bentuknya akan seperti ini: =IF(NOT(kondisi); nilai_jika_benar; nilai_jika_salah), sesuatu yang berguna ketika yang Anda cari justru kebalikan dari sebuah tes.

Fungsi AND dan OR menerima hingga 255 kondisi individuNamun, dalam praktiknya tidak disarankan untuk melangkah sejauh itu, karena rumus-rumus tersebut menjadi sangat sulit dibaca, diuji, dan dipelihara. Fungsi NOT, di sisi lain, hanya memperbolehkan satu kondisi.

Contoh praktis dengan YA, DAN, ATAU, dan TIDAK

Bayangkan Anda memiliki angka-angka di spreadsheet Anda dan ingin memvalidasi rentang. Anda dapat mengatur sesuatu seperti ini: =IF(AND(A2>0; B2<100); TRUE; FALSE)Rumus ini mengkonfirmasi bahwa A2 lebih besar dari 0 dan B2 kurang dari 100. Jika keduanya benar, rumus akan mengembalikan TRUE; jika salah satu salah, rumus akan mengembalikan FALSE.

Ini juga berfungsi sempurna dengan teks. Contohnya adalah =IF(AND(A3="Merah", B3="Hijau", TRUE, FALSE)Di sini, Y mensyaratkan bahwa A3 berisi “Merah” dan B3 “Hijau”. ​​Jika hanya satu dari keduanya yang cocok, hasilnya akan SALAH, karena kedua kondisi tidak terpenuhi secara bersamaan.

Penggunaan huruf O mengubah perilakunya. Misalnya: =IF(OR(A4>0; B4<50); TRUE; FALSE) Fungsi ini akan mengembalikan TRUE jika A4 lebih besar dari 0 atau jika B4 kurang dari 50; cukup salah satu dari keduanya bernilai benar agar fungsi OR dianggap benar.

Kasus umum lainnya: =IF(OR(A5="Merah", B5="Hijau", TRUE, FALSE)Di sini, rumus akan mengembalikan TRUE jika setidaknya satu dari dua sel berisi teks yang diharapkan. Untuk kasus di mana Anda ingin memeriksa beberapa opsi yang valid, operator OR adalah pilihan yang ideal.

Dengan NO, Anda dapat membalikkan kriterianya. Misalnya, =IF(NOT(A6>50); TRUE; FALSE) Fungsi ini akan mengembalikan nilai TRUE jika A6 tidak lebih besar dari 50. Atau =IF(NO(A7="Red"); TRUE; FALSE) Ini akan ditandai TRUE jika isi A7 berbeda dari “Merah”.

Bekerja dengan tanggal menggunakan logika kondisional

Logika bersyarat di Excel tidak terbatas pada angka atau teks; dengan Tanggal juga dapat dibandingkan. Sangat berguna untuk tanggal jatuh tempo, tenggat waktu, atau tindak lanjut.

Contoh sederhana: =IF(A2>B2; TRUE; FALSE) Rumus ini membandingkan dua tanggal; jika tanggal di sel A2 lebih baru daripada tanggal di sel B2, rumus akan mengembalikan nilai TRUE. Ini memungkinkan Anda untuk dengan cepat menentukan apakah suatu peristiwa terjadi setelah tanggal referensi.

Jika Anda ingin memvalidasi bahwa suatu tanggal berada dalam rentang tertentu, Anda dapat menggunakan Y: =IF(AND(A3>B2; A3Rumus ini akan memeriksa apakah A3 lebih lambat dari B2 dan lebih awal dari C2. Jika kedua kondisi tersebut terpenuhi, maka dianggap berada dalam rentang yang ditentukan.

Menggabungkan O membuka lebih banyak kemungkinan. Misalnya: =IF(OR(A4>B2; A4Kriterianya di sini adalah A4 lebih baru daripada B2 atau berada dalam rentang 60 hari dari B2, menggunakan jumlah hari tepat setelah tanggal tersebut.

Dan dengan NO, kasus-kasus yang dikecualikan tidak dapat diidentifikasi, seperti =IF(NOT(A5>B2); TRUE; FALSE), yang menandai TRUE ketika A5 tidak lebih lambat dari B2, yaitu, ketika lebih awal dari atau sama dengan tanggal referensi.

Gunakan AND, OR, dan NOT dalam pemformatan bersyarat

Logika kondisional tidak hanya digunakan dalam rumus biasa, tetapi juga merupakan kunci dari... pemformatan bersyarat berbasis rumusDalam konteks itu, Anda seringkali bahkan tidak perlu membungkus AND, OR, atau NOT di dalam YES.

Pada tab Beranda di Excel, di dalam Pemformatan bersyarat > Aturan baruAnda dapat memilih “Gunakan rumus untuk menentukan sel mana yang akan diformat” dan langsung menulis rumus logika, misalnya =A2>B2Jika kondisi tersebut benar, Excel akan menerapkan format yang Anda pilih.

Melanjutkan dengan contoh tanggal, Anda dapat menggunakan =AND(A3>B2; A3 Sebagai rumus pemformatan bersyarat untuk menyorot sel yang berisi tanggal antara B2 dan C2. Selama kondisi tersebut terpenuhi, sel akan diformat dengan pemformatan yang dipilih.

Kemungkinan lain: =O(A4>B2; A4<B2+60) untuk menyoroti tanggal yang memenuhi setidaknya satu dari kriteria tersebut. Menggunakan O memudahkan untuk mendeteksi beberapa jenis huruf besar/kecil dalam satu aturan pemformatan bersyarat.

Terakhir, Anda bisa menyarankan =BUKAN(A5>B2) Untuk menyoroti nilai yang tidak melebihi tanggal tertentu, tanpa perlu membungkusnya dalam IF. Pemformatan bersyarat langsung menginterpretasikan hasil benar/salah dari rumus tersebut.

Apa itu rumus IF bersarang dan kapan menggunakannya?

Ketika satu syarat saja tidak cukup dan Anda membutuhkan lebih dari dua kemungkinan hasilRumus IF bersarang mulai berperan: pada dasarnya, memasukkan fungsi IF di dalam fungsi IF lain sebagai bagian dari argumen value_if_false atau value_if_true.

Idenya adalah bahwa Excel mengevaluasi tes pertamaJika kondisi terpenuhi, ia akan mengembalikan hasil yang diharapkan; jika tidak, alih-alih memberikan nilai akhir, ia akan melanjutkan untuk mengevaluasi fungsi IF kedua yang berisi pengujian lain, dan seterusnya. Dengan cara ini, Anda dapat menghubungkan beberapa tingkat pengambilan keputusan.

Salah satu contoh yang terkenal adalah mengubah nilai numerik menjadi nilai huruf. Versi pertama yang sederhana mungkin terlihat seperti ini: =IF(D2>89; «A»; SI(D2>79; «B»; SI(D2>69; «C»; SI(D2>59; «D»; «F»)))).

Logika di balik rumus ini adalah langkah demi langkah: jika nilainya lebih besar dari 89, maka diberikan nilai A; jika tidak, diperiksa apakah nilainya lebih besar dari 79 untuk memberikan nilai B; jika itu juga tidak terjadi, 69 dicoba untuk nilai C; kemudian 59 untuk nilai D, dan jika tidak ada yang benar, maka diberikan nilai F. Ini adalah contoh klasik dari... JIKA bersarang dalam rantai.

Skema ini menjadi lebih kompleks jika Anda ingin memperhalusnya lebih lanjut, misalnya dengan memisahkan A+, A, dan A- atau menambahkan nuansa yang lebih banyak. Rumusnya bisa berkembang menjadi seperti ini: =IF(B2>97; «A+»; SI(B2>93; «A»; SI(B2>89; «A-«; SI(B2>87; «B+»; SI(B2>83; «B»; SI(B2>79; «B-«; SI(B2>77; «C+»; SI(B2>73; «C»; SI(B2>69; «C-«; IF(B2>57; «D+»; IF(B2>53; «D»; IF(B2>49; «D-«; «F»)))))))))))).

Meskipun secara fungsional benar, jenis rumus ini Ini panjang, membosankan untuk ditulis, dan sulit untuk dibuktikan.Selain itu, setiap perubahan pada batas atau label mengharuskan Anda untuk mengedit seluruh string secara manual, yang sangat meningkatkan kemungkinan terjadinya kesalahan yang sulit dideteksi.

Keterbatasan dan masalah rumus IF bersarang

Excel memungkinkan penyusunan bertingkat hingga 64 fungsi IF dalam rumus yang samaNamun, bukan berarti mendekati batas tersebut adalah ide yang bagus. Setelah beberapa level, rumus tersebut mulai menjadi sulit dikelola.

Di antara kekurangan utamanya adalah kompleksitas logika dan risiko kesalahan tersembunyiRangkaian Sistem Informasi (SI) mungkin tampak berfungsi dengan baik dalam sebagian besar kasus, namun gagal dalam sebagian kecil situasi yang tidak terdeteksi hingga masalah telah terjadi.

Kesulitan lainnya adalah pemeliharaan jangka menengah dan panjangJika Anda kembali ke lembar kerja itu beberapa bulan kemudian, atau jika orang lain mewarisinya, memahami secara tepat apa yang dimaksudkan oleh rumus yang penuh dengan tanda kurung itu akan sangat merepotkan, terutama jika tidak ada yang mendokumentasikan logika aslinya.

Jika Anda mendapati diri Anda menulis rumus IF yang terus berkembang dan menambahkan semakin banyak kondisi, mungkin sudah saatnya untuk mengusulkan strategi yang berbedaGunakan fungsi yang lebih sesuai atau susun ulang model data.

Contoh umum dari rumus IF bersarang yang menjadi sulit dikelola adalah perhitungan komisi berdasarkan tingkatan. Misalnya: =IF(C9>15000; 20%; SI(C9>12500; 17,5%; SI(C9>10000; 15%; SI(C9>7500; 12,5%; SI(C9>5000; 10%; 0)))))di mana Anda meningkatkan komisi sesuai dengan tingkat pendapatan Anda.

Pentingnya urutan dalam kondisi bersarang

Dalam rumus IF bersarang yang bekerja dengan rentang, Urutan perbandingan sangat penting.Jika Anda menetapkan kondisi yang terlalu luas di awal, pengujian selanjutnya tidak akan pernah dievaluasi untuk nilai-nilai tertentu, sehingga menghasilkan hasil yang salah.

Melanjutkan contoh komisi, jika Anda menulis perbandingan dalam urutan menaik (pertama >5000, kemudian >7500, dst.) alih-alih urutan menurun, Anda akan menemukan bahwa nilai tinggi memicu kondisi valid pertama dan hentikan evaluasiSebagai contoh, dengan pendapatan 12.500, rumus yang disusun dengan buruk dapat menghasilkan 10% hanya karena nilainya lebih besar dari 5.000 dan gagal memeriksa golongan pendapatan yang lebih tinggi.

Jenis kegagalan ini bisa sangat berbahaya karena... Tidak menampilkan kesalahan yang terlihat.Rumus tersebut menghasilkan persentase, yang tampaknya benar, tetapi perhitungannya salah. Dalam konteks penggajian, bonus, atau penetapan harga, perbedaan ini dapat memiliki konsekuensi yang signifikan.

Oleh karena itu, ketika menggunakan pernyataan IF bersarang untuk rentang atau cakupan, disarankan untuk mendefinisikan dengan jelas urutan kondisi yang ingin Anda evaluasi dan memeriksa beberapa kasus batas untuk memastikan bahwa logika tersebut berperilaku seperti yang diharapkan.

Pada titik ini, masuk akal untuk mempertimbangkan alternatif yang lebih andal, seperti menggunakan tabel referensi dengan VLOOKUP atau menggunakan fungsi yang lebih modern yang menyederhanakan struktur rumus.

Gunakan VLOOKUP sebagai alternatif untuk pernyataan IF bersarang yang panjang.

Dalam banyak skenario di mana Anda menggunakan rangkaian pernyataan IF yang panjang, Anda dapat menyelesaikannya dengan lebih rapi menggunakan... tabel referensi dan fungsi pencarian, seperti VLOOKUP. Idenya adalah untuk mengalihkan kompleksitas dari rumus ke tabel kecil yang mengumpulkan semua kasus.

Untuk melakukan ini, pertama-tama Anda membuat tabel dengan dua kolom: di kolom pertama Anda menempatkan rentang atau nilai referensi yang diurutkan (misalnya, batas tingkatan kelas atau kelompok pendapatan) dan di kolom kedua hasil yang terkait dengan setiap segmen (nilai huruf, persentase komisi, dll.).

Setelah Anda memiliki tabel tersebut, Anda dapat menggunakan rumus seperti ini: =VLOOKUP(C2; C5:D17; 2; TRUE)di mana C2 adalah nilai yang dicari, C5:D17 adalah rentang yang mencakup tabel, 2 menunjukkan bahwa Anda menginginkan hasil dari kolom kedua dan TRUE mengaktifkan pencarian rentang perkiraan.

Contoh serupa lainnya adalah =VLOOKUP(B9; B2:C6; 2; TRUE)Fungsi ini mencari nilai di sel B9 pada kolom pertama rentang B2:C6 dan mengembalikan nilai yang sesuai dari kolom kedua. Dengan cara ini Anda dapat mengganti sebuah nilai. IF bersarang bertingkat banyak dengan satu fungsi pencarian.

Keuntungannya jelas: Tabel referensi terlihat dan dapat diedit.Anda dapat mengubah batasan, persentase, atau label tanpa menyentuh rumus, dan jika Anda tidak ingin pengguna melihatnya, Anda selalu dapat meletakkannya di lembar lain dalam buku kerja yang sama.

SETIFY: evolusi alami dari IF bersarang

Pada versi Excel modern (Office 2019, 2021, dan Microsoft 365) Anda memiliki fungsi tersebut. YA. MENGATUR, dirancang secara khusus untuk mengurangi kebutuhan untuk merangkai pernyataan IF satu demi satu dan membuat kode jauh lebih mudah dibaca.

IFS.SET memungkinkan Anda untuk mendefinisikan hingga 127 pasangan “uji logika – hasil” dalam rumus yang sama. Alih-alih memiliki satu nilai jika salah, Anda mencantumkan kasus satu demi satu, dan Excel akan mengembalikan hasil yang sesuai. kondisi pertama yang terpenuhi.

Mengambil contoh nilai, Anda dapat mengubah rumus klasik yang terdiri dari beberapa IF menjadi sesuatu seperti ini: =IFS(D2>89; "A"; D2>79; "B"; D2>69; "C"; D2>59; "D"; TRUE; "F")Pasangan terakhir (TRUE; «F») berfungsi sebagai "penangkap semua" untuk setiap kasus yang tidak tercakup oleh kondisi di atas.

Keuntungan besarnya adalah bahwa Kekacauan tanda kurung menghilang Hal ini umum terjadi pada pernyataan IF bersarang, dan rumusnya jauh lebih mudah dibaca dan dipelihara, terutama ketika Anda memiliki beberapa bagian atau skenario yang berbeda.

Dalam konteks yang lebih lanjut, Anda bahkan dapat menggabungkan IF.SET dengan fungsi logika seperti AND atau OR dalam pengujian, mirip dengan cara Anda melakukannya dalam IF klasik, tetapi dengan tetap mempertahankan struktur yang lebih terorganisir.

Namun, karena tidak ada argumen value_if_false generik, Anda harus mendefinisikan secara eksplisit kasus sebaliknya, biasanya dengan pasangan TRUE terakhir – hasil default, sehingga rumus selalu mengembalikan sesuatu yang masuk akal.

Contoh tambahan penggunaan tingkat lanjut dari IF dan logika kondisional.

Selain contoh umum seperti nota dan komisi, fungsi IF dan variannya dapat dikombinasikan dengan fungsi Excel lainnya untuk menyelesaikan berbagai masalah. skenario yang cukup beragam Dalam hari ke hari.

Misalnya, Anda dapat menggunakan IF untuk Urutkan titik data menjadi dua kelompok.Aplikasi yang disetujui dan ditolak, klien aktif dan tidak aktif, produk di atas atau di bawah target penjualan, dll. Struktur logikanya sama; hanya kondisi dan teks atau nilai yang dikembalikan yang berubah.

Anda juga dapat memeriksanya nilai sebagai teksSebagai contoh, untuk memvalidasi apakah sebuah buku milik penulis tertentu. Rumusnya akan mirip dengan... =IF(B2="Stephen King", "Ya", "Tidak")Hal ini juga berlaku untuk daftar negara bagian, kode, atau teks lain apa pun yang perlu Anda verifikasi.

Ada beberapa kasus di mana hal itu masuk akal. Gunakan perintah IF bersarang untuk melakukan beberapa pemeriksaan berturut-turut.Contoh tipikalnya adalah pelacakan pengiriman: jika paket ditandai sebagai telah dikirim, Anda mengirimkan pesan balasan; jika belum, Anda memeriksa apakah paket sudah siap; jika belum, Anda menunjukkan "tertunda" atau status serupa.

Selain itu, SI dapat diintegrasikan dengan fungsi lain seperti ATAU, DAN, COCOKKAN, GABUNGKAN dan masih banyak lagi. Contoh yang sedikit lebih kompleks adalah melacak harga saham dan menampilkan teks yang menunjukkan apakah harga tersebut naik atau turun, dengan menambahkan persentase perubahan, misalnya "Naik 5%" atau "Turun 3%".

Dikombinasikan dengan fungsi penanganan kesalahan seperti IF.ERROR atau IF.NDAnda dapat menangani situasi di mana evaluasi tidak valid, dengan mengembalikan pesan khusus alih-alih kode kesalahan yang tidak ramah.

Kesalahan umum saat bekerja dengan IF dan logika kondisional

Salah satu masalah yang paling sering terjadi saat menggunakan IF adalah menemukan bahwa rumus tersebut mengembalikan angka 0 yang tampaknya tidak berartiHal ini biasanya terjadi karena Anda belum secara eksplisit mendefinisikan argumen value_if_true dan value_if_false, atau karena satu cabang mengembalikan nilai kosong sementara cabang lainnya diinterpretasikan sebagai angka.

Kesalahan klasik lainnya adalah pesannya. #NAMA?, yang hampir selalu menunjukkan bahwa ada kesalahan penulisan dalam rumus: nama fungsi, teks tanpa tanda kutip, pemisah yang salah, atau referensi ke fungsi yang tidak ada di versi Excel tersebut.

Untuk menghindari hal ini, disarankan Pastikan teks selalu diapit oleh tanda kutip.Periksa apakah uji logika memiliki sintaks yang benar (operator, tanda kurung, dll.) dan apakah nama fungsi sesuai persis dengan versi dalam bahasa Anda.

Saat bekerja dengan rumus yang panjang atau bersarang, sangat disarankan untuk menggunakan asisten evaluasi rumus dari tab Rumus. Panduan ini memungkinkan Anda untuk menelusuri evaluasi langkah demi langkah dan melihat hasil yang dikembalikan oleh setiap bagian, yang sangat penting ketika logikanya menjadi rumit.

Terakhir, pertahankan fitur-fitur seperti IF.ERROR atau IF.ND untuk membungkus rumus utama Anda dan menangkap kesalahan saat runtime secara elegan, menampilkan teks yang mudah dipahami alih-alih kode kesalahan yang mungkin membingungkan pengguna.

Menguasai semua fungsi logika bersyarat ini dan memahami keterbatasannya memungkinkan Anda untuk membuat spreadsheet yang jauh lebih bersih, lebih andal, dan lebih mudah dipelihara, mengurangi jumlah masalah tersembunyi dan menghemat waktu setiap kali Anda perlu memperbarui atau memperluas model Anda.