Přeskočit navigaci | Přeskočit na novinky

Jste zde: Úvodní stránka » excel » funkce-vyhledavaci » neprimy-odkaz-indirect

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak prakticky používat vyhledávací funkci NEPŘÍMÝ.ODKAZ (INDIRECT) v Microsoft Excelu - praktické příklady včetně ukázky ke stažení zdarma.

18.2.2019: Doplněno o maticové zápisy a funkci ODKAZ

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

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

Teoretický úvod

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

Syntaxe v češtině

NEPŘÍMÝ.ODKAZ(odkaz; [a1])

Syntaxe v angličtině

INDIRECT(odkaz, [a1])

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!

Grafická ukázka jak funguje

NEPŘIMÝ.ODKAZ teoretická ukázka

Zpět na seznam kapitol.

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

Základní použití funkce NEPŘÍMÝ.ODKAZ

NEPŘIMÝ.ODKAZ Excel - příklad

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) NEPŘIMÝ.ODKAZ Excel - příklad

Zpět na seznam kapitol.

Dynamické odkazy

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

NEPŘIMÝ.ODKAZ Excel - příklad
=NEPŘÍMÝ.ODKAZ("A" & B21) =NEPŘÍMÝ.ODKAZ(B22 & B21)

Zpět na seznam kapitol.

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

Spojení s funkcemi KDYŽ, SUMA.

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ŘIMÝ.ODKAZ Excel - příklad

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):

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

NEPŘIMÝ.ODKAZ Excel - ve spojení se SUM a dynamickým rozsahem

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.

NEPŘIMÝ.ODKAZ Excel - příklad
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

Podobné řešení jsem zatím nikde neviděl. Jen to, které jsem před 8 lety publikoval a využívá funkce POČET2 a POSUN. Toto je elegantnější, tak se třeba také postupně rozšíří ;)

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

NEPŘÍMÝ.ODKAZ s ověřením dat - Excel

Ke stažení:

Soubor NEPŘÍMÝ.ODKAZ s ověřením dat soubor ve formátu *.xlsx 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.

NEPRIMY-ODKAZ-INDIRECT-EXCEL-data-suma.JPG

Ř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í

NEPRIMY-ODKAZ-INDIRECT- ř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

NEPRIMY-ODKAZ-INDIRECT- řešení s maticovým zápisem

Čí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

NEPRIMY-ODKAZ-INDIRECT- řešení s maticovým zápisem

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

NEPRIMY-ODKAZ-INDIRECT- řešení s funkci ODKAZ

O funkci 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

NEPRIMY-ODKAZ-INDIRECT- ve VBA

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

Logo ke stažení příklady Excel

Příklady ke stažení zdarma

Soubor praktické příklady použití funkce NEPŘÍMÝ.ODKAZ (INDIRECT) soubor ve formátu *.xls ke stažení zdarma.


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

Další články související s vyhledávacími funkcemi:


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: 19.09.2020 11:00

Podpora - oceňte web a pozvěte mě na čokoládu

Pomohl vám článek? Vyřešili jste problém? Můžete mě podpořit zakoupení tabulky (samozdřejmě čokoládové), když kafe nepiji ;) Odkaz na zakoupení čokolády. Za veškerou podporu vám děkuji a samozdřejmě jí využiji do zdokonalování a rozšířování webu.

Případně přidejte odkaz na vaši oblíbenou sociální síť, případně využijste hashtag #JakNaExcel .

Poděkování

Děkuji za váš čas a doufám, že jste nalezli odpověď na svůj problém.

Vylepšení

Narazili jste v článku na nejasnost, chybu? Máte tip na vylepšení nebo doplnění článku? Budu rád pokud se zmínite v komentářích.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). 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 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.

   Pavel Lasák LinkedIn Profil    Pavel Lasák twitter Profil




Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2025 |