Editor Top
Cuando una Microsoft Excel Workbook contiene muchas hojas, la pestaña de la hoja que necesita a menudo no está a la vista, lo que es molesto y lo ralentiza un poco. Hay muchas soluciones, pero una lista de nombres de hojas puede ser útil, especialmente si los usuarios no tienen conocimientos básicos de Excel. Una vez que tenga una lista de nombres de hojas, los usuarios pueden usarlos para navegar por el libro de trabajo. Una manera fácil de generar la lista de nombres de hojas está disponible a través de Microsoft Power Query.
En este tutorial, le mostraré cómo usar Power Query para generar una lista de nombres de hojas que luego puede cargar en Excel y usar para navegar por el libro de trabajo. La técnica de la hoja es interesante, pero el enfoque real es exponer los metadatos del libro de trabajo mediante Power Query.
VER: Google Workspace frente a Microsoft 365: un análisis en paralelo con lista de verificación (TechRepublic Premium)
Estoy usando microsoft 365 en un sistema Windows 10 de 64 bits, pero puede usar versiones anteriores de Excel hasta Excel 2010. Excel para la web no admite Power Query, aunque admite consultas y conexiones existentes.
Puede descargar el archivo de demostración de Microsoft Excel para este tutorial.
Cómo deshabilitar OneDrive
Antes de hacer nada, debe desactivar OneDrive. Si no lo hace, obtendrá un error de que otra persona está usando el libro de trabajo, por lo que no podrá acceder a él. El culpable es OneDrive.
Para deshabilitar OneDrive temporalmente, búsquelo en la barra de tareas o en el área adicional (Figura A). Haga clic en el ícono de la nube azul y luego haga clic en Configuración: el engranaje en la esquina superior derecha. En la lista resultante, elija Salir de OneDrive. Confirme la acción eligiendo Cerrar OneDrive cuando se le solicite. Si es un suscriptor de 365, está utilizando OneDrive, se dé cuenta o no.
Figura A
Esta es una situación temporal. Cuando haya terminado, querrá volver a habilitar OneDrive, lo cual le mostraré cómo hacerlo más adelante.
Cómo iniciar Power Query
Trabajaremos con un libro de Excel simple, que se muestra en Figura B, con seis hojas en blanco. Una de las hojas también tiene un rango con nombre, indicado por bordes. Para fines de demostración, no necesitamos ningún dato porque buscamos los metadatos del libro de trabajo, que incluyen los nombres de las hojas.
Figura B
Una vez que haya deshabilitado OneDrive, estará listo para iniciar Power Query:
1. Desde dentro del libro de Excel con todas las pestañas, haga clic en la pestaña Datos.
2. En el grupo Obtener y transformar datos, haga clic en el menú desplegable Obtener datos y elija Desde archivo.
3. A continuación, elija Desde libro de Excel.
4. Cuando se le solicite, busque y especifique el mismo libro de trabajo y luego haga clic en Importar. Aquí es donde te meterás en problemas si no desactivas OneDrive primero. Si esto le sucede, cierre todo y deshabilite OneDrive, como se muestra arriba.
5. En el panel resultante, seleccione cualquier hoja (Figura C). Solo necesitas uno. Observe que la lista también contiene un rango con nombre, SheetANamedRange. No lo usaremos, pero quería que vieras que este proceso de carga ofrece más que las hojas. También mostrará los nombres de las tablas.
Figura C
6. Haga clic en Transformar datos para iniciar Power Query.
Por ahora, Power Query parece vacío.
Cómo exponer los metadatos del archivo de Excel en Power Query
No hay datos en Power Query, pero eso es lo que queremos. Esto nos permite exponer los metadatos del archivo.
A la derecha, la lista de Pasos aplicados tiene tres pasos, pero debemos mantener solo el primero, que es Fuente. Elimine Navegación y Tipo modificado haciendo clic en la X a la izquierda de cada paso. Al hacerlo, se exponen metadatos sobre el libro de trabajo, como se muestra en Figura D.
Figura D
La columna Tipo muestra el tipo de elemento. Hay seis hojas y un nombre definido. Solo necesitamos las seis hojas, por lo que nuestro primer paso es filtrar la fila de nombre definida. Al aplicar esta técnica a sus propios datos, es posible que no tenga ningún nombre definido, pero le recomiendo que aplique un filtro de todos modos si planea reutilizar esta consulta. Cuando actualice la lista más tarde, es posible que tenga un rango con nombre o una tabla.
Para aplicar un filtro para mostrar solo las hojas, haga lo siguiente:
1. Haga clic en el menú desplegable de la columna Tipo.
2. En la lista resultante, desmarque todo excepto Hojas (Figura E).
Figura E
3. Haga clic en Aceptar para aplicar el filtro.
Figura F muestra los registros resultantes. No hay ninguna fila para el nombre definido. Al aplicar esto a su propio trabajo, los metadatos pueden tener otros elementos además de los nombres de las hojas. Asegúrese de filtrar todo menos las hojas.
Figura F
En este punto, debemos eliminar todas las columnas excepto la columna Nombre. Para hacerlo,
1. Haga clic con el botón derecho en el encabezado Nombre para seleccionar esa columna.
2. Seleccione Quitar otras columnas del submenú resultante.
Después de eliminar todo menos la columna de nombres de hojas, haga clic en Cargar y cerrar para cargar la lista en Excel. El resultado es una Tabla en una hoja nueva denominada Abr (2).
Cómo usar la lista para navegar hojas en Excel
La forma en que use la tabla de nombres de hojas depende de usted, pero crearemos una columna de hipervínculos para una navegación rápida. Para convertir los nombres de las hojas en hipervínculos, ingrese la siguiente función en B2 y copie en las celdas restantes, como se muestra en Figura G:
=HYPERLINK("[PQSheetHyperlinks_Demo.xlsx]" & A2 & "!A1",A2)
Figura G
Esta función requiere el nombre de la hoja actual, “[PQSheetHyperlinks_Demo.xlsx]”, incluso cuando se mueve dentro del mismo libro de trabajo. El componente & A2 & concatena el nombre de la hoja en A2. El componente “!A1” selecciona A1 de la hoja correspondiente. El último argumento, A2, especifica el texto mostrado, que en este caso es el nombre de la hoja.
Cómo actualizar la tabla de nombres de hojas en Excel
Cuando agregue, elimine o cambie el nombre de las hojas, deberá actualizar la lista. Primero, guarde el libro de trabajo. Luego, haga clic con el botón derecho en la lista y elija Actualizar en el submenú resultante.
Si tiene Microsoft 365 y OneDrive, debe volver a habilitar OneDrive antes de que esto funcione. La forma más sencilla de hacerlo es iniciar sesión en su cuenta de Microsoft 365 y hacer clic en Sincronizar en el menú.
Es un poco extraño que deba deshabilitar OneDrive para generar la lista y luego habilitarlo para actualizar la lista. Esto podría cambiar con cualquier actualización.
