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" negali suprasti, ką norite daryti, todėl galbūt norėsite pradėti iš naujo.

Pradėkite pasirinkę gerai arba paspauskite ESC , kad uždarytumėte klaidos pranešimą.

Grįšite į langelį su sugadinta formule, kuri bus pateikta redagavimo režimu, o „Excel“ paryškins vietą, kurioje kilo problemų. Jei vis dar nežinote, ką daryti ir norite pradėti iš naujo, galite dar kartą paspausti ESC arba pažymėti mygtuką Atšaukti formulės juostoje, kuri nukreips jus nuo redagavimo režimo.

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

Jei norite pereiti į priekį, toliau pateiktame kontroliniame sąraše pateikiami trikčių šalinimo veiksmai, padėsiantys išsiaiškinti, kas galėjo būti negerai.

Pastaba: Jei naudojateInternetinė „Office“, negalite matyti tų pačių klaidų arba sprendimai gali būti netaikomi.

"Excel" suskumba įvairių svaro (#) klaidų, pvz., #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME? ir #NULL!, kad nurodytumėte, jog formulė veikia netinkamai. ir #NULL!, tai rodo, kad formulėje kažkas neveikia. klaida įvyksta dėl neteisingo formatavimo ar nepalaikomų duomenų tipų argumente. Arba matysite #REF! 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

Žiūrėkite į vieną iš šių temų, atitinkančių svaro klaidą, kurią matote:

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

Neveikiančios nuorodos dialogo langas programoje „Excel“

"Excel" rodo aukščiau esantį dialogo langą, kad įsitikintumėte, jog dabartinėje skaičiuoklėje esančios formulės visada nurodo naujausią reikšmę, jei pasikeitė nuorodos reikšmė. 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 į failų > parinktys > išplėstinė > Generalir išvalykite žymės langelį prašyti atnaujinti automatinius saitus .

Automatinio saito naujinimo raginimo parinkties vaizdas

Svarbu: Jei yra pirmas kartas, kai dirbate su neveikiančiais saitais formulėse, jei reikia atnaujinimo dėl nutrūkusių saitų, arba jei nežinote, ar atnaujinti nuorodas, žiūrėkite kontroliuoti, kada išorinės nuorodos (saitai) atnaujinamos.

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

  • Įsitikinkite, kad "Excel" nustatyta Rodyti formules skaičiuoklėje. Norėdami tai padaryti, pasirinkite skirtuką formulės ir grupėje Formulės tikrinimas pasirinkite Rodyti formules.

    Patarimas: Taip pat galite naudoti sparčiuosius klavišus CTRL + ' (raktas virš tabuliavimo klavišo). Tai atlikus, jūsų stulpeliai bus automatiškai išplėsti, kad būtų rodomos jūsų formulės, tačiau nesijaudinkite, kai perjungiate į įprastą rodinį, jūsų stulpeliai bus pakeisti.

  • Jei anksčiau pateiktas veiksmas nepadeda išspręsti problemos, gali būti, kad langelis formatuojamas 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 turite stulpelį su dideliu langelių diapazonu, suformatuotu kaip tekstas, galite pažymėti intervalą, taikyti pasirinktą skaičių formatą ir pereiti prie duomenų > tekstą į stulpelį > pabaigos. Formatas bus pritaikytas visiems pasirinktiems langeliams.

    Duomenų vaizdas > Dialogo langas Tekstas į stulpelius

Kai formulė neskaičiuoja, turėsite patikrinti, ar "Excel" įgalintas Automatinis skaičiavimas. Formulės neskaičiuos, jei įgalintas Rankinis skaičiavimas. Atlikite šiuos veiksmus, kad patikrintumėte, ar yra 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.

    Automatinio ir neautomatinio skaičiavimo parinkčių vaizdas

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

Ciklinė nuoroda atsiranda, kai formulė nurodo į langelį, kuriame jis yra. Pataisa yra perkelti formulę į kitą langelį arba pakeisti formulės sintaksę į tą, kuri padeda išvengti 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 įgalinti pašalinti arba leisti ciklinę nuorodą.

Jei norite gauti daugiau informacijos apie ciklines nuorodas, žiūrėkite Šalinti arba leisti ciklinę nuorodą.

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). Taip pat, jei įvedate " 11/2", "Excel" rodo datą, pvz., 2 – Nov arba 11/02/2009, vietoj 11 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), "negalioja", B5 * 1,05) neveiks, nes yra du uždarymo skliaustai, bet tik vienas atidaromasis skliaustas. 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: Matysite funkcijos argumentus, išvardytus Slankiosios funkcijos nuorodos įrankių juostoje po formule, kurią įvedate.

Funkcijos nuorodos įrankių juostos ekrano kopija

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ą, funkcijos gali pateikti netikėtus rezultatus arba Rodyti #VALUE! klaidą.

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

Neįvesti skaičių, suformatuotų su dolerio ženklais ($) arba dešimtainiais skyrikliais (,) formulėse, nes dolerio ženklai rodo absoliučiąsias nuorodas ir kablelius yra argumentų skyrikliai. Vietoj $1,000 formulėje įveskite 1000.

Jei naudojate formatuotus skaičius argumente, gausite netikėtus skaičiavimo rezultatus, tačiau taip pat galite matyti #NUM! klaidą. Pavyzdžiui, jei įvedate formulę = ABS (-2 134) , kad rastumėte absoliučiąją reikšmę-2134, programa "Excel" rodys #NUM! klaida, nes funkcija ABS priima tik vieną argumentą ir mato-2 ir 134 kaip atskirus argumentus.

Pastaba: Galite formatuoti formulės rezultatą su dešimtainiais skyrikliais ir valiutos simboliais įvedus formulę naudojant neformatuotus skaičius (konstantas). Paprastai nėra gerai, kad konstantų būtų galima naudoti formulėse, nes jas sunku surasti, jei reikia atnaujinti, ir jos yra labiau linkusios neteisingai įvesti. Galite daug geriau įdėti konstantas į langelius, kur jie yra atidaryti ir lengvai apibrėžiami.

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

  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“ į „*“

Tačiau jei naudojate langelių nuorodas, "Excel" pateiks #NAME? klaidą.

#NAME? klaida, kai langelio nuorodoje vietoj daugybos ženklo „*“ 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" turi tarpą prieš pabaigos kabutę, kad būtų pateikta tuščia vieta tarp žodžių "šiandien yra" ir "pirmadienis, gegužės 30 d." Jei tekste nėra kabučių, formulėje gali būti rodoma #NAME? klaida.

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ėtoji viduje funkcijoje SQRT, kuri, savo ruo tu, yra įdėta į funkciją IF.

Į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, kad darbalapyje, pavadintame ketvirčio duomenys darbaknygėje, būtų grąžinta langelio D3 reikšmė, įveskite: = ' ketvirčio duomenys '! D3. Jei aplink lapo pavadinimą nėra kabučių, formulėje rodoma #NAME? klaida.

Taip pat galite pasirinkti kito lapo reikšmes arba langelius, kad jie būtų nukreipti į formulę. 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.

Pvz., jei norite nurodyti langelius "Excel", kuris yra atidaryti naudojant "Excel", pardavimo lape a1 – A8 langelius, įveskite: = [Q2 Operations.xlsx] pardavimas! A1: A8. Jei nėra laužtinių skliaustų, formulėje rodoma #REF! klaida.

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 pasiekti kitos darbaknygės kelią – atidaryti kitą darbaknygę, tada iš originalios darbaknygės įvesti = ir naudoti " Alt" + "Tab ", kad būtų galima pereiti į kitą darbaknygę. Pažymėkite 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 #DIV/0! klaida.

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

Visada patikrinkite, ar turite formulių, kurios nurodo duomenis langeliuose, diapazonuose, apibrėžtuose pavadinimuose, darbalapiuose arba darbaknygėse, prieš panaikindami ką nors. 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ė reiškia langelius, kurie buvo pašalinti arba pakeisti kitais duomenimis, ir jei pateikia #REF! klaidaPažymėkite langelį su #REF! klaidą. Formulės juostoje pasirinkite #REF! ir jį panaikinkite. Tada dar kartą įveskite formulės intervalą.

  • Jei trūksta apibrėžto pavadinimo, o formulė, kuri remiasi šiuo vardu, pateikia #NAME? klaidą, Apibrėžkite naują pavadinimą, kuris nurodo norimą intervalą, arba pakeiskite formulę tiesiogiai į langelių intervalą (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, tada įsitikinkite, kad šie duomenys neprarasti pakeičiant formules, kurios nurodo trūkstamą darbaknygę su formulių rezultatais.

Kartais nukopijavus langelio turinį, norima įklijuoti tik reikšmę, o ne pagrindinę formulę, rodomą formulės juosta.

Pavyzdžiui, galbūt norėsite nukopijuoti gautą formulės reikšmę į kito darbalapio langelį. Arba galbūt norėsite panaikinti reikšmes, naudotas formulėje, nukopijavę gautą reikšmę į kitą darbalapio langelį. Abiejų šių veiksmų priežastis yra neleistina langelio nuorodos klaida (#REF!) kad būtų rodoma paskirties langelyje, nes langeliai, kuriuose yra reikšmės, kurias naudojote formulėje, nebegali būti nurodyti.

Galite išvengti šios klaidos įklijuodami gautas formulių reikšmes be formulės paskirties langeliuose.

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

  2. Skirtuko Pagrindinis grupėje mainų sritis pasirinkite Kopijuoti Mygtuko vaizdas .

    „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 arba nukopijuoti žymėjimą į kitą darbalapį arba darbaknygę, pasirinkite kitą darbalapio skirtuką arba perjunkite kitą darbaknygę, tada pasirinkite įklijavimo srities viršutinįjį kairįjį langelį.

  4. Skirtuko Pagrindinis grupėje mainų sritis pasirinkite Įklijuoti Mygtuko paveikslėlis , tada pasirinkite Įklijuoti reikšmesarba paspauskite ALT > E > S > V > "Windows", arba parinktis > komandą > v > v > "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 > Įvertinkite formulę.

    Grupė Formulių tikrinimas skirtuke Formulės

  3. Norėdami patikrinti pabrauktos nuorodos reikšmę, pasirinkite įvertinti . Įvertinimo rezultatas rodomas kursyvu.

    Formulių įvertinimo dialogo langas

  4. Jei pabrauktoji formulės dalis yra nuoroda į kitą formulę, pasirinkite žingsnis , kad būtų rodoma kita formulė lauke įvertinimas . Norėdami grįžti į ankstesnį langelį ir formulę, pasirinkite išeiti .

    Mygtuko veiksmas nepasiekiamas antrą kartą, kai nuoroda rodoma formulėje, arba jei formulė nurodo į langelį kitoje darbaknygėje.

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

    Formulės įvertinimo įrankis nebūtinai nurodys, kodėl jūsų formulė neveikia, tačiau gali padėti nurodyti, kur. 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).

    • Kai kurios funkcijos perskaičiuojamos kiekvieną kartą pasikeitus darbalapiui. 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 arba gauti pagalbos iš Atsakymų bendruomenės.

Taip pat žr.

„Excel“ formulių apžvalga

Formulių klaidų aptikimas

„Excel“ funkcijos (pagal abėcėlę)

„Excel“ funkcijos (pagal kategoriją)

Reikia daugiau pagalbos?

Tobulinkite savo „Office“ į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ų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×