Formula peringkat data (Rank multi kriteria 02)

---

Muatan :

• Kilasan tentang penyusunan composite key
• Contoh kasus
• Penyusunan Composite Key
• Rank dengan multi kriteria

---

Kilasan

Pada coretan sebelumnya, yaitu Formula peringkat data (multi kriteria 01) telah diketahui bahwa kebutuhan terhadap composite key bergantung pada jumlah kriteria pemeringkatan. Sedangkan penyusun composite key tersebut sangat spesifik tergantung karakteristik setiap kolom kriteria pemeringkatan. Penyusunan composite key dengan susunan aaa ... zzz yang bertipe numerik terbatasi oleh jumlah digit presisi maksimal sebuah cell yang sebanyak 15 digit. Dengan begitu, akan terbuka kemungkinan terjadinya beberapa kolom composite key agar bisa menghasilkan sebuah kolom composite akhir yang benar-benar mewakili seluruh kriteria pemeringkatan.



Pembahasan kali ini adalah tentang pemeringkatan berdasar banyak kriteria yang susunan composite key-nya tidak memungkinkan untuk disusun sebagai satu composite key dengan satu kali proses. Jika belum memahami tentang composite key dan cara sederhana untuk menyusunnya, silakan membaca coretan Formula peringkat data (multi kriteria 01).

---

Contoh kasus

Pembahasan kali ini akan menggunakan sebuah data nilai ujian proses seleksi pegawai baru. Total peserta ujian adalah 10.000 (sepuluh ribu) orang yang dilaksanakan serentak di 20 kota. Setiap peserta akan mendapatkan sebuah ID ketika mengumpulkan lembar jawaban. ID tersebut bersifat unik.

Jumlah item penilaian ada 3 sisi dan dinyatakan sebagai kolom Value1, Value2, Value3. Kolom-kolom value ini bisa berisi nilai dari 0 sampai 10.000 (sepuluh ribu) dengan jumlah digit dibelakang koma adalah sebanyak 4 digit. Kemudian seluruh nilai kolom-kolom Value direratakan menjadi sebuah nilai akhir.

Data tersebut adalah sebagai berikut :

Kriteria pemeringkatannya adalah :

1. Nilai rerata hasil ujian seleksi (kolom rerata) secara menurun (descending)
2. Nilai materi pertama ujian (kolom Value1) secara menurun (descending)
3. Nilai materi pertama ujian (kolom Value2) secara menurun (descending)
4. Nilai materi pertama ujian (kolom Value3) secara menurun (descending)
5. ID pengumpulan lembar jawaban oleh peserta ujian (kolom rec_ID) secara menaik (ascending)

Kasus yang akan diselesaikan adalah :

1. Peringkat keberapa tertinggikah peserta dengan ID 09159 dari seluruh peserta ujian seleksi ?
2. Susunlah data seluruh peserta ujian seleksi disertai peringkat masing-masing yang terurut dari tertinggi ke terendah sesuai kriteria pemeringkatan yang ditetapkan !

---

Penyusunan Composite Key

Untuk menyusun composite key data pemeringkatan perlu dilakukan penjabaran karakteristik data secara lebih detil. Gambaran karakteristiknya adalah sebagai berikut :

1. Peserta ujian
   
   • Jumlah peserta ujian adalah 10.000 (sepuluh ribu), yang berarti membutuhkan 5 digit.
2. Nilai rerata
   
   • Sebagai kriteria pertama dan merupakan hasil suatu proses komputasi.
   • Jumlah digit dari nilai-nilai dalam kolom ini akan bervariasi dan akan lebih dari 5 digit.
   • Composite key berdasar kolom ini akan berupa hasil komputasi dengan fungsi rank yang diurutkan descending, sehingga maksimal hanya dibutuhkan 5 digit saja.
   • Formula penyusunan composite key berdasar kolom ini adalah :
     
     Rank( E4 , $E$4:$E$13 )
     dengan opsi cara pengurutan descending adalah menggunakan nilai defaultnya.
3. Nilai Value1 sampai dengan Value3
   
   • Setiap kolom value berisi nilai dari 0 sampai 10.000 (sepuluh ribu) dengan 4 digit dibelakang koma, yang berarti akan membutuhkan 9 digit.
   • Composite key berdasar kolom ini akan berupa hasil komputasi dengan fungsi rank yang diurutkan descending, sehingga maksimal hanya dibutuhkan 5 digit saja.
   • Formula penyusunan composite key berdasar kolom-kolom Value adalah :
     
     • Kolom Value1 (kolom B)
       
       Rank( B4 , $B$4:$B$13 )
     • Kolom Value2 (kolom C)
       
       Rank( C4 , $C$4:$C$13 )
     • Kolom Value3 (kolom D)
       
       Rank( D4 , $D$4:$D$13 )
4. ID peserta (rec_id)
   
   • Terdiri dari 5 digit tetap dan bersifat unik.
   • Kolom ini menjadi kriteria ke-5 dari pemeringkatan dan akan diurutkan secara ascending.
   • Formula penyusunan composite key berdasar kolom ini adalah :
     
     Rank( A4 , $A$4:$A$13 , 1 )
5. Susunan composite key
   
   • Jumlah digit seluruh kriteria adalah 25 digit, hasil penjumlahan seluruh digit yang dibutuhkan composite key berdasar setiap kolom kriteria.
   • Batas jumlah digit maksimal sebuah cell di Excel agar tetap presisi adalah sebanyak 15 digit.
   • Composite key berdasar setiap kriteria perlu dipecah menjadi 2 (dua) bagian, yaitu :
     
     • Composite Key 1.1
       
       • Berisi 5 digit berdasar kolom rerata, 5 digit berdasar kolom Value1, 5 digit berdasar kolom Value2
       • Susunannya berupa xxxxx yyyyy zzzzz yang disusun dengan cara :
         
         1. Meletakkan formula berdasar kriteria pertama (kolom rerata) untuk menyusun bagian xxxxx
            
            =Rank( E4 , $E$4:$E$13 )
         2. Membentuk ruang untuk 5 digit berdasar kriteria kedua (kolom Value1), hingga terbentuk susunan xxxxx 00000, dengan mengalikan hasil nomor 1 dengan 10^5
            
            *10^5
            dan bentuk formula nomor 1 akan menjadi
            
            =Rank( E4 , $E$4:$E$13 ) *10^5
         3. Memasukkan nilai composite berdasar kriteria kedua (kolom Value1) hingga susunan berubah dari xxxxx 00000 menjadi xxxxx yyyyy dengan menjumlahkan hasil nomor 2 dengan formula composite berdasar kolom Value1.
            
            +Rank( B4 , $B$4:$B$13 )
            dan bentuk formula nomor 2 akan menjadi
            
            =Rank( E4 , $E$4:$E$13 ) *10^5+Rank( B4 , $B$4:$B$13 )
         4. Membentuk ruang untuk 5 digit berdasar kriteria ketiga (kolom Value2), hingga terbentuk susunan xxxxx yyyyy 00000, dengan mengalikan hasil nomor 3 dengan 10^5
            
            *10^5
            Karena hasil nomor 3 memiliki proses penjumlahan didalamnya, maka hasil nomor 3 tersebut harus ada dalam tanda kurung terlebih dulu sebelum dikalikan dengan 10^5 pembentuk ruang 5 digit baru. Bentuk formula nomor 3 akan menjadi
            
            =( Rank( E4 , $E$4:$E$13 ) *10^5+Rank( B4 , $B$4:$B$13 ) ) *10^5
         5. Memasukkan nilai composite berdasar kriteria ketiga (kolom Value2) hingga susunan berubah dari xxxxx yyyyy 00000 menjadi xxxxx yyyyy zzzzz dengan menjumlahkan hasil nomor 4 dengan formula composite berdasar kolom Value2.
            
            +Rank( C4 , $C$4:$C$13 )
            dan bentuk formula nomor 4 akan menjadi
            
            =( Rank( E4 , $E$4:$E$13 ) *10^5+Rank( B4 , $B$4:$B$13 ) ) *10^5+Rank( C4 , $C$4:$C$13 )
            yang bisa disederhanakan menjadi
            
            =Rank( E4 , $E$4:$E$13 ) *10^10+Rank( B4 , $B$4:$B$13 ) *10^5+Rank( C4 , $C$4:$C$13 )
       • Formula diletakkan pada kolom F mulai baris ke-4 Excel (cell F4). Kemudian di-copy ke baris data berikutnya.
     • Composite Key 1.2
       
       • Berisi 5 digit berdasar kolom Value3, 5 digit berdasar kolom rec_id
       • Susunannya berupa xxxxx yyyyy yang disusun dengan cara :
         
         1. Meletakkan formula berdasar kriteria keempat (kolom Value3) untuk menyusun bagian xxxxx
            
            =Rank( D4 , $D$4:$D$13 )
         2. Membentuk ruang untuk 5 digit berdasar kriteria kelima (kolom rec_id), hingga terbentuk susunan xxxxx 00000, dengan mengalikan hasil nomor 1 dengan 10^5
            
            *10^5
            dan bentuk formula nomor 1 akan menjadi
            
            =Rank( D4 , $D$4:$D$13 ) *10^5
         3. Memasukkan nilai composite berdasar kriteria kelima (kolom rec_id) hingga susunan berubah dari xxxxx 00000 menjadi xxxxx yyyyy dengan menjumlahkan hasil nomor 2 dengan formula composite berdasar kolom rec_id.
            
            +Rank( A4 , $A$4:$A$13 , 1 )
            dan bentuk formula nomor 2 akan menjadi
            
            =Rank( D4 , $D$4:$D$13 ) *10^5+Rank( A4 , $A$4:$A$13 , 1 )
       • Formula diletakkan pada kolom G mulai baris ke-4 Excel (cell G4). Kemudian di-copy ke baris data berikutnya.
   • Bentuk composite key berdasar setiap kriteria pemeringkatan yang masih berupa 2 (dua) item tersebut adalah :
     
     
     Composite Key berdasar setiap kriteria pemeringkatan ini dapat disebut sebagai Composite Key level 1.
     




6. Composite key akhir
   
   • Composite key berdasar setiap kriteria yang dibangun pada nomor 5, masih terdiri dari 2 (dua) item composite, yaitu Composite Key 1.1 dan Composite Key 1.2.
   • Dari 2 (dua) composite key level 1 (berdasar setiap kriteria pemeringkatan) perlu disusun composite key level berikutnya hingga didapatkan 1 (satu) kolom composite key tunggal.
   • Composite level kedua (Composite Key 2.1), akan disusun berdasar nilai composite key 1.1 (sebagai xxxxx) dan composite key 1.2 (sebagai yyyyy).
   • Composite key 1.1 dan composite key 1.2 masing-masing dapat berjumlah sebanyak jumlah record yaitu 10.000 (sepuluh ribu) baris data.
   • Formula penyusun composite key 2.1 yang berdasarkan nilai composite key level 1 adalah :
     
     • Berdasar composite key 1.1 dan diurutkan ascending
       
       Rank( F4 , $F$4:$F$13 , 1 )
     • Berdasar composite key 1.2 dan diurutkan ascending
       
       Rank( G4 , $G$4:$G$13 , 1 )
   • Susunan composite key 2.1 adalah xxxxx yyyyy disusun dengan cara :
     
     1. Meletakkan formula berdasar composite key 1.1 untuk menyusun bagian xxxxx
        
        =Rank( F4 , $F$4:$F$13 , 1 )
     2. Membentuk ruang untuk 5 digit berdasar composite key 1.2, hingga terbentuk susunan xxxxx 00000, dengan mengalikan hasil nomor 1 dengan 10^5
        
        *10^5
        dan bentuk formula nomor 1 akan menjadi
        
        =Rank( F4 , $F$4:$F$13 , 1 ) *10^5
     3. Memasukkan nilai composite key 1.2 hingga susunan berubah dari xxxxx 00000 menjadi xxxxx yyyyy dengan menjumlahkan hasil nomor 2 dengan formula composite berdasar composite key 1.2.
        
        +Rank( G4 , $G$4:$G$13 , 1 )
        dan bentuk formula nomor 2 akan menjadi
        
        =Rank( F4 , $F$4:$F$13 , 1 ) *10^5+Rank( G4 , $G$4:$G$13 , 1 )
   • Formula diletakkan pada kolom H mulai baris ke-4 Excel (cell H4). Kemudian di-copy ke baris data berikutnya.
   • Bentuk composite key 2.1 akan tampak seperti gambar berikut ini
     
     




7. Susunan tabel hingga terbentuknya composite key akhir yang dapat digunakan dalam fungsi rank adalah sebagai berikut :
   
   

---

Rank dengan multi kriteria

Penyelesaian kasus diatas dengan menggunakan fungsi Rank dilakukan terhadap kolom Composite Key 2.1. Proses rank akan dilakukan dengan pengurutan menaik (ascending). Proses penyelesaian kasus di atas adalah sebagai berikut :

1. Peringkat keberapa tertinggikah peserta dengan ID 09159 dari seluruh peserta ujian seleksi ?
   
   • Data yang dibutuhkan untuk memanfaatkan fungsi Rank :
     
     • nilai_yang_dicari_peringkatnya : nilai composite key 2.1 untuk peserta dengan ID 09159, yaitu cell H4
     • referensi_data_semesta_peringkat : seluruh data composite key 2.1, yaitu range H4:H13 yang absolut tetap ($H$4:$H$13)
     • [cara_mengurutkan] : opsi diurutkan secara menaik (ascending), yaitu 1
   • Susunan formula dengan fungsi Rank :
     
     =RANK( $H4 , $H$4:$H$13 , 1 )
   • Hasil formula adalah bernilai 9
2. Susunlah data seluruh peserta ujian seleksi disertai peringkat masing-masing yang terurut dari tertinggi ke terendah sesuai kriteria pemeringkatan yang ditetapkan !
   
   • Dibuat kolom untuk hasil pemeringkatan melalui pengurutan (kolom I).
   • Pengurutan terhadap kolom composite key 2.1 adalah dengan cara ascending dan bisa menggunakan formula kasus 1 kemudian di-copy ke baris data berikutnya.
   • Susunan formula dengan fungsi Rank :
     
     =RANK( $H4 , $H$4:$H$13 , 1 )

Tabel hasil penyelesaian kedua kasus di atas adalah :

• Hasil penyelesaian kasus 1 : cell I4 (berwarna kuning).
• Hasil penyelesaian kasus 2 : seluruh tabel.
• Perhatikan juga baris-baris yang diberi warna selain warna kuning.

---

Closing :

Penyusunan composite key harus terus dilakukan hingga diperoleh sebuah kolom composite key tunggal yang berisi seluruh kriteria pemeringkatan. Setiap composite key yang dibentuk sangat tergantung pada karakteristik data penyusun composite key tersebut. Pembahasan berikutnya adalah contoh variasi karakteristik data, sehingga akan lebih mudah dipahami tentang ketergantungan composite key terhadap karakteristik data penyusun composite key tersebut.

---

File(s) :

Rank multi kriteria pada sheet n_cols_multi_composites_1

---

Coretan terkait :

• Susun data sebagai tabel yang baik. Kenapa ?
• Formula peringkat data (Rank satu kriteria)
• Formula peringkat data (Rank multi kriteria 01)
• Formula peringkat data (Rank multi kriteria 03)
• Pemeringkatan data (Ranking)

---