Jakmile budete vědět, jak používat nepřímou funkci v Excelu, můžete získat informace z jiných listů, odkazovat na pojmenované rozsahy a kombinovat je s dalšími funkcemi, abyste vytvořili skutečně všestranný nástroj. Může to vyžadovat trochu cviku, než se s tím vypořádat, ale s nepřímou funkcí můžete udělat víc, než byste si mysleli.
Pokyny v tomto článku platí pro Excel pro Microsoft 365, Excel 2019 a Excel 2016.
Co je nepřímá funkce?
Nepřímá funkce je způsob, jak převést textový řetězec na odkaz. To znamená, že čerpá informace z odkazu na jinou buňku nebo rozsah. Vytváří odkaz z textu a nemění se, když jsou buňky, řádky nebo sloupce změněny, přidány nebo odebrány z citovaného rozsahu. Reference, které vytváří, jsou vyhodnocovány v reálném čase, takže reference vždy odpovídají datům, ze kterých čerpá.
Pokud vám to připadá trochu matoucí, nezlobte se. Nepřímý vzorec může být snáze srozumitelný s platnými příklady a v praxi. Máte-li pochybnosti, postupujte podle níže uvedených kroků a brzy to pochopíte.
Použití nepřímé funkce s pojmenovanými rozsahy
Pojmenované rozsahy v Excelu jsou skvělým způsobem, jak shromažďovat data pod jednou referencí, a nepřímá funkce z nich usnadňuje získání těchto informací. Zde je návod, jak na to:
-
Otevřete dokument aplikace Excel s již použitými pojmenovanými rozsahy. V našem příkladu máme informace o prodeji z různých potravin a nápojů, přičemž peníze vydělané za každý den v týdnu jsou shromážděny v pojmenovaných řadách pojmenovaných po produktech.
Image -
Vyberte buňku pro svůj pojmenovaný rozsah a jednu z nich do ní vložte. V našem příkladu jsme použili Burgers. Pokud si přejete, přidejte další názvy a vybarvení.
Image -
Vyberte jinou buňku, kam chcete nepřímý výstup přesunout. Vzhledem k tomu, že chceme sečíst celý objem prodeje za týden pro konkrétní jídlo, v tomto případě Burgery, zadáme do buňky následující:
=SUM(NEPŘÍMÉ(G5)
-
Toto označuje funkci SUM, která použije nepřímou funkci k získání informací z pojmenovaného rozsahu v buňce G5, v tomto případě Burgers. Výstup je 3781, celkový prodej za týden pro Burgery.
V našem příkladu můžeme burgery v buňce G5 nahradit Limonádou nebo Dezerty, dalšími dvěma pojmenovanými rozsahy, a výstup se místo toho změní na jejich součet SUM.
Image
Použití nepřímé funkce na více listech
Nepřímý vzorec je ještě výkonnější, když jej použijete k získávání informací z jiných listů. Nemusíte k tomu používat ani pojmenované rozsahy.
- Otevřete dokument Excel s více listy nebo je vytvořte se všemi potřebnými informacemi.
- V listu, kam chcete nepřímý výstup umístit, vytvořte buňku s názvem listu, ze kterého chcete čerpat informace. V našem příkladu je to FoodSales.
-
Vzhledem k tomu, že chceme získat informace z našeho FoodSales pro celkový počet prodaných Burgers, zadali jsme následující (nahraďte rozsah buněk a názvy listů s vašimi vlastními):
=SUM(NEPŘÍMÉ(B4&"!B4:B10"))
Image - Toto ji označuje jako funkci SUM, protože se snažíme najít součet. Poté určí buňku B4 jako referenční text pro nepřímou funkci. & spojuje prvky této funkce, následuje uvozovka a vykřičník a poté rozsah buněk, ze kterých chceme data čerpat. B4 až B10
-
Výstup je celkový prodej burgerů za daný týden. Když vytvoříme nový list FoodSales2 pro nový týden s jinými čísly, potřebujeme pouze upravit buňku B4 tak, aby říkala FoodSales2, abychom získali údaje o prodeji burgerů za daný týden.
Použití nepřímé funkce s referencí stylu R1C1
U listů, které se neustále rozšiřují, kde odkaz, který chcete použít, nebude vždy ve stejné buňce, lze použít odkazy stylu R1C1 s nepřímým vzorcem, které vám poskytnou potřebné informace. I nadále zde budeme používat naše příklady prodeje potravin, ale představte si, že je to pro pracovní list vyšší úrovně, který se zabývá celkovými týdenními tržbami.
- Otevřete dokument Excel se všemi daty, ze kterých chcete čerpat, a vyberte buňku pro výstup nepřímé funkce. V našem příkladu se díváme na celkové měsíční prodeje potravin a chceme znát nejnovější celkové prodeje za daný měsíc.
-
V našem příkladu vypadá vzorec takto:
=NEPŘÍMÉ("R12C"&COUNTA(12:12), FALSE)
Image -
Nepřímá funkce používá R12 (řádek 12) následovaný C k označení sloupce, uzavřeného v uvozovkách. & spojuje dvě části funkce dohromady. Pomocí funkce COUNTA spočítáme všechny neprázdné buňky v řádku 12 (výběr řádku nebo zadání 12:12), za nimiž následuje čárka. FALSE to označuje jako referenci R1C1.
-
Výstup je pak posledním záznamem v naší tabulce, v tomto případě 8102 nebo 8 102 $. Když nakonec přidáme údaje o prodeji za duben, poslední prodejní číslo se automaticky aktualizuje v reálném čase.
Image
