Viallisten kaavojen ratkaiseminen

Viallisten kaavojen ratkaiseminen

Huomautus: Microsoft haluaa tarjota sinulle ajantasaisinta ohjesisältöä mahdollisimman nopeasti omalla kielelläsi. Tämä sivu on käännetty automaation avulla, ja siinä saattaa olla kielioppivirheitä tai epätarkkuuksia. Tarkoitus on, että sisällöstä on sinulle hyötyä. Kertoisitko sivun alareunassa olevan toiminnon avulla, oliko tiedoista hyötyä? Tästä pääset helposti artikkelin englanninkieliseen versioon.

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, että Excel ei ymmärrä, mitä yrität tehdä, joten haluat ehkä vain poistua toiminnosta ja aloittaa alusta.

Aloita napsauttamalla OK tai sulje virheviesti painamalla ESC.

Palaat soluun, jossa on vioittunut kaava, joka on muokkaus tilassa, ja Excel korostaa paikan, jossa ongelma ilmenee. Jos et edelleenkään tiedä, mitä haluat tehdä, ja haluat aloittaa alusta, paina ESC -näppäintä uudelleen tai napsauta kaava rivin Peruuta -painiketta, jolloin sinut poistutaan muokkaus tilasta.

Kaava-palkin Peruuta-painikkeen kuva

Jos et ole varma, mitä voit tehdä tässä vaiheessa tai minkälaista apua tarvitset, voit etsiä samantyyppisiä kysymyksiä Excelin Community-keskustelupalstalta tai lähettää oman kysymyksen.

Linkki Excelin Community-keskustelupalstalle

Jos haluat jatkaa, voit seuraavan tarkistusluettelon vianmääritysvaiheiden avulla selvittää, mikä on ehkä mennyt väärin kaavoissa.

Excel tuo näyttöön erilaisia hajautus arvon (#) virheitä, kuten #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? ja #NULL!, jos haluat, että jokin kaavassa oleva merkki ei toimi oikein. Esimerkiksi #VALUE! virhe johtuu virheellisestä muotoilusta tai argumenttien tieto tyypeistä, joita ei tueta. Tai näet #REF! virhe, jos kaava viittaa soluihin, jotka on poistettu tai korvattu muilla tiedoilla. Vian määritys ohjeet vaihtelevat virheiden 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

Lisätietoja virheilmoitusta vastaavasta hash-virheestä on seuraavissa artikkeleissa:

Aina kun avaat laskenta taulukon, joka sisältää muissa laskenta taulukoissa oleviin arvoihin viittaavia kaavoja, sinua pyydetään päivittämään viitta ukset tai jättämään ne sellaisenaan.

Rikkoutuneen viittaukset -valintaikkuna Excelissä

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 poistamalla sitten Ehdota automaattisten linkkien päivitystä -valintaruudun valinnan. Napsauta Excel 2007:ssä Office-painike > Excel-asetukset. Office 2007 -painike

Ask-kuva automaattisen linkkiasetuksen päivittämiseksi

Tärkeää: Jos käsittelet kaavojen katkenneita linkkejä ensimmäistä kertaa tai et muista, miten katkenneet linkit korjataan, tai et ole varma, haluatko päivittää viittaukset, lue artikkeli Ulkoisten viittausten (linkkien) päivitysajankohdan valvominen.

Ellei kaava näytä arvoa, toimi seuraavasti:

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

    Vihje: Voit myös käyttää pikanäppäintä CTRL + ' (SARKAIN näppäimen yläpuolella oleva näppäin). Kun teet näin, sarakkeet laajenevat automaattisesti näyttämään kaavat, mutta älä huoli, kun siirryt takaisin normaaliin näkymään, jonka sarakkeita muutetaan.

  • 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 solualue, joka on muotoiltu tekstiksi, valitse alue, käytä haluamaasi lukumuotoa ja siirry kohtaan Tiedot > Sarakkeen teksti > Valmis. Muotoa käytetään kaikissa valituissa soluissa.

    Tiedot > Teksti sarakkeisiin -valintaikkunan kuva

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. Ota automaattinen laskenta käyttöön seuraavasti:

  1. Napsauta Tiedosto-välilehdestä Asetukset ja napsauta sitten Kaavat-luokkaa.

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

    Automaattisen ja manuaalisen laskennan asetukset -kuva

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ällaisessa tapa uksessa sinun on otettava käyttöön Poista tai salli kehä viittaus

Lisä tietoja kehä viittauksista on Ohje aiheessa 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 summaa (a1: A10), Excel näyttää teksti merkki jonon summan (a1: A10) kaavan tuloksen sijaan. Jos kirjoitat 11/2, Excel tuo näkyviin päivä määrän, kuten 2-Nov tai 11/02/2009, sen sijaan, että jaat sen 11 vuoteen 2.

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

Kun käytät funktiota kaavassa, kukin avaava kaari Sulje edellyttää, että funktio toimii oikein, joten varmista, että kaikki sulkeet ovat osa sopivaa paria. Esimerkiksi kaava = Jos (B5<0), "ei kelvollinen", B5 * 1.05) ei toimi, koska on olemassa kaksi sulkevaa suljetta, mutta vain yksi avaava Sulje. Oikea kaava näyttää tältä: =JOS(B5<0;"Ei kelpaa";B5*1,05).

Excel-funktioilla on argumentteja – arvoja, jotka on määritettävä, jotta funktio toimisi. Vain muutamalla funktiolla (kuten pi tai tänään) ei ole argumentteja. Tarkista kaavan syntaksi, joka tulee näkyviin, kun aloitat funktion kirjoittamisen ja varmistat, että funktiossa on tarvittavat argumentit.

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

Huomautus: Kun kirjoitat kaavaa, funktion argumentit näkyvät irrallisessa Funktioviittaus-työkalurivissä kaavan alla.

Näyttökuva Funktioviittaus-työkalurivistä

Jotkin funktiot, kuten summa, vaativat vain numeerisia argumentteja, kun taas muut funktiot, kuten Korvaava, vaativat teksti arvon vähintään yhdelle argumenteista. Jos käytät väärää tieto tyyppiä, funktiot saattavat palauttaa odottamattomia tuloksia tai näyttää #VALUE! -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 ja pilkut eivät toimi suomenkielisessä Excelissä. Kirjoita kaavaan arvon $1000 sijaan 1000.

Jos käytät argumenteissa muotoilu lukuja, saat odottamattomia lasku toimituksia, mutta saatat nähdä myös #NUM! -virheen. Jos esimerkiksi kirjoitat kaavan = ABS (-2 134) löytääksesi itseisarvon-2134, Excel tuo näkyviin #NUM! -virheen, koska ITSEARVO-funktio hyväksyy vain yhden argumentin.

Huomautus: Voit muotoilla kaavan Tulosta käyttämällä desimaali erotinta ja valuutta symboleita sen jälkeen, kun olet kirjoittanut kaavan muotoilemattomilla numeroilla (vakioilla). Kaavoissa ei yleensä kannata lisätä vakioita, koska niitä on vaikea löytää, jos niitä on päivitettävä myöhemmin, ja ne ovat alttiimpia kirjoitetuiksi virheellisesti. On paljon parempi sijoittaa vakiot soluihin, joissa ne ovat auki ja helposti viitattuina.

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 ratkaista ongelman vaihtamalla solun Teksti-tietotyypin Yleinen-tietotyypiksi noudattamalla seuraavia ohjeita:

  1. Valitse solu.

  2. Napsauta Aloitus >-nuolta Lukumuotoilu-kohdan vieressä (tai paina Ctrl + 1), ja napsauta Yleinen.

  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 solu viittauksia, Excel palauttaa #NAME? -virhettä.

#NIMI? Virhe käytettäessä x-funktiota solu viittauksilla * kertolaskua varten

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 työkirjan neljännesvuosittaiset tiedot-laskenta taulukon solun D3 arvon, kirjoita: = ' neljännesvuosittaiset tiedot '! D3. Ilman taulukon nimen ympärillä olevia lainaus merkkejä kaava tuo näkyviin #NAME?-virheen..

Voit viitata kaavassasi toisen taulukon arvoihin tai soluihin myös napsauttamalla haluamaasi arvoa tai solua. 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 soluihin a1 – A8, jotka ovat avoinna Excelissä avatussa Q2-operaatioiden työkirjassa, kirjoita: = [Q2 Operations. xlsx] myynti! A1: A8. Ilman Haka sulkeita kaava tuo näkyviin #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).

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: Saat polun toiseen työkirjaan helpoiten avaamalla toisen työkirjan, kirjoittamalla sitten alkuperäisessä työkirjassa = ja siirtymällä sitten toiseen työkirjaan Alt + sarkain -näppäinyhdistelmällä ja valitsemalla lopuksi haluamasi solun laskentataulukosta. Sulje lopuksi 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ä.

=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 tuloksilla, tutustu seuraaviin virheitä ja niiden mahdollisia korjaamistapoja koskeviin ohjeisiin:

  • Jos kaava viittaa soluihin, jotka on poistettu tai korvattu muilla tiedoilla, ja palauttaa #REF!-virheen., valitse solu, jossa on #REF! -virhettä. Valitse kaava riviltä #REF! ja poista se. Kirjoita kaavan arvo 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 Peruuta ja korvaa sitten puuttuvaan työkirjaan viittaavat kaavat niiden tuloksilla varmistaaksesi, ettei tietoja katoa.

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

Haluat ehkä esimerkiksi kopioida kaavan tulos arvon toisen laskenta taulukon soluun. Tai haluat ehkä poistaa arvot, joita käytit kaavassa sen jälkeen, kun olet kopioinut tulos arvon toiseen laskenta taulukon soluun. Kumpikin näistä toiminnoista aiheuttaa virheellisen solu viittaus virheen (#REF!) näkyvän kohde solussa, koska kaavassa käytettyjä arvoja sisältäviä soluja 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. Napsauta Aloitus-välilehden Leikepö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 eri laskentataulukkoon tai työkirjaan, napsauta toisen laskentataulukon välilehteä tai siirry toiseen työkirjaan ja valitse sitten kohdealueen vasen yläsolu.

  4. Valitse Aloitus -väli lehden leike pöytä -ryhmässä Liitä Painikkeen kuva , ja valitse sitten Liitä arvot, tai paina Alt _Gt_ E _Gt_ S _Gt_ v _GT_ Windows, tai optio _ng_ v _ng_ v _GT_ Mac.

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.

    Kaava-välilehden Kaavan tarkistaminen -ryhmä

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

    Kaavan laskeminen -valintaikkuna

  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 kaavassa on katkenneita linkkejä, mutta se voi auttaa näyttämään niiden paikan. Tämä voi olla erittäin hyödyllinen työkalu suuremmissa kaavoissa, joissa ongelmaa voi olla muutoin hankala löytää.

    Huomautukset: 

    • Joitakin osia JOS- ja VALITSE-funktioista ei lasketa, ja Laskenta-ruutuun saattaa tulla #PUUTTUU!-virhe.

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

    • Tietyt 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, saada tukea yhteisön vastauksista tai ehdottaa uutta ominaisuutta tai parannusta Excel User Voice -sivustolla.

Katso myös

Yleiskuva kaavoista Excelissä

Kaavoissa esiintyvien virheiden etsiminen

Excelin funktiot (aakkosjärjestyksessä)

Excelin funktiot (luokittain)

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

×