Valemivigade tuvastamine Excelis

Valemid võivad vahel tagastada veaväärtuse või anda ootamatuid tulemusi. Järgnevalt tutvustame mõningaid tööriistu, mille abil saate uurida nende probleemide põhjusi ja leida lahendusi.

Ülevaade lihtsate valemite sisestamisest

Valemid on võrrandid, mis arvutavad teie töölehe väärtusi. Valem algab võrdusmärgiga (=). Näiteks järgnev valem liidab 3 ja 1.

=3+1

Valem võib sisaldada ka funktsioone, viiteid, tehtemärke ja konstante.

Valemi osad

1. Funktsioonid: Excelis sisalduvad tehnikud valemid, mis teevad kindlaid arvutusi. Näiteks funktsioon PI() annab arvu π väärtuse: 3,142...

2. Lahtriviited: viitavad üksikutele lahtritele või lahtrivahemikele. Viide A2 annab lahtris A2 oleva väärtuse.

3. Konstandid: arvud või tekstväärtused, mis sisestatakse otse valemisse, nt väärtus 2.

4. Tehtemärgid: tehtemärk ^ (tsirkumfleks) tähendab arvu astendamist ja tehtemärk * (tärn) korrutamist. Kasutage väärtuste liitmiseks ja lahutamiseks tehtemärke + ja - ning jagamiseks tehtemärki /.

   Märkus.

   Mõned funktsioonid nõuavad argumente. Argumendid on väärtused, mida teatud funktsioonid kasutavad arvutamiseks. Kui see on nõutav, paigutatakse argumendid funktsiooni sulgude () vahele. Funktsioon PI ei nõua argumente, mistõttu on see tühi. Mõni funktsioon nõuab ühte või mitut argumenti ja võib jätta ruumi lisaargumentide jaoks. Olenevalt asukohasätetest peate argumentide eraldamiseks kasutama koma või semikoolonit (;).

Näiteks nõuab SUM-funktsioon ainult ühte argumenti, kuid mahutab kokku 255 argumenti.

=SUM(A1:A10) on ühe argumendi näide.

=SUM(A1:A10, C1:C10) on näide mitmest argumendist.

Levinud vigade parandamine valemi sisestamisel

Järgmises tabelis on kokku võetud levinumad vead, mida kasutajad valemite sisestamisel teevad, ja juhised nende vigade parandamiseks.

NB!	Lisateave
Alustage funktsioone alati võrdusmärgiga (=)	Kui võrdusmärk puudub, võidakse tipitud sisu kuvada teksti või kuupäevana. Kui tipite näiteks SUM(A1:A10), kuvab Excel tekstistringi SUM(A1:A10) ega arvuta seda. Kui tipite 11/2, kuvab Excel kuupäeva (nt 2-Nov, kui lahtri vorming on Üldine), mitte ei jaga 11 kahega.
Kontrollige, et kõik algus- ja lõpusulud oleksid paaris	Veenduge, et kõik sulud oleksid lisatud paarikaupa (algussulg ja lõpusulg). Kui kasutate valemis funktsiooni, on oluline, et iga sulg oleks õiges asendis, et funktsioon töötaks õigesti. Valem =IF(B5<0);"Ei kehti";B5*1,05) näiteks ei tööta, kuna seal on kaks lõpusulgu ja ainult üks avatud sulg, kus peaks olema ainult üks sulg. Valem peaks välja nägema selline: =IF(B5<0;"Ei kehti";B5*1,05).
Kasutage vahemiku tähistamiseks koolonit	Lahtrivahemikule viitamisel kasutage vahemiku esimese ja viimase lahtri viite eraldajana koolonit (:). Näiteks =SUM(A1:A5), mitte =SUM(A1 A5), mis tagastaks #NULL! Tõrge.
Sisestage kõik kohustuslikud argumendid	Mõnel funktsioonil on kohustuslikke argumente. Ärge sisestage liiga palju argumente.
Sisestage õiget tüüpi argumendid	Mõni funktsioon (nt SUM) nõuab arvargumente. Teised funktsioonid (nt REPLACE) seevastu nõuavad vähemalt ühe argumendina tekstväärtust. Kui kasutate argumendina valet tüüpi andmeid, võib Excel tagastada ootamatuid tulemeid või kuvada tõrke.
Ärge pesastage rohkem kui 64 funktsiooni	Funktsioonis ei saa sisestada ega pesastada üle 64 taseme.
Ümbritsege teised töölehenimed ülakomadega	Kui valem viitab muudel töölehtedel või -töövihikutes asuvatele väärtustele või lahtritele ja teise töövihiku või töölehe nimi sisaldab tühikuid või mittetähestikulisi märke, peate selle nime ümbritsema ülakomadega ( ' ), näiteks ='Kvartaliandmed'! D3 või ='123'! A1.
Kui viitate valemis töölehe nimele, pange selle lõppu hüüumärk (!)	Kui soovite näiteks tagastada väärtust, mis asub sama töövihiku lehe Kvartaliandmed lahtris D3, kasutage järgmist valemit: ='Kvartaliandmed'!D3.
Kaasake välistöövihikute tee	Veenduge, et kõik välisviited sisaldaksid töölehe nime ja teed. Viide töövihikule sisaldab töövihiku nime ja peab olema ümbritsetud nurksulgudega ([Workbookname.xlsx]). Samuti peab viide sisaldama töövihikus asuva töölehe nime. Kui töövihik, millele soovite viidata, pole Excelis avatud, saate valemisse siiski kaasata viite sellele töövihikule. Sel juhul tuleb sisestada täielik failitee, nagu järgmises näites: =ROWS('C:\Minu dokumendid\[Kv2 toimingud.xlsx]Müük'!A1:A8). See valem tagastab ridade arvu vahemikus, mis hõlmab teises töövihikus lahtreid A1 kuni A8 (8). Märkus. Kui täielik tee sisaldab tühikumärke, nagu eelmises näites, peate tee ümbritsema ülakomadega (tee alguses ja töölehe nime järel, enne hüüumärki).
Sisestage arvud ilma vorminguta	Kui sisestate valemisse arve, siis ärge vormindage neid. Kui soovite väärtusena sisestada näiteks 1000 €, sisestage valemisse 1000. Kui lisate arvudesse eraldajaid, näiteks tühikuid, võib Excel tagastada veaväärtuse. Kui soovite arve kuvada koos tuhandeliste eraldajatega või valuutatähistega, vormindage lahtrid pärast arvude sisestamist. Kui soovite näiteks liita lahtris A3 olevale väärtusele 3100 ja sisestate valemi =SUM(3;100;A3), liidab Excel arvud 3 ja 100 ning seejärel liidab selle summa lahtri A3 väärtusele, mitte ei lisa 3100 lahtrisse A3, mis oleks =SUM(3100;A3). Kui sisestate valemi =ABS(-2134), kuvab Excel veateate, kuna funktsioon ABS aktsepteerib ainult ühte argumenti: =ABS(-2134).

Valemites esinevate levinud vigade lahendamine

Valemitest vigade otsimiseks saate kasutusele võtta teatud reeglid. Nende reeglite kasutamine ei taga teie töölehe veatust, ent need suudavad üles leida suure osa levinumatest vigadest. Reegleid saab ükshaaval sisse ja välja lülitada.

Vigu saab tähistada ja parandada kahel viisil: ükshaaval (nagu õigekirjakontrolli kasutamisel) või kohe, kui olete vigased andmed töölehele sisestanud.

Tõrke lahendamiseks saate kasutada Exceli kuvatavaid suvandeid või võite viga eirata, kui valite Ignoreeri viga. Mõnes kindlas lahtris esineva vea ignoreerimisel ei kuvata selle lahtri viga enam edaspidiste veakontrollide käigus. Vajadusel võite kõik varem ignoreeritud vead lähtestada – sel juhul kuvatakse kõik vead uuesti.

Veakontrolli reeglite sisse- ja väljalülitamine

1. Exceli Windowsi versioonis avage Failisuvandite>>valemid või
   Mac-arvutis olevas Excelis valige Exceli menüü > Eelistused > Veakontroll.

2. Märkige jaotises Veakontroll ruut Luba taustal veakontroll. Leitud vead on tähistatud lahtri vasakus ülanurgas asuva kolmnurgaga.

   

3. Vea ilmnemiskohta tähistava kolmnurga värvi muutmiseks valige soovitud värv väljal Veaindikaatori värv.

4. Märkige või tühjendage jaotises Exceli kontrolli reeglid järgmiste reeglite märkeruudud.

   • Vea põhjustanud valemeid sisaldavad lahtrid: valem ei kasuta eeldatud süntaksit, argumente ega andmetüüpe. Veaväärtuste hulka kuuluvad #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, ja #VALUE!. Igal veaväärtusel on erinevad põhjused ja need lahendatakse erinevalt.

     Märkus.

     Kui sisestate veaväärtuse otse lahtrisse, talletatakse see veaväärtusena, kuid seda ei märgita veana. Kui aga sellele lahtrile viitab mõnes teises lahtris olev valem, tagastab valem sellest lahtrist veaväärtuse.

   • Tabelites olevas arvutatud veeruvalemis on ebakõla. Arvutatud veerg võib sisaldada üksikuid valemeid, mis erinevad põhiveeru valemist, mis loob erandi. Arvutatud veeru erandid luuakse siis, kui teete mõnda järgmistest:

     • tipite teistsugused valemist erinevad andmed ühte arvutatud veeru lahtrisse;
     • Tippige valem arvutatud veeru lahtrisse ja seejärel vajutage klahvikombinatsiooni Ctrl +Z või valige kiirpääsuribal nupp Võta tagasi.
     • tipite uue valemi arvutatud veergu, mis juba sisaldab ühte või mitut erandit;
     • kopeerite arvutatud veergu andmed, mis ei sobi arvutatud veeru valemiga; Kui kopeeritud andmed sisaldavad valemit, kirjutab see valem arvutatud veerus asuvad andmed üle.
     • teisaldate või kustutate mõnes muus töölehe piirkonnas lahtri, millele viitab üks arvutusliku veeru ridu.

   • Kahekohalise arvuna esitatud aastanumbreid sisaldavad lahtrid. Lahter sisaldab tekstikuupäeva, mida võib valemites kasutamisel valesti tõlgendada kui vale sajandit. Näiteks valemi =YEAR("1.1.31") kuupäev võib olla 1931 või 2031. Kasutage seda reeglit ebaselgete tekstikuupäevade kontrollimiseks.

   • Tekstina vormindatud arvud või arvud, millele eelneb ülakoma: lahter sisaldab tekstina talletatud arve. Tavaliselt juhtub seda muudest allikatest imporditud andmetega. Tekstina salvestatud arvud võivad sortimisel ootamatult tulemusi põhjustada, seega on parem need arvudeks teisendada. '=SUM(A1:A10) kuvatakse tekstina.

   • Valemid, mis ei ole regiooni teiste valemitega vastuolus: valem ei vasta selle lähedal asuvate muude valemite mustrile. Paljudel juhtudel erinevad teiste valemitega külgnevad valemid ainult kasutatavatest viidetest. Järgmises näites külgnevast valemist kuvab Excel lahtris D4 valemi =SUM(A10:C10) kõrval veateate, kuna külgnevad valemid inkrementitakse ühe rea võrra ja üks samm 8 rea võrra – Excel eeldab valemit =SUM(A4:C4).

     

     Kui valemis kasutatud viited pole külgnevate valemite viidetega kooskõlas, kuvab Excel veateate.

   • Valemid, mis ei sisalda piirkonnas asuvaid lahtreid. Valem ei pruugi automaatselt sisaldada viiteid andmetele, mille lisate algse andmevahemiku ja valemit sisaldava lahtri vahele. See reegel võrdleb valemis olevat viidet valemit sisaldava lahtriga külgneva tegeliku lahtrivahemikuga. Kui külgnevad lahtrid pole tühjad, vaid sisaldavad samuti väärtusi, kuvab Excel valemi kõrval veaväärtuse.
     Selle reegli rakendamisel lisab Excel valemi =SUM(D2:D4) kõrvale veateate, kuna lahtrid D5, D6 ja D7 külgnevad valemis viidatud lahtritega ja valemit sisaldava lahtriga (D8) ning need lahtrid sisaldavad andmeid, millele oleks tulnud valemis viidata.

     

   • Valemeid sisaldavad lukustamata lahtrid: valem pole kaitsmiseks lukustatud. Vaikimisi lukustatakse kõik töölehe lahtrid, nii et neid ei saa kaitstud töölehel muuta. See aitab vältida tahtmatuid vigu, näiteks valemite kogemata kustutamist või muutmist. See tõrge näitab, et lahter on seatud lukustamata, kuid leht pole kaitstud. Veenduge, et te ei soovi lahtrit lukustada.

   • Tühjadele lahtritele viitavad valemid: valem sisaldab viidet tühjale lahtrile. See võib põhjustada soovimatuid tulemeid, nagu kirjeldab järgmine näide.
     Oletagem, et soovite arvutada järgmises lahtriveerus leiduvate arvude keskmist. Kui kolmas lahter on tühi, ei kaasata seda arvutusse ja tulem on 22,75. Kui see lahter sisaldab aga väärtust 0, on tulemiks 18,2.

     

   • Tabelisse sisestatud andmed ei sobi: tabelis on valideerimistõrge. Lahtri valideerimissätete kontrollimiseks valige menüü >Andmeriistad jaotis >Andmete valideerimine.

Levinud valemivigade parandamine ükshaaval

1. Valige tööleht, mida soovite vigade osas kontrollida.

2. Kui tööleht on käsitsi arvutatud, vajutage uuestiarvutamiseks klahvi F9.
   Kui dialoogiboksi Veakontroll ei kuvata, valige Valemid>Valemiaudit>Veakontroll.

3. Kui olete varem vigu ignoreerinud, saate neid tõrkeid uuesti otsida. Selleks tehke järgmist. Selleks avage Failisuvandite>>valemid. Mac-arvutis olevas Excelis valige Exceli menüü > Eelistused > Veakontroll.
   Valige jaotises Veakontroll käsk Lähtesta ignoreeritud vead>OK.

   

   Märkus.

   Ignoreeritud vigade lähtestamisel lähtestatakse kõik aktiivses töövihikus kõigil lehtedel leiduvad vead.

   Näpunäide.

   Abiks võib olla see, kui paigutate dialoogiboksi Veakontroll otse valemiriba alla.

   

4. Valige üks dialoogiboksi paremas servas olevatest toimingunuppudest. Saadaolevad valikuvõimalused on erinevat tüüpi vigade puhul erinevad.

5. Valige Next (Edasi).

Levinud valemivigade parandamine ükshaaval

1. Valige lahtri kõrval Veakontrolli veakontroll ja seejärel valige soovitud suvand. Saadaolevad käsud erinevad igat tüüpi tõrgete puhul ja esimene kirje kirjeldab viga.
   Kui valite Ignoreeri viga, märgitakse viga iga järjestikuste kontrollide puhul ignoreerituks.

   

Veaväärtuse # parandamine

Kui valem ei saa tulemit õigesti hinnata, kuvab Excel veaväärtuse (nt #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, ja #VALUE!). Igal tõrketüübil on erinevad põhjused ja erinevad lahendused.

Järgmine tabel sisaldab artiklite linke, kust leiate täpsemat teavet nende vigade kohta, ja veateadete põhjuste lühikirjeldust.

Teema	Kirjeldus
Vea #### parandamine	Excel kuvab selle vea, kui veerg pole piisavalt lai kogu lahtrisisu kuvamiseks või lahter sisaldab negatiivset kuupäeva- või kellaajaväärtust. Nt valem, mis lahutab varasemast kuupäevast hilisema (nt valemi =15.06.2008-01.07.2008 tulemiks on negatiivne kuupäevaväärtus. Näpunäide. Proovige lahter automaatselt sobitada, topeltklõpsates veerupäiste vahel. Kui ### kuvatakse, kuna Excel ei saa kõiki märke kuvada, parandab see vea.
Vea #DIV/0! parandamine	Excel kuvab selle vea juhul, kui arvu proovitakse jagada nulliga (0) või lahtriga, mis ei sisalda väärtust. Näpunäide. Lisage järgmises näites veaohjur, mis on =IF(C2;B2/C2;0)
Vea #N/A parandamine	Excel kuvab selle vea juhul, kui funktsioonis või valemis pole väärtust saadaval. Kui kasutate sellist funktsiooni nagu VLOOKUP, siis kas otsitaval funktsioonil on otsinguvahemikus vaste? Enamasti mitte. Proovige veaväärtuse #N/A tõkestamiseks kasutada funktsiooni IFERROR. Antud juhul saaksite seda funktsiooni kasutada näiteks sellisel kujul: =IFERROR(VLOOKUP(D2;$D$6:$E$8,2;TRUE);0)
Vea #NAME? parandamine	See viga kuvatakse juhul, kui Excel ei tunne valemis olevat teksti. Näiteks võib vahemiku nimi või funktsiooni nimi olla valesti kirjutatud. Märkus. Kui kasutate funktsiooni, veenduge, et funktsiooni nimi oleks õigesti kirjutatud. Antud juhul on sõna SUM valesti kirjutatud. Eemaldage "e" ja Excel parandab selle.
Vea #NULL! parandamine	Excel kuvab selle vea juhul, kui määrate kahe mitteristuva ala ristumise. Ristumise tehtemärk on valemi viidetevaheline tühik. Märkus. Veenduge, et vahemikud oleksid õigesti eraldatud. Kuna alad C2:C3 ja E4:E6 ei lõiku, tagastatakse valemi =SUM(C2:C3 E4:E6) sisestamisel #NULL! #VALUE!. Kui lisate vahemike C ja E vahele koma, parandatakse viga =SUM(C2:C3,E4:E6)
Vea #NUM! parandamine	Excel kuvab selle vea juhul, kui valem või funktsioon sisaldab lubamatuid arvväärtusi. Kas kasutate itereerivat funktsiooni (nt IRR või RATE)? Sel juhul kuvatakse veaväärtus #NUM! on tõenäoliselt tingitud sellest, et funktsioon ei leia tulemit. Lahendustoimingud leiate spikriteemast.
Vea #REF! parandamine	Excel kuvab selle vea juhul, kui lahtriviide on sobimatu. Näiteks on võimalik, et olete kustutanud lahtreid, millele on teistes valemites viidatud, või olete kleepinud lahtrid, mille teisaldasite teiste valemite viidatud lahtrite peale. Kas kustutasite kogemata rea või veeru? Kustutasime valemis =SUM(A2;B2;C2) veeru B ning vaadake, mis juhtus. Kustutamise tagasivõtmiseks kasutage käsku Võta tagasi (Ctrl+Z), koostage valem uuesti või kasutage pidevat vahemikuviidet: =SUM(A2:C2), mida oleks veeru B kustutamisel automaatselt värskendatud.
Vea #VALUE! parandamine	Excel võib kuvada selle teate juhul, kui valem sisaldab eri andmetüüpe sisaldavaid lahtreid. Kas kasutate matemaatilisi tehtemärke (+; -; *; /; ^) erinevate andmetüüpidega? Kui see on nii, siis proovige hoopis funktsioone kasutada. Praegusel juhul lahendaks probleemi =SUM(F2:F5).

Valemi ja valemi tulemuste jälgimine jälgimisakna abil

Kui lahtrid pole töölehel nähtavad, saate neid lahtreid ja nende valemeid vaadata tööriistaribal Jälgimisaken. Jälgimisakna abil saate hõlpsalt kontrollida ja kinnitada suurte töölehtede valemiarvutusi. Jälgimisakent kasutades ei pea te pidevalt liikuma töölehe eri osade vahel.

Seda tööriistariba saab teisaldada või dokkida nagu mis tahes muud tööriistariba. Näiteks saab selle dokkida akna allserva. Tööriistariba jälgib järgmisi lahtriatribuute: 1) töövihik; 2) leht; 3) nimi (juhul, kui lahtri jaoks on määratud vastav nimega vahemik); 4) ´lahtriaadress; 5) väärtus; 6) valem.

Lahtrite lisamine jälgimisaknasse

1. Valige lahtrid, mille soovite jälgida.
   Valemitega töölehe kõigi lahtrite valimiseks valige Avaleht>Redigeerimine>käsk Otsi & Vali (või kasutage Mac-arvutis klahvikombinatsiooni Ctrl+G või Mac-arvutis Control+G)> ValigeErivalemid>.

   

2. Avage jaotis Valemite>valemiaudit> ja valige Jälgimisaken.

3. Valige Lisa jälgimine.

   

4. Veenduge, et olete valinud kõik lahtrid, mida soovite vaadata, ja valige Lisa.

   

5. Jälgimisakna veerulaiuse muutmiseks lohistage veeru paremat äärist päisest.

6. Et kuvada lahter, millele viitab tööriistariba Jälgimisaken aknas asuv kirje, topeltklõpsake kirjet.

   Märkus.

   Lahtrid, millel on välised viited muudele töövihikutele, kuvatakse tööriistaribal Jälgimisaken ainult siis, kui muud töövihikud on avatud.

Lahtrite eemaldamine jälgimisaknast

1. Kui tööriistariba Jälgimisaken ei kuvata, valige Valemite>valemiaudit> ja seejärel Jälgimisaken.

2. Valige lahtrid, mida soovite eemaldada.
   Mitme lahtri valimiseks vajutage juhtklahvi (CTRL) ja seejärel valige soovitud lahtrid.

3. Valige Delete Watch (Kustuta jälgimine).

   

Pesastatud valemi sammhaaval väärtustamine

Vahel on keeruline mõista, kuidas pesastatud valem arvutab lõpliku tulemi, kuna arvutuskäik hõlmab mitut vahepealset arvutust ja loogikatesti. Rakenduses Excel for Windows saate aga valemi arvutamise järjekorras hinnata pesastatud valemi erinevaid osi. Näiteks valemit =IF(AVERAGE(D2:D5)>50;SUM(E2:E5);0) on lihtsam mõista, kui näete järgmisi vahetulemeid:

Valemi väärtustamise dialoogiboks	Kirjeldus
=IF(AVERAGE(D2:D5)>50;SUM(E2:E5);0)	Algul kuvatakse pesastatud valem. Funktsioonid AVERAGE ja SUM on IF-funktsioonis pesastatud. Lahtrivahemik D2:D5 sisaldab väärtusi 55, 35, 45 ja 25, seega on funktsiooni AVERAGE(D2:D5) tulem 40.
=IF(40>50;SUM(E2:E5);0)	Lahtrivahemik D2:D5 sisaldab väärtusi 55, 35, 45 ja 25, seega on funktsiooni AVERAGE(D2:D5) tulem 40.
=IF(FALSE;SUM(E2:E5);0)	Kuna 40 pole suurem kui 50, on IF-funktsiooni esimese argumendi (argument loogikaavaldis) avaldis väär. IF-funktsioon tagastab kolmanda argumendi (argumendi väärtus_kui_väär) väärtuse. Funktsiooni SUM ei väärtustata, kuna see on funktsiooni IF teine argument (argument väärtus_kui_tõene) ja tagastatakse üksnes juhul, kui avaldis on tõene.

1. Valige Rakenduses Excel for Windows lahter, mida soovite hinnata. Korraga saab väärtustada ainult ühe lahtri.
2. Avage jaotis Valemid>Valemiaudit Väärtusta>valem.
3. Allakriipsutatud viite väärtuse uurimiseks valige nupp Väärtusta. Väärtustamise tulem kuvatakse kursiivis.
   Kui allakriipsutatud valemiosa viitab mõnele muule valemile, valige teise valemi kuvamiseks väljal Väärtustaminesuvand Samm sisse. Eelmise lahtri ja valemi juurde naasmiseks valige Samm välja.
   Nupp Samm sisse pole viiteks saadaval, kui valemis kuvatakse viide teist korda või kui valem viitab muus töövihikus olevale lahtrile.
4. Jätkake nupu Väärtusta valimist, kuni valemi iga osa on hinnatud.
5. Hindamise uuesti kuvamiseks valige Taaskäivita.
6. Hindamise lõpetamiseks valige Sule.

Kas vajate rohkem abi?

Võite alati küsida Exceli tehnikakogukonna eksperdilt või kogukonnafoorumites tuge.

Lisateave

Valemite ja lahtrite seoste kuvamine

Katkiste valemite ärahoidmine