Počítanie čísel, dátumov alebo textu v programe Excel
Pomocou funkcie INDIRECT v formulároch programu Excel sa jednoducho mení rozsah odkazov na bunky použitý vo vzorci bez toho, aby ste museli upravovať samotný vzorec.
Funkcia INDIRECT môže byť použitá s množstvom funkcií, ktoré akceptujú odkaz na bunku ako argument, ako sú napríklad funkcie SUM a COUNT.
V druhom prípade sa pomocou argumentu INDIRECT ako argument pre COUNT vytvorí dynamický rozsah odkazov na bunky, ktorý môže byť doplnený funkciou.
Neprimerané to robí tým, že otočí textové údaje - niekedy označované ako textový reťazec - do odkazu na bunky.
Príklad: Použitie dynamického rozsahu pomocou vzorca COUNT-INDIRECT
Tento príklad je založený na údajoch uvedených na obrázku vyššie.
Vzorec COUNT-INDIRECT vytvorený v tutoriále je:
= COUNT (NEPRIAME (E1 & ":" & E2))
V tomto vzorci argument pre funkciu INDIRECT obsahuje:
- odkazy na bunky E1 a E2 - ktoré obsahujú textové dáta D1 a D5
- operátor rozsahu - dvojbodka (:) obklopená dvojitými úvodzovkami ( "" ), ktorá mení dvojbodku na textový reťazec
- Dva ampersandy ( & ), ktoré sa používajú na zlúčenie alebo spojenie hrubého čreva s odkazmi na bunky E1 a E2
Výsledkom je, že INDIRECT konvertuje textový reťazec D1: D5 do odkazu na bunku a odovzdáva ho spolu s funkciou COUNT, ktorá má byť celá.
Dynamicky meniť rozsah vzorca
Pamätajte, že cieľom je vytvoriť vzorec s dynamickým rozsahom - ten, ktorý možno zmeniť bez úpravy samotného vzorca.
Zmenou textových údajov umiestnených v bunkách E1 a E2, napríklad z D1 a D5 na D3 a D6, sa rozsah, ktorý sa dá dosiahnuť funkciou, dá ľahko zmeniť z D1: D5 na D3: D6.
Tým sa eliminuje potreba upraviť vzorec v bunke G1.
Zadanie údajov výučby
- Do buniek D1 až E2 zadajte nasledujúce údaje
- Bunkové dáta D1 - 1 D2 - dva D3 - 3 D5 - 5 D6 - šesť E1 - D1 E2 - D5 F1 - Počet:
Zadanie vzorca COUNT-INDIRECT
- Kliknite na bunku G1 - tu sa zobrazia výsledky tohto príkladu
- Zadajte vzorec: = COUNT (INDIRECT (E1 & ":" & E2))
- Na dokončenie funkcie stlačte klávesu Enter na klávesnici
- Bunka G1 by mala obsahovať odpoveď 3
Upozorňujeme, že funkcia COUNT počíta iba bunky obsahujúce čísla, takže hoci štyri z piatich buniek v rozsahu D1: D5 obsahujú údaje, iba tri bunky obsahujú čísla.
Bunky, ktoré sú prázdne alebo obsahujú textové údaje, sa funkciou ignorujú.
Modifikácia rozsahu vzorcov
- Kliknite na bunku E1
- Zadajte odkaz na bunku D3
- Stlačením klávesu Enter na klávesnici prejdite na bunku E2
- V tejto bunke zadajte referenciu buniek D6
- Stlačte kláves Enter na klávesnici
- Odpoveď v bunke G1 by sa mala zmeniť na 2, pretože iba dve bunky v novom rozsahu D3: D6 obsahujú čísla
COUNTA, COUNTBLANK a NEPRIAME
Ďalšie dve funkcie programu Excel sú COUNTA, ktoré počítajú bunky obsahujúce ľubovoľný typ údajov - ignorujú iba prázdne alebo prázdne bunky a COUNTBLANK , ktoré počítajú len prázdne alebo prázdne bunky v rozsahu.
Keďže obe tieto funkcie majú podobnú syntax ako funkcia COUNT, môžu byť nahradené do vyššie uvedeného príkladu pomocou INDIRECT, aby vytvorili nasledujúce vzorce:
= COUNTA (NEPRIAME (E1 & ":" & E2))
= COUNTBLANK (NEPRIAME (E1 & ":" & E2))
Pre rozsah D1: D5 by COUNTA vrátila odpoveď 4 - pretože štyri z piatich buniek obsahujú dáta a OUNTBLANK a odpoveď 1 - pretože existuje len jedna prázdna bunka v rozsahu.