Vaši Excel podaci često se menjaju, stoga je vrlo korisno kreirati dinamički definisan opseg koji se automatski prilagođava veličini vaših podataka. Pogledajmo kako to možete učiniti.
Korišćenjem dinamički definisanog opsega, više nećete morati ručno da podešavate opsege u formulama, grafikonima i izvedenim tabelama kada dođe do promena u podacima. Sve će se odvijati automatski.
Za kreiranje dinamičkih opsega koriste se dve formule: OFFSET i INDEX. Ovaj tekst će se fokusirati na primenu funkcije INDEX, budući da je ona efikasnija. OFFSET je promenljiva funkcija i može usporiti rad sa velikim tabelama.
Stvaranje dinamički definisanog opsega u Excelu
Za naš prvi primer, imamo listu podataka sa jednom kolonom, prikazanu ispod.
Naš cilj je da ovaj opseg bude dinamičan, tako da ako se dodaju ili uklone neke zemlje, opseg se automatski ažurira.
U ovom primeru želimo da izbegnemo ćeliju zaglavlja. Želimo da koristimo opseg $A$2:$A$6, ali da on bude dinamičan. Da bismo to postigli, idemo na Formule > Definiši ime.
U polje „Naziv“ upišite „zemlje“, a u polje „Odnosi se na“ unesite formulu ispod.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Ponekad je brže i jednostavnije uneti ovu formulu u ćeliju tabele, a zatim je kopirati u okvir Novo ime.
Kako ovo funkcioniše?
Prvi deo formule određuje početnu ćeliju opsega (u ovom slučaju A2), a zatim sledi operator opsega (:).
=$A$2:
Korišćenje operatora opsega primorava funkciju INDEX da vrati opseg umesto vrednosti ćelije. Funkcija INDEX se zatim koristi zajedno sa funkcijom COUNTA. COUNTA broji broj ćelija koje nisu prazne u koloni A (šest u našem primeru).
INDEX($A:$A,COUNTA($A:$A))
Ova formula zahteva od funkcije INDEX da vrati opseg do poslednje ćelije koja nije prazna u koloni A ($A$6).
Konačni rezultat je $A$2:$A$6, i zahvaljujući funkciji COUNTA, opseg je dinamičan, jer će uvek pronaći poslednji red. Sada možete koristiti definisano ime „zemlje“ unutar pravila za validaciju podataka, formule, grafikone, ili bilo gde gde je potrebno referencirati nazive svih zemalja.
Kreiranje dvosmernog dinamički definisanog opsega
U prvom primeru opseg je bio dinamičan samo po visini. Međutim, uz malu modifikaciju i još jednu funkciju COUNTA, moguće je kreirati opseg koji je dinamičan i po visini i po širini.
U ovom primeru koristićemo podatke prikazane u nastavku.
Ovoga puta kreiraćemo dinamički definisan opseg koji uključuje i zaglavlja. Idemo na Formule > Definiši ime.
U polje „Naziv“ upišite „prodaja“, a u polje „Odnosi se na“ unesite formulu ispod.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Ova formula koristi $A$1 kao početnu ćeliju. Funkcija INDEX zatim koristi opseg celog radnog lista ($1:$1048576) kako bi pretražila i vratila podatke iz njega.
Jedna od funkcija COUNTA koristi se za brojanje nepranzih redova, a druga za nepranzne kolone, čineći opseg dinamičnim u oba smera. Iako je ova formula počela od A1, mogli ste da navedete bilo koju drugu početnu ćeliju.
Sada možete koristiti ovo definisano ime („prodaja“) u formulama ili kao niz podataka za grafikone, kako bi i oni bili dinamični.