Innhold
Hvis du i det minste er kjent med funksjonen VPR (VISNING) (hvis ikke, så kjør først her), så bør du forstå at denne og andre funksjoner som ligner på den (VIS, INDEKS og SØK, VELG, etc.) alltid gir som et resultat verdi – nummeret, teksten eller datoen vi ser etter i den gitte tabellen.
Men hva om vi, i stedet for en verdi, ønsker å få en direkte hyperkobling, ved å klikke på som vi umiddelbart kan hoppe til det funnet samsvaret i en annen tabell for å se på det i en generell sammenheng?
La oss si at vi har et stort bestillingsbord for våre kunder som input. For enkelhets skyld (selv om dette ikke er nødvendig), konverterte jeg tabellen til en dynamisk "smart" tastatursnarvei Ctrl+T og ga på fanen Constructor (Design) hennes navn tabOrdre:
På et eget ark konsolidert Jeg bygde en pivottabell (selv om det ikke trenger å være akkurat en pivottabell – enhver tabell passer i prinsippet), der salgsdynamikken etter måneder for hver klient beregnes i henhold til de første dataene:
La oss legge til en kolonne i ordretabellen med en formel som slår opp navnet på kunden for gjeldende ordre på arket konsolidert. Til dette bruker vi den klassiske samlingen av funksjoner INDEX (INDEKS) и MER UTSATT (KAMP):
La oss nå pakke formelen inn i en funksjon CELLE (CELLE), som vi vil be om å vise adressen til den funnet cellen:
Og til slutt setter vi alt som har blitt til en funksjon HYPERLINK (HYPERLINK), som i Microsoft Excel kan lage en live hyperkobling til en gitt sti (adresse). Det eneste som ikke er åpenbart er at du må lime hash-tegnet (#) i begynnelsen til den mottatte adressen slik at lenken blir korrekt oppfattet av Excel som intern (fra ark til ark):
Nå, når du klikker på en av koblingene, vil vi umiddelbart hoppe til cellen med navnet på selskapet på arket med pivottabellen.
For å gjøre det veldig bra, la oss forbedre formelen vår litt slik at overgangen ikke skjer til klientens navn, men til en spesifikk numerisk verdi nøyaktig i månedskolonnen da den tilsvarende ordren ble fullført. For å gjøre dette, må vi huske at funksjonen INDEX (INDEKS) i Excel er svært allsidig og kan blant annet brukes i formatet:
=INDEKS( XNUMXD_range; Linjenummer; Kolonne_nummer )
Det vil si, som det første argumentet kan vi ikke spesifisere kolonnen med navnene på selskaper i pivoten, men hele dataområdet i pivottabellen, og som det tredje argumentet, legge til nummeret på kolonnen vi trenger. Det kan enkelt beregnes av funksjonen MÅNED (MÅNED), som returnerer månedsnummeret for avtaledatoen:
Forbedring 2. Vakkert lenkesymbol
Andre funksjonsargument HYPERLINK – teksten som vises i en celle med en lenke – kan gjøres penere hvis du bruker ikke-standardtegn fra Windings, Webdings-fonter og lignende i stedet for de banale tegnene “>>”. Til dette kan du bruke funksjonen SYMBOL (CHAR), som kan vise tegn etter kode.
Så for eksempel vil tegnkode 56 i Webdings-fonten gi oss en fin dobbel pil for en hyperkobling:
Forbedring 3. Marker gjeldende rad og aktiv celle
Vel, for skjønnhetens endelige seier over sunn fornuft, kan du også legge ved filen vår en forenklet versjon av utheving av gjeldende linje og cellen som vi følger lenken til. Dette vil kreve en enkel makro, som vi vil henge for å håndtere valgendringshendelsen på arket konsolidert.
For å gjøre dette, høyreklikk på arkfanen Sammendrag og velg kommandoen Se kode (Utsikt kode). Lim inn følgende kode i Visual Basic-redigeringsvinduet som åpnes:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Endre størrelse(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Som du lett kan se, fjerner vi her først fyllet fra hele arket, og fyller deretter hele linjen i sammendraget med gult (fargekode 6), og deretter oransje (kode 44) med gjeldende celle.
Nå, når en celle inne i sammendragscellen er valgt (det spiller ingen rolle – manuelt eller som et resultat av å klikke på hyperlenken vår), vil hele raden og cellen med måneden vi trenger bli uthevet:
Skjønnhet 🙂
PS Bare husk å lagre filen i et makroaktivert format (xlsm eller xlsb).
- Opprette eksterne og interne koblinger med HYPERLINK-funksjonen
- Opprette e-poster med HYPERLINK-funksjonen