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.

Jūs tiksit atgriezts šūnā ar kļūdaino formulu, kas būs rediģēšanas režīmā, un Excel izcels vietā, kur tas ir radušās problēmas. Ja jūs joprojām nezināt, kā rīkoties no turienes un vēlaties sākt no jauna, vēlreiz nospiediet taustiņu ESC vai noklikšķiniet uz pogas Atcelt formulu joslā, kas tiks jūs izejat no rediģēšanas režīmā.

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 met jaukšana dažādus (#) kļūdas, piemēram, #VALUE!, #REF!, #NUM, # n/a, #DIV/0!, #NAME?, un #NULL!, lai norādītu kaut ko formulā nedarbojas pa labi. Piemēram, #VALUE! kļūdu izraisa nepareiza formatējumu vai neatbalstīta datu tipu argumenti. Vai, redzēsit #REF! kļūdu, ja formula atsaucas uz šūnām, kurās esat izdzēsis vai aizstāt ar citiem datiem. Problēmu novēršanas norādījumi atšķirsies katras kļūdas.

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:

Katru reizi, kad atverat izklājlapu, kurā ir formulas ar atsaucēm uz citām izklājlapas vērtības jums tiks prasīts atjaunināt atsauces vai atstāšana esošajā vietā kā-ir.

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.: Tastatūras īsinājumtaustiņš varat arī izmantot taustiņu kombināciju Ctrl + ' (taustiņš virs taustiņu Tab). Kad tas ir izdarīts, kolonnu tiks automātiski paplašināt, lai parādītu formulas, bet neuztraucieties, kad jūs pārslēgtos atpakaļ uz normālskatu kolonnu mainīs izmēru.

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

  • Ja jums ir liels diapazons kolonnas šūnās, kas formatēti kā teksts un pēc tam atlasiet diapazonu, kuru izvēle skaitļu formāta lietošana un dodieties uz datu > teksta kolonnas > pabeigt. Tas tiks lietotu formātu visām atlasītajām šūnām.

    Attēls, kurā redzams dialoglodziņš Dati > Teksts par kolonnām

Ja formula nevar aprēķināt, ir jāpaņem, ja programmā Excel ir iespējota automātisko aprēķinu. Formulas tiks nevar aprēķināt, ja ir iespējota manuālu aprēķinu. Veiciet tālāk norādītās darbības, lai pārbaudītu, vai nav tādu automātisko aprēķinu.

  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āiespējo iteratīva aprēķināšana.

Papildinformāciju par riņķveida atsaucēm skatiet rakstā Riņķveida atsauces atrašana un laboš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
Funkciju atsauces rīkjosla

Arī dažas funkcijas, piemēram, SUM, jāievada skaitliski argumenti tikai, kamēr citām funkcijām, piemēram, aizstāšanaun pieprasīt teksta vērtības ir vismaz viens savus argumentus. Ja izmantojat nepareizs datu tips, funkcijas var atgriezt neparedzētus rezultātus vai Rādīt #VALUE! kļūdas.

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.

Formatēta skaitļu izmantošana argumenti, iegūsit neparedzēti aprēķinu rezultātus, taču var tikt parādīti arī #NUM! kļūdas. Piemēram, ievadot formulas =ABS(-2,134) , lai atrastu-2134 absolūtā vērtība, programma Excel parāda #NUM! kļūdu, jo funkcija ABS pieņem tikai viens arguments.

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). To parasti nav laba ideja likt konstanšu lietošana formulās, jo tie var būt grūti atrast, ja nepieciešams atjaunināt vēlāk, un tie ir vieglāk tiek ierakstīta nepareizi. Tas ir daudz labāk likt konstantes šūnās, ja tie ir atvēršana un viegli 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
Kļūdas ziņojums, ja reizināšanai ar konstantēm zvaigznītes * vietā tiek izmantota zīme x

Ja izmantojat šūnas atsauces, Excel atgriezīs kļūdu #NAME?.

Kļūda #NAME?, ja reizināšanai * vietā tiek izmantota zīme x ar šūnas atsauci
Kļūda #NAME?, ja * vietā tiek izmantota zīme x ar šūnas atsaucēm

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”. Ja teksts nav pēdiņās, formula var rādīt kļūdu #NAME?.

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 šūnas D3, kas atrodas jūsu darbgrāmatas darblapā ar nosaukumu Ceturkšņa dati, vērtību, ierakstiet: ='Ceturkšņa dati'!D3. Ja nosaukums nav pēdiņās, formulā tiek rādīta kļūda #NAME?.

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 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. Ja kvadrātiekavu nav, formula rāda kļūdu #REF!.

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.: Vienkāršākais veids, kā iegūt ceļš uz citu darbgrāmatu, ir atvērt darbgrāmatu, pēc tam no oriģinālās darbgrāmatas, tips = un pēc tam izmantojiet Taustiņu kombināciju Alt + Tab , lai novirzītu uz citu darbgrāmatu un atlasiet jebkuru šūnu uz lapu, kuru vēlaties. Aizveriet avota darbgrāmatu. Lai parādītu pilnu faila ceļu un lapas nosaukumu, kā arī nepieciešamās sintakses automātiski atjaunina savu formulu. Varat pat kopēt un ielīmēt ceļu un lietojiet ikreiz, kad tas ir nepieciešams.

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.

=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 attiecas uz šūnām, kas ir izdzēstas vai aizstātas ar citiem datiem, un atgriež kļūdu #REF!, atlasiet šūnu ar 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, formula, kas attiecas uz šo definēto nosaukumu, atgriež kļūdu #NAME?, 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ž kļūdu #REF!, 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. 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, 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 parādās mērķa šūnā, jo uz šūnām, kurās iekļautas 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 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ī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. 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.

    Novērtēt formulu rīks netiks nepieciešams pateikt, kāpēc jūsu formulai ir bojāta, bet tas var palīdzēt norāda, kur. Tas var būt ļoti noderīgs rīks lielāku formulās, kur pretējā gadījumā var būt 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.

×