Bagaimana perjalanan data melalui Excel

Berikut adalah beberapa hal utama yang dapat Anda lakukan saat data berada di stasiun kereta Excel:

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

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

• Keamanan    Privasi data, kredensial, dan autentikasi selalu menjadi perhatian berkelanjutan. Untuk informasi selengkapnya, lihat Mengelola pengaturan dan izin sumber data dan 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.

• Koneksi/Properti    Setiap sumber data eksternal memiliki koneksi yang diurutkan dan informasi properti yang terkait dengannya kadang memerlukan perubahan bergantung 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 pada stasiun kereta api Excel yang sibuk ini.

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

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

Kotak Dialog atau Panel Jalur Perintah	Tab dan tabel	Topik Bantuan Utama
Sumber terbaru Data > Terbaru	(Tidak ada tab) Kotak dialog Navigator> Terhubung	Mengelola pengaturan dan izin sumber data
Properti Koneksi PANDUAN Koneksi Data OR Data > Kueri & koneksi > tab koneksi > (klik kanan koneksi) > Properti	Tab Definisi tab Penggunaan yang Digunakan Di tab	Properti koneksi
Properti Kueri Data> Koneksi yang sudah > (klik kanan koneksi) > Edit Properti Koneksi OR Data> Kueri & Koneksidengan | Tab Kueri > (klik kanan koneksi) > Properti OR Query> Properties OR Data> Refresh Semua> Koneksi (saat diposisikan pada lembar kerja kueri yang dimuat)	Tab Definisi tab Penggunaan yang Digunakan Di tab	Properti koneksi
Kueri & Koneksi Data > Kueri & Koneksi	Tab Koneksi tab Kueri	Properti koneksi
Koneksi yang Sudah Ada Data > Yang Sudah Ada	Tab Koneksi tab Tabel	Menyambungkan ke data eksternal
Properti data eksternal ATAU Properti rentang data eksternal OR Data> Properti (Dinonaktifkan jika tidak ditempatkan di lembar kerja kueri)	Digunakan di tab (dari kotak dialog Properti Koneksi)   Tombol Refresh di sebelah kanan tabel ke Properti Kueri	Mengelola rentang data eksternal dan propertinya
Tab Properti Koneksi > Definisi > Mengekspor File Koneksi KUERI OR> Mengekspor File Koneksi	(Tidak ada tab) Folder sumber data kotak dialog Kontak dengan File	Membuat, mengedit, dan mengelola koneksi ke data eksternal

Data dalam buku kerja Excel bisa berasal dari dua lokasi yang 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, Online Analytical Processing). Sumber data eksternal ini tersambung ke buku kerja melalui koneksi data, yang merupakan kumpulan informasi yang menjelaskan cara menemukan, masuk, dan mengakses sumber data eksternal.

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

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

Untuk membawa data eksternal ke Excel, Anda memerlukan akses ke data tersebut. Jika sumber data eksternal yang ingin Anda akses tidak berada 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 merupakan 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 mengoordinasikan aliran data antara Excel, file koneksi, dan sumber data.

Diagram berikut ini meringkas poin-poin penting tentang koneksi data.

1. Ada berbagai sumber data yang bisa Anda sambungkan ke: Analysis Services, SQL Server, Microsoft Access, database OLAP dan database relasional, lembar bentang, dan file teks.

2. Banyak sumber data memiliki terkait 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 dapat dengan mudah diedit.

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

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

• Koneksi dalam buku kerja    

  Daftar ini menampilkan semua koneksi saat ini dalam buku kerja. Daftar dibuat dari koneksi yang sudah Anda tetapkan, yang Anda buat dengan 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 pada komputer Anda    

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

• File koneksi pada jaringan    

  Daftar ini dapat dibuat dari kumpulan folder di jaringan lokal Anda, lokasi yang dapat digunakan di seluruh jaringan sebagai bagian dari penggunaan 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 di buku kerja atau di file koneksi. Jika tidak menemukan koneksi yang diinginkan, Anda dapat 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 & Koneksi >tab Koneksi > (klik kanan koneksi) > Properti) untuk mengontrol berbagai pengaturan untuk koneksi ke sumber data eksternal, dan untuk menggunakan, menggunakan kembali, atau mengalihkan file koneksi.

Catatan    Terkadang, kotak dialog Properti Koneksi dinamai kotak dialog Properti Kueri saat ada kueri yang dibuat di Power Query (sebelumnya 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 membuat perubahan dengan menggunakan kotak dialog Properti Koneksi, Anda sedang 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 di properti File Koneksi pada tab Definisi). Setelah Anda mengedit informasi koneksi (dengan pengecualian properti Nama Koneksi dan Deskripsi Koneksi), link ke file koneksi dihapus dan properti File Koneksi dihapus.

Untuk memastikan bahwa file koneksi selalu digunakan saat sumber data di-refresh, klik Selalu coba gunakan file ini untuk melakukan refresh data ini pada tab Definisi. Memilih kotak centang ini memastikan bahwa pembaruan pada 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 Koneksi, Anda bisa dengan mudah mengelola koneksi ini, termasuk membuat, mengedit, dan menghapusnya (Pilih Data > Kueri & Connections > Connections > (klik kanan koneksi) > Properti.) Anda bisa menggunakan kotak dialog ini untuk melakukan hal berikut: 

• Buat, edit, refresh, dan hapus koneksi yang sedang digunakan dalam buku kerja.

• Verifikasi sumber data eksternal. Anda mungkin ingin melakukan hal ini jika koneksi telah ditetapkan 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 pembuatan dan berbagi file koneksi dengan pengguna.

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

Menggunakan file ODC

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

Anda bisa berbagi file koneksi dengan orang lain untuk memberi mereka akses yang sama dengan 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 merupakan metode yang direkomendasikan untuk menyambungkan ke data dan berbagi data. Anda dapat dengan mudah mengonversi file koneksi tradisional (DSN, UDL, dan file kueri) menjadi file ODC dengan membuka file koneksi, lalu mengklik tombol Ekspor File Koneksi di 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 membuat sumber data. File kueri merupakan 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 yang relasional atau file teks. Saat membuka file ini di Microsoft Query, Anda dapat menampilkan data yang dikembalikan oleh kueri dan mengubah kueri untuk mengambil hasil yang berbeda. Anda bisa menyimpan file .dqy untuk setiap kueri yang Anda buat, baik dengan menggunakan Panduan Kueri atau langsung di Kueri Microsoft.

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

Menggunakan file kueri .rqy    Excel bisa 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 bisa membuka dan menyimpan file kueri dalam format .qry untuk digunakan dengan versi Microsoft Query yang lebih lama yang tidak bisa 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 Kueri Microsoft.

Menggunakan file kueri Web .iqy    Excel dapat membuka file kueri Web .iqy 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 adalah laporan PivotTable yang tersambung ke sumber data, yang tidak membuat rentang data eksternal. Di Excel, Anda dapat memformat dan meletakkan 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 Koneksi Data Office (ODC, Office Data Connection) diberi nama yang sama dengan nama file.

• Rentang data eksternal dari database diberi nama dengan nama kueri. Secara default Query_from_sumber 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 diberi nama dengan nama halaman Web tempat data diambil.

Jika lembar kerja Anda memiliki lebih dari satu rentang data eksternal dari sumber yang sama, rentang dinomori. Misalnya, MyText, MyText_1, MyText_2, dan lain-lain.

Rentang data eksternal memiliki properti tambahan (tidak sama dengan properti koneksi) yang bisa Anda gunakan untuk mengontrol data, seperti mempertahankan pemformatan sel dan lebar kolom. Anda bisa mengubah properti rentang data eksternal ini dengan mengklik Properti dalam grup Koneksi pada tab Data, lalu membuat perubahan Anda di 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 berbeda. Namun, tipe sumber data yang bisa Anda sambungkan berbeda antara tiap objek data.

Anda bisa menggunakan dan melakukan refresh 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 Pengaturan Autentikasi Layanan Excel.

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

Excel data objek	Membuat Eksternal data rentang?		OLE DB	ODBC	Teks file	HTML file	XML file	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
Kueri Microsoft	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 melakukan refresh 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 dapat 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 menuju ke jalur memori database.

Tentang MDAC, OLE DB, dan OBC

Pertama, mohon maaf atas semua akronim. Microsoft Data Access Components (MDAC) 2.8 disertakan dengan Microsoft Windows. Dengan MDAC, Anda bisa menyambungkan dan menggunakan data dari berbagai sumber data relasional dan non-bisnis. Anda bisa menyambungkan ke berbagai sumber data dengan menggunakan driver Konektivitas Database Terbuka (ODBC, Open Database Connectivity) atau OLE DB , yang dibuat dan dikirimkan 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 bisa 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 penyedia OLE DB ini, 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 akhirnya menggunakan driver ODBC tertentu (seperti driver Microsoft SQL ODBC) untuk menyambungkan ke sumber data (seperti database Microsoft SQL Server).

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) menggunakan Administrator Sumber Data ODBC untuk menyimpan informasi koneksi dalam registri atau file DSN, atau string sambungan dalam kode Microsoft Visual Basic untuk menyampaikan informasi koneksi secara langsung ke Manajer Driver ODBC.

   Untuk menetapkan 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 berbagai opsi, klik tombol Bantuan dalam 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. Ada 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 terlihat oleh semua pengguna di komputer.

Sumber data mesin sangat berguna saat Anda ingin menyediakan keamanan tambahan, karena ini membantu memastikan 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 di file teks, bukan registri, dan secara umum lebih fleksibel untuk digunakan daripada sumber data mesin. Misalnya, Anda dapat menyalin sumber data file ke komputer mana pun dengan driver ODBC yang sesuai, sehingga aplikasi dapat mengandalkan informasi koneksi yang konsisten dan akurat ke semua komputer yang menggunakannya. 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 menjadi tidak dapat dibagikan. Sumber data file yang tidak dapat dibagikan berada di komputer tunggal dan menunjuk 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 Microsoft OLE DB untuk SQL Server).

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

• Di 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 Tautan Data.