Bagaimana perjalanan data melalui Excel

Berikut adalah hal-hal utama yang bisa Anda lakukan saat data berada di stasiun kereta Excel:

• Mengimpor    Anda bisa mengimpor data dari berbagai sumber data eksternal yang berbeda. Sumber data ini dapat berada di komputer Anda, di awan, atau setengah jalan di seluruh dunia. Untuk informasi selengkapnya, lihat Mengimpor data dari sumber data eksternal.

• Power Query    Anda bisa menggunakan Power Query (sebelumnya disebut Dapatkan & Transformasi) untuk membuat kueri untuk membentuk, mentransformasi, dan menggabungkan data dalam berbagai cara. Anda bisa mengekspor pekerjaan Anda sebagai Templat Power Query untuk menentukan operasi aliran data di Power Apps. Anda bahkan dapat membuat tipe data untuk melengkapi tipe data yang ditautkan. Untuk informasi selengkapnya, lihat Power Query untuk Bantuan Excel.

• Keamanan    Privasi, kredensial, dan autentikasi data selalu menjadi perhatian berkelanjutan. Untuk informasi selengkapnya, lihat Mengelola pengaturan sumber data dan izin serta Mengatur tingkat privasi.

• Refresh    Data yang diimpor biasanya memerlukan operasi refresh untuk membawa perubahan, seperti penambahan, pembaruan, dan penghapusan, ke Excel. Untuk informasi selengkapnya, lihat Merefresh koneksi data eksternal di Excel.

• Connections/Properti    Setiap sumber data eksternal memiliki beragam koneksi dan informasi properti yang terkait dengannya yang terkadang memerlukan perubahan tergantung pada keadaan Anda. Untuk informasi selengkapnya, lihat Mengelola rentang data eksternal dan propertinya, Membuat, mengedit, dan mengelola koneksi ke data eksternal, dan Properti koneksi.

• Warisan    Metode tradisional, seperti Panduan Impor Warisan dan MSQuery, masih tersedia untuk digunakan. Untuk informasi selengkapnya, lihat Opsi impor dan analisis data dan Menggunakan Microsoft Query untuk mengambil data eksternal.

Bagian berikut ini menyediakan detail selengkapnya tentang apa yang terjadi di balik layar di stasiun kereta Excel yang sibuk ini.

Ada properti koneksi, kueri, dan rentang data eksternal. Properti koneksi dan kueri keduanya berisi informasi koneksi tradisional. Dalam judul kotak dialog, Properti Koneksi berarti tidak ada kueri yang terkait dengannya, tapi Properti Kueri berarti ada. Properti rentang data eksternal mengontrol tata letak dan format data. Semua sumber data memiliki kotak dialog Properti Data Eksternal , tapi sumber data yang memiliki kredensial terkait dan informasi refresh menggunakan kotak dialog Properti Data Rentang Eksternal yang lebih besar.

Informasi berikut ini merangkum kotak dialog, panel, jalur perintah, dan topik bantuan terkait yang paling penting.

Kotak Dialog atau Panel Jalur Perintah	Tab dan terowongan	Topik Bantuan Utama
Sumber terbaru Data > Sumber Terbaru	(Tanpa tab) Kotak dialog Tunnels to Connect > Navigator	Mengelola pengaturan dan izin sumber data
Properti Koneksi Panduan Koneksi Data OR Kueri> Data & Connections tab Connections > > (klik kanan koneksi) > Properti	Tab Definisi tab PenggunaanDigunakan Di tab	Properti koneksi
Properti Kueri Data > > Connections yang Sudah Ada (klik kanan koneksi) > Edit Properti Koneksi KUERI >Data & Koneksi| Tab Kueri > (klik kanan koneksi) > Properti Properti >Kueri OR OR Data > Refresh Semua Connections> (ketika diposisikan pada lembar kerja kueri yang dimuat)	Tab Definisi tab PenggunaanDigunakan Di tab	Properti koneksi
& Connections Kueri & Connections Kueri> Data	Tab Kueri Connections  tab	Properti koneksi
Connections yang sudah ada Data > Connections yang Sudah Ada	tabConnections tab Tabel	Menyambungkan ke data eksternal
Properti data eksternal PROPERTI rentang data eksternal OR PROPERTI >Data OR (Dinonaktifkan jika tidak diposisikan pada lembar kerja kueri)	Digunakan dalam tab (dari kotak dialog Properti Koneksi )   Tombol Refresh di terowongan kanan ke Properti Kueri	Mengelola rentang data eksternal dan propertinya
Tab Properti Koneksi > Definisi > Ekspor File Koneksi OR Kueri > Ekspor File Koneksi	(Tanpa tab) Kotak dialog Terowongan ke File Foldersumber data	Membuat, mengedit, dan mengelola koneksi ke data eksternal

Data dalam buku kerja Excel bisa berasal dari dua lokasi berbeda. Data mungkin disimpan secara langsung dalam buku kerja, atau mungkin disimpan di sumber data eksternal, seperti file teks, database, atau kubus Pemrosesan Analitik Online (OLAP). Sumber data eksternal ini tersambung ke buku kerja melalui koneksi data, yang merupakan sekumpulan informasi yang menjelaskan cara menemukan, masuk, dan mengakses sumber data eksternal.

Manfaat utama menyambungkan ke data eksternal adalah Anda bisa menganalisis data ini secara berkala tanpa berulang kali menyalin data ke buku kerja Anda, yang merupakan operasi yang bisa memakan waktu dan rentan terhadap kesalahan. Setelah menyambungkan ke data eksternal, Anda juga bisa secara otomatis merefresh (atau memperbarui) buku kerja Excel Anda dari sumber data asli setiap kali sumber data diperbarui dengan informasi baru.

Informasi koneksi disimpan dalam buku kerja dan juga bisa disimpan dalam file koneksi, seperti file Koneksi Data Office (ODC) (.odc) atau file Nama Sumber Data (.dsn).

Untuk membawa data eksternal ke Excel, Anda memerlukan akses ke data. Jika sumber data eksternal yang ingin Anda akses tidak ada di komputer lokal, Anda mungkin perlu menghubungi administrator database untuk kata sandi, izin pengguna, atau informasi koneksi lainnya. Jika sumber data adalah database, pastikan bahwa database tidak dibuka dalam mode eksklusif. Jika sumber data adalah file teks atau lembar bentang, pastikan pengguna lain tidak membukanya untuk akses eksklusif.

Banyak sumber data juga memerlukan driver ODBC atau penyedia OLE DB untuk mengkoordinasikan alur data antara Excel, file koneksi, dan sumber data.

Diagram berikut ini meringkas poin utama tentang koneksi data.

1. Terdapat berbagai sumber data yang dapat Anda sambungkan: Analysis Services, SQL Server, Microsoft Access, database, lembar bentang, dan file teks OLAP dan relasinal lainnya.

2. Banyak sumber data memiliki driver ODBC atau penyedia OLE DB terkait.

3. File koneksi menentukan semua informasi yang diperlukan untuk mengakses dan mengambil data dari sumber data.

4. Informasi koneksi disalin dari file koneksi ke dalam buku kerja, dan informasi koneksi bisa dengan mudah diedit.

5. Data disalin ke dalam buku kerja sehingga Anda bisa menggunakannya sama seperti Anda menggunakan data yang disimpan secara langsung dalam buku kerja.

Untuk menemukan file koneksi, gunakan kotak dialog Connections yang Sudah Ada. (Pilih Data > Connections yang Sudah Ada.) Menggunakan kotak dialog ini, Anda bisa melihat tipe koneksi berikut:

• Connections dalam buku kerja    

  Daftar ini menampilkan semua koneksi saat ini dalam buku kerja. Daftar dibuat dari koneksi yang sudah Anda tetapkan, yang Anda buat menggunakan kotak dialog Pilih Sumber Data dari Panduan Koneksi Data, atau dari koneksi yang sebelumnya Anda pilih sebagai koneksi dari kotak dialog ini.

• File koneksi di komputer Anda    

  Daftar ini dibuat dari folder Sumber Data Saya yang biasanya disimpan dalam folder Dokumen.

• Berkas koneksi pada jaringan    

  Daftar ini dapat dibuat dari sekumpulan folder di jaringan lokal Anda, lokasi yang dapat digunakan di seluruh jaringan sebagai bagian dari penyebaran kebijakan grup Microsoft Office, atau pustaka SharePoint. 

Anda juga bisa menggunakan Excel sebagai editor file koneksi untuk membuat dan mengedit koneksi ke sumber data eksternal yang disimpan dalam buku kerja atau dalam file koneksi. Jika Anda tidak menemukan koneksi yang Anda inginkan, Anda bisa membuat koneksi dengan mengklik Telusuri Selengkapnya untuk menampilkan kotak dialog Pilih Sumber Data , lalu mengklik Sumber Baru untuk memulai Panduan Koneksi Data.

Setelah membuat koneksi, Anda bisa menggunakan kotak dialog Properti Koneksi (Pilih Data > Kueri & Connections > tab Connections > (klik kanan koneksi) > Properti) untuk mengontrol berbagai pengaturan koneksi ke sumber data eksternal, dan untuk menggunakan, menggunakan kembali, atau beralih file koneksi.

Catatan    Terkadang, kotak dialog Properti Koneksi dinamai kotak dialog Properti Kueri ketika ada kueri yang dibuat di Power Query (dulu disebut Dapatkan & Transformasi) yang terkait dengannya.

Jika Anda menggunakan file koneksi untuk menyambungkan ke sumber data, Excel menyalin informasi koneksi dari file koneksi ke dalam buku kerja Excel. Saat Anda membuat perubahan dengan menggunakan kotak dialog Properti Koneksi , Anda mengedit informasi koneksi data yang disimpan di buku kerja Excel saat ini dan bukan file koneksi data asli yang mungkin telah digunakan untuk membuat koneksi (ditunjukkan dengan nama file yang ditampilkan dalam properti File Koneksi pada tab Definisi ). Setelah Anda mengedit informasi koneksi (dengan pengecualian properti Nama Koneksi dan Deskripsi Koneksi ), tautan ke file koneksi dihapus dan properti File Koneksi dihapus.

Untuk memastikan bahwa file koneksi selalu digunakan saat sumber data direfresh, klik Selalu coba menggunakan file ini untuk merefresh data ini pada tab Definisi . Memilih kotak centang ini memastikan bahwa pembaruan untuk file koneksi akan selalu digunakan oleh semua buku kerja yang menggunakan file koneksi tersebut, yang juga harus memiliki kumpulan properti ini.

Dengan menggunakan kotak dialog Connections, Anda bisa dengan mudah mengelola koneksi ini, termasuk membuat, mengedit, dan menghapusnya (Pilih Data > Kueri & Connections >tab Connections > (klik kanan koneksi) > Properti.) Anda bisa menggunakan kotak dialog ini untuk melakukan hal berikut: 

• Membuat, mengedit, merefresh, dan menghapus koneksi yang sedang digunakan dalam buku kerja.

• Verifikasi sumber data eksternal. Anda mungkin ingin melakukan ini sekiranya koneksi ditentukan oleh pengguna lain.

• Memperlihatkan di mana setiap koneksi digunakan dalam buku kerja saat ini.

• Mendiagnosis pesan kesalahan tentang koneksi ke data eksternal.

• Alihkan koneksi ke server atau sumber data lain, atau ganti file koneksi untuk koneksi yang sudah ada.

• Permudah membuat dan berbagi file koneksi dengan pengguna.

File koneksi sangat berguna untuk berbagi koneksi secara konsisten, membuat koneksi lebih mudah ditemukan, membantu meningkatkan keamanan koneksi, dan memfasilitasi administrasi sumber data. Cara terbaik untuk berbagi file koneksi adalah dengan meletakkannya di lokasi yang aman dan tepercaya, seperti folder jaringan atau pustaka SharePoint, di mana pengguna bisa membaca file tetapi hanya pengguna yang ditunjuk yang bisa mengubah file. Untuk informasi selengkapnya, lihat Berbagi data dengan ODC.

Menggunakan file ODC

Anda bisa membuat file Office Data Connection (ODC) (.odc) dengan menyambungkan ke data eksternal melalui kotak dialog Pilih Sumber Data atau dengan menggunakan Panduan Koneksi Data untuk menyambungkan ke sumber data baru. File ODC menggunakan tag HTML dan XML kustom untuk menyimpan informasi koneksi. Anda bisa dengan mudah menampilkan atau mengedit konten file di Excel.

Anda bisa berbagi file koneksi dengan orang lain untuk memberi mereka akses yang sama yang Anda miliki ke sumber data eksternal. Pengguna lain tidak perlu menyiapkan sumber data untuk membuka file koneksi, tetapi mereka mungkin perlu menginstal driver ODBC atau penyedia OLE DB yang diperlukan untuk mengakses data eksternal di komputer mereka.

File ODC adalah metode yang disarankan untuk menyambungkan ke data dan berbagi data. Anda bisa dengan mudah mengonversi file koneksi tradisional lainnya (file DSN, UDL, dan kueri) menjadi file ODC dengan membuka file koneksi lalu mengklik tombol Ekspor File Koneksi pada tab Definisi dari kotak dialog Properti Koneksi .

Menggunakan file kueri

File kueri adalah file teks yang berisi informasi sumber data, termasuk nama server tempat data berada dan informasi koneksi yang Anda sediakan saat Anda membuat sumber data. File kueri adalah cara tradisional untuk berbagi kueri dengan pengguna Excel lainnya.

Menggunakan file kueri .dqy    Anda bisa menggunakan Microsoft Query untuk menyimpan file .dqy yang berisi kueri untuk data dari database relasialis atau file teks. Saat Anda membuka file ini di Microsoft Query, Anda bisa menampilkan data yang dikembalikan oleh kueri dan mengubah kueri untuk mengambil hasil yang berbeda. Anda bisa menyimpan file .dqy untuk kueri apa pun yang Anda buat, baik dengan menggunakan Panduan Kueri atau secara langsung di Microsoft Query.

Menggunakan file kueri .oqy    Anda dapat menyimpan file .oqy untuk menyambungkan ke data dalam database OLAP, baik di server maupun dalam file kubus offline (.cub). Saat Anda menggunakan Panduan Koneksi Multi Dimensi di Microsoft Query untuk membuat sumber data untuk database atau kubus OLAP, file .oqy dibuat secara otomatis. Karena database OLAP tidak tertata dalam rekaman atau tabel, Anda tidak dapat membuat kueri atau file .dqy untuk mengakses database ini.

Menggunakan file kueri .rqy    Excel dapat membuka file kueri dalam format .rqy untuk mendukung driver sumber data OLE DB yang menggunakan format ini. Untuk informasi selengkapnya, lihat dokumentasi untuk driver Anda.

Menggunakan file kueri .qry    Microsoft Query dapat membuka dan menyimpan file kueri dalam format .qry untuk digunakan dengan versi Microsoft Query yang lebih lama yang tidak dapat membuka file .dqy. Jika Anda memiliki file kueri dalam format .qry yang ingin Anda gunakan di Excel, buka file di Microsoft Query, lalu simpan sebagai file .dqy. Untuk informasi tentang menyimpan file .dqy, lihat Bantuan Microsoft Query.

Menggunakan file kueri .iqy Web    Excel bisa membuka file kueri .iqy Web untuk mengambil data dari Web. Untuk informasi selengkapnya, lihat Mengekspor ke Excel dari SharePoint.

Rentang data eksternal (juga disebut tabel kueri) adalah nama atau nama tabel yang ditentukan yang menentukan lokasi data yang dibawa ke dalam lembar kerja. Saat Anda menyambungkan ke data eksternal, Excel secara otomatis membuat rentang data eksternal. Satu-satunya pengecualian untuk ini adalah laporan PivotTable yang tersambung ke sumber data, yang tidak membuat rentang data eksternal. Di Excel, Anda bisa memformat dan menata letak rentang data eksternal atau menggunakannya dalam perhitungan, seperti data lainnya.

Excel secara otomatis memberi nama rentang data eksternal sebagai berikut:

• Rentang data eksternal dari file Office Data Connection (ODC) diberi nama yang sama dengan nama file.

• Rentang data eksternal dari database diberi nama dengan nama kueri. Secara default Query_from_source adalah nama sumber data yang Anda gunakan untuk membuat kueri.

• Rentang data eksternal dari file teks diberi nama dengan nama file teks.

• Rentang data eksternal dari kueri Web dinamai dengan nama halaman Web tempat data diambil.

Jika lembar kerja Anda memiliki lebih dari satu rentang data eksternal dari sumber yang sama, rentang akan diberi nomor. Misalnya, Teks Saya, MyText_1, MyText_2, dan seterusnya.

Rentang data eksternal memiliki properti tambahan (tidak bingung dengan properti koneksi) yang bisa Anda gunakan untuk mengontrol data, seperti mempertahankan pemformatan sel dan lebar kolom. Anda dapat mengubah properti rentang data eksternal ini dengan mengklik Properti dalam grup Connections pada tab Data, lalu membuat perubahan dalam kotak dialog Properti Rentang Data Eksternal atau Properti Data Eksternal.

Ada beberapa objek data (seperti rentang data eksternal dan laporan PivotTable) yang bisa Anda gunakan untuk menyambungkan ke sumber data yang berbeda. Namun, tipe sumber data yang bisa Anda sambungkan berbeda di antara setiap objek data.

Anda bisa menggunakan dan merefresh data yang tersambung di Excel Services. Seperti sumber data eksternal apa pun, Anda mungkin perlu mengautentikasi akses Anda. Untuk informasi selengkapnya, lihat Merefresh koneksi data eksternal di Excel. Fatau informasi selengkapnya tentang kredensial, lihat Excel Services Pengaturan Autentikasi.

Tabel berikut ini merangkum sumber data mana yang didukung untuk setiap objek data di Excel.

Melampaui data benda	Menciptakan Eksternal data lingkup?		OLE DB	ODBC	Teks arsip	HTML arsip	XML arsip	SharePoint daftar
Panduan Impor Teks	Ya		Tidak	Tidak	Ya	Tidak	Tidak	Tidak
Laporan PivotTable (non-OLAP)	Tidak		Ya	Ya	Ya	Tidak	Tidak	Ya
Laporan PivotTable (OLAP)	Tidak		Ya	Tidak	Tidak	Tidak	Tidak	Tidak
Tabel Excel	Ya		Ya	Ya	Tidak	Tidak	Ya	Ya
Peta XML	Ya		Tidak	Tidak	Tidak	Tidak	Ya	Tidak
Kueri Web	Ya		Tidak	Tidak	Tidak	Ya	Ya	Tidak
Panduan Koneksi Data	Ya		Ya	Ya	Ya	Ya	Ya	Ya
Microsoft Query	Ya		Tidak	Ya	Ya	Tidak	Tidak	Tidak

solusi Excel Services untuk tabel Excel dan rentang bernama

Jika Anda ingin menampilkan buku kerja Excel di Excel Services, Anda bisa menyambungkan dan merefresh data, tapi Anda harus menggunakan laporan PivotTable. Excel Services tidak mendukung rentang data eksternal, yang berarti bahwa Excel Services tidak mendukung Tabel Excel yang tersambung ke sumber data, kueri Web, peta XML, atau Microsoft Query.

Namun, Anda bisa mengatasi batasan ini dengan menggunakan PivotTable untuk menyambungkan ke sumber data, lalu mendesain dan tata letak PivotTable sebagai tabel dua dimensi tanpa tingkat, grup, atau subtotal sehingga semua nilai baris dan kolom yang diinginkan ditampilkan. 

Mari kita lakukan perjalanan menyusuri jalur memori database.

Tentang MDAC, OLE DB, dan OBC

Pertama-tali, minta maaf untuk semua akronim. Microsoft Data Access Components (MDAC) 2.8 disertakan dengan Microsoft Windows . Dengan MDAC, Anda bisa menyambungkan ke dan menggunakan data dari berbagai sumber data relasialis dan nonrelasi. Anda dapat menyambungkan ke berbagai sumber data menggunakan driver Konektivitas Database Terbuka (ODBC) atau penyedia OLE DB , yang dibuat dan dikirim oleh Microsoft atau dikembangkan oleh berbagai pihak ketiga. Saat Anda menginstal Microsoft Office, driver ODBC tambahan dan penyedia OLE DB ditambahkan ke komputer Anda.

Untuk melihat daftar lengkap penyedia OLE DB yang terinstal di komputer Anda, tampilkan kotak dialog Properti Link Data dari file Link Data, lalu klik tab Penyedia .

Untuk melihat daftar lengkap penyedia ODBC yang terinstal di komputer Anda, tampilkan kotak dialog Administrator Database ODBC , lalu klik tab Driver .

Anda juga dapat menggunakan driver ODBC dan penyedia OLE DB dari produsen lain untuk mendapatkan informasi dari sumber selain sumber data Microsoft, termasuk tipe database ODBC dan OLE DB lainnya. Untuk informasi tentang menginstal driver ODBC atau ini penyedia OLE DB, periksa dokumentasi untuk database, atau hubungi vendor database Anda.

Menggunakan ODBC untuk menyambungkan ke sumber data

Dalam arsitektur ODBC, aplikasi (seperti Excel) tersambung ke Manajer Driver ODBC, yang pada gilirannya menggunakan driver ODBC tertentu (seperti driver Microsoft SQL ODBC) untuk menyambungkan ke sumber data (seperti database SQL Server Microsoft).

Untuk menyambungkan ke sumber data ODBC, lakukan hal berikut:

1. Pastikan bahwa driver ODBC yang sesuai diinstal di komputer yang berisi sumber data.

2. Tentukan nama sumber data (DSN) dengan menggunakan Administrator Sumber Data ODBC untuk menyimpan informasi koneksi dalam registri atau file DSN, atau string koneksi dalam kode Microsoft Visual Basic untuk mengirimkan informasi koneksi langsung ke Manajer Driver ODBC.

   Untuk menentukan sumber data, di Windows klik tombol Mulai lalu klik Panel Kontrol. Klik Sistem dan Pemeliharaan, lalu klik Alat Administratif. Klik Kinerja dan Pemeliharaan, klik Alat Administratif. lalu klik Sumber Data (ODBC). Untuk informasi selengkapnya tentang opsi yang berbeda, klik tombol Bantuan di setiap kotak dialog.

Sumber data mesin

Sumber data mesin menyimpan informasi koneksi dalam registri, di komputer tertentu, dengan nama yang ditentukan pengguna. Anda dapat menggunakan sumber data mesin hanya di komputer yang ditentukan. Terdapat dua tipe sumber data mesin, pengguna dan sistem. Sumber data pengguna hanya dapat digunakan oleh pengguna saat ini dan hanya terlihat bagi pengguna tersebut. Sumber data sistem dapat digunakan oleh semua pengguna di komputer dan dapat dilihat oleh semua pengguna di komputer.

Sumber data mesin sangat berguna saat Anda ingin menyediakan keamanan tambahan, karena membantu memastikan bahwa hanya pengguna yang masuk yang bisa menampilkan sumber data mesin, dan sumber data mesin tidak bisa disalin oleh pengguna jarak jauh ke komputer lain.

Sumber data file

Sumber data file (juga disebut file DSN) menyimpan informasi koneksi dalam file teks, bukan registri, dan umumnya lebih fleksibel untuk digunakan daripada sumber data mesin. Misalnya, Anda bisa menyalin sumber data file ke komputer mana pun dengan driver ODBC yang benar, sehingga aplikasi Anda bisa mengandalkan informasi koneksi yang konsisten dan akurat ke semua komputer yang digunakannya. Atau Anda dapat menempatkan sumber data file di server tunggal, berbagi antar banyak komputer di jaringan, dan dengan mudah mempertahankan informasi koneksi di satu lokasi.

Sumber data file juga dapat berupa file yang tidak dapat dibagikan. Sumber data file yang tidak dapat diterima berada di satu komputer dan mengarah ke sumber data mesin. Anda dapat menggunakan sumber data file yang tidak dapat dibagikan untuk mengakses sumber data mesin yang sudah ada dari sumber data file.

Menggunakan OLE DB untuk menyambungkan ke sumber data

Dalam arsitektur OLE DB, aplikasi yang mengakses data disebut konsumen data (seperti Excel), dan program yang memungkinkan akses asli ke data disebut penyedia database (seperti Penyedia OLE DB Microsoft untuk SQL Server).

File Universal Data Link (.udl) berisi informasi koneksi yang digunakan konsumen data untuk mengakses sumber data melalui penyedia OLE DB sumber data tersebut. Anda bisa membuat informasi koneksi dengan melakukan salah satu hal berikut ini:

• Dalam Panduan Koneksi Data, gunakan kotak dialog Properti Link Data untuk menentukan link data untuk penyedia OLE DB. 

• Buat file teks kosong dengan ekstensi nama file .udl, lalu edit file, yang menampilkan kotak dialog Properti Link Data .