Kaip išvengti sugadintų formulių

Kaip išvengti sugadintų formulių

Jei „Excel“ negali suderinti formulės, kurią bandote sukurti, galite gauti klaidos pranešimą, pvz.:

„Excel“ dialogo lango „Kilo problemų su šiuo langu“ vaizdas

Deja, tai reiškia, kad „Excel“ nesupranta, ką bandote padaryti, todėl galima būtų bandyti kurti formulę iš naujo.

Pradėkite spustelėdami Gerai arba paspausdami ESC uždarykite klaidos pranešimą.

Grįšite į langelį su sugadinta formule, kuri bus pateikta redagavimo režimu, o „Excel“ paryškins vietą, kurioje kilo problemų. Jeigu vis tiek nežinote, ką daryti ir norite pradėti iš naujo, galite dar kartą paspausti ESC arba formulių juostoje esantį spustelėti mygtuką Atšaukti, kad būtų uždarytas redagavimo režimas.

Formulės juostoje esančio mygtuko Atšaukti vaizdas

Jei nežinote, ką daryti šiuo metu arba nežinote, kokios pagalbos reikėtų, galite ieškoti panašių klausimų „Excel“ bendruomenės forume arba paskelbti savo klausimą.

Saitas su „Excel“ bendruomenės forumu

Jeigu norite sužinoti daugiau, šiame kontroliniame sąraše pateikiami trikčių diagnostikos veiksmai, kuriuos atlikus bus lengviau sužinoti, kodėl formulės veikia netinkamai.

Jei „Excel“ meta įvairių maišos (#) klaidų, pvz., #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? ir #NULL!, tai rodo, kad formulėje kažkas neveikia. Pvz., klaidą #VALUE! atsiranda dėl neteisingo formatavimo arba argumentuose nepalaikomų duomenų tipų. Galite matyti klaidą #REF!, jei formulė nurodo panaikintus arba kitais duomenimis pakeistus langelius. Trikčių diagnostikos instrukcijos kiekvienai klaidai bus skirtingos.

Pastaba : ### nėra su formule susijusi klaida. Tai tiesiog reiškia, kad stulpelis nėra pakankamai platus, kad būtų galima rodyti langelio turinį. Tiesiog vilkite stulpelį, kad jį išplėstumėte arba eikite Pagrindinis > Formatuoti > Automatiškai talpinti pagal stulpelių plotį.

Pagrindinis > Formatuoti > Automatiškai talpinti stulpelio plotį vaizdas

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

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.

Neveikiančios nuorodos dialogo langas programoje „Excel“

„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 panaikinkite parinkties Prašyti atnaujinti automatinius saitus žymėjimą. Naudojant „Excel 2007“ reikėtų spustelėti „Office“ mygtuką > „Excel“ parinktys. „Office 2007“ mygtukas

Automatinio saito naujinimo raginimo parinkties vaizdas

Svarbu : Jei pirmą kartą dirbate su neveikiančiais formulių saitais, reikia neveikiančių saitų atnaujinimo priemonės arba nežinote, ar reikia naujinti nuorodas, žr. Išorinių nuorodų (saitų) naujinimo valdymas.

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 spustelėkite Rodyti formules.

    Patarimas : 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.

    Duomenų vaizdas > Dialogo langas Tekstas į stulpelius

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. Spustelėkite skirtuką Failas, spustelėkite Parinktys, tada spustelėkite kategoriją Formulės.

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

    Automatinio ir neautomatinio skaičiavimo parinkčių vaizdas

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. Tokiu atveju reikės įgalinti iteracinį skaičiavimą.

Daugiau informacijos apie ciklines nuorodas rasite Ciklinės nuorodos radimas ir taisymas

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, todėl 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)

Pastaba : Funkcijos argumentus matysite išvardytus slankiojoje funkcijos nuorodos įrankių juostoje po formule, ją įvesdami.

Funkcijos nuorodos įrankių juostos ekrano kopija
Funkcijos nuorodos įrankių juosta

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!. Pvz., jei įvesite formulę =ABS(-2,134), kad rastumėte absoliučiąją -2134 reikšmę, „Excel“ parodys klaidą #NUM!, nes ABS funkcija priima tik vieną argumentą.

Pastaba : Įvedę formulę kaip neformatuotus skaičius (konstantas), galite formatuoti formulės rezultatą dešimtainių dalių skyrikliais ir valiutos simboliais taikydami skaičių formatą. Paprastai neverta formulėse naudoti konstantų, nes jas gali būti sunku rasti, jei vėliau prireiktų atnaujinti. Be to, labiau tikėtina, kad jos bus įvestos klaidingai. Daug patogiau konstantas įrašyti langeliuose, kur jos bus gerai matomos ir lengvai nurodomos.

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 išspręsti problemą, pakeiskite langelio duomenų tipą iš Tekstas į Bendra:

  1. Pažymėkite langelį.

  2. Spustelėkite Pagrindinis > rodyklę šalia Skaičių formatas (arba paspauskite Ctrl + 1) ir spustelėkite 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 (*).

Pranešimo laukas, kuriame prašoma pakeisti daugybos ženklą – iš „x“ į „*“
Klaidos pranešimas, kai norint dauginti naudojama „x“ vietoj „*“ su konstantomis

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

#NAME? klaida, kai langelio nuorodoje vietoj daugybos ženklo „*“ naudojamas „x“
#NAME? klaida, kai langelio nuorodoje vietoj simbolio „*“ naudojamas „x“

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.“ būtų tarpas. Jei tekstas bus be kabučių, formulėje gali būti rodoma klaida #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ė rodys klaidą #NAME?.

Norėdami formulėje nurodyti reikšmes arba langelius, esančius kitame lape, taip pat galite juos spustelėti. 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. Jei nebus laužtinių skliaustų, formulė rodys klaidą #REF!.

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

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

Pastaba : Jeigu visame kelyje yra tarpų, turite apskliausti kelią viengubomis kabutėmis (maršruto pradžioje ir po darbalapio pavadinimo, prieš šauktuką).

Patarimas : Lengviausias būdas gauti kitos darbaknygės kelią: atidarykite kitą darbaknygę, tada būdami pradinėje darbaknygėje įveskite „=“, paspaudę Alt + Tab pereikite į kitą darbaknygę ir pasirinkite bet kurį norimo lapo langelį. Tada uždarykite šaltinio darbaknygę. Formulė bus automatiškai atnaujinta, kad rodytų visą failo kelią ir lapo pavadinimą kartu su reikiama sintakse. Netgi galite šį kelią nukopijuoti ir įklijuoti reikiamoje vietoje.

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.

=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 pateikia klaidą #REF!, pažymėkite langelį, kuriame yra #REF! klaida. Formulės juostoje pažymėkite #REF! ir panaikinkite. Tada iš naujo įveskite formulės diapazoną.

  • Jei nėra apibrėžtojo vardo ir jį nurodanti formulė grąžina 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ą. Spustelėkite 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ų paskirties langelyje gali atsirasti neleistino langelio nuorodos klaida (#REF!), 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 spustelėkite Kopijuoti Mygtuko paveikslėlis .

    „Excel“ juostelės vaizdas

    Spartieji klavišai: Paspauskite CTRL + C

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

    Patarimas : Norėdami perkelti ar nukopijuoti pažymėtą sritį į kitą darbalapį arba darbaknygę, spustelėkite 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 spustelėkite Įklijuoti Mygtuko paveikslėlis , tada spustelėkite Į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. Spustelėkite Formulės > Įvertinti formulę.

    Grupė Formulių tikrinimas skirtuke Formulės

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

    Formulių įvertinimo dialogo langas

  4. Jei pabrauktoji formulės dalis yra nuoroda į kitą formulę, spustelėkite Įžengti, kad lauke Įvertinimas būtų rodoma kita formulė. Spustelėkite 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ą.

    Pastabos : 

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

    • Funkcijos, kurios 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.

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės, gauti pagalbos iš Atsakymų bendruomenės arba siūlyti naują funkciją ar patobulinimą „Excel“ vartotojo balse.

Taip pat žr.

„Excel“ formulių peržvalga

Klaidų aptikimas formulėse

„Excel“ funkcijos (abėcėlės tvarka)

„Excel“ funkcijos (pagal kategorijas)

Tobulinkite savo įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×