Kaavoissa esiintyvien virheiden etsiminen

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

Tarkista seuraavat asiat:	Lisätietoja
Jokaisen kaavan alussa on yhtäläisyysmerkki (=)	Jos jätät yhtäläisyysmerkin pois, kirjoittamasi tiedot voidaan näyttää tekstinä tai päivämääränä. Esimerkiksi jos kirjoitat SUMMA(A1:A10), Excel näyttää tekstimerkkijonon SUMMA(A1:A10) eikä suorita laskutoimitusta. Jos kirjoitat 11/2, Excel näyttää päivämäärän 11.helmi (edellyttäen, että solun muotoiluasetuksena on Yleinen) jakolaskun suorittamisen asemesta.
Alkusulkeita ja loppusulkeita on oltava yhtä paljon	Varmista, että kaikki sulkeet muodostavat parin (alku- ja loppusulkeet). Kun käytät funktiota kaavassa, on tärkeää, että kukin sulkeet ovat oikeassa asennossa, jotta funktio toimii oikein. Esimerkiksi kaava =JOS(B5<0);”Ei kelpaa”;B5*1,05) ei toimi, koska loppusulkeita on kaksi ja alkusulkeita vain yksi, vaikka molempia pitäisi olla vain yksi. Oikea kaava näyttää tältä: =JOS(B5<0;”Ei kelpaa”;B5*1,05).
Alueen ilmaiseminen kaksoispisteellä	Kun viittaat solualueeseen, erota viittaus alueen ensimmäiseen soluun viittauksesta alueen viimeiseen soluun kaksoispisteellä (:). Esimerkiksi =SUMMA(A1:A5), ei =SUMMA(A1 A5), joka palauttaisi #NULL! Virhe.
Kaikki tarvittavat argumentit on kirjoitettu	Joillakin funktioilla on pakollisia argumentteja. Varmista myös, että et ole kirjoittanut liikaa argumentteja.
Kirjoitettu argumenttien tyyppi on oikea	Jotkin funktiot, kuten SUMMA, vaativat numeerisia argumentteja. Muut funktiot, kuten KORVAA, edellyttävät vähintään yhden argumentin tekstiarvoa. Jos käytät argumenttina vääräntyyppisiä tietoja, Excel voi palauttaa odottamattomia tuloksia tai näyttää virheen.
Sisäkkäisiä funktioita on enintään 64	Voit käyttää funktiossa sisäkkäisiä funktioita enintään 64 tasolla.
Muiden taulukoiden nimet on kirjoitettu heittomerkkeihin	Jos kaava viittaa muiden laskentataulukoiden tai työkirjojen arvoihin tai soluihin ja työkirjan tai taulukon nimessä on välilyöntejä tai merkki, joka ei ole kirjain, nimi on kirjoitettava puolilainausmerkkeihin ( ’ ), kuten =’Vuosineljänneksen tiedot’!D3, tai =’123’!A1.
Huutomerkki (!) on asetettu laskentataulukon nimen jälkeen, kun siihen viitataan kaavassa	Jos esimerkiksi haluat palauttaa arvon saman työkirjan Neljännesvuoden tiedot -laskentataulukon solusta D3, käytä seuraavaa kaavaa: ='Neljännesvuoden tiedot'!D3.
Ulkoisten työkirjojen nimiin on sisällytetty polku	Varmista, että kukin ulkoinen viite sisältää työkirjan nimen ja työkirjaan johtavan polun. Työkirjaviittaus sisältää työkirjan nimen, ja sen on oltava hakasulkeissa ([Työkirjannimi.xlsx]). Viittauksessa on myös oltava työkirjan laskentataulukon nimi. Jos työkirja, johon haluat viitata, ei ole avattuna Excelissä, kaavaan voi silti lisätä työkirjaan osoittavan viittauksen. Tällöin kaavaan kirjoitetaan koko polku, kuten =RIVIT(’C:\Omat tiedostot\[N2-toiminnot.xlsx]Myynti’!A1:A8). Tämä kaava palauttaa rivien määrän toisen työkirjan alueessa A1–A8 (8). Huomautus: Jos täydellisessä polussa on välilyöntejä, kuten edellisessä esimerkissä, polku on kirjoitettava puolilainausmerkkeihin, jotka sijoitetaan polun alkuun ja laskentataulukon nimen jälkeen ennen huutomerkkiä.
Luvut on kirjoitettu ilman muotoilua	Älä käytä luvuissa erotinmerkkejä, kun kirjoitat niitä kaavoihin. Jos haluat kaavaan esimerkiksi arvon 1 000 €, kirjoita se muodossa 1000. Jos kirjoitat esimerkiksi pilkun (jota käytetään esimerkiksi Yhdysvalloissa lukujen tuhaterottimena) osaksi lukua, Excel tulkitsee pilkun erotinmerkiksi. Jos haluat kaavan tulosten lukujen näkyvän niin, että niissä on tuhaterotin ja rahayksikön tunnus, muotoile solut muotoilemattomia lukuargumentteja käyttävien kaavojen kirjoittamisen jälkeen. Jos esimerkiksi haluat lisätä solun A3 arvoon luvun 3100 ja kirjoitat kaavan =SUMMA(3 100,A3),Excel laskee yhteen luvut 3 ja 100 ja lisää sitten summan A3-arvoon sen sijaan, että lisäisi luvun 3100 A3:een, jolloin se olisi =SUMMA(3100,A3). Tai jos kirjoitat kaavan =ITSEISARVO(-2,134), Excel näyttää virheen, koska ITSEISARVO-funktio hyväksyy vain yhden argumentin: =ITSEISARVO(-2134).

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.

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:

Kaavan laskeminen -valintaikkunassa	Kuvaus
=JOS(KESKIARVO(D2:D5) > 50,SUMMA(E2:E5),0)	Sisäkkäisiä osia sisältävä kaava näkyy ensimmäiseksi. KESKIARVO-funktio ja SUMMA-funktio ovat JOS-funktion sisällä. Solualue D2:D5 sisältää arvot 55, 35, 45 ja 25, joten funktion KESKIARVO(D2:D5) tulos on 40.
=JOS(40>50,SUMMA(E2:E5),0)	Solualue D2:D5 sisältää arvot 55, 35, 45 ja 25, joten funktion KESKIARVO(D2:D5) tulos on 40.
=JOS(Epätosi,SUMMA(E2:E5),0)	40 ei ole suurempi kuin 50, joten JOS-funktion ensimmäisen argumentin (totuus_testi-argumentin) arvo on EPÄTOSI. JOS-funktio palauttaa kolmannen argumentin arvon (arvo_jos_epätosi-argumentti). SUMMA-funktiota ei lasketa, koska se on JOS-funktion toinen argumentti (arvo_jos_tosi-argumentti), ja se palautetaan vain, kun lauseke on tosi.

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.