V tomto článku sa naučíme Ako získať prírastky referenčných buniek pomocou funkcie INDIRECT v programe Excel.
Scenár :
Pri práci s množinami údajov v rôznych listoch niekedy potrebujeme hľadať sekvenciu hodnôt z iného hárka pomocou funkcie NEPRIAMY. Napríklad extrahovanie dôležitých informácií v rôznych dátových poliach z rôznych hárkov do hlavného hárka. Na tento účel môžete použiť hodnotu kopírovania a prilepenia z hárku s údajmi do hlavného hárka. Vrátená hodnota sa však neaktualizuje, pretože hodnota sa aktualizuje v údajovom hárku. Nasledujúci vysvetlený vzorec šetrí vašu opakovanú prácu na vyberaní každého prvku jeden po druhom z iného listu.
Ako problém vyriešiť?
Pri tomto probléme zvážime spôsob, akým vyberáme prvky z rovnakého hárka, potom použijeme vzorec na extrahovanie zoznamu hodnôt z rôznych hárkov. Na vytvorenie vzorca na to sa použije funkcia Excel INDIRECT & CELL, ako je to vysvetlené nižšie.
Všeobecný vzorec:
| = NEPRIAMY ("názov_listu" & "!" & CELL ("adresa", bunka_ref) |
sheet_name: názov listu
cell_ref: odkaz na bunku bez názvu hárka.
Príklad:
Toto všetko môže byť mätúce na pochopenie. Vezmime si príklad, aby sme pochopili, ako použiť vzorec na extrahovanie radu hodnôt z rôznych hárkov. Tu máme pracovný list s hodnotami v rôznych listoch. Potrebujeme extrahovať zoznam hodnôt pomocou vzorca
Použite vzorec:
| = NEPRIAMY ($ C $ 3 & "!" & CELL ("adresa", B1)) |
Vysvetlenie:
- Pri extrahovaní hodnoty z iného listu. Excel akceptuje syntax, ktorá je Sheet1! B1. (List Sheet1 je názov listu a B1 je odkaz na bunku).
- Funkcia CELL vráti odkaz na bunku a privedie odkaz na funkciu INDIRECT.
- & operátor spojí alebo spojí dva alebo viac argumentov.
- Funkcia INDIRECT vracia hodnotu v poskytnutom odkaze na bunku.
Tu B1 je odkaz na bunku toho istého listu, ale hodnota je extrahovaná z dátového listu.
Hodnota v bunke Údajový list B1 je 5800. Skopírujte vzorec a získajte zoznam hodnôt začínajúcich od hodnoty extrahovanej z prvého výsledku.
Tu nájdete zoznam hodnôt začínajúcich od bunky B1 až B7 v hárku s údajmi.
Excel zvýši odkaz na bunku z iného hárka pomocou odkazu na bunku.
Vieme, ako z jedného listu získať prírastok referenčnej hodnoty pre bunku. Na extrahovanie hodnoty z rovnakého hárku použijeme vzorec. Obecný vzorec je = bunka_ref.
Tento vzorec robí, že pasta vyberie hodnotu z adresy, ktorá je referenciou bunky. Keď sa vo vzorci používa odkaz na bunku. Dá sa použiť na rôzne úlohy a hodnota sa aktualizuje tak, ako sa hodnota aktualizuje z bunky. Rozšírte vzorec na bunky nižšie, aby ste získali zoznam hodnôt.
Ako tu vidíte, máme požadovanú skupinu. Teraz musíme tieto hodnoty dostať do hlavného hárka. Program Excel akceptuje syntax pri extrahovaní hodnôt z iného listu, ktorý je = Referencia! A1. (Odkaz je názov listu a A1 je odkaz na bunku).
Skopírujte vzorec do zostávajúcich buniek pomocou Ctrl + D alebo ťahajte nadol z pravého dolného rohu bunky.
Ako vidíte, tu máme všetky požadované hodnoty z referenčného listu. Existuje ešte jedna metóda na získanie hodnôt z rôznych hárkov.
Extrahujte zoznam hodnôt z rôznych hárkov pomocou pomenovaného rozsahu.
Môžeme použiť metódu na volanie celého poľa priamo pomocou pomenovaného rozsahu z rôznych hárkov. Za týmto účelom vyberieme pole alebo tabuľku a pomenujeme vybratú časť v poli s názvom a nazveme ju menom, kedykoľvek je to v pracovnom hárku požadované.
Tu ste vybrali celú tabuľku a pomenovali ju Údaje. Pole s názvom sa nachádza v ľavom hornom rohu listu, ako je zvýraznené vo vyššie uvedenom hárku.
V novom hárku extrahujeme pomenovanú tabuľku. Prejdite na nový hárok, vyberte rovnaký počet buniek a zadajte vzorec uvedený nižšie.
Použite vzorec:
| { = Údaje } |
Poznámka: používajte Ctrl + Shift + Enter namiesto Enter zadajte vzorec pracujúci pre polia. Nepoužívajte kučeravé zátvorky ručne.
Ako vidíme, vzorec funguje dobre. Ktorúkoľvek z týchto metód je možné použiť kdekoľvek je to potrebné.
Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.
Poznámky:
- Tieto vzorce extrahujú iba hodnoty, nie formát. Rovnaké formátovanie môžete použiť aj pomocou nástroja na kreslenie formátu na listy.
- Hodnota sa aktualizuje, keď zmeníme hodnotu v referencii bunky.
- Hodnoty vo vzorci môžeme upravovať pomocou rôznych operátorov, ako je matematický operátor pre čísla, operátor „&“ pre textové hodnoty.
Dúfam, že tento článok o tom, ako získať zvýšenie referencie buniek pomocou funkcie NEPRIAMY, je vysvetľujúci. Tu nájdete ďalšie články o extrahovaní hodnôt pomocou vzorcov. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
Všetko o rozsahoch pomenovaných v Exceli: rozsahy programu Excel, ktoré sú označené názvami, sa vo vzorcoch programu Excel ľahko používajú. Tu sa o tom všetko dozviete.
Pole s názvom v programe Excel : Pole s názvom programu Excel nie je nič iné ako malá oblasť zobrazenia v ľavej hornej časti listu programu Excel, ktorá zobrazuje názov aktívnej bunky alebo rozsahov v programe Excel. Bunku alebo pole môžete premenovať na odkazy.
Ako získať názov hárka listu v programe Excel : Funkcia CELL v programe Excel vám poskytne informácie o akomkoľvek pracovnom hárku, ako je stĺpček, obsah, názov súboru … atď. Tu sa dozviete, ako získať názov hárka pomocou funkcie CELL.
Ako získať sekvenčné číslo riadka v programe Excel: Niekedy potrebujeme v tabuľke získať poradové číslo riadka, môže to byť poradové číslo alebo čokoľvek iné. V tomto článku sa naučíme číslovať riadky v programe Excel od začiatku údajov.
Zvýšte číslo v textovom reťazci v programe Excel: Ak máte veľký zoznam položiek a potrebujete v Exceli zvýšiť posledné číslo textu starého textu, budete potrebovať pomoc z dvoch funkcií TEXT a PRAVO.
Populárne články:
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.