Kako postaviti raspon vrijednosti u Excelu

Kada koristite Microsoft Excel, vrlo često korisnik ne zna unaprijed koliko će informacija biti u tablici kao rezultat. Stoga ne razumijemo u svim situacijama koji bi raspon trebao biti pokriven. Uostalom, skup ćelija je promjenjiv koncept. Da biste se riješili ovog problema, potrebno je generiranje raspona učiniti automatskim tako da se temelji isključivo na količini podataka koje je unio korisnik.

Automatsko mijenjanje raspona ćelija u Excelu

Prednost automatskih raspona u Excelu je ta š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 naziv, a zatim će se automatski ažurirati ovisno o podacima koje sadrži.

Kako napraviti automatsku promjenu raspona u Excelu

Pretpostavimo da ste investitor koji treba uložiti u neki objekt. Kao rezultat toga, želimo dobiti informacije o tome koliko možete ukupno zaraditi za cijelo vrijeme dok će novac raditi za ovaj projekt. Ipak, da bismo dobili te podatke, moramo redovito pratiti koliku nam ukupnu dobit ovaj objekt donosi. Napravite isto izvješće kao na ovoj snimci zaslona.

Na prvi pogled rješenje je očito: trebate samo zbrojiti cijeli stupac. Ako se unosi pojavljuju u njemu, iznos će se ažurirati neovisno. Ali ova metoda ima mnogo nedostataka:

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

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

1. Idite na karticu "Formule" koja se nalazi u glavnom izborniku. Pojavit će se odjeljak "Definirana imena", gdje se nalazi gumb "Dodijeli ime", na koji moramo kliknuti.
2. Zatim će se pojaviti dijaloški okvir u kojem trebate ispuniti polja kao što je prikazano na snimci zaslona. Važno je napomenuti da moramo primijeniti funkciju = ISTISAN zajedno s funkcijom PROVJERITIza stvaranje raspona s automatskim ažuriranjem. 
3. Nakon toga trebamo koristiti funkciju IZNOS, koji koristi naš dinamički raspon kao argument. 

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

OFFSET funkcija u Excelu

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

1. Početna stanica. Pomoću ovog argumenta korisnik može naznačiti koja će se ćelija u rasponu smatrati gornjom lijevom. Javit će se dolje i desno.
2. Pomak raspona za retke. Koristeći ovaj raspon, postavljamo broj ćelija za koje bi se trebao pojaviti pomak od gornje lijeve ćelije raspona. Možete koristiti ne samo pozitivne vrijednosti, već i nulu i minus. U tom slučaju, pomak se možda uopće neće dogoditi ili će se provesti u suprotnom smjeru.
3. Pomak raspona prema stupcima. Ovaj je parametar sličan prethodnom, samo vam omogućuje postavljanje stupnja horizontalnog pomaka raspona. Ovdje također možete koristiti i nulte i negativne vrijednosti.
4. Količina raspona visine. Zapravo, naslov ovog argumenta jasno nam pokazuje što on znači. Ovo je broj ćelija za koji se raspon treba povećati.
5. Vrijednost raspona u širini. Argument je sličan prethodnom, samo što se odnosi na stupce.

Ne morate navesti posljednja dva argumenta ako ne morate. U ovom slučaju, vrijednost raspona bit će samo jedna ćelija. Na primjer, ako navedete formulu =POMAK(A1;0;0), ova će se formula odnositi na istu ćeliju kao ona u prvom argumentu. Ako je vertikalni pomak postavljen na 2 jedinice, tada će se ćelija u ovom slučaju odnositi na ćeliju A3. Sada ćemo detaljno opisati što funkcija znači PROVJERITI.

COUNT funkcija u Excelu

Korištenje funkcije PROVJERITI utvrđujemo koliko smo ćelija u stupcu B ukupno ispunili. Odnosno, pomoću dvije funkcije određujemo koliko je ćelija u rasponu popunjeno i na temelju dobivenih 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 pokazuje gdje počinje naš dinamički raspon. U našem slučaju to je ćelija B2. Ostali parametri imaju nulte koordinate. Ovo sugerira da nam ne trebaju pomaci u odnosu na gornju lijevu ćeliju. Sve što ispunjavamo je okomita veličina raspona, kao što smo koristili funkciju PROVJERITI, koji određuje broj ćelija koje sadrže neke podatke. Četvrti parametar koji smo ispunili je jedinica. Tako pokazujemo da bi ukupna širina raspona trebala biti jedan stupac.

Dakle, koristeći funkciju PROVJERITI korisnik može što učinkovitije koristiti memoriju učitavanjem samo onih ćelija koje sadrže neke vrijednosti. Sukladno tome, neće biti dodatnih pogrešaka u radu povezanih s lošim performansama računala na kojem će proračunska tablica raditi.

Sukladno tome, kako biste odredili veličinu raspona ovisno o broju stupaca, morate izvršiti sličan niz radnji, samo u ovom slučaju morate odrediti jedinicu u trećem parametru, a formulu u četvrtom PROVJERITI.

Vidimo da uz pomoć Excel formula ne možete samo automatizirati matematičke izračune. Ovo je samo kap u moru, ali zapravo vam omogućuju da automatizirate gotovo svaku operaciju koja vam padne na pamet.

Dinamički grafikoni u Excelu

Tako smo u zadnjem koraku uspjeli stvoriti dinamički raspon čija veličina u potpunosti ovisi o tome koliko ispunjenih ćelija sadrži. Sada, na temelju ovih podataka, možete izraditi dinamičke grafikone koji će se automatski mijenjati čim korisnik napravi bilo kakvu promjenu ili doda dodatni stupac ili redak. Redoslijed radnji u ovom slučaju je sljedeći:

1. Odaberemo svoj raspon, nakon čega umetnemo grafikon tipa "Histogram s grupiranjem". Ovu stavku možete pronaći u odjeljku "Umetni" u odjeljku "Grafikoni - Histogram".
2. Kliknemo lijevom tipkom miša na slučajni stupac histograma, nakon čega će se u funkcijskom retku prikazati funkcija =SERIES(). Na snimci zaslona možete vidjeti detaljnu formulu. 
3. Nakon toga potrebno je unijeti neke izmjene u formulu. Morate zamijeniti raspon nakon "Sheet1!" na naziv raspona. To će rezultirati sljedećom funkcijom: =REDAK(Tablica1!$B$1;;Tablica1!prihod;1)
4. Sada preostaje dodati novi zapis u izvješće kako bismo provjerili ažurira li se grafikon automatski ili ne.

Pogledajmo naš dijagram.

Rezimirajmo kako smo to učinili. U prethodnom koraku izradili smo dinamički raspon čija veličina ovisi o tome koliko elemenata sadrži. Da bismo to učinili, koristili smo kombinaciju funkcija PROVJERITI и RASPOLAGANJE. Nazvali smo ovaj raspon, a zatim smo upotrijebili referencu na ovo ime kao raspon našeg histograma. Nije toliko važno koji određeni raspon odabrati kao izvor podataka u prvoj fazi. Glavna stvar je da ga kasnije zamijenite nazivom raspona. Na taj način možete uštedjeti puno RAM-a.

Imenovani rasponi i njihova upotreba

Razgovarajmo sada detaljnije o tome kako ispravno stvoriti imenovane raspone i koristiti ih za obavljanje zadataka koji su postavljeni za korisnika programa Excel.

Prema zadanim postavkama koristimo uobičajene adrese ćelija kako bismo uštedjeli vrijeme. Ovo je korisno kada trebate napisati raspon jednom ili više puta. Ako se mora koristiti cijelo vrijeme ili mora biti prilagodljiv, tada se trebaju koristiti imenovani rasponi. Oni znatno olakšavaju kreiranje formula, a korisniku neće biti teško analizirati složene formule koje uključuju velik broj funkcija. Opišimo neke od koraka koji su uključeni u stvaranje dinamičkih raspona.

Sve počinje imenovanjem ćelije. Da biste to učinili, jednostavno ga odaberite, a zatim upišite naziv koji nam je potreban u polje njegovog naziva. Važno je da se lako pamti. Prilikom imenovanja treba uzeti u obzir neka ograničenja:

1. Maksimalna duljina je 255 znakova. Ovo je sasvim dovoljno da dodijelite ime koje vam srce želi.
2. Naziv ne smije sadržavati razmake. Dakle, ako sadrži više riječi, moguće ih je odvojiti podvlakom.

Ako kasnije na drugim listovima ove datoteke trebamo prikazati ovu vrijednost ili je primijeniti za izvođenje daljnjih izračuna, tada nema potrebe za prebacivanjem na prvi list. Možete samo zapisati naziv ove ćelije raspona.

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

Osim toga, moguće je izraditi imenovani raspon pomoću kartice Formule pomoću alata Postavi naziv. Nakon što ga odaberemo, pojavit će se prozor u kojem trebamo odabrati naziv za naš raspon, kao i ručno odrediti područje na koje će se proširiti. Također možete odrediti gdje će ovaj raspon raditi: unutar jednog lista ili u cijeloj knjizi.

Ako je raspon imena već stvoren, tada za njegovu upotrebu postoji posebna usluga koja se naziva upravitelj imena. Omogućuje ne samo uređivanje ili dodavanje novih imena, već i njihovo brisanje ako više nisu potrebna.

Treba imati na umu da pri korištenju imenovanih raspona u formulama, nakon brisanja, formule neće automatski biti prebrisane točnim vrijednostima. Stoga se mogu pojaviti pogreške. Stoga, prije brisanja imenovanog raspona, morate biti sigurni da se ne koristi ni u jednoj formuli.

Drugi način za stvaranje imenovanog raspona je da ga dobijete iz tablice. Da biste to učinili, postoji poseban alat pod nazivom "Stvori iz odabira". Kako razumijemo, da biste ga koristili, prvo morate odabrati raspon koji ćemo urediti, a zatim postaviti mjesto na kojem imamo naslove. Kao rezultat toga, na temelju tih podataka Excel će automatski obraditi sve podatke, a naslovi će se automatski dodijeliti.

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

Stoga smo otkrili kako stvoriti dinamičke imenovane raspone i kako vam oni omogućuju automatizaciju rada s velikim količinama podataka. Kao što vidite, dovoljno je koristiti nekoliko funkcija i programskih alata ugrađenih u funkcionalnost. Uopće nema ništa komplicirano, iako se početniku na prvi pogled može činiti tako.