Kako uporabljati dinamični obseg v Excelu s COUNTIF in INDIRECT

Kazalo:

2024 Avtor: Abigail Brown | [email protected]. Nazadnje spremenjeno: 2023-12-17 07:00

Kaj morate vedeti

Funkcija INDIRECT spremeni obseg referenc celic v formuli brez urejanja formule.
Uporabite INDIRECT kot argument za COUNTIF, da ustvarite dinamični obseg celic, ki ustrezajo podanim kriterijem.
Kriterije določi funkcija INDIRECT in štejejo se le celice, ki ustrezajo kriterijem.

Ta članek pojasnjuje, kako uporabiti funkcijo INDIRECT v Excelovih formulah za spreminjanje obsega referenc celic, uporabljenih v formuli, ne da bi morali urejati samo formulo. To zagotavlja uporabo istih celic, tudi ko se vaša preglednica spremeni. Informacije veljajo za Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel za Mac in Excel Online.

Uporaba dinamičnega obsega s formulo COUNTIF - INDIRECT

Funkcijo INDIRECT je mogoče uporabiti s številnimi funkcijami, ki sprejmejo sklic na celico kot argument, kot sta funkciji SUM in COUNTIF.

Uporaba INDIRECT kot argumenta za COUNTIF ustvari dinamični obseg referenc na celice, ki jih lahko funkcija prešteje, če vrednosti celice ustrezajo kriteriju. To stori tako, da pretvori besedilne podatke, včasih imenovane besedilni niz, v referenco celice.

Ta primer temelji na podatkih, prikazanih na zgornji sliki. Formula COUNTIF - INDIRECT, ustvarjena v vadnici, je:

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

V tej formuli argument za funkcijo INDIRECT vsebuje:

Celica se sklicuje na E1 in E2, ki vsebujeta besedilna podatka D1 in D6.
Operator obsega, dvopičje (:), obdan z dvojnimi narekovaji (" "), ki pretvori dvopičje v besedilo niz.
Dva znaka & (&), ki se uporabljata za veriženje ali združevanje dvopičja s sklici celic E1 in E2.

Rezultat je, da INDIRECT pretvori besedilni niz D1:D6 v sklic na celico in ga posreduje funkciji COUNTIF, ki se prešteje, če so navedene celice večje od 10.

Funkcija INDIRECT sprejema poljuben vnos besedila. To so lahko celice na delovnem listu, ki vsebujejo besedilo ali sklice na besedilne celice, ki so vnesene neposredno v funkcijo.

Dinamično spremenite obseg formule

Ne pozabite, da je cilj ustvariti formulo z dinamičnim razponom. Dinamično območje je mogoče spremeniti brez urejanja same formule.

S spreminjanjem besedilnih podatkov, ki se nahajajo v celicah E1 in E2, iz D1 in D6 v D3 in D7, lahko obseg, ki ga sešteje funkcija, preprosto spremenite iz D1:D6 v D3:D7. To odpravlja potrebo po neposrednem urejanju formule v celici G1.

Funkcija COUNTIF v tem primeru šteje le celice, ki vsebujejo številke, če so večje od 10. Čeprav štiri od petih celic v obsegu D1:D6 vsebujejo podatke, le tri celice vsebujejo številke. Celice, ki so prazne ali vsebujejo besedilne podatke, funkcija prezre.

Štetje besedila s COUNTIF

Funkcija COUNTIF ni omejena na štetje številskih podatkov. Prav tako prešteje celice, ki vsebujejo besedilo, tako da preveri, ali se ujemajo z določenim besedilom.

To naredite tako, da v celico G2 vnesete naslednjo formulo:

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

V tej formuli se funkcija INDIRECT sklicuje na celice B1 do B6. Funkcija COUNTIF sešteje število celic, ki imajo v sebi besedilno vrednost dve.

V tem primeru je rezultat 1.

COUNTA, COUNTBLANK in INDIRECT

Dve drugi Excelovi funkciji za štetje sta COUNTA, ki šteje celice, ki vsebujejo vse vrste podatkov, pri tem pa prezre samo prazne ali prazne celice, in COUNTBLANK, ki šteje samo prazne ali prazne celice v obsegu.

Ker imata obe funkciji podobno sintakso kot funkcija COUNTIF, ju lahko nadomestite v zgornjem primeru z INDIRECT, da ustvarite naslednje formule:

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

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

Za obseg D1:D6 COUNTA vrne odgovor 4, ker štiri od petih celic vsebujejo podatke. COUNTBLANK vrne odgovor 1, ker je v obsegu samo ena prazna celica.

Zakaj uporabljati POSREDNO funkcijo?

Prednost uporabe funkcije INDIRECT v vseh teh formulah je, da je mogoče nove celice vstaviti kamor koli v obseg.

Razpon se dinamično premika znotraj različnih funkcij in rezultati se ustrezno posodabljajo.

Brez funkcije INDIRECT bi bilo treba vsako funkcijo urediti tako, da vključuje vseh 7 celic, vključno z novo.

Prednosti funkcije INDIRECT so, da je besedilne vrednosti mogoče vstaviti kot sklice na celice in da dinamično posodablja obsege vsakič, ko se vaša preglednica spremeni.

To poenostavi splošno vzdrževanje preglednic, zlasti za zelo velike preglednice.