Kako koristiti dinamički raspon u Excelu s COUNTIF i INDIRECT

Sadržaj:

2024 Autor: Abigail Brown | [email protected]. Zadnja promjena: 2023-12-17 06:49

Što treba znati

Funkcija INDIRECT mijenja raspon referenci ćelija u formuli bez uređivanja formule.
Koristite INDIRECT kao argument za COUNTIF za stvaranje dinamičkog raspona ćelija koje zadovoljavaju navedene kriterije.
Kriterije uspostavlja funkcija INDIRECT, a broje se samo ćelije koje zadovoljavaju kriterije.

Ovaj članak objašnjava kako koristiti INDIRECT funkciju u Excel formulama za promjenu raspona referenci ćelija koje se koriste u formuli bez potrebe za uređivanjem same formule. To osigurava korištenje istih ćelija, čak i kada se proračunska tablica promijeni. Informacije se odnose na Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel za Mac i Excel Online.

Koristite dinamički raspon s formulom COUNTIF - INDIRECT

Funkcija INDIRECT može se koristiti s nizom funkcija koje prihvaćaju referencu ćelije kao argument, kao što su funkcije SUM i COUNTIF.

Korištenje INDIRECT kao argumenta za COUNTIF stvara dinamički raspon referenci ćelije koje funkcija može prebrojati ako vrijednosti ćelije zadovoljavaju kriterij. To čini pretvaranjem tekstualnih podataka, koji se ponekad nazivaju tekstualni niz, u referencu ćelije.

Ovaj primjer se temelji na podacima prikazanim na gornjoj slici. Formula COUNTIF - INDIRECT stvorena u vodiču je:

=COUNTIF(INDIRECT(E1&":"&E2), ">10")

U ovoj formuli, argument za INDIRECT funkciju sadrži:

Referenca ćelije E1 i E2, koja sadrži tekstualne podatke D1 i D6.
Operator raspona, dvotočka (:) okružena dvostrukim navodnicima (" ") koja dvotočku pretvara u tekst niz.
Dva ampersanda (&) koja se koriste za ulančavanje ili spajanje dvotočke s referencama ćelija E1 i E2.

Rezultat je da INDIRECT pretvara tekstualni niz D1:D6 u referencu ćelije i prosljeđuje ga funkciji COUNTIF koja se broji ako su referencirane ćelije veće od 10.

Funkcija INDIRECT prihvaća sve unose teksta. To mogu biti ćelije na radnom listu koje sadrže tekst ili reference tekstualnih ćelija koje se unose izravno u funkciju.

Dinamički promijenite raspon formule

Zapamtite, cilj je stvoriti formulu s dinamičkim rasponom. Dinamički raspon se može promijeniti bez uređivanja same formule.

Promjenom tekstualnih podataka koji se nalaze u ćelijama E1 i E2, iz D1 i D6 u D3 i D7, ukupni raspon funkcije može se lako promijeniti iz D1:D6 u D3:D7. Time se eliminira potreba za izravnim uređivanjem formule u ćeliji G1.

Funkcija COUNTIF u ovom primjeru samo broji ćelije koje sadrže brojeve ako su veće od 10. Iako četiri od pet ćelija u rasponu D1:D6 sadrže podatke, samo tri ćelije sadrže brojeve. Ćelije koje su prazne ili sadrže tekstualne podatke funkcija zanemaruje.

Brojenje teksta s COUNTIF

Funkcija COUNTIF nije ograničena na brojanje numeričkih podataka. Također broji ćelije koje sadrže tekst provjeravajući podudaraju li se s određenim tekstom.

Da biste to učinili, u ćeliju G2 unesite sljedeću formulu:

=COUNTIF(INDIRECT(E1&":"&E2), "dva")

U ovoj formuli funkcija INDIRECT upućuje na ćelije B1 do B6. Funkcija COUNTIF zbraja ukupan broj ćelija koje u sebi imaju tekstualnu vrijednost dva.

U ovom slučaju rezultat je 1.

COUNTA, COUNTBLANK i INDIRECT

Dvije druge Excel funkcije brojanja su COUNTA, koja broji ćelije koje sadrže bilo koju vrstu podataka, a zanemaruje samo prazne ćelije, i COUNTBLANK, koja broji samo prazne ili prazne ćelije u rasponu.

Budući da obje ove funkcije imaju sličnu sintaksu kao funkcija COUNTIF, mogu se zamijeniti u gornjem primjeru s INDIRECT kako bi se stvorile sljedeće formule:

=COUNTA(INDIRECT(E1&":"&E2))

=COUNTBLANK(INDIRECT(E1&":"&E2)

Za raspon D1:D6, COUNTA vraća odgovor 4, budući da četiri od pet ćelija sadrže podatke. COUNTBLANK vraća odgovor 1 jer postoji samo jedna prazna ćelija u rasponu.

Zašto koristiti INDIRECT funkciju?

Prednost korištenja INDIRECT funkcije u svim ovim formulama je da se nove ćelije mogu umetnuti bilo gdje u rasponu.

Raspon se dinamički pomiče unutar različitih funkcija, a rezultati se ažuriraju u skladu s tim.

Bez funkcije INDIRECT, svaku bi funkciju trebalo urediti da uključi svih 7 ćelija, uključujući novu.

Prednosti funkcije INDIRECT su da se tekstualne vrijednosti mogu umetnuti kao reference na ćeliju i da ona dinamički ažurira raspone kad god se vaša proračunska tablica promijeni.

Ovo čini cjelokupno održavanje proračunske tablice mnogo lakšim, posebno za vrlo velike proračunske tablice.