Kamis, 10 Mei 2012

Formula peringkat data teks (Rank data text)

Muatan :

  • Kilasan tentang pemeringkatan data teks
  • Contoh kasus
  • Konversi data teks menjadi data numerik
  • Penyusunan Composite Key
  • Kalkulasi rank

Kilasan

Data hasil penilaian secara kualitatif adalah sebuah contoh data teks yang sering ditemui sebagai data yang akan dicari peringkatnya. Banyak juga ditemui adanya skala prioritas kode tertentu sebagai kriteria pemeringkatan. Padahal banyak sekali kode yang tersusun sebagai data bertipe teks.

Fungsi rank, yang hanya bekerja pada data bertipe numerik saja, tidak dapat digunakan untuk mengkomputasi peringkat data yang bertipe teks. Umumnya dibutuhkan sebuah proses untuk mengkonversi data teks, yang menjadi kriteria pemeringkatan tersebut, menjadi suatu data bertipe numerik. Hal ini tentu saja membutuhkan suatu tabel referensi yang memetakan kesetaraan nilai antara data teks dengan suatu data numerik tertentu.



Pembahasan kali ini akan difokuskan pada langkah-langkah pemeringkatan terhadap data teks, yang tentu saja didalamnya akan ada proses pengkonversian ke data numerik terlebih dulu. Sebelum melangkah lebih jauh, sebaiknya dipahami lebih dulu tentang karakteristik fungsi rank melalui coretan sebelumnya. Termasuk cara menyusun composite key yang befungsi sebagai penggabungan seluruh kriteria pemeringkatan, yang juga memiliki karakteristik tersendiri.

Contoh kasus

Pembahasan kali ini akan menggunakan sebuah data imajinasi tentang ujian kenaikan jabatan. Total peserta ujian setiap tahun hanya 10 orang. Setiap peserta memiliki ID karyawan yang unik. Ujiannya berupa simulasi kasus-kasus dalam pekerjaan. Setiap kasus yang dapat diselesaikan akan diberi penilaian secara kualitatif sebagai nilai indeks. Jumlah kasus yang dapat diselesaikan dalam satu kurun waktu simulasi juga mempengaruhi hasil ujian.

Jumlah kasus yang dapat diselesaikan adalah sebanyak jumlah karakter asterix (*) dalam kolom grade_kasus. Minimal adalah tidak ada kasus yang terselesaikan atau blank. Maksimal adalah 5 asterix. Selain itu, setiap tahun perusahaan menetapkan kebijakan tentang divisi yang mendapatkan prioritas untuk jumlah karyawan yang bisa naik jabatan. Urutan divisi sesuai prioritas tersebut tahun ini ada pada tabel referensi prioritas divisi. Maksimal hanya 5 divisi per tahun.

Pada tabel referensi tersebut, juga telah dibuatkan peta data kesetaraan antara index_nilai dengan bobot indeksnya. Tabel peta kesetaraan ini dapat digunakan dalam mengkonversi nilai indeks yang bertipe text menjadi bertipe numerik. Berikut ini adalah tabel referensi yang dimaksud.

Masa kerja karyawan juga menjadi salah satu kriteria kenaikan jabatan. Karyawan yang lebih lama masa kerjanya akan mendapat prioritas ketika nilai hasil pemeringkatan berdasarkan index_nilai,grade_kasus, dan prioritas divisi adalah sama dimiliki lebih dari satu karyawan. Masa kerja ini tercermin dari ID karyawan. Semakin rendah nilai ID, berarti masa kerjanya semakin lama, karena ID karyawan didapatkan saat mulai menjadi karyawan perusahaan tersebut.

Data hasil ujian kenaikan jabatan untuk tahun ini adalah sebagai berikut :

Jadi, pemeringkatan dilakukan berdasar 4 kriteria, yaitu :
  1. Nilai indeks (kolom index_nilai) secara menurun (descending)
  2. Jumlah simulasi kasus yang terselesaikan (kolom grade_kasus) secara menurun (descending)
  3. Prioritas divisi tahunan (kolom Divisi) secara menaik (ascending)
  4. Masa kerja secara menurun (descending)

Kasus yang akan diselesaikan adalah :
  1. Peringkat keberapa tertinggikah karyawan dengan ID 525 dari seluruh peserta ujian kenaikan jabatan tahun ini ?
  2. Susunlah data seluruh peserta ujian kenaikan jabatan tahun ini disertai peringkat masing-masing yang terurut dari tertinggi ke terendah sesuai kriteria pemeringkatan yang ditetapkan !

Konversi data teks menjadi data numerik

Data kriteria pemeringkatan yang bertipe teks membutuhkan proses konversi menjadi data bertipe numerik. Keberadaan tabel referensi memudahkan proses tersebut. Tetapi bisa saja terjadi bahwa tabel referensi tidak berbentuk sebuah peta data kesetaraan secara eksplisit. Contohnya tabel referensi untuk index_nilai memiliki tabel kesetaraan nilai bertipe numerik, hingga relatif mudah proses pengkonversiannya. Sedangkan data prioritas divisi hanyalah berupa sebuah daftar prioritas yang membutuhkan pembuatan kesetaraan nilai bertipe numeriknya. Lebih lanjut, akan dibahas setiap kolom kriteria secara runut mulai dari kriteria pertama.

Proses pengkonversian kriteria pemeringkatan menjadi data bertipe numerik adalah sebagai berikut :
  1. Nilai indeks (kolom index_nilai) secara menurun (descending)
    • Kolom ini terkait erat dengan tabel referensi kolom index_nilai dan nilai.
    • Pada tabel referensi, kolom index_nilai terurut secara descending, yaitu dari indeks tertinggi (A) sampai indeks terendah (X).
    • Pada tabel referensi, kolom nilai juga terurut secara descending, yaitu dari nilai tertinggi (5) sampai nilai terendah (-99).
    • Peta kesetaraan menunjukkan kesamaan arah antara data bertipe teks (index_nilai) dengan data bertipe numerik (nilai) yang menjadi nilai kesetaraannya, yaitu kedua kolom sama-sama terurut menurun (descending).
    • Proses lookup index_nilai pada tabel data akan mendapatkan data pada kolom nilai di tabel referensi dengan kemudahan berupa kesamaan arah tersebut.
    • Formula konversi data index_nilai :
        VLookUp( B4 , $H$4:$I$10 , 2 , FALSE )
    • Permasalahan berikutnya adalah adanya nilai negatif pada tabel referensi kolom nilai. Hal ini akan mempengaruhi operasi matematis ketika menyusun composite key nantinya. Oleh sebab itu diperlukan proses untuk menyusun suatu interval kolom nilai yang seluruhnya berupa bilangan positif. Salah satu caranya adalah menjumlahkan dengan angka tertentu, sehingga nilai -99 dapat menjadi 0 atau 1. Tetapi, hal ini justru akan menyulitkan proses pembacaan data secara manual.
    • Kolom nilai juga bisa dilihat sebagai kode tanda positif atau negatif yang digabungkan dengan kode nilai kesetaraan. Dengan begitu, maka tanda positif bisa diartikan bernilai 1 dan tanda negatif bernilai 0. Maka nilai -99 akan setara dengan 099, sedangkan nilai 5 akan setara dengan 105. Nilai 0 berarti setara dengan 100.
    • Untuk mengkonversi tanda positif atau negatif dari kolom nilai menjadi nilai 1 atau 0, bisa dilakukan dengan proses perbandingan formula konversi data index_nilai.
    • Formula konversi tanda positif atau negatif adalah :
        ( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) >= 0 )
    • Jika tanda dalam kolom nilai telah dikonversi menjadi 1 atau 0, maka yang dibutuhkan dari kolom nilai adalah nilai absolutnya. Maka formula konversi index_nilai menjadi nilai kesetaraannya perlu diabsolutkan dengan fungsi Abs. Formulanya menjadi :
        Abs( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) )
    • Akhirnya, berdasar kriteria pertama (kolom index_nilai), didapatkan 2 (dua) penyusun composite key, yaitu :
      1. Tanda positif atau negatif kolom nilai di tabel referensi : (1 digit)
          ( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) >= 0 )
  2. Data tanpa tanda dari kolom nilai di tabel referensi : (2 digit)
      Abs( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) )





  • Kedua penyusun tersebut masih menggunakan nilai data aslinya (tanpa proses pengurutan apapun). Hal ini akan menjadi pertimbangan dalam proses konversi kolom kriteria yang lain, yaitu berusaha untuk tetap menggunakan nilai asli data.
  • Total digit kriteria berdasar kolom ini adalah 3 digit.
  • Jumlah simulasi kasus yang terselesaikan (kolom grade_kasus) secara menurun (descending)
    • Kolom ini berisi data teks yang makna datanya terkait dengan jumlah karakter.
    • Semakin banyak karakter asterix (*) maka semakin tinggi nilainya. Maka dapat dikatakan bahwa kolom ini berisi nilai jumlah karakter.
    • Nilai minimal kolom ini adalah 0, yaitu ketika data tidak berisi karakter asterix (*).
    • Nilai maksimal kolom ini adalah 5, yaitu ketika data berisi 5 karakter asterix (*).
    • Kriteria berdasar kolom ini selalu berisi 1 digit.
    • Formula konversi untuk kriteria berdasar kolom ini adalah
        Len( C4 )
    • Kolom ini menjadi penyusun composite key dengan nilai asli datanya dan tidak diurutkan.
  • Prioritas divisi tahunan (kolom Divisi) secara menaik (ascending)
    • Tabel referensi prioritas divisi adalah tabel bantu untuk mengkonversi data divisi yang bertipe teks menjadi data bertipe numerik.
    • Tabel referensi prioritas divisi berisi urutan prioritas dari tertinggi ke terendah. Jadi pada tabel referensi telah didapatkan kondisi yang terurut secara menurun (descending).
    • Prioritas tertinggi disetarakan menjadi prioritas 1, maka akan terbentuk suatu data numerik yang terurut menaik (ascending). Hal ini bisa didapatkan dengan fungsi Match sebagai berikut :
        Match( D4 , $K$4:$K$7 , 0 )
    • Penyusun composite key berdasar kolom ini akan selalu berisi 1 digit.
    • Jumlah digit penyusun composite key sampai sebelum kriteria ini telah terkumpul sebanyak 4 digit. Maka kriteria berdasar kolom ini dapat dimasukkan kedalam composite key yang sama dengan kriteria sebelumnya.
    • Pada kriteria sebelumnya, penyusun composite key ada yang berupa nilai asli data (nilai numerik hasil konversi secara langsung) dan terurut descending. Maka penyusun composite key berdasar kolom ini juga harus berdasar yang terurut descending. Artinya, prioritas tertinggi tidak bisa diberi nilai setara dengan 1. Justru prioritas tertinggi harus diberi nilai yang tertinggi.
    • Formula dengan fungsi Match di atas akan selalu menghasilkan nomor baris dalam tabel referensi kolom prioritas divisi yang dimulai dari 1 sampai n, dengan n adalah jumlah record prioritas divisi.
    • Untuk membalikkan nilai urutan data dari terurut ascending menjadi descending adalah melalui pengurangan jumlah record data dengan nomor recordnya. Jadi, jika jumlah record prioritas divisi dikurangi hasil Match akan membentuk suatu deret angka yang terurut descending mulai dari 0 sampai n-1, dengan n adalah jumlah record prioritas divisi.
    • Untuk menghindari munculnya nilai 0 untuk prioritas terendah, maka hasil pengurangan tersebut harus ditambah dengan 1. Artinya, jumlah record + 1 setara dengan jumlah cell kolom prioritas divisi beserta headernya yang berisi data, yang dapat diformulasikan sebagai
        CountA( $K$3:$K$7 )
    • Formula penyusun composite key berdasar kolom divisi yang berkaitan dengan prioritas divisi adalah
        CountA( $K$3:$K$7 ) - Match( D4 , $K$4:$K$7 , 0 )
  • Masa kerja secara menurun (descending)
    • Masa kerja berhubungan erat dengan kolom nip. Semakin lama masa kerja, maka semakin rendah nilai nip. Artinya, masa kerja secara menurun setara dengan nilai nip secara menaik. Jadi kriteria berdasar masa kerja adalah kolom nip yang terurut menaik (ascending).
    • Jumlah digit penyusun composite key sampai dengan kriteria ke-3 adalah 5 digit. Maka kriteria pemeringkatan berdasar nip bisa disatukan kedalam composite key yang ada.
    • Permasalahannya adalah perbedaan pengurutan antara penyusun composite key sampai dengan kriteria-3 dengan tuntutan pengurutan terhadap kriteria ke-4 ini. Hal ini mirip dengan kasus terhadap kolom prioritas divisi.
    • Nilai nip yang rendah harus mendapat nilai kesetaraan yang tinggi. Hal ini bisa diperoleh jika kolom nip disusun sebagai hasil nilai peringkat dengan fungsi rank.
    • Opsi pemeringkatan dalam fungsi rank terhadap kolom nip adalah descending agar nilai nip yang rendah mendapat hasil rank yang tinggi.
    • Jumlah digit penyusun composite key berdasar kolom nip adalah 2 digit, yaitu sebanyak jumlah digit dari jumlah record maksimum yang 10 orang.
    • Formula penyusun composite key berdasar kolom nip adalah
        Rank( A4 , $A$4:$A$13 )



    • Penyusunan Composite Key

    Setelah didapatkan detil dari setiap kriteria pemeringkatan, maka dapat disusun sebuah composite key sebagai berikut :
    1. Composite key akan berisi 7 digit dengan penjabaran sebagai berikut :
      • Kriteria 1 berisi 1 digit tanda positif atau negatif dan 2 digit absolut hasil konversi index_nilai menjadi nilai, yang berbentuk v ww.
      • Kriteria 2 berisi 1 digit hasil konversi grade_kasus, yang berbentuk x.
      • Kriteria 3 berisi 1 digit hasil konversi divisi, yang berbentuk y.
      • Kriteria 4 berisi 2 digit berdasar kolom nip sebagai wujud krieria berdasar masa kerja, yang berbentuk zz
    2. Format lengkap composite key adalah v ww x y zz
    3. Seluruh item penyusun composite key telah diubah pengurutannya agar seragam, yaitu untuk diurutkan secara menurun (descending).
    4. Formula untuk menyusun composite key dibuat dengan cara :
      • Memasukkan 1 digit tanda positif atau negatif
          =( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) >= 0 )
      • Memasukkan 2 digit hasil konversi index_nilai disertai ruang untuk seluruh digit setelah 1 digit tandap positif atau negatif, yaitu sebanyak 6 digit
          *10^6+Abs( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) )
        maka formula akan menjadi
          =( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) >= 0 ) *10^6+Abs( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) )
      • Memasukkan 1 digit hasil konversi grade_kasus disertai ruang untuk seluruh digit setelah 2 digit hasil konversi index_nilai, yaitu sebanyak 4 digit
          *10^4+Len( C4 )
        maka formula akan menjadi
          =( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) >= 0 ) *10^6+Abs( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) ) *10^4+Len( C4 )
      • Memasukkan 1 digit hasil konversi divisi disertai ruang untuk seluruh digit setelah 1 digit hasil konversi grade_kasus, yaitu sebanyak 3 digit
          *1000+CountA( $K$3:$K$7 )-Match( D4 , $K$4:$K$7 , 0 )
        maka formula akan menjadi
          =( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) >= 0 ) *10^6+Abs( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) ) *10^4+Len( C4 ) *1000+CountA( $K$3:$K$7 )-Match( D4 , $K$4:$K$7 , 0 )
      • Memasukkan 2 digit berdasar kolom nip disertai ruang untuk seluruh digit setelah 1 digit hasil konversi divisi, yaitu sebanyak 2 digit
          *100+Rank( A4 , $A$4:$A$13 )
        maka formula lengkap penyusun composite key adalah
          =( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) >= 0 ) *10^6+Abs( VLookUp( B4 , $H$4:$I$10 , 2 , FALSE ) ) *10^4+Len( C4 ) *1000+CountA( $K$3:$K$7 )-Match( D4 , $K$4:$K$7 , 0 ) *100+Rank( A4 , $A$4:$A$13 )
    5. Formula penyusun composite key akan diletakkan pada kolom E mulai baris ke-4 Excel (cell E4), kemudian di-copy ke baris berikutnya, seperti gambar berikut ini.

    Rank dengan multi kriteria

    Penyelesaian kasus diatas dengan menggunakan fungsi Rank dilakukan terhadap kolom Composite Key. Proses rank akan dilakukan dengan pengurutan menurun (descending) karena seluruh penyusun composite key telah disesuaikan memiliki arah pengurutan menurun. Proses penyelesaian kasus di atas adalah sebagai berikut :

    1. Peringkat keberapa tertinggikah karyawan dengan ID 525 dari seluruh peserta ujian kenaikan jabatan tahun ini ?
      • Data yang dibutuhkan untuk memanfaatkan fungsi Rank :
        • nilai_yang_dicari_peringkatnya : nilai composite key untuk karyawan dengan ID 525, yaitu cell E4
        • referensi_data_semesta_peringkat : seluruh data composite key, yaitu range E4:E13 yang absolut tetap ($E$4:$E$13)
        • [cara_mengurutkan] : opsi diurutkan secara menurun (descending), yaitu 0 atau diabaikan
      • Susunan formula dengan fungsi Rank :
          =RANK( $E4 , $E$4:$E$13 , 0 )
        atau jika menggunakan nilai default opsi pengurutan yang berupa pengurutan descending, maka nilai 0 dapat diabaikan dan formula akan berbentuk
          =RANK( $E4 , $E$4:$E$13 )
      • Hasil formula adalah bernilai 1
    2. Susunlah data seluruh peserta ujian kenaikan jabatan tahun ini disertai peringkat masing-masing yang terurut dari tertinggi ke terendah sesuai kriteria pemeringkatan yang ditetapkan !
      • Dibuat kolom untuk hasil pemeringkatan melalui pengurutan (kolom F).
      • Pengurutan terhadap kolom composite key adalah dengan cara descending dan bisa menggunakan formula kasus 1 kemudian di-copy ke baris data berikutnya.
      • Susunan formula dengan fungsi Rank :
          =RANK( $E4 , $E$4:$E$13 , 0 )
        atau jika menggunakan nilai default opsi pengurutan yang berupa pengurutan descending, maka nilai 0 dapat diabaikan dan formula akan berbentuk
          =RANK( $E4 , $E$4:$E$13 )

    Tabel hasil penyelesaian kedua kasus di atas adalah :

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


    Closing :

    Pada data dengan kriteria pemeringkatan memiliki tipe data berupa teks diperlukan proses pengkonversian menjadi data numerik yang setara. Sebuah tabel referensi yang berisi peta kesetaraan akan memudahkan proses pengkonversian tersebut.
    Composite key dengan banyak item penyusun dan ada yang menggunakan nilai asli data, maka ubah seluruh penyusun yang harus terurut agar sesuai dengan cara pengurutan item-item dengan nilai asli data tersebut.

    File(s) :




    Coretan terkait :




    Diposting oleh di
    Label: ,

    Tidak ada komentar:

    Posting Komentar

    Langganan: Posting Komentar (Atom)