Jak automatycznie utworzyć spis treści dla wszystkich arkuszy i aktualizacji?
Przypuśćmy, że masz skoroszyt zawierający setki arkuszy roboczych, a przejście do określonego arkusza wśród wielu arkuszy może powodować ból głowy dla większości z nas. W takim przypadku utworzenie spisu treści dla tych arkuszy może pomóc w szybkim i łatwym przejściu do danego arkusza. W tym samouczku omówimy, jak utworzyć spis treści dla wszystkich arkuszy i automatycznie aktualizować tabelę podczas wstawiania, usuwania lub zmieniania nazw arkuszy.
Utwórz spis treści dla wszystkich arkuszy i automatycznie aktualizuj za pomocą formuł
Utwórz spis treści dla wszystkich arkuszy i automatycznie aktualizuj za pomocą kodu VBA
Twórz spis treści dla wszystkich arkuszy i aktualizuj automatycznie dzięki zaawansowanej funkcji
Utwórz spis treści dla wszystkich arkuszy i automatycznie aktualizuj za pomocą formuł
W programie Excel możesz utworzyć spis treści, definiując nazwę zakresu i odwołując się do nazwy w formule, wykonaj następujące czynności:
1. Wstaw nowy arkusz przed wszystkimi arkuszami, w których chcesz utworzyć spis treści, i zmień jego nazwę zgodnie z potrzebami.
2. Następnie kliknij Formuła > Zdefiniuj nazwęzobacz zrzut ekranu:
3, w Nowe imie okno dialogowe, podaj nazwę Lista arkuszy do Imię pole, możesz zmienić je na własne, a następnie wprowadzić tę formułę: = GET.WORKBOOK (1) & T (NOW ()) do Odnosi się do pole tekstowe, patrz zrzut ekranu:
4. Następnie kliknij OK aby zamknąć okno dialogowe.
5. Teraz przejdź do nowego arkusza, w którym chcesz utworzyć spis treści, a następnie wprowadź następującą formułę w pustej komórce:
6. A następnie przeciągnij uchwyt wypełniania w dół do komórek, aż zostaną wyświetlone puste komórki, a wszystkie nazwy arkuszy (w tym ukryte arkusze) bieżącego skoroszytu zostaną wymienione, jak pokazano na poniższym zrzucie ekranu:
7. Następnie należy utworzyć hiperłącza do zawartości tabeli, skorzystaj z poniższego wzoru:
8. Teraz po kliknięciu tekstu hiperłącza od razu przejdzie on do tego arkusza. A jeśli wstawisz nowy arkusz, usuniesz arkusz lub zmienisz nazwę arkusza, zawartość tabeli zostanie automatycznie zaktualizowana.
- 1. Dzięki tej metodzie wszystkie ukryte arkusze są również wyświetlane w treści tabeli.
- 2. Powinieneś zapisać plik jako Skoroszyt Excel z włączoną obsługą makr format, w ten sposób, gdy następnym razem otworzysz ten plik, formuły mogą działać dobrze.
Utwórz spis treści dla wszystkich arkuszy i automatycznie aktualizuj za pomocą kodu VBA
Czasami nie musisz wyświetlać ukrytych arkuszy w zawartości tabeli. Aby rozwiązać ten problem, poniższy kod VBA może wyświadczyć ci przysługę.
1. Wstaw nowy arkusz przed wszystkimi arkuszami, w których chcesz utworzyć spis treści, i zmień jego nazwę zgodnie z potrzebami. Następnie kliknij prawym przyciskiem myszy kartę arkusza i wybierz Wyświetl kod z menu kontekstowego, zobacz zrzut ekranu:
2. W otwartym Microsoft Visual Basic for Applications okno, skopiuj i wklej poniższy kod do okna kodu arkusza:
Kod VBA: Utwórz spis treści dla wszystkich arkuszy i automatycznie aktualizuj
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. Następnie naciśnij F5 klucz do uruchomienia tego kodu, a zawartość tabeli zostanie utworzona od razu w nowym arkuszu, wszystkie ukryte arkusze nie będą wyświetlane w zawartości tabeli, patrz zrzut ekranu:
4. Formularz od teraz, przy usuwaniu, wstawianiu lub zmianie nazwy arkusza, zawartość tabeli będzie aktualizowana dynamicznie.
Twórz spis treści dla wszystkich arkuszy i aktualizuj automatycznie dzięki zaawansowanej funkcji
Jeśli Kutools dla programu Excel, jego Panel nawigacji może pomóc w wyświetlaniu wszystkich nazw arkuszy w lewym okienku w pionie i szybko i łatwo przejść do określonego arkusza.
Po zainstalowaniu Kutools dla programu Excel, zrób tak:
1. Kliknij Kutools > Nawigacjazobacz zrzut ekranu:
2. W rozszerzonym Nawigacja okienko, kliknij Skoroszyt i arkusz ikona, wszystkie otwarte skoroszyty są wymienione w górnym polu listy, a wszystkie widoczne arkusze w skoroszycie są wyświetlane w dolnym polu listy, patrz zrzut ekranu:
3. A teraz możesz przejść do arkusza, klikając nazwę arkusza w lewym okienku. A gdy usuniesz, wstawisz lub zmienisz nazwę arkusza, lista arkuszy w panelu będzie dynamicznie aktualizowana.
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
