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

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

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