Kamis, 31 Maret 2011

Formula sort data pada data teks


Muatan :

  • Kilasan tentang sort data
  • Perlakuan Excel terhadap karakter
  • Prinsip sort dengan formula
  • Contoh kasus disertai per langkah proses kalkulasi formula di sebuah cell


Kilasan

Penyusunan laporan sering berbentuk data terurut (sorted), baik menaik (ascending) maupun menurun (descending). Pada laporan yang bersumber dari data terurutkan, tentu akan menghasilkan data hasil yang terurutkan juga. Permasalahan bisa timbul ketika kriteria pengurutan data hasil di laporan bukan lagi seperti kriteria pengurutan pada data sumber. Misalnya laporan yang berupa summary berisi agregat dari data sumber akan diurutkan secara menurun berdasar nilai deviasi dua kolom laporan. Sedangkan nilai deviasi adalah hasil suatu kalkulasi terhadap data sumber.



Kelakuan fitur Sort pada Excel yang menuntut dilakukannya pengurutan ulang (reapply) merupakan proses yang dicoba untuk dihindari. Pada kondisi-kondisi semacam itulah dibutuhkan formula penyusun suatu laporan yang mampu menyusun secara terurut.


Perlakuan Excel terhadap karakter

Sebelum melangkah lebih jauh, perlu dimaklumi bahwa Excel memperlakukan karakter secara case insensitive. Hal ini juga berlaku pada proses sort ataupun perbandingan karakter. Pada beberapa sisi, hal ini membawa keuntungan tersendiri, tetapi pada sisi lain bisa menjadi penghambat yang cukup serius.

Coba perhatikan sifat karakter kosong (""), yang sering juga disebut sebagai NULLSTRING, acap kali diperoleh dari hasil sebuah formula yang berusaha menjadikan blank sebagai nilai keluaran formula. Pada hakikatnya, Excel berusaha mengabaikan blank (Empty), sehingga pada beberapa proses seperti sort, posisi blank cell selalu diletakkan pada bagian bawah hasil.

Sedangkan pada proses perbandingan, nullstring adalah sama dengan blank (nilai default sebuah cell ketika belum diisi apapun). Nullstring diposisikan sebagai sebuah karakter antara angka 9 dan karakter ':' (titik dua). Konversi ke tipe data numerik akan menghasilkan 0 pada blank, dan akan menghasilkan error value #VALUE! pada nullstring.
Gambar di atas menunjukkan hasil pengurutan menaik dari kolom sort_ascending. Nullstring terletak di bawah angka 7 dan di atas karakter 'A'. Blank diabaikan, sehingga terletak dipaling bawah. Pada tabel ASCII, karakter huruf kapital memang terletak di atas karakter huruf kecil. Jika pada tabel di atas karakter 'A' diubah menjadi karakter 'a' dan karakter 'z' diubah menjadi karakter 'Z', kemudian diurutkan kembali secara menaik, maka didapatkan bahwa posisi karakter 'a' di atas posisi karakter 'Z'.


Sort dengan formula

Proses pengurutan data dengan fitur Sort bisa menggunakan beberapa kriteria. Pengurutan menggunakan formula lebih mudah menggunakan satu kriteria. Jika akan mengurutkan berdasar banyak kriteria, maka diperlukan sebuah composite key. Composite key bisa disusun sebagai penggabungan beberapa kolom kriteria menjadi sebuah kolom. Tipe data composite key bisa berupa numerik (diusahakan) ataupun teks.

Penggabungan kolom yang bertipe numerik pada penyusunan composite key yang bertipe teks harus memiliki panjang yang sama, agar proses pengurutan tetap benar. Misalkan ketika pengurutan menaik pada composite key bernilai {"AAA;19","AAA;123"} akan menghasilkan {"AAA;123","AAA;19"}, sedangkan nilai 19 mestinya lebih dulu daripada nilai 123. Susunan nilai-nilai composite key untuk contoh di atas akan memberi hasil urutan yang benar jika tersusun sebagai {"AAA;019","AAA;123"}.

Prinsip dasar proses sort dengan formula adalah mengurutkan berdasar jumlah data setiap item data sumber dibandingkan item lainnya, hingga didapatkan posisi data tersebut pada data sumber. Jadi yang diurutkan adalah nilai jumlah data setiap item hasil perbandingan dengan item lainnya. Pada data yang bersifat unique, hal ini akan relatif lebih mudah.


Contoh penyusunan data terurut dengan formula

Gambar berikut ini adalah contoh data (kolom A) beserta hasil proses pengurutan secara menaik (Asc) dan menurun (Desc).
Data n_data dan pengali adalah cell bantu untuk memudahkan proses kalkulasi dalam penyusunan data terurutkan. Data n_data adalah jumlah record data sumber. Data pengali adalah cell bantu untuk menyusun data numerik terurut.

Data numerik terurut itu terdiri dari jumlah data sumber yang kurang dari setiap item data sumber beserta posisi item tersebut dalam data sumber. Keberadaan error value #NUM! menjadi tanda bahwa seluruh data yang sesuai kriteria telah dimasukkan ke dalam data hasil. Cell yang berwarna latar biru (cell F12) adalah cell yang dijabarkan per langkah proses kalkulasi formula sort. Array formula yang digunakan untuk urut menaik (ascending) adalah (pada data ke-11, yaitu cell F12)
=INDEX($A$2:$A$18,MOD(SMALL(COUNTIF($A$2:$A$18,"<"&$A$2:$A$18)*$D$2
+ROW($A$2:$A$18)-ROW($A$1),ROW(11:11)),$D$2))

Untuk urut menurun (descending), cukup mengganti fungsi SMALL menjadi fungsi LARGE, sehingga menjadi (pada data ke-11, yaitu cell H12)
=INDEX($A$2:$A$18,MOD(LARGE(COUNTIF($A$2:$A$18,"<"&$A$2:$A$18)*$D$2
+ROW($A$2:$A$18)-ROW($A$1),ROW(11:11)),$D$2))



Proses kalkulasi formula di cell F12

Pada cell F12, berisi array formula untuk penyusunan secara menaik sebagai berikut :
=INDEX($A$2:$A$18,MOD(SMALL(COUNTIF($A$2:$A$18,"<"&$A$2:$A$18)*$D$2
+ROW($A$2:$A$18)-ROW($A$1),ROW(11:11)),$D$2))

Langkah-langkah proses kalkulasi di dalam formula tersebut dapat dikelompokkan dalam 3 (tiga) tahap, yaitu :
  1. Menyusun nomor urut setiap item disertai nomor index data dan mengurutkannya (Step1 sampai Step4)
    Bagian array formula yang dikerjakan pada tahap ini adalah :
    SMALL(COUNTIF($A$2:$A$18,"<"&$A$2:$A$18)*$D$2
    +ROW($A$2:$A$18)-ROW($A$1),

    tanpa ada bagian parameter data ke-k yang akan diambil pada fungsi SMALL. Artinya, sampai pada tahap mengurutkan data saja. Gambar berikut adalah langkah setiap proses pada tahap ini.
    Cell D2 adalah cell bantu yang telah dijelaskan di atas.

    Step1
      Proses menyusun array jumlah item yang nilainya kurang dari masing-masing item data. Elemen array hasil adalah sebanyak jumlah record data. Jumlah masing-masing item dalam data sumber ini merupakan nomor urut data secara menaik.
    Step2 dan Step3
      Proses menyusun data numerik terurut yang telah menyimpan nomor index data. Hasil Step1 barulah jumlah masing-masing item, tetapi letak item tersebut belum ada, sehingga ketika diurutkan tidak dapat diketahui lagi posisi data di dalam data sumber. Hal ini akan menimbulkan kesulitan dalam usaha mengambil nilai data yang akan ditampilkan.

      Penggunaan fungsi Match memungkinkan untuk mengetahui posisi data, tetapi pada kasus ini, dibutuhkan kalkulasi ulang array bagian COUNTIF($A$2:$A$18,"<"&$A$2:$A$18) untuk dijadikan sebagai array lookup fungsi Match. Sedangkan bagian SMALL(COUNTIF($A$2:$A$18,"<"&$A$2:$A$18),ROW(11:11)) sebagai lookup value fungsi Match. Susunannya akan menjadi :
      MATCH(SMALL(COUNTIF($A$2:$A$18,"<"&$A$2:$A$18),ROW(11:11)),
      COUNTIF($A$2:$A$18,"<"&$A$2:$A$18),0)


      Usaha untuk mempersedikit proses kalkulasi ulang array formula yang sama dilakukan dengan menyertakan nomor index posisi data pada nomor urutan yang berupa jumlah item hasil Step1. Cell bantu D2 adalah penyusun jumlah digit untuk wadah nomor index posisi data. Nilai cell bantu D2 bersifat dinamis sesuai jumlah data, sehingga setiap item pasti mendapat ruang yang cukup di belakan nomor urut data hasil Step1.

      Penyiapan ruang dilakukan pada Step2 dengan mengalikan hasil Step1 dengan nilai cell bantu D2. Ruang yang dimaksud adalah jumlah digit di belakang nomor urut data. Kemudian nomor index posisi data diletakkan pada ruang yang telah disiapkan melalui Step2. Peletakan dilakukan oleh Step3 dengan cara menjumlahkan hasil Step2 dengan nomor index data. Nomor index data didapat dari nomor baris Excel data tersebut dikurangi nomor baris Excel header data, yaitu bagian :
      +ROW($A$2:$A$18)-ROW($A$1)
    Step4
      Proses pengurutan dilakukan pada step ini. Fungsi SMALL untuk pengurutan menaik dan fungsi LARGE untuk pengurutan menurun. Perlu diperhatikan bahwa pada step ini, fungsi SMALL dikalkulasi sampai tahap mengurutkan saja, tanpa proses pengambilan data, karena tidak disertai input parameter bagian nilai ke-k. Tampak bahwa hasil urutan berdasar nomor urut hasil Step1 dan nomor index posisi data dapat diketahui pada n digit terakhir tergantung jumlah angka 0 pada nilai cell bantu D2. Array terurut hasil step ini akan digunakan pada tahap selanjutnya.
  2. Ekstraksi posisi data dari hasl susunan array terurut (Step5 dan Step6)
    Tahap ini adalah proses pengambilan nomor index data yang dibutuhkan. Index data didapat dari proses ekstraksi hasil array yang diurutkan pada Tahap 1. Gambar berikut ini adalah proses tahap 2 pada cell F12, yang merupakan lanjutan proses tahap 1.
    Ekstraksi diawali dengan mengambil hasil pengurutan oleh fungsi SMALL pada Tahap 1, untuk item array tertentu. Cell F12 adalah data hasil ke-11, maka akan diambil data ke-11 dari array hasil fungsi SMALL.

    Pada tahap ini, hasil bisa diletakkan pada sebuah kolom bantu. Hal ini akan mempermudah pengambilan data untuk banyak kolom yang merujuk pada baris yang sama. Selain itu, kalkulasi tahap 1 dan tahap 2 cukup dilakukan 1 kali.

    Step5
      Proses lanjutan fungsi SMALL yang berupa kegiatan pengambilan hasil pengurutan, dengan menentukan nilai input parameter data ke-k. Hasil yang diambil untuk cell F12 adalah data ke-11, maka digunakanlah fungsi Row dengan susunan :
      ROW(11:11)

      Jika bagian input parameter data ke-k ini diisi sebuah item, maka hasilnya adalah sebuah item. Jika diisi array numerik 1 dimensi, maka hasilnya berupa array 1 dimensi. Hasil berupa array dituliskan kedalam cells berjumlah n item array yang sebaris atau sekolom tergantung orientasi array 1 dimensi yang digunakan pada bagian input parameter data ke-k. Untuk menghasilkan langsung seluruh item hasil pengurutan oleh fungsi Small, maka digunakan ROW(1:17). Hasil dituliskan pada 17 cells sekolom.
    Step6
      Proses ini berfungsi untuk mendapatkan nomor index data. Hasil Step5 diekstraksi berdasar nilai pada cell bantu D2 yang menjadi penyusun ruang untuk nomor index data. Penyusunan ruang melalui proses perkalian nomor urut data dengan nilai cell bantu D2. Ruang nomor index data selalu berupa jumlah digit dibelakang nomor urut. Jumlah digit tersebut adalah sejumlah angka nol pada nilai cell bantu D2. Maka nomor index data adalah sisa bagi hasil Step5 dengan nilai cell bantu D2. Fungsi untuk mengkalkulasi sisa bagi adalah fungsi MOD yang membutuhkan input parameter berupa nilai yang akan dibagi dan nilai pembaginya. Susunan formula secara garis besar adalah :
      MOD( formula_step5 , $D$2 )
  3. Pengambilan nilai data sebagai item data hasil (Step7)
      Tahap ini berisi proses untuk mengambil nilai data sumber berdasar nomor index data. Proses ini bisa menggunakan fungsi OFFSET atau fungsi INDEX. Tidak tertutup kemungkinan untuk menggunakan fungsi yang lainnya. Kasus ini menggunakan fungsi INDEX. Nilai data sumber adalah pada range A2:A8 dengan nilai index data adalah hasil Step6. Susunannya secara umum adalah :
      =INDEX( $A$2:$A$18 , formula_step6 )
      Berikut ini adalah gambar dari proses Step7.
      Pada laporan yang akan menampilkan banyak kolom hasil dan telah menggunakan kolom bantu untuk menghasilkan nilai-nilai pada step7, maka rujukan ke formula_step7 pada fungsi INDEX bisa diganti dengan merujuk ke cell bantu baris tersebut, pada kolom bantu yang berisi hasil Step7.


Closing :

Step1 pada penerapan sort data untuk data numerik cukup dengan merujuk ke range data sumber, tanpa perlu kalkulasi menggunakan fungsi CountIF.


File(s) :



Coretan terkait :



9 komentar:

  1. Mr. Kid, napa ndk ada contoh softcopynya di Excel agr kami bisa down load dan lbh mudah ut mempelajarinya.

    Darwis Mks

    BalasHapus
  2. @Darwis : Considered it done.
    Silakan dinikmati Pak. Terimakasih atas usulannya.

    BalasHapus
  3. terimakasih coretan excelnya. sangat bermanfaat buat saya yang lagi belajar excel...

    BalasHapus
  4. @cadex : sama-sama Pak. Selamat Belajar. Jika ada usulan materi coretan, mohon dikabari ya.

    BalasHapus
  5. Mr. Kid kenapa harus pake kolom pengali ? , dan apabila saya tidak memakai pengali apakah rumusnya akan berjalan ? , maklum masih belajar

    BalasHapus
  6. @Wong_Semarang
    Nilai pengali adalah sarana untuk menyertakan indeks baris data. Artinya, posisi record yang telah diurutkan akan dengan mudah diperoleh dengan memanfaatkan nilai yang disimpan dalam digit pengali.
    Misalkan saja, ada data transaksi dengan kolom konsumen, tanggal transaksi dan sebagainya. Ketika akan menampilkan record yang terurut berdasar nama konsumen, tentu ingin diambil seluruh record pada seluruh tanggal transaksinya. Dengan adanya nilai pengali, indeks baris record setiap konsumen pada tanggal yang berbeda dapat diketahui dengan mudah.
    Ketika data yang diurutkan berupa sebuah daftar alias berupa data satu kolom atau satu baris saja, maka nilai pengali tidak ada manfaatnya.

    BalasHapus
  7. Mr Kid, bagaimana kalau saya pakai array sampai dengan baris ke 30 (C2:C30) sedangkan data yang terisi hanya sampai 17 (C2:C18) alias dari C19:C30 cell tersebut kosong. Mohon bantuannya. terima kasih

    BalasHapus
    Balasan
    1. Berhubung array formula selalu mengerjakan seluruh cells yang dirujuk, maka say lebih suka merekomendasikan untuk membuat rujukan yang dinamis dibanding menambah kriteria untuk mengabaikan baris yang masih kosong dalam rujukan.

      Misal ada sebuah data dengan header di baris 1 mulai A1 sampai K1, dan record mulai baris 2, dengan kolom B pasti berisi nilai bukan hasil formula dan menjadi kolom kunci (misal kode tertentu yang harus diisi). Record data baru sampai baris 18 dan kolom B penuh terisi sampai baris 18. Sedangkan array formula akan memeriksa data di kolom C, maka rujukan ke kolom C bisa berbunyi :
      $C$2:Index( $c:$c , Max( 2 , CountA( $b:$b ) ) )
      yang akan menghasilkan rujukan dinamis mengikuti isi kolom B, yaitu ke C2:C18.
      Ketika record data bertambah (misal sampai baris 20 alias kolom B terisi sampai baris 20), maka rujukan di atas akan menghasilkan C2:C20.

      Hal diatas bisa lebih baik daripada menggunakan rujukan statis C2:C30 yang dimaksudkan untuk berjaga-jaga bila suatu waktu nanti record data mencapai baris 30.

      ;)





      Hapus
    2. @ Mr. Kid
      terima kasih FB nya, it's good

      Hapus