Jak vytvořit obsah pro všechny listy a aktualizace automaticky?
Předpokládejme, že máte sešit, který obsahuje stovky listů, a navigace na konkrétní list mezi více listy může většině z nás způsobit bolest hlavy. V tomto případě vám vytvoření obsahu pro tyto listy může pomoci rychle a snadno přejít na určitý list. Tento tutoriál bude hovořit o tom, jak vytvořit obsah pro všechny listy a automaticky aktualizovat tabulku při vkládání, mazání nebo změně názvů listů.
Vytvořte obsah pro všechny listy a aktualizujte jej automaticky pomocí vzorců
Vytvořte obsah pro všechny listy a automaticky se aktualizujte pomocí kódu VBA
Vytvořte obsah pro všechny listy a automaticky se aktualizujte pomocí výkonné funkce
Vytvořte obsah pro všechny listy a aktualizujte jej automaticky pomocí vzorců
V Excelu můžete vytvořit obsah definováním názvu rozsahu a odkazem na název ve vzorci, proveďte prosím následující kroky:
1. Vložte nový list před všechny listy, kde chcete vytvořit obsah, a přejmenujte jej podle potřeby.
2. Potom klepněte na tlačítko Vzorec > Definujte jméno, viz screenshot:
3. V Nové jméno dialogovém okně zadejte název List do Příjmení pole, můžete jej změnit na vlastní a poté zadat tento vzorec: = GET.WORKBOOK (1) & T (HNED ()) do Odkazuje na textové pole, viz screenshot:
4. Potom klepněte na tlačítko OK pro zavření dialogového okna.
5. Nyní přejděte na nový list, kde chcete vytvořit obsah, a do prázdné buňky zadejte následující vzorec:
6. A pak přetáhněte úchyt výplně dolů k buňkám, dokud se nezobrazí prázdné buňky a všechny názvy listů (včetně skrytých listů) aktuálního sešitu budou uvedeny jako níže uvedený snímek obrazovky:
7. Poté byste měli vytvořit hypertextové odkazy na obsah tabulky, použijte prosím níže uvedený vzorec:
8. Nyní, když kliknete na text hypertextového odkazu, přejde na daný list najednou. A pokud vložíte nový list, odstraníte list nebo změníte název listu, obsah tabulky se automaticky aktualizuje.
- 1. Touto metodou se všechny skryté listy zobrazí také v obsahu tabulky.
- 2. Soubor byste měli uložit jako Sešit se zvýšeným makra Excel formát, tímto způsobem při příštím otevření tohoto souboru mohou vzorce fungovat dobře.
Vytvořte obsah pro všechny listy a automaticky se aktualizujte pomocí kódu VBA
Někdy není nutné zobrazit skryté listy v obsahu tabulky. Chcete-li tento problém vyřešit, může vám pomoci následující kód VBA.
1. Vložte nový list před všechny listy, kde chcete vytvořit obsah, a přejmenujte jej podle potřeby. Poté klikněte pravým tlačítkem na záložku listu a vyberte Zobrazit kód z kontextové nabídky viz screenshot:
2. V otevřeném Microsoft Visual Basic pro aplikace zkopírujte a vložte níže uvedený kód do okna kódu listu:
Kód VBA: Automaticky vytvořte obsah pro všechny listy a aktualizace
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. Poté stiskněte tlačítko F5 klíč ke spuštění tohoto kódu a obsah tabulky se vytvoří najednou v novém listu, všechny skryté listy se v obsahu tabulky nezobrazí, viz snímek obrazovky:
4. Formulář je nyní zapnutý, při mazání, vkládání nebo přejmenování listu se obsah tabulky dynamicky aktualizuje.
Vytvořte obsah pro všechny listy a automaticky se aktualizujte pomocí výkonné funkce
Pokud máte Kutools pro Excel, to je Navigační panel vám pomůže uvést všechny názvy listů do levého panelu svisle a rychle a snadno přejít na konkrétní list.
Po instalaci Kutools pro Excel, udělejte prosím toto:
1. klikněte Kutools > Navigace, viz screenshot:
2. V rozšířeném Navigace podokno, klepněte na tlačítko Sešit a list všechny otevřené sešity jsou uvedeny v horním seznamu a všechny viditelné listy v sešitu jsou zobrazeny v dolním seznamu, viz snímek obrazovky:
3. A nyní můžete přejít na list pouhým kliknutím na název listu v levém panelu. A když odstraníte, vložíte nebo přejmenujete list, seznam listů v podokně se dynamicky aktualizuje.
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
