Cum se creează un interval definit dinamic în Excel

Configurare noua (How To)

Situatie

Datele dvs. Excel se schimbă frecvent, astfel încât este util să creați un interval definit dinamic, care se extinde automat și se contractă la dimensiunea intervalului dvs. de date. Să vedem cum.

Folosind un interval definit dinamic, nu va trebui să editați manual intervalele de formule, diagrame și tabele rotative atunci când se schimbă datele. Acest lucru se va întâmpla automat.

Două formule sunt utilizate pentru a crea intervale dinamice: OFFSET și INDEX. Acest articol se va concentra pe utilizarea funcției INDEX, deoarece este o abordare mai eficientă. OFFSET este o funcție volatilă și poate încetini foile de calcul mari.

Solutie

Pasi de urmat

Creați un interval definit dinamic în Excel:

Pentru primul nostru exemplu, avem lista de date cu o singură coloană văzută mai jos.

Data range to make dynamic

Avem nevoie ca acest lucru să fie dinamic, astfel încât dacă sunt adăugate sau eliminate mai multe țări, intervalul se actualizează automat.

Pentru acest exemplu, dorim să evităm celula din antet. Ca atare, dorim intervalul $ A $ 2: $ A $ 6, dar dinamic. Faceți acest lucru făcând clic pe Formulas> Define Name.

Create a defined name in Excel

Tastați „countries” în caseta „Name” și apoi introduceți formula de mai jos în caseta „Refers to”.

=$A$2:INDEX($A:$A,COUNTA($A:$A))

Tastați această ecuație într-o celulă a foii de calcul și apoi copierea acesteia în caseta New Name este uneori mai rapid și mai ușor.

Using a formula in a defined name

Cum funcționează asta?

Prima parte a formulei specifică celula de start a intervalului (A2 în cazul nostru) și apoi operatorul de interval (:).

 

=$A$2:

Utilizarea operatorului de intervale forțează funcția INDEX să returneze un interval în loc de valoarea unei celule. Funcția INDEX este apoi utilizată cu funcția COUNTA. COUNTA numără numărul de celule necompletate în coloana A (șase în cazul nostru).

 

INDEX($A:$A,COUNTA($A:$A))

Această formulă solicită funcției INDEX să returneze intervalul ultimei celule necompletate din coloana A ($ A $ 6).

Rezultatul final este de $ A $ 2: $ A $ 6 și, din cauza funcției COUNTA, este dinamic, deoarece va găsi ultimul rând. Acum puteți folosi acest nume definit „countries” în cadrul unei reguli, formulă, diagramă de validare a datelor sau oriunde trebuie să facem referință la numele tuturor țărilor.

Creați un interval definit în două moduri dinamic:

Primul exemplu a fost doar înălțime dinamică. Cu toate acestea, cu o ușoară modificare și o altă funcție COUNTA, puteți crea o gamă dinamică atât în înălțime cât și lățime.

În acest exemplu, vom folosi datele prezentate mai jos.

Data for a two way dynamic range

De data aceasta, vom crea un interval definit dinamic, care include anteturile. Faceți clic pe Formulas > Define Name.

Create a defined name in Excel

Introduceți „sales” în caseta „Name”și introduceți formula de mai jos în caseta „Refers To”.

 

=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

Two way dynamic defined range formula

Această formulă folosește $ A $ 1 ca celulă de început. Funcția INDEX utilizează apoi o gamă a întregii foi de lucru ($ 1: 1048576 $) pentru a căuta și a reveni din. Una dintre funcțiile COUNTA este folosită pentru a număra rândurile necompletate și o alta pentru coloanele necompletate care o fac dinamică în ambele direcții. Deși această formulă a pornit de la A1, ați putea specifica orice celulă de început.

Acum puteți utiliza acest nume definit (sales) într-o formulă sau ca o serie de date grafic pentru a le face dinamice.

Tip solutie

Permanent

Voteaza

(8 din 19 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?