Excel Dinamik Dinamakan Julat: Cara Membuat dan Menggunakan

Tutorial ini menunjukkan kepada anda bagaimana untuk membuat dan menggunakan julat dinamik dinamik dalam Excel, secara automatik mengemaskini pengiraan apabila data anda berubah. Tidak seperti julat bernama statik, julat dinamik menyesuaikan diri untuk memasukkan data baru tanpa campur tangan manual.

Tutorial minggu lepas dilindungi statik bernama Ranges. Jika data anda kerap berubah, julat dinamakan dinamik adalah penting untuk pengiraan yang cekap dan automatik. Tutorial ini menyediakan arahan langkah demi langkah.

Topik utama:

• Membuat julat bernama dinamik satu dimensi
• Membuat julat bernama dinamik dua dimensi
• Menggunakan julat dinamakan dinamik dalam formula

Membuat julat bernama dinamik di Excel

Mari kita mulakan dengan julat bernama dinamik yang dinamik dan berubah-ubah:

1. Navigasi ke tab Formula , maka kumpulan nama yang ditetapkan , dan pilih Nama Define . Sebagai alternatif, tekan Ctrl F3 untuk membuka Pengurus Nama dan klik Baru ....
2. Kotak dialog nama baru muncul. Masukkan:
   • Nama untuk julat anda (misalnya, "item").
   • Pilih skop (biasanya "Buku Kerja").
   • Dalam merujuk kepada kotak, gunakan sama ada formula OFFSET atau INDEX (dijelaskan di bawah).
3. Klik OK.

Imej di bawah menunjukkan menentukan julat dinamik bernama "item" yang merangkumi semua data dalam lajur A (tidak termasuk tajuk):

Menggunakan formula OFFSET

Formula OFFSET umum untuk julat dinamik adalah:

OFFSET(first_cell, 0, 0, COUNTA(range), 1)

Di mana:

• first_cell adalah sel kiri atas julat anda (misalnya, $A$1 ).
• range adalah keseluruhan lajur (contohnya, $A:$A ) atau julat yang cukup besar untuk merangkumi data yang dijangkakan (contohnya, $A$2:$A$2000 ).

COUNTA mengira sel-sel bukan kosong, menentukan ketinggian julat. Laraskan untuk baris header dengan menolak bilangan baris header dari hasil COUNTA . Sebagai contoh, jika data bermula dalam A2 (satu baris tajuk):

=OFFSET(Sheet3!$A$2, 0, 0, COUNTA(Sheet3!$A:$A)-1, 1)

Menggunakan formula INDEX

Sebagai alternatif, gunakan INDEX dan COUNTA :

first_cell:INDEX(column, COUNTA(column))

Ini menggabungkan sel permulaan tetap (misalnya, $A$2 ) dengan sel berakhir yang ditentukan oleh INDEX dan COUNTA . Contohnya:

=$A$2:INDEX($A:$A, COUNTA($A:$A))

COUNTA mengembalikan bilangan sel bukan kosong; INDEX menggunakan ini untuk mencari sel terakhir. Hasilnya adalah julat dinamik.

Nota penting mengenai OFFSET dan INDEX :

• OFFSET adalah tidak menentu (sering dikira kerap), yang berpotensi memberi kesan kepada prestasi pada dataset yang besar. INDEX biasanya lebih cekap.
• Elakkan sel kosong dalam julat data untuk kedua -dua formula.
• Pastikan tiada data tambahan wujud di bawah julat dinamik.

Membuat julat dinamik dua dimensi

Untuk julat dengan baris dan lajur dinamik, ubah formula INDEX / COUNTA :

first_cell:INDEX($1:$1048576, COUNTA(first_column), COUNTA(first_row))

Ini menggunakan dua fungsi COUNTA untuk menentukan baris dan lajur terakhir. Keseluruhan lembaran kerja ( $1:$1048576 ) digunakan sebagai array.

Menggunakan julat bernama dinamik dalam formula

Rentang dinamik dengan lancar diintegrasikan ke dalam formula. Contoh di bawah menunjukkan formula INDEX / MATCH menggunakan julat bernama dinamik ( Lookup_range , Return_range , Lookup_value ) untuk kemas kini automatik sebagai perubahan data:

=INDEX(Return_range, MATCH(Lookup_value, Lookup_range, 0))

Ini menghapuskan kemas kini formula manual apabila menambah data baru.

Buku kerja contoh yang boleh dimuat turun:

Excel Dinamik Dinamakan Range - Contoh Buku Kerja

Tutorial ini menyediakan panduan yang komprehensif untuk mewujudkan dan menggunakan julat dinamik dinamik dalam Excel untuk spreadsheet yang lebih cekap dan mudah disesuaikan.

Atas ialah kandungan terperinci Excel Dinamik Dinamakan Julat: Cara Membuat dan Menggunakan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!