GoBlog Theme Banner 728x90
Home » » Formula Excel Pendistribusian Persediaan

Formula Excel Pendistribusian Persediaan

Posted by Unknown Posted on 05.26 with 1 comment

Muatan :

  • Kilasan tentang formula distribusi
  • Konsep pendistribusian persediaan dengan formula
  • Cerita tentang sebuah kasus
  • Penyusunan formula

Kilasan

Salah satu kasus yang berkaitan dengan permintaan dan persediaan adalah mengkomputasi pendistribusian persediaan sesuai dengan permintaan yang ada. Hasil akhir yang diharapkan adalah diketahuinya dengan jelas item-item permintaan yang masih belum terpenuhi karena keterbatasan persediaan. Jumlah kekurangan persediaan setiap item permintaan yang belum terpenuhi bisa menjadi informasi penting dalam tahap pengadaan persediaan berikutnya. Tentu masih banyak informasi lain yang bisa didapatkan dari terpetakannya permintaan dan persediaan tersebut.

Bahasan kali ini akan difokuskan pada formula untuk mengkomputasi jumlah persediaan yang digunakan untuk memenuhi permintaan.

Konsep pendistribusian persediaan dengan formula

Pada dasarnya, konsep pendistribusian persedian adalah sebagai berikut :
  1. Mengetahui jumlah permintaan secara progresif
  2. Mengetahui sisa persedian yang masih bisa digunakan untuk memenuhi permintaan secara progresif
  3. Menentukan jumlah permintaan yang bisa dipenuhi oleh persediaan secara progresif
Maksud dari kata 'progresif' adalah pada keadaan saat itu. Sebuah item persediaan yang telah digunakan untuk memenuhi sebuah permintaan tidak mungkin digunakan untuk memenuhi permintaan yang lain.

Dengan berpegang teguh pada konsep diatas, maka pendistribusian persediaan dengan formula bisa digambarkan sebagai berikut :
  1. Penyusunan tabel permintaan vs persediaan
  2. Data permintaan akan tertuang secara vertikal, sehingga setiap baris terfokus pada sebuah permintaan
  3. Data item-item persediaan akan tertuang secara horisontal dan bersifat unik berdasar kriteria permintaan
  4. Dari hal nomor 1 dan 2 diatas, maka terbentuklah ruang untuk nilai-nilai pemenuhan permintaan yang bisa disediakan oleh setiap item persediaan
  5. Prioritas akan terbentuk dengan sendirinya berdasarkan urutan item-item permintaan (sisi vertikal) dan berdasarkan urutan item-item persediaan (sisi horisontal)
Untuk memudahkan pembahasan, maka pembahasan kali ini tidak akan mencakup tentang cara penyusunan prioritas dan cara penyusunan item-item persediaan yang harus unik berdasar kriteria permintaan. Salah satu cara penyusunan prioritas bisa memanfaatkan coretan Formula sort data pada data teks. Sedangkan salah satu cara menyusun item-item persediaan yang bersifat unik bisa memanfaatkan coretan Formula penyusun data unique yang terurut (sorted), setelah didapatkan susunan persediaan yang terurut menurut prioritas pemanfaatannya.

Cerita tentang sebuah kasus

Sebuah gudang sedang melakukan penataan ulang setiap slot persediaan yang dimilikinya. Setiap slot bisa menyimpan banyak produk. Masa tinggal setiap produk dalam sebuah slot diwakili oleh suatu nilai kualitatif yang disebut level. Level hanya terbagi menjadi 2, yaitu :
  • level A
    untuk produk yang masa tinggalnya didalam gudang telah lama dan harus segera terpakai.
  • level B
    untuk produk yang masa tinggalnya didalam gudang masih relatif baru menurut proses kalkulasi tertentu dan dapat digunakan jika seluruh level A untuk suatu produk telah habis terpakai.
Pada contoh kasus ini, akan digunakan sebuah produk saja, yaitu produk 901, untuk memudahkan pemahaman terhadap pembahasan. Gambar berikut adalah data :
  • permintaan dari slot-slot gudang yang membutuhkan persediaan baru yang dapat diambil dari slot lainnya untuk suatu produk tertentu. [Tabel di A2:D5]
  • referensi kriteria pemilihan sumber persediaan berdasar levelnya. [Tabel di G2:H4]
  • persediaan yang bisa digunakan oleh slot lain yang lengkap dengan status levelnya. [Tabel di J2:M7]
Dari gambar diatas dapat diketahui bahwa :
  • Tabel kebutuhan telah terurut menurut kolom Slot dan Level. Pada kasus ini data kebutuhan bersifat unik menurut kolom Slot dan Level. Meski demikian, penerapan di dunia nyata, tidak diharuskan terbentuknya keunikan data kebutuhan berdasar suatu kondisi tertentu.
  • Tabel kelebihan telah terurut menurut kolom Level dan Slot. Data tidak unik.
  • Tabel referensi menunjukkan kaidah pemenuhan kebutuhan berdasar Level. Level A bisa dipenuhi oleh persediaan dengan level A maupun B. Tidak tampak keterangan tentang keharusan untuk menggunakan seluruh item level A sebelum menggunakan item level B.

Gambar berikut ini adalah target hasil kalkulasi.
Pada gambar diatas, tampak bahwa :
  • item permintaan (dari tabel kebutuhan) tersusun vertikal.
  • slot-slot persedian (dari tabel kelebihan) tersusun horisontal yang bersifat unik menurut kriteria pemenuhan kebutuhan, yaitu berdasar level dan slot (area dengan latar hijau).
  • keharusan untuk menggunakan item persediaan dengan level A sampai habis lebih dulu sebelum menggunakan item dengan level B (area dengan latar hijau). Baris level pada sisi slot kelebihan terurut mulai dari level A seluruhnya diikuti level B setelahnya tanpa mempedulikan nomor slot.
  • pada item permintaan pertama (baris 14) dengan level A dan kebutuhan senilai 40 item dipenuhi oleh :
    • Item persediaan level A dari slot 2 sebanyak 20 item yang merupakan total dari cell L3 dan L4.
      -> Dengan terpenuhinya sebanyak 20 item ini, maka sisa kebutuhan adalah 20 item (40 total item kebutuhan - 20 item dipenuhi oleh [level A slot 2])
    • Item persediaan level A dari slot 4 sebanyak 15 item.
      -> Sisa kebutuhan pada keadaan ini adalah 5 item (40 total item kebutuhan - 20 item dipenuhi oleh [level A slot 2] - 15 item dipenuhi oleh [level A slot 4]).
    • Item persediaan level B dari slot 2 sebanyak 5 item.
      -> Meskipun pada tabel kelebihan untuk level B slot 2 tersedia sebanyak 10 item, yang digunakan untuk memenuhi kebutuhan pertama (baris 14 ini) hanyalah 5 item.
      -> Sisa item yang dibutuhkan pun jadi 0 item atau habis, sehingga slot berikutnya (cell i14) bernilai 0.
  • pada item permintaan kedua (baris 15) dengan level B dan kebutuhan senilai 50 item dipenuhi oleh :
    • Item persediaan level B dari slot 2 sebanyak 5 item.
      -> Hal ini terjadi bukan dikarenakan sudah habisnya persediaan level A akibat dipakai untuk memenuhi permintaan pertama, tetapi disebabkan oleh level permintaan yang berupa level B.
      -> Menurut tabel referensi, permintaan kedua harus dipenuhi hanya oleh item persediaan level B juga.
      -> Jika dilihat dari tabel kelebihan saja, disana ada 10 item level B slot 2.
      -> Berhubung permintaan pertama telah menggunakan 5 item dari level B slot 2, maka yang tersisa untuk memenuhi permintaan berikutnya hanyalah 5 item.
      -> Item tersisa inilah yang bisa digunakan oleh level B slot 2 untuk memenuhi permintaan kedua.
      -> Sisa kebutuhan pada keadaan ini adalah 45 item (50 total item kebutuhan - 5 item dipenuhi oleh [level B slot 2]).
    • Item persediaan level B slot 5 sebanyak 45 item.
      -> Sampai disini, semua permintaan kedua dapat terpenuhi.
  • pada item permintaan ketiga (baris 16) dengan level B dan kebutuhan senilai 20 item dipenuhi oleh :
    • Item persediaan level B slot 5 sebanyak 15 item.
      -> Jika dilihat dari tabel kelebihan saja, disana ada 60 item level B slot 5.
      -> Berhubung total permintaan pertama dan permintaan kedua telah menggunakan 45 item dari level B slot 5, maka yang tersisa untuk memenuhi permintaan berikutnya hanyalah 15 item.
      -> Item tersisa inilah yang digunakan oleh level B slot 5 untuk memenuhi permintaan ketiga.
      -> Sisa kebutuhan pada keadaan ini adalah 5 item (20 item total kebutuhan - 15 item dipenuhi oleh [level B slot 5]).
    • Sampai disini, tidak ada lagi slot persediaan dengan level B yang masih bisa digunakan untuk memenuhi sisa kebutuhan sebanyak 5 item ini.
  • summary tentang jumlah kebutuhan yang belum terpenuhi masih menyisakan kebutuhan sebanyak 5 item. Data ini akan bermanfaat ketika menyusun data pemesanan produk untuk persediaan kepada supplier.

Penyusunan formula

Formula disusun dalam tahap-tahap berikut :
  1. Membuat nama-nama range
    • Pada bahasan ini, nama range akan memudahkan alur penyusunan formula, karena rujukan ke tabel-tabel data dapat langsung dibedakan dengan bentuk rujukan ke record yang sedang diproses.
    • Cara membuat nama range :
      • Blok range yang akan diberi nama
      • (Excel 2003 ke bawah) :: menu Insert -> Name -> Define

        (Excel 2007 ke atas) :: Klik kanan cell yang terblok -> pilih Name a range
      • Tulis namanya
      • Tekan OK
    • Berikut ini daftar range yang harus diberi nama :
      No.Range yang diblokNama RangeKeterangan
      1G3:H4_RefLevel_Data level source
      2J3:J7_ExSlot_Data kelebihan kolom Slot
      3K3:K7_ExProduk_Data kelebihan kolom Produk
      4L3:L7_ExExcess_Data kelebihan kolom Excess
      5M3:M7_ExLevel_Data kelebihan kolom Level
  2. Menyusun tabel hasil dilengkapi informasi level persediaan yang bisa digunakan oleh setiap permintaan dan beberapa kolom summary (lihat gambar)
    • Kolom Source_Level
      • Berisi informasi tentang level persediaan yang bisa digunakan oleh record permintaan tersebut
      • Fungsi VLookUp digunakan untuk me-lookup data level pada baris permintaan tersebut untuk mengambil data kolom source_level ditabel referensi (nama range _RefLevel_) dengan tipe pencarian yang sama persis (Exact Match atau opsi FALSE atau opsi bernilai 0 pada fungsi VLookUp)
      • Formula untuk cell E14 adalah (copy formula ke baris permintaan lainnya)
          =VLookUp( D14 , _RefLevel_ , 2 , 0 )
    • Area slot kelebihan (area hijau di F12:I13)
      • Disusun berdasar kriteria pemenuhan permintaan yang harus berdasar Level dan Slot
      • Urutan prioritas adalah menghabiskan Level A lebih dulu sebelum menggunakan Level B, yang berarti sisi Level diurutkan Ascending dan sisi Slot mengikuti urutan dalam tabel kelebihan
    • Kolom-kolom summary terdiri dari :
      • Jumlah Terpenuhi dengan formula pada cell J14 (copy ke baris permintaan lainnya)
          =Sum( F14:I14 )
      • Jumlah Belum Terpenuhi dengan formula pada cell K14 (copy ke baris permintaan lainnya)
          =C14 - J14
  3. Formula kalkulasi jumlah persediaan yang diperoleh dari setiap level dan slot dari tabel kelebihan (area range F14:I16)
      Formula akan disusun dalam beberapa step. Untuk memudahkan pemahaman, formula akan disusun untuk cell F14 yang kemudian di-copy ke cell lain di area F14:I16. Step-step tersebut adalah :
      1. Kondisi bahwa Level dari slot kelebihan yang sesuai source_level yang akan dihitung. Jika tidak sesuai maka nilai hasilnya adalah 0
        • Pemeriksaan ada atau tidaknya level dari slot kelebihan (baris 12) pada daftar source_level di record permintaan tersebut dapat dibantu oleh fungsi CountIF dengan kriteria Contain, yaitu dengan memanfaatkan wildcard character asterix (*) :
            CountIF( $E14 ,"*"& F$12 &"*")
              Perhatikan peletakan tanda absolute reference ($), karena formula akan di-copy ke cell lainnya.
        • Nilai permintaan atau total kebutuhan yang akan dikalkulasi haruslah lebih dari 0 ( $C14 > 0 )
        • Formula pengkondisiannya akan menjadi :
            =IF(CountIF( $E14 ,"*"& F$12 &"*") * ( $C14 > 0 ), "hitung" , 0 )
        • Hasil formula yang berupa kata 'hitung' akan diganti oleh formula kalkulasi jumlah item persediaan yang akan disusun pada step berikutnya.
        • Seluruh level persediaan yang tidak sesuai untuk menjadi source_level permintaan telah terisi dengan nilai 0 (diformat number menjadi '-')
      2. Jumlah item persediaan (Excess) dijumlahkan dengan fungsi SumProduct berdasar kecocokan nilai Produk, Level, Slot, dan Excess lebih dari 0 (4 kriteria) yang harus terpenuhi seluruhnya (operator AND yang setara dengan perkalian)
        • Kriteria berdasar Produk ( _ExProduk_ = $B14 )
        • Kriteria berdasar Level ( _ExLevel_ = F$12 )
        • Kriteria berdasar Slot ( _ExSlot_ = F$13 )
        • Kriteria berdasar Excess ( _ExExcess_ > 0 )
        • Susunan formula SumProduct untuk menjumlahkan nilai-nilai Excess persediaan
            SumProduct( (_ExProduk_=$B14) * (_ExLevel_=F$12) * (_ExSlot_=F$13) * ( _ExExcess_ > 0 ) * _ExExcess_ )
        • Menggantikan kata 'hitung' dalam formula step-1, menjadi :
            =IF(CountIF( $E14 ,"*"& F$12 &"*") * ( $C14 > 0 ) , SumProduct((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *( _ExExcess_ > 0 )*_ExExcess_) , 0 )
        • Hasilnya masih berupa seluruh item persediaan dan belum memperhitungkan jumlah item persediaan yang sudah digunakan oleh permintaan sebelumnya. Hal ini tampak pada kolom H dan I
      3. Mendapatkan jumlah item persediaan yang tersisa karena telah digunakan oleh permintaan sebelumnya
        • Pada hasil formula step-2 di kolom H, total persediaan untuk slot 2 level B adalah 10. Jika permintaan pertama (baris 14) telah menggunakan 10 item, maka seharusnya pada permintaan kedua nilainya menjadi 0. Begitu juga pada permintaan ketiga. Artinya, yang dibutuhkan adalah sisa persediaan secara progresif pada keaadaan tersebut. Dengan kata lain, ketika mengkalkulasi permintaan kedua, maka sisa persediaan yang dapat digunakan oleh permintaan kedua.
        • Sisa persediaan adalah total persediaan - total terpakai sampai saat itu
            Peringatan!!! bahwa proses kalkulasi baru sampai pada tahap pengerjaan per kolom (per slot per level)
        • Total persediaan adalah hasil SumProduct yang disusun di step-2
        • Total terpakai sampai saat itu (sampai permintaan baris yang aktif diproses) adalah penjumlahan yang bertambah referensi barisnya. Formula pengkalkulasinya juga harus bisa digunakan oleh record pertama permintaan. Oleh sebab itu, rujukan cell pertama dalam penjumlahan ini adalah baris 13. Formulanya akan tersusun sebagai berikut : (perhatikan peletakan tanda absolute reference '$')
            SUM( F$13:F13 )
        • Karena nilai F13 bisa bertipe numerik, maka untuk menetralisir dampaknya, dibutuhkan proses pengurangan terhadap nilai di F13 dengan memanfaatkan fungsi N(). Susunan formula penetralisir dampak tipe data numerik di F13 adalah
            - N( F$13 )
        • Susunan formula total terpakai sampai saat itu adalah
            SUM( F$13:F13 )- N( F$13 )
        • Status formula total terpakai sampai saat itu adalah sebagai pengurang. Maka susunan formulanya menjadi
            - ( SUM( F$13:F13 )- N( F$13 ) )
          yang dapat menyebabkan terjadinya perubahan tanda, sehingga formula menjadi
            - SUM( F$13:F13 )+ N( F$13 )
        • Formula tersebut masih belum menyertakan kriteria bahwa yang dijumlahkan pada kolom F tersebut haruslah yang memenuhi seluruh kriteria (dengan operator AND yang setara perkalian [ * ]) sebagai berikut :
          1. Produk sebelum baris dikalkulasi (sebelum baris 14) adalah sama dengan produk pada baris 14 yang berupa ekspresi
              ( $B$13:$B13 = $B14 )
          2. Pada source_level sebelum baris dikalkulasi (sebelum baris 14) ditemukan adanya nilai source_level yang sama dengan source_level slot kelebihan yang sedang dikalkulasi (source_level kolom F) yang berupa ekspresi
              IsNumber( Find( F$12 & "," , $E$13:$E13 & "," ) )
              • Fungsi Find akan mencari secara case sensitive (A<>a)
              • Ketika fungsi Find tidak menemukan yang dicari, maka hasilnya adalah error value #Value!
              • Fungsi IsNumber digunakan untuk mengeliminasi munculnya error value tersebut. Jika Find tidak menemukannya, maka hasil IsNumber adalah FALSE yang setara 0
          Maka formula :
            - SUM( F$13:F13 )+ N( F$13 )
          akan mendapatkan tambahan proses terhadap array berdasar kriteria di atas, sehingga fungsi SUM akan diganti dengan fungsi SumProduct. Dengan adanya kedua kriteria diatas, maka penetralisir dampak tipedata numerik pada cell F13 dapat dihilangkan karena tidak mungkin tercapainya kondisi yang sesuai kriteria diatas untuk baris 13. Susunan formula menjadi :
            - SumProduct( F$13:F13 * ( $B$13:$B13 = $B14 ) * IsNumber( Find( F$12 & "," , $E$13:$E13 & "," ) ) )
        • Formula sisa item persediaan yang dapat dipakai untuk permintaan baris tersebut adalah formula SumProduct hasil step-2 yang disatukan dengan formula jumlah item terpakai sampai saat itu yang sudah berstatus sebagai pengurang. Susunannya formula hasil step-2 seutuhnya akan berubah menjadi
            =IF(CountIF( $E14 ,"*"& F$12 &"*") * ($C14>0) , SumProduct((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *( _ExExcess_ > 0 )*_ExExcess_) - SumProduct( F$13:F13 * ( $B$13:$B13 = $B14 ) * IsNumber( Find( F$12 & "," , $E$13:$E13 & "," ) ) ) , 0 )
        • Sampai disini, sudah tampak pada kolom H dan I bahwa yang digunakan untuk mengisi cell adalah sisa persediaan yang belum terpakai oleh permintaan sebelumnya
      4. Mendapatkan jumlah item pemenuhan kebutuhan sesuai sisa kebutuhan saat itu
        • Pada hasil formula step-3, didapatkan hasil berupa nilai persediaan tersisa. Formula tersebut belum mempertimbangkan kondisi jika kebutuhan telah dipenuhi oleh slot dan level sebelumnya, maka sisa kebutuhanlah yang harus dipenuhi dan bukan lagi total kebutuhan.
        • Misal pada F14:H14. Ketika F14 telah memenuhi kebutuhan sebanyak 20 item, maka sisa kebutuhan yang harus dipenuhi oleh G14 adalah sebanyak 40 item total kebutuhan - 20 item yang dipenuhi oleh F14, yaitu sebanyak 20 item.
        • Begitu juga ketika akan mencari nilai di H14, maka sisa kebutuhan yang harus dipenuhi oleh H14 adalah sebanyak 40 item total kebutuhan - ( 20 item F14 + 15 item G14 ) = 5 item. Formula step-3 yang belum memperhitungkan hal ini akan menghasilkan 10 item yang berupa sisa persediaan slot 2 level B.
        • Formula sisa kebutuhan dibentuk menyerupai formula sisa persediaan. Perbedaannya adalah pada orientasi formula. Formula sisa persediaan berorientasi vertikal (khusus per lot per level persediaan), sedangkan formula sisa kebutuhan adalah berorientasi horisontal (khusus per permintaan saja).
        • Sisa kebutuhan adalah total kebutuhan - total yang telah terpenuhi sampai saat itu. Total kebutuhan adalah nilai di kolom C (dalam hal ini adalah $C14).
        • Total kebutuhan yang telah terpenuhi adalah penjumlahan seluruh cell sebelumnya. Karena formula harus bisa digunakan oleh slot dan level pertama yang ada dalam daftar slot kelebihan (oleh kolom F), maka titik awalnya adalah kolom E dengan formula Sum yang referensi rujukannya akan bertambah pada orientasi horisontal (dalam sebuah permintaan saja). Formulanya adalah
            Sum( $E14:E14 )
        • Karena data pada kolom E bisa bertipe data numerik, maka perlu antisipasi berupa penetralisir (pengurang) dampak nilai di kolom E, yaitu dengan memanfaatkan fungsi N(). Formula penetralisir dampak nilai numerik di kolom E adalah
            - N( $E14 )
        • Formula total kebutuhan yang telah terpenuhi adalah
            Sum( $E14:E14 )- N( $E14 )
        • Formula sisa kebutuhan akan tersusun sebagai
            $C14 - ( Sum( $E14:E14 ) - N( $E14 ) )
          yang dapat diuraikan tanda pengurangannya menjadi
            $C14 - Sum( $E14:E14 ) + N( $E14 )
        • Sampai disini, sudah didapatkan formula-formula :
          • Sisa kebutuhan yang berbunyi
              $C14-SUM($E14:E14)+N($E14)
          • Sisa persediaan yang berbunyi
              SUMPRODUCT((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *(_ExExcess_>0)*_ExExcess_) -SumProduct(F$13:F13*($B$13:$B13=$B14)*IsNumber(Find(F$12&",",$E$13:$E13&",")))
        • Sebagian kondisi yang terbangun dari Sisa kebutuhan terhadap Sisa persediaan adalah
          • Jika Sisa kebutuhan >= Sisa persediaan maka ambil nilai Sisa persediaan
          • Jika Sisa kebutuhan < Sisa persediaan maka ambil nilai Sisa kebutuhan
          yang artinya, akan selalu mengambil nilai terendahnya. Untuk sebagian kondisi ini, fungsi Min() dapat dimanfaatkan untuk memilih nilai terendah dari Sisa kebutuhan dan Sisa persediaan dalam bentuk rumusan formula
            Min( Sisa kebutuhan , Sisa persediaan )
          Formula seutuhnya akan menjadi
            Min( $C14-SUM($E14:E14)+N($E14) , SUMPRODUCT((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *(_ExExcess_>0)*_ExExcess_) -SumProduct(F$13:F13*($B$13:$B13=$B14)*IsNumber(Find(F$12&",",$E$13:$E13&","))) )
        • Formula step-3 akan berubah menjadi
            =IF(CountIF( $E14 ,"*"& F$12 &"*")*($C14>0) , Min( $C14-SUM($E14:E14)+N($E14) , SUMPRODUCT((_ExProduk_=$B14)*(_ExLevel_=F$12)*(_ExSlot_=F$13) *(_ExExcess_>0)*_ExExcess_) -SumProduct(F$13:F13*($B$13:$B13=$B14)*IsNumber(Find(F$12&",",$E$13:$E13&","))) ) , 0 )
        • Sampai disini, seluruh formula telah terbentuk.

Closing :

Prioritas dalam kalkulasi distribusi diatas tergantung urutan record permintaan dan urutan daftar slot dan level.

File(s) :


Coretan terkait :


kelompok :
ditulis oleh
Kesan : 
1 komentar:

Jumat, 11 Mei 2012

Pemeringkatan data (Ranking)

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
Baca juga tentang macam-macam pemeringkatan disini.

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).

  1. 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 )
      dan menghasilkan nilai 3
  2. 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 )
      kemudian dikurangi dengan 1, sehingga menjadi
        +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

  3. 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 )
      karena saat ini, data yang diproses baru sampai baris ke-4 Excel. Kemudian hasilnya dikurangi dengan 1, sehingga menjadi
        +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
      dengan kejadian setiap proses di dalam cell E8 adalah sebagai berikut

  4. 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 )
      kemudian dikurangi dengan 1, sehingga menjadi
        +CountIF( $B$4:$B$13 , $B4 ) -1
      yang hasilnya nanti dibagi dengan 2 agar menjadi setengahnya. Maka formula akan menjadi
        + ( 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
  5. 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)
          Angka 10 disesuaikan dengan jumlah record. Jika akan menggunakan kolom data composite key sebagai pembentuk, maka formula dapat disusun sebagai
            ( 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 )
    • 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
Contoh hasil pengolahan untuk pengurutan secara menaik (ascending) adalah

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 :


kelompok : , ,
ditulis oleh
Kesan : 
Tidak ada komentar:

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.
  2. 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.
  3. 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 )
  4. 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 :


kelompok : ,
ditulis oleh
Kesan : 
Tidak ada komentar:

Rabu, 09 Mei 2012

Formula peringkat data (Rank multi kriteria 03)

Muatan :

  • Kilasan tentang pengaruh karakteristik data terhadap composite key
  • Contoh kasus
  • Penyusunan Composite Key
  • Rank dengan multi kriteria

Kilasan

Pada coretan sebelumnya, yaitu Formula peringkat data (multi kriteria 02) telah digambarkan tentang penyusunan composite key yang memerlukan beberapa tahap proses. Telah ditekankan pula bahwa karakteristik data setiap kriteria pemeringkatan akan mempengaruhi bentuk composite key. Yang tampak jelas pada coretan tersebut adalah kriteria pemeringkatan yang berisi nilai dengan jumlah digit yang bervariasi. Pembahasan kali ini akan membahas contoh varian nilai data kriteria pemeringkatan yang memiliki jumlah digit yang tetap.

Contoh kasus

Pembahasan kali ini akan menggunakan sebuah data nilai ujian proses seleksi pegawai baru. Total peserta ujian adalah 100.000 (seratus 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 10 sisi dan dinyatakan sebagai kolom Value 01 sampai Value 10. Kolom-kolom value ini memiliki 2 karakteristik dasar, yaitu :
  1. Item nilai dari 0 sampai 100 sebagai bilangan bulat dimiliki oleh kolom-kolom Value selain Value 04 dan Value 10 dan memiliki bobot penilaian sebesar 1 poin.
  2. Item nilai dari 0 sampai 100 dengan 2 digit dibelakang koma dimiliki oleh Value 04 dan Value 10. Bobot Value 04 adalah 3, sedangkan bobot Value 10 adalah 4.
Seluruh nilai kolom-kolom Value direratakan menjadi sebuah nilai akhir dengan memperhitungkan bobot masing-masing kolom Value.

Data tersebut adalah sebagai berikut :

Pemeringkatan dilakukan berdasar 12 (dua belas) kriteria pemeringkatannya sebagai berikut :
  • Kriteria 1
      Nilai rerata hasil ujian seleksi (kolom rerata) secara menurun (descending)
  • Kriteria 2 sampai 11
      Nilai kolom Value 01 sampai kolom Value 10 secara menurun (descending)
  • Kriteria 12
      Nilai 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 100.000 (seratus ribu), yang berarti membutuhkan 6 digit.
  2. Nilai-nilai kolom Value selain Value 04 dan Value 10
    • Kolom-kolom ini berisi nilai dari 0 sampai 10 sebagai bilangan bulat, yang berarti akan membutuhkan 3 digit setiap kolom Value-nya.
    • Composite key berdasar kolom-kolom ini akan berupa nilai kolom itu sendiri, sehingga maksimal hanya dibutuhkan 3 digit saja setiap kolomnya.
  3. Nilai-nilai kolom Value 04 dan Value 10
    • Kedua kolom ini berisi nilai dari 0 sampai 100 dengan 2 digit dibelakang koma, yang berarti akan membutuhkan 5 digit setiap kolomnya.
    • Jumlah digit dari total record data adalah 6 digit. Maka kedua kolom ini akan menggunakan nilainya sendiri sebagai nilai penyusun composite key dibandingkan dengan menggunakan nilai rank.
    • Kedua kolom ini memerlukan proses pembulatan nilai. Dengan kondisi 2 digit dibelakang koma, maka masing-masing kolom perlu dikali 100 untuk memelihara jumlah digit dibelakang koma agar tetap valid. Kemudian dilanjutkan dengan proses pembulatan dengan menggunakan fungsi Int.
    • Formula penyusun composite key untuk kedua kolom ini adalah :
      • Kolom Value 04
          Int( E4 * 100 )
      • Kolom Value 10
          Int( K4 * 100 )
  4. Nilai rerata
    • Sebagai kriteria pertama dan merupakan hasil suatu proses komputasi.
    • Jumlah digit dari nilai-nilai dalam kolom ini akan bervariasi mulai dari 0 sampai 100 dengan 4 digit angka dibelakang koma atau lebih.
    • Composite key berdasar kolom ini akan berupa hasil komputasi dengan fungsi rank, sehingga maksimal hanya dibutuhkan 6 digit saja (sebanyak jumlah record data).
    • Ada penyusun composite key yang masih menggunakan nilai asli (bukan hasil rank) dan nilai-nilai tersebut tidaklah pasti bersifat unik.
    • Pengurutan dalam fungsi rank untuk kolom rerata harus sama dengan karakterisitk nilai kolom rerata itu sendiri, yaitu nilai terendah adalah 0 dan akan terus naik hingga mencapai nilai tertingginya. Hal ini menunjukkan pengurutan yang menaik (ascending). Maka fungsi rank terhadap kolom rerata harus diurutkan menaik (ascending).
    • Formula penyusunan composite key berdasar kolom ini adalah :
        Rank( L4 , $L$4:$L$13 , 1)
  5. ID peserta (rec_id)

    • Terdiri dari 6 digit tetap dan bersifat unik.
    • Kolom ini menjadi kriteria ke-12 dari pemeringkatan, tetapi kriteria pertama yang harus diurutkan ascending.
    • Kolom ini akan disusun menjadi satu composite key tersendiri karena memiliki pengurutan yang berbeda dengan kriteria sebelumnya (kriteria ke-11 berdasar kolom Value 10)
    • Adanya penggunaan nilai asli kolom pada kriteria yang lain dan dikarenakan kolom rec_id memungkinkan untuk menggunakan nilainya sendiri, maka penyusun composite berdasar kriteria ini akan berisi nilai kolom rec_id.
  6. Composite key level 1
    • Jumlah digit seluruh kriteria adalah 46 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 beberapa bagian, yaitu :
      • Composite Key 1.1
        • Berisi 6 digit berdasar kolom rerata, 3 digit berdasar kolom Value 01, 3 digit berdasar kolom Value 02, 3 digit berdasar kolom Value 03 total 15 digit.
        • Keempat kolom yang disatukan menjadi sebuah composite key memiliki cara pengurutan sama bila ada penyusun yang menggunakan nilai aslinya yang pasti terurut ascending (dari 0 sampai sekian).
        • Susunannya berupa wwwwww xxx yyy zzz
        • Formula penyusun composite key 1.1 adalah :
            = Rank( L4 , $L$4:$L$13 , 1 ) * 10^9 + B4 * 10^6 + C4 * 1000 + D4
        • Formula diletakkan pada kolom M mulai baris ke-4 Excel (cell M4). Kemudian di-copy ke baris data berikutnya.
      • Composite Key 1.2
        • Berisi 5 digit berdasar kolom Value 04, 3 digit berdasar kolom Value 05, 3 digit berdasar kolom Value 06, 3 digit berdasar kolom Value 07 total 14 digit.
        • Keempat kolom yang disatukan menjadi sebuah composite key memiliki cara pengurutan sama bila ada penyusun yang menggunakan nilai aslinya yang pasti terurut ascending (dari 0 sampai sekian).
        • Susunannya berupa wwwww xxx yyy zzz
        • Formula penyusun composite key 1.2 adalah :
            = Int( E4 * 100 ) * 10^9 + F4 * 10^6 + G4 * 1000 + H4
        • Formula diletakkan pada kolom N mulai baris ke-4 Excel (cell N4). Kemudian di-copy ke baris data berikutnya.
      • Composite Key 1.3
        • Berisi 3 digit berdasar kolom Value 08, 3 digit berdasar kolom Value 09, 5 digit berdasar kolom Value 10 total 11 digit.
        • Ketiga kolom yang disatukan menjadi sebuah composite key memiliki cara pengurutan sama bila ada penyusun yang menggunakan nilai aslinya yang pasti terurut ascending (dari 0 sampai sekian).
        • Sisa 4 digit untuk mencapai batas digit presisi cell Excel yang sebesar 15 digit tidak dapat diisi oleh kriteria berikutnya, yaitu berdasar rec_id yang membutuhkan 6 digit. Selain itu, rec_id harus diurutkan berbeda dengan penyusun composite key 1.3 ini. Maka kriteria berdasar rec_id akan diletakkan pada composite key berikutnya.
        • Susunannya berupa xxx yyy zzzzz
        • Formula penyusun composite key 1.3 adalah :
            = I4 * 10^8 + J4 * 10^5 + Int( K4 * 100 )
        • Formula diletakkan pada kolom O mulai baris ke-4 Excel (cell O4). Kemudian di-copy ke baris data berikutnya.
      • Composite Key 1.4
        • Berisi 6 digit berdasar kolom rec_id.
        • Susunannya berupa xxxxxx
        • Formula penyusun composite key 1.4 adalah :
            = A4
        • Formula diletakkan pada kolom P mulai baris ke-4 Excel (cell P4). Kemudian di-copy ke baris data berikutnya.
      • Seluruh composite level 1 masih terurut sesuai karakteristik nilai asli kolom, yaitu ascending.
      • Bentuk composite key level 1 adalah :
  7. Composite key level 2
    • Composite key level 1 terdiri dari 4 item, mulai dari Composite Key 1.1 sampai Composite Key 1.4, sehingga memerlukan composite key level 2.
    • Disusun berdasar rank setiap item composite key level 1. Setiap item composite key level 1 akan berisi 6 digit.
    • Proses pengurutan masing-masing composite key level 1 akan disesuaikan dengan isi dari masing-masing composite key level 1 tersebut, yaitu :
      • Composite key 1.1 berisi kolom kriteria yang harus diiurutkan secara descending, sedangkan setiap nilai penyusunnya masih diurutkan secara ascending akibat penggunaan nilai aslinya. Maka composite key 1.1 akan diurutkan secara descending dengan formula :
          RANK( M4 , $M$4:$M$13 )
      • Composite key 1.2 berisi kolom kriteria yang harus diiurutkan secara descending, sedangkan setiap nilai penyusunnya masih diurutkan secara ascending akibat penggunaan nilai aslinya. Maka composite key 1.2 akan diurutkan secara descending dengan formula :
          RANK( N4 , $N$4:$N$13 )
      • Composite key 1.3 berisi kolom kriteria yang harus diiurutkan secara descending, sedangkan setiap nilai penyusunnya masih diurutkan secara ascending akibat penggunaan nilai aslinya. Maka composite key 1.3 akan diurutkan secara descending dengan formula :
          RANK( O4 , $O$4:$O$13 )
      • Composite key 1.4 berisi kolom kriteria yang harus diurutkan secara ascending dengan formula :
          RANK( P4 , $P$4:$P$13 , 1 )
    • Total digit composite key level 1 yang harus disusun menjadi composite key level 2 adalah sebanyak 24 digit. Maka dibutuhkan 2 item composite key level 2, yaitu :
      • Composite Key 2.1
        • Berisi 6 digit berdasar composite key 1.1 terurut descending, 6 digit berdasar composite key 1.2 terurut descending.
        • Susunannya berupa xxxxxx yyyyyy
        • Formula penyusunnya :
            =RANK( M4 , $M$4:$M$13 ) *10^6+RANK( N4 , $N$4:$N$13 )
        • Formula diletakkan pada kolom Q baris 4 Excel (cell Q4), kemudian di-copy ke baris berikutnya.
      • Composite Key 2.2
        • Berisi 6 digit berdasar composite key 1.3 terurut descending, 6 digit berdasar composite key 1.4 terurut ascending.
        • Susunannya berupa xxxxxx yyyyyy
        • Formula penyusunnya :
            =RANK( O4 , $O$4:$O$13 ) *10^6+RANK( P4 , $P$4:$P$13 , 1 )
        • Formula diletakkan pada kolom R baris 4 Excel (cell R4), kemudian di-copy ke baris berikutnya.
    • Composite key level 2 telah terurut sesuai dengan seluruh kriteria.
    • Bentuk composite key level 2 akan tampak seperti gambar berikut ini
  8. Composite key level 3
    • Composite key level 2 terdiri dari 2 item.
    • Disusun berdasarkan rank masing-masing item composite key level 2 dan diurutkan secara ascending karena composite key level 2 telah terurut sesuai seluruh kriteria kasus.
    • Formula penyusun composite key level 3 berdasar setiap item composite key level 2 adalah :
      • Berdasar composite key 2.1
          RANK( Q4 , $Q$4:$Q$13 , 1 )
      • Berdasar composite key 2.2
          RANK( R4 , $R$4:$R$13 , 1 )
    • Setiap item composite key level 2 yang diproses dengan fungsi Rank akan menghasilkan 6 digit. Maka total digit dari 2 item composite key level 2 adalah 12 digit.
    • Composite key level 3 hanya membutuhkan 1 item saja, yaitu composite key 3.1 yang berisi :
      • 6 digit berdasar rank composite key 2.1 terurut ascending sebagai xxxxxx
      • 6 digit berdasar rank composite key 2.2 terurut ascending sebagai yyyyyy
    • Susunan composite key 3.1 adalah xxxxxx yyyyyy
    • Formula penyusun composite key 3.1
        =RANK( Q4 , $Q$4:$Q$13 , 1 ) *10^6+RANK( R4 , $R$4:$R$13 , 1 )
    • Formula diletakkan pada kolom S baris 4 Excel (cell S4), kemudian di-copy ke baris berikutnya.
    • Composite Key 3.1 adalah kolom composite tunggal dan menjadi yang terakhir untuk digunakan pada proses komputasi rank terhadap data.
    • Bentuk composite key level 3 akan tampak seperti gambar berikut ini
  9. Susunan seluruh composite key adalah sebagai berikut :
Tabel data hingga composite key yang siap digunakan adalah sebagai berikut :
Composite key level 1 dan composite key level 2 dapat disembunyikan, memanfaatkan Hide Column maupun fitur Data Group.

Rank dengan multi kriteria

Penyelesaian kasus diatas dengan menggunakan fungsi Rank dilakukan terhadap kolom Composite Key 3.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 3.1 untuk peserta dengan ID 09159, yaitu cell S4
      • referensi_data_semesta_peringkat : seluruh data composite key 3.1, yaitu range S4:S13 yang absolut tetap ($S$4:$S$13)
      • [cara_mengurutkan] : opsi diurutkan secara menaik (ascending), yaitu 1
    • Susunan formula dengan fungsi Rank :
        =RANK( $S4 , $S$4:$S$13 , 1 )
    • Hasil formula adalah bernilai 4
  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 T).
    • Pengurutan terhadap kolom composite key 3.1 adalah dengan cara ascending dan bisa menggunakan formula kasus 1 kemudian di-copy ke baris data berikutnya.
    • Susunan formula dengan fungsi Rank :
        =RANK( $S4 , $S$4:$S$13 , 1 )
Tabel hasil penyelesaian kedua kasus di atas adalah :
  • Hasil penyelesaian kasus 1 : cell T4 (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. Ketika salah satu penyusun sebuah item composite key menggunakan nilai data dan bukan hasil proses pemeringkatan, maka setiap penyusun item tersebut harus memiliki cara pengurutan yang sama. Pembahasan berikutnya adalah contoh pemeringkatan terhadap data kualitatif yang biasanya bertipe data teks, sehingga akan lebih mudah memahami ketergantungan composite key terhadap karakteristik data setiap penyusunnya.

File(s) :

     

Coretan terkait :

kelompok : ,
ditulis oleh
Kesan : 
Tidak ada komentar:

Senin, 07 Mei 2012

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.

Sumber : http://excel-mr-kid.blogspot.com/2012/09/formula-pendistribusian-persediaan.html
Label:

1 komentar:

  1. Adiwin135 Januari 2017 pukul 11.26

    http://www.fathanblogger.net
    Fathan Blogger : .net Keyword : Tutorial , Seo , Blogger , Freelancer , Smartphone , Review , Aplikasi.

    BalasHapus

GoBlog Theme Banner 300x250

Artikel Populer :