Obsah
Pokud jste alespoň obeznámeni s funkcí VPR (VYHLEDAT) (pokud ne, tak nejprve spusťte zde), pak byste měli pochopit, že tato a další jemu podobné funkce (VIEW, INDEX a SEARCH, SELECT atd.) vždy poskytují výsledek hodnota – číslo, text nebo datum, které v dané tabulce hledáme.
Co když ale místo hodnoty chceme získat živý hypertextový odkaz, kliknutím na který bychom mohli okamžitě přejít na nalezenou shodu v jiné tabulce a podívat se na ni v obecném kontextu?
Řekněme, že jako vstup máme pro naše zákazníky velký objednávkový stůl. Pro pohodlí (i když to není nutné) jsem převedl tabulku na dynamickou „chytrou“ klávesovou zkratku Ctrl+T a dal na tab Stavitel (Design) její jméno tabOrders:
Na samostatném listu Konsolidované Sestavil jsem kontingenční tabulku (i když to nemusí být zrovna kontingenční tabulka – hodí se v zásadě jakákoli tabulka), kde se podle výchozích dat počítá dynamika prodeje po měsících pro každého klienta:
Přidejme do tabulky objednávek sloupec se vzorcem, který vyhledá jméno zákazníka pro aktuální objednávku na listu Konsolidované. K tomu používáme klasickou spoustu funkcí INDEX (INDEX) и VÍCE EXPOZOVANĚ (ZÁPAS):
Nyní zabalíme náš vzorec do funkce BUŇKA (BUŇKA), kterou požádáme o zobrazení adresy nalezené buňky:
A nakonec jsme všechno, co se ukázalo, dali do funkce HYPERLINK (HYPERLINK), který v Microsoft Excel umí vytvořit živý hypertextový odkaz na danou cestu (adresu). Jediná věc, která není zřejmá, je, že budete muset přilepit znak hash (#) na začátku k přijaté adrese, aby byl odkaz správně vnímán aplikací Excel jako interní (z listu na list):
Nyní, když kliknete na některý z odkazů, okamžitě přeskočíme do buňky s názvem společnosti na listu s kontingenční tabulkou.
Aby to bylo opravdu dobré, trochu vylepšeme náš vzorec tak, aby přechod nenastal na jméno klienta, ale na konkrétní číselnou hodnotu přesně ve sloupci měsíce, kdy byla odpovídající zakázka dokončena. K tomu si musíme pamatovat, že funkce INDEX (INDEX) v Excelu je velmi univerzální a lze jej použít mimo jiné ve formátu:
= INDEX ( XNUMXD_rozsah; Číslo řádku; Číslo_sloupce )
To znamená, že jako první argument můžeme zadat nikoli sloupec s názvy společností v pivotu, ale celou datovou oblast kontingenční tabulky a jako třetí argument přidat číslo sloupce, který potřebujeme. Lze to snadno vypočítat funkcí MĚSÍC (MĚSÍC), který vrátí číslo měsíce pro datum dohody:
Zlepšení 2. Krásný symbol odkazu
Druhý argument funkce HYPERLINK – text, který se zobrazuje v buňce s odkazem – lze zkrášlit, pokud místo banálních znaků „>>“ použijete nestandardní znaky z písma Windings, Webdings a podobně. K tomu můžete použít funkci SYMBOL (CHAR), který umí zobrazovat znaky podle jejich kódu.
Takže například kód znaku 56 ve fontu Webdings nám poskytne pěknou dvojitou šipku pro hypertextový odkaz:
Zlepšení 3. Zvýrazněte aktuální řádek a aktivní buňku
Pro konečné vítězství krásy nad zdravým rozumem můžete k našemu souboru připojit také zjednodušenou verzi zvýraznění aktuálního řádku a buňky, na kterou odkazujeme. To bude vyžadovat jednoduché makro, které zavěsíme pro zpracování události změny výběru na listu Konsolidované.
Chcete-li to provést, klepněte pravým tlačítkem myši na kartu listu Souhrn a vyberte příkaz Pohled kód (Pohled kód). Vložte následující kód do okna editoru jazyka Visual Basic, které se otevře:
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
Jak můžete snadno vidět, zde nejprve odstraníme výplň z celého listu a poté vyplníme celý řádek v souhrnu žlutou (kód barvy 6) a poté oranžovou (kód 44) aktuální buňkou.
Nyní, když je vybrána libovolná buňka uvnitř souhrnné buňky (nezáleží na tom – ručně nebo v důsledku kliknutí na náš hypertextový odkaz), zvýrazní se celý řádek a buňka s měsícem, který potřebujeme:
Krása 🙂
PS Nezapomeňte soubor uložit ve formátu s podporou maker (xlsm nebo xlsb).
- Vytváření externích a interních odkazů pomocí funkce HYPERLINK
- Vytváření e-mailů pomocí funkce HYPERLINK