Cara Membuat Rentang Dinamis Yang Didefinisikan di Excel
Data Excel Anda sering berubah, jadi berguna untuk membuat rentang yang ditentukan secara dinamis yang secara otomatis memperluas dan mengecil sesuai ukuran rentang data Anda. Mari kita lihat caranya.
Dengan menggunakan rentang yang ditentukan dinamis, Anda tidak perlu mengedit rentang formula, bagan, dan PivotTables secara manual saat data berubah. Ini akan terjadi secara otomatis.
Dua rumus digunakan untuk membuat rentang dinamis: OFFSET dan INDEX. Artikel ini akan fokus pada penggunaan fungsi INDEX karena merupakan pendekatan yang lebih efisien. OFFSET adalah fungsi yang mudah menguap dan dapat memperlambat spreadsheet besar.
Buat Rentang yang Ditentukan Dinamis di Excel
Untuk contoh pertama kami, kami memiliki daftar data satu kolom yang terlihat di bawah.
Kami membutuhkan ini agar dinamis sehingga jika lebih banyak negara ditambahkan atau dihapus, rentang otomatis diperbarui.
Untuk contoh ini, kami ingin menghindari sel header. Karena itu, kami ingin kisaran $ A $ 2: $ A $ 6, tetapi dinamis. Lakukan ini dengan mengklik Rumus> Tetapkan Nama.
Ketik "negara" di kotak "Nama" dan kemudian masukkan rumus di bawah ini di kotak "Rujukan ke".
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Mengetik persamaan ini ke dalam sel spreadsheet dan kemudian menyalinnya ke kotak Nama Baru terkadang lebih cepat dan mudah.
Bagaimana Ini Bekerja?
Bagian pertama dari rumus menentukan sel awal rentang (A2 dalam kasus kami) dan kemudian operator rentang (:) berikut.
=$A$2:Menggunakan operator rentang memaksa fungsi INDEX untuk mengembalikan rentang, bukan nilai sel. Fungsi INDEX kemudian digunakan dengan fungsi COUNTA. COUNTA menghitung jumlah sel yang tidak kosong di kolom A (enam dalam kasus kami).
INDEX($A:$A,COUNTA($A:$A))Formula ini meminta fungsi INDEX untuk mengembalikan rentang sel tidak kosong terakhir di kolom A ($ A $ 6).
Hasil akhirnya adalah $ A $ 2: $ A $ 6, dan karena fungsi COUNTA, ia dinamis, karena akan menemukan baris terakhir. Anda sekarang dapat menggunakan nama yang didefinisikan "negara-negara" ini di dalam aturan Validasi Data, rumus, bagan, atau di mana pun kita perlu merujuk nama-nama semua negara.
Buat Rentang Ditentukan Dinamis Dua Arah
Contoh pertama tingginya hanya dinamis. Namun, dengan sedikit modifikasi dan fungsi COUNTA lainnya, Anda dapat membuat rentang yang dinamis baik tinggi maupun lebar.
Dalam contoh ini, kita akan menggunakan data yang ditunjukkan di bawah ini.
Kali ini, kami akan membuat rentang yang ditentukan secara dinamis, yang mencakup tajuk. Klik Rumus> Tentukan Nama.
Ketik ‘" penjualan "di kotak" Nama "dan masukkan rumus di bawah ini di kotak" Rujukan Ke ".
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Formula ini menggunakan $ A $ 1 sebagai sel awal. Fungsi INDEX kemudian menggunakan rentang seluruh lembar kerja ($ 1: $ 1048576) untuk melihat dan kembali.
Salah satu fungsi COUNTA digunakan untuk menghitung baris yang tidak kosong, dan yang lain digunakan untuk kolom yang tidak kosong sehingga dinamis di kedua arah. Meskipun formula ini dimulai dari A1, Anda bisa menentukan sel awal apa pun.
Anda sekarang dapat menggunakan nama yang didefinisikan ini (penjualan) dalam formula atau sebagai seri bagan data untuk membuatnya dinamis.