sadržaj
Ako ste barem upoznati s funkcijom VPR (Vlookup) (ako ne, onda prvo pokrenite ovdje), onda biste trebali shvatiti 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 tabeli.
Ali šta ako, umjesto vrijednosti, želimo dobiti hipervezu uživo, klikom na koju bismo mogli odmah skočiti na pronađeno podudaranje u drugoj tabeli da ga pogledamo u opštem kontekstu?
Recimo da imamo veliku tabelu narudžbi za naše kupce kao ulaz. Radi praktičnosti (iako to nije neophodno), pretvorio sam tabelu u dinamičku „pametnu“ prečicu na tastaturi Ctrl+T i dao na račun konstruktor (Dizajn) njeno ime tabOrders:
Na posebnom listu Konsolidovani Napravio sam stožernu tabelu (iako ne mora da bude baš pivot tabela – svaka tabela je u principu prikladna), gde se, prema početnim podacima, izračunava dinamika prodaje po mesecima za svakog klijenta:
Dodajmo kolonu u tablicu narudžbi s formulom koja traži ime kupca za trenutnu narudžbu na listu Konsolidovani. Za to koristimo klasičnu gomilu funkcija INDEX (INDEX) и IZLOŽENIJI (UTAKMICA):
Sada umotajmo našu formulu u funkciju CELL (ĆELIJA), od kojeg ćemo tražiti da prikaže adresu pronađene ćelije:
I konačno, sve što je ispalo stavimo u funkciju HYPERLINK (HIPERLINK), koji u Microsoft Excel-u može kreirati živu hipervezu na datu putanju (adresu). Jedino što nije očito je da ćete na primljenu adresu morati zalijepiti znak hash (#) na početku kako bi Excel ispravno percipirao vezu kao internu (od lista do lista):
Sada, kada kliknete na bilo koju od veza, odmah ćemo skočiti na ćeliju s nazivom kompanije na listu sa pivot tablicom.
Da bude stvarno dobro, hajde da malo poboljšamo našu formulu tako da se prijelaz ne dogodi na ime klijenta, već na određenu brojčanu vrijednost tačno u koloni mjeseca kada je odgovarajući nalog završen. Da bismo to učinili, moramo zapamtiti da je funkcija INDEX (INDEX) u Excelu je vrlo svestran i može se koristiti, između ostalog, u formatu:
=INDEX( XNUMXD_range; Line_number; Broj_kolone )
Odnosno, kao prvi argument možemo navesti ne kolonu s imenima kompanija u pivot-u, već cijelo područje podataka pivot tablice, a kao treći argument dodati broj kolone koja nam je potrebna. Može se lako izračunati pomoću funkcije MONTH (MJESEC), koji vraća broj mjeseca za datum ugovora:
Poboljšanje 2. Prekrasan simbol veze
Drugi argument funkcije HYPERLINK – tekst koji se prikazuje u ćeliji sa vezom – može se uljepšati ako umjesto banalnih znakova “>>” koristite nestandardne znakove iz Windings, Webdings fontova i slično. Za to možete koristiti funkciju SIMBOL (CHAR), koji može prikazati znakove po njihovom kodu.
Tako će nam, na primjer, kod znakova 56 u fontu Webdings dati lijepu dvostruku strelicu za hipervezu:
Poboljšanje 3. Označite trenutni red i aktivnu ćeliju
Pa, za konačnu pobjedu ljepote nad zdravim razumom, možete u naš fajl priložiti i pojednostavljenu verziju isticanja trenutne linije i ćelije do koje slijedimo link. Ovo će zahtijevati jednostavan makro, koji ćemo objesiti za rukovanje događajem promjene odabira na listu Konsolidovani.
Da biste to uradili, kliknite desnim tasterom miša na karticu lista Rezime i izaberite komandu pogled kod (Pogled kod). Zalijepite sljedeći kod u prozor uređivača Visual Basica koji se otvara:
Privatni pod radni list_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44
Kao što možete lako vidjeti, ovdje prvo uklanjamo ispunu iz cijelog lista, a zatim cijeli red u sažetku popunjavamo žutom (kod boje 6), a zatim narandžastom (šifra 44) trenutnom ćelijom.
Sada, kada se izabere bilo koja ćelija unutar ćelije sa sažetkom (nije bitno – ručno ili kao rezultat klika na našu hipervezu), cijeli red i ćelija s mjesecom koji nam je potreban bit će istaknuti:
Ljepota 🙂
PS Samo zapamtite da sačuvate datoteku u formatu sa omogućenim makroima (xlsm ili xlsb).
- Kreiranje eksternih i internih veza sa funkcijom HYPERLINK
- Kreiranje e-pošte sa funkcijom HYPERLINK