Mengungkap Rahasia LOGICAL TEST dan ARRAY OPERATIONS pada Microsoft Excel

Sebagaimana kita ketahui bahwa aplikasi Microsoft Excel adalah salah satu aplikasi pengolah spreadsheet, yang sangat umum digunakan untuk mengolah data pada tabel. Berbagai fungsi dan formula sudah disediakan di aplikasi tersebut untuk para pengguna Microsoft Excel

untuk berbagai tujuan dalam pengolahan data tersebut. Namun jika kita mau mengoptimalkan fungsi dan formula tersebut, ternyata dapat menghasilkan sesuatu yang lebih efektif dan efisien dalam penggunaan formula untuk menghasilkan berbagai output yang kita inginkan. Pada tulisan kali ini, akan diungkap cara cerdas menggunakan dan mengoptimalkan fungsi Logical Test di Excel.

Logical Test

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 variabel dengan satu variabel lainnya dengan operator: <, =, >, <=, >=, <>. Selanjutnya, logical test juga dapat kita kombinasikan dengan fungsi AND() dan/atau OR() apabila kita ingin membandingkan lebih dari satu variabel.

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 kita ketikkan kata “true” di Excel atau apabila kita menggunakan logical test dengan keluaran TRUE, maka secara otomatis Excel akan mengubahnya menjadi “TRUE” dengan huruf kapital dan justifikasi “CENTER”.

Pertanyaan utamanya adalah berapakah nilai angka dari TRUE dan FALSE menurut Excel?

Jawabannya: Excel menganggap TRUE bernilai 1 dan FALSE bernilai 0.

Bukti dapat kita peroleh dengan mengalikan keluaran 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 bagi kita?Perhatikan gambar Contoh 1.

Contoh. 1.

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? Cara termudah menyelesaikan masalah ini adalah menggunakan kolom bantuan seperti gambar Contoh. 2.

                                                                                                Contoh. 2.

                                                               

Sesuai ilustrasi pada gambar Contoh. 2, jika kolom jumlah (sel L2) bernilai lebih dari 1, maka siswa tersebut 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 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:

Seseorang hanya 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.

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”)

Array Operations

Sebelum kita mendefinisikan Array Operations ini, mari kita cermati gambar Contoh. 3.

Contoh. 3.

Formula yang kita gunakan di sel C2 adalah =A2*B2 yang merupakan hasil perkalian isi Kolom A dan Kolom B, sementara sel C7 berisi formula =SUM(C2:C6) yang menampilkan nilai total dari hasil perkalian di Kolom C.  Pertanyaan kita adalah apakah angka 130 tersebut dapat kita hasilkan melalui satu formula tunggal saja, sehingga kita tidak membutuhkan kolom bantuan (Kolom C)?

Jawabnya, bisa! Mari kita gunakan formula berikut : =SUM((A2:A6)*(B2:B6)). Berbeda dari teknik biasa, formula ini harus dieksekusi menggunakan kombinasi tombol CTRL + SHIFT + ENTER, jika tidak hasilnya pasti tidak sesuai. Teknik inilah yang disebut sebagai array operations, dimana kita dapat menggabungkan beberapa perhitungan menggunakan formula yang serupa dalam satu formula tunggal dengan fungsi SUM() sebagai akumulatornya.

Teknik di atas sebenarnya juga dapat diselesaikan menggunakan fungsi SUMPRODUCT() dengan formula yang mirip, yaitu =SUMPRODUCT(A2:A6,B2:B6), namun kenapa harus menggunakan banyak fungsi?

Contoh selanjutnya adalah kasus yang kita gunakan pada topik logical test di atas.

Contoh. 4

Kasus I: Seseorang dinyatakan tidak lulus apabila memiliki nilai “D” lebih dari satu

Formula awal adalah: =IF((A2=”D”)+(B2=”D”)+(C2=”D”)+(D2=”D”)+(E2=”D”)>1,”TIDAK LULUS”,”LULUS”)

Sedangkan jika kita menggunakan teknik array operations, maka formulanya dapat kita persingkat menjadi:

=IF(SUM((A2:E2="D")*1)>1,"TIDAK LULUS","LULUS"), dengan output “TIDAK LULUS” karena terdapat nilai D sebanyak dua buah.

Kasus II: Seseorang hanya dinyatakan LULUS jika yang bersangkutan berhasil mendapatkan SATU nilai “D”, tidak boleh lebih dan tidak boleh kurang.

Formula awal adalah: =IF((A2=”D”)+(B2=”D”)+(C2=”D”)+(D2=”D”)+(E2=”D”)=1,”LULUS”,”TIDAK LULUS”)

Sedangkan jika kita menggunakan teknik array operations, maka formulanya dapat kita persingkat menjadi:

=IF(SUM((A2:E2="D")*1)=1," LULUS","TIDAK LULUS"), dengan output “TIDAK LULUS” karena terdapat nilai D sebanyak dua buah.

Kasus III: seseorang HANYA BOLEH LULUS jika MAKSIMAL memperoleh nilai “D” sebanyak satu buah

Formula awal adalah:

=IF((A2=”D”)+(B2=”D”)+(C2=”D”)+(D2=”D”)+(E2=”D”)<=1,”LULUS”,”TIDAK LULUS”)

Sedangkan jika kita menggunakan teknik array operations, maka formulanya dapat kita persingkat menjadi:

=IF(SUM((A2:E2="D")*1)<=1," LULUS","TIDAK LULUS"), dengan output “TIDAK LULUS” karena terdapat nilai D sebanyak dua buah.

Alternatif dari teknik yang kita gunakan disini adalah menggunakan fungsi COUNTIF() dengan formula Kasus I: =IF(COUNTIF(A2:E2,"D")>1,"TIDAK LULUS","LULUS").

Kasus II: =IF(COUNTIF(A2:E2,"D")=1," LULUS","TIDAK LULUS")

Kasus III: =IF(COUNTIF(A2:E2,"D")<=1," LULUS","TIDAK LULUS")

Dengan tingkat pemahaman yang lebih baik terhadap logical test dan array operations, pada dasarnya kita tidak terlalu perlu untuk menguasai banyak fungsi yang disediakan Excel agar dapat bekerja dengan cepat dan efektif. Gabungan keduanya ditambah fungsi SUM() sebagai akumulator dapat digunakan untuk menggantikan penggunaan fungsi SUMPRODUCT(), SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS().

Demikian semoga bermanfaat bagi kita semua, selamat mencoba!