Logika: IF, IFS, AND, OR, XOR, dan IFERROR · Excel

Ajukan pertanyaan ya/tidak ke Excel, buat rumus memilih satu jawaban atau jawaban lain dengan IF, tangani beberapa kemungkinan dengan IFS, gabungkan syarat dengan AND, OR, XOR, dan NOT, lalu ganti error yang sudah diperkirakan dengan sesuatu yang lebih enak dibaca lewat IFERROR.

Idenya

Di modul-modul sebelumnya, setiap rumus melakukan perhitungan yang sama di setiap baris. Kalau kamu menulis =B2*5% lalu menyalinnya ke bawah, Ayu mendapat 5% dari penjualannya, Budi mendapat 5% dari penjualannya, begitu juga semua orang lain. Untuk hitungan sederhana itu sudah cukup, tapi banyak keputusan sehari-hari di kantor tidak sesederhana itu.

Coba pikirkan bagaimana bonus penjualan biasanya bekerja. Perusahaan tidak memberikannya ke semua orang, hanya ke orang yang benar-benar mencapai targetnya bulan itu. Jadi harus ada yang mengecek daftar satu per satu, membandingkan penjualan tiap orang dengan targetnya, lalu memutuskan ya atau tidak. Atau bayangkan laporan yang menunjukkan seberapa besar pertumbuhan tiap orang dibanding bulan lalu. Orang yang baru bergabung bulan ini tidak punya "bulan lalu" untuk dibandingkan, dan daripada menampilkan angka yang rusak, kamu pasti lebih suka sheet-nya cukup menulis bahwa dia orang baru.

Fungsi logika di Excel melakukan pengecekan itu untukmu. Kamu cukup menulis aturannya sekali, lalu rumusnya memeriksa setiap baris sendiri-sendiri dan memberi jawaban yang cocok untuk baris itu.

Contoh di modul ini memakai sheet kecil milik sebuah tim penjualan. Setiap baris adalah satu orang, berisi penjualan bulan ini, targetnya, berapa tahun dia sudah bekerja, berapa hari dia datang terlambat, dan penjualannya bulan lalu:

	A	B	C	D	E	F
1	Staff	Sales	Target	Years	Late days	Last month
2	Ayu	4200000	4000000	3	0	4000000
5	Dewi	2900000	3000000	2	0

Mengajukan pertanyaan ya/tidak

Setiap keputusan berawal dari pertanyaan yang jawabannya ya atau tidak, misalnya "apakah Ayu mencapai targetnya?" Di Excel, pertanyaan seperti itu kamu ajukan lewat perbandingan: dua nilai diletakkan di kiri dan kanan sebuah tanda seperti > atau =, lalu Excel memberi tahu apakah pernyataan itu benar. Jawabannya selalu TRUE (ya) atau FALSE (tidak).

Operator	Artinya
`=`	sama dengan
`<>`	tidak sama dengan
`>` / `<`	lebih besar / lebih kecil dari
`>=` / `<=`	lebih besar / lebih kecil dari, atau sama dengan

# Apakah Ayu mencapai targetnya?
H2: =B2>=C2
# Apakah Budi mencapai targetnya?
H3: =B3>=C3
# Apakah nama di A4 adalah Citra?
H4: =A4="Citra"

H2: TRUE
H3: FALSE
H4: TRUE

Ayu menjual 4200000 dan targetnya 4000000, jadi jawaban untuk "apakah dia mencapainya?" adalah TRUE. Budi menjual 3150000 padahal targetnya 3500000, jadi jawabannya FALSE. Perbandingan juga bisa dipakai untuk teks, dan begitulah cara H4 mengecek namanya. Satu hal yang sering bikin kaget: Excel tidak membedakan huruf besar dan kecil saat membandingkan teks, jadi ="citra" juga menghasilkan TRUE.

IF: ini atau itu

TRUE dan FALSE sudah cukup untuk Excel, tapi kalau kamu menyerahkan satu kolom penuh TRUE dan FALSE ke atasanmu, dia harus berhenti dulu dan menebak apa artinya masing-masing. IF mengubah jawaban ya/tidak itu menjadi sesuatu yang memang ingin dilihat orang, seperti label atau jumlah uang. Kamu memberinya tiga hal, dengan urutan ini:

IF(pertanyaan, jawaban jika ya, jawaban jika tidak)

Penjelasan untuk anak 5 tahun

"Kalau sayurnya dihabiskan, kamu dapat es krim. Kalau tidak, tidak ada es krim." Itulah IF. Ada pertanyaannya (sudah dihabiskan belum?), jawaban kalau ya (es krim), dan jawaban kalau tidak (tidak dapat apa-apa).

H1: Result
H2:H6: =IF(B2>=C2, "Hit", "Missed")

H1: Result
H2: Hit
H3: Missed
H4: Hit
H5: Missed
H6: Hit

Excel mengecek pertanyaannya untuk setiap baris secara terpisah. Karena itulah Ayu dan Budi mendapat hasil yang berbeda dari rumus yang sama.

Kedua jawabannya tidak harus berupa kata. Bisa juga angka, atau bahkan perhitungan yang hanya dijalankan saat dibutuhkan. Kembali ke contoh bonus, misalkan aturannya begini: siapa pun yang mencapai target mendapat 5% dari penjualannya, dan yang tidak mencapainya tidak mendapat apa-apa:

H1: Bonus
H2:H6: =IF(B2>=C2, B2*5%, 0)

H1: Bonus
H2: 210000
H3: 0
H4: 255000
H5: 0
H6: 190000

Tips

Kata ditulis di dalam tanda kutip ganda, seperti "Hit". Angka tidak. Kalau kamu menulis "0" dengan tanda kutip, Excel menganggapnya teks yang kebetulan terlihat seperti nol, dan SUM akan melewatinya.

IFS: lebih dari dua pilihan

IF hanya punya tempat untuk dua jawaban, ya atau tidak. Kadang kamu butuh lebih dari itu. Misalkan ketua tim ingin memberi setiap orang salah satu dari tiga peringkat: Star untuk siapa pun yang menjual 5000000 atau lebih, Good untuk yang setidaknya mencapai target, dan Keep going untuk sisanya. Itu tiga kemungkinan hasil, dan IFS bisa menanganinya. Kamu memberinya pasangan pertanyaan dan jawaban, sebanyak yang kamu perlukan:

IFS(pertanyaan1, jawaban1, pertanyaan2, jawaban2, ...)

Excel memeriksa pertanyaannya dari kiri ke kanan dan berhenti di pertanyaan pertama yang TRUE, lalu memberikan jawaban di sebelahnya. Sisanya tidak dilihat sama sekali. Karena itu urutan penulisannya penting, dan dua kebiasaan ini akan menyelamatkanmu dari masalah:

Taruh yang paling sulit dicapai di depan. Citra menjual 5100000, yang juga sudah di atas targetnya. Kalau pertanyaan "mencapai target" ditaruh lebih dulu, Excel akan berhenti di situ dan memberinya Good, dan tidak akan pernah sempat menanyakan apakah dia layak jadi Star.
Akhiri dengan TRUE, "sesuatu". TRUE saja selalu benar, jadi pasangan terakhir ini menangkap siapa pun yang tidak cocok dengan pertanyaan-pertanyaan sebelumnya. Tanpanya, orang-orang itu akan mendapat error #N/A.

H1: Rating
H2:H6: =IFS(B2>=5000000, "Star", B2>=C2, "Good", TRUE, "Keep going")

H1: Rating
H2: Good
H3: Keep going
H4: Star
H5: Keep going
H6: Good

Di spreadsheet lama kamu akan sering melihat hal yang sama ditulis sebagai IF di dalam IF: =IF(B2>=5000000, "Star", IF(B2>=C2, "Good", "Keep going")). Cara kerjanya sama, hanya lebih melelahkan untuk dibaca.

AND, OR, XOR, NOT

Sejauh ini setiap IF hanya mengecek satu hal. Aturan di dunia nyata sering mengecek lebih dari satu. Mungkin perusahaan memutuskan bonus hanya untuk orang yang mencapai target dan sama sekali tidak pernah terlambat bulan ini. Itu dua pertanyaan, padahal IF hanya menerima satu. Keempat fungsi ini menyelesaikannya dengan menggabungkan beberapa pertanyaan menjadi satu TRUE atau FALSE, yang kemudian bisa kamu berikan ke IF:

Fungsi	TRUE jika
`AND(a, b, ...)`	semua pertanyaan TRUE
`OR(a, b, ...)`	setidaknya satu TRUE
`XOR(a, b)`	tepat satu dari keduanya TRUE
`NOT(a)`	`a` bernilai FALSE (membalik jawabannya)

Bayangkan seperti ini

Untuk masuk bioskop kamu butuh tiket DAN harus cukup umur. Dua-duanya, tanpa pengecualian. Untuk membayar di toko kamu bisa pakai uang tunai ATAU kartu. Salah satu saja sudah cukup.

# Apakah Ayu mencapai target dan tidak pernah terlambat?
I2: =AND(B2>=C2, E2=0)
# Apakah Budi sudah bekerja 4+ tahun atau mencapai targetnya?
I3: =OR(D3>=4, B3>=C3)
# Apakah tepat satu dari dua syarat Citra yang benar?
I4: =XOR(B4>=C4, E4=0)
H2:H6: =IF(AND(B2>=C2, E2=0), "Bonus", "-")

I2: TRUE
I3: FALSE
I4: TRUE
H2: Bonus
H3: -
H4: -
H5: -
H6: -

Lihat baris terakhir: hanya Ayu yang mendapat bonus. Citra dan Eko sama-sama mencapai target, tapi masing-masing pernah terlambat setidaknya sekali, jadi AND menjawab FALSE untuk mereka.

XOR milik Citra bernilai TRUE karena alasan yang sama. Dia mencapai targetnya (ya) tapi tidak bebas terlambat (tidak), jadi tepat satu dari dua pertanyaan itu yang benar.

Dalam praktiknya kamu akan sering sekali memakai AND dan OR, hampir selalu di dalam IF seperti baris terakhir tadi. XOR dan NOT jauh lebih jarang muncul.

(Sebenarnya, XOR dengan lebih dari dua pertanyaan bernilai TRUE jika jumlah pertanyaan yang TRUE ganjil. Kamu akan jarang membutuhkannya.)

IFERROR: jawaban cadangan

Untuk melihat seberapa besar pertumbuhan tiap orang dibanding bulan lalu, kamu membagi penjualan bulan ini dengan bulan lalu lalu dikurangi 1: =B2/F2-1. Untuk Ayu hasilnya 4200000 / 4000000 - 1, yaitu 0.05, atau tumbuh 5%. Masalahnya ada di Dewi. Dia baru bergabung bulan ini, jadi sel bulan lalunya (F5) kosong. Di sini Excel menganggap sel kosong sebagai 0, dan karena tidak ada yang bisa dibagi nol, barisnya menampilkan #DIV/0!.

Error itu bukan kesalahan di rumusmu. Error itu memang sudah bisa diduga, karena orang baru memang belum punya data bulan lalu. IFERROR(rumus, cadangan) dibuat persis untuk situasi seperti ini: Excel mencoba menjalankan rumusnya, dan kalau hasilnya error, yang ditampilkan adalah cadangannya.

H2:H6: =IFERROR(B2/F2-1, "New")

H2: 0.05
H3: 0.05
H4: 0.02
H5: New
H6: -0.02564102564

Awas

IFERROR menyembunyikan semua error, termasuk salah ketikmu sendiri. Kalau kamu salah menulis nama fungsi, yang terlihat hanya "New" dan kamu tidak akan pernah tahu ada yang salah. Jadi pakai IFERROR hanya kalau kamu tahu error apa yang akan muncul dan kenapa. Pilih juga cadangan yang jujur, seperti "New" atau "n/a". Angka 0 palsu diam-diam akan merusak rata-ratamu.

Rangkuman

Perbandingan (=, <>, <, >, <=, >=) mengajukan pertanyaan ya/tidak. Excel menjawab TRUE atau FALSE.
IF(pertanyaan, jika ya, jika tidak) memilih di antara dua jawaban. IFS memilih dari beberapa dan berhenti di TRUE yang pertama.
AND butuh semuanya, OR cukup satu, XOR butuh tepat satu, NOT membalik jawabannya.
Kata di dalam rumus diberi tanda kutip. Angka tidak.
IFERROR(rumus, cadangan) mengganti error yang sudah kamu duga dengan sesuatu yang enak dibaca.