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.