Dynamic Named Ranges di Excel

Dynamic Named Ranges di Excel

Dynamic Named Ranges di Excel

Dynamic Named Ranges di Excel adalah sebuah kumpulan data yang bisa diatur mengikuti penambahan baris atau kolom menyesuaikan pertambahan baris atau kolomnya, ini sangat berguna untuk membuat suatu aplikasi yang banyak menggunakan integrasi dari berbagai sheet atau table dengan vlookup

Di Excel, rentang dinamis adalah referensi (atau rumus yang Anda gunakan untuk membuat referensi) yang mungkin berubah berdasarkan input pengguna atau hasil dari sel atau fungsi lain. Ini lebih dari sekedar sekelompok sel tetap seperti $ A1: $ A100. Saya menggunakan rentang dinamis sebagian besar untuk daftar drop-down yang dapat disesuaikan. Untuk menghindari menampilkan sekumpulan kekosongan dalam daftar, saya menggunakan rumus untuk mereferensikan rentang yang meluas ke nilai terakhir dalam kolom. Gambar di bawah ini menunjukkan 4 rumus berbeda yang mereferensikan rentang A2: A9 dan dapat diperluas untuk menyertakan lebih banyak baris jika pengguna menambahkan lebih banyak kategori ke daftar.

Saat kita berbicara tentang rentang bernama dinamis , kita berbicara tentang menggunakan Manajer Nama (melalui tab Formula) untuk menentukan nama formula, seperti categoryList . Kami kemudian dapat menggunakan Nama itu dalam rumus lain atau sebagai Sumber untuk daftar turun bawah .

Artikel ini bukan tentang keuntungan luar biasa menggunakan Nama Excel, meskipun ada banyak. Sebaliknya, ini tentang banyak rumus berbeda yang dapat Anda gunakan untuk membuat rentang bernama dinamis .

Untuk melihat contoh ini beraksi, unduh file Excel di bawah ini.

Unduh File Contoh (DynamicRanges.xlsx)

Apakah Anda memiliki tantangan Dynamic Named Range yang perlu Anda selesaikan? Jika Anda tidak dapat mengetahuinya setelah membaca artikel ini, lanjutkan dan ajukan pertanyaan Anda dengan berkomentar di bawah.

Halaman ini (isi):

  • A) Fungsi OFFSET
  • B) Fungsi INDEX untuk Rentang Dinamis Non-Volatile
  • C) Fungsi TIDAK LANGSUNG
  • D) Referensi Terstruktur dengan Tabel Excel
  • E) Fungsi PILIH dan JIKA
  • Temukan Posisi Nilai TERAKHIR di Kolom
  • Bonus: Buat Area Cetak Dinamis
  • Tidak Ada Rentang Bernama Dinamis di Google Spreadsheet

Tonton Videonya

A) Fungsi OFFSET

Fungsi paling umum yang digunakan untuk membuat rentang bernama dinamis mungkin adalah fungsi OFFSET . Ini memungkinkan Anda untuk menentukan rentang dengan jumlah baris dan kolom tertentu, mulai dari sel referensi . Sintaks untuk fungsi OFFSET adalah:

= OFFSET ( referensi , baris_offset , kolom_offset , [ tinggi ], [ lebar ])

Nilai offset_rows dan offset_cols memberi tahu fungsi di mana Anda ingin sel kiri atas rentang Anda berada, relatif terhadap sel referensi . Nilai tinggi dan lebar memberi tahu fungsi berapa banyak baris dan kolom yang ingin Anda sertakan dalam rentang Anda.

Daftar Dasar yang Dapat Disesuaikan

Lembar Kerja Daftar Makanan

Dalam template agenda makan , saya memiliki daftar makanan yang saya gunakan sebagai Sumber untuk banyak daftar drop-down yang berbeda. Saya ingin mengizinkan pengguna untuk mengedit dan menambahkan ke daftar, sehingga rentang nama dinamis yang digunakan oleh drop-down perlu diperluas ke nilai terakhir dalam daftar. Saya dapat menentukan kisaran sebagai $ A: $ A (seluruh kolom) atau $ A1: $ A1000 (dengan asumsi mereka tidak akan menambahkan lebih dari 1000 item ke daftar), tetapi kemudian saya akan berakhir dengan banyak kosong sel di akhir daftar tarik-turun saya.

Menggunakan fungsi OFFSET, saya menentukan rentang yang dimulai pada sel $ A $ 1 dan berakhir pada sel yang berisi nilai terakhir di kolom . Jika nilai terakhir di kolom berada di baris 15, rumus untuk rentang bernama adalah:

= OFFSET ($ A $ 1,0,0,15,1)

Untuk membuat rumus ini dinamis , saya akan menggunakan rumus atau referensi sebagai ganti nilai ketinggian (15) untuk menentukan berapa banyak baris yang ingin saya sertakan dalam rentang. Saya hanya mengharapkan nilai teks dalam daftar ini, jadi saya akan menggunakan fungsi MATCH untuk menemukan nilai teks terakhir di kolom seperti ini:

= OFFSET ($ A $ 1,0,0, MATCH (“zzzz”, $ A: $ A), 1)

Ada banyak rumus yang bisa Anda gunakan untuk menentukan tinggi atau lebar rentang, seperti COUNTA, COUNT, VLOOKUP, LOOKUP, MATCH, atau berbagai rumus array. Rumus apa yang Anda gunakan mungkin bergantung pada apakah Anda mengharapkan akan ada sel kosong dalam daftar, nilai kesalahan (seperti # N / A atau # DIV / 0!), Atau data dari tipe yang berbeda (teks atau numerik atau keduanya), dan apakah kecepatan kalkulasi adalah masalah.

Nanti di artikel ini saya akan berbicara lebih banyak tentang berbagai rumus untuk menemukan nilai terakhir dalam rentang tersebut. Untuk saat ini, kami akan terus menggunakan MATCH.

Rentang Dinamis yang Kuat

Jika Anda mengizinkan pengguna untuk mengedit daftar, mereka mungkin akan menghapus baris pertama dalam daftar, atau mereka mungkin menyisipkan baris di atas baris pertama dalam daftar (antara baris label dan item pertama). Untuk membuat rumus yang berfungsi terlepas dari tindakan ini oleh pengguna, saya suka menggunakan label untuk referensi dan menyertakan label dalam rentang , seperti yang diperlihatkan dalam contoh berikut.

Referensi untuk Rentang Dinamis yang Kuat

Contoh 1: Sertakan label dalam daftar

= OFFSET ( referensi , 0,0, MATCH (“zzz”, range ), 1)

Contoh 2: Kecualikan label dari daftar

= OFFSET ( referensi , 1 , 0, MATCH (“zzz”, range ) -1 , 1)

Perhatikan pada contoh kedua bahwa referensi dan rentang tidak berubah. Untuk mengecualikan label dari daftar, kami menggunakan offset 1 baris dan mengurangi satu baris dari angka yang dikembalikan oleh fungsi MATCH.

B) Fungsi INDEX untuk Rentang Dinamis Non-Volatile

Sebuah fungsi yang mudah menguap dalam sel atau kisaran bernama dihitung ulang setiap kali setiap sel dalam kalkulasi ulang lembar kerja (bukan hanya ketika salah satu argumen fungsi ini berubah). Selain itu, rumus apa pun yang bergantung pada sel yang berisi fungsi volatil juga dihitung ulang. Jika Anda memiliki file besar dengan ribuan rumus tidak efisien yang mereferensikan sel yang berisi fungsi volatile, Excel mungkin membutuhkan waktu lama untuk menghitung ulang. OFFSET adalah fungsi yang mudah menguap. INDEX bukan, jadi beberapa orang lebih suka menggunakan INDEX untuk rentang dinamis.

Fungsi INDEX bisa mengembalikan referensi ke rentang atau sel, bukan hanya nilainya. Artinya, seperti OFFSET, Anda dapat menggunakan INDEX di banyak tempat di mana Excel mengharapkan referensi sel. Misalnya, untuk membuat rentang A1: A4 Anda dapat menggunakan INDEX (A: A, 1): INDEX (A: A, 4) .

Kita dapat menggunakan INDEX untuk membuat rentang dinamis yang sama seperti yang ditunjukkan dalam contoh OFFSET:

Contoh 1: Sertakan label dalam daftar

= INDEX ( rentang , 1): INDEX ( rentang , MATCH (“zzz”, rentang ))

Contoh 2: Kecualikan label dari daftar

= INDEX ( rentang , 2 ): INDEX ( rentang , MATCH (“zzz”, rentang ))

Untuk mengecualikan label (sel pertama dalam¬†rentang¬†), satu-satunya perubahan yang perlu kami buat adalah “2” di INDEX (¬†rentang¬†, 2).¬†Menggunakan INDEX (¬†range¬†, 1) dan INDEX (¬†range¬†, 2) dan bukan hanya referensi sel membuat rumus lebih kuat.¬†Ini mencegah masalah mengubah referensi menjadi #REF!¬†jika baris referensi terhapus.

CATATANSaat menggunakan INDEX, rumus tidak dapat dimasukkan secara langsung sebagai Sumber untuk daftar turun bawah. Namun, jika Anda membuat rumus sebagai rentang bernama dinamis , Anda dapat menggunakan nama sebagai Sumber untuk daftar turun bawah.

Ngomong-ngomong, saya belajar tentang menggunakan fungsi INDEX untuk membuat rentang dinamis non-volatile dari Mynda Treacy di MyOnlineTrainingHub.com dan Debra Dalgleish di Contextures.com .

Pesanan Pembelian dengan Daftar HargaPesanan Pembelian dengan Daftar Harga

Lihat Dynamic Named Ranges beraksi. Template ini menggunakan beberapa daftar yang dapat disesuaikan untuk memilih deskripsi item, vendor dan pengiriman ke alamat, dan opsi lainnya. Daftar deskripsi item menggunakan rumus OFFSET dan daftar lainnya menggunakan rumus INDEX.

C) Fungsi TIDAK LANGSUNG

Fungsi INDIRECT adalah fungsi mengagumkan lainnya untuk membuat rentang dinamis. Ini juga merupakan fungsi yang mudah menguap , tetapi seperti yang saya sebutkan di atas, itu mungkin tidak masalah. Fungsi INDIRECT memungkinkan Anda menentukan referensi dari string teks, dan Anda dapat menggunakan penggabungan untuk membuat string teks, seperti ini:

= indirect (“Sheet1! A1: A” & row_num )

Dalam contoh yang ditampilkan di bagian atas posting ini, untuk membuat referensi “Sheet1! A2: A9” kami mengganti¬†row_num¬†dengan MATCH (“zzz”, $ A: $ A) untuk mengembalikan nomor baris dari nilai teks terakhir.

Penting:¬†Saat menggunakan INDIRECT untuk mereferensikan rentang pada lembar kerja yang berbeda, jangan lupa untuk menyertakan nama lembar kerja dalam string.¬†Saat Anda memasukkan referensi di kolom¬†Refers To¬†melalui Name Manager, Excel secara otomatis akan mengubah $ A: $ A menjadi “Sheet1! $ A: $ A” tetapi tidak akan tahu cara mengubah string teks Anda.¬†Ini adalah salah satu kontra menggunakan TIDAK LANGSUNG karena jika Anda mengubah nama lembar kerja, Anda harus ingat untuk memperbarui rumus TIDAK LANGSUNG Anda.

D) Referensi Terstruktur dengan Tabel Excel

Dimulai dengan Excel 2007, Microsoft memperkenalkan cara baru untuk bekerja dengan data dalam apa yang disebut Tabel Excel , jangan disamakan dengan Tabel Data , atau penggunaan umum lain dari istilah tabel (ya, ini membingungkan).

Jika Anda menggunakan “Tabel Excel” resmi, Anda dapat menggunakan jenis referensi yang dikenal sebagai¬†referensi terstruktur¬†.¬†Referensi terstruktur menggunakan nama tabel dan label kolom seperti ini:

= Table1 [Kategori]

Anda tidak perlu tahu cara mengetik referensi terstruktur yang benar. Saat Anda memasukkan rumus, jika Anda memilih kolom di Tabel, Excel akan secara otomatis mengubah referensi Anda ke referensi terstruktur.

Tuas Ubah Ukuran Tabel Excel

Alasan jenis referensi ini dapat dianggap dinamis adalah karena cara unik referensi tabel berperilaku saat Anda memperluas atau mengontrak tabel. Artinya, jika Anda memperluas tabel ke bawah dengan menyeret gagang pengubah ukuran di pojok kanan bawah tabel, reference = Table1 [Kategori] akan secara otomatis menyertakan baris baru.

Jadi, alih-alih menggunakan rumus OFFSET untuk memperluas rentang ke nilai terakhir dalam daftar yang dapat disesuaikan, kita dapat membiarkan pengguna mengubah ukuran daftar menggunakan pegangan seret. Tentu saja, ini mengharuskan pengguna untuk mengetahui cara menggunakan Tabel dan pegangan seret. Itulah salah satu alasan mengapa saya tidak sering menggunakan teknik ini di template saya (itu, dan kurangnya kompatibilitas dengan OpenOffice dan Google Sheets).

E) Fungsi PILIH dan JIKA

Meskipun saya tidak menggunakan fungsi PILIH atau rumus IF bersarang untuk membuat daftar panjang variabel, mereka adalah fungsi yang cukup kuat untuk membuat jenis rentang dinamis lainnya. Misalnya, dua rumus di bawah ini mengembalikan kisaran yang berbeda ( range_1 , range_2 atau range_3 ) berdasarkan angka tertentu :

= choose ( angka , rentang_1 , rentang_2 , rentang_3 , …)

= IF ( angka = 1, range_1 , IF ( angka = 2, range_2 , IF ( angka = 3, range_3 )))

Fungsi PILIH pada dasarnya adalah pintasan untuk rumus IF bertumpuk dalam kasus ini. Jika memungkinkan, saya lebih suka menggunakan PILIH daripada rumus IF bertumpuk (jika hanya untuk menghindari satu juta tanda kurung penutup di akhir rumus))))))))))).

Lihat MEMILIH beraksi:¬†Artikel saya ”¬†Membuat Daftar Drop Down di Excel¬†” memperlihatkan contoh menggunakan PILIH untuk membuat daftar drop-down dependen.

Temukan Posisi Nilai TERAKHIR di Kolom

Saat menggunakan rentang bernama dinamis untuk daftar panjang variabel, kita perlu mengetahui cara menemukan¬†posisi¬†numerik¬†dari nilai terakhir di kolom.¬†Dalam contoh di atas kami menggunakan MATCH (“zzz”,¬†range¬†) untuk nilai teks, tetapi untuk data numerik atau kombinasi teks dan data numerik kami mungkin perlu menggunakan sesuatu yang berbeda.¬†Rumus di bawah ini mencakup semua skenario yang saya temui.

Gambar berikut akan digunakan sebagai contoh. Ini menunjukkan referensi dan jangkauan dan nomor posisi dari nilai-nilai dalam jangkauan.

Posisi Nilai Terakhir dalam Rentang

1. Temukan Posisi Nilai Numerik Terakhir

Rumus berikut akan memungkinkan rentang menyertakan nilai kosong, nilai kesalahan, dan teks, tetapi rumus hanya akan mengembalikan posisi nilai numerik terakhir . Hasilnya akan menjadi 4 pada contoh di atas.

= MATCH (1E + 100, range , 1)

= LOOKUP (42,1 / ISNUMBER ( kisaran ), ROW ( kisaran ) – ROW ( referensi ) +1)

Lihat artikel saya tentang VLOOKUP dan INDEX-MATCH untuk penjelasan lebih rinci tentang rumus MATCH dan LOOKUP ini. LOOKUP tidak bekerja dengan cara yang sama di OpenOffice dan Google Sheets. Jadi, untuk kompatibilitas maksimal, saya masih lebih suka menggunakan MATCH.

Nilai 9.99999999999999E + 307 dapat digunakan untuk fungsi MATCH karena ini adalah nilai numerik terbesar yang dapat disimpan dalam sel, tetapi itu agak berlebihan. Saya lebih suka menggunakan sesuatu yang lebih ringkas seperti 1E + 100 atau 10 ^ 100 (googol).

2. Temukan Posisi Nilai Teks Terakhir

Rumus berikut akan memungkinkan rentang menyertakan nilai kosong, nilai kesalahan, dan numerik, tetapi rumus hanya akan mengembalikan posisi nilai teks terakhir (baris 5 dalam contoh di atas).

= MATCH (“zzzz”, rentang , 1)

= LOOKUP (42,1 / ISTEXT ( rentang ), ROW ( rentang ) – ROW ( referensi ) +1)

Catatan:¬†Menggunakan “zzzz” untuk nilai pencarian bukanlah bukti yang bodoh.¬†Karakter Unicode Yunani, Sirilik, Ibrani, dan Arab (dan mungkin karakter Unicode lainnya) muncul setelah z dalam urutan pengurutan.¬†Anda dapat mencoba menggunakan¬†simbol unicode¬†bernilai tinggi¬†seperti ūüóŅ jika Anda mengharapkan daftar berisi simbol.

3. Temukan Posisi Nilai Tidak Kosong Terakhir

Saat kami ingin mengembalikan nilai terakhir yang tidak kosong, kami dapat menggunakan posisi terbesar yang dikembalikan oleh dua rumus MATCH yang tercantum di atas, atau kami dapat menggunakan rumus LOOKUP.

= MAX ( MATCH (9E + 307, rentang ), MATCH (“ūüóŅ”, rentang ))

= LOOKUP (42, 1 / NOT ( ISBLANK ( range )), ROW ( range ) – ROW ( referensi ) +1)

Perhatikan bahwa¬†string¬†kosong¬†“” tidak kosong.¬†Ini diperlakukan sebagai¬†nilai¬†teks¬†.

Sel yang berisi rumus tidak kosong, meskipun rumus mengembalikan string kosong. Jadi, gunakan metode selanjutnya jika Anda ingin menemukan nilai terakhir yang tidak kosong saat Anda menggunakan rumus yang mungkin mengembalikan string kosong.

4. Temukan Posisi Nilai Terakhir Yang Tidak Kosong “”

Dalam banyak aplikasi praktis (seperti kalkulator pinjaman ), kita mungkin tidak perlu mencari sel tidak kosong terakhir (artinya tidak ada nilai atau rumus), tetapi sebaliknya mungkin ingin mencari sel tidak kosong terakhir .

Jika kita mendefinisikan “kosong” sebagai string kosong “”, maka kita dapat mengembalikan nilai tidak kosong terakhir menggunakan rumus di bawah ini.¬†Ini mungkin terlihat seperti¬†rumus array¬†, tetapi sebenarnya tidak, berkat cara kerja LOOKUP (Lihat¬†artikel ini¬†di exceljet.net).

= LOOKUP (42, 1 / ( range <> “”), ROW ( range ) – ROW ( referensi ) +1)

Seperti yang saya sebutkan sebelumnya, LOOKUP tidak bekerja dengan cara yang sama di OpenOffice dan Google Sheets, tetapi SUMPRODUCT memberikan solusi yang baik.

= SUMPRODUCT ( MAX (( range <> “”) * ( ROW ( range ) – ROW ( referensi ) +1)))

Fungsi SUMPRODUCT memungkinkan Anda menghindari memasukkan rumus sebagai¬†rumus array¬†(tidak harus menggunakan Ctrl + Shift + Enter).¬†Di dalam fungsi SUMPRODUCT, kita mengalikan hasil boolean (FALSE = 0 atau TRUE = 1) untuk¬†perbandingan¬†(¬†range¬†<> “”) dengan nomor baris relatif, sehingga hasil maksimum adalah nomor baris dari sel kosong terakhir .

Bonus: Buat Area Cetak Dinamis

Saat Anda membuat area cetak di Excel melalui Tata Letak Halaman> Area Cetak> Setel Area Cetak, Excel sebenarnya membuat rentang bernama khusus yang disebut Print_Area . Anda kemudian dapat masuk ke Rumus> Pengelola Nama dan mengedit rentang bernama tersebut untuk menggunakan rumus seperti ini:

= OFFSET ( start_cell , 0,0, baris , kolom )

Ini adalah teknik yang saya gunakan di beberapa template keuangan seperti Kalkulator Hipotek Rumah untuk membatasi area cetak berdasarkan lamanya jadwal pembayaran.

Tangkapannya : Jika Anda mengedit Tata Letak Halaman (margin, penskalaan, dll.), Referensi Print_Area diubah menjadi referensi biasa. Saya tidak yakin mengapa rumus tersebut dihapus, tetapi saya telah mengirimkan permintaan kepada Microsoft untuk memperbaikinya ( pilih saran itu di sini ). Untuk menghindari pembuatan ulang rumus, saya biasanya menyalin rumus dan menempelkannya ke editor teks sehingga saya dapat dengan mudah membuat ulang area cetak dinamis setelah saya selesai membuat perubahan pada tata letak halaman.

Tidak Ada Rentang Bernama Dinamis di Google Spreadsheet

Mungkin hal-hal akan berubah di masa depan, tetapi pada pembaruan terakhir saya (28 Sep 2017), Anda tidak dapat membuat rumus bernama menggunakan fitur Rentang Bernama di Google Spreadsheet. Ini berarti bahwa meskipun Anda dapat menggunakan Rentang Bernama untuk daftar tarik-turun, Anda tidak dapat membuat Rentang Bernama Dinamis menggunakan rumus di Google Sheets.

Ben Collins¬†menunjukkan bagaimana Anda bisa menggunakan sel untuk membuat string teks seperti “A1: A” &¬†row_num¬†,¬†beri¬†nama sel, lalu gunakan INDIRECT (theName) untuk mendapatkan beberapa fungsi rentang bernama dinamis.¬†Sayangnya, itu masih tidak berfungsi untuk daftar drop-down, karena Anda tidak dapat menggunakan INDIRECT (theCell) sebagai Range untuk daftar drop-down.

Referensi Terkait Dynamic Named Ranges

  • Dynamic Named Ranges¬†di¬†ozgrid.com¬†– Menurut saya situs Dave Hawley adalah tempat pertama saya melihat istilah “dynamic¬†name¬†range” digunakan.¬†Sekarang, Anda dapat menemukan artikel tentang subjek tersebut di sebagian besar situs bantuan Excel.
  • Menggunakan referensi terstruktur dengan tabel Excel¬†di¬†support.office.com
  • Excel Named Ranges¬†di¬†contextures.com¬†– Artikel hebat oleh Debra Dalgleish tentang cara membuat dan menggunakan rentang bernama di Excel.

Tinggalkan Komentar

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

Need Help? Chat with us