Stáhněte si ukázkový sešit
Tento výukový program ukazuje, jak používat Excel NEPŘÍMÁ funkce v aplikaci Excel vytvořit odkaz na buňku z textu.
NEPŘÍMÝ přehled funkcí
NEPŘÍMÁ Funkce vytvoří odkaz na buňku z textového řetězce.
(Všimněte si, jak se zobrazují vstupy vzorců)
NEPŘÍMÁ funkce Syntaxe a vstupy:
| 1 | = NEPŘÍMÝ (ref_text, C1) |
ref_text - Řetězec představující odkaz na buňku nebo odkaz na rozsah. Řetězec může být ve formátu R1C1 nebo A1 nebo může mít pojmenovaný rozsah.
a1 - VOLITELNÉ: Udává, zda je reference ve formátu R1C1 nebo A1. FALSE pro R1C1 nebo TRUE / vynecháno pro A1.
Co je to NEPŘÍMÁ funkce?
Funkce INDIRECT vám umožňuje zadat textový řetězec a nechat počítač interpretovat tento řetězec jako skutečný odkaz. To lze použít k odkazu na rozsah na stejném listu, jiném listu nebo dokonce na jiném sešitu.
UPOZORNĚNÍ: Funkce INDIRECT je jednou z nestálých funkcí. Většinu času, kdy pracujete ve své tabulce, počítač přepočítá vzorec pouze v případě, že vstupy změnily své hodnoty. Nestálá funkce se však přepočítává každý čas, kdy provedete změnu v jakékoli buňce. Je třeba dbát opatrnosti, abyste zajistili, že nezpůsobíte velký čas přepočtu kvůli nadměrnému používání volatilní funkce nebo s mnoha buňkami závislými na výsledku volatilní funkce.
Vytvořte odkaz na buňku
Řekněme, že chcete načíst hodnotu z A2, ale chcete se ujistit, že váš vzorec zůstává na A2 bez ohledu na vkládání/odebírání nových řádků. Můžete napsat vzorec
| 1 | = NEPŘÍMÝ ("A2") |
Všimněte si, že argumentem uvnitř naší funkce je textový řetězec „A2“, nikoli odkaz na buňku. Protože se jedná o textový řetězec, není nutné uvádět absolutní odkaz jako $ A $ 2. Text se nikdy nezmění, a proto tento vzorec vždy bude ukazovat na A2, bez ohledu na to, kam bude přesunut.
NEPŘÍMÉ číslo řádku
Můžete zřetězit textové řetězce a hodnoty z buněk dohromady. Spíše než psát „A2“, jako jsme to dělali dříve, můžeme z buňky B2 zachytit číselnou hodnotu a použít ji v našem vzorci. Napsali bychom vzorec jako
| 1 | = NEPŘÍMÉ („A“ a B2) |
Zde se používá symbol „&“ ke zřetězení textového řetězce „A“ s hodnotou z buňky B2. Pokud by tedy hodnota B2 byla aktuálně 10, pak by náš vzorec zněl jako
| 123 | = NEPŘÍMÝ („A“ a 10)= NEPŘÍMÝ ("A10")= A10 |
NEPŘÍMÁ hodnota sloupce
Můžete také zřetězit v odkazu na sloupec. Tentokrát řekněme, že víme, že chceme získat hodnotu z řádku 10, ale chceme mít možnost změnit, ze kterého sloupce se má tahat. Požadované písmeno sloupce vložíme do buňky B2. Náš vzorec by mohl vypadat
| 1 | = NEPŘÍMÉ (B2 a „10“) |
Pokud je hodnota B2 „G“, pak náš vzorec vyhodnotí takto
| 123 | = NEPŘÍMÝ („G“ a 10)= NEPŘÍMÝ ("G10")= G10 |
NEPŘÍMÝ styl r1c1
V našem předchozím příkladu jsme museli použít písmeno k označení odkazu na sloupec. Důvodem je, že jsme používali to, co je známé jako odkazování na styl A1. Ve stylu A1 jsou sloupce dány písmenem a řádky číslicemi. Absolutní reference jsou označeny pomocí „$“ před položkou, u které chceme zůstat absolutní.
V r1c1 jsou řádky i sloupce spuštěny pomocí čísla. Absolutní odkaz na a1 by byl zapsán jako
| 1 | = R1C1 |
Můžete si to přečíst jako „řádek 1, sloupec 1“. Relativní odkazy jsou uvedeny v závorkách, ale číslo označuje pozici vzhledem k buňce se vzorcem. Pokud bychom tedy psali vzorec v buňce A10 a potřebujeme odkazovat na A1, napsali bychom vzorec
| 1 | = R [-9] C |
Můžete to přečíst jako „Buňka o 9 řádků výše, ale ve stejném sloupci.
Může to být užitečné, protože INDIRECT může podporovat použití notace r1c1. Zvažte předchozí příklad, kde jsme načítali hodnotu z řádku 10, ale chtěli jsme moci sloupec změnit. Místo psaní dopisu řekněme, že do buňky B2 vložíme číslo. Náš vzorec by pak mohl vypadat
| 1 | = NEPŘÍMÝ („R10C“ a B2, NEPRAVDA) |
Vynechali jsme 2nd hádka až dosud. Pokud je tento argument vynechán nebo True, funkce se vyhodnotí pomocí stylu A1. Protože je nepravdivý, vyhodnotí se v r1c1. Předpokládejme, že hodnota B2 je 5. Náš vzorec to vyhodnotí takto
| 12 | = NEPŘÍMÝ ("R10C5", NEPRAVDA)= $ E $ 10 |
NEPŘÍMÉ rozdíly s A1 vs r1c1
Pamatujte, že jsme dříve ukázali, že jelikož obsah tohoto vzorce byl textový řetězec, nikdy se nezměnil?
| 1 | = NEPŘÍMÝ ("A2") |
Tento vzorec bude vždy vypadat na buňku A2, bez ohledu na to, kde vzorec přesunete. V r1c1, protože můžete určit relativní polohu pomocí závorek, toto pravidlo nezůstává konzistentní. Pokud tento vzorec umístíte do buňky B2
| 1 | = NEPŘÍMÝ ("RC [-1]") |
Bude se dívat na buňku A2 (protože sloupec A je jeden nalevo od sloupce B). Pokud zkopírujete tento vzorec do buňky B3, text uvnitř zůstane stejný, ale NEPŘÍMÝ se nyní bude dívat na buňku A3.
NEPŘÍMÉ s názvem listu
Můžete také zkombinovat název listu do vašich NEPŘÍMÝCH odkazů. Důležitým pravidlem, které je třeba mít na paměti, je, že byste kolem názvů měli umisťovat jednoduché uvozovky a název listu od odkazu na buňku je třeba oddělit vykřičníkem.
Řekněme, že jsme měli toto nastavení, kde uvádíme název listu, řádek a sloupec.
Náš vzorec pro kombinování všech těchto prvků do odkazu by vypadal takto:
| 1 | = NEPŘÍMÉ ("'" & A2 & "'!" & B2 & C2) |
Náš vzorec pak bude vyhodnocen takto:
| 123 | = NEPŘÍMÉ ("'" & "List2" & "'!" & "B" & "5")= NEPŘÍMÝ ("'" List2'! B5 ")= 'List2'! B5 |
Technicky vzato, protože slovo „Sheet2“ neobsahuje žádné mezery, nemáme potřeba jednoduché uvozovky. Je naprosto platné napsat něco takového
| 1 | = List2! A2 |
Není však na škodu umístit uvozovky, když je nepotřebujete. Doporučuje se zahrnout je tak, aby váš vzorec zvládl instance tam, kde by mohly být potřeba.
NEPŘÍMO do jiného sešitu
Zmíníme také, že INDIRECT může vytvořit odkaz na jiný sešit. Omezením je, že INDIRECT nebude načítat hodnoty z uzavřeného sešitu, takže toto konkrétní použití má omezenou praktičnost. Pokud sešit, na který INDIRECT ukazuje, není otevřený, funkce vyvolá „#REF!“ chyba.
Syntaxe při psaní názvu sešitu je, že musí být v hranatých závorkách. Použijme toto nastavení a zkusme načíst hodnotu z buňky C7.
Náš vzorec by byl
| 1 | = NEPŘÍMÉ ("'[" & A2 & "]" & B2 & "'! C7") |
Opět věnujte pozornost umístění jednoduchých uvozovek, závorek a vykřičníku. Náš vzorec pak bude vyhodnocen takto:
| 123 | = NEPŘÍMÉ ("'[" & "Sample.xlsx" & "]" & "Souhrn" & "'! C7")= NEPŘÍMÉ ("'[Sample.xslx] Souhrn'! C7")= '[Sample.xlsx] Souhrn'! C7 |
NEPŘÍMÉ pro vytvoření dynamického rozsahu
Když máte velkou sadu dat, je důležité zkusit a optimalizovat vzorce tak, aby nevykonávaly více práce, než je potřeba. Například místo odkazování na všechny sloupce A můžeme chtít pouze odkazovat na přesný počet buněk v našem seznamu. Zvažte následující rozložení:
Do buňky B2 jsme umístili vzorec
| 1 | = COUNTA (A: A) |
Funkce COUNTA je pro počítač velmi snadno vypočítatelná, protože jednoduše kontroluje, kolik buněk ve sloupci A má nějakou hodnotu, na rozdíl od nutnosti provádět logické kontroly nebo matematické operace.
Nyní sestavíme náš vzorec, který sečte hodnoty ve sloupci A, ale chceme zajistit, aby se díval pouze na přesný rozsah s hodnotami (A2: A5). Náš vzorec napíšeme jako
| 1 | = SUM (NEPŘÍMÝ ("A2: A" & B2)) |
Náš NEPŘÍMÝ chytí číslo 5 z buňky B2 a vytvoří odkaz na rozsah A2: A5. SUM pak může použít tento rozsah pro svůj výpočet. Pokud do buňky A6 přidáme další hodnotu, číslo v B2 se aktualizuje a náš vzorec SUM se také automaticky aktualizuje, aby zahrnoval tuto novou hodnotu.
UPOZORNĚNÍ: Se zavedením tabulek v Office 2007 je mnohem efektivnější ukládat data do tabulky a používat strukturální referenci než stavět vzorec, který jsme použili v tomto příkladu, kvůli nestálé povaze INDIRECT. Mohou to však být případy, kdy potřebujete vytvořit seznam položek a nemůžete použít tabulku.
Dynamické grafy s NEPŘÍMÝM
Vezměme si předchozí příklad a přejdeme k dalšímu kroku. Spíše než psát vzorec, který nám poskytne součet hodnot, vytvoříme pojmenovaný rozsah. Tento rozsah bychom mohli nazvat „MyData“ a nechat ho odkazovat
| 1 | = NEPŘÍMÉ ("A2: A" & COUNTA ($ A: $ A)) |
Všimněte si toho, protože protože to dáváme do pojmenovaného rozsahu, vyměnili jsme odkaz na B2 a místo toho jsme tam přímo vložili funkci COUNTA.
Nyní, když máme tento pojmenovaný rozsah, můžeme jej použít v grafu. Vytvoříme prázdný řádkový graf a poté přidáme datovou řadu. Pro hodnoty řady můžete napsat něco jako
| 1 | = List1! MyData |
Graf nyní bude používat tento odkaz k vykreslení hodnot. Jak jsou do sloupce A přidávány další hodnoty, NEPŘÍMÝ bude odkazovat na větší a větší rozsah a náš graf bude i nadále aktualizován o všechny nově přidané hodnoty.
Dynamické ověřování dat pomocí NEPŘÍMÉHO
Při shromažďování vstupů od uživatelů někdy existuje potřeba, aby možnosti jedné volby z čeho vybírat, závisí na předchozí volbě. Zvažte toto rozložení, kde náš první sloupec umožňuje uživateli vybrat si mezi ovocem, zeleninou a masem.
Ve 2nd sloupec, nechceme mít velký seznam zobrazující všechny možné možnosti, protože jsme již věci trochu zúžili. Vytvořili jsme tedy další 3 seznamy, které vypadají takto:
Dále přiřadíme každý z nich tyto seznamy do pojmenovaného rozsahu. Tj., Všechno ovoce bude v řadě zvané „Ovoce“ a zelenina v „Zelenině“ atd.
Zpátky v naší tabulce jsme připraveni nastavit ověření dat ve 2nd sloupec. Vytvoříme ověření typu Seznam se zadáním:
| 1 | = NEPŘÍMÝ (A2) |
NEPŘÍMÝ bude číst volbu provedenou ve sloupci A a uvidí název kategorie. Definovali jsme rozsahy s těmito názvy, takže NEPŘÍMÝ poté převezme tento název a vytvoří odkaz na požadovaný rozsah.
Další poznámky
Pomocí NEPŘÍMÉ funkce vytvořte odkaz na buňku z textu.
Nejprve vytvořte řetězec textu, který představuje odkaz na buňku. Řetězec musí být buď v obvyklém sloupci písmene a čísle řádku ve stylu A1 (M37), nebo ve stylu R1C1 (R37C13). Odkaz můžete zadat přímo, ale obvykle budete odkazovat na buňky, které definují řádky a sloupce. Nakonec zadejte formát odkazu na buňku, který si vyberete. TRUE nebo vynecháno pro odkaz stylu A1 nebo FALSE pro styl R1C1.
Při práci s NEPŘÍMÝMI vzorci možná budete chtít použít Funkce ŘADA získat číslo řádku odkazu nebo Funkce COLUMN získat číslo sloupce (nikoli písmeno) odkazu.
Zpět na seznam všech funkcí v Excelu
NEPŘÍMÝ v Tabulkách Google
Funkce INDIRECT funguje v Tabulkách Google úplně stejně jako v Excelu:
