Как да създадете автоматично съдържание за всички листове и актуализации?
Да предположим, че имате работна книга, която съдържа стотици работни листове, навигирането до конкретен лист сред множество листове може да причини главоболие на повечето от нас. В този случай създаването на съдържание за тези работни листове може да ви помогне да отидете бързо и лесно до конкретния лист. Този урок ще говори за това как да създадете таблица със съдържание за всички листове и да актуализирате таблицата автоматично при вмъкване, изтриване или промяна на имената на листове.
Създайте таблица със съдържание за всички листове и се актуализира автоматично с формули
Създайте таблица със съдържание за всички листове и се актуализира автоматично с VBA код
Създайте таблица със съдържание за всички листове и се актуализира автоматично с мощна функция
Създайте таблица със съдържание за всички листове и се актуализира автоматично с формули
В Excel можете да създадете съдържание, като дефинирате име на диапазон и посочите името във формула, моля, изпълнете следните стъпки:
1. Вмъкнете нов лист преди всички листове, където искате да създадете съдържанието, и го преименувайте според нуждите си.
2, След това кликнете върху Формула > Определете име, вижте екранна снимка:
3. В Ново име диалогов прозорец, задайте име Списък с листове в Име поле, можете да го промените на свой собствен и след това въведете тази формула: =GET.WORKBOOK(1)&T(NOW()) в Отнася се до текстово поле, вижте екранна снимка:
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. Кликнете Kutools > навигация, вижте екранна снимка:
2. В разширения навигация панел, щракнете върху Работна тетрадка и лист икона, всички отворени работни книги са изброени в горното списъчно поле, а всички видими листове в работната книга се показват в долното списъчно поле, вижте екранната снимка:
3. И сега можете да отидете до листа само като щракнете върху името на листа от левия прозорец. А когато изтриете, вмъкнете или преименувате лист, списъкът с листове в панела ще се актуализира динамично.
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
