NEPŘÍMÁ funkce v aplikaci Excel
Nepřímá funkce v aplikaci Excel je vestavěná funkce, která slouží k odkazování a získávání hodnot buněk z textového řetězce, tento vzorec má odkaz z odkazované buňky, má dva argumenty, první argument není volitelný, zatímco druhý argument je volitelný, což odkazuje na typ shody, lze tuto funkci použít také s jiným vzorcem.
Funkce INDIRECT v aplikaci Excel má několik použití. Vrátí odkaz na buňku určený textovým řetězcem. Pokud například buňka B1 obsahuje řetězec, řekněme rozsah c4 a c4 mají hodnotu „Jessica“. Nyní, pokud použijeme funkci INDIRECT a předáme referenční text jako B1, vrátí hodnotu obsaženou v rozsahu buněk c4, která je „Jessica“.
Funkce INDIRECT aplikace Excel umožňuje zadat adresu buňky NEPŘÍMĚ. Jak můžete ve výše uvedeném NEPŘÍMÉM příkladu aplikace Excel, pokud buňka B1 obsahuje text C4, vrátí tento vzorec nepřímého excelu obsah buňky C4 ( Jessica ). Tato funkce je velmi užitečná. Funkci INDIRECT můžete použít, kdykoli chcete změnit odkaz na buňku ve vzorci nepřímého excelu, aniž byste změnili samotný vzorec.
NEPŘÍMÝ Excel vzorec
Vysvětlení
Ref_text je odkaz na buňku, která obsahuje odkaz ve stylu A1, odkaz ve stylu R1C1, název definovaný jako odkaz nebo odkaz na buňku jako textový řetězec.
Argument a1 je volitelný
| a1 | False nebo 0, když je ref_text ve stylu R1C1 |
| a1 | Vynecháno nebo True (1), když je ref_text ve stylu A1 |
Jak používat funkci NEPŘÍMÝ v aplikaci Excel?
Pojďme si před použitím funkce adresy v sešitu aplikace Excel vzít INDIRECT excel příklad:
Předpokládejme, že pro web www.xyz.com máme analytická data Google pro návštěvníky webu z různých kanálů publika pro pět různých zemí, jak je uvedeno níže v tabulce:
Můžeme použít funkci INDIRECT v aplikaci Excel za účelem převodu textového řetězce na odkaz, takže odkazujeme na buňku H4 a použijeme tento název jako odkaz. Použití funkce INDIRECT v aplikaci Excel v tomto případě spočívá v odkazování na rozsahy jmen pomocí hodnot buňky, takže v buňce I4 bychom mohli jednoduše použít funkci sum k výpočtu celkového počtu návštěvníků ze země Kanada.
Nyní můžeme přímo spočítat součet návštěvníků z každé země pomocí funkce součtu, ale pomocí funkce INDIRECT v aplikaci Excel můžeme vypočítat součet návštěvníků z různých zemí změnou odkazu na název namísto změny samotného vzorce nepřímého excelu.
Vytvořili jsme rozsah jmen pro každého návštěvníka země; to lze snadno provést zadáním rozsahu jmen do prostoru těsně pod schránkou, jak je znázorněno níže.
Další metodou je, že můžete přejít do vzorců-> správce jmen -> vyberte rozsah, který chcete pojmenovat -> klikněte na správce jmen -> zadejte rozsah jmen.
Podobně vytvořila pojmenovanou řadu v aplikaci Excel i pro návštěvníky z jiných zemí.
Nyní v I4, výpočet součtu návštěvníka ze země Kanada pomocí funkce NEPŘÍMÝ a pomocí rozsahu jmen v H4 (Kanada, výše uvedený název pro F3: F7), dostaneme celkovou součet návštěvníka.
Pokud vytvoříme seznam dat pomocí ověření dat pro země a použijeme tento seznam na H4
A změnou různých názvů zemí z Kanady na Indii, USA, Austrálii nebo Singapuru získáme celkový součet návštěvníka v I4 pomocí funkce NEPŘÍMÝ v aplikaci Excel.
Takže pro každý případ neměníme vzorec nepřímého excelu, který je v buňce I4; měníme odkaz na jméno v H4 a počítáme součet návštěvníků pro každou zemi.
Můžeme se posunout o krok dále a můžeme odkazovat na názvy na úrovni listů také pomocí funkce INDIRECT v aplikaci Excel. Například nyní použijeme funkci NEPŘÍMÝ v odkazu na různé listy v sešitu.
Vytvořili jsme různé listy s názvy zemí pro celkový počet prodejů uskutečněných s každým vyhledávacím kanálem pro různé země.
Pro Indii
Pro USA
Podobně pro ostatní země.
Opět budeme počítat celkový prodej uskutečněný prostřednictvím každého kanálu pomocí funkce INDIRECT v aplikaci Excel pro různé listy s názvy zemí jako referencí.
V tomto případě, protože máme prodejní data v různých listech, budeme muset zřetězit různé prvky, které jsou datovým rozsahem, takže ref_text bude začínat názvem listu, což je v tomto případě Indie v B4, pak budeme použijte operátor & zvaný ampersand, který se používá pro zřetězení.
Dostaneme tedy celkovou částku prodeje pro Indii; pokud nyní změníme název země listu v B4 z Indie na jiné země, jako je USA, Singapur nebo Kanada, získáme celkovou prodejní hodnotu těchto zemí.
Opět v tomto případě jsme jednoduše změnili hodnoty ref_text v buňce B4 beze změny hlavního vzorce nepřímého excelu, který je v C4, abychom získali součet prodejů pro různé země, které jsou v různých listech pomocí funkce NEPŘÍMÝ.
Nyní použijeme funkci INDIRECT pomocí odkazu R1C1, abychom získali prodejní hodnotu za poslední měsíc. Máme prodeje za různé měsíce ve sloupcích B, C a D po dobu tří měsíců, leden, únor a březen, které mohou růst na další měsíce, jako je duben, květen, červen atd. Tabulka prodeje tedy bude vždy růst v závislosti na přidání prodeje v nadcházejícím měsíci. V tomto příkladu NEPŘÍMÉHO Excelu tedy chceme vypočítat prodej za poslední měsíc.
Posledním měsícem je tedy březen a chceme pomocí funkce INDIRECT vypočítat celkový prodej březnového měsíce, což je 249 344 USD.
V tomto příkladu INDIRECT Excel najdeme použití druhého argumentu funkce INDIRECT
NEPŘÍMÝ (ref_text, a1)
a1: což je volitelný argument, je to logická hodnota (logická hodnota), která určuje, jaký typ odkazu (ref_text) je obsažen v buňce
pokud a1 je true (1) nebo vynecháno, ref_text je považován za odkaz ve stylu A1
pokud a1 je false (0), ref_text je interpretován jako styl R1C1
V čem se styl A1 liší od stylu R1C1?
Funkce INDIRECT v aplikaci Excel obvykle používá notaci A1. Každá adresa buňky se skládá z písmene sloupce a čísla řádku. Funkce INDIRECT v aplikaci Excel však také podporuje notaci R1C1. V tomto systému se buňka A1 označuje jako buňka R1C1, buňka A2 jako R2C1 atd.
Chcete-li změnit notaci R1C1, zvolte Soubor-> možnost a otevřete dialogové okno možností aplikace Excel, klepněte na kartu vzorec a zaškrtněte možnost odkazu na styl R1C1. Nyní si všimnete, že všechna písmena sloupců se mění na čísla. A všechny odkazy na buňky ve vzorcích se také upraví.
Když tedy použijeme styl R1C1 ve funkci INDIRECT v aplikaci Excel, předáme hodnotu a1 jako false, a pokud použijeme styl A1, předáme hodnotu true a1.
Nyní v tomto příkladu INDIRECT Excel budeme používat styl R1C1 kvůli požadavku, protože sloupce s měsícem budou růst.
Při použití stylu R1C1 je celková prodejní hodnota v řádku 10 a hodnota posledního sloupce, která je ve sloupci D, bude vypočítána pomocí funkce COUNTA , která poskytne číslo posledního sloupce obsahující hodnotu. Count (10:10) in this case will give 4, which the column number in R1C1 style. Kontaktovali jsme tedy tuto hodnotu, abychom získali R10C4, který obsahuje požadovanou hodnotu, která je celkovou prodejní hodnotou za poslední měsíc (249 344 USD).
A protože zde používáme styl R1C1, předáme falešnou hodnotu argumentu a1.
Nyní přidejme ještě jeden sloupec s měsícem dubnového výprodeje.
Získejte tedy nové hodnoty prodeje z minulého měsíce, které odpovídají hodnotám z posledního měsíce dubna. Podobně, pokud přidáme další měsíce, vždy získáme novou prodejní hodnotu z minulého měsíce, aniž bychom změnili vzorec nepřímého excelu pro každý měsíc. Toto je specialita funkce INDIRECT v aplikaci Excel.
Jak přizpůsobit pole tabulky ve funkci INDIRECT Excel VLOOKUP?
Máme pět zemí a vyhledávací kanál a prodej, který web provedl prostřednictvím těchto různých kanálů z různých zemí. Nyní v buňce B3 chceme získat prodej, který proběhl prostřednictvím organického vyhledávání ze země Spojené státy.
U každé tabulky pro různé země jsme pojmenovali každou tabulku krátkým názvem pro zemi, Ind pro Indii, státy pro USA, Can pro Kanadu atd .
Pokud přímo použijeme funkci INDIRECT Excel vlookup k získání hodnoty, jak je uvedeno níže
Dostaneme chybu, protože funkce INDIRECT Excel vlookup není schopna rozpoznat pole tabulky předané jako hodnotu buňky B2, takže použijeme funkci INDIRECT v aplikaci Excel, aby vlookup umožnil rozpoznání tabulky_array jako platného argumentu.
Pokud změníme hodnoty B1 a B2, dostaneme podle potřeby různé hodnoty Sale v B3.
Poznámka: V rozsahu jmen se nerozlišují velká a malá písmena. Proto - Ind, IND a InD jsou pro Excel stejné.
V dalším příkladu INDIRECT Excel použijeme funkci INDIRECT v aplikaci Excel k vytvoření závislého seznamu. Máme seznam pozic zaměstnance ve společnosti; pak pro každou pozici máme jména zaměstnanců.
Nyní v buňce I2 budeme vytvářet seznam pro různé pozice zaměstnanců jako jejich pozice a v I3 zobrazíme seznam, který bude záviset na hodnotě v I2. Pokud je I2 ředitelem, pak I3 by měl odrážet jména ředitelů, které Andrew a Micheal; pokud I2 je manažer, pak I3 by měl odrážet jména manažera, kterými jsou Camille, David, Davis a William, a stejným způsobem pro supervizora.
V tomto případě opět použijeme funkci INDIRECT v aplikaci Excel k vytvoření závislého seznamu. Ověření dat, v nastaveních výběru kritérií pro ověření jako seznamu a ve zdrojovém poli budeme používat funkci INDIRECT v aplikaci Excel následujícím způsobem.
= NEPŘÍMÉ ($ I $ 2)
Když vybíráme jména ředitelů v I3, odráží se jako seznam se jmény Andrew a Micheal, když I2 je manažer, pak I3 odráží jména manažerů Camille, Davida, Davise a Williama a podobně pro vedoucího, jak je znázorněno níže na obrázku.
Věci k zapamatování
- Argument ref_text musí být platný odkaz na buňku, jinak INDIRECT vrátí #REF! Chyba. Argument ref_text může odkazovat na jiný sešit.
- Pokud ref_text odkazuje na jiný sešit (externí odkaz), musí být otevřen druhý sešit. Pokud zdrojový sešit není otevřený, INDIRECT vrátí #REF! Chybová hodnota.
- Pokud ref_text odkazuje na oblast buněk mimo limit řádku 1 048 576 nebo limit sloupce 16 384 (XFD), vrátí funkce INDIRECT #REF! Chyba.
