V tomto článku se naučíme Jak získat přírůstky odkazů na buňky pomocí funkce NEPŘÍMÉ v aplikaci Excel.
Scénář:
Při práci s datovými sadami v různých listech někdy potřebujeme hledat posloupnost hodnot z jiného listu pomocí funkce NEPŘÍMÉ. Například extrahování důležitých informací v různých datových polích z různých listů do hlavního listu. K tomu můžete použít zkopírování a vložení hodnoty z datového listu na hlavní list. Vrácená hodnota se ale neaktualizuje, protože se hodnota aktualizuje v datovém listu. Níže vysvětlený vzorec šetří vaši opakovanou práci při výběru každého prvku jeden po druhém z jiného listu.
Jak problém vyřešit?
Pro tento problém budeme zvažovat způsob, jakým používáme výběr prvků ze stejného listu, poté použijeme vzorec k extrahování seznamu hodnot z různých listů. Funkce Excel INDIRECT & CELL bude použita k vytvoření vzorce, jak je popsáno níže.
Obecný vzorec:
| = NEPŘÍMÝ ("název_listu" & "!" & CELL ("adresa", buňka_ref) |
sheet_name: název listu
cell_ref: odkaz na buňku bez názvu listu.
Příklad:
Pochopit to všechno může být matoucí. Vezměme si příklad, abychom pochopili, jak pomocí vzorce extrahovat řadu hodnot z různých listů. Zde máme list s hodnotami v různých listech. Potřebujeme extrahovat seznam hodnot pomocí vzorce
Použijte vzorec:
| = NEPŘÍMÉ ($ C $ 3 & "!" & CELL ("adresa", B1)) |
Vysvětlení:
- Při extrahování hodnoty z jiného listu. Excel akceptuje syntaxi, která je Sheet1! B1. (Zde List1 je název listu a B1 je odkaz na buňku).
- Funkce CELL vrací odkaz na buňku a přivádí odkaz na funkci INDIRECT.
- & operátor spojí nebo zřetězí dva nebo více argumentů.
- Funkce INDIRECT vrací hodnotu v uvedeném odkazu na buňku.
Zde B1 je odkaz na buňku stejného listu, ale hodnota je extrahována z datového listu.
Hodnota v buňce Datový list B1 je 5800. Zkopírováním vzorce získáte seznam hodnot počínaje hodnotou extrahovanou z prvního výsledku.
Zde máme seznam hodnot počínaje buňkou B1 až B7 datového listu.
Excel zvýší odkaz na buňku z jiného listu pomocí odkazu na buňku.
Víme, jak získat přírůstek referenčních buněk ze stejného listu. Pojďme použít vzorec k extrahování hodnoty ze stejného listu. Obecný vzorec je = buňka_ref.
Co tento vzorec dělá, je, že pasta vybere hodnotu z adresy, která je odkazem na buňku. Když je ve vzorci použit odkaz na buňku. Může být použit pro různé úkoly a hodnota se aktualizuje, jak se hodnota aktualizuje z buňky. Rozbalením vzorce na níže uvedené buňky získáte seznam hodnot.
Jak zde vidíte, máme požadovanou arrey. Nyní musíme tyto hodnoty dostat na hlavní list. Excel přijímá syntaxi při extrahování hodnot z jiného listu, což je = Reference! A1. (Zde Reference je název listu a A1 je odkaz na buňku).
Zkopírujte vzorec do zbývajících buněk pomocí Ctrl + D nebo přetáhněte dolů z pravé dolní části buňky.
Jak vidíte, zde máme všechny požadované hodnoty z referenčního listu. Existuje ještě jedna metoda, jak získat hodnoty z různých listů.
Extrahujte seznam hodnot z různých listů pomocí pojmenovaného rozsahu.
Můžeme použít metodu pro volání celého pole přímo pomocí pojmenovaného rozsahu z různých listů. Za tímto účelem vybereme pole nebo tabulku a pojmenujeme vybranou část v poli název a zavoláme ji jménem, kdykoli je to v listu vyžadováno.
Zde jsme vybrali celou tabulku a pojmenovali ji Data. Pole se jménem se nachází v levém horním rohu listu, jak je zvýrazněno ve výše uvedeném listu.
Extrahujeme pojmenovanou tabulku do nového listu. Přejděte na nový list, vyberte stejný počet buněk a zadejte vzorec uvedený níže.
Použijte vzorec:
| { = Data } |
Poznámka: použijte Ctrl + Shift + Enter místo Enter do vzorce pracujícího pro pole. Nepoužívejte kudrnaté závorky ručně.
Jak vidíme, vzorec funguje dobře. Kteroukoli z těchto metod lze použít, kdykoli je potřeba.
Zde jsou všechny pozorovací poznámky týkající se používání vzorce.
Poznámky:
- Tyto vzorce pouze extrahují hodnoty, nikoli formát. Stejné formátování můžete použít pomocí nástroje pro malíř formátování napříč listy.
- Hodnota se aktualizuje, když měníme hodnotu v odkazu na buňku.
- Hodnoty uvnitř vzorce můžeme upravovat pomocí různých operátorů, jako je matematický operátor pro čísla, operátor „&“ pro textové hodnoty.
Doufám, že tento článek o tom, jak získat přírůstek referenčních buněk pomocí funkce INDIRECT, je vysvětlující. Zde najdete další články o extrahování hodnot pomocí vzorců. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail
Vše o rozsahech pojmenovaných v Excelu: rozsahy aplikace Excel, které jsou označeny názvy, lze ve vzorcích aplikace Excel snadno použít. Vše se dozvíte zde.
Pole Název v aplikaci Excel : Pole názvu aplikace Excel není nic jiného než malá oblast zobrazení v levé horní části listu aplikace Excel, která zobrazuje název aktivní buňky nebo rozsahů v aplikaci Excel. Buňku nebo pole můžete přejmenovat na odkazy.
Jak získat název listu listu v aplikaci Excel : Funkce CELL v aplikaci Excel vám poskytne informace týkající se jakéhokoli listu, jako je sloupec, obsah, název souboru atd. Atd. Zde se dozvíte, jak získat název listu pomocí funkce CELL.
Jak získat sekvenční číslo řádku v aplikaci Excel: Někdy potřebujeme získat pořadové číslo řádku v tabulce, může to být pro sériové číslo nebo cokoli jiného. V tomto článku se naučíme, jak číslovat řádky v Excelu od začátku dat.
Zvýšení čísla v textovém řetězci v aplikaci Excel: Pokud máte velký seznam položek a potřebujete v Excelu zvýšit poslední číslo textu starého textu, budete potřebovat pomoc ze dvou funkcí TEXT a PRAVO.
Oblíbené články:
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.