Uporabite funkcijo INDIRECT v formulah Excel, da spremenite obseg referenčnih celic, uporabljenih v formuli, ne da bi morali urediti samo formulo. To zagotavlja, da se uporabljajo iste celice, tudi če se vaša preglednica spremeni.
Opomba: Navodila v tem članku veljajo za Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel za Mac in Excel Online.
Uporabite dinamični obseg z obrazcem COUNTIF - INDIRECT
Funkcijo INDIRECT lahko uporabite s številnimi funkcijami, ki sprejemajo referenco celic kot argument, kot so funkcije SUM in COUNTIF.
Uporaba INDIRECT-a kot argumenta za COUNTIF ustvarja dinamično območje referenc celic, ki ga funkcija lahko šteje, če vrednosti celice ustrezajo merilom. To naredi tako, da v referenčni okvir pretvori tekstovne podatke, ki se včasih imenujejo tudi besedilni niz.
Ta primer temelji na podatkih, prikazanih na zgornji sliki. Formula COUNTIF - INDIRECT, ustvarjena v vajah, je:
= COUNTIF (INDIRECT (E1 & ":" & E2), ">" 10 ")
V tej formuli argument za funkcijo INDIRECT vsebuje:
- Referenčne številke celic E1 in E2, ki vsebujejo besedilne podatke D1 in D6.
- Operater obsega, debelo črevo (:), obdani z dvojnimi narekovaji (' '), ki preoblikuje debelo črevo v besedilni niz.
- Dva ampersands (&), ki se uporabljajo za združevanje ali združevanje debelega črevesa s celicami E1 in E2.
Rezultat tega je, da INDIRECT pretvori besedilni niz D1: D6 v referenco celice in jo prenese skupaj z funkcijo COUNTIF, ki se šteje, če so referenčne celice večje od 10.
Funkcija INDIRECT sprejema vse vnose besedila. To so lahko celice v delovnem listu, ki vsebujejo reference besedila ali besedilnih celic, ki so neposredno vnesene v funkcijo.
Dinamično spremenite obseg formule
Ne pozabite, cilj je ustvariti formulo z dinamičnim razponom. Dinamično območje lahko spremenite brez urejanja same formule.
S spreminjanjem besedilnih podatkov, ki se nahajajo v celicah E1 in E2, od D1 in D6 do D3 in D7, se lahko obseg, ki ga funkcija ujema s funkcijo, lahko preprosto spremeni iz D1: D6 v D3: D7. To odpravlja potrebo po neposredno urejanju formule v celici G1.
Funkcija COUNTIF v tem primeru šteje samo celice, ki vsebujejo številke, če so večje od 10. Čeprav štiri od petih celic v območju D1: D6 vsebujejo podatke, le tri celice vsebujejo številke. Funkcija ne upošteva celic, ki so prazne ali vsebujejo besedilne podatke.
Štetje besedila s COUNTIF
Funkcija COUNTIF ni omejena na štetje številčnih podatkov. Prav tako šteje celice, ki vsebujejo besedilo, tako da preverijo, ali se ujemajo z določenim tekstom.
Če želite to narediti, v celico G2 vnesemo naslednjo formulo:
= COUNTIF (INDIRECT (E1 & ":" & E2), "dve")
V tej formuli se funkcija INDIRECT nanaša na celice B1 do B6. Funkcija COUNTIF je skupno število celic, ki imajo besedilno vrednost dva v njih.
V tem primeru je rezultat 1.
COUNTA, COUNTBLANK in INDIRECT
Dve drugi funkciji števila Excelov sta COUNTA, ki štejeta celice, ki vsebujejo katero koli vrsto podatkov, pri čemer ignorirajo samo prazne ali prazne celice, in COUNTBLANK, ki šteje samo prazne ali prazne celice v območju.
Ker imata obe funkciji podobno skladnjo s funkcijo COUNTIF, jih lahko nadomestite z zgornjim primerom z ukazom INDIRECT, da ustvarite naslednje formule:
= COUNTA (INDIRECT (E1 & ":" & E2))= COUNTBLANK (INDIRECT (E1 & ":" & E2)
Za območje D1: D6, COUNTA vrne odgovor 4, saj štiri od petih celic vsebujejo podatke. COUNTBLANK vrne odgovor na 1, saj je v dosegu samo ena prazna celica. Korist pri uporabi funkcije INDIRECT v vseh teh formulah je, da se lahko nove celice vstavijo kjerkoli v območju. Območje se dinamično premika znotraj različnih funkcij, rezultati pa se ustrezno posodabljajo. Brez funkcije INDIRECT bi bilo treba urejati vsako funkcijo, ki bi vključevala vse 7 celic, vključno z novo. Prednosti funkcije INDIRECT so, da se lahko besedilne vrednosti vstavijo kot reference celic in da dinamično posodablja območja, ko se preglednica spremeni. S tem je veliko olajšanje vzdrževanja celotne preglednice, še posebej pri zelo velikih preglednicah. Zakaj uporabljati funkcijo INDIRECT?
