Как автоматически создать оглавление для всех листов и обновлений?
Предположим, у вас есть рабочая книга, содержащая сотни рабочих листов, и переход к определенному листу среди нескольких листов может вызвать у большинства из нас головную боль. В этом случае создание оглавления для этих рабочих листов может помочь вам быстро и легко перейти к определенному листу. В этом руководстве будет рассказано о том, как создать оглавление для всех листов и автоматически обновлять таблицу при вставке, удалении или изменении имен листов.
Создайте оглавление для всех листов и автоматически обновляйте его с помощью формул
Автоматическое создание оглавления для всех листов и обновлений с помощью кода VBA.
Создавайте оглавление для всех листов и автоматически обновляйте их с помощью мощной функции.
Создайте оглавление для всех листов и автоматически обновляйте его с помощью формул
В Excel вы можете создать оглавление, указав имя диапазона и сославшись на имя в формуле. Для этого выполните следующие действия:
1. Вставьте новый лист перед всеми листами, на которых вы хотите создать оглавление, и переименуйте его, как вам нужно.
2, Затем нажмите Формула > Определить имя, см. снимок экрана:
3. В Новое имя диалоговое окно, укажите имя Список в Фамилия поле, вы можете изменить его на свое собственное, а затем ввести эту формулу: = ПОЛУЧИТЬ.РАБОТУ (1) & T (СЕЙЧАС ()) в Относится к текстовое поле, см. снимок экрана:
4, Затем нажмите OK чтобы закрыть диалоговое окно.
5. Теперь перейдите на новый лист, где вы хотите создать оглавление, а затем введите следующую формулу в пустую ячейку:
6. Затем перетащите дескриптор заполнения вниз к ячейкам, пока не отобразятся пустые ячейки, и все имена листов (включая скрытые листы) текущей книги будут перечислены, как показано ниже:
7. Затем вы должны создать гиперссылки для содержимого таблицы, используя следующую формулу:
8. Теперь при щелчке по тексту гиперссылки он сразу переходит на этот лист. И если вы вставите новый лист, удалите лист или измените имя листа, содержимое таблицы будет обновлено автоматически.
- 1. С помощью этого метода все скрытые листы также отображаются в содержимом таблицы.
- 2. Вы должны сохранить файл как Excel Macro-Enabled Workbook формат, таким образом, когда вы откроете этот файл в следующий раз, формулы будут хорошо работать.
Автоматическое создание оглавления для всех листов и обновлений с помощью кода VBA.
Иногда вам не нужно отображать скрытые листы в содержимом таблицы. Чтобы решить эту проблему, следующий код VBA может оказать вам услугу.
1. Вставьте новый лист перед всеми листами, на которых вы хотите создать оглавление, и переименуйте его, как вам нужно. Затем щелкните правой кнопкой мыши вкладку листа и выберите Просмотреть код из контекстного меню см. снимок экрана:
2. В открытом Microsoft Visual Basic для приложений скопируйте и вставьте приведенный ниже код в окно кода листа:
Код VBA: автоматическое создание оглавления для всех листов и обновлений
Private Sub Worksheet_Activate()
‘Updateby ExtendOffice
Dim xWsh As Worksheet
Dim xWshs As Worksheets
Dim xShowHinddenWorkSheet As Boolean
Dim xI As Long
Dim xRg As Range
Dim xStrTitle, xStrTCHeader, xStrWShName As String
xShowHinddenWorkSheet = False 'Change this to True to display the hidden sheets as you need
xStrTitle = "A1"
xStrTCHeader = "A3"
On Error Resume Next
Application.ScreenUpdating = False
Me.Cells.Clear
Me.Range(xStrTitle).Font.Bold = True
Me.Range(xStrTitle).Font.Size = Me.Range(xStrTitle).Font.Size + 2
Me.Range(xStrTitle).Value = "Table of Contents"
Me.Range(xStrTCHeader).Value = "No."
Me.Range(xStrTCHeader).Offset(0, 1).Value = "Sheet Name"
Me.Range(xStrTCHeader).Resize(1, 2).Font.Bold = True
xStrWShName = Me.Name
xI = 1
For Each xWsh In Application.ActiveWorkbook.Worksheets
If xWsh.Name <> xStrWShName Then
If (xWsh.Visible = xlSheetVisible) Or xShowHinddenWorkSheet Then
Me.Hyperlinks.Add Anchor:=Me.Range(xStrTCHeader).Offset(xI, 1), Address:="", SubAddress:="'" & xWsh.Name & "'!A1", TextToDisplay:=xWsh.Name
Me.Range(xStrTCHeader).Offset(xI).Value = xI
xI = xI + 1
End If
End If
Next
Application.ScreenUpdating = True
End Sub
3, Затем нажмите F5 ключ для запуска этого кода, и содержимое таблицы создается сразу на новом листе, все скрытые листы не будут отображаться в содержимом таблицы, см. снимок экрана:
4. Теперь при удалении, вставке или переименовании листа содержимое таблицы будет обновляться динамически.
Создавайте оглавление для всех листов и автоматически обновляйте их с помощью мощной функции.
Если вы только что Kutools for Excel, Его Панель навигации может помочь вам перечислить все имена листов на левой панели по вертикали и быстро и легко перейти к конкретному листу.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Кутулс > Навигация, см. снимок экрана:
2. В расширенном Навигация панели, нажмите Рабочая тетрадь и лист значок, все открытые книги перечислены в верхнем списке, а все видимые листы в книге отображаются в нижнем списке, см. снимок экрана:
3. И теперь вы можете перейти к листу, просто щелкнув имя листа на левой панели. А когда вы удаляете, вставляете или переименовываете лист, список листов на панели будет динамически обновляться.
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
