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 mengatur 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 bidang Sumber : (lihat Gambar 1).
Dalam jenis dropdown dasar ini, daftar entri yang diizinkan ditentukan dalam validasi data itu sendiri; Oleh karena itu, untuk membuat perubahan pada daftar, pengguna harus membuka dan mengedit validasi data. Ini mungkin sulit, bagaimanapun, untuk pengguna yang tidak berpengalaman, atau dalam kasus di mana daftar pilihan panjang.
Pilihan lain adalah untuk menempatkan daftar dalam rentang bernama dalam spreadsheet, dan kemudian 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 merujuk ke seluruh rentang FruitChoices, termasuk sel kosong H9 dan H10.
Karena alasan ini, ketika 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 nama rentang dinamis sebagai sumber untuk pilihan tarik turun. 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 rumus, bukan rentang tetap alamat sel, untuk menentukan rentang bernama.
Cara Menyiapkan Rentang Dinamis di Excel
Nama rentang normal (statis) mengacu pada rentang sel tertentu ($ H $ 3: $ H $ 10 dalam contoh kami, 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 (semacam makro telah dinonaktifkan).
Mari kita periksa formula ini secara rinci. Pilihan untuk Buah berada di blok sel langsung di bawah judul ( 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 (BENAR, INDEX (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading mengacu pada tajuk yang 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 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 (BENAR, 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 di 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 di mana 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 sel 20-baris yang didefinisikan fungsi OFFSET.
ISBLANK kemudian membuat satu set 20 nilai TRUE dan FALSE, yang menunjukkan apakah masing-masing sel dalam rentang 20-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 formula berikutnya adalah fungsi INDEX:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (yang di atas, 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 BENAR / SALAH yang dibuat oleh fungsi ISBLANK.
INDEX biasanya 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 (BENAR, di atas, 0) -1, 20), 1)
Fungsi MATCH mengembalikan 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 ke-9 dalam 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, di atas, 0) -1 mengembalikan nilai 8 .
Bagian formula berikutnya 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 dimasukkan karena, jika seluruh blok sel (semua 20 baris) diisi dengan entri, fungsi MATCH akan mengembalikan kesalahan.
Ini karena kita 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 karenanya, MATCH mengembalikan kesalahan), fungsi IFERROR akan mengembalikan nilai 20 (mengetahui bahwa harus ada 20 entri dalam daftar).
Akhirnya, OFFSET (FruitsHeading, 1, 0, yang 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 bagaimanapun banyak baris sepanjang 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 dimulai di sel atas dan berdekatan) dan dropdown akan selalu mencerminkan arus daftar (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 diberi 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 judul "NAMES", 40 baris dalam file contoh) ditetapkan nama rentang NameBlock . Formula alternatif untuk mendefinisikan NamesList adalah:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK ), 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 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. Nikmati!
