Formulių klaidų aptikimas

Toliau pateiktoje lentelėje apibendrinamos kai kurios dažniausiai pasitaikančios klaidos, kurias vartotojas gali padaryti įvesdamas formules, ir paaiškinama, kaip jas ištaisyti.

Įsitikinkite, kad	Daugiau informacijos
Kiekvieną funkciją pradėkite lygybės ženklu (=)	Jei lygybės ženklo neįvesite, tai, ką įvedate, gali būti rodoma kaip tekstas arba data. Pavyzdžiui, jei įvedate SUM(A1:A10),„Excel“ rodo teksto eilutę SUM(A1:A10) ir neatlieka skaičiavimo. Jei įvesite 11/2, „Excel“ rodys datą lapkričio 2 d. (jei langelio formatas yra Bendras), o ne padalys 11 iš 2.
Suderinkite visus atidaromuosius ir uždaromuosius skliaustelius	Patikrinkite, ar visi skliausteliai turi savo porą (yra ir atidaromasis, ir uždaromasis skliaustelis). Kai formulėje naudojate funkciją, svarbu, kad kiekvienas skliaustas būtų tinkamoje padėtyje, kad funkcija veiktų tinkamai. Pavyzdžiui, formulė =IF(B5<0),"Not valid",B5*1.05) neveiks, nes yra du uždaromieji skliausteliai ir tik vienas atidaromasis skliaustelis, o turėtų būti abiejų skliaustelių po vieną. Formulė turėtų atrodyti taip: =IF(B5<0,"Not valid",B5*1.05).
Diapazonui nurodyti naudokite dvitaškius	Kai nurodote langelių diapazoną, nuorodai į pirmąjį diapazono langelį ir nuorodai į paskutinį diapazono langelį. atskirti naudokite dvitaškį (:). Pavyzdžiui, =SUM(A1:A5), o ne =SUM(A1 A5), kuri grąžintų klaidą #NULL!.
Įveskite visus būtinus argumentus	Kai kurių funkcijų atveju būtina nurodyti argumentų. Be to, įsitikinkite, kad neįvedėte per daug argumentų.
Įveskite tinkamo tipo argumentus	Kai kurioms funkcijoms, pvz., SUM, reikia skaitinių argumentų. Kitoms funkcijoms, pvz., REPLACE, bent vienam argumentui reikia tekstinės reikšmės. Jei kaip argumentą naudojate netinkamo tipo duomenis, „Excel“ gali pateikti netikėtų rezultatų arba pateikti klaidą.
Įdėkite ne daugiau kaip 64 funkcijas	Į funkciją galite įvesti arba įdėti ne daugiau kaip 64 funkcijų lygius.
Kitų lapų pavadinimus rašykite viengubose kabutėse	Jei formulė nurodo reikšmes ar langelius, esančius kituose darbalapiuose arba darbaknygėse, ir to darbalapio arba darbaknygės pavadinime yra tarpų arba neraidinių simbolių, tokį pavadinimą turite rašyti viengubose kabutėse ( ' ), pvz., ='Ketvirčio duomenys'!D3, arba =‘123’!A1.
Kai formulėje nurodote darbalapio pavadinimą, po jo įrašykite šauktuką (!)	Pavyzdžiui, norėdami grąžinti reikšmę iš tos pačios darbaknygės darbalapio, kurio pavadinimas Ketvirčio duomenys, langelio D3, naudokite šią formulę: ='Ketvirčio duomenys'!D3.
Įtraukite kelią į išorines darbaknyges	Įsitikinkite, kad kiekvienoje išorinėje nuorodoje yra darbaknygės pavadinimas ir jos kelias. Darbaknygės nuorodoje yra darbaknygės pavadinimas, jis turi būti rašomas laužtiniuose skliausteliuose ([Darbaknygėspavadinimas.xlsx]). Nuorodoje taip pat turi būti darbaknygės darbalapio pavadinimas. Jei darbaknygė, kurią norite nurodyti, nėra atidaryta programoje „„Excel““, vis tiek galite į formulę įtraukti jos nuorodą. Pateikiate visą failo maršrutą, pvz., kaip šiame pavyzdyje: =ROWS('C:\Mano dokumentai\[2K Operacijos.xlsx]Pardavimas'!A1:A8). Ši formulė grąžina diapazono, kuriame yra kitos darbaknygės langeliai nuo A1 iki A8, eilučių skaičių (8). : Jeigu visame maršrute yra tarpų, pvz., kaip pirmiau pateiktame pavyzdyje, turite apskliausti maršrutą viengubomis kabutėmis (maršruto pradžioje ir po darbalapio pavadinimo, prieš šauktuką).
Įvedėte neformatuotus skaičius	Neformatuokite į formules įvedamų skaičių. Pavyzdžiui, jei norite įvesti reikšmę 1000 EUR, formulėje įveskite 1000. Jei kaip skaičiaus dalį įvesite kablelį, „„Excel““ laikys jį skyriklio simboliu. Jei norite, kad skaičiai būtų rodomi su tūkstančių ar milijonų skyrikliu arba valiutos simboliais, langelius formatuokite po to, kai įvesite skaičius. Pavyzdžiui, jei norite pridėti 3100 prie langelio A3 reikšmės ir įvedate formulę =SUM(3,100,A3),„Excel“ sudeda skaičius 3 ir 100, tada tą sumą prideda prie A3 reikšmės, užuot įtraukę 3100 į A3, o =SUM(3100,A3). Arba, jei įvesite formulę =ABS(-2,134), „Excel“ rodys klaidą, nes abs funkcija priima tik vieną argumentą: =ABS(-2134).

Norėdami patikrinti, ar formulėje nėra klaidų, galite taikyti tam tikras taisykles. Šios taisyklės negarantuoja, kad darbalapyje nebus klaidų, bet jos gali labai daug nuveikti ieškodamos dažnai pasitaikančių klaidų. Atskiras taisykles galite įjungti ir išjungti po vieną.

Klaidas galima pažymėti ir ištaisyti dviem būdais: po vieną klaidą vienu metu (panašiai kaip ir rašybos tikrintuve) arba iš karto kai jos atsiranda darbalapyje, kai įvedate duomenis.

Klaidą galite išspręsti naudodami "Excel" rodomas parinktis arba galite nepaisyti klaidos pasirinkdami Nepaisyti klaidos. Jei nepaisote tam tikrame langelyje esančios klaidos, toliau tikrinant, ar nėra klaidų, klaida tame langelyje nerodoma. Vis dėlto, galite iš naujo nustatyti visas anksčiau nepaisytas klaidas, kad jos vėl būtų rodomos.

1. Jei naudojate "Excel" sistemoje "Windows", eikite į Failų > parinktys > Formulės arba
   jei naudojate "Excel", skirtą "Mac", pasirinkite "Excel" meniu > Nuostatos > Klaidų tikrinimas.

2. Dalyje Klaidų tikrinimas pažymėkite Leisti foninį klaidų tikrinimą. Bet kokia aptikta klaida pažymėta trikampiu viršutiniame kairiajame langelio kampe.

   

3. Norėdami pakeisti klaidos atsiradimo vietą žyminčio trikampio spalvą, lauke Nurodyti klaidas naudojant šią spalvą pasirinkite norimą spalvą.

4. Dalyje „Excel“ tikrinimo taisyklės pažymėkite arba panaikinkite bet kurios iš šių taisyklių žymės langelius:

   • Langeliai, kuriuose yra formulių, kurių rezultatas yra klaida: formulė nenaudoja numatytos sintaksės, argumentų ar duomenų tipų. Klaidų reikšmės apima #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!. Kiekviena iš šių klaidų reikšmių turi skirtingas priežastis ir yra išspręsta įvairiais būdais.

     : Jei klaidos reikšmę įvesite tiesiai į langelį, ji bus saugoma kaip klaidos reikšmė, bet nebus pažymėta kaip klaida. Tačiau, jei kitame langelyje esanti formulė nurodo į tą langelį, formulė iš to langelio grąžina klaidos reikšmę.

   • Nesuderinama apskaičiuoto stulpelio formulė, esanti lentelėse: apskaičiuotame stulpelyje gali būti atskirų formulių, kurios skiriasi nuo pagrindinės stulpelio formulės, kuriančios išimtį. Apskaičiuoto stulpelio išimtys kuriamos, kai atliekate vieną iš šių veiksmų:

     • Įvedate kitus duomenis nei formulė, esanti apskaičiuoto stulpelio langelyje.

     • Apskaičiuoto stulpelio langelyje įveskite formulę, tada naudokite Ctrl + Z arba pasirinkite Anuliuoti sparčiosios prieigos įrankių juostoje.

     • Įvedate naują formulę į apskaičiuojamąjį stulpelį, kuriame jau yra viena ar daugiau išimčių.

     • Į apskaičiuotą stulpelį kopijuojate duomenis, kurie neatitinka apskaičiuoto stulpelio formulės. Jei nukopijuotuose duomenyse yra formulė, ji perrašo duomenis, esančius apskaičiuotame stulpelyje.

     • Perkelkite arba panaikinkite kitoje darbalapio srityje esantį langelį, kurį nurodo viena iš apskaičiuoto stulpelio eilučių.

   • Langeliai, kuriuose yra 2 skaitmenimis išreikšti metai: langelyje yra teksto data, kurią naudojant formulėse gali būti neteisingai interpretuojamas amžius. Pvz., data formulėje =YEAR("1/1/31") gali būti interpretuojama kaip 1931 ir kaip 2031. Šią taisyklę naudokite, kai norite patikrinti nevienareikšmiškas teksto datas.

   • Skaičiai, suformatuoti kaip tekstas arba prieš juos įvestas apostrofas: langelyje yra skaičiai, saugomi kaip tekstas. Dažniausiai taip nutinka, kai duomenys importuojami iš kitų šaltinių. Dėl teksto pavidalu saugomų skaičių gali būti netikėtų rūšiavimo rezultatų, todėl geriausia konvertuoti juos į skaičius. =SUM(A1:A10) yra matomas kaip tekstas.

   • Formulės, nesuderinamos su kitomis srities formulėmis: formulė neatitinka kitų šalia esančių formulių modelio. Daugeliu atvejų greta kitų formulių esančios formulės skiriasi tik naudojamose nuorodose. Toliau pateiktame keturių gretimų formulių pavyzdyje "Excel" rodo klaidą šalia formulės =SUM(A10:C10) langelyje D4, nes gretimos formulės didėja per vieną eilutę, o vienas padidėja 8 eilutėmis – "Excel" tikisi formulės =SUM(A4:C4).

     

     Jei formulėje naudojamos nuorodos neatitinka gretimų formulių nuorodų, "Excel" pateikia klaidą.

   • Formulės, neįtraukiančios langelių į sritį: formulė ne visada automatiškai įtraukia nuorodas į duomenis, kuriuos įrašote tarp pradinio duomenų diapazono ir langelio, kuriame yra formulė. Ši taisyklė formulėje esančią nuorodą lygina su faktiniu langelių, kurie yra greta formulės langelio, diapazonu. Jei gretimuose langeliuose yra papildomų reikšmių ir jie nėra tušti, „Excel“ rodo klaidą šalia formulės.

     Pvz., "Excel" įterpia klaidą šalia formulės =SUM(D2:D4), kai taikoma ši taisyklė, nes langeliai D5, D6 ir D7 yra greta langelių, nurodytų formulėje, ir langelio, kuriame yra formulė (D8), ir tuose langeliuose yra duomenų, kurie turėjo būti nurodyti formulėje.

     

   • Atrakinti langeliai, kuriuose yra formulių: formulė neužrakinta, kad būtų galima apsaugoti. Pagal numatytuosius nustatymus visi darbalapio langeliai yra užrakinti, todėl jų negalima keisti, kai darbalapis apsaugotas. Tai gali padėti išvengti netyčinių klaidų, pvz., netyčia panaikinti arba pakeisti formules. Ši klaida nurodo, kad langelis nustatytas atrakinti, bet lapas nebuvo apsaugotas. Patikrinkite ir įsitikinkite, kad nenorite, jog langelis būtų užrakintas.

   • Formulės, nurodančios tuščius langelius: formulėje yra nuoroda į tuščią langelį. Tai gali sukelti nenumatytus rezultatus, kaip parodyta toliau pateiktame pavyzdyje.

     Tarkime, kad norite apskaičiuoti toliau pateiktų langelių stulpelio skaičių vidurkį. Jei trečiasis langelis tuščias, jis neįtraukiamas į skaičiavimą, o rezultatas yra 22,75. Jei trečiojo langelio reikšmė bus 0, rezultatas bus lygus 18,2.

     

   • Į lentelę įvesti duomenys neleistini: lentelėje įvyko tikrinimo klaida. Patikrinkite langelio tikrinimo parametrą skirtuke Duomenys > grupėje Duomenų įrankiai > Duomenų tikrinimas.

1. Pasirinkite darbalapį, kuriame norite ieškoti klaidų.

2. Jei darbalapis yra apskaičiuotas neautomatiniu būdu, spustelėkite klavišą F9, kad būtų perskaičiuotas.

   Jei dialogo langas Klaidų tikrinimas nerodomas, pasirinkite Formulės >Formulių tikrinimas > Klaidų tikrinimas.

3. Jei anksčiau neignoravote kokių nors klaidų, galite vėl patikrinti, ar jų nėra, atlikdami šiuos veiksmus: eikite į Dalį Failo > parinktys > Formulės. Jei naudojate "Excel", skirtą "Mac", pasirinkite "Excel" meniu > Nuostatos > klaidų tikrinimas.

   Sekcijoje Klaidų tikrinimas pasirinkite Iš naujo nustatyti nepaisytas klaidas > Gerai.

   

   : Iš naujo nustatydami nepaisytas klaidas, iš naujo nustatote visas aktyvios darbaknygės darbalapiuose esančias klaidas.

   : Bus patogiau, jei dialogo langą Klaidų tikrinimas perkelsite tiesiai po formulės juosta.

   

4. Pasirinkite vieną iš veiksmų mygtukų dešinėje dialogo lango pusėje. Kiekvieno klaidos tipo atveju galimi veiksmai gali skirtis.

5. Pasirinkite Pirmyn.

1. Šalia langelio pasirinkite Klaidų tikrinimas , tada pasirinkite norimą parinktį. Galimos kiekvieno klaidos tipo komandos skiriasi, o pirmasis įrašas aprašo klaidą.

   Jei pasirinksite Nepaisyti klaidos, klaida bus pažymėta nepaisyti kiekvieno iš eilės einančių tikrintuvas.

   

Jei formulė negali tinkamai įvertinti rezultato, programa "Excel" rodo klaidos reikšmę, pvz., #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!. Kiekvienas klaidos tipas turi skirtingas priežastis ir skirtingus sprendimus.

Toliau pateiktoje lentelėje yra saitų į straipsnius, kuriuose išsamiai aprašomos šios klaidos ir pateikiamas trumpas aprašas nuo ko pradėti.

Tema	Aprašas
Klaidos #### taisymas	„Excel“ rodo šią klaidą, kai stulpelis nėra pakankamai platus visiems simboliams langelyje parodyti arba langelyje yra neigiamos datos ar laiko reikšmės. Pavyzdžiui, formulė, kuri atima ateities datą iš praeities datos, pvz., =06/15/2008-07/01/2008, gaunama neigiamas datos reikšmė. : Pabandykite automatiškai talpinti langelį dukart spustelėję tarp stulpelių antraščių. Jei ### rodomas, nes "Excel" negali rodyti visų simbolių, tai pataiso.
Klaidos #DIV/0! taisymas	„Excel“ pateikia šią klaidą, kai skaičius padalinamas arba iš nulio (0), arba iš reikšmės neturinčio langelio. : Įtraukite į klaidų apdorojimo programą, kaip toliau pateiktame pavyzdyje: =IF(C2,B2/C2,0)
Klaidos #N/A taisymas	„Excel“ pateikia šią klaidą, kai reikšmė funkcijoje arba formulėje negalima. Jei naudojate funkciją, pvz., VLOOKUP, ar tai, ko ieškote, turi atitikmenį peržvalgos diapazone? Dažniausiai – ne. Pabandykite naudoti IFERROR, kad nebūtų rodoma #N/A. Tokiu atveju galite naudoti: =IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)
Klaidos #NAME? taisymas	Ši klaida pateikiama, kai „Excel“ neatpažįsta formulėje esančio teksto. Pvz., diapazono pavadinimas arba funkcijos pavadinimas gali būti parašytas neteisingai. : Jei naudojate funkciją, įsitikinkite, kad teisingai parašytas funkcijos pavadinimas. Šiuo atveju SUM parašyta neteisingai. Pašalinkite "e" ir "Excel" taiso.
Klaidos #NULL! taisymas	„Excel“ pateikia šią klaidą, kai nurodote dviejų nesusikertančių (kryžminių) sričių sankirtą. Sankirtos operatorius yra tarpo simbolis, kuris formulėje atskiria nuorodas. : Įsitikinkite, kad diapazonai tinkamai atskirti – sritys C2:C3 ir E4:E6 nesusikerta, todėl įvedus formulę =SUM(C2:C3 E4:E6) grąžinamas #NULL! klaidą. Įdėjus kablelį tarp C ir E diapazonų, tai ištaiso =SUM(C2:C3,E4:E6)
Klaidos #NUM! taisymas	„Excel“ pateikia šią klaidą, kai formulėje ar funkcijoje yra neleistinų skaitinių reikšmių. Ar naudojate funkciją, kuri kartojasi, pvz., IRR arba RATE? Jei taip, klaida #NUM! greičiausiai yra todėl, kad funkcija negali rasti rezultato. Žr. sprendimo veiksmų temą žinyne.
Klaidos #REF! taisymas	„Excel“ pateikia šią klaidą, kai langelio nuoroda negalioja. Pavyzdžiui, galbūt panaikinote langelius, kuriuos nurodė kitos formulės, arba įklijavote langelius, kuriuos perkėlėte virš langelių, kuriuos nurodė kitos formulės. Ar netyčia panaikinote eilutę arba stulpelį? Šioje formulėje = SUM(A2,B2,C2) panaikinome stulpelį B ir pažvelkite, kas nutiko. Naudodami Anuliuoti (Ctrl + Z), anuliuokite naikinimą, atkurkite formulę arba naudoti ištisinio diapazono nuorodą, pvz., =SUM(A2:C2), kuri būtų automatiškai atnaujinama, kai stulpelis B panaikinamas.
Klaidos #VALUE! taisymas	„Excel“ gali pateikti šią klaidą, jei formulėje yra langelių, kuriuose yra skirtingų tipų duomenys. Ar naudojate matematinius ženklus (+, -, *, /, ^) su skirtingais duomenų tipais? Jei taip, pabandykite naudoti funkciją. Šiuo atveju problemą išspręstų =SUM(F2:F5).

Kai langeliai darbalapyje nematomi, tuos langelius ir jų formules galite peržiūrėti peržvalgos lango įrankių juostoje. Peržvalgos lange patogu patikrinti ar patvirtinti didelių darbalapių formulės skaičiavimus ir rezultatus. Naudojant peržvalgos langą nereikia nuolatinio slinkimo ar ėjimo į kitas darbalapio dalis

Ši įrankių juosta gali būti perkelta arba užfiksuota kaip bet kuri kita įrankių juosta. Pavyzdžiui, galite prijungti ją lango apačioje. Įrankių juosta seka šias langelio ypatybes: 1) darbaknygę, 2) lapą, 3) pavadinimą (jei langelyje yra atitinkamas Pavadintas intervalas), 4) langelio adresą, 5) reikšmę ir 6) formulę.

Langelių įtraukimas į peržvalgos langą

1. Pažymėkite langelius, kuriuos norite peržiūrėti.

   Norėdami pažymėti visus darbalapio langelius su formulėmis, eikite į Pagrindinis > Redagavimas > pasirinkite Rasti & Pasirinkti (arba galite naudoti Ctrl + G arba Control + G "Mac" kompiuteryje)> Eiti į specialiąsias > formules.

   

2. Eikite į Formulės > Formulių tikrinimas > pasirinkite Peržvalgos langas.

3. Pasirinkite Įtraukti peržvalgą.

   

4. Patvirtinkite, kad pažymėjote visus langelius, kuriuos norite peržiūrėti, ir pasirinkite Įtraukti.

   

5. Norėdami pakeisti peržvalgos lango stulpelio plotį, vilkite dešiniąją stulpelio antraštės kraštinę.

6. Norėdami rodyti langelį, kurį nurodo įrašas įrankių juostoje Peržvalgos langas, dukart spustelėkite įrašą.

   : Langeliai, kuriose yra išorinių nuorodų į kitas darbaknyges, įrankių juostoje Peržvalgos langas rodomi tik tada, kai atidarytos kitos darbaknygės.

Langelių pašalinimas iš peržvalgos lango

1. Jei įrankių juosta Peržvalgos langas nerodoma, eikite į Formulės > Formulių tikrinimas > pasirinkite Peržvalgos langas.

2. Pažymėkite norimus perkelti langelius.

   Norėdami pažymėti kelis langelius, paspauskite CTRL ir pažymėkite langelius.

3. Pasirinkite Naikinti peržvalgą.

   

Kartais sunku suprasti, kaip įdėtoji formulė apskaičiuoja galutinį rezultatą, nes yra keli tarpiniai skaičiavimai ir loginiai testai. Tačiau naudodami dialogo langą Formulės įvertinimas galite matyti skirtingas įdėtosios formulės dalis, vertinamas pagal formulės skaičiavimo tvarką. Pavyzdžiui, formulę =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) lengviau suprasti, kai matote šiuos tarpinius rezultatus:

Dialogo lange Formulės vertinimas	Aprašas
=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)	Iš pradžių rodoma įdėtoji formulė. Funkcijos AVERAGE ir SUM įdėtos į funkciją IF. Langelių diapazone D2:D5 yra reikšmės 55, 35, 45 ir 25, todėl funkcijos AVERAGE(D2:D5) rezultatas yra 40.
=IF(40>50,SUM(E2:E5),0)	Langelių diapazone D2:D5 yra reikšmės 55, 35, 45 ir 25, todėl funkcijos AVERAGE(D2:D5) rezultatas yra 40.
=IF(False,SUM(E2:E5),0)	Kadangi 40 nedidesnis už 50, funkcijos IF pirmojo argumento (argumentas logical_test) išraiška yra Klaidinga. Funkcija IF grąžina trečiojo argumento reikšmę (argumentas value_if_false). Funkcija SUM nevertinama, nes ji yra antrasis funkcijos IF argumentas (argumentas value_if_true), kuris grąžinamas tik tada, kai reiškinio reikšmė Teisinga.

1. Pažymėkite langelį, kurį norite vertinti. Vienu metu galima vertinti tik vieną langelį.

2. Eikite į Formulės > Formulių tikrinimas > Įvertinti formulę.

3. Pasirinkite Įvertinti, kad išnagrinėtumėte pabrauktos nuorodos reikšmę. Įvertinimo rezultatas rodomas kursyvu.

   Jei pabraukta formulės dalis yra nuoroda į kitą formulę, pasirinkite Įeikite , kad lauke Įvertinimas būtų rodoma kita formulė. Pasirinkite Išeiti, kad grįžtumėte prie ankstesnio langelio ir formulės.

   Mygtuko Įžengti nurodyti negalima, jei nuoroda pateikiama formulėje antrą kartą arba jei formulė nurodo į langelį kitoje darbaknygėje.

4. Tęskite pasirinkimą Įvertinti , kol bus įvertinta kiekviena formulės dalis.

5. Norėdami peržiūrėti vertinimą dar kartą, pasirinkite Paleisti iš naujo.

6. Norėdami baigti vertinimą, pasirinkite Uždaryti.