Muatan :
- Kilasan tentang pemeringkatan data
- Macam-macam pemeringkatan data
- Formula berdasar macam-macam pemeringkatan data
Kilasan
Pemeringkatan data adalah proses untuk mengurutkan data berdasar suatu kriteria tertentu, yang diikuti proses pemberian nomor peringkat data. Peringkat data adalah hubungan sebuah data dengan data yang lain yang sama-sama memenuhi suatu kriteria.Misal ada sebuah data berdasar suatu kriteria bernilai {95,63,58,63}. Pemeringkatan data secara menaik (ascending) akan menghasilkan {(58,1),(63,2),(63,2),(95,3)}, yang artinya bahwa setiap data diurutkan secara ascending dan diberi peringkat data. Peringkat data 95 adalah 3 yang artinya nilai 95 adalah terendah (diurutkan ascending) ke-3 dari 4 record data.
Penetapan peringkat data untuk data yang bernilai sama sangat mungkin berbeda antar kasus. Misalnya setiap data harus mendapatkan peringkat data yang unik. Pembahasan kali ini akan difokuskan pada beberapa macam pemeringkatan data yang sering digunakan dengan menggunakan formula.
Macam-macam pemeringkatan data
Beberapa cara pemeringkatan data yang sering digunakan, terutama perilaku pemeringkatan terhadap data dengan nilai yang sama, adalah sebagai berikut :- Standard competition (1 2 2 4)
- Data dengan nilai yang sama akan mendapatkan peringkat yang sama
- Adanya peringkat data yang sama akan membuat hilangnya nomor-nomor peringkat yang mestinya dimiliki oleh nilai-nilai data yang sama tersebut
- Nomor peringkat yang hilang tersebut sebanyak jumlah cacah nilai-nilai data yang sama dikurangi satu
- Fungsi Rank di Excel menggunakan cara pemeringkatan jenis ini
- Data {58,63,63,95} akan mendapat peringkat {1,2,2,4}
- Jumlah nilai data 63 ada 2 record, maka akan terjadi 2 - 1 = 1 nomor peringkat yang tidak digunakan setelah nomor peringkat untuk data 63
- Nilai data 63 memiliki peringkat nomor 2 secara menaik (ascending), maka peringkat nomor 2 + 1 = 3 tidak digunakan
- Modified competition (1 3 3 4)
- Mirip dengan Standard competition
- Perbedaannya terletak pada pemberian nomor peringkat untuk data yang memiliki nilai sama
- Cara ini akan memberikan nomor peringkat yang harusnya dimiliki oleh nilai data yang sama yang terakhir ditemukan
- Data {58,63,63,95} akan mendapat peringkat {1,3,3,4}
- Nilai 63 yang ada 2 record harusnya mendapat peringkat 2 dan 3
- Maka nomor peringkat dari nilai 63 yang terakhir ditemukan yang digunakan, yaitu 3
- Nomor peringkat 2 tidak digunakan
- Dapat diformulasikan dengan nomor peringkat menurut standard competition ditambah banyaknya (jumlah cacah) nilai data yang diperingkatkan tersebut, kemudian dikurangi satu
- Ordinal (1 2 3 4)
- Setiap data akan mendapatkan nomor peringkat yang bersifat unik, meskipun memiliki nilai data yang sama
- Nilai data yang sama yang ditemui lebih dulu akan diberi nomor peringkat lebih dulu
- Nilai data yang sama yang ditemui berikutnya akan diberi nomor peringkat sebesar 1 lebih dari nilai data yang sama sebelumnya
- Data {58,63,63,95} akan mendapat peringkat {1,2,3,4}
- Dapat diformulasikan dengan nomor peringkat menurut standard competition ditambah banyaknya (jumlah cacah) nilai data yang ditemui sampai saat itu, kemudian dikurangi satu
- Fractional (1 2.5 2.5 4)
- Mirip dengan Standard competition
- Perbedaannya terletak pada pemberian nomor peringkat untuk data yang memiliki nilai sama
- Nilai-nilai data yang sama akan mendapatkan nomor peringkat sebesar rerata dari nomor-nomor peringkat ordinal dari nilai-nilai yang sama
- Data {58,63,63,63,63} akan mendapat peringkat {1,3.5,3.5,3.5,3.5}
- Nilai data 63 ada 4 item, yang harusnya mendapat nomor peringkat ordinal 2,3,4,5
- Rerata dari 2,3,4 adalah ( 2 + 3 + 4 + 5) / 4 = 14 / 4 = 3.5
- Dapat diformulasikan dengan setengah dari satu lebih sedikit dari banyaknya nilai data yang sama, ditambahkan ke nomor peringkat nilai data tersebut menurut standard competition
- Dense (1 2 2 3)
- Nilai data yang sama akan mendapatkan nomor peringkat yang sama
- Nilai data berikutnya, yang tidak sama, akan mendapatkan nomor peringkat berikutnya
- Tidak ada nomor peringkat yang tidak digunakan, alias selalu berurutan, tetapi boleh tidak unik
- Data {58,63,63,95} akan mendapat peringkat {1,2,2,3}
- Dapat diformulasikan dengan mencari posisi data di sebuah daftar unik nilai data yang telah terurut sesuai kebutuhan
Formula berdasar macam-macam pemeringkatan data
Penyusunan peringkat data menggunakan Excel untuk berbagai macam cara pemeringkatan data di atas dapat dilakukan dengan memahami karakteristik setiap macam pemeringkatan. Excel menyediakan fungsi Rank yang cara pemeringkatannya sebagai standard competition. Beberapa macam yang telah dijelaskan di atas memanfaatkan hasil cara standard competition yang kemudian dikomputasi lebih lanjut untuk memenuhi karakteristik macam tersebut.Data berikut ini akan digunakan pada pembahasan lebih lanjut untuk memahami alur penyusunan formula, macam pemeringkatan, dan mendapatkan gambaran hasilnya.
Kolom yang akan diperingkatkan adalah kolom composite key. Anggap saja data sesungguhnya memiliki sekian banyak kriteria dan telah tersusun composite key-nya, sehingga akan tampak seperti pemeringkatan dengan satu kriteria saja.
Formula setiap macam pemeringkatan yang telah dijelaskan di atas akan diletakkan pada baris ke-4 Excel, untuk mencari peringkat data dari nilai di cell B4. Untuk memahami alur kalkulasi didalam formula yang telah tersusun nantinya, akan digunakan data di cell B8. Pembahasan akan menggunakan proses pemeringkatan dengan pengurutan menurun (descending).
- Standard competition (1 2 2 4)
- Cara ini adalah yang digunakan oleh fungsi rank Excel. Maka formula untuk cara ini adalah menggunakan fungsi rank dengan pengurutan descending.
- Formulanya adalah
- =Rank( $B4 , $B$4:$B$13 )
- Formula diletakkan pada cell C4
- Untuk data di cell B8, maka formula menjadi
- =Rank( B8 , $B$4:$B$13 )
- Modified competition (1 3 3 4)
- Dapat diformulasikan dengan nomor peringkat menurut standard competition ditambah banyaknya (jumlah cacah) nilai data yang diperingkatkan tersebut, kemudian dikurangi satu
- Formula menurut cara standard competition
- =Rank( B4 , $B$4:$B$13 )
- Banyaknya record yang bernilai sama dengan data yang diolah (B4) diformulasikan sebagai
- +CountIF( $B$4:$B$13 , $B4 )
- +CountIF( $B$4:$B$13 , $B4 ) -1
- Formulasi lengkap yang diletakkan di cell D4 akan menjadi
- =Rank( $B4 , $B$4:$B$13 ) +CountIF( $B$4:$B$13 , $B4 ) -1
- Proses kalkulasi terhadap data di B8 akan menghasilkan nilai di D8, dengan kejadian setiap proses di dalam cell D8 adalah sebagai berikut
- Ordinal (1 2 3 4)
- Dapat diformulasikan dengan nomor peringkat menurut standard competition ditambah banyaknya (jumlah cacah) nilai data yang ditemui sampai saat itu, kemudian dikurangi satu
- Formula menurut cara standard competition
- =Rank( $B4 , $B$4:$B$13 )
- Banyaknya record yang bernilai sama dengan data yang diolah (B4) yang ditemui sampai saat itu diformulasikan sebagai
- +CountIF( $B$4:$B4 , $B4 )
- +CountIF( $B$4:$B4 , $B4 ) -1
- Formulasi lengkap yang diletakkan di cell E4 akan menjadi
- =Rank( $B4 , $B$4:$B$13 ) +CountIF( $B$4:$B4 , $B4 ) -1
- Proses kalkulasi terhadap data di B8 akan menghasilkan nilai di E8 adalah proses kalkulasi terhadap formula
- =Rank( $B8 , $B$4:$B$13 ) +CountIF( $B$4:$B8 , $B8 ) -1
- Fractional (1 2.5 2.5 4)
- Dapat diformulasikan dengan setengah dari satu lebih sedikit dari banyaknya nilai data yang sama, ditambahkan ke nomor peringkat nilai data tersebut menurut standard competition
- Formula menurut cara standard competition
- =Rank( $B4 , $B$4:$B$13 )
- Banyaknya record yang bernilai sama dengan data yang diolah (B4) diformulasikan sebagai
- +CountIF( $B$4:$B$13 , $B4 )
- +CountIF( $B$4:$B$13 , $B4 ) -1
- + ( CountIF( $B$4:$B$13 , $B4 ) -1 ) / 2
- Formulasi lengkap yang diletakkan di cell F4 akan menjadi
- =Rank( $B4 , $B$4:$B$13 ) + ( CountIF( $B$4:$B$13 , $B4 ) -1 ) / 2
- Proses kalkulasi terhadap data di B8 akan menghasilkan nilai di F8, dengan kejadian setiap proses di dalam cell F8 adalah sebagai berikut
- Dense (1 2 2 3)
- Dapat diformulasikan dengan mencari posisi data di sebuah daftar unik nilai data yang telah terurut sesuai kebutuhan
- Langkah penyusunan formula adalah sebagai berikut :
- Step 1 : memberi tanda pada setiap nilai yang baru pertama kali ditemui ketika diturut dari record pertama
- Proses ini dilakukan dengan memanfaatkan fungsi Frequency yang memiliki karakteristik tidak akan mengkalkulasi untuk nilai batas yang sama
- Proses dilakukan sebagai array formula, dengan input data array dan bins array untuk fungsi Frequency adalah seluruh data kolom composite key
- Array formula tersebut adalah
- Frequency( $B$4:$B$13 , $B$4:$B$13 )
- Hasilnya berupa array 10 baris x 1 kolom, dengan nilai :
- 0 untuk nilai data yang kedua kali ditemukan sama
- lebih dari 0 untuk nilai data yang belum pernah ditemukan
- Step 2 : memilih hanya data yang ditemukan pertama kali untuk setiap nilai data
- Proses ini adalah pemilihan nilai data, maka fungsi IF dapat digunakan
- Fungsi IF akan berisi kondisi berdasar hasil Step 1 yang lebih dari 0 saja
- Bentuk fungsi IF untuk proses ini adalah
- IF( Frequency( $B$4:$B$13 , $B$4:$B$13 ) , $B$4:$B$13 )
- Hasilnya berupa array 10 baris x 1 kolom, karena diberi input berupa hasil array formula, dengan nilai :
- Bertipe Boolean bernilai FALSE jika hasil Step 1 bernilai 0
- Berupa nilai dari kolom composite key jika hasil Step 1 bernilai lebih dari 0
CATATAN : Sampai dengan Step 2, susunan array masih mengikuti susunan dikolom composite key - Step 3 : menyusun dimensi array hasil sekaligus menentukan urutan data yang akan diambil dari fungsi pengurut data
- Pengurutan akan dilakukan terhadap hasil array formula di Step 2 yang maksimal sebanyak 10 baris untuk kasus ini
- Pengurutan akan dilakukan menggunakan fungsi Large, karena dibutuhkan pengurutan secara menurun (descending)
- Formula pembentuk dimensi array hasil pengurutan dapat menjadi inputan untuk input parameter k dari fungsi Large
- Formula tersebut adalah
- Row($1:$10)
- ( Row( $B$4:$B$13 ) - Row( $B$3 ) )
- Hasil berupa array 10 baris x 1 kolom yang berisi deret angka dari 1 sampai dengan 10
- Step 4 : pengurutan data secara descending
- Pengurutan menggunakan fungsi Large
- Data yang diurutkan adalah array hasil Step 2
- Input parameter k diisi dengan hasil array formula Step 3
- Susunan array formula pengurutan adalah
- Large( IF( Frequency( $B$4:$B$13 , $B$4:$B$13 ) , $B$4:$B$13 ) , Row($1:$10) )
- Hasil berupa array formula 10 baris x 1 kolom, dengan nilai :
- Terurut menurun (descending) untuk nilai yang bertipe numerik
- Error value #NUM! untuk yang bertipe Boolean dengan nilai FALSE
CATATAN : Sampai dengan Step 4, susunan array hasil telah terurut sesuai kebutuhan, tetapi harus di-input sebagai array formula - Step 5 : mengubah cara input formula, dari array formula menjadi formula bukan array
- Fungsi Index dengan input jumlah data, baik jumlah baris atau jumlah kolom yang dirujuk, yang diisi dengan 0 berarti mengambil seluruh refernsi yang ada
- Hasil fungsi Index yang berupa referensi akan membuat array hasil Step 4 sebagai sebuah referensi array, bukan sebagai list array
- Formula pada Step 4 akan menjadi
- Index( Large( IF( Frequency( $B$4:$B$13 , $B$4:$B$13 ) , $B$4:$B$13 ) , Row($1:$10) ) , 0 )
- Hasil akan berupa referensi array 10 baris x 1 kolom dengan nilai seperti hasil Step 4, tetapi formula bukan array formula. Jadi cukup menekan ENTER saja ketika usai menulis formula
CATATAN : Mulai dari Step 5, formula tidak perlu di-ENTER sebagai array formula - Step 6 : mencari nomor peringkat data sebuah nilai (data cell B4) pada hasil Step 5
- Hasil Step 5 yang telah terurut sesuai kebutuhan, yaitu descending
- Maka nomor peringkat dapat diartikan juga sebagai posisi suatu nilai data (data cell B4) pada hasil Step 5
- Fungsi Match dapat digunakan untuk keperluan pencarian posisi data
- Opsi cara pencarian dengan fungsi Match adalah sama persis yang setara dengan meng-input nilai 0 pada input parameter match_type
- Parameter lookup_array diisi dengan formula Step 5
- Parameter lookup_value diisi dengan referensi merujuk nilai data, yaitu cell B4
- Formula mencari nomor peringkat adalah
- Match( $B4 , Index( Large( IF( Frequency( $B$4:$B$13 , $B$4:$B$13 ) , $B$4:$B$13 ) , Row($1:$10) ) , 0 ) , 0 )
- Step 1 : memberi tanda pada setiap nilai yang baru pertama kali ditemui ketika diturut dari record pertama
- Formula lengkap pemeringkatan cara Dense yang akan diletakkan pada cell G4 adalah
- =Match( $B4 , Index( Large( IF( Frequency( $B$4:$B$13 , $B$4:$B$13 ) , $B$4:$B$13 ) , Row($1:$10) ) , 0 ) , 0 )
- Proses kalkulasi yang terjadi di dalam cell G8 untuk mengolah data B8 adalah sebagai berikut
Hasil lengkap setiap cara untuk proses yang dijabarkan di atas, yaitu untuk pengurutan menurun (descending) adalah
Untuk proses pengurutan secara menaik (ascending), maka harus mengubah hal-hal berikut ini :
- Seluruh opsi pengurutan (parameter [order] pada formula yang menggunakan fungsi Rank harus diisi dengan 1
- Pengurutan dengan cara Dense harus mengganti fungsi Large dengan fungsi Small
Untuk memudahkan perbandingan setiap cara, baik pada pengolahan dengan pengurutan menurun (descending) atau menaik (ascending), bisa diamati melalui gambar berikut ini
Closing :
Pemilihan cara pemeringkatan sangat spesifik tergantung kasusnya. Cara pengurutan data sebelum proses pemeringkatan juga mempengaruhi bentuk formula yang harus digunakan.
File(s) :
Coretan terkait :
- Susun data sebagai tabel yang baik. Kenapa ?
- Array Formulas. Kenalan yuuk !
- Formula peringkat data (satu kriteria)
- Formula peringkat data (multi kriteria 01)
membuat ranking excel mudah ya
BalasHapusartikel nya sangat bermanfaat
terima kasih
terima kasih
BalasHapussangat membantu
semoga mendapat pahala kebaikan
Terimakasih banyak , artikelnya sangat membantu pekerjaan saya
BalasHapusluar biasa.
BalasHapussinurat.