Tweet

NEPŘÍMÝ.ODKAZ (INDIRECT) - funkce Excel

Obsah článku o NEPŘÍMÝ.ODKAZ (INDIRECT)

Seznam kapitol tohoto článku, aneb co se zde dozvíte.

• Teoretický úvod funkce NEPŘÍMÝ.ODKAZ (INDIRECT)
• Základní použití příklady
• Pokročilejší příklady
• Dynamické odkazy - dynamická změna části odkazu
• Spojení funkce NEPŘÍMÝ.ODKAZ s jinou funkci:
  • KDYŽ
  • SUMA
  • PRŮMĚR
  • MIN, MAX
  • ...
  • Dynamicka oblast - dynamicky definovaná oblast pro výpočty
• Odkaz na jiné listy
• Odkaz na jiné sešity
• NEPŘÍMÝ.ODKAZ v ověření dat
• Výpočty tabulka jako tabulka
• Maticové použití funkce:
  • NEPŘÍMÝ.ODKAZ s využitím SMALL/ LARGE pro SUMA, PRŮMĚR - maticově
  • Číselná řada s NEPŘÍMÝ.ODKAZ - maticově
• NEPŘÍMÝ.ODKAZ a ODKAZ pro vložení adresy
• Chyby
• NEPŘÍMÝ.ODKAZ a VBA
• Příklady ke stažení zdarma
• Související články s funkci NEPŘÍMÝ.ODKAZ

Teoretický úvod

Trocha teorie než se pustíme do praktických příkladů.

Syntaxe v češtině

Syntaxe v angličtině

Popis argumentu

• odkaz - Odkaz na buňku obsahující další odkaz typu A1 nebo R1C1, název definovaný jako odkaz nebo odkaz na buňku ve tvaru textového řetězce.
• a1 - Nepovinný argument. Logickou hodnotu, která určuje typ odkazu.
  • PRAVDA (není uveden) - adresa typu A1.
  • NEPRAVDA - adresa typu R1C1.

Poznámky

• Pokud hodnota argumentu odkaz neodpovídá platnému odkazu na buňku, vrátí funkce chybovou hodnotu #ODKAZ
• Pokud argument odkaz odkazuje na jiný sešit, musí být sešit otevřen, jinak obdržíte chybovou hodnotu #ODKAZ!

Zpět na seznam kapitol.

Základní použití příklady

Pro ukázku funkce předpokládám v buňce A5 hodnotu 12, v buňce B6 hodnotu A5.

=NEPŘÍMÝ.ODKAZ("A5")

První příklad je na první pohled rovnocenný s jednoduchou funkcí =A5, ale při automatizaci výpočtu, případně při přidávání řádku (sloupců) u vzorce =A5, může dojít ke změně. Při použití funkce NEPŘÍMÝ.ODKAZ se stále bude odkazovat na A5!.

=NEPŘÍMÝ.ODKAZ(B6)

V buňce B6, která obsahuje hodnotu A5, což je odkaz na buňku A5, ve které je hodnota 12.

Poznámka

Odkazy mohou být i v absolutním tvaru $A$5.

=NEPŘÍMÝ.ODKAZ($B$6)

Zpět na seznam kapitol.

Pokročilejší příklady

Využití pojmenovaných oblastí, Odkazy R1C1.

Pojmenované oblasti

Předpokladem je buňka pojmenovaná DPH.

=NEPŘÍMÝ.ODKAZ(B12) =NEPŘÍMÝ.ODKAZ("DPH")

Odkazy R1C1

=NEPŘÍMÝ.ODKAZ(B16) =NEPŘÍMÝ.ODKAZ(B16;PRAVDA) =NEPŘÍMÝ.ODKAZ(B17;NEPRAVDA)

Zpět na seznam kapitol.

Dynamické odkazy

Spojení odkazu z několika samostatných buněk.

=NEPŘÍMÝ.ODKAZ("A" & B21) =NEPŘÍMÝ.ODKAZ(B22 & B21)

Zpět na seznam kapitol.

Spojení funkce NEPŘÍMÝ.ODKAZ s jinou funkci

NEPŘÍMÝ.ODKAZ ve spojení KDYŽ

Chcete-li zobrazit pokud je splněná podmínka:

=KDYŽ(B25="";0;NEPŘÍMÝ.ODKAZ(A25)) =KDYŽ(B26="";0;NEPŘÍMÝ.ODKAZ("A25"))

NEPŘÍMÝ.ODKAZ ve spojení SUMA

Použití ve spojení s funkcí SUMA. Kdy funkce SUMA provede součet buněk, které náležejí do danné oblasti. Ať definované názvem, nebo rozsahem. Při použití definovaného názvu, lze krásně využít dynamické tvorbu oblasti, která se automaticky zvětšuje na základě přidáváných hodnot (Dynamicky pojmenované oblasti).

=SUMA(NEPŘÍMÝ.ODKAZ("Jan")) =SUMA(NEPŘÍMÝ.ODKAZ("Honza")) =SUMA(NEPŘÍMÝ.ODKAZ("A29:A33")) =SUMA(NEPŘÍMÝ.ODKAZ("B29:B33"))

NEPŘÍMÝ.ODKAZ ve spojení PRŮMĚR

Použití ve spojení s funkcí PRŮMĚR

=PRŮMĚR(NEPŘÍMÝ.ODKAZ("Jan")) =PRŮMĚR(NEPŘÍMÝ.ODKAZ("Honza"))

NEPŘÍMÝ.ODKAZ ve spojení MIN, MAX

Použití ve spojení s funkcí MIN, MAX

=MIN(NEPŘÍMÝ.ODKAZ("Jan")) =MAX(NEPŘÍMÝ.ODKAZ("Honza"))

Dynamické odkazy

Ve spojení funkce NEPŘÍMÝ.ODKAZ s jinou funkcí, lze nádherně využít dynamicky definované pojmenované oblasti. Více o dynamcky pojmenovaných odkazech v samostatném článku (zde se jen an dynamicky definovanou oblast / název odkážeme):

• Dynamicky pojmenované oblasti

Pokud nastudujete můžete využít:

=POSUN($A$2;0;0;POČET2($A$2:$A$200);1) atd.

Jiné řešení

Velice výhodné je vzužít nadefinování tabulky jako Tabulka a následn ě se na sloupce přímo odkazovat. Více viz další kapitoly.

Zpět na seznam kapitol.

NEPŘÍMÝ.ODKAZ s funkci SUM a dynamickým odkazem

Potřebuji dynamický rozsah ve funkci SUM (atd. např. MIN, MAX, COUNTIFS).

=SUMA(NEPŘÍMÝ.ODKAZ("B5:B8")) =SUMA(NEPŘÍMÝ.ODKAZ("B"&D14&":B"&E14)) "=SUMA(NEPŘÍMÝ.ODKAZ( C14&D14&"":""&C14&E14 ))"

Ukázka

Odkaz na jiné listy

Jak se pomocí funkce NEPŘÍMÝ.ODKAZ odkaz na jiné listy.

Odkaz na jiný list

Odkáže se na List2 buňku B5 a bude na tuto buňku neustále směřovat, ať na listě budete přídávat, ubírat listy. Stále obdržíte hodnotu co je v Listě 2 v buňce na B5. =NEPŘÍMÝ.ODKAZ("List2!$B$5")

Nebo se odkázat na buňku ve které je List2!$B$5, pokud bude zadáno v B4 tak v jiné buňce zadat:

=NEPŘÍMÝ.ODKAZ(B4)

Zpět na seznam kapitol.

Odkaz na jiné sešity

Podobně se lze odkazovat i na jiný sešit.

A3 ... služby.xlsx A4 ... List2 A5 ... B1 =NEPŘÍMÝ.ODKAZ("'[" & A3 & "]" & A4 & "'!" & A5)

Zpět na seznam kapitol.

NEPŘÍMÝ.ODKAZ v ověření dat

Jak využít NEPŘÍMÝ.ODKAZ v ověření dat. Aneeb využití tabulky naformátované jako tabulka (tím pádem se mi dynamicky zvětšuje) a propisuje do ověření dat.

Jak na To ukážu v této kapitole:

Předpokládám

Máte vloženou tabulku jako tabulka nad seznamem zaměstnců. Jak vložit tabulku jako tabulka jsem poslal v článku: tabulka jako tabulka. Tabulka má své jméno a odkaz na sloupec to využijete. Neboli. Daná (moje) tabulka se jmenuje Tabulka1 a sloupc Jméno pracovníka. Celkově se tedy na sloupec v tabulce mohu odkazovat: Tabulka1[Jméno pracovníka]

Řešení

Následně vložíte ověření dat nad seznamem. Jak na ověření dat jsem popsal v článku: ověření dat, kde vyberete seznam a odkážete se pomocí funkce NEPŘÍMÝ.ODKAZ - víte jak se jemnuje tabulka a příslušný sloupec, který chcete mít v ověření dat (bue se načítat). Výsledek bude:

=NEPŘÍMÝ.ODKAZ("Tabulka1[Jméno pracovníka]")

Nebo-li jméno s odkazem na sloupec dáte v NEPŘÍMÝ.ODKAZ do uvozovek. Potvrdíte OK a máte hotovo

Ukázka

Ke stažení:

Soubor NEPŘÍMÝ.ODKAZ s ověřením dat ke stažení zdarma.

Jiné řešení

Řešit lze i jinak. Například dynamický oblast pro ověření dat využitím funkce POSUN. Nebo propojené dynamické seznamy.

Výpočty tabulka jako Tabulka

Jak využívat funkci NEPŘÍMÝ.ODKAZ v tabulkách nastylovaných jako Tabulka.

Data

Mějme tabulky definované jako Tabulka, kdy názvy tabulek jsou Divize1, Divize2 atd. Tabulky obsahují výrobky a za kolik obratu daný výrobek v daném měsíci přinesl. Sloupce jsou označeny názvy měsícu jako textový formát 01, 02, atd.

Potřebujete sečíst obrat v daném měsíci a v dané divizi.

Řešení

Klasicky se můžete na sloupec odkázat:

=SUMA(Divize1[01])

Do buněk O11 a O12 zadávate Divizi a Měsíc (můžete využít ověření dat).

Řešení může vypadat:

1) Test zda funguje ručně zadaný název ve funkci NEPŘÍMÝ.ODKAZ

=SUMA(NEPŘÍMÝ.ODKAZ("Divize1[01]"))

2) Doplníte funkci NEPŘÍMÝ.ODKAZ o odkaz na měsíc (pokud máte jen jednu tabulku máte hotovo)

=SUMA(NEPŘÍMÝ.ODKAZ("Divize1["&O13&"]"))

3) Kompletní ukázka kdy se můžete dynamicky odkazovat na jméno tabulky i sloupce

=SUMA(NEPŘÍMÝ.ODKAZ(O12&"["&O13&"]"))

Ukázka řešení

Poznámky

Podobně se dá využívat odkazů na názvy sloupců, atd.

NEPŘÍMÝ.ODKAZ s využitím SMALL/ LARGE pro SUMA, PRŮMĚR

Z oblasti, chci vypočíst SUMA, PRŮMĚR z oblasti, ale do průměru chci zahrnout jen nejmenší/největší čísla (třeba tři):

Maticově

Výpočty SUMA a tři minima se musí zadávat maticově (Ctrl + Shift + Enter):

=SUMA(SMALL(B5:B13;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:3")))) .. pro 4 hodnoty =SUMA(SMALL(B5:B13;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:4"))))

Výpočty SUMA a tři maxima se musí zadávat maticově (Ctrl + Shift + Enter):

=SUMA(LARGE(B5:B13;ŘÁDEK(NEPŘÍMÝ.ODKAZ("1:3"))))

Ukázka

Číselná řada s NEPŘÍMÝ.ODKAZ

Jak zadávat číslednou řadu od do využitím maticové funkce. Funkce se musí zadávat maticově (Ctrl + Shift + Enter):

{=ŘÁDEK(NEPŘÍMÝ.ODKAZ(I4&":"&I5))} co funkce vztvoří vzužitím klávesy F9 ve funkci ={10;11;12;13;14;15;16;17;18;19;20}

Ukázka

NEPŘÍMÝ.ODKAZ a ODKAZ pro vložení adresy

Potřebuji přečíst hodmotu z řádku 4 a sloupce 3:

=NEPŘÍMÝ.ODKAZ(ODKAZ(4;3)) lze využít i odkaz R1C1, ale musí se zadávat do uvozovek, což využitím funkce ODKAZ =NEPŘÍMÝ.ODKAZ("R4C3";NEPRAVDA)

Ukázka

O funkci ODKAZ (ADDRESS)

• ODKAZ (ADDRESS)

Chyby s funkcí NEPŘÍMÝ.ODKAZ

Ve funkci NEPŘÍMÝ.ODKAZ mohou vznikat chyby, nějčastěji, když funkci NEPŘÍMÝ.ODKAZ zapomenete použít.

Chybný odkaz

Odkaz na buňku se musí správně zadat:

neexistuje sloupec XFE: =NEPŘÍMÝ.ODKAZ("XFE1") #ODKAZ! název odkazované buňky není v uvozovkách =NEPŘÍMÝ.ODKAZ(XFD1) #NÁZEV? Správně: =NEPŘÍMÝ.ODKAZ("XFD1")

Chybí NEPŘÍMÝ.ODKAZ

Potřebuji se ve funkci SUMA odkázat na oblast s názvem Honza.

CHYBA! =SUMA("Honza") #HODNOTA! =SUMA(Honza) #NÁZEV? správně =SUMA(NEPŘÍMÝ.ODKAZ("Honza"))

NEPŘÍMÝ.ODKAZ s využitím VBA

Při zadání do buňky

Range("B7").Formula = "=INDIRECT(B4)" Range("B8").FormulaLocal = "=NEPŘÍMÝ.ODKAZ(B4)"

VBA s odkazem na buňku.

x = Range(Range("B4").Value).Value MsgBox x

Ukázka

Tip. Další ukázky místo Range, lze využít Cells, a upravit vlastnosti místo Value, Value2, Text, Formula ....

Příklady ke stažení zdarma

Soubor praktické příklady použití funkce NEPŘÍMÝ.ODKAZ (INDIRECT) ke stažení zdarma.

Související články s funkci HYPERTEXTOVÝ.ODKAZ

Další články související se statistickými funkcemi:

• Přehled vyhledávacích funkcí
• HYPERTEXTOVÝ.ODKAZ (HYPERLINK) - odkaz hypertextový
• ODKAZ (ADDRESS) - adresa buňky
• SVYHLEDAT a NEPŘÍMÝ.ODKAZ

Závěrem

Popis funkce NEPŘÍMÝ.ODKAZ máte za sebou, pokud jsem něco neuvedl, nebo máte zajimavé využití této funkce, můžete zmínit v komentářích.

Článek byl aktualizován: 21.02.2019 17:24

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře