Trikovi u Excelu, 12 deo: Imenovani rasponi ili Kako komplikovane formule načiniti kraćim i jasnijim

Trikovi u Excelu, 12 deo: Imenovani rasponi ili Kako komplikovane formule načiniti kraćim i jasnijim

Trikovi u Excelu

 

 IMENOVANI RASPONI

U vrijeme kada sam bio mlad i nadobudan (naučio ponešto o Excelu), mislio sam da znam mnogo. Sve do jednog dana…

Kolega mi je poslao fajl pripremljen u Excelu sa podacima o artiklima, naručenim količinama i jediničnim cijenama, sa ciljem da ja taj fajl malo vizuelno dotjeram i provjerim tačnost formula.

„Formule nisu komplikovane, lako ćeš se snaći“ – bilo je posljednje što sam čuo prije nego što je kolega otišao na godišnji odmor.

Hajd’, lijepo, pomislih i bacih se na posao. Uistinu, tabela nije bila komplikovana, ali je kontrola formula počela da mi zadaje glavobolje. Da se odmah razumjemo: nije bilo mnogo kolona ni komplikovanih proračuna, ali je bilo dosta redova pa sam se u jednom momentu uhvatio kako se pitam da li ćelija $A5655 na jednom radnom listu odgovara zbiru raspona ćelija G$696:O$891 sa drugog i još četiri slična raspona sa trećeg radnog lista, podjeljenog sa nekim čudnim koeficijentom.

 

Slika 1 Prvobitni izgled tabele

Slika 1 Prvobitni izgled tabele

 

Pošto je rok za završetak kontrole bio „jučer“, nazovem kolegu (koji je već bio na plaži), unaprijed se radujući što ću mu bar malo pokvariti odmor.

– Ahaaaaa, znači, to te „žulja“ – vedro je odvratio. – Nikakav problem, daj mi par minuta pa ću ti poslati verziju koja te neće zbunjivati.

Ostadoh u čudu, pitajući se kako to misli pojednostaviti za nekoliko minuta. Već sam sebe zamišljao kako čitam uputstvo od 300 stranica u kome „sve lijepo piše“ i, naravno, ne završavam posao na vrijeme. Ma, kakvo vrijeme, ne završavam ga nikako!

Uistinu, za nekoliko minuta stigao mi je e-mail sa fajlom koji je izgledao identično onome kojeg sam već imao. Samo izgledao….

 

2

Slika 2 Izgled tabele nakon “izmjena”

Umjesto slova, brojki i dolarskih znakova u formulama je pisalo: „cijena*količina“, „SUM(količina), AVERAGE(cijena)“, „cijena u eurima“, „ukupna cijena u eurima“ itd. Osjećao sam se poput čovjeka koji je mislio da je slijep a onda je odjednom skinuo sunčane naočari.

 

Slika 3. Prikaz formula nakon „izmjena“

Slika 3. Prikaz formula nakon „izmjena“

 

Nisam mogao izdržati pa sam ga uvečer opet nazvao (nisam se usuđivao da mu još jednom pokvarim dan na plaži) kako bih saznao kako je to napravio, a naročito kako je to uspio za tako kratko vrijeme.

– Pa, vidi, u pitanju su tzv. imenovani rasponi. Odabereš raspon ćelija i daš mu neko ime. Nakon toga, u formulama koristiš ime tog raspona umjesto referenciranja na ćelije. Tako su formule mnogo kraće i jasnije. Naravno, onda ih i neko ko nije upoznat sa fajlom – poput tebe, može lako razumjeti.

– A kako praviš imenovane raspone?

– Odabereš raspon ćelija i u gornjem lijevom uglu (gdje se vidi adresa aktivne ćelije) ukucaš ime raspona (u ovom primjeru „Količina“) i pritisneš tipku ENTER. Pazi da ime raspona ne počinje donjom crtom ili brojem i da ne sadržava razmake.

 

Slika 4 Pravljenje imenovanog raspona pomoću Address Cell

Slika 4 Pravljenje imenovanog raspona pomoću Address Cell

– Onda, vjerovatno, u formuli samo ukucam ime raspona?

– Upravo tako: Excel čak prepoznaje prva slova imena raspona i automatski ti nudi raspone koji odgovaraju unesenim slovima. A spisak kreiranih imenovanih raspona možeš vidjeti ili ubaciti u formulu klikom na adresu aktivne ćelije i odabirom željenog raspona.

 

Slika 5 Excel "prepoznaje" imenovani raspon

Slika 5 Excel “prepoznaje” imenovani raspon

– Mogu li se rasponi ćelija na koje se odnose imenovani rasponi promjeniti? Mislim na situacije kada npr. dodam nove redove koje želim uključiti u već imenovani raspon?

– Da, ali moraš ići na meni (tab, ribbon) „Formulas“ pa odabrati „Name Manager“. Odabereš raspon, klikneš na „Edit“, selektuješ željene ćelije, potvrdiš pritiskom na kvačicu i klikneš na dugme „Close“.

 

Slika 6 Izmjena postojećeg imenovanog raspona

Slika 6 Izmjena postojećeg imenovanog raspona

Brisanje je također lako: odabereš imenovani raspon i klikneš na „Delete“. Naravno, brisanje raspona ne znači i brisanje podataka na koje se imenovani raspon odnosi, već samo brisanje imena. Kod brisanja zapamti da će sve formule koje koriste imenovani raspon koji brišeš javiti grešku.

– Lako je kreirati imenovani raspon za jednu grupu ćelija. Pretpostavimo da imam tabelu sa 20 kolona, moram li 20 puta ponoviti postupak za kreiranje imenovanih raspona?

– Ne, postoji mnogo brži način: selektuj kolone (uključujući i imena kolona) i na meniju „Formulas“ odaberi „Create from selection“, uključi opciju „Top Rows“ i dobićeš 20 imenovanih raspona koji se odnose na 20 kolona. Svaki raspon će dobiti ime po imenu kolone.

 

Slika 7 Pravljenje imenovanih raspona na osnovu odabranih podataka

Slika 7 Pravljenje imenovanih raspona na osnovu odabranih podataka

– Želim promjeniti kurs eura? Vidim imenovani raspon u formulama i formule ispravno funkcionišu, ali ga ne vidim u spisku imenovanih raspona u gornjem lijevom uglu.

 

8

Slika 8 Imenovani raspon “EUR” se vidi u formulama, ali se ne vidi na spisku imenovanih raspona

– Pa izgledalo bi neprofesionalno da imamo usamljenu ćeliju na koju će se formule referencirati. Rješenje je da se napravi imenovani raspon koji neće biti nijedna ćelija već će biti pohranjen u memoriji fajla. U ovom slučaju to je kurs eura (1,95583 KM). Kada se u formuli bude koristio raspon pod imenom „EUR“, koristiće se vrijednost koju si unio (1,95583).

Na meniju „Formulas“ odaberi „Define Name“. Dodjeli ime (EUR) a u polju „Refers to“ unesi kurs eura. Jako bitna stvar je da li se imenovani raspon odnosi samo na određeni radni list ili na cijeli fajl.

 

Slika 9 Kreiranje imenovanog raspona u memoriji fajla

Slika 9 Kreiranje imenovanog raspona u memoriji fajla

Napomene:

– Kod brisanja imenovanih raspona paziti na formule koje se referenciraju na imenovani raspon koji želiš obrisati jer formule više neće funkcionisati

– Kod kreiranja imenovanih raspona je bolje koristiti meni „Formulas“ i definisati odmah je li imenovani raspon na nivou radnog lista ili fajla. Ovo je jako osjetljivo pitanje jer mogu postojati dva imenovana raspona s istim imenom na dva radna lista. Kod komplikovanijih formula ovo lako može da zbuni. Preporučujem da se imenovani rasponi kreiraju na nivou fajla, ili bar da imaju jedinstvene nazive.

Najava: Pošto imamo sve sastojke (Data Validation, VLOOKUP i Imenovane raspone), „uz jednu žlicu“ INDIRECT funkcije, u jednom od narednih tekstova ćemo pokazati kako napraviti „pametne padajuće liste“, odnosno, kako napraviti da kada se u jednoj padajućoj listi odabere jedna opcija da se sadržaj druge padajuće liste automatski mjenja.

U narednom broju: matrične formule (ili formule nizova, array formulas) – naravno, višećelijske i jednoćelijske

Autor

Almir A.  Budalica

Microsoft Certified Trainer

http://www.excelbezbola.com

+387-61-509-288 (Viber)


4 Komentara

  1. igor :
    21.08.2013 u 14:32|Permalink

    Pozdrav Almire,
    jedno pitanje. iako sam napravio imenovane raspone, kad koristim formulu i samo zaokruzim celije, one su napisane po standardnim nazivima. da bi ovi nazivi funkcionisali moram u formuli koristiti nova imena ( cijena, kolicina).da li to treba tako biti?
    hvala i pozdrav

    • Almir :
      21.08.2013 u 14:58|Permalink

      Pozdrav Igore,
      Bojim se da nisam siguran šta je pitanje. Šta znači zaokruživanje ćelija i kakvi su standardni nazivi? Ukoliko misliš na to da ti pri odabiru ćelija Excel ne pokazuje ime imenovanog raspona, formula će jednako funkcionisati i na taj način.

      • igor :
        21.08.2013 u 18:14|Permalink

        da mozda sam bio malo konfuzan :) dakle napravim imenovani raspon i savrseno radi kada ja rucno kucam formulu u nekoj celiji ali ako probam =sum() a unutra obuhvatim celije rucno, i dalje stoji npr A1 ili B1. e sad ne znam da li je to normalno :)

        • Almir :
          21.08.2013 u 18:25|Permalink

          Vjerovatno nisi obuhvatio cijeli raspon, ili si obuhvatio i dodatne ćelije. Može biti i da si obuhvatio i ime kolone. Probaj da odabereš raspon bez imena kolone, trebalo bi da ti se pojavi =SUM(ime_raspona).

Napiši komentar

Newsletter pretplata

Autori Tagovi
  • Aleksandar Duković (4)
  • Aleksandar Nedeljković (1)
  • Aleksandar Nešić (1)
  • Aleksandar Pejčić (1)
  • Aleksandar Predić (2)
  • Aleksandar Teofilović (3)
  • Aleksandra Dutina (2)
  • Aleksandra Janković (1)
  • Aleksandra Paunić (1)
  • Aleksandra Đorđević (2)
  • Almir A. Budalica (19)
  • Ana Petonjić (1)
  • Ana Petrović (1)
  • Andreas Graf (1)
  • Andrej Beslać (1)
  • Andrej Kuku (1)
  • Andrija Pavlović (2)
  • Anja Atanasijević (1)
  • Anja Dadasović (1)
  • Biljana Dimitrijević (1)
  • Biljana Dukovic (1)
  • Biljana Masić (6)
  • Bjarte Bogsnes (1)
  • Bogdan Kravić (1)
  • Bojan Grahovac (1)
  • Bojan Pavlović (1)
  • Bojan Radoš (2)
  • Bojan Radun (1)
  • Bojan Šćepanović (300)
  • Bojana Pejčić (26)
  • Bojana Radović (1)
  • Bojana Vesić Anić (1)
  • Bojana Vesić Antić (1)
  • Branislav Lončar (3)
  • Branislav Vujović (1)
  • Branislav Zobenica (1)
  • Cmilja Tucaković (1)
  • Dalibor Pajić (1)
  • Daniela Laketic (31)
  • Danijela Medić (1)
  • Danijela Popović (1)
  • Danijela Resimić (1)
  • Danijela Tuco (1)
  • Darko Bjelić (2)
  • Darko Samardžija (1)
  • Darko Vlajkovic (27)
  • David Momčilović (1)
  • Davor Sakač (1)
  • Dejan Marković (1)
  • Dejan Đorđević (1)
  • Dejana Manić Kavgić (2)
  • Denis Trbović (3)
  • Desa Ćuk (1)
  • Dimitrije Stojanović (1)
  • Vojislav Marjanović (3)
  • Dragan Radosavljević (2)
  • Dragan Ranisavljević (1)
  • Dragan Vještica (3)
  • Dragana Gondžo (1)
  • Dragana Guzina (1)
  • Dragana Lukić (1)
  • Dragana Nerandžić (1)
  • Dragana Ovčarov (1)
  • Dragana Stojanović (2)
  • Dragana Đorđević (1)
  • Draško Marković (1)
  • Dražan Planinić (1)
  • Dušan Krejaković (1)
  • Excel genije! (151)
  • Fabrizio Renzi (1)
  • Goran Petrović (1)
  • Goran Popović (4)
  • Goran Slijepčević (1)
  • Goran Vasić (1)
  • Goran Vujasinović (1)
  • Gorana Golubovic Vuksanovic (1)
  • Goranka Radojčić (2)
  • Gordana Jankov (1)
  • Gostujuci autor (514)
  • Ilija Vujović (1)
  • Irina Zdravković (2)
  • Istok Pavlović (1)
  • Ivan Anđelković (1)
  • Ivan Bešker (1)
  • Ivan Ćirković (1)
  • Ivan Dačković (1)
  • Ivan Kostadinović (1)
  • Ivan Veselinović (3)
  • Ivan Đurić (1)
  • Ivana Dagović (1)
  • Ivana Jevtić (1)
  • Ivana Milić (1)
  • Ivana Popović (1)
  • Ivana Savović (1)
  • Ivana Višnjić (3)
  • Ivana Vlahović (1)
  • Jasenka Stekić (1)
  • Jelena Jovanović (1)
  • Jelena Lečić Mirčetić (1)
  • Jelena Radovanac (1)
  • Jelena Ristić (1)
  • Jelena Stojmenović (1)
  • Jelena Vučković (1)
  • Jovan Gligorijević (1)
  • Jovan Krstić (1)
  • Jovana Kondić (2)
  • Jovana Stanojčić (1)
  • Jovo Stokić (2)
  • Katarina Ćevriz (2)
  • Kristina Bojović (1)
  • Ksenija Karić (1)
  • Lazar Džamić (1)
  • Leo Pandžić (2)
  • Lidija Latinović (1)
  • Ljubomir Skupek (1)
  • Lucija Vesić (1)
  • Maja Mrkalj (1)
  • Maja Nikčević Rmandić (1)
  • Maja Petrović (1)
  • Maja Šobot (1)
  • Maja Verlašević (1)
  • Maja Zikic (1)
  • Mari Glavonjić (2)
  • Marija Đurović (1)
  • Marija Kostić (1)
  • Marija Rokvić (1)
  • Marija Švigir (32)
  • Marija Tatarević (3)
  • Marijana Krička (1)
  • Marina Mitić Jekić (1)
  • Mariora Andraš Tomić (1)
  • Marko Agatonović (2)
  • Marko Bekić (1)
  • Marko Devrnja (1)
  • Matjaž Fajfar (2)
  • Milan Listeš (2)
  • Milan Maglov (1)
  • Milan Manić (1)
  • Milan Marković (1)
  • Milan Milenković (1)
  • Milan Radivojevic (1)
  • Milan Simić (1)
  • Milan Zaletel (1)
  • Milan Đorđević (1)
  • Mile Mitrović (47)
  • Milena Janjić (3)
  • Milena Nikolov (1)
  • Milena Radosavljević Đorić (1)
  • Milena Rajić (1)
  • Milenko Dželetović (1)
  • Milica Altgelt (1)
  • Milica Ivic (1)
  • Milica Đorđević (1)
  • Miloš Cvetković (7)
  • Miloš Jauković (1)
  • Miloš Jeličić (2)
  • Miloš Nedeljković (1)
  • Miloš Pucarević (1)
  • Miloš Puzić (1)
  • Miloš Simić (1)
  • Miloš Vasić (1)
  • Miloš Zeković (1)
  • Miona Živkov-Ivanišević (4)
  • Mirjana Pašalić (2)
  • Jelena Jovanović (5)
  • Mladen Ranković (1)
  • Natalija Jegdić (1)
  • Natalija Mihajlović (1)
  • Natalija Pešić (2)
  • Natalija Popovic (1)
  • Nataša Kuzmanovski (1)
  • Nataša Stamenković (2)
  • Nataša Žikić Buha (1)
  • Nebojša Vlatković (1)
  • Neda Čičarević Tepić (1)
  • Neda Jovanović (1)
  • Nemanja Knežević (1)
  • Nemanja Nikolić (3)
  • Nenad Dlačić (1)
  • Nenad Kovačević (1)
  • Nenad Radunović (1)
  • Nenad Trajkovski (1)
  • Nevena Radovanović (1)
  • Nevena Vračar (1)
  • Nikola Avram (1)
  • Nikola Cvijanović (1)
  • Nikola Petreski (1)
  • Nikola Stokić (2)
  • Nikola Turkan (1)
  • Nikša Vušurović (1)
  • Olga Mirković (1)
  • Olivera Dostanić (1)
  • Olivera Krneta (1)
  • Olivera Nikodijević (1)
  • Petar Ćurčić (1)
  • Petar Popović (1)
  • Petar Živković (3)
  • Predrag Krasojevic (7)
  • Predrag Kudra (1)
  • Predrag Micić (1)
  • Predrag Petrović (5)
  • Rade Hajder (1)
  • Rade Radanović (2)
  • Rade Stojisavljević (1)
  • Radomir Petronijević (1)
  • Rajko Vasojević (1)
  • Roland Seeliger (1)
  • Sandra Rapo (1)
  • Sanja Jevđenijević (1)
  • Sanja Mrđanov (2)
  • Saša Stamenković (1)
  • Silvia Tomić (1)
  • Slavica Vučetić (1)
  • Slavica Vujičić (1)
  • Slaviša Lečić (2)
  • Slavko Vujnovic (7)
  • Slobodan Anić (2)
  • Slobodan Radoičić (1)
  • Slobodan Roksandić (1)
  • Slobodan Žepinić (1)
  • Snežana Glavonjić (1)
  • Snežana Trajkovski (1)
  • Snježana Pivaš (1)
  • Sonja Nenić Andreev (1)
  • Srdjan Grbić (1)
  • Srđan Grubić (1)
  • Srđan Pavlović (1)
  • Stefanija Georgieva (8)
  • Stevan Ćomić (1)
  • Svetlana Mirković Borčić (1)
  • Svetlana Pajić (1)
  • Svetlana Žikić (2)
  • Tamara Dragašević (1)
  • Tamara Lazić (3)
  • Tamara Matović (2)
  • Tatjana Bolpačić (22)
  • Tatjana Jovanović (1)
  • Tatjana Lukić (2)
  • Tijana Anđelić (1)
  • Tijana Drljević (1)
  • Tijana Rauš (1)
  • Tomislav Mimica (1)
  • Tomo Djekovic (1)
  • Vedran Babik (1)
  • Vedrana Božić (2)
  • Vedrana Vukša (1)
  • Velibor Ilić (1)
  • Veljko Stanojković (1)
  • Veljko Žarić (1)
  • Veroljub Zmijanac (1)
  • Vesna Stojanvić (1)
  • Violeta Kovačević (1)
  • Vladan Buha (1)
  • Vladan Matović (1)
  • Vladimir Petković (2)
  • Vladimir Popović (1)
  • Vladimir Stojković (1)
  • Yeghishe Avagyan (1)
  • Žaklina Teofilović (2)
  • Žarko Milovanović (1)
  • Žarko Savić (1)
  • Željko Ćulibrk (1)
  • Željko Vidojević (1)
  • Živka Mutlak (1)
  • Živorad Radovanović (1)
  • Zoran Blagojević (1)
  • Zoran Daljević (1)
  • Zorica Dinić (1)
  • Zorica Popović (1)
  • Đorđe Koprivica (2)
  • Đorđe Milinković (1)
  • Đorđe Živanović (1)
  • Najnoviji Broj Controlling Magazina

    Controlling magazin #12

    Tema:
    "SUPPLY CHAIN"

    Autori:
    Preko 60 autora: Actavis, Elektromreža Srbije, BASF, Uniqa osiguranje...

    Preuzmi elektronsko izdanje