Anda dapat menggunakan Microsoft Query untuk mengambil data dari sumber eksternal. Dengan menggunakan Microsoft Query untuk mengambil data dari database dan file perusahaan, Anda tidak perlu mengetik ulang data yang ingin dianalisis di Excel. Anda juga dapat merefresh laporan dan ringkasan Excel secara otomatis dari database sumber asli setiap kali database diperbarui dengan informasi baru.
Pelajari selengkapnya tentang Microsoft Query
Dengan Microsoft Query, Anda dapat tersambung ke sumber data eksternal, memilih data dari sumber eksternal tersebut, mengimpor data tersebut ke lembar kerja, dan merefresh data sesuai kebutuhan agar data lembar kerja tetap disinkronkan dengan data di sumber eksternal.
Jenis database yang dapat 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 dapat mengambil data dari buku kerja Excel dan dari file teks.
Microsoft Office menyediakan driver yang dapat 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 jenis database OLAP lainnya. Untuk informasi tentang menginstal driver ODBC atau driver sumber data yang tidak tercantum di sini, periksa dokumentasi database, atau hubungi vendor database Anda.
Memilih data dari database Anda mengambil data dari database dengan membuat kueri, yang merupakan pertanyaan yang Anda ajukan tentang data yang disimpan dalam database eksternal. Misalnya, jika data Anda disimpan dalam database Access, Anda mungkin ingin mengetahui angka penjualan untuk produk tertentu berdasarkan wilayah. Anda dapat mengambil bagian data dengan memilih hanya data untuk produk dan kawasan yang ingin dianalisis.
Dengan Microsoft Query, Anda dapat memilih kolom data yang Anda inginkan dan hanya mengimpor data tersebut ke Excel.
Memperbarui lembar kerja Anda dalam satu operasi Setelah memiliki data eksternal di buku kerja Excel, setiap kali database berubah, Anda dapat me-refresh data untuk memperbarui analisis — tanpa harus membuat ulang laporan ringkasan dan bagan. Misalnya, Anda dapat membuat ringkasan penjualan bulanan dan me-refresh ringkasan penjualan setiap bulan saat angka penjualan baru masuk.
Cara Microsoft Query menggunakan sumber data Setelah menyiapkan sumber data untuk database tertentu, Anda dapat 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 tersambung ke database eksternal dan untuk memperlihatkan data apa yang tersedia. Setelah membuat kueri dan mengembalikan data ke Excel, Microsoft Query menyediakan buku kerja Excel dengan informasi kueri dan sumber data sehingga Anda dapat menyambungkan kembali ke database saat Anda ingin me-refresh 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.
Menyambungkan ke sumber data
Apa itu sumber data? Sumber data adalah kumpulan informasi tersimpan yang memungkinkan Excel dan Microsoft Query tersambung ke database eksternal. Ketika menggunakan Microsoft Query untuk menyiapkan sumber data, Anda memberikan nama kepada sumber data, lalu memberikan nama dan lokasi database atau server, jenis database, serta informasi masuk dan kata sandi Anda. Informasi tersebut juga mencakup nama driver OBDC atau driver sumber data, yang merupakan program yang membuat koneksi ke jenis database tertentu.
Untuk menyiapkan sumber data menggunakan Microsoft Query:
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 grup menu Wizard Warisan . Menu ini tidak ditampilkan secara default. Untuk mengaktifkan, buka File, Opsi, Data, dan aktifkan di bagian Tampilkan panduan impor data warisan .
Lakukan salah satu langkah berikut:
- Untuk menentukan sumber data 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.
Klik dua kali <Sumber Data Baru>.
-atau-
<Klik Sumber Data Baru>, lalu klik OK.
Kotak dialog Buat Sumber Data Baru ditampilkan.Pada langkah 1, ketik nama untuk mengidentifikasi sumber data.
Pada 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, Anda perlu mendapatkan dan menginstal driver ODBC yang kompatibel dengan Microsoft Office dari vendor pihak ketiga, seperti produsen database. Hubungi vendor database untuk instruksi penginstalan.
- Database OLAP tidak memerlukan driver ODBC. Ketika Anda menginstal Microsoft Query, driver diinstal untuk database yang dibuat menggunakan Microsoft SQL Server Analysis Services. Untuk tersambung ke database OLAP lainnya, Anda perlu menginstal driver sumber data dan perangkat lunak klien.
Klik Sambungkan, lalu berikan informasi yang diperlukan untuk tersambung ke sumber data Anda. Untuk database, buku kerja Excel, dan file teks, informasi yang Anda berikan tergantung pada tipe sumber data yang Anda pilih. Anda mungkin diminta untuk memberikan nama masuk, kata sandi, versi database yang digunakan, lokasi database, atau informasi lain yang spesifik untuk jenis database tersebut.
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.
Setelah memasukkan informasi yang diperlukan, klik OK atau Selesai untuk kembali ke kotak dialog Buat Sumber Data Baru .
Jika database Anda memiliki tabel dan Anda ingin tabel tertentu ditampilkan secara otomatis di Panduan Kueri, klik kotak untuk langkah 4, lalu klik tabel yang Anda inginkan.
Jika Anda tidak ingin mengetikkan nama masuk dan kata sandi saat menggunakan sumber data, pilih kotak centang Simpan ID pengguna dan kata sandi saya di definisi sumber data . Kata sandi yang disimpan tidak dienkripsi. Jika kotak centang tidak tersedia, hubungi administrator database Anda untuk menentukan apakah opsi ini dapat disediakan.
Catatan
Hindari menyimpan informasi masuk saat menyambungkan ke sumber data. Informasi ini mungkin disimpan sebagai teks biasa, dan pengguna yang mencurigakan dapat mengakses informasi tersebut untuk membahayakan keamanan sumber data.
Setelah menyelesaikan langkah-langkah ini, nama sumber data Anda muncul di kotak dialog Pilih Sumber Data .
Menggunakan Panduan Kueri untuk menentukan kueri
Menggunakan Panduan Kueri untuk sebagian besar kueri Panduan Kueri memudahkan Anda memilih dan menyatukan data dari berbagai tabel dan bidang dalam database Anda. Dengan menggunakan 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 wizard mengenali bidang kunci utama dalam satu tabel dan bidang dengan nama yang sama di tabel kedua.
Anda juga dapat menggunakan wizard untuk mengurutkan rangkaian hasil dan melakukan pemfilteran sederhana. Pada langkah terakhir wizard, Anda dapat memilih untuk mengembalikan data ke Excel, atau menyempurnakan kueri lebih lanjut di Microsoft Query. Setelah membuat kueri, Anda dapat menjalankannya di Excel atau Microsoft Query.
Untuk memulai Panduan Kueri, lakukan langkah-langkah berikut.
- Pada tab Data , dalam grup Dapatkan Data Eksternal , klik Dari Sumber Lain, lalu klik Dari Microsoft Query.
- Dalam kotak dialog Pilih Sumber Data , pastikan kotak centang Gunakan Wizard Kueri untuk membuat/mengedit kueri dipilih.
- Klik dua kali 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 ingin membuat kueri yang lebih kompleks daripada yang diizinkan oleh Panduan Kueri, Anda dapat bekerja langsung di Microsoft Query. Anda dapat menggunakan Microsoft Query untuk melihat dan mengubah kueri yang mulai dibuat di Panduan Kueri, atau membuat kueri baru tanpa menggunakan wizard. Bekerja langsung di Microsoft Query ketika Anda ingin membuat kueri yang melakukan hal berikut:
- Memilih data tertentu dari bidang Dalam database besar, Anda mungkin ingin memilih beberapa data dalam bidang dan menghilangkan data yang tidak diperlukan. Misalnya, jika memerlukan data untuk dua produk dalam bidang yang berisi informasi untuk banyak produk, Anda dapat menggunakan kriteria untuk memilih data hanya untuk dua produk yang Anda inginkan.
- Ambil data berdasarkan kriteria yang berbeda setiap kali Anda menjalankan kueri Jika perlu membuat laporan atau ringkasan Excel yang sama untuk beberapa area dalam data eksternal yang sama — seperti laporan penjualan terpisah untuk setiap wilayah — Anda dapat membuat kueri parameter. Saat menjalankan kueri parameter, Anda akan diminta untuk menggunakan nilai sebagai kriteria saat kueri memilih catatan. Misalnya, kueri parameter mungkin meminta Anda memasukkan wilayah tertentu, dan Anda dapat menggunakan kembali kueri ini untuk membuat setiap laporan penjualan regional.
- Menggabungkan data dengan cara yang berbeda Gabungan dalam yang dibuat oleh Wizard Kueri adalah jenis gabungan yang paling umum digunakan dalam membuat kueri. Namun, terkadang Anda ingin menggunakan jenis 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 untuk pelanggan yang belum melakukan pembelian. Dengan Microsoft Query, Anda dapat menggabungkan tabel ini sehingga semua catatan pelanggan diambil, beserta data penjualan untuk pelanggan yang telah melakukan pembelian.
Untuk memulai Microsoft Query, lakukan langkah-langkah berikut.
- Pada tab Data , dalam grup Dapatkan Data Eksternal , klik Dari Sumber Lain, lalu klik Dari Microsoft Query.
- Dalam kotak dialog Pilih Sumber Data , pastikan kotak centang Gunakan Wizard Kueri untuk membuat/mengedit kueri sudah kosong.
- Klik dua kali sumber data yang ingin Anda gunakan.
-atau-
Klik sumber data yang ingin Anda gunakan, lalu klik OK.
Menggunakan kembali dan berbagi kueri Di Wizard Kueri dan Microsoft Query, Anda dapat menyimpan kueri sebagai file .dqy yang dapat diubah, digunakan kembali, dan dibagikan. Excel dapat membuka file .dqy secara langsung, yang memungkinkan Anda atau pengguna lain membuat rentang data eksternal tambahan dari kueri yang sama.
Untuk membuka kueri tersimpan dari Excel:
- Pada tab Data , dalam grup Dapatkan Data Eksternal , klik Dari Sumber Lain, lalu klik Dari Microsoft Query. Kotak dialog Pilih Sumber Data akan ditampilkan.
- Dalam kotak dialog Pilih Sumber Data , klik tab Kueri .
- Klik dua kali kueri tersimpan yang ingin Anda buka. Kueri ditampilkan di Microsoft Query.
Jika Anda ingin membuka kueri tersimpan dan Microsoft Query sudah terbuka, klik menu File Kueri Microsoft, lalu klik Buka.
Jika Anda mengeklik dua kali file .dqy, Excel akan terbuka, menjalankan kueri, lalu menyisipkan hasilnya ke lembar kerja baru.
Jika ingin berbagi ringkasan atau laporan Excel yang didasarkan pada data eksternal, Anda dapat memberikan buku kerja yang berisi rentang data eksternal kepada pengguna lain, atau Anda dapat membuat templat. Templat memungkinkan Anda menyimpan ringkasan atau laporan tanpa menyimpan data eksternal sehingga file menjadi lebih kecil. Data eksternal diambil saat pengguna membuka templat laporan.
Bekerja dengan data di Excel
Setelah membuat kueri di Wizard Kueri atau Microsoft Query, Anda dapat mengembalikan data ke lembar kerja Excel. Data kemudian menjadi rentang data eksternal atau laporan PivotTable yang dapat Anda format dan refresh.
Memformat data yang diambil Di Excel, Anda dapat menggunakan alat, seperti bagan atau subtotal otomatis, untuk menyajikan dan meringkas data yang diambil oleh Microsoft Query. Anda dapat memformat data, dan pemformatan Anda dipertahankan saat Anda me-refresh data eksternal. Anda dapat menggunakan label kolom Anda sendiri sebagai pengganti nama bidang, dan menambahkan nomor baris secara otomatis.
Excel dapat secara otomatis memformat data baru yang Anda ketik di akhir rentang agar sesuai dengan baris sebelumnya. Excel juga dapat secara otomatis menyalin rumus yang telah diulang pada baris sebelumnya dan memperluasnya ke baris tambahan.
Catatan
Agar dapat diperluas ke baris baru dalam rentang, format dan rumus harus muncul setidaknya dalam tiga dari lima baris sebelumnya.
Anda dapat mengaktifkan opsi ini (atau menonaktifkan lagi) kapan saja:
- KlikOpsi File>>Tingkat Lanjut.
- Di bagian Opsi pengeditan , pilih pemeriksaan Format dan rumus Perluas rentang data . Untuk menonaktifkan lagi pemformatan rentang data otomatis, kosongkan kotak centang ini.
Me-refresh data eksternal Ketika me-refresh data eksternal, Anda menjalankan kueri untuk mengambil data baru atau yang diubah yang sesuai dengan spesifikasi Anda. Anda dapat me-refresh kueri di Microsoft Query dan Excel. Excel menyediakan beberapa opsi untuk me-refresh kueri, termasuk merefresh data setiap kali Anda membuka buku kerja dan merefreshnya secara otomatis pada interval waktu. Anda dapat terus bekerja di Excel saat data sedang direfresh, dan Anda juga dapat memeriksa status saat data sedang direfresh. Untuk informasi selengkapnya, lihat Me-refresh koneksi data eksternal di Excel.