Katkenud valemite vältimine

Katkenud valemite vältimine

Kui Excel ei leia teie loodavale valemile lahendit, võidakse kuvada umbes selline veateade:

Exceli dialoogiboksi „Leidsime selles valemis probleemi“ pilt

Kahjuks tähendab see, et Excel ei saa aru, mida proovite teha, nii et kõige mõistlikum oleks see katki jätta ja uuesti otsast alustada.

Alustuseks klõpsake veateate sulgemiseks nuppu OK või vajutage paoklahvi (ESC).

Seejärel naasete vigase valemiga lahtrisse, mis on redigeerimisrežiimis, ja Excel tõstab esile probleemse koha. Kui te ikka ei tea, mida edasi teha, ja soovite otsast peale alustada, võite redigeerimisrežiimist väljumiseks vajutada paoklahvi (ESC) või klõpsata valemiribal nuppu Loobu.

Valemiriba tühistamisnupu pilt

Kui te pole kindel, mida nüüd peale hakata või mis liiki abi te vajate, võite otsida enda omaga sarnaseid küsimusi Exceli kogukonnafoorumist või postitada sinna oma küsimuse.

Exceli kogukonnafoorumi link

Kui soovite valemi parandamist jätkata, leiate järgnevast kontroll-loendist tõrkeotsingujuhised, mille abil saate välja selgitada, mis võib valemites vigu põhjustada.

Excel võib kuvada mitmesuguseid trellsildiga (#) veaväärtusi (nt #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? ja #NULL!) näitamaks, et midagi valemis ei toimi õigesti. Näiteks veaväärtuse #VALUE! põhjuseks on vale vorming või see, et argumentides esineb andmeid, mille tüüpi ei toetata. Veaväärtus #REF! aga kuvatakse juhul, kui valem viitab lahtritele, mis on kustutatud või muude andmetega asendatud. Tõrkeotsingujuhised on iga vea puhul erinevad.

Märkus. : Väärtus #### ei tähista valemiga seotud viga. See tähendab lihtsalt, et veerg pole lahtrite sisu kuvamiseks piisavalt lai. Veeru laiemaks muumiseks lohistage seda lihtsalt äärest või valige Avaleht > Vorming > Automaatsobita veeru laiusega.

Menüü Avaleht > Vorming > Automaatsobita veeru laiusega pilt

Täpsemat teavet leiate kuvatavale trellsildiga veaväärtusele vastavast teemast.

Kui avate arvutustabeli, kus on viiteid teistes arvutustabelites sisalduvatele valemitele, küsitakse teilt alati, kas soovite viited värskendada või samaks jätta.

Katkenud viidete dialoogiboks Excelis

Excel kuvab ülaltoodud dialoogiboksi selleks, et kõik töölehe valemid viitaksid viiteväärtuste muutuse korral alati värskendatud väärtustele. Soovi korral saate viited värskendada või värskendamise vahele jätta. Kui te viiteid värskendada ei soovi, saate linke töölehel alati ise värskendada.

Dialoogiboksi kuvamise töölehe avamisel saate igal ajal keelata. Selleks avage Fail > Suvandid > Täpsemalt > Üldist ja tühjendage ruut Küsi enne automaatlinkide värskendamist. Rakenduses Excel 2007 tuleb klõpsata Microsoft Office’i nuppu ja siis valikut Exceli suvandid. Office 2007 nupp

Suvandi Küsi enne automaatset värskendamist pilt

NB! : Kui puutute valemites sisalduvate katkenud linkidega kokku esimest korda, vajate katkenud linkide parandamiseks mäluvärskendust või kui te ei tea, kas viiteid tuleks värskendada, vaadake teemat Välisviidete (linkide) värskendamise reguleerimine.

Kui valem väärtust ei kuva, tehke järgmist.

  • Veenduge, et Excel oleks häälestatud kuvama arvutustabelis valemeid. Selleks klõpsake menüü Valemid jaotises Valemiaudit nuppu Kuva valemid.

    Näpunäide. : Saate kasutada ka kiirklahve Ctrl + ' (klahv tabeldusklahvi (Tab) kohal). Seda tehes muudate veerud valemite kuvamiseks automaatselt laiemaks. Olge mureta: veerulaius muutub endiseks, kui aktiveerite taas normaalvaate.

  • Kui probleem ei lahene ka pärast eespool antud juhiste täitmist, on võimalik, et lahter on vormindatud tekstina. Sel juhul saate lahtrit paremklõpsata ja teha valikud Vorminda lahtreid > Üldine (või kasutada klahvikombinatsiooni Ctrl + 1) ja seejärel vajutada vormingu muutmiseks klahvi F2 ja siis sisestusklahvi Enter.

  • Kui veerus on suur hulk tekstivormingus lahtreid, saate valida lahtrivahemiku ja rakendada sellele soovitud arvuvormingu ning seejärel teha valikud Andmed > Tekst veergudesse > Valmis. Sel juhul rakendatakse soovitud vorming kõigile valitud lahtritele.

    Dialoogiboksi Andmed > Tekst veergudesse pilt

Kui valem ei arvuta, peate kontrollima, kas Excelis on lubatud automaatne arvutamine. Kui lubatud on käsitsi arvutamine, siis valemid ei arvuta. Automaatse arvutamise kontrollimiseks tehke järgmist.

  1. Klõpsake menüüs Fail nuppu Suvandid ja seejärel kategooriat Valemid.

  2. Veenduge, et jaotise Arvutussuvandid alamjaotises Töövihiku arvutamine oleks tehtud valik Automaatselt.

    Automaatse ja käsitsi arvutamise suvandite pilt

Arvutuste kohta lisateabe saamiseks lugege teemat Valemi ümberarvutuse, iteratsiooni või kuvatava arvutustäpsuse muutmine.

Ringviide ilmneb siis, kui valem viitab lahtrile, kus valem ise asub. Lahendus on kas teisaldada valem mõnda teise lahtrisse või muuta valemisüntaks ringviiteid vältivaks. Vahel aga võib ringviiteid vaja minna, kuna need põhjustavad funktsioonide itereerimist ehk arvutamise kordamist kuni kindla arvulise tingimuse täitmiseni. Sel juhul peate lubama iteratiivsed arvutused.

Ringviidete kohta lisateabe saamiseks lugege teemat Ringviite leidmine ja parandamine.

Kui sisestus ei alga võrdusmärgiga, pole see valem ja seda ei arvutata – see on levinud viga.

Kui tipite näiteks SUM(A1:A10), kuvab Excel valemi tulemi asemel tekstistringi SUM(A1:A10). Kui tipite lahtrisse 11/2, kuvab Excel kuupäeva (nt 11. veebr või 11.02.2009), mitte arvude 11 ja 2 jagatise.

Nende ootamatute tulemite vältimiseks alustage funktsiooni alati võrdusmärgiga. Tippige näiteks valem =SUM(A1:A10) ja =11/2.

Kui kasutate valemis funktsiooni, peab igale algussulule vastama lõpusulg, et valem töötaks õigesti, seega veenduge, et kõik sulud oleksid lisatud paarikaupa. Valem =IF(B5<0);"Ei kehti";B5*1,05) näiteks ei tööta, kuna selles on kaks lõpusulgu, aga ainult üks algussulg. Õige valem oleks selline: =IF(B5<0;"Ei kehti";B5*1,05).

Exceli funktsioonid sisaldavad argumente – väärtusi, mis tuleb funktsiooni toimimiseks sisestama. Ainult mõni funktsioon (nt PI või TODAY) ei kasuta argumente. Kontrollige valemi süntaksit, mis kuvatakse, kui hakkate funktsiooni tippima, ja veenduge, et funktsioon sisaldaks kõiki nõutavaid argumente.

Funktsioon UPPER näiteks aktsepteerib argumendina ainult ühte tekstistringi või lahtriviidet: =UPPER("tere") või =UPPER(C2)

Märkus. : Valemi tippimisel kuvatakse valemi all ujuv funktsiooniviidete tööriistariba funktsiooni argumentide loendiga.

Funktsiooniviidete tööriistariba kuvatõmmis
Funktsiooniviidete tööriistariba

Samuti pange tähele, et mõni funktsioon (nt SUM) nõuab üksnes arvargumente, teised aga (nt REPLACE) nõuavad vähemalt ühe argumendi jaoks tekstväärtust. Vale andmetüübi kasutamisel võivad funktsioonid tagastada ootamatuid tulemeid või kuvada veaväärtuse #VALUE!.

Kui teil on vaja kiiresti mõne funktsiooni süntaksit järele vaadata, leiate vastava loendi teemast Exceli funktsioonid (kategooriate kaupa).

Ärge sisestage valemitesse dollarimärkidega ($) vormindatud arve ega tühikutega ( ) arve, kuna dollarimärgid tähistavad absoluutviiteid ja tühikuid võib rakendus tõlgendada argumentide eraldajana. Arvu $1 000 asemel sisestage valemisse lihtsalt 1000.

Kui kasutate argumentides vormindatud arve, saate arvutustes ootamatuid tulemeid või kuvatakse veaväärtus #NUM!. Kui sisestate näiteks arvu -2134 absoluutväärtuse leidmiseks valemi =ABS(-2 134), kuvab Excel vea, kuna funktsioon ABS tunnistab ainult ühte argumenti.

Märkus. : Valemi tulemi saate komakohtade või valuutatähisega vormindada pärast vormindamata arvudega (konstantidega) valemi sisestamist. Üldiselt pole soovitatav valemitesse konstante panna, sest neid on värskendamise korral keeruline leida ning nende valesti tippimise tõenäosus on suurem. Mõistlikum on sisestada konstandid eraldi lahtritesse, kus nad paremini silma hakkavad, ning seega on neile ka hõlpsam viidata.

Valem ei pruugi tagastada oodatud tulemit, kui lahtri andmetüüpi ei saa arvutustes kasutada. Kui sisestate näiteks lihtsa valemi =2+3 lahtrisse, mis on vormindatud tekstina, ei saa Excel sisestatud andmeid arvutada. Lahtris kuvatakse üksnes valem =2+3. Selle vea parandamiseks määrake järgmiste juhite abil lahtri andmetüübiks valiku Tekst asemel Üldine.

  1. Valige lahter.

  2. Klõpsake menüüd Avaleht ja välja Arvuvorming juures olevat noolenuppu (või vajutage klahvikombinatsiooni Ctrl + 1) ning valige tüüp Üldine.

  3. Vajutage lahtris redigeerimisrežiimi aktiveerimiseks klahvi F2 ja seejärel vajutage valemi kinnitamiseks sisestusklahvi (Enter).

Kui aga sisestate kuupäeva lahtrisse, mis kasutab andmetüüpi Arv, võidakse see kuupäeva asemel kuvada arvulise kuupäevaväärtusena. Selle arvu kuvamiseks kuupäevana valige galeriist Arvuvorming tüüp Kuupäev.

Üsna sageli kasutatakse valemis korrutustehte jaoks märki x, kuid Excel tunnistab korrutusmärgina üksnes tärni (*). Kui kasutate valemis konstante, kuvab Excel tõrketeate ja võib valemi teie eest parandada, asendades märgi x tärniga (*).

Teateboks, milles palutakse korrutamiseks asendada märk x tärniga (*)
Veateade korrutamiseks tärni (*) asemel märgi x kasutamise korral

Kui kasutate lahtriviiteid, tagastab Excel veaväärtuse #NAME?.

Viga #NAME?, mis ilmneb siis, kui lahtriviidetes kasutatakse korrutamiseks märgi * asemel märki x
Viga #NAME? tärni (*) asemel märgi x kasutamisel lahtriviidetes

Kui loote valemit, mis sisaldab teksti, pange tekst jutumärkidesse.

Valem ="Täna on " & TEXT(TODAY();"dddd, dd. mmmm") kombineerib teksti „Täna on ” funktsioonide TEXT ja TODAY tulemitega ning tagastab lahtris taolise teksti nagu Täna on esmaspäev, 30. mai.

Pange tähele, et valemis on tekstistringis "Täna on " enne lõpujutumärki tühik: see tühik jääb fraaside „Täna on” ja „esmaspäev, 30. mai” vahele. Kui jätate jutumärgid teksti ümber panemata, võidakse valemis kuvada viga #NAME?.

Valemis saate kombineerida ehk pesastada kuni 64 tasemel funktsioone.

Näiteks valemis =IF(SQRT(PI())<2;"Vähem kui kaks!";"Rohkem kui kaks!") on 3 funktsioonitaset: funktsioon PI on pesastatud funktsiooni SQRT, mis omakorda on pesastatud funktsiooni IF.

Kui tipite mõnel teisel töölehel asuvate väärtuste või lahtrite viite ja kui selle töölehe nimi sisaldab mittetähestikulisi märke (nt tühikut), pange nimi ülakomade (') vahele.

Kui soovite näiteks tagastada väärtuse, mis asub teie töövihiku lehe Kvartaliandmed lahtris D3, tippige järgmine valem: ='Kvartaliandmed'!D3. Kui töölehenime ümber ülakomasid pole, kuvatakse valemi lahtris viga #NAME?.

Samuti võite teisel lehel olevaid väärtusi või lahtreid klõpsata, et need valemisse lisada. Sel juhul paneb Excel automaatselt töölehenimede ümber ülakomad.

Kui tipite mõnes teises töövihikus asuvate väärtuste või lahtrite viite, pange töövihiku nimi nurksulgudesse ([]) ja lisage selle järele soovitud väärtusi või lahtreid sisaldava töölehe nimi.

Kui soovite näiteks viidata parajasti Excelis avatud töövihiku Kv2 tegevus lehel Müük olevatele lahtritele A1 kuni A8, tippige järgmine valem: =[Kv2 tegevus.xlsx]Müük!A1:A8. Kui nurksulge ei kasutata, kuvatakse valemi lahtris viga #REF!.

Kui töövihik pole Excelis avatud, tippige täielik failitee.

Näiteks: (=ROWS'C:\Minu dokumendid\[Kv2 tegevus.xlsx]Müük'!A1:A8).

Märkus. : Kui täielik tee sisaldab tühikuid, peate tee ümbritsema ülakomadega (tee alguses ja pärast töölehe nime, enne hüüumärki).

Näpunäide. : Lihtsaim viis teise töövihiku tee toomiseks on avada teine töövihik, seejärel tippida algsesse töövihikusse märk =, siis kasutada teisele töövihikule üleminekuks klahvikombinatsiooni Alt + Tab ning valida sealt soovitud lahter. Sulgege seejärel lähtetöövihik. Teie valem värskendub automaatselt ning kuvab täieliku failitee ja lehe nime koos nõutava süntaksiga. Saate tee ka kopeerida ja kleepida, et see alati käepärast oleks.

Lahtri sisu jagamine teise lahtriga, mille väärtus on null (0) või kus polegi väärtust, annab tulemiks veaväärtuse #DIV/0!.

Selle vea vältimiseks saate seda kohe lahendada proovida ja kontrollida nimetaja olemasolu.

=IF(B1,A1/B1,0)

Süntaks ütleb, et KUI (B1 on olemas, jaga A1 B1-ga, muul juhul tagasta 0)

Enne kustutamist kontrollige alati, kas mõni valem viitab kustutatavas lahtris, vahemikus, määratletud nimes, töölehel või -vihikus olevatele andmetele. Sel juhul saate enne viidatud andmete eemaldamist asendada vastavad valemid nende tulemitega.

Kui te ei saa valemeid nende tulemitega asendada, lugege järgmist teavet vigade ja võimalike lahenduste kohta.

  • Kui valem viitab kustutatud või teiste andmetega asendatud lahtritele ja tagastab veaväärtuse#REF!, valige viga #REF! sisaldav lahter. Valige valemiribal väärtus #REF! ja kustutage see. Seejärel sisestage valemi vahemik uuesti.

  • Kui määratletud nimi on puudu ja sellest nimest sõltuv valem tagastab veaväärtuse #NAME?, määratlege soovitud vahemikule viitamiseks uus nimi või muutke valemit nii, et see viitaks otse lahtrivahemikule (nt A2:D8).

  • Kui tööleht on puudu ja sellest sõltuv valem tagastab veaväärtuse #REF! ei saa seda viga kahjuks parandada – kustutatud töölehe andmeid ei saa asendada.

  • Puuduva töövihiku korral jääb sellele viitav valem samaks kuni valemi värskendamiseni.

    Näiteks juhul, kui valem on =[Vihik1.xlsx]Leht1'!A1 ja üksust Vihik1.xlsx pole enam, on selles töövihikus viidatud väärtused endiselt saadaval. Kui te aga sellele töövihikule viitavat valemit redigeerite ja valemi salvestate, kuvab Excel dialoogiboksi Väärtuste värskendamine, et sisestaksite failinime. Klõpsake nuppu Loobu ja seejärel veenduge, et need andmed kaotsi ei läheks, asendades puudvale töövihikule viitvad valemid nende tulemitega.

Vahel võib teil lahtrisisu kopeerimisel tekkida vajadus kleepida ainult väärtus, mitte selle aluseks olev ja valemiribal kuvatav valem.

Oletame näiteks, et soovite kopeerida valemi tulemiks oleva väärtuse mõnel teisel töölehel asuvasse lahtrisse. Või soovite kustutada valemis kasutatud väärtused pärast seda, kui olete tulemiks saadud väärtuse kopeerinud mõnda teise töölehelahtrisse. Mõlemal juhul kuvatakse sihtlahtris sobimatu lahtriviite viga (#REF!), sest valemis kasutatud väärtusi sisaldavatele lahtritele ei saa enam viidata.

Selle vea vältimiseks võite sihtlahtritesse kleepida üksnes valemite tulemuseks olevad väärtused ilma valemita.

  1. Valige töölehel lahtrid, milles sisalduvaid valemi põhjal arvutatud väärtusi soovite kopeerida.

  2. Klõpsake menüü Avaleht jaotises Lõikelaud nuppu Kopeeri Viiktekst 1 .

    Exceli lindi pilt

    Kiirklahvid. Vajutage klahvikombinatsiooni CTRL + C.

  3. Valige kleepimisala ülemine vasakpoolne lahter.

    Näpunäide. : Valiku teisaldamiseks või kopeerimiseks teisele töölehele või teise töövihikusse klõpsake teise töölehe sakki või aktiveerige teine töövihik ja valige kleepimisala ülemine vasakpoolne lahter.

  4. Klõpsake menüü Avaleht jaotises Lõikelaud nuppu Kleebi Viiktekst 1 ja seejärel käsku Kleebi väärtused, või vajutage Windowsi arvutis klahve Alt > E > S > V > Enter või Mac-arvutis Option > Command > V > V > Enter.

Mõistmaks, kuidas keerukas või pesastatud valem lõpptulemi arvutab, saate selle valemi väärtustada.

  1. Valige väärtustatav valem.

  2. Valige Valemid > Arvuta valemid.

    Valemiauditi jaotis valemimenüüs

  3. Allakriipsutatud viite väärtuse uurimiseks klõpsake nuppu Väärtusta. Väärtustamise tulem kuvatakse kursiivis.

    Valemi väärtustamise dialoogiboks

  4. Kui allakriipsutatud valemiosa viitab mõnele teisele valemile, klõpsake nuppu Samm sisse, et kuvada dialoogiboksis Väärtustamine teine valem. Eelmise lahtri ja valemi juurde naasmiseks klõpsake nuppu Samm välja.

    Nupp Samm sisse pole saadaval, kui valemis kuvatakse viide teist korda või kui valem viitab teises töövihikus olevale lahtrile.

  5. Jätkake, kuni kõik valemi osad on arvutatud.

    Valemite arvutamise tööriist ei pruugi teile teada anda, miks valem on vigane, kuid võib aidata teil välja selgitada, kus viga asub. See võib olla väga käepärane tööriist suuremates valemites, kus muidu oleks raske probleemi üles leida.

    Märkmed : 

    • Mõnda funktsiooni IF ja CHOOSE osa ei saa arvutada ning dialoogiboksis Väärtustamine võidakse kuvada veaväärtus #N/A.

    • Tühjad viited kuvatakse dialoogiboksis Väärtustamine kui nullväärtused (0).

    • Osa funktsioone arvutatakse uuesti iga kord, kui tööleht muutub. Nende funktsioonide puhul (sh funktsioonid RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY ja RANDBETWEEN) võivad dialoogiboksis Valemi väärtustamine kuvatavad tulemid erineda töölehe lahtris olevatest tegelikest tulemitest.

Kas vajate rohkem abi?

Võite oma küsimuse alati esitada mõnele Exceli tehnikakogukonna eksperdile, otsida abi vastustefoorumist või soovitada mõnd uut funktsiooni või täiustust Exceli User Voice’i lehel.

Vt ka

Exceli valemite ülevaade

Valemites vigade leidmine

Exceli funktsioonid (tähestikuliselt)

Exceli funktsioonid (kategooriate kaupa)

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×