Tag: matrične funkcije

V prvih treh nadaljevanjih sage o matričnih funkcijah:

• Matrične funkcije prvič…
• … in matrične funkcije drugič …
• … ter matrične funkcije še tretjič …

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č…

Predhodno:

• Matrične funkcije prvič…
• … in matrične funkcije drugič …

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č…

Predhodno:

• Matrične funkcije prvič

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č…

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.

Kaj so to matrične (mrežne) funkcije?

Č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.

Zelo zelo zelo pomembno

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)

potem je to navadna funkcija, če pa piše

{ =SUM(A1:A10) }

potem pa je to matrična funkcija. Še enkrat(!): Zavitih oklepajev ne vnašate VI, temveč jih Excel vpiše avtomatično, ko pritisnete kombinacijo tipk CTRL+SHIFT+ENTER.

Hitri primer matrične funkcije

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))

In pritisnite CTRL+SHIFT+ENTER… in rezultat je 5050 😉

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)) }

Uf Uf Uf 🙂 :), rezultat je 29087, kako je Excel prišel do njega in kako deluje ta matrična funkcija…. pa naslednjič 😉

Nadaljevanje: Matrične funkcije prvič