Gunakan Nama Rentang Dinamis di Excel untuk Dropdown Fleksibel

Gunakan Nama Rentang Dinamis di Excel untuk Dropdown Fleksibel.

 Gunakan Nama Rentang Dinamis di Excel untuk Dropdown Fleksibel



Spreadsheet Excel sering menyertakan dropdown sel untuk menyederhanakan dan/atau menstandardisasi entri data. Dropdown ini dibuat menggunakan fitur validasi data untuk menentukan daftar entri yang diizinkan.


Untuk menyiapkan daftar tarik-turun sederhana, pilih sel tempat data akan dimasukkan, lalu klik Validasi Data (pada tab Data), pilih Validasi Data, pilih Daftar (di bawah Izinkan:), lalu masukkan item daftar (dipisahkan dengan koma ) di bidang Sumber: (lihat Gambar 1).

Daftar isi


Dalam jenis dropdown dasar ini, daftar entri yang diizinkan ditentukan dalam validasi data itu sendiri; oleh karena itu, untuk melakukan perubahan pada daftar, pengguna harus membuka dan mengedit validasi data. Namun, ini mungkin sulit bagi pengguna yang tidak berpengalaman, atau dalam kasus di mana daftar pilihannya panjang.


Pilihan lain adalah menempatkan daftar dalam rentang bernama dalam spreadsheet, dan kemudian tentukan nama rentang tersebut (diawali dengan tanda sama dengan) di bidang Sumber: validasi data (seperti yang ditunjukkan pada Gambar 2).


Metode kedua ini memudahkan untuk mengedit pilihan dalam daftar, tetapi menambahkan atau menghapus item dapat menjadi masalah. Karena rentang bernama (FruitChoices, dalam contoh kami) mengacu pada rentang sel tetap ($H$3:$H$10 seperti yang ditunjukkan), jika lebih banyak pilihan ditambahkan ke sel H11 atau di bawahnya, mereka tidak akan muncul di dropdown (karena sel-sel itu bukan bagian dari rentang FruitChoices).


Demikian juga jika, misalnya, entri Pears dan Strawberry dihapus, mereka tidak akan lagi muncul di dropdown, tetapi dropdown akan menyertakan dua pilihan "kosong" karena dropdown masih merujuk seluruh rentang FruitChoices, termasuk sel kosong H9 dan H10.


Untuk alasan ini, saat menggunakan rentang bernama normal sebagai sumber daftar untuk dropdown, rentang bernama itu sendiri harus diedit untuk menyertakan lebih banyak atau lebih sedikit sel jika entri ditambahkan atau dihapus dari daftar.


Solusi untuk masalah ini adalah dengan menggunakan nama rentang dinamis sebagai sumber untuk pilihan dropdown. Nama rentang dinamis adalah nama yang secara otomatis diperluas (atau dikontrak) agar sama persis dengan ukuran blok data saat entri ditambahkan atau dihapus. Untuk melakukannya, Anda menggunakan rumus, bukan rentang alamat sel tetap, untuk menentukan rentang bernama.


Cara Mengatur Rentang Dinamis di Excel


Nama rentang normal (statis) mengacu pada rentang sel tertentu ($H$3:$H$10 dalam contoh kita, lihat di bawah):


Tetapi rentang dinamis didefinisikan menggunakan rumus (lihat di bawah, diambil dari spreadsheet terpisah yang menggunakan nama rentang dinamis):


Sebelum memulai, pastikan Anda mengunduh file contoh Excel kami (makro sortir telah dinonaktifkan).

Mari kita periksa rumus ini secara rinci. Pilihan untuk Buah ada di blok sel tepat di bawah heading (BUAH). Judul itu juga diberi nama: FruitsHeading:


Seluruh rumus yang digunakan untuk menentukan rentang dinamis untuk pilihan Buah adalah:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)


FruitsHeading mengacu pada heading yang berada satu baris di atas entri pertama dalam daftar. Angka 20 (digunakan dua kali dalam rumus) adalah ukuran maksimum (jumlah baris) untuk daftar (ini dapat disesuaikan sesuai keinginan).


Perhatikan bahwa dalam contoh ini, hanya ada 8 entri dalam daftar, tetapi ada juga sel kosong di bawahnya di mana entri tambahan dapat ditambahkan. Angka 20 mengacu pada seluruh blok tempat entri dapat dibuat, bukan jumlah entri sebenarnya.


Sekarang mari kita urai rumus menjadi beberapa bagian (kode warna setiap bagian), untuk memahami cara kerjanya:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)


Bagian "paling dalam" adalah OFFSET (FruitsHeading,1,0,20,1). Ini merujuk pada blok 20 sel (di bawah sel FruitsHeading) di mana pilihan dapat dimasukkan. Fungsi OFFSET ini pada dasarnya mengatakan: Mulai dari sel FruitsHeading, turun 1 baris dan lebih dari 0 kolom, lalu pilih area yang panjangnya 20 baris dan lebar 1 kolom. Jadi itu memberi kita blok 20 baris tempat pilihan Buah dimasukkan.


Bagian berikutnya dari rumus adalah fungsi ISBLANK:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(di atas),0,0),0)-1,20),1)


Di sini, fungsi OFFSET (dijelaskan di atas) telah diganti dengan “yang di atas” (untuk mempermudah membaca). Tetapi fungsi ISBLANK beroperasi pada rentang 20 baris sel yang ditentukan oleh fungsi OFFSET.


ISBLANK kemudian membuat satu set 20 nilai TRUE dan FALSE, yang menunjukkan apakah masing-masing sel individu dalam rentang 20 baris yang dirujuk oleh fungsi OFFSET kosong (kosong) atau tidak. Dalam contoh ini, 8 nilai pertama dalam kumpulan akan menjadi FALSE karena 8 sel pertama tidak kosong dan 12 nilai terakhir adalah TRUE.


Bagian berikutnya dari rumus adalah fungsi INDEX:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(di atas,0,0),0)-1,20),1)


Sekali lagi, "yang di atas" mengacu pada fungsi ISBLANK dan OFFSET yang dijelaskan di atas. Fungsi INDEX mengembalikan larik yang berisi 20 nilai TRUE / FALSE yang dibuat oleh fungsi ISBLANK.


INDEX biasanya digunakan untuk memilih nilai tertentu (atau rentang nilai) dari blok data, dengan menentukan baris dan kolom tertentu (di dalam blok itu). Tetapi menyetel input baris dan kolom ke nol (seperti yang dilakukan di sini) menyebabkan INDEX mengembalikan array yang berisi seluruh blok data.


Bagian berikutnya dari rumus adalah fungsi MATCH:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(BENAR,di atas,0)-1,20),1)

Fungsi MATCH mengembalikan posisi nilai TRUE pertama, dalam larik yang dikembalikan oleh fungsi INDEX. Karena 8 entri pertama dalam daftar tidak kosong, 8 nilai pertama dalam larik adalah FALSE, dan nilai kesembilan adalah TRUE (karena baris ke-9^ dalam rentang kosong).


Jadi fungsi MATCH akan mengembalikan nilai 9. Namun, dalam kasus ini, kami benar-benar ingin mengetahui berapa banyak entri dalam daftar, jadi rumusnya mengurangi 1 dari nilai MATCH (yang memberikan posisi entri terakhir). Jadi akhirnya, MATCH(TRUE,the above,0)-1 mengembalikan nilai 8.


Bagian berikutnya dari rumus adalah fungsi IFERROR:

=OFFSET(FruitsHeading,1,0,IFERROR(di atas,20),1)


Fungsi IFERROR mengembalikan nilai alternatif, jika nilai pertama yang ditentukan menghasilkan kesalahan. Fungsi ini disertakan karena, jika seluruh blok sel (semua 20 baris) diisi dengan entri, fungsi MATCH akan mengembalikan kesalahan.


Ini karena kami memberi tahu fungsi MATCH untuk mencari nilai TRUE pertama (dalam array nilai dari fungsi ISBLANK), tetapi jika NONE dari sel kosong, maka seluruh array akan diisi dengan nilai FALSE. Jika MATCH tidak dapat menemukan nilai target (TRUE) dalam larik yang dicari, ia mengembalikan kesalahan.


Jadi, jika seluruh daftar penuh (dan karena itu, MATCH mengembalikan kesalahan), fungsi IFERROR akan mengembalikan nilai 20 (mengetahui bahwa harus ada 20 entri dalam daftar).


Akhirnya, OFFSET(FruitsHeading,1,0,the above,1) mengembalikan rentang yang sebenarnya kita cari: Mulai dari sel FruitsHeading, turun 1 baris dan lebih dari 0 kolom, lalu pilih area yang panjangnya banyak baris ada entri dalam daftar (dan lebar 1 kolom). Jadi seluruh rumus bersama-sama akan mengembalikan rentang yang hanya berisi entri aktual (hingga sel kosong pertama).


Menggunakan rumus ini untuk menentukan rentang yang merupakan sumber untuk dropdown berarti Anda dapat dengan bebas mengedit daftar (menambahkan atau menghapus entri, selama entri yang tersisa mulai dari sel atas dan bersebelahan) dan dropdown akan selalu mencerminkan saat ini daftar (lihat Gambar 6).


File contoh (Daftar Dinamis) yang telah digunakan di sini disertakan dan dapat diunduh dari situs web ini. Namun, makro tidak berfungsi, karena WordPress tidak menyukai buku Excel dengan makro di dalamnya.


Sebagai alternatif untuk menentukan jumlah baris dalam blok daftar, blok daftar dapat diberi nama rentangnya sendiri, yang kemudian dapat digunakan dalam formula yang dimodifikasi. Dalam file contoh, daftar kedua (Nama) menggunakan metode ini. Di sini, seluruh blok daftar (di bawah judul "NAMES", 40 baris dalam file contoh) diberi nama rentang NameBlock. Rumus alternatif untuk mendefinisikan NamesList kemudian:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)


di mana NamesBlock menggantikan OFFSET(FruitsHeading,1,0,20,1) dan ROWS(NamesBlock) menggantikan 20 (jumlah baris) dalam rumus sebelumnya.


Jadi, untuk daftar dropdown yang dapat dengan mudah diedit (termasuk oleh pengguna lain yang mungkin belum berpengalaman), coba gunakan nama rentang dinamis! Dan perhatikan bahwa, meskipun artikel ini telah difokuskan pada daftar dropdown, nama rentang dinamis dapat digunakan di mana pun Anda perlu merujuk rentang atau daftar yang ukurannya dapat bervariasi. Menikmati!


Sekian artikel tentang Gunakan Nama Rentang Dinamis di Excel untuk Dropdown Fleksibel. Baca juga artikel mengenai Gunakan Netstat untuk Melihat Mendengarkan Port dan PID di Windows yang mungkin perlu Sobat ketahui.

Lebih baru Lebih lama