Viallisten kaavojen ratkaiseminen Excelissä

Jos Excel ei pysty ratkaisemaan kaavaa, jota yrität luoda, saatat saada seuraavanlaisen virhesanoman:

Tämä tarkoittaa valitettavasti sitä, että Excel ei ymmärrä, mitä yrität tehdä, joten sinun on päivitettävä kaava tai varmistettava, että käytät funktiota oikein.

Palaa soluun, jossa on katkennut kaava, joka on muokkaustilassa, ja Excel korostaa ongelman paikan. Jos et vieläkään tiedä, mitä tehdä ja haluat aloittaa alusta, voit painaa ESC-näppäintä uudelleen tai valita kaavariviltä Peruuta-painikkeen , joka vie sinut pois muokkaustilasta.

Jos haluat siirtyä eteenpäin, seuraavassa tarkistusluettelossa on vianmääritysohjeita, joiden avulla voit selvittää, mikä on saattanut mennä vikaan. Saat lisätietoja valitsemalla otsikot.

Käytätkö kaavassa oikeita luetteloerottimia?

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 artikkelissa Kaavavirheet, kun luetteloerotinta ei ole määritetty oikein

Näetkö ristikkovirheen (#) Excelin kaavoissa?

Excel heittää useita ristikkovirheitä (#), kuten #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, ja #NULL! osoittaaksesi, 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.

Viittaa seuraaviin ohjeaiheisiin, jotka vastaavat näkemääsi ristikkovirhettä:

• #LUKU! -virheen korjaaminen
• #ARVO! -virheen korjaaminen
• #PUUTTUU!-virheen korjaaminen
• #JAKO/0! -virheen korjaaminen
• #VIITTAUS! -virheen korjaaminen
• #NIMI?-virheen korjaaminen
• #TYHJÄ! -virheen korjaaminen

Katkenneiden linkkien korjaaminen Excelin kaavoissa

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ä olevan valintaikkunan sen varmistamiseksi, että nykyisen laskentataulukon kaavat osoittavat aina päivittyneimpään arvoon siltä varalta, että viitearvo 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 siirtymällä Kohtaan Tiedostoasetusten >> lisäasetukset > Yleiset ja poistamällä Pyydä päivittämään automaattiset linkit -ruudun valinnan.

Kaava näyttää Excelin arvon sijasta syntaksin

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 solua hiiren kakkospainikkeella ja valita Muotoile solut > yleiset (tai Ctrl + 1) ja muuttaa sitten muotoa painamalla F2 > Enter -näppäintä .

• Jos sinulla on sarake, jossa on suuri solualue, joka on muotoiltu tekstiksi, voit valita alueen, käyttää haluamaasi lukumuotoilua ja siirtyä tietoteksti > sarakkeen > loppuun. Muotoa käytetään kaikissa valituissa soluissa.

  

Ota automaattinen työkirjan laskenta käyttöön, jos kaavaa ei lasketa Excelissä

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 Laskenta-asetukset-osanTyökirjan laskenta -kohdassa, että Automaattinen-vaihtoehto on valittuna.

   

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

Kaavassa on vähintään yksi kehäviittaus

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.

Onko funktion alussa yhtäläisyysmerkki (=)?

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.

Onko alkusulkeita ja loppusulkeita yhtä paljon?

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 kelvollinen",B5*1,05) ei toimi, koska suljesulkeita on kaksi, mutta vain yksi avaava sulkeet. Oikea kaava näyttäisi tältä: =JOS(B5<0,"Ei kelvollinen",B5*1,05).

Sisältyvätkö kaikki tarvittavat argumentit syntaksiin?

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, kun taas toiset funktiot, kuten KORVAA, vaativat tekstiarvon vähintään yhdelle argumenteistaan. 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.

Muotoilemattomien lukujen käsitteleminen Excel-kaavoissa

Älä kirjoita kaavoihin lukuja, jotka on muotoiltu dollarimerkillä ($) tai desimaalierottimella (,), koska dollarimerkit osoittavat , että absoluuttiset viittaukset ja pilkut ovat argumentin erottimia. 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.

Kuuluvatko viitatut solut oikeisiin tietotyyppeihin?

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.

Yritätkö kertoa lukuja käyttämättä *-symbolia?

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 #NAME? Virhe.

Puuttuuko kaavoista lainausmerkit tekstin ympäriltä?

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. Jos tekstin ympärillä ei ole lainausmerkkejä, kaava voi näyttää #NIMI?-virheen.

Onko kaavassa enemmän kuin 64 funktiota?

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

Esimerkiksi kaava =JOS(NELIÖJUURI(PII())<2,"Pienempi kuin kaksi!","Enemmän kuin kaksi!") sisältää kolme funktiotasoa; PII-funktio sisältyy NELIÖJUURI-funktioon, joka puolestaan sisältyy JOS-funktioon.

Onko taulukoiden nimet kirjoitettu puolilainausmerkkeihin?

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ää #NAME? -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.

Ulkoisten työkirjapolkujen korjaaminen Excel-kaavoissa

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ää #REF! -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).

Oletko jakamassa numeerisia arvoja nollalla?

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).

Viittaako kaava poistettuihin tietoihin?

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 jos se palauttaa #REF! -virheen, valitse solu, jossa on #REF! -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.

Oletko kopioinut ja liittänyt soluja, jotka liittyvät laskentataulukon kaavaan?

Joskus, kun kopioit solun sisällön, haluat liittää vain arvon eikä kaavarivillä näkyvää pohjana 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 (#REF!), 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-painikkeen .
   
   Pikanäppäin: Paina näppäinyhdistelmää CTRL+C.

3. Valitse kohdealueen 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ä ja valitse sitten Liitä arvot tai paina Alt > E > S > V > Enter for Windows tai Optio > Command > V > V > Enter Macissa.

Jos kaava sisältää sisäkkäisiä osia, laske kaava vaihe kerrallaan

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ää.

   Huomautus

   • 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.

Tarvitsetko lisätietoja?

Voit aina pyytää apua Excel Tech Communityn asiantuntijalta tai saada tukea yhteisöissä.

Katso myös

Yleiskuva kaavoista Excelissä

Excelin ohje & oppimiseen