Jak používat nepřímou funkci v aplikaci Excel

Jakmile víte, jak používat nepřímou funkci v aplikaci Excel, 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ě univerzální nástroj. Může to trvat trochu praxe, než se s tím vyrovnáme, 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 to 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. Vytvoří odkaz z textu a nezmění se, když jsou buňky, řádky nebo sloupce změněny, přidány nebo odstraněny z citovaného rozsahu. Odkazy, které vytváří, se vyhodnocují v reálném čase, takže odkaz je vždy přesný k datům, ze kterých čerpá. Pokud vám to připadá trochu matoucí, netrapte se. Nepřímý vzorec lze snáze pochopit pomocí platných příkladů a v praxi. Máte-li pochybnosti, postupujte podle níže uvedených kroků a brzy se vám to podaří.

Použití nepřímé funkce s pojmenovanými rozsahy

Pojmenované rozsahy v aplikaci Excel jsou skvělým způsobem, jak shromažďovat data pod jednou referencí, a díky nepřímé funkci se tyto informace o něco snáze získávají. Postup je následující:

1. 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 jídel a nápojů, přičemž peníze vydělané za každý den v týdnu jsou shromažďovány v pojmenovaných rozsazích s názvem po produktech.

2. Vyberte buňku pro pojmenovaný rozsah a zadejte do ní jednu z nich. V našem příkladu jsme použili Burgery. Pokud chcete, přidejte další názvy designérů a vybarvení.

   

3. Vyberte jinou buňku, kam má nepřímý výstup přejít. Jelikož chceme sčítat veškerý objem prodeje od týdne u konkrétního jídla, v tomto případě Burgers, do buňky napíšeme následující:

   = SUM (NEPŘÍMÝ (G5)

4. To označuje funkci SUM, která použije nepřímou funkci k čerpání informací z pojmenovaného rozsahu v buňce G5, v tomto případě Burgery. Výstup je 3781, což je celkový prodej za tento týden pro hamburgery. V našem příkladu můžeme nahradit Burgery v buňce G5 limonádou nebo dezerty, dalšími dvěma pojmenovanými rozsahy a místo toho se výstup změní na jejich SUM.

   

Použití nepřímé funkce na více listech

Nepřímý vzorec je ještě silnější, když jej použijete k získání informací z jiných listů. K tomu nemusíte používat pojmenované rozsahy.

1. Otevřete dokument aplikace Excel s více listy nebo je vytvořte se všemi potřebnými informacemi.

2. V listu, kam chcete použít nepřímý výstup, vytvořte buňku s názvem listu, ze kterého chcete čerpat informace. V našem příkladu je Prodej potravin.

3. Protože chceme získat informace z našeho Prodej potravin list na celkový počet Burgery prodáno, zadali jsme následující (nahraďte rozsah buněk a názvy listů vlastním):

   = SUM (NEPŘÍMÉ (B4 & „! B4: B10“))

   

4. Toto jej označuje jako funkci SUMA, protože se snažíme najít celkem. Poté označí buňku B4 jako referenční text pro nepřímou funkci. The & spojuje prvky této funkce, následuje citace a vykřičník a poté rozsah buněk, ze kterých chceme data čerpat. B4 přes B10.

5. Výstupem je celkový prodej Burgerů za daný týden. Když vytváříme nový list FoodSales2 pro nový týden s různými čísly, musíme pouze upravit buňku B4 Prodej potravin2 získat údaje o tržbách 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 R1C1 Style s nepřímým vzorcem, aby vám poskytly informace, které potřebujete. Budeme zde nadále používat naše příklady prodeje potravin, ale představte si, že jde o list vyšší úrovně, který zkoumá celkové týdenní tržby.

1. Otevřete dokument Excel se všemi daty, ze kterých chcete čerpat, a vyberte buňku pro nepřímý výstup funkce. V našem příkladu zkoumáme měsíční součty prodejů potravin a chceme znát nejnovější souhrnný prodej za měsíc.

2. V našem příkladu vypadá vzorec takto:

   = NEPŘÍMÉ („R12C“ & COUNTA (12:12), FALSE)

   

3. Nepřímá funkce používá R12 (řádek 12) následovaný a C k označení sloupce uzavřeného v uvozovkách. The & spojuje dvě části funkce dohromady. Používáme COUNTA funkce spočítat všechny neprázdné buňky v řádku 12 (výběr řádku nebo psaní 12:12), následovaný čárkou. FALSE to označuje jako referenci R1C1.

4. Výstupem je pak poslední položka v naší tabulce, v tomto případě 8102, nebo 8 102 $. Když nakonec přidáme dubnová prodejní data, nejnovější prodejní číslo se automaticky aktualizuje v reálném čase.