Funkcia NEPRIAME mení rozsah odkazov na bunky vo vzorci bez úpravy vzorca.
Použite NEPRIAME ako argument pre COUNTIF na vytvorenie dynamického rozsahu buniek, ktoré spĺňajú zadané kritériá.
Kritériá určuje funkcia NEPRIAME a započítavajú sa iba bunky, ktoré spĺňajú kritériá.
Tento článok vysvetľuje, ako použiť funkciu NEPRIAME vo vzorcoch programu Excel na zmenu rozsahu odkazov na bunky použitých vo vzorci bez toho, aby ste museli upravovať samotný vzorec. To zaisťuje, že sa použijú rovnaké bunky, aj keď sa vaša tabuľka zmení. Informácie platia pre Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel pre Mac a Excel Online.
Použite dynamický rozsah so vzorcom COUNTIF – INDIRECT
Funkciu INDIRECT možno použiť s množstvom funkcií, ktoré akceptujú odkaz na bunku ako argument, ako sú funkcie SUM a COUNTIF.
Použitie NEPRIAME ako argumentu pre COUNTIF vytvorí dynamický rozsah odkazov na bunky, ktoré môže funkcia spočítať, ak hodnoty buniek spĺňajú kritériá. Robí to tak, že textové údaje, niekedy označované ako textový reťazec, premení na odkaz na bunku.
Image
Tento príklad je založený na údajoch zobrazených na obrázku vyššie. Vzorec COUNTIF – INDIRECT vytvorený v návode je:
=COUNTIF(NEPRIAME(E1&":"&E2), ">10")
V tomto vzorci argument pre funkciu NEPRIAME obsahuje:
Bunka odkazuje na E1 a E2, ktoré obsahujú textové údaje D1 a D6.
Operátor rozsahu, dvojbodka (:) obklopená dvojitými úvodzovkami (" "), ktorá mení dvojbodku na text string.
Dva ampersandy (&), ktoré sa používajú na zreťazenie alebo spojenie dvojbodky s odkazmi na bunky E1 a E2.
Výsledkom je, že INDIRECT skonvertuje textový reťazec D1:D6 na odkaz na bunku a odovzdá ho funkcii COUNTIF, ktorá sa spočíta, ak sú bunky, na ktoré sa odkazuje, väčšie ako 10.
Funkcia NEPRIAME akceptuje akýkoľvek textový vstup. Môžu to byť bunky v pracovnom hárku, ktoré obsahujú text alebo odkazy na textové bunky, ktoré sa zadávajú priamo do funkcie.
Dynamicky meniť rozsah vzorca
Pamätajte, že cieľom je vytvoriť vzorec s dynamickým rozsahom. Dynamický rozsah je možné zmeniť bez úpravy samotného vzorca.
Zmenou textových údajov umiestnených v bunkách E1 a E2 z D1 a D6 na D3 a D7 možno jednoducho zmeniť rozsah sčítaný funkciou z D1:D6 na D3:D7. To eliminuje potrebu priamo upravovať vzorec v bunke G1.
Funkcia COUNTIF v tomto príklade počíta bunky obsahujúce čísla iba vtedy, ak sú väčšie ako 10. Aj keď štyri z piatich buniek v rozsahu D1:D6 obsahujú údaje, iba tri bunky obsahujú čísla. Bunky, ktoré sú prázdne alebo obsahujú textové údaje, funkcia ignoruje.
Počítanie textu s COUNTIF
Funkcia COUNTIF nie je obmedzená na počítanie číselných údajov. Počíta aj bunky, ktoré obsahujú text, a to tak, že skontroluje, či sa zhodujú s určitým textom.
Za týmto účelom zadajte do bunky G2 nasledujúci vzorec:
=COUNTIF(NEPRIAME(E1&":"&E2); "dva")
V tomto vzorci funkcia NEPRIAME odkazuje na bunky B1 až B6. Funkcia COUNTIF spočítava počet buniek, ktoré majú textovú hodnotu dve.
V tomto prípade je výsledok 1.
COUNTA, COUNTBLANK a INDIRECT
Dve ďalšie funkcie Excelu na počítanie sú COUNTA, ktorá počíta bunky obsahujúce akýkoľvek typ údajov, pričom ignoruje iba prázdne alebo prázdne bunky, a COUNTBLANK, ktorá počíta iba prázdne alebo prázdne bunky v rozsahu.
Keďže obe tieto funkcie majú podobnú syntax ako funkcia COUNTIF, možno ich do vyššie uvedeného príkladu nahradiť reťazcom INDIRECT a vytvoriť tak nasledujúce vzorce:
=COUNTA(NEPRIAME(E1&":"&E2))
=COUNTBLANK(NEPRIAME(E1&":"&E2)
Pre rozsah D1:D6 vráti COUNTA odpoveď 4, pretože štyri z piatich buniek obsahujú údaje. COUNTBLANK vráti odpoveď 1, pretože v rozsahu je iba jedna prázdna bunka.
Prečo používať NEPRIAME funkciu?
Výhoda použitia funkcie NEPRIAME vo všetkých týchto vzorcoch spočíva v tom, že nové bunky možno vložiť kdekoľvek v rozsahu.
Rozsah sa dynamicky posúva v rámci rôznych funkcií a výsledky sa podľa toho aktualizujú.
Image
Bez funkcie INDIRECT by bolo potrebné každú funkciu upraviť tak, aby zahŕňala všetkých 7 buniek vrátane novej.
Výhody funkcie NEPRIAME sú v tom, že textové hodnoty možno vkladať ako odkazy na bunky a dynamicky aktualizuje rozsahy pri každej zmene tabuľky.
To značne zjednodušuje celkovú údržbu tabuliek, najmä pri veľmi veľkých tabuľkách.
Funkcia COUNTIF v Exceli počíta, koľkokrát sa konkrétne údaje našli vo vybranej skupine buniek v pracovnom hárku. Aktualizované tak, aby zahŕňalo Excel 2019
Funkcia COUNTIF v Exceli počíta počet buniek, ktoré zodpovedajú určitým kritériám, čo môže byť v mnohých prípadoch užitočné. Tu je návod, ako používať COUNTIF s jednoduchými kritériami v Exceli
Prečítajte si, ako skryť a odkryť posúvače a ako obnoviť rozsah posúvača vertikálneho posúvača v programe Microsoft Excel. Aktualizované tak, aby zahŕňalo Excel 2019
Naučte sa vytvárať, upravovať a definovať pomenovaný rozsah v programe Microsoft Excel, ako aj rozsah a obmedzenia pomenovania. Aktualizované tak, aby zahŕňalo Excel 2019
