Jatketaan Excelin aika-arvojen kertaamista. Tässä postauksessa esittelen pari käyttökelpoista funktiota ja lisää esimerkkejä aika-arvoilla laskemisesta.
Päivittyvän päiväyksen ja kellonajan lisääminen
Päiväys tai kellonaika voidaan lisätä staattisena, johon edellisessä artikkelissa esittelin kaksi näppäinyhdistelmää. Seuraavat funktiot lisäävät taulukkoon päiväyksen ja kellonajan, jotka päivitetään ajan tasalle aina kun taulukko lasketaan uudelleen: NYT (NOW) palauttaa käyttöjärjestelmän tiedoista saatavan kuluvan hetken päiväyksen ja kellonajan. TÄMÄ.PÄIVÄ (TODAY) palauttaa vain kuluvan päivän päiväyksen. Funktiot eivät tarvitse mitään argumentteja, joten riittää kun niiden jäljessä olevat sulkeet ovat paikoillaan, esimerkiksi NYT().
Esimerkkejä päiväyksillä laskemisesta
Edellisellä kerralla todettiin myös, että solussa olevaa päiväystä voi käyttää kaavaviittauksessa. Esimerkiksi solussa H2 olevan päiväyksen pohjalta voidaan laskea vaikka eräpäivä toiseen soluun: =H2+14. Oikeastaan vielä parempi olisi kirjoittaa maksuaika numeromuodossa soluun ja käyttää lukumuotoilua näyttämään solussa teksti "14 pv netto". Sopiva lukumuotoilu saadaan aikaan Muotoile solut -valintaikkunan Oma-luokkaan lisätyllä merkinnällä # "14 pv netto".
Edellisessä artikkelissa laskettiin työaikoja kahden kellonajan erotuksena. Voit myös joutua laskemaan kellonaikojen erotuksen tilanteessa, jossa vuorokausi vaihtuu välillä. Vuorokauden vaihtuminen aiheuttaa ongelman, koska laskutoimitus tuottaa negatiivisen kellonajan eikä Excel kelpuuta sitä. Apu löytyy kaavasta =B3+(B3<A3)-A3.
Sulkujen sisäpuolella oleva laskutoimitus tuottaa tulokseksi totuusarvon, TOSI tai EPÄTOSI. Silloin kun laskutoimituksen tulos on TOSI, se vastaa ykköstä (1), joka kellonaikoja laskettaessa on 24 (tuntihan on 1/24 vuorokautta). Todellisuudessa siis solun C3 kaava laskee seuraavan laskutoimituksen: 6+24-22=8. Jos tilanne olisi toisinpäin, eli B-sarakkeen solu on suurempi kuin A-sarakkeen luku, totuusarvo palauttaa nollan (0).
Salafunktio PVMERO
Lopuksi funktio, jota jostakin syystä ei näytetä funktioluettelossa eikä Excelin ohjekaan ole siitä tietävinään. PVMERO (DATEDIF) on kätevä funktio esimerkiksi ihmisen ikää laskettaessa. Ikähän tarkoittaa niitä täysiä vuosia, jotka on ehditty elää. Et siis ala pyöristää ikääsi esimerkiksi vuoden puolenvälin jälkeen vaan ilmoitat aina täydet vuodet. Jos esimerkiksi syntymävuosi on kirjoitettuna soluun A14, solussa B14 oleva kaava laskisi iän näin: =PVMERO(A14;TÄMÄ.PÄIVÄ();"y"). Huomaa, että Excel ei tunnista funktiota eikä näin ollen avaa valintaikkunaa sen argumenttien syöttämistä varten. Kaava on kirjoitettava suoraan kaavariville.
PVMERO-funktion argumentteina syötetään kaksi päivämäärää, joiden erotuksena ikä lasketaan. Kolmas argumentti osoittaa palautettavien tietojen lajin, joka tässä tapauksessa on täysiä vuosia. Voisit käyttää myös argumenttia "m" kun haluat laskea tietyn aikavälin kokonaiset kuukaudet tai "d" laskeaksesi täydet päivät.
Toinen tapa laskea esimerkiksi kokonaisia vuosia olisi funktio VUOSI.OSA (YEARFRAC), joka aikaisemmissa versioissa vaati analyysityökalujen asennuksen. Excel 2007 -versiossa se sisältyy jo vakiofunktioihin! Edellisen esimerkin ikä VUOSI.OSA-funktion avulla laskettaisiin tällaisella kaavalla: =KOKONAISLUKU(VUOSI.OSA(TÄMÄ.PÄIVÄ();A14;1)).
10 kommenttia:
Excelhän saadaan kyllä ymmärtämään negatiivista aikaa, jos vain halutaan.
Eli asiaan olisi kyllä olemassa yksinkertainen ratkaisu, vaikka oppaista se tuntuu aina puuttuvan ;-)
Joo, tarkoitat ilmeisesti sitä työkirjakohtaista asetusta, jolla otetaan käyttöön 1904-päivämääräjärjestelmä. Tätä ei kuitenkaan kannata antaa neuvoksi silloin, kun on kyseessä esim. yritys jossa on PC-ympäristö ja suurimmalla osalla kuitenkin käytössään oletusasetukset. Ongelmia on tiedossa (muillekin kuin neuvon antajalle) jos linkitetään muiden työkirjojen kanssa. Laskentahan toteutetaan kuitenkin päiväyksiä vastaavien sarjanumeroiden varassa.
Tämä päivä = booring... Käytännönläheisempi funtiorakenne on verrata lähtöpäivää (esim s-aika) tapahtuman pvmäärään. Eli olet syntynyt joskus ja kilpailu on joskus (vaikka tulevaisuudessa). Jospa A1 sisältää syntymäajan B1 tapahtuman päivämäärän, voisi ikäfuntio C1 ruudussa olla =PVMERO(A1;B1;"Y"), jolloi C1 eilmaisee iän vuosina (tiedä häntä pitääkö C1 muotoilla Luku ilman dessuja). D1 voi sitten sisältää lookup funktion "ikähaarukasta", tarvittaessa jne (tai upottaa D1 tai C1 funtitoon, mikä on siis toinen jutska).
Toimii ainakin Excel 2003:ssa. Mainitsemaasi .osa jutskaa en ole siinä testannu (jos edes siinä onkaan) ku tälläkin pärjänny.
Käytännöllinen ollut kun ilmoittautuminen ollut googledokujen mahdollistettua vaikka ilmoittautumislomakkeen tekemisen webin kautta, jossa yksi kenttä on syntymäaika, joka sitten mahdollista copy pasteta exceliin jatkotyöstämistä varten...
En jostain syystä saa kaavaa toimimaan, excel herjaa virhettä kun käytän puolipisteitä... Pitääkö päivämäärät sisältävät solut olla "päivämäärä" vaiko "yleinen" muodossa? Yritän laskea kuukausittaista erotusta päivämäärille 1.8.1940 - 5.5.1945.
Puolipisteet on oletus silloin kun käytössä on Windowsin suomenkieliset maa-asetukset. Kuulostaa siltä, että sinulla on luetteloerottimena jotakin muuta (tämän voi tarkistaa Ohjauspaneelista).
Olen laatinyt työajanseurantalomaketta, mikä laskee päivittäisen työajan, ja näyttää kuinka monta tuntia/minuuttia kertyi plussaa tai jäi vajaaksi päivittäin. Negatiiviset kelloaajat sain näkyviin tuolla 1904-päivämääräasetuksella.
Nyt tulee ongelma, kun pitäisi laskea yhteen nämä ylijäämää/alijäämätunnit kuukauden lopussa, ja jos kokonaissumma on negatiivinen, Excel näyttääkin ison positiivisen luvun. Esim. jos kuukauden alijäämä on -1:38, niin Excel näyttää 22:22. Onko tähän mitään ratkaisua?
Outo juttu, piti oikein testata. 1904-päivämääräasetuksella taulukkoni kyllä laskee oikein yhteen ja myös näyttää oikein negatiivisetkin luvut. Voisikohan vika olla solumuotoilussa?
Solumuotoiluna on t:mm. Jos laitan [t]:mm, em. esimerkillä tulokseksi tulee 358:22. En tiedä onko kyseessä versiosta johtuva ominaisuus vai mikä lie, en ole koskaan törmännyt itsekään tähän olen käyttänyt Exceliä paljon ihan eka versioista lähtien. Tämä versio on 2010. Täytyy kokeilla vielä vanhemmilla versioilla...
Ongelma ratkesi seuraavalla kaavalla:
=JOS(SUMMA(G4:G34)<"0:00";-1*(24-SUMMA(G4:G34));SUMMA(G4:G34))
Alue G4:G34 sisältää päiväkohtaiser yli-/alijäämät, ja kun tajusin tehdä JOS-funktion ehtoon vertailun kellonaikaan, niin alkoi laskemaan oikein. Ensäimmäinen suoriteosa piti vielä kertoa miinus ykkösellä, jotta se näkyy alijäämäisenä.
Näin se alitajunta toimii, olen tehnyt kaikkea muuta ja nyt lounastauolla avasin työkirjan ja ratkaisu putkahti itsestään mieleen :)
Uskon jo, että ihan solumuotoilusta ei tainnutkaan olla kyse - ja taisin kokeilla tilannetta liian simppelillä taulukolla :/
Tosi hienoa vielä kun tulit kertomaan löytämäsi ratkaisun, kiitos :) Näitä vanhoja postauksiakin ilmeisesti luetaan edelleen niin hyvä, että tulee täydennyksiä.
Lähetä kommentti