Yearly Archives: 2018

Puuttuvat etunollat

Puuttuvien etunollien lisäys funktiota hyödyntäen.

Esimerkkinä postinumero jossa on maksimissaan 5 merkkiä.
Solussa A1 on 100 kun pitäisi olla 00100.
Luodaan seuraavanlainen kaava:
=TOISTA(0;5-PITUUS(A1))&A1
=REPT(0;5-LEN(A1))&A1

TOISTA (REPT) -funktio toistaa merkin 0.
PITUUS (LEN) -funktio laskee kuinka monta merkkiä solussa on
kaavalla 5-PITUUS(A1) saadaan selville, kuinka monta merkkiä puuttuu
& -merkki yhdistää toistettavat 0:at jo olemassa olevaan merkkijonoon

Lopputulos on 00100

Yksi syy lisää käyttää taulukoita Excelissä

Excelin taulukot ovat paljon paljon muuta kun listojen kaunis väritys.

Kun olet määritellyt listan taulukoksi (Table) saat taulukkotyökalut käyttöösi. Niistä löydät mm osittajan suodattamista varten ja summarivin joka näyttää suodatun summat, keskiarvot jne.

Taulukko laajenee automaattisesti kun lisäät rivejä ja sarakkeita joka taas helpottaa pivot-raporttien hallintaa.

Mutta saat paljon muutakin.

Ensiksi nimeä taulukko. Teet sen taulukkotyökalun valintanauhasta, ylä vasemmalla on taulukon nimiruutu. Kirjoita siihen joku kuvaava nimi ilman välilyöntejä ja erikoismerkkejä, esim. Myynti, Varasto tai muuta vastaavaa.

Nyt voi siirtyä mihin tahansa työkirjan laskentatauluun ja kirjoittaa kaavan jossa viittaat luotuun tauluun ja sen sarakkeisiin.

Esim.: =SUMMA(TaulukoNimi[SarakkeenNimi]. Excel jopa ehdottaa sinulle sarakkeiden nimiä samalla tavalla, kun funktion argumentteja.
Kaavojen kirjoittaminen yksinkertaistuu, ei enää hankalia hiirellä alueiden maalaamisia!

Taulun käyttö indeksin ja vastineen kanssa: =INDEX(Sales[Ship Country];MATCH(A1;Sales[Order ID];0))

ja PHAKU -funktion kanssa: =VLOOKUP(A1;Sales[#All];3;FALSE)

Vertaillaan arvoja edellisen vuoden/kuukauden arvoihin

Käytetään tässä Power Pivotia. Siirretään/linkitetään tiedot Excelistä Power Pivottiin.
Excelissä määritellään lista taulukoksi (Table) ja lisätään Tietomalliin, Power Pivot – Lisää tietomalliin (Power Pivot – Add to Data Model).

Esimerkki taulukko ja laskelmat:

Toiminto vie PowerPivot puolelle jossa määritellään vielä päivämäärä taulukko, Rakenne – Päivämäärätaulukko, Uusi (Design – Date Table, New)

Veriossa 2016 päivämäärät määräytyy tuodun taulun mukaan.
Tämän jälkeen luodaan tuodun taulun ja päivämäärätaulukon välille yhteys.
Napsauta Aloitus – Kaavionäkymä, (Home – Diagram View)
Raahaa hiirellä Päivämäärätaulukon Date kenttä arvotaulukossa olevan Day kentän päälle.
Näin saat luotua 1 suhde moneen yhteyden taulujen välille.
Palaa normaalinäkymään napsauttamalla Tietonäkymä (Data View).

Kun tämä on tehty niin luodaan mittarit.
Siirry Excelin puolella ja napsauta Power Pivot valintanauhaa.
Mittareita tarvitaan kaksi ja käytettävät funktiot ovat CALCULATE, SUM, PREVEOUSYEAR ja PREVEOUSMONTH.
Napsauta Mittayksiköt – Uusi Mittari (Measure, New Measure)
Kirjoita Taulukon nimi (Table name) ruutuun sen taulun nimi johon mittari luodaan, esimerkissä Results
Mittayksikön nimi (Measure name) ruutuun mittarin nimi, esimerkissä ValuesTotal
Kaava (Formula) ruutuun kirjoita kaava =SUM(Results[Value])
Voit tarkistaa kaavan oikeinkirjoituksen napsauttamalla Tarkista kaava (Check Formula) painiketta
Kun olet valmis napsauta OK

Luodaan uusi mittari jossa lasketaan edellisen vuoden arvot:
Napsauta Mittayksiköt – Uusi Mittari (Measure, New Measure)
Kirjoita Taulukon nimi (Table name) ruutuun sen taulun nimi johon mittari luodaan, esimerkissä Results
Mittayksikön nimi (Measure name) ruutuun mittarin nimi, esimerkissä ValuesPrevYear
Kaava (Formula) ruutuun kirjoita kaava =CALCULATE(SUM([Value]);PREVIOUSYEAR(‘Calendar'[Date]))
Voit tarkistaa kaavan oikeinkirjoituksen napsauttamalla Tarkista kaava (Check Formula) painiketta
Kun olet valmis napsauta OK

Luodaan vielä kolmas mittari jossa verrataan kuluvan kuukauden arvoja edellisen kuukauteen.
Napsauta Mittayksiköt – Uusi Mittari (Measure, New Measure)
Kirjoita Taulukon nimi (Table name) ruutuun sen taulun nimi johon mittari luodaan, esimerkissä Results
Mittayksikön nimi (Measure name) ruutuun mittarin nimi, esimerkissä ValuesPrevMonth
Kaava (Formula) ruutuun kirjoita kaava =CALCULATE(SUM([Value]);PREVIOUSMONTH(‘Calendar'[Date]))
Voit tarkistaa kaavan oikeinkirjoituksen napsauttamalla Tarkista kaava (Check Formula) painiketta
Kun olet valmis napsauta OK

Luo Pivot taulukot jossa näitä mittareita käytetään.
Päivämäärätaulukosta raahaat Vuosi (Year) kentän riviotsikoksi (Rows)
Arvo (Values) ruutuun raahaa arvotaulun (Results) ValuesTotal ja ValuesPrevYear

Kuukausi vertailuun luo uusi Pivot johon arvo (Values) ruutuun raahaat arvotaulun (Results) ValuesTotal ja ValuesPrevMonth

Verrataan tämän viikon lukuja edellisvuoden vastaaviin

Excelin funktioiden avulla se voidaan tehdä alla olevan esimerkin mukaan.
Esimerkissäni lähden siitä, että minulla on kaikki tieto allekkain taulukkomuodossa.
Lähtötieto määritellään taulukoksi (Lisää/Taulukko (Insert/Table)).
Taululle annoin nimen Results. Taulun sarakkeiden nimiksi tulee Day ja Value. Näitä käytän myöhemmin kaavoissani.

Ohessa esimerkkitaulu:

Sitten rakensin kaavat.
Soluun B1 kirjoitan päivämäärän josta lähden liikkeelle. Siinä voisi olla myös funktio TÄMÄ.PÄIVÄ (TODAY) jos haluan lähtökohdaksi aina tämän päivän.
Soluun D7 laitan kaavan =B1
Soluun D8 tulee kaava =D7-1. Tämä vähentää aina kyseisestä päivästä yhden päivän.
Soluun D9 kaava =D8-1 jne.
E7-E13 soluihin haen arvon kaavalla:
=JOSVIRHE(INDEKSI(Results[Value];VASTINE(D7;Results[Day];0));0)
=IFERROR(INDEX(Results[Value];MATCH(D7;Results[Day];0));0)
Kaavassa käytän kolmea funktiota: VASTINE (MATCH) joka hakee päivämäärän taulukosta ja palauttaa sen rivinumeron jossa se sijaitsee, INDEKSI (INDEX) palauttaa arvon joka sijaitsee arvosarakkeessa sillä rivillä jonka funktio vastine määrittelee ja lopuksi JOSVIRHE (IFERROR) funktio joka antaa kaavan arvoksi nolla (0) mikäli päivämäärää ei löydy.
Kaavoissa viitataan määriteltyyn taulukkoon ja sen sarakkeisiin esimerkiksi seuraavasti: Results[Day], Results on taulun nimi ja [Day] taulun sarake.
Sarakkeessa F lasketaan päivämäärä seuraavasti:
=ARVO(PÄIVÄ(D7)&”.”&KUUKAUSI(D7)&”.”&VUOSI(D7)-1)
=VALUE(DAY(D7)&”.”&MONTH(D7)&”.”&YEAR(D7)-1)
Funktiot PÄIVÄ (Day) poimii päivän, KUUKAUSI (MONTH) kuukauden ja VUOSI (YEAR) vuoden josta vähennetään yksi (1). Nämä yhdistetään merkkijonoksi &-merkillä jossa piste on erottimena. Koska piste on tekstiä on se laitettava lainausmerkkeihin (“). ARVO (VALUE) funktio muuntaa merkkijonon arvoksi jotta voin sitä käyttää G-sarakkeen kaavoissa (A-sarake josta päivämäärä haetaan on päivämäärämuoto).
Toinen helpompi tapa on käyttää funktiota PÄIVÄ.KUUKAUSI (EDATE) ja silloin kaava olisi:
=PÄIVÄ.KUUKAUSI(D7;-12)
=EDATE(D7;-12)
G-sarakkeen kaavat ovat rakenteeltaan samanlaisia kuin E-sarakkeessa:
=JOSVIRHE(INDEKSI(Results[Value];VASTINE(F7;Results[Day];0));0)
=IFERROR(INDEX(Results[Value];MATCH(F7;Results[Day];0));0)