Stiahnite si ukážkový zošit
Tento návod ukazuje, ako používať Excel NEPRIAMA funkcia v Exceli na vytvorenie odkazu na bunku z textu.
NEPRIAMY prehľad funkcií
NEPRIAMA funkcia vytvorí odkaz na bunku z textového reťazca.
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
NEPRIAMA funkcia Syntax a vstupy:
| 1 | = NEPRIAMY (ref_text, C1) |
ref_text - Reťazec predstavujúci odkaz na bunku alebo odkaz na rozsah. Reťazec môže mať formát R1C1 alebo A1 alebo môže mať pomenovaný rozsah.
a1 - VOLITEĽNÉ: Udáva, či je referencia vo formáte R1C1 alebo A1. FALSE pre R1C1 alebo TRUE / vynechané pre A1.
Čo je to funkcia INDIRECT?
Funkcia INDIRECT vám umožňuje zadať textový reťazec a nechať počítač interpretovať tento reťazec ako skutočnú referenciu. Toto je možné použiť na odkazovanie na rozsah na rovnakom hárku, inom hárku alebo dokonca na inom zošite.
UPOZORNENIE: Funkcia INDIRECT je jednou z volatilných funkcií. Väčšinu času, keď pracujete so svojou tabuľkou, počítač prepočíta vzorec iba vtedy, ak vstupy zmenili svoje hodnoty. Prchavá funkcia sa však prepočítava každý čas, kedy vykonáte zmenu v akejkoľvek bunke. Je potrebné postupovať opatrne, aby ste zaistili, že nespôsobíte veľký čas prepočtu v dôsledku nadmerného používania volatilnej funkcie alebo veľkého počtu buniek závislých od výsledku volatilnej funkcie.
Vytvorte odkaz na bunku
Povedzme, že chcete načítať hodnotu z A2, ale chcete sa uistiť, že váš vzorec pobyty na A2 bez ohľadu na vkladanie/vyberanie nových riadkov. Môžete napísať vzorec z
| 1 | = NEPRIAMY ("A2") |
Všimnite si toho, že argument v našej funkcii je textový reťazec „A2“, nie odkaz na bunku. Pretože sa jedná o textový reťazec, nie je potrebné uvádzať absolútny odkaz ako $ A $ 2. Text sa nikdy nezmení, a preto tento vzorec bude vždy smerovať na A2, bez ohľadu na to, kam sa presunie.
NEPRIAMY riadok
Môžete spájať textové reťazce a hodnoty z buniek spoločne. Namiesto písania „A2“, ako sme to urobili predtým, môžeme z bunky B2 získať číselnú hodnotu a použiť ju v našom vzorci. Napísali by sme vzorec ako
| 1 | = NEPRIAMY („A“ a B2) |
Tu sa používa symbol „&“ na zreťazenie textového reťazca „A“ s hodnotou z bunky B2. Ak by teda hodnota B2 bola v súčasnosti 10, náš vzorec by to čítal ako
| 123 | = NEPRIAMY („A“ a 10)= NEPRIAMY ("A10")= A10 |
NEPRIAMA hodnota stĺpca
Môžete tiež zreťaziť v odkaze na stĺpec. Tentoraz povedzme, že vieme, že chceme získať hodnotu z riadka 10, ale chceme mať možnosť zmeniť, z ktorého stĺpca sa má čerpať. Požadované písmeno stĺpca vložíme do bunky B2. Náš vzorec by mohol vyzerať
| 1 | = NEPRIAMY (B2 a „10“) |
Ak je hodnota B2 „G“, potom sa náš vzorec vyhodnotí takto
| 123 | = NEPRIAMY („G“ a 10)= NEPRIAMY („G10“)= G10 |
NEPRIAMY štýl r1c1
V našom predchádzajúcom príklade sme museli použiť písmeno na označenie odkazu na stĺpec. Dôvodom je, že sme používali to, čo je známe ako odkazovanie na štýl A1. V štýle A1 sú stĺpce dané písmenom a riadky sú dané číslami. Absolútne referencie sú označené pomocou „$“ pred položkou, ktorou chceme zostať absolútni.
V r1c1 sa riadky aj stĺpce začínajú pomocou čísla. Absolútny odkaz na a1 by bol zapísaný ako
| 1 | = R1C1 |
Môžete si to prečítať ako „riadok 1, stĺpec 1“. Relatívne odkazy sú uvedené v zátvorkách, ale číslo označuje polohu vzhľadom na bunku so vzorcom. Ak by sme teda písali vzorec v bunke A10 a potrebujeme sa odvolávať na A1, napísali by sme vzorec
| 1 | = R [-9] C |
Môžete to prečítať ako „Bunka o 9 riadkov vyššie, ale v rovnakom stĺpci.
Toto môže byť užitočné, pretože INDIRECT môže podporovať používanie notácie r1c1. Zoberme si predchádzajúci príklad, kde sme získavali hodnotu z riadka 10, ale chceli sme mať možnosť zmeniť stĺpec. Namiesto toho, aby sme dali list, povedzme, že do bunky B2 dáme číslo. Náš vzorec potom môže vyzerať
| 1 | = NEPRIAMY („R10C“ a B2, NEPRAVDA) |
Vynechali sme 2nd hádka až doteraz. Ak je tento argument vynechaný alebo True, funkcia bude vyhodnotená pomocou štýlu A1. Keďže je to nepravda, vyhodnotí sa to v r1c1. Predpokladajme, že hodnota B2 je 5. Náš vzorec to vyhodnotí takto
| 12 | = NEPRIAMY ("R10C5", NEPRAVDA)= $ E $ 10 |
NEPRIAMY rozdiel s A1 vs r1c1
Nezabudnite, že sme predtým ukázali, že keďže obsah tohto vzorca bol textový reťazec, nikdy sa nezmenil?
| 1 | = NEPRIAMY ("A2") |
Tento vzorec sa bude vždy pozerať na bunku A2 bez ohľadu na to, kam vzorec presuniete. V r1c1, pretože relatívnu polohu môžete uviesť pomocou zátvoriek, toto pravidlo nezostáva konzistentné. Ak tento vzorec umiestnite do bunky B2
| 1 | = NEPRIAMY ("RC [-1]") |
Bude sa pozerať na bunku A2 (pretože stĺpec A je jeden vľavo od stĺpca B). Ak skopírujete tento vzorec do bunky B3, text vo vnútri zostane rovnaký, ale NEPRIAMY sa teraz bude pozerať na bunku A3.
NEPRIAMY s názvom listu
Môžete tiež skombinovať názov hárka do vašich NEPRIAMYCH referencií. Dôležitým pravidlom, ktoré by ste si mali zapamätať, je, že okolo názvov by ste mali umiestniť jednoduché úvodzovky a názov hárka od odkazu na bunku je potrebné oddeliť výkričníkom.
Povedzme, že sme mali toto nastavenie, kde uvádzame názov nášho hárka, riadok a stĺpec.
Náš vzorec na kombináciu všetkých týchto prvkov do referencie bude vyzerať takto:
| 1 | = NEPRIAMY ("'" & A2 & "'!" & B2 & C2) |
Náš vzorec sa potom vyhodnotí takto:
| 123 | = NEPRIAMY ("'" & "List2" & "'!" & "B" & "5")= NEPRIAMY ("'" List2'! B5 ")= 'List2'! B5 |
Technicky vzaté, pretože slovo „Sheet2“ nemá žiadne medzery, my nie potrebovať jednoduché úvodzovky. Je úplne platné napísať niečo podobné
| 1 | = List2! A2 |
Nie je na škodu umiestniť úvodzovky, keď ich nepotrebujete. Je osvedčené zahrnúť ich tak, aby váš vzorec zvládol inštanciu tam, kde by to mohlo byť potrebné.
NEPRIAME na iný zošit
Tiež spomenieme, že INDIRECT môže vytvoriť odkaz na iný zošit. Obmedzením je, že INDIRECT nebude načítavať hodnoty z uzavretého zošita, takže toto konkrétne použitie má obmedzenú praktickosť. Ak zošit, na ktorý NEPRIAMY ukazuje, nie je otvorený, funkcia vyvolá „#REF!“ chyba.
Syntax pri písaní názvu zošita je, že musí byť v hranatých zátvorkách. Použime toto nastavenie a pokúsime sa načítať hodnotu z bunky C7.
Náš vzorec by bol
| 1 | = NEPRIAMY ("'[" & A2 & "]" & B2 & "'! C7") |
Opäť dávajte pozor na umiestnenie jednoduchých úvodzoviek, zátvoriek a výkričníkov. Náš vzorec sa potom vyhodnotí takto:
| 123 | = NEPRIAMY ("'[" & "Sample.xlsx" & "]" & "Súhrn" & "'! C7")= NEPRIAMY ("'[Sample.xslx] Súhrn'! C7")= '[Sample.xlsx] Súhrn'! C7 |
NEPRIAME na vytvorenie dynamického rozsahu
Keď máte veľký súbor údajov, je dôležité pokúsiť sa optimalizovať vzorce tak, aby nevykonávali viac práce, ako je potrebné. Napríklad namiesto odkazovania na všetky stĺpce A by sme chceli odkazovať na presný počet buniek v našom zozname. Zvážte nasledujúce rozloženie:
Do bunky B2 sme vložili vzorec
| 1 | = COUNTA (A: A) |
Funkciu COUNTA počítač ľahko vypočíta, pretože jednoducho kontroluje, koľko buniek v stĺpci A má nejakú hodnotu, na rozdiel od toho, že je potrebné vykonávať akékoľvek logické kontroly alebo matematické operácie.
Teraz zostavme náš vzorec, ktorý spočíta hodnoty v stĺpci A, ale chceme sa uistiť, že sa pozerá iba na presný rozsah s hodnotami (A2: A5). Náš vzorec napíšeme ako
| 1 | = SÚČET (NEPRIAMY ("A2: A" a B2)) |
Náš NEPRIAMY chytí číslo 5 z bunky B2 a vytvorí odkaz na rozsah A2: A5. SUM potom môže použiť tento rozsah na svoj výpočet. Ak do bunky A6 pridáme inú hodnotu, číslo v B2 sa aktualizuje a náš vzorec SUMA sa automaticky aktualizuje, aby zahrnul aj túto novú hodnotu.
UPOZORNENIE: So zavedením tabuliek v balíku Office 2007 je oveľa efektívnejšie ukladať údaje do tabuľky a používať štruktúrované referencie, než stavať vzorec, ktorý sme použili v tomto prípade, kvôli volatilnej povahe INDIRECT. Môžu to však byť prípady, keď potrebujete vytvoriť zoznam položiek a nemôžete použiť tabuľku.
Dynamické grafy s NEPRIAMYM
Vezmime si predchádzajúci príklad a prejdeme ešte o krok. Namiesto písania vzorca, ktorý nám poskytne súčet hodnôt, vytvoríme pomenovaný rozsah. Tento rozsah by sme mohli nazvať „MyData“ a nechať sa naň odkazovať
| 1 | = NEPRIAMY ("A2: A" A COUNTA ($ A: $ A)) |
Všimnite si toho, pretože pretože to zaraďujeme do pomenovaného rozsahu, vymenili sme odkaz na B2 a namiesto toho sme tam priamo vložili funkciu COUNTA.
Teraz, keď máme tento pomenovaný rozsah, mohli by sme ho použiť v grafe. Vytvoríme prázdny čiarový graf a potom pridáme rad údajov. Pre sériové hodnoty by ste mohli napísať niečo ako
| 1 | = List1! MyData |
Tabuľka bude teraz používať tento odkaz na vykreslenie hodnôt. Keď sú do stĺpca A pridané ďalšie hodnoty, NEPRIAMY bude odkazovať na väčší a väčší rozsah a náš graf bude aj naďalej aktualizovaný o všetky novo pridané hodnoty.
Dynamické overovanie údajov pomocou NEPRIAMO
Pri zhromažďovaní vstupov od používateľov niekedy existuje potreba urobiť z možností jednej voľby, z ktorých si vyberiete, v závislosti od predchádzajúcej voľby. Zvážte toto rozloženie, kde náš prvý stĺpec umožňuje používateľovi vybrať si medzi ovocím, zeleninou a mäsom.
V 2nd stĺpci, nechceme mať veľký zoznam zobrazujúci všetky možné možnosti, pretože sme už veci trochu zúžili. Vytvorili sme teda ďalšie 3 zoznamy, ktoré vyzerajú takto:
Ďalej priradíme každú z nich títo zoznamy do pomenovaného rozsahu. T.j., všetko ovocie bude v rozsahu nazývanom „ovocie“ a zelenina v „zelenine“ atď.
V našej tabuľke sme pripravení nastaviť validáciu údajov v 2nd stĺpci. Vytvoríme overenie typu zoznamu so zadaním:
| 1 | = NEPRIAMY (A2) |
NEPRIAMY bude čítať voľbu vykonanú v stĺpci A a uvidí názov kategórie. Definovali sme rozsahy s týmito názvami, takže INDIRECT potom prevezme tento názov a vytvorí odkaz na požadovaný rozsah.
Doplňujúce Poznámky
Na vytvorenie odkazu na bunku z textu použite funkciu NEPRIAMY.
Najprv vytvorte reťazec textu, ktorý predstavuje odkaz na bunku. Reťazec musí mať buď obvyklé číslo stĺpca a štýlu stĺpca v štýle A1 (M37), alebo štýl R1C1 (R37C13). Referenciu môžete zadať priamo, ale zvyčajne budete odkazovať na bunky, ktoré definujú riadky a stĺpce. Nakoniec zadajte požadovaný formát odkazu na bunku. TRUE alebo vynechané pre referenciu štýlu A1 alebo FALSE pre štýl R1C1.
Pri práci s NEPRIAMYMI vzorcami možno budete chcieť použiť Riadková funkcia získať číslo riadka referencie alebo COLUMN (funkcia) získať číslo stĺpca (nie písmeno) referencie.
Vráťte sa do zoznamu všetkých funkcií v programe Excel
NEPRIAMY v Tabuľkách Google
Funkcia NEPRIAMY funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
