Kā nepieļaut kļūdainas formulas programmā Excel

Formulās ar vairākiem argumentiem tiek izmantots sarakstu atdalītājs, lai atdalītu argumentus. Izmantotais atdalītājs var atšķirties atkarībā no jūsu OS lokalizācijas un Excel iestatījumiem. Visbiežāk lietotie sarakstu atdalītāji ir komats "," un semikols ";".

Formulas darbība tiks pārtraukta, ja kādā no tās funkcijām tiks lietoti nepareizi atdalītāji.

Papildinformāciju skatiet: , ja veidojas formulu kļūdas, kad sarakstu atdalītājs nav pareizi iestatīts 

Excel parāda dažādas mārciņas tagu (#) kļūdas, piemēram, #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? un #NULL!, lai norādītu, ka jūsu formula nedarbojas pareizi. un #NULL!, lai norādītu, ka kāds elements jūsu formulā nedarbojas pareizi. kļūdu izraisa nepareizs formatējums vai neatbalstīti datu tipi argumentos. Vai arī redzēsit #REF! Vai arī redzēsit kļūdu #REF!, ja formula veido atsauci uz šūnām, kuras ir izdzēstas vai aizstātas ar citiem datiem. Problēmu novēršanas norādījumi katrai kļūdai ir atšķirīgi.

Skatiet kādu no tālāk norādītajām tēmām atbilstoši redzamajai mārciņas taga kļūdai:

• Kļūdas #NUM! labošana

• Kļūdas #VALUE! labošana

• Kļūdas #N/A labošana

• Kļūdas #DIV/0! labošana

• Kļūdas #REF! labošana

• Kļūdas #NAME? labošana

• Kļūdas #NULL! labošana

Ikreiz, kad atvērsit izklājlapu, kurā ir iekļautas formulas ar atsaucēm uz vērtībām citās izklājlapās, tiks parādīts uzaicinājums atjaunināt atsauces vai atstāt tās nemainītas.

Programma Excel parāda iepriekš minēto dialoglodziņu, lai nodrošinātu to, ka pašreizējā izklājlapā ietvertās formulas vienmēr norāda uz visjaunākajām vērtībām (gadījumā, ja atsauces vērtība ir mainīta). Varat izvēlēties atjaunināt atsauces vai izlaist, ja nevēlaties atjaunināt. Pat tad, ja izvēlēsities neatjaunināt atsauces, izklājlapā ietvertās saites vienmēr pēc savas izvēles varat atjaunināt manuāli.

Vienmēr varat atspējot arī dialoglodziņa rādīšanu sākumā. Lai to paveiktu, atveriet Fails > Opcijas > Papildu > Vispārīgs un notīriet izvēles rūtiņu Vaicāt par automātisko saišu atjaunināšanu.

Ja formulā netiek parādīta vērtība, veiciet tālāk norādītās darbības.

• Pārbaudiet, vai programmā Excel ir iestatīta formulu rādīšana jūsu izklājlapā. Lai to paveiktu, atlasiet cilni Formulas un grupā Formulu auditēšana atlasiet Rādīt formulas.

  Padoms.: Varat arī izmantot tastatūras īsinājumtaustiņu Ctrl + ` (taustiņš virs tabulācijas taustiņa Tab). Tā rīkojoties, kolonnas tiks automātiski paplašinātas, lai parādītu formulas, bet neuztraucieties, ja, pārslēdzot atpakaļ uz parasto skatu, kolonnu lielums tiek mainīts.

• Ja iepriekš minētā darbība joprojām neatrisina problēmu, iespējams, šūna ir formatēta kā teksts. Lai mainītu formātu, ar peles labo pogu noklikšķiniet uz šūnas un atlasiet Formatēt šūnas > Vispārīgs (vai nospiediet taustiņu kombināciju Ctrl+1), pēc tam nospiediet F2 > taustiņu Enter.

• Ja jums ir liels šūnu diapazons kolonnā, kas formatēts kā teksts, varat atlasīt diapazonu, lietot izvēlētu skaitļu formātu un pāriet uz Dati > Teksts kolonnā > Pabeigt. Šis formāts tiks lietots visām atlasītajām šūnām.

  

Ja formula neko neaprēķina, pārbaudiet, vai programmā Excel ir iespējots automātiskais aprēķins. Ja būs iespējots manuālais aprēķins, formulas neveiks aprēķināšanu. Lai veiktu automātisko aprēķinu, veiciet tālāk norādītās darbības.

1. Atlasiet cilni Fails, atlasiet Opcijas un pēc tam atlasiet kategoriju Formulas.

2. Sadaļā Aprēķina opcijas zem Darbgrāmatas aprēķins pārbaudiet, vai ir atlasīta automātiskā opcija.

   

Papildinformāciju par aprēķiniem skatiet rakstā Formulas pārrēķināšanas, iterācijas vai precizitātes maiņa.

Riņķveida atsauce tiek izveidota, ja formula veido atsauci uz šūnu, kurā tā atrodas. Risinājums ir šāds: pārvietojiet formulu uz citu šūnu vai mainiet formulas sintaksi uz tādu, kas neveido riņķveida atsauces. Tomēr dažos scenārijos riņķveida atsauces var būt nepieciešamas, jo tās veido funkciju iterāciju — atkārtot, līdz tiek sasniegts konkrēts skaitlisks nosacījums. Šādā gadījumā ir jāiespējo Riņķveida atsauces noņemšana vai atļaušana.

Papildinformāciju par riņķveida atsaucēm skatiet sadaļā Riņķveida atsauces noņemšana vai atļaušana.

Ja jūsu ieraksts nesākas ar vienādības zīmi, tā nav formula, tāpēc netiks aprēķināta — šī ir bieži sastopama kļūda.

Kad ierakstāt SUM(A1:A10), Excel parāda teksta virkni SUM(A1:A10), nevis formulas rezultātu. Bez tam, ja ierakstīsit 11/2, Excel parādīs datumu, piemēram, 2-Nov vai 02.11.2009., nevis dalīts 11 ar 2.

Lai nepieļautu šādus neparedzētus rezultātus, vienmēr sāciet funkciju ar vienādības zīmi. Piemēram, ierakstiet: =SUM(A1:A10) un =11/2

Ja formulā izmantojat funkciju, tad, lai funkcija darbotos pareizi, katrām atverošajām iekavām ir jābūt aizverošajām iekavām. Pārliecinieties, vai visas iekavas ir lietotas pa pāriem. Piemēram, formula =IF(B5<0),"Nav derīgs",B5*1.05) nedarbosies, jo tajā ir divas aizverošās iekavas, bet tikai viena atverošā iekava. Pareizā formula izskatītos šādi: =IF(B5<0,"Nav derīgs",B5*1.05).

Excel funkcijām ir argumenti — vērtības, kas jānorāda, lai funkcija darbotos. Tikai dažās funkcijās (piemēram, PI vai TODAY) nav argumentu. Pārbaudiet formulas sintaksi, kas tiek parādīta, kad sākat rakstīt funkciju, lai nodrošinātu, ka ievadāt visus nepieciešamos argumentus.

Piemēram, funkcija UPPER kā argumentu pieņem tikai vienu teksta virkni vai šūnas atsauci: =UPPER("labdien") vai =UPPER(C2)

Turklāt dažām funkcijām, piemēram, SUM, ir nepieciešami tikai skaitliski argumenti, bet citām funkcijām, piemēram, REPLACE, ir nepieciešama teksta vērtība vismaz vienam tās argumentam. Ja izmantojat nepareizu datu tipu, funkcijas var atgriezt neparedzētus rezultātus vai parādīt kļūdu #VALUE!.

Ja vēlaties ātri aplūkot konkrētas funkcijas sintaksi, skatiet sarakstu Excel funkcijas (pēc kategorijas).

Neievadiet formulās skaitļus, kas formatēti ar dolāra zīmēm ($) vai decimāldaļu atdalītājus (,), jo dolāra zīmes norāda absolūtas atsauces, bet komati ir argumentu atdalītāji. Ievadiet formulā nevis 1,000 $, bet 1000.

Ja izmantojat formatētus skaitļus argumentos, tiks parādīti neparedzēti aprēķinu rezultāti, bet var tikt parādīta arī kļūda #NUM!. Piemēram, ja ievadāt formulu =ABS(-2,134), lai atrastu -2134 absolūto vērtību, programma Excel parāda kļūdu #NUM!, jo ABS funkcija pieņemt tikai vienu argumentu, un -2 un 134 tā uztver kā atsevišķus argumentus.

Formula var neatgriezt paredzētos rezultātus, ja aprēķinos nevar izmantot šūnas datu tipu. Piemēram, ja ievadāt vienkāršu formulu =2+3 šūnā, kas ir formatēta kā teksts, Excel nevar aprēķināt jūsu ievadītos datus. Šūnā ir redzams tikai =2+3. Lai šo problēmu novērstu, mainiet šūnas datu tipu no Teksts uz Vispārīgs, piemēram, šādi:

1. Atlasiet šūnu.

2. Atlasiet Sākums un atlasiet bultiņu, lai izvērstu skaitļu vai skaitļu formāta grupu (vai nospiediet taustiņu kombināciju Ctrl + 1). Pēc tam atlasiet Vispārīgi.

3. Nospiediet taustiņu F2, lai ieslēgtu šūnas rediģēšanas režīmu, un pēc tam nospiediet taustiņu Enter, lai akceptētu formulu.

Datums, ko ievadāt šūnā, kurā tiek izmantots datu tips Skaitlis, var tikt rādīts kā skaitlisks datums, nevis kā datums. Lai šo skaitli rādītu kā datumu, galerijā Skaitļu formāts izvēlieties formātu Datums.

Bieži kā reizināšanas operators formulā tiek izmantots x, bet Excel reizināšanai izmanto tikai zvaigznīti (*). Ja formulā izmantojat konstantes, Excel rāda kļūdas ziņojumu un var izlabot formulu jūsu vietā, aizstājot x ar zvaigznīti (*).

Ja izmantojat šūnas atsauces, Excel atgriezīs #NAME?. Ja norādītā pozīcija atrodas pirms lauka pirmā vienuma vai aiz lauka pēdējā vienuma, formula radīs kļūdu #REF!.

Ja izveidojat formulu, kurā ir teksts, ielieciet tekstu pēdiņās.

Piemēram, formulā ="Šodien ir " & TEXT(TODAY(),"dddd, mmmm dd") ir apvienots teksts “Šodien ir ” ar funkciju TEXT un TODAY rezultātiem, un tā šūnā atgriež tekstu, kas līdzīgs Šodien ir pirmdiena, 30. maijs.

Formulā "Šodien ir " pirms aizverošajām pēdiņām ir jābūt atstarpei, lai nodrošinātu brīvo vietu starp vārdiem Šodien ir” un “pirmdiena, 30. maijs”..

Vienā formulā var kombinēt (vai ligzdot) līdz 64 funkciju līmeņiem.

Piemēram, formulai =IF(SQRT(PI())<2,"Mazāk nekā divi!","Vairāk nekā divi!") ir 3 funkciju līmeņi: Funkcija PI ir ligzdota funkcijā SQRT, kas savukārt ir ligzdota funkcijā IF.

Kad rakstāt atsauci uz vērtībām vai šūnām citā darblapā un šīs lapas nosaukumā ir rakstzīme, kas nav alfabētā (piemēram, atstarpe), iekļaujiet nosaukumu vienpēdiņās (').

Piemēram, lai atgrieztu šūnas D3, kas atrodas jūsu darbgrāmatas darblapā ar nosaukumu Ceturkšņa dati, vērtību, ierakstiet: ='Ceturkšņa dati'!D3. Ja lapas nosaukums nav pēdiņās, formula parāda kļūdu #NAME?.

Varat arī atlasīt vērtības vai šūnas citā darblapā, lai uz tām atsauktos savā formulā. Excel pēc tam automātiski pievieno pēdiņas lapu nosaukumiem.

Kad rakstāt atsauci uz vērtībām vai šūnām citā darbgrāmatā, iekļaujiet darbgrāmatas nosaukumu, kas ietverts kvadrātiekavās ([]), kam seko tās darblapas nosaukums, kurā ir vērtības vai šūnas.

Piemēram, lai atsauktos uz šūnām A1 līdz A8 lapā Pārdošana darbgrāmatā 2. ceturkšņa darbība, kas ir atvērta programmā Excel, ierakstiet: =[2. ceturkšņa darbība.xlsx]Pārdošana!A1:A8. Bez kvadrātiekavām formulā tiek parādīta #REF! kļūda.

Ja darbgrāmata nav atvērta programmā Excel, ierakstiet pilnu ceļu uz failu.

Piemēram, =ROWS('C:\Mani dokumenti\[2. ceturkšņa darbība.xlsx]Pārdošana'!A1:A8).

Dalot šūnu ar citu šūnu, kurā ir nulle (0) vai nav vērtības, tiek parādīta kļūda #DIV/0!. .

Lai novērstu šo kļūdu, pievērsieties tai tieši un pārbaudiet, vai pastāv saucējs. Varat izmantot: 

=IF(B1,A1/B1,0)

Tiek rādīts IF(B1 pastāv, pēc tam dalīt A1 ar B1, pretējā gadījumā atgriezt 0).

Vienmēr pārbaudiet, vai jums ir formulas, kurās ir atsauces uz datiem šūnās, diapazonos, definētajos nosaukumos, darblapās vai darbgrāmatās, pirms kaut ko dzēšat. Pēc tam varat šīs formulas aizstāt ar to rezultātiem, pirms noņemat datus, uz kuriem ir atsauces.

Ja formulas nevarat aizstāt ar to rezultātiem, pārskatiet šo informāciju par kļūdām un iespējamajiem risinājumiem:

• Ja formula atsaucas uz šūnām, kas ir izdzēstas vai aizstātas ar citiem datiem, un ja tā atgriež #REF! kļūdu,atlasiet šūnu, kurā ir #REF! Ja norādītā pozīcija atrodas pirms lauka pirmā vienuma vai aiz lauka pēdējā vienuma, formula radīs kļūdu #REF!. Formulu joslā atlasiet #REF! un izdzēsiet to. Pēc tam vēlreiz ievadiet formulas diapazonu.

• Ja trūkst definēta nosaukuma, un formula, kas attiecas uz šo nosaukumu, atgriež #NAME? kļūdu, definējiet jaunu nosaukumu, kas atsaucas uz vajadzīgo diapazonu, vai mainiet formulu, veidojot atsauci tieši uz šūnu diapazonu (piemēram, A2:D8).

• Ja trūkst darblapas un formula, kas uz to attiecas, atgriež #REF! kļūdu, to nevar novērst, diemžēl izdzēstu darblapu nevar atkopt.

• Ja trūkst darbgrāmatas, formula, kas uz to attiecas, paliek neskarta, līdz formulu atjaunināt.

  Piemēram, ja jūsu formula ir =[Grāmata1.xlsx]Lapa1'!A1 un jums vairs nav faila Grāmata1.xlsx, šajā darbgrāmatā izmantotās vērtības paliek pieejamas. Tomēr, ja rediģējat un saglabājat formulu, kas attiecas uz šo darbgrāmatu, Excel rāda dialoglodziņu Vērtību atjaunināšana un lūdz ievadīt faila nosaukumu. Atlasiet Atcelt un pēc tam pārliecinieties, vai šie dati nav zaudēti, aizstājot formulas, kas attiecas uz trūkstošo darbgrāmatu, ar formulas rezultātiem.

Dažreiz, kopējot šūnas saturu, vēlaties ielīmēt tikai vērtību, nevis formulu, kas attēlota formulu josla.

Piemēram, vēlaties kopēt formulas rezultāta vērtību šūnā, kas atrodas citā darblapā. Vai, iespējams, vēlaties izdzēst vērtības, kuras izmantojāt formulā pēc rezultāta vērtības kopēšanas darblapas šūnā. Abas šīs darbības izraisa nederīgas šūnas atsauces kļūdu (#REF!), kas jāparāda mērķa šūnā, jo uz šūnām, kurās ir formulā izmantotās vērtības, vairs nevar norādīt atsauci.

Varat izvairīties no šīs kļūdas, ielīmējot formulu rezultātu vērtības mērķa šūnās bez formulas.

1. Darblapā atlasiet šūnas, kurās iekļautas tās formulas rezultātu vērtības, kuras vēlaties kopēt.

2. Cilnes Sākums grupā Starpliktuve atlasiet Kopēt .

   

   Īsinājumtaustiņš: nospiediet taustiņu kombināciju CTRL+C.

3. Atlasiet ielīmēšanas apgabals kreiso augšējo šūnu.

   Padoms.: Lai pārvietotu vai kopētu atlasi citā darblapā vai darbgrāmatā, atlasiet citu darblapas cilni vai pārslēdzieties uz citu darbgrāmatu un atlasiet ielīmēšanas apgabala kreiso augšējo šūnu.

4. Cilnes Sākums grupā Starpliktuve atlasiet Ielīmēt un pēc tam atlasiet Ielīmēt vērtības. Varat arī nospiest Alt > E > S > V > Enter, ja izmantojat Windows, vai Option > Command > V > V > Enter (Opcija > Komanda > V > V > Enter), ja izmantojat Mac.

Lai saprastu, kā sarežģīta vai ligzdota formula aprēķina gala rezultātu, varat formulu novērtēt.

1. Atlasiet formulu, kuru vēlaties novērtēt.

2. Atlasiet Formulas > Novērtēt formulu.

   

3. Atlasiet Novērtēt, lai pārbaudītu pasvītrotās atsauces vērtību. Novērtēšanas rezultāts tiek parādīts slīprakstā.

   

4. Ja formulas pasvītrotā daļa ir atsauce uz citu formulu, atlasiet Ieiet, lai otra formula tiktu rādīta lodziņā Novērtējums. Atlasiet Iziet, lai pārietu atpakaļ uz iepriekšējo šūnu un formulu.

   Poga Ieiet nav pieejama, kad atsauce formulā tiek parādīta otrreiz vai ja formula attiecas uz šūnu citā darbgrāmatā.

5. Turpiniet, līdz katra formulas daļa ir novērtēta.

   Formulas novērtēšanas rīks, iespējams, nenorādīs, kāpēc formula ir bojāta, taču var norādīt, kurā vietā tā ir bojāta. Tas var būt ļoti noderīgs rīks lielu formulu gadījumā, kur ir grūti atrast problēmu.

   Piezīmes.: 

   • Dažas funkciju IF un CHOOSE daļas netiks novērtētas, un lodziņā Novērtējums var tikt parādīta kļūda #N/A.

   • Tukšas atsauces tiek rādītas kā nulles vērtības (0) lodziņā Novērtējums.

   • Dažas funkcijas tiek pārrēķinātas ikreiz, kad darblapā tiek veiktas izmaiņas. Šīs funkcijas, tostarp RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY un RANDBETWEEN, var likt dialoglodziņā Novērtēt formulu rādīt rezultātus, kas atšķiras no faktiskajiem rezultātiem darblapas šūnā.