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
Seznam kapitol tohoto článku, aneb co se zde dozvíte.
Trocha teorie než se pustíme do praktických příkladů.
NEPŘÍMÝ.ODKAZ(odkaz; [a1])
INDIRECT(odkaz, [a1])
Základní použití funkce NEPŘÍMÝ.ODKAZ
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.
Odkazy mohou být i v absolutním tvaru $A$5.
=NEPŘÍMÝ.ODKAZ($B$6)
Využití pojmenovaných oblastí, Odkazy R1C1.
Předpokladem je buňka pojmenovaná DPH.
=NEPŘÍMÝ.ODKAZ(B12)
=NEPŘÍMÝ.ODKAZ("DPH")
=NEPŘÍMÝ.ODKAZ(B16)
=NEPŘÍMÝ.ODKAZ(B16;PRAVDA)
=NEPŘÍMÝ.ODKAZ(B17;NEPRAVDA)
Spojení odkazu z několika samostatných buněk.
=NEPŘÍMÝ.ODKAZ("A" & B21)
=NEPŘÍMÝ.ODKAZ(B22 & B21)
Spojení s funkcemi KDYŽ, SUMA.
Chcete-li zobrazit pokud je splněná podmínka:
=KDYŽ(B25="";0;NEPŘÍMÝ.ODKAZ(A25))
=KDYŽ(B26="";0;NEPŘÍMÝ.ODKAZ("A25"))
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"))
Použití ve spojení s funkcí PRŮMĚR
=PRŮMĚR(NEPŘÍMÝ.ODKAZ("Jan"))
=PRŮMĚR(NEPŘÍMÝ.ODKAZ("Honza"))
Použití ve spojení s funkcí MIN, MAX
=MIN(NEPŘÍMÝ.ODKAZ("Jan"))
=MAX(NEPŘÍMÝ.ODKAZ("Honza"))
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.
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.
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 ))"
Jak se pomocí funkce NEPŘÍMÝ.ODKAZ odkaz na jiné listy.
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)
Podobně se lze odkazovat i na jiný sešit.
A3 ... služby.xlsx
A4 ... List2
A5 ... B1
=NEPŘÍMÝ.ODKAZ("'[" & A3 & "]" & A4 & "'!" & A5)
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:
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]
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
Soubor
NEPŘÍMÝ.ODKAZ s ověřením dat
ke stažení zdarma.
Řešit lze i jinak. Například dynamický oblast pro ověření dat využitím funkce POSUN. Nebo propojené dynamické seznamy.
Jak využívat funkci NEPŘÍMÝ.ODKAZ v tabulkách nastylovaných jako Tabulka.
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.
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&"]"))
Podobně se dá využívat odkazů na názvy sloupců, atd.
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):
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"))))
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}
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)
Ve funkci NEPŘÍMÝ.ODKAZ mohou vznikat chyby, nějčastěji, když funkci NEPŘÍMÝ.ODKAZ zapomenete použít.
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")
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"))
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
Tip. Další ukázky místo Range, lze využít Cells, a upravit vlastnosti místo Value, Value2, Text, Formula ....
Soubor praktické příklady použití funkce NEPŘÍMÝ.ODKAZ (INDIRECT)
ke stažení zdarma.
Další články související s vyhledávacími funkcemi:
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
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 .
Děkuji za váš čas a doufám, že jste nalezli odpověď na svůj problém.
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.
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.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
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 |