Koristite funkciju INDIRECT u Excel formulama da biste promijenili raspon referenci za ćelije koji se upotrebljavaju u formuli bez potrebe za uređivanjem same formule. Time se osigurava korištenje iste ćelije, čak i kada se proračunska tablica mijenja.
Bilješka: Upute u ovom članku odnose se na Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel za Mac i Excel Online.
Upotrijebite dinamički raspon s formulom COUNTIF - INDIRECT
INDIRECT funkcija može se koristiti s brojnim funkcijama koje prihvaćaju referencu stanica kao argument, kao što su SUM i COUNTIF funkcije.
Upotrebljavajući INDIRECT kao argument za COUNTIF izrađuje dinamički raspon referencija za ćelije koji se mogu računati pomoću funkcije ako vrijednosti ćelije zadovoljavaju kriterije. To čini tako da tekstualne podatke, koji se ponekad nazivaju tekstni niz, pretvaraju u referencu na ćeliju.
Ovaj se primjer temelji na podacima prikazanim na gornjoj slici. Formula COUNTIF - INDIRECT izrađena u vodiču je:
= COUNTIF (INDIRECT (E1 i "" i E2), "> 10")
U ovoj formuli, argument za funkciju INDIRECT sadrži:
- Referencije stanice E1 i E2, koje sadrže tekstualne podatke D1 i D6.
- Operator dometa, debelo crijevo (:) okružen dvostrukim navodnicima (' ') koji pretvara debelo crijevo u tekstni niz.
- Dvije ampersands (&) koje se koriste za povezivanje ili povezivanje debelog crijeva s referencama stanice E1 i E2.
Rezultat je taj da INDIRECT pretvara tekstni niz D1: D6 u referencu ćelije i prosljeđuje ga na funkciju COUNTIF koja se broji ako su referentne ćelije veće od 10.
INDIRECT funkcija prihvaća sve unose teksta. To mogu biti ćelije u radnom listu s tekstom ili tekstnim ćelijama koje se unose izravno u funkciju.
Dinamički promijenite raspon formule
Zapamtite, cilj je stvoriti formulu s dinamičkim rasponom. Dinamički raspon može se promijeniti bez uređivanja same formule.
Promjenom tekstualnih podataka koji se nalaze u stanicama E1 i E2, od D1 i D6 do D3 i D7, raspon ukupne funkcije može se lako promijeniti iz D1: D6 do D3: D7. Time se eliminira potreba za izravnim uređivanjem formule u ćeliji G1.
Funkcija COUNTIF u ovom primjeru broji samo stanice koje sadrže brojeve ako su veće od 10. Iako četiri od pet ćelija u dometu D1: D6 sadrže podatke, samo tri ćelije sadrže brojeve. Funkcija zanemaruje ćelije koje su prazne ili sadrže tekstualne podatke.
Brojanje teksta COUNTIF
Funkcija COUNTIF nije ograničena na brojenje numeričkih podataka. Također broji stanice koje sadrže tekst provjerom odgovaraju li određeni tekst.
Da biste to učinili, sljedeća formula unesena je u ćeliju G2:
= COUNTIF (INDIRECT (E1 i "" i E2), "dva")
U ovoj formuli funkcija INDIRECT odnosi se na stanice B1 do B6. Funkcija COUNTIF iznosi ukupan broj ćelija koje imaju vrijednost teksta dva u njima.
U ovom slučaju, rezultat je 1.
COUNTA, COUNTBLANK i INDIRECT
Dvije druge funkcije računanja brojeva su COUNTA, koja broji ćelije koje sadrže bilo koju vrstu podataka, a ignoriraju samo prazne ili prazne ćelije i COUNTBLANK, koja broji samo prazne ili prazne ćelije u rasponu.
Budući da obje ove funkcije imaju sličnu sintaksu na funkciju COUNTIF, one se mogu zamijeniti u gornjem primjeru s INDIRECT-om kako bi stvorili sljedeće formule:
= COUNTA (INDIRECT (E1 i "" i E2))= COUNTBLANK (INDIRECT (E1 i "" i E2)
Za raspon D1: D6 COUNTA vraća odgovor od 4, jer četiri od pet ćelija sadrže podatke. COUNTBLANK vraća odgovor od 1 jer u rasponu postoji samo jedna prazna ćelija. Prednost korištenja funkcije INDIRECT u svim ovim formulama je da nove stanice mogu biti umetnute bilo gdje u rasponu. Raspon se dinamički mijenja unutar različitih funkcija, a rezultati se ažuriraju u skladu s tim. Bez funkcije INDIRECT, svaka funkcija trebala bi biti uređena tako da uključuje sve sedam ćelija, uključujući i novu. Prednosti INDIRECT funkcije su da se tekstne vrijednosti mogu umetnuti kao reference stanica i da dinamički ažuriraju raspone svaki put kada se promijeni proračunska tablica. To olakšava održavanje ukupne proračunske tablice, posebno za vrlo velike proračunske tablice. Zašto koristiti INDIRECT funkciju?
