Author Archives: Petteri

Etäisyyden laskeminen koordinaateista Excelillä

Kuluneen viikonlopun aikana muutin blogini kaukaa Texasista Helsinkiin. Aikoinaan blogia perustaessani en arvannut kuinka suosituksi tämä tulisi kasvamaan, joten päädyin silloin valitsemaan opiskelijabudjettiin kaikista sopivimman vaihtoehdon, joka sattui sijaitsemaan kaukana rapakon toisella puolella. Tämä valinta osoittautui aika nopesti hitaaksi, mutta vasta nyt uskalsin viimein tarttua härkää sarvista ja muuttaa kaiken sisällön tänne kotoisaan Pohjolaan, lähemmäksi lukijoita. Kiitos siis teille lukijoille kärsivällisyydestä!

Tarkkoja lukuja uuden serverin nopeudesta ei vielä tässä vaiheessa ole, mutta alustavien lukujen perusteella sivut latautuvat nyt noin 3 sekuntia nopeammin, eli tuhatta lukijaa kohden aikaa säästyy nyt noin 3 sek * 1000 / 60 (sek/min) = 50 minuuttia.

Etäisyyden laskeminen koordinaateista

Ajattelin tähän yhteyteen jakaa yhden kaavan, josta olen jo pitkään ajatellut kirjoittaa. Kyseessä on siis etäisyyden laskeminen koordinaateista käyttäen desimaaliasteita. Kaavan olen löytänyt täältä, josta löytyy myös tarkempi kuvaus kaavan toiminnasta.

Lasketaan seuraavaksi kyseisellä kaavalla Houstonin ja Helsingin välinen etäisyys.

Kaava:

=ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) *6371

Ellet ole erityisen kiinnostunut kahden pisteen välisen etäisyyden laskemisesta pallon pinnalla, niin voit kopioida ja käyttää kaavaa sellaisenaan. Kirjoituksen lopussa arvioin kaavan luotettavuutta tarkemmin.

Suomenkielisellä versiolla kaava on seuraava:

=ACOS(COS(RADIAANIT(90-B2)) *COS(RADIAANIT(90-B3)) +SIN(RADIAANIT(90-B2)) *SIN(RADIAANIT(90-B3)) *COS(RADIAANIT(C2-C3))) *6371

Kaava käyttää laskennassa desimaaliasteisia koordinaatteja.

Syöttämällä ensimmäisen kaupungin koordinaatit alueelle B2:C2 ja toisen kaupungin koordinaatit alueelle B3:C3, saamme vastaukseksi 8639,13 kilometriä.

Kaavan antaman tuloksen arviointi

Verrataan seuraavaksi kaavan antamaa tulosta Googlen ensimmäisenä hakutuloksena hakusanalla “Distance between two cities” olevan distancecalculator.net-sivuston antamaan tulokseen, jota voinemme pitää kohtuullisen luotettavana.

Erotukseksi jää siten noin 3 kilometriä.

Näin pieni ero saattaa johtua siitä, että kaupunkien koordinaatteina on käytetty hieman eri pistettä. Valitettavasti distancecalculator.net -sivu ei ilmoita laskelmissa käytettäviä koordinaatteja, niin tätä hypoteesia ei voi testata. Kaavassa käytetty arvio maapallon säteestä (6371 kilometriä) ei ole myöskään tarkka arvo, joten ero saattaa johtua myös siitä.

Eron ollessa kuitenkin näin pieni, voimme kuitenkin olettaa, että kaava antaa oikean ja riittävän kuvan näiden kaupunkien välisestä etäisyydestä.

Helppo tapa poistaa tyhjät rivit taulukosta

Tässä ohjeessa esitellään kaksi tapaa poistaa tyhjät rivit taulukosta.

Tapa 1: Tämä tapa poistaa tyhjät solut muuttamatta niiden järjestystä

Aloitus: Lista nimistä, joiden välissä on tyhjiä rivejä.

Vaihe 1: Valitse taulukon alue

Vaihe 2: Lisää taulukkoon suodattimet painamalla Ctrl + Shift + L

Taulukon header – eli otsikkotietojen viereen ilmestyy nuolet kun taulukkoon lisätään suodatin.

Vaihe 3: Klikkaa otsikkorivillä näkyvää suodatinta ja valitse (Blanks)

Vaihe 4: Nyt taulukossasi näkyy vain tyhjät rivit (Huom rivinumero vasemmalla)

Vaihe 5: Valitse uudestaan tyhjät rivit  (mutta ei otsikkoja), klikkaa oikealla ja valitse Delete Row

Vaihe 6: Tyhjennä suodatin valitsemalla Clear Filter From …..

Lopputulos: Taulukossa ei ole enää tyhjiä rivejä

Tapa 2: Supernopea tapa, joka saattaa vaikuttaa tietojen järjestykseen

Alkutilanne: Taulukon tietojen välissä tyhjiä rivejä

Vaihe 2: Valitse taulukko ja lisää suodattimet painamalla Ctrl + Shift + L

Vaihe 3: Klikkaa filtteriä ja laita tiedot aakkosjärjestykseen

Lopputulos: Taulukossa ei ole tyhjiä rivejä, mutta niiden järjestys on muuttunut

Huom! Koska taulukko oli lista nimistä niiden lukumäärän mukaan, taulukon järjestys ei olisi muuttunut jos tiedot olisi järjestetty Lukumäärän mukaan. Toisin sanoen, jos olet varma minkä perusteella taulukko on järjestetty niin Tapa 2 on nopein ja yksinkertaisin keino poistaa tyhjät rivit.

Jos taas listalla olevien tietojen järjestyksellä on merkitystä, Tapa 1 on paras valinta.

Lähde: Väestöjärjestelmän etunimitilasto

PS. Jos tämä tuntui liian helpolta, niin käy lukemassa myös parin vuoden takainen hankala ohje!

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

Y-tunnus Excelissä

Jos olet eksynyt tälle sivulle, niin todennäköisesti etsit kaavaa jolla voit nopeasti tarkistaa, onko Y-tunnuksen tarkistenumero oikein Excelissä.

Onneksi olkoon! Olet saapunut oikeaan paikkaan.

Y-tunnus koostuu 7 numerosta, yhdestä väliviivasta ja tarkisteesta.

Alla oleva kaava tarkistaa, onko solussa A1 olevan Y-tunnuksen tarkistenumero oikein, ja antaa solun arvoksi joko TRUE tai FALSE.

=(IF(MOD(LEFT(A1;1)*7+MID(A1;2;1)*9+MID(A1;3;1)*10+MID(A1;4;1)*5+MID(A1;5;1)*8+MID(A1;6;1)*4+MID(A1;7;1)*2;11)=0;0;11-MOD(LEFT(A1;1)*7+MID(A1;2;1)*9+MID(A1;3;1)*10+MID(A1;4;1)*5+MID(A1;5;1)*8+MID(A1;6;1)*4+MID(A1;7;1)*2;11)))=NUMBERVALUE(RIGHT(A1;1))

Kaava saattaa äkkiseltään vaikuttaa hankalalta selittää -mutta ei huolta!

Käytännössä kaava kertoo asetuksen mukaisesti Y-tunnuksen luvut merkki kerrallaan, summaa ne yhteen ja laskee jakojäännöksen perusteella tarkisteen. Tämän jälkeen kaava vertaa sitä solussa A1 olevan Y-tunnuksen viimeiseen merkkiin (eli tarkisteeseen) ja kertoo täsmäävätkö ne.

Tarkisteen laskemista varten voit jättää perästä vertailun pois, eli:

=IF(MOD(LEFT(A1;1)*7+MID(A1;2;1)*9+MID(A1;3;1)*10+MID(A1;4;1)*5+MID(A1;5;1)*8+MID(A1;6;1)*4+MID(A1;7;1)*2;11)=0;0;11-MOD(LEFT(A1;1)*7+MID(A1;2;1)*9+MID(A1;3;1)*10+MID(A1;4;1)*5+MID(A1;5;1)*8+MID(A1;6;1)*4+MID(A1;7;1)*2;11))

Vinkki:

Kun olet kopioinut kaavan taulukkoosi, valitse solu ja paina Ctrl + H. Nyt voit päivittää kaavan kaikki A1 viittaukset haluaamaasi soluun, esimerkiksi E4.

Katso alla olevasta linkistä tarkemmat ohjeet tarkisteen laskemiseen.

Lähde: http://tarkistusmerkit.teppovuori.fi/tarkmerk.htm

Henkilötunnus Excelissä

Blogiin tulleen kysymyksen innoittamana päätin kirjoittaa postauksen henkilötunnuksen käytöstä Excelissä. Kuten kaikki varmasti tietää, niin henkilötunnus (ent. sosiaaliturvatunnus) koostuu syntymäpäivästä, välimerkistä, joka osoittaa syntymävuosisadan, sekä loppuosasta.

Sen vuoksi pienellä kikkailulla tämän pohjalta voidaan laskea henkilön syntymäpäivä ja esimerkiksi ikä. Jos tulit etsimään vastausta siihen, kuinka laskea ikä henkilötunnuksen perusteella excelissä ja sinulla on perustaidot hallussa, niin tällä kaavalla pääset alkuun:

=IF(MID(B4;7;1)=”-“;YEARFRAC(E4;DATE(“19″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2));1);IF(MID(B4;7;1)=”A”;YEARFRAC(E4;DATE(“20″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2));1);NA()))

kokonaisuus

Tämän pitkän ja tuskallisen näköisen kaavan logiikka etenee seuraavasti:

(on muuten pisin tässä blogissa julkaistu kaava tähän mennessä!)

  1. Tarkistetaan, onko seitsemäs merkki “-” vai “A”, eli syntymävuosisata
  2. Jos “-“, syntymäpäivä saadaan kaavalla DATE(“19″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2))
  3. Ikä lasketaan kaavalla YEARFRAC(E4;DATE(“19″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2));1), jossa solussa E4 on päivämäärä, jona ikä halutaan. Voit korvata sen kaavalla NOW() jos haluat laskea iän tänään.
  4. Jos seitsemäs merkki on “A”, henkilö on syntynyt tällä vuosituhannella ja syntymäpäivä saadaan kaavalla DATE(“20″&MID(B4;5;2);MID(B4;3;2);LEFT(B4;2))
  5. Soluun tulee täten henkilön ikä vuosina laskettuna solussa B4 olevan hetun perusteella solussa E4 olevana päivänä

Huomion arvoisia asioita:

Valitettavasti Excel sopii huonosti ennen 1900 vuotta olleiden päivämäärien käsittelyyn, joten tämä kaava ei sovellu 1800-luvulla syntyneiden henkilöiden (esim. Mannerheim ja Sibelius) henkilötunnusten käsittelyyn. Näissä tapauksissa (ja kaikissa, joissa hetun 7. merkki on jotain muuta kuin “-” tai “A”) kaava palauttaa virheen #N/A.

Muistathan että henkilötunnusta voi käyttää henkilön yksilöimiseen, mutta ei tunnistamiseen! Lisää aiheesta täältä: http://www.hs.fi/kotimaa/a1477187628597

Mikä on sinun henkilötunnuksesi? Ethän kirjoittaisi sitä kuitenkaan mihinkään julkisesti?

INDEX MATCH – funktio, eli VLOOKUP ja paljon enemmän

INDEX(MATCH) on yksi Excelin tehokkaimmista kaavoista. Sitä voidaan käyttää VLOOKUPin tavoin, mutta toisin kuin VLOOKUP, sen toiminnallisuus ei rajoitu pystysuoriin hakuihin ja voit esimerkiksi lisätä taulukkoihisi sarakkeita huoletta ilman, että kaavat menevät solmuun. Moni ammattilainen onkin korvannut VLOOKUPin kokonaan INDEX MATCHillä ja käyttää sitä tänäänkin päivittäisessä työssään.

INDEX MATCH koostuu itseasiassa kahdesta funktiosta:

INDEX (suomeksi INDEKSI) ja MATCH (suomeksi VASTINE). Lyhyesti selitettynä INDEX-funktio toimii siten, että sille

1.syötetään lista lukuja, joista se

2. palauttaa listasta järjestykseltään käyttäjän haluaman luvun.

Yksistään tästä funktiosta lienee siis harvemmin käytännöllistä hyötyä. Yhdessä MATCHin kanssa (joka hakee taulukosta arvon ja palauttaa sen järjestysluvun, lue lisää tästä) ne muodostavat kuitenkin sellaisen dynaamisen duon, jota Excel-asiantuntijat ovat jo pitkään ennustaneet VLOOKUPin tuhoajaksi.

VLOOKUP löytyy kuitenkin vielä joistakin taulukoista. Yksi syy siihen lienee sen yksinkertaisuus. INDEX MATCH koostuu nimittäin kahdesta eri funktiosta, joten sen opetteleminen vaatii hieman kärsivällisyyttä. Suosittelenkin siis ottamaan nyt hyvän asennon ja rentotutumaan hetkeksi – tämän kirjoituksen luettuasi olet huomaamattasi oppinut käyttämään kahta funktiota ja jopa yhdistämään ne!

TEHTÄVÄ: Kuinka paljon kala on?

Luonnonvarakeskuksen tilastotietokannasta löytyy kalan tuottajahinnat vuodelta 2015.

taulukko

Kalan tuottajahinnat 2015

Taulukossa on 19 eri kalalajin tuottajahinnat vuodesta 2006 vuoteen 2015, perkaamattomina ja perattuina. Selvitetään siitä, kuinka paljon kilo perattua siikaa maksoi vuonna 2008 käyttämällä INDEX MATCH -funktiota. INDEX MATCH -funktio tarvitsee silloin kolme arvoa:
=INDEX( *1* ; MATCH( *2* ; *3* ;0))

*1* lista, jossa tulos on

*2* hakuarvo

*3* lista, josta hakuarvo löytyy

RATKAISU:

*1*   Syötetään INDEX-funktiolle lista luvuista.

lista-luvuista

Tässä tapauksessa olemme kiinnostuneita vuoden 2008 luvuista, joten valitsemme sen sarakkeen luvut.

*2* Annetaan INDEX-funktion sisällä olevalle MATCH-funktiolle hakuarvo.

match

Tässä tapauksessa valitsemme solun B20. Myös lainausmerkkien sisällä teksti “Siika” toimisi.

*3* Annetaan MATCH-funktiolle lista, josta Siika etsitään.

lista-kaloista

*4*  [match_type], laita tähän arvoksi 0

nolla

Ja päätä funktio laittamalla sulut kiinni ja paina Enter.

tulos

TULOS: Näin ollen saamme vastaukseksi 4,44€

Lopuksi:

Tämän yksinkertaisen esimerkin tarkoituksena on näyttää, kuinka INDEX MATCHiä voidaan käyttää kuten VLOOKUPia. INDEX MATCHin erinoimaisuus ei kuitenkaan jää todellakaan vain tähän!

  1. Voit käyttää INDEX MATCHiä myös vaakasuoraan hakuun, jolloin valitset vain vaakasuorat taulukot.
  2. Hae arvoja, jotka sijaitsevat sarakkeen vasemmalla puolella (VLOOKUP ei tähän pysty!)
  3. Voit lisätä taulukkoon uusia sarakkeita ja INDEX MATCH pysyy menossa mukana.
  4. Sarakkeita ei tarvitse laskea toisin kuin VLOOKUPia käytettäessä.
  5. INDEX MATCH on pohja monelle muullekin kaavalle. Kun osaat tämän, voit pienellä vaivalla hakea arvoja esimerkiksi kaksiuloitteisesta taulukosta INDEX MATCH MATCH -funktiolla tai hakea arvoja kahden tai useamman sarakkeen arvon perusteella.

MATCH-funktio – Hae arvon sijainti listasta

MATCH-funktio (tai VASTINE niille jotka käyttävät suomenkielistä käännöstä) kertoo vastaavuuden sisältävän solun sijainnin listalla.

Tämä edeltävä lause kaikessa yksinkertaisuudessaan sisältää kaksi tärkeää pointtia:

  1. Vastaavuus = Tämä funktio hakee vastaavuuksia. Voit siis käyttää sitä aivan VLOOKUPin tapaan esimerkiksi silloin, kun riittää tieto siitä, onko jokin arvo listassa vai ei.
  2.  Sijainti = Tässä piilee MATCH-funktion mahtavuuden avain. Parhaiten MATCH-funktiota tuleekin hyödynnettyä yhdessä muiden funktioiden kanssa. Erityisesti INDEX()-funktion kanssa ne muodostavat sellaisen killerikombon, jonka opittuasi et enää haikaile VLOOKUPin perään!

Tämä yksinkertainen funktio kuuluukin siksi jokaisen ajan hermoilla olevan Excelin käyttäjän työkalupakkiin.

Kuinka MATCH-funktio toimii?

lista

Otetaan esimerkiksi lista henkilöistä. Ensimmäisessä sarakkeessa on etunimi ja toisessa ikä. Vinkki: Jos käyttämässäsi aineistossa tiedot on erotettu esimerkiksi pilkulla, voit käyttää apuna Text-To-Columns työkalua.

Tehtävä: Kuinka mones nimi Emilia on listassa?

=MATCH( *1*  ;  *2*  ; *3*)

MATCH-funktio tarvitsee toimiakseen kolme eri kriteeriä.

*1* lookup_value eli Arvo, jota haetaan

Tähän kohtaan voit kirjoittaa arvon hapsujen sisällä, eli “Emilia”, tai viitata suoraan soluun eli esimerkiksi E5. Jos haet numeroa, niin jätä hapsut pois.

Vinkki:  Voit hakea myös tekstin osaa laittamalla tähdet hapsuihin ja erottamalla ne &-merkillä. Esimerkiksi “*”&”Hel”&”*”

*2* lookup_array eli Lista, josta haetaan

Tähän kohtaan tulee lista, jossa haettavan arvo pitäisi löytyä. Esimerkkimme tapauksessa MATCH-funktion tähän kohtaan tulee arvo B3:B19

*3* match_type eli Kirjoita tähän 0

Lyhyesti: Kirjoita tähän kohtaan arvo 0.

Muita arvoja (-1 tai 1) käytetään tilanteissa, joissa sekä haettava arvo- että lista ovat numeerisia ja järjestyksessä (nouseva tai laskeva). Tällöin funktio palauttaa osuman puuttuessa seuraavan/edellisen luvun järjestyksen.

(Jos joku käyttää näitä muita arvoja esim. päivittäisessä työssään, niin jätä kommentti! Itse käytän näitä niin harvoin, että on vaikea keksiä käytännön esimerkkiä, jossa niitä käytettäisiin.)

Lopullinen kaava näyttää silloin esimerkiksi tältä:

=MATCH(E5;B3:B18;0)

emilia

Ja palauttaa arvon 11.

tulos

Missä sinä käytät MATCH-funktiota? Jätä kommentti!

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!

Vaihe 1: Lasketaan kuinka paljon rahaa maksataan takaisin

Kuukausia on 12, kuukausierä on 16,36€, joten rahaa maksetaan takaisin 12*16,36 € = 196,32 €

Screen Shot 10-06-14 at 12.01 PM

Vaihe 2: Lasketaan takaisin maksettavan summan ja hinnan erotus

Tämä on yksinkertainen vähennyslasku.

Screen Shot 10-06-14 at 12.01 PM 001

Vaihe 3: Laske koron osuus alkuperäisestä summasta

Eli toisin sanoen jaa koron määrä euroissa hinnalla.

Screen Shot 10-06-14 at 12.02 PM

Huom! Vertailun vuoksi taulukossa on esitettynä myös Korottoman Klarna-erämaksuvaihtoehdon korko. Älä missään nimessä sekoita tätä todelliseen vuosikorkoon!

Vaihe 4: Totea lopputulos

Screen Shot 10-06-14 at 12.10 PM

Kuten huomaat, asiakkaalle todellisen vuosikoron (64%) ilmoittaminen saattaisi antaa huonon kuvan erämaksuvaihtoehdosta. Suosittelenkin esimerkiksi pikavippifirmoja ja pankkeja ilmoittamaan korkonsa Korottomana Klarna-korkona (0%), joka antaa asiakkaalle huomattavasti paremman kuvan tästä maksuvaihtoehdosta!

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!