Kaavoissa esiintyvien virheiden etsiminen

Seuraavassa taulukossa on yhteenveto yleisimmistä virheistä, joita voit tehdä kaavoja kirjoittaessasi, ja ohjeita niiden korjaamiseksi:

Voit käyttää kaavojen tarkistuksessa tiettyjä sääntöjä. Säännöt eivät takaa, että työkirjasi on virheetön, mutta niiden avulla löydät yleisimmät virheet. Voit ottaa säännöt käyttöön ja poistaa ne käytöstä yksitellen.

Virheitä voidaan merkitä ja korjata kahdella tavalla: yksi virhe kerrallaan samoin kuin tarkistettaessa oikeinkirjoitusta tai heti niiden ilmetessä lisättäessä tietoja laskentataulukkoon.

Voit ratkaista virheen Käyttämällä Excelin näyttämiä asetuksia tai ohitat virheen valitsemalla Ohita virhe. Jos tietyssä solussa oleva virhe ohitetaan, kyseinen virhe ei näy tulevissa virheentarkistuksissa. Aiemmin ohitetut virheet voidaan kuitenkin nollata, minkä jälkeen ne tulevat uudelleen näkyviin.

1. Jos käytössäsi on Windowsin Excel, siirry kohtaan Tiedoston > asetukset > kaavat tai
   Macin Excelissä valitse Excel-valikko > Asetukset > Virheentarkistus. 

   Valitse Excel 2007 Microsoft Office -painike ,> Excelin asetukset > kaavat.

2. Valitse Virheentarkistus-kohdasta, Salli virheiden tarkistaminen taustalla. Kaikki löydetyt virheet merkitään kolmiolla solun vasempaan yläkulmaan.

   

3. Jos haluat muuttaa virheestä ilmoittavan kolmion väriä, valitse Ilmaise virheet tällä värillä -ruudusta uusi väri.

4. Valitse Excelin tarkistussäännöt -kohdassa seuraavien sääntöjen valintaruudut tai poista niiden valinta:

   • Solut, jotka sisältävät virheen aiheuttavia kaavoja: Kaava ei käytä odotettua syntaksia, argumentteja tai tietotyyppejä. Virhearvoja ovat muun muassa #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!ja #VALUE!. Kullakin näistä virhearvoista on eri syyt, ja ne ratkaistaan eri tavoin.

     Huomautus: Jos kirjoitat virhearvon suoraan soluun, se tallennetaan virhearvona, mutta sitä ei merkitä virheeksi. Jos kuitenkin jossakin toisessa solussa oleva kaava viittaa kyseiseen soluun, kaava palauttaa toisessa solussa virheen.

   • Taulukossa on ristiriitainen lasketun sarakkeen kaava: Laskettu sarake voi sisältää kaavoja, jotka ovat erilaisia kuin sarakkeen poikkeuksen aiheuttava pääkaava. Excel luo poikkeuksen laskettuun sarakkeeseen, kun teet jonkin seuraavista:

     • Kirjoitat lasketun sarakkeen soluun muita tietoja kuin kaavan.

     • Kirjoita kaava lasketun sarakkeen soluun ja valitse pikatyökaluriviltäCtrl +Z tai kumoa .

     • Kirjoitat uuden kaavan sellaiseen laskettuun sarakkeeseen, joka sisältää jo ainakin yhden poikkeuksen.

     • Kopioit laskettuun sarakkeeseen tietoja, jotka ovat erilaisia kuin lasketun sarakkeen kaava. Jos kopioidut tiedot sisältävät kaavan, tämä kaava korvaa lasketun sarakkeen tiedot.

     • Siirrät tai poistat solun toiselle laskentataulukon alueelle, johon on viittaus lasketun sarakkeen riveillä.

   • Solut, jotka sisältävät kaksinumeroisia vuosilukuja: Solussa on tekstimuotoinen päivämäärä, jonka vuosisata voidaan tulkita väärin, jos päivämäärää käytetään kaavoissa. Esimerkiksi kaavassa =VUOSI(”1.1.31”) vuosi voi olla 1931 tai 2031. Tarkista epäselvät tekstimuotoiset päivämäärät tämän säännön avulla.

   • Luvut on muotoiltu tekstiksi, tai niiden edessä on heittomerkki: Solussa on tekstimuodossa tallennettuja lukuja. Tällaiset luvut ovat yleensä peräisin muista lähteistä tuoduista tiedoista. Tekstinä tallennetut luvut voivat aiheuttaa odottamattomia lajittelutuloksia, joten ne kannattaa muuntaa luvuiksi. ’=SUMMA(A1:A10) näkyy tekstinä.

   • Kaavat, jotka ovat yhteensopimattomia alueen muiden kaavojen kanssa: Kaava ei vastaa muiden sen lähellä olevien kaavojen muotoa. Monissa tapauksissa muiden kaavojen vieressä olevat kaavat eroavat toisistaan vain käytetyissä viittauksissa. Seuraavassa neljä toisiinsa liittyvää kaavaa sisältävässä esimerkissä Excel näyttää virheen solun D4 kaavan =SUMMA(A10:C10) vieressä, koska muut kaavat käyttävät yhden rivin lisäystä ja kaava =SUMMA(A10:C10) käyttää kahdeksan rivin lisäystä – Excel odottaa tällöin kaavaa =SUMMA(A4:C4).

     

     Jos kaavassa käytetyt viittaukset eivät ole yhdenmukaisia vierekkäisten kaavojen viittausten kanssa, Excel näyttää virheen.

   • Kaavat, jotka ohittavat alueen soluja: Kaava ei ehkä automaattisesti sisällä viittauksia tietoihin, jotka lisäät alkuperäisen tietoalueen ja kaavan sisältämän solun väliin. Sääntö vertaa viittausta kaavan sisältämän solun viereiseen solualueeseen. Jos viereiset solut eivät ole tyhjiä vaan sisältävät lisää arvoja, Excel näyttää virheen kaavan vieressä.

     Excel esimerkiksi lisää virheen kaavan =SUMMA(D2:D4) viereen, kun sääntöä käytetään, koska solut D5, D6 ja D7 ovat kaavassa viitattujen solujen ja kaavan sisältävän solun (D8) vieressä, ja solut sisältävät tietoja, joihin kaavassa olisi pitänyt viitata.

     

   • Lukitsemattomat solut, joissa on kaavoja: Kaavaa ei ole suojattu lukitsemalla. Oletusarvoisesti kaikki laskentataulukon solut on lukittu, joten niitä ei voi muuttaa, kun laskentataulukko on suojattu. Näin voit estää vahingossa tehdyt virheet, kuten kaavojen poistamisen tai muuttamisen vahingossa. Tämä virhe tarkoittaa, että solun lukitus on poistettu, mutta laskentataulukkoa ei ole suojattu. Varmista, ettet halua lukita solua.

   • Tyhjiin soluihin viittaavat kaavat: Kaava sisältää viittauksen tyhjään soluun. Tulos ei ehkä vastaa odotuksia, kuten seuraavassa esimerkissä.

     Oletetaan, että haluat laskea seuraavan sarakkeen solujen sisältämien lukujen keskiarvon. Jos kolmas solu on tyhjä, sitä ei sisällytetä laskentaan ja tulos on 22,75. Jos kolmannen solun arvo on 0, tulos on 18,2.

     

   • Taulukkoon kirjoitetut tiedot ovat virheellisiä: Taulukossa on kelpoisuustarkistusvirhe. Tarkista solun kelpoisuuden tarkistusasetus valitsemalla Tiedot-välilehti > Datatyökalut-ryhmä Tietojen kelpoisuuden tarkistaminen.

1. Valitse laskentataulukko, josta haluat etsiä virheitä.

2. Jos laskentataulukko on manuaalisesti laskettava taulukko, laske se uudelleen painamalla F9-näppäintä.

   Jos Virheentarkistus-valintaikkuna ei ole näkyvissä, valitse Kaavat > Kaavan valvonta > Virheentarkistus.

3. Jos olet aiemmin ohittanut virheet, voit tarkistaa virheet uudelleen seuraavasti: siirry kohtaan Tiedosto > Asetukset > Kaavat. Valitse Macin Excelissä Excel-valikko, > Asetukset > Virheentarkistus.

   Valitse Virheentarkistus-osassaPalauta ohitetut virheet > OK.

   

   Huomautus: Ohitettujen virheiden nollaaminen nollaa aktiivisen työkirjan kaikkien laskentataulukoiden kaikki virheet.

   Vihje:  Virheentarkistus-valintaikkunan siirtäminen aivan kaavarivin alapuolelle voi helpottaa käyttöä.

   

4. Valitse jokin valintaikkunan oikeassa reunassa olevista toimintopainikkeista. Käytettävissä olevat vaihtoehdot määräytyvät virhelajin mukaan.

5. Valitse Seuraava.

1. Valitse solun vierestä Virheentarkistus ja valitse sitten haluamasi vaihtoehto. Kullakin virhelajilla on omat komentonsa, ja virheen kuvaus on ensimmäisellä rivillä.

   Jos valitset Ohita virhe, virhe merkitään ohitettavaksi jokaisessa peräkkäisessä tarkistuksessa.

   

Jos kaava ei pysty arvioimaan tulosta oikein, Excel näyttää virhearvon, kuten #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, ja #VALUE!. Kullakin virhetyypillä on eri syyt ja erilaiset ratkaisut.

Seuraavassa taulukossa on linkkejä näitä virheitä kuvaaviin artikkeleihin ja lyhyt kuvaus, jonka avulla pääset nopeasti alkuun.

Aihe	Kuvaus
####-virheen korjaaminen	Excel näyttää tämän virheen, kun sarake ei ole tarpeeksi leveä näyttääkseen kaikki solun merkit tai solu sisältää negatiivisen päivämäärä- tai aika-arvon. Esimerkiksi kaavan, joka vähentää tulevan päivämäärän menneisyydessä olevasta päivämäärästä, kuten 15.06.2008-01.07.2008, tuloksena on negatiivinen arvo. Vihje: Yritä sovittaa automaattisesti soluun kaksoisnapsauttamalla sarakeotsikoiden väliä. Jos ### tulee näkyviin, koska Excel ei voi näyttää kaikkia merkkejä, tämä korjaa sen.
#JAKO/0! -virheen korjaaminen	Excel näyttää tämän virheen, kun luku jaetaan joko nollalla (0) tai solulla, joka ei sisällä arvoa. Vihje: Lisää virheenkäsittelytoiminto, kuten seuraavassa esimerkissä, joka on =JOS(C2,B2/C2,0)
#PUUTTUU!-virheen korjaaminen	Excel näyttää tämän virheen, jos funktiolle tai kaavalle ei ole käytettävissä arvoa. Jos käytät esimerkiksi PHAKU-funktiota, näkyykö etsimällesi arvolle vastine hakualueelta? Useimmiten ei. Kokeile estää #PUUTTUU!-virhe käyttämällä JOSVIRHE-funktiota. Tällöin voit käyttää seuraavaa kaavaa: =JOSVIRHE(PHAKU(D2,$D$6:$E$8,2,TOSI),0)
#NIMI? -virheen korjaaminen	Tämä virhe näytetään, jos Excel ei tunnista kaavan tekstiä. Esimerkiksi alueen nimi tai funktion nimi on voitu kirjoittaa väärin. Huomautus: Jos käytät funktiota, varmista, että funktion nimi on kirjoitettu oikein. Tässä tapauksessa SUMMA on kirjoitettu väärin. Poista e ja Excel korjaa sen.
#TYHJÄ! -virheen korjaaminen	Excel näyttää tämän virheen, jos kaavassa on määritetty kahden alueen leikkauspiste, mutta alueet eivät leikkaa. Leikkausoperaattori on välilyönti, joka erottelee kaavan viittauksia. Huomautus: Varmista, että alueet on erotettu oikein – alueet C2:C3 ja E4:E6 eivät leikkaa, joten kaavan =SUMMA(C2:C3 E4:E6) kirjoittaminen palauttaa #NULL! -virheen. Pilkkujen asettaminen C- ja E-alueiden väliin korjaa sen =SUMMA(C2:C3;E4:E6)
#LUKU! -virheen korjaaminen	Excel näyttää tämän virheen, jos kaavassa tai funktiossa on virheellisiä numeerisia arvoja. Käytätkö funktiota, joka iteroi, kuten SISÄINEN.KORKO tai KORKO? Jos on, #NUM! -virhe johtuu todennäköisesti siitä, että funktio ei löydä tulosta. Lisätietoja ratkaisuvaiheista on ohjeaiheessa.
#VIITTAUS! -virheen korjaaminen	Excel näyttää tämän virheen, jos soluviittaus on virheellinen. Olet ehkä esimerkiksi poistanut soluja, joihin muut kaavat viittasivat, tai olet saattanut liittää soluja, jotka olet siirtänyt muiden kaavojen viittaamien solujen päälle. Poistitko vahingossa rivin tai sarakkeen? Poistimme sarakkeen B tästä kaavasta = SUMMA(A2,B2,C2) ja katso, mitä tapahtui. Kumoa poisto joko Kumoa-komennolla (Ctrl + Z) tai luo kaava uudelleen tai käytä jatkuvaa solualuetta seuraavasti: =SUMMA(A2:C2), jolloin päivitys tapahtuu automaattisesti, kun sarake B poistetaan.
#ARVO! -virheen korjaaminen	Excel näyttää tämän virheen, jos kaavassa on eri tietotyyppejä sisältäviä soluja. Käytätkö matemaattisia operaattoreita (+, -, *, / tai ^) eri tietotyyppien kanssa? Jos käytät, kokeile funktiota. Tässä tapauksessa =SUMMA(F2:F5) korjaisi ongelman.

Kun solut eivät näy laskentataulukossa, voit katsella näitä soluja ja niiden kaavoja Seurantaikkuna-työkalurivillä. Seurantaikkunan avulla on helppo tutkia, tarkistaa tai vahvistaa kaavojen laskutoimitukset ja tulokset suuressa laskentataulukossa. Kun käytät seurantaikkunaa, sinun ei tarvitse jatkuvasti selata laskentataulukkoa ja siirtyä taulukon paikasta toiseen.

Tämän työkalurivin voi siirtää tai kiinnittää kuten muutkin työkalurivit. Voit esimerkiksi kiinnittää sen ikkunan alaosaan. Työkalurivin avulla voidaan seurata seuraavia solun ominaisuuksia: 1) Työkirja, 2) Taulukko, 3) Nimi (jos solussa on vastaava nimetty alue), 4) Solun osoite, 5) Arvo ja 6) Kaava.

Solujen lisääminen seurantaikkunaan

1. Valitse seurattavat solut.

   Jos haluat valita kaikki kaavat sisältävän laskentataulukon solut, siirry aloitusvalikkoon > Muokkaaminen > valitse Etsi & Valitse (tai voit käyttää Ctrl+G- tai Control+G-näppäinyhdistelmää Mac-tietokoneessa)> Siirry erikoiskokeisiin > kaavat.

   

2. Siirry kohtaan Kaavat > Kaavan valvonta > valitse Seurantaikkuna.

3. Valitse Lisää seuranta.

   

4. Varmista, että olet valinnut kaikki solut, joita haluat katsella, ja valitse Lisää.

   

5. Voit muuttaa Seurantaikkuna-sarakkeen leveyttä vetämällä sarakeotsikon oikeaa reunaa.

6. Jos haluat tarkastella solua, johon Seurantaikkuna-työkalurivin merkintä viittaa, kaksoisnapsauta merkintää.

   Huomautus: Solut, joissa on ulkoisia viittauksia muihin työkirjoihin, näkyvät Seurantaikkuna-työkalurivillä vain silloin, kun kyseiset työkirjat ovat avoinna.

Solujen poistaminen seurantaikkunasta

1. Jos Seurantaikkuna-työkalurivi ei ole näkyvissä, siirry kohtaan Kaavat > Kaavan valvonta > valitse Seurantaikkuna.

2. Valitse solut, jotka haluat poistaa.

   Jos haluat valita useita soluja, paina CTRL-näppäintä ja valitse sitten solut.

3. Valitse Poista seuranta.

   

Joskus voi olla vaikeaa ymmärtää, kuinka sisäkkäisiä osia sisältävä kaava laskee lopputuloksen, koska se joutuu laskemaan useita välituloksia ja tekemään loogisia testejä. Käyttämällä Kaavan laskeminen -valintaikkunaa voit kuitenkin nähdä, missä järjestyksessä Excel laskee kaavan eri osat. Esimerkiksi kaava =JOS(KESKIARVO(D2:D5)>50,SUMMA(E2:E5),0) on helpompi ymmärtää, kun näet seuraavat välitulokset:

1. Valitse laskettava solu. Vain yksi solu voidaan laskea kerrallaan.

2. Valitse Kaavat > Kaavan valvonta > Laske kaava.

3. Tarkista alleviivatun viittauksen arvo valitsemalla Laske. Laskennan tulos näkyy kursivoituna.

   Jos kaavan alleviivattu osa on viittaus toiseen kaavaan, valitse Vaihe Sisään , jos haluat näyttää toisen kaavan Arviointi-ruudussa . Voit palata takaisin edelliseen soluun ja kaavaan valitsemalla Suorita toimintosarja loppuun.

   Suorita rivi -painike ei ole käytettävissä viittausta varten, jos viittaus on kaavassa toiseen kertaan tai jos kaava viittaa jonkin muun työkirjan soluun.

4. Jatka Laske-vaihtoehdon valitsemista, kunnes kaavan jokainen osa on arvioitu.

5. Jos haluat nähdä arvioinnin uudelleen, valitse Käynnistä uudelleen.

6. Lopeta arviointi valitsemalla Sulje.