Перейти до основного матеріалу

Як створити список усіх назв робочих аркушів із книги?

Припустимо, у вас є книга з декількома аркушами, тепер ви хочете перерахувати всі імена аркушів у поточній книзі, чи існує якийсь швидкий метод для створення списку тез імен аркушів у Excel, не вводячи їх по одному? Цей підручник містить вказівки щодо переліку імен робочих аркушів у програмі Excel.

Отримайте список усіх назв робочих аркушів із книги динамічно за допомогою формул

Отримайте список усіх назв робочих аркушів із книги з корисною функцією

Отримайте список усіх назв робочих аркушів із книги з кодом VBA


Отримайте список усіх назв робочих аркушів із книги динамічно за допомогою формул

В Excel ви можете визначити назву діапазону, а потім застосувати формулу для переліку всіх назв аркушів із поточної книги, виконайте такі дії:

1. Перейдіть до клацання Formula > Менеджер імен, див. скріншот:

2, в Менеджер імен діалогове вікно натисніть кнопку Нові , див. знімок екрана:

3. В вискочив Нове ім'я діалогове вікно, вкажіть ім'я в ІМ'Я текстове поле, а потім скопіюйте наведену нижче формулу в Відноситься до текстове поле, див. знімок екрана:

=GET.WORKBOOK(1)&T(NOW())

4. Потім натисніть OK > близько щоб закрити діалогові вікна, зараз перейдіть на аркуш, де потрібно перерахувати всі назви аркушів, а потім введіть таку формулу в порожню комірку:

=IFERROR(INDEX(MID(Sheetnames,FIND("]",Sheetnames)+1,255),ROWS($A$2:A2)),"")

примітки: У наведеній вище формулі, Назви аркушів - це назва діапазону, який вас цікавить на кроці 3.

5. А потім перетягніть маркер заповнення вниз до комірок, коли відображаються порожні комірки, і тепер усі імена аркушів поточної книги перераховані, як показано на знімку екрана нижче:

6. Якщо ви хочете створити гіперпосилання для кожного аркуша, використовуйте формулу нижче:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

примітки: У наведеній вище формулі, A2 - комірка, що містить ім'я аркуша, і A1 - це комірка, в якій ви бажаєте знаходити активну комірку. Наприклад, якщо клацнути текст гіперпосилання, він знайде комірку А1 аркуша.

7. Тепер, коли ви натискаєте текст гіперпосилання, ви перейдете до цього аркуша, див.

Tips :
  • 1. За допомогою наведених формул назви створених аркушів перелічуються динамічно, при зміні імені аркуша в книзі ім’я аркуша індексу буде оновлено автоматично.
  • 2. Ви повинні зберегти файл як Книга Excel з підтримкою макросів форматі, якщо ви хочете, щоб формули працювали добре після закриття та повторного відкриття файлу.

Отримайте список усіх назв робочих аркушів із книги з корисною функцією

З Створіть список назв аркушів утиліта сторонньої надбудови Kutools для Excel, ви можете створити список назв робочих аркушів одним клацанням миші та зв’язати кожен аркуш гіперпосиланням.

Примітка:Щоб застосувати це Створіть список назв аркушів, по-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Натисніть Kutools Plus > Робочий аркуш > Створіть список назв аркушів, див. скріншот:

2, в Створіть список назв аркушів у діалоговому вікні, будь ласка, вкажіть такі налаштування:

(1.) Виберіть стилі індексу аркуша Ви можете створити назви робочого аркуша за допомогою гіперпосилань або за допомогою кнопок макросів.

(2.) Введіть назви робочого аркуша для індексу аркуша.

(3.) Вкажіть розташування індексу робочого аркуша.

(4.) Вкажіть, скільки стовпців ви хочете використовувати на новому аркуші для відображення імен аркуша.

3. Закінчивши налаштування, натисніть OK. Усі імена робочого аркуша перелічені із посиланнями на новому аркуші поточної книги. Дивіться знімок екрана:

назви робочого аркуша з гіперпосиланнями назви робочого аркуша з кнопками макросів
Чайові: щоб використовувати цю функцію, вам слід встановити Kutools для Excel спочатку, будь ласка натисніть, щоб завантажити та отримати 30-денну безкоштовну пробну версію зараз.

Отримайте список усіх назв робочих аркушів із книги з кодом VBA

1. Утримуйте клавішу ALT + F11 і відкриває Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний макрос у Модулі Вікно

VBA: Перелічіть усі назви робочого аркуша з гіперпосиланнями на новому аркуші:

Sub CreateIndex()
'updateby Extendoffice
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

3, натисніть F5 щоб запустити цей макрос. Тепер усі назви робочих аркушів в активній робочій книзі перераховано в новому робочому аркуші під назвою «Індекс», і назви аркушів також пов’язані з кожним аркушем, див. знімок екрана:

Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
First, thanks to the author. And adding the Czech version.

tp 3. =O.KNIZE(1)&T(NYNÍ())
tp 4. =IFERROR(INDEX(ČÁST(nazvylistu;NAJÍT("]";nazvylistu)+1;255);ŘÁDKY($A$2:A4));"")
This comment was minimized by the moderator on the site
Hello,

je viens de tester la méthode via macros (dynamique avec formules) et ça marche au poil donc je tiens vraiment à remercier l'auteur de cet article parce que ça va vraiment m'aider dans mon travail ! Juste, je me permet de corriger les formules pour la version française. Et alors je ne sais pas si c'est parce que je suis sous la version 2019 mais Excel rouspète quand il n'y a pas d'argument en 3ème position de la fonction STXT ("MID" en version anglaise) donc obligé d'en rajouter un. Donc voilà ce que ça donne :

=LIRE.CLASSEUR(1)&T(MAINTENANT())

=SIERREUR(INDEX(STXT(nomsFeuilles;TROUVE("]";nomsFeuilles)+1,255;20);LIGNES($A$2:A2));"")

Bon travail à tous ! ;-)
This comment was minimized by the moderator on the site
Hello, Gizmil
Thank you for your comment, there are some functions are only available for English in Excel.
Your formula may help others.
Thanks again!
This comment was minimized by the moderator on the site
I close and open my document and all values in my sheet names column are all gone and blank but still the formula is there. I tried entering the same formula but it doesn't show the value anymore
This comment was minimized by the moderator on the site
Hello, Anne,
Sorrry for replying late, after creating the range names and formulas, you should save the workbook as Excel Macro-Enabled Workbook format, so next time, when you open the Excel file,the formulas can work well.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
I tried this one and it works. But when I close and open the file again all the values in my sheet names are blank and gone but the formula is still there. I tried enteing the same formula again but it doesn't show the value anymore
This comment was minimized by the moderator on the site
BRILLIANT!! Thank you so much! 😊
This comment was minimized by the moderator on the site
Causes problems when document protection is enabled by email or corporate policy
This comment was minimized by the moderator on the site
Thanks so much, this worked great.
This comment was minimized by the moderator on the site
Works great!! Thank you!!!!
This comment was minimized by the moderator on the site
sooooo helpful, works as expected!!!!! Thanks
This comment was minimized by the moderator on the site
THANK YOU SO MUCH! I freaking love your website. In a matter of minutes I've had a ton of time saved with two sections of this site including this one. Love it!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations