Použite funkciu INDIRECT v formulároch programu Excel na zmenu rozsahu referencií buniek použitých vo vzorci bez toho, aby ste museli upravovať samotný vzorec. Tým sa zabezpečí, že sa použijú rovnaké bunky, aj keď sa vaša tabuľka zmení.
Poznámka: Pokyny v tomto článku sa vzťahujú na Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel pre Mac a Excel Online.
Použite dynamický rozsah pomocou vzorca COUNTIF-INDIRECT
Funkcia INDIRECT môže byť použitá s množstvom funkcií, ktoré akceptujú odkaz na bunku ako argument, napríklad funkcie SUM a COUNTIF.
Použitie funkcie INDIRECT ako argument pre COUNTIF vytvára dynamický rozsah odkazov na bunky, ktorý môže byť spočítaný funkciou, ak hodnoty bunky spĺňajú kritériá. Robí to tým, že textové údaje, niekedy označované ako textový reťazec, sa premenia na odkaz bunky.
Tento príklad je založený na údajoch uvedených na obrázku vyššie. Vzor COUNTIP - NEPRIAMY vytvorený v príručke je:
= COUNTIF (nepriamy (E1 a ":" a E2), "> 10"),
V tomto vzorci argument pre funkciu INDIRECT obsahuje:
- Odkazy na bunky E1 a E2, ktoré obsahujú textové dáta D1 a D6.
- Operátor rozsahu, dvojbodka (:) obklopené dvojitými úvodzovkami (' '), ktorý premení dvojbodku na textový reťazec.
- Dva ampersandy (&), ktoré sa používajú na spojenie alebo spojenie dvojbodky s odkazmi na bunky E1 a E2.
Výsledkom je, že INDIRECT konvertuje textový reťazec D1: D6 do referencie bunky a odovzdáva ju k funkcii COUNTIF, ktorá sa má počítať, ak referenčné bunky sú väčšie ako 10.
Funkcia INDIRECT prijíma akékoľvek textové vstupy. Môžu to byť bunky v pracovnom hárku, ktoré obsahujú odkazy na textové alebo textové bunky, ktoré sa zadávajú priamo do funkcie.
Dynamicky zmeňte dosah formulí
Pamätajte, že cieľom je vytvoriť vzorec s dynamickým rozsahom. Dynamický rozsah môže byť zmenený bez úpravy samotného vzorca.
Zmenou textových údajov umiestnených v bunkách E1 a E2, od D1 a D6 do D3 a D7, rozsah, ktorý sa dá dosiahnuť funkciou, sa dá ľahko zmeniť z D1: D6 na D3: D7. Tým sa eliminuje potreba priamo upraviť vzorec v bunke G1.
Funkcia COUNTIF v tomto príklade počíta iba bunky obsahujúce čísla, ak sú väčšie ako 10. Aj keď štyri z piatich buniek v rozsahu D1: D6 obsahujú dáta, iba tri bunky obsahujú čísla. Bunky, ktoré sú prázdne alebo obsahujú textové údaje, sa funkciou ignorujú.
Počítanie textu s COUNTIF
Funkcia COUNTIF nie je obmedzená na počítanie číselných údajov. Taktiež počíta bunky, ktoré obsahujú text, a to tak, že kontrolujú, či zodpovedajú určitému textu.
Za týmto účelom sa do bunky G2 zadá nasledujúci vzorec:
= COUNTIF (nepriamy (E1 a ":" a E2), "dva")
V tomto vzore INDIRECT odkazuje na bunky B1 až B6. Funkcia COUNTIF je celkový počet buniek, ktoré majú textovú hodnotu dva v nich.
V tomto prípade je výsledok 1.
COUNTA, COUNTBLANK a NEPRIAME
Ďalšie dve funkcie programu Excel sú COUNTA, ktoré počítajú bunky obsahujúce ľubovoľný typ údajov a zároveň ignorujú iba prázdne alebo prázdne bunky a COUNTBLANK, ktorý počíta len prázdne alebo prázdne bunky v rozsahu.
Keďže obe tieto funkcie majú podobnú syntax s funkciou COUNTIF, môžu byť nahradené do vyššie uvedeného príkladu pomocou INDIRECT, aby vytvorili nasledujúce vzorce:
= COUNTA (nepriamy (E1 a ":" a E2))= COUNTBLANK (nepriamy (E1 a ":" a E2)
Pre rozsah D1: D6 COUNTA vráti odpoveď na hodnotu 4, pretože štyri z piatich buniek obsahujú údaje. COUNTBLANK vráti odpoveď na hodnotu 1, pretože v tejto oblasti je iba jedna prázdna bunka. Výhodou použitia funkcie INDIRECT vo všetkých týchto vzorcoch je, že nové bunky môžu byť vložené kdekoľvek v tomto rozsahu. Rozsah sa dynamicky posúva vo vnútri rôznych funkcií a aktualizuje výsledky podľa toho. Bez funkcie INDIRECT by sa každá funkcia mala upravovať tak, aby zahŕňala všetkých 7 buniek vrátane nových. Výhody funkcie INDIRECT sú, že textové hodnoty môžu byť vložené ako odkazy na bunky a dynamicky aktualizovať rozsahy vždy, keď sa tabuľka mení. Tým je celková údržba tabuľky oveľa jednoduchšia, najmä pri veľmi veľkých tabuľkách. Prečo používať funkciu INDIRECT?
