Kaip išvengti sugadintų formulių programoje „Excel“

Formulės, kuriose yra daugiau nei vienas argumentas, argumentams atskirti naudoja sąrašo skyriklius. Naudojami skyrikliai gali skirtis atsižvelgiant į jūsų OS lokalę ir „Excel“ parametrus. Dažniausiai naudojami sąrašo skyrikliai yra kablelis „,“ ir kabliataškis „;“.

Formulė neveikia, jei kuri nors iš jos funkcijų naudoja netinkamus skyriklius.

Daugiau informacijos žr. Formulių klaidos, kai netinkamai nustatytas sąrašo skyriklis 

„Excel“ meta įvairių grotelių (#) klaidų, pvz., #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? ir #NULL!, kad nurodytų jog kažkas jūsų formulėje veikia netinkamai. ir #NULL!, tai rodo, kad formulėje kažkas neveikia. klaidos priežastis yra netinkamas formatavimas arba nepalaikomo tipo duomenys argumentuose. Arba bus rodoma #REF! Galite matyti klaidą #REF!, jei formulė nurodo panaikintus arba kitais duomenimis pakeistus langelius. Trikčių diagnostikos instrukcijos kiekvienai klaidai bus skirtingos.

Peržiūrėkite bet kurią iš toliau pateiktų temų, atitinkančių matomą svaro simbolio klaidą:

• Klaidos #NUM! taisymas

• Klaidos #VALUE! taisymas

• Klaidos #N/A taisymas

• Klaidos #DIV/0! taisymas

• Klaidos #REF! taisymas

• Klaidos #NAME? taisymas

• Klaidos #NULL! taisymas

Kiekvieną kartą atidarę skaičiuoklę, kurioje yra formulių, nurodančių reikšmes kitose skaičiuoklėse, būsite paraginti atnaujinti nuorodas arba palikti jas, kokios yra.

„Excel“ rodo anksčiau paminėtą dialogo langą, norint užtikrinti, kad esamos skaičiuoklės formulės visada nurodytų naujausias reikšmes tuo atveju, jei buvo pakeistos nurodomos reikšmės. Galite pasirinkti atnaujinti nuorodas arba, jei nenorite atnaujinti, praleisti. Net jei pasirinksite nenaujinti nuorodų, panorėję visada galėsite rankiniu būdu atnaujinti skaičiuoklės saitus.

Bet kada galite išjungti, kad paleidžiant dialogo langas nebūtų rodomas. Norėdami tai padaryti, eikite į Failas > Parinktys > Išplėstinės > Bendra ir išvalykite parinkties Prašyti atnaujinti automatinius saitus žymėjimą.

Jei formulė nerodo reikšmės, atlikite šiuos veiksmus:

• Įsitikinkite, kad, „Excel“ nustatyta taip, kad skaičiuoklėje rodytų formules. Norėdami tai padaryti, skirtuko Formulės grupėje Formulių tikrinimas pasirinkite Rodyti formules.

  : Taip pat galite naudoti sparčiuosius klavišus Ctrl + ` (virš tabuliacijos klavišo esantį klavišą). Taip pat galite naudoti sparčiuosius klavišus Ctrl + ` (virš tabuliacijos klavišo esantį klavišą). Kai tai padarysite, stulpeliai automatiškai praplatės, kad būtų rodomos formulės. Tačiau nesijaudinkite, kai vėl perjungsite įprastinį rodinį, stulpelių dydis bus pakeistas.

• Jei atlikus minėtus veiksmus problemos neišsprendžiate, gali būti, kad langelis suformatuotas kaip tekstas. Dešiniuoju pelės mygtuku spustelėkite langelį, tada pasirinkite Formatuoti langelius > Bendra (arba Ctrl + 1), tada paspauskite F2 > Enter, kad pakeistumėte formatą.

• Jei stulpelyje yra didelis langelių, suformatuotų kaip tekstas, diapazonas, galima pasirinkti diapazoną, pritaikyti norimą skaičių formatą, tada eiti į Duomenys > Tekstas į stulpelį > Baigti. Formatas bus pritaikytas visiems pasirinktiems langeliams.

  

Kai formulė neskaičiuoja, turite patikrinti, ar programoje „Excel“ įjungtas automatinis skaičiavimas. Formulės neskaičiuos, jei bus įjungtas rankinis skaičiavimas. Atlikite šiuos veiksmus ir patikrinkite, ar įjungtas Automatinis skaičiavimas:

1. Pasirinkite skirtuką Failas, pasirinkite Parinktys, tada pasirinkite kategoriją Formulės.

2. Sekcijos Skaičiavimo parinktys dalyje Darbaknygės skaičiavimas, įsitikinkite, kad pažymėta parinktis Automatinis.

   

Daugiau informacijos apie skaičiavimus rasite Formulės perskaičiavimo, iteracijos arba tikslumo keitimas.

Ciklinės nuorodos susidaro, kai formulė nurodo langelį, kuriame ji pati yra. Taisyti galima perkeliant formulę į kitą langelį arba pakeičiant formulės sintaksę į tokią, kurioje nebus ciklinių nuorodų. Tačiau kai kuriais atvejais gali prireikti ciklinių nuorodų, nes jas naudojant funkcijos kartojamos tol, kol patenkinama konkreti skaitinė sąlyga. Tokiais atvejais turėsite įjungti Ciklinės nuorodos šalinimas arba leidimas.

Daugiau informacijos apie ciklines nuorodas žr. Ciklinės nuorodos šalinimas arba leidimas.

Jei įrašo pradžioje nėra lygybės ženklo, tai nėra formulė ir jo nebus galima apskaičiuoti (dažna klaida).

Įvedus SUM(A1:A10), vietoje formulės rezultato „Excel“ rodoma kita eilutė SUM(A1:A10). Jei įvesite 11/2, „Excel“ bus rodoma data, pvz., lapkričio 2 d. arba 2009-11-02, o ne 11 dalyba iš 2.

Kad išvengtumėte šių netikėtų rezultatų, visada pirmiausia įveskite lygybės ženklą. Pavyzdžiui, įveskite: =SUM(A1:A10) ir =11/2

Kai formulėje naudojate funkciją, svarbu, kad kiekvieną atidaromąjį skliaustelį atitiktų uždaromasis – tik tada funkcija veiks tinkamai. Patikrinkite, ar visi skliausteliai turi savo porą. Pavyzdžiui, formulė =IF(B5<0),"Not valid",B5*1,05) neveiks, nes yra du uždaromieji skliausteliai ir tik vienas atidaromasis skliaustelis. Teisinga formulė atrodo taip: =IF(B5<0,"Not valid",B5*1,05).

„Excel“ funkcijos turi argumentus (reikšmes, be kurių funkcija neveiks). Tik kelios funkcijos (pvz., PI arba TODAY) neturi argumentų. Patikrinkite formulės sintaksę, kuri rodoma, kai įvedate lygybės ženklą ir funkciją, ir įsitikinkite, kad įvedėte visus reikalingus argumentus.

Pavyzdžiui, funkcija UPPER kaip argumentą priima tik vieną teksto arba langelio nuorodos eilutę: =UPPER("labas") arba =UPPER(C2)

Be to, kai kurios funkcijos, pvz., SUM, reikalauja tik skaitinių argumentų, o kitos funkcijos, pvz., REPLACE, reikalauja, kad bent vienas argumentas būtų tekstinė reikšmė. Jei naudojate netinkamą duomenų tipą, kai kurios funkcijos gali pateikti netikėtų rezultatų arba klaidą #VALUE!.

Jei jums reikia greitai rasti konkrečios funkcijos sintaksę, žr. sąrašą „Excel“ funkcijos (pagal kategoriją).

Formulėse nerašykite skaičių su dolerio ženklais ($) arba dešimtainių skyrikliais (,), nes dolerio ženklai reiškia absoliučiąsias koordinates, o kableliais skiriami argumentai. Vietoj $1,000 formulėje įveskite 1000.

Jei argumentuose naudosite formatuotus skaičius, gausite netikėtų skaičiavimo rezultatų ar net klaidą #NUM!. Pavyzdžiui, jei įvesite formulę =ABS(-2,134), kad rastumėte absoliučiąją vertę -2134, „Excel“ parodys #NUM! klaidą, nes ABS funkcija priima tik vieną argumentą ir mato -2 ir 134 kaip atskirus argumentus.

Formulė gali nepateikti laukiamų rezultatų, jei langelio duomenų tipo negalima naudoti skaičiuojant. Pavyzdžiui, jei įvesite paprastą formulę =2+3 į langelį, suformatuotą kaip tekstas, „Excel“ negalės apskaičiuoti įvestų duomenų. Langelyje matysite tik = 2+3. Norėdami tai ištaisyti, pakeiskite langelio duomenų tipą iš Tekstas į Bendra:

1. Pažymėkite langelį.

2. Pasirinkite Pagrindinis ir pasirinkite rodyklę, kad išplėstumėte grupę Skaičius arba Skaičių formatas (arba paspauskite „Ctrl“ + 1). Tada pasirinkite Bendra.

3. Paspauskite F2, kad langelis pereitų į redagavimo režimą, tada paspauskite klavišą Enter, kad būtų priimta formulė.

Jei įvesite datą į langelį, kuriame yra duomenų tipas Skaičius, jis bus rodomas kaip skaitinė datos reikšmė, o ne data. Kad šis skaičius būtų rodomas kaip data, pasirinkite formatą Data galerijoje Skaičių formatas.

Simbolis x dažnai naudojamas kaip daugybos operatorius, tačiau „Excel“ vietoj jo daugybai reikia naudoti žvaigždutę (*). Kai formulėje naudojate konstantą, „Excel“ rodo klaidos pranešimą ir gali ištaisyti formulę pakeisdama x žvaigždute (*).

Tačiau jei naudosite langelio nuorodas, „Excel“ pateiks #NAME?. klaidą.

Jei kuriate formulę, kurioje yra teksto, jį išskirkite kabutėmis.

Pvz., formulė ="Šiandien yra " & TEXT(TODAY(),"dddd, mmmm dd") suderina tekstą „Šiandien yra “ su funkcijų TEXT ir TODAY rezultatais, o langelyje pateikia Šiandien yra pirmadienis, gegužės 30 d.

Formulėje „Šiandien yra “ tekste yra tarpas prieš pabaigos kabutes, kad tarp žodžių „Šiandien yra“ ir „pirmadienis, gegužės 30 d.“ Be kabučių teksto prieš tekstą ir po jo formulė gali rodyti klaidą #NAME?.

Formulę galite sudaryti (arba į ją įdėti) iš ne daugiau kaip 64 lygių funkcijų.

Pvz., formulėje =IF(SQRT(PI())<2,„Mažiau nei du!“,„Daugiau nei du!“) yra 3 lygių funkcijos: PI funkcija įdėta į SQRT funkciją, kuri įdėta į IF funkciją.

Įvesdami nuorodą į reikšmes ar langelius kitame darbalapyje, jei to lapo pavadinime yra ne raidinių-skaitinių simbolių (pvz., tarpas), išskirkite pavadinimą viengubomis kabutėmis (').

Pavyzdžiui, norėdami grąžinti reikšmę iš darbaknygės darbalapio, kurio pavadinimas Ketvirčio duomenys, langelio D3 įveskite: ='Ketvirčio duomenys'!D3. Be kabučių aplink lapo pavadinimą formulė rodo klaidą #NAME?.

Norėdami formulėje nurodyti reikšmes arba langelius, esančius kitame lape, taip pat galite juos pasirinkti. Tada „Excel“ automatiškai lapų pavadinimus išskiria kabutėmis.

Jei įvedate nuorodą į reikšmes arba langelius, esančius kitoje darbaknygėje, darbaknygės pavadinimas turi būti rašomas laužtiniuose skliausteliuose ([]), o po jo turi būti nurodytas darbalapio pavadinimas, kuriame yra reikšmės arba langeliai.

Pavyzdžiui, norėdami nurodyti langelius nuo A1 iki A8, esančius „Excel“ atidarytos darbaknygės 2K operacijos lape Pardavimas, įveskite: = [2K operacijos.xlsx]Pardavimas!A1:A8. Be laužtinių skliaustų formulė rodo klaidą #REF!".

Jei darbaknygė neatidaryta programa „Excel“, įveskite visą failo kelią.

Pvz., =ROWS('C:\Mano dokumentai\[2K operacijos.xlsx]Pardavimas'!A1:A8).

Dalijant langelį iš kito langelio, kuriame yra nulis (0) arba nėra reikšmės, įvyksta klaida #DIV/0!.

Kad išvengtumėte šios klaidos, galite kreiptis į jį tiesiogiai ir patikrinti, ar yra vardiklis. Galite naudoti: 

=IF(B1,A1/B1,0)

Kuriame sakoma, AR(yra B1, tada A1 dalijamas iš B1, kitu atveju pateikiamas 0).

Prieš ką nors naikindami visada patikrinkite, ar neturite formulių, kurios nurodo langelių duomenis, diapazonus, apibrėžtus vardus, darbalapius arba darbaknyges. Tada prieš pašalindami nurodomus duomenis galėsite pakeisti šias formules jų rezultatais.

Jei formulės negalite pakeisti jų rezultatais, peržiūrėkite šią informaciją apie klaidas ir galimus sprendimus:

• Jei formulė nurodo langelius, kurie buvo panaikinti arba pakeisti kitais duomenimis, ir jei ji pateikia klaidą #REF!,pasirinkite langelį su #REF! klaidą. Formulės juostoje pasirinkite #REF! ir panaikinkite. Tada dar kartą įveskite formulės diapazoną.

• Jei nėra apibrėžtojo vardo ir jį nurodanti formulė pateikia klaidą #NAME?,apibrėžkite naują vardą, kuris nurodo norimą diapazoną, arba pakeiskite formulę, kad būtų tiesiogiai nurodytas langelių diapazonas (pvz., A2:D8).

• Jei nėra darbalapio ir jį nurodanti formulė grąžina klaidą #REF! , nėra būdo, kaip išspręsti šią problemą, nes panaikinto darbalapio atkurti negalima.

• Jei nėra darbaknygės, ją nurodanti formulė lieka nepakitusi, kol jos neatnaujinsite.

  Pavyzdžiui, jei formulė yra = [Knyga1.xlsx]Lapas1'!A1, tačiau failo Knyga1.xlsx nebeturite, toje darbaknygėje nurodytos reikšmės lieka pasiekiamos. Tačiau jei redaguojate ir įrašote formulę, kuri nurodo tą darbaknygę, „Excel“ pateikia dialogo langą Naujinti reikšmes ir paragina įvesti failo vardą. Pasirinkite Atšaukti, o tada įsitikinkite, kad pakeičiant formules jų rezultatais šie duomenys nebuvo prarasti.

Kartais, kopijuodami langelio turinį, norite įklijuoti tik reikšmę, o ne esamą formulę, rodomą „formulės juosta“.

Pavyzdžiui, galbūt norėsite kopijuoti formulės rezultato reikšmę į langelį, esantį kitame darbalapyje. Arba norėsite panaikinti reikšmes, kurias naudojote formulėje, nukopijavę rezultato reikšmę į kitą darbalapio langelį. Dėl šių veiksmų gali atsirasti neleistino langelio nuorodos klaida (#REF!) paskirties langelyje, nes langelių, kuriuose yra formulėje naudotos reikšmės, nebegalima nurodyti.

Šios klaidos galite išvengti į paskirties langelius įklijuodami tik formulių rezultatų reikšmes.

1. Darbalapyje pasirinkite langelius, kuriuose yra kopijuotinos formulės rezultatų reikšmės.

2. Skirtuko Pagrindinis grupėje Mainų sritis pasirinkite Kopijuoti .

   

   Spartieji klavišai: Paspauskite CTRL + C

3. Pažymėkite viršutinį kairįjį įklijavimo sritis langelį.

   : Norėdami perkelti ar nukopijuoti pažymėtą sritį į kitą darbalapį arba darbaknygę, pasirinkite kito darbalapio skirtuką arba perjunkite kitą darbaknygę ir pažymėkite įklijavimo srities viršutinį kairįjį langelį.

4. Skirtuko Pagrindinis grupėje Mainų sritis pasirinkite Įklijuoti , tada pasirinkite Įklijuoti reikšmes arba paspauskite Alt > E > S > V > Enter, jei naudojate „Windows“, arba Option > Command > V > V > Enter, jei naudojate „Mac“.

Norėdami suprasti, kaip sudėtingos arba įdėtosios formulės apskaičiuoja galutinį rezultatą, galite įvertinti šią formulę.

1. Pažymėkite langelį, kuriame yra norima įvertinti formulė.

2. Pasirinkite Formulės > Įvertinti formulę.

   

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

   

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

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

5. Tęskite, kol bus įvertinta kiekviena formulės dalis.

   Įrankis Įvertinti formulę nebūtinai informuos, kodėl formulė neveikia, tačiau gali padėti išsiaiškinti priežastis. Tai gali būti labai naudingas įrankis didesnėse formulėse, kai gali būti sunku rasti problemą.

   : 

   • Kai kurios funkcijų IF ir CHOOSE dalys nebus įvertintos, o lauke Įvertinimas gali būti rodoma klaida #N/A.

   • Tuščios nuorodos lauke Įvertinimas rodomos kaip nulinės reikšmės (0).

   • Kai kurios funkcijos perskaičiuojamos kiekvieną kartą pakeitus darbalapį. Naudojant šias funkcijas, įskaitant RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY ir RANDBETWEEN funkcijas, dialogo lange Formulės vertinimas gali būti rodomi rezultatai, kurie skiriasi nuo faktinių rezultatų darbalapio langelyje.