Catetan

MEMBUAT QUERY DI EXCEL 11


Selama bekerja di perusahaan yang bergerak di bidang yang berkaitan dengan data processing, aku selalu mencoba mencari cara mempermudah didalam pengolahan data, terutama yang berkaitan dengan pengolahan data di excel.

Disini saya mencoba membuat catatan yang berkaitan dengan Query di excel, proses ini akan sangat membantu sekali apabila data yang ada berjumlah ribuan row dan puluhan file, karena dengan keterbatasan RAM akan membuat loading computer menjadi lama, menghabiskan waktu dan kepala nyut…nyutan, karena diburu-buru datanya harus cepat selesai.

Untuk Query di excel ini, tidak diperlukan tahu mengenai pengetahuan bahasa SQL yang mendalam, bahkan lebih mudah daripada belajar VBA Macro.

Sebagai catatan, untuk melakukan query, data yang akan di query harus ditutup, dan untuk selanjutnya bukalah workbook baru di excel, pada menu DATA, pilih IMPORT EXTERNAL DATA, selanjutnya pilih NEW DATABASE QUERY, sehingga akan muncul jendela windows seperti di bawah ini :

Buatlah data source baru dengan memilih <New Data Source>, dan klik OK.

Setelah muncul window Create New Data Source pada kotak pertama berilah nama data query-nya, dan pada kotak kedua pilihlah tipe driver database yang akan di akses, dalam hal ini adalah Microsoft Excel Driver(*.xls). Kemudian pilih tombol CONNECT, sehingga muncul window dibawah ini.

Selanjutnya pilih Version-nya Excel 97-2000, walaupun excel yang ada adalah versi 2003, tetaplah memilih versi 97-2000, kemudian klik tombol SELECT WORKBOOK, sampai muncul windows dibawah.

Pada jendela Select Workbook, pilih File Excel yang akan diquery dan klik OK. Pada jendela ODBC Microsoft Excel Setup, klik OK yang akan kembali ke jendela Create New Data Source, dimana akan muncul keterangan source data yang akan diquery.

Selanjutnya pilih OK, dan akan kembali ke jendela Choose Data Source, untuk memasukkan nama query yang telah kita buat.

Pilih nama query yang telah kita buat dalam hal ini Excel_query, dan jangan lupa, Kosongkan kotak checklist Use the Query Wizard to create/edit queries, klik OK, dimana selanjutnya akan muncul jendela Microsoft Query dengan Add Tables.
Apabila kondisinya Add Tables tidak muncul, pilih tombol Options, dan akan muncul jendela Table Options, Centang System Tables, seperti halnya dibawah ini.
Kemudian klik OK, sehingga pada jendela Add Table akan muncul keterangan seperti dibawah. Pilih semua table dan klik Add, sehingga semua table masuk ke jendela Microsoft Query Pada jendela Microsoft Query Jangan lupa untuk melakukan klik tombol Show/Hide Table dan Show / Hide Criteria, Sehingga jendelanya akan berbentuk seperti dibawah ini.
Kemudian buat relationship antar tabel dimana dalam hal ini untuk tabel EMP mempunyai relationship dengan tabel JUAL berdasarkan NIP, dan tabel MODEL mempunyai relationship dengan tabel JUAL berdasarkan PROD_CODE, contohnya :
Selanjutnya pada criteria coba buat seperti dibawah ini, dimana selanjutnya bisa dicoba sendiri.
maksuk dari gambar diatas adalah melakukan query berdasarkan kolom brand dengan nilainya APPLE. Dan pada Area Data, pilih kolom yang akan menampilkan data hasil query, dalam hal ini kolom yang akan dipilih adalah, No_Jual, Brand, Model, Jumlah_Barang, Harga, Tgl_Jual dan Nama, seperti dibawah ini.

Langkah selanjutnya untuk menampilkan data hasil query, klik tombol run query, Dan hasilnya akan tampak seperti dibawah ini.
Sebagai catatan yang patut diingat adalah bahwa jumlah row yang ada di excel hanya 65,536 rows, dan untuk mengetahui jumlah data rows hasil query adalah dengan melihat last recordnya , dalam hal ini rows data query yang ada hanya berjumlah 11 row. Untuk mengetahui kode SQL-nya coba klik . Dan kode SQL yang muncul dari hasil query adalah seperti dibawah ini
Selanjutnya untuk menyimpan data hasil query ke worksheet adalah dengan melakukan klik tombol Return Data , Dimana akan muncul jendela Import Data, pilih areanya, kemudian klik OK. Begitulah sehingga query data di excel ini dibuat, untuk melihat contohnya terlampir file yang bisa di DOWNLOAD disini

11 comments:

Anonim

14 Feb 2010, 20.29.00
pusing juga kalau awam
Anonim

26 Apr 2010, 10.46.00
aku run tapi kog ga berhasil ya..
karsono

26 Apr 2010, 14.34.00
Kalau datanya sudah sesuai dengan kondisi yang dijelaskan diatas, kesalahan yang biasa sering dilakukan adalah penulisan huruf pada baris value dari area kriteria yang akan di query, yang patut diingat adalah huruf besar/capital dan kecil akan mempengaruhi proses query, jadi huruf pada baris value harus sesuai dengan huruf di databasenya, untuk contoh diatas, huruf value pada menu brand adalah huruf besar/capital yaitu :'APPLE', kedua Kolom menu untuk hasil query belum dipilih, jadi pilih dulu kolom value dengan cara double klik menu pada AREA TABLE atau pilih secara langsung dari pilihan pada AREA DATA, semoga membantu.
Ayuningtyas

8 Des 2010, 10.41.00
terima kasih.....cuma gak beda aja kalo di 2007.
"ditutup, dan untuk selanjutnya bukalah workbook baru di excel, pada menu DATA, pilih IMPORT EXTERNAL DATA, selanjutnya pilih NEW DATABASE QUERY, sehingga akan muncul jendela windows seperti di bawah ini :"

tapi dari Tab Data --> Get External Data--> From Other Resources --> From Microsoft Query .

Lainnya hampir sama kok...
berhasil :)
YENDRI, SE, AK

20 Feb 2011, 22.46.00
aku punya 2 tabel sbb :

Tabel : Solist Tabel : Ledger
------------ --------------
Prod CCY Rekg Jumlah Rekg Jumlah
---- --- ---- ------ ---- ------
AAA IDR 123 100 123 200
BBB IDR 123 200 123 400
CCC IDR 123 300

Untuk memastikan total yang ada pada solist = ledger, saya lakukan join, namun hasilnya malah ga karuan, sbb :

Rekg Jml_Solist Jml_Ledger Selisih
---- ---------- ---------- -------
123 1200 1800 -600

seharusnya hasilnya adalah sbb :

Rekg Jml_Solist Jml_Ledger Selisih
---- ---------- ---------- -------
123 600 600 0

Mohon pencerahan dari Bapak2/ibu2..
Makasih
YENDRI, SE, AK

21 Feb 2011, 14.29.00
aku ingin join 2 buah tabel, namun hasilnya ga benner. mohon bantuan bapak2/ibu2...

Tabel : Solist
--------------
Prod CCY Rekg Jumlah
---- --- ---- ------
AAA..IDR..123....100
BBB..IDR..123....200
CCC..IDR..123....300

Tabel : Ledger
--------------
Rekg Jumlah
---- ------
123.....200
123.....400

Untuk memastikan total yang ada pada solist = ledger, saya lakukan join, namun hasilnya malah ga karuan, sbb :

Rekg Jml_Solist Jml_Ledger Selisih
---- ---------- ---------- -------
123........1200.......1800....-600

seharusnya hasilnya adalah sbb :

Rekg Jml_Solist Jml_Ledger Selisih
---- ---------- ---------- -------
123.........600........600.......0

Mohon pencerahan dari Bapak2/ibu2..
Makasih
kimi

17 Apr 2012, 09.10.00
Mohon bantuan para suhu

saya ingin menampilkan beberapa data dari excel dengan kriteria,seperti saya ingin menamppilkan data dengan kriteria tertentu.atau select* from table where kondisi=a.jadi yang muncul di kolom nanti lebih dari satu data
Unknown

18 Agu 2015, 23.44.00
thanks to your info brother
Unknown

4 Nov 2015, 10.43.00
Pusing om
ICT_TIKSPANDABPP

1 Jun 2017, 12.35.00
Alhamdulillah Numpang Ikut belajar Pak terima kasih atas Ilmunya
mahrusyahafidz

22 Feb 2018, 08.07.00
contoh filenya ... share...

Posting Komentar