Stiahnite si ukážkový zošit
Tento tutoriál ukazuje, ako používať Funkcia Excel ADDRESS v Exceli na vrátenie adresy bunky ako textu.
Prehľad funkcií ADDRESS
Funkcia ADDRESS vracia adresu bunky ako text.
Ak chcete použiť funkciu pracovného hárka ADDRESS Excel, vyberte bunku a zadajte:
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
Funkcia ADDRESS Syntax a vstupy:
| 1 | = ADDRESS (riadok_číslo, stĺpček_číslo, abs_číslo, C1, list_text) |
riadok_číslo - Číslo riadku pre referenciu. Príklad: 5 pre riadok 5.
col_num - Číslo stĺpca pre referenciu. Príklad: 5 pre stĺpec E. Do stĺpca E nemôžete zadať „E“
abs_num - [voliteľné] Číslo predstavujúce, či má mať odkaz absolútny alebo relatívny odkaz na riadok/stĺpec. 1 pre absolútne. 2 pre absolútny riadok/relatívny stĺpec. 3 pre relatívny riadok/absolútny stĺpec. 4 pre príbuzného.
a1 - [voliteľné]. Číslo udávajúce, či sa má použiť štandardný (A1) referenčný formát bunky alebo formát R1C1. 1/TRUE pre Standard (predvolené). 0/NEPRAVDA pre R1C1.
list_text - [voliteľné] Názov pracovného hárka, ktorý sa má použiť. Predvolené pre aktuálny hárok.
Čo je funkcia ADDRESS?
Funkcia ADDRESS je trochu unikátna funkcia. Väčšinu času v tabuľke povieme počítaču odkaz na bunku a tá nám poskytne hodnotu z tejto bunky. S ADRESOU namiesto toho vytvoríme názov bunky. Táto adresa môže byť relatívna alebo absolútna v štýle A1 alebo R1C1 a môže, ale nemusí obsahovať názov hárka. Táto funkcia väčšinou nemá veľa praktických použití, ale dozvedieť sa o nej môže byť zábava. Keď sa používa, je zvyčajne v kombinácii s inými funkciami, ako uvidíme nižšie.
Základný príklad
Povedzme, že chceme vytvoriť odkaz na bunku v 4th stĺpec a 1sv riadok, alias bunka D1. Môžeme použiť rozloženie na obrázku tu:
Náš vzorec v A3 je jednoducho
| 1 | = ADRESA (B1, B2) |
Všimnite si toho, že neuvádzaním konkrétneho argumentu pre relatívny/absolútny, sme získali všetko absolútne. Získali sme tiež predvolený štýl odkazovania typu A1 bez názvu listu.
V kombinácii s NEPRIAMYM
Ako sme už povedali, samotná funkcia ADDRESS nám v skutočnosti nepriniesla nič užitočné. Mohli by sme to však skombinovať s funkciou INDIRECT, aby sme získali nejakú funkciu. Zvážte toto rozloženie, kde máme zoznam položiek v stĺpci D. Ak by sme zachovali rovnaký vzorec ako predtým, vygenerujeme odkaz na D1 takto
| 12 | = ADRESA (B1, B2)= $ D $ 1 |
Keď vložíme funkciu adresy do INDIRECT funkcie, budeme môcť použiť vygenerovanú referenciu na bunku a použiť ju praktickým spôsobom. INDIRECT prevezme odkaz na „$ D $ 1“ a použije ho na načítanie hodnoty z tejto bunky.
| 123 | = NEPRIAMY (ADRESA (B1, B2)= NEPRIAMY ($ D $ 1)= "Apple" |
Poznámka: Aj keď vyššie uvedené je dobrým príkladom toho, ako urobiť funkciu ADDRESS užitočnou, nie je to dobrý vzorec na bežné používanie. Vyžadovalo to dve funkcie a kvôli NEPRIAMOMU bude mať volatilnú povahu. Lepšou alternatívou by bolo použiť INDEX takto: = INDEX (1: 1048576, B1, B2)
Adresa konkrétnej hodnoty
Niekedy, keď máte veľký zoznam položiek, musíte vedieť, kde sa v zozname položka nachádza. Pozrite sa na túto tabuľku skóre od študentov. Pokračovali sme a vypočítali minimálne, stredné a maximálne hodnoty týchto skóre v bunkách E2: G2.
Mohli by sme filtrovať našu tabuľku pre každú z týchto položiek, aby sme zistili, kde sa nachádza (znova si predstavte, že je to oveľa rozsiahlejší zoznam), alebo by sme mohli dokonca použiť podmienené formátovanie, aby sa používateľovi vizuálne zobrazilo. Ak je však zoznam tisíce riadkov, nechceme sa posúvať tak ďaleko, aby sme videli, čo chceme. Namiesto toho použijeme hodnoty v E2: G2 na určenie adresy buniek obsahujúcich naše hodnoty. Na tento účel použijeme funkciu MATCH s adresou ADDRESS. Nezabudnite, že MATCH vráti relatívnu polohu hodnoty v rámci rozsahu.
Náš vzorec v E3 potom je:
| 1 | = ADRESA (ZHODA (E2, $ B: $ B, 0), 2) |
Rovnaký vzorec môžeme skopírovať do G3 a zmení sa iba referencia E2, pretože je to jediná relatívna referencia. Keď sa pozrieme späť na E3, funkcia MATCH dokázala v 5 nájsť hodnotu 98th riadok stĺpca B. Naša funkcia ADDRESS to potom použila na zostavenie úplnej adresy „$ B $ 5“.
Preložte písmená stĺpcov z čísel
Až do tohto bodu všetky naše príklady nechali funkcii ADDRESS vrátiť absolútnu referenciu. Tento nasledujúci príklad vráti relatívnu referenciu. V tejto tabuľke chceme zadať číslo do stĺpca A a vrátiť zodpovedajúci názov stĺpca.
Aby sme dosiahli náš cieľ, necháme funkciu ADDRESS vrátiť odkaz na riadok 1 v relatívnom formáte a potom odstránime „1“ z textového reťazca, takže nám zostanú iba písmená. Uvažujme v rade 3 v tabuľke, kde je náš vstup 13. Náš vzorec v B3 je
| 1 | = NÁHRADA (ADRESA (1, A3, 4), "1", "") |
Všimnite si toho, že sme dali 3rd argument vo funkcii ADDRESS, ktorá riadi relatívne vs. absolútne odkazovanie. Funkcia ADDRESS vydá „M1“ a potom funkcia SUBSTITUTE odstráni „1“, takže nám zostane iba „M“.
Zistite adresu pomenovaných rozsahov
V programe Excel môžete pomenovať rozsah alebo rozsahy buniek, čo vám umožní jednoducho odkazovať na pomenovaný rozsah namiesto odkazu na bunku.
Väčšina pomenovaných rozsahov je statická, čo znamená, že vždy odkazujú na rovnaký rozsah. Môžete však vytvoriť aj dynamické pomenované rozsahy, ktorých veľkosť sa mení podľa niektorých vzorcov.
Pri dynamickom pomenovanom rozsahu možno budete potrebovať poznať presnú adresu, na ktorú váš pomenovaný rozsah ukazuje. Môžeme to urobiť pomocou funkcie ADDRESS.
V tomto prípade sa pozrieme na to, ako definovať adresu pre náš pomenovaný rozsah s názvom „Známky“.
Vráťme si svoj stôl predtým:
Ak chcete získať adresu rozsahu, potrebujete poznať ľavú hornú bunku a dolnú ľavú bunku. Prvú časť je možné ľahko vykonať pomocou funkcie RIADOK a STĹPEC. Náš vzorec v E1 môže byť
| 1 | = ADRESA (RIADOK (stupne), COLUMN (stupne)) |
Funkcia ROW vráti riadok prvej bunky v našom rozsahu (ktorý bude 1) a COLUMN urobí to isté podobne pre stĺpec (tiež 1).
Na získanie bunky v pravom dolnom rohu použijeme funkciu ROWS a COLUMNS. Pretože môžeme zistiť počiatočný bod nášho rozsahu, ak vypočítame, aký vysoký je rozsah, a odčítame náš počiatočný bod, dostaneme správny koncový bod. Vzorec na to vyzerá
| 1 | = ADRESA (RADY (stupne) -ROW (stupne) +1, COLUMNS (stupne) -COLUMN (stupne) +1) |
Nakoniec, aby sme to všetko spojili do jedného reťazca, môžeme jednoducho spojiť hodnoty dohromady s dvojbodkou v strede. Vzorec v E3 môže byť
| 1 | = E1 & ":" & E2 |
Poznámka: Aj keď sme dokázali určiť adresu alebo rozsah, naša funkcia ADDRESS určila, či majú byť odkazy uvedené ako relatívne alebo absolútne. Vaše dynamické rozsahy budú mať relatívne odkazy, ktoré táto technika nezachytí.
2nd Poznámka: Táto technika funguje iba v spojitom rozsahu názvov. Ak ste mali pomenovaný rozsah, ktorý bol definovaný ako niečo ako tento vzorec
| 1 | = A1: B2, A5: B6 |
potom by vyššie uvedená technika mala za následok chyby.
Doplňujúce Poznámky
Na vygenerovanie adresy z daného čísla riadka a stĺpca použite funkciu ADDRESS. Dôležité: Musíte zadať stĺpec číslo. Zadanie písmena stĺpca vygeneruje chybu. V prípade potreby môžete pomocou funkcie stĺpca vypočítať číslo stĺpca pre referenciu na bunku.
Abs_num vám umožňuje prepínať absolútne a relatívne odkazy na bunky.
1,2,3,4 a1, $ a $ 2 … relatívne/absolútne atď …
Potom zadajte, či chcete použiť a1 alebo R1C1. režim a1 je štandardný režim, v ktorom sú bunky označené číslom stĺpca a číslom riadka (napr. a4). V režime R1C1 sú bunky odkazované na čísla riadkov a stĺpcov (napr. R4C1). a1 je predvolený režim. Nepoužívajte to, pokiaľ nemáte dobrý dôvod.
V poslednom argumente môžete zadať názov hárka, ak bude odkaz na bunku v inom pracovnom hárku. Zadajte názov listu obklopený parantézou (napr. „Sheet3“).
Vráťte sa do zoznamu všetkých funkcií v programe Excel
ADRESA fungovať v Tabuľkách Google
Funkcia ADDRESS funguje v Tabuľkách Google úplne rovnako ako v programe Excel.
