Viallisten kaavojen ratkaiseminen Excelissä

Kaavoissa, joissa on useampi kuin yksi argumentti, erotetaan argumentit luetteloerottimilla. Käytettävä erotin voi vaihdella käyttöjärjestelmän aluekohtaisten asetusten ja Excel-asetusten mukaan. Yleisimmät luetteloerottimet ovat pilkku "," ja puolipiste ";".

Kaava katkeaa, jos jossakin sen funktioista käytetään virheellisiä erottimia.

Lisätietoja on seuraavassa artikkelissa: Kaavavirheitä Excelissä, kun luetteloerotinta ei ole määritetty oikein

Excel näyttää ristikkomerkillä (#) useita eri virheitä, kuten #ARVO!, #VIITTAUS!, #LUKU, #PUUTTUU, #JAKO/0!, #NIMI? ja #TYHJÄ!, osoittamaan, että jokin kaavassa ei toimi oikein. Esimerkiksi #ARVO! -virhe johtuu virheellisestä muotoilusta tai argumenttien sisältämistä tietotyypeistä, joita ei tueta. Voit myös nähdä #VIITTAUS! -virheen, jos kaava viittaa soluihin, jotka on poistettu tai joiden tiedot on korvattu muilla tiedoilla. Vianmäärityksen ohjeet vaihtelevat kunkin virheen osalta.

Saat lisätietoja näkemästäsi ristikkomerkkivirheestä seuraavista aiheista:

• #LUKU! -virheen korjaaminen

• #ARVO! -virheen korjaaminen

• #PUUTTUU!-virheen korjaaminen

• #JAKO/0! -virheen korjaaminen

• #VIITTAUS! -virheen korjaaminen

• #NIMI? -virheen korjaaminen

• #TYHJÄ! -virheen korjaaminen

Aina kun avaat laskentataulukon, joka sisältää muiden laskentataulukoiden arvoihin viittaavia kaavoja, sinua pyydetään päivittämään viittaukset tai jättämään ne entiselleen.

Excel näyttää yllä näkyvän valintaikkunan sen varmistamiseksi, että nykyisen laskentataulukon kaavat viittaavat aina eniten päivitettyyn arvoon siltä varalta, että viittauksen arvo on muuttunut. Voit päivittää viittaukset tai ohittaa, jos et halua päivittää. Vaikka et päivittäisi viittauksia, voit aina päivittää laskentataulukon linkit manuaalisesti milloin tahansa.

Voit aina poistaa valintaikkunan näkymisen käynnistyksen yhteydessä. Voit tehdä tämän valitsemalla Tiedosto > Asetukset > Lisäasetukset > Yleiset ja tyhjentämällä sitten Ehdota automaattisten linkkien päivitystä -ruudun.

Ellei kaava näytä arvoa, toimi seuraavasti:

• Varmista, että Excel on määritetty näyttämään laskentataulukon kaavat. Valitse Kaavat-välilehden Kaavan tarkistaminen -ryhmässä Näytä kaavat.

  Vihje: Voit myös käyttää pikanäppäintä Ctrl + ` (sarkaimen yläpuolella oleva näppäin). Kun teet tämän, sarakkeet levenevät automaattisesti kaavojen näyttämiseksi, mutta älä huoli, kun siirryt takaisin normaalinäkymään, sarakkeiden koko muuttuu.

• Jos et pysty ratkaisemaan ongelmaa yllä olevan vaiheen avulla, solu on muotoiltu ehkä tekstiksi. Voit napsauttaa hiiren kakkospainikkeella solua ja valita Muotoile solut > Yleiset (tai Ctrl + 1), ja muuttaa muotoa painamalla sitten F2 > Enter.

• Jos sarakkeessa on suuri alue soluja, jotka on muotoiltu tekstiksi, valitse alue, käytä haluamaasi lukumuotoa ja siirry kohtaan Tiedot > Sarakkeen teksti > Valmis. Muotoa käytetään kaikissa valituissa soluissa.

  

Kun kaava ei suorita laskutoimituksia, sinun on tarkistettava, onko automaattinen laskenta käytössä Excelissä. Kaavat eivät toimi, jos manuaalinen laskenta on otettu käyttöön. Tarkista automaattinen laskenta seuraavien vaiheiden avulla.

1. Valitse Tiedosto-välilehti, Asetukset ja sitten Kaavat-luokka.

2. Varmista Laskennan asetukset -osan Työkirjan laskentatila -kohdassa, että Automaattinen on valittuna.

   

Katso lisätietoja laskutoimituksista artikkelista Kaavan uudelleenlaskennan, iteraation tai tarkkuuden muuttaminen.

Kehäviittaus tapahtuu, kun kaava viittaa samaan soluun, jossa se sijaitsee. Voit korjata tämän joko siirtämällä kaavan toiseen soluun tai muuttamalla kaavan syntaksin sellaiseksi, että se välttää kehäviittauksia. Joissain tapauksissa saatat kuitenkin tarvita kehäviittauksia, koska niiden ansiosta kaavojasi toistetaan, kunnes tietty lukuehto saavutetaan. Tällaisissa tapauksissa sinun on otettava käyttöön asetus Poista tai salli kehäviittaus.

Lisätietoja kehäviittauksista on artikkelissa Kehäviittauksen poistaminen tai salliminen.

Jos merkintä ei ala yhtäläisyysmerkillä, se ei ole kaava eikä sitä lasketa– yleinen virhe.

Kun kirjoitat esimerkiksi SUMMA(A1:A10), Excel näyttää tekstimerkkijonon SUMMA(A1:A10) kaavan tuloksen sijaan. Jos taas kirjoitat 11/2, Excel näyttää jakolaskun suorittamisen sijasta päivämäärän, kuten 2.11. tai 2.11.2009.

Voit välttää odottamattomat tulokset aloittamalla funktion aina yhtäläisyysmerkillä. Kirjoita esimerkiksi: =SUMMA(A1:A10 ja =11/2.

Kun kaavassa käytetään funktiota, jokaisen alkusulkeen jälkeen täytyy tulla loppusulje, jotta kaava toimii oikein. Varmista, että kaikki sulkeet muodostavat parin. Esimerkiksi kaava =JOS(B5<0),"Ei kelpaa",B5*1,05) ei toimi, koska suljesulkeita on kaksi, mutta vain yksi avaava sulke. Oikea kaava näyttää tältä: =JOS(B5<0;"Ei kelpaa";B5*1,05).

Excel-funktioilla on argumentteja, eli arvoja, jotka sinun on annettava, jotta funktio toimii. Vain muutamat funktiot (kuten PII tai TÄMÄ.PÄIVÄ) eivät hyväksy argumentteja. Tarkista kaavasyntaksi, joka tulee näkyviin, kun alat kirjoittaa funktiota ja varmista, että funktiolla on tarvittavat argumentit.

Esimerkiksi ISOT-funktiossa voi käyttää vain yhtä tekstimerkkijonoa tai soluviittausta argumenttina: =ISOT("hei") tai =ISOT(C2)

Jotkin funktiot, kuten SUMMA, edellyttävät vain numeerisia argumentteja. Toiset funktiot, kuten KORVAA, puolestaan edellyttävät ainakin yhden argumentin tekstiarvon. Jos käytät väärää tietotyyppiä, funktiot voivat palauttaa odottamattomia tuloksia tai näyttää #ARVO!-virheen.

Jos haluat pikaisesti tarkistaa tietyn funktion syntaksin, tutustu Excelin funktiot (luokittain) -luetteloon.

Älä käytä kaavojen luvuissa dollarimerkkiä ($) tai pilkkua tuhaterottimena, koska dollarimerkeillä osoitetaan suorat viittaukset eivätkä pilkut toimi suomenkielisessä Excelissä. Kirjoita kaavaan arvon $1000 sijaan 1000.

Jos käytät argumenteissa muotoiltuja lukuja, saat odottamattomia laskentatuloksia, mutta saatat nähdä myös #NUM!- virheen. Jos esimerkiksi haluat määrittää luvun -2134 itseisarvon ja kirjoitat kaavan =ITSEISARVO(-2,134) käyttämällä pilkkua tuhaterottimena, Excel näyttää #LUKU! -virheen, koska ITSEISARVO-funktio hyväksyy vain yhden argumentin. Se tulkitsee argumentit -2, ja 134 erillisinä argumentteina.

Kaava ei ehkä palauta odotettuja tuloksia, jos solun tietotyyppiä ei voi käyttää laskutoimituksissa. Jos esimerkiksi kirjoitat yksinkertaisen kaavan =2+3 tekstiksi muotoiltuun soluun, Excel ei voi laskea syöttämiäsi tietoja. Solussa näkyy vain =2+3. Voit korjata ongelman muuttamalla solun tietotyypin Teksti-tietotyypiksi Yleiset seuraavasti:

1. Valitse solu.

2. Valitse Aloitus ja laajenna Luku- tai Lukumuotoilu-ryhmä valitsemalla nuoli (tai painamalla näppäinyhdistelmää CTRL + 1). Valitse sitten Yleiset.

3. Aseta solu muokkaustilaan painamalla F2-näppäintä ja hyväksy sitten kaava painamalla Enter-näppäintä.

Luku-tietotyyppiä käyttävään soluun kirjoittamasi päivämäärä voidaan näyttää numeerisena päivämääräarvona päivämäärän sijaan. Jos haluat näyttää luvun päivämääränä, valitse Lukumuotoilu-valikoimassa Päivämäärä-muoto.

On aika yleistä, että kaavan kertolaskuoperaattorina käytetään x-merkkiä, mutta Excel hyväksyy ainoastaan asteriskin (*) kertolaskuissa. Jos käytät kaavassa vakioita, Excel näyttää virheviestin ja voi korjata kirjoittamasi kaavan korvaamalla x-merkin asteriskilla (*).

Jos kuitenkin käytät soluviittauksia, Excel palauttaa #NIMI? -virhearvon.

Jos luot kaavan, jossa on tekstiä, kirjoita teksti lainausmerkkien sisään.

Esimerkiksi kaava ="Tänään on " & TEKSTI(TÄMÄ.PÄIVÄ();"pppp, p.k.") yhdistää "Tänään on " -tekstin TEKSTI- ja TÄMÄ.PÄIVÄ-funktioiden tuloksiin ja palauttaa soluun esimerkiksi tekstin Tänään on maanantai, 30.5..

Kaavassa "Tänään on " on välilyönti ennen loppulainausmerkkiä. Tämän ansiosta sanojen "Tänään on" ja "maanantai, 30.5." välissä on välilyönti..

Voit käyttää kaavassa sisäkkäisiä funktioita enintään 64 tasolla.

Esimerkiksi kaavassa =JOS(NELIÖJUURI(PII())<2;"Vähemmän kuin kaksi!";"Enemmän kuin kaksi!") on kolme funktiotasoa: PII-funktio sisältyy NELIÖJUURI-funktioon, joka puolestaan sisältyy JOS-funktioon.

Jos kaava viittaa toisen laskentataulukon arvoihin tai soluihin ja taulukon nimessä on jokin aakkosiin kuulumaton merkki, kuten välilyönti, nimi on kirjoitettava puolilainausmerkkeihin (').

Jos esimerkiksi haluat palauttaa arvon työkirjan Neljännesvuoden tiedot -laskentataulukon solusta D3, kirjoita: ='Neljännesvuoden tiedot'!D3. Jos taulukon nimen ympärillä ei ole lainausmerkkejä, kaava näyttää #NIMI?-virheen.

Voit viitata kaavassasi toisen taulukon arvoihin tai soluihin myös valitsemalla haluamasi arvon tai solun. Tällöin Excel lisää automaattisesti puolilainausmerkit taulukon nimen ympärille.

Jos kaava viittaa toisen työkirjan arvoihin tai soluihin, työkirjan nimi on kirjoitettava hakasulkeisiin ([]) ja sen perään on lisättävä kyseiset arvot tai solut sisältävän taulukon nimi.

Jos esimerkiksi haluat viitata Excelissä avoinna olevan Q2 Operations -työkirjan Myynti-laskentataulukon soluihin A1–A8, kirjoita= =[Q2 Operations.xlsx]Myynti! A1:A8. Ilman hakasulkeita kaava näyttää #VIITTAUS!-virheen.

Jos työkirja ei ole avoinna Excelissä, kirjoita tiedoston koko polku.

Esimerkiksi: =RIVIT('C:\Omat tiedostot\[Toisen vuosineljänneksen toiminnot.xlsx]Myynti'!A1:A8).

Jos solu jaetaan toisella solulla, jossa on arvo nolla tai ei mitään arvoa, tuloksena on #JAKO/0! ‑virhe.

Voit välttää tämän virheen korjaamalla sen suoraan ja testaamalla, onko olemassa nimittäjä. Voit käyttää seuraavaa:

=JOS(B1,A1/B1,0)

Tämä tarkoittaa JOS(B1 on olemassa, jaa A1 B1:lle, muussa tapauksessa palauta 0).

Ennen kuin poistat mitään soluihin, alueisiin, määritettyihin nimiin, laskentataulukoihin tai työkirjoihin sisältyviä tietoja, tarkista, ettei niihin ole viitattu kaavoissa. Tarvittaessa voit korvata kaavat niiden tuloksilla, ennen kuin poistat tiedot, joihin kaavoissa on viitattu.

Jos et voi korvata kaavoja niiden tuloksilla, tarkista nämä virheet ja mahdolliset ratkaisut:

• Jos kaava viittaa soluihin, jotka on poistettu tai korvattu muilla tiedoilla ja saat #VIITTAUS!-virheen, valitse solu, joka sisältää #VIITTAUS! -virhearvon. Valitse kaavarivillä #VIITTAUS! ja poista se. Kirjoita sitten kaavan alue uudelleen.

• Jos määritetty nimi puuttuu ja siihen viittaava kaava palauttaa #NIMI? ‑virheen, määritä uusi nimi, joka viittaa haluamaasi alueeseen, tai muuta kaavaa siten, että se viittaa suoraan solualueeseen (esimerkiksi A2:D8).

• Jos laskentataulukko puuttuu ja siihen viittaava kaava palauttaa #VIITTAUS!-virheen, virhettä ei voi korjata. -virhe, tätä ei valitettavasti voi korjata – poistettua laskentataulukkoa ei voi palauttaa.

• Jos työkirja puuttuu, siihen viittaava kaava säilyy muuttumattomana, kunnes päivität kaavan.

  Esimerkiksi, jos kirjoitat kaavan =[Työkirja1.xlsx]Taul1'!A1 eikä sinulla ole enää tiedostoa nimeltä Työkirja1.xlsx, tästä työkirjasta peräisin olevat arvot pysyvät edelleen käytettävissä. Jos kuitenkin muokkaat kyseiseen työkirjaan viittaavaa kaavaa ja tallennat sen, Excel näyttää Päivitä arvot ‑valintaikkunan ja pyytää lisäämään tiedostonimen. Valitse Peruuta ja varmista sitten, että tietoja ei menetetä korvaamalla puuttuvaan työkirjaan viittaavat kaavat kaavan tuloksilla.

Joskus solun sisältöä kopioidessasi voit haluta liittää vain arvon, mutta et kohteessa kaavarivi näkyvää, tietojen perustana olevaa kaavaa.

Saatat esimerkiksi haluta kopioida kaavan tuloksena saatavan arvon jonkin toisen laskentataulukon soluun. Voit myös haluta poistaa kaavassa käyttämäsi arvot sen jälkeen, kun olet kopioinut tuloksena saatavan arvon toiseen saman laskentataulukon soluun. Molemmat toiminnot aiheuttavat virheellisen soluviittausvirheen (#VIITTAUS!) näkymisen kohdesolussa, koska soluihin, jotka sisältävät kaavassa käytetyt arvot, ei voi enää viitata.

Voit välttää tämän virheen liittämällä vain kaavojen tuloksena saatavat arvot kohdesoluihin ja jättämällä kaavan liittämättä.

1. Valitse laskentataulukossa solut, jotka sisältävät kopioitavat kaavan tulosarvot.

2. Valitse Aloitus-välilehdenLeikepöytä-ryhmässäKopioi .

   

   Pikanäppäin: Paina näppäinyhdistelmää CTRL+C.

3. Valitse kohdealue vasen yläsolu.

   Vihje: Jos haluat siirtää tai kopioida valinnan eri laskentataulukkoon tai työkirjaan, valitse toisen laskentataulukon välilehti tai siirry toiseen työkirjaan ja valitse sitten kohdealueen vasen yläsolu.

4. Valitse Aloitus-välilehdenLeikepöytä-ryhmässäLiitä ja valitse sitten Liitä arvot tai paina Alt > E > S > V > Enter for Windows tai Optio > Komento > V > V > Enter Macissa.

Selvittääksesi, miten monimutkainen tai sisäkkäisiä osia sisältävä kaava laskee lopputuloksen, voit laskea kyseisen kaavan.

1. Valitse laskettava kaava.

2. Valitse Kaavat > Laske kaava.

   

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

   

4. Jos kaavan alleviivattu osa on viittaus toiseen kaavaan, valitsemalla Suorita rivi voit näyttää toisen kaavan Laskenta-ruudussa. Voit palata takaisin edelliseen soluun ja kaavaan valitsemalla Suorita toimintosarja loppuun.

   Vaihe sisään -painike ei ole käytettävissä, kun viittaus näkyy kaavassa toisen kerran tai jos kaava viittaa toisen työkirjan soluun.

5. Jatka, kunnes olet laskenut kaavan jokaisen osan.

   Kaavan laskenta -työkalu ei välttämättä kerro, miksi kaava ei toimi, mutta se voi auttaa näyttämään virheen sijainnin. Tämä voi olla erittäin hyödyllinen työkalu suuremmissa kaavoissa, joissa ongelmaa voi olla muutoin hankala löytää.

   Huomautukset: 

   • Joitakin JOS - ja VALITSE-funktioiden osia ei arvioida, ja #N/A-virhe saattaa näkyä Arviointi-ruudussa .

   • Jos viittaus on tyhjä, Laskenta-ruudussa näkyy nolla-arvo (0).

   • Jotkut funktiot lasketaan uudelleen aina, kun laskentataulukkoon tehdään muutoksia. Nämä funktiot, joita ovat esimerkiksi SATUNNAISLUKU, ALUEET, INDEKSI, SIIRTYMÄ, SOLU, EPÄSUORA, RIVIT, SARAKKET, NYT, TÄMÄ PÄIVÄ ja SATUNNAISLUKU.VÄLILTÄ, voivat näyttää Kaavan laskeminen ‑valintaikkunassa tuloksia, jotka poikkeavat laskentataulukon solussa näkyvistä todellisista tuloksista.