Použijte funkci INDIRECT v formulářích aplikace Excel pro změnu rozsahu odkazů na buňky používaných ve vzorci, aniž byste museli upravovat vlastní vzorec. Tím zajistíte, že budou použity stejné buňky i po změně tabulky.
Poznámka: Pokyny v tomto článku platí pro Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel pro Mac a Excel Online.
Použijte dynamický rozsah pomocí vzorce COUNTIF - INDIRECT
Funkci INDIRECT lze použít s řadou funkcí, které přijmou odkaz na buňky jako argument, například funkce SUM a COUNTIF.
Použití příkazu INDIRECT jako argumentu pro COUNTIF vytváří dynamický rozsah odkazů na buňky, který může být počítán funkcí, pokud hodnoty buňky splňují kritéria. To dělá to tím, že textová data, někdy označována jako textový řetězec, do odkazu na buňku.
Tento příklad je založen na datech uvedených na obrázku výše. Vzorec COUNTIF - NEPŘÍMÝ vytvořený v tutoriálu je:
= COUNTIF (NEPŘÍMÉ (E1 & ":" & E2), "> 10")
V tomto vzorci obsahuje argument pro funkci INDIRECT:
- Odkazy na buňky E1 a E2, které obsahují textová data D1 a D6.
- Operátor rozsahu, dvojtečka (:) obklopené dvojitými uvozovkami (' '), který změní dvojtečku na textový řetězec.
- Dva ampersandy (&), které se používají k spojování nebo spojování dvojtečky s odkazy na buňky E1 a E2.
Výsledkem je, že INDIRECT převede textový řetězec D1: D6 do odkazu na buňku a přenese ji k funkci COUNTIF, která se započítává, pokud jsou referenční buňky větší než 10.
Funkce INDIRECT přijímá veškeré textové vstupy. Mohou to být buňky v listu, které obsahují textové nebo textové buněčné odkazy, které jsou zadávány přímo do funkce.
Dynamicky změňte rozsah formulace
Nezapomeňte, že cílem je vytvořit vzorec s dynamickým rozsahem. Dynamický rozsah lze měnit bez úpravy samotného vzorce.
Změnou textových dat umístěných v buňkách E1 a E2, od D1 a D6 až po D3 a D7, lze oblast, kterou lze pomocí funkce změnit, snadno měnit z D1: D6 na D3: D7. To eliminuje potřebu přímo upravit 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 dat. Také počítá buňky, které obsahují text tím, že kontrolují, zda odpovídají určitému textu.
Chcete-li to provést, zadáte následující vzorec v buňce G2:
= COUNTIF (INDIRECT (E1 & ":" & E2), "dva")
V tomto vzorce funkce INDIRECT odkazuje na buňky B1 až B6. Funkce COUNTIF udává počet buněk, které mají textovou hodnotu dva v nich.
V tomto případě je výsledek 1.
COUNTA, COUNTBLANK a NEPŘÍMÉ
Dva další funkce programu Excel jsou COUNTA, které počítají buňky obsahující libovolný typ dat a zároveň ignorují pouze prázdné nebo prázdné buňky a COUNTBLANK, který počítá pouze prázdné nebo prázdné buňky v rozsahu.
Protože obě tyto funkce mají podobnou syntaxi s funkcí COUNTIF, mohou být nahrazeny výše uvedeným příkladem příkazem INDIRECT a vytvořit následující vzorce:
= COUNTA (NEPŘÍMÉ (E1 & ":" & E2))= COUNTBLANK (NEPŘÍMÉ (E1 & ":" & E2)
Pro rozsah D1: D6 vrátí COUNTA odpověď 4, protože čtyři z pěti buněk obsahují data. COUNTBLANK vrátí odpověď 1, protože je v této oblasti pouze jedna prázdná buňka. Výhodou použití funkce INDIRECT ve všech těchto vzorcích je, že nové buňky lze vložit kdekoliv v rozsahu. Rozsah se dynamicky posune dovnitř různých funkcí a výsledky se aktualizují. Bez funkce INDIRECT by měla být každá funkce upravena tak, aby zahrnovala všech 7 buněk včetně nové. Výhody funkce INDIRECT jsou, že textové hodnoty lze vložit jako odkazy na buňky a dynamicky aktualizovat rozsahy, kdykoli se tabulka mění. Díky tomu je celková údržba tabulky mnohem snazší, zvláště u velkých tabulek. Proč používat funkci INDIRECT?
