Как создать динамический список с названиями листов в Excel?
Если в вашей рабочей книге несколько листов и вы хотите создать динамический список всех их названий на новом листе — как это сделать? В этом руководстве представлены несколько эффективных способов быстро решить эту задачу в Excel.
Создание динамического списка Имя листа с помощью «Присвоить имя» и формулы
Создание динамического списка Имя листа с помощью кода VBA
Создание динамического списка Имя листа с помощью Kutools для Excel 
Отображение динамического списка Имя листа с помощью Kutools для Excel
Создание динамического списка Имя листа с помощью «Присвоить имя» и формулы
1. Выберите ячейку на пустом листе (в данном случае — A1), затем нажмите Формулы > Присвоить имя. См. снимок экрана:
2. В открывшемся диалоговом окне Новое имя введите название Sheets в поле Имя (при необходимости вы можете изменить его) и введите следующую формулу =SUBSTITUTE(GET.WORKBOOK(1),«[»&,GET.WORKBOOK(16)&,«]»,«») в поле «Ссылка на». См. снимок экрана:
3. Нажмите ОК. Перейдите в выбранную ячейку (A1) и введите в неё формулу =INDEX(Sheets,ROWS($A$1:$A1)) (A1 — ячейка, в которую вы вводите формулу; «Sheets» — имя, заданное на шаге 2), затем перетащите маркер автозаполнения вниз до появления ошибки #ССЫЛ!.
Совет: Если листы добавляются или удаляются, снова перейдите в ячейку A1, нажмите клавишу Enter и повторно перетащите маркер автозаполнения.
Создание динамического списка Имя листа с помощью кода VBA
Если вы хотите создать динамический список имён листов с гиперссылками на каждый из них, воспользуйтесь кодом VBA.
1. Создайте новый лист и переименуйте его в «Index». См. снимок экрана:
2. Щелкните правой кнопкой мыши по имени листа Index и выберите в контекстном меню пункт Просмотреть код. См. снимок экрана:
3. В открывшемся окне скопируйте и вставьте приведённый ниже код VBA.
VBA: создание динамического списка с именами листов.
Private Sub Worksheet_Activate()
'Updateby20150305
Dim xSheet As Worksheet
Dim xRow As Integer
Dim calcState As Long
Dim scrUpdateState As Long
Application.ScreenUpdating = False
xRow = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With
For Each xSheet In Application.Worksheets
If xSheet.Name <> Me.Name Then
xRow = xRow + 1
With xSheet
.Range("A1").Name = "Start_" & xSheet.Index
.Hyperlinks.Add anchor: = .Range("A1"), Address: = "", _
SubAddress: = "Index", TextToDisplay: = "Back to Index"
End With
Me.Hyperlinks.Add anchor: = Me.Cells(xRow, 1), Address: = "", _
SubAddress: = "Start_" & xSheet.Index, TextToDisplay: = xSheet.Name
End If
Next
Application.ScreenUpdating = True
End Sub4. Нажмите Выполнить или F5, чтобы запустить макрос. Теперь создан динамический список «Имя листа».
Совет:
1. При удалении или добавлении листов в рабочей книге список «Имя листа» обновляется автоматически.
2. Вы можете щелкнуть название листа в списке «Список имен», чтобы перейти на соответствующий лист.
Если два описанных выше метода кажутся недостаточно удобными, обратите внимание на следующие два способа — они помогут решить эту задачу ещё проще.
Создание динамического списка Имя листа с помощью Kutools для Excel
Если вам нужно быстро получить список всех имён листов рабочей книги с гиперссылками на исходные листы, воспользуйтесь функцией Kutools для Excel — Создать связующую таблицу.
После бесплатной установкиKutools для Excel выполните следующие действия:
1. Нажмите KUTOOLS PLUS > Лист > Создать связующую таблицу. См. снимок экрана:
2. В диалоговом окне Создать связующую таблицу:
(2) Укажите имя нового индексного листа, введя данные в поле Имя связующей таблицы;
(3) Укажите место размещения добавляемого индексного листа в списке Расположение в книге;
(4) Если вы хотите отобразить названия листов в виде одного столбца, выберите значение «1 столбец» в списке Количество строк для объединения.
3. Нажмите ОК. Теперь вы увидите список названий листов.
Советы:
1. Щелкните по названию листа, чтобы мгновенно перейти на соответствующий исходный лист.
2. Список названий листов не обновляется автоматически при добавлении или удалении листов.
3. Кроме того, вы можете создать список кнопок для перехода на соответствующие листы — для этого достаточно установить флажок Создать кнопки и макросы в диалоговом окне. См. снимок экрана:
Нажмите здесь, чтобы узнать больше о функции «Создать связующую таблицу».
Создание кликабельного списка названий листов
Отображение динамического списка Имя листа с помощью Kutools для Excel
Если у вас установлено Kutools для Excel, вы также можете воспользоваться утилитой Навигация, чтобы отобразить кликабельные Имя листа на панели
После бесплатной установкиKutools для Excel выполните следующие действия:
1. Нажмите Kutools > Навигация. Затем выберите Рабочая книга и Лист, чтобы отобразить структуру рабочей книги и её листов. Выберите нужную рабочую книгу — и её листы появятся на панели Навигация. См. снимок экрана:
Совет:
После добавления или удаления листов нажмите кнопку 
на панели Навигация, чтобы обновить список имён листов.
Нажмите здесь, чтобы узнать больше о функции «Навигация».
Навигация — список листов
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек