Jak používat dynamický rozsah v Excelu s COUNTIF a INDIRECT

2024 Autor: Abigail Brown | [email protected]. Naposledy změněno: 2023-12-17 06:42

Co je dobré vědět

• Funkce NEPŘÍMÉ mění rozsah odkazů na buňky ve vzorci bez úpravy vzorce.
• Použijte NEPŘÍMÉ jako argument pro COUNTIF k vytvoření dynamického rozsahu buněk, které splňují zadaná kritéria.
• Kritéria jsou stanovena funkcí NEPŘÍMÝ a počítají se pouze buňky, které splňují kritéria.

Tento článek vysvětluje, jak pomocí funkce NEPŘÍMÉ ve vzorcích aplikace Excel změnit rozsah odkazů na buňky použitých ve vzorci, aniž byste museli upravovat samotný vzorec. To zajistí, že se použijí stejné buňky, i když se tabulka změní. Informace platí pro Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel pro Mac a Excel Online.

Použijte dynamický rozsah pomocí vzorce COUNTIF – NEPŘÍMÝ

Funkci NEPŘÍMÉ lze použít s řadou funkcí, které přijímají odkaz na buňku jako argument, jako jsou funkce SUM a COUNTIF.

Použití INDIRECT jako argumentu pro COUNTIF vytvoří dynamický rozsah odkazů na buňky, které může funkce spočítat, pokud hodnoty buněk splňují kritéria. Dělá to tak, že převádí textová data, někdy označovaná jako textový řetězec, na odkaz na buňku.

Tento příklad je založen na datech zobrazených na obrázku výše. Vzorec COUNTIF – INDIRECT vytvořený v tutoriálu je:

=COUNTIF(NEPŘÍMÉ(E1&":"&E2); ">10")

V tomto vzorci argument pro funkci NEPŘÍMÉ obsahuje:

• Buňka odkazuje na E1 a E2, které obsahují textová data D1 a D6.
• Operátor rozsahu, dvojtečka (:) ohraničená dvojitými uvozovkami (" "), která mění dvojtečku na text řetězec.
• Dva ampersandy (&), které se používají ke zřetězení nebo spojení dvojtečky s odkazy na buňky E1 a E2.

Výsledkem je, že NEPŘÍMÝ převede textový řetězec D1:D6 na odkaz na buňku a předá jej funkci COUNTIF, která se započítá, pokud jsou odkazované buňky větší než 10.

Funkce NEPŘÍMÉ přijímá jakýkoli textový vstup. Mohou to být buňky v listu, které obsahují text nebo odkazy na textové buňky, které se zadávají přímo do funkce.

Dynamicky změnit rozsah vzorce

Nezapomeňte, že cílem je vytvořit vzorec s dynamickým rozsahem. Dynamický rozsah lze změnit bez úpravy samotného vzorce.

Změnou textových dat umístěných v buňkách E1 a E2 z D1 a D6 na D3 a D7 lze snadno změnit rozsah sčítaný funkcí z D1:D6 na D3:D7. To eliminuje potřebu přímo upravovat vzorec v buňce G1.

Funkce COUNTIF v tomto příkladu počítá pouze buňky obsahující čísla, pokud jsou větší než 10. I když čtyři z pěti buněk v rozsahu D1:D6 obsahují data, pouze tři buňky obsahují čísla. Buňky, které jsou prázdné nebo obsahují textová data, jsou funkcí ignorovány.

Počítání textu s COUNTIF

Funkce COUNTIF není omezena na počítání číselných údajů. Počítá také buňky obsahující text tím, že kontroluje, zda se shodují s určitým textem.

Za tímto účelem se do buňky G2 zadá následující vzorec:

=COUNTIF(NEPŘÍMÉ(E1&":"&E2); "dva")

V tomto vzorci funkce NEPŘÍMÉ odkazuje na buňky B1 až B6. Funkce COUNTIF sčítá počet buněk, které mají textovou hodnotu dvě.

V tomto případě je výsledek 1.

COUNTA, COUNTBLANK a INDIRECT

Dvě další funkce Excel počítání jsou COUNTA, která počítá buňky obsahující jakýkoli typ dat, přičemž ignoruje pouze prázdné nebo prázdné buňky, a COUNTBLANK, která počítá pouze prázdné nebo prázdné buňky v rozsahu.

Vzhledem k tomu, že obě tyto funkce mají podobnou syntaxi jako funkce COUNTIF, lze je do výše uvedeného příkladu dosadit INDIRECT a vytvořit tak následující vzorce:

=COUNTA(NEPŘÍMÉ(E1&":"&E2))
=COUNTBLANK(NEPŘÍMÉ(E1&":"&E2)
Pro rozsah D1:D6 vrací COUNTA odpověď 4, protože čtyři z pěti buněk obsahují data. COUNTBLANK vrátí odpověď 1, protože v rozsahu je pouze jedna prázdná buňka.

Proč používat NEPŘÍMOU funkci?
Výhoda použití funkce NEPŘÍMÉ ve všech těchto vzorcích spočívá v tom, že nové buňky lze vložit kamkoli v rozsahu.
Rozsah se dynamicky posouvá uvnitř různých funkcí a výsledky se podle toho aktualizují.





Bez funkce NEPŘÍMÉ by bylo nutné každou funkci upravit tak, aby zahrnovala všech 7 buněk, včetně nové.
Výhody funkce NEPŘÍMÉ jsou v tom, že textové hodnoty lze vkládat jako odkazy na buňky a dynamicky aktualizuje rozsahy při každé změně tabulky.
To značně usnadňuje celkovou údržbu tabulek, zejména u velmi velkých tabulek.