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

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

Apgailestaujame, tačiau reiškia, kad "Excel" negali suprasti, ką bandote atlikti, todėl turėsite atnaujinti formulę arba įsitikinti, kad tinkamai naudojate funkciją.

Grįžkite į 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į pasirinkti mygtuką Atšaukti , kad išeitumėte iš redagavimo režimo.

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

Ar formulėje naudojate tinkamus sąrašo skyriklius?

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

Ar "Excel" formulėse matote svaro simbolio (#) klaidą?

"Excel" meta įvairių grotelių (#) klaidų, pvz., #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, ir #NULL! kad nurodytumėte, kad 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

Neveikiančių saitų taisymas "Excel" formulėse

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 į Išplėstinės > bendrosios failų > parinktys >ir išvalykite langelį Prašyti atnaujinti automatinius saitus.

Formulė rodo sintaksę, o ne reikšmę programoje "Excel"

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.

  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 įprastą rodinį, stulpelių dydis bus pakeistas.

• Jei atlikus minėtus veiksmus problemos neišsprendžiate, gali būti, kad langelis suformatuotas kaip tekstas. Galite dešiniuoju pelės mygtuku spustelėti langelį ir pasirinkti 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ą ir pereiti į Duomenų > tekstas į stulpelio > pabaigą. Formatas bus pritaikytas visiems pasirinktiems langeliams.

  

Automatinio darbaknygės skaičiavimo įgalinimas, jei formulė neskaičiuoja programoje "Excel"

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. Įsitikinkite, kad skyriaus Skaičiavimo parinktys dalyje Darbaknygės skaičiavimas pasirinkta parinktis Automatinis .

   

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

Formulė yra vienas ar daugiau ciklinių nuorodų

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.

Ar jūsų funkcija prasideda lygybės ženklu (=)?

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.

Ar suderinote atidaromuosius ir uždaromuosius skliaustus?

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

Ar visi reikalingi argumentai yra sintaksėje?

„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ą).

Neformatuotų skaičių tvarkymas "Excel" formulėse

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.

Ar nurodyti langeliai priklauso tinkamam duomenų tipui?

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š teksto į bendrąjį :

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 skaitinių duomenų tipas, ji bus rodoma kaip skaitinė datos reikšmė, o ne data. Kad šis skaičius būtų rodomas kaip data, pasirinkite formatą Data galerijoje Skaičių formatas.

Ar bandote dauginti nenaudodami simbolio *?

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ą.

Ar formulių tekste netrūksta kabučių?

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?.

Ar formulėje daugiau nei 64 funkcijos?

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

Pavyzdžiui, formulė =IF(SQRT(PI())<2,"Mažiau nei du!","Daugiau nei du!") turi 3 funkcijų lygius; Funkcija PI įdėta į funkciją SQRT, kuri įdėta į funkciją IF.

Ar lapų pavadinimai parašyti viengubose kabutėse?

Į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.

Išorinės darbaknygės kelių taisymas "Excel" formulėse

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

Ar nedalijate skaitinių reikšmių iš nulio?

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

Ar formulė nurodo panaikintus duomenis?

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!, pažymėkite 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ė 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ą. Pasirinkite Atšaukti, o tada įsitikinkite, kad pakeičiant formules jų rezultatais šie duomenys nebuvo prarasti.

Ar nukopijavote ir įklijavote langelius, susietus su formule, esančia skaičiuoklėje?

Kartais, kopijuodami langelio turinį, norite įklijuoti tik reikšmę, o ne formulių juostoje rodomą formulę.

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. On the Home tab, in the Clipboard group, select .
   
   Spartieji klavišai: Paspauskite CTRL + C

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

   Patarimas

   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 Įklijavimo , tada pasirinkite Įklijuoti reikšmes arba paspauskite Alt > E > S > V > Enter "Windows" sistemoje arba Option > Command > V > V Enter > "Mac" kompiuteryje.

Jei turite įdėtąją formulę, įvertinkite ją peržiūrėdami kiekvieną veiksmą

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. Pasirinkti formules>į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ą.

   Pastaba

   • Kai kurios funkcijų IF ir CHOOSE dalys nebus įvertintos, o #N/A klaida gali būti rodoma lauke Įvertinimas .
   • 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.

Reikia daugiau pagalbos?

Visada galite kreiptis eksperto į "Excel" technologijų bendruomenę arba gauti pagalbos bendruomenėse.

Taip pat žr.

„Excel“ formulių apžvalga

"Excel" žinynas & mokymąsi