Jak používat nepřímou funkci v Excelu

Jakmile budete vědět, jak používat nepřímou funkci v Vynikat, můžete získat informace z jiných listů, odkazovat na pojmenované rozsahy a kombinovat je s další funkce vytvořit skutečně všestranný nástroj. Zorientovat se může vyžadovat trochu cviku, ale s nepřímou funkcí můžete udělat více, než byste si mysleli.

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ídá datům, ze kterých čerpá.

Pokud se vám to zdá trochu matoucí, nezlobte se. Nepřímý vzorec může být snáze srozumitelný s platnými příklady a v praxi. Pokud máte pochybnosti, vyzkoušejte níže uvedené kroky 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 jedním odkazem, a nepřímá funkce z nich usnadňuje získání těchto informací. Jak na to:

1. Otevřete dokument aplikace Excel pomocí pojmenované rozsahy již byly použity. V našem příkladu máme informace o prodeji různých potravin a nápojů, přičemž peníze vydělané za každý den v týdnu se shromažďují v pojmenovaných řadách s názvem podle produktů.

   

2. Vyberte buňku pro pojmenovanou oblast a jednu z nich do ní vložte. V našem příkladu jsme použili Burgery. Pokud si přejete, přidejte další názvy a vybarvení.

   

3. Vyberte jinou buňku, kam chcete nepřímý výstup přesunout. Vzhledem k tomu, že chceme sečíst celý objem prodeje z týdne za konkrétní jídlo, v tomto případě Burgery, zadáme do buňky následující:

   =SUM(NEPŘÍMÉ(G5)

4. To 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ě Burgery. Výstup je 3781, celkový prodej za týden pro Burgery.

   V našem příkladu můžeme hamburgery 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.

   

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.

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

2. 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 Prodej potravin.

3. Protože chceme čerpat 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ů svými):

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

   

4. To 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. The & 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 přes B10.

5. Výstupem 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, abychom řekli Prodej potravin2 získat údaje o prodejí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 reference, kterou chcete použít, nebude vždy v stejné buňky, odkazy na styl R1C1 lze použít s nepřímým vzorcem, aby vám poskytly informace potřeba. 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.

1. Otevřete dokument aplikace 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ý prodej za daný měsíc.

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

   =NEPŘÍMÉ("R12C"&COUNTA(12:12);NEPRAVDA)

   

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 funkci COUNTA k počítání všech neprázdných buněk v řádku 12 (výběr řádku nebo zadání 12:12), za kterými následuje čárka. FALSE to označuje jako odkaz R1C1.

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