Pendahuluan. Bagi anda yang sudah terbiasa
mengolah data menggunakan Microsoft Excel, tentu tidak asing lagi dengan fungsi “Lookup
and reference” semisal VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH,
INDIRECT, OFFSET atau kombinasi dari fungsi-fungsi tersebut,
termasuk kelemahannya. Kelemahannya? Maksudnya?, Ya, ternyata
fungsi-fungsi embedded Excel tersebut masih menyisakan “kelemahan”. Diantaranya;
ketika anda mencari suatu value yang melekat pada sebuah record kunci (Primary Key)
yang tidak uniq misalnya (i.e karena terdapat lebih dari satu Primary Key yang
similar atau sama di dalam database, red), maka hasil akhir pencarian akan
kembali kepada value yang melekat pada record kunci yang pertama kali ditemukan,
bukan pada record kunci yang terakhir atau yang paling up-to-date. Bagaimana
jika value yang anda cari itu justru terletak pada record kunci (identik) yang paling
akhir?, That’s the problem of those Function. Contoh case realnya adalah sbb;
Dari tabel diatas kita
ingin mengetahui; berapa “Jumlah total Aktivasi perdana yang tercatat terakhir dalam
database”. Nah untuk mencarinya, kita ambil salah satu syntax dari Lookup Function
diatas, taruhlah Index dan Match.
Maka formulasinya adalah sbb;
=INDEX($B$2:$B$13,MATCH(“Activation”,$A$2:$A13$,0),1)
Berapa hasilnya? 260.500.
Padahal jumlah aktivasi perdana terakhir yang seharusnya terdisplay di ActiveCell
adalah 161.611, bukan 260.500. Begitu pula dengan function lainnya
seperti VLOOKUP(“Activation”,$A$2:$B$13,2;FALSE)
atau =INDIRECT(“B”&MATCH(“Activation”;$A$3:$A$13;0)),
hasilnya tetap akan sama.
Lantas bagaimana
solusinya?. (Mungkin) ada banyak cara yang bisa kita lakukan, salah satunya adalah
dengan memaksimalkan fungsi Visual Basic Application (VBA) Excel atau Macros seperti yang akan saya bahas berikut ini. (Wallaahu a’lam, jika ada rekan-rekan blogger yang mau
berbagi trik lain yang lebih efektif, silahkan bisa disharing di sini, red). Terlampir
adalah syntaxnya;
With Application
.ScreenUpdating = False
End With
Set ws = Sheets("Sheet1")
Set wa = Sheets("Sheet2")
With wa
i = .Range("B2").Value
End With
With ws
On Error Resume Next
Set c = .Cells.Find(What:=i,
LookIn:=xlValues, lookat:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
On Error GoTo 0
If Not c Is
Nothing Then
FirstAddress = c.Address
Do
rg = c.Offset(0, 1).Address
Set
c = .Range(FirstAddress, .Range("A" &
Rows.Count).End(xlUp)).FindNext(c)
Loop
While Not (c Is Nothing) And (c.Address <> FirstAddress)
End If
End With
ws.Range(rg).Copy _
Destination:=wa.Range("C2")
'Useful for Releasing Memory
Set ws = Nothing
Set wa = Nothing
With Application
.ScreenUpdating = True
End With
End Sub
Atau bisa juga dengan menggunakan
syntax yang sama tapi beberapa bagian syntaxnya direplace, yakni;
Syntax sebelumnya;
Do
rg = c.Offset(0, 1).Address
Set c =
.Range(FirstAddress, .Range("A" &
Rows.Count).End(xlUp)).FindNext(c)
Loop While Not (c Is Nothing) And (c.Address <> FirstAddress)
Dimodifikasi dengan;
For n = 1 To
WorksheetFunction.CountIf(.Range(FirstAddress, .Range("A" &
Rows.Count).End(xlUp)), i)
rg = c.Offset(0, 1).Address
Set c = .Range(FirstAddress, .Range("A"
& Rows.Count).End(xlUp)).FindNext(c)
Next n
Hasilnya
tetap akan sama. Sebelum kita masuk pada step by step practice-nya, ijinkan saya
menjelaskan terlebih dahulu makna dari syntax di atas (sebatas apa yang saya tahu,
red).
Penjelasan Syntax
Pertama;
Set ws = Sheets("Sheet1")
Set wa = Sheets("Sheet2")
Untuk
mempersingkat karakter syntax, maka bagian syntax yang bisa kita definisikan,
kita definisikan. Contoh; ws adalah nama lain dari Sheet1
yang (di dalamnya) terdapat table Data Performansi SCN (seperti yang
ditunjukkan pada gambar diatas, red), sedangkan wa
adalah sheet2 yang digunakan untuk menampilkan Primary
Key (i
= .Range("B2").Value) sekaligus hasil pencarian (ws.Range(rg).Copy
Destination:=wa.Range("C2"))
Kedua;
c
= .Cells.Find(What:=i, LookIn:=xlValues, lookat:=xlPart, _
SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False)
Syntax
diatas merupakan Find Method dalam VBA. Formulasinya sbb;
expression .Find(What,
After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte,
SearchFormat)
expression A
variable that represents a Range object.
Parameter
“What:=”
itu wajib diisi (required). Apa yang harus diisikan?, “The data to
search for. Can be a string or any Microsoft Excel data type” yakni
data yang kita cari, dalam hal ini adalah Primary Key (dalam contoh di atas misalnya
Activation). Parameter “LookIn:=” itu bersifat optional,
boleh ada dan boleh tidak. Kalau ada,
maka bisa diisi dengan xlValues atau xlFormulas,
tergantung referensinya berwujud apa, apakah value (angka, string, char dll)
ataukah formulasi. Parameter “lookat:=” juga bersifat Optional, bisa
diisi dengan xlPart atau xlWhole. Dari referensi
yang saya baca, xlPart dan xlWhole ini
sama dengan 0 dan 1 pada fungsi MATCH atau
TRUE
dan FALSE
pada fungsi VLOOKUP. Pada fungsi MATCH
misalnya, jika kita ingin mengetahui ada tidaknya data dalam Table yang
valuenya sama persis (exact) dengan Primary Key-nya, maka match_type-nya harus diset
0,
jika tidak (harus sama persis alias mendekati, red), maka cukup kita set 1.
Begitupula untuk case lookat:= ini, jika kita ingin mengetahui ada
tidaknya data dalam Table/ database yang valuenya sama persis (exact) dengan
Primary Key, maka match_type-nya harus diset xlWhole,
jika tidak maka cukup kita set xlPart. Sebagaimana paramater
sebelumnya yang bersifat optional, “SearchOrder:=” juga bersifat
demikian. Parameter ini bisa diisi dengan xlByRows atau xlByColumns,
keduanya menunjukkan “whether to search by rows or search by columns. Default
value is xlByRows”.
Kurang lebih sama dengan fungsi VLOOKUP
dan HLOOKUP,
dimana VLOOKUP itu by Rows sedangkan HLOOKUP by Columns.
Parameter “SearchDirection:=” menujukkan metode pencarian data apakah
ke atas (xlPrevious) atau
ke bawah (xlNext) dan sifatnya Optional. Adapun Parameter “MatchCase:=” dan “SearchFormat:=” itu
terkait apakah value yang dicari itu Case-Sensitive dan Specified Formatting
atau tidak, jika iya maka diisi True dan jika tidak maka cukup diisi False.
Pertanyaannya, jika syntax tersebut dirunning, apa hasil yang akan dimunculkan
oleh c?. Jika data yang dicari sesuai dengan requirement i.e sama persis dengan
Primary Key (i), maka hasilnya adalah Primary Key itu sendiri (dalam
contoh di atas adalah Activation, red).
Ketiga;
If Not c Is Nothing Then
FirstAddress = c.Address
Do
rg = c.Offset(0, 1).Address
Set c
= .Range(FirstAddress, .Range("A" &
Rows.Count).End(xlUp)).FindNext(c)
Loop While Not (c Is Nothing)
And (c.Address <> FirstAddress)
End If
Maksudnya,
jika c itu available dalam database, maka alamat cell-nya (FirstAddress) adalah
c.Address.
Dalam contoh case ini misalnya, FirstAddress = $A$4 (Silahkan lihat
kembali gambar di atas. Activation yang pertama kali terdeteksi pada database
adalah cell $A$4, red). Adapun syntax berikutnya berfungsi untuk mencari value
yang melekat pada record kunci (Primary Key) terakhir yang up-to-date. Ketika proses
running (program) baru sampai pada syntax: FirstAddress = c.Address,
maka status rg is Empty atau rg = “”, baru setelah
melewati syntax rg = c.Offset(0, 1).Address, rg akan menghasilkan
address $B$4 (masih berupa address, belum value, red). Selanjutnya untuk
mencari tahu apakah masih ada Primary Key (Activation) pada record setelahnya, digunakan
syntax expression
.FindNext(After), expression diisi
dengan Range i.e .Range(FirstAddress, .Range("A" &
Rows.Count).End(xlUp)) dan After diisi dengan c
yang merepresentasikan cell yang mengandung Primary Key “Activation” setelah FirstAddress. Selama
c
masih available di record-record setelahnya, maka proses looping akan terus
dilakukan sebanyak WorksheetFunction.CountIf(.Range(FirstAddress,
.Range("A" & Rows.Count).End(xlUp)), i) dan
berhenti setelah c tidak lagi available (yakni di $A$10).
Keempat;
ws.Range(rg).Copy
_
Destination:=wa.Range("C2")
Sebagaimana
yang sudah dijelaskan pada keterangan pertama sebelumnya, syntax ws.Range(rg).Copy
Destination:=wa.Range("C2") berfungsi untuk mengcopy
hasil pencarian value ($B$4) pada sheet ws ke cell C2 pada sheet wa.
Step by Step Implementasi Program
1).
Buat table yang sama seperti yang dicontohkan gambar di atas (Pada Sheet1)
2).
Copy syntax FindLastRecord secara lengkap, masuk ke Developer →
Code →
Visual Basic. Pada jendela Microsoft Visual Basic, klik Insert → Module
dan pastekan syntax tersebut ke dalam Windows Code sehingga akan tampak seperti disamping;
3).
Masukkan record kunci (Primary Key) pada cell B2 di Sheet2 yakni “Activation”
atau “Distribution”, atau whatever it is selama key tersebut tersedia di
database.
4).
Klik tombol Run pada jendela Microsoft Visual Basic dan lihat apa yang terjadi.
Penutup
Sesuatu
yang terlihat biasa belum tentu (hakikatnya) juga biasa-biasa saja, bisa jadi ia menyimpan potensi yang
luar biasa hanya saja kita tidak atau belum mengetahuinya. Demikian pula dengan
Excel, menurut sebagian orang yang belum “mengetahui” dan belum sempat mengeksplor
Tools ini lebih dalam, tampilan Excel sepintas terlihat biasa saja, fungsinya
juga terlihat biasa-biasa saja (Kali, bagi, tambah, kurang). Namun bagi mereka
yang telaten mau mengeksplorasi, try and error, mau belajar meskipun harus
berproses (seperti para MVP Excel itu, red), Tools ini akan terasa sangat
powerful dan membantu (dengan segala kelebihan dan kekurangannya tentunya). Let’s explore this tool
optimally...
