Blog

Rentang dinamis di Excel: kapan memakai OFFSET atau tabel

Perbarui total dan pilihan tarik turun saat baris ditambahkan. Pelajari OFFSET dan COUNTA, dampak ID kosong, serta cara menggunakan tabel Excel.

Rentang yang disorot meluas ke bawah saat satu baris ditambahkan.

Anda menambah produk dalam daftar Excel, tetapi total dan pilihan tarik turun tetap sama. Jika rujukannya masih A2:A10, baris ke-11 berada di luar rentang. Rentang dinamis memungkinkan Excel menentukan akhir daftar tanpa perlu mengedit rumus setiap kali data berubah.

Menghitung tinggi dan lebar dengan OFFSET

Jika data berurutan tanpa celah, hitung jumlah judul kolom dan ID produk sebagai tinggi dan lebar OFFSET. Buat contoh di Sheet1 dan masukkan rumus penjumlahan di lembar lain. Pakai judul kolom berbahasa Jepang persis seperti contoh agar referensi terstruktur di bawah berfungsi.

Sel Kolom A Kolom B Kolom C
Baris 1 商品ID (ID produk) 商品名 (nama produk) 数量 (jumlah)
Baris 2 A01 Pena 4
Baris 3 A02 Buku catatan 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

Mulai dari A1 tanpa pergeseran baris atau kolom. Jumlah sel berisi data di kolom A memberi tinggi, dan jumlah judul di baris 1 memberi lebar. Contoh menghasilkan A1:C3 dengan tiga baris, tiga kolom, dan total 10. Tambahkan A03, Penggaris, dan 2 di baris 4 agar rentang menjadi A1:C4 dan total 12. ID produk berupa teks, dan rentang yang dihitung bebas dari data lain. Referensi OFFSET dari Microsoft

Hapus hanya ID pada A3. Tiga sel di kolom A akan terhitung: judul A1 dan ID di A2 serta A4. Karena itu, OFFSET mengembalikan tiga baris pertama, A1:C3. Jumlah 6 di baris 3 masih dihitung, tetapi jumlah 2 di baris 4 tidak. Totalnya kembali menjadi 10.

A3 kosong. OFFSET mencakup A1:C3 dan menghasilkan 10, termasuk 6 di baris 3 dan tanpa 2 di baris 4.
Garis hijau menandai rentang A1:C3 yang dikembalikan OFFSET di Excel untuk Windows.

Judul kosong di tengah juga dapat membuat kolom terakhir keluar dari rentang. COUNTA menghitung sel dengan rumus yang menghasilkan string kosong (""). Dalam buku kerja lama, isi judul dan ID tanpa celah. Periksa batas hitung dan sel kosong. Sel jumlah yang kosong tidak mengubah tinggi jika ID tetap diisi. Referensi COUNTA dari Microsoft

Jika judul dimulai di baris kedua

Jika baris pertama berisi judul laporan, mulai daftar di A2. Hitung tinggi dan lebar mulai dari baris 2 agar judul laporan tidak masuk. Jika contoh digeser satu baris ke bawah, rentangnya menjadi A2:C4 untuk judul kolom dan dua produk. Totalnya tetap 10.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

Memakai tabel yang meluas ketika baris ditambah

Untuk daftar baru, pilih rentang termasuk judul kolom, lalu pilih Sisipkan → Tabel. Beri nama Items pada tabel. Rumus berikut menjumlahkan kolom jumlah dari luar tabel. Rentang penjumlahan ikut meluas saat baris ditambahkan. Panduan referensi terstruktur dari Microsoft

=SUM(Items[数量])

Menjumlahkan hanya baris yang memiliki ID produk

Jika A1:C4 dijadikan tabel Items, meski A3 kosong rumus di atas menjumlahkan 4 + 6 + 2 dan menghasilkan 12. Untuk menghitung hanya baris yang memiliki ID, gunakan SUMIF dengan syarat kolom ID tidak kosong. Hasil contoh ini 6 dari 4 + 2 di baris 2 dan 4. Referensi SUMIF dari Microsoft

=SUMIF(Items[商品ID],"<>",Items[数量])

Menyinkronkan pilihan tarik turun dengan tabel

Untuk pilihan nama produk di lembar lain, buka Rumus → Pengelola Nama di Excel desktop. Buat ItemChoices dengan cakupan buku kerja dan isi Merujuk ke dengan =Items[商品名]. Untuk sel masukan, pilih Daftar pada Validasi Data dan isi Sumber dengan =ItemChoices. Judul kolom tidak ikut, dan baris baru di akhir tabel akan menambah pilihan. Isi nama di setiap baris baru agar tidak ada pilihan kosong. Panduan membuat daftar tarik turun dari Microsoft