Formula umum
=$A$1:INDEX($A:$A,lastrow)
Ringkasan
Salah satu cara untuk membuat rentang bernama dinamis di Excel adalah dengan menggunakan fungsi INDEX. Dalam contoh yang ditampilkan, rentang bernama "data" ditentukan oleh rumus berikut:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
yang ditetapkan ke kisaran $ A $ 2: $ A $ 10.
Catatan: rumus ini dimaksudkan untuk menentukan rentang bernama yang bisa digunakan dalam rumus lain.
Penjelasan
Halaman ini menunjukkan contoh rentang bernama dinamis yang dibuat dengan fungsi INDEX bersama dengan fungsi COUNTA. Rentang bernama dinamis secara otomatis meluas dan menyusut saat data ditambahkan atau dihapus. Mereka adalah alternatif untuk menggunakan Tabel Excel, yang juga berubah ukuran saat data ditambahkan atau dihapus.
Fungsi INDEX mengembalikan nilai pada posisi tertentu dalam rentang atau larik. Anda dapat menggunakan INDEX untuk mengambil nilai individual atau seluruh baris dan kolom dalam suatu rentang. Apa yang membuat INDEX sangat berguna untuk rentang bernama dinamis adalah ia sebenarnya mengembalikan referensi. Ini berarti Anda dapat menggunakan INDEX untuk membuat referensi campuran seperti $ A $ 1: A100.
Dalam contoh yang ditampilkan, rentang bernama "data" ditentukan oleh rumus berikut:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
yang ditetapkan ke kisaran $ A $ 2: $ A $ 10.
Bagaimana rumus ini bekerja
Perhatikan pertama bahwa rumus ini terdiri dalam dua bagian yang berada di kedua sisi operator rentang (:). Di sebelah kiri, kami memiliki referensi awal untuk rentang tersebut, dengan kode keras:
$A$2
Di sebelah kanan adalah referensi akhir untuk rentang tersebut, dibuat dengan INDEX seperti ini:
INDEX($A:$A,COUNTA($A:$A))
Di sini, kami memberi makan INDEX semua kolom A untuk larik, lalu menggunakan fungsi COUNTA untuk mencari tahu "baris terakhir" dalam rentang tersebut. COUNTA berfungsi dengan baik di sini karena ada 10 nilai di kolom A, termasuk baris tajuk. Oleh karena itu, COUNTA mengembalikan 10, yang langsung masuk ke INDEX sebagai nomor baris. INDEX lalu mengembalikan referensi ke $ A $ 10, baris terakhir yang digunakan dalam kisaran:
INDEX($A:$A,10) // resolves to $A$10
Jadi, hasil akhir rumusnya adalah kisaran ini:
$A$2:$A$10
Rentang dua dimensi
Contoh di atas berfungsi untuk rentang satu dimensi. Untuk membuat rentang dinamis dua dimensi yang jumlah kolomnya juga dinamis, Anda dapat menggunakan pendekatan yang sama, diperluas seperti ini:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Seperti sebelumnya, COUNTA digunakan untuk mencari "baris terakhir", dan kami menggunakan COUNTA lagi untuk mendapatkan "kolom terakhir". Ini diberikan ke indeks sebagai row_num dan column_num.
Namun, untuk larik, kami menyediakan lembar kerja lengkap, yang dimasukkan sebagai semua 1048576 baris, yang memungkinkan INDEX mengembalikan referensi dalam ruang 2D.
Catatan: Excel 2003 hanya mendukung 65535 baris.
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:
- Baris terakhir dalam data campuran dengan kosong
- Baris terakhir dalam data campuran tanpa ada yang kosong
- Baris terakhir dalam data teks
- Baris terakhir dalam data numerik
