Dapatkan daftar nama sheet di excel

DAPATKAN. WORKBOOK adalah perintah makro yang mengambil array nama sheet di buku kerja saat ini. Array yang dihasilkan terlihat seperti ini

{"[workbook.xlsm]Sheet1","[workbook.xlsm]Sheet2","[workbook.xlsm]Sheet3","[workbook.xlsm]Sheet4","[workbook.xlsm]Sheet5"}

Ekspresi samar digabungkan dengan hasilnya

&T(NOW())

Tujuan dari kode ini adalah untuk memaksa penghitungan ulang untuk mengambil perubahan pada nama sheet. Karena SEKARANG adalah fungsi yang mudah menguap, fungsi ini menghitung ulang dengan setiap perubahan lembar kerja. Fungsi SEKARANG mengembalikan nilai numerik yang mewakili tanggal dan waktu. Fungsi T mengembalikan string kosong ("") untuk nilai numerik, sehingga penggabungan tidak berpengaruh pada nilai

Kembali ke lembar kerja, sel B6 berisi rumus ini yang disalin

=INDEX(MID(sheetnames,FIND("]",sheetnames)+1,255),ROWS($B$5:B5))

Bekerja dari dalam ke luar, fungsi MID digunakan untuk menghapus nama lembar kerja. Array yang dihasilkan terlihat seperti ini

{"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}

Ini masuk ke fungsi INDEX sebagai "array". Fungsi ROW menggunakan rentang yang diperluas untuk menghasilkan nomor baris yang bertambah. Di setiap baris baru, INDEX mengembalikan nilai larik berikutnya. Ketika tidak ada lagi nama sheet untuk ditampilkan, rumus akan mengembalikan kesalahan #REF

Catatan. karena rumus ini bergantung pada perintah makro, Anda harus menyimpan sebagai buku kerja dengan makro aktif jika Anda ingin rumus terus memperbarui nama lembar setelah file ditutup dan dibuka kembali. Jika Anda menyimpan sebagai lembar kerja biasa, kode nama lembar akan dihapus

Q. Saya memiliki buku kerja Excel dengan lusinan kustom - bernama worksheets. Is it possible to create a list of those worksheet names in Excel without having to retype them?

A. Ya, Anda dapat membuat daftar nama lembar kerja buku kerja Excel Anda sebagai berikut. Dari tab Formulas , pilih Defined Names , Define Name to launch the New Name dialog box pictured below.

Masukkan SheetNames ke dalam kolom Nama, masukkan rumus berikut ke dalam kolom Rujuk ke. =REPLACE(DAPATKAN. LKS(1),1,CARI("]",DAPATKAN. WORKBOOK(1)),"") , lalu pilih OK. Tindakan ini akan membuat rumus bernama yang kemudian dapat digunakan bersama dengan fungsi INDEX untuk menghasilkan daftar nama lembar kerja. Melanjutkan, mari kita asumsikan Anda memiliki 25 lembar kerja bernama. Selanjutnya, pilih sel tempat Anda ingin menampilkan daftar nama (sel B2 dalam contoh ini), dan masukkan angka 1 hingga 25 (dalam sel A2 hingga A26 dalam contoh ini, seperti gambar di bawah). Kemudian di sel B2 masukkan rumus =INDEX(SheetNames,A2) , lalu salin dan tempel rumus ke bawah 25 baris. Rumus ini akan mengembalikan daftar nama tab lembar kerja Anda dalam urutan yang sama dengan tab lembar kerja Anda .

(Catatan. Buku kerja harus disimpan sebagai jenis file buku kerja Excel Makro - Diaktifkan . ) Defined Name formula.)

Sekarang Anda telah berhasil menambahkan daftar isi ke buku kerja Anda, saya sarankan Anda menambahkan hyperlink juga, untuk membantu navigasi. Ini dapat dilakukan dengan menambahkan rumus =HYPERLINK("#'"&B2&"'. A1","Buka Lembar") di sel C2 (pada contoh di bawah) dan salin rumus ini di samping setiap nama lembar kerja .

Akibatnya, Anda akan dapat mengeklik setiap tautan untuk melompat ke berbagai lembar kerja yang tercantum dalam daftar isi Anda. (Sebagai alternatif, Anda dapat memilih setiap nama dalam daftar isi satu per satu dan menekan Ctrl+K untuk menerapkan hyperlink langsung ke setiap nama lembar kerja. Keuntungan dari pendekatan alternatif ini adalah bahwa kolom hyperlink yang terpisah (seperti kolom C pada contoh di atas) tidak diperlukan, tetapi kerugiannya adalah setiap hyperlink harus diterapkan satu per satu. )

Selanjutnya, saya sarankan Anda menyisipkan tombol pada setiap lembar kerja yang akan mengembalikan Anda ke daftar isi, seperti berikut.

1. Beri nama daftar isi Anda. Mulailah dengan menempatkan kursor Anda di bagian atas daftar isi dan di Kotak Nama (terletak tepat di atas kolom A), ketik TOC dan tekan tombol Enter. Lokasi bernama ini akan menjadi sel tempat tombol navigasi yang dibuat pada langkah-langkah di bawah ini akan mengarah
2. Buat tombol kotak teks hyperlink. Di manapun dalam buku kerja, sisipkan Kotak Teks (dari menu Bentuk tab Sisipkan), beri label Kembali ke Daftar Isi , dan format tombol menjadi . (Dalam contoh gambar di bawah, saya membuat Kotak Teks berwarna merah, memusatkan dan menebalkan teks, menerapkan efek tombol, dan menerapkan bayangan kanan bawah. )
3. Terapkan hyperlink ke tombol kotak teks Anda. Klik kanan kotak teks untuk memilihnya, tekan Ctrl+K untuk membuka kotak dialog Insert Hyperlink, pilih Place in This Document (di bawah menu Link to), gulir ke bawah ke Defined Names, pilih TOC, dan tekan OK. Tindakan ini akan membuat tombol yang dapat diklik yang akan mengembalikan Anda ke daftar isi
4. Salin dan tempel tombol kotak teks hyperlink di seluruh lembar kerja Anda. Klik kanan tombol kotak teks lagi untuk memilihnya, klik kiri di bagian paling tepi tombol kotak teks untuk memastikan Anda telah memilih seluruh tombol kotak teks dan bukan hanya teks di dalam kotak teks, tekan Ctrl+C untuk menyalin . (Anda mungkin ingin menempelkan beberapa salinan tombol kotak teks ini pada lembar kerja Anda yang lebih besar untuk menambah kenyamanan navigasi. )

(Petunjuk. Untuk memilih kotak teks hyperlink - diaktifkan tanpa memicu efek hyperlink, . ) Anda dapat mengunduh contoh buku kerja Excel yang berisi daftar isi dan hyperlink navigasi di carltoncollins. com/list. xlsm. right-click the text box button and then left-click the very edge of the text box button — this action will allow you to resize or reposition the button as desired.) You can download an example Excel workbook containing a table of contents and navigational hyperlinks at carltoncollins.com/list.xlsm.

Tentang Penulis

J. Carlton Collins (carlton@asearch. com) adalah konsultan teknologi, presenter konferensi, dan editor kontributor JofA

Kirim pertanyaan

Apakah Anda memiliki pertanyaan teknologi untuk kolom ini? . org. Kami menyesal tidak dapat menjawab satu per satu semua pertanyaan yang diajukan

Bagaimana cara mengekstrak beberapa nama sheet di Excel?

Bagaimana cara mendapatkan daftar lembar kerja di buku kerja?

Bagaimana cara mendapatkan daftar nama sheet di Excel VBA?

Bagaimana cara membuat indeks semua lembar di Excel?