Sunday, November 21, 2010

Fungsi SUMIF

Salah satu bentuk lain dari fungsi IF adalah fungsi SUMIF, yaitu suatu fungsi yang digunakan untuk menjumlah beberapa angka jika memenuhi kriteria tertentu yang ditetapkan. Contoh mudahnya dalam kehidupan sehari-hari adalah penjumlahan berat badan anak-anak yang berumur antara 7 - 10 tahun. Atau jumlahkan total komisi dari penjualan di atas Rp 3.000.000,-. Atau jumlahkan total penjualan oleh Amir dalam bulan ini.














Berikut adalah contohnya:
Kita memiliki 3 orang salesman, yaitu Amir, Budi dan Choki yang masing-masing bertugas untuk melakukan penjualan ke warung-warung di seputar kompleks. Hasil penjualan tersebut dikalkukasi setiap minggu untuk mengetahui perolehan penjualan oleh masing-masing salesman.


Fungsi yang dapat kita gunakan adalah:
=SUMIF($R$3:$R$17,T2,$S$3:$S$17)
Jika sel rujukan (R3-R17) sama dengan sel ketentuan (T2:Amir/U2:Budi/V2:Choki), maka Jumlahkan sel tujuan (S3-S17).
Catatan: Sel rujukan dan sel tujuan perlu kita berikan tanda mutlak ($) agar sel tersebut tidak mengalami perubahan ketika fungsi/rumus tersebut kita copy-kan ke kanan/sel lain.

Wednesday, November 17, 2010

Fungsi IF Bertingkat

Setelah kita mengenal penggunaan fungsi IF secara tunggal, sebenarnya fungsi tersebut dapat kita maksimalkan dengan menggunakannya secara bertingkat. Artinya, tidak saja satu fungsi IF yang kita gunakan, tetapi beberapa fungsi IF dalam sekali penggunaan.


Sebagai contoh dalam kahidupan sehari-hari adalah, jika cuaca di kota hujan deras maka kemungkinan lalu lintas akan macet, jika hujan biasa kemungkinan akan padat merayap, jika mendung kemungkinan lalu lintas akan mengalir perlahan, sedangkan jika cuaca cerah maka lalu lintas akan lancar.












Fungsi yang dapat kita gunakan adalah:
=IF(D22=$G$20,$H$20,IF(D22=$G$21,$H$21,IF(D22=$G$22,$H$22,$H$23)))


Sebagai contoh kedua, misalnya dalam pengelompokan Nilai Hasil Ujian. Setiap nilai mahasiswa pada ujian mata kuliah akan dicantumkan dalam kelompok Nilai Huruf, yaitu nilai A, B, C, D dan E. Ketentuan pengelompokan huruf nilai ujian tersebut adalah sebagaimana terlihat pada gambar berikut:












Fungsi yang dapat kita gunakan adalah:
=IF(AND(D5>$G$7,D5<$H$5),$I$6,IF(AND(D5>$G$6,D5<$H$4),$I$5,IF(AND(D5>$G$5,D5<$H$3),$I$4,IF(D5>$G$4,$I$3,$I$7))))


Selain dengan menggunakan urutan menaik, yaitu jika nilainya antara 20-40, maka hurufnya D, jika antara 40-60 hurufnya C, jika antara 60-80 hurufnya B, jika antara 80-100 hurufnya A, jika selainnya (kurang dari 20) hurufnya E.
Pernyataan untuk menyusun fungsi tersebut juga dapat kita susun secara menurun, yaitu jika nilainya antara 100-80 hurufnya A, jika antara 80-60 hurufnya B, jika antara 60-40 hurufnya C, jika antara 40-20 hurufnya D, dan sisanya berhuruf E.
Fungsi yang dapat kita tuliskan adalah:
=IF(AND(M8<=$G$3,M8>=$H$3),$I$3,IF(AND(M8<=$G$4,M8>=$H$4),$I$4,IF(AND(M8<=$G$5,M8>=$H$5),$I$5,IF(AND(M8<=$G$6,M8>=$H$6),$I$6,$I$7))))












Dalam fungsi-fungsi diatas, kita menggunakan fungsi selain IF yaitu fungsi AND. Kita akan bahas pada tulisan berikutnya.
Selamat mencoba...

Tuesday, November 16, 2010

Modifikasi Fungsi IF (Tunggal)

Pada postingan kali ini, saya masih menggunakan fungsi IF tapi dengan sedikit modifikasi. Pada contoh-contoh sebelumnya kita memasukkan nilai jika benar maupun salah secara manual. Hal ini mungkin tidak akan jadi masalah ketika jumlah data (data set) yang kita miliki masih terbatas atau tidak banyak. Namun, kita akan mengalami kesulitan seandainya jumlah data yang ada mencapai ratusan atau bahkan ribuan.
Jika jumlah data sangat banyak dan terjadi perubahan pada prasyarat maupun pernyataan hasil, maka kita akan kerepotan melakukan penyesuaian. Sedikit perubahan pada prasyarat maupun nilai/pernyataan hasil mengharuskan kita untuk mengubahnya satu per satu. Tentunya bukan pekerjaan yang efisien, bukan?
Nah, hal ini akan dapat diatasi seandainya kita menggunakan sel rujukan. Sehingga perubahan cukup kita lakukan pada sel rujukan, dan secara otomatis blok sel hasil akan menyesuaikan dengan perubahan di sel rujukan.

Untuk mudahnya, mari kita andaikan kita memiliki data nilai mahasiswa sebanyak 500 orang. Kita sudah melakukan input nilai hasil ujiannya, dan dosen pengampu menyatakan bahwa nilai minimal kelulusan adalah 55. Dengan mudah kita dapat mengaplikasikan fungsi IF sederhana sebagaimana contoh sebelumnya. Cukup bikin satu fungsi IF dan copy-kan kebawah sebanyak 500 sel. Tidak sampai satu jam kita sudah bisa menyajikan hasilnya, berapa banyak mahasiswa yang Lulus dan yang harus Mengulang.

Mendadak, sehari sebelum pengumuman, dosen tersebut mengubah keputusannya. Bahwa nilai minimal kelulusan dinaikkan menjadi 60 dan keterangannya adalah Lulus dan Tidak Lulus. Bayangkan jika kita harus mengubahnya secara manual sebanyak 500 data...wah, mata bisa jadi keriting, tuch... hehehe.. :-)

Untungnya kita telah memanfaatkan sel referensi, sehingga perubahan tersebut dapat kita penuhi dalam waktu singkat...nggak sampai 5 menit, dech...




Penjelasan Gambar:
Kolom B adalah Nomor Urut
Kolom C adalah Nama Mahasiswa
Kolom D adalah Nilai Ujian
Kolom E adalah Hasil Ujian
=IF(D12>=$J$2, $L$11, $L$12)
Karena kita hanya akan membuat satu fungsi dan kita copy-kan ke bawah, maka sel-sel rujukan kita berikan harga mutlak yaitu diberikan tanda $ (contoh: $J$2) sehingga baik kolom J maupun baris nomor 2 tidak akan berubah saat kita copy-kan ke bawah.
Sehingga dengan menggunakan sel referensi tersebut, perubahan cukup kita lakukan di sel referensi, yaitu di drop-down sel J2 untuk perubahan Nilai Minimal Kelulusan dan sel L11 dan L12 untuk Kriteria Kelulusan.



Catatan:
Untuk membuat drop-down Nilai Minimal Kelulusan, caranya adalah sebagai berikut:
  1. Kita buat dulu Nilai Pilihan Prasyarat, yaitu di sel L2 - L9
  2. Letakkan cursor di sel tujuan, yaitu sel J2
  3. Pilih Tab Data (di bagian atas Jendela Excel), klik Data Validation dan pilih Data Validation...
Dan di jendela Data Validation kita isikan Allow: pilih List, berikan tanda check pada Ignore Blank dan In-dell drop-down. Source: $L$3:$L$9. OK

Dah jadi, dech... selanjutnya kita tinggal mengubah-ubah data di tabel rujukan dan otomatis tabel hasil akan menyesuaikan perubahan nilainya. Selamat mencoba...

Fungsi IF (Tunggal)

Sebagai artikel perdana, pada halaman ini akan saya tuliskan penerapan Fungsi IF dengan nilai tunggal. Untuk mempermudah gambaran, dalam percakapan sehari-hari kita sangat sering bertemu dengan fungsi pengandaian ini.


Misal, Jika Nilai ujian Mata Kuliah Matematika adalah 60 atau lebih, maka dinyatakan Lulus.
Maka pengertian dari pernyataan tersebut adalah:

  1. Mahasiswa dinyatakan Lulus MK Matematika jika Nilainya 60 atau lebih
  2. Mahasiswa dinyatakan Tidak Lulus MK Matematika jika Nilainya kurang dari 60.
Fungsi yang dapat dijalankan dalam Excel untuk pernyataan tersebut adalah:
=IF(pernyataan untuk sel yang dinilai, hasil jika pernyataan terpenuhi, hasil jika pernyataan tidak terpenuhi)


Contoh 1:
=IF(C2>=60, "Lulus", "Tidak Lulus")
Keterangan:
Nilai pada sel target, yaitu sel C2 akan dinilai apakah nilainya sama dengan 60 atau lebih; jika nilai tersebut terpenuhi, yaitu nilainya sama dengan 60 atau lebih, maka akan dihasilkan pernyataan Lulus; sedangkan apabila nilainya tidak terpenuhi, yaitu kurang dari 60, maka akan dihasilkan pernyataan Tidak Lulus.










Contoh 2:
=IF(G5*H5>=100000000, "Mahal", "Tidak Mahal")
Keterangan:
Kita akan menilai hasil perkalian sel target, jika ternyata hasil perkalian antara luas tanah dan harga per meter sama dengan atau lebih dari Rp 100.000.000,- maka dikategorikan sebagai Mahal; sedangkan jika hasil perkalian tersebut kurang dari Rp 100.000.000,- maka dikategorikan Tidak Mahal.










Contoh 3:
=IF(M4="Bajaj", L4*3, L4*4)
Keterangan:
Jika Nilai pada sel target, yaitu sel M4 sama dengan Bajaj (karena nilai tersebut tidak berupa angka, tetapi berupa text, maka penyebutan Bajaj harus menggunakan tanda petik ganda), maka akan dikalikan antara jumlah unit bajaj dengan 3 (jumlah roda tiap bajaj); jika ternyata nilai pada sel M4 bukan Bajaj (kita asumsikan kendaraan roda empat lainnya), maka akan dikalikan antara jumlah unit kendaraan dengan 4 (jumlah roda kendaraan bukan bajaj).



Pengantar

SELAMAT DATANG

Akhirnya hadir juga blog ini, setelah sekian lama ilmu tentang MS Excel hanya terpendam dalam otak dan diantara tumpukan buku di rak. Semoga melalui blog ini kita bisa saling berbagi tentang kedahsyatan Excel.

Saya sendiri telah menggunakan Excel sejak lama sebagai alat bantu dalam berbagai hal. Mengingat keunggulan Excel dalam pengelolaan data, tidak jarang saya juga menggunakan Excel untuk mengelola data text maupun juga database skala kecil, maupun juga untuk membuat formulir.

Saya yakin bahwa masih banyak kelebihan Excel yang saya sendiri belum memiliki kemampuan dan pengetahuan untuk memaksimalkannya. Silakan tinggalkan komen untuk saling berbagi...

Salam Dahsyat :-)