Kamis, 24 Maret 2011

LookUp banyak kriteria


Muatan :


  • Sekilas tentang beberapa komponen utama dalam lookup
  • Lookup banyak kriteria secara exact
  • Lookup banyak kriteria dengan referensi berupa interval kelas (grup)


Kilasan

Kegiatan lookup memiliki 4 komponen utama, yaitu :
  1. Nilai yang akan di-lookup (lookup_value)
  2. Data/referensi pencarian nilai yang di-lookup (lookup_array)
  3. Cara pencariannya (sama persis ~ exact ~ atau pendekatan ~ approximate ~ )
  4. Data/referensi hasil output (result_array)
Susunan nomor 2 dan nomor 3 akan mempengaruhi cara penyusunan formula.




Lookup banyak kriteria secara exact

Data yang digunakan adalah sebuah report. Hal ini memang kurang memenuhi sifat ke-database-an, tetapi sangat sering terjadi.
Permasalahan yang menggunakan data tersebut adalah :
Langkah pertama adalah penjabaran masalah. Hasil penjabarannya sebagai berikut :
  1. Nilai yang akan di-lookup (lookup_value)
    • Bagian pencarian pada sisi baris :
      Baris data yang diambil adalah berdasar kriteria Tanggal dan Lokasi yang sesuai.
    • Bagian pencarian pada sisi kolom :
      Kolom data yang diambil adalah berdasar kriteria Level dan Shift (Siang atau Malam) yang sesuai.
  2. Data/referensi pencarian nilai yang di-lookup (lookup_array)
    Nilai yang di-lookup adalah sesuai atau tidak sesuai, maka disusunlah seluruh kondisi berdasar kriteria yang menghasilkan pernyataan sesuai atau tidak sesuai.
    • Bagian pencarian pada sisi baris :
      Susunannya adalah (A3:A11=C15)*(B3:B11=C16) yang menghasilkan 1 (sesuai) atau 0 (tidak sesuai).
    • Bagian pencarian pada sisi kolom :
      Susunan untuk shift Malam adalah (C1:E1=C17)*(D2:F2=C18) yang menghasilkan 1 (sesuai) atau 0 (tidak sesuai). Untuk shift Siang susunanya adalah (C1:E1=C17)*(C2:E2=C18). Hal ini disebabkan oleh susunan level yang hanya memiliki angka pada shift siang saja. Ketika dilihat oleh manusia, semua baik-baik saja dan jelas bahwa baik shift siang maupun malam memiliki level yang sama sesuai nilai level di atas nilai shift tersebut. Bagi komputer, shift malam tidak memiliki nilai level. Maka alangkah baiknya jika ada baris bantu yang berisi nilai level untuk seluruh item shift.
  3. Cara pencariannya (sama persis ~ exact ~ atau pendekatan ~ approximate ~ )
    Nilai yang di-lookup hanya ada dua kemungkinan, yaitu sesuai atau tidak sesuai, maka tipe pencariannya adalah sama persis (exact).
  4. Data/referensi hasil output (result_array)
    Hasil selalu mengikuti posisi shift sebagai header terlengkap. Karena range berdasar Shift adalah D2:F2, maka hasil output adalah seluruh data dari kolom D sampai kolom F, yaitu D3:F11.
Formula dapat disusun berupa array formula, karena ada bagian yang membutuhkan kerja array, yaitu komputasi berdasar beberapa kriteria. Fungsi Index dapat digunakan untuk menghasilkan output. Fungsi Match sebagai proses lookup. Bagian row_num dan col_num pada fungsi Index akan berupa array formula fungsi Match yang bersesuaian, yaitu bagian pencari baris pada sisi row_num dan bagian poencari kolom pada sisi col_num.

Ketika memanfaatkan baris bantu untuk susunan Level, maka akan tampak seperti gambar di bawah ini.
Formula bagian berdasar kriteria Level dan Shift berubah menjadi lebih universal. Rujukan yang dibandingkan melalui fungsi Match adalah seluruh kolom header. Rujukan untuk fungsi Index juga disesuaikan menjadi seluruh kolom data.

Pada contoh di atas, seluruh tanggal bersifat unique. Formula tetap berlaku meskipun tanggal tidak unique, tetapi secara keseluruhan (kombinasi tanggal dan lokasi) tetaplah unique.


Lookup banyak kriteria dengan referensi berupa interval kelas (grup)

Proses lookup dengan data referensi lookup yang berisi interval kelas (grup) selalu terurut. Setidaknya berdasar sebuah field utama yang menentukan tata urutan interval kelas. Misalnya field waktu menjadi penentu tata urutan interval kelas.

Tabel di bawah ini adalah data snapshot dari data job_schedule pada periode bulan Juni.
Field tanggal pada kasus ini menunjukkan tanggal dimulainya tugas Supervisor. Masa tugas Supervisor adalah sampai masa pergantian kepada Supervisor lainnya di tanggal tertentu sesudahnya. Masa tugas setiap Supervisor berbeda-beda, tergantung Lokasi dan Level pekerjaan, selain tergantung Golongan si Supervisor itu sendiri. Maka dapat disimpulkan bahwa field tanggal adalah nilai-nilai batas bawah dari suatu interval masa kerja seorang Supervisor. Misalnya record ke-1 (baris 2 Excel), Supervisor Aaa mulai bertugas dari tanggal 01-06-2010 sampai tanggal 05-06-2010. Pada tanggal 06-06-2010, si Aaa akan digantikan oleh si Ddd (record ke-6 ~ baris 7 Excel).

Data memiliki fields kunci yang unique, dengan tata pengurutan interval kelas berdasar field tanggal. Fields kunci data di atas adalah field Tanggal, Lokasi, Level, dan Shift. Field Supervisor adalah sebuah field property. Tabel tersusun sederhana memenuhi kaidah database (tabel normal).

Pada contoh kasus ini, akan tampak pengolahan yang relatif lebih mudah dibandingkan dengan penggunaan data dalam bentuk report seperti contoh kasus yang sebelumnya. Permasalahan yang menggunakan data tersebut adalah sebagai berikut.
Nilai kriteria untuk field Lokasi, Level, dan Shift adalah exact, tetapi nilai kriteria field Tanggal adalah pendekatan (approximate). Maka yang perlu diketahui adalah interval kelas untuk kriteria tanggal tersebut (10-06-2010) yang ke-unique-annya dipengaruhi oleh field utama lainnya, yaitu field Lokasi, Level, dan Shift. Dengan diketahuinya interval kelas pada kriteria tersebut, maka dapat diketahui Supervisor penanggung jawabnya.

Contoh solusi yang digunakan adalah array formula Index(Match) untuk memudahkan pemahaman konsepnya. Bagian Index untuk mengambil nilai output sesuai permasalahan, dan bagian Match untuk kegiatan lookup sesuai kriteria yang ada. Susunan input parameter pada formula Index(Match) adalah :
  • Referensi untuk fungsi Index adalah data hasil, yaitu kolom Supervisor (E2:E13)
  • Nilai lookup untuk fungsi Match adalah nilai kriteria tanggal, yaitu 10-06-2010 (cell C17)
  • Kondisi selain interval kelas adalah kesesuaian antara data dan kriteria untuk fields Lokasi, Level, dan Shift, yang harus terpenuhi seluruhnya. Susunan kondisinya adalah (B2:B13=C18)*(C2:C13=C19)*(D2:D13=C20)
  • Array data batas bawah interval kelas adalah data tanggal yang seusai antara data dan kriteria pada field Lokasi, Level,dan Shift. Oleh sebab itu digunakan fungsi IF sebagai pemilih data tanggal, sehingga untuk kondisi yang tidak sesuai, nilai item array batas bawah interval kelas akan diisi dengan nilai yang tipe datanya tidak sama dengan tipe data tanggal. Penggunaan nilai default fungsi IF pada kondisi salah, yaitu nilai boolean FALSE, adalah upaya untuk membedakan dengan tipe data untuk record yang sesuai (tipe data datetime pada kolom tanggal). Susunan array lookup untuk fungsi Match adalah
      IF( (B2:B13=C18)*(C2:C13=C19)*(D2:D13=C20) , A2:A13 )
  • Nilai untuk input parameter match_type pada fungsi Match adalah 1 (satu),karena data terurut menaik (ascending) pada kolom tanggal yang berisi nilai-nilai batas bawah interval kelas.
Susunan array formula Index(Match) secara utuh menjadi :
=INDEX(E2:E13,MATCH(C17,IF((B2:B13=C18)*(C2:C13=C19)*(D2:D13=C20),A2:A13),1))

Proses kalkulasi step-by-step di dalam array formula tersebut adalah seperti gambar berikut ini.
Step1 sampai step3 adalah kalkulasi kondisi masing-masing kriteria selain interval kelas, yaitu kondisi berdasar Lokasi, Level, dan Shift.

Step4 adalah kalkulasi seluruh kondisi berdasar Lokasi, Level, dan Shift, sehingga didapatkan array yang bernilai 1 (seluruh kriteria Lokasi, Level, Shift terpenuhi) atau 0 (yang tidak terpenuhi).

Step5 adalah pemilihan data tanggal berdasar kondisi hasil step4. Jika hasil step4 terpenuhi, maka data tanggal digunakan. Andaikan hasil step4 tidak terpenuhi, maka nilai boolean FALSE yang menjadi nilai item array.

Step6 adalah kalkulasi fungsi Match dengan nilai lookup berupa nilai kriteria tanggal (cell C17) dan match_type=1, pada array lookup hasil step5. Jika C17 diisi dengan nilai yang sangat besar, seperti tanggal saat ini atau malah tanggal masa akan datang, maka akan didapatkan hasil match dibaris yang terakhir mungkin. Hal ini akan bermanfaat ketika diinginkan pencarian data terbaru yang ada dengan banyak kondisi.

Step7 adalah proses pengambilan nilai hasil array formula, yaitu dari kolom Supervisor.


Closing :

Seluruh array formula yang digunakan di atas masih memungkinkan akan menghasilkan error value, yaitu ketika kegiatan lookup oleh fungsi Match menghasilkan error value. Fungsi Match menghasilkan error value ketika kegiatan lookup tidak menemukan nilai lookup pada array lookup. Fungsi IFError (pada Excel 2007 ke atas) ataupun fungsi-fungsi dari grup Information seperti IsError, IsNumber, atau IsNA dapat digunakan untuk mengeliminasi hasil array formula yang berupa error value.


File(s) :



Coretan terkait :




6 komentar:

  1. Terimakasih Mr. Kid utk ilmunya. Kalau salah satu kriterianya tidak didefinisikan secara pasti bagaimana? misalkan kriteria tanggal adalah tanggal terbaru (tidak didefinisikan tanggal jelasnya). terimakasih.

    Lisa.

    BalasHapus
  2. @Lisa :
    Untuk kasus dengan kriteria tanggal terbaru, maka gunakan cara pada bagian Lookup banyak kriteria dengan referensi berupa interval kelas (grup).

    Cell C17 diisi dengan tanggal terbaru yang mungkin, seperti tanggal hari ini atau tahun depan dsb. Dengan opsi pencarian pada fungsi Match yang bernilai 1, maka akan didapatkan data terakhir yang mendekati isian tanggal terbaru di C17. Kriteria lain yang lebih pasti diletakkan di bagian kondisi dalam fungsi IF.

    BalasHapus
  3. Bagaimana jika mau mengindeks tanggal dengan kriteria tertentu? misalkan saya mau merekap supervisor Aaa bertugas pada tanggal berapa saja dan kolom lain mengikuti tanggal berdasarkan kriteria SPV Aaa. Terima kasih.

    BalasHapus
    Balasan
    1. Hai Eka,

      Kegiatannya adalah memfilter data. Coba lihat tentang formula filtering di :
      http://excel-mr-kid.blogspot.co.id/2011/04/formula-filter-data-filtering-formula.html

      Hapus