Как создать список всех имён листов из книги?

Представьте, что у вас есть книга с несколькими листами, и вы хотите автоматически создать список всех имён листов со ссылками для быстрого перехода на нужный лист. В этом руководстве описаны простые и эффективные способы создания такого списка в Excel.

Динамическое получение списка всех Имя листа из книги с помощью формул

Получение списка всех Имя листа из книги с помощью Kutools для Excel

Получение списка всех Имя листа из книги с помощью кода VBA

Динамическое получение списка всех Имя листа из книги с помощью формул

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

1. Перейдите в раздел «Формулы» > «Менеджер имен» (см. снимок экрана):

2. В диалоговом окне «Менеджер имен» нажмите кнопку «Создать» (см. снимок экрана):

3. В появившемся диалоговом окне «Новое имя» введите имя в поле «Имя» и скопируйте приведённую ниже формулу в поле «Ссылается на» (см. снимок экрана):

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

4. Затем нажмите «ОК» > «Закрыть», чтобы закрыть диалоговые окна. Перейдите на лист, где вы хотите разместить список имён всех листов, и введите следующую формулу в пустую ячейку:

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

Примечание: в приведённой выше формуле «Sheetnames» — это имя ячейки, которое вы создали на шаге 3.

5. Затем протяните маркер заполнения вниз до появления пустых ячеек. Теперь все имена листов из текущей книги отображаются, как показано на снимке экрана ниже:

6. Чтобы создать гиперссылку для каждого листа, используйте следующую формулу:

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

Примечание: в приведённой выше формуле «A2» — это ячейка, содержащая имя листа, а «A1» — ячейка, на которую переместится курсор после перехода по ссылке. Например, при нажатии на гиперссылку вы сразу окажетесь в ячейке A1 соответствующего листа.

7. Теперь, нажав на текст гиперссылки, вы перейдёте на соответствующий лист — см. демонстрацию ниже:

Получение списка всех Имя листа из книги с помощью Kutools для Excel

С помощью функции «Создать связующую таблицу» из надстройки «Kutools для Excel» вы сможете одним щелчком создать список имён листов и добавить гиперссылки для быстрого перехода к каждому из них.

1. Нажмите «KUTOOLS PLUS» > «Лист» > «Создать связующую таблицу» (см. снимок экрана):

2. В диалоговом окне «Создать связующую таблицу» укажите следующие параметры:

(1.) Выберите понравившийся «Стиль»: вы можете создать имя листа с гиперссылками или с кнопками макросов. (2.) Укажите имя листа для оглавления. (3.) Укажите, где разместить оглавление рабочих листов. (4.) Укажите, сколько столбцов вы хотите использовать на новом листе для отображения имени листа.

3. После завершения настройки нажмите «ОК». Теперь все имена листов отображаются со ссылками в новом листе книги «Текущая книга». См. снимок экрана:

Имя листа с гиперссылками	Имя листа с кнопками макросов

Получение списка всех Имя листа из книги с помощью кода VBA

1. Удерживая клавиши «ALT» + «F11», откройте окно Microsoft Visual Basic for Applications.

2. Нажмите «Вставка» > «Модуль» и вставьте приведённый ниже макрос в окно модуля.

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», чтобы запустить макрос. Все имена листов из текущей книги теперь отображаются на новом листе с именем «Index» и содержат гиперссылки для перехода к соответствующим листам. См. снимок экрана ниже:

Связанные статьи