¿Cómo crear una tabla de contenido para todas las hojas y actualizaciones automáticamente?
Supongamos que tiene un libro de trabajo que contiene cientos de hojas de trabajo, navegar a una hoja específica entre varias hojas puede causar dolor de cabeza para la mayoría de nosotros. En este caso, crear una tabla de contenido para estas hojas de trabajo puede ayudarlo a ir a la hoja determinada de manera rápida y sencilla. Este tutorial hablará sobre cómo crear una tabla de contenido para todas las hojas y actualizar la tabla automáticamente al insertar, eliminar o cambiar los nombres de las hojas también.
Cree una tabla de contenido para todas las hojas y actualice automáticamente con fórmulas
Cree una tabla de contenido para todas las hojas y actualice automáticamente con un código VBA
Cree una tabla de contenido para todas las hojas y actualice automáticamente con fórmulas
En Excel, puede crear una tabla de contenido definiendo un nombre de rango y haciendo referencia al nombre en una fórmula, siga los siguientes pasos:
1. Inserte una nueva hoja antes de todas las hojas en las que desea crear la tabla de contenido y cámbiele el nombre según lo necesite.
2. Luego, haz clic Fórmula > Definir nombre, ver captura de pantalla:
3. En la Nuevo nombre cuadro de diálogo, especifique un nombre Lista de hojas en el Nombre cuadro, puede cambiarlo por el suyo propio y luego ingresar esta fórmula: = OBTENER LIBRO DE TRABAJO (1) & T (AHORA ()) en el Se refiere a cuadro de texto, ver captura de pantalla:
4. Luego, haz clic OK para cerrar el cuadro de diálogo.
5. Ahora, vaya a la nueva hoja donde desea crear una tabla de contenido y luego ingrese la siguiente fórmula en una celda en blanco:
6. Y luego arrastre el controlador de relleno hacia las celdas hasta que se muestren las celdas en blanco, y todos los nombres de las hojas (incluidas las hojas ocultas) del libro de trabajo actual se enumeran como se muestra a continuación:
7. Luego, debe crear hipervínculos para el contenido de la tabla, use la siguiente fórmula:
8. Ahora, al hacer clic en el texto del hipervínculo, irá a esa hoja de inmediato. Y si inserta una nueva hoja, elimina una hoja o cambia el nombre de la hoja, el contenido de la tabla se actualizará automáticamente.
- 1. Con este método, todas las hojas ocultas también se muestran en el contenido de la tabla.
- 2. Debe guardar el archivo como Libro de Excel habilitado para macros formato, de esta manera, cuando abra este archivo la próxima vez, las fórmulas pueden funcionar bien.
Cree una tabla de contenido para todas las hojas y actualice automáticamente con un código VBA
A veces, no necesita mostrar las hojas ocultas en el contenido de la tabla. Para resolver este problema, el siguiente código VBA puede hacerte un favor.
1. Inserte una nueva hoja antes de todas las hojas en las que desea crear la tabla de contenido y cámbiele el nombre según lo necesite. Luego, haga clic con el botón derecho en la pestaña de la hoja y elija Ver código en el menú contextual, vea la captura de pantalla:
2. En el abierto Microsoft Visual Basic para aplicaciones ventana, copie y pegue el siguiente código en la ventana de código de hoja:
Código VBA: cree una tabla de contenido para todas las hojas y se actualice automáticamente
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. Entonces presione F5 clave para ejecutar este código, y el contenido de la tabla se crea de inmediato en la nueva hoja de trabajo, todas las hojas ocultas no se mostrarán en el contenido de la tabla, vea la captura de pantalla:
4. De ahora en adelante, al eliminar, insertar o renombrar una hoja, el contenido de la tabla se actualizará dinámicamente.
Cree una tabla de contenido para todas las hojas y actualice automáticamente con una función poderosa
Si tiene Kutools for Excel, su Panel de navegación puede ayudarlo a enumerar todos los nombres de las hojas en un panel izquierdo verticalmente y navegar a la hoja específica rápida y fácilmente.
Después de instalar Kutools for Excel, haz lo siguiente:
1. Haga Clic en Kutools > Navegación, ver captura de pantalla:
2. En el expandido Navegación Panel, haga clic Libro de trabajo y hoja icono, todos los libros de trabajo abiertos se enumeran en el cuadro de lista superior, y todas las hojas visibles dentro del libro de trabajo se muestran en el cuadro de lista inferior, vea la captura de pantalla:
3. Y ahora, puede ir a la hoja simplemente haciendo clic en el nombre de la hoja en el panel izquierdo. Y cuando elimine, inserte o cambie el nombre de una hoja, la lista de hojas en el panel se actualizará dinámicamente.
Las mejores herramientas de productividad de oficina
Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haga clic aquí para obtener la función que más necesita...
Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
- ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
