Formula generik
=$A$1:INDEX($A:$A,lastrow)
Ringkasan
Salah satu cara untuk membuat julat dinamik dalam Excel adalah dengan menggunakan fungsi INDEX. Dalam contoh yang ditunjukkan, julat bernama "data" ditentukan oleh formula berikut:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
yang mencapai julat $ A $ 2: $ A $ 10.
Catatan: formula ini dimaksudkan untuk menentukan julat bernama yang dapat digunakan dalam formula lain.
Penjelasan
Halaman ini menunjukkan contoh julat dinamik yang dibuat dengan fungsi INDEX bersama dengan fungsi COUNTA. Julat dinamik berkembang secara automatik dan berkontrak apabila data ditambahkan atau dikeluarkan. Mereka adalah alternatif untuk menggunakan Jadual Excel, yang juga ukurannya kerana data ditambahkan atau dikeluarkan.
Fungsi INDEX mengembalikan nilai pada kedudukan tertentu dalam julat atau array. Anda boleh menggunakan INDEX untuk mendapatkan nilai individu atau keseluruhan baris dan lajur dalam julat. Apa yang menjadikan INDEX sangat berguna untuk julat dinamik ialah ia benar-benar mengembalikan rujukan. Ini bermakna anda boleh menggunakan INDEX untuk membina rujukan campuran seperti $ A $ 1: A100.
Dalam contoh yang ditunjukkan, julat bernama "data" ditentukan oleh formula berikut:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
yang mencapai julat $ A $ 2: $ A $ 10.
Bagaimana formula ini berfungsi
Perhatikan terlebih dahulu bahawa formula ini terdiri dalam dua bahagian yang terletak di kedua-dua sisi operator jarak jauh (:). Di sebelah kiri, kami mempunyai rujukan permulaan untuk julat, dengan kod keras seperti:
$A$2
Di sebelah kanan adalah rujukan akhir untuk julat, dibuat dengan INDEX seperti ini:
INDEX($A:$A,COUNTA($A:$A))
Di sini, kami memberi makan INDEX semua lajur A untuk array, kemudian gunakan fungsi COUNTA untuk mengetahui "baris terakhir" dalam julat. COUNTA berfungsi dengan baik di sini kerana terdapat 10 nilai di lajur A, termasuk baris tajuk. Oleh itu, COUNTA mengembalikan 10, yang masuk terus ke INDEX sebagai nombor baris. INDEX kemudian mengembalikan rujukan ke $ A $ 10, baris terakhir yang digunakan dalam julat:
INDEX($A:$A,10) // resolves to $A$10
Jadi, hasil akhir formula adalah julat ini:
$A$2:$A$10
Julat dua dimensi
Contoh di atas berfungsi untuk julat satu dimensi. Untuk membuat julat dinamik dua dimensi di mana bilangan lajur juga dinamik, anda boleh menggunakan pendekatan yang sama, diperluas seperti ini:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Seperti sebelumnya, COUNTA digunakan untuk mengetahui "lastrow", dan kita menggunakan COUNTA sekali lagi untuk mendapatkan "lastcolumn". Ini dibekalkan ke indeks sebagai baris_num dan kolum_num masing-masing.
Namun, untuk array, kami menyediakan lembaran kerja penuh, dimasukkan sebagai semua 1048576 baris, yang membolehkan INDEX mengembalikan rujukan di ruang 2D.
Catatan: Excel 2003 hanya menyokong 65535 baris.
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:
- Baris terakhir dalam data campuran dengan kosong
- Baris terakhir dalam data campuran tanpa kekosongan
- Baris terakhir dalam data teks
- Baris terakhir dalam data berangka
