Senin, 07 Mei 2012

Formula peringkat data (Rank multi kriteria 01)


Muatan :

  • Kilasan tentang kriteria pemeringkatan
  • Contoh kasus
  • Penyusunan Composite Key
  • Rank dengan multi kriteria


Kilasan

Fungsi rank mencari sebuah nilai dalam satu kumpulan data. Kriteria pemeringkatan bisa jadi terdiri dari banyak kolom dengan cara pengurutan per kriteria yang juga berbeda-beda. Untuk itu, dibutuhkan suatu proses untuk menyusun sebuah nilai yang telah mewakili seluruh kriteria pemeringkatan. Nilai ini sering disebut dengan composite key. Fungsi rank pada suatu data yang pemeringkatannya berdasar banyak kriteria bisa dikatakan sebagai sebuah proses mencari sebuah nilai composite key dalam sekumpulan nilai-nilai composite key.



Tidak ada kaidah khusus dalam penyusunan composite key. Penyusunan composite key sangat tergantung pada karakteristik setiap kolom penyusunnya. Meski demikian, composite key untuk kegiatan pemeringkatan data dituntut untuk berisi data bertipe numerik. Kecenderungannya, composite key untuk kegiatan pemeringkatan data disusun dari nilai rank setiap kolom kriterianya.

Pembahasan kali ini adalah tentang pemeringkatan berdasar banyak kriteria dengan fokus utama pada pengenalan penyusunan kolom composite key. Jika belum memahami cara penggunaan fungsi rank, silakan membaca coretan Formula peringkat data (satu kriteria).


Contoh kasus

Pembahasan kali ini akan menggunakan sebuah data nilai penjualan yang berhasil diperoleh oleh setiap karyawan bagian penjualan. Total karyawan bagian penjualan adalah 10 orang. Setiap karyawan memiliki ID karyawan yang bersifat unik. Data tersebut adalah sebagai berikut :

Kriteria pemeringkatannya adalah :
  1. Nilai penjualan (kolom value) secara menurun (descending)
  2. ID karyawan (kolom ID) secara menaik (ascending)

Kasus yang akan diselesaikan adalah :
  1. Peringkat keberapa tertinggikah nilai penjualan karyawan dengan ID 525 dalam 10 orang karyawan bagian penjualan tersebut ?
  2. Susunlah data seluruh karyawan bagian penjualan disertai peringkat masing-masing karyawan yang terurut dari tertinggi ke terendah sesuai kriteria pemeringkatan yang ditetapkan !


Penyusunan Composite Key

Diketahui ada 2 (dua) kriteria pemeringkatan. Kedua kriteria ini akan diproses agar menjadi satu nilai yang disebut dengan nilai composite key yang bertipe numerik.

Cara termudah untuk menyusun composite key adalah dengan menyusun suatu kombinasi angka yang mewakili setiap kriteria, seperti xxx yyy dengan xxx adalah nilai peringkat berdasar kolom kriteria pertama dan yyy adalah nilai peringkat berdasar kolom kriteria kedua. Penyusunan xxx yyy memerlukan pengetahuan secara detil tentang data terkait. Umumnya, fungsi rank bisa dijadikan alat untuk menyusun xxx maupun yyy.

Untuk kasus diatas, composite key bisa disusun sebagai berikut :
  1. Nilai penjualan (kolom value) secara menurun (descending)
    • Tipe data kolom ini adalah numerik dengan jumlah digit yang bervariasi.
    • Jumlah record data maksimal adalah 10 orang bagian penjualan yang maksimal ada 2 digit, yaitu angka 1 diikuti angka 0.
    • Karena jumlah digit dari jumlah maksimal record < jumlah digit dari nilai maksimal data, maka penyusun composite key dari kriteria ini menggunakan nomor peringkat hasil fungsi rank terhadap kolom value ini.







    • Kriteria berdasar kolom ini akan diperingkatkan dengan pengurutan menurun (descending).






  2. ID karyawan (kolom ID) secara menaik (ascending)
    • Tipe data kolom ini adalah numerik dengan jumlah digit saat ini adalah 3, tetapi bisa jadi akan berubah dan lebih bervariasi.
    • Jumlah record data maksimal adalah 10 orang bagian penjualan yang maksimal ada 2 digit, yaitu angka 1 diikuti angka 0.
    • Karena jumlah digit dari jumlah maksimal record < jumlah digit dari nilai maksimal data, maka penyusun composite key dari kriteria ini menggunakan nomor peringkat hasil fungsi rank terhadap kolom id ini.







    • Kriteria berdasar kolom ini akan diperingkatkan dengan pengurutan menaik (ascending).






  3. Susunan composite key
    • Composite key disusun oleh 2 (dua) kriteria.
    • Jumlah digit nilai composite dari kriteria pertama adalah 2 dan dinotasikan sebagai xx
    • Jumlah digit nilai composite dari kriteria kedua adalah 2 juga dan dinotasikan sebagai yy
    • Setiap kriteria memiliki nilai composite hasil fungsi rank terhadap masing-masing kriteria dan bertipe numerik
    • Format composite key menjadi xx yy, maka nilai composite kriteria pertama (xx) harus menyediakan ruang 2 digit untuk nilai composite kriteria kedua (yy)
    • Pembentukan ruang 2 digit untuk kriteria kedua (yy) dilakukan dengan mengalikan nilai composite kriteria pertama (xx) dengan 10^2 atau 100, sehingga kriteria pertama (xx) akan menjadi (xx00). Kemudian kriteria kedua menambahkan nilai kriteria pertama yang telah disertai ruang 2 digit tersebut. Formulasinya menjadi xx*100+yy.
Langkah-langkah penyusunan composite key dalam bentuk formula adalah seperti gambar berikut :
Setiap cell berwarna kuning adalah proses yang terjadi pada satu cell composite key untuk data pertama (baris ke-4 Excel). Detil data dapat dilihat di gambar sebelumnya pada bagian Contoh Kasus. Alur proses penyusunan composite key adalah sebagai berikut :
  • Step 1
      Menyusun nilai composite berdasar kriteria pertama (xx) memanfaatkan fungsi rank, dengan pengurutan menurun (descending)
    • =RANK(B4,$B$4:$B$13)
    • "Cari nomor peringkat nilai Value baris 4 (B4) disekumpulan data Value yang absolut ($B$4:$B$13) dengan cara pengurutan descending (karena inputan untuk parameter opsi tidak diberi nilai apapun, maka akan menggunakan nilai defaultnya yaitu 0 atau pengurutan descending)."
    • Hasil rank akan memberikan nomor peringkat 1 kepada nilai Value yang tertinggi. Semakin rendah nilai Value, maka nomor peringkat akan semakin tinggi sebagai akibat dari pengurutan descending.
  • Step 2
      Menyiapkan ruang untuk nilai composite berdasar kriteria kedua sebanyak 2 digit, melalui perkalian nilai composite berdasar kriteria pertama dengan 10^2 atau 100
    • =RANK(B4,$B$4:$B$13) * 100
    • "Hasil step 1 dikalikan dengan 100 agar terbentuk ruang 2 digit dibelakang hasil step 1."
  • Step 3
      Menyusun nilai composite berdasar kriteria kedua (yy) memanfaatkan fungsi rank, dengan pengurutan menaik (ascending)
    • =RANK(A4,$A$4:$A$13,1)
    • "Cari nomor peringkat nilai ID baris 4 (A4) disekumpulan data ID yang absolut ($A$4:$A$13) dengan cara pengurutan ascending (1)."
    • Hasil rank akan memberikan nomor peringkat 1 kepada nilai ID yang terrendah. Semakin tinggi nilai ID, maka nomor peringkat akan semakin tinggi sebagai akibat dari pengurutan ascending.
  • Step 4
      Menyusun nilai composite key untuk data pertama (baris ke-4 Excel) dengan susunan berupa xx yy melalui penjumlahan hasil step 2 dan hasil step 3
    • =RANK(B4,$B$4:$B$13) * 100 + RANK(A4,$A$4:$A$13,1)
    • "Hasil step 2 ditambahkan dengan hasil step 3 agar terbentuk susunan xx yy melalui [ format cells -> number -> custom -> 00 00 ]."
    • Susunan composite key akan menghasilkan nilai terendah pada kondisi nilai value tertinggi diikuti perbandingan nilai ID yang relatif lebih rendah untuk nilai value yang sama. Artinya, jika composite key diurutkan menaik (ascending), akan didapat nilai terendah composite key berisi kondisi nilai Value tertinggi dengan nilai ID yang terendah pada nilai Value tersebut.
Kolom composite key akan diletakkan pada kolom C yang tepat sebagai kolom kosong pertama setelah seluruh kolom data. Langkah-langkah diatas berarti dilakukan pada cell C4. Kemudian cell C4 ini di-copy ke seluruh record data di kolom C (kolom Composite Key). Hasilnya akan tampak seperti gambar berikut :

Rank dengan multi kriteria

Penyelesaian kasus diatas dengan menggunakan fungsi Rank dilakukan terhadap kolom Composite Key. Composite Key telah terurut berdasar kondisi setiap kriteria, maka proses rank akan dilakukan dengan pengurutan menaik (ascending). Proses penyelesaian kasus di atas adalah sebagai berikut :
  1. Peringkat keberapa tertinggikah nilai penjualan karyawan dengan ID 525 dalam 10 orang karyawan bagian penjualan tersebut ?
    • Data yang dibutuhkan untuk memanfaatkan fungsi Rank :
      • nilai_yang_dicari_peringkatnya : nilai composite key untuk karyawan ID 525, yaitu cell C4
      • referensi_data_semesta_peringkat : seluruh data composite key, yaitu range C4:C13 yang absolut tetap ($C$4:$C$13)
      • [cara_mengurutkan] : opsi diurutkan secara menaik (ascending), yaitu 1
    • Susunan formula dengan fungsi Rank :
        =RANK( $C4 , $C$4:$C$13 , 1 )
    • Hasil formula adalah bernilai 1
  2. Susunlah data seluruh karyawan bagian penjualan disertai peringkat masing-masing karyawan yang terurut dari tertinggi ke terendah sesuai kriteria pemeringkatan yang ditetapkan !
    • Dibuat kolom untuk hasil pemeringkatan melalui pengurutan (kolom D).
    • Pengurutan terhadap kolom composite key adalah dengan cara ascending dan bisa menggunakan formula kasus 1 kemudian di-copy ke baris data berikutnya.
    • Susunan formula dengan fungsi Rank :
        =RANK( $C4 , $C$4:$C$13 , 1 )
Tabel hasil penyelesaian kedua kasus di atas adalah :
  • Hasil penyelesaian kasus 1 : cell D4 (berwarna kuning).
  • Hasil penyelesaian kasus 2 : seluruh tabel.

Closing :

Pada penentuan nomor peringkat suatu data yang kriteria pemeringkatannya lebih dari 1 (satu) kriteria (multi kriteria) dibutuhkan sebuah proses penyusunan composite key. Composite key adalah sebuah kriteria tunggal hasil penyatuan berbagai kriteria. Fungsi rank yang bekerja hanya pada data bertipe numerik. Maka pastikan seluruh nilai dalam composite key memiliki tipe data numerik.

File(s) :


Coretan terkait :


Tidak ada komentar:

Posting Komentar