Pomocí funkce INDIRECT ve vzorcích aplikace Excel můžete změnit rozsah odkazů na buňky použité ve vzorci, aniž byste museli upravovat samotný vzorec. Tím je zajištěno, že budou použity stejné buňky, i když se tabulka změní. 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 se vzorcem COUNTIF – INDIRECT
Funkci INDIRECT lze použít s řadou funkcí, které přijímají odkaz na buňku jako argument, například 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 buňky splňují kritéria. Dělá to tak, že změní textová data, někdy označovaná jako textový řetězec, na odkaz na buňku.
type = „code“> 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í ke zřetězení nebo spojení dvojtečky s odkazy na buňky E1 a E2.
Výsledkem je, že INDIRECT převede textový řetězec D1: D6 na odkaz na buňku a předá ji funkci COUNTIF, která se má spočítat, pokud jsou odkazované buňky větší než 10. Funkce INDIRECT přijímá jakékoli textové vstupy. 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ěňte 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 sečtený funkcí z D1: D6 na D3: D7. To eliminuje potřebu přímé úpravy vzorce v buňce G1. Funkce COUNTIF v tomto příkladu počítá pouze buňky obsahující čísla, pokud jsou větší než 10. Přestože č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. Počítá také buňky obsahující text kontrolou, zda odpovídají určitému textu. K tomu je do buňky G2 zadán následující vzorec: = COUNTIF (INDIRECT (E1 & „:“ & E2), „two“)
type = „code“> V tomto vzorci odkazuje funkce INDIRECT na buňky B1 až B6. Funkce COUNTIF sčítá počet buněk, které mají textovou hodnotu dva v nich. V tomto případě je výsledek 1.
COUNTA, COUNTBLANK a INDIRECT
Dvě další funkce pro počítání v Excelu jsou COUNTA, která počítá buňky obsahující jakýkoli typ dat, zatímco ignoruje 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 jako funkce COUNTIF, lze je do výše uvedeného příkladu nahradit funkcí INDIRECT a vytvořit následující vzorce: = COUNTA (INDIRECT (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 v rozsahu je pouze jedna prázdná buňka.
Proč používat NEPŘÍMOU funkci?
Výhodou použití funkce NEPŘÍMÝ ve všech těchto vzorcích je, že nové buňky lze vložit kamkoli v rozsahu. Rozsah se dynamicky posouvá uvnitř různých funkcí a výsledky se odpovídajícím způsobem aktualizují.
