Tag: se splača

Graf v Excelovem komentarju

»Slika pove 1000 besed«, so me učili že od osnovne šole dalje. A kaj ko so mnogokrat pomembne tudi številke. Tako se uporabniki včasih srečate s problemom, ko želite ljudem (večinoma šefom in predpostavljenim) posredovati neke informacije, a pri tem morate vse skupaj stlačiti na en ekran oz. želite da je informacija predstavljena čimbolj kompaktno.

Kadar so številke zelo pomembne, hkrati pa bi želeli prikazati tudi različne grafe iz teh istih številk, se pojavi problem, saj morajo prejemniki tako skakati iz številk na grafe in nazaj. Mnogokrat je to moteče in zato izgubljajo fokus!

Kaj pa če bi hkrati predstavili številk in graf? Z malce iznajdlivosti je možno tudi to!

Continue reading Graf v Excelovem komentarju

Dinamični graf

Počasi je, preko prispevkov o dinamičnih območjih in o poimenovanjih območij, zares prišel čas, da si ogledamo izdelavo dinamičnega grafa. To je graf, ki prikaže toliko podatkov, kot jih zapišete.

S pomočjo tega »trika« imate tako možnost ustvariti graf enkrat za vselej in ne vedno, ko se poveča število podatkov. Tipična uporaba je ob prikazu poslovanja, ki prikazuje mesece in vsak mesec se pojavi nova postavka zato je potrebno izrisati nov graf… Ali pač ne ;).

Continue reading Dinamični graf

Poimenovanje območij

Preden torej nadaljujemo na dinamične grafe o katerih sem govoril že v prejšnjem blogu, bi vam predstavil še poimenovanje območij.

Ideja je skrajno preprosta in uporabna. Namesto, da se na neko celico ali območje sklicujete preko znanega zapisa D10 ali D10:F350, to celico (ali območje) poimenujete in se potem v formulah raje sklicujete na ime. Vse omenjeno je tudi zapisano v enem izmed mojih nasvetov uporabnikom Excela.

Continue reading Poimenovanje območij

Dinamična območja in funkcija OFFSET

V današnjem blogu sem imel namen pisati o dinamičnih grafih, to je o grafih, ki prikažejo toliko podatkov, kot jih je v tabeli, torej se prilagajajo količini podatkov.

A ob pisanju se je izkazalo, da bi bil blog prevelik, zato sem tematiko razbil na 3 sklope in danes bomo spoznali prvega – dinamična območja.

Continue reading Dinamična območja in funkcija OFFSET

Spajanje dokumentov – slike

Na forumu, se je pojavilo zanimivo vprašanje, na katerega sem pozabil odgovoriti v knjigi o spajanju dokumentov. Vprašanje je namreč kako spojiti slike v dokument. Kot rečeno, sem na omenjeno tematiko v knjigi pozabil, zato to napako odpravljam v današnjem blogu.

Seznam s slikami

Kot veste (ali pa lahko preberete v zgoraj omenjeni knjigi), se spajanja dokumentov lotimo tako, da pripravimo nek seznam na eni strani, ter nek kalup (dokument v Word-u) na drugi strani. Ob spajanju dokumentov, pa Word na vsako mesto, ki smo ga v kalupu označili doda ustrezen element iz seznama. Tako lahko hitro in preprosto dobim personalizirane dokumente, elektronsko pošto, vabila…

Omenjen seznam se lahko nahaja v Excelu, Wordu, tekstovni datoteki, podatkovni bazi… In v takšnem seznamu se seveda lahko nahajajo tudi slike. Že res da lahko imamo v podatkovni bazi tudi slike, v večini drugih seznamov pa slik preprosto ne moremo imeti. Če vstavimo sliko v Excel, potem Excel naredi nov objekt in slika se nahaja v objektu samem in ne v celici Excela.

To pa z drugimi besedami pomeni da boste imeli seznam pripravljen tako, da bodo v njem samo poti do slik. In seveda je želja, da se v Wordu, namesto poti vidi pač slika sama. In kako to doseči?

Prikaži sliko!

Predvidevajmo torej, da imate seznam, v katerem je zapisana tudi pot do slike:


Ko v wordu spojite dokument, dobite takšen vzorec;

Slika z imenom «Ime» izgleda takole «Slika»

In če to spojite dobite seznam brez slik

Word ima v kalupu seveda polja, kamor vstavlja posamezne elemente obrazca. V kolikor pritisnete kombinacijo ALT+F9 boste v dokumentu omenjena polja tudi videli:

Slika {MERGEFIELD "Ime" } izgleda takole { MERGEFIELD "Silka" }

Da bomo torej v dokumentu videli tudi slike je potrebno v dokument vstaviti še eno polje in to je polje INCLUDEPICTURE, kar bi po naše pomenilo ‘Vključi sliko’. Naredite torej sledeče:

  1. Pobrišite polje MERGEFIELD »slika«
  2. Pritisnite CTRL+F9 in Word vstavi v dokument novo polje v katerega vpišite INCLUDEPICTURE
  3. Znotraj polja še enkrat pritisnite CTRL+F9 in Word bo vstavil še eno polje, v katerega vpišite IF TRUE
  4. Potem CTRL+F9 pritisnite še enkrat in v novo polje vpišite MERGEFIELD “Slika”
  5. S tem ste torej dobili sledeče:

{ INCLUDEPICTURE { IF TRUE { MERGEFIELD "Slika" } } }

Ko sedaj spojite dokument pa dobite pričakovan rezultat:

Zakaj IF stavek

V kolikor razmišljate čemu služi tisti IF stavek vas moram potolažiti, da je slednji tam samo zaradi napake Word-a, ki brez IF stavka elementa ne preračuna in vedno pokaže isto sliko. V kolikor pa v polje vstavite omenjen IF stavek pa Word polje preračuna ob vsakem izpisu in to pomeni, da je tudi slika ob vsakem zapisu druga!

In to je natanko tisto, kar smo želeli doseči 😉

Vse o spajanju dokumentov si lahko preberete tudi v elektronski knjigi!.

Dodatek 30.10.2011

V fazi združevanja dokumentov lahko tudi preglejujete posamezne združene elemente v Wordu in v tem primeru vam Word vedno kaže eno in isto sliko – torej ne osveži slike. Rešitev tega problema pa je tudi trivialna! Polju INCLUDEPICTURE je potrebno dodati atribut \d. Izvesti je torej potrebno še šesti korak, kjer polju INCLUDEPICTURE na koncu dodate še atribut \d, ter s tem dobite sledeč zapis:

{ INCLUDEPICTURE { IF TRUE { MERGEFIELD "Slika" } } \d }

Manjka podatek?

Včasih se zgodi, da v večji Excelovi tabeli ugotovimo, da nam nek podatek manjka. Največkrat to ugotovimo, ker ima tabela premalo vrstic.

Večina tabel je namreč zgrajenih tako, da imajo v nekem stolpcu (ponavadi na levi strani) pač nek urejen podatke, ki mu na desni strani sledijo podatki. Ta urejen podatek na levi strani je lahko številka, datum, ura…

Problem je torej sledeč: »Kako najti, kateri podatek nam manjka?«

Za lažjo predstavo vzemimo konkreten problem, kjer imate tabelo v katero vsak delovnik vpišete podatke o prodaji. Seveda je to popolnoma enakovredno primeru, ko dobite neke podatke iz centralnega računalnika na osnovi delovnih dni. Ali pa podatke o urah…

Vsem tem tabelam je torej skupno, da imajo nek stolpec, v katerem bi se morali nahajati naraščujoči podatki a eden (ali več) manjka. Da jih nekaj manjka preprosto ugotovite, tako da pogledate velikost tabele. Če bi morali biti v tabeli podatki za delovne dni potem bi v tej tabeli moralo biti toliko vrstic kolikor je delovnih dni.. pa jih ni ?… Kateri manjka?

Dobro, tabelo lahko pregledujete ročno in iščete datum ki manjka a to je zamudno in dokaj vrjetno, da se boste zmotili oz. težko našli vse datume. Zaposliti je torej potrebno računalnik!

Delovni dnevi

Zanalašč sem vzel težji primer, saj vam bom tako prikazal rešitev več problemov hkrati. Najprej moramo najti delovne dneve!

Vpišite torej prvi datum. Z miško razpotegnite datume navzdol in Excel bo v vsako naslednjo celico zapisal nov (naslednji) datum. S tem ste dobili stolpec z naraščujočimi datumi.


Sedaj je potrebno še ugotoviti kateri dnevi so delovni (če bi imeli kje seznam praznikov bi vpletli še te, a danes pač predpostavimo, da so delovni dnevi vsi dnevi razen sobote in nedelje. Dan v tednu nam vrne funkcija WEEKDAY.

Zatorej v sosednji stolpec zapišemo funkcijo =WEEKDAY(<celica z datumom>;2) in razpotegnemo navzdol:

Pridružimo dneve iz naše originalne tabele

S pomočjo dodatka MExcel tem datumom pridružimo datume iz naše tabele. S tem dobimo še eno vrstico z datumi.

Kdo manjka?

Sedaj imamo seznam datumov, oznako za kateri dan v tedni gre in zraven še seznam datumov iz naše originalne tabele. Tukaj lažje že na oko vidimo kateri datum manjka… seveda pa je še mnogo bolje zaposliti Excel in mu reči:

Če je oznaka dneva manjša od 6 (torej ponedeljek – petek) in je sosedna celica prazna (ni datuma) potem je to napaka, sicer je vse OK:

=IF(AND(Q5<6;ISBLANK(R5));"MANJKA";".")

Pa jih imamo – vse packe 😉

…in mrežne funkcije še četrtič, malce bolj uporabne…

V prvih treh nadaljevanjih sage o matričnih funkcijah:

Smo matrične funkcije dodobra spoznali, zdaj pa je že čas, da si pogledamo njihovo uporabno plat.

Resnica ali laž?

Pred nadaljevanjem je dobro, da se zavedate, kaj Excel vidi ko ga vi vprašate ali je neko število večje od drugega. Odgovor je lahko da (resnica) ali ne (neresnica) in Excel bo zatorej vrnil TRUE (resnica) ali FALSE (neresnica).

A še uporabnejše je dejstvo, da vsako resnico hkrati vidi tudi kot vrednost 1, neresnico pa kot vrednost 0; čim pa imamo vrednosti pa lahko nad njimi izvajamo matematične funkcije ;).

Štetje (seštevanje) s pogojem

Excel ima vgrajeno funkcijo COUNTIF, ki šteje samo elemente, ki ustrezajo pogojem. Če želimo recimo na nekem območju prešteti vse vrednosti, ki so večje od 10 lahko to storimo s sledečo funkcijo:

=COUNTIF(A1:A200;">10")

Toda zelo pogosta želja in potreba je štetje (COUNT) ali seštevanje (SUM) (saj je pravzaprav enakovredno) podatkov glede na dva pogoja. Npr. izračunati želite vrednost prodaje vseh računalnikov v Ljubljani, na voljo pa seveda imate tabelo s prodajo artiklov po različnih krajih po Sloveniji.

Če predvidevamo, da je blagovna skupina zapisana v koloni B, kraj prodaje pa v E, vrednost pa v G, potem je vaša naloga, da seštejete vse vrednosti v koloni G, kjer je blagovna skupina enaka ‘Računalnik’ in kraj enak ‘Ljubljana’.

To pa se z vgrajeno funkcijo COUNT if ne da več storit. A kot lahko sklepate, pa to z matričnimi funkcijami lahko izvemo (saj sicer tega verjetno nebi omenjal?).

No če zapišemo sledečo funkcijo (recimo, da imamo podatke v vrsticah 1 do 2000):

{ =SUM((B2:B2000)*(E2:E2000)*(G2:G2000)) }

bomo dobili vrednot 0, saj bomo poskušali množiti besedilo s številkami. A poglejte v drugi del razlage matričnih funkcij in ugotovili boste, da Excel pravzaprav množi trojke števil (B2*E2*G2)+ (B3*E3*G3)+…+ (B2000*E2000*G2000), od katerih so v B in E zapisani teksti!

Če funkcijo preoblikujemo takole:

{ =SUM(((B2:B2000)="RAČUNALNIK")*((E2:E2000)="LJUBLJANA")*(G2:G2000)) }

Pa bomo dobili pravilen rezultat! Zakaj?

Razlog je omenjen že zgoraj. Recimo, da se v B4 nahaja tekst »RAČUNALNIK«. V tem primeru nam test ‘ali B4=RAČUNALNIK’ vrne TRUE, torej 1. Če pa tam piše »NAMIZNA SVETILKA« pa vrne FALSE, torej 0. Enakovredno tudi za mesto v koloni E.

Sedaj pa imamo torej sledeče trojke:

 – če ni računalnik in ni prodan v Ljubljani
  0 * 0 * (vrednost v G)

 – če je računalnik in ni prodan v Ljubljani
  1 * 0 * (vrednost v G)

 – če ni računalnik in je prodan v Ljubljani
  0 * 1 * (vrednost v G)

 – če je računalnik in je prodan v Ljubljani
  1 * 1 * (vrednost v G)

Ker množenje z 0 vedno vrne 0 imamo na ta način vrednosti različne od o samo v vrsticah z računalniki prodanimi v Ljubljani… in to je tisto kar iščemo 🙂

Pa še nekaj primerov:

Vrednost vseh prodanih poceni računalnikov

{ =SUM(((B2:B2000)="RAČUNALNIK")*((G2:G2000)<300) *(G2:G2000) }

Koliko dragih računalnikov smo prodali v KOPRU?
{ =SUM(((B2:B2000)="RAČUNALNIK")*((G2:G2000)>1200) )*((E2:E2000)="KOPER") *(G2:G2000) }

Več pa prihodnjič…

… ter matrične funkcije še tretjič …

Predhodno:

Generatorji števil

Čisto ob koncu prvega prispevka sem vam v hitrem primeru matrične funkcije zapisal sledečo funkcijo:

{=SUM(ROW(1:100)) }

Kot sem zapisal, in kot smo preizkusili, je funkcija vrnila rezultat 5050, kar je seštevek prvih 100 števil. Funkcija deluje, saj ji podfunkcija ROW(1:100) pač vrne sto števil, 1,2,3….100.

To omenjam posebej, saj je ob uporabi matričnih funkcij ta »trik« zelo uporaben. S pomočjo takšnega generatorja števil, vam torej ni potrebno v celice vnašati zaporedja, temveč ga lahko kar »vzamemo«.

Odgovor na prvotno vprašanje

V že omenjenem prvem prispevku o matričnih funkcijah pa sem vam čisto na koncu zapisal sledečo matrično funkcijo, ki vrne rezultat 29087, ter vam obljubil, da vam bom razložil, kaj funkcija počne in kako Excel zatorej pride do takšnega rezultata?

{ =SUM(IF(MOD(ROW(1:1000);17)=0;ROW(1:1000);0)) }

Za razlago poteka te funkcije je najlažje, da si namesto ROW(1:1000), pač izmislimo neko število (npr. 523) in poglejmo, kaj funkcija naredi v tem primeru:

{ =IF(MOD(523;17)=0;523;0) }

Excel torej najprej preveri ali je 523 deljivo s 17 in ker ni, vrne 0. Če pa bi namesto 523 vpisali 527, ki pa je deljivo s 17, pa bi Excel vrnil 527.

Dobro; ta if stavek torej vrne 0, če neko število ni deljivo s 17 in število samo, če je deljivo s 17.

Matrična funkcija pa uporabi generator prvih 1000 števil in posledično za vsako izmed njih preveri ali je deljivo s 17 ali ne. Ko Excel pridobi teh 1000 števil, pa ji preprosto sešteje. Ker števila, ki niso deljiva s 17 vračajo 0 in torej ne prispevajo k rezultatu, nam zatorej funkcija vrne seštevek vseh števil med 1 in 1000, ki so deljiva s 17.

Kaj pa če bi nas zanimalo, koliko je teh števil?

Vse kar je potrebno storiti je, da v primeru deljivosti pač vrnemo število 1 in potem seštejemo vse te enice:

{ =SUM(IF(MOD(ROW(1:1000);17)=0;1;0)) }

Lahko pa, če število ni deljivo s 17, pač vrnemo prazno vrednost in potem preštejemo vsa števila:

{ =COUNT(IF(MOD(ROW(1:1000);17)=0; ROW(1:1000);"")) }

In v vsakem primeru dobite vrednost 58, saj je med 1 in 1000 pač 58 števil, ki so deljiva s 17. POZOR: Pravilni odgovor na vprašanje koliko števil med 1 in 1000 je deljivih s 17 pa je seveda:

=INT(1000/17)

Ne pozabite; samo zato, ker poznate matrične funkcije še ni potrebno vsega reševati z njimi ;)…

Več pa prihodnjič…

… in matrične funkcije drugič …

Predhodno:

Delitev matričnih funkcij

Kot je vidno že iz definicije, se matrične funkcije delijo vsaj na:

  • Funkcije, ki vračajo en razultat
  • Funkcije, ki vračajo več rezultatov

Kako lahko funkcija zapisana v eno celico vrne več rezultatov? Zanimivo vprašanje, ki pa ga bomo pustili za naslednjič sej je očitno težje, kot kakršnakoli funkcija, ki vrača en rezultat 😉

Matrične funkcije, ki vračajo en rezultat

To je torej podverzija matričnih funkcij, ki so zaprte v neko agregatno funkcijo (SUM (seštej), COUNT (štej) ali katero podobno…).

Vse primere danes bomo izvajali na tabeli, ki je prikazana na sliki.

Kot je vidno na sliki imamo dve tabelci števil. Prva je na področju A1 do C3, druga pa F3 do H5.

Ob uporabi matričnih funkcij morate vedeti, da so namesto števil oz. navadnih argumentov v funkciji pač prisotne celotne tabele podatkov. Matrične funkcije torej operirajo nad tabelami podatkov oz. nad matricami (kot smo navajeni iz matematike). Funkcija pa operacije vedno izvaja nad istoležečimi celicami v vseh udeleženih tabelah, kar bo lepo vidno v sledečih primerih.

Oglejmo si funkcijo: {=SUM(A1:A3*F3:F5)}

Opozorilo še enkrat in zadnjič: Matrične funkcije vnašate v Excel s kombinacijo tipk CTRL+SHIFT+ENTER. Gornjo funkcijo torej vnesete tako, da vpišete ‘=SUM’, nato z miško označite A1:A3, pritisnete znak ‘*’ in nato spet z miško F3:F5 in potem še zaklepaj ter CTRL+SHIFT+ENTER.

Ko boste uporabljali matrične funkcije vam bo lažje, če se boste navadili da vsako matrico zavijete v lastne oklepaje (kar izračuna seveda ne spremeni, je pa lažje brati) in dobite takole: {=SUM((A1:A3)*(F3:F5))}

A pustimo sedaj shemantiko in poglejmo, kaj vrne ta funkcija in zakaj vrne to kar vrne. Ko funkcijo izvedete, boste dobili rezultat 198. Excel je ta rezultat dobil tako, da je zmnožil istoležeče celice v obeh matrikah in posamezne zmnožke seštel, torej (A1*F3) + (A2*F4) + (A3*F4), kar pomeni (1*12) + (4*15) + (7*18), kar je 198.

Funkcija {=SUM((A1:C3)*(F3:H5))} vrne 780, kar je seštevek (1*12) + (2*13) + (3*14) + (4*15) + (5*16) + (6*17) + (7*18) + (8*19) + (9*20)…

Seveda pa lahko uporabite tudi druge operatorje; npr: {=SUM((F3:H5)/(A1:C3))}, kar se prevede na (12/1) + (13/2) + (14/3) + (15/4) + (16/5) + (17/6) + (18/7) + (19/8) + (20/9) in da rezultat 40,11865

Pozor ob obliki matrik

Preden končam današnji prispevek, bi vas opozoril še na zelo pomembno »malenkost«… Kakšen rezultat vrne funkcija {=SUM(B1:B3*F3:H3)}?

Glede na zgoraj zapisano lahko razmišljamo takole: = (B1*F3) + (B2*G3) + (B3*H3) oz. =(2*12) + (5*13) + (8 * 14), kar je enako 201… Toda Excel vrne 585!!! Kako hudiča je dobil tako veliko številko?

Ha, hudič se zmeraj skriva v podrobnostih. Jasno sem zapisal, da Excel vedno upošteva istoležeče celice. V našem primeru pa množimo kolono z vrstico in razen prve celice, drugi dve nimata istoležečih celic v drugi matriki. Torej B1 in F3 sta istoležeči, toda B2 pripada celica F4 in ne G3, kot je bilo napačno razmišljanje v prejšnjem odstavku…

Ker Excel v matrikah nima istoležečih celic… jih ustvari in Excel pravzaprav množi sledeči matriki


in tako seveda dobi rezultat 585!

A kot sem povedal že na začetku; matrične funkcije niso mačji kašelj in zahtevajo malce znanja in razmišljanja… A dokler obdelujete enako velike matrike je stvar še dokaj preprosta, zatorej se držite preprostega pravila (vsaj na začetku): »Vedno obdelujte enako velike matrike, ali kolone ali vrstice ali večje tabele – a vedno enako velike!«

Več pa naslednjič…