Import di Excel dengan bantuan ADO, VBA dan SQL (Bag. 3 -Early Binding)

Masih dengan Database Access yang sama, bahasan kali ini akan membahas contoh aplikasi untuk import dan Eksport data dari Access ke Excel dan Eksport dari Excel ke Access dengan bantuan VBA dan ADO dengan metode Early Binding. Silahkan download file contohnya Early Binding disini.

Pembahasan sebelumnya ada disini dan disini.

Setelah download file contoh, buka file Mahasiswa_EB.xlsm, dan buka VBE nya (Alt + F11). Klik dua kali pada Module1 untuk melihat isi dari Prosedur ImportTableEB. Seperti yang sudah dijelaskan pada bahasan sebelumnya, untuk menggunakan metode Early Binding maka sebelumnya kita harus menambahkan ADO Library nya melalui menu Tools – Refference dan pilih Microsoft ActiveX Data Object Library dengan versi paling tinggi.

menambahkan Library ADO

Sekarang coba perhatikan perbanding skrip prosedur Import dengan cara Early Binding dan Late Binding. Sebelah kiri adalah skrip metode Early Binding dan sebelah kanan adalah skrip Late Binding. Pada prinsip nya ada 4 perbedaan (walaupun pada kenyataan nya ada 10 poin yang tertulis) :

perbandingan skrip

1.       Pada saat deklarasi variabel conn untuk koneksi (Nomer 1)

2.       Pada saat deklarasi variabel rs untuk recordset (Nomer 2)

3.       Pada saat definsi variabel conn sebagai New ADODB.Connection (Nomer 3)

4.       Dan pada saat definsi variabel rs sebagai New ADODB.Recordset (Nomer 4)

Selebih nya mungkin ada pada nomer 7,8 dan 9 sebagai rangkaian looping untuk menuliskan nama Field-field (Kolom) nya. Yah mungkin perintah SQL nya pada nomer 5 dan 6 sebagai cara untuk menghapus data yang sudah ada sebelumnya juga beda ya ? tapi untuk perintah SQL, selama masih menggunakan SELECT, bebas-bebas aja tinggal diganti sesuai keperluan. Begitu juga cara menghapus data sebelumnya (Nomer 6).Pada kesempatan mendatang saya akan coba berbagi mengenai berbagai macam perintah SQL yang dapat digabungkan dengan perintah SELECT.

Perbedaan yang sama juga akan kita temukan pada skrip untuk meng eksport dari Excel ke dalam tabel Access yang akan kita bahas diwaktu mendatang. Seperti yang sudah saya sebutkan sebelumnya, metode Early Binding ini adalah metode yang direkomendasikan dibanding metode Late Binding. Dan masing-masing mempunyai kelebihan dan kekurangan. Semoga bermanfaat.

Eksport & Import di Excel dengan bantuan ADO, VBA dan SQL (Bag. 2)

Import dari Access ke Excel dengan VBA, ADO dan SQL (Late Binding)

Untuk bahasan sekilas mengenai ADO silahkan buka disini.

Database yang akan kita gunakan adalah Database Mahasiswa yang mana didalamnya ada Tabel Mahasiswa. Silahkan download file database dan Excel nya disini dan download ke dalam folder yang sama.

Didalam Tabel Mahasiswa tersebut terdapat beberapa field dengan tipe dan format seperti berikut :

Tabel Mahasiswa

Sedangkan untuk file Excel, ada dua Sheet. Sheet pertama bernama Input yang akan digunakan untuk Eksport dari Excel ke Access. Kolom-kolom yang ada di Sheet ini sama persis dengan yang ada pada Tabel Mahasiswa. Begitu juga formatnya.

Sheet berikutnya adalah Sheet Laporan yang akan digunakan sebagai tempat untuk Import data dari Access. Cara pertama adalah menggunakan metode Late Binding untuk meng import Tabel Mahasiswa. Ikuti langkah-langkah berikut :

1.       Buka file Excel Mahasiswa dan buka VBE (Alt + F11)

2.       Insert module baru

3.       Buat Prosedur baru. Di contoh dibawah prosedurnya saya beri nama ImportTabel

4.       Tuliskan skrip seperti dibawah :

Penjelasan Skrip :

Karena metode yang kita gunakan adalah Late Binding, artinya kita tidak mengatur referensi ADO Library melalui menu Tools à Refference melainkan menuliskan langsung dalam skrip VBA.

1.       Variabel conn akan kita gunakan sebagai ADO Connection

2.       Variabel rs akan kita gunakan sebagai ADO Recordset

3.       Variabel connstr akan kita gunakan sebagai Connection String

4.       Variabel dbpath adalah lokasi tempat dimana file Access atau Database berada

5.       Sedangkan sqlstr adalah variabel yang akan kita gunakan menuliskan SQL Query

6.       Karena variabel conn dideklarasikan sebagai Obyek maka berikutnya kita wajib menggunakan statetemen Set untuk menciptakan Obyek ADODB Connection nya.

7.       Hal yang sama untuk variabel rs. Karena sudah di deklarasikan sebagai Obyek maka kita wajib menciptakan Obyek ADODB recordset melalui statement Set dan CreateObject

Skrip berikutnya adalah :

8.       Isi variabel dbpath dengan mengambil path dimana file Excel Mahasiswa.xlsm berada (Thisworkbook.path) dan menggabungkan string nya dengan string nama Databasenya yaitu Mahasiswa.accdb. Jangan lupa tanda “\” sebelum Mahasiswa.

9.       Isi variabel connstr adalah Connection String untuk Database MS Access 2013 ditambah dengan Data Source nya yang diambil dari isi variabel dbpath . Tipe Access Database 2007 keatas dapat menggunakan Connection String seperi diatas. Untuk menentukan jenis Connection String yang tepat untuk masing-masing Database dapat dilihat di www.connectionstring.com

10.       Karena kita akan mengambil isi dari semua Field dalam tabel Mahasiswa maka variabel sqlstr kita isi dengan query SQL SELECT * From Mahasiswa

11.       Setelah definisi path Database, Connection String dan SQL Query nya beres kita coba buka koneksi nya. Syntax nya : connection.open connection string nya.

12.       Setelah koneksi sudah terbuka, kita coba buka Recordset nya. Baris ini membuka Recordset menggunakan query dalam variabel sqlstr menggunakan Active Connection dalam variabel conn

Sampai dengan tahap ini coba untuk gunakan tombol F8 untuk proses Debug satu-satu. Apabila penulisan skrip tidak ada yang salah maka tidak akan muncul Error.

Skrip berikutnya adalah :

13.   Baris ini berfungsi untuk menghapus apabila sudah ada data hasil proses sebelumnya.

14. Baris berikutnya langsung mencetak semua Recordset pada Sheet Laporan mulai dari Range A2 menggunakan metode CopyFromRecordset

15.   Seperti yang sudah dibahas sebelumnya, Recordset yang sudah dibuka sebelumnya wajib untuk ditutup kembali

16.   Sama seperti diatas, Connection yang sudah dibuka sebelumnya wajib untuk ditutup kembali

17.    Baris ini dan 18 berfungsi untuk membersihkan isi variabel rs dan conn dari Memory.

Untuk sementara ini dulu. Semoga bermanfaat.

Eksport & Import di Excel dengan bantuan ADO, VBA dan SQL (Bag. 1)

A.   Latar belakang

Beberapa pertanyaan mungkin timbul beberapa diantaranya mengapa harus menggunakan ADO + Macro ? kan ada fasilitas eksport dan import di Excel maupun Access. Ya kali, kalau cuma sesekali nggak apa. Tapi kalau sudah jadi pekerjaan rutin kan lebih mudah kalau tinggal sekali klik saja J. Dan dengan bantuan SQL, kita menentukan kriteria record yang akan kita ambil. Ini akan terasa sangat membantu pada saat data kita sudah mencapai ribuan atau bahkan puluhan ribu baris.

Atau mungkin kenapa harus dari Excel ke Access atau sebaliknya ? Excel punya kelebihan dalam hal formula, Pivot Table dan Chart untuk analisa data. Access punya kelebihan relasi antara Tabel, Query dan Integritas antar relasi nya. Paling tidak buat saya pribadi, kalau kita menggabungkan kedua kelebihan pada kedua aplikasi ini maka akan banyak hal yang dapat dilakukan.

B.    Sekilas tentang ADO

gambaran mengenai ADO di Excel

Sebelum melangkah lebih jauh, ada baiknya apabila kita mengenal sedikit apakah ADO itu. ADO adalah singkatan dari ActiveX Data Object. ADO adalah teknologi yang diciptakan Microsoft sebagai perantara untuk mengakses berbagai macam Database. Database dapat berupa file Microsoft Access, Excel, Dbase, SQL Server dan sebagainya. Sebelum ADO, teknologi sejenis adalah DAO (Data Access Object) dan RDO (Remote Data Object). Pengembangan dari ADO adalah ADO.net yang saat ini banyak digunakan pada pemrograman dot Net.

C.    Obyek Connection dan Recordset

Komponen paling penting dalam ADO adalah obyek Connection / Koneksi dan Recordset / kumpulan data. Kedua obyek ini apabila di ibaratkan sama hal nya apabila kita membutuhkan satu buah folder berisikan data / informasi dari sebuah instansi atau departemen. Nomer telpon instansi yang dimaksud di ibaratkan sama dengan obyek Connection. Dan folder berisikan data yang kita butuhkan di ibarakan sama dengan obyek Recordset.

Langkah pertama yang harus kita lakukan apabila kita membutuhkan sekumpulan data dari sebuah Instansi adalah kita harus mengetahui dulu nomer telpon instansi tersebut. Nomer telpon nya harus benar. Kalau perlu, nomer ekstension petugas terkait juga harus jelas. Sesudah itu kita dapat menghubungi nomer telpon tersebut dan memberikan informasi informasi apa saja yang kita butuhkan. Apabila informasi yang kita butuhkan kepada petugas benar, maka hasil nya (sebuah Recordset) sudah tentu juga benar dan sesuai.

Setelah komunikasi selesai, dan data sudah kita dapatkan, maka koneksi telpon tadi dapat kita tutup. Dengan menggunakan contoh diatas secara garis besar maka kita dapat susun langkah-langkah nya sebagai berikut :

•  Buka koneksi / Connection
•  Buka recordset
•  Berdasarkan koneksi diatas, jelaskan kriteria Recordset (melalui perintah SQL)
•  Kalau sudah dapat Recordset, tutup recordset
•  Tutup koneksi / Connection

Secara garis besar pola nya dapat dilihat pada langkah nomer 1 dan 5, kemudian 2 dan 4. Yaitu buka koneksi dulu, buka recordset, tutup recordset nya dulu, baru tutup koneksinya.

Ada banyak komponen lain selain Connection dan Recordset.  Seperti Connection String, Cursor, Lock Type dan lain-lain. Untuk bahasan ini kita akan bahas sampai dengan Connection String saja. Connection String ini adalah property atau bagian dari Obyek Connection. Pada Connection String inilah kita menentukan tipe database yang akan kita Eksport atau Import dari dan ke Excel beserta atribut-atributnya.

D. Metode Early dan Late Binding

Untuk melakukan Eksport ataupun Import dari dan ke Microsoft Excel dapat dilakukan dengan dua cara yaitu Early dan Late Binding. Late Binding ini agak sedikit ribet. Tapi jangan khawatir, nggak ribet banget kok. Yang harus tetap di ingat adalah 2 obyek diatas : Connection dan Recordset ditambah Connection String nya.

a.       Metode Early Binding

Menu Refferences ADO Library

Sesuai dengan namanya, Early = Awal, yaitu kumpulan obyek-obyek ADO seperti Connection dan Recordset yang disebutkan diatas tadi, atau disebut Library, referensi nya kita atur di awal. Caranya adalah melalui menu VBE (Alt + F11) – Tools – Refference. Scroll ke bawah dan Pilih “Microsoft Active X Data Object Library”. Akan ada beberapa pilihan dengan angka dibelakangnya sebagai versi. Pilih versi paling tinggi. Di laptop yang saya gunakan dengan Windows 10 dan Office 2013 versi tertinggi referensi ADO Library nya adalah versi 6.1.

Keuntungan menggunakan metode ini adalah pada saat menuliskan skrip, Excel akan menunjukan property dan metode-metode (methods) yang terkait melalui intellisense nya. Contoh apabila kita akan membuka koneksi dengan menuliskan con sebagai variabel koneksi, maka metode Open dan Close akan langsung terlihat begitu kita ketik “con.” (con dan titik).

b.      Metode Late Binding

Metode ini adalah lawan dari metode Early Binding. Yaitu pengaturan referensi kita lakukan belakangan dengan cara mendeklarasikan obyek-obyek nya melalui skrip (Create Object). Baik obyek connection maupun recordset. Setelah obyek nya ada, barulah kita definisikan obyek-obyek tersebut. Obyek mana yang menjadi Connection dan obyek mana yang menjadi Recordset.

Dengan tidak adanya referensi library sebelumnya, maka kekurangan nya tidak adanya fasilitas intellisense. Sehingga kita harus hafal baik property, methods maupun event nya.

Sebaliknya, kita tidak perlu khawatir saat aplikasi kita berpindah ke pc lain. Aplikasi dapat langsung digunakan. Mungkin yang perlu dilakukan hanyalah di property connection string.

Terus pilih yang mana ? Microsoft sendiri menyarankan untuk menggunakan Early Binding (https://support.microsoft.com/en-us/help/245115/using-early-binding-and-late-binding-in-automation). Dari sisi performa menggunakan Early Binding jelas lebih cepat. Karena referensi nya sudah kita tentukan di awal. Pada metode Late Binding, pada saat aplikasi di jalankan (runtime) aplikasi masih harus menciptakan sebuah Obyek dahulu baru mendefinisikan nya menjadi bagian dari ADO. Untuk lebih jelasnya ikuti terus bahasannya lebih lanjut.

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.