Как создать оглавление для всех листов и обновлять его автоматически?
Предположим, у вас есть книга, содержащая сотни рабочих листов. Навигация по определенному листу среди множества может вызвать головную боль у большинства из нас. В этом случае создание оглавления для этих листов может помочь вам быстро и легко перейти к нужному листу. В этом руководстве мы расскажем, как создать оглавление для всех листов и автоматически обновлять таблицу при вставке, удалении или изменении названий листов.
Использовать формулу для создания оглавления для всех листов с автоматическим обновлением
Использовать Kutools для Excel для создания оглавления для всех листов с автоматическим обновлением
Использовать код VBA для создания оглавления для всех листов с автоматическим обновлением
Использовать формулу для создания оглавления для всех листов с автоматическим обновлением
В Excel вы можете создать оглавление, определив имя диапазона и сославшись на это имя в формуле. Пожалуйста, следуйте указанным ниже шагам:
1. Вставьте новый лист перед всеми листами, где вы хотите создать оглавление, и переименуйте его по своему усмотрению.
2. Затем нажмите "Формула" > "Определить имя", см. скриншот:
3. В диалоговом окне "Новое имя" укажите имя "Sheetlist" в поле "Имя", вы можете изменить его на свое, а затем введите следующую формулу в текстовое поле "Ссылается на":
=GET.WORKBOOK(1)&T(NOW())
4. Затем нажмите кнопку "OK", чтобы закрыть диалоговое окно.
5. Теперь перейдите на новый лист, где вы хотите создать оглавление, и введите следующую формулу в пустую ячейку:
=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")6. Затем перетащите маркер заполнения вниз до тех пор, пока не появятся пустые ячейки, и все названия листов (включая скрытые листы) текущей книги будут перечислены, как показано на скриншоте ниже:
7. Затем вам нужно создать гиперссылки для содержания таблицы. Пожалуйста, используйте следующую формулу:
=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")
8. Теперь, когда вы нажмете на текст гиперссылки, вы сразу перейдете на этот лист. И если вы вставите новый лист, удалите лист или измените название листа, содержание таблицы будет обновлено автоматически.
- 1. С помощью этого метода все скрытые листы также отображаются в содержании таблицы.
- 2. Вы должны сохранить файл в формате "Книга Excel с поддержкой макросов", чтобы при следующем открытии файла формулы работали корректно.
Использовать Kutools для Excel для создания оглавления для всех листов с автоматическим обновлением
Если у вас есть "Kutools для Excel", его "Панель навигации" поможет вам перечислить все названия листов в вертикальной панели слева и быстро и легко перейти к конкретному листу.
После установки Kutools для Excel сделайте следующее:
1. Нажмите "Kutools" > "Навигация", см. скриншот:
2. В расширенной панели "Навигация" нажмите значок "Книга и Лист", все открытые книги перечислены в верхнем списке, а все видимые листы внутри книги отображаются в нижнем списке, см. скриншот:
3. Теперь вы можете перейти на лист, просто щелкнув название листа в левой панели. А когда вы удаляете, вставляете или переименовываете лист, список листов в панели будет обновляться динамически.
Использовать код VBA для создания оглавления для всех листов с автоматическим обновлением
Иногда вам не нужно отображать скрытые листы в содержании таблицы. Чтобы решить эту проблему, следующий код VBA может помочь.
1. Вставьте новый лист перед всеми листами, где вы хотите создать оглавление, и переименуйте его по своему усмотрению. Затем щелкните правой кнопкой мыши вкладку листа и выберите "Просмотр кода" из контекстного меню, см. скриншот:
2. В открывшемся окне "Microsoft Visual Basic for Applications" скопируйте и вставьте приведенный ниже код в окно кода листа:
Код 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. С этого момента при удалении, вставке или переименовании листа содержание таблицы будет обновляться динамически.
Лучшие инструменты для повышения продуктивности в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек