Lembar kerja Excel sering menyertakan dropdown sel untuk menyederhanakan dan / atau membakukan entri data. Dropdown ini dibuat menggunakan fitur validasi data untuk menentukan daftar entri yang diijinkan.
Untuk menyiapkan daftar dropdown sederhana, pilih sel tempat data akan dimasukkan, lalu klik Validasi Data(pada tab Data), pilih Validasi Data, pilih Daftar(di bawah Perbolehkan :), lalu masukkan item daftar (dipisahkan dengan koma) di
Dalam jenis dropdown dasar ini, daftar entri yang diijinkan ditentukan dalam validasi data itu sendiri; Oleh karena itu, untuk membuat 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 pilihan panjang.
Opsi lain adalah menempatkan daftar dalam rentang bernama dalam spreadsheet, lalu tentukan nama rentang itu (diawali dengan tanda sama dengan) di bidang Sumber: bidang validasi data (seperti yang ditunjukkan pada Gambar 2).
Metode kedua ini memudahkan untuk mengedit pilihan dalam daftar, tetapi menambahkan atau menghapus item bisa menjadi masalah. Karena rentang bernama (FruitChoices, dalam contoh kami) merujuk ke rentang sel yang tetap ($ H $ 3: $ H $ 10 seperti yang ditunjukkan), jika lebih banyak pilihan ditambahkan ke sel H11 atau di bawah, mereka tidak akan muncul di dropdown (karena sel-sel itu bukan bagian dari rentang FruitChoices).
Demikian juga jika, misalnya, entri Pir dan Stroberi dihapus, mereka tidak akan lagi muncul di dropdown, tetapi sebaliknya dropdown akan mencakup dua Pilihan "kosong" karena dropdown masih mereferensikan 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 memasukkan lebih banyak atau lebih sedikit sel jika entri ditambahkan atau dihapus dari daftar.
Solusi untuk masalah ini adalah dengan menggunakan dinamisrentang nama 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 melakukan ini, Anda menggunakan formula, alih-alih rentang tetap alamat sel, untuk menentukan rentang bernama.
Cara Mengatur Dinamis Rentang dalam Excel
Nama rentang normal (statis) mengacu pada rentang sel yang ditentukan ($ H $ 3: $ H $ 10 dalam contoh kami, lihat di bawah):
Tetapi rentang dinamis ditentukan menggunakan rumus (lihat di bawah, diambil dari spreadsheet terpisah yang menggunakan nama rentang dinamis):
Sebelum memulai, pastikan Anda mengunduh File contoh Excel kami (sortir macro telah dinonaktifkan).
Mari kita periksa rumus ini secara detail. Pilihan untuk Buah berada di blok sel langsung di bawah tajuk (BUAH). Judul itu juga diberi nama: FireitsHeading
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)
FruitsHeadingmerujuk pada tajuk 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 bawah ini di mana entri tambahan dapat ditambahkan. Angka 20 merujuk ke seluruh blok tempat entri dapat dibuat, bukan ke jumlah entri yang sebenarnya.
Sekarang mari kita pisahkan 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 referensi blok 20 sel (di bawah sel FruitsHeading) di mana pilihan dapat dimasukkan. Fungsi OFFSET ini pada dasarnya mengatakan: Mulai pada sel FruitsHeading, turun 1 baris dan lebih dari 0 kolom, lalu pilih area yang panjangnya 20 baris dan lebar 1 kolom. Sehingga memberi kami blok 20-baris tempat pilihan Buah dimasukkan.
Bagian berikutnya dari rumus adalah fungsi ISBLANK:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),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 sel 20-baris yang didefinisikan oleh fungsi OFFSET.
ISBLANK kemudian membuat satu set 20 nilai TRUE dan FALSE, yang menunjukkan apakah masing-masing sel individu dalam 20- rentang baris yang dirujuk oleh fungsi OFFSET kosong (kosong) atau tidak. Dalam contoh ini, 8 nilai pertama dalam set akan FALSE karena 8 sel pertama tidak kosong dan 12 nilai terakhir akan BENAR.
Bagian berikutnya dari rumus adalah fungsi INDEX:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Sekali lagi, "di atas" mengacu pada fungsi ISBLANK dan OFFSET yang dijelaskan di atas. Fungsi INDEX mengembalikan array yang berisi 20 nilai TRUE / FALSE yang dibuat oleh fungsi ISBLANK.
INDEXbiasanya digunakan untuk memilih nilai tertentu (atau rentang nilai) dari blok data, dengan menentukan baris dan kolom tertentu (dalam blok itu). Tetapi mengatur 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(TRUE,the above,0)-1,20),1)
Fungsi MATCHmengembalikan posisi nilai TRUE pertama, dalam array yang dikembalikan oleh fungsi INDEX. Karena 8 entri pertama dalam daftar tidak kosong, 8 nilai pertama dalam array akan FALSE, dan nilai kesembilan akan BENAR (karena baris 9 thdalam rentang kosong).
Jadi fungsi MATCH akan mengembalikan nilai 9. Namun, dalam kasus ini, kami benar-benar ingin tahu berapa banyak entri dalam daftar, sehingga rumus mengurangi 1 dari nilai MATCH (yang memberikan posisi entri terakhir). Jadi pada akhirnya, MATCH (TRUE, yang di atas, 0) -1 mengembalikan nilai 8
Bagian berikutnya dari rumus adalah fungsi IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,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-nilai FALSE. Jika MATCH tidak dapat menemukan nilai target (TRUE) dalam array yang dicari, ia mengembalikan kesalahan.
Jadi, jika seluruh daftar penuh (dan karena itu, MATCH mengembalikan kesalahan), fungsi IFERROR akan alih-alih kembalikan nilai 20 (mengetahui bahwa harus ada 20 entri dalam daftar).
Akhirnya, OFFSET (FruitsHeading, 1,0, di atas, 1)mengembalikan rentang yang sebenarnya kita cari: Mulai di sel FruitsHeading, turun 1 baris dan lebih dari 0 kolom, lalu pilih area yang memiliki banyak baris namun ada entri dalam daftar (dan lebar 1 kolom). Jadi seluruh rumus bersama-sama akan mengembalikan rentang yang hanya berisi entri aktual (ke sel kosong pertama).
Menggunakan rumus ini untuk menentukan rentang yang merupakan sumber dropdown berarti Anda dapat dengan bebas mengedit daftar (menambah atau menghapus entri, selama entri yang tersisa mulai di sel atas dan bersebelahan) dan dropdown akan selalu mencerminkan daftar saat ini (lihat Gambar 6).
contoh file (Daftar Dinamis) yang telah digunakan di sini disertakan dan dapat diunduh dari situs web ini. Makro tidak berfungsi, karena WordPress tidak suka buku Excel dengan makro di dalamnya.
Sebagai alternatif untuk menentukan jumlah baris dalam blok daftar, blok daftar dapat ditetapkan nama rentang 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 tajuk “NAMES”, 40 baris dalam file contoh) ditetapkan nama rentang NameBlock. Formula alternatif untuk menentukan NamesList adalah:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
di mana NamesBlockmenggantikan 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 tidak berpengalaman), coba gunakan nama rentang dinamis! Dan perhatikan bahwa, meskipun artikel ini telah difokuskan pada daftar dropdown, nama rentang dinamis dapat digunakan di mana saja Anda perlu referensi rentang atau daftar yang dapat bervariasi dalam ukuran. Selamat menikmati!