Obsah
Ak ste aspoň oboznámení s funkciou VPR (VLOOKUP) (ak nie, najprv spustite tu), potom by ste mali pochopiť, že táto a ďalšie jej podobné funkcie (VIEW, INDEX a SEARCH, SELECT atď.) vždy poskytujú výsledok hodnotu – číslo, text alebo dátum, ktorý hľadáme v danej tabuľke.
Čo ak však namiesto hodnoty chceme získať živý hypertextový odkaz, kliknutím na ktorý by sme mohli okamžite prejsť na nájdenú zhodu v inej tabuľke a pozrieť sa na ňu vo všeobecnom kontexte?
Povedzme, že ako vstup máme pre našich zákazníkov veľký objednávkový stôl. Pre pohodlie (aj keď to nie je potrebné) som previedol tabuľku na dynamickú „inteligentnú“ klávesovú skratku ctrl+T a dal na tab staviteľ (Dizajn) jej meno tabOrders:
Na samostatnom liste konsolidovaný Zostavil som kontingenčnú tabuľku (aj keď to nemusí byť presne kontingenčná tabuľka – v zásade je vhodná akákoľvek tabuľka), kde sa podľa prvotných údajov vypočítava dynamika predaja po mesiacoch pre každého klienta:
Pridajme stĺpec do tabuľky objednávok so vzorcom, ktorý vyhľadá meno zákazníka pre aktuálnu objednávku na hárku konsolidovaný. Na to používame klasickú skupinu funkcií INDEX (INDEX) и VIAC EXPOZOVANÉ (ZÁPAS):
Teraz zabaľme náš vzorec do funkcie BUNKA (BUNKA), ktorú požiadame o zobrazenie adresy nájdenej bunky:
A nakoniec všetko, čo sa ukázalo, vložíme do funkcie HYPERLINK (HYPERLINK), ktorý v programe Microsoft Excel dokáže vytvoriť živý hypertextový odkaz na danú cestu (adresu). Jediná vec, ktorá nie je zrejmá, je, že budete musieť prilepiť znak hash (#) na začiatku k prijatej adrese, aby bol odkaz správne vnímaný programom Excel ako interný (z listu na list):
Teraz, keď kliknete na niektorý z odkazov, okamžite preskočíme na bunku s názvom spoločnosti na hárku s kontingenčnou tabuľkou.
Aby to bolo naozaj dobré, mierne vylepšíme náš vzorec tak, aby prechod nenastal na meno klienta, ale na konkrétnu číselnú hodnotu presne v stĺpci mesiaca, kedy bola príslušná objednávka dokončená. Aby sme to dosiahli, musíme si uvedomiť, že funkcia INDEX (INDEX) v Exceli je veľmi všestranný a dá sa použiť okrem iného vo formáte:
=INDEX( XNUMXD_rozsah; Poradové číslo; Číslo_stĺpca )
To znamená, že ako prvý argument môžeme zadať nie stĺpec s názvami spoločností v kontingenčnej tabuľke, ale celú dátovú oblasť kontingenčnej tabuľky a ako tretí argument pridať číslo stĺpca, ktorý potrebujeme. Dá sa jednoducho vypočítať pomocou funkcie MESIAC (MESIAC), ktorý vráti číslo mesiaca pre dátum obchodu:
Zlepšenie 2. Krásny symbol odkazu
Druhý funkčný argument HYPERLINK – text, ktorý sa zobrazuje v bunke s odkazom – môže byť krajší, ak namiesto banálnych znakov „>>“ použijete neštandardné znaky z písma Windings, Webdings a podobne. Na to môžete použiť funkciu SYMBOL (CHAR), ktorý dokáže zobraziť znaky podľa ich kódu.
Napríklad kód znaku 56 v písme Webdings nám poskytne peknú dvojitú šípku pre hypertextový odkaz:
Zlepšenie 3. Zvýraznite aktuálny riadok a aktívnu bunku
Pre konečné víťazstvo krásy nad zdravým rozumom môžete k nášmu súboru pripojiť aj zjednodušenú verziu zvýraznenia aktuálneho riadku a bunky, na ktorú odkazujeme. To si bude vyžadovať jednoduché makro, ktoré zavesíme na spracovanie udalosti zmeny výberu na hárku konsolidovaný.
Ak to chcete urobiť, kliknite pravým tlačidlom myši na kartu Zhrnutie a vyberte príkaz pohľad kód (Vyhliadka kód). Prilepte nasledujúci kód do okna editora Visual Basic, ktoré sa otvorí:
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
Ako môžete ľahko vidieť, tu najskôr odstránime výplň z celého hárka a potom vyplníme celý riadok v súhrne žltou (kód farby 6) a potom oranžovou (kód 44) aktuálnou bunkou.
Teraz, keď je vybratá ľubovoľná bunka v súhrnnej bunke (nezáleží na tom – manuálne alebo ako výsledok kliknutia na náš hypertextový odkaz), zvýrazní sa celý riadok a bunka s mesiacom, ktorý potrebujeme:
Krása 🙂
PS Nezabudnite uložiť súbor vo formáte s povoleným makrom (xlsm alebo xlsb).
- Vytváranie externých a interných odkazov pomocou funkcie HYPERLINK
- Vytváranie emailov s funkciou HYPERLINK