Viallisten kaavojen ratkaiseminen Excelissä

Jos Excel ei pysty ratkaisemaan kaavaa, jota yrität luoda, näkyviin voi tulla seuraavanlainen virhesanoma:

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 takaisin soluun, jossa on rikkoutunut kaava. Se on muokkaustilassa, ja Excel korostaa ongelmallisen kohdan. Jos et vieläkään tiedä, mitä tehdä ja haluat aloittaa alusta, paina ESC-näppäintä uudelleen tai valitse kaavarivin Peruuta-painike , jolloin poistut muokkaustilasta.

Jos haluat jatkaa, voit seuraavan tarkistusluettelon vianmääritysvaiheiden avulla selvittää, mikä on ehkä mennyt pieleen. 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 seuraavissa artikkeleissa: Kaavavirheitä, kun luetteloerotinta ei ole määritetty oikein

Näetkö Excelin kaavoissa ristikkomerkin (#) virheen?

Excel näyttää ristikkomerkillä (#) merkittyjä virheitä, kuten #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? ja #NULL! ilmaisemaan, 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 ohjeaiheista:

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

Excel-kaavojen katkenneiden linkkien 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 varmistaakseen, 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 siirtymällä kohtaan Tiedoston > asetukset > Yleiset lisäasetukset >ja tyhjentämällä Pyydä automaattisten linkkien päivitystä -ruudun valinnan.

Kaava näyttää syntaksin arvon sijaan Excelissä

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 näin, sarakkeet levennetään automaattisesti näyttämään kaavat. Kun palaat takaisin normaalinäkymään, sarakkeiden koko palautetaan.

• 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 muotoa painamalla F2-näppäintä>.

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

  

Automaattisen työkirjan laskennan ottaminen 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, että Laskenta-asetukset-osanTyökirjan laskeminen -kohdassa on valittuna Automaattinen-vaihtoehto .

   

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 merkinnän alussa ei ole yhtäläisyysmerkkiä, se ei ole kaava, eikä sitä lasketa — tämä on 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 kelpaa",B5*1,05) ei toimi, koska loppusulkeita on kaksi, mutta alkusulkeita on vain yksi. Oikea kaava näyttää tältä: =JOS(B5<0;"Ei kelpaa";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. 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.

Muotoilemattomien lukujen käsitteleminen Excel-kaavoissa

Älä käytä kaavojen luvuissa dollarimerkkiä ($) tai pilkkua tuhaterottimena, koska dollarimerkeillä osoitetaan suorat viittaukset ja pilkut erottavat argumentit. Kirjoita kaavaan arvon $1000 sijaan 1000.

Jos käytät argumenteissa muotoiltuja lukuja, saat odottamattomia laskutuloksia tai #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 välttämättä palauta odotettuja tuloksia, jos solun tietotyyppiä ei voi käyttää laskutoimituksissa. Jos esimerkiksi syötät tekstiksi muokattuun soluun kaavan =2+3, Excel ei voi laskea kaavan tietoja. Solussa näkyy vain =2+3. Voit korjata ongelman vaihtamalla solun tietotyypin tekstin sijaan yleiseksi noudattamalla seuraavia ohjeita:

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-tietotyypin mukaiseen soluun syötetty 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? -virheen.

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;"Vähemmän kuin kaksi!";"Enemmän kuin kaksi!") on 3 toimintotasoa; 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 Toisen vuosineljänneksen toiminnot -työkirjan Myynti-taulukon soluihin A1–A8, kirjoita: =[Toisen vuosineljänneksen Operations.xlsx]Myynti! A1:A8. Ilman hakasulkeita kaava näyttää #REF!-virheen.

Jos työkirja ei ole avoinna Excelissä, kirjoita tiedoston täydellinen 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, tutustu seuraaviin virheitä ja niiden mahdollisia ratkaisuja koskeviin ohjeisiin:

• Jos kaava viittaa soluihin, jotka on poistettu tai korvattu muilla tiedoilla ja saat #REF!-virheen, valitse solu, jossa #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. -virheen, tätä ei 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 korvaa sitten puuttuvaan työkirjaan viittaavat kaavat niiden tuloksilla varmistaaksesi, ettei tietoja katoa.

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

Joskus solun sisältöä kopioidessasi haluat ehkä liittää vain arvon, mutta et 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!) 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-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äLiitä-painikkeen ja valitse sitten Liitä arvot tai paina Alt-näppäintä > , E, > S, > V > , ENTER-NÄPPÄINTÄ Windowsissa tai optio-komentoa >> , V > V > Enter-näppäintä 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.
   Suorita rivi -painike ei ole käytettävissä, jos viittaus on kaavassa toiseen kertaan tai jos kaava viittaa jonkin 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.INDEKSI-funktioiden osia ei lasketa, ja #N/A-virhe saattaa tulla näkyviin Käsittely-ruutuun .
   • 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 kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta tai saada tukea yhteisöistä.

Katso myös

Yleiskuva kaavoista Excelissä

Excelin ohje & oppiminen