MENGUNGKAP RAHASIA LOGICAL TEST EXCEL

Well, judulnya memang saya buat bombastis, karena kali ini saya ingin menuliskan hal yang tidak pernah saya jadikan materi ajar di ruang kelas. Bukannya tidak mau apalagi pelit, soalnya para peserta diklat tidak pernah menanyakan masalah ini.. hehe.

Logical test biasanya kita gunakan saat kita memanfaatkan fungsi IF() dalam menyelesaikan suatu perhitungan. Syntax dari fungsi IF() adalah =IF(logical_test,[value_if_TRUE),[value_if_FALSE]).

Sederhananya logical test kita gunakan untuk membandingkan satu variable dengan satu variable lainnya dengan operator: <, =, >, <=, >=, <>. Logical test dapat kita kombinasikan dengan fungsi AND() dan/atauOR() jika kita harus membandingkan lebih dari satu variable.

Adapun keluaran (output) dari logical test ini adalah pernyataan TRUE atau FALSE. Contoh, jika A=9 dan B=19, maka logical test A>B akan menghasilkan keluaran FALSE karena A kurang dari B, sedangkan logical test A<=B akan menghasilkan keluaran TRUE, karena A memang kurang dari atau sama dengan B.

Lalu rahasianya di mana? Jika anda ketikkan kata “true” di Excel atau anda gunakan logical test dengan keluaran TRUE, maka secara otomatis Excel akan mengubahnya menjadi (“TRUE”) dengan justifikasi “CENTER”. Pertanyaannya adalah berapakah nilai angka dari TRUE dan FALSE menurut Excel?

Jawabannya: Excel menganggap TRUE bernilai 1 dan FALSE bernilai 0. Buktinya bisa anda dapatkan dengan mengalikan logical test tersebut dengan angka 1, misalnya, jika sel A3 bernilai 28 dan sel A4 bernilai 16, maka =(A3>A4)*1 akan menghasilkan nilai 1, perhatikan bahwa dalam situasi ini =A3>A4 akan menghasilkan nilai TRUE. Sebaliknya jika formulanya kita ubah menjadi =(A3<A4)*1, maka keluarannya adalah 0 (Nol), perhatikan bahwa dalam situasi ini =A3<A4 akan menghasilkan nilai FALSE, jadi TRUE = 1, sedangkan FALSE = 0.

Jika tadi nilai TRUE kita kalikan 1, sekarang mari kita coba menambahkannya dengan angka 1 dengan formula =(A3>A4)+1 dan ternyata keluarannya adalah 2. Kesimpulannya? Keluaran logical test ini dapat kita kalikan dan/atau tambahkan dengan angka (number).

Lalu bagaimana keluaran dari =(A3>A4) + (A3>A4), well… hasilnya adalah 2, dengan demikian, kesimpulan berikutnya adalah TRUE + TRUE = 2, TRUE + FALSE = 1, FALSE + FALSE = 0, TRUE * TRUE = 1, TRUE * FALSE = 0, dan FALSE * FALSE = 0. Menarik????

Lalu apa manfaatnya?
Perhatikan gambar berikut:

Bagaimana Anda merumuskan masalah ini: Seseorang dinyatakan tidak lulus apabila memiliki nilai “D” lebih dari satu, apakah anda akan menggunakan fungsi OR() atau AND() untuk menyelesaikannya? Bingung?? Cara termudah menyelesaikan masalah ini adalah menggunakan kolom bantuan seperti gambar di bawah ini:


Sesuai ilustrasi di atas, jika kolom jumlah (sel L2) bernilai lebih dari 1, maka ybs. tidak lulus. Formula yang digunakan di sel G2 adalah =IF(A2=”D”,1,0) yang kemudian kita copy ke seluruh baris hingga kolom E.
Sekarang mari kita manfaatkan pemahaman baru kita tentang nilai TRUE dan FALSE. Dalam kasus ini, kita menggunakan logical test yang sama dengan teknik sebelumnya, yaitu kondisi TRUE jika sebuah sel bernilai “D”. Sesuai persyaratan kelulusan, jika hasil seluruh logical test kita jumlahkan, maka seseorang boleh lulus jika total hasil penjumlahan tersebut tidak boleh lebih dari 1. Setuju?
Adapun formulanya adalah:
=(A2=”D”)+(B2=”D”)+(C2=”D”)+(D2=”D”)+(E2=”D”) dengan keluaran = 2 atau ybs. tidak lulus.

Jika kita kombinasikan dengan fungsi IF untuk pengambilan keputusan, maka formulanya menjadi:
=IF((A2=”D”)+(B2=”D”)+(C2=”D”)+(D2=”D”)+(E2=”D”)>1,”TIDAK LULUS”,”LULUS”)
Simple bukan?

Selanjutnya, jika nilai MP1 ATAU MP5 bernilai “D”, maka yang bersangkutan tidak lulus.
Biasanya anda akan memformulasikannya =IF(OR(A2=”D”,E2=”D”),”TIDAK LULUS”,”LULUS”)
Dengan teknik yang baru formulanya adalah =IF((A2=”D”)+(E2=”D”)>0,”TIDAK LULUS”,”LULUS”)

Berikutnya, jika nilai MP1 DAN MP5 bernilai “D”, maka yang bersangkutan tidak lulus.
Biasanya anda akan memformulasikannya =IF(AND(A2=”D”,E2=”D”),”TIDAK LULUS”,”LULUS”)
Dengan teknik yang baru formulanya adalah =IF((A2=”D”)+(E2=”D”)=2,”TIDAK LULUS”,”LULUS”)

Sampai titik ini, anda dapat mencermati bahwa dengan teknik yang baru, KITA SEBENARNYA SUDAH TIDAK TERLALU MEMBUTUHKAN FUNGSI OR DAN AND LAGI – say goodbye to OR dan AND!!! 

Sekarang formulasikan kasus berikut ini:
Seorang dinyatakan LULUS jika yang bersangkutan berhasil mendapatkan SATU nilai “D”, tidak boleh lebih dan tidak boleh kurang. Kondisi ini disebut Exclusive OR atau XOR. Sampai dengan versi 2010, Excel belum menyediakan fungsi XOR, sementara di versi 2013 katanya fungsi ini sudah tersedia. Tapi siapa yang butuh? (sombong? Enggak! Justru galau karena belum punya versi 2013 hehehe) Cekidot!!!
Jika yang boleh lulus hanya yang berhasil mendapat nilai “D” sebanyak satu buah, maka formula berikut harus menghasilkan nilai 1: =(A2=”D”)+(B2=”D”)+(C2=”D”)+(D2=”D”)+(E2=”D”)
Atau selengkapnya:
=IF((A2=”D”)+(B2=”D”)+(C2=”D”)+(D2=”D”)+(E2=”D”)=1,”LULUS”,”TIDAK LULUS”)

Terakhir, seseorang HANYA BOLEH LULUS jika MAKSIMAL memperoleh nilai “D” sebanyak satu buah.
Kata para pakar, kondisi ini disebut NAND (negatif).
Langsung saja, formulanya adalah:
=IF((A2=”D”)+(B2=”D”)+(C2=”D”)+(D2=”D”)+(E2=”D”)<=1,”LULUS”,”TIDAK LULUS”)
Oke komplit sudah seluruh kondisi sudah kita cover: AND, OR, XOR, dan NAND

Sumber

SHARE THIS