Mendapatkan Top 10 List dengan Formula Excel

Pihak manajemen sering sekali menginginkan informasi top 10 dari berbagai hal, misalkan top 10 produk dengan penjualan tertinggi, top 10 karyawan dengan KPI terbaik, dll.

Solusinya tentu melakukan pengurutan atau sorting pada data asal tersebut dan kemudian diambil 10 record saja.

Nah, seiring kompleksitas pekerjaan kita. Sering sekali kita tidak ingin sumber data kita diganggu. Kita hanya ingin table terpisah yang mengambil 10 data berdasarkan pengurutan di table asal.

Bagaimana kita melakukan hal tersebut? Caranya bisa dengan menggunakan gabungan fungsi MAX, INDEX dan MATCH. Berikut langkah-langkahnya:

1. Download file contoh dari https://belajar-excel.googlecode.com/files/belajar-excel-pageviews-sort.xlsx.
2. Buka file tersebut dengan Excel.
3. Terlihat ada 13 baris data jumlah pageviews dari website BelajarExcel.info. Kita akan mengurutkan top 10 jumlah pageviews dengan topiknya.
   
   
   
4. Pada cell G4 masukkan rumus berikut.
   
   =LARGE($C$4:$C$16,E4)
5. Copy rumus tersebut ke cell G5 s/d G13 dengan menggunakan fill handle.
   
   
6. Terlihat data sudah terurut dari besar ke kecil, namun memang kita batasin untuk 10 baris saja.
   
   
   
   
   Perhatikan, kalau ada 2 nilai yang sama yaitu 1251. Ini yang akan menjadi tantangan selanjutnya untuk mendapatkan nama topik terkait. Dengan menyadari hal ini, kita akan menemukan nilai ini adalah kemunculan keberapa kalinya dengan rumus COUNTIF.
7. Pada cell H4 (di bawah kolom Occurrence), masukkan formula berikut.
   
   =COUNTIF($G$4:G4,G4)
   
   Rumus ini digunakan untuk menghitung kemunculan nilai dimulai dari data awal ($G$4) sampai baris ditemukannya nilai tersebut (G4). Kebetulan cellnya masih sama, tapi pada saat kita copy berikutnya maka alamat relatif cellnya akan berubah.
8. Copy rumus di atas ke cell H5 sampai dengan H13. Hasilnya terlihat sebagai berikut. Perhatikan untuk angka 1251 kita mendapatkan angka 1 untuk kemunculan pertama, dan angka 2 untuk kemunculan kedua.
   
   
9. Sekarang kita akan coba mencari nomor baris kemunculan datanya di table asal dengan menggunakan fungsi MATCH. Pada kolom I4 masukkan rumus match berikut.
   
   =MATCH(G4,$C$4:$C$16,0)
   
   Setelah itu copy rumus tersebut ke I5 s/d I13. Terlihat hasilnya seperti pada gambar berikut, terlihat pada kemunculan ke 2 hasil indeks yang didapatkan tetap sama. Ini akan diubah pada perubahan rumus selanjutnya.
   
   
10. Rubah cell I4 dengan rumus berikut.
    
    =IF(H4>1,MATCH(G4,INDIRECT("C"&I3+1&":C16"),0)+I3,MATCH(G4,$C$4:$C$16,0))
    
    Copy kembali rumus tersebut ke cell I5 s/d I13. Hasilnya terlihat sebagai berikut.
    
    
    
    Penjelasan: Karena untuk kemunculan kedua dan seterusnya diperlukan pengecekan tambahan, maka kita gunakan IF pada formula ini. Untuk kemunculan pertama rumusnya sama dengan sebelum perubahan. Tapi untuk kemunculan kedua dan seterusnya, kita akan mengambil range lookup yang dimulai dari penemuan pada baris sebelumnya (lihat illustrasi di bawah).
    
    
11. Akhirnya kita akan menggunakan fungsi Indirect untuk mengambil Pada cell F4 masukkan rumus berikut.
    
    =INDIRECT("B"&(I4+3))
    
    Copy rumus tersebut ke cell F5 s/d F13. Hasilnya tampak sebagai berikut.
    
    
12. Selesai.

Demikian proses membuat top 10 nilai dengan menggunakan formula tanpa VBA (Visual Basic for Application) programming. 

Namun, pertanyaan yang mungkin ada di benak Anda adalah kenapa kita menggunakan tambahan dua kolom yaitu Occurence dan Match. Mungkinkah kita tidak menggunakan kedua kolom tersebut?

Jawabannya iya. 

Jadi kalau kita hapus kolom H (Occrence) dan I (Match) maka rumus untuk F4 Anda ganti menjadi sebagai berikut.

=INDIRECT("B"&(IF(COUNTIF($G$4:G4,G4)>1,MATCH(G4,INDIRECT("C"&MATCH(G4,$C$4:$C$16,0)+1&":C16"),0)+MATCH(G4,$C$4:$C$16,0),MATCH(G4,$C$4:$C$16,0))+3))

Cukup panjang ya? Copy rumus tersebut ke F5 s/d F13, dan Anda akan dapatkan hasil berikut ketika kita menghapus kedua kolom tambahan tersebut.