Formule, formule ...
Aplikacija Microsoft Excel uporabnikom ponuja celo vrsto orodij za delo s števili in drugimi podatki. Prava moč pa se pokaže prav pri obdelavi teh informacij, predvsem tistih, povezanih s števili. Excel, denimo, premore več kot 300 matematičnih funkcij in formul, nekatere med njimi bomo spoznali v nadaljevanju.
Uporabnost Excela se skriva predvsem v njegovi prijaznosti do uporabnika. Za uporabo že omenjenih več sto formul in drugih matematičnih funkcij namreč ni treba biti matematik ali statistik, saj uporabniku takoj priskoči na pomoč čarovnik, ki ga po korakih vodi do končnega cilja - rezultata. Uporabnik v Excelu "vklopi" uporabo formul z vnosom enačaja (znaka =) kot prvega znaka v celici, zatem pa vnese želeno formulo, ki je lahko sestavljena iz najrazličnejših matematičnih simbolov in vrednosti. Čarovnik za formule poskrbi, da bodo tudi zapleteni algoritmi, ki se skrivajo za temi formulami, videti obvladljivi. Laično bi lahko zapisali, da je Excel pravzaprav vrhunski kalkulator.
Veliko Excelovih formul je usmerjenih na posamezna področja, kot so finance, statistika, matematični in inženirski izračuni. Čarovnik, imenovan Function Wizard, ima omenjene formule in funkcije razvrščene v namenske kategorije, tako da jih uporabnik lažje najde in razloči.
Vnos funkcij
Excel ponuja več načinov opravljanja preprostih matematičnih operacij, kot so seštevanje (+), odštevanje (-), množenje (*) in deljenje (/). Uporabnik jih enostavno uporabi po vnosu enačaja, v celico, denimo, vpiše =19+78, po pritisku tipke Enter pa mu bo Excel v celici že izpisal rezultat. Ker imajo uporabniki Excela v svojih tabelah navadno že goro številk, lahko po vnosu enačaja preprosto klikajo celice s posameznimi vrednostmi in jih uporabljajo skupaj z matematičnimi in drugimi funkcijami. Če pogosto uporabljate matematične funkcije, si velja gumbe zanje dodati kar v vrstico za hitri zagon operacij. O tem, kako funkcije dodate v vrstico za hitri zagon, smo pisali v prejšnji številki.
Izbira celice ali več celic še posebej olajša matematične izračune.
Naprednejši uporabniki lahko že sami, brez uporabe čarovnika, vpišejo v celico kakšno funkcijo. Seveda to zahteva nekaj več poznavanja angleškega besedišča in matematičnih funkcij, kot so npr. SUM, AVERAGE in COUNT. Vsoto števil (ang. sum) bi tako dobili z vnosom =SUM(128+12+43+31). Seveda seštevek deluje tudi, če je katero izmed števil negativno (npr. =SUM(128+12+43-31)).
Ali ste vedeli?
Ko v Excelu izberete večje število celic z numeričnimi vrednostmi, aplikacija v spodnji vrstici (spodnji del zaslona) že izračuna vsoto in povprečje vrednosti v celicah.
Uporaba poimenovanj celic
Excel omogoča poimenovanje posameznih celic ali skupin celic. Nedvomno si uporabnik laže zapomni imena Davek ali pa Prodaja v vzhodni regiji kakor pa naslove celic, ki vsebujejo omenjene podatke. Ena izmed prednosti Excela se skriva v možnosti uporabe imen in lokacij celic, ki jih predstavljajo, v matematičnih formulah. Dodaten plus si aplikacija zasluži za svojo "pamet", saj ob prenosu omenjenih razdelkov celic na drugo lokacijo, denimo, Excel samodejno posodobi formule, ki se nanašajo nanje. Edina omejitev pri poimenovanju celic ali skupin celic je skupna dolžina znakov v imenu - ta ne sme preseči 255 znakov, kar bi moralo zadoščati praktično vsem uporabnikom, obenem pa mora biti prvi znak imena vedno črka. Ko smo že pri omejitvah pri poimenovanju - presledki in simboli razen pike in podčrtaja ne pridejo v poštev. A iz praktičnih izkušenj lahko svetujemo, da je najprimerneje ustvarjati kratka imena, ki si jih uporabnik laže zapomni.
Pred poimenovanjem skupine celic najprej izberemo želene celice. Nato kliknemo zavihek Formulas in izberemo možnost Define Name. Odpre se nam pogovorno okno za vnos imena. Poleg imena tudi določimo, ali naj poimenovanje skupine celic velja samo za določen delovni list ali za celoten delovni zvezek v Excelu.
Izbranim poimenovanim celicam lahko dodamo tudi opisni komentar vsebine.
Po izbiri obsega celic in njegovem poimenovanju bo izbrano ime na voljo vedno, ko bomo uporabili možnost uporabe v matematičnih funkcijah ali formulah (Use in Formula). Če bi pozneje radi spremenili poimenovanje izbranih celic, opravimo isti postopek, le da s funkcijo Name manager izberemo gumb Edit, ki nam omogoči preimenovanje.
Poimenovanje celic lahko opravimo tudi na večjih vnosih, t. i. rangih, kjer so podatki razporejeni čez več stolpcev. Pri tem je smiselno vključiti tudi naslove stolpcev, saj bodo vključeni v samo ime. Preprosto izberemo več stolpcev s podatki in njihovimi imeni ter kliknemo zavihek Formulas. Tokrat izberemo možnost Create from Selection, pokaže pa se nam okno za izbiro lokacije celic, ki sestavljajo rang. Ker imamo ustrezne celice že izbrane, izbiro samo potrdimo, sicer pa za poimenovanje izberemo eno izmed štirih ponujenih možnosti. Tudi ta rang celic in njihovih vrednosti nam bo sedaj na voljo za izračune skupaj z naprednimi formulami.
Če bi želeli katerega izmed poimenovanih rangov ali skupin celic izbrisati, preprosto kliknemo celico, ki je del večje skupine, in izberemo možnost Name Manager. Nato izberemo ime, ki ga želimo izbrisati, in kliknemo Delete.
Uporaba konstante
Konstanta je še ena izmed vrednosti, ki jih pogosto uporabljamo in se poredko spreminja. Lep zgled konstante je, denimo, davek na dodano vrednost, ki je 20 odstotkov. Tega poslovni uporabniki pogosto uporabljajo pri svojih kalkulacijah, zato ga velja v Excel vnesti kot konstanto in ga hitreje priklicati ob uporabi formul.
Takole shranjen vnos nam bo že ob priklicu funkcij ponudil možnost "DDV".
Storiti moramo le to, da vpišemo vrednost v posamezno celico, v našem primeru torej 0,2 (to ponazarja 20 odstotkov) in jo s pomočjo funkcije New Name v razdelku Formulas shranimo kot konstanto ter ji določimo področje veljave - ali zgolj na trenutnem listu, celotnem delovnem zvezku ...
Ustvarjanje formul, ki obsegajo imena
Sedaj nam bo prav prišlo znanje, ki smo ga osvojili na prejšnjih zgledih. Denimo, da želimo kupcu izstaviti račun, ki poleg prodajne cene vsebuje tudi podatek o davku na dodano vrednost. Ceni bomo tako dodali še vrednost davka in dobili končno ceno. Ker imamo davek že vnesen kot konstanto, se postavimo v polje, predvideno za vnos vrednosti davka, ter uporabimo funkcijo enačaja in kliknemo celico, ki vsebuje vrednost, kateri želimo dodati davek. Nato preprosto dodamo znak za množenje (*) in vpišemo ddv. Excel bo postoril vse drugo.
Uporaba čarovnika
Bolj zapletene matematične in statistične operacije seveda niso za vsakogar, jih pa lahko Excel močno približa povprečnemu uporabniku. To nalogo prevzema čarovnik Function Wizard, ki poenostavi uporabo kompleksnejših funkcij. Do čarovnika lahko pridemo na dva načina, naš "šolski" primer pa bo vključeval predstavitev funkcije zaokroževanja vrednosti (ROUND). Eden izmed načinov, kako vklopiti čarovnika za funkcije, predvideva, da se uporabnik postavi v polje, kjer želi imeti končni rezultat operacij(e), in klikne gumb Insert Function. Drugi, precej hitrejši način pa je uporaben v primerih, ko uporabnik pozna svojo funkcijo, saj v isto celico vnese enačaj in vpiše samo funkcijo ali pa jo z dvojnim klikom izbere s seznama funkcij na desni strani.
Obe metodi seveda prikličeta novo okno, saj funkcije želijo vnos polj s podatki, s katerimi bodo operirale. Te vrednosti bodisi poklikamo ali vnesemo ročno in Excelu prepustimo kalkuliranje.
V našem primeru bomo skušali zaokrožiti število 3189,773.
In sicer ga bomo zaokrožili na 0 decimalnih mest natančno. Excel že v oknu s funkcijo prikaže končni rezultat.
Mojster za posojila
Marsikateremu uporabniku najrazličnejši oglasi prodajalcev, ki ponujajo nakupe na obroke, povedo bolj malo o sami natančnosti končnih številk. Malce bolje je pri jemanju posojila na banki, kjer bančni uslužbenci natančneje predstavijo pomen samih številk na papirju. A ker se v 21. stoletju ne odpravljamo prav pogosto v banko, nam bo v tolažbo podatek, da je tudi Excel pravi mojster pri izračunavanju posojil iz podatkov o njih.
Pri nakupu avtomobila ali stanovanja nam tako lahko priskoči na pomoč Excelova funkcija Payment (PMT), saj lahko v tabeli hitro oblikujemo izračune različnih ponudnikov posojil in si izračunamo bodisi velikost posameznega obroka, dobo odplačila ... vse seveda glede na posredovane podatke. Podatke lahko vnesemo najprej v razpredelnico ali pa preprosto vklopimo funkcijo PMT in jih vnašamo neposredno v zahtevana polja. Zaradi preglednosti bomo v našem primeru uporabili prvo možnost.
Funkcija Payment (PMT) od uporabnika zahteva najmanj tri ključne podatke. Poleg (letne ali mesečne) obrestne mere - parameter RATE, je tu še število obdobij plačil (NPER) in sedanja vrednost (PV).
Izračunali bomo mesečni obrok ob 15-letnem odplačevanju (180 mesecev) glavnice v višini 12.000 evrov ob konstantni letni obrestni meri 5 odstotkov. Kot vidimo, moramo slednjo prevesti na mesečni nivo, torej bomo 5 % delili z 12. To lahko elegantno postorimo kar v samem Excelu, saj smo se že naučili, da preproste matematične operacije zlahka sami vpišemo v polja. Excel nam postreže z rezultatom, da bomo za kredit pod opisanimi pogoji mesečno plačevali slabih 95 evrov.
Seveda Excelova funkcija PMT ponuja računanje tudi drugih omenjenih vrednosti, če le imamo na voljo ustrezne podatke.
Računanje povrnitve naložbe
Podjetja občasno kupijo tudi kak dražji stroj ali drugo opremo, s katero imajo svoje načrte po kovanju prihodkov. Kolikšna bo povrnitev naložbe in kdaj nastopi, lahko izračuna tudi Excel, če uporabimo funkcijo IRR (ang. Internal Rate of Return). Seveda ima ta svoje omejitve, za naše umetne primere namreč predpostavljamo, da si izplačilo in plačila sledijo v rednih letnih zaporedjih, to pa je seveda daleč od praktične rabe. A za grobo oceno to zadostuje, ekonomisti pa bodo seveda posegli po natančnejših orodjih.
Oglejmo si zgled stroja, za katerega bomo odšteli 6607 evrov, v naslednjih letih pa nam bo prinesel nekaj tisoč evrov prihodkov. Kolikšen bo odstotek prejemkov nad izdatkom (če sploh), bomo izračunali s pomočjo funkcije IRR. Ker je naložba izdatek, jo bomo v tabelo vnesli kot negativno število. Za uporabo funkcije IRR je seveda nujno, da vsebuje vsaj eno pozitivno in negativno število. Kot prikaže izračun, nam nabava stroja prinese 12-odstotni dobiček glede na investirana sredstva.
Natančnost funkcije IRR je v praksi zgolj približna, saj Excel pri enostavnem izračunu predvideva, da so se vsa plačila izvajala prvi dan v letu.
Iskanje največje/najmanjše vrednosti
Prodajniki v podjetju pogosto želijo prepoznati najboljše vrednosti v posamezni seriji. Excel zato ponuja vrednosti iskanja najvišje oziroma najvišjih vrednosti v nizu podatkov. Mi bomo seveda za primer uporabili točke pri igri, s funkcijo LARGE pa izpisali točke, ki vodijo na prva tri mesta. Funkcija LARGE namreč poišče najvišjo n-to vrednost v nizu in jo zapiše v želeno polje.
Če bi želeli uporabiti vse doslej osvojeno znanje, bi točke igralcev postavili v niz in ga poimenovali "točke", formule za prva tri mesta pa bi se glasile =LARGE(točke,1) + LARGE(točke,2) + LARGE(točke,3).
Za iskanje najmanjših vrednosti v nizu pa preprosto zamenjamo ime funkcije LARGE s SMALL.
Formule s pogoji
V praksi nimamo vedno opravka z enostavnimi primeri, temveč moramo pri računanju upoštevati več dejavnikov. Brez skrbi, v ta namen Excel pozna formule s pogoji, zato zna omejitve upoštevati tudi pri svojih izračunih. Le ustrezno mu jih moramo predstaviti. Za demonstracijo bomo priredili prejšnji primer točkovanja v igri, in sicer bomo iz igralcev sestavili dve ekipi, zanimalo pa nas bo, kdo je bil najboljši posameznik v teh ekipah. Ekipi, bolje rečeno posamezne igralce, preprosto opremimo s številko 1 ali 2, ki bo rabila kot razločevalni dejavnik.
Formula s pogojem vedno uporablja vsaj dve funkciji, loči pa ju ukaz IF. Ukaz IF določa pogoj, v našem prvem primeru, da mora biti igralec član ekipe 1. Pri določanju primerjalnih pogojev si poleg strogega enačaja (=) lahko omislimo tudi vse druge sorodne omejitve, na primer: večji kot (>), večji ali enak (>=), manjši kot (<) ter manjši ali enak (<=).
Drugi del izbrane formule s pogojem opravlja operacijo s števili, ki temu pogoju zadoščajo. Excel torej naprej opravi s funkcijo, ki se skriva v pogoju "IF", in nato šele kalkulacijo med "zadetki".
Mi bomo tako najprej poiskali najboljšega igralca v ekipi 1. Postavimo se v polje, kjer želimo izpisati najvišjo vrednost igralcev, ki sodelujejo v ekipi 1. Nato iz nabora funkcij uporabimo funkcijo MAX, ki poišče in izpiše najvišjo vrednost v nizu. Takoj za njo v oklepaj vpišemo pogoj IF, ki se glasi IF(ekipa=1,točke). Nato postopek ponovimo še za igralce v ekipi 2.
Pogojne vsote
Na tem mestu velja omeniti tudi funkcijo SUMIF, ki združuje funkciji SUM in IF. Kot taka precej poenostavi izračun pogojnih vsot, je pa res, da ponuja manj možnosti kompleksnih izračunov kot kombinacija dveh posameznih funkcij.
Funkcija SUMIF tako upošteva tri dejavnike: razpon števil, pogoj, ki velja za ta števila, in področje, kjer ta pogoj velja. Številke, ki ustrezajo pogoju, so torej seštete. Za naš primer bi, denimo, lahko določili, da naredi seštevek vseh točk ekipe 1 ali 2. Torej moramo najprej formuli povedati, da izbira med ekipama 1 in 2, in najprej določiti, da štejejo le rezultati, ki imajo opis "1". Iz teh nato naredi vsoto.
Za preprostejše izračune pogojnih vsot je funkcija SUMIF kot naročena.
Za primer seštevanja je funkciji SUMIF zelo podobna tudi funkcija COUNTIF.
Dodajanje kalkulatorja
Kako pogosto se vam je že zgodilo, da ste želeli opraviti hiter izračun brez uporabe formule ali funkcij. Uporabnikom, ki pogosto posegajo po kalkulatorju, zato priporočamo integracijo le-tega v vrstico za hitri dostop (Quick Access toolbar), saj jim bo tako vedno pri roki.
Ko enkrat v Excel dodamo kalkulator, ki je povsem enak tistemu, ki ga uporablja nameščeni operacijski sistem, ga lahko začnemo uporabljati. S klikom možnosti View lahko izbiramo med tradicionalnim in znanstvenim kalkulatorjem.
Ali ste vedeli?
Če bi radi hitro izračunali povprečno vrednost vnesenih števil s kalkulatorjem, preklopite na znanstveni pogled (Scientific view). Kliknite gum Sta, ki je namenjen statističnim operacijam. Zatem začnite vnašati števila in po vsakem vnosu pritisnite tipko Dat. Ko ste vnesli vse vrednosti, kliknite gumb Ave za izračun povprečne vrednosti. Seveda pa lahko vnesena števila zdaj uporabite še v druge statistične namene ...
Matematične operacije
Kot rečeno, Excel je dober matematik. Večina uporabnikov sicer uporablja le štiri osnovne matematične operacije, a sama aplikacija zmore še precej več. Z uporabo funkcije PRODUCT, denimo, lahko zmnožimo dve ali več števil, funkcija SQRT pa nam izpiše kvadratni koren števila. Pri tem velja omeniti, da korenjenje števil deluje le za pozitivna števila, negativnih korenov pa Excel ne zna izračunati, saj ob uporabi ukaza SQRT(-1) uporabniku vrne polje z vnosom #NUM.
Med zanimivejšimi funkcijami velja na tem mestu omeniti še funkcijo POWER, ki se uporablja za računanje potence, pri čemer sta število in potenca ločena s podčrtajem. Ukaz POWER(3;9) nam bo dal rezultat števila 3 na deveto potenco.
Računanje časa
Z Excelovimi formulami in funkcijami je mogoče opravljati tudi izračune datuma in časa. Tako lahko izračunamo število ur ali dni med dvema datumoma. Posebne funkcije pretvorijo datum in čas v številčno vrednost, s katero je moč računati. Excel, denimo, tako počne z datumi, ki se začnejo po 1. januarju 1900. Kalkulacije časa so lahko natančne do šestdesetinke (1/60) sekunde.
Najprej si bomo na zgledu ogledali računanje časovne razlike, denimo, da imamo urnik odhodov in prihodov (letala, vlaka, avtobusa ...), zanima pa nas čas potovanja. Ker so aplikacijo Excel razvili pretežno ameriški programerji, pri vnosu časa velja obvezno uporabljati oznaki AM in PM za označitev dopoldneva oziroma popoldneva. Temu se sicer lahko izognemo s formatiranjem celic in nastavitvijo ustreznih prikazov časa - prikladno, če sestavljate večji urnik, zamudno, če bi radi opravili le hiter izračun.
Izračun časovne razlike je silno preprost. Vrednosti v polju prihoda odštejemo vrednost v polju odhoda.
Ali ste vedeli?
Pri računanju časovne razlike, kjer se vmes zamenja datum (polnoč), velja uporabiti funkcijo MOD. Formula se nato glasi takole=MOD(poznejši čas - zgodnejši čas, 1).
Računanje z datumi
Ena izmed posebnih funkcij pri izračunih datuma in časa omogoča tudi računanje delovnih dni med dvema datumoma. Tako kot druge Excelove funkcije, je tudi ta dosegljiva prek čarovnika, sliši pa na ime NETWORKDAY. Seveda upošteva le delovne dni, torej brez konca tedna, določimo lahko celo ločen datumski interval - ko smo, denimo, na počitnicah.
