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 soovite edasi liikuda, leiate järgmisest kontrollnimekirjast tõrkeotsingu juhised, mis aitavad teil aru saada, mis võib olla valesti läinud.

Excel viskab mitmesuguseid naelu (#) tõrked (nt #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? ja #NULL!), et valemis ei töötaks midagi õigesti. Näiteks #VALUE! tõrke põhjuseks on argumendid vale vorming või mittetoetatavad andmetüübid. Või näed #REF! tõrge, kui valem viitab lahtritele, mis on kustutatud või asendatud muude andmetega. Tõrkeotsingu juhised on iga tõrke korral 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

Osutage järgmistele naela tõrkele vastavatele teemadele.

Iga kord, kui avate arvutustabeli, mis sisaldab valemeid, mis viitavad muudes arvutustabelites olevatele väärtustele, palutakse teil viited värskendada või jätta need samaks.

Katkenud viidete dialoogiboks Excelis

Excel kuvab dialoogiboksi ülalpool, et veenduda, et praeguse arvutustabeli valemid osutavad alati kõige uuemale väärtusele juhuks, kui kontrollväärtusest on muudetud. 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

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 kiirklahvi CTRL + ' (tabeldusklahvi (TAB) all). Kui seda teete, laiendatakse teie veerud teie valemite kuvamiseks automaatselt, kuid ärge muretsege, kui lülitate veergudesse tagasi tavalise vaate, jäävad teie veerud muutmata.

  • 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. Sellisel juhul tuleb teil lubada ringviite eemaldamine või lubamine

Lisateavet ringviite kohta leiate teemast ringviite eemaldamine või lubamine

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

Kui tipite midagi SUM (A1: A10), kuvab Excel valemi tulemi asemel tekstistringi SUM (A1: A10) . Alternatiivina, kui tipite 11/2, kuvatakse Excelis kuupäev (nt 2 – Nov või 11/02/2009), selle asemel et eraldada arvu 11 kahega.

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

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. Kui kasutate vale andmetüüpi, võivad funktsioonid tagastada ootamatud tulemid või kuvada tõrke #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 argumentidena vormindatud numbreid, kuvatakse ootamatud arvutuse tulemid, kuid võite näha ka #NUM! tõrget. Kui sisestate näiteks valemi = ABS (-2 134) , et leida väärtus-2134, kuvatakse Excelis #NUM! tõrge, sest funktsioon ABS aktsepteerib ainult ühte argumenti, mis näeb välja-2 ja 134 eraldi argumentidena.

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. Probleemi lahendamiseks määrake lahtrile andmetüübi Tekst asemel vorming Üldine. Selleks tehke järgmist.

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

Kuid kui kasutate lahtriviidete, tagastab Excel #NAME? #VALUE!.

#NAME? tõrge, kui kasutate korrutamiseks x-i lahtriviidete asemel

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

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 töölehel lahtris D3 oleva väärtuse töövihikus kvartaalsed andmed, tippige: = ' kvartaalsed andmed '! D3. Kui jutumärgid pole ümber lehe nime, kuvatakse valemis #NAME? tõrge.

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 lahtritele a1 kuni A8, mis asub Excelis avatud Q2 operatsioonide töövihikus, tippige järgmine tekst: = [Q2 Operations. xlsx] Sales! A1: A8. Kui nurksulgudes pole, kuvatakse valemis #REF! tõrge.

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 eraldamine mõnele muule lahtrile, millel on null (0) või väärtuseta tulemit #DIV/0! tõrge.

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ääratud 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 lahtritele, mis on kustutatud või asendatud muude andmetega, tagastatakse tõrge #REF!Valige #REFga lahter. #VALUE!. Valige valemiribal nupp #REF! ja kustutage see. Seejärel sisestage valemi vahemik uuesti.

  • Kui määratletud nimi on puudu ja sellele nimele viitav valem annab tulemiks #NAME?Määratlege uus nimi, mis viitab soovitud vahemikule, või muutke valemit, et viidata 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.

Näiteks soovite võib-olla kopeerida tulemuseks saadud väärtuse mõnele muule töölehele asuvale lahtrile. Või soovite pärast tulemuseks saadud väärtuse kopeerimist töölehe mõnele muule lahtrile kustutada ka valemis kasutatud väärtused. Mõlemad toimingud põhjustavad lubamatut lahtriviite tõrketeadet (#REF!) kuvatakse sihtkoha lahtris, sest lahtreid, mis sisaldavad väärtusi, mida te valemis kasutasite, 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

    Kiirklahv: 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äärtusedvõi vajutage klahvikombinatsiooni alt > E > S > V > sisestage Windowsi jaoks või suvand > v > v > sisestage Mac-arvutisse.

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

    • 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

Valemivigade tuvastamine

Exceli funktsioonid (tähestikuliselt)

Exceli funktsioonid (kategooriate kaupa)

Märkus.:  See leht on tõlgitud automaatselt ning sellel võib leiduda grammatikavigu ja ebatäpsusi. Tahame, et sellest sisust oleks teile abi. Andke meile teada, kui see teave oli teile abiks. Soovi korral saate ingliskeelset artiklit lugeda siit.​

Täiendage Office'i kasutamise 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.

×