Como criar um sumário para todas as planilhas e atualizá-lo automaticamente?
Suponha que você tenha uma pasta de trabalho que contenha centenas de planilhas. Navegar até uma planilha específica entre várias pode ser um desafio para a maioria de nós. Nesse caso, criar um sumário para essas planilhas pode ajudá-lo a acessar rapidamente e facilmente a planilha desejada. Este tutorial abordará como criar um sumário para todas as planilhas e atualizar a tabela automaticamente ao inserir, excluir ou alterar os nomes das planilhas.
Use fórmulas para criar um sumário para todas as planilhas e atualizá-lo automaticamente.
Use código VBA para criar um sumário para todas as planilhas e atualizá-lo automaticamente.
Use fórmulas para criar um sumário para todas as planilhas e atualizá-lo automaticamente.
No Excel, você pode criar um sumário definindo um nome de intervalo e referenciando esse nome em uma fórmula. Siga os passos abaixo:
1. Insira uma nova planilha antes de todas as outras onde deseja criar o sumário e renomeie-a conforme necessário.
2. Em seguida, clique em "Fórmula" > "Definir Nome", veja a captura de tela:
3. Na caixa de diálogo "Novo Nome", especifique um nome "Sheetlist" no campo "Nome", você pode alterá-lo conforme desejar, e depois insira a seguinte fórmula no campo "Refere-se a":
=GET.WORKBOOK(1)&T(NOW())
4. Depois, clique no botão "OK" para fechar a caixa de diálogo.
5. Agora, vá para a nova planilha onde deseja criar o sumário e insira a seguinte fórmula em uma célula em branco:
=IFERROR(INDEX(MID(Sheetlist,FIND("]",Sheetlist)+1,255),ROWS($A$2:A2)),"")6. E arraste a alça de preenchimento para baixo até que células em branco sejam exibidas, e todos os nomes das planilhas (incluindo as planilhas ocultas) da pasta de trabalho atual serão listados, como mostrado na captura de tela abaixo:
7. Em seguida, você deve criar hiperlinks para o conteúdo da tabela. Por favor, use a fórmula abaixo:
=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")
8. Agora, ao clicar no texto do hyperlink, você será direcionado imediatamente para essa planilha. 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 no formato "Pasta de Trabalho Habilitada para Macro do Excel", dessa forma, quando abrir o arquivo novamente, as fórmulas funcionarão corretamente.
Use o Kutools para Excel para criar um sumário para todas as planilhas e atualizá-lo automaticamente.
Se você tiver o "Kutools para Excel", seu "Painel de Navegação" pode ajudá-lo a listar todos os nomes das planilhas em um painel à esquerda verticalmente e navegar até a planilha específica rapidamente e facilmente.
Após instalar o Kutools para Excel, faça o seguinte:
1. Clique em "Kutools" > "Navegação", veja a captura de tela:
2. No painel "Navegação" expandido, clique no ícone "Pasta de Trabalho e Planilha", todas as pastas de trabalho abertas são listadas na caixa de lista superior, e todas as planilhas visíveis dentro da pasta de trabalho são exibidas na caixa de lista inferior, veja a captura de tela:
3. E agora, você pode ir para a planilha apenas clicando no nome da planilha no painel à esquerda. E quando você exclui, insere ou renomeia uma planilha, a lista de planilhas no painel será atualizada dinamicamente.
Use código VBA para criar um sumário para todas as planilhas e atualizá-lo automaticamente.
Às vezes, você não precisa exibir as planilhas ocultas no conteúdo da tabela. Para resolver esse problema, o seguinte código VBA pode ajudá-lo.
1. Insira uma nova planilha antes de todas as outras onde deseja criar o sumário e renomeie-a conforme necessário. Em seguida, clique com o botão direito do mouse na guia da planilha e escolha "Visualizar Código" no menu de contexto, veja a captura de tela:
2. Na janela aberta "Microsoft Visual Basic for Applications", copie e cole o código abaixo na janela de código da planilha:
Código VBA: Criar um sumário para todas as planilhas e atualizá-lo 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. Depois, pressione a tecla "F5" para executar este código, e o conteúdo da tabela será criado imediatamente na nova planilha, todas as planilhas ocultas não serão exibidas no conteúdo da tabela, veja a captura de tela:
4. A partir de agora, ao excluir, inserir ou renomear uma planilha, o conteúdo da tabela será atualizado dinamicamente.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins