Bagaimana saya bisa mendapatkan daftar lembar?

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 Sub

Langkah 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

Bagaimana cara mendapatkan daftar lembar di Excel?

Bagaimana cara mendapatkan daftar sheet di Google Sheets?

Bagaimana cara mendaftar beberapa lembar di Excel?

Apa cara pintas untuk melihat semua lembar di Excel?