Kā nepieļaut kļūdainas formulas

Kā nepieļaut kļūdainas formulas

Piezīme.: Mēs vēlamies sniegt jums visjaunāko palīdzības saturu jūsu valodā, cik vien ātri to varam. Šī lapa ir tulkota automatizēti, un tajā var būt gramatiskas kļūdas un neprecizitātes. Mūsu nolūks ir šo saturu padarīt jums noderīgu. Vai lapas lejasdaļā varat mums pavēstīt, vai informācija jums noderēja? Ērtai atsaucei šeit ir šis raksts angļu valodā.

Ja Excel nevar atrisināt formulu, ko mēģināt veidot, var tikt parādīts šāds kļūdas ziņojums:

Excel dialoglodziņa "Problēma ar šo formulu" attēls

Diemžēl tas nozīmē, ka Excel nesaprot, ko vēlaties izdarīt, tāpēc varat iziet un sākt no jauna.

Vispirms noklikšķiniet uz Labi vai nospiediet ESC, lai aizvērtu kļūdas ziņojumu.

Atgriezīsities šūnā ar bojāto formulu, kas būs rediģēšanas režīmā, un Excel iezīmēs vietu, kur radās problēma. Ja joprojām nezināt, ko darīt, un vēlaties sākt no jauna, vēlreiz nospiediet taustiņu ESC vai noklikšķiniet uz pogas Atcelt formulu joslā, un rediģēšanas režīms tiks aizvērts.

Formulu joslas pogas Atcelt attēls

Ja neesat pārliecināts, kā rīkoties šajā brīdī vai kāda palīdzība nepieciešama, varat meklēt līdzīgus jautājumus Excel kopienas forumā vai izlikt savu jautājumu.

Saite uz Excel kopienas forumu

Ja vēlaties doties tālāk, šajā kontrolsarakstā ir sniegtas problēmu novēršanas darbības, lai palīdzētu uzzināt, kādas ir iespējamās problēmas ar formulām.

Excel parāda dažādas atsauces tagu (#) kļūdas, piemēram, #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? un #NULL!, lai norādītu, ka kāds elements jūsu formulā nedarbojas pareizi. Piemēram, kļūdu #VALUE! izraisa nepareizs formatējums vai neatbalstīti datu tipi argumentos. 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.

Piezīme.: #### nav ar formulu saistīta kļūda. Tas tikai nozīmē, ka kolonna nav pietiekami plata, lai parādītu šūnas saturu. Vienkārši velciet kolonnu, lai to padarītu platāku, vai atveriet Sākums > Formatēt > Automātiski ietilpināt kolonnas platumu.

Attēls, kurā redzama komanda Sākums > Formāts > Automātiski ietilpināt kolonnas platumu

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

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.

Bojātu atsauču dialoglodziņš programmā Excel

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 noņemiet atlasi opcijai Vaicāt par automātisko saišu atjaunināšanu. Programmā Excel 2007 noklikšķiniet uz Office pogas > Excel opcijas. Office 2007 poga

Attēls, kurā redzama opcija Vaicāt par automātisko saišu atjaunināšanu

Svarīgi!: Ja darbu ar bojātām saitēm formulās veicat pirmoreiz, bojāto saišu atrisināšanai ir nepieciešams atsvaidzinātājs. Ja neesat pārliecināts, vai atsauces ir jāatjauno, izlasiet rakstu Kontrole ārējo atsauču (saišu) atjaunināšanas laikā.

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, noklikšķiniet uz cilnes Formulas un grupā Formulu auditēšana noklikšķiniet uz 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.

    Attēls, kurā redzams dialoglodziņš Dati > Teksts par kolonnā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 atrastu automātisko aprēķinu, veiciet tālāk norādītās darbības.

  1. Noklikšķiniet uz cilnes Fails, uz Opcijas un pēc tam noklikšķiniet uz kategorijas Formulas.

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

    Opciju Automātiskā un manuālā aprēķināšana attēls

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āatļauj riņķveida atsauces noņemšana vai atļaušana

Papildinformāciju par riņķveida atsaucēm skatiet rakstā 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. 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 vajadzīgas aizverošās iekavas, lai visas iekavas būtu daļa no atbilstoša pāra. 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)

Piezīme.: Rakstot formulu, jūs ievērosit, ka funkcijas argumenti ir uzskaitīti peldošajā funkciju atsauces rīkjoslā zem formulas.

Funkciju atsauces rīkjoslas ekrānuzņēmums

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 tiek izmantots nepareizs datu tips, funkcijas var atjaunot neparedzētus rezultātus vai Rādīt #VALUE! kļūdu.

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ājiem (,), 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 argumentos izmantojat formatētus skaitļus, tiek parādīti neparedzēti aprēķinu rezultāti, taču, iespējams, redzēsit arī #NUM! kļūdas ziņojumu. Piemēram, ja ievadāt formulu = ABS (-2 134) , lai atrastu absolūto vērtību-2134, Excel rāda #NUM! kļūda, jo funkcija Abs akceptē tikai vienu argumentu.

Piezīme.: Varat formulas rezultātu formatēt ar decimāldaļu atdalītājiem un valūtu simboliem pēc tam, kad esat ievadījis formulu, izmantojot neformatētus skaitļus (konstantes). Kopumā formulās nav ieteicams ievadīt konstantes, jo, ja vēlāk ir nepieciešams atjaunināt, tās var būt grūti atrast; tāpat tās bieži tiek ierakstītas kļūdaini. Daudz labāk ir ievadīt konstantes šūnās, kur tās ir labi redzamas un uz tām var viegli veidot atsauces.

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 to novērstu, nomainiet šūnas datu tipu no Teksts uz Vispārīgs šādi:

  1. Atlasiet šūnu.

  2. Noklikšķiniet uz Sākums > uz bultiņas blakus Skaitļu formāts (vai nospiediet taustiņu kombināciju Ctrl+1) un noklikšķiniet uz Vispārīgs.

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

Ziņojuma lodziņš, kurā tiek lūgts aizstāt zīmi x ar *, lai reizinātu

Tomēr, ja izmantojat šūnu atsauces, Excel atgriezīs #NAME? kļūdu.

#NAME? kļūda, izmantojot x ar šūnu atsaucēm, nevis * reizināšanai

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, formula =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 vērtību no šūnas D3 darblapā ar nosaukumu ceturkšņa dati darbgrāmatā, ierakstiet: = "ceturkšņa dati"! D3. Ja apkārt lapas nosaukumam nav pēdiņu, formula rāda #NAME? kļūdu.

Varat arī noklikšķināt uz vērtībām vai šūnām 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 izveidotu atsauci uz šūnām, kas atrodas no a1 līdz A8, pārdošanas lapā, kas tiek atvērta programmā Excel. A1: A8. Ja nav kvadrātiekavas, formula rāda #REF! kļūdu.

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

Piezīme.: Ja pilnajā ceļā ir atstarpes rakstzīmes, ceļš ir jāiekļauj vienpēdiņās (ceļa sākumā un starp darblapas nosaukumu un izsaukuma zīmi).

Padoms.: Vieglākais veids, kā iegūt ceļu uz citu darbgrāmatu, ir atvērt citu darbgrāmatu, pēc tam sākotnējā darbgrāmatā ierakstīt =, izmantot taustiņu kombināciju Alt+Tab, lai pārietu uz otru darbgrāmatu, un lapā atlasīt jebkuru vajadzīgo šūnu. Pēc tam aizveriet avota darbgrāmatu. Formula tiks automātiski atjaunināta, parādot pilnu faila ceļu un lapas nosaukumu kopā ar vajadzīgo sintaksi. Varat pat nokopēt un ielīmēt ceļu, lai izmantotu pēc nepieciešamības.

AtDalot šū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.

=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 atgriež #REF! kļūduAtlasiet šūnu ar #REF! kļūdu. 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ļūduDefinējiet jaunu nosaukumu, kas atsaucas uz vajadzīgo diapazonu, vai mainiet formulu, lai tā attiektos tieši uz šūnu diapazonu (piemēram, A2: D8).

  • Ja trūkst darblapas un formula, kas uz to attiecas, atgriež #REF! kļūda nav iespējams izlabot šo problēmu, diemžēl — izdzēsto 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. Noklikšķiniet uz 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, iespējams, vēlēsities kopēt formulas rezultējošo vērtību citā darblapā. Vai arī, iespējams, vēlēsities dzēst formulā izmantotās vērtības pēc iegūtās vērtības kopēšanas uz citu šūnu darblapā. Abas šīs darbības izraisa nederīgu šūnas atsauces kļūdu (#REF!) lai tiktu parādīta mērķa šūnā, jo šūnās, kurās ir formulā izmantotās vērtības, vairs nevar būt atsauces.

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 noklikšķiniet uz Kopēt Poga Mapju saraksts navigācijas rūtī .

    Excel lentes attēls

    Ī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ā, noklikšķiniet uz citas darblapas cilnes vai pārslēdzieties uz citu darbgrāmatu un atlasiet ielīmēšanas apgabala kreiso augšējo šūnu.

  4. Cilnes Sākums grupā Starpliktuve noklikšķiniet uz Ielīmēt Pogas attēls un pēc tam noklikšķiniet uz Ielīmēt vērtībasvai nospiediet taustiņu kombināciju ALT _GT_ E _GT_ S _GT_ V _GT_ ENTER for Windows vai option > v > v ENTER on a 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. Noklikšķiniet uz Formulas > Novērtēt formulu.

    Cilnes Formulas grupa Formulu auditēšana

  3. Noklikšķiniet uz 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ā.

    Formulas novērtēšanas dialoglodziņš

  4. Ja formulas pasvītrotā daļa ir atsauce uz citu formulu, noklikšķiniet uz Ieiet, lai otra formula tiktu rādīta lodziņā Novērtējums. Noklikšķiniet uz 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.

    • Funkcijas, kas 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ā.

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistiem Excel Tech kopienā, saņemt atbalstu Answers kopienā vai ieteikt jaunu līdzekli vai uzlabojumu vietnē Excel User Voice.

Skatiet arī

Pārskats par formulām programmā Excel

Kļūdu noteikšana formulās

Excel funkciju saraksts (pēc alfabēta)

Excel funkciju saraksts (pēc kategorijas)

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta aģentiem.

×