Mengelola rentang data eksternal dan propertinya

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.

   

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 .

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. 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.

   

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.

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.

   

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.

   

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.

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.

   

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. 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.

   

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 .

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.