Dalam menjalankan tugas kampus, saya akan menerangkan fungsi dari beberapa more functions yaitu countif, sumif, dan vlookup. Langsung saja cekidot..!!!!
FUNGSI COUNTIF
Fungsi COUNTIF termasuk kedalam kategori Logic
Functions tersebut. Fungsi ini pada dasarnya memiliki kegunaan yang sama dengan
fungsi COUNT yaitu untuk menghitung
banyaknya data. Namun pada fungsi COUNTIF, banyaknya data ini hanya akan
dihitung jika sesuai dengan kriteria atau kondisi tertentu. Aturan
penulisannya sebagai berikut:
=COUNTIF(Range Data;Kondisi)
Dari aturan penulisan tersebut dapat Anda lihat bahwa
fungsi COUNTIF ini terdiri dari 2 argumen dimana tiap-tiap argumen dipisahkan
dengan tanda pemisah tertentu. Hal ini berbeda dengan beberapa fungsi yang
telah dibahas sebelumnya yang masih menggunakan argumen tunggal yang sangat
sederhana.
Agar lebih jelas dalam memahami penggunaan fungsi
COUNTIF ini, perhatikan contoh tabel berikut ini. Tiap-tiap siswa memiliki
status kelulusan yang berbeda dimana ada yang LULUS dan ada yang GAGAL.
Sementara yang ditanyakan adalah berapakah jumlah siswa yang LULUS?
Berdasarkan aturan penulisan COUNTIF, status LULUS
merupakan kondisi yang dikehendaki, dan range yang memuat status LULUS tersebut
merupakan range data yang akan dihitung oleh Excel yaitu sel C3 hingga C6.
Dengan demikian formula untuk menghitung banyaknya siswa yang lulus tersebut
adalah sebagai berikut:
C7=COUNTIF(C3:C6;LULUS)
Namun berdasarkan aturan penulisan formula,
LULUS adalah variabel yang tidak dikenali oleh Excel sehingga formula diatas
jelas akan menghasilkan pesan kesalahan jika dijalankan. Agar kata LULUS tersebut
dikenali, maka kita berikan tanda kutip diantaranya, sehingga formulanya
menjadi:
C7=COUNTIF(C3:C6;"LULUS")
Dan sekarang formula ini akan menghasilkan nilai yang
benar seperti tampak pada gambar berikut ini.
FUNGSI SUMIF
Selain COUNTIF, fungsi
logika lainnya yang umum digunakan adalah fungsi SUMIF. Sesuai dengan
fungsi dasarnya yaitu SUM, fungsi SUMIF
juga digunakan untuk menjumlahkan data pada range tertentu, hanya saja data
yang akan dijumlahkan mengacu pada kondisi tertentu.
Aturan penulisan fungsi SUMIF ini sebagai berikut:
=SUMIF(range kondisi;kondisi;range data)
Agar lebih jelas perhatikan contoh pada tabel berikut
ini. Yang ditanyakan pada tabel tersebut bukanlah total laba keseluruhan
melainkan total laba hanya untuk barang yang bagus saja serta total laba hanya
untuk barang yang rusak saja.
Pada kasus ini, kata BAGUS dan RUSAK
merupakan sebuah kondisi yang berada pada range C3 hingga C6.
Sementara data yang akan dijumlahkan berada pada range D3 hingga D6.
Dengan demikian, sesuai dengan aturan penulisan SUMIF, maka formula untuk
menghitung Total Laba Barang Bagus di sel D9 adalah sebagai
berikut:
D9=SUMIF(C3:C6;BAGUS;D3:D6)
Sesuai dengan aturan penulisan formula,
kata BAGUS bukan merupakan variabel yang dikenali Excel, maka kita perlu
menambahkan tanda kutip untuk mengapit kata tersebut, hingga formulanya akan
menjadi:
D9=SUMIF(C3:C6;"BAGUS";D3:D6)
Lalu untuk menghitung Total Laba Barang Rusak
di sel D10, tentunya Anda tinggal mengganti kata BAGUS menjadi RUSAK,
hingga formulanya menjadi:
D10=SUMIF(C3:C6;"RUSAK";D3:D6)
Dan contoh hasilnya dapat dilihat pada gambar berikut
ini.
FUNGSI VLOOKUP
VLOOKUP termasuk kedalam salah satu fungsi yang paling banyak
digunakan dalam aplikasi Excel. Sesuai dengan nama dasarnya yaitu lookup yang
bisa diartikan melihat atau mencari, maka fungsi ini akan menghasilkan suatu
formula untuk mengisi data pada tabel berdasarkan data pada tabel lainnya
atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang
spesifik. Awalan huruf V didepan kata lookup merupakan singkatan dari kata
Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang
digunakan, dimana judul kolomnya terletak dibagian atas dan data-datanya
tersusun kebawah secara vertikal. Contoh sebuah tabel vertical ini bisa dilihat
pada gambar berikut ini.
Aturan penulisan VLOOKUP mungkin bisa dibilang agak
rumit bagi yang baru saja mempelajari penggunaan formula pada Excel. Namun jika
sudah terbiasa, aturan ini sebetulnya bisa dibilang sederhana, yaitu:
=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
Penjelasan aturan penulisan tersebut sebagai berikut:
- nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai ini harus ada baik pada tabel yang akan diisi maupun pada tabel referensi.
- range_tabel_referensi: Adalah range dari tabel yang berisikan data referensi untuk mengisi hasil yang diharapkan. Pastikan bahwa range yang Anda pilih tidak menyertakan judul kolomnya.
- no_index-kolom: Adalah nomor urut data dalam tabel referensi yang akan dituliskan hasilnya. Dimulai dari kolom paling kiri pada tabel referensi tersebut dengan nomor index 1, dan seterusnya.
- tipe_data: Ada 2 jenis tipe data yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda gunakan jika nilai data berharga pasti.
Agar lebih jelas bagaimana cara menggunakan fungsi
VLOOKUP ini, perhatikan contoh berikut ini.
Pada contoh tersebut terdapat 2 buah tabel. Data pada
tabel Laporan Penjualan jelas akan selalu bertambah tiap harinya. Setiap
kali ada pembeli maka Anda tinggal mengisikan data tanggal, nama pembeli serta
kode voucher-nya. Sementara data Voucher serta Harga akan terisi
secara otomatis tiap kali Anda mengisikan data pada kolom Kode.
Pengisian data secara otomatis ini bisa dicapai dengan melihat tabel referensi
yaitu tabel Stok Gudang dan tentunya dengan menggunakan formula VLOOKUP.
Langkah pertama akan diisi dahulu data pada tabel Voucher
yaitu data pada sel E12. Untuk mempermudah pembahasan, aturan penulisan
VLOOKUP kembali dituliskan sebagai berikut:
E12=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)
nilai_kunci yang digunakan adalah data pada kolom Kode
dalam tabel Laporan Penjualan, yaitu sel D12. Alasannya karena Voucher
akan bisa terisi dengan bersandar pada data dalam kolom Kode tersebut.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;range_tabel_referensi;no_index_kolom;tipe_data)
Kemudian range_tabel_referensi jelas adalah
range data pada tabel Stok Gudang yaitu B4:D7.
Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;no_index_kolom;tipe_data)
no_index_kolom Anda tentukan dengan melihat didalam range_tabel_referensi.
Dalam hal ini Anda akan mengisi data pada kolom Voucher maka Anda lihat
data untuk Voucher tersebut didalam range_tabel_referensi berada pada kolom
keberapa?
Dari gambar diatas dapat dilihat bahwa data yang akan
diambil berada pada kolom ke-2, maka no_index_kolom yang digunakan
adalah 2. Dengan demikian formulanya menjadi:
E12=VLOOKUP(D12;B4:D7;2;tipe_data)
Yang terakhir adalah tipe_data. Disini yang
akan digunakan adalah FALSE. Penjelasannya adalah karena data tersebut
bersifat pasti, misalnya kode S sudah pasti untuk Simpati, kode E sudah pasti
untuk Esia, dan demikian seterusnya. Dengan demikian formula lengkap untuk
mengisi data pada kolom Voucher tersebut adalah:
E12=VLOOKUP(D12;B4:D7;2;FALSE)
Untuk mengisi data pada sel selanjutnya yaitu sel E13
dan E14 seperti biasanya Anda dapat menggunakan Auto Fill.
Namun perhatikan bahwa tabel referensi yang menjadi acuan memiliki range alamat
sel yang statis alias tetap. Oleh karenanya, sebelum Anda menjalankan proses
Auto Fill maka Anda harus mengunci terlebih dahulu range tabel referensi
tersebut agar menjadi sel yang absolut.
Hingga formulanya akan menjadi:
E12=VLOOKUP(D12;$B$4:$D$7;2;FALSE)
Dan proses Auto Fill kini dapat
dijalankan untuk mengisi data pada sel-sel berikutnya.
Berikutnya, bagaimana mengisi data pada kolom Harga
yaitu sel F12 hingga F14? Formula yang digunakan tentunya tetap
sama hanya saja no_index_kolom yang berbeda yaitu 3 karena data
yang akan diisikan diambil dari kolom ke-3 pada tabel referensi.
Dan berikut ini formula yang digunakan setelah
menggunakan sel absolut untuk range tabel referensinya.
F12=VLOOKUP(D12;$B$4:$D$7;3;FALSE)
Pada contoh diatas Anda telah melihat penggunaan tipe data FALSE yaitu tipe data yang pasti. Untuk tipe data TRUE aturan penulisan formula-nya tetap sama, hanya saja data yang akan diisikan adalah data yang tidak pasti atau berada pada range tertentu. Contoh untuk penggunaan tipe data TRUE ini bisa dilihat pada tabel berikut ini.
Anda harus mengisikan formula VLOOKUP pada kolom
Nilai dalam tabel Daftar Nilai Siswa, yaitu sel E13 hingga
sel E15. Nilai yang akan diisikan tersebut mengacu pada range tertentu
dalam tabel referensi, yaitu:
- Nilai E: Score 0 s/d 39
- Nilai D: Score 40 s/d 59
- Nilai C: Score 60 s/d 79
- Nilai B: Score 80 s/d 89
- Nilai A: Score 90 s/d 59
Kondisi ini jelas menggambarkan sebuah data yang tidak
pasti oleh karenanya tipe data yang digunakan adalah TRUE. Dan formula
akhir yang digunakan adalah sebagai berikut.
=VLOOKUP(D13;$B$4:$C$8;2;TRUE)
Yang harus Anda perhatikan untuk tipe data TRUE ini
adalah urutan data pada tabel referensi harus menaik atau tersusun dari data
terkecil hingga data terbesar.
Sumber :
http://social.technet.microsoft.com/wiki/contents/articles/16290.mengenal-formula-pada-excel-part-13-fungsi-countif-id-id.aspx
http://social.technet.microsoft.com/wiki/contents/articles/16307.mengenal-formula-pada-excel-part-14-fungsi-sumif-id-id.aspx
http://social.technet.microsoft.com/wiki/contents/articles/16485.mengenal-formula-pada-excel-part-24-fungsi-vlookup-id-id.aspx
