Inhoud
Als je in ieder geval bekend bent met de functie VPR (VERT.ZOEKEN) (zo niet, voer dan eerst hier uit), dan moet u begrijpen dat deze en andere soortgelijke functies (VIEW, INDEX en SEARCH, SELECT, etc.) altijd als resultaat geven waarde – het nummer, de tekst of de datum die we zoeken in de gegeven tabel.
Maar wat als we, in plaats van een waarde, een live hyperlink willen krijgen, door erop te klikken waarmee we onmiddellijk naar de gevonden overeenkomst in een andere tabel kunnen springen om deze in een algemene context te bekijken?
Laten we zeggen dat we een grote besteltabel hebben voor onze klanten als input. Voor het gemak (hoewel dit niet nodig is), heb ik de tabel geconverteerd naar een dynamische "slimme" sneltoets Ctrl+T en gaf op de tab Constructeur (Ontwerp) haar naam tabBestellingen:
Op een apart blad Geconsolideerde Ik heb een draaitabel gebouwd (hoewel het niet precies een draaitabel hoeft te zijn - in principe is elke tabel geschikt), waar, volgens de initiële gegevens, de verkoopdynamiek per maand voor elke klant wordt berekend:
Laten we een kolom aan de besteltabel toevoegen met een formule die de naam van de klant voor de huidige bestelling op het blad opzoekt Geconsolideerde. Hiervoor gebruiken we de klassieke reeks functies INDEX (INHOUDSOPGAVE) и MEER BLOOTGESTELD (WEDSTRIJD):
Laten we nu onze formule in een functie verpakken CELL (CEL), die we zullen vragen om het adres van de gevonden cel weer te geven:
En tot slot zetten we alles wat is geworden in een functie HYPERLINK (HYPERLINK), die in Microsoft Excel een live hyperlink naar een bepaald pad (adres) kan maken. Het enige dat niet voor de hand ligt, is dat je het hekje (#) aan het begin op het ontvangen adres moet plakken, zodat de link door Excel correct als intern wordt ervaren (van blad tot blad):
Wanneer u nu op een van de links klikt, springen we onmiddellijk naar de cel met de naam van het bedrijf op het blad met de draaitabel.
Om het echt goed te maken, laten we onze formule iets verbeteren, zodat de overgang niet naar de naam van de klant plaatsvindt, maar naar een specifieke numerieke waarde precies in de maandkolom wanneer de bijbehorende bestelling is voltooid. Om dit te doen, moeten we onthouden dat de functie INDEX (INHOUDSOPGAVE) in Excel is zeer veelzijdig en onder andere te gebruiken in het formaat:
=INDEX( XNUMXD_bereik; Regelnummer; Kolomnummer )
Dat wil zeggen, als eerste argument kunnen we niet de kolom specificeren met de namen van bedrijven in de spil, maar het volledige gegevensgebied van de draaitabel, en als derde argument het nummer van de kolom toevoegen die we nodig hebben. Het kan eenvoudig worden berekend door de functie MAAND (MAAND), die het maandnummer voor de dealdatum retourneert:
Verbetering 2. Mooi linksymbool
Tweede functieargument HYPERLINK – de tekst die wordt weergegeven in een cel met een link – kan mooier worden gemaakt als je niet-standaard tekens uit Windings, Webdings-lettertypen en dergelijke gebruikt in plaats van de banale tekens ">>". Hiervoor kunt u de functie SYMBOOL (TEKEN), die tekens kan weergeven op basis van hun code.
Dus, bijvoorbeeld, tekencode 56 in het Webdings-lettertype geeft ons een mooie dubbele pijl voor een hyperlink:
Verbetering 3. Markeer huidige rij en actieve cel
Welnu, voor de uiteindelijke overwinning van schoonheid op gezond verstand, kun je ook een vereenvoudigde versie aan ons bestand toevoegen van het markeren van de huidige regel en de cel waarnaar we de link volgen. Dit vereist een eenvoudige macro, die we zullen ophangen om de selectiewijzigingsgebeurtenis op het blad af te handelen Geconsolideerde.
Klik hiervoor met de rechtermuisknop op de bladtab Samenvatting en selecteer de opdracht Bekijk code (Visie code). Plak de volgende code in het geopende Visual Basic-editorvenster:
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
Zoals je gemakkelijk kunt zien, verwijderen we hier eerst de vulling van het hele blad en vullen dan de hele regel in de samenvatting met geel (kleurcode 6) en vervolgens oranje (code 44) met de huidige cel.
Wanneer nu een cel in de samenvattingscel is geselecteerd (het maakt niet uit - handmatig of als gevolg van het klikken op onze hyperlink), wordt de hele rij en cel met de maand die we nodig hebben gemarkeerd:
Schoonheid
PS Vergeet niet om het bestand op te slaan in een macro-enabled formaat (xlsm of xlsb).
- Externe en interne links maken met de HYPERLINK-functie
- E-mails maken met de HYPERLINK-functie