Como criar um sumário para todas as planilhas e atualizações automaticamente?
Supondo que você tenha uma pasta de trabalho que contém centenas de planilhas, navegar para uma planilha específica entre várias planilhas pode causar dor de cabeça para a maioria de nós. Nesse caso, criar um sumário para essas planilhas pode ajudá-lo a acessar a determinada planilha de maneira rápida e fácil. Este tutorial falará sobre como criar um índice para todas as planilhas e atualizar a tabela automaticamente ao inserir, excluir ou alterar os nomes das planilhas.
Crie um índice para todas as planilhas e atualize automaticamente com fórmulas
Crie um índice para todas as planilhas e atualizações automaticamente com um código VBA
Crie um índice para todas as planilhas e atualizações automaticamente com um recurso poderoso
Crie um índice para todas as planilhas e atualize automaticamente com fórmulas
No Excel, você pode criar um índice definindo um nome de intervalo e referenciando o nome em uma fórmula, siga as etapas a seguir:
1. Insira uma nova planilha antes de todas as planilhas nas quais deseja criar o sumário e renomeie-o conforme necessário.
2. Então clique Fórmula > Definir o Nome, veja a captura de tela:
3. No Novo nome caixa de diálogo, especifique um nome Lista de folhas no Nome caixa, você pode alterá-lo para o seu próprio e, em seguida, insira esta fórmula: = GET.WORKBOOK (1) & T (NOW ()) no Refere-se a caixa de texto, veja a captura de tela:
4. Então clique OK para fechar a caixa de diálogo.
5. Agora, vá para a nova planilha onde você deseja criar um índice e insira a seguinte fórmula em uma célula em branco:
6. E, em seguida, arraste a alça de preenchimento para as células até que as células em branco sejam exibidas e todos os nomes de planilhas (incluindo as planilhas ocultas) da pasta de trabalho atual tenham sido listados conforme a captura de tela abaixo:
7. Em seguida, você deve criar hiperlinks para o conteúdo da tabela, use a fórmula abaixo:
8. Agora, ao clicar no texto do hiperlink, ele irá para essa planilha de uma só vez. E se você inserir uma nova planilha, excluir uma planilha ou alterar o nome da planilha, o conteúdo da tabela será atualizado automaticamente.
- 1. Com este método, todas as planilhas ocultas também são exibidas no conteúdo da tabela.
- 2. Você deve salvar o arquivo como Pasta de trabalho habilitada para macro do Excel formato, desta forma, quando você abrir este arquivo na próxima vez, as fórmulas podem funcionar bem.
Crie um índice para todas as planilhas e atualizações automaticamente com um código VBA
Às vezes, você não precisa exibir as planilhas ocultas no conteúdo da tabela. Para resolver esse problema, o código VBA a seguir pode lhe fazer um favor.
1. Insira uma nova planilha antes de todas as planilhas nas quais deseja criar o sumário e renomeie-o conforme necessário. Em seguida, clique com o botão direito do mouse na guia da planilha e escolha Ver código no menu de contexto, veja a captura de tela:
2. No aberto Microsoft Visual Basic para Aplicações janela, copie e cole o código abaixo na janela de código da planilha:
Código VBA: crie um índice para todas as planilhas e atualizações automaticamente
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. Então aperte F5 key para executar este código, e o conteúdo da tabela é criado de uma só vez na nova planilha, todas as planilhas ocultas não serão exibidas no conteúdo da tabela, veja a captura de tela:
4. Form agora, ao excluir, inserir ou renomear planilha, o conteúdo da tabela será atualizado dinamicamente.
Crie um índice para todas as planilhas e atualizações automaticamente com um recurso poderoso
Se você tem Kutools for Excel, as TIC Painel de navegação pode ajudá-lo a listar todos os nomes de planilhas em um painel esquerdo verticalmente e navegar até a planilha específica de maneira rápida e fácil.
Depois de instalar Kutools for Excel, por favor, faça o seguinte:
1. Clique Kutools > Navegação, veja a captura de tela:
2. No expandido Navegação painel, clique em Livro e Folha de Trabalho ícone, todas as pastas de trabalho abertas são listadas na caixa de listagem superior e todas as planilhas visíveis na pasta de trabalho são exibidas na caixa de listagem inferior, veja a captura de tela:
3. E agora, você pode ir para a planilha apenas clicando no nome da planilha no painel esquerdo. E quando você excluir, inserir ou renomear uma planilha, a lista de planilhas no painel será atualizada dinamicamente.
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
