Rumus Excel: Rentang bernama dinamis dengan OFFSET -

Formula umum

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Ringkasan

Salah satu cara untuk membuat rentang bernama dinamis dengan rumus adalah dengan menggunakan fungsi OFFSET bersama dengan fungsi COUNTA. Rentang dinamis juga dikenal sebagai rentang perluasan - rentang dinamis meluas dan menyusut untuk mengakomodasi data baru atau yang dihapus.

Catatan: OFFSET adalah fungsi yang mudah menguap, yang berarti ia menghitung ulang dengan setiap perubahan pada lembar kerja. Dengan mesin modern dan kumpulan data yang lebih kecil, ini seharusnya tidak menimbulkan masalah tetapi Anda mungkin melihat kinerja yang lebih lambat pada kumpulan data yang besar. Dalam kasus tersebut, pertimbangkan untuk membuat rentang bernama dinamis dengan fungsi INDEX sebagai gantinya.

Dalam contoh yang ditampilkan, rumus yang digunakan untuk rentang dinamis adalah:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Penjelasan

Rumus ini menggunakan fungsi OFFSET untuk menghasilkan rentang yang meluas dan menyusut dengan menyesuaikan tinggi dan lebar berdasarkan jumlah sel yang tidak kosong.

Argumen pertama di OFFSET mewakili sel pertama dalam data (asal), yang dalam hal ini adalah sel B5. Dua argumen berikutnya adalah offset untuk baris dan kolom, dan diberikan sebagai nol.

Dua argumen terakhir mewakili tinggi dan lebar. Tinggi dan lebar dibuat dengan cepat menggunakan COUNTA, yang membuat referensi yang dihasilkan menjadi dinamis.

Untuk tinggi, kami menggunakan fungsi COUNTA untuk menghitung nilai yang tidak kosong di kisaran B5: B100. Ini mengasumsikan tidak ada nilai kosong dalam data, dan tidak ada nilai di luar B100. COUNTA mengembalikan 6.

Untuk lebar, kami menggunakan fungsi COUNTA untuk menghitung nilai yang tidak kosong di kisaran B5: Z5. Ini mengasumsikan tidak ada sel header, dan tidak ada header di luar Z5. COUNTA mengembalikan 6.

Pada titik ini, rumusnya terlihat seperti ini:

=OFFSET(B5,0,0,6,6)

Dengan informasi ini, OFFSET mengembalikan referensi ke B5: G10, yang sesuai dengan rentang 6 baris dengan tinggi 6 kolom.

Catatan: Rentang yang digunakan untuk tinggi dan lebar harus disesuaikan agar sesuai dengan tata letak lembar kerja.

Variasi dengan referensi kolom / baris penuh

Anda juga dapat menggunakan referensi kolom dan baris lengkap untuk tinggi dan lebar seperti:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Perhatikan bahwa tinggi disesuaikan dengan -2 untuk memperhitungkan nilai header dan judul di sel B4 dan B2. Keuntungan dari pendekatan ini adalah kesederhanaan rentang di dalam COUNTA. Kerugiannya berasal dari kolom dan baris penuh ukuran besar - kehati-hatian harus dilakukan untuk mencegah nilai yang salah di luar rentang, karena dapat dengan mudah membuang hitungan.

Menentukan baris terakhir

Ada beberapa cara untuk menentukan baris terakhir (posisi relatif terakhir) dalam sekumpulan data, bergantung pada struktur dan konten data di lembar kerja: