Dalam beberapa kasus, Anda mungkin memiliki lusinan lembar bernama di buku kerja Anda. Namun, jika Anda memerlukan daftar nama sheet, Anda dapat menggunakan rumus. Pada artikel ini, saya akan membahas 5 cara konvensional yang sebagian besar berupa formula dan juga kode VBA untuk menemukan daftar nama sheet di Excel
Jika Anda perlu mendapatkan nama lembar saja, bukan daftar, Anda dapat mengunjungi artikel Nama Lembar Excel. Di sini, kami akan menunjukkan proses pengambilan daftar nama sheet dalam satu kolom
Daftar isi
Download Buku Latihan
Daftar Nama Lembar. xlsm
Cara Mendapatkan Daftar Nama Sheet di Excel
Pada gambar berikut, Anda melihat bahwa kami memiliki 12 lembar yang sebagian besar merupakan harga dalam beberapa bulan dalam setahun. Selain itu, nama sheetnya seperti Price_Jan, Price_Feb, dan seterusnya
Sayangnya, tidak ada fungsi khusus untuk mendapatkan daftar nama sheet di Excel. Jadi kita harus menggunakan kombinasi beberapa fungsi atau proses 2 langkah menggunakan Name Manager & formula
Mari selami metodenya
1. Menggunakan Kombinasi Fungsi INDEX dan FIND untuk Mendapatkan Daftar Nama Sheet
Jika Anda ingin mencari daftar nama sheet menggunakan beberapa fungsi, Anda dapat menggunakan rumus yang berisi fungsi INDEX, LEFT, MID, dan FIND
Ikuti langkah-langkah di bawah ini
Langkah
⏩ Klik pada tab Rumus > opsi Pengelola Nama dari pita Nama yang Ditetapkan
⏩ Kemudian Anda akan melihat kotak dialog yaitu Name Manager
⏩ Tekan pada opsi Baru
⏩ Masukkan Nama (di sini namanya Lembar Kerja) dan rumus di bawah ini di bagian Merujuk ke
=GET.WORKBOOK(1) & T(NOW())_
Catatan. DAPATKAN. WORKBOOK adalah fungsi berkemampuan makro yang menyimpan nama sheet di buku kerja.
⏩ Setelah itu masukkan rumus berikut di sel B5 tempat Anda ingin mendapatkan nama sheet
=INDEX(MID(Worksheets,FIND("]",Worksheets)+1,255),ROWS($B$5:B5))
⏩ Terakhir, jika Anda menekan Enter dan menggunakan Fill Handle Tool untuk sel-sel di bawah ini, Anda akan mendapatkan daftar nama sheet seperti berikut
Baca Selengkapnya. Cara Mendapatkan Nama Lembar Excel (2 Metode)
2. Memanfaatkan Fungsi INDEX bersama dengan Fungsi REPLACE (Daftar Otomatis)
Sekali lagi, Anda dapat memasukkan rumus di bawah ini di bagian Merujuk ke dan Nama sebagai Nama Lembar setelah mengklik opsi Baru dari kotak dialog Manajer Nama
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Sekarang, masukkan rumus di mana Anda ingin mendapatkan daftarnya
=INDEX(SheetNames,B5)_
Di sini, B5 adalah sel awal dari nomor seri (S/N)
3. Menerapkan Fungsi TRANSPOSE untuk Menemukan Daftar Nama Sheet
Selanjutnya, Anda dapat menerapkan fungsi TRANSPOSE yang mengembalikan rentang sel horizontal sebagai rentang sel vertikal, atau sebaliknya
Sebelum melakukan itu, pastikan Namanya adalah GetSheetNames dan masukkan rumus di bawah ini
=GET.WORKBOOK(1)
Kemudian masukkan rumus berikut
=TRANSPOSE(GetSheetNames)_
Akhirnya, Anda akan mendapatkan output berikut
4. Menggunakan Fungsi LOOKUP untuk Mendapatkan Daftar Nama Sheet
Sebelum menggunakan fungsi LOOKUP, buat nama baru di mana Nama dapat berupa Lembar dan rumus di bagian Merujuk ke
=GET.WORKBOOK(1)&T(NOW())
Catatan. Formula pengaktifan makro yang sama digunakan dalam metode pertama (Nama. Lembar Kerja), Anda juga dapat menggunakannya jika Anda mau
Kemudian masukkan rumusnya
=LOOKUP("xxxxx",CHOOSE({1,2},"",INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROWS(B$1:B1))))_
Jika Anda menekan Enter dan menggunakan FIl Handle Tool, Anda akan mendapatkan output berikut
5. Membuat Daftar Nama Lembar Dinamis Menggunakan Fungsi SUBSTITUTE
Selain itu, Anda dapat membuat daftar nama sheet yang dinamis menggunakan fungsi SUBSTITUTE
Perbaiki nama sebagai Dynamic_List_of_SheetNames dan masukkan rumus di bawah ini
=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")_
Setelah itu, masukkan rumus berikut
=GET.WORKBOOK(1) & T(NOW())_0
Di sini, B5 adalah sel awal dari nomor seri (S/N)
6. Daftar Nama Sheet Menggunakan Kode VBA
Sekarang, mari kita lihat bagaimana Anda bisa menerapkan kode VBA untuk mendapatkan daftar nama sheet di excel
Langkah 1.
Pertama, buka modul dengan mengklik Pengembang>Visual Basic
Kedua, pergi ke Sisipkan> Modul
Langkah 2.
Kemudian, salin kode berikut di modul Anda
Sub GetListOfAllSheets() Dim w As Worksheet Dim i As Integer i = 5 Sheets("Price_Jun").Range("B:B").Clear For Each w In Worksheets Sheets("Price_Jun").Cells(i, 2) = w.Name i = i + 1 Next w End SubLangkah 3
Terakhir, jalankan kodenya
Catatan
Berhati-hatilah dengan fakta berikut saat menggunakan kode VBA
- Nama lembar kerja. Di sini, nama worksheetnya adalah “Price_Jun”
- Sel(i, 2) berarti lokasi sel dari baris i (di sini, i=5) dan kolom 2
Setelah menjalankan kode, hasilnya adalah sebagai berikut
Baca Selengkapnya. Cara Mencari Nama Sheet dengan VBA di Excel (3 Contoh)
Hal-hal untuk diingat
I. Sebagai DAPATKAN. WORKBOOK adalah fungsi yang diaktifkan makro, simpan file Excel di. format xlsm (format ekstensi yang ditunjuk untuk file Excel yang diaktifkan makro)
II. Berhati-hatilah dengan kesalahan #BLOCKED yang mungkin ditemukan alih-alih keluaran jika buku kerja tidak dapat diperbarui
Kesimpulan
Pada artikel di atas, saya mencoba membahas metode untuk mendapatkan daftar nama sheet. Jelas, saya percaya metode ini mungkin bermanfaat bagi Anda. Bagaimanapun, jika Anda memiliki pertanyaan dan saran, jangan lupa untuk membagikannya di bagian komentar berikut