Menggunakan Microsoft Query untuk mengambil data eksternal

Menggunakan Microsoft Query, Anda bisa menyambungkan ke sumber data eksternal, memilih data dari sumber eksternal tersebut, mengimpor data tersebut ke lembar kerja Anda, dan merefresh data sesuai kebutuhan untuk mempertahankan data lembar kerja Anda tetap sinkron dengan data di sumber eksternal.

Tipe database yang bisa Anda akses     Anda dapat mengambil data dari beberapa tipe database, termasuk Microsoft Office Access, Microsoft SQL Server, dan Microsoft SQL Server OLAP Services. Anda juga bisa mengambil data dari buku kerja Excel dan dari file teks.

Microsoft Office menyediakan driver yang bisa Anda gunakan untuk mengambil data dari sumber data berikut:

• Microsoft SQL Server Analysis Services (penyedia OLAP)

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Paradoks

• Database file teks

Anda juga dapat menggunakan driver ODBC atau driver sumber data dari produsen lain untuk mengambil informasi dari sumber data yang tidak tercantum di sini, termasuk tipe database OLAP lainnya. Untuk informasi tentang menginstal driver ODBC atau driver sumber data yang tidak tercantum di sini, periksa dokumentasi untuk database, atau hubungi vendor database Anda.

Memilih data dari database     Anda mengambil data dari database dengan membuat kueri, yang merupakan pertanyaan yang Anda tanyakan tentang data yang disimpan dalam database eksternal. Misalnya, jika data disimpan dalam database Access, Anda mungkin ingin mengetahui angka penjualan untuk produk tertentu menurut kawasan. Anda bisa mengambil bagian dari data dengan memilih hanya data untuk produk dan kawasan yang ingin Anda analisis.

Dengan Microsoft Query, Anda bisa memilih kolom data yang Anda inginkan dan hanya mengimpor data tersebut ke Excel.

Memperbarui lembar kerja Anda dalam satu operasi     Setelah Anda memiliki data eksternal dalam buku kerja Excel, setiap kali database berubah, Anda bisa refresh data untuk memperbarui analisis Anda — tanpa harus membuat ulang laporan ringkasan dan bagan Anda. Misalnya, Anda bisa membuat ringkasan penjualan bulanan dan merefreshnya setiap bulan ketika angka penjualan baru masuk.

Cara Microsoft Query menggunakan sumber data     Setelah menyiapkan sumber data untuk database tertentu, Anda bisa menggunakannya kapan pun Anda ingin membuat kueri untuk memilih dan mengambil data dari database tersebut — tanpa harus mengetik ulang semua informasi koneksi. Microsoft Query menggunakan sumber data untuk menyambungkan ke database eksternal dan untuk memperlihatkan data apa yang tersedia. Setelah Anda membuat kueri dan mengembalikan data ke Excel, Microsoft Query menyediakan buku kerja Excel dengan kueri dan informasi sumber data sehingga Anda bisa menyambungkan kembali ke database saat Anda ingin merefresh data.

Menggunakan Microsoft Query untuk mengimpor data     untuk mengimpor data eksternal ke Excel dengan Microsoft Query, ikuti langkah-langkah dasar ini, yang masing-masing dijelaskan secara lebih mendetail di bagian berikut ini.

Apa itu sumber data?     Sumber data adalah sekumpulan informasi yang disimpan yang memungkinkan Excel dan Microsoft Query tersambung ke database eksternal. Saat Anda menggunakan Microsoft Query untuk menyiapkan sumber data, Anda memberi nama sumber data, lalu memasukkan nama dan lokasi database atau server, tipe database, serta informasi masuk dan kata sandi Anda. Informasi ini juga menyertakan nama driver OBDC atau driver sumber data, yang merupakan program yang membuat koneksi ke tipe database tertentu.

Untuk menyiapkan sumber data menggunakan Microsoft Query:

1. Pada tab Data , dalam grup Dapatkan Data Eksternal , klik Dari Sumber Lain, lalu klik Dari Microsoft Query.

   Catatan: Excel 365 telah memindahkan Microsoft Query ke dalam grup menu Panduan Warisan.  Menu ini tidak diperlihatkan secara default.  Untuk mengaktifkan, masuk ke File, Opsi, Data, dan aktifkan di bagian Perlihatkan panduan impor data warisan .

2. Lakukan salah satu langkah berikut:

   • Untuk menentukan sumber data untuk database, file teks, atau buku kerja Excel, klik tab Database .

   • Untuk menentukan sumber data kubus OLAP, klik tab Kubus OLAP . Tab ini hanya tersedia jika Anda menjalankan Microsoft Query dari Excel.

3. Klik ganda <>Sumber Data Baru .

   -atau-

   Klik <>Sumber Data Baru , lalu klik OK.

   Kotak dialog Buat Sumber Data Baru ditampilkan.

4. Di langkah 1, ketikkan nama untuk mengidentifikasi sumber data.

5. Di langkah 2, klik driver untuk tipe database yang Anda gunakan sebagai sumber data.

   Catatan: 

   • Jika database eksternal yang ingin Anda akses tidak didukung oleh driver ODBC yang diinstal dengan Microsoft Query, maka Anda perlu mendapatkan dan menginstal driver ODBC kompatibel Microsoft Office dari vendor pihak ketiga, seperti produsen database. Hubungi vendor database untuk instruksi penginstalan.

   • Database OLAP tidak memerlukan driver ODBC. Saat Anda menginstal Microsoft Query, driver diinstal untuk database yang dibuat menggunakan Microsoft SQL Server Analysis Services. Untuk menyambungkan ke database OLAP lainnya, Anda perlu menginstal driver sumber data dan perangkat lunak klien.

6. Klik Sambungkan, lalu sediakan informasi yang diperlukan untuk menyambungkan ke sumber data Anda. Untuk database, buku kerja Excel, dan file teks, informasi yang Anda sediakan bergantung pada tipe sumber data yang Anda pilih. Anda mungkin diminta untuk memasukkan nama masuk, kata sandi, versi database yang Anda gunakan, lokasi database, atau informasi lain yang spesifik untuk tipe database.

   Penting: 

   • Gunakan kata sandi yang kuat dengan menggabungkan huruf besar dan huruf kecil, angka, dan simbol. Kata sandi yang lemah tidak menggabungkan elemen ini. Kata sandi yang kuat: Y6dh!et5. Kata sandi yang lemah: House27. Kata sandi harus terdiri dari 8 karakter atau lebih. Frasa sandi akan lebih baik jika menggunakan 14 karakter atau lebih.

   • Penting bagi Anda untuk mengingat kata sandi Anda. Jika Anda lupa kata sandi Anda, Microsoft tidak dapat mengembalikannya. Simpan kata sandi yang Anda tulis di tempat yang jauh dari informasi yang diproteksi.

7. Setelah Memasukkan informasi yang diperlukan, klik OK atau Selesai untuk kembali ke kotak dialog Buat Sumber Data Baru .

8. Jika database Anda memiliki tabel dan Anda ingin tabel tertentu ditampilkan secara otomatis dalam Panduan Kueri, klik kotak untuk langkah 4, lalu klik tabel yang Anda inginkan.

9. Jika Anda tidak ingin mengetikkan nama masuk dan kata sandi saat Anda menggunakan sumber data, pilih kotak centang Simpan ID pengguna dan kata sandi saya dalam definisi sumber data . Kata sandi yang disimpan tidak dienkripsi. Jika kotak centang tidak tersedia, lihat administrator database Anda untuk menentukan apakah opsi ini bisa disediakan.

   Catatan Keamanan: Hindari menyimpan informasi masuk saat menyambungkan ke sumber data. Informasi ini mungkin disimpan sebagai teks biasa, dan pengguna berbahaya dapat mengakses informasi untuk membahayakan keamanan sumber data.

Setelah Anda menyelesaikan langkah-langkah ini, nama sumber data Anda muncul dalam kotak dialog Pilih Sumber Data .

Menggunakan Panduan Kueri untuk sebagian besar kueri     Panduan Kueri memudahkan untuk memilih dan menyampulkan data dari tabel dan bidang yang berbeda dalam database Anda. Dengan Panduan Kueri, Anda dapat memilih tabel dan bidang yang ingin disertakan. Gabungan dalam (operasi kueri yang menentukan bahwa baris dari dua tabel digabungkan berdasarkan nilai bidang yang identik) dibuat secara otomatis ketika panduan mengenali bidang kunci utama dalam satu tabel dan bidang dengan nama yang sama dalam tabel kedua.

Anda juga bisa menggunakan panduan untuk mengurutkan kumpulan hasil dan melakukan pemfilteran sederhana. Dalam langkah terakhir panduan, Anda bisa memilih untuk mengembalikan data ke Excel, atau lebih lanjut memperbaiki kueri di Microsoft Query. Setelah membuat kueri, Anda bisa menjalankannya di Excel atau di Microsoft Query.

Untuk memulai Panduan Kueri, lakukan langkah-langkah berikut ini.

1. Pada tab Data , dalam grup Dapatkan Data Eksternal , klik Dari Sumber Lain, lalu klik Dari Microsoft Query.

2. Dalam kotak dialog Pilih Sumber Data , pastikan bahwa kotak centang Gunakan Panduan Kueri untuk membuat/mengedit kueri dipilih.

3. Klik ganda sumber data yang ingin Anda gunakan.

   -atau-

   Klik sumber data yang ingin Anda gunakan, lalu klik OK.

Bekerja langsung di Microsoft Query untuk tipe kueri lainnya     Jika Anda ingin membuat kueri yang lebih kompleks daripada yang diperbolehkan Panduan Kueri, Anda bisa bekerja secara langsung di Microsoft Query. Anda bisa menggunakan Microsoft Query untuk menampilkan dan mengubah kueri yang mulai Anda buat dalam Panduan Kueri, atau Anda bisa membuat kueri baru tanpa menggunakan panduan. Bekerja langsung di Microsoft Query saat Anda ingin membuat kueri yang melakukan hal berikut:

• Memilih data tertentu dari bidang     Dalam database yang besar, Anda mungkin ingin memilih beberapa data dalam bidang dan menghilangkan data yang tidak diperlukan. Misalnya, jika Anda memerlukan data untuk dua produk dalam bidang yang berisi informasi untuk banyak produk, Anda bisa menggunakan kriteria untuk memilih data hanya untuk dua produk yang Anda inginkan.

• Mengambil data berdasarkan kriteria yang berbeda setiap kali Anda menjalankan kueri     Jika Anda perlu membuat laporan atau ringkasan Excel yang sama untuk beberapa area dalam data eksternal yang sama — seperti laporan penjualan terpisah untuk setiap kawasan — Anda bisa membuat kueri parameter. Ketika menjalankan kueri parameter, Anda akan diminta untuk menggunakan nilai sebagai kriteria saat kueri memilih rekaman. Misalnya, kueri parameter mungkin meminta Anda untuk memasukkan kawasan tertentu, dan Anda bisa menggunakan kembali kueri ini untuk membuat setiap laporan penjualan kawasan Anda.

• Menggabungkan data dengan cara yang berbeda     Gabungan dalam yang dibuat Panduan Kueri adalah tipe gabungan yang paling umum digunakan dalam membuat kueri. Namun, terkadang Anda ingin menggunakan tipe gabungan yang berbeda. Misalnya, jika Anda memiliki tabel informasi penjualan produk dan tabel informasi pelanggan, gabungan dalam (tipe yang dibuat oleh Panduan Kueri) akan mencegah pengambilan catatan pelanggan bagi pelanggan yang belum melakukan pembelian. Menggunakan Microsoft Query, Anda bisa menggabungkan tabel ini sehingga semua catatan pelanggan diambil, bersama dengan data penjualan untuk pelanggan yang telah melakukan pembelian.

Untuk memulai Microsoft Query, lakukan langkah-langkah berikut.

1. Pada tab Data , dalam grup Dapatkan Data Eksternal , klik Dari Sumber Lain, lalu klik Dari Microsoft Query.

2. Dalam kotak dialog Pilih Sumber Data , pastikan bahwa kotak centang Gunakan Panduan Kueri untuk membuat/mengedit kueri dikosongkan.

3. Klik ganda sumber data yang ingin Anda gunakan.

   -atau-

   Klik sumber data yang ingin Anda gunakan, lalu klik OK.

Menggunakan kembali dan berbagi kueri     Dalam Panduan Kueri dan Microsoft Query, Anda dapat menyimpan kueri sebagai file .dqy yang dapat diubah, digunakan kembali, dan dibagikan. Excel bisa membuka file .dqy secara langsung, yang memungkinkan Anda atau pengguna lain untuk membuat rentang data eksternal tambahan dari kueri yang sama.

Untuk membuka kueri yang disimpan dari Excel:

1. Pada tab Data , dalam grup Dapatkan Data Eksternal , klik Dari Sumber Lain, lalu klik Dari Microsoft Query. Kotak dialog Pilih Sumber Data ditampilkan.

2. Dalam kotak dialog Pilih Sumber Data , klik tab Kueri .

3. Klik ganda kueri yang disimpan yang ingin Anda buka. Kueri ditampilkan di Microsoft Query.

Jika Anda ingin membuka kueri yang disimpan dan Microsoft Query sudah terbuka, klik menu File Kueri Microsoft, lalu klik Buka.

Jika Anda mengklik ganda file .dqy, Excel akan terbuka, menjalankan kueri, lalu menyisipkan hasilnya ke lembar kerja baru.

Jika Anda ingin berbagi ringkasan atau laporan Excel yang didasarkan pada data eksternal, Anda bisa memberi pengguna lain buku kerja yang berisi rentang data eksternal, atau Anda bisa membuat templat. Templat memungkinkan Anda menyimpan ringkasan atau laporan tanpa menyimpan data eksternal sehingga file lebih kecil. Data eksternal diambil ketika pengguna membuka templat laporan.

Setelah Anda membuat kueri baik dalam Panduan Kueri atau Microsoft Query, Anda bisa mengembalikan data ke lembar kerja Excel. Data kemudian menjadi rentang data eksternal atau laporan PivotTable yang bisa Anda format dan refresh.

Memformat data yang diambil     Di Excel, Anda bisa menggunakan alat, seperti bagan atau subtotal otomatis, untuk menyajikan dan meringkas data yang diambil oleh Microsoft Query. Anda bisa memformat data, dan pemformatan Anda dipertahankan saat Anda merefresh data eksternal. Anda bisa menggunakan label kolom Anda sendiri, bukan nama bidang, dan menambahkan nomor baris secara otomatis.

Excel bisa secara otomatis memformat data baru yang Anda ketik di akhir rentang untuk mencocokkan baris sebelumnya. Excel juga bisa secara otomatis menyalin rumus yang telah diulangi di baris sebelumnya dan memperluasnya ke baris tambahan.

Anda dapat mengaktifkan opsi ini (atau menonaktifkan lagi) kapan saja:

1. Klik File > Opsi > Tingkat Lanjut.

2. Di bagian Opsi pengeditan , pilih centang Perluas format dan rumus rentang data . Untuk menonaktifkan pemformatan rentang data otomatis lagi, kosongkan kotak centang ini.

Melakukan refresh data eksternal     Saat merefresh data eksternal, Anda menjalankan kueri untuk mengambil data baru atau yang diubah yang sesuai dengan spesifikasi Anda. Anda bisa merefresh kueri di Microsoft Query dan Excel. Excel menyediakan beberapa opsi untuk merefresh kueri, termasuk merefresh data setiap kali Anda membuka buku kerja dan merefreshnya secara otomatis pada interval berwaktu. Anda bisa terus bekerja di Excel saat data sedang direfresh, dan Anda juga bisa memeriksa status saat data sedang direfresh. Untuk informasi selengkapnya, lihat Merefresh koneksi data eksternal di Excel.

Atas Halaman