Stáhněte si ukázkový sešit
Tento tutoriál ukazuje, jak používat Funkce Excel ADDRESS v aplikaci Excel vrátit adresu buňky jako text.
Přehled funkcí ADDRESS
Funkce ADDRESS vrací adresu buňky jako text.
Chcete -li použít funkci listu ADDRESS Excel, vyberte buňku a zadejte:
(Všimněte si, jak se zobrazují vstupy vzorců)
Funkce ADDRESS Syntaxe a vstupy:
| 1 | = ADDRESS (řádek_číslo, sloupec_číslo, abs_číslo, C1, list_text) |
řádek_číslo - Číslo řádku pro referenci. Příklad: 5 pro řádek 5.
col_num - Číslo sloupce pro referenci. Příklad: 5 pro sloupec E. Do sloupce E nemůžete zadat „E“
abs_num - [nepovinné] Číslo představující, zda má odkaz obsahovat absolutní nebo relativní odkazy na řádky/sloupce. 1 pro absolutní. 2 pro absolutní řádek/relativní sloupec. 3 pro relativní řádek/absolutní sloupec. 4 pro relativní.
a1 - [nepovinné]. Číslo udávající, zda použít standardní (A1) formát odkazu na buňku nebo formát R1C1. 1/TRUE pro Standard (výchozí). 0/NEPRAVDA pro R1C1.
list_text - [nepovinné] Název listu, který se má použít. Výchozí nastavení je aktuální list.
Co je funkce ADDRESS?
Funkce ADDRESS je trochu unikátní funkcí. Většinu času v tabulce řekneme počítači odkaz na buňku a ona nám dá hodnotu z této buňky. S ADRESOU místo toho vytvoříme název buňky. Tato adresa může být relativní nebo absolutní ve stylu A1 nebo R1C1 a může, ale nemusí obsahovat název listu. Z velké části tato funkce nemá mnoho praktických využití, ale může být zábavné se o ní dozvědět. Když se používá, je obvykle v kombinaci s dalšími funkcemi, jak uvidíme níže.
Základní příklad
Řekněme, že chceme vytvořit odkaz na buňku ve 4th sloupec a 1Svatý řádek, alias buňka D1. Můžeme použít rozložení na obrázku zde:
Náš vzorec v A3 je jednoduše
| 1 | = ADRESA (B1, B2) |
Všimněte si, že neuvedením konkrétního argumentu pro relativní/absolutní jsme získali vše absolutní. Získali jsme také výchozí styl odkazování typu A1 bez názvu listu.
V kombinaci s NEPŘÍMÝM
Jak jsme řekli, funkce ADDRESS nám ve skutečnosti nedala nic užitečného. Mohli bychom to ale zkombinovat s NEPŘÍMOU funkcí, abychom získali nějaké funkce. Zvažte toto rozložení, kde máme seznam položek ve sloupci D. Pokud bychom zachovali stejný vzorec jako dříve, vygenerujeme odkaz na D1 takto
| 12 | = ADRESA (B1, B2)= $ D $ 1 |
Když vložíme funkci adresy do NEPŘÍMÉ funkce, budeme moci použít vygenerovaný odkaz na buňku a použít jej praktickým způsobem. INDIRECT převezme odkaz „$ D $ 1“ a použije ho k načtení hodnoty z této buňky.
| 123 | = NEPŘÍMÝ (ADRESA (B1, B2)= NEPŘÍMÝ ($ D $ 1)= "Apple" |
Poznámka: Výše uvedené je dobrým příkladem toho, jak udělat funkci ADDRESS užitečnou, ale není vhodné ji normálně používat. Vyžadovalo to dvě funkce a kvůli NEPŘÍMÉMU to bude mít volatilní povahu. Lepší alternativou by bylo použít INDEX takto: = INDEX (1: 1048576, B1, B2)
Adresa konkrétní hodnoty
Někdy, když máte velký seznam položek, musíte vědět, kde se v seznamu položka nachází. Zvažte tuto tabulku skóre od studentů. Pokračovali jsme a vypočítali minimální, medián a maximální hodnoty těchto skóre v buňkách E2: G2.
Můžeme filtrovat naši tabulku pro každou z těchto položek, abychom zjistili, kde se nachází (opět si představte, že je to mnohem větší seznam), nebo bychom dokonce mohli použít podmíněné formátování, aby se uživateli vizuálně zobrazilo. Pokud je však seznam tisíce řádků, nechceme se posouvat tak daleko, abychom viděli, co chceme. Místo toho použijeme hodnoty v E2: G2 k určení adresy buněk obsahujících naše hodnoty. K tomu použijeme funkci MATCH s ADDRESS. Pamatujte, že MATCH vrátí relativní polohu hodnoty v rozsahu.
Náš vzorec v E3 pak je:
| 1 | = ADRESA (MATCH (E2, $ B: $ B, 0), 2) |
Stejný vzorec můžeme zkopírovat do G3 a změní se pouze reference E2, protože je to jediná relativní reference. Když se podíváme zpět na E3, funkce MATCH dokázala najít hodnotu 98 v 5th řádek sloupce B. Naše funkce ADDRESS poté použila k vytvoření úplné adresy „$ B $ 5“.
Přeložte písmena sloupců z čísel
Až do tohoto bodu všechny naše příklady nechaly funkci ADDRESS vrátit absolutní odkaz. Tento další příklad vrátí relativní odkaz. V této tabulce chceme zadat číslo do sloupce A a vrátit odpovídající název písmene sloupce.
Abychom dosáhli svého cíle, necháme funkci ADDRESS vrátit odkaz na řádek 1 v relativním formátu a poté odstraníme „1“ z textového řetězce, takže nám zbude pouze písmeno. Vezměme si v naší tabulce řádek 3, kde náš vstup je 13. Náš vzorec v B3 je
| 1 | = NÁHRADA (ADRESA (1, A3, 4), "1", "") |
Všimněte si, že jsme dali 3rd argument ve funkci ADDRESS, která řídí relativní vs. absolutní odkazování. Funkce ADDRESS vydá „M1“ a poté funkce SUBSTITUTE odstraní „1“, takže nám zůstane pouze „M“.
Zjistěte adresu pojmenovaných rozsahů
V aplikaci Excel můžete pojmenovat rozsah nebo oblasti buněk, což vám umožní jednoduše odkazovat na pojmenovaný rozsah namísto odkazu na buňku.
Většina pojmenovaných rozsahů je statická, což znamená, že vždy odkazují na stejný rozsah. Můžete však také vytvořit dynamické pojmenované rozsahy, které se mění podle velikosti podle některých vzorců.
U dynamického pojmenovaného rozsahu možná budete muset znát přesnou adresu, na kterou váš pojmenovaný rozsah ukazuje. To můžeme provést pomocí funkce ADDRESS.
V tomto příkladu se podíváme na to, jak definovat adresu pro náš pojmenovaný rozsah s názvem „Stupně“.
Vraťme si náš stůl z dřívější doby:
Abyste získali adresu rozsahu, potřebujete znát levou horní buňku a dolní levou buňku. První část je dostatečně snadné splnit pomocí funkce ŘÁDEK a SLOUPEK. Náš vzorec v E1 může být
| 1 | = ADRESA (ŘADA (stupně), COLUMN (stupně)) |
Funkce ROW vrátí řádek první buňky v našem rozsahu (který bude 1) a COLUMN provede totéž podobně pro sloupec (také 1).
Abychom získali buňku vpravo dole, použijeme funkci ŘÁDKY a SLOUPKY. Protože můžeme zjistit počáteční bod našeho rozsahu, vypočítáme -li, jak vysoký rozsah je, a odečteme náš počáteční bod, získáme správný koncový bod. Vzorec pro toto vypadá
| 1 | = ADRESA (ŘADY (stupně) -ROW (stupně) +1, COLUMNS (stupně) -COLUMN (stupně) +1) |
Nakonec, abychom to dali dohromady do jednoho řetězce, můžeme jednoduše spojit hodnoty společně s dvojtečkou uprostřed. Vzorec v E3 může být
| 1 | = E1 & ":" & E2 |
Poznámka: Přestože jsme byli schopni určit adresu nebo rozsah, naše funkce ADDRESS určila, zda mají být odkazy uvedeny jako relativní nebo absolutní. Vaše dynamické rozsahy budou mít relativní reference, které tato technika nezachytí.
2nd Poznámka: Tato technika funguje pouze v souvislém rozsahu názvů. Pokud jste měli pojmenovaný rozsah, který byl definován jako něco jako tento vzorec
| 1 | = A1: B2, A5: B6 |
pak by výše uvedená technika měla za následek chyby.
Další poznámky
Pomocí funkce ADDRESS vygenerujte adresu z daného čísla řádku a sloupce. Důležité: Musíte zadat sloupec číslo. Zadání písmene sloupce vygeneruje chybu. V případě potřeby můžete pomocí funkce sloupce vypočítat číslo sloupce pro odkaz na buňku.
Abs_num vám umožňuje přepínat absolutní a relativní odkazy na buňky.
1,2,3,4 a1, $ a $ 2 … relativní/absolutní atd …
Poté určete, zda použít a1 nebo R1C1. Režim a1 je standardní režim, kde jsou buňky odkazovány číslem jejich sloupce a řádku (např. a4). V režimu R1C1 jsou buňky odkazovány čísly jejich řádků a sloupců (např. R4C1). a1 je výchozí režim. Tuto možnost použijte, pokud k tomu nemáte pádný důvod.
V posledním argumentu můžete zadat název listu, pokud bude odkaz na buňku na jiném listu. Zadejte název listu obklopený parantézou (např. „Sheet3“).
Zpět na seznam všech funkcí v Excelu
ADRESA funkce v Tabulkách Google
Funkce ADDRESS funguje v Tabulkách Google úplně stejně jako v Excelu.
