Kako postaviti raspon vrijednosti u Excelu

Kada koristite Microsoft Excel, vrlo često korisnik ne zna unaprijed koliko će informacija biti u tabeli kao rezultat. Stoga ne razumijemo u svim situacijama koji opseg treba pokriti. Na kraju krajeva, skup ćelija je promjenjiv koncept. Da biste se riješili ovog problema, potrebno je generiranje raspona učiniti automatskim tako da se bazira isključivo na količini podataka koje je korisnik unio.

Automatsko mijenjanje raspona ćelija u Excelu

Prednost automatskih raspona u Excelu je u tome što znatno olakšavaju korištenje formula. Osim toga, oni omogućuju značajno pojednostavljenje analize složenih podataka koji sadrže veliki broj formula, koje uključuju mnoge funkcije. Ovom rasponu možete dati ime, a zatim će se automatski ažurirati ovisno o tome koje podatke sadrži.

Kako napraviti automatsku promjenu raspona u Excelu

Pretpostavimo da ste investitor koji treba da investira u neki objekat. Kao rezultat toga, želimo da dobijemo informaciju o tome koliko ukupno možete zaraditi za sve vreme dok će novac raditi za ovaj projekat. Ipak, da bismo dobili ove informacije, potrebno je redovno pratiti koliku nam ukupnu dobit donosi ovaj objekat. Napravite isti izvještaj kao na ovom snimku ekrana.

Na prvi pogled, rješenje je očigledno: samo trebate zbrojiti cijelu kolonu. Ako se u njemu pojave unosi, iznos će se ažurirati nezavisno. Ali ova metoda ima mnoge nedostatke:

1. Ako se problem riješi na ovaj način, ćelije uključene u kolonu B neće biti moguće koristiti u druge svrhe.
2. Takva tablica će potrošiti puno RAM-a, što će onemogućiti korištenje dokumenta na slabim računarima.

Stoga je potrebno ovaj problem riješiti kroz dinamička imena. Da biste ih stvorili, morate izvršiti sljedeći niz radnji:

1. Idite na karticu "Formule" koja se nalazi u glavnom meniju. Pojaviće se odeljak „Definisana imena“, gde se nalazi dugme „Dodeli ime“ na koje treba da kliknemo.
2. Zatim će se pojaviti dijaloški okvir u kojem trebate popuniti polja kao što je prikazano na snimku ekrana. Važno je napomenuti da moramo primijeniti funkciju = IZMJEŠTENJE zajedno sa funkcijom CHECKda kreirate raspon za automatsko ažuriranje. 
3. Nakon toga trebamo koristiti funkciju SUMA, koji koristi naš dinamički raspon kao argument. 

Nakon dovršetka ovih koraka, možemo vidjeti kako se pokrivenost ćelija koje pripadaju rasponu "prihoda" ažurira dok tamo dodajemo nove elemente.

Funkcija OFFSET u Excelu

Pogledajmo funkcije koje smo ranije snimili u polju “range”. Korištenje funkcije ODSTRANJEVANJE možemo odrediti količinu raspona s obzirom na to koliko je ćelija u koloni B popunjeno. Argumenti funkcije su sljedeći:

1. Start ćelija. Ovim argumentom korisnik može naznačiti koja će se ćelija u rasponu smatrati gornjom lijevom stranom. Izvještavaće dolje i desno.
2. Opseg pomjeren po redovima. Koristeći ovaj raspon, postavljamo broj ćelija za koje treba doći do pomaka od gornje lijeve ćelije raspona. Možete koristiti ne samo pozitivne vrijednosti, već i nulu i minus. U ovom slučaju, pomicanje se možda uopće neće dogoditi ili će se izvršiti u suprotnom smjeru.
3. Opseg pomjeren po stupcima. Ovaj parametar je sličan prethodnom, samo što vam omogućava da podesite stepen horizontalnog pomeranja opsega. Ovdje također možete koristiti i nulte i negativne vrijednosti.
4. Količina raspona po visini. Zapravo, naslov ovog argumenta nam jasno daje do znanja šta on znači. Ovo je broj ćelija za koji bi se raspon trebao povećati.
5. Vrijednost raspona u širini. Argument je sličan prethodnom, samo što se tiče kolona.

Ne morate specificirati posljednja dva argumenta ako ne morate. U ovom slučaju, vrijednost raspona će biti samo jedna ćelija. Na primjer, ako navedete formulu =OFFSET(A1;0;0), ova formula će se odnositi na istu ćeliju kao i ona u prvom argumentu. Ako je vertikalni pomak postavljen na 2 jedinice, tada će se u ovom slučaju ćelija odnositi na ćeliju A3. Hajde da sada detaljno opišemo šta funkcija znači CHECK.

COUNT funkcija u Excelu

Korištenje funkcije CHECK određujemo koliko ćelija u koloni B smo ukupno popunili. Odnosno, pomoću dvije funkcije određujemo koliko ćelija u rasponu je popunjeno i na osnovu primljenih informacija određujemo veličinu raspona. Stoga će konačna formula biti sljedeća: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Pogledajmo kako pravilno razumjeti princip ove formule. Prvi argument ukazuje na to gdje počinje naš dinamički raspon. U našem slučaju, ovo je ćelija B2. Dalji parametri imaju nulte koordinate. Ovo sugerira da nam ne trebaju pomaci u odnosu na gornju lijevu ćeliju. Sve što popunjavamo je vertikalna veličina raspona, kao što smo koristili funkciju CHECK, koji određuje broj ćelija koje sadrže neke podatke. Četvrti parametar koji smo popunili je jedinica. Tako pokazujemo da ukupna širina opsega treba da bude jedna kolona.

Dakle, korištenjem funkcije CHECK korisnik može koristiti memoriju što je moguće efikasnije učitavanjem samo onih ćelija koje sadrže neke vrijednosti. Shodno tome, neće biti dodatnih grešaka u radu povezanih sa lošim performansama računara na kojem će tabela raditi.

U skladu s tim, da biste odredili veličinu raspona ovisno o broju stupaca, morate izvršiti sličan slijed radnji, samo u ovom slučaju morate navesti jedinicu u trećem parametru, a formulu u četvrtom CHECK.

Vidimo da uz pomoć Excel formula ne možete samo automatizirati matematičke proračune. Ovo je samo kap u moru, ali u stvari vam omogućavaju automatizaciju gotovo svake operacije koja vam padne na pamet.

Dinamički grafikoni u Excelu

Dakle, u posljednjem koraku uspjeli smo stvoriti dinamički raspon čija veličina u potpunosti ovisi o tome koliko popunjenih ćelija sadrži. Sada, na osnovu ovih podataka, možete kreirati dinamičke grafikone koji će se automatski promijeniti čim korisnik izvrši bilo kakvu promjenu ili doda dodatni stupac ili red. Redoslijed radnji u ovom slučaju je sljedeći:

1. Odabiremo naš raspon, nakon čega ubacujemo grafikon tipa "Histogram s grupiranjem". Ovu stavku možete pronaći u odeljku „Insert“ u odeljku „Grafikoni – Histogram“.
2. Kliknemo levim mišem na nasumični stupac histograma, nakon čega će funkcija =SERIES() biti prikazana u funkcijskoj liniji. Na snimku ekrana možete vidjeti detaljnu formulu. 
3. Nakon toga, potrebno je napraviti neke promjene u formuli. Morate zamijeniti raspon nakon “Sheet1!” na naziv opsega. To će rezultirati sljedećom funkcijom: =ROW(Tab1!$B$1;;List1!prihod;1)
4. Sada ostaje dodati novi zapis u izvještaj kako biste provjerili da li se grafikon automatski ažurira ili ne.

Pogledajmo naš dijagram.

Hajde da sumiramo kako smo to uradili. U prethodnom koraku kreirali smo dinamički raspon čija veličina ovisi o tome koliko elemenata sadrži. Da bismo to učinili, koristili smo kombinaciju funkcija CHECK и ODSTRANJEVANJE. Napravili smo ovaj raspon imenovanim, a zatim smo koristili referencu na ovo ime kao raspon našeg histograma. Koji određeni raspon odabrati kao izvor podataka u prvoj fazi nije toliko važno. Glavna stvar je da ga kasnije zamijenite imenom raspona. Na ovaj način možete uštedjeti mnogo RAM-a.

Imenovani opsezi i njihova upotreba

Razgovarajmo sada detaljnije o tome kako pravilno kreirati imenovane opsege i koristiti ih za izvršavanje zadataka koji su postavljeni za korisnika Excel-a.

Podrazumevano koristimo obične adrese ćelija kako bismo uštedjeli vrijeme. Ovo je korisno kada trebate napisati raspon jednom ili više puta. Ako ga treba koristiti cijelo vrijeme ili mora biti prilagodljiv, tada bi se trebali koristiti imenovani rasponi. Oni znatno olakšavaju kreiranje formula, a korisniku neće biti teško analizirati složene formule koje uključuju veliki broj funkcija. Hajde da opišemo neke od koraka uključenih u kreiranje dinamičkog raspona.

Sve počinje imenovanjem ćelije. Da biste to učinili, jednostavno ga odaberite, a zatim upišite ime koje nam je potrebno u polje njegovog imena. Važno je da se lako pamti. Postoje neka ograničenja koja treba uzeti u obzir prilikom imenovanja:

1. Maksimalna dužina je 255 znakova. Ovo je sasvim dovoljno da dodijelite ime koje vam srce želi.
2. Ime ne smije sadržavati razmake. Stoga, ako sadrži nekoliko riječi, onda ih je moguće razdvojiti pomoću donje crte.

Ako kasnije na drugim listovima ove datoteke trebamo prikazati ovu vrijednost ili je primijeniti za obavljanje daljnjih proračuna, onda nema potrebe za prebacivanjem na prvi list. Možete samo zapisati ime ćelije ovog opsega.

Sljedeći korak je kreiranje imenovanog raspona. Procedura je u osnovi ista. Prvo morate odabrati raspon, a zatim odrediti njegovo ime. Nakon toga, ovo ime se može koristiti u svim drugim operacijama podataka u Excelu. Na primjer, imenovani rasponi se često koriste za definiranje zbira vrijednosti.

Osim toga, moguće je kreirati imenovani raspon pomoću kartice Formule pomoću alata Set Name. Nakon što ga odaberemo, pojaviće se prozor u kojem treba da izaberemo ime za naš opseg, kao i da navedemo područje na koje će se širiti ručno. Također možete odrediti gdje će ovaj raspon raditi: unutar jednog lista ili u cijeloj knjizi.

Ako je raspon imena već kreiran, onda da biste ga koristili postoji posebna usluga koja se zove upravitelj imena. Omogućava ne samo uređivanje ili dodavanje novih imena, već i njihovo brisanje ako više nisu potrebni.

Treba imati na umu da kada koristite imenovane opsege u formulama, onda nakon brisanja formule neće biti automatski prepisane ispravnim vrijednostima. Stoga može doći do grešaka. Stoga, prije nego što izbrišete imenovani raspon, morate biti sigurni da se ne koristi ni u jednoj od formula.

Drugi način da kreirate imenovani opseg je da ga dobijete iz tabele. Da biste to učinili, postoji poseban alat pod nazivom "Kreiraj iz odabira". Kako razumijemo, da biste ga koristili, prvo morate odabrati raspon koji ćemo uređivati, a zatim postaviti mjesto na kojem imamo naslove. Kao rezultat, na osnovu ovih podataka, Excel će automatski obraditi sve podatke, a naslovi će biti automatski dodijeljeni.

Ako naslov sadrži nekoliko riječi, Excel će ih automatski odvojiti donjom crtom.

Tako smo shvatili kako kreirati dinamičke imenovane raspone i kako vam omogućavaju automatizaciju rada s velikim količinama podataka. Kao što vidite, dovoljno je koristiti nekoliko funkcija i programskih alata ugrađenih u funkcionalnost. Uopšte nema ništa komplikovano, iako početniku na prvi pogled može izgledati tako.