Kirjoittajan arkistot:Petteri

Laskuri lomabudjettia varten

lomabudjetti

Pitkää talvea on välillä hyvä lähteä hetkeksi pakoon ja suunnata nokka kohti etelää. Vähän shoppailua, ulkona syömistä ja juomista…. äkkiä saattaakin käydä niin, että lompakon pohja tulee vastaan ja loput lomasta vietetään hotellihuoneessa eineksiä syödessä. Samalla monta suurta kokemusta jää kokematta ja elämystä elämättä.

Pieni suunnittelu ei siis ole pahitteeksi myöskään loman aikana. Tähän ongelmaan vastatakseni suunnittelin tälläisen pienen ja yksinkertaisen laskurin, jota päivittämällä näet kuinka paljon rahaa on käytettävissä päivää kohden.

Etukäteen suunnitellut budjetit tuppaavat kuitenkin usein hieman elämään loman aikana. Taulukon hienous tuleekin siinä. että voit päivittää jokaisen päivän jälkeen todelliset menot ja taulukko laskee automaattisesti jäljellä olevan summan!

Lataa tästä Excel-gurun ilmainen lomabudjettilaskuri ja pidä menot kurissa myös loman aikana!

Lataa tästä!

Todellisen vuosikoron laskeminen Excelillä

Törmäsinpä tässä mielenkiintoiseen keksintöön selaillessani Nokian matkapuhelimia erään suomalaisen verkkokaupan verkkokaupassa.  Nokkelat myyntimiehet olivat nimittäin keksineet tarjota vaihtoehdoksi erämaksutapaa, jossa ei ole yhtään Klarna-korkoa!

Screen Shot 09-05-14 at 05.19 PM

Todellisesta vuosikorosta ei sen sijaan ollut ilmoituksessa mitään mainintaa. Tämä tunnusluku antaa korosta hieman ikävämmän kuvan kuin Klarna-korko. Itseasiassa todellinen vuosikorko antaa niin rehellisen kuvan, että siitä on jopa säädetty kuluttajansuojalain 7. luvun 6§:ssä, ja sen laskentatavasta on annettu oikeusministeriön asetus 824/2010.

Yksinkertaistettuna todellinen vuosikorko tarkoittaa sitä lukua, kuinka paljon enemmän rahaa todellisuudessa siirtyy sinulta lainanantajalle vuodessa verrattuna siihen, mitä lainanantaja sinulle maksaa.

Ja tämähän on Klarna-erämaksun tapauksessa todella yksinkertainen laskutoimitus!

Edit 28.5.2020: Päivitetty teksti lukijalta saadun kommentin perusteella

Todellisen vuosikoron kaava

Todellinen vuosikorko lasketaan alla olevan kaavan perusteella. Kaavassa vasen puoli esittää vastaanotettua rahaa (tai tässä tapauksessa puhelinta) ja oikea puoli maksuja.

https://fi.wikipedia.org/wiki/Todellinen_vuosikorko

Todellinen vuosikorko on kaavassa X. Yhtälön ratkaiseminen X:n perusteella olisi melkoisen työlästä, mutta onneksi Excelin Solver-toiminnolla sen saa laskettua näppärästi.

Yhtälön vasen puoli

Jutun lopussa voit ladata Excel-työkirjan, johon voit syöttää itse omat arvot. Käyn kuitenkin periaatteen tässä läpi.

Yhtälön vasemmalla puolella on listattuna kaikki nostot, eli vastaanotettu raha. Sarakkeessa Ck on nostoerän määrä ja sarakkeessa tk on aika vuosina.

Yhtälön vasen puoli

Sarakkeessa yhtälön arvo on kaava:

=E14*POWER((1+N7);-F14)

jossa E14 on nostoerän määrä, F14 on aika vuosina (joka on tässä 0, koska korko lähtee juoksemaan ajassa 0, jolloin puhelin vastaanotetaan) ja N7 on solu, jossa todellinen vuosikorko sijaitsee (palaan tähän myöhemmin).

Yhtälön oikea puoli

Yhtälön oikealla puolella on taas uloslähtevä raha, eli lainojen lyhennykset ja muut maksut.

Yhtälön oikea puoli

Yhtälön oikean puolen kaava on täsmälleen samanlainen, eli ensimmäisessä solussa on sama kaava:

=L14*POWER((1+$N$7);-M14)

Huomataan, että koska ensimmäinen maksuerä maksetaan ensimmäisen kuukauden kuluttua, sl on silloin =0,08 eli 1/12 jne. Molemmissa kaavoissa viitataan samaan soluun N7, jossa sijaitsee todellinen vuosikorko.

X:n ratkaiseminen

Todellisen vuosikoron laskeminen onnistuu Excelin Solver-toiminnolla. Suomeksi työkalun nimi on Ratkaisin.

Solverin saat aktivoitua menemällä:

File -> Options -> Add-ins -> Solver Add-in -> Go…

Ja klikkaamalla Solver Add-inin päälle.

Nyt Solver löytyy välilehdeltä: Data -> Solver


Yhtälön ratkaiseminen

Lopullinen työkalu näyttää tältä. Sinisessä laatikossa olevassa solussa on todellinen vuosikorko, vihreässä nostot, punaisessa maksut ja keltaisessa näiden välinen suhde. Koska molempien puolien tulee olla samat, niiden välinen suhde on oltava 1.

Katsotaan seuraavaksi Solveria.

Asetamme seuraavaksi Solverissa keltaisen solun tavoitearvoksi luvun 1, eli kirjoitamme Set Objective-kohtaan solun sijainnin O32 ja Value Of-kohtaan 1.

Seuraavaksi menemme Solverin kovimpaan ytimeen. Solverin avulla voimme siis komentaa Exceliä haarukoimaan arvoja niin kauan, kunnes tavoitesolun arvoksi saadaan toivottu luku (eli 1).

Tässä tapauksessa haarukoimme sinisessä laatikossa olevaa arvoa, eli todellista vuosikorkoa. Kun painamme Solve, Excel haarukoi arvoja niin kauan kunnes sopiva luku löytyy, ja ilmoittaa kun se on löytynyt.

Nyt saimme solun arvoksi 1,674 eli todellinen vuosikorko on jopa 167,4%!

Jaan tähän laskelmissani käyttämäni Excel-työkirjan, jolla voit laskea omien lainojen tai osamaksusopimusten todellisen vuosikoron.

Lataa Excel-tiedosto tästä:

Todellinen vuosikorkolaskuri

Kommentti 28.5.2020

Aiemmin laskelmissa ei otettu huomioon kuukausittaisten maksujen vaikutusta koron laskelmaan, vaan korko laskettiin vain vertaamalla lopullisten korkokulujen suhdetta alkuperäiseen hintaan vuoden kuluttua. Tämä tapa ei ottanut huomioon ensimmäisten kuukausien korkeampaa laskennallista vuosikorkoa, joka näkyi lopputuloksessa pienempänä korkona.

Kiitos lukijalta tulleen kommentin, tämäkin asia saatiin selvitettyä. Missään tapauksessa en silti edelleenkään kutsuisi tämänlaista osamaksutapaa korottomaksi.

Nyt kuusi vuotta alkuperäisen artikkelin julkaisemisesta, huomaan että Verkkokauppa.com käyttää hinnoissaan Apuraha-kertaluotto- ilmaisua. Tämä on jo paljon parempi tapa, vaikka ei todellinen vuosikorko ei ole heti näkyvissä.

Alkuperäisen postauksen kritiikki ei kohdistunutkaan luottoihin, eikä korkeisiin korkoihinkaan, vaan väitteeseen, että osamaksuvaihtoehto olisi ollut koroton.

Jäikö jotain kysyttävää? Jätä kommentti!

Koska sillis alkaa? – Päivämäärät ja ajat Excelillä

1. tammikuuta vuonna 1900 katajainen kansamme heräili tarkastamaan kuinka kaappikellot olivat selvinneet Y1,9K:sta. Huokaistuaan helpotuksesta isännät ja emännät jatkoivat arkisia askariaan huomaamatta arjessa sen suurempaa eroa. Jotain suurta oli kuitenkin tapahtunut.

Samana päivänä nimittäin tapahtui eräs toinenkin merkittävä asia. Microsoftin Office-ohjelmistojen kellot alkoivat tikittämään. Tästä syystä 1.1.1900 nähdään Excelissä edelleen päivänä 1, kun taas 2. tammikuuta on päivä 2 ja niin edelleen. 1. lokakuuta 2014 on Excel-ajassa 41913, eli 41913 päivää ajanlaskun jälkeen.

Excel-ajan perusyksikkönä käytetään päivää. Tunnit ja minuutit muunnetaan desimaalijärjestelmään, eli ilmaistaan murto-osana päivästä. Aamu kahdeksan aikaan Excelin kello on siis 0,3333.., puolen päivän aikaan 0,5 ja niin edelleen. Koska tämä ei selvästikään ole kaikista näppärin tapa käsitellä aikoja ja päivämääriä, näiden lukujen ulkomuoto voidaan muotoilla sopivammaksi. Käytännössä Excel tunnistaa päivämäärät ja kellonajat yleensä itsestään, joten normaalissa käytössä näet todennäköisesti vain nämä muotoilut. Älä kuitenkaan hämäänny tästä, sillä konepellin alla ajat ovat kuitenkin aina perusmuodossaan!

Huom: Macilla ensimmäinen päivämäärä on oletuksena 2.1.1904, mutta tämän asetuksen voi vaihtaa.  Pidä tämä kuitenkin mielessä, jos käsittelet tiedostoja eri käyttöjärjestelmillä.

Esimerkkitapaus: Koska sillis alkaa?

Vaihe 1: Mikä päivä tänään on? Entä paljon kello on?

Ennenkuin aletaan tekemään päätöksiä, tarkastetaan tämänhetkinen tilanne. Tätä varten Excelissä on kaksi kaavaa:

=TODAY(), (suomeksi TÄMÄ.PÄIVÄ)

joka palauttaa tämänhetkisen päivämäärän.

=NOW(), (suomeksi NYT)

joka palauttaa tämänhetkisen tarkan ajan päivämäärineen. Ajatusleikkinä =NOW() funktion arvon pyöristäminen alaspäin palauttaisi täysin saman tuloksen kuin TODAY().

Screen Shot 10-01-14 at 11.13 AM

NOW()-funktio palauttaa myös päivämäärän, mutta voit muuttaa solun muotoilua siten, että vain kellonaika näkyy.

Screen Shot 10-01-14 at 11.14 AM

Vaihe 2: Silliksen ajankohta

Taulukoissa ajat ovat yleensä jossain muussa muodossa kuin vaiheessa 1 olevat täydelliset aikatiedot. Usein päivämäärä ja kellonaika on esimerkiksi eri sarakkeissa tai soluissa, jolloin niitä joudutaan yhdistelemään. Niin tehdään myös tässä esimerkissä.

Screen Shot 10-01-14 at 11.20 AM

Warrantin vuosijuhlasillis pidetään lauantaina 4.10.2014. Excelille tämä päivämäärä on 41916. Kuljetukset sillikselle lähtevät 11:30, joka on desimaalijärjestelmässä 0,4791666..

Saat siis täydelliset tiedot ajankohdasta (päivämäärä ja kellonaika) yksinkertaisesti plus-laskulla.

Screen Shot 10-01-14 at 11.22 AM

Vinkki: Pyri aina luomaan tiedot siten, että soluissa pysyy täydelliset tiedot ajankohdasta. Tämä helpottaa esimerkiksi yli päivämäärärajojen laskemista, ja voit kuitenkin piilottaa päivämäärät aina muotoluilla.

Vaihe 3: När börjar sillis?

Kahden ajankohdan eron laskeminen tapahtuu niinikään yksinkertaisella vähennyslaskulla.

Screen Shot 10-01-14 at 11.28 AM

Screen Shot 10-01-14 at 11.30 AM

Mitä? Onko sillis jo ohi?

Äkkiseltään luulisi että laskutoimituksessa on tapahtunut jokin virhe, sillä saamamme tulos vaikuttaa hieman omituiselta. Tätä ei kuitenkaan kannata säikähtää, sillä tämä johtuu tietysti siitä, että Excel muotoili automaattisesti kahden päivämääräsolun välisen erotuksen myös päivämääräksi.

Tämän voimme kuitenkin korjata luomalla samantien oman Custom-muotoilun. Pääset tähän valikkoon valitsemalla solut ja klikkaamalla More number formats kuten alla.

Screen Shot 10-01-14 at 11.38 AM 001

Tästä valikosta voit säätää solulle sopivat muotoilut. Muotoilut tehdään siten, että kaikki teksti kirjoitetaan ”-merkkien sisään. Muuttuvat aikatiedot tulevat suomenkielisen sanan etukirjaimesta, eli:

v= vuosi

k= kuukausi

p = päivä

t = tunti

m= minuutti

s=sekunti

Vinkki: Kirjoita kirjain kahdesti ja saat pakotettua numeron kaksinumeroiseksi eli nollan eteen. Kirjoittamalla kkk saat kuun lyhyessä muodossa (esim tammi) ja kkkk saat kuun pitkässä muodossa (tammikuu).

Tässä esimerkissä solun muotoiluksi asetetaan:

”Sillikseen on ”p ”päivää” t ”tuntia” mm ”minuuttia ja ”s” sekuntia”.

Screen Shot 10-01-14 at 11.39 AM

Jolloin solu näyttää tältä:

Screen Shot 10-01-14 at 11.48 AM

Jotta saat ajankohtaisimman tiedon, voit päivittää taulukon painamalla F9-näppäintä.

EXTRA: Koska Excel näkee ajat aina päivien murto-osina, saattaa vertailujen tekeminen sekunnilleen esimerkiksi VLOOKUPilla aiheuttaa joskus pyöristyksestä johtuvia ongelmia. Käytä silloin apuna esimerkiksi ROUND-funktiota riittävällä määrällä desimaaleja.

Lataa sillislaskuri tästä:

lataatästä

 

Jäikö jotain kysyttävää? Jätä kommentti!

Alkoholiäänestys – Äänet puolueittain Excelillä!

Edustaja Pelkosen kirjoituksen kommenteista löytyi mielenkiintoinen linkki uuden alkoholimainonnan kieltävän lain äänestystuloksiin. Näistä tuloksista ja keskustelusta innostuneena päätin kirjoittaa ohjeet siihen, kuinka voit myös itse kotisohvalla laskea äänestystulokset puolueittain!

Vaihe 1: Kopioi tiedot taulukkoon

Maalaa siniseksi, Ctrl +C ja Ctrl +V. Liitä tiedot siten, että Excelin solumuotoilut säilyvät.

Screen Shot 09-26-14 at 10.49 AM

Screen Shot 09-26-14 at 10.49 AM 001

 

Vaihe 2: Yhdistä siten, että kaikki tulokset ovat samassa sarakkeessa

Koska tulokset olivat netissä jaettuna kahteen sarakkeeseen, ne täytyy yhdistää Excelissä yhteen. Tämä onnistuu leikkaamalla oikeanpuoleiset solut ja liittämällä ne vasemmanpuoleisten arvojen perään.

Screen Shot 09-26-14 at 10.50 AM

Vinkki! Valitse solut nopeammin painamalla näppäimistöstä Ctrl + Shift + Nuolinäppäin!

Vaihe 4: Lisää nimisolujen viereen tyhjä sarake

Valitsemalla sarakkeen ja painamalla Hiiren oikea -> Insert voit lisätä sarakkeen vasemmalle puolelle tyhjän sarakkeen.

Screen Shot 09-26-14 at 10.51 AM

Vaihe 5: Erottele edustajan nimi ja puolue omiin soluihinsa

Tähän voit käyttää näppärää ”Text to Columns”-työkalua, joka löytyy Data-välilehden alta. Nimi ja puolue on erotettu toisistaan /-merkillä, joten valitse se erottimeksi ja paina Finish.

Screen Shot 09-26-14 at 10.51 AM 001

Vaihe 6: Lisää sarakkeille nimet

Lisää uusi rivi samaan tyyliin kuin uusi sarake, ja kirjoita siihen tiedoille sopiva nimi, esim, Nimi, Puolue ja Ääni.

:Screen Shot 09-26-14 at 10.52 AM 001

Nyt lähtötiedot näyttäisivät olevan siinä kunnossa, että niitä voidaan analysoida. Usein tässä vaiheessa taulukkoon lasketaan vielä uusia arvoja, tai haetaan niitä esimerkiksi VLOOKUPin avulla.

Esimerkki:

Edustaja Pelkosen mukaan eduskunnassa hallituspuolueen edustajan tapa vastustaa on jättää äänestyksissä äänestämättä. Jos tahdot laskea myös nämä äänet kielteisiksi, niin voit lisätä taulukkoon uuden sarakkeen ja kopioida siihen kaavan:

=IF(C2=”Poissa”;”Poissa”;(IF(OR(C2=”Tyhjää”;C2=”Ei”);”Vastustaa”;”Ei vastusta”)))

Tässä kaavassa on sisäkkäin kaksi IF-lausetta: Ensimmäinen joka palauttaa arvon Poissa, mikäli solun arvo on ”Poissa”. Jälkimmäinen IF-lause palauttaa arvon Vastustaa, jos solun arvo on ”Tyhjää” tai ”Ei”. Tämän avuksi voidaan käyttää OR-funktiota. Mikäli mikään ehdoista ei täyty, arvoksi jää Ei vastusta.

Tässä kirjoituksessa (kuten myös äänestyksissä) huomioidaan kuitenkin vain annetut äänet, joten kyseistä kaavaa ei käytetä tässä esimerkissä.

Vaihe 7: Luo Pivot-taulukko

Luot Pivot-taulukon Insert-välilehdestä ja valitsemalla vasemmalta Pivot-taulukko. Rakenna Pivot-taulukko siten, että sarakkeisiin tulee Äänet ja riveihin Puolueet. Arvokenttään laitetaan Nimi, joka antaa summan esiintyneiden nimien mukaan (eli kuinka monta edustajaa)

Screen Shot 09-26-14 at 10.53 AM

Vaihe 8: Näytä numerot prosentteina

Koska tarkoituksena oli selvittää puolueiden sisäiset äänestysprosentit, asetetaan taulukko näyttämään arvot prosentteina rivin summasta. Tämä onnistuu klikkaamalla taulukkoa oikealla ja valitsemalla Show Values As -> % -of Row Total.

Screen Shot 09-26-14 at 10.54 AM

Vaihe 9: Järjestä suurimmasta pienimpään

Klikkaa taulukkoa taas oikealla ja valitse Sort -> Sort Largest to Smallest.

Screen Shot 09-26-14 at 10.54 AM 001

Vaihe 10: Ihaile lopputulosta

Screen Shot 09-26-14 at 11.37 AM

Äänteen prosentuaalinen osuus puolueittain.

Jäikö kysyttävää? Jätä kommentti!

Suuri arvosanataulukko – Taulukon siistiminen ja muotoilu

Tämä tarina alkaa siitä, kun löysin edestäni läjän kurssien arvosanoja ja päätin hieman tarkastella niitä. Data oli allaolevassa muodossa, joten sen saaminen analysoitavaan muotoon vaati useita pieniä kikkoja, joita ajattelin tässä valaista hieman lukijalle.

Eri tavoin muotoiltuja taulukoita saattaa tulla vastaan oikeastaan missä vain, joten suosittelenkin lukijaa kiinnittämään huomionsa erityisesti taulukoinnin lopputulokseen, eli siihen minkälaisessa muodossa taulukon tietojen kuulusi olla. Kaikki toimenpiteet sitä ennen ovat vain askelia sitä kohti, eli älä lue tätä ohjetta kuin piru raamattua, vaan mieti miten juuri sinun datassa pääsisit oikeaan lopputulokseen.

Aluksi data oli tässä muodossa, useampaan eri välilehteen jaettuna.

Screen Shot 09-25-14 at 09.43 AM

Lopulta taulukko näytti tältä, kaikki yhdessä välilehdessä.

Screen Shot 09-25-14 at 01.17 PM

Eron näkee toki jo kaukaa, mutta kannattaa huomata erityisesti muutamat seikat alkuperäisestä:

  • Arvosanat ovat pystyssä samassa solussa siten, että vasemmalla on annettu arvosana, keskellä opiskelijoiden lukumäärä ja lopuksi prosenttiosuus. Nämä ovat erotettu välilyönnillä toisistaan.
  • Kurssien koodit, nimet ja päivämäärät ovat taulukon sivussa epämääräisessä järjestyksessä, kuitenkin siten, että vain yksi niistä on rivillä ja sarakkeessa kerrallaan.

Lopputuloksesta näet, kuinka yhdellä rivillä on vain ja ainoastaan yksi havainto arvosanojen määrästä kerrallaan siten, että esimerkiksi:

”4. huhtikuuta 2013 kurssillla HALL3020 annettiin arvosanaa ’4’ 12 kappaletta”

Aloitetaanpas!

Vaihe 1: Yhdistä välilehdet samaan taulukkoon

Tämä vaihe saattaa kuulostaa turhalta, mutta jos kaikki taulukot ovat samassa mudossa useammalla välilehdellä, voit yhdistää ne kerralla samaan taulukkoon.

Vaihe 2: Lisätään kurssien tiedot kaikille riveille

Koska kurssien tiedot olivat kaikki omissa sarakkeissaan, niin tämä on todella yksinkertainen vaihe. Ajatuksena on, että jos solu on tyhjä, sen arvo on sama kuin sen yläpuolella olevassa.

Excelille selität tämän kirjoittamalla:

=IF(solu kurssisarakkeessa=””;ylempi solu;solu kurssisarakkeessa)

eli esimerkiksi

=IF(B23=””;E22;B23)

Kopioi tämä kaava kaikkiin soluihin painamalla oikean alakulman +-näppäintä. Toista sama muille sarakkeille.

Screen Shot 09-25-14 at 11.59 AM

Vaihe 3: Kopioi kurssien tiedot arvosanojen viereen

Muotoilin vaiheen otsikon tarkoituksella hankalasti sen vuoksi, että huomio kiinnittyisi oikeaan asiaan. Tarkoituksena ei siis ole silmät ummessa kopioida jotain arvoja, vaan päämääränä on saada jokaisen arvosanan kohdalle tarvittavat tiedot kurssista.

Väliin jää siis väistämättä kokonaisia rivejä, joissa on virheitä ja epämääräisiä arvoja. Älä huolehdi niistä, sillä voit poistaa ne vielä myöhemmin.

Kun kaikki arvot vaiheessa 2 on kopioitu, siirretään kurssien tiedot taulukkon vasemmalle puolelle. Tämä helpottaa uusien arvojen lisäämistä myöhemmin, sillä solut eivät ole enää tiellä. Siirtäminen vaatii pari liikettä:

  • ensin kopioidaan ja liitetään aiemmin lasketut solut siten, että vain niiden arvot jäävät taulukkoon. Tämä onnistuu painamalla Ctrl + Alt + V ja valitsemalla ”Paste Values”, tai kopioimalla ensin ja valitsemalla Paste Values jälkikäteen.

Screen Shot 09-25-14 at 09.56 AM

  • Tämän jälkeen valitaan juuri liitetyt arvot ja siirretään ne taulukon vasempaan reunaan. Tämä onnistuu valitsemalla sarakkeet, painamalla Ctrl + X , klikkaamalla ensimmäisen sarakkeen kohdalla oikella ja Insert Cut Cells.

Screen Shot 09-25-14 at 09.56 AM 001

Lopuksi voit poistaa alkuperäiset solut, joissa on vielä kaavat.

Vaihe 4: Jaa teksti sarakkeisiin

Koska arvosanat, lukumäärät ja osuudet olivat samassa solussa, välilyönneillä eroteltuina, voit jakaa niiden arvot sarakkeisiin Text to Colums-työkalulla. Käytä erottimena välilyöntiä ja paina Finish. Arvot ovat omissa soluissaan. Jos numerot ovat muotoiltu siten, että niiden sisällä on välilyönti (esimerkiksi 2 100), niin kiroa taulukon laatija ja korjaa virheet käsin.

Screen Shot 09-25-14 at 09.52 AM

Vaihe 5: Korjaa prosentit prosenttiarvoiksi

Prosentit korjataan prosenttiarvoiksi luomalla uuteen sarakkeeseen niiden arvo/100 ja  kopioimalla tämän uuden sarakkeen arvot vanhan päälle.

Screen Shot 09-25-14 at 10.00 AM

Vaihe 6: Poista turhat ja epämääräiset arvot taulukosta

Tässä vaiheessa kaikki taulukon arvot ovat periaatteessa kunnossa, mutta niiden joukossa on paljon sekalaisia arvoja. Voit poistaa ne siten, että valitset turhat rivit ja painat Deleteä.

Tätä vaihetta nopeuttaa huomattavasti, jos epämääräiset arvot ovat kaikki samassa paikassa. Kikkailemalla Sort-työkalun kanssa saat järjestettyä ne samaan kohtaan. Esimerkissä esimerkiksi sarake Lukumäärä voidaan järjestää laskevien arvojen mukaan, jolloin taulukon pohjalle jää ne rivit, joissa arvo on tyhjä. Voit myös poistaa hypoteesien vastaiset havainnot tässä vaiheessa (vitsi vitsi).

Screen Shot 09-25-14 at 10.01 AM

Screen Shot 09-25-14 at 10.03 AM

Lukumäärämän mukaan järjestäminen jätti taulukkoon vain todellisia arvoja.

Vaihe 7: Poista duplikaatit

Kriittisesti tietoon suhtautuvat huomasivat heti ylemmästä taulukosta, että Excelistä löytyy sellainen näppärä työkalu, kuin Remove Duplicates. Tämän avulla taulukossa mahdolliset kaksi kertaa ilmenevät rivit voidaan poistaa. Voit valita sarakkeet, joiden mukaan duplikaatin tunnistaminen tapahtuu, jolloin esimerkiksi työkalu jättää huomioimatta mahdolliset erot ajassa.

Ai niin, muistithan antaa sarakkeille nimet, sillä ei tässä mitään luolamiehiä enää olla!

Screen Shot 09-25-14 at 10.14 AM

Vaihe 8: Lisätään taulukkoon oppiaineiden koodit

Koska kaikki kurssikoodit ovat samanmuotoisia, tämä on helppo tehtävä. Ensimmäiset neljä merkkiä saat LEFT-kaavalla (suomeksi VASEN):

=LEFT(solun sijainti;4)

Muista taas kopioida ja liittää vain arvot, niin suuren taulukon käsittely on nopeampaa!

Screen Shot 09-25-14 at 10.17 AM

Vaihe 9: Taulukko on valmis!

Screen Shot 09-25-14 at 12.58 PM

Näin helposti se kävi! Tällä kertaa taulukon muotoiluun tarvittiin näitä työkaluja, ensi kerralla ehkä muita. Esimerkiksi VLOOKUP tulee tarpeeseen, mikäli taulukkoon täytyy hakea arvoja toisesta taulukosta. Joskus taulukko ei näytä aluksi ollenkaan järkevältä, mutta usein saat sen käsiteltävään muotoon pienellä kikkailulla!

Tämän jälkeen voit muodostaa taulukon perusteella esimerkiksi Pivot-taulukolla, jota tullaan käsittelemään seuraavassa postauksessa.

Lähdeluettelo aakkosjärjestykseen – Word ja Excel yhdessä

 

Lähdeluetteloa ei saa lajiteltua Wordissa aakkosjärjestykseen, mutta tämän puutteen pystyy kiertämään ottamalla Excel avuksi. Vaikka tämänkin kikan avulla jää vielä hieman käsin tehtävää työtä, työ on kuitenkin kokonaisuudessaan paljon vaivattomampaa ja myös jälki on tarkempaa.

Vaihe 1: Ota varmuuskopio

Ei siitä ole haittaakaan!

Vaihe 2: Kopioi lähdeluettelo

Maalaat tekstin siniseksi ja Ctrl + C. Tuttu juttu.

Screen Shot 09-09-14 at 09.41 AM

Vaihe 3: Kopioi Exceliin

Voit koittaa ensin liittää tekstin perinteisesti Ctrl + V -komennolla. Joskus solut kuitenkin kopioituvat omituiseen muotoon, joten varmin tapa on käyttää Ctrl + Alt + V-komentoa ja liittää teksti Unicode Text-muodossa.

Screen Shot 09-09-14 at 09.43 AM

Vaihe 4: Levennä solut siten, että teksti näkyy kokonaan

Klikkaamalla tai vetämällä sarakkeen reunaa voit kasvattaa solujen leveyttä.

Screen Shot 09-09-14 at 09.43 AM 001

Vaihe 5: Järjestä aakkosjärjestykseen

Pidä lähdeluettelo valittuna ja paina Sort & Filter -> Sort A to Z, suomenkielisessä versiossa löytyy Lajittele & Suodata painikkeen alta.

Screen Shot 09-09-14 at 09.44 AM

Vaihe 6: Tarkista alkuperäinen lähdeluettelo

Jos listan kopioisi nyt Wordiin, siitä katoaisi muotoilut. Sen vuoksi viimeinen vaihe täytyy tehdä käsin. Pitämällä Exceliä toisessa ikkunassa ja Wordia toisessa voit tarkistaa jokaisen lähteen erikseen.

Lähteiden siirtäminen onnistuu helpoiten leikkaamalla ja liittämällä, eli maalaamalla teksti siniseksi, painamalla Ctrl + X ja liittämällä sen oikeaan kohtaan.

Screen Shot 09-09-14 at 09.44 AM 001

Jäikö kysyttävää? Jätä kommentti!

Satunnaisotos Excelillä – RAND-funktio

Kuvitellaanpa tilanne, jossa kerättäisiin tietoja yrityksen asiakastyytyväisyydestä ympäri Suomea. Asiakkaita voi olla tuhansia, joten populaatio eli perusjoukko on niin suuri, ettei jokaisen mielipidettä ole mahdollista kysyä. Sen vuoksi populaation pohjalta tulee muodostaa niin sanottu otoskehikko, johon jokaisella populaation jäsenellä on yhtä suuri todennäköisyys tulla valituksi. Menemättä tässä ohjeessa sen syvemmin tutkimusmenetelmiin, yhtäläinen todennäköisyys tulla valituksi on satunnaisotoksen kulmakivi. Hyvä artikkeli surveytutkimuksesta löytyy esimerkiksi täältä.

Tutkimusta varten hankittavat tiedot ovat yleensä jossain järjestyksessä, esimerkiksi aakkosjärjestyksessä tai aikajärjestyksessä. Sen vuoksi satunnaisotokseen ei voida ottaa vain esimerkiksi sataa viimeisintä tietoa; erot vastausnopeuksissa saattaisivat johtaa siihen että esimerkiksi hämäläisten yritysten osuus olisi korostunut. Tästä aiheutuisi niin sanottua systemaattista harhaa.

Tietojen satunnaistaminen on todella yksinkertainen prosessi, mutta vaatii pientä hoksaamista. Käytän tässä esimerkkinä listaa yrityksistä, johon on kerätty tietoja asiakastyytyväisyydestä. Otsikosta poiketen kokeilin vaihtelun vuoksi tehdä tämän ilmaisella LibreOfficella, jossa toimii samat kaavat kuin Excelissäkin.

Vaihe 1: Lista aakkosjärjestyksessä

Screenshot from 2014-09-02 11:36:40

Vaihe 2: Lisää sarake satunnaismuuttujalle

Tyhjä sarake taulukon eteen lisätään yksinkertaisesti painamalla taulukon ensimmäisen sarakkeen kirjainta (tässä tapauksessa A) oikealla, ja painamalla Insert Column, Lisää Sarake tai vastaavaa.

Taulukon tyhjän sarakkeen ensimmäiseen soluun lisätään satunnaismuuttuja. Satunnaismuuttujan kaava on sekä Excelissä, että LibreOfficessa (myös Google Docsissa) RAND(). Suomenkielisessä käännöksessä RAND on SATUNNAISLUKU. RAND on siitä erikoinen kaava, että sen perässä oleviin sulkuihin ei laiteta mitään, joten pelkkä RAND() riittää.

Screenshot from 2014-09-02 11:38:58

Lisää kaava ylimpään soluun ja klikkaa solun oikeassa alakulmassa olevaa +-merkkiä, jolloin kaava kopioituu koko sarakkeeseen.

Vaihe 3:  Järjestä satunnaisluvun mukaan

LibreOfficessa lajittelupainike on A – > Z näköinen, Excelissä vastaava löytyy Sort & Filter valikosta. Kun lajittelu on valmis, voit poistaa satunnaislukusarakkeen.

Screenshot from 2014-09-02 12:08:51

Vaihe 4: Valitse satunnaisotoksen koko

Tämä vaihe onkin sitten yllättävän yksinkertainen. Koska taulukko on satunnaisjärjestyksessä, jokainen siitä  otettu joukko on myös satunnaisotos. Tarvitsetko satunnaisotoksen kymmenestä yrityksestä? Valitse silloin kymmenen ylintä tietoa. Tarvitsetko 20? Valitse silloin 20.

Screenshot from 2014-09-02 11:41:50

 

EXTRA:

Kuten arvata saattaa, RAND-muuttujalla voi arpoa myös muita satunnaislukuja. Yksinään RAND arpoo lukuja väliltä 0-1. Jos tarvitset siis arvoja esimerkiksi väliltä 0-6, voit kertoa RANDin kuudella, eli:

=RAND()*6

Jos tarvitset satunnaisluvun joltain tietyltä väliltä esimerkiksi kouluarvosanoja varten (opettajille tiedoksi), voit lisätä RANDiin kokonaislukuja, eli:

=RAND()*6+4

Jos vastaukseksi riittää kokonaisluvut, voit käyttää myös yksinkertaisempaa RANDBETWEEN-kaavaa, eli:

=RANDBETWEEN(4;10)

 

Jäikö jotain kysyttävää? Jätä viesti!

Case Myyntiraportti ja mitä opin siitä – VLOOKUPin ja Pivot taulukon heikkouksista

Kävipä tässä niin, että sain tehtäväkseni toimeksiannon, jonka päämääränä oli selvittää asiakas- ja myyntirekisterin pohjalta tietoja esimerkiksi parhaista asiakassegmenteistä ja toimialoista. Näin mielenkiintoista projektia ei ollutkaan ennen tullut vastaan, joten päätinkin tarttua heti toimeen ja otin Excel esiin!

Tiedot rakentuivat samaan tyyliin kuin varmasti lukuisissa suomalaisissa yrityksissä, eli yhdessä taulukossa oli tiedot myynneistä, toisessa tiedot asiakkaista ja kolmannessa vielä vähän lisätietoa asiakkaista.Näiden pohjalta oli tarkoitus luoda muutamia tyypillisiä raportteja, eli jakaa myynnit esimerkiksi ikäryhmittäin, sukupuolittain ja toimialoittain. Taulukkojen ainoa linkki toisiinsa oli asiakasnumero, eli ID, joten ratkaisu oli selvä: haetaan tarvittavat tiedot asiakkaista myyntirekisteriin käyttämällä VLOOKUP-kaavaa, ja luodaan sen jälkeen Pivot-taulukko, jonka avulla myyntirekisteriä voidaan pyöritellä tarpeen mukaan.

Olen käyttänyt tätä lähestymistapaa lukemattomia kertoja aiemmin, mutta en kuitenkaan koskaan näin suuressa mittakaavassa. Korostettakoon siksi näin aluksi, että tämä lähestymistapa ei ole missään nimessä väärä, vaan monessa tapauksessa itseasiassa paras vaihtoehto. Tätä lähestymistapaa käyttämällä kohtasin kuitenkin muutamia ongelmia, joita aion esitellä ratkaisuineen tässä kirjoituksessa.

1. Laskentateho

Suuria tietokantoja käsitellessä vastaan tulee auttamatta puutteet laskentatehossa. Omassa pari vuotta vanhassa läppärissäni on 2,30GHz tuplaydinprosessori ja 4,00 Gt RAM:ia, ja vaikka se ei mikään tehomylly olekaan, en ole koskaan aiemmin huomannut että Exceliä käyttäessä loppuisi tehot kesken. Tarve suurelle laskentateholle tulee siitä, kun solujen arvot haetaan toisesta taulukosta. Esimerkiksi asiakkaan nimen, iän ja paikkakunnan etsiminen VLOOKUPilla 10 000 nimen listasta 20 000 myynnille vaatii prosessorilta paljon.

Ongelmia laskentatehon loppumiseen voi ratkaista esimerkiksi asettamalla Excelistä automaattinen laskeminen pois. Tämä löytyy valikosta Formulas -> Calculation. Kun laskeminen on asetettu manuaaliseksi, voit laskea solut koko työkirjasta F9- näppäimellä tai sheetistä painamalla shift + F9.

Screen Shot 08-27-14 at 11.31 AM

Toinen tapa keventää taulukkoa on poistaa soluista kaavat ja jättää taulukkoon pelkät arvot. Tähän ei tietääkseni ole mitään poppaskonstia olemassa, joten helpoin tapa on (1) kopioida koko sarakkeen arvot ensin tyhjään sarakkeeseen, jonka jälkeen (2) leikkaat nämä arvot alkuperäisen päälle. Tämä konsti on sinänsä tehokas, mutta kääntöpuolena on etteivät muutokset alkuperäisessa taulukossa enää päivity.

Ai niin, tähän väliin voisi muuten mainita että ennen kuin teet mitään, kannattaa ensimmäisenä tarkistaa lähtödata, ettei kaikkea tarvitse tehdä kahteen kertaan. (Terveisiä vuoden asiakas ”asfafsafs”:lle, joka ostit tuotteita miljoonalla!)

2. Kertaluonteisuus

Äskeiseen esimerkkiin liittyen, pelkkä VLOOKUP-ja Pivot-taulukko – kombinaatio toimii huonosti pitkäkestoisessa projektissa. Koska uusien arvojen laskeminen tai hakeminen vaatii paljon laskentatehoa, käytännössä soluissa saattaa olla vain pelkät arvot. Uusien arvojen lisääminen on silloin kyllä teoriassa mahdollista, mutta vaatii melko paljon aikaa ja vaivaa – päivitys, kaavat uudestaan, kaavat pelkiksi arvoksi.

Ideaalitilanteessa taulukkoon päivittyisi tiedot uusista myynneistä ja ajankohtaiset tiedot asiakkaista helposti. Järkevin tapa onkin pitää tiedot taulukkomudossa (luodaan painamalla Ctrl +  T), jolloin uudet rivit lisääntyvät dataan automaattisesti. Voit myös laskea taulukkoon uusia arvoja samaan tyyliin kuin tavalliseenkin taulukkoon. Taulukon käyttöönottoa puoltaakin erityisesti kätevämpi tapa viitata alueisiin – kun normaalit Excel-kaavat viittaavat soluun tai alueeseen, DAX-kaavat viittaavat sarakkeeseen,

Vrt:

=Count(A:A)

Laskee arvojen lukumäärän sarakkeessa A.

=Count(laskut[id])

Laskee arvojen lukumäärän taulukon ”laskut” sarakkessa ”id”.

Tavallista suuremman aloituskynnyksen vuoksi peruskäyttäjälle jää siten ainoaksi vaihtoehdoksi tietojen lisääminen kopioimalla, jonka vuoksi lopputulos on aika staattinen – käytännössä pelkkä katsaus yhteen hetkeen. Seuraavaa raporttia varten solut täytyisi laskea aina uudestaan ja uudestaan. Tämän vuoksi suosittelen lämpimästi tutustumaan Microsoft Office Excel-taulukoihin.

3. Pivot-taulukoinnin rajat

Enpä olisi etukäteen uskonut, mutta projektia toteuttaessani Pivot-taulukoinnin rajat tulivat vastaan. Ongelma ilmeni, kun oli aika selvittää toimialojen sukupuolijakaumat. Lähdin toteuttamaan tätä käyttämällä binäärinä sukupuolta (0/1) ja laskemalla tämän jälkeen toimialakohtaisesti käyttäjien sukupuolen keskiarvon. Lopputuloksena olisi silloin prosenttiluku, esim. 17%, jota tulkittaisiin siten että 17% on sukupuolta 1.

Koska moni asiakas toimi useammalla kuin yhdellä toimialalla, luvut täytyi laskea myyntikohtaisesti, eli hakea VLOOKUP:lla tieto asiakkaan sukupuolesta asiakas ID:n perusteella. Tämän jälkeen loin Pivot-taulukon, johon tuli pystyyn toimialan koodit ja niiden viereen keskiarvot sukupuolista. Eli tyyliin:

Screen Shot 08-27-14 at 01.20 PM 001

Tässä vaiheessa hälytyskellot alkoivat soimaan, sillä luvut eivät näyttäneet lainkaan oikeilta. Pienen tarkistelun jälkeen huomasinkin missä meni vikaan: koska tiedot olivat myyntirekisteristä, jokainen myynti tulkittiin taulukossa omaksi havainnoksi sukupuolesta. Eli käytännössä esimerkiksi yksi erittäin aktiivinen miesasiakas pystyisi muuttamaan toimialan sukupuolijakauman virheelliseksi.

Googlen perusteella vastassa oli yleinen ongelma, joka on korjattu Excel 2013:ssa uudella Distinct Count- mahdollisuudella. Vaihtoehdot jäävät kuitenkin vähiin vanhempia versioita käytettäessä. Koska luvut olivat raportoinnin kannalta todella oleellisia, oli aika improvisoida. Ratkaisin tämän laatimalla ensin Pivot-taulukon kaikista käyttäjistä toimialalla, kopioimalla sen uuteen taulukkoon ja laatimalla näistä arvoista jälleen uuden Pivot-taulukon. Ratkaisu oli hieman kiven alla, mutta pääasia on, että luvut pitivät paikkaansa.

Lopputulos:

Ottaen huomioon kohdat 1, 2 ja 3, Excel peruskäytössään on hieman rajoittunut raportointityökalu. Onneksi raportointimahdollisuuksia on kuitenkin mahdollista laajentaa Business Intelligenseä tai muuta useamman taulukon analyysiä ajatellen hyödyntämällä Excelin DAX-kaavoja. Koska nämä ovat kuitenkin aivan oma juttunsa, niiden käyttäminen vaatii perehtymistä.

Toinenkin mielenkiintoinen asia tuli esille. Usein siitä kuulee puhuttavan, mutta vasta tätä projektia toteuttaessa todella ymmärsin miksi Power Pivot-lisäosa on niin suosittu. Esimerkiksi kohdassa 3 esitelty Distinct Count on tällä hetkellä mahdollista laskea Power Pivotilla yhtä helposti kuin tavalliset Count ja Sum – arvot. Myös arvojen hakeminen eri taulukoista on huomattavasti helpompaa Power Pivotilla. Siksi suosittelen kokeilemaan ilmaista Power Pivot-lisäosaa, jonka saat ladattua Microsoftin sivuilta.

Excel 2010:lle täältä.

Mitä ajatuksia tämä kirjoitus herätti? Minkälaisia ongelmia sinä olet kohdannut Pivot-taulukoissa?

Painotettu keskiarvo Excelillä – SUMPRODUCT-funktio

Tässä esimerkissä käymme läpi, kuinka lasketaan painotettu (aritmeettinen) keskiarvo Excelillä.

Vaikka tämä saattaa tuntua helpolta, niin on hyvä ensin palauttaa mieleen se, miten keskiarvo lasketaan, eli:

keskiarvo

Esimerkkinä käytetään tällä kertaa Matrix-elokuvan saamia arvosteluja IMDB:stä.

Screen Shot 08-19-14 at 09.57 AM

Ensin kopioimme taulukon Exceliin, jolloin saamme seuraavan näkoisen taulukon. Prosenttiuluvut voimme poistaa, sillä niitä ei tarvita tässä esimerkissä.

Screen Shot 08-19-14 at 09.54 AM

Vaihe 1: Lasketaan jokaisen luokan havainnot yhteen (eli havainto + havainto + … )

Tämä on olennainen osa painotettujen keskiarvojen laskemisessa. Vaikka tämä saattaa kuulostaa hienolta, kyse on oikeastaan täysin samasta asiasta kuin tavallisessakin keskiarvossa.

Periaatteessa voisimme laskea tämän jokaisen luokan kohdalla erikseen, eli B2*C2 + B3*C3  … jne. ja laskea lopuksi nämä kaikki yhteen. Excelissä on kuitenkin juuri tätä varten suunniteltu funktio, eli SUMPRODUCT (suomeksi TULOJEN.SUMMA). Se toimii siten, että valitset ensin vasemmanpuoleisen sarakkeen, lisäät puolipisteen erottimeksi (Google Docsissa ja LibreOfficessa usein pilkku) ja sen jälkeen valitset oikeanpuoleisen sarakkeen.

Silloin Excel kertoo jokaisen rivin kohdalla solut yhteen ja lopuksi antaa kaikkien yhteenlasketun summan.

Näin ollen kaavan ensimmäinen osa on:

=SUMPRODUCT(B2:B11;C2:C11)

Screen Shot 08-19-14 at 10.14 AM

 

Vaihe 2: Lasketaan kaikkien havaintojen lukumäärä yhteen (eli havaintojen lukumäärä)

Tämä on helppo vaihe, johon voidaan käyttää yksinkertaisesti SUM-funktiota (suomeksi SUMMA)

=SUM(B2:B11)

Screen Shot 08-19-14 at 10.21 AM

Vaihe 3: Yhdistetään vaiheet 1 ja 2

Tässä vaiheessa jäljelle on jäänyt enää aiempien kahden funktion yhdistäminen. Tämän olisi voinut toki tehdä jo suoraan vaiheessa 2, mutta päätin kuitenkin kirjoittaa ne tähän erilleen selvyyden vuoksi.

= Vaihe 1 / Vaihe 2

=SUMPRODUCT(B2:B11;C2:C11) / SUM(B2:B11)

Lopputulos:

IMDB:ssä Matrixille annettujen pisteiden aritmeettinen keskiarvo on 8,594795622.

Screen Shot 08-19-14 at 10.35 AM

Vinkki:

Voit pyöristää luvun esimerkiksi yhden desimaalin tarkkuudelle painamalla alla näkyvän kuvan painiketta.

Screen Shot 08-19-14 at 10.36 AM

 

Jäikö kysyttävää? Jätä kommentti!

Poista tyhjät rivit listasta Excelillä

Update 2017: Tämä ohje on suhteellisen hankala tapa ja toimii siten parhaiten kiireetömien syysiltojen ajanvietteeksi. Alla olevasta linkistä pääset uuteen ja tehokkaampaan ohjeeseen!

Helppo tapa poistaa tyhjät rivit taulukosta!

 

Kesä alkaa olla takanapäin ja mölkkykausi lähenee loppuaan. Pitkän mölkkykauden jälkeen monella saattaakin painaa mielessä kysymys: mitä mölkyn jälkeen? Syksy onkin hyvä hetki aloittaa jälkipelien puiminen, joten on aika kaivaa kesän pistetilastot esille.

Kotikoneella vastaan saattaa tulla ikävä yllätys: Excel-taitoisena esittäytynyt pisteidenlaskija onkin keskittynyt enemmän taulukon ulkoasun hiomiseen ja jättänyt tyhjiä rivejä taulukkoon! Näin rakennetun taulukon hyödyntäminen on usein käytännössä mahdotonta, joten niistä täytyisi päästä eroon.

Näillä ohjeilla siis:
Poistat tyhjät rivit Excel-taulukosta menettämättä järjestystä

Screen Shot 08-14-14 at 10.08 AM

Vaihe 1: Lisää taulukkoon avuksi lista numerojärjestyksessä

Voit säästää vaivaa kirjoittamalla vain muutaman ensimmäisen, valitsemalla nämä ja kopiomalla ne taulukon alareunaan asti vetämällä mustasta +-merkistä. Lopulta taulukko näyttää vaikkapa tältä.

Screen Shot 08-14-14 at 10.09 AM

 

Vaihe 2: Järjestä taulukko jonkin sellaisen sarakkeen mukaan, jossa on tietoja

Valitse ensin ylin rivi, ja klikkaa Sort & Filter -> Custom Sort.

Screen Shot 08-14-14 at 10.09 AM 001

Valitse esim tässä tapauksessa VOITTAJAT: ja järjestä se aakkosjärjestyksen mukaan seuraavasti.

Screen Shot 08-14-14 at 10.10 AM 001

Koska et ole valinnut koko taulukkoa, Excel ehdottaa että siihen lisätään myös valinnan (tässä tapauksessa otsikkorivin) viereiset tiedot. Valitse silloin Expand the selection. Näin menettelemällä säästää usein paljon vaivaa, varsinkin isoissa taulukoissa.

Screen Shot 08-14-14 at 10.10 AM

Vaihe 3: Poista numerot tyhjiltä riveiltä

Jos numeroita on monta, valitse ylin solu ja paina Ctrl + Shift + Alas. Näin valitset ne kaikki.

Screen Shot 08-14-14 at 10.11 AM

Vaihe 4: Järjestä taulukko uudelleen numeroiden mukaan

Tämä tapahtuu samoin kuten vaiheessa 2.

Screen Shot 08-14-14 at 10.12 AM

Lopuksi poista vielä apuna käytetyt numerot. Nyt taulukko on kunnossa ja spekulointi voi alkaa!

Screen Shot 08-14-14 at 10.12 AM 001

Jäikö kysyttävää? Jätä kommentti!