Használja az INDIRECT függvényt az Excel képletekben, hogy megváltoztassa a képletben használt sejt-referenciák tartományát anélkül, hogy maga a képletet kellene módosítania. Ez biztosítja, hogy ugyanazokat a cellákat használják, még akkor is, ha a táblázata megváltozik.
jegyzet: A cikkben szereplő utasítások az Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac és Excel Online alkalmazásokra vonatkoznak.
Dinamikus tartomány használata a COUNTIF - INDIRECT képlet segítségével
Az INDIRECT funkció számos olyan funkcióval használható, amelyek elfogadják a cellahivatkozást, mint például az SUM és COUNTIF függvényeket.
Az INDIRECT mint COUNTIF argumentum használatával létrehozza a sejtek referenciáinak dinamikus tartományát, amelyeket a függvény megszámolhat, ha a cellaértékek megfelelnek a kritériumoknak. Ez úgy történik, hogy szöveges adatokat, amelyeket néha szövegszövegnek is neveznek, cellahivatkozásra.
Ez a példa a fenti képen látható adatokon alapul. A bemutatóban létrehozott COUNTIF - INDIRECT formula:
= COUNTIF (közvetett (E1 & ":" & E2), "> 10")
Ebben a képletben az INDIRECT függvény argumentuma a következőket tartalmazza:
- Az E1 és E2 cellahivatkozások, amelyek a D1 és D6 szöveges adatokat tartalmazzák.
- A hatótávolság operátor, a vastagbél (:) kettős idézőjelekkel (' '), amely a kettőspontot szöveget tartalmazza.
- Két amper (és), amelyekkel a kettőspontot az E1 és az E2 sejtes referenciákkal összekapcsolják vagy összekapcsolják.
Az eredmény az, hogy az INDIRECT a D1: D6 szöveget átalakítja egy cellahivatkozásra, és átadja azt a számláló COUNTIF függvénynek, ha a hivatkozott cellák nagyobbak, mint 10.
Az INDIRECT funkció bármilyen szövegbevitelt fogad el. Ezek lehetnek a munkalapon lévő cellák, amelyek szöveget vagy szövegcella hivatkozásokat tartalmaznak, és közvetlenül a függvénybe kerülnek be.
Dinamikusan változtassa meg a képlet tartományát
Ne feledje, a cél egy dinamikus tartományú képlet létrehozása. A dinamikus tartomány megváltoztatható anélkül, hogy maga a képletet szerkesztené.
Az E1 és E2 cellákban található szövegadatok megváltoztatásával D1 és D6-ról D3-ra és D7-re a funkció által elért tartomány könnyen átváltható D1: D6-ról D3: D7-re. Ez kiküszöböli a G1 cellában található képlet közvetlen módosítását.
A COUNTIF függvény ebben a példában csak a számokat tartalmazó cellákat számolja, ha 10-nél nagyobbak. Még akkor is, ha a D1: D6 tartományban lévő öt cellából négy tartalmazza az adatokat, csak három cella tartalmaz számokat. Az üres vagy szöveges adatokat tartalmazó cellákat a függvény figyelmen kívül hagyja.
Számlálás a COUNTIF segítségével
Az COUNTIF funkció nem korlátozódik a numerikus adatok számlálására. Szintén számítja azokat a cellákat, amelyek szöveget tartalmaznak annak ellenőrzésével, hogy megfelelnek-e egy bizonyos szövegnek.
Ehhez a következő képletet kell megadni a G2 cellában:
= COUNTIF (közvetett (E1 & ":" & E2), "két")
Ebben a képletben az INDIRECT függvény a B1 - B6 cellákat jelenti. Az COUNTIF függvény összegzi a szövegértékkel rendelkező cellák számát két bennük.
Ebben az esetben az eredmény 1.
COUNTA, COUNTBLANK és KÖZVETLEN
Két másik Excel-számláló funkciót tartalmaz: COUNTA, amely számít minden olyan típusú cellát, amely csak üres vagy üres cellákat vesz figyelembe, valamint COUNTBLANK, amely csak üres vagy üres cellákat számít egy tartományban.
Mivel mindkét funkciónak hasonló a COUNTIF függvényéhez tartozó szintaxisa, a fenti példával helyettesítheti az INDIRECT értékkel a következő képletek létrehozásával:
= COUNTA (közvetett (E1 & ":" & E2))= COUNTBLANK (közvetett (E1 & ":" & E2)
A D1: D6 tartománynál a COUNTA 4-es számú válasz ad vissza, mivel az öt cella közül négy tartalmaz adatokat. A COUNTBLANK egy 1-es választ ad vissza, mivel a tartományban csak egy üres cella van. Az INDIRECT funkció használatának előnye, hogy mindegyik képletben az új sejtek beilleszthetők a tartomány bármely pontjára. A tartomány dinamikusan elmozdul a különböző funkciók belsejében, és az eredmények ennek megfelelően frissülnek. Az INDIRECT funkció nélkül minden funkciót szerkeszteni kell, hogy az tartalmazza mind a 7 cellát, beleértve az újat is. Az INDIRECT funkció előnyei, hogy a szöveges értékek beilleszthetők cellaszámokra, és dinamikusan frissítheti a tartományokat, amikor a táblázat megváltozik. Ez megkönnyíti az általános táblázatok karbantartását, különösen a nagyméretű táblázatok esetében. Miért használjon INDIRECT funkciót?
