Mengelola rentang data eksternal dan propertinya

Anda dapat mengubah properti rentang data eksternal untuk mengontrol data yang telah diimpor ke dalam lembar kerja melalui koneksi ke sumber data eksternal, seperti database, kueri web, atau file teks.

Rentang data eksternal (juga disebut tabel kueri) adalah nama yang ditentukan atau nama tabel 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 — laporan PivotTable tidak menggunakan rentang data eksternal. Di Excel, Anda bisa memformat dan menata rentang data eksternal atau menggunakannya dalam penghitungan, seperti dengan data lainnya.

Properti rentang data eksternal

Rentang data eksternal memiliki properti tambahan (jangan dikelirukan dengan properti koneksi) yang bisa Anda gunakan untuk mengontrol data, seperti pelestarian pemformatan sel dan lebar kolom. Anda dapat mengubah properti rentang data eksternal ini dengan mengklik properti dalam grup koneksi pada tab data . Lalu buat perubahan Anda dengan melakukan hal berikut:

  • Untuk rentang data eksternal yang dibuat dari Microsoft query dan panduan koneksi data, gunakan kotak dialog properti data eksternal .

  • Untuk rentang data eksternal yang dibuat dari file teks atau kueri web yang diimpor yang mengambil data HTML, gunakan kotak dialog properti rentang data eksternal .

  • Untuk rentang data eksternal yang dibuat dari kueri web yang mengambil data XML, gunakan kotak dialog properti peta XML .

Menggunakan templat dengan rentang data eksternal

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

  1. Pilih lembar kerja yang ingin Anda Cari rentang data eksternal.

  2. Pada bilah rumus, klik panah di samping kotak nama, lalu klik nama rentang data eksternal yang Anda inginkan.

  1. Pada tab Rumus, dalam grup Nama yang Ditentukan, klik Manajer Nama.

    Grup Nama yang Ditentukan pada tab Rumus

  2. Dalam kotak dialog pengatur nama , klik nama rentang data eksternal, lalu klik Edit. Anda juga dapat mengklik ganda nama tersebut.

  3. Dalam kotak dialog Edit nama , ketikkan nama baru untuk referensi dalam kotak nama .

Catatan: Tombol tutup hanya menutup kotak dialog pengatur nama . Anda tidak harus mengklik tutup untuk melakukan perubahan yang telah dibuat.

Anda bisa mengubah kueri yang mendasari untuk rentang data eksternal yang dibuat dari Microsoft query, file teks yang diimpor, kueri web, atau panduan koneksi data.

  1. Klik sel dalam rentang data eksternal yang ingin Anda ubah kueri dasarnya.

  2. Pada tab data , di grup koneksi , klik properti.

    Gambar Pita Excel

  3. Dalam kotak dialog properti data eksternal , klik properti koneksi properti .

  4. Dalam kotak dialog properti koneksi , klik tab definisi , lalu klik Edit kueri.

Untuk informasi selengkapnya tentang menyusun dan mengedit kueri di Microsoft query, lihat Bantuan Microsoft query.

  1. Klik sel dalam rentang data eksternal yang ingin Anda ubah kueri dasarnya.

  2. Pada tab data , di grup koneksi , klik koneksi.

    Gambar Pita Excel

  3. Dalam kotak dialog koneksi buku kerja , klik properti.

  4. Dalam kotak dialog properti koneksi , klik tab definisi , lalu klik Edit kueri.

  5. Dalam kotak dialog impor file teks , klik impor.

  6. Buat perubahan pada file teks yang diimpor dalam panduan impor teks, lalu klik selesai.

    Untuk informasi selengkapnya tentang mengimpor file teks, lihat mengimpor atau mengekspor file teks.

  1. Klik sel dalam rentang data eksternal yang ingin Anda ubah kueri dasarnya.

  2. Pada tab data , di grup koneksi , klik properti.

    Gambar Pita Excel

  3. Dalam kotak dialog koneksi buku kerja , klik properti.

  4. Dalam kotak dialog properti koneksi , klik tab definisi , lalu klik Edit kueri.

  5. Buat perubahan pada kueri web dalam kotak dialog Edit kueri web , lalu klik selesai.

Untuk informasi selengkapnya tentang membuat dan mengedit kueri web, lihat menyambungkan ke halaman web.

  1. Klik sel dalam rentang data eksternal yang ingin Anda ubah kueri dasarnya.

  2. Pada tab data , di grup koneksi , klik properti.

    Gambar Pita Excel

  3. Dalam kotak dialog properti data eksternal , klik properti koneksi properti .

  4. Dalam kotak dialog properti koneksi , klik tab definisi .

  5. Lakukan salah satu dari hal berikut ini:

    • Dalam kotak tipe perintah , klik tabel lalu, dalam kotak teks perintah , Ubah nilai menjadi nama tabel, tampilan, atau kueri yang sesuai.

    • Dalam kotak tipe perintah , klik SQL atau default lalu, dalam kotak teks perintah , Edit pernyataan SQL.

      Catatan: Tergantung pada cara koneksi ditentukan, kotak tipe perintah mungkin tidak tersedia (tampak redup).

  1. Pada bilah rumus, klik panah di samping kotak nama, dan pilih nama rentang data eksternal yang ingin Anda salin.

    Untuk tabel Excel, pilih nama rentang, lalu tekan CTRL + A untuk memilih header tabel.

    Jika Anda ingin menyertakan label kolom atau rumus yang bukan bagian dari rentang data eksternal, pilih sel yang berisi label kolom atau rumus yang ingin Anda salin. Klik panah di samping kotak nama di bilah rumus, dan klik nama rentang data eksternal yang ingin Anda salin.

  2. Pada tab Beranda, di grup Clipboard, klik Salin.

    Grup Clipboard pada tab Beranda

  3. Beralih ke buku kerja tempat Anda ingin menempelkan rentang data eksternal.

  4. Klik sel kiri atas area tempel.

    Untuk memastikan bahwa data eksternal tidak menggantikan data yang sudah ada, pastikan bahwa lembar kerja tidak memiliki data di bawah atau di sebelah kanan sel yang Anda klik.

  5. Pada tab Beranda, dalam grup Clipboard, klik Tempel.

Catatan: Jika Anda hanya menyalin bagian dari rentang data eksternal, kueri yang mendasari tidak disalin, dan data yang disalin tidak bisa direfresh.

Anda dapat mengontrol cara menangani kumpulan data yang lebih kecil atau lebih besar yang dikembalikan ke Excel ketika data diperbarui.

  1. Pada tab data , di grup koneksi , klik properti.

    Gambar Pita Excel

  2. Dalam kotak dialog properti rentang data eksternal , di bawah jika jumlah baris dalam rentang data berubah setelah refresh, klik salah satu hal berikut ini:

    • Menyisipkan sel untuk data baru, menghapus sel yang tidak terpakai

      Catatan: 

      • Saat satu atau beberapa baris ditambahkan di sumber data, sel langsung di bawah rentang data eksternal berpindah ke bawah, tetapi sel di sebelah kanan rentang data eksternal tidak berpindah.

      • Saat satu atau beberapa baris dihapus di sumber data, sel langsung di bawah rentang data eksternal dipindah ke atas, tapi sel di sebelah kanan rentang data eksternal tidak berpindah.

    • Menyisipkan seluruh baris untuk data baru, menghapus sel yang tidak terpakai

      Catatan: 

      • Saat satu atau beberapa baris ditambahkan di sumber data, sel langsung di bawah dan di sebelah kanan rentang data eksternal menurun.

      • Saat satu atau beberapa baris dihapus di sumber data, sel langsung di bawah dan di sebelah kanan rentang data eksternal tidak berpindah.

    • Menimpa sel yang sudah ada dengan data baru, menghapus sel yang tidak terpakai

      Catatan: 

      • Saat satu atau beberapa baris ditambahkan di sumber data, sel langsung di bawah rentang data eksternal ditimpa, tapi sel di sebelah kanan rentang data eksternal tidak berpindah.

      • Saat satu atau beberapa baris dihapus di sumber data, sel langsung di bawah dan di sebelah kanan rentang data eksternal tidak berpindah.

  1. Pada tab data , di grup koneksi , klik properti.

    Gambar Pita Excel

  2. Di bawah pemformatan dan tata letak data, lakukan satu atau beberapa hal berikut ini:

    • Untuk menyertakan nama bidang sebagai baris pertama, pilih kotak centang Sertakan nama bidang .

      Catatan: Kotak centang ini hanya tersedia untuk rentang data eksternal yang dikonversi dari program Microsoft Office 2003, atau ke rentang data eksternal yang dibuat secara programatik.

    • Untuk menambahkan kolom nomor baris, pilih kotak centang sertakan nomor baris .

      Catatan: Kotak centang ini tidak tersedia untuk file teks yang diimpor, file XML, atau kueri web.

    • Untuk mempertahankan pemformatan sel yang Anda terapkan, pilih kotak centang Pertahankan pemformatan sel .

      Catatan: Untuk kueri web, opsi ini secara otomatis dihapus saat Anda memilih PEMFORMATAN HTML penuh dalam kotak dialog opsi . Anda mengakses kotak dialog opsi dari kotak dialog Edit kueri .

    • Untuk mempertahankan lebar kolom yang Anda atur, pilih kotak centang Sesuaikan lebar kolom .

    • Untuk mempertahankan pengurutan, pemfilteran, dan tata letak kolom yang Anda terapkan, pilih kotak centang Pertahankan/Urutkan kolom/tata letak .

      Catatan: Kotak centang ini tidak tersedia untuk kueri web.

    • Untuk mempertahankan pemfilteran kolom yang Anda terapkan, pilih kotak centang Pertahankan filter kolom .

      Catatan: Kotak centang ini hanya tersedia untuk kueri web yang didasarkan pada data XML.

Catatan: Prosedur berikut ini hanya berlaku untuk rentang data eksternal yang dikonversi dari program Microsoft Office 2003, atau ke rentang data eksternal yang dibuat secara programatik. Semua rentang data eksternal yang dibuat dengan menggunakan antarmuka pengguna dibuat sebagai tabel Excel. Tabel ini secara otomatis diperluas saat baris baru ditambahkan dan mengisi kolom terhitung.

  1. Masukkan rumus dalam sel yang berdekatan dengan baris pertama data dalam rentang data eksternal.

    Catatan: Baris pertama data mungkin baris pertama atau kedua dalam rentang data eksternal, tergantung pada apakah baris pertama berisi header.

  2. Pilih sel dan klik ganda gagang isian untuk menyalin rumus ke semua baris dalam rentang data eksternal.

  3. Klik sel dalam rentang data eksternal.

  4. Pada tab data , di grup koneksi , klik properti.

    Gambar Pita Excel

  5. Dalam kotak dialog properti rentang data eksternal , pilih kotak centang isi rumus isian ke bawah dalam kolom yang berdekatan dengan data .

    Catatan: Jika rentang data eksternal meluas saat Anda refresh, Excel hanya menyalin rumus yang berbatasan langsung dengan atau dalam rentang data eksternal.

Membekukan rentang data eksternal mempertahankan data tetapi bukan kueri yang mendasari, sehingga rentang data eksternal beku tidak bisa direfresh.

  1. Klik lembar kerja yang berisi tabel Excel dari mana Anda ingin menghapus koneksi data.

  2. Pada bilah rumus, klik panah di samping kotak nama, lalu klik nama rentang data eksternal yang ingin Anda hapus koneksi datanya. Rentang data kemudian dipilih.

  3. Pada tab alat , dalam grup data tabel eksternal , klik Batalkan tautan. Rentang data tetap ada dan masih menyandang nama yang sama, tetapi koneksi dihapus.

Catatan: Prosedur berikut ini hanya berlaku untuk rentang data eksternal yang dikonversi dari program Microsoft Office 2003, atau ke rentang data eksternal yang dibuat secara programatik. Semua rentang data eksternal yang dibuat dengan menggunakan antarmuka pengguna dibuat sebagai tabel Excel. Tabel ini secara otomatis diperluas saat baris baru ditambahkan dan mengisi kolom terhitung.

  1. Pada tab data , di grup koneksi , klik properti.

    Gambar Pita Excel

  2. Di bawah definisi kueri, kosongkan kotak centang Simpan definisi kueri .

Catatan:  Saat Anda menyimpan buku kerja Anda, kueri yang mendasari dihapus dari buku kerja. Namun, file kueri database yang disimpan (file. dqy atau. ODC) tidak dihapus. Jika Anda menyimpan kueri saat Anda membuatnya dalam panduan kueri atau di Microsoft query, file kueri disimpan di komputer Anda, dan Anda bisa menggunakan kueri lagi untuk mengambil data eksternal. Untuk informasi selengkapnya, lihat Bantuan Microsoft query.

Catatan: Prosedur berikut ini hanya berlaku untuk rentang data eksternal yang dibuat dengan menggunakan panduan koneksi data atau Microsoft query (dan bukan ke file teks atau kueri web yang diimpor), rentang data eksternal yang dikonversi dari program Microsoft Office 2003, atau eksternal Rentang data yang dibuat secara programatik. Semua rentang data eksternal yang dibuat dengan menggunakan antarmuka pengguna dibuat sebagai tabel Excel.

  1. Sisipkan baris kosong di atas rentang data eksternal dengan melakukan salah satu hal berikut:

    • Pilih sel di atas yang ingin Anda sisipkan baris baru. Lalu, pada tab Beranda , dalam grup sel , klik panah di samping sisipkan, lalu klik sisipkan baris lembar. Karena Anda telah memilih satu sel, satu baris baru disisipkan di atas baris saat ini.

    • Klik pemilih baris baris di atas yang ingin Anda sisipkan baris baru. Lalu, pada tab Beranda , dalam grup sel , klik sisipkan. Karena Anda telah memilih satu seluruh baris, satu baris baru disisipkan di atas baris yang dipilih.

  2. Ketikkan label yang Anda inginkan dalam sel di dalam baris kosong.

  3. Klik sel dalam rentang data eksternal.

  4. Pada tab data , di grup koneksi , klik properti.

    Gambar Pita Excel

  5. Dalam kotak dialog properti rentang data eksternal , di bawah pemformatan dan tata letak data, kosongkan kotak centang Sertakan nama bidang , lalu klik OK.

  6. Untuk menghapus nama bidang yang sudah ada dan me-refresh rentang data eksternal, klik sel dalam rentang data eksternal, lalu klik refresh data Gambar tombol .

Catatan: Saat Anda mengambil data dari database, perubahan apa pun pada nama kolom di Microsoft query dipertahankan dalam rentang data eksternal. Untuk informasi tentang mengubah nama kolom dalam kueri, lihat Bantuan Microsoft query.

  1. Klik lembar kerja yang berisi rentang data eksternal yang ingin Anda hapus.

  2. Pada bilah rumus, klik panah di samping kotak nama, lalu klik nama rentang data eksternal yang ingin Anda hapus.

    Jika rentang data eksternal adalah tabel Excel, tekan CTRL + A untuk memilih seluruh tabel.

  3. Untuk menghapus rentang data eksternal, tekan DELETE.

  4. Untuk menghapus kueri yang mendasari, klik ya saat Excel meminta Anda.

Perlu bantuan lainnya?

Anda dapat bertanya kapan saja kepada para ahli di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Answers, atau menyarankan peningkatan maupun fitur baru di User Voice Excel.

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×