Contenido
Si al menos está familiarizado con la función VPR (VLOOKUP) (si no, primero ejecute aquí), entonces debe comprender que esta y otras funciones similares (VER, ÍNDICE y BÚSQUEDA, SELECCIONAR, etc.) siempre dan como resultado propuesta de – el número, texto o fecha que estamos buscando en la tabla dada.
Pero, ¿qué pasa si, en lugar de un valor, queremos obtener un hipervínculo en vivo, al hacer clic en el cual podríamos saltar instantáneamente a la coincidencia encontrada en otra tabla para verla en un contexto general?
Digamos que tenemos una tabla de pedidos grande para nuestros clientes como entrada. Por conveniencia (aunque esto no es necesario), convertí la tabla en un atajo de teclado dinámico "inteligente" Ctrl+T y dio en la ficha Constructor (Diseño) su nombre pestañaPedidos:
En una hoja aparte Consolidado Construí una tabla dinámica (aunque no tiene por qué ser exactamente una tabla dinámica, cualquier tabla es adecuada en principio), donde, según los datos iniciales, se calcula la dinámica de ventas por meses para cada cliente:
Agreguemos una columna a la tabla de pedidos con una fórmula que busque el nombre del cliente para el pedido actual en la hoja Consolidado. Para esto usamos el conjunto clásico de funciones ÍNDICE (ÍNDICE) и MÁS EXPUESTO (JUEGO):
Ahora envolvamos nuestra fórmula en una función. CÉLULA (CÉLULA), al que le pediremos que muestre la dirección de la celda encontrada:
Y finalmente, ponemos todo lo que ha resultado en una función. HYPERLINK (HIPERVÍNCULO), que en Microsoft Excel puede crear un hipervínculo activo a una ruta determinada (dirección). Lo único que no es obvio es que tendrás que pegar el signo de almohadilla (#) al principio a la dirección recibida para que Excel perciba correctamente el enlace como interno (de hoja en hoja):
Ahora, cuando haga clic en cualquiera de los enlaces, saltaremos instantáneamente a la celda con el nombre de la empresa en la hoja con la tabla dinámica.
Para que sea realmente bueno, mejoremos ligeramente nuestra fórmula para que la transición no ocurra en el nombre del cliente, sino en un valor numérico específico exactamente en la columna del mes cuando se completó el pedido correspondiente. Para ello, debemos recordar que la función ÍNDICE (ÍNDICE) en Excel es muy versátil y se puede utilizar, entre otras cosas, en el formato:
= ÍNDICE ( XNUMXD_rango; Número de línea; número_columna )
Es decir, como primer argumento, podemos especificar no la columna con los nombres de las empresas en el pivote, sino toda el área de datos de la tabla dinámica, y como tercer argumento, agregar el número de la columna que necesitamos. Se puede calcular fácilmente con la función MES (MES), que devuelve el número de mes para la fecha de la oferta:
Mejora 2. Hermoso símbolo de enlace
Argumento de la segunda función HYPERLINK – el texto que se muestra en una celda con un enlace – se puede hacer más bonito si usa caracteres no estándar de Windings, fuentes Webdings y similares en lugar de los signos banales “>>”. Para esto puedes usar la función SYMBOL (CARBONIZARSE), que puede mostrar caracteres por su código.
Entonces, por ejemplo, el código de carácter 56 en la fuente Webdings nos dará una bonita flecha doble para un hipervínculo:
Mejora 3. Resalte la fila actual y la celda activa
Bueno, para la victoria final de la belleza sobre el sentido común, también puede adjuntar a nuestro archivo una versión simplificada de resaltar la línea actual y la celda a la que seguimos el enlace. Esto requerirá una macro simple, que colgaremos para manejar el evento de cambio de selección en la hoja Consolidado.
Para hacer esto, haga clic derecho en la pestaña Resumen de la hoja y seleccione el comando Ver código (Ver código). Pegue el siguiente código en la ventana del editor de Visual Basic que se abre:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Como puede ver fácilmente, aquí primero eliminamos el relleno de toda la hoja y luego llenamos toda la línea en el resumen con amarillo (código de color 6) y luego con naranja (código 44) con la celda actual.
Ahora, cuando se selecciona cualquier celda dentro de la celda de resumen (no importa, manualmente o como resultado de hacer clic en nuestro hipervínculo), se resaltará toda la fila y la celda con el mes que necesitamos:
Belleza 🙂
PS Solo recuerde guardar el archivo en un formato habilitado para macros (xlsm o xlsb).
- Creación de enlaces externos e internos con la función HIPERENLACE
- Creación de correos electrónicos con la función HIPERVINCULO