Naloge iz Excela Damijana Keržič Ljubljana, 2024 NALOGE IZ EXCELA Avtorica: dr. Damijana Keržič Recenzent: dr. Mitja Dečman Lektorica: Nina Barlič Oblikovalka naslovnice: dr. Damijana Keržič Oblikovalka besedila in prelomov: dr. Damijana Keržič Založila: Založba Univerze v Ljubljani Za založbo: Gregor Majdič, rektor Univerze v Ljubljani Izdala: Fakulteta za upravo Univerze v Ljubljani Za izdajatelja: Mirko Pečarič, dekan Fakultete za upravo Univerze v Ljubljani Prva elektronska izdaja, Ljubljana, 2024 Publikacija je brezplačna. Publikacija je v digitalni obliki prosto dostopna na https://ebooks.uni-lj.si/ Publikacija je izšla v okviru projekta razvojnega stebra financiranja (RSF) Univerze v Ljubljani, natančneje pod ukrepom A.II.1. »Uporaba in razvoj odprtih učnih gradiv na UL v luči spodbujanja njihovega soustvarjanja s študenti« v letu 2024. Kataložni zapis o publikaciji (CIP) pripravili v Narodni in univerzitetni knjižnici v Ljubljani COBISS.SI-ID 212527875 ISBN 978-961-297-430-5 (PDF) This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License. Kazalo nalog NALOGA 01 Statistični podatki .......................................................................................................7 Urejanje in oblikovanje celic NALOGA 02 Letni obračun malice ...................................................................................................8 SUM, AVERAGE, MIN, MAX, izračun s formulo NALOGA 03 Obresti ...................................................................................................................... 11 Izračun s formulo, gručni stolpčni grafikon NALOGA 04 Združeni narodi ......................................................................................................... 14 ROUND, izračun s formulo, naložen stolpčni grafikon, priprava strani za tisk NALOGA 05 Prevajalci .................................................................................................................. 18 VLOOKUP, ROUND, izračun s formulo, pogojno oblikovanje, palični grafikon NALOGA 06 Prodaja avtomobilov ................................................................................................ 21 VLOOKUP, ROUND, IF, izračun s formulo, pogojno oblikovanje, gručni palični grafikon NALOGA 07 Nočitve ..................................................................................................................... 24 ROUND, VLOOKUP, SUMIF, COUNTIF, izračun s formulo, 100-odstotno naložen palični grafikon NALOGA 08 Barometer kakovosti ................................................................................................ 27 VLOOKUP, ROUND, IF, izračun s formulo, pogojno oblikovanje, kombiniran grafikon, priprava strani za tisk NALOGA 09 Seštevek točk ........................................................................................................... 30 ROUND, IF, COUNTIF, AVERAGEIF, izračun s formulo, pogojno oblikovanje, grafikoni, filtriranje, razvrščanje NALOGA 10 Fakultetni urnik ........................................................................................................ 38 COUNTA, COUNTIF, AVERAGEIF, IF, izračun s formulo, pogojno oblikovanje NALOGA 11 Obisk bazena ............................................................................................................ 40 VLOOKUP, IF, COUNTIF, pogojno oblikovanje, grafikon, priprava strani za tisk NALOGA 12 Slovenske občine ..................................................................................................... 43 VLOOKUP, IF, OR, AND, NOT, COUNT, COUNTA, COUNTIF, SUMIF, AVERAGEIF, XY raztreseni grafikon NALOGA 13 Račun v trgovini ........................................................................................................ 48 VLOOKUP, NOW, CONCAT, MID, TEXT, RANDBETWEEN, izračun s formulo Damijana Keržič Naloge iz Excela Uvodnik Pred vami je zbirka nalog iz Excela, ki dopolnjuje e-učbenik Uvod v Microsoft Excel 365 in je namenjena študentom Fakultete za upravo Univerze v Ljubljani. Vsebina je tako nekoliko bolj prilagojena študijskemu programu in konceptu dela študentov fakultete, je pa gradivo lahko v pomoč tudi vsem, ki se z uporabniško programsko opremo srečujejo pri svojem vsakdanjem delu. V gradivu so uporabljeni slovenski izrazi, ki so skladni s tistimi, ki jih uporablja slovenska različica programa. Pomembni elementi Excela, ki jih naloga vključuje, so zapisani v naslovu naloge. Pred navodili naloge je navedena datoteka, ki jo potrebujete za delo, ter opis podatkov. Za navodili je dodana rešitev naloge. V primeru, da je rešitev tabela, ki se razteza prek več strani, je prikazana le prva stran. Če rešitev obsega več delovnih listov, je levo zgoraj zapisano ime delovnega lista z rešitvijo. Naj vas pri tem opozorimo, da pretvorbe v PDF datoteko lahko popačijo oblikovanje (debelino črt, barvo, ipd.), prav tako na barve in pisavo lahko vplivajo različne verzije MS Excela. Vsaka naloga se začne z odpiranjem navedene datoteke in ob koncu shranjevanjem, če seveda želite svoje delo obdržati. Navodilu posameznega koraka naloge, označenemu s številko, lahko sledijo kratki namigi o postopku reševanja, ki so označeni z alinejami. Ker pa je to zbirka nalog, ne vsebuje razlage snovi, ki jo študent dobi na vajah oziroma v učbeniku. Datoteke za delo so dostopne na naslovu https://repozitorij.uni-lj.si/IzpisGradiva.php?id=164823 Splošni napotki za delo  Pred reševanjem natančno preberemo opis in oznake podatkov na delovnih listih.  Trudimo se pisati univerzalne formule, zato uporabljamo ustrezne absolutne naslove.  Ne računamo in v pomožne celice ne zapisujemo vmesnih rezultatov, ampak navodilo za izračun v celico vedno zapišemo v celoti.  Pri kopiranju formule v stolpcih oz. vrsticah pazimo, da ne spremenimo že urejenega oblikovanja, zato uporabimo možnost polnitve brez oblikovanja. Oznake, ki jih uporabljamo Oblike zapisa oznak za lažje prepoznavanje elementov, ki so uporabljene v gradivu, so:  Poimenovanje datotek: naloga.xlsx  Naslovi celic in imena delovnih listov: A1, List1  Zapisi formul in funkcij: SUM(A1:B4)  Vrednosti v celicah: Točke, 123,45  Opis poti do ukazov na karticah in zavihkih: Osnovno>Pisava  Ukazi: Nadpisano  Ukazi v spustnem meniju ali vpisi vrednosti: 4 pt Damijana Keržič Naloge iz Excela NALOGA 01 Statistični podatki Urejanje in oblikovanje celic Naloga je namenjena osnovnemu vnosu podatkov in oblikovanju celic. 1. Odpremo prazen Excelov delovni zvezek. 2. V celice B3:B7 vpišemo števila 20.273, 111, 1000, 2.060.663 in 0,46 %. 3. V celice D4:D8 vpišemo Površina, Gostota prebivalstva, Tisočica, Prebivalstvo, Odstotek nepismenih. 4. Premaknemo vsebino celic B3:B7 v celice E4:E8. 5. Zbrišemo vrstico 6. 6. Besedila v stolpcu D zapišemo v več vrsticah in stolpec razširimo tako, da bo vsaka beseda v svoji vrstici. Nato samodejno prilagodimo višino vrstice. 7. V celico D3 vpišemo Slovenija in v C4 vir: SURS 2013. Celice C4:C8 združimo, besedilo zapišemo navpično navzgor in samodejno prilagodimo širino stolpca. 8. Celici D3 in F3 združimo, povečamo velikost na 16 pik in nastavimo višino vrstice na 30. Besedilo sredinsko poravnamo v navpični in vodoravni smeri. 9. V F4 vpišemo km2 in v F5 oseb/km2.  2 zapišemo s pomočjo oblikovanja pisave v oknu Osnovno>Pisava, možnost Nadpisano. 10. Obrobimo celice z debelo in tanko črto poljubne barve, kot je prikazano. Osenčimo celici s poljubno barvo. 11. Izključimo mrežne črte.  Kartica Ogled>Pokaži, možnost Mrežne črte. Slovenija Površina 20.273 km2 13 Gostota prebivalstva 111 oseb/km2 SURS 20 Prebivalstvo 2.060.663 vir: Odstotek nepismenih 0,46% 7 Damijana Keržič Naloge iz Excela NALOGA 02 Letni obračun malice SUM, AVERAGE, MIN, MAX, izračun s formulo Preprost izračun mesečnih izdatkov za malico v enem letu. Datoteka: delovnik.xlsx Opis podatkov na delovnem listu delovnik.  V celici G2 je zapisana vrednost obračunane ure na dan UD.  V celici P1 je vrednost nadomestila za malico M.  V celicah D5:D21 so podatki o številu delovnih dni (d) v mesecu (m) Sd,m. V celicah E5:E21 so podatki o številu praznikov (p) na delovni dan v mesecu (m) Sp,m. Enotno označimo Si,m ; i = d, p.  V celicah K3:M16 (oblikovana tabela) so za tri zaposlene (z) zapisane odsotnosti v času delovnih dni za vseh 12 mesecev Oz,m. 1. Na delovnem listu delovnik naj bodo v stolpcih B in C zapisane zaporedne številke in imena mesecev.  Označimo B5:C5 in z zapolnitveno ročico polnimo vse do vrstice 16. 2. V stolpcu F naj bo skupno število plačnih dni v mesecu po formuli Ss,m = Sd,m + Sp,m. 3. V vrstici 17 naj bodo vsote delovnih dni (d), praznikov (p) in skupno (s) število vseh v enem letu. Formula za izračun se glasi Si = Σ m=1. 12 Si,m ; i = d, p, s.  V D17 vpišemo funkcijo SUM in jo prekopiramo še v stolpec E (prazniki) in F (skupaj). 4. V vrstici 18 (D18:F18) naj bo izračunano letno povprečje, ki ga prikažemo celoštevilsko, v D19 najmanjša in v D20 največja vrednost v letu.  Za izračun povprečja v D18 uporabimo funkcijo AVERAGE ali formulo = Sd / 12 in prekopiramo še v sosednji celici. Popravimo zapis – brez decimalnih mest. Pozor: Samodejni izbor obsega za funkcijo AVERAGE ni pravilen, saj je poleg vrednosti mesecev vključena tudi skupna vsota, zato je treba obseg popraviti.  Izračunamo še najmanjšo vrednost s funkcijo MIN in največjo s funkcijo MAX. 5. V stolpcu G naj bo zapisano število obračunanih ur v mesecu. Formula za izračun je Um = Ss,m * UD. Pozor: Pravilno zapisana funkcija potrebuje en absolutni naslov ($). 8 Damijana Keržič Naloge iz Excela 6. V stolpcu H naj bo v odstotkih izražen mesečni letni delež plačilnih dni in izpisan z dvema decimalnima mestoma: = Ss,m / Ss. Pozor: Pravilno zapisana funkcija potrebuje en absolutni naslov ($). 7. Obseg B4:H16 oblikujemo s poljubnim pripravljenim slogom, ki ima različno obarvane vrstice in glavo. Pozor: Po oblikovanju tabelo ponovno pretvorimo v obseg tako, da na kartici Načrt tabele>Orodja izberemo Pretvori v obseg. Vrednost v B4 brišemo. 8. Vrednost v P1 oblikujemo z valuto €. 9. Celice N3:P16 oblikujemo enako, kot so oblikovane priležne celice K3:M16.  Uporabimo preslikovalnik oblik . 10. V stolpcih N, O in P izračunamo mesečni prejemek zaposlenega za malico po formuli Mz,m = (Sd,m – Oz,m) * M. Pozor: Pravilno zapisana funkcija v N5 potrebuje absolutne naslove ($). 11. Zapremo datoteko. 9 € € € € € € € € € € € € € 3 ed 6 0 0 0 4 0 4 6 4 3 0 4 ,1 ,8 ,6 ,6 ,6 ,3 ,6 ,3 ,5 ,3 ,7 ,6 ,3 6 edv 4 2 2 2 0 2 0 3 0 8 2 0 3 2 2 2 1 2 1 7 1 2 2 1 co M 1 1 1 1 1 1 1 1 1 1 1 ali € € € € € € € € € € € € 6 0 3 3 0 2 4 7 6 6 8 0 ca za m vako ,8 ,6 ,7 ,7 ,6 ,8 ,0 ,4 ,8 ,8 ,0 ,6 4 2 8 8 2 5 9 6 4 4 8 2 ali ek N 3 2 2 2 2 8 4 1 3 3 9 2 1 1 1 1 1 1 1 1 1 m rejem € € € € € € € € € € € € p et 7 0 3 3 8 7 9 2 6 0 7 7 ,4 ,6 ,7 ,7 ,0 ,4 ,6 ,8 ,8 ,6 ,4 ,4 Km 6 2 8 8 8 6 9 5 4 2 6 6 1 2 2 2 9 1 7 8 3 2 1 1 1 1 1 1 1 1 1 1 1 ed 0 0 1 1 2 0 5 8 4 1 0 3 edvM st tno vako 0 0 0 0 0 6 15 1 0 0 4 1 so N do et 3 0 0 0 4 1 10 6 0 2 1 2 Km ikvn % % % % % % % % % % % % 1 6 5 3 3 5 1 5 3 1 6 1 ,8 ,6 ,0 ,4 ,4 ,0 ,8 ,0 ,4 ,8 ,6 ,8 i dela ž 8 7 8 8 8 8 8 8 8 8 7 8 le rn e u d 8 c 184 160 168 176 176 168 184 168 176 184 160 184 2088 ese a mn ur št. 23 20 21 22 22 21 23 21 22 23 20 23 22 261 ajp sku 1 0 0 1 2 1 0 1 0 1 0 2 9 1 iki raznp 22 20 21 21 20 20 23 20 22 22 20 21 21 20 23 iki 252 i vn ev lo n e d d erb er er j er b b ar ar st m b m AJ rečje an eč u u o arec ril aj ijn lij gu vem vp kt o ece o ajm ajv mesec jan febr m ap m ju ju av septe o n d SKUP P N N 1 2 3 4 5 6 7 8 9 10 11 12 Damijana Keržič Naloge iz Excela NALOGA 03 Obresti Izračun s formulo, gručni stolpčni grafikon Izračunali bomo, za koliko se razlikujeta končni vrednosti privarčevane vsote v primeru navadnega izračuna obresti oziroma ko se obrestno obrestuje. Opazovali bomo petletno obdobje. Prikazali bomo tudi grafično. Navadno obrestovanje. V vsakem obrestovalnem obdobju se vrednost glavnice G poveča za enako vrednost. Po n mesecih in (odstotni mesečni) obrestni meri p je glavnica izračunana po formuli Gn = G + G * n * p Obrestno obrestovanje. Poleg začetne glavnice se v vsakem obrestovalnem obdobju upošteva še obresti prejšnjega obdobja. Formula izračuna je Gn = G * (1 + p)n Datoteka: obresti.xlsx Opis podatkov na delovnem listu obresti.  V celici B1 je vrednost glavnice G.  V celicah B4:E5 sta obrestni meri za navadno in obrestno obrestovanje (p). 1. Pripravimo preglednico za izračun.  V stolpcu A od A5 dalje izdelamo številčno zaporedje mesecev do 60 (n). Uporabimo polnitev V nizih… , Osnovno>Urejanje.  Širino stolpca A nastavimo na 9.  Celici A3 in A4 združimo in besedilo zapišemo v več vrsticah.  Vrednost v B1 oblikujemo z valuto evro.  Celice D3:F4 oblikujemo enako kot so B3:C4. 2. V stolpca B in C izračunamo novo glavnico za vsak mesec in obrestni meri v B4 in C4 za navadno obrestovanje.  Funkcijo za izračun vpišemo le v B5 in premislimo, katere naslove moramo absolutno nasloviti. 3. V stolpca D in E izračunamo novo glavnico v mesecu po obrestno obrestnem računu in z obrestnima merama v D4 in E4.  Operator za potenciranje je ^. Znak se nahaja na tipki s številko 3. 11 Damijana Keržič Naloge iz Excela 4. Izdelamo gručni stolpčni grafikon, ki prikazuje vrednosti za obrestno mero 3,5 % pri obeh načinih obrestovanja. Grafikon postavimo prek prvega dela tabele.  Ker je podatkov veliko, grafikon razširimo. 5. Grafikon dodatno oblikujemo.  Vrzel med stolpci in prekrivanje stolpcev nastavimo na 0.  Stolpca obarvamo s poljubno svetlejšo barvo in obrobimo z skladno temnejšo.  Dodamo trendni črti (linearna, eksponentna). Črti naj bosta polni, debeline 2 pt in usklajeni z barvami stolpcev.  Merilo navpične osi naj bo od 1000 do 8000, številke pa prikazane brez decimalnih mest.  Mrežne črte naj bodo črtkane, temno sive.  Legendo prestavimo na levo stran, prek grafikona. 12 60 59 58 57 56 55 54 53 52 51 50 49 48 47 46 45 44 43 42 41 40 39 38 37 36 35 34 33 € € € € € € € € € € € € € € € € € € € 32€ € € € € € € € € € stovanj ,00 ,44 ,39 ,89 ,00 ,79 ,32 ,66 ,87 ,02 ,20 ,47 ,92 ,63 ,69 ,17 ,19 ,83 ,19 31,37 ,49 ,65 ,97 ,58 ,59 ,13 ,34 ,36 ,33 30 8% 038 077 118 160 205 250 298 347 398 452 507 564 623 685 749 816 885 956 031 29108 188 271 357 447 540 637 737 841 949 3, 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 2. 2. 2. 2. 2. 2. 2. 2. 2. 2. 2. anje 28 ov 27 est erjava obre 26 25 obr € € € € € € € € € € € € € € € € € € € 24€ € € € € € € € € € o Prim ,00 ,23 ,72 ,52 ,69 ,26 ,28 ,81 ,90 ,60 ,97 ,07 ,96 ,69 ,35 ,99 ,68 ,49 ,50 23,79 ,43 ,51 ,11 ,33 ,24 ,96 ,57 ,17 ,88 restn 22 5% 035 071 108 147 187 229 272 316 362 410 459 511 563 618 675 733 794 857 922 989 059 131 206 283 363 445 531 620 711 Ob 21 3, 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 2. 2. 2. 2. 2. 2. 2. 2. 2. 20 19 ) 18 17 € € € € € € € € € € € vanje € ) € € € € € € € 16€ € € € € € € € € € sto ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 vanje ,00 ,00 ,00 ,00 ,00 ,00 ,00 15,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 14 sto 8% 038 076 114 152 190 228 266 304 342 380 418 o obre 456 494 532 570 608 646 684 722 13760 798 836 874 912 950 988 026 064 102 anje 3, 1. 1. 1. 1. 1. 1. 1. 1. 1. nje 1. 1. 1. stn 1. 1. 1. 1. 1. 1. 1. 1.12 1. 1. 1. 1. 1. 1. 2. 2. 2. ov vanje o obre 11 dn est sto stova 10 ava 9 € obr € € € € € € € € € (N o obre € o obre € € € € € € € € € 8€ € € € € € € € € € nentna (Obre ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 stn ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 7,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 spo earna 6 000 vadno 035 070 105 140 175 210 245 280 315Navadn 350 Obre 385 Ek 420 Lin 455 490 525 560 595 630 665 700 735 770 805 840 875 910 945 980 015 1. 5% Na 3, 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 51. 1. 1. 1. 1. 1. 1. 1. 1. 2. 4 3 2 ca 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 120 21 22 23 24 25 26 27 28 29 anja avni ev € € € € € -€ Gl Meseci 000 000 € 000 000 000 € 000 000 000 € varč 8. 7. 6. 5. 4. 3. 2. 1. Damijana Keržič Naloge iz Excela NALOGA 04 Združeni narodi ROUND, izračun s formulo, naložen stolpčni grafikon, priprava strani za tisk Pomoč državi je odvisna od gostote prebivalstva. V nalogi primerjamo razliko izračuna pomoči z gostoto, ki je prikazana z določenim številom decimalnih mest, in izračunano gostoto z zaokrožitvijo. Datoteka: pomocZN.xlsx Opis podatkov na delovnem listu pomoč.  Za države (D), navedene v C1:I1, so v C2:I2 podatki o številu prebivalcev PREBD in v C3:I3 površine teh držav POVRD.  V celicah B9:B12 in B16:B19 je zapisana višina pomoči za posamezno kategorijo pomoči (i) VPi, kjer je i = hrana, obleka, material, zdravila. 1. Širina stolpcev od C do I naj bo 16. 2. V C4:I4 izračunamo gostoto prebivalstva države po formuli GOSD = PREBD / POVD. Rezultat prikažemo brez decimalnih mest. 3. V C5:I5 izračunamo gostoto prebivalstva na celo mesto natančno, GOSZD.  Uporabimo enako formulo, vendar izračun zaokrožimo s funkcijo ROUND. Pozor: Vizualno so rezultati v vrstici 4 in 5 za posamezno državo enaki, vendar pa se števila, ki so vsebina celice, med seboj razlikujejo. Preverimo na izračunu. 4. V celice C9:I12 izračunamo višino pomoči državi (D) za posamezno kategorijo (i) PD,i = VPi * GOSD.  Pred izračunom preverite, kako je oblikovana oblika zapisa v celici. Razmislite, zakaj se je po izračunu oblikovanje spremenilo. Pozor: V C9 zapišemo formulo, ki bo ob kopiranju v ostale celice izračunala pravilen rezultat. Potrebujemo absolutni naslov, in sicer dva $. 14 Damijana Keržič Naloge iz Excela 5. V celice C16:I19 izračunamo višino pomoči državi (d) za posamezno kategorijo (i) PD,i = VPi * GOSZD.  Na istih mestih kot v prejšnjem izračunu potrebujemo absolutni naslov.  Opazimo, da so zaradi zaokroževanja vrednosti prejete pomoči po prvem in drugem izračunu različne. Nekatere države celo ne bi dobile pomoči, saj se je izračunana zaokrožena gostota zaokrožila na nič. V praksi torej vedno razmislimo, kako natančne izračune želimo imeti, saj ti vplivajo na nadaljnje izračune. 6. Delovni list naj vsebuje dva naložena stolpčna grafikona. Prvi naj prikazuje višino pomoči Afganistanu, Albaniji in Alžiriji po posameznih kategorijah pomoči. Drugi pa naj prikazuje za vse tri omenjene države prikazano pomoč po kategorijah.  Oba grafikona predstavljata iste številske podatke, vendar v drugačnem pogledu. Grafikona si oglejte v rešitvah. 7. Oblikujemo prvi grafikon.  Za Albanijo so podatkovne vrednosti na sredini, na belem polnilu, prosojnosti 40 %.  Največja vrednost navpične osi naj bo 52.000.000. Nastavimo še enote in sicer glavno različico na 10.000.000 in podrazličico na 5.000.000.  Prikažemo primarne pomožne vodoravne mrežne črte. Pozor: Ker nam program samodejno spremeni minimalno vrednost, jo ponovno sami nastavimo na 0. 8. Oblikujemo drugi grafikon.  Prikažemo glavne navpične mrežne črte. Vodoravne mrežne črte naj bodo črtkane. Črtam spremenimo barvo v poljubno.  Risalno površino grafikona obarvamo s poljubno svetlejšo barvo.  Enoto prikaza na navpični osi nastavimo na milijon. Izberemo oznake navpične osi in v podoknu Oblikovanje osi>Možnosti osi v spustnem meniju Enote prikaza izberemo ustrezno obliko. 15 Damijana Keržič Naloge iz Excela 9. Delovni list pomoč pripravimo za tisk na enem listu A4 formata. V glavi lista naj bo na levi naše ime, na desni trenutni datum, spodaj levo ime delovnega zvezka in desno številka strani.  Na kartici Postavitev strani spremenimo list v ležečega in po potrebi zmanjšamo merilo.  Elemente glave in noge izberemo z ustreznimi ukazi za vnos v polja glave in noge, ime vpišemo. 16 1 13.11.2024 14 14 32.853.800 2.381.741 irija irija 1.379.402,71 € 2.069.104,07 € 2.758.805,43 € 4.138.208,14 € irija 1.400.000,00 € 2.100.000,00 € 2.800.000,00 € 4.200.000,00 € Alž Alž Alž Alžirija 109 109 28.748 pomoči 3.129.678 lavira ja ja ja orijah zd ni ni ni 10.886.593,85 € 16.329.890,78 € 21.773.187,70 € 32.659.781,55 € 10.900.000,00 € 16.350.000,00 € 21.800.000,00 € 32.700.000,00 € ja ateg l Alba Alba Alba ani teria 46 46 Alb po k ma 652.090 leke n 29.863.010 n n državam ob sta sta sta 4.579.584,11 € 6.869.376,16 € 9.159.168,21 € 4.600.000,00 € 6.900.000,00 € 9.200.000,00 € gani gani 13.738.752,32 € gani 13.800.000,00 € ana Af Af Af anim anistan hr 0 0 Afg izbr 200 € € € € 7.138 - - - - € € € € € € € € € Pomoč a a 2.801,91 € 4.202,86 € 5.603,81 € 8.405,72 € a - 00 € ,00 ,00 ,00 ,00 ,00 ,00 ,00 ,00 zij zij zij 90 80 70 60 50, 40 30 20 10 oni j li Mi ha ha ha Ab Ab Ab 7 7 710.200 4.839.400 € apur apur 681.413,69 € 700.000,00 € 55 1.022.120,53 € 1.362.827,37 € 2.044.241,06 € apur 1.050.000,00 € 1.400.000,00 € 2.100.000,00 € 1, la ng ng ng 78 vi Si Si Si 9. m zdra 1 1 .6532 žava € 32.965 34700 dr 707, l im 183. ateria ko ko 95.000,00 € an 142.500,00 € 190.000,00 € 285.000,00 € ko 100.000,00 € 150.000,00 € 200.000,00 € 300.000,00 € .77 m žirijaAl ona ona ona 21 i izbr M M M € 780, 720 banija 1454 1454 89 Al 9. pomoč obleke .32 1.046.814 jah 16 tan jn jn jn ori € nis 85 hra hra hra 3, Afga 145.390.833,33 € 218.086.250,00 € 290.781.666,67 € 436.172.500,00 € 145.400.000,00 € 218.100.000,00 € 290.800.000,00 € 436.200.000,00 € ateg 59 na Ba Ba Ba 6. hra a .88 2 po k 10 km ostot € € € € € € o 2 g oči oči - 00 00 00 00 00 km reb/ ena Pomoč 0, om om na a p 00 000, 000, 000, 000, va valstv p 100.000,00 € 150.000,00 € 200.000,00 € 300.000,00 € p 100.000,00 € 150.000,00 € 200.000,00 € 300.000,00 € 0. rši na na 000. 000. 000. 000. .00 rža okorž 50 40. 30. 20. 10. ek D prebi pov gostot za viši viši im oč rial vila oč rial vila oč e Pri na ate na ate Im pom hra obleke m zdra pom hra obleke m zdra pom Damijana Keržič Naloge iz Excela NALOGA 05 Prevajalci VLOOKUP, ROUND, izračun s formulo, pogojno oblikovanje, palični grafikon Za prevode dokumentov so prispele ponudbe prevajalcev. Poiskali bomo najugodnejšo ponudbo. Datoteka: prevajalci.xlsx Delovni zvezek vsebuje dva delovna lista: kode in izračun. Opis podatkov na delovnem listu kode.  V celicah D2:G10 so podatki o prevajalcih (P), v stolpcu G je zapisan popust prevajalca XP.  V celicah D13:F21 so podatki o dokumentih (D), v stolpcu F je število znakov v dokumentu ZD. Opis podatkov na delovnem listu izračun.  V A2 je število znakov na avtorsko polo AP.  V celicah C2:H2 so kode prevajalcev.  V celicah C4:H4 so tarife prevoda na avtorsko polo za prevajalca TP.  V celicah A6:A13 so kode dokumentov, namenjenih za prevod. Navodila naloge se nanašajo na reševanje na listu izračun. 1. Vsebino celice Prevajalci zapišemo prek celic C1:H1 in poljubno obarvamo. 2. Vrednosti v C4:H4 izpišemo v evrih. 3. V celice B6:B13 poiščemo in prikažemo število znakov dokumenta (ZD), ki ga določa koda.  Uporabimo VLOOKUP. 4. V celice C3:H3 vpišemo priimke prevajalcev glede na kode v zgornjih priležnih celicah.  Uporabimo VLOOKUP. 5. Za vsakega prevajalca izračunamo ceno za prevod posameznega dokumenta po formuli PP,D = ZD / AP * TP na cela mesta natančno.  Ne pozabimo na zaokrožen izračun s funkcijo ROUND in absolutne naslove. 6. V vrstici 14 izračunamo skupno vrednost prevoda za vsakega prevajalca SPP. 18 Damijana Keržič Naloge iz Excela 7. V vrstici 15 izračunamo zaokroženo na cela mesta ceno s popustom, ki jo obljubljajo prevajalci po formuli SPPP = SPP * (1 – Xp).  Vrednost popusta za vsakega prodajalca poiščemo z VLOOKUP.  Ne pozabimo na zaokrožitev z ROUND. 8. Izpis v celicah C6:H15 oblikujemo z evri. 9. Izrišemo dvojno vodoravno črto poljubne barve pod vrednostmi v vrsticah 2, 4, 5 in 13. 10. V vrsticah 14 in 15 obarvamo zeleno tisto celico, katere vrednost je v vrstici najmanjša.  Barvanje ročno ni rešitev. Uporabimo pogojno oblikovanje.  Ali lahko s preslikovalnikom oblik prenesemo pogojno oblikovanje iz vrstice 14 v vrstico 15? 11. S paličnim grafikonom prikažemo skupno ceno prevodov brez in s popustom za vse prevajalce. Prevajalci so opisani s kodo in priimkom. Oblikujemo s Slogom 3 in odstranimo naslov in vodoravno os. 19 št. znakov za avtorsko polo Prevajalci 1500 0P1 0P3 0P4 0P7 0P9 0P5 Komar Kumar Caf Logan Oblak Peterski tarifa prevajalca na polo 28,00 € 27,50 € 28,50 € 29,00 € 26,70 € 27,00 € koda dokumenta št.znakov cena za prevod a2341 6530 122 ,00 € 120 ,00 € 124 ,00 € 126 ,00 € 116 ,00 € 118 ,00 € f6575 3508 65,00 € 64,00 € 67,00 € 68,00 € 62,00 € 63,00 € f3546 13600 254 ,00 € 249 ,00 € 258 ,00 € 263 ,00 € 242 ,00 € 245 ,00 € a5430 10230 191 ,00 € 188 ,00 € 194 ,00 € 198 ,00 € 182 ,00 € 184 ,00 € a6465 2354 44,00 € 43,00 € 45,00 € 46,00 € 42,00 € 42,00 € n2341 6530 122 ,00 € 120 ,00 € 124 ,00 € 126 ,00 € 116 ,00 € 118 ,00 € n1234 8923 167 ,00 € 164 ,00 € 170 ,00 € 173 ,00 € 159 ,00 € 161 ,00 € a5609 6579 123 ,00 € 121 ,00 € 125 ,00 € 127 ,00 € 117 ,00 € 118 ,00 € Skupaj 1. 088,00 € 1. 069,00 € 1. 107,00 € 1. 127,00 € 1. 036,00 € 1. 049,00 € Skupaj s popustom 1. 023,00 € 1. 016,00 € 1. 052,00 € 1. 065,00 € 1. 005,00 € 1. 002,00 € 1.002,00 € PETERSKI 0P5 1.049,00 € 1.005,00 € OBLAK 0P9 1.036,00 € 7 1.065,00 € LOGAN 0P 1.127,00 € 4 1.052,00 € CAF 0P 1.107,00 € 1.016,00 € KUMAR 0P3 1.069,00 € 1.023,00 € KOMAR 0P1 1.088,00 € Skupaj s popustom Skupaj Damijana Keržič Naloge iz Excela NALOGA 06 Prodaja avtomobilov VLOOKUP, ROUND, IF, izračun s formulo, pogojno oblikovanje, gručni palični grafikon Zbrani so podatki o prodaji osebnih avtomobilov po znamkah in modelih v dveh zaporednih letih. Opravili bomo primerjavo prodaje obeh let in izračunali tržne deleže za prvih dvajset najbolje prodajanih modelov. Datoteka: avto.xlsx Delovni zvezek vsebuje dva delovna lista prodaja in analiza. Opis podatkov na delovnem listu prodaja.  V A2:A21 so zapisani modeli avtomobilov, v B2:B21 pa pripadajoča znamka.  V C2:C21 (leto 2022) in D2:D21 (leto 2023) so podatki o številu prodanih avtomobilov glede na model (M) in leto (L) PM,L. Opis podatkov na delovnem listu analiza.  Glede na model v B5:B24 so v C5:C24 in D5:D24 podatki o številu prodanih avtomobilov v decembru za ustrezno leto PdecM,L. 1. Na delovnem listu analiza opravimo izračun. 2. V A5:A24 glede na model v stolpcu B izpišemo ustrezno avtomobilsko znamko. 3. V E5:E24 izračunamo tržni delež za leto 2022 in v F5:F24 tržni delež za leto 2023 decembrske prodaje v skupni prodaji na tri decimalna mesta natančno za posamezni model najbolje prodajanih dvajset modelov. Izračun prikažemo v odstotkih z enim decimalnim mestom. Formula za izračun TDdecM,L = PdecM,L / ΣM PdecM,L.  Formulo zapišemo v E5 in jo kopiramo v preostale celice. Pravilno zapisana formula potrebuje le dva $. 4. V G5:G24 zapišemo leto, ko je bil decembrski tržni delež večji. Z rdečo in zeleno puščico označimo, ali je tržni delež v letu 2023 padel (rdeča) ali narastel (zelena). 5. V C25 in D25 izračunamo skupni decembrski tržni delež v letu za prvih dvajset najbolj prodajanih avtomobilov. Formula TDdecL = ΣM PdecM,L / ΣM PM,L. Prikažemo v odstotkih in z dvema decimalnima mestoma. 6. V drugi tabeli (vrstice 26 do 35) izračunamo skupno število najbolj prodajanih avtomobilov v decembru glede na posamezno znamko (npr. skupno število prodanih škod …) v letih 2022 (stolpec C) in 2023 (stolpec D). 21 Damijana Keržič Naloge iz Excela 7. V drugi tabeli (vrstice 26 do 35) izračunamo na tri decimalna mesta natančno tržni delež decembrske prodaje posamezne znamke za leto 2022 (stolpec E) in 2023 (stolpec F), podobno kot v točki 3. Prikažemo v odstotkih in z enim decimalnim mestom. 8. V stolpec G druge tabele (G26:G35) izračunamo razliko tržnih deležev v letu 2023 glede na leto 2022. 9. Narišemo gručni palični grafikon, ki prikazuje razlike med tržnimi deleži avtomobilskih znamk in ga dodatno oblikujemo:  Os grafikona premaknemo na levo.  Širino vrzeli nastavimo na 100 %.  Izpišemo vrednosti podatkov in odstranimo vodoravno os. 22 Nova osebna vozila v letih 2022 in 2023 Prvih 20 najbolje prodajanih modelov prodaja december decembrski tržni delež Znamka Model 2022 2023 2022 2023 večji tržni delež ŠKODA OCTAVIA 53 72 6,2% 5,9% 2022 RENAULT CLIO 75 92 8,8% 7,5% 2022 RENAULT CAPTUR 57 92 6,7% 7,5% 2023 VOLKSWAGEN T-ROC 59 78 6,9% 6,4% 2022 TOYOTA YARIS CROSS 24 99 2,8% 8,1% 2023 ŠKODA KAMIQ 45 66 5,3% 5,4% 2023 TESLA MODEL Y 6 77 0,7% 6,3% 2023 VOLKSWAGEN TAIGO 59 77 6,9% 6,3% 2022 VOLKSWAGEN TIGUAN 77 56 9,0% 4,6% 2022 ŠKODA KODIAQ 36 45 4,2% 3,7% 2022 TOYOTA COROLLA 29 43 3,4% 3,5% 2023 DACIA SANDERO 29 35 3,4% 2,9% 2022 VOLKSWAGEN T-CROSS 46 71 5,4% 5,8% 2023 PEUGEOT 2008 19 52 2,2% 4,3% 2023 HYUNDAI TUCSON 16 39 1,9% 3,2% 2023 DACIA DUSTER 62 96 7,2% 7,9% 2023 VOLKSWAGEN GOLF 23 46 2,7% 3,8% 2023 KIA SPORTAGE 42 16 4,9% 1,3% 2022 VOLKSWAGEN POLO 66 43 7,7% 3,5% 2022 CITROEN C3 33 25 3,9% 2,0% 2022 DECEMBER tržni delež 4,83% 6,57% ŠKODA 134 183 15,7% 15,0% -0,7% RENAULT 132 184 15,4% 15,1% -0,3% VOLKSWAGEN 330 371 38,6% 30,4% -8,2% TOYOTA 53 142 6,2% 11,6% 5,4% TESLA 6 77 0,7% 6,3% 5,6% DACIA 91 131 10,6% 10,7% 0,1% PEUGEOT 19 52 2,2% 4,3% 2,1% HYUNDAI 16 39 1,9% 3,2% 1,3% KIA 42 16 4,9% 1,3% -3,6% CITROEN 33 25 3,9% 2,0% -1,9% Razlika tržnih deležev za leto 2023 glede na leto 2022 CITROEN -1,9% KIA -3,6% HYUNDAI 1,3% PEUGEOT 2,1% DACIA 0,1% TESLA 5,6% TOYOTA 5,4% VOLKSWAGEN -8,2% RENAULT -0,3% ŠKODA -0,7% Damijana Keržič Naloge iz Excela NALOGA 07 Nočitve ROUND, VLOOKUP, SUMIF, COUNTIF, izračun s formulo, 100-odstotno naložen palični grafikon Za turistični kraj imamo podane njegove kapacitete ter nočitve v njih za celoletno obdobje. Izračunali bomo zasedenost ležišč. Datoteka: nočitve.xlsx Delovni zvezek vsebuje dva delovna lista nočitev in kapacitete. Opis podatkov na delovnem listu nočitev.  V C1:K1 so oznake počitniških objektov.  V C4:K15 so podatki o nočitvah gostov po vrstah počitniških objektov (O) za posamezni mesec (M) NO,M.  V L4:L15 so zapisani mesečni deleži nočitev domačih gostov DM za vse vrste nočitev. Opis podatkov na delovnem listu kapacitete.  V A1:D11 je seznam vrst objektov s podatki o številu ležišč.  V F1:G14 so razporejeni meseci po sezonah in dnevi v mesecu. 1. Na listu nočitve v C2:G2 izpišemo število ležišč posamezne vrste počitniškega objekta, zapisanega v vrstici 1, LO.  Uporabimo VLOOKUP. 2. V A4:A15 izpišemo sezono, v katero sodi mesec.  Uporabimo VLOOKUP. 3. V C16:K19 izračunamo število gostov glede na sezono, zapisano v A16:A19.  Uporabimo SUMIF.  V izračunanih celicah se pojavi zgoraj levo zelen trikotnik. V spletu poiščite pomoč, zakaj. 4. Obseg celic A21:K33, oblikujemo enako, kot je oblikovan obseg A3:K15.  Uporabimo preslikovalnik oblik . 5. V A22:A33 vpišemo število dni v mesecu ŠM.  Uporabimo VLOOKUP. 24 Damijana Keržič Naloge iz Excela 6. V tej tabeli izračunamo na tri decimalna mesta natančno zasedenost ležišč z domačimi gosti za posamezen objekt po formuli ZO,M = (NO,M * DM) / (LO *ŠM). Prikažemo v odstotkih in enim decimalnim mestom. 7. V C34:K34 izračunamo, koliko mesecev v letu je imelo zasedenost več kot 50 %, v C35:K35 pa izračunamo, koliko je bilo mesecev z manj kot 30 % zasedenostjo.  Uporabimo COUNTIF in tri $. 8. Za zimske mesece izdelamo 100-odstotno naložen palični grafikon. ki prikazuje število nočitev za objekte s kapaciteto manjšo od 50. Grafikon dodatno oblikujemo:  barve v modre odtenke,  območje grafikona naj ima za ozadje poljubno zimsko sliko,  vrednosti podatkov izpisane v grafikonu na belih osnovah, prosojnosti 50 %. 25 H1 H2 H3 H4 H5 A1 A2 A3 A4 delež 30 125 75 110 70 23 18 20 18 domačih sezona mesec Število nočitev gostov Z januar 75 258 2.186 1.506 564 145 140 137 167 0,816 Z februar 23 600 1.816 2.083 499 227 265 209 200 0,914 PM marec 12 957 1.736 2.104 459 126 176 145 75 0,851 PM april 45 719 1.247 2.383 389 178 218 200 125 0,823 PM maj 112 402 1.535 1.721 1.209 216 130 145 213 0,751 PL junij 325 1.276 1.943 1.683 1.340 290 282 250 299 0,838 PL julij 356 1.665 1.391 1.709 1.768 347 300 278 314 0,854 PL avgust 296 275 1.971 836 1.009 399 416 321 412 0,716 J september 123 1.513 1.407 1.472 998 231 208 196 223 0,736 J oktober 45 1.415 1.442 2.804 435 110 165 210 154 0,784 J november 123 1.815 1.234 2.421 389 237 251 75 99 0,901 Z december 134 1.609 2.365 1.815 1.009 345 241 198 210 0,871 PM 169 2078 4518 6208 2057 520 524 490 413 PL 977 3216 5305 4228 4117 1036 998 849 1025 J 291 4743 4083 6697 1822 578 624 481 476 Z 232 2467 6367 5404 2072 717 646 544 577 dnevi mesec Zasedenost ležišč domačih gostov 31 januar 6,6% 5,4% 76,7% 36,0% 21,2% 16,6% 20,5% 18,0% 24,4% 28 februar 2,5% 15,7% 79,0% 61,8% 23,3% 32,2% 48,1% 34,1% 36,3% 31 marec 1,1% 21,0% 63,5% 52,5% 18,0% 15,0% 26,8% 19,9% 11,4% 30 april 4,1% 15,8% 45,6% 59,4% 15,2% 21,2% 33,2% 27,4% 19,1% 31 maj 9,0% 7,8% 49,6% 37,9% 41,8% 22,8% 17,5% 17,6% 28,7% 30 junij 30,3% 28,5% 72,4% 42,7% 53,5% 35,2% 43,8% 34,9% 46,4% 31 julij 32,7% 36,7% 51,1% 42,8% 69,6% 41,6% 45,9% 38,3% 48,1% 31 avgust 22,8% 5,1% 60,7% 17,6% 33,3% 40,1% 53,4% 37,1% 52,9% 30 september 10,1% 29,7% 46,0% 32,8% 35,0% 24,6% 28,3% 24,0% 30,4% 31 oktober 3,8% 28,6% 48,6% 64,5% 15,7% 12,1% 23,2% 26,6% 21,6% 30 november 12,3% 43,6% 49,4% 66,1% 16,7% 30,9% 41,9% 11,3% 16,5% 31 december 12,5% 36,2% 88,6% 46,4% 40,5% 42,1% 37,6% 27,8% 32,8% >50% 0 0 7 5 2 0 1 0 1 <30% 10 9 0 1 6 6 5 8 6 Zimski meseci 18 A4 167 200 210 20 A3 137 209 198 18 A2 140 265 241 23 A1 145 227 345 30 75 23 134 H1 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% januar februar december Damijana Keržič Naloge iz Excela NALOGA 08 Barometer kakovosti VLOOKUP, ROUND, IF, izračun s formulo, pogojno oblikovanje, kombiniran grafikon, priprava strani za tisk Za upravne enote so bile opravljene ocene kakovosti po različnih kriterijih. Izračunali bomo dodatek za upravno enoto glede na oceno kriterija. Datoteka: kakovost.xlsx Delovni zvezek vsebuje dva delovna lista kakovost in UE. Opis podatkov na delovnem listu ocene.  V C3:J14 so za upravne enote (U) vrednosti ocenjene kakovosti po kriterijih (K) OK,U.  V L15 je vrednost točke T. Opis podatkov na delovnem listu UE.  V A1:C13 je seznam s podatki o upravnih enotah. 1. Na listu ocene v B3:B14 izpišemo imena upravnih enot, ki ustrezajo matični številki zapisani v stolpcu A. 2. Vrednost v celici L15 oblikujemo z valuto €. 3. V celicah C3:J14 vrednosti, manjše od povprečja, ločeno po kriterijih, izpišemo krepko rdeče.  Uporabimo pogojno oblikovanje. Pravilo nastavimo v celicah C3:C14 in s preslikovalnikom oblik prenesemo še na ostale stolpce. 4. V C15:J15 izračunamo na dve decimalni mesti natančno povprečne ocene kriterija POK.  Uporabimo ROUND in AVERAGE. 5. Obseg A2:J14 prekopiramo v A18 in pobrišemo številske vrednosti. 6. V C19:J30 izračunamo za upravno enoto dodatek za kakovost po formuli: DK,U = OK,U / POK * 100 * T. Izpišemo z valuto . €  Formula potrebuje tri $. 7. V stolpce M2:T2 prekopiramo celice C2:J2. V N3:T15 zapišemo +, če je ocena kriterija upravne enote (OK,U), vpisane v stolpcu A, večja od povprečja (POK), oz. --, če ni. 27 Damijana Keržič Naloge iz Excela 8. Izdelamo kombinirani grafikon za vse kriterije za Ljubljano in Kranj, ki z gručnim stolpčnim diagramom prikazuje ocene kakovosti ter skupna povprečja s črtnim z oznakami. Grafikon oblikujemo: merilo na navpični osi se prične pri 4,10 in konča pri 4,85 s korakom 0,15, stolpci naj bodo brez razmika, črtni grafikon z oznakami pa naj bo brez črte in z oznako črtice velikosti 20 in vpisano vrednostjo. 9. Pripravimo izpis na enem listu. V glavi lista je njegovo ime. 28 je ti vl ag ra a rip st m o o P n p -- -- -- + + + + -- -- + + + a in m esl vze o a je p Z n za h -- -- -- + + + + -- -- + -- + sča eb vo d ra st o P n izve -- -- + + + + + -- -- + + + ta v lite rite vaK sto -- -- + -- + + + -- -- + -- + jen ja ja n u ap zb u V za -- -- + + + + + -- -- -- + + i ne jen sl a o n p Z za h -- -- + + + + + -- -- + + + j 1 nva na stue Reše e m -- -- + + + + -- -- -- + -- + o j n ci p a o rmfo Dost st in -- -- -- + + + + -- -- + + -- st € o 6 n čke ,8 d 0 e to vr 8 5 0 2 6 8 3 6 3 2 7 4 1 st st € € € € € € € € € € € € o ,7 ,6 ,8 ,8 ,9 ,8 ,8 ,7 ,7 ,8 ,8 ,8 ,8 o 6 4 2 8 8 5 6 1 7 8 7 4 n 4 4 4 4 4 4 4 4 4 4 4 4 4 n ,4 ,1 ,8 ,1 ,6 ,2 ,3 ,1 ,5 ,1 ,0 ,5 je ti je ti 5 3 5 6 8 7 6 5 4 6 7 6 vl a a g vl g 8 8 8 8 8 8 8 8 8 8 8 8 t ra a ra a rip m m o rip o gati P p P p jenosvl e 9 7 7 1 4 1 0 1 1 9 7 5 8 ra n ,6 ,5 ,7 ,8 ,9 ,9 ,8 ,7 ,7 ,7 ,7 ,8 ,7 € € € € € € € € € € € € poma 8 2 2 4 8 4 6 4 4 8 2 6 81 Prip ce je 4 4 4 4 4 4 4 4 4 4 4 4 4 je o n n ,3 ,2 ,8 ,5 ,8 ,3 ,3 ,7 ,7 ,1 ,8 ,2 4, a ih ih n a n 4 2 5 6 8 8 6 4 4 6 5 7 m e m e 8 8 8 8 8 8 8 8 8 8 8 8 sl sl vze o vze o e a p a p nj Z za Z za ma enih 6 9 7 7 3 6 8 3 6 8 3 2 6 st st € € € € € € € € € € € € posl o ,6 ,6 ,7 ,7 ,8 ,8 ,7 ,6 ,6 ,7 ,8 ,8 ,7 4 4 4 4 4 4 4 4 4 4 4 4 4 o 9 4 8 8 6 1 6 5 9 6 6 8 za 78 Zavze sn sn ,1 ,7 ,1 ,1 ,2 ,8 ,3 ,6 ,1 ,3 ,2 ,0 4 4 6 6 7 7 6 3 4 6 7 7 4, ča e e 8 8 8 8 8 8 8 8 8 8 8 8 b ča b vo d vo d ra ra P izve P izve snost 9 1 8 1 5 4 9 9 4 9 0 5 5 dbe ,5 ,5 ,6 ,6 ,8 ,8 ,6 ,4 ,5 ,6 ,6 ,6 ,6 € € € € € € € € € € € € voča ve 4 4 4 4 4 4 4 4 4 4 4 4 4 9 1 5 6 0 1 4 4 7 4 8 0 iz ,8 ,4 ,5 ,2 ,7 ,5 ,7 ,0 ,9 ,7 ,0 ,0 76 Pra ta 4 3 6 5 9 9 6 3 3 6 5 6 4, v ta v 8 8 8 8 8 8 8 8 8 8 8 8 lite rite lite rite va va K sto K sto e ) toritev čj 4 6 2 4 8 1 7 7 6 1 6 5 2 5 T re ,6 ,5 ,7 ,7 ,8 ,9 ,7 ,5 ,6 ,7 ,7 ,7 ,7 € € € € € € € € € € € € - ta s 4 8 0 6 2 6 1 7 1 2 3 5 1 4 4 4 4 4 4 4 4 4 4 4 4 4 OS per lite povp I ( je je ,5 ,0 ,0 ,3 ,9 ,4 ,9 ,2 ,9 ,8 ,7 ,5 65 T n ja n ja 4 3 6 6 8 9 6 3 4 5 6 6 8 8 8 8 8 8 8 8 8 8 8 8 Ko 4, Kva ja n KOV ja n er OS u a a p u p zb u KA zb u a in Kop V za A V za KOV upanja 2 7 0 0 5 6 9 3 8 7 1 9 9 jan za ,6 ,5 ,7 ,7 ,8 ,8 ,6 ,5 ,5 ,7 ,7 ,6 ,6 K Z € € € € € € € € € € € € na KA 4 4 4 4 4 4 4 4 4 4 4 4 4 E 2 0 8 8 3 2 0 7 8 7 7 0 ih T ih ,7 ,8 ,1 ,1 ,9 ,1 ,0 ,0 ,9 ,4 ,3 ,0 lja NE Ljubl n n 4 3 6 6 8 9 6 3 3 7 6 6 e e 8 8 8 8 8 8 8 8 8 8 8 8 72 bujanje Ljub je je 4, n sl n sl Vz OCE a o DODA a o n p n p Z za Z za enih na 3 4 7 6 0 9 3 8 9 0 3 9 6 ,6 ,5 ,6 ,6 ,9 ,7 ,6 ,4 ,5 ,7 ,6 ,6 ,6 na € € € € € € € € € € € € posl 4 4 4 4 4 4 4 4 4 4 4 4 4 5 9 8 0 3 0 5 8 1 4 5 5 za je je ,4 ,7 ,1 ,0 ,4 ,4 ,4 ,6 ,7 ,7 ,4 ,5 n n 5 3 6 6 0 8 5 2 4 6 5 6 8 8 8 8 9 8 8 8 8 8 8 8 nje va stu va stu e e 4,69 Zna m m Reše 1 Reše 1 1 4 8 3 7 9 4 6 4 9 4 8 9 4 na ,4 ,4 ,5 ,5 ,7 ,7 ,5 ,3 ,3 ,5 ,5 ,4 ,5 € € € € € € € € € € € € 1 6 1 7 4 9 8 1 6 0 6 5 tu st 4 4 4 4 4 4 4 4 4 4 4 4 4 st nje o j o j ,1 ,8 ,8 ,5 ,7 ,7 ,3 ,2 ,1 ,0 ,7 ,0 mes n ci n ci 4 4 5 6 0 9 6 2 3 6 6 5 p a p a 8 8 8 8 9 8 8 8 8 8 8 8 o 66 Reševa rm o rm 4, fo fo Dost in Dost in ica ica nost cij ta vi ta vi o istr a o istr a op orma b b o B Dr B sto rica je o Dr sto rica Dost inf S b b 54 ska e a a o č S ska e a a o n o je 4, m n jn G r re n o je m n jn G r UE lje c vl lja j e UE lje c vl lja j e e rska ve o n rska ve o n u b sto p vp b sto p lo le le a rb rško ju ra o o o e u lo a rb rško ju ra o o 85 70 55 40 25 10 4, 4, 4, 4, 4, 4, im M S Rad Ž T K Novo L K P Nova K p im M S Rad Ž T K Novo L K P Nova K a a tičn tičn a št. a št. m 98276 84563 47822 45788 57322 47456 37822 56777 36777 72677 94643 34636 m 98276 84563 47822 45788 57322 47456 37822 56777 36777 72677 94643 34636 Damijana Keržič Naloge iz Excela NALOGA 09 Seštevek točk ROUND, IF, COUNTIF, AVERAGEIF, izračun s formulo, pogojno oblikovanje, grafikoni, filtriranje, razvrščanje Izračunali bomo končno število točk študenta, ki je sestavljena iz treh uteženih delov: točk, prejetih na izpitu, točk, zbranih iz naloge, in točk, zbranih pri kvizih. Na podlagi zbranih točk bomo študentu izračunali končno oceno. Naredili bomo tudi kratko statistiko doseženih točk na izpitu glede na oznako testa. Datoteka: Rezultati.xlsx Opis podatkov na delovnem listu ocene.  V D1 je utež za točke nalog UN, v E1 utež za točke kvizov UK, in v F1 utež za točke izpita UT.  V celicah C3:C56 je zapisana oznaka izpita ( A, B, C), ki ga je pisal študent.  V celicah D3:D56 so prejete točke študenta za naloge TNŠ.  V celicah E3:E56 so prejete točke študenta za kvize TKŠ.  V celicah F3:F56 so prejete točke študenta na izpitu TIŠ. Če študent ni pisal izpita, ima zapisano n. 1. V stolpec G, Skupaj, izračunamo končno število točk študenta: 80 % prinesejo točke izpita, 10 % točke nalog, ki jih pomnožimo še s 4, in 10 % kvizi, oziroma s formulo TŠ = UT * TIŠ + UN * TNŠ * 4 + UK * TKŠ. Rezultat prikažemo brez decimalnih mest. V primeru, da študent testa ni pisal, celico pustimo prazno. Zapišemo v eni formuli, z gnezdenimi funkcijami.  Ker se moramo odločiti, kdaj bomo izračunali skupno število točk in kdaj ne, potrebujemo funkcijo IF.  Razmislimo, kam postaviti $, da lahko kopiramo formulo še v druge celice? 2. V stolpec H, Ocena, izračunamo končno oceno študenta, ki je rezultat celoštevilskega deljenja TŠ / 10, ki mu prištejemo 1. Če je število točk večje ali enako 95, potem 1 ne prištejemo.  Za izračun celoštevilske vrednosti uporabimo funkcijo ROUNDDOWN, za pogoj IF. 3. Sredinimo vrednosti v stolpcih G in H. 30 Damijana Keržič Naloge iz Excela 4. Tabeli dodamo obrobe poljubne barve: zunanja, pod glavo tabele in med pri mkom ter oznako testa debela, notranje tanke. Med imenom in priimkom črte ni. 80% 10% 10% Ime Pri mek Test Izpit Naloge Kvizi Skupaj Ocena Aleks Voranc B 70 20 85 73 8 Aleš Tekač A 34 5 70 36 4 Ana Karničar C 56 15 75 58 6 Anita Komar B 72 15 60 70 7 Blaž Košorok C 86 25 35 82 9 Dragana Kumar A 85 10 65 79 8 5. V oblikovani tabeli naredimo kratko statistiko. Preštejemo, koliko študentov je pisalo test A, B ali C (L4:L7) in kolikšno povprečno število točk so dosegli na posameznem testu (M4:M7), koliko povprečno točk skupaj (N4:N7) ter povprečno končno oceno (O4:O7).  Uporabimo COUNTIF in AVERAGEIF.  Razmislimo, kje postaviti $, da bomo funkcijo zapisali le enkrat.  Pri kopiranju formule obdržimo oblikovanje tabele. 6. Izberemo si poljuben barvni odtenek in obarvamo celice stolpca Test, in sicer svetlo, če je v celici A, srednje močno, če je B, in temno, če je C.  Označimo vse oznake testov in uporabimo pogojno oblikovanje. Izberemo Pogojno oblikovanje>Pravila za označevanje celic>Besedilo, ki vsebuje … in nastavimo vsa tri pravila, enega za drugim. 7. Označimo celice H3:H30 z rdečo piko, če je ocena manjša ali enaka 5, in zeleno piko, če je večja od 5.  Označimo celice in izberemo Pogojno oblikovanje>Nabori ikon in dodatno uredimo pravilo oblikovanja v Pogojno oblikovanje>Upravljaj pravila. Pozor: Pri nastavitvi pogojev pravila moramo primerjati števila. 31 Damijana Keržič Naloge iz Excela 8. Izdelamo nov delovni list prikaz. Na njem pripravimo tri tabele, vsaka prikazuje rezultate posameznega testa A, B in C.  Uporabimo filter na tabeli v stolpcu Test na listu ocene in prekopiramo ustrezne dele tabele. 9. V vseh tabelah na listu prikaz študente razvrstimo po abecednem redu priimkov. 10. Podatke tabele, ki prikazujejo test A, prikažemo s kombiniranim grafikonom. Prikažemo točke Skupaj – točke ( stolpčni) in Ocene ( črtni z oznakami).  Grafikon dodatno oblikujemo: merilo obeh osi zmanjšamo, in sicer na 100 in 10.  Črtnemu grafikonu odstranimo črto in vrednosti izpišemo desno od oznake. 11. Podatke tabele, ki prikazujejo test B, prikažemo s histogramom v poljubnem predpripravljenem slogu.  Širino razreda nastavimo na 1.  Merilo navpične osi nastavimo med 0 in 3. 12. Podatke tabele, ki prikazujejo test C, prikažemo s polarnim grafikonom. Grafikon dodatno oblikujemo: imena študentov izpišemo na poljubni prelivajoči se barvi, mrežnim črtam spremenimo barvo, risalno površino grafikona oblikujemo s poljubno teksturo, območje grafikona pa s poljubnim vzorcem. 32 Damijana Keržič Naloge iz Excela 13. Dodamo nov delovni list in ga poimenujemo grafi. Sem prekopiramo podatke z lista ocene celice A2:H30, pri čemer lepimo vrednosti in oblikovanje. 14. Študente na listu grafi uredimo po vrstnem redu oznake testa (od A do C) in znotraj oznake v abecednem redu po pri mku. 15. Narišemo črtni grafikon z oznakami, ki prikazuje dosežene točke na izpitu. Oznaki podatkovne točke za študenta, ki nista pisala, naj bosta rdeče barve, brez obrobe in velikosti 8. Izrišemo navpične mrežne črte in jih premaknemo k oznakam vodoravne osi.  Oblikovanje oznake: Dvoklik na oznaki in v podoknu Oblikovanje podatkovne točke izberemo ustrezno oblikovanje.  Premik mrežnih črt: V podoknu Oblikovanje osi>Možnosti osi>Položaj osi in izberemo Na črtkicah. 33 Damijana Keržič Naloge iz Excela 16. Narišemo XY raztreseni grafikon, ki prikazuje točke skupaj (navpična os) in oceno (vodoravna os), brez študentov, ki nimata ocene. Grafikon dodatno oblikujemo: navpična os prikazuje vrednosti od 3 do 10, vodoravna os od 30 do 100. Podatkovni točki za študenta z najvišjo oceno dodamo podatkovni oblaček, ki ga premaknemo levo in dopišemo (ročno) še pri mek. Dodamo opis osi: vodoravna Ocena in navpična Skupaj – točke. V ozadje grafikona vstavimo poljubno sliko in ji po potrebi nastavimo prosojnost. Pozor: Izrisani grafikon ima vodoravno in navpično os zamenjano, zato popravimo. V Načrt grafikona>Podatki izberemo Urejanje podatkov in uredimo podatkovni niz, kjer zamenjamo podatke za vrednosti serij X in Y. 34 na 7 7 7 Oce 66 70 67 Skupaj čje vpre Izpit 68 71 69 Po lo 9 8 9 Števi Test A B C ena 8 4 6 7 9 8 9 8 9 8 7 7 5 8 5 8 6 4 9 6 8 10 6 9 8 6 Oc upaj 73 36 58 70 82 79 87 75 84 73 60 61 44 75 41 71 57 39 89 60 80 97 56 89 72 54 Sk n n 80% Izpit 70 34 56 72 86 85 94 76 85 78 64 66 46 77 39 68 58 38 95 55 79 97 57 90 74 56 izi 10% Kv 85 70 75 60 35 65 75 80 80 35 65 70 45 55 70 55 65 50 45 85 55 85 90 95 45 85 70 55 oge 5 5 5 10% al 20 15 15 25 10 10 15 20 10 10 10 15 10 25 15 10 10 25 20 10 25 14 20 15 10 N st B A B A A A A A B B B A B A A B B Te C C C C C C C C C k arič okor ak ekar jc ak jc iime anc kač mar anc zek ov of lak velbar an velbar rja of ver atar ov Pr Vor Te Karn Komar Koš Ku Karaš N Šk Smr C Kofol Vesel Žv Iskra C Bu Vor Šk Se Kran Tr Logan Furlan N Kran Ob Bre na na a a a ša dija arl tja in a a a a eks eš a ita až aga ob av stina aja anc ih iro ocene as ejc aš rban rša Ime Al Al An An Bl Dr Jak Janj Katari Katju Kl Kri Lan Luka M M M M N N Rok Sab Sanj Saš Tj U U Vanj 10 8 6 4 2 0 5 an ka Žv Lu r 4 ča ač eš rni ok Tek Al lbar a Ka 6 ve ak C An ov aša Blaž Košor k N Tj ancaM ova 8 r a N ma nj Ku ragana Ja D ena 6 Oc lbarve t C C Kranjc Rok Test A aj ja 0 Tes 7 80 60 40 20 100 ina Skup Klavdi Kofol ist lak Kr 9 š a Ob ra kob anc Urš Ka Ja of el iro Vor Šk 8 ra M aja rina Isk M ta Lan Ves Ka 10 lan ša Fur Sa 0 80 60 40 20 100 10 8 9 7 6 8 6 4 5 6 8 7 9 9 4 8 8 8 5 6 9 8 8 9 7 6 Ocena Ocena Ocena paj paj paj 97 75 87 60 60 79 56 36 44 54 71 70 89 89 39 80 73 73 41 58 82 75 72 84 61 57 Sku Sku Sku t t t Izpi 97 77 94 64 55 85 57 34 46 Izpi 56 68 72 90 95 38 79 70 Izpi 78 39 56 86 76 74 85 66 58 Kvizi 95 70 75 70 55 65 45 70 55 Kvizi 55 65 60 85 85 45 85 85 Kvizi 65 55 75 35 80 70 80 45 50 loge 25 15 10 5 25 10 14 5 5 loge 10 25 15 20 10 10 20 20 loge 10 10 15 25 15 15 20 10 15 Na Na Na A A A A A A A A A Test Test B B B B B B B B Test C C C C C C C C C r ek š c ar č ek ar c r nc ek ča k nc im ak ak rlan ol nj im rja nj ver ta im lbar lbar Pri Fu Iskra Kara Kof Kra Kum Nov Teka Žvan Pri Brezek Bu Kom Kra Se Škof Tra Vora Pri Cve Cve Karni Košorok Nov Obla Škof Vesel Vora z na rl n a ika a rina e ša gana a aja kob š ka e iha ta stj bina ks e anca až nj n iro pr ra Im Sa M Ja Kristi Rok D Tjaša Ale Lu Im Vanj M Ani Urba Nejc Na Sa Ale Im Klavdija M Ana Bl Ja Urša Kata La M r a k re Sm an g Lo 10 nc Vora l e s Ve LAN f o Šk ; 9710 9 k la Ob FUR k a ov N rok Košo r ča rni Ka 8 r a b l e v C r a b l e v C nc Vora 7 r a t ra T f o Šk t r ve Se cenaO njc a Kr Izpi 6 r a Kom rja u B k ze e r B n a v Ž 5 ač ek T k a ov N r ma Ku 4 njc a Kr ofol K š ra Ka ra k s I 3 n a l Fur 90 80 70 60 50 40 30 100 očke t - j a p u Sk 0 80 60 40 20 120 100 10 8 9 7 6 8 6 4 5 6 8 7 9 9 4 8 8 5 8 6 9 8 8 9 7 6 Ocena paj 97 75 87 60 60 79 56 36 44 54 71 70 89 89 39 80 73 41 73 58 82 75 72 84 61 57 Sku t n n Izpi 97 77 94 64 55 85 57 34 46 56 68 72 90 95 38 79 70 39 78 56 86 76 74 85 66 58 Kvizi 95 70 75 70 55 65 45 70 55 55 65 60 85 85 45 85 85 55 65 75 35 80 70 80 45 50 90 35 loge 25 15 10 5 25 10 14 5 5 10 25 15 20 10 10 20 20 10 10 15 25 15 15 20 10 15 10 10 Na A A A A A A A A A Test B B B B B B B B C C C C C C C C C r r ek š c ar č ar c r nc ča k nc im ak ak rlan ol nj rja nj ver ta lbar lbar reka Pri Fu Iskra Kara Kof Kra Kum Nov Teka Žvan Brezek Bu Kom Kra Se Škof Tra Vora Cve Cve Karni Košorok Nov Obla Škof Vesel Vora Logan Sm na a rl n a rina a ša e ša gana a aja kob š ka iha ta stj bina ks anca až nj n iro nj grafi ra Im Sa M Ja Kristi Rok D Tjaša Ale Lu Vanj M Ani Urba Nejc Na Sa Ale M Klavdija Ana Bl Ja Urša Kata La M Sa Katju Damijana Keržič Naloge iz Excela NALOGA 10 Fakultetni urnik COUNTA, COUNTIF, AVERAGEIF, IF, izračun s formulo, pogojno oblikovanje Izdelali bomo kratko statistiko tedenskega urnika in prisotnosti študentov pri predmetih. Datoteka: urnik.xlsx Opis podatkov na delovnem listu predmetnik.  V celicah A2:E12 je urnik predavanj v tednu.  V celicah G4:J12 je število prisotnih študentov pri uri predmeta (UP) po dnevih (D) ŠD,UP. 1. V celice B13:E13 zapišemo število ur predavanj v dnevu.  Preštejemo neprazne celice. 2. V celice B17:E22 izpišemo število ur posameznega predmeta (P) na dan ŠUD,P.  Uporabimo funkcijo COUNTIF. Razmislimo še o pravilni postavitvi absolutnih naslovov ($) ter ohranitvi obrob tabele (polnjenje brez oblikovanja). 3. V celicah B25:E26 naj se v celicah izpiše preveč, če je skupno število ur predavanj na posamezen dan večje kot meja, zapisana v A25 oziroma A26, sicer naj se izpiše v redu.  Uporabimo funkcijo IF. Potrebujemo tudi absolutne naslove ($) za univerzalno zapisano funkcijo. 4. V celice G17:J22 izračunamo povprečno število navzočih študentov na uro za posamezen predmet v dnevu POVD,P = ΣUP ŠD,UP / ŠUD,P (z besedo: vsota prisotnih študentov pri vseh urah posameznega predmeta na dan/število ur tega predmeta v tem dnevu). Prikažemo brez decimalnih mest.  Uporabimo AVERAGEIF in absolutne naslove. Pozor: Kljub pravilno zapisani formuli se izpiše #DEL/0! . Napaka nastane, ker na določen dan ni ur predavanj pri posameznem predmetu. Če želimo, lahko te napačne vrednosti izbrišemo, lahko pa težavo rešimo tako, da postavimo pogoj, da v primeru, ko je število ur enako 0, v celico ne izpišemo ničesar ("" ), sicer naj se izračuna rezultat. Uporabimo funkcijo IF. 5. Celicam G4:J12 dodamo oznake. Če je število prisotnih študentov >35, potem izrišemo zelen trikotnik. Če je število med vključno 30 in 35, rumeno črtico. Če pa je število prisotnih študentov <30, rdeč trikotnik.  Podobno kot v NALOGA 06 uredimo ustrezno pogojno oblikovanje. 38 40 40 29 28 15 14 20 19 29 20 40 na da ntaemd ČET rei p 15 10 20 20 18 zn 13 19 e v m ton sa e o d p tu a š SRE zri ihtn 26 26 20 20 20 33 32 u 26 33 20 so v v ri to p ned vilo tu š Šte R ih O tn T sori 55 12 30 30 51 23 22 p 23 30 34 51 vilote šočnre N vp PO Po 8 0 2 2 0 2 0 cije cije iza iza o o n n a a stv stv d d rg rg ika ika o o vo vo m m o o ja ja o o n n č č ri ri n n u u o o o o tistika tistika ve ve e e re re ČET T T Ek Ek Rač Rač Sta Sta n p p a 5 d 2 0 0 3 0 0 a nta tika tika tika e jie zik I zik I a a a md m u u j je j je rm rm rm ri d d u u fo fo fo re SRE T T In In In p v p v re v re a 7 g 0 0 2 0 2 3 e iten DAN cije cije zn e iza iza e m m re vo vo vo n n a a b k ra ra ra rg rg sa o o p p p o o v v v r p Pre Urni ja ja č u R tistika tistika d d d u o o o ri ri o o ve d O e e vilo re T Sta Sta Uv Uv Uv T T p v re 7 Šte 2 2 0 2 0 1 vo tika tika ika ika ra a a m m p o o v zik I zik I č u N rm rm n n d ve d fo fo o o o j je j je u u re PO In In Ek Ek Uv T T p v re 0 0 0 0 0 0 0 0 0 5 7 :0 :0 :0 :0 :0 :0 :0 :0 :0 8 9 0 1 2 3 4 5 6 0 0 1 1 1 1 1 1 1 cije izan r a vo u ika tika rg ra AJ o p zik I m a o v n jari d j je o tistika rm o o u fo e Ura SKUP T Ek Sta In T Uv Damijana Keržič Naloge iz Excela NALOGA 11 Obisk bazena VLOOKUP, IF, COUNTIF, pogojno oblikovanje, grafikon, priprava strani za tisk Zbranih je nekaj podatkov o obisku kopališča v izbranem časovnem obdobju. Izračunali bomo skupne vrednosti prodanih vstopnic, ločeno po storitvah, ki jih kopališče ponuja. Datoteka bazen.xlsx Delovni zvezek vsebuje dva delovna lista obisk in šifrant. Opis podatkov na delovnem listu obisk.  V celicah B3:F3 so šifre posamezne storitve.  V celicah A4:A13 so datumi obravnavanih dni.  V celicah B4:F13 je število obiskovalcev v dnevu (D) za posamezno storitev (S) OD,S. Opis podatkov na delovnem listu šifrant.  Za vsako šifro storitve je podan opis storitve ter vstopnina (VS). 1. V celice B2:F2 glede na šifro storitve izpišemo ime storitve. Izpišemo v več vrsticah in spremenimo smer pisanja. 2. Vrednosti v celicah B4:F13 zapišemo krepko rdeče, če je vrednost <10. 3. V G4:G13 prve tabele na eno decimalno mesto natančno izračunamo povprečno število obiskovalcev v dnevu PD. Izračun prikažemo z enim decimalnim mestom. 4. V H3:H13 izpišemo +, če je bilo število obiskovalcev za storitev savna + bazen večje od povprečja v dnevu, sicer --. 5. V B16:G17 izračunamo, koliko je bilo dni, ko je bilo število obiskovalcev za posamezno storitev <10 oziramo >=10.  Uporabimo COUNTIF. Pravilno zapisana formula zahteva tri $. 6. Celice A20:A29 zapolnimo z zaporednimi datumi, enako kot v prvi tabeli. 40 Damijana Keržič Naloge iz Excela 7. V celice B19:F19 izpišemo vrednost vstopnine za storitev zapisano v vrstici 3 (VS). Vrednosti izpišemo z znakom € pred številko.  Izpis oblike poiščemo v spustnem seznamu v pogovornem oknu Oblikuj celice>Številke. 8. V B20:F29 izračunamo zaslužek v dnevu za storitev po formuli: ZD,S = OD,S * VS + (OD,S - PD) * VS.  Pravilno zapisana formula zahteva tri $. 9. Narišemo palični grafikon, ki prikazuje število kupljenih vstopnic dopoldanskega in celodnevnega kopanja za vse zapisane dni. Oblikujemo grafikon: največja vrednost na vodoravni osi je 20 z enoto prikaza 2, oblika zapisa datuma naj bo kot v rešitvah izpisana na poljubni barvni podlagi, dodamo oznake podatkov, zmanjšamo širino vrzeli, zbrišemo merilo vodoravne osi in izberemo poljuben barvni slog. 41 9 je opan k 8 no 5 15 2 nev 13 11 7 5 celod 102 12 20 o insk 101 15 14 14 danol 9 9 8 8 7 5 -dopski 20 Obi 2020 2020 2020 2020 2020 2020 2020 2020 2020 nij 20 junij junij junij junij junij junij junij junij junij . ju 08. 01. a, 06 k, 05. da, 10. ek, 09. da, 03. ek, 02. ot sre tor delja, 07. trtek, 04. deljek, pete sre tor deljek, ne sob če pone pone / +-- -- -- + -- + -- -- + -- + JE 6,0 7,0 9,0 7,0 9,0 7,0 7,0 8,0 8,0 6,0 EČ POVPR n da 1 2 3 4 5 11 1 3 2 3 9 1 l ce a vn a s 104 - 22,00 88,00 66,00 66,00 22,00 22,00 44,00 88,00 330,00 110,00 € -€ -€ -€ € € € -€ -€ -€ € n ze ba 5 5 11 3 10 3 3 13 4 6 7 3 + a vn a s 105 - a 20,00 80,00 60,00 20,00 20,00 20,00 260,00 220,00 360,00 120,00 € € € € išč -€ € -€ -€ € € € e j n pa o k 5 8 12 5 9 11 2 15 13 7 6 4 o vn dne lo e c 102 opal 12,00 48,00 36,00 36,00 96,00 108,00 180,00 108,00 180,00 264,00 216,00 € € € € € € € -€ € € € uri 2 a vn a s isk k 8 6 2 3 2 4 6 4 103 13 11 12 14 6,00 Ob 60,00 30,00 90,00 30,00 6,00 24,00 12,00 102,00 102,00 120,00 € € € € € -€ -€ € -€ € € e j n pa o k o 9 14 5 14 20 8 15 9 7 8 6 4 k s n lda po do 101 dnilo 5,00 5,00 vi 60,00 45,00 50,00 30,00 50,00 105,00 105,00 155,00 115,00 šte € € € € € € € € € € € 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 iskov pnice 1.06. 2.06. 3.06. 4.06. 5.06. 6.06. 7.06. 8.06. 9.06. to 10.06. ob 1.06. 2.06. 3.06. 4.06. 5.06. 6.06. 7.06. 8.06. 9.06. lo 10.06. vi na vs šte <10 >=10 ce Damijana Keržič Naloge iz Excela NALOGA 12 Slovenske občine VLOOKUP, IF, OR, AND, NOT, COUNT, COUNTA, COUNTIF, SUMIF, AVERAGEIF, XY raztreseni grafikon Izdelali bomo osnovno statistiko slovenskih občin. Datoteka: SI_obcine.xlsx Delovni zvezek vsebuje tri delovne liste analiza, seznam in prebivalstvo. Opis podatkov na delovnem listu analiza.  Delovni list ima vpisane besede, ki predstavljajo glavo tabele. Opis podatkov na delovnem listu seznam.  Delovni list vsebuje dve podatkovni tabeli.  Večja tabela (A1:G215) vsebuje podatke o slovenskih občinah (stanje iz l. 2011). Prva dva stolpca vsebujeta kodi regije ( regija_id) in občine ( občina_id). Za vsako občino so zapisani podatki o površini, regiji in pokrajini, v katero občina sodi, številu naselij v občini ter datum njene ustanovitve.  Manjša tabela (I2:J14) vsebuje seznam slovenskih regij, v prvem stolpcu je podatek regija_id, v drugem pa ime pripadajoče regije. Opis podatkov na delovnem listu prebivalstvo.  Tabela vsebuje podatke o številu prebivalcev v občinah. Občina je podana s podatkom občina_id, ki ji sledijo trije stolpci, število prebivalcev, število moških in število žensk. 1. Pripravimo in oblikujemo podatke na delovnem listu seznam.  Stolpca B in C prestavimo pred stolpec A.  Besedilo celice B1 sredinimo preko celic A1:G1.  Oblikovanje celice C4 prenesemo na I2:I14. Uporabimo Preslikovalnik oblik.  Samodejno širimo drugi stolpec J in izrišemo obrobe celic J2:J14. 2. Pripravimo in oblikujemo vrednosti na delovnem listu analiza.  Prva vrstica, A1:K1, je enako oblikovana kot A1. Kvadratni kilometer zapišemo s simbolom km2 tako, da izberemo v pogovornem oknu Oblikuj Celice>Pisava Nadpisano.  Stolpec A, od A2 dalje, zapolnimo z vrednostmi občina_id, ki jih kopiramo z lista seznam, in jih uredimo po velikosti od najmanjše do največje. 43 Damijana Keržič Naloge iz Excela 3. Na delovnem listu analiza zapolnimo polja tabele od stolpca B do G s podatki občine, ki ustreza vrednosti občina_id v stolpcu A.  Uporabimo VLOOKUP.  Vrednosti za stolpce B, D in E pridobimo z lista seznam.  Vrednosti za stolpce F in G pridobimo z lista prebivalstvo.  Prav tako se podatek za stolpec C nahaja na listu seznam, vendar do njega ne moremo v enem koraku. Najprej namreč poiščemo podatek regija_id, v kateri je občina, nato v seznamu regij še ustrezno ime te regije. Nalogo rešimo z gnezdenjem dveh funkcij VLOOKUP. V seznamu regij zunanja funkcija z znanim regija_id poišče ime regije v seznamu regij. Notranja funkcija v seznamu občin poišče podatek regija_id. =VLOOKUP(VLOOKUP(A2;seznam!A$4:G$215;3;false);seznam!I$3:J$14;2;false) 4. Samodejno razširimo stolpca B in C. 5. V stolpec H vpišemo M, če je število moških večje ali enako številu žensk v občini, in Ž, če je žensk več.  Uporabimo IF. 6. V K2 vpišemo število vseh občin.  Uporabimo COUNT ali COUNTA (odvisno od stolpca, kjer bomo preštevali celice). 7. Preštejemo, koliko je občin, kjer je moških prebivalcev več kot žensk, in koliko, kjer je žensk več.  Uporabimo COUNTIF in dva $. 8. Dodamo nov list in ga poimenujemo analiza-pokrajine. V A1:D1 zaporedno v celice vpišemo Pokrajina, Občine, Površina in Naselja. 9. Stolpec A na listu analiza-pokrajine zapolnimo z imeni pokrajin.  Z lista seznam prekopiramo celice G4:G215 z imeni pokrajin in odstranimo podvojene vrednosti. Ukaz najdemo na Podatki>Podatkovna orodja . 10. V stolpec Občine vpišemo število občin v pokrajini.  Uporabimo COUNTIF in dva $. 44 Damijana Keržič Naloge iz Excela 11. V stolpca Površina in Naselja seštejemo, koliko je skupna površina občin oziroma koliko je naselij v pokrajini.  Uporabimo SUMIF. Za univerzalno zapisano funkcijo, ki jo bomo le še kopirali v vse celice, moramo uporabiti sedem $. 12. Tabelo s podatki A1:D8 poljubno oblikujemo. 13. Narišemo grafikon XY raztreseni, ki prikazuje razmerje med številom naselij (vodoravna os) in površino (navpična os) v pokrajinah. Pozor: V grafikonu je treba zamenjati podatke obeh osi, kot je opisano v NALOGA 06.  Podatkovne točke obarvamo z različnimi barvami: Oblikovanje nizov podatkov>Zaznamek in izberemo Spreminjaj barve po točkah.  Grafikonu dodamo logaritmično trendno črto rdeče barve.  Vsako podatkovno točko opišemo z imenom pokrajine tako, da dodamo oznake in v Oblikovanje oznake podatkov>Možnosti za nalepke izberemo Vrednosti iz obsega. 45 analiza površina število število število občina_id ime občine regija km2 naselij moških žensk Ž/M Število 001 Ajdovščina GORIŠKA REGIJA 245,2 45 9600 9416 M vse 212 002 Beltinci POMURSKA REGIJA 62,3 8 4147 4205 Ž Ž 121 003 Bled GORENJSKA REGIJA 72,3 10 3995 4196 Ž M 91 004 Bohinj GORENJSKA REGIJA 333,7 24 2586 2616 Ž 005 Borovnica OSREDNJESLOVENSKA REGIJA 42,3 12 1998 1994 M 006 Bovec GORIŠKA REGIJA 367,3 14 1564 1636 Ž 007 Brda GORIŠKA REGIJA 72 45 2856 2907 Ž 008 Brezovica OSREDNJESLOVENSKA REGIJA 91,2 16 5687 5844 Ž 009 Brežice SPODNJEPOSAVSKA REGIJA 268,1 109 11932 12369 Ž 010 Tišina POMURSKA REGIJA 38,8 12 2033 2099 Ž 011 Celje SAVINJSKA REGIJA 94,9 40 23921 24761 Ž 012 Cerklje na Gorenjskem GORENJSKA REGIJA 78 30 3579 3650 Ž 013 Cerknica NOTRANJSKO-KRAŠKA REGIJA 241,3 65 5737 5613 M 014 Cerkno GORIŠKA REGIJA 131,6 30 2455 2344 M 015 Črenšovci POMURSKA REGIJA 33,7 6 2057 2052 M 016 Črna na Koroškem KOROŠKA REGIJA 156 9 1788 1714 M 017 Črnomelj JUGOVZHODNO SLOVENSKA REGIJA 339,7 119 7412 7277 M 018 Destrnik PODRAVSKA REGIJA 34,4 17 1301 1299 M 019 Divača OBALNO-KRAŠKA REGIJA 147,8 31 2042 1865 M 020 Dobrepolje OSREDNJESLOVENSKA REGIJA 103 24 2033 1925 M 021 Dobrova - Polhov Gradec OSREDNJESLOVENSKA REGIJA 117,5 33 3735 3730 M 022 Dol pri Ljubljani OSREDNJESLOVENSKA REGIJA 33,3 19 2805 2811 Ž 023 Domžale OSREDNJESLOVENSKA REGIJA 72,3 51 16909 17437 Ž 024 Dornava PODRAVSKA REGIJA 28,4 12 1480 1462 M 025 Dravograd KOROŠKA REGIJA 105 24 4553 4503 M 026 Duplek PODRAVSKA REGIJA 39,4 10 3316 3400 Ž 027 Gorenja vas - Poljane GORENJSKA REGIJA 153,3 73 3626 3676 Ž 028 Gorišnica PODRAVSKA REGIJA 61,2 11 2021 2022 Ž 029 Gornja Radgona POMURSKA REGIJA 73,4 30 4249 4368 Ž 030 Gornji Grad SAVINJSKA REGIJA 90,1 7 1294 1370 Ž 031 Gornji Petrovci POMURSKA REGIJA 66,8 14 1030 1115 Ž 032 Grosuplje OSREDNJESLOVENSKA REGIJA 133,8 66 9957 9669 M 033 Šalovci POMURSKA REGIJA 58,2 6 739 784 Ž 034 Hrastnik ZASAVSKA REGIJA 58,6 19 4796 5037 Ž 035 Hrpelje - Kozina OBALNO-KRAŠKA REGIJA 195 38 2176 2110 M 036 Idrija GORIŠKA REGIJA 293,7 35 5975 5985 Ž 037 Ig OSREDNJESLOVENSKA REGIJA 98,8 24 3518 3456 M 038 Ilirska Bistrica NOTRANJSKO-KRAŠKA REGIJA 480 63 6939 6907 M 039 Ivančna Gorica OSREDNJESLOVENSKA REGIJA 227 137 7969 7812 M 040 Izola/Isola OBALNO-KRAŠKA REGIJA 28,6 137 7955 7996 Ž 041 Jesenice GORENJSKA REGIJA 75,8 13 10733 10700 M 042 Juršinci PODRAVSKA REGIJA 36,3 13 1227 1184 M 043 Kamnik OSREDNJESLOVENSKA REGIJA 265,6 102 14390 14856 Ž 044 Kanal GORIŠKA REGIJA 146,5 22 2865 2798 M 045 Kidričevo PODRAVSKA REGIJA 71,5 18 3178 3449 Ž 046 Kobarid GORIŠKA REGIJA 192,7 33 2101 2101 M 047 Kobilje POMURSKA REGIJA 19,7 1 301 304 Ž 048 Kočevje JUGOVZHODNO SLOVENSKA REGIJA 563,7 68 8097 8340 Ž 049 Komen OBALNO-KRAŠKA REGIJA 102,7 35 1755 1800 Ž 050 Koper/Capodistria OBALNO-KRAŠKA REGIJA 303,2 104 26412 26743 Ž 051 Kozje SAVINJSKA REGIJA 56,7 23 1597 1594 M 052 Kranj GORENJSKA REGIJA 89,7 49 27379 28053 Ž 053 Kranjska Gora GORENJSKA REGIJA 256,3 10 2648 2690 Ž 054 Krško SPODNJEPOSAVSKA REGIJA 286,5 157 13297 12689 M 055 Kungota PODRAVSKA REGIJA 49 19 2388 2404 Ž 056 Kuzma POMURSKA REGIJA 22,9 5 769 811 Ž 057 Laško SAVINJSKA REGIJA 197,5 85 6603 6854 Ž 058 Lenart PODRAVSKA REGIJA 62 22 4065 4092 Ž 059 Lendava/Lendva POMURSKA REGIJA 123 23 5256 5625 Ž 060 Litija OSREDNJESLOVENSKA REGIJA 221 108 7477 7472 M 061 Ljubljana OSREDNJESLOVENSKA REGIJA 275 38 134731 145547 Ž 062 Ljubno SAVINJSKA REGIJA 78,9 9 1323 1346 Ž 063 Ljutomer POMURSKA REGIJA 107,2 44 5755 5981 Ž 064 Logatec OSREDNJESLOVENSKA REGIJA 173,1 19 6862 6713 M 065 Loška dolina NOTRANJSKO-KRAŠKA REGIJA 166,8 21 1990 1897 M 066 Loški Potok JUGOVZHODNO SLOVENSKA REGIJA 134,5 17 975 976 Ž 067 Luče SAVINJSKA REGIJA 109,5 7 763 756 M 068 Lukovica OSREDNJESLOVENSKA REGIJA 74,9 66 2808 2714 M 069 Majšperk PODRAVSKA REGIJA 72,8 26 2025 1994 M 070 Maribor PODRAVSKA REGIJA 147,5 33 54118 56828 Ž 071 Medvode OSREDNJESLOVENSKA REGIJA 77,6 32 7753 7955 Ž analiza 072 Mengeš OSREDNJESLOVENSKA REGIJA 22,5 4 3556 3851 Ž 073 Metlika JUGOVZHODNO SLOVENSKA REGIJA 108,9 60 4207 4179 M 074 Mežica KOROŠKA REGIJA 26,4 6 1834 1806 M 075 Miren - Kostanjevica GORIŠKA REGIJA 62,8 16 2409 2395 M 076 Mislinja KOROŠKA REGIJA 112,2 11 2362 2307 M 077 Moravče OSREDNJESLOVENSKA REGIJA 61,4 50 2533 2571 Ž 078 Moravske Toplice POMURSKA REGIJA 144,5 28 2959 3002 Ž 079 Mozirje SAVINJSKA REGIJA 54 8 2017 2086 Ž 080 Murska Sobota POMURSKA REGIJA 64,4 12 9085 10135 Ž 081 Muta KOROŠKA REGIJA 38,8 6 1735 1751 Ž 082 Naklo GORENJSKA REGIJA 28,3 13 2639 2666 Ž 083 Nazarje SAVINJSKA REGIJA 42,3 15 1323 1289 M 084 Nova Gorica GORIŠKA REGIJA 280 43 15695 16237 Ž 085 Novo mesto JUGOVZHODNO SLOVENSKA REGIJA 236 99 18133 18262 Ž 086 Odranci POMURSKA REGIJA 6,9 1 832 820 M 087 Ormož PODRAVSKA REGIJA 142 60 6227 6333 Ž 088 Osilnica JUGOVZHODNO SLOVENSKA REGIJA 36,2 19 230 183 M 089 Pesnica PODRAVSKA REGIJA 75,8 30 3745 3778 Ž 090 Piran/Pirano OBALNO-KRAŠKA REGIJA 44,6 11 8650 9025 Ž 091 Pivka NOTRANJSKO-KRAŠKA REGIJA 223,6 11 3048 2945 M 092 Podčetrtek SAVINJSKA REGIJA 60,6 26 1680 1659 M 093 Podvelka KOROŠKA REGIJA 103,9 9 1298 1203 M 094 Postojna NOTRANJSKO-KRAŠKA REGIJA 269,9 40 7875 7882 Ž 095 Preddvor GORENJSKA REGIJA 87 14 1724 1843 Ž 096 Ptuj PODRAVSKA REGIJA 66,7 10 11441 12084 Ž 097 Puconci POMURSKA REGIJA 107,6 23 2961 3145 Ž 098 Rače - Fram PODRAVSKA REGIJA 51,2 13 3460 3528 Ž 099 Radeče SAVINJSKA REGIJA 52 23 2197 2253 Ž 100 Radenci POMURSKA REGIJA 34,1 22 2555 2675 Ž 101 Radlje ob Dravi KOROŠKA REGIJA 93,9 14 3193 3107 M 102 Radovljica GORENJSKA REGIJA 118,7 52 9158 9791 Ž 103 Ravne na Koroškem KOROŠKA REGIJA 63,4 16 5774 5750 M 104 Ribnica JUGOVZHODNO SLOVENSKA REGIJA 153,6 63 4713 4626 M 105 Rogašovci POMURSKA REGIJA 40,1 11 1553 1638 Ž 106 Rogaška Slatina SAVINJSKA REGIJA 71,5 41 5543 5477 M 107 Rogatec SAVINJSKA REGIJA 39,6 9 1567 1581 Ž 108 Ruše PODRAVSKA REGIJA 60,8 7 3671 3586 M 109 Semič JUGOVZHODNO SLOVENSKA REGIJA 146,7 46 1956 1897 M 110 Sevnica SPODNJEPOSAVSKA REGIJA 272,2 114 8825 8687 M 111 Sežana OBALNO-KRAŠKA REGIJA 217,4 64 6581 6467 M 112 Slovenj Gradec KOROŠKA REGIJA 173,7 22 8383 8445 Ž 113 Slovenska Bistrica PODRAVSKA REGIJA 260 77 12493 12704 Ž 114 Slovenske Konjice SAVINJSKA REGIJA 97,8 58 7287 7165 M 115 Starše PODRAVSKA REGIJA 34 8 1998 2101 Ž 116 Sveti Jurij ob Ščavnici POMURSKA REGIJA 51,3 27 1437 1457 Ž 117 Šenčur GORENJSKA REGIJA 40 12 4218 4277 Ž 118 Šentilj PODRAVSKA REGIJA 65 22 4186 4243 Ž 119 Šentjernej JUGOVZHODNO SLOVENSKA REGIJA 96 58 3462 3436 M 120 Šentjur SAVINJSKA REGIJA 222,3 108 9433 9563 Ž 121 Škocjan JUGOVZHODNO SLOVENSKA REGIJA 60,4 39 1603 1590 M 122 Škofja Loka GORENJSKA REGIJA 145 62 11223 11666 Ž 123 Škofljica OSREDNJESLOVENSKA REGIJA 43,3 19 4735 4832 Ž 124 Šmarje pri Jelšah SAVINJSKA REGIJA 107,7 77 5041 5230 Ž 125 Šmartno ob Paki SAVINJSKA REGIJA 94,9 54 1617 1603 M 126 Šoštanj SAVINJSKA REGIJA 95,6 11 4510 4248 M 127 Štore SAVINJSKA REGIJA 28,1 12 2180 2115 M 128 Tolmin GORIŠKA REGIJA 381,5 72 5834 5812 M 129 Trbovlje ZASAVSKA REGIJA 57,8 16 8292 8646 Ž 130 Trebnje JUGOVZHODNO SLOVENSKA REGIJA 194,6 131 6201 5918 M 131 Tržič GORENJSKA REGIJA 155,4 35 7508 7666 Ž 132 Turnišče POMURSKA REGIJA 23,8 4 1630 1701 Ž 133 Velenje SAVINJSKA REGIJA 83,5 26 16715 16147 M 134 Velike Lašče OSREDNJESLOVENSKA REGIJA 103,2 88 2172 2054 M 135 Videm PODRAVSKA REGIJA 80,2 31 2874 2769 M 136 Vipava GORIŠKA REGIJA 107,4 20 2766 2811 Ž 137 Vitanje SAVINJSKA REGIJA 59,4 8 1177 1096 M 138 Vodice OSREDNJESLOVENSKA REGIJA 31,4 16 2348 2387 Ž 139 Vojnik SAVINJSKA REGIJA 75,3 55 4214 4311 Ž 140 Vrhnika OSREDNJESLOVENSKA REGIJA 126,3 25 8143 8334 Ž 141 Vuzenica KOROŠKA REGIJA 50,1 5 1356 1356 M 142 Zagorje ob Savi ZASAVSKA REGIJA 147,1 74 8343 8661 Ž 143 Zavrč PODRAVSKA REGIJA 19,3 9 963 751 M 144 Zreče SAVINJSKA REGIJA 67 27 3281 3126 M analiza 146 Železniki GORENJSKA REGIJA 164,8 30 3493 3314 M 147 Žiri GORENJSKA REGIJA 49,2 18 2416 2513 Ž 148 Benedikt PODRAVSKA REGIJA 24,1 14 1239 1193 M 149 Bistrica ob Sotli SAVINJSKA REGIJA 31,1 11 701 709 Ž 150 Bloke NOTRANJSKO-KRAŠKA REGIJA 75,1 45 843 747 M 151 Braslovče SAVINJSKA REGIJA 54,9 22 2708 2709 Ž 152 Cankova POMURSKA REGIJA 30,6 8 917 979 Ž 153 Cerkvenjak PODRAVSKA REGIJA 24,5 15 1031 999 M 154 Dobje SAVINJSKA REGIJA 17,5 13 479 492 Ž 155 Dobrna SAVINJSKA REGIJA 31,7 11 1119 1068 M 156 Dobrovnik/Dobronak POMURSKA REGIJA 31,1 3 639 699 Ž 157 Dolenjske Toplice JUGOVZHODNO SLOVENSKA REGIJA 110,2 29 1692 1703 Ž 158 Grad POMURSKA REGIJA 37,4 7 1087 1117 Ž 159 Hajdina PODRAVSKA REGIJA 21,6 7 1848 1891 Ž 160 Hoče - Slivnica PODRAVSKA REGIJA 53,7 13 5525 5523 M 161 Hodoš/Hodos POMURSKA REGIJA 18,1 2 184 198 Ž 162 Horjul OSREDNJESLOVENSKA REGIJA 32,5 9 1430 1463 Ž 163 Jezersko GORENJSKA REGIJA 68,8 2 316 319 Ž 164 Komenda OSREDNJESLOVENSKA REGIJA 24,1 14 2857 2868 Ž 165 Kostel JUGOVZHODNO SLOVENSKA REGIJA 62,3 52 334 308 M 166 Križevci POMURSKA REGIJA 46,2 16 1927 1851 M 167 Lovrenc na Pohorju PODRAVSKA REGIJA 84,4 7 1579 1551 M 168 Markovci PODRAVSKA REGIJA 29,8 9 1968 2038 Ž 169 Miklavž na Dravskem polju PODRAVSKA REGIJA 12,5 4 3167 3258 Ž 170 Mirna Peč JUGOVZHODNO SLOVENSKA REGIJA 48 28 1440 1404 M 171 Oplotnica PODRAVSKA REGIJA 33,2 21 2027 1990 M 172 Podlehnik PODRAVSKA REGIJA 46 13 947 952 Ž 173 Polzela SAVINJSKA REGIJA 34 9 2930 3092 Ž 174 Prebold SAVINJSKA REGIJA 40,9 8 2490 2507 Ž 175 Prevalje KOROŠKA REGIJA 58,1 12 3370 3475 Ž 176 Razkrižje POMURSKA REGIJA 9,8 6 668 674 Ž 177 Ribnica na Pohorju KOROŠKA REGIJA 59,3 6 609 595 M 178 Selnica ob Dravi PODRAVSKA REGIJA 64,5 14 2288 2264 M 179 Sodražica JUGOVZHODNO SLOVENSKA REGIJA 49,5 22 1129 1064 M 180 Solčava SAVINJSKA REGIJA 102,8 4 253 265 Ž 181 Sveta Ana PODRAVSKA REGIJA 37,2 11 1164 1180 Ž 182 Sveti Andraž v Slov. goricah PODRAVSKA REGIJA 17,6 7 588 564 M 183 Šempeter - Vrtojba GORIŠKA REGIJA 15 2 3175 3205 Ž 184 Tabor SAVINJSKA REGIJA 34,8 7 821 804 M 185 Trnovska vas PODRAVSKA REGIJA 22,9 7 691 667 M 186 Trzin OSREDNJESLOVENSKA REGIJA 8,6 1 1885 1949 Ž 187 Velika Polana POMURSKA REGIJA 18,7 3 730 751 Ž 188 Veržej POMURSKA REGIJA 12 3 638 644 Ž 189 Vransko SAVINJSKA REGIJA 53,3 16 1294 1365 Ž 190 Žalec SAVINJSKA REGIJA 117,1 38 10707 10814 Ž 191 Žetale PODRAVSKA REGIJA 38 5 704 641 M 192 Žirovnica GORENJSKA REGIJA 46,2 10 2209 2215 Ž 193 Žužemberk JUGOVZHODNO SLOVENSKA REGIJA 164,3 18 2305 2269 M 194 Šmartno pri Litiji OSREDNJESLOVENSKA REGIJA 18,2 10 2815 2722 M 195 Apače POMURSKA REGIJA 53,5 21 1778 1827 Ž 196 Cirkulane PODRAVSKA REGIJA 32 13 1161 1137 M 197 Kostanjevica na Krki SPODNJEPOSAVSKA REGIJA 151 28 1205 1211 Ž 198 Makole PODRAVSKA REGIJA 36,9 13 1035 1020 M 199 Mokronog - Trebelno JUGOVZHODNO SLOVENSKA REGIJA 71 43 1485 1498 Ž 200 Poljčane PODRAVSKA REGIJA 37,5 18 2175 2399 Ž 201 Renče - Vogrsko GORIŠKA REGIJA 30 6 2164 2124 M 202 Središče ob Dravi PODRAVSKA REGIJA 33 5 1047 1077 Ž 203 Straža JUGOVZHODNO SLOVENSKA REGIJA 28,5 11 1919 1946 Ž 204 Sveta Trojica v Slov. goricah PODRAVSKA REGIJA 26 8 1066 1045 M 205 Sveti Tomaž PODRAVSKA REGIJA 38 17 1029 1073 Ž 206 Šmarješke Toplice JUGOVZHODNO SLOVENSKA REGIJA 34 24 1572 1629 Ž 207 Gorje GORENJSKA REGIJA 116,2 12 1425 1444 Ž 208 Log - Dragomer OSREDNJESLOVENSKA REGIJA 11 3 1840 1800 M 209 Rečica ob Savinji SAVINJSKA REGIJA 30,1 12 1184 1121 M 210 Sveti Jurij v Slov. goricah PODRAVSKA REGIJA 31 8 1069 1028 M 211 Šentrupert JUGOVZHODNO SLOVENSKA REGIJA 49 25 1642 1164 M 212 Mirna JUGOVZHODNO SLOVENSKA REGIJA 31,3 22 1269 1304 Ž 213 Ankaran OBALNO-KRAŠKA REGIJA 8 1 1475 1509 Ž analiza-pokrajine Pokrajina Občine Površina Naselja Primorska 24 3918,5 878 Štajerska 87 5529,7 1832 Prekmurje 19 948,6 177 Gorenjska 31 3236 1039 Notranjska 11 1538,4 302 Koroška 9 783,7 111 Dolenjska 31 4408,3 1718 6000 Štajerska 5000 Dolenjska 4000 Primorska ina Gorenjska 3000 vršPo 2000 Notranjska 1000 Prekmurje Koroška 0 0 500 1000 1500 2000 Število naselij Damijana Keržič Naloge iz Excela NALOGA 13 Račun v trgovini VLOOKUP, NOW, CONCAT, MID, TEXT, RANDBETWEEN, izračun s formulo Prikazali bomo primer preproste izdelave računa za izdelke, kupljene v trgovini. Datoteka: trgovina.xlsx Delovni zvezek vsebuje tri delovne liste izdelki, prodajalci in račun. Opis podatkov na delovnem listu izdelki.  V A1:H55 je seznam izdelkov (I), ki se prodajajo v trgovini.  ŠIFRA Izdelka (stolpec B) v prvi števki pove, ali je izdelek v davčni skupini D1 (šifra se začne z 1) ali v D2 (šifra se začne z 2) (tabela K20:L22).  V stolpcu E je zapisana osnovna mera enote količine izdelka EI.  V stolpcu F je cena izdelka (I) brez DDV CI.  V L21:L22 je vrednost davčne stopnje DI. Opis podatkov na delovnem listu prodajalci.  V B2:E10 je seznam prodajalcev s kodo in nazivom.  V G2:N4 je tedenski urnik dela zapisan s kodami prodajalcev. Opis podatkov na delovnem listu račun.  V A8:A18 so zapisane kode nakupljenih izdelkov.  V D8:D18 pa njihove količine KI. 1. Na listu izdelki v stolpec A vpišemo kodo izdelka, ki jo sestavimo iz črke A, ki ji sledi šifra izdelka, nato znak minus in črka S ter koda skupine: AŠIFRA Izdelek-SKODA Skupina  Uporabimo CONCAT.  Stolpec samodejno razširimo. 2. Na listu račun v F1 izpišemo datum in uro nakupa, v F2 pripadajoči dan v tednu in v F3 uro z dodanim izpisom dop. ali pop.  V F1 uporabimo funkcijo NOW.  Za izpis dneva v tednu in ure uporabimo funkcijo TEXT. V Excelovi pomoči poiščemo opis funkcije za pravilno določitev drugega argumenta. 48 Damijana Keržič Naloge iz Excela 3. Številko računa v F5 vpišemo kot naključno izbrano 6-mestno število.  Za naključni izbor imamo na voljo dve funkciji, RAND in RANDBETWEEN. Izberemo drugo. Zakaj? Pozor: Funkciji NOW in RANDBETWEEN ob vsakem izračunu katerekoli vrednosti na delovnem listu spremenita oziroma posodobita vrednosti. 4. Zapolnimo stolpce B8:B18, C8:C18 in E8:E18 z ustreznimi vrednostmi.  Za naziv izdelka v stolpcu B uporabimo VLOOKUP.  Za naziv skupine v stolpcu C uporabimo gnezdenje dveh funkcij VLOOKUP. Razlaga 0.  Za davčna skupina v stolpcu E uporabimo IF in s funkcijo MID iz KODA izdelka razberemo davčno stopnjo in vpišemo D1 oziroma D2. 5. V F8:F18 izračunamo znesek kupljenega izdelka po formuli ZDDVI = CI * (1 + DI) * KI/EI.  Uporabimo VLOOKUP za iskanje cene (CI), enote (EI) in davčne (DI) stopnje izdelka. 6. V celici E23 in E24 izračunamo skupni znesek izdelkov ločeno po davčnih stopnjah ZDS.  Uporabimo funkcijo SUMIF. Pravilno postavljeni absolutni naslovi zahtevajo štiri $. 7. V F23 in F24 izračunamo vračunan DDV (VDDVDS) za vsako od davčnih stopenj po formuli: VDDVDS = DDS / (1 + DDS) * ZDS, pri čemer je DS davčna skupina.  Uporabimo VLOOKUP za izpis vrednosti davčne stopnje v D23:D24. 8. V celico B27 zapišemo še naziv in ime prodajalca, ki nas je tisti dan postregel: Streže vam naziv Ime.  Naziv in ime poiščemo s pomočjo funkcije VLOOKUP. Pri tem nam podatek v F3 pove, ali imamo dopoldanski ali popoldanski urnik. Pozor: Tedenski urnik na listu prodajalci ni organiziran tako, da bi omogočal uporabo funkcije VLOOKUP. Vrstice in stolpce moramo zamenjati. Uporabimo kopiranje in posebno lepljenje Transponiraj v B12. 49 Damijana Keržič Naloge iz Excela  Za določitev izpisa dopoldanskega ali popoldanskega prodajalca potrebujemo gnezdente VLOOKUP (najprej določimo kodo zaposlenega, nato pa uporabimo funkcijo MID in IF).  Funkcija CONCAT združi besedilne nize. Ne pozabimo na presledke in piko na koncu stavka.  V pomoč zapisana funkcija brez argumentov 1. KORAK: Določimo izpis 2. KORAK: Poiščemo iz stolpca 2 oz. 3 glede na 3. KORAK: S kodo kodo prodajalca. znak v zapisu F3. poiščemo naziv prodajalca. CONCAT(_;VLOOKUP(VLOOKUP(_;_;IF(MID(_;_;_)=_;_;_);_);_;_;_); _;VLOOKUP(VLOOKUP(_;_;IF(MID(_;_;_)=_;_;_);_);_;_;_);_) 4. KORAK: Sestavimo stavek. Streže vam študentka Sara. 50 ki ki izdel izdel očki e ine vo lečni esni ijače a eci ta ripom vk n m argar in revij n m p ne p da skupine n p ava rije in ži ni isi h i la leko i eso i lenj dje dka oka ja in m s sti alet sop Naziv kru m m ze sa sla m ol ki či to alkohol ča stopnja 9,5% 22% upinaSk skupina A na vč KOD 01-010 01-011 02-010 03-010 03-011 00-010 04-011 04-012 04-020 11-100 15-021 05-021 22-000 da D1 D2 loga 35 3 1 3 5 7 10 1 30 5 2 43 4 2 14 12 34 2 12 15 9 5 30 10 2 15 2 8 5 3 15 18 20 9 12 za rez b DDV 2,10 € 1,95 € 2,95 € 2,55 € 3,25 € 3,00 € 3,14 € 3,45 € 0,80 € 0,75 € 0,70 € 0,48 € 0,50 € 9,25 € 14,50 € 17,50 € 19,50 € 17,50 € 17,90 € 15,50 € 3,50 € 3,75 € 1,00 € 1,75 € 3,15 € 2,75 € 3,25 € 2,50 € 1,75 € 2,25 € 1,25 € 1,30 € 1,10 € 2,75 € 3,75 € cena a ere kg kg kg kg kg kg kg kg l l l l l kg kg kg kg kg kg kg kg kg kg kg kg kg kg kg kg kg kg kg kg kg kg enot m era 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 m 0,8 0,8 0,8 0,9 0,8 0,9 0,7 0,2 0,2 0,5 plje plje rne plje rne er Grosu r r to rne rne leka m rna r lina r iland Grosu .m leka Grosu lina Ži uto d išes lina leka leka - M lina .m M h - - Lj - Yov Lj Peka M M rna rna .m .m gol del kru - Lj ivja ka uca h - ir ati tr ti ir la a dič Peka alec - s kru ,5 - Lj ,6 - Lj vadni dni fidus s nče zdel a š r - Peka na - jona - zl ni -na -sa Bi ent jski - end - ra fižol ča i arine et rt rt rt m bozo pir la dižnik ka ka ne ara ziv i i hlebec - i hlebec - bel hlebec - ošt ta alna ol nozr leko 3 leko 1 r J r E noški m rgonzol ata ata and na bel bel pol črni M Sosed - St pol m m jogu jogu jogu si si Bohin Na Ca Brie Go sol sol krom čebu česen para paprika stročji cve brokol jabol jabol bana pom m A pina KOD Sku 01-010 01-010 01-010 01-010 01-010 01-010 01-010 01-010 01-011 01-011 01-011 01-011 01-011 01-011 01-011 01-011 01-011 01-011 01-011 01-011 03-010 03-010 03-010 03-010 03-010 03-010 03-010 03-010 03-010 03-010 03-011 03-011 03-011 03-011 03-011 ek FRA ŠI Izdel 1-001-01 1-001-02 1-001-03 1-001-04 1-001-05 1-001-06 1-001-07 1-001-08 1-002-01 1-002-02 1-002-03 1-002-04 1-002-05 1-002-06 1-002-07 1-002-08 1-002-09 1-002-10 1-002-11 1-002-12 1-003-01 1-003-02 1-003-13 1-003-14 1-003-15 1-003-26 1-003-27 1-003-28 1-003-39 1-003-37 1-004-01 1-004-02 1-004-33 1-004-24 1-004-25 -010 -010 -010 -010 -010 -010 -010 -010 -011 -011 -011 -011 -011 -011 -011 -011 -011 -011 -011 -011 -010 -010 -010 -010 -010 -010 -010 -010 -010 -010 -011 -011 -011 -011 -011 ek i elk A Izdel -001-01-S01 -001-02-S01 -001-03-S01 -001-04-S01 -001-05-S01 -001-06-S01 -001-07-S01 -001-08-S01 -002-01-S01 -002-02-S01 -002-03-S01 -002-04-S01 -002-05-S01 -002-06-S01 -002-07-S01 -002-08-S01 -002-09-S01 -002-10-S01 -002-11-S01 -002-12-S01 -003-01-S03 -003-02-S03 -003-13-S03 -003-14-S03 -003-15-S03 -003-26-S03 -003-27-S03 -003-28-S03 -003-39-S03 -003-37-S03 -004-01-S03 -004-02-S03 -004-33-S03 -004-24-S03 -004-25-S03 izd KOD A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 A1 prodajalci Prodajalci KODA zaposlen Pri mek Ime Naziv ponedeljek torek sreda četrtek petek sobota Dopoldn s1011 Golja Hana študent e t1056 t1067 t1012 t1041 p1055 t1041 Popoldn t1012 Potokar Liza prodajalka e s1054 p1055 t1067 s1068 t1041 s1068 t1041 Terpin Robert prodajalec s1054 Šmit Jana študentka p1055 Ferce Maja praktikantka t1056 Breša Marjana prodajalka t1067 Kranc Andreja prodajalka s1068 Senčar Sara študentka Dopold Popoldn Urnik ne e ponedelje k t1056 s1054 torek t1067 p1055 sreda t1012 t1067 četrtek t1041 s1068 petek p1055 t1041 sobota t1041 s1068 račun TRGOVINA d.o.o. Datum in ura 12.09.2024 13:11 Trgovska 7 četrtek 1000 Ljubljana 1:11 pop. tel: 01 1999 999 ID št.: SI999999999 Račun št. 191240 KODA Izdelek naziv izdelka naziv skupine količina davčna skupina znesek Z DDV A1-002-08-S01-011 Bohinjski sir mleko in mlečni izdelki 0,28 D1 5,37 € A1-002-05-S01-011 jogurt Bifidus - Yoviland mleko in mlečni izdelki 3 D1 3,29 € A2-010-13-S05-021 Merlot - Vina Kras alkoholne pijače 1 D2 4,27 € A2-010-02-S05-021 pivo - Laško alkoholne pijače 2 D2 6,10 € A1-002-01-S01-011 mleko 3,5 - Lj.mlekarne mleko in mlečni izdelki 3 D1 2,63 € A2-010-07-S15-021 toaletni papir - Paloma toaletni pripomočki 2 D2 6,71 € A1-003-13-S03-010 krompir zelenjava 1,5 D1 1,64 € A1-004-02-S03-011 jabolka - zlati delišes sadje 1,46 D1 2,08 € A1-004-24-S03-011 pomaranče sadje 1,34 D1 4,04 € A1-001-03-S01-010 polbela štruca - Mlinar kruh in pecivo 0,5 D1 1,62 € A2-012-07-S22-000 ŽIT časopisi in revije 1 D2 4,58 € SKUPAJ 42,30 € Zneski po davčnih skupinah Znesek plačila Vračunan DDV D1 9,5% 20,65 € 1,79 € D2 22,0% 21,66 € 3,91 € Skupaj DDV: 5,70 € Streže vam študentka Sara. Document Outline rezultatiR.pdf prevajalciR.pdf nočitveR.pdf kakovostR.pdf delovnikR.pdf obrestiR.pdf pomocZNR.pdf trgovinaR.pdf SI_obcineR.pdf urnikR.pdf avtoR.pdf bazenR.pdf Naloge_iz_Excela_lekt_rešitve_CIP.pdf Kazalo nalog Uvodnik NALOGA 01 Statistični podatki Urejanje in oblikovanje celic NALOGA 02 Letni obračun malice SUM, AVERAGE, MIN, MAX, izračun s formulo NALOGA 03 Obresti Izračun s formulo, gručni stolpčni grafikon NALOGA 04 Združeni narodi ROUND, izračun s formulo, naložen stolpčni grafikon, priprava strani za tisk NALOGA 05 Prevajalci VLOOKUP, ROUND, izračun s formulo, pogojno oblikovanje, palični grafikon NALOGA 06 Prodaja avtomobilov VLOOKUP, ROUND, IF, izračun s formulo, pogojno oblikovanje, gručni palični grafikon NALOGA 07 Nočitve ROUND, VLOOKUP, SUMIF, COUNTIF, izračun s formulo, 100-odstotno naložen palični grafikon NALOGA 08 Barometer kakovosti VLOOKUP, ROUND, IF, izračun s formulo, pogojno oblikovanje, kombiniran grafikon, priprava strani za tisk NALOGA 09 Seštevek točk ROUND, IF, COUNTIF, AVERAGEIF, izračun s formulo, pogojno oblikovanje, grafikoni, filtriranje, razvrščanje NALOGA 10 Fakultetni urnik COUNTA, COUNTIF, AVERAGEIF, IF, izračun s formulo, pogojno oblikovanje NALOGA 11 Obisk bazena VLOOKUP, IF, COUNTIF, pogojno oblikovanje, grafikon, priprava strani za tisk NALOGA 12 Slovenske občine VLOOKUP, IF, OR, AND, NOT, COUNT, COUNTA, COUNTIF, SUMIF, AVERAGEIF, XY raztreseni grafikon NALOGA 13 Račun v trgovini VLOOKUP, NOW, CONCAT, MID, TEXT, RANDBETWEEN, izračun s formulo