Dasar Macro - Menampilkan nilai kolom yang bersebelahan pada Listbox dan Combobox

Bismillah. Assalamualaikum.

Tutorial ini berasumsi pembaca sudah paham bagaimana mengatur Property pada control userform. Untuk lebih jelasnya, silahkan download file contohnya disini.

Control ListBox dan ComboBox adalah 2 control yang paling sering digunakan pada sebuah aplikasi sebagai fasilitas yang diberikan kepada user untuk memilih beberapa pilihan. Contoh kasus yang sering terjadi pada saat kita menggunakan kedua control tersebut pada Userform adalah menampilkan nilai pada kolom-kolom disebelahnya sesuai nilai yang terpilih khusus nya pada ListBox ataupun ComboBox yang berkolom lebih dari satu atau Multi-columns. Jadi ingat ya ? kata kunci nya adalah kolom nya lebih dari satu :).

Nah, kasus tersebut dapat diselesaikan dengan menggunakan property Column yang ada pada control ListBox dan ComboBox. Syntax atau penulisan property Column adalah Object.Column. Object dalam hal ini mengacu pada control ListBox atau ComboBox, dan argument Column adalah nilai indeks kolom pada Tabel atau Database. Kolom pertama nilai Indeks nya 0 dan kolom berikutnya adalah 1,2,3 dan seterus nya sesuai banyaknya kolom pada database.

Contoh nya seperti ini :

Dibawah ini adalah database Siswa yang terdiri dari 4 Kolom. Kolom pertama adalah Nomer Induk Siswa, Kolom kedua adalah Nama Siswa, Kolom ketiga adalah Tempat Lahir dan ke empat adalah Tanggal Lahir.

Database Siswa

Karena sifatnya yang unik maka kita menjadikan kolom pertama yaitu Nomer Induk Siswa sebagai kolom acuan nya (atau Primary Key) dan mengatur property BoundColumn nya ke angka 1, dan ColumnCount nya ke angka 4 sesuai banyak kolom dalam Database tersebut.

Nilai property lain yang harus diatur adalah RowSource. Pada file contoh, property RowSource diatur mengacu pada Range “Data”.

Property Bound dan Column Count

Jadi pada saat aplikasi dijalankan, user akan memilih nilai Nomer Induk Siswa pada ComboBox atau ListBox kemudian nilai pada kolom Nama, Tempat Lahir dan Tanggal Lahir akan muncul pada masing-masing TextBox.

Penjelasan skrip nya adalah sebagai berikut :

Untuk Combobox skrip nya diletak kan pada Event ComboBox Change. Jadi tiap kali nilai ComboBox berubah, nilai pada kolom pertama setelah kolom acuan (kolom Nama) dijadikan nilai TextBox txtNama (skrip Me.txtNama.Value = Me.ComboBox1.Column(1)).

Begitu juga untuk nilai kolom kedua setelah kolom acuan (kolom Tempat Lahir) dijadikan nilai TextBox txtTptLahir(skrip Me.txtTptLahir.Value = Me.ComboBox1.Column(2)).

Untuk kolom Tanggal Lahir (kolom ke 3 setelah kolom NIS) skripnya ditambahkan fungsi Format untuk merubah nilai String menjadi format tanggal Medium (Skrip Me.txtTglLahir.Value = Format(Me.ComboBox1.Column(3), "Medium Date"))

Hal yang sama juga berlaku untuk control ListBox yang kebetulan mempunyai property Column. Untuk ListBox, skrip diletak kan pada event Click.

Pada screenshot diatas terlihat Indeks Column nya dimulai dari angka 1 diatur ke masing-masing TextBox.

Semoga tutorial ini bermanfaat. 

Wassalam.

Menjadikan Data lebih "bersahabat" untuk Pivot Table

Bismillah..

Tidak semua tabel data dapat digunakan sebagai sumber data sebuah Pivot Table. Apalagi kalau susunan sumber datanya nya tidak “berkaidah database” seperti contoh gambar 1 dibawah. Dengan kolom Bulan yang memanjang kesamping sebanyak 20 kolom sudah pasti dengan bentuk data semacam ini tidak akan bisa digunakan sebagai sumber data untuk Pivot Table.

Gambar 1. Bentuk data yang memanjang kesamping

Seperti apakah data yang berkaidah database itu ? nanti kita bahas dilain waktu ya ? tapi satu hal yang paling mudah untuk mengetahui apakah data tersebut sudah berkaidah database adalah apabila range data nya digunakan sebagai sumber data untuk Pivot Table. Kalau hasil nya tidak sesuai, berarti data nya harus di beresin dulu :) atau istilah nya di Normalisasi. Normalisasi sendiri apabila menggunakan bahasa sehari-hari adalah proses menjadikan sebuah Tabel Data menjadi lebih baik, atau istilahnya keren nya berkaidah database tadi.

Nah, dibahasan kali ini akan dibahas bagaimana cara mudah proses Normalisasi sebuah Tabel Data yang akan dijadikan sumber data sebuah Pivot Table dengan menggunakan fasilitas Pivot Table Wizard untuk menghasilkan Tabel Data dari contoh gambar 1 diatas menjadi contoh gambar 2 dibawah ini. Sebelumnya silahkan download dulu file contoh nya disini.

Gambar 2. Bentuk data yang berkaidah database yang sudah dinormalisasi.

Pertama yang harus diperhatikan adalah mengenai menu Pivot Table Wizard sendiri. Menu ini Shortcut Key nya adalah Alt + D + P. Tapi sejak Excel 2007 menu ini dihilangkan tanpa alasan yang saya juga tidak tahu kenapa. Menu ini bisa saja ditampilkan melalui Quick Access Toolbar di pojok kiri atas. Langkah untuk mengaktifkan nya adalah sebagai berikut :

1. Klik panah kebawah pada Quick Access Toolbar 
2. Akan terlihat menu Customize Quick Access Toolbar. Pilih More Commands... 
   
3. Pada menu berikutnya Pilih Choose Commands from -> All Commands 
4. Di list dibawah nya, scroll kebawah dan pilih PivotTable and PivotChart Wizard, klik Add 
   

Lanjut ke proses Normalisasi, langkahnya adalah sebagai berikut :

1. Pastikan cell aktif berada pada area Tabel Data
2. Bisa tekan tombol Alt + d + p atau aktifkan menu Pivot Table Wizard.
3. Pada langkah pertama atau Step 1 of 3 pilih Multiple consolidation ranges dan pastikan pilihan What kind of report you want to create pilihan Pivot Table sudah terpilih. Kalau sudah klik Next. 
   
4. Pada langkah ke 2a atau Step 2a of 3 pilih I will create the page fields 
   
5. Langkah 2b of 3 pilih Range Datanya dan klik Add dan klik Next 
   
6. Langkah 3 of 3 pilih New Worksheet untuk menempatkan Pivot Table di Sheet baru, dan klik Finish. 
   
7. Pada Pivot Table Field List disebelah kanan, hapus tanda centang Row dan Column sehingga hanya tersisa Value saja seperti contoh 
   
8. Pada cell Value (di file contoh Cell A4), Klik 2 kali / double click cell nya sehingga keluar Detail nya dalam format Table Data 
9. Rubah kolom Judulnya. Pada contoh digunakan SALESMAN, PERIODE dan PENJUALAN 
   
10. Jadikan Tabel Data ini sebagai sumber data Pivot Table. Shortcut key nya Alt + n + v + t dan klik OK

Dengan cara normalisasi diatas Tabel Data yang tidak berkaidah database sebelumnya sudah dapat digunakan untuk Pivot Table. Field Periode dan Salesman bisa dijadikan Row dan Penjualan dijadikan Value nya. Kalau mau, kolom Periode bisa di grouping lagi supaya lebih ringkas.

Selamat mencoba.

Dasar lookup data (Fungsi lookup) 2 - Lookup 2 arah menggunakan OFFSET dan MATCH

Bismillah.

Pada pembahasan kali ini akan ditunjukan bagaimana menggunakan perpaduan fungsi MATCH dan OFFSET sebagai fungsi lookup (pencarian) untuk pencarian 2 arah pada Dataset yang berbentuk Cross Tabular seperti contoh dibawah.

Contoh dataset dengan layout Cross Tabular

Sebelumnya, silahkan download file contoh disini.

Kasus :

Cari data / angka penjualan masing-masing Sales tiap-tiap bulan pada laporan kuartal pada laporan penjualan seperti pada contoh diatas.

Penyelesaian :

Mencari data dari bentuk Dataset Cross-tabular (Atau Cross Tab. Disebut demikian karena datanya (pada contoh diatas, Range C4) berada dipersimpangan antara label bulan Februari dan label nama Sales3) seperti contoh, salah satunya dapat menggunakan perpaduan OFFSET dan MATCH.

Penulisan rumus lengkapnya adalah : =OFFSET(A1;(MATCH(I3;A2:A7;0));(MATCH(I2;B1:E1;0)))

Penjelasan rumus :

Syntax fungsi Match : MATCH(lookup_value, lookup_array, [match_type])

Bahasa manusianya : MATCH ( Nilai yang dicari, Tabel / Range yang akan dicari, [Kondisi pencarian] )

Catatan : 

Argumen Kondisi pencarian bersifat Optional. Bisa ditulis, bisa juga tidak. Argumen ini hanya menggunakan nilai 1,0 dan -1. Nilai 1 sama dengan lebih besar atau sama dengan (=>). Nilai 0 sama artinya dengan = atau sama persis. Sedangkan nilai -1 sama dengan lebih kecil atau sama  dengan (<=). Apabila tidak di tulis, maka Excel menganggap argumen ini bernilai 1.

Lebih praktisnya : apabila list acuan sudah di Sort dari kecil ke besar (Ascending) gunakan argumen 1. Kalau list dalam keadaan sudah di Sort dari besar ke kecil (Decending) maka gunakan -1.

Syntax fungsi OFFSET : OFFSET(reference, rows, cols, [height], [width])

Bahasa manusianya : OFFSET ( dari cell ini sebagai Acuan, Berapa banyak baris, Berapa banyak Kolom, [Sebanyak n baris], [Sebanyak n Kolom])

MATCH pertama digunakan untuk mencari data nama Sales dan MATCH kedua digunakan mencari data nama bulan. Karena fungsi OFFSET membutuhkan 3 argumen: cell acuan, Baris dan Kolom, maka 2 fungsi MATCH tersebut yang akan mengisi argumen Baris dan Kolom nya.

Semoga bermanfaat dan jelas ya ? kalau masih ada yang perlu ditanyakan, silahkan hubungi via email : gridsnlogic@gmail.com

Wassalam.

Menggunakan MS Query untuk menghubungkan Data – (MS Query Bag. 1)

Bismillah..

MS Query mungkin bukan tools untuk analisa data yang canggih seperti layaknya tools analisa canggih seperti PowerPivot, PowerQuery dan lainnya. Apalagi kalau mengingat tampilan nya yang mirip sebuah Aplikasi taun ’90 an yang terlihat agak kuno.

Tapi dengan kemampuan MS Query yang dapat menghubungkan tiap-tiap kolom dalam Excel sheet dan menjadikan nya selayaknya seperti Tabel dalam sebuah Database yang saling terkoneksi, maka dengan penggunaan MS Query ini untuk proses Lookup data yang biasa kita lakukan dengan Formula, dapat dilakukan dengan lebih cepat dan Praktis.

Ya, saya paham.. terkait dengan postingan sebelumnya mengenai Excel sebagai Database disini :). Tapi sepertinya mindset saya harus berubah saat baca buku Excel Advanced Report Development nya Timothy Zapawa.

Beberapa kelebihan penggunaan Microsoft Query dalam pengolahan data yang banyak (Big Data) dibandingkan dengan formula Lookup (Vlookup, Index – Match, dll) antara lain :

1. Proses awal saat membuka (loading) file hasil MS Query akan jauh lebih cepat. Dibandingkan dengan file yang penuh dengan formula lookup yang akan jauh lebih lambat.

2. Dengan adanya penambahan baris baru / data baru pada sumber data, yang perlu kita lakukan hanyalah klik Refresh pada data hasil Query.

3. Proses Refresh data juga dapat di atur pada tiap interval waktu.

4. Ditambah dengan menambahkan sedikit perintah SQL dalam proses filter data, proses Import dapat menjadi lebih efisien.

5. Proses analisa data dapat segera dilakukan dengan adanya fasilitas untuk menghasilkan data dalam bentuk Pivot Table / Data Table.

Ok. Langsung aja. Untuk contoh kasus kali ini kita gunakan file Northwind.xlsx yang bisa di unduh disini. Kasusnya adalah gabung beberapa Sheet untuk membuat sebuah laporan berapa banyak transaksi (OrderID) yang dibuat oleh masing-masing karyawan dengan menampilkan produk untuk tiap transaksi.

Sebelumnya bukalah 1 Workbook baru. Untuk mengakses MS Query dapat dilakukan melalui Tab Data (saya gunakan Excel 2010) --> Grup Get External Data --> From Other Sources -->  from Microsoft Query. Untuk lebih praktis nya, Keyboard shortcut nya adalah Alt + d + d + n.

1. Pada dialog box Choose Data Source pilih Excel Files* dan klik OK

2. Pilih directory dimana file MSQuery_Northwind.xlsx disimpan pada dialog box Select Workbook dan pada kotak Database Name lalu klik OK

3. Sheet-sheet yang ada dalam Workbook Northwind.xlsx tersebut akan dimunculkan didalam dialog box Add Tables. Kalau ada Sheet yang tidak muncul, klik Options.. dan pastikan kotak System Tables di centang √

4. Pilih Table / Sheet Employees dan klik Add. Atau klik dua kali pada Tabel Employees. Lakukan hal yang sama untuk Tabel / Sheet Order Details, Orders, dan Products lalu klik Close. Klik tombol Auto Query untuk segera menampilkan hasil Query.

5. Klik field EmployeeID pada Tabel Employees, dan tarik (drag) untuk menghubungkan field tersebut ke field EmployeeID di Tabel Orders. Lakukan hal yang sama untuk Field OrderID, dan ProductID pada Tabel Orders, Orders Details dan ProductID

6. Pilih EmployeeID dari Tabel Empoyees dan seret ke Data Grid. Lakukan hal yang sama untuk field FirstName dan LastName, OrderID dan ProductID dari Tabel OrderDetails, ProductName dan UnitPrice dari Tabel Products sehingga tampilan nya akan seperti contoh gambar dibawah.

7. Klik Tombol Return Data pada toolbar dan pastikan pilihan Tabel dan Existing Worksheet terpilih pada saat dialog box Import Data muncul. Dalam gambar contoh dipilih Range A1 pada Sheet1 (=Sheet1!$A$1). Klik OK.

Dan data sudah disajikan dalam format Data Table dan terhubung :)

Pada posting berikut insya Allah akan dibahas lebih lanjut mengenai MS Query ini.

Fungsi Lookup (1) - Vlookup metode Approximate Match

Pernah dihadapkan pada kedua contoh kasus berikut ini ?

Contoh kasus 1 :

Tentukan komisi untuk masing-masing Pegawai, yang dilihat dari total penjualan.

Contoh kasus 2 :

Tentukan rate pajak untuk tiap-tiap total jumlah gaji.

Dengan merubah satu argumen pada fungsi VLOOKUP, yaitu Range_Lookup menjadi 1 atau TRUE maka kedua kasus tersebut diatas dapat diselesaikan.

Untuk lebih jelasnya, unduh file contoh disini dan mari kita lihat lebih jauh mengenai fungsi VLOOKUP ini.

Syntax :  VLOOKUP (lookup_value, TableArray, Column_Index, [range_lookup])

Keterangan argumen :

- Lookup_Value : adalah nilai yang dicari

§  - TableArray : adalah range data yang akan kita cari – termasuk kolom acuan

§  - Column_index : indeks kolom yang akan ditampilkan apabila data ditemukan

§ - Range_lookup : bersifat optional – menentukan metode pencarian dengan menggunakan argumen 0 / FALSE untuk EXACT MATCH, dan argumen 1/ TRUE untuk metode pencarian APPROXIMATE MATCH.

Contoh :

Bahasa manusia untuk rumus VLOOKUP diatas adalah :

Cari Nomer Pegawai di cell A4 pada Range D3 sampai dengan E8, kemudian tampilkan kolom kedua pada Range tersebut dengan metode pencarian EXACT MATCH (di wakilkan oleh argumen FALSE atau 0) atau yang sama persis apabila ditemukan data yang sama.

Maka dengan merubah argumen Range_Lookup menjadi TRUE, penjelasan untuk contoh kasus 1 sebelumnya adalah :

Logika dari metode pencarian APPROXIMATE MATCH ini adalah cari yang terbesar SETELAH nilai yang dicari pada Tabel / Range Acuan, kemudian mundur 1 langkah. Dalam kasus 1 diatas, nilai terbesar SETELAH nilai 3,000,000 pada Tabel sebelah kanan adalah 4,000,000. Karena semestinya nilai yang dicari tidak lebih besar, maka VLOOKUP akan mundur 1 langkah setelah nilai 4,000,000 dan mendapatkan nilai Penjualan 1,000,000 dengan komisi 150,000.

Penjelasan yang sama berlaku untuk contoh Kasus 2 pada contoh gambar diatas. 

Bahasa manusia untuk rumus VLOOKUP diatas adalah : cari nilai pada Cell A20 (dan seterusnya) secara vertikal pada Range D20 sampai dengan E22 dengan metode pencarian APPROXIMATE MATCH (diwakilkan oleh argumen 1 / TRUE) dan tampilkan kolom ke dua.

Dengan memahami logika pencarian metode APPROXIMATE MATCH, maka nilai terbesar setelah nilai yang dicari pada Tabel / Range di sebelah kanan (nilai yang dicari adalah 10,000,000. Dan nilai terbesar setelah itu adalah 20,000,000), dan kemudian mundur satu langkah, maka akan didapatkan nilai 10,000,000 dengan Rate pajak sebesar 0,05.

Demikian semoga bermanfaat :D.