Monthly Archives: July 2017

Matriisikaavoja

Leave a reply

Joissakin tilanteissa matriisikaava voi olla paras mahdollinen tapa luoda kaava ja joissakin tapauksissa jopa ainoa tapa. Jotkut Excelin funktiot vaativat myös matriisikaavaa toimiakseen.

Matriisikaava syötetään aina näppäinyhdistelmällä Ctrl+Vaihto+Enter, (Ctrl-Shift+Enter).
Jos laitat matriisikaavan solualueelle, et voi muokata yksittäistä kaavasolua, vaan koko matriisikaava on muokattava. Valitset sen alueen, jossa matriisikaava on ja sitten muokkaat sitä.

Kaikessa yksinkertaisuudessaan se on alue, jonka kaikkia elementtejä kerrotaan samalla arvolla, esim.:

 

Esimerkissä kerrotaan alueen A1:B2 arvot solun A4 arvolla ja tulos laitetaan alueelle A6:B7.
Kaikissa alueen A6:B7 soluissa on sama kaava {=A1:B2*A4}.
Teet laskennan seuraavasti:
Valitse tulosalue (A6:B7)
Kirjoita kaava =A1:B2*A4
Vahvista kaava painamalla Ctrl+Vaihto+Enter, tämä on matriisikaavan syöttötapa.
Excel lisää kaavan ympärille aaltosulut, { }.

Toinen esimerkki

 

Laskelma, joka palauttaa virheitä, mutta haluat summata tulokset virheistä huolimatta.
Esimerkiksi seuraava laskelma:
Sarakkeessa A on arvoja, joita jaetaan sarakkeen B arvoilla.
Jos sarakkeessa B on nolla (0) tai tyhjä, saadaan virhe ja summaus ei tule onnistumaan (solussa C5 on normaali summakaava).
Mutta jos summakaava laitetaan seuraavasti {=SUMMA(JOSVIRHE(C1:C3;””))} saadaan oikea vastaus {=SUMM(IFERROR(C1:C3;””))}. Tämän kaavan tulos on solussa C6.

 

Kolmas esimerkki

 

Lasketaan sarakkeen B ja C muutoksen keskiarvo suoraan soluun B7 ilman että ensin laskettaisiin erotukset D sarakkeeseen ja sitten D sarakkeen keskiarvo.

 

Kaava on matriisikaava muotoa {=KESKIARVO(B2:B5-C2:C5)}
{=AVERAGE(B2:B5-C2:C5)}
Kaava laskee solujen B ja C:n erotuksen ja sen jälkeen keskiarvon erotuksista.

Neljäs esimerkki

 

Lasketaan kuinka monta solua alueella B2:D5 on tekstiä.

 

Tällöin kirjoitetaan seuraavanlainen kaava joka on matriisimuodossa:
{=SUMMA(ONTEKSTI(B2:D6)*1)}
{=SUM(ISTEXT(B2:D6)*1)}
Funktio ONTEKSTI palauttaa TOSI jos solussa on tekstiä ja TOSI * 1 on 1, joten kun ykköset summataan saadaan tekstisolujen määrä.

 

Viides esimerkki

 

Lasketaan SUUNTAUS (TREND) soluihin B5:B7 perustuen arvoihin B2:B4.
Funktio palauttaa matriisin, joten se on kirjoitettava matriisikaavana.
Kun kirjoitat kaavan, valitse ensin alue, johon haluat tuloksen (B5:B7).

 

Kuudes esimerkki

 

Kun käytetään Excelin toimintoa Arvotaulukko, luo toiminto tulossoluihin matriisikaavan.

 

 

Esimerkissä lasketaan tuleva arvo eri vuosille ja eri korkoprosenteilla, jos sijoitetaan 500 € kerran kuukaudessa.

Solun B7 kaava on: =TULEVA.ARVO(B1/12;B2;B3) ja se luodaan ensimmäiseksi (FV).
Tämän jälkeen kirjoitetaan maksuerät soluihin C7:I7 (itse olen kirjoittanut vuodet C6:I6 ja laittanut kaavan C7:I7 joka on =12*C6, =12*D6… jne.
Sitten valitsen alueen B7:I12 ja toiminnon Arvotaulukko kohdasta Entä jos analyysi Tiedot valintanauhasta (Data, What-If Analysis, Data Table) .
Rivin syöttösoluksi (Row input cell) valitsen B2, jossa minulla on maksuerät, ja sarakkeen syöttösoluksi (Column input cell) B1, jossa minulla on korkoprosentti.

 

Taulukko näyttää minulle tulevan arvon eri vuosille eri korkoprosenteilla.

Kansiossa olevien tiedostojen luku VBA:ssa

Leave a reply

VBA:ssa voit lukea kansion Dir käskyllä löytääksesi haluamasi tiedoston tai vaikka kansion kaikki tiedostot.

Käytä lukemisessa Do While – Loop solmukkaa. Kun olet käsitellyt tiedoston ja haluat lukea seuraavan tiedoston suorita Dir toistamiseen ennen Loop käskyä.

Alla olevassa esimerkissä kansionkin voisi parametrisoida ja Sub:n muuttaa Function proseduuriksi.

 

Sub EtsiTiedosto()

Dim NextBook As String

NextBook = Dir(“c:\test\*.xlsx”)     ‘lue hakemistosta kaikki .xlsx tiedostot

Do While NextBook <> “”     ‘silmukka niin kauan kun tiedostoja löytyy

…

    Koodi joka käsittelee löydetyn tiedoston

…
 

NextBook = Dir()         ‘lue hakemisto uudestaan

Loop

End Sub

Etsitään alin/ylin lukuarvo alueelta ja muotoillaan se

Leave a reply

Valitaan ensin alue (esimerkissä A1:H100)
Valitaan Aloitusvalintanuhasta Ehdollinen muotoilu (Home, Conditional Formatting) ja siitä
Valitaan Uusi sääntö (New Rule)
Valitse Määritä kaavan avulla, mitkä solut muotoillaan (Use a formula to determine which cells to format)
Kirjoita kaava siihen varattuun ruutuun: =A1=MIN($A1:$H100)
Napsauta painiketta Muotoile (Format)
Valitse haluamasi muotoilu
Paina OK-painiketta kunnes tulet takaisin taulukkoon.
Vastaavasti suurimman arvon saat korvaamalla MIN (MIN) funktion MAKS (MAX) funktiolla.

Kaavassa vertaillaan aina kulloista solua rivin pienimpään arvoon. Jos solun arvo on sama kun rivin pienin arvo se muotoillaan. Tämän takia kaavassa lukitaan sarakkeet ($A ja $H).

Kaavan kopiointi solualueelle

Leave a reply

Kaavan voi kopioida monella eri tavalla jollekin alueelle.

  1. Valitse kaava ja napsauta Kopioi. Tämän jälkeen valitse ne solut johon kaavan haluat ja napsauta Liitä (Copy, Paste)
  2. Lista/taulukko muotoisessa tiedossa sen voi kopioida kaksoisnapsauttamalla valitun solun alaoikeassa kulmassa olevaa täyttökahvaa.
    Kaava kopioituu niin pitkälle kun naapurisolussa on tietoa.
  3. Valitse ensin solualueet, kirjoita kaava ja paina Ctrl+Enter, kaava kopioituu valittuihin soluihin.
    Ei tarvitse olla yhtenäinen alue.
  4. Kirjoita kaava ja testaa että se on oikea. Valitse sen jälkeen ao. solu ja se alue johon sen haluat kopioida. Paina F2 (aktivoi solun) ja sen jälkeen painat Ctrl+Enter.
  5. Joissakin tapauksissa voi myös käyttää tapaa että kaavan kopioi kaavakentästä toiseen soluun.
    Mennään soluun jossa kaava, valitaan kaava kaavakentässä ja painetaan Kopioi.
    Siirrytään soluun jonne se halutaan ja napsautetaan Liitä. Huomaa että soluviittaukset pysyvät samoina kun siinä solussa josta kaava kopioitiin. Eli jos kaava jonka kopioit on =A1+B1 ja liität sen vaikka soluun D10 niin kaava on edelleen =A1+B1

 

Jotta kaavat toimisi pitää kaavat luonnollisesti olla oikein kirjoitettu, eli muista lukita soluviittaukset oikein. Käytä apunasi funktiopainiketta F4 viittaustavan muuttamiseen. Joka kerta kun painat F4 muuttuu viittaustapa (A1, $A$1, A$1, $A1, A1).

 

Pivot useammasta taulusta

Leave a reply

Tauluissa jotka haluat yhdistää tulee olla jokin yhteinen tekijä kuten asiakasnumero, henkilönumero tai jokin muu vastaava sarake (vertaa PHAKU funktion käyttöä).

Määrittele ensin listasi taulukoiksi: Osoitin listaan ja Lisää – Taulukko (Insert – Table). Kannattaa antaa tauluille kuvaava nimi Taulukko työkalut, Rakenne, Taulukon nimi).

Tämän jälkeen määrittele taulujen yhteys (pivotissa ja versio 2016 yhteyden voi myös antaa Excelin tehtäväksi, joskus onnistuu ja joskus ei).

Yhteyden määrittelet Tiedot valintanauhan ryhmästä Datatyökalut ja Yhteydet toiminnolla (Data – Data Tools, relationships).

Valitse ensin (yläriville) se taulukko jossa on yksilölliset tiedot ja avain sarake, se sarake jossa nämä yksilölliset tiedot sijaitsevat.

Seuraavalla riville valitse se taulukko jonka haluat liittää ja se sarake josta löytyy vastaavat tiedot.

Alla olevassa esimerkissä henkilönumero.

Nyt voit luoda pivotin tiedoista ja poimia sarakkeita molemmista tauluista. Huomattavasta parempi vaihtoehto kuin Phaku.