Memfilter table pada Excel

Beberapa bulan lalu Saya mendapatkan informasi tentang sebuah program dashboard yang terintegrasi dengan SAP bernama XCelsius (sekarang telah dibeli SAP).

http://www.sap.com/solutions/sapbusinessobjects/sme/xcelsius/index.epx

Setelah membaca informasinya Saya lihat program XCelsius ini ternyata cukup gampang dalam pengoperasiannya, user dengan pengetahuan yang memadai pada Excel dapat menggunakannya dengan mudah. Kemudahan ini dikarenakan XCelsius dapat membaca fungsi-fungsi dari excel. Hasil dari XCelsius adalah sebuah file flash (*.SWF).

Selama trial 15 hari, Saya mencoba untuk melihat sejauh apa program ini bisa menangani data yang besar. Ternyata agak sulit juga bila data yang disajikan banyak, saya mencoba memasukkan data sebanyak 4000 baris, ternyata XCelsius menjadi tidak dapat loading. Memang sih waktu dimasukkan 4000 data XCelsius ada ngomel-ngomel karena data yang dimasukkan kebanyakan (standar mereka 512 records).

Selama uji coba tersebut, Saya mendapatkan satu rumus untuk memfilter records. Filter ini bekerja mirip seperti autofilter pada Excel, hanya saja memakai rumus. Kenapa tidak memakai autofilter? Karena XCelsius tidak dapat memanfaatkan fasilitas tersebut pada Excel.

Berikut ini adalah cara autofilter dengan menggunakan rumus pada Excel :

1. Buat sebuah Sheet kosong dan isi dengan 4 data seperti dibawah ini :
   
2. Sekarang Kita ingin menfilter data tersebut sehingga hanya golongan darah yang kita inginkan yang muncul. Hasil yang diinginkan adalah seperti pada gambar dibawah ini (kita mencari golongan darah A):

1. Kolom G1 memuat data golongan darah yang dicari (“A” dalam kasus ini). Kolom F4 sampai G8 menampilkan hasilnya. Nah Kolom D4 sampai E8 adalah kolom untuk membantu mendapatkan hasilnya. Sekarang Saya akan menunjukkan bagaimana kolom D4:E8 bekerja.
2. Pertama-tama klik cell D5, dan masukkan rumus ini : “=MATCH($G$1;$B$2:$B$7;0)”. Rumus ini berupaya mencari kata “A” dalam range B2 sampai B7. Hasilnya adalah cell pertama dalam range tersebut yaitu B2, maka nilai akhir dari perhitungan ini adalah “1″. Kenapa hasil perhitungan bukan “2″? Karena range dimulai dari B2 bukan B1.
3. Kedua klik cell E1 dan masukan rumus ini : “=D5″. Hasilnya pasti satu juga karena cell ini hanya menjiplak. Fungsi cell ini akan saya terangkan kemudian
4. Ketiga, pada cell D6 akan dibuat seperti D5 (step 4 diatas) hanya saja sekarang rangenya harus mulai dari satu titik setelah nilai A terakhir kali ditemukan. Nilai A ditemukan pada cell B2 maka rumus harus mulai dari cell B3. Untuk ini, kita akan memanfaatkan fungsi powerful excel yaitu INDEX(). Pada cell D6 ketiklah : “=MATCH($G$1;INDEX($B$2:$B$7;E5+1;1):$B$7;0)”
   Cara kerjanya adalah sebagai berikut :
   awalnya kita memiliki fungsi “=MATCH($G$1;INDEX($B$2:$B$7;E5+1;1):$B$7;0)”, fungsi INDEX($B$2:$B$7;E5+1;1) akan mengembalikan referensi “B3″ karena Saya memberikan perintah “Tolong ambil nilai dari kolom ke satu, baris ke E5 + 1 (hasilnya 2) dari range B2 sampai B7″. Baris ke 2 dan kolom pertama dari range “B2:B7″ adalah “B3″! Hasil hari INDEX adalah sebagai berikut :
   “=MATCH($G$1;$B$3:$B$7;0)” Function index telah diterjemahkan sebagai “B3″, Hasil dari fungsi match adalah 2 karena kata “A” berikutnya ada pada baris ke “2″ dari range “B3:B7″
5. Keempat, Klik cell “E6″ dan masukkan fungsi berikut : “=E5+D6″. Fungsi ini mengakibatkan kita selalu tahu kapan terakhir kali huruf A ditemukan.
6. Klik pada kolom D6 dan tempatkan cursor pada sudut kanan bawah sehingga menjadi cursor hitam. Klik dan geser cursor hitam sehingga mencapai D8. Lakukan hal yang sama dengan cell E6 (klik dan geser cursor hitam ke E8). Ini adalah screen shot hasil sementara yang diperoleh:
   
7. Lihatlah kolom E5:E8, kolom tersebut sekarang berisi infomasi posisi huruf A pada tabel data  . Sekarang kita hanya perlu membuat fungsi terakhir untuk membaca data. Pada cell F5 tuliskan rumus berikut : “=INDEX($A$2:$B$7;E5;1)”
   Rumus =INDEX($A$2:$B$7;E5;1) berarti : “Dari range A2 sampai B7 ambillah nilai dari cell yang berada pada baris ke 1 (karena nilai cell E5 adalah 1) dan kolom ke 1″
8. Pada cell G5 ketikkan =INDEX($A$2:$B$7;E5;2). Fungsinya seperti kolom F5 hanya saja sekarang kolom kedua yang dipilih.
9. Klik cell F5 dan tempatkan cursor pada sudut kanan bawah cell sehingga berubah menjadi cursor hitam. Click and Drag ke F8. Lakukan hal yang sama dengan G5 (click and drag ke G8). Hasil akhirnya adalah sebagai berikut :
   

Sekarang kita telah mempunyai satu table filter. Bila ingin menghilangkan #N/A gunakan rumus ISNA(). Misalkan pada cell F5 : =IF(ISNA(D5);”";INDEX($A$2:$B$7;E5;1))

sumber : http://pandarion.wordpress.com/2009/02/09/memfilter-table-pada-excel/