Posts Tagged ‘matrične funkcije’

Edinstvene vrednosti v Excelu III

Torek, November 6th, 2012

http://www.matjazev.net/blog/2012/10/16/edinstvene-vrednosti-v-excelu-ii/ kako prešteti edinstvene vrednosti znotraj nekega Excelovega območja in prišli do spoznanja, da omenjena formula deluje samo nad številskimi podatki.

V kolikor pa se med vrednostmi nahajajo tudi nizi znakov, datumi ali kaj tretjega pa opisana metoda odpove.

Poiskati je torej potrebno način, ki bo preštel različne vrednosti neglede na njihovo vsebino. Iščemo torej bolj splošno formulo, ki jo bomo lahko uporabili vedno.

Seveda se sprašujete zakaj nisem te formule zapisal že takoj na začetku saj bo očitno delovala nad vsemi tipi podatkov in je zatorej boljša kot tista, ki sem vam jo predstavil zadnji. Odgovor je seveda v zahtevnosti.

Današnja formula je celo krajša in lepša od FREQUENCY, a je težje razumljiva oz. vem priporočam, da se poglobite v razumevanje zakaj deluje oz. zakaj vrača pravilen rezultat.

Uporabljene formule

Za končni rezultat bomo potrebovali dve vgrajeni Excelovi funkciji SUMPRODUCT in COUNTIF. Funkcijo COUNTIF sem v blogu že opisoval, SUMPRODUCT pa še nisem omenjal.

COUNTIF funkcije preprosto prešteje število celic, ki ustrezajo nekemu pogoju, funkcija SUMPRODUCT pa je bolj podobna mrežnim funkcijam in medsebojno sešteva medsebojne rezultate.

IDEJA REŠITVE

Za vsako celico bomo ugotovili kolikokrat se pojavi na celotnem območju (temu bo služila funkcija COUNTIF).

Sedaj je potrebno te podatke normalizirati, kar pomeni, da če se neka vrednost pojavi N krat, jo bomo mi upoštevali samo 1/N krat… Z drugimi besedami – vse rezultate je potrebno potencirati na -1 oz. 1 deliti z njimi:

Kot vidite lahko normalizirane vrednosti preprosto seštejemo (zato smo jih normalizirali!) dobimo iskani rezultat – število edinstvenih vrednosti ne nekem območju.

In če sedaj opisano idejo zapišemo z eno formulo dobimo sledeč rezultat:

=SUMPRODUCT((A3:A15<>"")/COUNTIF(A3:A15;A3:A15&""))

In to je splošna formula štetja edinstvenih vrednosti v Excelu!

Edinstvene vrednosti v Excelu II

Torek, Oktober 16th, 2012

Zadnjič sem prikazal uporabo funkcije FREQUENCY, saj jo bomo potrebovali za štetje edinstvenih vrednosti.

Imamo torej funkcijo, ki nam zna povedati, koliko števil pade v neko območje. Kako lahko to uporabimo za štetje ednistvenih vrednost?

Najprej pomislimo, kaj dobimo, če funkciji FREQUENCY posredujemo isti niz števil v obeh primerih… V tem primeru dobimo za vsako število zapisano kolikokrat se pojavi; toda če se isto število pojavi večkrat, funkcija FREQUENCY za vse ponovitve vrne vrednot 0.

Slednje je zelo pomembno, saj je to ključ naše rešitve, katere ideja je preprosta: »Seštej vse števila, ki jih vrne funkcija FREQUENCY in pri tem vsako število, ki je več kot 1, preprosto upoštevaj kot 1«

Rešitev:

=SUM(IF(FREQUENCY(A1:A100;A1:A100)>0;1))

In to je vse! Ta funkcija prešteje vse edinstvene vrednosti na območju A1 do A100. Ne pozabite pa, da je to matrična funkcija, kar pomeni, da jo morate potrditi s kombinacijo CTRL+SHIFT+ENTER.

Ima pa omenjena funkcija oz. omenjena rešitev manjšo pomanjklivost. Deluje namreč samo in izključno nad številkami. Če želimo prešteti vse edinstvene vrednosti, ki vsebujejo besedilo (recimo edinstvena imena), pa nam omenjena funkcija ne bo mogla prav nič pomagati.

V tem primeru bo potrebno poseči po močnejšem orožju, ki ga bom prikazal naslednjič.

Edinstvene vrednosti v Excelu I – funkcija FREQUENCY

Petek, Oktober 5th, 2012

V enem izmed prejšnjih prispevkov sem prikazal, kaj je potrebno storiti, da bi lahko v vrtilnih tabelah šteli edinstvene vrednosti in to je splošna rešitev problema edinstvenih vrednosti a njena pomanjklivost je v dejstvu, da potrebujemo dodatno kolono, ki označuje edinstvene vrednosti.

Včasih pa se soočimo s problemom, ko želimo preprosto ugotoviti koliko je edinstvenih vrednosti na nekem območju in potrebujemo funkcijo, ki bi nam to znala povedati.

A preden se spustimo v iskanje te rešitve, si oglejmo uporabo vgrajene funkcije FREQUENCY. Funkcija je specifična, saj vrača več vrednosti in je torej matrična funkcija!

Njen namen je razporediti vrednosti iz nekega območja v posamezna območja. Recimo ljudi v starostna območja ali artikle v cenovne razrede in podobno.

Primer uporabe funkcije FREQUENCY

Zamislimo si seznam velikosti otrok, ki jih želimo razporediti v velikostne razrede. Ugotoviti torej želimo, koliko otrok je manjših od 140cm, koliko jih je med 140cm in 150cm, 150cm in 160cm…

Imamo torej tabelo, kot je prikazana na levi strani, želimo pa rezultat, kot je prikazan na desni strani, kjer vidimo točno koliko otrok spada v kateri velikosti razred.

Postopek:

  1. Označimo celice E2:E7
  2. Vpišemo formulo
    =FREQUENCY(B2:B18;D2:D7)
  3. Pritisnemo kombinacijo SHIFT+CTRL+ENTER (ne pozabite to je mrežna funkcija!)
  4. S tem smo rezultat funkcije dobili v 6 celic hkrati.

Pomen prvega parametra je torej jasen – to je seznam vrednosti, ki jih želimo razporediti.

Pomen drugega parametra pa mogoče ni tako samoumeven. V drugem parametru namreč postavljamo gornje meje razredov in Excel te meje vedno sortira v naraščujočem vrstnem redu ter potem vrednosti druži v tako pridobljene razrede. V celico, ki ima prazen razred (v našem primeru celica E7) pa vstavi število, ki presega gornjo zapisno mejo.

V našem primeru smo torej otroke razporedili v razrede (0–130, 131–140, 141–150, 151–160, 161–170, večji). Seveda ni nujno, da so razredi enako veliki – Excelu je popolnoma vseeno.

Če bi torej napisali sledeče številke (122, 130, 145, 155, 170), bi Excel izračunal število otrok v velikostnih razredih (0–122, 123–130, 131–145, 146–155, 156–170, večji).

Naslednjič pa bom pokazal kako nam lahko ta funkcija pomaga pri štetju edinstvenih vrednosti!

Edinstvene vrednosti v vrtilnih tabelah

Ponedeljek, Julij 30th, 2012

V Excelu ni načina, ki bi nam omogočil iskanje edinstvenih vrednosti med podatki v vrtilni tabeli (to možnost ima dodatek PowerPivot v Excelu 2010, a o tem kdaj drugič).

Primer takšnega vprašanja, na katerega znotraj vrtilne tabele ni odgovora, je: »Koliko različnim strankam smo prodali določen izdelek?«.

Če imamo torej tabelo s podatki o prodaji izdelkov strankam, lahko v vrtilni tabeli preprosto in takoj vidimo koliko izdelkov smo prodali, koliko strankam smo jih prodali ipd… Ne moremo pa videti koliko različnim strankam smo jih prodali.

(more…)

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

Četrtek, Oktober 13th, 2011

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

Sreda, Oktober 5th, 2011

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

Sreda, September 21st, 2011

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

Matrične funkcije prvič…

Četrtek, September 15th, 2011

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č