Kako uporabljati dinamične imenovane obsege v Excelu

Anonim

V svojem nedavnem članku sem v Excelu govoril o imenovanih razponih. Med raziskovanjem poimenovanih območij se je pojavila tema dinamičnega razpona. Tako bom v tem članku razložil, kako lahko naredite dinamični razpon v Excelu.

Kaj je dinamično imenovano območje v Excelu?

Običajno imenovano območje je statično. Če definirate C2: C10 kot Postavka, postavka se bo vedno skliceval na C2: C10, dokler in če ga ne uredite ročno. Na spodnji sliki štejemo prazna poljaArtikel seznam. Prikazuje 2. Če bi bilo dinamično, bi prikazalo 0.

Dinamični obseg imen je obseg imen, ki se glede na podatke širi in krči. Na primer, če imate seznam elementov v območju C2: C10 in ga poimenujte Predmeti, se mora razširiti na C2: C11 če dodate nov element v obseg in bi se moral skrčiti, če zmanjšate, ko izbrišete, kot je opisano zgoraj.

Kako ustvariti dinamični obseg imen

Ustvarite poimenovana območja z uporabo Excelovih tabel

Da, tabele Excel lahko ustvarijo dinamične imenovane obsege. Vsak stolpec bodo naredili v tabeli z imenom range, ki je zelo dinamična.
Obstaja pa ena pomanjkljivost imen tabel, ki jih ne morete uporabiti pri preverjanju podatkov in pogojnem oblikovanju. Toda tam se lahko uporabijo določeni obsegi Named.

Uporabite formulo INDIRECT in COUNTA

Za dinamično imenovanje lahko uporabimo funkcijo INDIRECT in COUNTA

. Kako? Pa poglejmo.

Splošna formula, ki jo je treba napisati v razdelku Nanaša se na:

= INDIRECT ("$ startCell: $ndingColumnLetter $" & COUNTA ($ columnLetter: $ columnLetter))

Zgornja splošna formula je lahko videti zapletena, vendar je v resnici enostavna. Poglejmo na primeru.
Osnovna ideja je določiti zadnjo uporabljeno celico.

Primer dinamičnega razpona

V zgornjem primeru smo imeli statično ime obseg Postavka v območju C2: C10. Naj bo dinamična.

    • Odprite Upravitelj imen s pritiskom na CTRL+F3.
    • Zdaj, če ime že obstaja v obsegu, ga kliknite in nato kliknite Uredi. V nasprotnem primeru kliknite Novo.
    • Poimenujte ga Element.
    • V Se nanaša na: Oddelek napišite pod formulo.
= INDIRECT ("$ C2: $ C $" & COUNTA ($ C: $ C))
  • Pritisnite gumb V redu.

In končano je. Zdaj, ko boste v polje z imenom ali v katero koli formulo vnesli element, se bo to nanašalo na C2 kot zadnjo uporabljeno celico v obsegu.

Pozor: Nobena celica ne sme biti prazna v razponu. V nasprotnem primeru se bo obseg zmanjšal za število praznih celic.

Kako deluje?

Kot sem rekel, edina stvar je najti zadnjo uporabljeno celico. V tem primeru vmes ne sme biti nobena celica prazna. Zakaj? Vedeli boste.

INDIRECT funkcija v Excelu pretvori besedilo v obseg. = INDIRECT ("$ C $ 2: $ C $ 9") se bo nanašalo na absolutni razpon $ C $ 2: $ C $ 10. Dinamično moramo najti zadnjo številko vrstice (9).
Ker imajo vse celice določeno vrednost v območju C2: C10, lahko s funkcijo COUNTA poiščemo zadnjo vrstico.
Torej,= NEPOSREDNO("$ C2: $ C $" & ta del popravi začetno vrstico in stolpec ter COUNTA($ C: $ C) dinamično izračuna zadnjo uporabljeno vrstico.

Ja, tako lahko naredite najučinkovitejše dinamične imenovane obsege, ki bodo delovali z vsako formulo in funkcionalnostjo Excela. Ko spremenite podatke, vam ni treba znova urejati imenovanega obsega.

Prenesite datoteko:

Dinamični imenovani obsegi v Excelu

Kako uporabljati imenovane obsege v Excelu

17 neverjetnih lastnosti tabel Excel

Priljubljeni članki:

50 bližnjic v Excelu za večjo produktivnost

Kako uporabljati funkcijo VLOOKUP v Excelu

Kako uporabljati funkcijo COUNTIF v Excelu

Kako uporabljati funkcijo SUMIF v Excelu