Microsoft Excel sangat berguna untuk mengolah data. Pengolahan data bisa dilakukan dengan memakai rumus Excel yang relevan.
Rumus Excel atau
fungsi microsoft Excel
adalah persamaan yang dapat melakukan perhitungan, meminta kembali
informasi, memanipulasi isi sel lain, melakukan kondisi pengujian, dan
banyak lagi. Pengetikan rumus Excel selalu diawali dengan tanda sama
dengan (=).
Sebuah
rumus Excel dapat mengandung fungsi, operasi matematika, dan konstanta. Fungsi adalah rumus Excel atau formula yang merupakan bawaan dari
Microsoft Excel. Kita akan melihat beberapa fungsi microsoft Excel yang paling sering dipakai.
Rumus Excel VLOOKUP
Syntax dari
fungsi microsoft excel ini adalah :
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Rumus Excel VLOOKUP
dipakai untuk mencari nilai dalam kolom pertama dari tabel dan
mengembalikan nilai dalam baris yang sama dari kolom lain dalam tabel.
Supaya lebih jelas, kita pakai fungsi Microsoft Excel VLOOKUP dalam
contoh berikut.
Toko Saribusana memiliki tabel Daftar Barang sbb. :
Kemudian, saya diminta membuat tabel hasil penjualan bulan Juli 2011 seperti berikut :
Untuk mengisi kolom Keterangan, saya akan ambil dari Daftar Barang Toko
Saribusana berdasarkan Kode yang ada, maka rumus Excel nya adalah
=VLOOKUP(B12,$B$3:$E$6,2,FALSE)
B12 : Kode Barang di B12 adalah acuan yang dipakai untuk mengambil data di tabel Daftar Barang.
$B$3:$E$6 : range tabel yang akan kita ambil
datanya, dalam hal ini adalah tabel Daftar Barang tanpa mengikutsertakan
judul kolom. Dan, jangan lupa gunakan $ sebab range tabel adalah
absolut.
2 :
kolom ke-2 yaitu kolom
Keterangan di range tabel adalah kolom yang akan kita ambil datanya.
Kolom ke-1 selalu dimulai dari kolom paling kiri.
Singkatnya, kita menyuruh Excel melihat Kode di cel B12 lalu cari di
range tabel Daftar Barang sesuai Kode yang sama, setelah itu ambil nilai
di kolom Keterangannya dan isikan di tabel Hasil Penjualan. Hasilnya :
Demikian juga untuk mengisi kolom Harga Penjualan di cel E12, dengan memperhatikan bahwa
Harga Penjualan = Harga Pokok * (1+Markup), maka , rumus Excel nya adalah
=VLOOKUP(B12,$B$3:$E$6,3,FALSE)*(1+VLOOKUP(B12,$B$3:$E$6,4,FALSE))
Di fungsi Microsoft Excel tersebut, kita menyuruh Excel melihat Kode
di B12 lalu cari kode yang sama di tabel Daftar Barang, setelah itu
ambil Harga Pokoknya dan kalikan Harga Pokok dengan (1+Markup). Nilai
Markup diambil dengan cara yang sama dengan cara mengambil nilai Harga
Pokok. Maka kita mendapatkan Harga Penjualan untuk Kode di B12.
Oke, silakan mencoba dulu dan mengutak atik fungsi Microsoft Excel VLOOKUP. Semoga mengerti.
Oh, ya, V di VLOOKUP singkatan dari vertikal. Penggunaan VLOOKUP
(bukan HLOOKUP) ketika nilai-nilai acuan Anda berada dalam kolom di
sebelah kiri tabel data dan Anda mengambil data lain di sebelah
kanannya. Gunakan fungsi microsoft excel HLOOKUP ketika nilai-nilai
acuan Anda berada dalam baris di sebelah atas tabel data dan Anda ingin
mengambil data-data di baris bawahnya.
Rumus Excel IF
Syntax dari
fungsi microsoft excel ini adalah :
IF(logical_test,value_if_true,value_if_false)
Maka dengan menggunakan fungsi microsoft excel IF, kita memberikan :
- suatu kondisi kepada Excel, lalu
- jika kondisi itu terpenuhi maka lakukan A,
- jika tidak terpenuhi maka lakukan B.
Oke, kita coba dalam kasus yang sama yaitu Toko Saribusana. Kembali
saya diminta menambahkan sebuah kolom di Tabel Penjualan bulan Juli 2011
yang menunjukkan status barang. Jika penjualan barang lebih dari 500
unit berarti
Barang Laku, jika penjualan diantara 300 dan 500 maka
Cukup Laku, dan di bawah 300 berarti
Ga Laku.
Rumus excel di sel G12 adalah
=IF(D12>300,IF(D12>500,”Laku Keras”,”Kurang Laku”),”Ga Laku”)
Saya meminta Excel untuk mengecek :
- Apakah unit terjual di D12 lebih dari 300 =IF(D12>300
- Jika ya maka cek lagi apakah D12 lebih dari 500, IF(D12>500
- Jika ya maka Laku Keras, “Laku Keras”
- jika tidak maka Kurang Laku. “Kurang Laku”
- jika tidak berarti barang Ga Laku. “Ga Laku”
Di atas terlihat bahwa terjadi dua kali pengecekan, atau saya memakai
2 kali rumus excel IF. Fungsi If kedua berada di dalam fungsi If
pertama. Fungsi IF dapat dipakai di dalam fungsi IF lainnya, jangan lupa
fungsi IF di dalam tidak memakai tanda sama dengan [=] lagi.
Sekarang kita akan mencoba fungsi microsoft excel yang berhubungan dengan tanggal.
Fungsi Microsoft Excel Date and Time
Kedua fungsi microsoft excel ini sering juga dipakai dalam mengolah data di Excel.
Langsung saja, di atas adalah tabel Daftar Siswa Tercepat dalam Ujian
IPA dari tiap kelas. Petugas administrasi memasukkan data tanggal,
bulan, tahun dalam kolom-kolom terpisah supaya lebih cepat dan mudah.
Nah, tugas saya mencari siapa yang tercepat dari kelima kontestan
tersebut.
Pertama, saya satukan dulu tanggal, bulan dan tahun menjadi satu di sel C12 dengan fungsi microsoft excel :
=DATE(E3,D3,C3)
Fungsi microsoft excel ini mengambil data di sel E3,D3,C3 dan menjadikannya tanggal.
Lalu satukan juga jam, menit, dan detik tiap siswa di sel D12 dengan rumus :
=TIME(F3,G3,H3)
Rumus ini mengambil data di sel E3,D3,C3 dan menjadikannya waktu,
untuk format time yang Anda ingini, ubah dalam menu Format Cells (klik
kanan di sel, pilih Format Cells, pilih Time, pilih format yang Anda
mau).
Baru saya cari siswa tercepat di sel E12 dengan fungsi microsoft excel :
=RANK(D12,$D$12:$D$16,1)
Fungsi Rank mencari urutan dalam suatu range, lalu Anda bisa pilih
mau Ascending atau Descending. Hasilnya adalah tabel berikut, selamat
buat Cassey, kamu tercepat!
Okay, sementara itu dulu fungsi microsoft excel yang bisa saya berikan.
Kita akan bertemu lagi di tips & trik berikutnya.
