Formula generică
=$A$1:INDEX($A:$A,lastrow)
rezumat
O modalitate de a crea o gamă dinamică denumită în Excel este utilizarea funcției INDEX. În exemplul prezentat, intervalul numit „date” este definit de următoarea formulă:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
care se rezolvă la intervalul $ A $ 2: $ A $ 10.
Notă: această formulă este menită să definească un interval numit care poate fi utilizat în alte formule.
Explicaţie
Această pagină prezintă un exemplu de gamă dinamică denumită creată cu funcția INDEX împreună cu funcția COUNTA. Intervalele dinamice denumite se extind și se contractă automat când datele sunt adăugate sau eliminate. Ele reprezintă o alternativă la utilizarea unui tabel Excel, care se redimensionează și pe măsură ce datele sunt adăugate sau eliminate.
Funcția INDEX returnează valoarea la o poziție dată într-un interval sau matrice. Puteți utiliza INDEX pentru a prelua valori individuale sau rânduri și coloane întregi dintr-un interval. Ceea ce face INDEX deosebit de util pentru intervalele denumite dinamice este că returnează de fapt o referință. Aceasta înseamnă că puteți utiliza INDEX pentru a construi o referință mixtă, cum ar fi $ A $ 1: A100.
În exemplul prezentat, intervalul numit „date” este definit de următoarea formulă:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
care se rezolvă la intervalul $ A $ 2: $ A $ 10.
Cum funcționează această formulă
Rețineți mai întâi că această formulă este compusă din două părți care se așează de fiecare parte a operatorului de gamă (:). În stânga, avem referința inițială pentru gamă, codată tare ca:
$A$2
În dreapta este referința finală pentru interval, creată cu INDEX astfel:
INDEX($A:$A,COUNTA($A:$A))
Aici, alimentăm INDEX toată coloana A pentru matrice, apoi folosim funcția COUNTA pentru a afla „ultimul rând” din interval. COUNTA funcționează bine aici, deoarece există 10 valori în coloana A, inclusiv un rând de antet. Prin urmare, COUNTA returnează 10, care merge direct în INDEX ca număr de rând. INDEX returnează apoi o referință la $ A $ 10, ultimul rând utilizat din interval:
INDEX($A:$A,10) // resolves to $A$10
Deci, rezultatul final al formulei este acest interval:
$A$2:$A$10
Un interval bidimensional
Exemplul de mai sus funcționează pentru un interval unidimensional. Pentru a crea un interval dinamic bidimensional în care și numărul de coloane este dinamic, puteți utiliza aceeași abordare, extinsă astfel:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
La fel ca înainte, COUNTA este folosit pentru a afla „lastrow”, iar noi folosim COUNTA din nou pentru a obține „ultima coloană”. Acestea sunt furnizate la index ca rând_num și respectiv coloană_num.
Cu toate acestea, pentru matrice, furnizăm foaia de lucru completă, introdusă ca toate 1048576 rânduri, ceea ce permite INDEX să returneze o referință într-un spațiu 2D.
Notă: Excel 2003 acceptă doar 65535 de rânduri.
Determinarea ultimului rând
Există mai multe moduri de a determina ultimul rând (ultima poziție relativă) dintr-un set de date, în funcție de structura și conținutul datelor din foaia de lucru:
- Ultimul rând din date mixte cu spații goale
- Ultimul rând din date mixte fără spații libere
- Ultimul rând din datele text
- Ultimul rând din datele numerice
