Obsah
Na první pohled (hlavně při čtení nápovědy) funkce NEPŘÍMÝ (NEPŘÍMÝ) vypadá jednoduše a dokonce zbytečně. Jeho podstatou je proměnit text, který vypadá jako odkaz, na plnohodnotný odkaz. Tito. pokud potřebujeme odkazovat na buňku A1, můžeme buď obvykle vytvořit přímý odkaz (zadejte rovnítko do D1, klikněte na A1 a stiskněte Enter), nebo můžeme použít NEPŘÍMÝ za stejným účelem:
Vezměte prosím na vědomí, že argument funkce – odkaz na A1 – se zadává v uvozovkách, takže se zde ve skutečnosti jedná o text.
"No dobře," řeknete. "A jaký je přínos?"
Nesuďte však podle prvního dojmu – ten klame. Tato funkce vám může pomoci v mnoha situacích.
Příklad 1. Transponujte
Klasika žánru: musíte otočit vertikální prům
drážka do vodorovné (transponovat). Samozřejmě můžete použít speciální vložku nebo funkci TRANSP (PŘEMÍSTIT) v maticovém vzorci, ale vystačíte si s naším NEPŘÍMÝ:
Logika je jednoduchá: abychom získali adresu další buňky, slepíme písmeno „A“ se speciálním znakem „&“ a číslem sloupce aktuální buňky, které nám funkce poskytne SLOUPEK (SLOUPEC).
Opačný postup je lepší udělat trochu jinak. Protože tentokrát potřebujeme vytvořit vazbu na buňky B2, C2, D2 atd., je výhodnější místo klasické „námořní bitvy“ použít linkový režim R1C1. V tomto režimu se naše buňky budou lišit pouze číslem sloupce: B2=R1C2C2=R1C3D2=R1C4 atd.
Zde přichází na řadu druhý volitelný argument funkce. NEPŘÍMÝ. Pokud se rovná LEŽÍCÍ (NEPRAVDIVÉ), pak můžete nastavit adresu linky v režimu R1C1. Takže můžeme snadno transponovat horizontální rozsah zpět na vertikální:
Příklad 2. Součet podle intervalu
Již jsme analyzovali jeden způsob sčítání přes okno (rozsah) dané velikosti na listu pomocí funkce LIKVIDACE (POSUN). Podobný problém lze také vyřešit pomocí NEPŘÍMÝ. Pokud potřebujeme shrnout data pouze z určitého rozsahu-období, pak je můžeme slepit z kousků a následně z nich udělat plnohodnotný odkaz, který můžeme vložit dovnitř funkce SOUČET (SOUČET):
Příklad 3. Rozbalovací seznam inteligentních tabulek
Někdy Microsoft Excel nepovažuje názvy a sloupce inteligentních tabulek za úplné odkazy. Takže například při pokusu o vytvoření rozevíracího seznamu (tab Data – Validace dat) na základě sloupce Zaměstnanci z chytrého stolu Lidé dostaneme chybu:
Pokud odkaz „zabalíme“ naší funkcí NEPŘÍMÝ, pak to Excel snadno přijme a náš rozevírací seznam se bude dynamicky aktualizovat při přidávání nových zaměstnanců na konec chytré tabulky:
Příklad 4. Nerozbitné odkazy
Jak víte, Excel automaticky opravuje referenční adresy ve vzorcích při vkládání nebo odstraňování řádků a sloupců na listu. Ve většině případů je to správné a pohodlné, ale ne vždy. Řekněme, že potřebujeme přenést jména z adresáře zaměstnanců do sestavy:
Pokud dáte běžné odkazy (do první zelené buňky zadejte =B2 a zkopírujte to), tak když smažete např. Dášu, dostaneme #ODKAZ! chyba v zelené buňce, která jí odpovídá. (#REF!). V případě použití funkce pro vytváření odkazů NEPŘÍMÝ žádný takový problém nebude.
Příklad 5: Sběr dat z více listů
Předpokládejme, že máme 5 listů se zprávami stejného typu od různých zaměstnanců (Michail, Elena, Ivan, Sergej, Dmitrij):
Předpokládejme, že tvar, velikost, pozice a pořadí zboží a měsíců ve všech tabulkách jsou stejné – liší se pouze čísla.
Můžete sbírat data ze všech listů (nesčítat je, ale dávat je pod sebe na „hromadu“) pomocí jediného vzorce:
Jak vidíte, myšlenka je stejná: přilepíme odkaz na požadovanou buňku daného listu a NEPŘÍMÝ změní to na „živý“. Pro větší pohodlí jsem nad tabulku přidal písmena sloupců (B, C, D) a vpravo čísla řádků, které je třeba vzít z každého listu.
Úskalí
Pokud používáte NEPŘÍMÝ (NEPŘÍMÝ) musíte mít na paměti jeho slabiny:
- Pokud vytvoříte odkaz na jiný soubor (slepením názvu souboru do hranatých závorek, názvu listu a adresy buňky), funguje to pouze tehdy, když je otevřený původní soubor. Pokud jej zavřeme, zobrazí se chyba #LINK!
- INDIRECT nemůže odkazovat na dynamicky pojmenovaný rozsah. Na statice – žádný problém.
- NEPŘÍMÉ je volatilní nebo „volatilní“ funkce, tj. je přepočítána pro jakoukoli změnu v kterékoli buňce listu, a to nejen pro ovlivnění buněk, jako u normálních funkcí. To má špatný vliv na výkon a je lepší se nenechat unést velkými NEPŘÍMÝMI tabulkami.
- Jak vytvořit dynamický rozsah pomocí automatického nastavení velikosti
- Sčítání přes rozsahové okno na listu s funkcí OFFSET