Как создать динамический список имен листов в Excel?

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

Создайте динамический список имен рабочих листов с помощью Определить имя и формулу

Создайте динамический список имен рабочих листов с кодом VBA

Создайте динамический список имен рабочих листов с помощью Kutools for Excel

Отображение динамического списка имен рабочих листов с помощью Kutools for Excel

Создайте динамический список имен рабочих листов с помощью Определить имя и формулу

1. Выберите ячейку на пустом листе, здесь я выбираю A1, а затем нажимаю Формулы > Определить имя. см. снимок экрана:

2. Затем в Новое имя диалог, тип Листы в ФИО текстовое поле (вы можете изменить по своему усмотрению) и введите эту формулу = ПОДСТАВИТЬ (GET.WORKBOOK (1), "[" & GET.WORKBOOK (16) & "]", "") в текстовое поле Ссылается на. Смотрите скриншот:

3. Нажмите OK. Перейдите к выбранной ячейке (A1) и введите эту формулу = ИНДЕКС (Листы; СТРОКИ ($ A $ 1: $ A1)) (A1 - это ячейка, в которую вы вводите эту формулу, «Таблицы» - это имя, которое вы определили на шаге 2), затем перетащите дескриптор автозаполнения вниз до #REF! появляется.

Функции: Если есть удаленные или добавленные листы, вам нужно перейти к ячейке 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 Sub

4. Нажмите Run or F5 для запуска VBA теперь создается динамический список имен рабочих листов.

Наконечник:

1. Когда рабочий лист книги удаляется или вставляется, список имен рабочих листов автоматически изменяется.

2. Вы можете щелкнуть имя листа в списке имен, чтобы перейти на лист.

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

Создайте динамический список имен рабочих листов с помощью Kutools for Excel

 Если вы хотите только быстро перечислить все имена рабочих листов книги и связать их с исходными листами, вы можете использовать Kutools for ExcelАвтора Создать список имен листов.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Нажмите Кутулс Плюс > Рабочий лист > Создать список имен листов. Смотрите скриншот:

2. в Создать список имен листов диалог:

 

(1) Проверьте нужный стиль индекса в Стили указателя листов раздел;
(2) Назовите новый индексный лист, введя данные в Укажите имя листа для указателя листов текстовое окно;
(3) Укажите место, в которое вы поместите добавленную индексную страницу в Вставьте указатель листа в список;
(4) Если вы хотите отобразить имена листов в одном списке, выберите 1 столбец в Отображение индекса листа в .

3. Нажмите Ok. Теперь вы можете увидеть список имен листов.

Советы:

1. Вы можете щелкнуть имя листа, чтобы быстро перейти к исходному листу.

2. Список или имена листов не могут динамически изменяться при вставке или удалении листов.

3. На самом деле, вы также можете создать список кнопок для ссылок на соответствующие листы, просто нужно проверить Содержит кнопки и макросы в диалоге. см. снимок экрана:

Щелкните здесь, чтобы узнать больше о создании списка имен листов.

  Создать интерактивный список имен листов

Отображение динамического списка имен рабочих листов с помощью Kutools for Excel

Если вы с Kutools for Excel, вы также можете использовать Навигация утилита для отображения имен связанных листов на панели

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Нажмите Кутулс > Навигация. Нажмите Рабочая тетрадь и лист чтобы отобразить книгу и рабочий лист, и вы можете выбрать книгу, тогда ее рабочие листы будут отображаться в Навигация панель. Смотрите скриншот:
   

Наконечник:

Когда рабочие листы удалены или добавлены, вы можете щелкнуть кнопка обновления  в Navigation, чтобы обновить имена рабочих листов.

Щелкните здесь для получения дополнительной информации о навигации.

  Панель навигации - листы списка