Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Войти
x
or
x
x
Регистрация
x

or

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

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

Создайте динамический список имен рабочих мест с именем Define Name и Formula

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

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

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


Область переходов - Workbook & Sheet (просмотреть все открытые книги в списке и все листы
текущей книги являются списками на панели, вы можете щелкнуть имя листа, чтобы переключиться на нее.)

лист списка навигации doc
Вкладка «Office» Включите редактирование и просмотр с вкладками в Office и упростите свою работу ...
Kutools для Excel добавляет расширенные функции 300 в Excel и повышает производительность на 80%
  • Супер Формула Бар (легко редактировать несколько строк текста и формул); Макет чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Содержание сплит-клеток; Объедините дублирующиеся строки и сумму / среднее... предотвратить повторяющиеся клетки; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выберите пустые строки (все ячейки пусты); Супер найти и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения формулы ссылки; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Любимые и быстро вставляемые формулы, Диапазоны, графики и рисунки; Шифрование ячеек с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечение текстаДобавить текст, Удалить по позиции, Удалить пространство; Создание и печать промежуточных итогов подкачки; Преобразование содержимого ячеек и комментариев...
  • Суперфильтр (сохранить и применить схемы фильтров к другим листам); Расширенный поиск по месяцам / неделям / дням, частоте и более; Специальный фильтр жирным шрифтом, курсивом ...
  • Объединить рабочие тетради и рабочие листы; Объединение таблиц на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Kutools работает с Office 2007-2019 и 365. Он поддерживает все языки и очень прост в установке и развертывании. Полнофункциональная 60-дневная бесплатная пробная версия.


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

формула Никогда не нужно беспокоиться о длинных формулах в Excel больше! Kutools for Excel's Авто текст может добавлять все формулы в группу
как автоматический текст, и освободите свой мозг! Нажмите здесь, чтобы узнать Автотекст Нажмите здесь, чтобы получить бесплатную пробную версию

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

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

Чаевые: Если листы удалены или добавлены, вам нужно перейти в A1 и нажать клавишу Enter, а затем снова обработать ручку автозаполнения.
док-динамическая worksheetname-список-4


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

1. Создайте новый лист и переименуйте его как Index. Смотрите скриншот:

док-динамическая worksheetname-список-5 док-динамическая worksheetname-список-6

2. Щелкните правой кнопкой мыши на имени листа индекса, выберите Просмотреть код из контекстного меню. Смотрите скриншот:
док-динамическая worksheetname-список-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 теперь создается динамический список имен рабочих листов.
док-динамическая worksheetname-список-8

Наконечник:

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

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

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


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

Kutools for Excel, с более чем 120 удобные функции Excel, повышают эффективность работы и экономят ваше рабочее время.

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

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

doc динамическое имя рабочего листа 17

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

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

3. Нажмите Ok, Теперь вы можете увидеть имена листов, которые перечислены.
док-динамическая worksheetname-список-11

Советы:

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

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

3. Фактически, вы также можете создать список кнопок для ссылки на относительный лист, просто нужно проверить Содержит кнопки и макросы в диалоговом окне. см. screeshshot:
doc dynamic listheetname list 16

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


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

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

1. Нажмите Kutools > Навигация, Смотрите скриншот:
док-динамическая worksheetname-список-12

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

док-динамическая worksheetname-список-13 док-динамическая worksheetname-список-14

Наконечник:

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

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


Kutools для Excel - лучший инструмент для повышения производительности в офисе Повысьте производительность на 80%

  • Супер Формула Бар (легко редактировать несколько строк текста и формул); Макет чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Содержание сплит-клеток; Объедините дублирующиеся строки и сумму / среднее... предотвратить повторяющиеся клетки; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выберите пустые строки (все ячейки пусты); Супер найти и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения формулы ссылки; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Любимые и быстро вставляемые формулы, Диапазоны, графики и рисунки; Шифрование ячеек с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечение текстаДобавить текст, Удалить по позиции, Удалить пространство; Создание и печать промежуточных итогов подкачки; Преобразование содержимого ячеек и комментариев...
  • Суперфильтр (сохранить и применить схемы фильтров к другим листам); Расширенный поиск по месяцам / неделям / дням, частоте и более; Специальный фильтр жирным шрифтом, курсивом ...
  • Объединить рабочие тетради и рабочие листы; Объединение таблиц на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Работает с Office 2007-2019 и 365 и поддерживает все языки. Это легко развернуть в вашей компании. Полнофункциональная 60-дневная бесплатная пробная версия.
вкладка kte 201905

Вкладка «Office» предоставляет интерфейс с вкладками для Office и упрощает работу

  • Включить редактирование и чтение с вкладками в Word, Excel, PowerPoint, Издатель, Доступ, Visio и Проект.
  • Открывайте и создавайте несколько документов в новых вкладках одного и того же окна, а не в новых окнах.
  • Увеличивает вашу производительность на 50% и уменьшает сотни щелчков мышью для вас каждый день!
нижняя часть офиса
Say something here...
symbols left.
You are guest ( Sign Up? )
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 · 1 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 · 1 years ago
      Sorry, I do not find that code can finish your job.
  • To post as a guest, your comment is unpublished.
    Ed Bower · 1 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 · 1 years ago
    Just what I needed and worked perfectly. Thank you!
  • To post as a guest, your comment is unpublished.
    KP · 2 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 · 2 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 · 2 years ago
    Thank you. It is very useful. :D
  • To post as a guest, your comment is unpublished.
    Nash · 2 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 · 2 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?