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

Sadržaj:

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

Šta treba znati

INDIRECT funkcija mijenja raspon referenci ćelija u formuli bez uređivanja formule.
Koristite INDIRECT kao argument za COUNTIF da kreirate dinamički raspon ćelija koje ispunjavaju određene kriterije.
Kriterijumi su uspostavljeni INDIRECT funkcijom i računaju se samo ćelije koje ispunjavaju kriterij.

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. Ovo osigurava da se koriste iste ćelije, čak i kada se vaša tabela promijeni. Informacije se odnose na Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel za Mac i Excel Online.

Koristite dinamički raspon sa COUNTIF - INDIREKTNO formulom

INDIRECT funkcija se može koristiti sa brojnim funkcijama koje prihvataju referencu ćelije kao argument, kao što su funkcije SUM i COUNTIF.

Upotreba INDIRECT kao argumenta za COUNTIF stvara dinamički raspon referenci ćelija koje se mogu brojati od strane funkcije ako vrijednosti ćelije ispunjavaju kriterij. To radi pretvaranjem tekstualnih podataka, koji se ponekad nazivaju tekstualnim nizom, u referencu ćelije.

Ovaj primjer je zasnovan na podacima prikazanim na gornjoj slici. Formula COUNTIF - INDIRECT kreirana u tutorijalu je:

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

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

Ćelija upućuje na E1 i E2, koje sadrže tekstualne podatke D1 i D6.
Operator raspona, dvotačka (:) okružena dvostrukim navodnicima (" ") koja dvotačku pretvara u tekst niz.
Dva ampersanda (&) koja se koriste za spajanje ili spajanje dvotočka sa referencama ćelije E1 i E2.

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

INDIRECT funkcija prihvata bilo koji unos teksta. To mogu biti ćelije na radnom listu koje sadrže tekst ili tekstualne reference ćelija koje se unose direktno u funkciju.

Dinamički promijenite raspon formule

Zapamtite, cilj je kreirati formulu sa 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, od D1 i D6 do D3 i D7, raspon koji je zbroj funkcija može se lako promijeniti iz D1:D6 u D3:D7. Ovo eliminiše potrebu za direktnim uređivanjem formule u ćeliji G1.

Funkcija COUNTIF u ovom primjeru broji ćelije koje sadrže brojeve samo 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 ignorira.

Odbrojavanje teksta sa COUNTIF

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

Da biste to učinili, u ćeliju G2 se unosi sljedeća formula:

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

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

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, zanemarujući 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 kao funkcija COUNTIF, mogu se zamijeniti u gornji primjer sa INDIRECT kako bi se kreirale sljedeće formule:

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

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

Za opseg D1:D6, COUNTA vraća odgovor 4, pošto četiri od pet ćelija sadrže podatke. COUNTBLANK vraća odgovor 1 jer postoji samo jedna prazna ćelija u rasponu.

Zašto koristiti INDIREKTNU 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 INDIRECT funkcije, svaka funkcija bi se morala urediti da uključi svih 7 ćelija, uključujući i novu.

Prednosti INDIRECT funkcije su da se tekstualne vrijednosti mogu umetnuti kao reference na ćelije i da dinamički ažurira raspone kad god se promijeni tabela.

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