Ako používať dynamický rozsah v Exceli s COUNTIF a INDIRECT

2024 Autor: Abigail Brown | [email protected]. Naposledy zmenené: 2023-12-17 07:00

Čo by ste mali vedieť

• 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.

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ú.





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.