NEPŘÍMÝ.ODKAZ – konsolidace dat z více listů pomocí funkce

Autor: Miroslav Lorenc

Funkci Excelu NEPŘÍMÝ.ODKAZ (INDIRECT) nejvíce využijeme v případech, kdy se potřebujeme odkazovat na místo, jehož adresa je někde zapsaná jako text (alespoň z části). 

Syntaxe funkce

• =NEPŘÍMÝ.ODKAZ(odkaz;[A1])

Odkaz – nejčastěji odkaz na buňku, která obsahuje text, který chceme použít jako odkaz.

A1 – logická hodnota PRAVDA pro styl odkazu A1 nebo NEPRAVDA pro odkazy typu R1C1.

Příklad: konsolidace dat z více listů

Ve firmě máme 21 obchodních zástupců (interní označení DX_7033, DX_7058, DX_7118,…) a ti prodávají stejné zboží (evidujeme je jako artikly 1486, 1526, 2567,…) – celkem 30 druhů. V našem příkladu má každý obchodní zástupce na jednom listu (pojmenovaném svým ID) uvedeno, jaké artikly v daném období obchodoval a kolik za ně utržil.

Nyní bychom potřebovali udělat souhrnný report, ve kterém vypíšeme celkové prodeje za všechny obchodní zástupce.

 

Do prvního sloupce vypíšeme seznam obchodních zástupců. Ten budeme mít určitě někde ve firmě k dispozici. Pokud bychom ho náhodou neměli nebo bychom pracovali s cizími daty a potřebovali vypsat názvy listů sešitu, můžeme využít generování seznamu listů pomocí VBA:

Sub SeznamNazvuListu()
' Vypíše do sloupce A pod sebe názvy všech listů v sešitu For i = 1 To Sheets.Count Cells(i, 1) = Sheets(i).Name Next i End Sub

Pro výpočet souhrnných prodejů za jednotlivé obchodní zástupce můžeme použít funkci NEPŘÍMÝ.ODKAZ.

• =SUMA(NEPŘÍMÝ.ODKAZ(A2&”!”&”B:B”))

První argument “odkaz” je tvořen relativním odkazem na buňku A2, ke kterému pomocí textového operátoru & připojíme vykřičník – tím nám vznikne odkaz na list daného obchodního zástupce a dále adresujeme sloupec B.
Druhý argument A1 je nepovinný, když ho neuvedeme, použije se typ odkazu A1, což je náš případ.
Nyní bychom rádi report rozšířili o přehled všech obchodních zástupců a všech prodávaných artiklů (těch víme, že je max. 30).
Seznam obchodních zástupců vytvoříme obdobně, jako v předešlém případě.

Seznam (očíslování) artiklů v prvním řádku můžeme udělat např. tak, že napíšeme do B1 text “Artikl 1” a pak tuto buňku za současného přidržení klávesy CTRL zkopírujeme tažením doprava.

Vzorce v jednotlivých buňkách tabulky budou všude stejné (jeden vzorec):

• =NEPŘÍMÝ.ODKAZ($A2&”!”&”A”&SLOUPEC(B1))

První argument “odkaz” se skládá ze smíšeného odkazu $A2 (požaduje fixování na sloupec A, ale adresy řádků se mají měnit), ke kterému pomocí textového operátoru & připojíme vykřičníka dále písmeno A a nakonec pořadové číslo řádku. Zde převádíme hodnoty z jednotlivých řádků parciálních listů za obchodní zástupce do sloupců souhrnného reportu. Funkce SLOUPEC nám zde pouze dosazuje pořadové číslo sloupce, které se mění stejně jako řádky v dílčích výkazech na listech obchodníků.

 

Funkce NEPŘÍMÝ.ODKAZ má mnoho dalších možností využití, toto byla ukázka jen jednoho z nich. 

Nakonec (ale spíše úplně na začátku) by bylo dobré si položit otázky:

1. Proč se data načítají do dílčích listů za jednotlivé obchodní zástupce a ne do společné tabulky, kde by byla data logicky strukturována?
2. Nebylo by jednodušší a univerzálnější řešení pomocí Power Query místo funkce NEPŘÍMÝ.ODKAZ?

Zdrojový soubor vč. výsledeků