Как создать автоматически обновляемое оглавление для всех листов?
Предположим, у вас есть книга, содержащая сотни листов. Поиск нужного листа среди такого количества может вызвать затруднения у большинства пользователей. В такой ситуации создание оглавления поможет быстро и легко перейти к нужному листу. В данном руководстве рассказывается, как создать оглавление для всех листов и настроить его автоматическое обновление при вставке, удалении или переименовании листов.
Использование формулы для автоматического создания и обновления оглавления всех листов
Использование Kutools для Excel для автоматического создания и обновления оглавления всех листов
Использование кода VBA для автоматического создания и обновления оглавления всех листов
Использование формулы для автоматического создания и обновления оглавления всех листов
В Excel можно создать оглавление, задав имя ячейке и сославшись на это имя в формуле. Выполните следующие действия:
1. Вставьте новый лист перед всеми остальными — именно на нём будет создано оглавление — и задайте ему подходящее имя.
2. Затем выберите «Формулы» > «Присвоить имя» (см. снимок экрана):
3. В диалоговом окне «Новое имя» введите в поле «Имя» название «Sheetlist» (его можно заменить на любое другое по вашему выбору), а затем укажите следующую формулу в поле «Ссылка на»:
=GET.WORKBOOK(1)&T(NOW())
4. Нажмите кнопку «ОК», чтобы закрыть диалоговое окно.
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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек