walaupun kasusnya dah selesai, ikutan ah... kayanya asik buat bahan dongeng pasca idul fitri nih... :)
Saran : Andai ada kebingungan dengan beberapa formula tentang pemeringkat data, bisa jalan-jalan kesini <http://excel-mr-kid.blogspot.co.id/2012/05/pemeringkatan-data-ranking.html> . Pinjam formula mas Heri (fokus pada yang diblok kuning dulu ya) =IF(J6>0;RANK(J6;$J$6:$J$21;0);RANK(J6;$J$6:$J$21;1)) Jika J6 positif, maka urutkan descending Jika J6 bukan positif (yaitu negatif atau 0), maka urutkan ascending oh iya, sebelum lupa... mas Heri menggunakan regional setting Indonesian (ada karakter ; dalam formula), jadi seluruh formula dalam dongeng ini juga akan menggunakan setting Indonesian. Kalau komputer setempat pakai setting English, ganti karakter ; menjadi , ya... KECUALI ; yang berwarna biru dan tebal Dongengnya mulai disini.... Kita ubah dikit, supaya nilai 0 tidak jadi peringkat 1 diarea nilai-nilai kolom J yang negatif atau nol (bukan positif). Formulanya begini : =IF(*J6<0*;RANK(J6;$J$6:$J$21;1);RANK(J6;$J$6:$J$21;0)) Jika J6 negatif, maka urutkan ascending -> 0 bukanlah nilai negatif, jadi pasti peringkat ke-1 dari nilai-nilai J yang negatif pastilah bernilai negatif Jika J6 bukan negatif (positif atau nol), maka urutkan descending, jadi pasti peringkat ke-1 dari nilai-nilai J yang positif atau nol adalah bernilai positif Jadi, nilai J6 yang nol akan mendapat peringkat rendah (peringkat tertinggi adalah peringkat ke-1) Sekarang, dari formula yang sudah sedikit diperbaiki di atas, yaitu yang berbunyi : =IF(*J6<0*;RANK(J6;$J$6:$J$21;1);RANK(J6;$J$6:$J$21;0)) bisa disederhanakan menjadi : =RANK(J6;$J$6:$J$21;*J6<0*) Jika J6 negatif, maka urutkan ascending (kode pengurutan angka 1 alias setara dengan TRUE yang digantikan oleh bagian *J6<0*) Jika J6 bukan negatif (yaitu positif atau nol), maka urutkan descending (kode pengurutan 0 alias setara dengan FALSE yang digantikan oleh bagian *J6<0*) Hasilnya, Nilai positif tertinggi akan bernilai peringkat 1 Nilai negatif tertinggi akan bernilai peringkat 1 juga Andai di kolom J ada 15 records positif, 15 records negatif, dan 0 record bernilai 0 (tidak ada nilai 0) maka peringkat 1 sampai 15 akan dimiliki oleh bagian nilai-nilai yang positif maupun yang negatif. Permasalahan berikutnya : Bagaimana mengambil nilai yang positif saja dari peringkat 1 sampai 10 ? >> tentu butuh kolom lain sebagai kriteria, misalnya berdasar kolom i yang berisi teks BUY dan SELLER >> artinya, butuh array formula filtering untuk menyusun top 10 BUY sebagai 1 output dan top 10 SELLER sebagai 1 output yang lain >> coba dibayangkan bila ada banyak sekali records yang harus diolah dengan rank lalu masih perlu diolah dengan array formula filtering di tempat lain >> tentulah kurang efisien dan mungkin akan terasa berat di suatu saat nanti So, solusinya bagaimana ? Salah satu alternatifnya adalah menambahkan penanda pada nilai peringkat hasil formula rank di atas. Misal, BUY adalah seluruh data peringkat dengan jumlah digit 3, dimana 2 digit terakhir adalah nilai peringkatnya. Untuk SELLER adalah seluruh data peringkat dengan jumlah digit 4, dimana 2 digit terakhir adalah nilai peringkatnya. Contoh : 101 adalah peringkat ke-1 dari BUY 1001 adalah peringkat ke-1 dari SELLER Dengan cara ini, maka formula penyusun top 10 BUY ataupun top 10 SELLER akan menjadi lebih sederhana. Formula penyusun peringkatnya menjadi : =IF( i6="SELLER" ; 1000 ; 100 ) + RANK( J6 ; $J$6:$J$21 ; *J6<0 *) Yang dilatar hijau bisa diubah agar menggunakan kolom J menjadi : =IF( *J6<0* ; 1000 ; 100 ) + RANK( J6 ; $J$6:$J$21 ; *J6<0 *) hehehe... masih bisa disederhanakan menjadi : =IF( *J6<0* ; 10 ; 1 ) * 10^2 + RANK( J6 ; $J$6:$J$21 ; *J6<0 *) (ingat, angka 2 adalah ketentuan awal bahwa 2 digit terakhir sebagai nilai peringkatnya) Jadi, didapat formula : =IF( *J6<0* ; 10 ; 1 ) * 10^2 + RANK( J6 ; $J$6:$J$21 ; *J6<0 *) Mungkin akan muncul masalah baru, ketika record datanya positif melebihi 99 records... Tentu dengan menambahkan angka 0 dibelakang 100 dan 1000 bisa menyelesaikannya. Contoh, bila nilai positif ada *9999* records dan nilai negatif ada *9999* records juga, berarti butuh *4* digit untuk nilai peringkat. Berarti, perlu penambahan 2 angka 0 (ingat, tadi nilai peringkat diset 2 digit terakhir dan sekarang harus jadi 4 digit terakhir). Formulanya bisa menjadi : =IF( *J6<0* ; 10 ; 1 ) * 10^*4* + RANK( J6 ; $J$6:$J$21 ; *J6<0 *) Jadi, Anda bisa dengan mudah menyesuaikannya dengan kebutuhan setempat kan ya... Gak seru kalau masalahnya terus berhenti sampai disini... Tambahlah ah masalahnya... Masalah berikutnya, terkadang, ada 2 records yang nilainya sama persis, dan harusnya nomor peringkatnya tetap urut dan unique (supaya formula penyusun top 10 BUY dan top 10 SELLER tetap sederhana) Berarti, perlu menggunakan formula pemeringkat yang unique, seperti dengan bantuan fungsi CountIF Jadi, formula yang tadinya berbunyi : =IF( *J6<0* ; 10 ; 1 ) * 10^2 + RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 Dari formula di atas, bagian penyusun nilai peringkatnya adalah RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 bagian penyusun kode peringkat berdasar BUY dan SELLER adalah IF( *J6<0* ; 10 ; 1 ) * 10^2 + Selesai deh.... xixixixi... setelah satu urusan selesai, mulailah menyelesaikan urusan lain dengan lebih baik lagi... Nah... urusan berikutnya : Berapapun jumlah records nya, hanya 12 records BUY dan 12 records SELLER saja yang diberi peringkat. Jadi, records sisanya tidak usah diberi peringkat. hmmm... ini dia.. berarti masih tetap butuh 2 digit terakhir sebagai nomor peringkat, karena 12 adalah bilangan 2 digit. anggap saja masih butuh unique walau ada angka yang sama. Jadi, formula yang akan disesuaikan adalah tetap yang berbunyi : =IF( *J6<0* ; 10 ; 1 ) * 10^2 + RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 Masih ingatkan kalau bagian penyusun peringkatnya adalah bagian : RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 Supaya bagian penyusun peringkat di atas menghasilkan maksimal 12 saja, dan sisanya adalah terkesan kosong, maka bagian tersebut diubah menjadi begini : IF( RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 < 13 ; RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 ; "" ) waaa...... formulanya dah mulai jadi kereta nih... lengkapnya begini formula tersebut : =IF( *J6<0* ; 10 ; 1 ) * 10^2 + IF( RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 < 13 ; RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 ; "" ) hiii... ngeri dah... bakal puyeng nih... kalo gitu disederhanakan saja ya... fokus ke bagian ini saja lebih dulu... IF( RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 < 13 ; RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 ; "" ) Bagian ini yang kita sederhanakan lebih dulu... Ingat ulang ya... pengennya hanya sampai peringkat 12 saja, yang setelahnya diberi kesan kosong alias ndak usah ada peringkatnya... Sekarang, coba digali-gali dalam ingatan... Adakah disana tersimpan sesuatu yang sesuatu banget bernama Format Number ? >> yaiy... ada.. ada.. >> Good lah kalo begitu Kalau ada Format Number, berarti disampingnya ada asesoris berupa fungsi yang bernama TEXT kan ya ? >> heeh.. heeh.. Kita manfaatkan si fungsi TEXT ya... Text( RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 ; "[<=12]0*;*" ) Bagian [<=12]0*;* adalah format number custom nya artinya, kalau nilai yang akan di-format number itu <= 12, maka diformat dengan menampilkan bilangan apa adanya dan selain itu (karakter *;*), tidak ditampilkan Jadi, formula lengkapnya bisa menjadi : =IF( *J6<0* ; 10 ; 1 ) * 10^2 *+* Text( RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 ; "[<=12]0*;*" ) Masalahnya, akibat penjumlahan oleh tanda plus yang gede tebel item itu, akan membuat error value kalau hasil peringkat adalah di atas 12. Oleh sebab itu perlu dinetralisir... dan... fungsi IFError bisa digunakan.... Formulanya menjadi : =IFError( IF( *J6<0* ; 10 ; 1 ) * 10^2 *+* Text( RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 ; "[<=12]0*;*" ) ; "" ) tada... inilah formula akhirnya : =IFError( IF( *J6<0* ; 10 ; 1 ) * 10^2 *+* Text( RANK( J6 ; $J$6:$J$21 ; *J6<0 *) + CountIF( J$6:J6 ; J6 ) - 1 ; "[<=12]0*;*" ) ; "" ) Akhirnya, untuk sementara, si formula bisa hidup tenang dan bahagia digunakan untuk menyusun top 10 BUY maupun top 10 SELLER the end... atau to be continued ya ? hehehe... Berhubung lagi baik hati, disertakanlah contoh filenya... lengkap dengan tambahan beberapa records untuk mempermudah mencapai puncak puyengnya... met berpuyeng ria Wassalamu'alaikum Kid 2016-07-21 15:57 GMT+07:00 'Heri Pamungkas ( Gmail )' [email protected] [belajar-excel] <[email protected]>: > > > > السَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ > Semangat pagi,. > > Sambil menunggu para Suhu sementara bisa menggunakan : > > =IF(J6>0;RANK(J6;$J$6:$J$21;0);RANK(J6;$J$6:$J$21;1)) > > di Cell M6 > > > وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ > > Warm Regards, > > On 21/07/2016 14:45, [email protected] [belajar-excel] wrote: > > > > Selamat siang rekan belajar Excel, > > Mohon bantuan atas kesulitan yang sedang saya kerjakan. File lampiran > terlampir. > Atas bantuannya saya ucapkan terima kasih. > > Ronald > > > > >
re-HOTSExcel.xlsx
Description: MS-Excel 2007 spreadsheet
