Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як створити динамічний список назв робочих аркушів у Excel?

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

Створіть динамічний список імен робочих аркушів за допомогою функції «Визначити ім’я та формулу»

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

Створіть динамічний список назв робочих аркушів за допомогою Kutools для Excel гарна ідея3

Відображення динамічного списку імен робочих аркушів за допомогою Kutools для Excel гарна ідея3


1. Виділіть комірку на чистому аркуші, тут я вибираю А1, а потім клацну Формули > Визначте ім’я. див. скріншот:
doc-dynamic-worksheetname-list-1

2. Потім у Нове ім'я діалог введіть простирадла в Назва текстове поле (ви можете змінити, як вам потрібно), і введіть цю формулу = ЗАМІСТ (GET.WORKBOOK (1), "[" & GET.WORKBOOK (16) & "]", "")) в Посилається на текстове поле. Дивіться знімок екрана:
doc-dynamic-worksheetname-list-2

3. клацання OK. Перейдіть до вибраної комірки (А1) і введіть цю формулу = ІНДЕКС (Аркуші, РЯДКИ ($ A $ 1: $ A1)) (A1 - клітинка, яку ви вводите у цю формулу, «Таблиці» - це ім’я, яке ви визначили на кроці 2), а потім перетягніть маркер автозаповнення вниз до #REF! з'являється.
doc-dynamic-worksheetname-list-3

Чайові: Якщо є вилучені або додані робочі аркуші, вам потрібно перейти до A1і натиснути клавішу Enter, а потім знову клацнути ручку автозаповнення.
doc-dynamic-worksheetname-list-4


Якщо ви хочете створити динамічний список назв робочих аркушів, які можуть посилатися на кожен аркуш, ви можете використовувати код VBA.

1. Створіть новий аркуш і перейменуйте його як Індекс. Дивіться знімок екрана:

doc-dynamic-worksheetname-list-5       doc-dynamic-worksheetname-list-6

2. Клацніть правою кнопкою миші на назві аркуша Index, виберіть Переглянути код з контекстного меню. Дивіться знімок екрана:
doc-dynamic-worksheetname-list-7

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. клацання прогін or F5 для запуску VBA тепер створюється динамічний список імен робочих аркушів.
doc-dynamic-worksheetname-list-8

Порада:

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

2. Ви можете натиснути на назву аркуша у списку імен, щоб перейти до аркуша.

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


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

Kutools для Excel, з більш ніж 300 зручні функції, полегшує вам роботу. 

після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:

1. клацання Kutools Plus > Робочий аркуш > Створіть список назв аркушів. Дивіться знімок екрана:

doc динамічна назва робочого аркуша 17

2 В Створіть список назв аркушів діалогове вікно:

 doc список динамічних аркушів 2
(1) Перевірте стиль індексу, який вам потрібен Стилі індексу аркушів розділ;
(2) Назвіть новий аркуш покажчика із введенням даних Вкажіть назву аркуша для Індексу аркуша текстове вікно;
(3) Вкажіть місце, куди ви помістите доданий аркуш покажчика в Вставте індекс аркуша в Список;
(4) Якщо ви хочете відобразити назви аркушів в одному списку, виберіть 1 стовпець в Відображення індексу аркуша в список

3. клацання Ok. Тепер ви бачите, як перелічені назви аркушів.
doc kutools створити список аркушів 3

Порада:

1. Ви можете натиснути на назву аркуша, щоб швидко перейти до початкового аркуша.

2. Назви списків або аркушів не можуть динамічно змінюватися при вставленні або видаленні аркушів.

3. Насправді ви також можете створити список кнопок, щоб зв'язати відповідний аркуш, просто потрібно перевірити Містить кнопки та макроси у діалоговому вікні. див. знімок:
doc kutools створити список аркушів 4

Клацніть тут, щоб дізнатись більше про створення списку назв аркушів.


Якщо ви з Kutools для Excel, Ви також можете використовувати навігація утиліта для відображення пов’язаних назв робочого аркуша на панелі

після безкоштовна установка Kutools для Excel, будь-ласка, виконайте наведені нижче дії:

1. клацання Кутулс > навігація. Клацання Робоча книга та аркуш щоб показати книгу та аркуш, і ви можете вибрати книгу, тоді її аркуші відображатимуться у навігація панелі. Дивіться знімок екрана:
панель навігації doc 1   

Порада:

Коли є аркуші, видалені або додані, ви можете натиснути кнопка оновлення панель навігації doc 2 в Navigation панель для оновлення імен робочого аркуша.

Клацніть тут для отримання додаткової інформації про навігацію.


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Kelly · 3 years ago
    Thanks for this helpful info. I created a dynamic list with VBA code in the Index tab. I found that it only updates when I click back to the Index tab, though. Is there a way to ensure it auto updates as soon as a new tab is created? I refer to the tab names elsewhere in the workbook and others will be using it so I'm trying to make it seamless. Thank you!
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      Sorry, I do not find that code can finish your job.
  • To post as a guest, your comment is unpublished.
    Ed Bower · 3 years ago
    Or you can simply rightClick either the left or right tab-horizontal-scroll arrow at the bottomLeft, and then click the worksheet name (from the simple vertical list) that you want to jump to.
  • To post as a guest, your comment is unpublished.
    PB · 3 years ago
    Just what I needed and worked perfectly. Thank you!
  • To post as a guest, your comment is unpublished.
    KP · 4 years ago
    Defining the name "Sheets" in the define name DOES NOT WORK. The "refers to" box will not save the formula as typed and converts it to a text string.
  • To post as a guest, your comment is unpublished.
    j4h54n84 · 4 years ago
    how can I use this code to create the list of worksheets in horizontal order instead of vertical? is it possible?
  • To post as a guest, your comment is unpublished.
    Cindy · 4 years ago
    Thank you. It is very useful. :D
  • To post as a guest, your comment is unpublished.
    Nash · 5 years ago
    How can i remove the links to each sheets and how to not include the "sheet1" and "Index" sheet?

    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
  • To post as a guest, your comment is unpublished.
    Matt · 5 years ago
    How would I go about editing this code to include a checkbox to the left of the link that I could use to hide rows on a cover sheet? Also how could I insert a row on the worksheets below A1 to have the return to index link show up there?