Kā nepieļaut kļūdainas formulas

Kā nepieļaut kļūdainas formulas

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 programma Excel nevar saprast, ko mēģināt darīt, tāpēc, iespējams, vēlēsities sākt no jauna.

Vispirms atlasiet Labi vai nospiediet taustiņu 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 atlasiet pogu Atcelt formulu joslā, kas atvērs rediģēšanas režīmu.

Formulu joslas pogas Atcelt attēls

Ja vēlaties doties tālāk, šajā kontrolsarakstā ir sniegtas problēmu novēršanas darbības, lai palīdzētu uzzināt, kas var būt noticis nepareizi.

Piezīme.: Ja izmantojatOffice tīmeklim, iespējams, neredzēsit tās pašas kļūdas, vai arī risinājumus var nelietot.

Programmā Excel tiek rādītas dažādas mārciņas (#) kļūdas, piemēram, #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? un #NULL!, lai norādītu, ka formulā nav pareiza darbība. un #NULL!, lai norādītu, ka kāds elements jūsu formulā nedarbojas pareizi. kļūdas cēlonis ir nepareizs formatējums vai neatbalstīti datu tipi. 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.

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 jebkuru no tālāk norādītajām tēmām, kas atbilst mārciņas 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, ka pašreizējās izklājlapas formulās vienmēr ir norādīts uz atjauninātu vērtību gadījumā, ja ir mainīta atsauces vērtība. 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 izdarītu, dodieties uz sadaļu failu > opcijas > papildu > vispārīgiun notīriet izvēles rūtiņu vaicāt par automātisko saišu atjaunināšanu .

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

Svarīgi!: Ja šī ir pirmā reize, kad strādājat ar bojātām saitēm formulās, ja jums ir nepieciešams atsvaidzinātājs, lai atrisinātu bojātas saites, vai arī nezināt, vai atjaunināt atsauces, skatiet rakstu kontrole par ārējo atsauču (saišu)atjaunināšanu.

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

  • Pārliecinieties, vai programmā Excel ir iestatīta rādīt formulas savā izklājlapā. Lai to izdarītu, 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). Kad tas ir paveikts, kolonnas tiks automātiski paplašinātas, lai tiktu rādītas formulas, taču Neraizējieties — ja pārslēgsit atpakaļ uz normālskatu, kolonnas mainīs lielumu.

  • Ja iepriekš norādītā darbība joprojām nenovērš problēmu, iespējams, ka šū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 kolonna ar lielu šūnu diapazonu, kas ir formatēti kā teksts, varat atlasīt diapazonu, lietot izvēlētu skaitļu formātu un pāriet uz datiem > tekstu uz kolonnu > 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 netiek aprēķināta, ir jāpārbauda, vai programmā Excel ir iespējots automātiskais aprēķins. Formulas netiek aprēķinātas, ja ir iespējots manuāls aprēķins. Veiciet šīs darbības, lai pārbaudītu automātisko aprēķinu.

  1. Atlasiet cilni Fails , atlasiet Opcijasun 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.

    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 mainīšana.

Riņķveida atsauce rodas, ja formulā ir atsauce uz šūnu, kurā tā atrodas. Labojums ir formulas pārvietošana uz citu šūnu vai formulas sintakses mainīšana uz kādu, kas neizmanto 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. Šādos gadījumos ir jāiespējo opcija Noņemt vai atļaut riņķveida atsauci.

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. Vai arī, ja rakstāt 11/2, programma Excel rāda datumu, piemēram, 2-Nov vai 11/02/2009, nevis dalot 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 pastāv 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.: Jūs redzēsit funkcijas argumentus, kas ir uzskaitīti peldošā funkciju atsauču rīkjoslā zem formulas, to rakstot.

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 skaitļus, kas formatēti, izmantojot dolāra zīmes ($) vai decimālos atdalītājus (,) formulās, jo dolāra zīmes norāda absolūtās atsauces un komatus. 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 pieņem tikai vienu argumentu, un tā redz-2 un 134 kā atsevišķus argumentus.

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). Parasti formulās nav ieteicams ievietot konstantes, jo tās var būt grūti atrast, ja vēlāk ir jāatjaunina, un tās ir vairāk tendētas uz rakstīšanu nepareizi. Tās ir daudz labākas, lai ievietotu konstantes šūnās, kur tās ir izkārtotas atvērtā un viegli atsaucē.

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 grupu skaitlis vai numerācijas formāts (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 (*).

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? Ja norādītā pozīcija atrodas pirms lauka pirmā vienuma vai aiz lauka pēdējā vienuma, formula radīs kļūdu #REF!.

#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āks par 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ī atlasīt vērtības vai šūnas citā lapā, lai tajās norādītu formulas. 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ā Operations.xlsx 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.: Vienkāršākais veids, kā iegūt ceļu uz citu darbgrāmatu, ir atvērt citu darbgrāmatu un pēc tam no sākotnējās darbgrāmatas, ierakstiet = un izmantojiet taustiņu kombināciju ALT + TAB , lai pārietu uz citu darbgrāmatu. Atlasiet jebkuru šūnu lapā, kuru vēlaties izmantot, un pēc tam slēdziet 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. Jūs varētu 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, kas norāda uz datiem šūnās, diapazonos, definētos nosaukumos, darblapās vai darbgrāmatās, pirms izdzēšat neko. 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 tiek atgriezta #REF! kļūdaAtlasiet šūnu ar #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ļū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. Atlasiet Atceltun pēc tam pārliecinieties, vai šie dati netiek zaudēti, aizstājot formulas, kas norāda 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.

Šo kļūdu var novērst, 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 Pogas attēls .

    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ā, atlasiet cita darblapa vai pārslēdzieties uz citu darbgrāmatu un pēc tam atlasiet ielīmēšanas apgabala augšējo kreiso šūnu.

  4. Cilnes Sākums grupā starpliktuve atlasiet Ielīmēt Poga Mapju saraksts navigācijas rūtī un pēc tam atlasiet Ielīmēt vērtībasvai nospiediet taustiņu kombināciju > E > S > V > enter for Windows vai option > komandu > v > v > ENTER Mac datorā.

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ētu formulu.

    Cilnes Formulas grupa Formulu auditēšana

  3. Lai pārbaudītu pasvītrotās atsauces vērtību, atlasiet novērtēt . 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, atlasiet solis šeit , lai parādītu otru formulu lodziņā novērtējums . Atlasiet Iziet, lai atgrieztos iepriekšējā šūnā un formulā.

    Poga darbība nav pieejama, ja formulā tiek parādīta otrā reize, vai formula attiecas uz šūnu citā darbgrāmatā.

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

    Līdzeklis formulas novērtēšana ne vienmēr norāda, kāpēc formula ir bojāta, taču tā var palīdzēt norādīt, kur. 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 katru reizi, kad tiek mainīta darblapa. Šī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 vērsties pie speciālista Excel Tech kopienā vai saņemt atbalstu Answers kopienā.

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)

Vai nepieciešama papildu palīdzība?

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 atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×