Viallisten kaavojen ratkaiseminen

Viallisten kaavojen ratkaiseminen

Jos Excel ei voi ratkaista kaavaa, jota yrität luoda, näkyviin voi tulla seuraavanlainen virheviesti:

Excelin "Ongelma tässä kaavassa" -valintaikkuna

Tämä tarkoittaa valitettavasti sitä, että Excel ei ymmärrä, mitä yrität tehdä, joten kannattaa ehkä aloittaa alusta.

Aloita valitsemalla OK tai sulje virhesanoma painamalla ESC-näppäintä.

Palaat soluun, jossa on rikkinäinen kaava, joka on muokkaustilassa, ja Excel korostaa ongelman paikan. Jos et edelleenkään tiedä, mitä sinun pitäisi tehdä ja haluat aloittaa alusta, voit painaa ESC-näppäintä uudelleen tai valita kaavarivillä Peruuta-painikkeen, jolloin poistut muokkaustilasta.

Kaava-palkin Peruuta-painikkeen kuva

Jos haluat siirtyä eteenpäin, seuraava tarkistusluettelo sisältää vianmääritysohjeita, joiden avulla voit selvittää, mikä on voinut mennä vikaan.

Huomautus: Jos käytät samaaOfficen verkkoversio, et ehkä näe samoja virheitä tai ratkaisut eivät ehkä ole voimassa.

Excel antaa erilaisia punnan (#) virheitä, kuten #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? ja #NULL!, jotta kaavan jokin osa ei toimi oikein. Esimerkiksi #VALUE! -virhe johtuu argumenttien virheellisestä muotoilusta tai tietotyypeistä, joita ei tueta. Tai näet #REF! -virheen, jos kaava viittaa soluihin, jotka on poistettu tai korvattu muilla tietoilla. Vianmääritysohjeet vaihtelevat kunkin virheen mukaan.

Huomautus: #### ei ole kaavoihin liittyvä virhe. Se tarkoittaa vain sitä, että sarake ei ole tarpeeksi leveä solun sisällön näyttämiseen. Levennä saraketta vetämällä sitä, tai valitse Aloitus > Muotoile > Sovita sarakkeen leveys.

Kuva Aloitus > Muotoile > Sovita sarakkeen leveys

Tutustu seuraaviin ohjeaiheisiin, jotka vastaavat näkyviin tulee ruutuvirhettä:

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

Rikkoutuneen viittaukset -valintaikkuna Excelissä

Excel näyttää yllä olevan valintaikkunan ja varmistaa, että nykyisen laskentataulukon kaavat osoittaa aina eniten päivitettyä arvoa, jos viittausarvo muuttuu. 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 menemällä Kohtaan > asetukset > lisäasetukset >Yleiset ja poista Pyydä päivittämään automaattiset linkit -valintaruudun valinta.

Ask-kuva automaattisen linkkiasetuksen päivittämiseksi

Tärkeää: Jos tämä on ensimmäinen kerta, kun käytät katkenneita linkkejä kaavoissa, jos tarvitset päivityksen katkenneiden linkkien ratkaisemiseen tai jos et tiedä, haluatko päivittää viittaukset, katso kohta Ulkoisten viittausten (linkkien)päivitysten hallinta.

Ellei kaava näytä arvoa, toimi seuraavasti:

  • Varmista, että Excel on määritetty näyttämään laskentataulukon kaavat. Voit tehdä tämän valitsemalla Kaavat-välilehden ja valitsemalla Kaavan valvonta -ryhmässäNäytä kaavat.

    Vihje: Voit käyttää myös pikanäppäintä Ctrl + ' (sarkainnäppäimen yläpuolella oleva näppäin). Kun teet näin, sarakkeet suurenevat automaattisesti näyttämään kaavat, mutta ei huolta, kun vaihdat takaisin normaalinäkymään, sarakkeiden koko muuttuu.

  • Jos yllä oleva vaihe ei edelleenkään ratkaise ongelmaa, solu on ehkä muotoiltu 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 solualue, joka on muotoiltu tekstiksi, voit valita alueen, käyttää haluamasi lukumuotoilun ja valita Tiedot > Teksti sarakkeeseen > Valmis. Muotoa käytetään kaikissa valituissa soluissa.

    Tiedot > Teksti sarakkeisiin -valintaikkunan kuva

Kun kaava ei laske, sinun on tarkistattava, onko automaattinen laskenta käytössä Excelissä. Kaavat eivät laske, jos manuaalinen laskenta on käytössä. Voit tarkistaa automaattisen laskutoimituksen noudattamalla seuraavia ohjeita.

  1. Valitse Tiedosto-välilehti,valitse Asetuksetja valitse sitten Kaavat-luokka.

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

    Automaattisen ja manuaalisen laskennan asetukset -kuva

Lisätietoja laskelmista on artikkelissa Kaavojen uudelleenlaskennan, iteroinnin tai tarkkuuden muuttaminen.

Kehäviittaus tapahtuu, kun kaava viittaa soluun, jossa se sijaitsee. Korjaus on joko siirtää kaava toiseen soluun tai muuttaa kaavan syntaksiksi sellaisen kaavan syntaksiksi, joka välttää kehäviittaukset. Joissain tapauksissa saatat kuitenkin tarvita kehäviittauksia, koska niiden ansiosta kaavojasi toistetaan, kunnes tietty lukuehto saavutetaan. Tällöin sinun on otettava käyttöön Poista tai salli kehäviittaus.

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

Jos merkinnän alussa ei ole yhtäläisyysmerkkiä, se ei ole kaava, eikä sitä lasketa — tämä on tavallinen virhe.

Kun kirjoitat jotain, kuten SUMMA(A1:A10),Excel näyttää tekstimerkkijonon SUMMA(A1:A10) kaavan tuloksen sijaan. Jos kirjoitat päivämäärän 11.11.2009,Excel näyttää päivämäärän, kuten 2.11.2009 tai 2.11.2009, sen sijaan, että se jakasi 11:n arvolla 2.

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 sulkeita on kaksi, mutta alkusulkeita on vain yksi. Oikea kaava näyttää tältä: =JOS(B5<0;"Ei kelpaa";B5*1,05).

Excel-funktioilla on argumentteja, eli arvoja, jotka tarvitset funktion toimintaa varten. Vain muutama funktio (kuten PII tai TÄMÄ.PÄIVÄ) ei ota mitään argumentteja. Tarkista funktion kirjoittamisen aikana näkyviin tuleva kaavasyntaksi ja varmista, että funktiossa on tarvittavat argumentit.

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

Huomautus: Funktion argumentit näkyvät irrallisen funktioviittauksen työkalurivillä kaavan alapuolella kirjoittaessasi sitä.

Näyttökuva Funktioviittaus-työkalurivistä

Jotkin funktiot, kuten SUMMA,edellyttävät vain numeerisia argumentteja, kun taas toiset funktiot, kuten KORVAA,vaativat tekstiarvon ainakin yhdelle argumenteistaan. Jos käytät väärää tietotyyppiä, funktiot voivat palauttaa odottamattomia tuloksia tai näyttää #VALUE! -virheen.

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

Älä kirjoita kaavojin lukuja, jotka on muotoiltu dollarimerkkien ($) tai desimaalierotinten (,) avulla, koska dollarimerkit osoittavat suorat viittaukset ja pilkut ovat argumenttien erotinmerkkejä. Kirjoita kaavaan arvon $1000 sijaan 1000.

Jos käytät argumentissa muotoiltuja lukuja, saat odottamattomia laskutuloksia, mutta saatat nähdä myös #NUM! -virheen. Jos esimerkiksi kirjoitat kaavan =ITSEISARVO(-2,134), jotta saat selville itseisarvon -2134, Excel näyttää #NUM! -virheen, koska ITSE.ABS-funktio hyväksyy vain yhden argumentin ja näkee -2 ja 134 erillisinä argumenttina.

Huomautus: Voit muotoilla kaavan tuloksen desimaalierotin- ja valuuttasymboleilla kaavan kirjoittamisen jälkeen käyttämällä muotoilemattomia lukuja (vakioita). Vakioita ei yleensä kannattaa lisätä kaavoihin, koska niitä voi olla vaikea löytää, jos niitä on päivitettävä myöhemmin ja ne ovat altis kirjoitettomille väärin. On paljon parempi laittaa vakiot soluihin, joissa ne ovat avoinna ja niihin on helppo viitata.

Kaava ei välttämättä palauta odotettuja tuloksia, jos solun tietotyyppi ei sovellu laskutoimituksiin. 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 tämän vaihtamalla solun Teksti-tietotyypin Yleiset-tietotyypiksi näin:

  1. Valitse solu.

  2. Valitse Aloitus ja laajenna Luku- tai Lukumuotoilu-ryhmä valitsemalla nuoli (tai paina 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.

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

Viestiruutu, jossa pyydetään korvaamaan x-merkki *-merkillä kertolaskua varten

Jos kuitenkin käytät soluviittauksia, Excel palauttaa #NAME? Jos esimerkiksi yrität luoda Pivot-taulukon näiden tietotyyppien tietojen perusteella, saat #ARVO!-virheen.

#NIMI? -virhe, kun x-kirjainta käytetään soluviittausten kanssa *-kertolaskun sijaan

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 kaava =JOS(QRT(PII())<2,"Pienempi kuin kaksi!","Enemmän kuin kaksi!") on 3 funktiotasoa; PII-funktioonQRT-funktionsisällä, ja se puolestaan on SISÄKKÄINEN JOS-funktion sisällä.

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 vuosineljänneksen tiedot -laskentataulukon solusta D3, kirjoita : ='Vuosineljänneksen tiedot'! D3. Jos taulukon nimen ympärillä ei ole lainausmerkkejä, kaava näyttää #NAME?.

Voit myös valita toisen taulukon arvoja tai soluja, jos haluat viitata niihin kaavassa. 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ä avoimen Q2 Operations -työkirjan Myynti-taulukon soluihin A1–A8, kirjoita =[Operations.xlsx]Myynti! A1:A8. Ilman hakasulkeita kaava näyttää #REF!.

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

Huomautus: Jos täydellisessä polussa on välilyöntejä, polku on kirjoitettava puolilainausmerkkeihin, jotka sijoitetaan polun alkuun ja laskentataulukon nimen jälkeen ennen huutomerkkiä.

Vihje: Helpoin tapa saada polku toiseen työkirjaan on avata toinen työkirja, kirjoittaa sitten alkuperäisessä työkirjassa =ja siirtyä toiseen työkirjaan näppäinyhdistelmällä Alt+Sarkain. Valitse haluamasi laskentataulukon solu ja sulje sitten lähdetyökirja. Kaavasi päivitetään automaattisesti niin, että siinä näkyy koko tiedostopolku ja taulukon nimi tarvittavan syntaksin ohella. Voit myös kopioida ja liittää polun ja käyttää sitä aina, kun sitä tarvitset.

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

=JOS(B1,A1/B1,0)

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

Tarkista aina ennen poistoa, onko soluissa, alueissa, määritetyissä nimissä, laskentataulukoissa tai työkirjoissa kaavoja, jotka viittaavat soluissa, alueissa, määritetyissä nimissä, laskentataulukoissa tai työkirjoissa. Tarvittaessa voit korvata kaavat niiden tuloksilla, ennen kuin poistat tiedot, joihin kaavoissa on viitattu.

Jos et voi korvata kaavoja tuloksilla, tutustu seuraaviin virheitä ja niiden mahdollisia korjaamistapoja koskeviin ohjeisiin:

  • Jos kaava viittaa soluihin, jotka on poistettu tai korvattu muilla tisanoilla, ja jos se palauttaa #REF! -virheen, valitse solu, jossa #REF! Jos esimerkiksi yrität luoda Pivot-taulukon näiden tietotyyppien tietojen perusteella, saat #ARVO!-virheen. Valitse kaavarivillä #REF! 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. 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 Peruutaja varmista sitten, että tietoja ei menetetä, korvaamalla puuttuvaan työkirjaan viittaavat kaavat kaavan tuloksilla.

Joskus, kun kopioit solun sisällön, haluat ehkä liittää vain arvon, ei pohjana olevaa kaavaa, joka näkyy kaavarivi.

Voit esimerkiksi kopioida kaavan tuloksena saatavan arvon toisen laskentataulukon soluun. Voit myös poistaa kaavassa käyttämäsi arvot sen jälkeen, kun olet kopioinut tuloksena olevan arvon toiseen laskentataulukon soluun. Molemmat toiminnot aiheuttavat virheellisen soluviittausvirheen (#REF!) näkyvät 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ä kaavojen tuloksena saatavat arvot kohdesoluihin ilman kaavaa.

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

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

    Excelin valintanauhan kuva

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

  3. Valitse kohdealue vasen yläsolu.

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

  4. Valitse Aloitus-välilehden Leikepöytä-ryhmässä Liitä Painikkeen kuva ja valitse sitten Liitä arvot tai paina Alt > E > S > V > Enter for Windows tai Vaihtoehto > 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>kaavan laskeminen.

    Kaava-välilehden Kaavan tarkistaminen -ryhmä

  3. Valitse Laske, jos haluat tarkastella alleviivauksen arvoa. Laskennan tulos näkyy kursivoituna.

    Kaavan laskeminen -valintaikkuna

  4. Jos kaavan alleviivattu osa on viittaus toiseen kaavaan, valitse Vaihe sisään, jos haluat näyttää toisen kaavan Laskenta-ruudussa. Palaa edelliseen soluun ja kaavaan valitsemalla Siirry ulos.

    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 laskeminen -työkalu ei välttämättä kerro, miksi kaava on katkennut, mutta se voi auttaa eromaan siitä, missä kohtaa kaava on. Tämä voi olla erittäin hyödyllinen työkalu suuremmissa kaavoissa, joissa ongelmaa voi olla muutoin hankala löytää.

    Huomautukset: 

Tarvitsetko lisätietoja?

Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta tai saada tukea yhteisön vastauksista.

Katso myös

Yleiskuva kaavoista Excelissä

Kaavoissa esiintyvien virheiden etsiminen

Excelin funktiot (aakkosjärjestyksessä)

Excelin funktiot (luokittain)

Tarvitsetko lisäohjeita?

Kehitä Office-taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×