Formula Excel: Julat dinamik dengan OFFSET -

Formula generik

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

Ringkasan

Salah satu cara untuk membuat julat dinamik dengan formula adalah dengan menggunakan fungsi OFFSET bersama dengan fungsi COUNTA. Julat dinamik juga dikenali sebagai julat pengembangan - ia secara automatik berkembang dan berkontrak untuk menampung data baru atau dihapus.

Catatan: OFFSET adalah fungsi tidak stabil, yang bermaksud ia dikira semula dengan setiap perubahan pada lembaran kerja. Dengan mesin moden dan set data yang lebih kecil, ini tidak akan menimbulkan masalah tetapi anda mungkin melihat prestasi lebih perlahan pada set data yang besar. Sekiranya demikian, pertimbangkan untuk membina julat dinamik dengan fungsi INDEX.

Dalam contoh yang ditunjukkan, formula yang digunakan untuk julat dinamik adalah:

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

Penjelasan

Rumus ini menggunakan fungsi OFFSET untuk menghasilkan julat yang mengembang dan berkontrak dengan menyesuaikan ketinggian dan lebar berdasarkan jumlah sel yang tidak kosong.

Argumen pertama di OFFSET mewakili sel pertama dalam data (asal), yang dalam kes ini adalah sel B5. Dua argumen seterusnya adalah ofset untuk baris dan lajur, dan diberikan sebagai sifar.

Dua argumen terakhir mewakili ketinggian dan lebar. Tinggi dan lebar dihasilkan dengan cepat menggunakan COUNTA, yang menjadikan rujukan yang dihasilkan dinamik.

Untuk ketinggian, kami menggunakan fungsi COUNTA untuk mengira nilai yang tidak kosong dalam julat B5: B100. Ini tidak menganggap nilai kosong dalam data, dan tidak ada nilai melebihi B100. COUNTA kembali 6.

Untuk lebar, kami menggunakan fungsi COUNTA untuk mengira nilai yang tidak kosong dalam julat B5: Z5. Ini tidak menganggap sel header, dan tidak ada header melebihi Z5. COUNTA kembali 6.

Pada ketika ini, formula seperti ini:

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

Dengan maklumat ini, OFFSET mengembalikan rujukan ke B5: G10, yang sesuai dengan jarak tinggi 6 baris dengan 6 lajur.

Catatan: Julat yang digunakan untuk ketinggian dan lebar harus disesuaikan agar sesuai dengan susun atur lembaran kerja.

Variasi dengan rujukan lajur / baris penuh

Anda juga boleh menggunakan rujukan lajur dan baris penuh untuk ketinggian dan lebar seperti:

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

Perhatikan bahawa ketinggian diselaraskan dengan -2 untuk mempertimbangkan nilai tajuk dan tajuk di sel B4 dan B2. Kelebihan pendekatan ini adalah kesederhanaan rentang di COUNTA. Kekurangannya berasal dari lajur dan baris penuh berukuran besar - penjagaan mesti diambil untuk mengelakkan nilai yang salah di luar jangkauan, kerana mereka dapat membuang jumlahnya dengan mudah.

Menentukan baris terakhir

Terdapat beberapa cara untuk menentukan baris terakhir (posisi relatif terakhir) dalam satu set data, bergantung pada struktur dan kandungan data dalam lembaran kerja: