Menggunakan Fungsi IF

Diantara fungsi logika excel, Fungsi IF merupakan salah satu fungsi microsoft excel yang sering kita gunakan. Hal ini kita butuhkan untuk memenuhi sebuah kondisi syarat tertentu.

Misalnya dalam kasus nilai ujian, seorang anak dinyatakan lulus jika beberapa mapel memenuhi kriteria nilai tertentu.

Untuk lebih memahami bagaimana menggunakan funsgsi-fungsi excel tersebut ke dalam rumus excel, kali ini kita pelajari bersama cara menggunakan rumus gabungan tersebut. Namun, sebelum itu ada baiknya anda membaca tutorial atau cara menggunakan fungsi-fungsi tersebut pada artikel belajar excel sebelumnya. 

MENGGUNAKAN RUMUS IF 

Fungsi IF digunakan untuk menjalankan sebuah rumus excel tertentu jika kondisi atau syarat tertentu terpenuhi dan menjalankan sebuah rumus excel lainnya jika kondisinya tidak terpenuhi.

Sintak atau cara penulisan fungsi IF adalah :

Rumus Excel :   =IF(logical_tes;[value_if_true];[value_if_false])

Agar lebih mudah difahami kita langsung ke contoh soal saja.

Ada data siswa, dari hasil tes ulangan yang hanya lulus yang mempunyai nilai diatas 60. Tentukan siapa saja yang akan lulus

Data siswanya sebagai berikut :

Dari data diatas siswa yang dinyatakan LULUS / TIDAK LULUS ada di KOLOM C Berisi Nilai 60

Maka masukkan rumus excel berikut di cell D2

=IF(C2>=60;"Lulus";"Tidak Lulus")

Copy-Paste atau tarik kebawah menggunakan fitur autofill rumus pada sel D2 diatas sampai pada sel D11. Hasil akhir pada masing-masing sel pada kolom D adalah sebagai berikut:

Dari data diatas maka kita ketahui bahwa siswa yang TIDAK LULUS adalah 5, 7 dan 10

Dari penjelasan diatas semoga sudah tidak ada lagi pertanyaan mengenai rumus ini.

SUMPRODUCT EXCEL : Mengalikan dan Menjumlahkan Baris Data Yang Bersesuain Pada microsoft Excel

SUMPRODUCT merupakan salah satu fungsi pada Microsoft Excel yang digunakan dalam rumus Excel untuk mendapatkan jumlah dari hasil perkalian antara baris-baris yang bersesuaian pada beberapa Array atau sekumpulan data.

SINTAKS

SUMPRODUCT(array1, [array2], [array3], ...)

Penjelasan sintaks fungsi SUMPRODUCT:

• array1 : (Diperlukan) Kumpulan data atau Array pertama yang ingin kita kalikan dan jumlahkan.
• [array2], [array3], ... : (Opsional) Kumpulan data atau Array lainnya yang ingin dapatkan total penjumlahannya setalah dikalikan dengan Array pertama.

CATATAN:

1. Dimensi Array (Tinggi dan lebar) pada argumen-argumen fungsi Sumproduct ini harus sama
2. Data pada array yang tidak berupa angka (numeric) akan dianggap sebagai nol (0)

CONTOH

Perhatikan contoh penggunaan fungsi sumproduct berikut dalam rumus microsoft Excel.

=SUMPRODUCT(B2:B6;C2:C6)

Pada rumus excel tersebut nilai pada masing-maisng sel B2:B6 akan dikalikan dengan nilai pada masing-masing sel pada C2:C6 kemudian masing-masing nilai perkalian tersebut akan dijumlahkan.

=(5*1)+(4*2)+(3*3)+(2*4)+(1*5)

Hasilnya adalah 5+8+9+8+5 = 35

=SUMPRODUCT(B2:C6)

Untuk rumus kedua diatas lebar array pertama adalah 2 kolom dengan tinggi 5 baris (C2:B6). Sedangkan argumen Array ke-dua tidak diisi sehingga hasilnya hanya menjumlahkan nilai sel yang ada pada B2:B6 yakni 5+4+3+2+1+1+2+3+4+5 = 30

=SUMPRODUCT(B2:C6;D2:E6)

Pada contoh ketiga, array pada masing-masing argument memiliki dimensi 2 kolom x 5 baris. Masing-masing nilai pada array ini akan dikalikan dengan baris nilai yang sesuai sehingga didapat:

(5*10)+(4*20)+(3*30)+(2*40)+(1*50)+(15*150)+
(1*100)+(2*200)+(3*100)+(4*200)+(5*100)+(15*700)

Hasilnya adalah 2450.

=SUMPRODUCT(B2:B6;C2:D6)

Untuk rumus excel ketiga diatas argumen array pada fungsi sumproduct tidak memiliki dimensi yang sama. Dimensi Array pertama  1 kolom dan 5 baris, sedangkan array kedua memiliki dimensi 2 kolom x 5 baris sehingga menghasilkan nilai error #VALUE.

Masih kurang jelas? Silahkan disampaikan dikolom komentar.

Cara Menemukan Data Ganda (Duplikat) Pada Excel

Conditional formatting merupakan salah satu fitur microsoft excel yang digunakan untuk melakukan format sel sesuai nilai dari sel tertentu yang dimaksud. Dengan fitur ini kita akan lebih mudah menemukan data duplikat, data ganda atau data yang sama pada sebuah rentang data tertentu. Begitu pula sebaliknya, yakni menemukan data unik dari sekumpulan data yang kita miliki.

ebelum kita lanjut barangkali anda juga ingin membaca:

1. Cara Menandai Sel yang Berisi Teks Tertentu Pada Excel
2. Cara Menandai Sel yang Berisi Angka Tertentu Pada Excel

Untuk menemukan data ganda atau data yang memiliki duplikat lakukan langkah-langkah berikut :

CARA 1 MENEMUKAN DUPLIKAT DATA EXCEL

1. Seleksi rentang data yang kita ingin cari data duplikatnya.
   
   
   
2. Pilih menu : Home -- Conditional Formatting -- Highlights Cells Rules -- Duplicates Values
   
   
   
3. Dari Option Box yang muncul tentukan format sel yang dikehendaki.
   
   
   
4. Klik OK selesai.

Hasil yang kita peroleh dari langkah-langkah diatas adalah seperti gambar berikut:

Jika format sel yang anda kehendaki tidak ada pada pilihan diatas lakukan cara kedua berikut.

CARA 2 MENEMUKAN DUPLIKAT DATA EXCEL

1. Seleksi rentang data yang kita ingin cari data duplikatnya.
   
   
   
2. Pilih menu : Home -- Conditional Formatting -- New Rule
   
   
   
3. Dari Option Box yang muncul pilih "Format only unique or duplicate value"
4. Pada bagian "Edit the rule description" pilih Duplicate dan tentukan format yang anda kehendaki. Misal sel akan kita fill warna merah.
   
   
   
5. Klik OK selesai.

Hasil yang akan kita dapat sebagai berikut

Untuk menemukan data Unik atau data yang tidak ganda anda hanya perlu memilih unique pada langkah-langkah diatas.

Cara Menandai/Memberi Warna Sel yang Berisi Teks Tertentu Pada Microsoft Excel

Sebelumnya kita sudah belajar Excel tentang cara menandai sel yang berisi angka tertentu dengan fitur Conditional Formatting. Sekarang kita akan belajar tentang cara menandai atau memberi warna pada sel Excel yang berisi teks sesuai kriteria tertentu.

Untuk kriteria teks, microsoft excel menyediakan 4 (Empat) model kriteria yang bisa kita terapkan.

1. Containing : Menandai sel Excel yang berisi/mengandung teks tertentu.
2. Not Containing : Menandai sel Excel yang tidak berisi/mengandung tekstertentu.
3. Beginning With : Menandai sel Excel yang diawali dengan teks tertentu.
4. Ending With : Menandai sel Excel yang diakhiri dengan teks tertentu.

Sebagai contoh kita akan menggunakan tabel data berikut:

Dari tabel data diatas kita ingin menandai sel yang berisi teks "Mengulang" dengan warna fill merah. Langkah-Langkah yang kita perlukan untuk menandai atau mewarnai sel dengan salah satu dari empat kriteria diatas adalah :

1. Seleksi Range data yang akan kita tandai (H2:H11)
2. Pilih menu Home -- Conditional Formatting -- New Rule
3. Pilih Format only cells that contain.
4. Pada opsi Rule Description pilih Spesific Text -- Containing
5. Masukkan teks "Mengulang" pada kotak isian sebelahnya.
6. Atur Format fill merah atau sesuai yg kita kehendaki.
7. OK dan selesai. Lihat hasilnya.

Untuk lebih jelaskan perhatikan gambar-gambar berikut:

Dengan menerapkan conditional formatting diatas hasil yang kita dapat akan seperti ini:

Sekarang kita coba dengan menerapkan rule Not Containing "Tanpa".

Hasil yang akan kita dapatkan dengan menerapkan conditional formatting diatas adalah sebagai berikut :

Untuk kriteria lain bisa anda coba sendiri. misalnya memberikan rule Not Containing "Mengulang", Beginning with "Lulus", Containing "Dengan", dll.

Saya kira Cara menandai atau memberi warna sel excel yang sesuai kriteria teks tertentu ini sangat mudah. jadi tidak perlu saya perpanjang lagi.

Masih kurang jelas? silahkan disampaikan pada kolom komentar.

Cara Menandai Sel di Microsoft Excel Berdasarkan Nilai Tertentu Secara Otomatis

Bagaimanakah cara memberi warna pada angka tertentu secara otomatis pada excel? Untuk memudahkan analisa data pada microsoft Excel, adakalanya kita perlu menandai atau merubah format sel Excel. Misalnya merubah warna sel Excel. Format tersebut tentunya akan kita terapkan pada nilai sel yang memenuhi kriteria tertentu yang kita kehendaki.

Untuk kebutuhan di atas excel sudah menyediakan fitur Conditional Formatting. Yakni, fitur yang digunakan untuk melakukan format sel atau range sesuai kriteria tertentu atau kondisi yang kita inginkan. Tentang fitur ini sudah sedikit saya tulis pada tutorial yang lalu. Jika anda belum membacanya silahkan baca pada tautan berikut:

• Menandai Sel, Range atau Tabel dengan Conditional Formatting Pada Microsoft Excel

Pada tutorial kali ini kita akan belajar Excel dalam menggunakan fitur conditional formatting pada microsoft excel untuk melakukan format sel atau merubah warna sel sesuai dengan nilai angka tertentu.

Dengan menggunakan fitur Conditional formatting kita dapat menandai sel tertentu secara otomatis berdasarkan kriteria tertentu. Salah satu kriteria tersebut adalah berdasarkan nilai atau value dari sel. Value atau isi sel yang saya maksud disini adalah berupa nilai angka (numeric).

Setidaknya ada 8 (Delapan) kriteria yang bisa kita gunakan dalam kasus ini, yakni:

1. Between : Menandai sel yang memiliki value atau nilai DIANTARA dua angka tertentu.
2. Not Between :Menandai sel yang memiliki value atau nilai TIDAK diantara dua angka tertentu.
3. Equal To : Menandai sel yang memiliki value atau nilai SAMA DENGAN angka tertentu.
4. Not Equal To : Menandai sel yang memiliki value atau nilai TIDAK SAMA DENGAN angka tertentu.
5. Greater Than : Menandai sel yang memiliki value atau nilai LEBIH DARI angka tertentu.
6. Less Than : Menandai sel yang memiliki value atau nilai KURANG DARI angka tertentu.
7. Greater Than or Equal To : Menandai sel yang memiliki value atau nilai LEBIH DARI atau SAMA DENGAN angka tertentu.
8. Less Than or Equal To ; Menandai sel yang memiliki value atau nilai KURANG DARI atau SAMA DENGAN angka tertentu.

Sebagai contoh kita akan menandai sel-sel excel pada data berikut yang berisi nilai diantara angka 10.000 dan 20.000.

Langkah-langkah yang perlu kita lakukan adalah:

1. Seleksi seluruh sel atau range yang akan kita terapkan conditional formatting didalamnya. Sesuai data diatas range yang kita maskud adalah H2:M7.
2. Pilih menu Home-Condotional formatting-New Rule
3. Pada menu yang muncul pilih : Format only cells that contain
4. Tantukan kondisi format only cells with : Cell Value -- between
5. Pada kotak disebelahnya masukkan rentang angka yang kita kehendaki. dalam contoh ini adalah 10.000 dan 20.000
6. Berikutnya tentukan format cell yang kita kehendaki misalanya warna sel yang memenuhi kriteria tersebut kita rubah warnanya menjadi biru.
7. Kemudian Klik OK dan Apply/OK.
8. Hasil akhirnya seperti pada gambar dibawah ini.

Sekarang silahkan coba rubah nilai-nilai angka yang ada pada range H2:M7 tersebut.

Untuk lebih jelasnya silahkan perhatikan gambar urutan penerapan conditional formatting untuk menendai sel berdasarkan kriteria angka dianatara 10.000 dan 20.000 berikut :

Penerapan conditional formatting ini bisa saling tindih atau saling tumpuk. Misalnya setelah kita menandai sel yang bernilai diantara 10.000 dan 20.000 kita ingin sel yang bernilai 15.000 berwarna merah.

Caranya lakukan langkah-langkah seperti sebelumnya. Hanya saja karena yang ingin kita tandai berikutnya adalah yang bernilai sama dengan 15.000 saja maka pada option conditional formatting kita pilih  Cell Value -- equal to

Hasil akhirnya akan seperti pada gambar dibawah ini.

Untuk kriteria-kriteria yang lain silahkan dicoba sendiri. Jika ada yang perlu ditanyakan silahkan disampaikan pada kolom komentar.

Cara diatas hanya berlaku untuk menandai angka tertentu lalu bagaimana cara untuk menandai teks tertentu? Akan saya bahas pada tutorial rumus excel berikutnya.