Hipervincles dinàmics entre taules

Si almenys esteu familiaritzat amb la funció VPR (VISUALITZACIÓ) (si no, primer executeu aquí), aleshores hauríeu d'entendre que aquesta i altres funcions similars a ella (VISUALITZAR, INDEX i CERCAR, SELECCIONAR, etc.) sempre donen com a resultat valor – el número, text o data que busquem a la taula donada.

Però, què passa si, en comptes d'un valor, volem obtenir un hiperenllaç en directe, fent clic sobre el qual podríem saltar instantàniament a la coincidència trobada en una altra taula per mirar-la en un context general?

Suposem que tenim una gran taula de comandes per als nostres clients com a entrada. Per comoditat (tot i que això no és necessari), vaig convertir la taula en una drecera de teclat "intel·ligent" dinàmica Ctrl+T i va donar a la pestanya constructor (Disseny) el seu nom pestanyaComandes:

En un full a part Consolidat Vaig construir una taula dinàmica (tot i que no ha de ser precisament una taula dinàmica, qualsevol taula és adequada en principi), on, segons les dades inicials, es calcula la dinàmica de vendes per mesos per a cada client:

Afegim una columna a la taula de comandes amb una fórmula que cerqui el nom del client per a la comanda actual al full Consolidat. Per a això utilitzem el conjunt clàssic de funcions ÍNDEX (ÍNDEX) и MÉS EXPOSADA (PARTIDA):

Ara anem a embolicar la nostra fórmula en una funció CEL·LULAR (CEL·LULA), al qual demanarem que mostri l'adreça de la cel·la trobada:

I finalment, posem tot el que ha resultat en funció HIPERENLLAÇ (HIPERENLLAÇ), que a Microsoft Excel pot crear un hiperenllaç en directe a una ruta determinada (adreça). L'únic que no és obvi és que hauràs d'enganxar el signe hash (#) al principi a l'adreça rebuda perquè l'enllaç sigui percebut correctament per Excel com a intern (de full a full):

Ara, quan feu clic a qualsevol dels enllaços, instantàniament saltarem a la cel·la amb el nom de l'empresa al full amb la taula dinàmica.

Millora 1. Navegueu a la columna desitjada

Perquè sigui realment bo, millorem lleugerament la nostra fórmula perquè la transició no es produeixi al nom del client, sinó a un valor numèric concret exactament a la columna del mes quan es va completar la comanda corresponent. Per fer-ho, hem de recordar que la funció ÍNDEX (ÍNDEX) en Excel és molt versàtil i es pot utilitzar, entre altres coses, en el format:

=ÍNDEX( Interval_XNUMXD; Número_línia; Número_columna )

És a dir, com a primer argument, podem especificar no la columna amb els noms de les empreses en el pivot, sinó tota l'àrea de dades de la taula dinàmica, i com a tercer argument, afegir el número de la columna que necessitem. Es pot calcular fàcilment mitjançant la funció MES (MES), que retorna el número del mes per a la data de l'acord:

Millora 2. Símbol d'enllaç preciós

Segon argument de funció HIPERENLLAÇ – el text que es mostra en una cel·la amb un enllaç – es pot fer més bonic si utilitzeu caràcters no estàndard de tipus de lletra Windings, Webdings i similars en lloc dels signes banals “>>”. Per a això podeu utilitzar la funció SÍMBOL (CHAR), que pot mostrar caràcters pel seu codi.

Així, per exemple, el codi de caràcter 56 a la font Webdings ens donarà una bona fletxa doble per a un hiperenllaç:

Millora 3. Ressalteu la fila actual i la cel·la activa

Bé, per a la victòria final de la bellesa sobre el sentit comú, també podeu adjuntar al nostre fitxer una versió simplificada de ressaltar la línia actual i la cel·la a la qual seguim l'enllaç. Això requerirà una macro senzilla, que penjarem per gestionar l'esdeveniment de canvi de selecció al full Consolidat.

Per fer-ho, feu clic amb el botó dret a la pestanya Resum del full i seleccioneu l'ordre Veure comanda codi (Vista codi). Enganxeu el codi següent a la finestra de l'editor de Visual Basic que s'obre:

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  

Com podeu veure fàcilment, aquí primer traiem el farciment de tot el full, i després omplim tota la línia del resum amb groc (codi de color 6) i després taronja (codi 44) amb la cel·la actual.

Ara, quan se selecciona qualsevol cel·la dins de la cel·la de resum (no importa, manualment o com a resultat de fer clic al nostre hiperenllaç), es ressaltarà tota la fila i la cel·la amb el mes que necessitem:

Bellesa 🙂

PS Només recordeu desar el fitxer en un format habilitat per a macros (xlsm o xlsb).

• Creació d'enllaços externs i interns amb la funció HYPERLINK
• Creació de correus electrònics amb la funció HYPERLINK