Cara Membuat Rumus Kondisional di Excel: Panduan Lengkap dengan Contoh, Trik, dan Aplikasi

Kuasai rumus kondisional di Excel Saat ini, ini merupakan keterampilan penting bagi mereka yang bekerja dengan data setiap hari serta bagi mereka yang ingin meningkatkan tingkat pengorganisasian dan produktivitas mereka di bidang apa pun. Anda mungkin pernah mendapati diri Anda perlu menambahkan hanya nilai-nilai tertentu, menghitung catatan yang memenuhi kondisi tertentu, atau secara otomatis menyorot informasi dalam lembar kerja. Kabar baiknya adalah Excel memungkinkan Anda melakukan semua ini—dan banyak lagi—dengan cara yang sederhana, cepat, dan sangat fleksibel berkat fungsi kondisional.

Dalam artikel ini, Anda akan menemukan cara kerja rumus-rumus ini, kegunaannya, dan cara mengintegrasikannya ke dalam alur kerja Anda untuk menghemat waktu. Kami akan menunjukkan contoh-contoh yang jelas dan praktis, menjawab pertanyaan umum, dan mengungkap trik yang digunakan para ahli untuk membawa pengelolaan data ke tingkat berikutnya. Biasakan diri dan buka Excel—tur ini menjanjikan akan sangat membantu! sebagai permulaan dan maju!

Apa rumus kondisional di Excel dan mengapa Anda harus mempelajari cara menggunakannya?

itu rumus bersyarat di Excel memungkinkan Anda untuk melakukan perhitungan atau tindakan hanya jika kondisi tertentu terpenuhi. Mereka berfungsi sebagai semacam pertanyaan logis: jika apa yang diajukan itu benar, maka suatu hasil akan dihasilkan; jika salah, yang lain. Hal ini tidak hanya membuat lembar data Anda lebih cerdas, tetapi juga memungkinkan Anda untuk mengotomatiskan proses, menghindari kesalahan manual, dan menganalisis sejumlah besar data dengan efisiensi yang mengesankan.

Ketika seseorang mulai belajar pemrograman, pernyataan kondisional (seperti IF-ELSE) biasanya merupakan hal pertama yang mereka pelajari. Di Excel, padanannya adalah kondisi yang dapat kita buat menggunakan rumus, yang menghasilkan penting untuk analisis data, pengambilan keputusan dan visualisasi hasil secara profesional. Fungsi IF Ini adalah yang paling terkenal dari semuanya, tetapi pada kenyataannya alam semesta kondisional mencakup lebih banyak lagi: COUNTIF, SUMIF, AVERAGEIF dan varian-variannya berdasarkan himpunan, sebagai tambahan terhadap kombinasi dengan fungsi logika (AND, OR, NOT).

Bagaimana mengetahui cara membuat rumus ini akan membantu Anda? Dari mengotomatiskan laporan, mengklasifikasikan catatan, mengendalikan inventaris, mendeteksi kesalahan, menyoroti pola dan tren, hingga meningkatkan peluang Anda untuk mendapatkan pekerjaan yang lebih baik! Excel adalah salah satu alat yang paling diminati di pasar kerja, dan menguasai fungsi kondisionalnya dapat membuat banyak perbedaan.

Untuk apa sebenarnya fungsi kondisional digunakan?

itu fungsi kondisional Mereka dirancang untuk melakukan operasi matematika, statistik, atau organisasi pada serangkaian data, tetapi hanya berdasarkan kriteria tertentu yang Anda tentukan. Mereka berguna untuk kasus sehari-hari yang sederhana dan proyek analisis profesional yang rumit. Mari kita lihat beberapa aplikasi yang paling umum:

• Mengotomatiskan perhitungan berulang: Mereka meminimalkan kesalahan dan menghemat waktu dengan memungkinkan Anda melakukan operasi dalam hitungan detik yang biasanya harus Anda lakukan secara manual berulang kali.
• Filter dan analisis data yang relevan: Dengan ini, dimungkinkan untuk mengekstrak hanya informasi yang memenuhi kondisi tertentu, misalnya, penjualan di atas ambang batas tertentu, karyawan yang termasuk dalam departemen tertentu, tanggal kedaluwarsa yang akan datang, dll.
• Mengidentifikasi pola dan tren: Saat Anda menggabungkan rumus bersyarat dengan alat visual seperti pemformatan bersyarat, Anda dapat menyorot nilai ekstrem, nilai outlier, preferensi pelanggan, tanggal kritis, dan banyak lagi secara sekilas.
• membuat keputusan berdasarkan informasi: Dengan memungkinkan Anda menganalisis sejumlah besar informasi dari berbagai sudut, mereka membantu Anda mendasarkan kesimpulan dan tindakan pada data, bukan asumsi.
• Buat dasbor dan kartu skor: Dasbor yang paling berguna dan visual hampir selalu bergantung pada kondisi untuk menampilkan indikator utama dengan cara yang dinamis dan otomatis.

Tidak mengherankan, saat ini, keterampilan ini dianggap sebagai keterampilan inti di kantor, administrasi, keuangan, logistik, kendali mutu, sumber daya manusia, dan jabatan lainnya. Di dalam dan di luar pekerjaan, mengetahui cara menggunakan kondisional akan membuat hidup Anda lebih mudah..

Jenis rumus kondisional apa yang ada di Excel?

Excel mengintegrasikan beberapa fungsi seri kondisional, masing-masing dirancang untuk memecahkan masalah sehari-hari seperti menambah, menghitung, membuat rata-rata, mencari, membandingkan, atau mengembalikan informasi berdasarkan kriteria yang disesuaikan. Mereka juga dapat dikombinasikan satu sama lain untuk mencapai hasil yang lebih maju dan beradaptasi dengan tugas yang semakin menantang.

Berikut ini adalah beberapa yang utama:

• fungsi JIKA: Melakukan pemeriksaan logika dan mengembalikan satu hasil jika BENAR dan hasil lain jika SALAH.
• fungsi COUNTIF: Menghitung berapa banyak sel dalam suatu rentang yang memenuhi suatu kondisi.
• Fungsi SUMIF: Menjumlahkan nilai sel yang memenuhi persyaratan yang ditentukan.
• fungsi AVERAGEIF: Menghitung rata-rata nilai sel yang memenuhi kriteria.
• Fungsi berdasarkan set: COUNTIFS, SUMIFS, dan AVERAGEIFS memungkinkan Anda menerapkan beberapa kriteria sekaligus, sangat berguna dalam analisis yang kompleks.
• Fungsi dan operator logika: Y, O, TIDAK memungkinkan Anda membangun beberapa atau lebih kondisi yang lebih canggih.

Selain itu, Anda dapat menumpuk fungsi—dengan kata lain, menggunakan satu fungsi di dalam fungsi lain untuk menguji kondisi berantai atau hierarki keputusan—praktik utama dalam hal mengklasifikasikan atau mengelompokkan data ke dalam kategori berbeda.

Memahami perbedaan antara "kriteria" dan "kondisi" di Excel

Dalam konteks Excel, penting untuk membedakan antara kondisi y kriteria:

• Condición: merujuk pada ekspresi logika lengkap dengan operator dan nilai (contoh: A2 <= 3). Digunakan pada fungsi seperti IF, AND, OR, NOT, dan lain-lain.
• Kriteria: adalah bagian spesifik yang ditafsirkan Excel sebagai filter saat menghitung, menjumlahkan, atau membuat rata-rata (misalnya, ">50" dalam COUNTIF).

Kriteria biasanya diapit tanda kutip., dan dapat sesederhana angka eksak atau serumit referensi dan operator logika secara bersamaan.

Fungsi IF: titik awal untuk rumus kondisional

La fungsi JIKA Tidak diragukan lagi, ini adalah dasar semua rumus kondisional di Excel. Ini digunakan dalam berbagai konteks, dan setara dengan IF dari pemrograman klasik.

Sintaksnya adalah:

=IF(uji_logika;nilai_jika_benar;nilai_jika_salah)

Masing-masing elemen ini memainkan peran:

• tes_logika: tempat Anda menentukan kondisi yang akan diperiksa (misalnya, A1 > 50).
• nilai_jika_benar: apa yang akan terjadi jika kondisinya benar (bisa berupa teks, angka, operasi, atau bahkan fungsi lain).
• nilai_jika_salah: apa yang terjadi jika kondisinya salah.

Contoh sederhana: Jika Anda memiliki kolom nilai (B2) dan Anda ingin menandai "Lulus" untuk nilai yang nilainya 60 atau lebih, dan "Gagal" untuk nilai yang lain:

=IF(B2>=60; "Lulus"; "Gagal")

Anda dapat menyalin rumus ini ke bawah untuk mengevaluasi seluruh daftar nilai, dan mengubah kondisi atau teks sesuai kebutuhan. Dan jika yang Anda butuhkan adalah mengklasifikasikan ke dalam lebih dari dua kategori, cukup menumpuk lebih dari satu fungsi IF di dalam fungsi IF lain:

=IF(B2>=90; "Sangat Baik"; IF(B2>=60; "Lulus"; "Gagal"))

Kesalahan paling umum saat menggunakan IF biasanya disebabkan oleh tanda kurung yang tidak ditutup, referensi yang salah, atau argumen yang tidak lengkap. Jika Anda menerima angka 0 yang tidak terduga atau #NAME? kesalahan, periksa rumus dengan hati-hati.

Menggabungkan fungsi kondisional: IF dengan AND, OR dan NOT

Dalam situasi yang lebih kompleks, Anda mungkin perlu menganalisis lebih dari satu kondisi pada saat yang bersamaan. Di sinilah Anda dapat menggabungkan JIKA dengan fungsi logika seperti AND, OR dan NOT.

• =JIKA(DAN(kondisi1; kondisi2); nilai1; nilai2): Tindakan akan dilaksanakan hanya jika kedua kondisi tersebut benar.
• =IF(ATAU(kondisi1; kondisi2); nilai1; nilai2): : Cukuplah jika satu kondisi itu benar agar dapat diterapkan.
• =JIKA(TIDAK(kondisi1); nilai1; nilai2): Mengembalikan kebalikan dari pengujian logika yang ditentukan.

Misalnya, lulus hanya jika nilai lebih dari 60 dan kehadiran lebih dari 80%:

=IF(AND(B2>=60;C2>80);»Lulus»;»Tidak lulus»)

Fleksibilitas fungsi logika memungkinkan Anda membuat hierarki keputusan dan aturan tingkat lanjut yang menambahkan kekuatan luar biasa pada lembar kerja Anda.

Fungsi COUNTIF dan COUNTIFS untuk menghitung berdasarkan kondisi

Saat Anda perlu tahu berapa banyak catatan yang memenuhi persyaratan, fungsi COUNTIF adalah sekutu yang sempurna. Sintaksisnya adalah:

=COUNTIF(rentang; kriteria)

Misalnya, untuk menghitung berapa kali nama keluarga "Martínez" muncul dalam daftar:

=COUNTIF(A2:A10; "Martinez")

Anda dapat menggunakan referensi sel alih-alih teks langsung, dan bahkan menerapkan kriteria yang lebih maju, seperti lebih besar dari, kurang dari, sama dengan sel, teks tertentu, dsb.

Jika Anda ingin menghitung sel yang memenuhi beberapa kriteria sekaligus, fungsi yang sesuai adalah HITUNG JIKA DIATUR. Strukturnya adalah:

=COUNTIFS(rentang1; kriteria1; rentang2; kriteria2; …)

Misalnya, untuk mengetahui berapa banyak produk yang "Beige" dan juga memiliki jumlah yang sama dengan 1:

=COUNTIFS(A2:A10; "Krem"; B2:B10; 1)

Fungsi SUMIF dan SUMIFS untuk penjumlahan bersyarat

Ketika pertanyaannya adalah Berapa jumlah nilai yang memenuhi suatu kondisi? Fungsi SUMIF mulai berlaku:

=SUMIF(rentang; kriteria; )

El peringkat adalah tempat filter akan diterapkan, kriteria kondisi yang telah ditetapkan, dan jumlah_rentang (opsional) adalah area yang benar-benar akan ditambahkan. Jika tidak ditentukan, menambahkan rentang pencarian itu sendiri. Misalnya, untuk menjumlahkan penjualan salesman Perez:

=JUMLAH.JIKA(A2:A20; "Perez"; B2:B20)

Jika ada lebih dari satu kondisi, fungsi yang sesuai adalah SUM.IF.SET. Strukturnya adalah:

=SUMIF(jumlah_rentang; rentang_kriteria1; kriteria1; rentang_kriteria2; kriteria2; …)

Dengan cara ini Anda dapat menambahkan, misalnya, semua penjualan di atas 500 euro di bulan Maret:

=SUMIF(B2:B20; C2:C20; ">500"; D2:D20; "Maret")

AVERAGEIF dan AVERAGEIFS: menghitung rata-rata bersyarat

Rumus untuk menghitung rata-rata suatu rentang menurut suatu kondisi Mereka mengikuti logika yang sama:

=AVERAGEIF(rentang; kriteria; )

Misalnya, untuk menghitung gaji rata-rata karyawan dengan nama keluarga "Perez":

=RATA-RATA.JIKA(A2:A100; "Perez"; B2:B100)

Dan jika Anda membutuhkan lebih dari satu kondisi yang harus dipenuhi, gunakan AVERAGEIFS.

Fungsi logika AND, OR, dan NOT dengan format bersyarat

Selain digunakan untuk melakukan perhitungan atau klasifikasi, fungsi logika AND, OR dan NOT juga banyak digunakan untuk memformat sel secara otomatis secara kondisional. Anda dapat mempelajari lebih lanjut tentang cara menerapkan trik ini dalam tutorial kami di Tips dan trik untuk mendapatkan hasil maksimal dari kalkulator Windows.

Dari tab inisiasi - Format bersyarat - Aturan baru, Anda dapat memilih “Gunakan rumus untuk menentukan sel mana yang akan diformat” dan menulis rumus seperti ini:

• =DAN(A2>10;B2<5)
• =ATAU(A2=»Ya»;B2=»Tidak»)
• =TIDAK(A2>50)

Hal ini memungkinkan, misalnya, untuk secara otomatis menyorot sel merah yang memenuhi beberapa kondisi sekaligus. Ini adalah cara terbaik untuk mendeteksi data penting dengan mudah.

Masalah umum dan cara mengatasinya

Apa yang harus saya lakukan jika fungsi saya mengembalikan 0 atau kesalahan? Dalam banyak kasus, kesalahan disebabkan oleh referensi yang salah, tanda kurung yang tidak ditutup dengan benar, atau argumen yang terlewat. Jika Anda melihat angka nol yang tidak diharapkan, periksa apakah tidak ada satu pun dari tiga argumen dalam SI yang hilang. Jika sebuah muncul #NAMA?, biasanya menunjukkan kesalahan pengetikan atau penggunaan koma/titik koma yang salah, tergantung pada pengaturan regional.

Untuk menghindari hal ini, selalu tulis fungsi menggunakan panduan fungsi. rumus excel dan meninjau argumen satu per satu.