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
Zadnjič se je na forumu pojavilo zanimivo vprašanje, s katerim se trudi mnogo uporabnikov. Namreč pogojno oblikovati neko celico, ki ima fiksno vrednost je dokaj preprosto, kaj pa če želimo v v neki tabeli obarvati rdeče celico z najnižjo vrednostjo?
Najnižje vrednosti v tabeli ni težko poiskati:
=MIN([področje tabele])
Če je vrednot trenutne celice enaka minimalni vrednosti celotne tabele, potem jo obarvaj.
Če bi šli vpisovat pogojno oblikovanje v vsako celico tabele bi bilo to seveda skrajno zamudno in tudi dokaj nesmiselno. Kaj pa če se pogoj malce spremeni ali bomo šli zopet popravljat 5000 celic? Seveda ne!
Pogoj za obarvanje zatorej določimo za celotno tabelo naenkrat, pri tem pa se moramo zavedati, da bo Excel formulo razumel natanko tako, kot razume vse formule, ki jih vnašamo na večje območje celic hkrati.
Naša formula se bo vedno sklicevala na zgornjo desno celico tabele, Excel pa jo bo ustrezno priredil za vse ostale celice na celotnem področju.
Predpostavimo torej da se naša tabela nahaja na območju F10:P42 in da želimo z rdečo obarvati celico, ki je na tem območju najmanjša:
V vsakem primeru se nam v tabeli RDEČE obarva celica, ki nosi najmanjšo vrednost.
V pogojno oblikovanje smo vpisali formulo:
=F10=MIN($F$10:$P$42)
Ob tem vas še enkrat opozarjam, da smo v formulo vpisali celico F10, saj je to zgornja desna celica našega območja in Excel jo bo ustrezno zamenjal v vsaki celici. V celici K27 bo Excel zatorej videl sledečo formulo:
=F27=MIN($F$10:$P$42)
maksimalna vredost
=F10=MAX($F$10:$P$42)
=F10<AVARAGE($F$10:$P$42)
Mnogokrat je želja v neki veliki tabeli najti vse podvojene vrednosti:
=COUNTIF($F$10:$P$42;F10)>1
Zadnjič sem vam opisal kako lahko izdelate »lebdeči graf« in seveda lahko povsem isto tehniko uporabite tudi pri izrisu gantograma:
Gantogram na sliki je seveda malce ozek (toliko da se slika vidi na blogu), a sicer je to čisto pravi gantogram. Kot že zapisano, pa sem ga tudi ustvaril na povsem enak način, kot »lebdeč« graf, le da so tukaj »stolpci« obrnjeni vodoravno.
Omenjen graf sem izdelal na podlagi sledeče tabele:
Sam graf pa omenjam zato, ker je potrebno (zaradi datumov!) ob gantogramu malce več truda. Če namreč izdelate graf na podlagi gornje tabele, boste dobili sledeč zmazek:
Na omenjenem grafu sta vidni vsaj dve iztopajoči napaki. Prva napaka so sami odseki, ki so vsi enaki, druga napaka pa so neki čudni datumi spodaj. Če graf povečate vidite, da so spodaj zapisani datumi od 0.1.1900 do 4.10.2173!!!
Za raziskavo te »skrivnosti« si morate prebrati, kako Excel sploh vidi datume. Kot vidite, je datum pač navadna številka in številka 0 je pač »datum« 0.1.1900. Na drugi strani pa imamo datum 4.10.2173. Zakaj?
No; če datumsko celico 11.10.2011 prikažete kot število, boste videli 40827, 8.11.2011 pa kot 40855. Vsota omenjenih števil je 81682, kar predstavlja datum 20.8.2123 in Excel na koncu tabele pač pokaže večji datum in je izbral 4.10.2173.
V drugi stolpec moramo – kot že pri »lebdečem« grafu – dodati pač samo število dni vsake naloge, ki je seveda razlika obeh datumov. Dobimo torej spodnjo tabelo:
S tem že dobimo precej bolj smiseln graf, a datumi so še zmeraj čudni in preveliki – leto je odveč! Zato je potrebno izbrati os z datumi in ji najprej ročno nastaviti meje datumov:
Nakar ji nastavimo še obliko datumov (damo dan in mesec), s čimer dobimo gantogram z začetka današnjega bloga 😉
Zadnjič sem ob oblikovanju grafa za magistrsko nalogo s področja botanike naletel na zanimiv problem. Šlo je za raziskovanje rasti neke posebne rastline in v nalogi je bila tudi tabela z izmerjenimi velikostmi listov.
Šlo je za veliko rastlin, ki so bile vsak teden natančno popisane in izmerjene, nakar je bila izdelana manjša tabela v kateri so bile zapisane samo velikosti najmanjšega in največjega lista v posameznem mesecu. Tabela je torej izgledala takole:
Želja pa je bila izdelati graf, ki bi prikazal elemente grafa kot »plavajoče« delce od minimalne do maksimalne vrednosti. Potrebno je bilo torej izdelati graf, kot je prikazan na sliki:
Če boste to obliko grafa iskali med vgrajenimi grafi je ne boste našli, lahko pa takšen graf preprosto ustvarite. Trik je v tem, da najprej malce popravite tabelo in sicer tako da bo v drugem stolpcu samo razlika med minimalno in maksimalno vrednostjo:
In na podlagi te tabele izdelate povsem navaden stolpčni graf, kjer se en stolpec nahaja na drugem in potem spodnjemu stolpcu označite da je neviden oz brez barve in dobite natančno graf, kot ga potrebujete oz. kot je prikazan zgoraj 😉
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.
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?
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»
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" }{ INCLUDEPICTURE { IF TRUE { MERGEFIELD "Slika" } } }
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!.
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 }
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!
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:
S pomočjo dodatka MExcel tem datumom pridružimo datume iz naše tabele. S tem dobimo še eno vrstico z datumi.
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";".")
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.
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 ;).
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")
Č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)) }Če funkcijo preoblikujemo takole:
{ =SUM(((B2:B2000)="RAČUNALNIK")*((E2:E2000)="LJUBLJANA")*(G2:G2000)) }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)
Pa še nekaj primerov:
Vrednost vseh prodanih poceni računalnikov
{ =SUM(((B2:B2000)="RAČUNALNIK")*((G2:G2000)<300) *(G2:G2000) }{ =SUM(((B2:B2000)="RAČUNALNIK")*((G2:G2000)>1200) )*((E2:E2000)="KOPER") *(G2:G2000) }
Predhodno:
Čisto ob koncu prvega prispevka sem vam v hitrem primeru matrične funkcije zapisal sledečo funkcijo:
{=SUM(ROW(1: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«.
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.
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č…
Predhodno:
Kot je vidno že iz definicije, se matrične funkcije delijo vsaj na:
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 😉
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
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č…
Na podlagi prošnje v forumu sem se odločil napisati nekaj o tki. matričnih (mrežnih) funkcijah. Osebno uporabljam izraz mrežne funkcije, ker sem se ga navadil, ko še nisem slišal za uradni prevod. Uradno pa so to matrične funkcije in resnici na ljubo je to boljši izraz.
A to za vas, kot uporabnika, ni pomembno. Vi želite stvar samo uporabiti oz. ugotoviti ali je dobra ali ne.
Če najprej pogledam kaj o matričnih funkcijah pravi MS pomoč:
matrična formula: Formula, ki izvede večje število izračunov na eni ali več množicah vrednosti in vrne enega ali več rezultatov. Matrične formule so postavljene med zavite oklepaje { } in se vnašajo s pritiskom tipk CTRL+SHIFT+ENTER.
Priznati moram, da mi iz tega opisa ni ravno čisto jasno za kaj pri vsem skupaj sploh gre, zato bi jaz opisal matrične funkcije takole:
matrična formula: Matrična funkcija je funkcija, ki operira za nizi podatkov (matricami) in katere rezultat je lahko ena vrednost (nek agregat) ali pa nova matrica vrednosti. Matrične formule so postavljene med zavite oklepaje { } in se vnašajo s pritiskom tipk CTRL+SHIFT+ENTER.
Ne vem če je moja definicija kaj boljša a dejstvo je, da je suho opisovanje matričnih formul (brez primerov) težko opravilo. Zato bom v tem in nekaj naslednjih prispevkih poskušal prikazati namen in uporabo matričnih funkcij.
Ko v Excelu vnesete matrično funkcijo, jo morate potrditi s kombinacijo tipk CTRL+SHIFT+ENTER (torej držite tipko CTRL in SHIFT ter pritisnete ENTER). Če vam je to uspelo, bo Excel pred in za funkcijo zapisal zavita oklepaja. Če torej v vrstici za formule piše:
=SUM(A1:A10)
{ =SUM(A1:A10) }Ker bo za danes dovolj, za konec samo še primer matrične funkcije. Recimo, da nas zanima seštevek prvih 100 števil. V celico zapišite formulo:
=SUM(ROW(1:100))
Ali pa recimo seštevek vseh števil od 1 do 1000, ki so deljiva s 17:
{ =SUM(IF(MOD(ROW(1:1000);17)=0;ROW(1:1000);0)) }Nadaljevanje: Matrične funkcije prvič