Tag Archives: josvirhe

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)

Summan laskeminen ei onnistu

Kun summan laskeminen ei onnistu sen takia kun alueella on virhearvoja niin tällöin voidaan käyttää matriisikaavaa ja JOSVIRHE (ISERROR) funktiota.

Esimerkki:

Sarakkeissa A ja B on arvoja. A-sarakkeen arvot jaetaan B sarakkeiden arvoilla jolloin saadaan C sarakkeeseen virhearvo jos B:ssä on nolla (0) tai on tyhjä.

Tällöin kuten esimerkissä näkyy saadaan myös virhe soluun C5 jossa yritetään summata C-sarakkeen arvoja. Jotta summa voitaisiin laskea C:n virhearvoista huolimatta käytetään matriisikaavaa kaavaa ja funktiota JOSVIRHE (IFERROR) summakaavassa:
{=SUMMA(JOSVIRHE(C1:C4;””))}
{=SUM(IFERROR(C1:C4;””))}

Kaava kirjoitetaan normaalisti mutta enterin sijaista painetaan Ctrl+Vaihto+Enter jolloin saadaan aikaan matriisikaava.

Esimerkin D-sarakkeessa on käytetyt kaavat.

Esimerkki

Esimerkki


 

Eroon jako/0 ilmoituksesta

Mikäli jaetaan luku nollalla (0) tai tyhjällä solulla saadaan virheilmoitus #Jako/0! (#Div/0!).
Lomakepohjissa tämä saattaa olla riesa ja kauneusvirhe josta halutaan päästä eroon.

Helpoin tapa on käyttää funktiota JOSVIRHE (IFERROR).
Eli esimerkiksi jos jaetaan solu A1 solulla B1 ja soluun C1 halutaan jakotulos mutta ei virheilmoitusta niissä tapauksissa jossa B1 on nolla tai tyhjä. Tällöin kaava C1:ssä olisi seuraavan lainen =JOSVIRHE (A1/B1;””), =IFERROR(A1/B1;””).

 

 

 

Kahden listan vertailu

Verrataan kahta listaa keskenään ja poimitaan toisesta listasta tietoja ensimmäiseen.
Tähän on kätevä käyttää funktiota PHAKU (VLOOKUP).
Esimerkissä poimitaan henkilön palkka palkat.xlsx –työkirjasta.
Kaava solussa C2 on =PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI).
=VLOOKUP(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;FALSE)

PHAKU (VLOOKUP)

PHAKU (VLOOKUP)

Hakuarvo on tässä tapauksessa se arvo jonka perusteella toisesta taulukosta haetaan tietoja.
Taulukko_matriisi on se taulukko alue jonka vasemmanpuoleisesta sarakkeesta arvoa haetaan.
Sar_indeksi_nro on sen sarakkeen numero valitulla taulukkoalueella josta arvo poimitaan.
Alue_halu on epätosi koska halutaan löytää täsmällinen vastine.

Taulu josta haetaan tietoja kannattaa lajitella hakuarvon perusteella.

Mikäli tietoja ei toisesta taulusta löydy palauttaa funktio virhearvon #PUUTTUU! (#N/A).
Tämän saat pois seuraavalla tavalla kaavalla (Excel versiot 2007 ja uudemmat):
=JOSVIRHE(PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI);”Ei löydy”)
JOSVIRHE = IFERROR
EPÄTOSI = FALSE
Kaavan tekstin “Ei löydy” sijasta voit käyttää mitä tahansa haluamaasi tekstiä tai sitten “” jotta solu jäisi tyhjäksi.
=JOSVIRHE(PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI);””)

Jos käytössäsi on vanhempi Excel voit poistaa virhesanoman esimerkiksi seuraavanlaisella kaavalla:
=JOS(ONVIRHE(PHAKU(A3;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI));””;PHAKU(A3;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI)).
JOS = IF
ONVIRHE = ISERROR
Huomaa että tässä kaavassa haku suoritetaan kaksi kertaa joka tekee kaavasta hitaan.