Як створити зміст для всіх аркушів і автоматично оновлювати?
Припустимо, що у вас є робоча книга, яка містить сотні робочих аркушів, перехід до певного аркуша серед кількох аркушів може викликати головний біль у більшості з нас. У цьому випадку створення змісту для цих робочих аркушів може допомогти вам швидко та легко перейти до певного аркуша. У цьому підручнику буде розказано про те, як створити зміст для всіх аркушів і автоматично оновити таблицю під час вставки, видалення або зміни імен аркушів.
Створіть зміст для всіх аркушів і автоматично оновлюйте формули
Створюйте зміст для всіх аркушів і автоматично оновлюйте його за допомогою коду VBA
Створюйте зміст для всіх аркушів і автоматично оновлюйте його за допомогою потужної функції
Створіть зміст для всіх аркушів і автоматично оновлюйте формули
В Excel ви можете створити зміст, визначивши ім’я діапазону та посилаючись на ім’я у формулі, виконайте такі дії:
1. Вставте новий аркуш перед усіма аркушами, на яких потрібно створити зміст, і перейменуйте його за потреби.
2. Потім натисніть кнопку Formula > Визначте ім’я, див. скріншот:
3, в Нове ім'я діалоговому вікні вкажіть назву Список аркушів в ІМ'Я ви можете змінити його на власний, а потім ввести цю формулу: = ОТРИМАТИ РОБОЧУ КНИГУ (1) & T (ЗАРАЗ ()) в Відноситься до текстове поле, див. знімок екрана:
4. Потім натисніть кнопку OK , щоб закрити діалогове вікно.
5. Тепер перейдіть до нового аркуша, де ви хочете створити зміст, а потім введіть наступну формулу в порожню клітинку:
6. Потім перетягніть маркер заповнення вниз до комірок, доки не відобразяться порожні комірки, а всі назви аркушів (включаючи приховані аркуші) поточної книги не будуть перераховані, як показано на знімку екрана нижче:
7. Потім ви повинні створити гіперпосилання для вмісту таблиці, скористайтеся наведеною нижче формулою:
8. Тепер, клацнувши текст гіперпосилання, він одразу перейде на цей аркуш. І якщо ви вставите новий аркуш, видалите аркуш або зміните назву аркуша, вміст таблиці оновиться автоматично.
- 1. За допомогою цього методу всі приховані аркуші також відображаються у вмісті таблиці.
- 2. Ви повинні зберегти файл як Книга Excel з підтримкою макросів Таким чином, коли ви наступного разу відкриєте цей файл, формули працюватимуть добре.
Створюйте зміст для всіх аркушів і автоматично оновлюйте його за допомогою коду 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 для Excel, його Панель навігації може допомогти вам перерахувати всі назви аркушів на лівій панелі по вертикалі та швидко й легко переходити до певного аркуша.
після установки Kutools для Excel, будь ласка, зробіть так:
1. Натисніть Кутулс > навігація, див. скріншот:
2. У розширеному навігація панель, клацніть Робоча книга та аркуш усі відкриті книги відображаються у верхньому списку, а всі видимі аркуші в книзі відображаються в нижньому списку, див. знімок екрана:
3. Тепер ви можете перейти до аркуша, просто клацнувши назву аркуша на лівій панелі. А коли ви видаляєте, вставляєте або перейменовуєте аркуш, список аркушів на панелі оновлюватиметься динамічно.
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
