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.
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.
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.
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.
De data aceasta, vom crea un interval definit dinamic, care include anteturile. Faceți clic pe Formulas > Define Name.
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))
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.
Leave A Comment?