Pada waktu SMP dulu, saya diajarkan untuk menggunakan program komputer WS untuk menghasilkan tulisan yang biasanya pada akhirnya akan dicetak. Selain WS, saya pun diajarkan Lotus yang biasa dipergunakan untuk melakukan perhitungan masal dan bentuknya tabel. Sekarang, WS dan Lotus sudah tinggal sejarah. Fungsi WS sudah tergantikan oleh Microsoft Word dan fungsi Lotus sudah tergantikan oleh Microsoft Excel. Sewaktu saya kuliah, saya sering sekali menggunakan Microsoft Word dan merasa bahwa Microsoft Excel tidak terlalu berguna. Setelah bekerja, saya baru merasakan betapa bermanfaatnya Microsoft Excel karena sehari-hari saya sering melakukan olah data. Olah data secara masal akan lebih mudah dengan bantuan fitur-fitur yang Microsoft Excel berikan. Dari ratusan fitur-fitur tersebut berikut fitur-fitur Microsoft Excel yang paling sering saya pergunakan.
1. Formula vlookup
Vlookup dipergunakan untuk melakukan pencocokan dan menampilkan pasangan karakter yang hendak ditampilkan. Berikut cara-caranya:
a. Misalkan saya memilki 2 Sheet yaitu Sheet Report1 dan Sheet No TCR. Saya ingin mencari tahu isi kolom Site ID dan Status dari HCR No. pada Sheet No TCR. Jawabannya sebenarnya ada pada Sheet Report1, di sana terdapat pasangan-pasangan dari setiap Site ID, Status dan HCR No. Dengan vlookup, pencocokan masal dapat dilakukan dengan cepat.
b. Pilih kolom yang ingin kita ketahui nilainya dari pencocokan, lalu cari VLOOKUP pada Name Box yang ada di sekitar kiri atas dan tekan Enter. Berikutnya akan muncul Function Arguments.
c. Sort nilai yang akan dicocokkan, dalam kasus ini seluruh kolom A pada Sheet No TCR.
d. Masukkan nilai sumber pencocokkan pada Table_array, dalam kasus ini adalah seluruh kolom A dan B pada Sheet Report1.
e. Masukkan berapa kolom yang diperlukan dari kolom HCR No. ke kolom Site ID pada Col_index_num, dalam kasus ini nilai 2 yaitu kolom A & B pada Sheet Report1. Misalkan yang hendak dicari adalah GM Area dari HCR No. , maka Col_index_num nilainya 7 yaitu jumlah kolom dari kolom A sampai kolom G pada Sheet Report1.
f. Masukkan angka 0 yang artinya False pada Range_lookup agar pencocokan dilakukan untuk nilai yang sama persis, bukan mendekati. Nantinya hasil akan keluar bisa nilai A pada Sheet Report1 sama persis dengan nilai A pada Sheet No TCR.
g. Nilai B2 Sheet No TCR sama dengan nilai B9 Sheet Report1 karena nilai A2 Sheet No TCR dan A9 Sheet Report1 sama-sama HCR00023 nilainya.
h. Berikut hasilnya bila cara yang sama diberlakukan untuk mencari nilai kolom B & C Sheet No TCR.
2. Formula Menemukan Durasi Waktu
Saya biasa menggunakan TODAY pada pengurangan waktu untuk memperoleh nilai durasi hari yang terhitung sampai tanggal terkini, tanggal ketika file excel tersebut dibuka. Berikut cara-caranya:
a. Misalkan saya ingin memasukkan ke dalam kolom D, informasi akan durasi hari dari dari yang ditunjukkan oleh kolom C hingga hari ketika saya membuka file Excel tersebut. Pada kolom cell D2, saya masukkan formula =TODAY()-C2 sehingga muncul sebuah deretan informasi tapi masih dalam bentuk tanggal, bukan hari.
b. Pada cell D2, lakukan klik kanan mouse, lalu pilih “Format Cells…” dan mengganti kategori menjadi “Number” sehingga muncullah durasi dalam satuan hari pada cell D2 :). Karena saya membuat contoh ini pada 31-8-2015, maka muncul angka 50 pada D2.
c. Lakukan “copy” dan “paste” cell D2 ke dalam cell-cell di bawahnya sehingga diperoleh durasi hari pada kolom D.
3. Formula If
Ahhhh if adalah formula yang ada pada berbagai bahas pemrograman seperti C++, Pascal, Basic dan kawan-kawan. Mirip seperti pada bahasa pemrograman, if pada Excel dipergunakan untuk menentukan nilai benar atau salah dari hasil ekpresi logika. Formulanya kurang lebih seperti ini: = if(ekspresi logika;nilai benar;nilai salah). Formula ini dapat dibuat bertingkat atau nested seperti ini: =if(ekspresi logika;nilai benar;if(ekspresi logika 2;nilai benar;if(…. dan seterusnya maksimal sampai 8 tingkat. Formula ini dapat digunakan bersambung di kolom atau cell lain pula. Agar lebih jelas, berikut contohnya:
a. Misalkan saya ingin mengelompokkan nilai-nilai pada kolom D apakah termasuk kecil sekali atau kecil atau sedang atau besar atau besar sekali dengan ketentuan sebagai berikut:
- Nilai D adalah Kecil Sekali bila nilainya lebih kecil sama dengan 0.
- Nilai D adalah Kecil bila nilainya lebih besar dari 0 dan lebih kecil sama dengan dari 100.
- Nilai D adalah Sedang bila nilainya lebih besar dari 100 dan lebih kecil sama dengan dari 200.
- Nilai D adalah Besar bila nilainya lebih besar dari 200 dan lebih kecil sama dengan dari 300.
- Nilai D adalah Besar Sekali bila nilainya lebih besar dari 300 dan lebih kecil sama dengan dari 400.
- Nilai D adalah Error bilai nilainya lebih besar dari 400.
b. Masukkan nilai =IF(D2<=0;”Kecil Sekali”;IF(D2<=100;”Kecil”;IF(D2<=200;”Sedang”;M2))). Pada contoh kali ini, saya coba buat contoh untuk formula if yang bersambung ke cell lain, dalam hal ini cell M2. Karena pengelompokkannya terdiri dari 6 aturan, maka sebenarnya semua dapat dimuat dalam 1 formula tanpa harus bersambung.
c. Masukkan =IF(D2<=300;”Besar”;IF(D2<=400;”Besar Sekali”;”Error”)) pada cell M2.
d. Lakukan copy dan paste pada cell-cell di bawahnya dan diperoleh hasil pengelompokan pada kolom L. Kolom M hanyalah coretan untuk contoh penggunaan formula if yang bersambung.
4. Grafik
Dalam setiap laporan atau presentasi, saya sering harus membuat grafik dari tabel data yang cuantik :P. Ahhhh, supaya lebih jelas, berikut contohnya:
a. Misalkan saya ingin membuat grafik dari data pada beberapa cell di kolom A dan D seperti yang ditunjukkan pada gambar di bawah.
b. Sort cell-cell tersebut lalu pilih menu “Insert” sehingga muncul pilihan jenis grafik apa yang ingin ditampilkan. Misalkan grafik batang yang hendak ditampilkan, maka pilihlah “Column” dan varian dari grafik batangnya, apakah yang datanya dijejerkan? Apakah yang datanya dijadikan dalam 1 batang Apakah bentuk batangnya tajam-tajam?
c. Misalkan jenis grafik batang yang dipilih adalah grafik batang yang tidak tajam dan datanya dibuat berjejer tidak disatukan dalam 1 batang, maka akan muncul gambar seperti di bvawah ini.
d. Warna yang otomatis muncul biasanya biru, tapi itu bisa diubah dengan memilih warna-warna lain yang terletak pada menu “Design”.
e. Bosan dengan warna yang itu-itu saja? Yuk kita coba tambahkan variasi yang dapat mempercantik grafik. Lakukan klik kanan pada gambar grafik dan pilih “Format Chart Area”.
f. Pilih “Fill” lalu silahkan pilih jenis variasi yang hendak dibubuhkan pada grafik.
5. Pivot
Pivot digunakan untuk membuat pengklasifikasian bertingkat sekelompok data. Inilah salah satu fungsi yang sering saya pergunakan untuk memperoleh jumlah dari beberapa data yang disajikan terpisah namun masih dalam 1 tabel data yang sama, bingung? Aaaahhhh supaya jelas, berikut contohnya:
a. Misalkan saya ingin mengetahui berapa tiket HCR berstatus solved yang dibuat oleh Alief Cakep untuk GM Area Route 2 dan berapa tiket berstatus open yang dibuat oleh Tono untuk GM Area Route 1. Kemudian saya ingin melihat bagaimana perbandingan jumlah creator, status dan GM Area dengapn meisahan setiap Mgr. Area dalam 1 grafik yang sama. Data tiket HCR ada pada kolom A, data creator ada pada kolom E, data status ada pada kolom G, data GM Area ada pada kolom H dan data Mgr. Area ada pada kolom I. Maka pertama-tama pilih “Insert” lalu “Pivot Table” dan Pivot Chart”.
b. Pilih semua cell pada kolom A, E, G, H dan I, atau boleh juga semua kolom asalkan terdapat kolom A, E, G, H dan I di dalamnya.
c. Perhatikan pilihan judul kolom yang terletak di bawah tulisan “Choose fields to add to report”, posisinya ada di bagian kanan. Di antara pilihan tersebut, tarik “Created by” (judul kolom E) dan “GM Area” (judul kolom H) ke dalam kotak yang berjudul “Row Labels”. Lalu tarik pula “HCR No.” (judul kolom A) ke dalam kotak yang berjudul “Values”. Kemudian tarik “Status” (judul kolom G) ke dalam kotak “Column Labels”. Terakhir, tarik “Mgr. Area” (judul kolom H) ke dalam kotak “Report Filter”.
d. Diperoleh data bahwa tiket HCR berstatus solved yang dibuat oleh Alief Cakep untuk GM Area Route 2 adalah sebanyak 1. Sedangkan tiket berstatus open yang dibuat oleh Tono untuk GM Area Route 1 adalah sebanyak 1.
e. Muncul grafik yang dipisahkan berdasarkan data Mgr. Area.
6. Text to Column
Seringkali saya memperoleh deretan data yang bentuknya tidak rapi atau ideal ketika di paste ke Excel. Di sanalah fungsi text to column dapat dipergunakan, berikut contohnya:
a. Misalkan saya hendak memisahkan karakter sebelum huruf R dengan karakter sesudah huruf R pada semua cell di kolom H. Pilih “Data”, kemudian “Text to Column”.
b. Pilih “Delimited”.
c. Pilih “Other” lalu masukkan “R” pada cell kosong disamping pilihan “Other”. Cell kosong tersebut hanya dapat dimasukkan 1 karakter, bukan kata apalagi kalimat.
d. Lihat “Data Preview” apakah sudah sesuai dengan yang diharapkan? Kalau sudah, pilih “Finish”.
e. Karakter sebelum huruf R akan muncul di kolom H dan karakter setelah huruf R akan muncul di sebelah kolom H yaitu kolom I.
7. Formula SEARCH dan MID
Gabungan formula Search & Mid dapat dipergunakan untuk menampilkan potongan karakter dari cell lain dengan karakter/kata/kalimat sebagai tanda pemisah, berikut contohnya:
a. Misalkan saya ingin menampilkan di kolom K, 5 karakter setelah kata-kata CR dari semua cell di kolom A.
b. Masukkan formula =MID(A2;SEARCH(“CR”;A2);5) pada K2. Pada formula ini, SEARCH dipergunakan untuk mencari kata-kata CR pada kolom A2 itu ada pada karakter keberapa dihitung dari karakter pertama. Kemudian informasi tersebut dipergunakan untuk melengkapi fungsi MID sehingga diperoleh 5 karakter setelah kata-kata CR pada A2. Pada contoh di bawah ini, saya masukkan pula SEARCH(“CR”;A2) pada J2 untuk melihat hasil dari fungsi SEARCH sebelum digabungkan dengan fungsi MID.
c. Lakukan copy paste pada cell-cell di bawahnya sehingga kita memperoleh hasil seperti di bawah ini.
Sekian kumpulan fitur-fitur Excel yang relatif sering saya pergunakan di kantor. Semoga bermanfaat :).
Alief Workshop 