Sadržaj
Ako ste barem upoznati s funkcijom VPR (VLOOKUP) (ako ne, onda prvo pokrenite ovdje), tada biste trebali razumjeti da ova i druge funkcije slične njoj (VIEW, INDEX i SEARCH, SELECT, itd.) uvijek daju kao rezultat vrijednost – broj, tekst ili datum koji tražimo u datoj tablici.
Ali što ako, umjesto vrijednosti, želimo dobiti živu hipervezu, klikom na koju možemo trenutno skočiti na pronađeno podudaranje u drugoj tablici da ga pogledamo u općem kontekstu?
Recimo da imamo veliku tablicu narudžbi za naše kupce kao ulaz. Radi praktičnosti (iako to nije potrebno), pretvorio sam tablicu u dinamički "pametni" tipkovnički prečac Ctrl+T i dao na tab Konstruktor (Oblikovati) njeno ime tabNarudžbe:
Na posebnom listu Konsolidirani Napravio sam zaokretnu tablicu (iako to ne mora biti baš zaokretna tablica – načelno je svaka tablica prikladna), gdje se prema početnim podacima izračunava dinamika prodaje po mjesecima za svakog klijenta:
Dodajmo stupac u tablicu narudžbi s formulom koja traži ime kupca za trenutnu narudžbu na listu Konsolidirani. Za to koristimo klasičnu hrpu funkcija INDEKS (INDEKS) и EKSPONIRANIJI (UTAKMICA):
Sada upakirajmo našu formulu u funkciju CELL (ĆELIJA), od kojeg ćemo tražiti da prikaže adresu pronađene ćelije:
I na kraju, sve što je ispalo stavljamo u funkciju HIPERLINK (HIPERVEZA), koji u Microsoft Excelu može stvoriti živu hipervezu na zadanu putanju (adresu). Jedina stvar koja nije očita je da ćete morati zalijepiti znak hash (#) na početku na primljenu adresu kako bi Excel ispravno percipirao link kao interni (od lista do lista):
Sada, kada kliknete na bilo koji od linkova, odmah ćemo skočiti na ćeliju s nazivom tvrtke na listu sa pivot tablicom.
Kako bi bila stvarno dobra, malo poboljšajmo našu formulu tako da se prijelaz ne dogodi na ime klijenta, već na određenu brojčanu vrijednost točno u stupcu mjeseca kada je odgovarajuća narudžba završena. Da bismo to učinili, moramo zapamtiti da funkcija INDEKS (INDEKS) u Excelu je vrlo svestran i može se koristiti, između ostalog, u formatu:
=INDEX( XNUMXD_raspon; Broj_linije; Broj_stupca )
Odnosno, kao prvi argument možemo navesti ne stupac s nazivima tvrtki u zaokretnoj tablici, već cijelo područje podataka zaokretne tablice, a kao treći argument dodati broj stupca koji nam je potreban. Može se lako izračunati pomoću funkcije MJESEC (MJESEC), koji vraća broj mjeseca za datum dogovora:
Poboljšanje 2. Prekrasan simbol veze
Drugi argument funkcije HIPERLINK – tekst koji se prikazuje u ćeliji s vezom – možete uljepšati ako umjesto banalnih znakova “>>” koristite nestandardne znakove iz Windingsa, Webdings fontova i slično. Za to možete koristiti funkciju SIMBOL (CHAR), koji može prikazati znakove prema njihovom kodu.
Tako će nam, na primjer, kod znaka 56 u fontu Webdings dati lijepu dvostruku strelicu za hipervezu:
Poboljšanje 3. Označite trenutni redak i aktivnu ćeliju
Pa, za konačnu pobjedu ljepote nad zdravim razumom, našoj datoteci možete priložiti i pojednostavljenu verziju isticanja trenutne linije i ćelije na koju slijedimo vezu. To će zahtijevati jednostavnu makronaredbu, koju ćemo objesiti za obradu događaja promjene odabira na listu Konsolidirani.
Da biste to učinili, desnom tipkom miša kliknite karticu Sažetak lista i odaberite naredbu Pogled kod (Pogled kodirati). Zalijepite sljedeći kod u prozor uređivača Visual Basica koji se otvara:
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
Kao što možete lako vidjeti, ovdje prvo uklanjamo ispunu s cijelog lista, a zatim cijeli redak u sažetku ispunjavamo žutom (kod boje 6), a zatim narančastom (kod 44) s trenutnom ćelijom.
Sada, kada se odabere bilo koja ćelija unutar ćelije sažetka (nije bitno - ručno ili kao rezultat klika na našu hipervezu), cijeli redak i ćelija s mjesecom koji nam je potreban bit će istaknuti:
Ljepota 🙂
PS Samo ne zaboravite spremiti datoteku u formatu s omogućenim makronaredbama (xlsm ili xlsb).
- Izrada vanjskih i unutarnjih poveznica pomoću HYPERLINK funkcije
- Izrada e-pošte s HYPERLINK funkcijom