Vigaste Exceli valemite ärahoidmine

Rohkem kui ühe argumendiga valemites kasutatakse argumentide eraldamiseks loendieraldajaid. Kasutatav eraldaja võib olenevalt opsüsteemi lokaadist ja Exceli sätetest varieeruda. Levinumad loendieraldajad on koma „,“ ja semikoolon „;“.

Valem katkeb, kui mõnes selle funktsioonis kasutatakse vale eraldajat.

Lisateavet leiate artiklist Valemivead, kui loendieraldaja pole õigesti määratud 

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 argumentides toetuseta andmetüüpide kasutamine. 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.

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

• Vea #NUM! parandamine

• Vea #VALUE! parandamine

• Vea #N/A parandamine

• Vea #DIV/0! parandamine

• Vea #REF! parandamine

• Vea #NAME? parandamine

• Vea #NULL! parandamine

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

Ülaltoodud dialoogiboksi kuvamine Excelis aitab tagada, 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.

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

• Veenduge, et Excel oleks häälestatud kuvama arvutustabelis valemeid. Selleks valige menüü Valemid ja seejärel valige jaotises Valemiaudit nupp Kuva valemid.

  : Olenevalt teie arvutis valitud lokaadist ja klahvipaigutusest on võimalik, et saate kasutada ka kiirklahvi 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 toodud juhise 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; seejärel valige Andmed > Tekst veergudesse > Valmis. Sel juhul rakendatakse soovitud vorming kõigile valitud lahtritele.

  

Kui valemi tulemit ei arvutata, peate kontrollima, kas Excelis on automaatne arvutamine lubatud. Kui lubatud on käsitsi arvutamine, siis valemeid ei arvutata. Automaatse arvutamise kontrollimiseks tehke järgmist.

1. Valige menüüs Fail nupp Suvandid ja seejärel kategooria Valemid.

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

   

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

Ringviide tekib 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 sätte Ringviite eemaldamine või lubamine.

Ringviidete kohta lisateabe saamiseks lugege artiklit 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 näiteks SUM(A1:A10), kuvab Excel valemi tulemi asemel tekstistringi SUM(A1:A10). Kui tipite 11/2, kuvatakse kuupäev (nt 2-Nov või 02.11.2009), mitte ei jagata 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. 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, ent 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)

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ühikuid ( ), kuna dollarimärgid tähistavad absoluutviiteid ja tühikuid võib rakendus tõlgendada valesti. Arvu $1 000 asemel sisestage valemisse lihtsalt 1000.

Kui kasutate argumentides vormindatud arve, saate arvutustes ootamatuid tulemusi või vea #NUM!. Kui sisestate näiteks arvu -2134 absoluutväärtuse leidmiseks valemi =ABS(-2 134), kuvab Excel veateate või veaväärtuse #NUM!, kuna funktsioon ABS aktsepteerib ainult ühte argumenti ja see näeb arve -2 ja 134 eraldi argumentidena.

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. Valige Avaleht; siis valige jaotise Arv või Arvuvorming laiendamiseks noolenupp (või vajutage klahvikombinatsiooni Ctrl + 1). Seejärel valige Ü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 (*).

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

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 väärtuse, mis asub teie töövihiku lehe Kvartaliandmed lahtris D3, tippige järgmine valem: ='Kvartaliandmed'!D3. Kui töölehe nime ümber pole jutumärke, kuvatakse valemis viga #NAME?..

Samuti võite valida mõnel teisel lehel olevad väärtused või lahtrid, 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. Ilma nurksulgudeta kuvab valem vea #REF!..

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

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

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. Saate kasutada järgmist: 

=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 lahtritele, mis on kustutatud või muude andmetega asendatud ja kui valem tagastab vea #REF!, valige lahter, kus on veaväärtus #REF!. #VALUE!. Valige valemiribal #REF! ja kustutage see. Seejärel sisestage valemi vahemik uuesti.

• Kui määratletud nimi puudub ja sellele nimele viitav 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. Valige Loobu ja seejärel veenduge, et need andmed ei läheks kaotsi: asendage puuduvale töövihikule viitavad valemid nende valemite tulemitega.

Vahel võib teil lahtrisisu kopeerimisel tekkida vajadus kleepida ainult väärtus, mitte selle aluseks olev valem, mille kuvamiskoht on valemiriba.

Oletagem 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 tulemuseks saadud väärtuse kopeerimist mõnda teise töölehelahtrisse. Mõlemad toimingud põhjustavad kehtetu lahtriviite vea (#REF!) kuvamise sihtlahtris, kuna valemis kasutatud väärtusi sisaldavatele lahtritele ei saa enam viidata.

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

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

2. Valige menüü Avaleht jaotises Lõikelaud nupp Kopeeri .

   

   Kiirklahv: CTRL+C

3. Valige kleepimisala ülemine vasakpoolne lahter.

   : Valiku teisaldamiseks või kopeerimiseks teisele töölehele või teise töövihikusse valige teise töölehe sakk või aktiveerige teine töövihik ja valige kleepimisala ülemine vasakpoolne lahter.

4. Valige menüü Avaleht jaotises Lõikelaud nupp Kleebi ; seejärel valige käsk Kleebi väärtused. Windowsi arvutis võite ka vajutada klahve Alt > E > S > V > Enter ning Mac-arvutis klahve 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.

   

3. Allakriipsutatud viite väärtuse uurimiseks valige nupp Väärtusta. Väärtustamise tulem kuvatakse kursiivis.

   

4. Kui allakriipsutatud valemiosa viitab mõnele teisele valemile, valige Samm sisse, et kuvada teine valem dialoogiboksis Väärtustamine. Eelmise lahtri ja valemi juurde naasmiseks valige 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õ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.