„Excel“ formulės neveikia: galimos priežastys ir kaip tai ištaisyti!

Jei dirbate su „Excel“ formulėmis, anksčiau ar vėliau susidursite su problema, kai „Excel“ formulės visai neveikia (arba pateikia neteisingą rezultatą).

Nors būtų buvę puiku, jei būtų tik kelios galimos priežastys, dėl kurių netinkamai veikia formulės. Deja, yra per daug dalykų, kurie gali suklysti (ir dažnai pasitaiko).

Bet kadangi mes gyvename pasaulyje, kuriame laikomasi Pareto principo, jei patikrinsite keletą bendrų problemų, tai greičiausiai išspręs 80% (o gal net 90% ar 95% problemų, kai formulės neveikia „Excel“).

Ir šiame straipsnyje aš pabrėšiu tas bendras problemas, kurios greičiausiai yra jūsų priežastis „Excel“ formulės neveikia.

Taigi pradėkime!

Neteisinga funkcijos sintaksė

Pradėsiu nuo to, kad atkreipiu dėmesį į akivaizdų dalyką.

Kiekviena „Excel“ funkcija turi tam tikrą sintaksę, pvz., Argumentų, kuriuos ji gali priimti, skaičių arba argumentų, kuriuos ji gali priimti, tipą.

Ir daugeliu atvejų priežastis, kodėl jūsų „Excel“ formulės neveikia arba duoda neteisingą rezultatą, gali būti neteisingas argumentas (arba trūkstami argumentai).

Pavyzdžiui, funkcijai VLOOKUP naudojami trys privalomi argumentai ir vienas pasirenkamas argumentas.

Jei pateikiate neteisingą argumentą arba nenurodote neprivalomo argumento (kur to reikia, kad formulė veiktų), tai duos neteisingą rezultatą.

Pvz., Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, kur turite žinoti pažymio balą 2 egzamine (F2 langelyje).

Jei naudosiu toliau pateiktą formulę, gausiu neteisingą rezultatą, nes trečiame argumente (kuriame prašoma įvesti stulpelio indekso numerį) naudoju neteisingą vertę.

= VLOOKUP (A2, A2: C6,2, FALSE)

Šiuo atveju formulė apskaičiuoja (nes pateikia vertę), tačiau rezultatas neteisingas (vietoj 2 egzamino balo jis duoda 1 egzamino balą).

Kitas pavyzdys, kai turite būti atsargūs dėl argumentų, yra naudojant VLOOKUP su apytiksle atitiktimi.

Kadangi norėdami nurodyti, kur norite, kad VLOOKUP atliktų tikslią ar apytikslę atitiktį, turite naudoti pasirenkamą argumentą, todėl nenurodžius (arba naudojant neteisingą argumentą) gali kilti problemų.

Žemiau pateikiamas pavyzdys, kai turiu kai kurių mokinių pažymių duomenis ir noriu išgauti 1 egzamino pažymius dešinėje esančioje lentelėje esantiems studentams.

Kai naudoju žemiau pateiktą VLOOKUP formulę, kai kuriems pavadinimams pateikiama klaida.

= VLOOKUP (E2, $ A $ 2: $ C $ 6,2)

Taip atsitinka, nes nenurodžiau paskutinio argumento (kuris naudojamas norint nustatyti, ar atlikti tikslią, ar apytikslę atitiktį). Kai nenurodote paskutinio argumento, jis automatiškai nustato apytikslę atitiktį pagal numatytuosius nustatymus.

Kadangi šiuo atveju turėjome atlikti tikslią atitiktį, kai kurių pavadinimų formulė pateikia klaidą.

Nors aš paėmiau funkcijos VLOOKUP pavyzdį, šiuo atveju tai gali būti taikoma daugeliui „Excel“ formulių, kuriose taip pat yra pasirenkamų argumentų.

Taip pat skaitykite: Klaidų nustatymas naudojant „Excel“ formulių derinimą

Papildomos erdvės, sukeliančios netikėtus rezultatus

Pirmaujančias, užpakalines erdves sunku išsiaiškinti ir jos gali sukelti problemų, kai naudojate langelį, kurio formulėse yra ši informacija.

Pavyzdžiui, toliau pateiktame pavyzdyje, jei bandysiu naudoti VLOOKUP, kad gaučiau „Mark“ balą, tai man duoda klaidą #N/A (nepasiekiama klaida).

Nors matau, kad formulė yra teisinga, o pavadinimas „Žymėti“ aiškiai yra, sąrašas yra nematomas, tačiau nematau, kad langelyje, kuriame yra pavadinimas, yra užpakalinė erdvė (D2 langelyje).

„Excel“ nemano, kad šių dviejų langelių turinys yra vienodas, todėl laiko jį neatitikimu, kai pateikiama vertė naudojant VLOOKUP (arba tai gali būti bet kuri kita paieškos formulė).

Norėdami išspręsti šią problemą, turite pašalinti šiuos papildomus tarpo simbolius.

Tai galite padaryti naudodami bet kurį iš šių būdų:

1. Prieš naudodami formulėse, išvalykite langelį ir pašalinkite visas priekines/galines tarpus
2. Naudokite TRIM funkciją formulėje, kad įsitikintumėte, jog nepaisoma jokių priekinių/galinių/dvigubų tarpų.

Aukščiau pateiktame pavyzdyje galite naudoti toliau pateiktą formulę, kad įsitikintumėte, jog ji veikia.

= VLOOKUP (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

Nors aš paėmiau VLOOKUP pavyzdį, tai taip pat yra dažna problema dirbant su TEXT funkcijomis.

Pavyzdžiui, jei aš naudoju LEN funkciją skaičiuoti bendrą simbolių skaičių ląstelėje, jei yra priekinės arba galinės tarpo vietos, jos taip pat būtų skaičiuojamos ir duotų neteisingą rezultatą.

Išsinešti / kaip pataisyti: Jei įmanoma, prieš naudodami formulėse, išvalykite duomenis pašalindami visus galinius/galinius ar dvigubus tarpus. Jei negalite pakeisti pradinių duomenų, naudokite TRIM funkciją formulėse, kad tai padarytumėte.

Rankinio skaičiavimo naudojimas vietoj automatinio

Šis vienas nustatymas gali jus išprotėti (jei nežinote, kad tai sukelia visas problemas).

„Excel“ turi du skaičiavimo režimus - Automatinis ir Rankinis.

Pagal numatytuosius nustatymus automatinis režimas yra įjungtas, o tai reiškia, kad tuo atveju, jei naudoju formulę arba keičiu esamas formules, ji automatiškai (ir akimirksniu) atlieka skaičiavimus ir pateikia man rezultatą.

Tai nustatymas, prie kurio visi esame pripratę.

Automatinio nustatymo metu, kai darote bet kokius pakeitimus darbalapyje (pvz., Įvedate naują formulę net tam tikram langelio tekstui), „Excel“ automatiškai viską perskaičiuoja (taip, viskas).

Tačiau kai kuriais atvejais žmonės įgalina rankinį skaičiavimo nustatymą.

Tai dažniausiai daroma, kai turite sunkų „Excel“ failą, kuriame yra daug duomenų ir formulių. Tokiais atvejais galbūt nenorėsite, kad „Excel“ viską perskaičiuotų atlikus nedidelius pakeitimus (nes tai gali užtrukti kelias sekundes ar net minutes), kol šis perskaičiavimas bus baigtas.

Jei įgalinsite rankinį skaičiavimą, „Excel“ neskaičiuos, nebent priversite.

Ir tai gali priversti jus galvoti, kad jūsų formulė nėra apskaičiuojama.

Viskas, ką jums reikia padaryti šiuo atveju, arba grąžinti automatinį skaičiavimą arba priversti perskaičiuoti paspaudus klavišą F9.

Toliau pateikiami veiksmai, kaip pakeisti skaičiavimą iš rankinio į automatinį:

1. Spustelėkite skirtuką Formulė
2. Spustelėkite Skaičiavimo parinktys
3. Pasirinkite Automatinis

Svarbu: jei keičiate skaičiavimą iš rankinio į automatinį, patartina sukurti atsarginę savo darbaknygės atsarginę kopiją (tik tuo atveju, jei jūsų darbaknygė pakabins arba „Excel“ strigs)

Išsinešti / kaip pataisyti: Jei pastebite, kad jūsų formulės neduoda laukto rezultato, pabandykite ką nors paprasto bet kuriame langelyje (pvz., Pridėkite 1 prie esamos formulės. Kai nustatysite problemą kaip tą, kurioje reikia pakeisti skaičiavimo režimą, atlikite jėgos skaičiavimą naudojant F9.

Ištrinamos eilutės/stulpeliai/langeliai, vedantys į #REF! Klaida

Vienas iš dalykų, kurie gali turėti pražūtingą poveikį esamoms „Excel“ formulėms, yra tada, kai ištrinate bet kurią eilutę/stulpelį, kuris buvo naudojamas skaičiavimuose.

Kai taip atsitinka, kartais „Excel“ pakoreguoja nuorodą ir įsitikina, kad formulės veikia gerai.

Ir kartais… aš negaliu.

Laimei, viena aiški nuoroda, kurią gaunate, kai lūžta langelių/eilučių/stulpelių formulės, yra #REF! klaida ląstelėse. Tai yra nuorodos klaida, nurodanti, kad formulėje yra tam tikrų problemų.

Leiskite man parodyti, ką turiu omenyje, naudodamas pavyzdį.

Žemiau naudoju SUM formulę, kad pridėčiau langelius A2: A6.

Dabar, jei ištrinsiu bet kurią iš šių langelių/eilučių, SUM formulė grąžins #REF! klaida. Taip atsitinka todėl, kad kai ištryniau eilutę, formulė nežino, į ką dabar kreiptis.

Matote, kad trečiasis formulės argumentas tapo #REF! (kuris anksčiau nurodė langelį, kurį ištrynėme).

Išsinešti / kaip pataisyti: Prieš ištrindami bet kokius formulėse naudojamus duomenis, įsitikinkite, kad po ištrynimo nėra klaidų. Taip pat rekomenduojama reguliariai kurti savo darbų atsarginę kopiją, kad būtumėte tikri, jog visada turėsite ką atkurti.

Neteisingas skliaustų išdėstymas (BODMAS)

Kai jūsų formulės tampa vis didesnės ir sudėtingesnės, naudinga naudoti skliaustus, kad būtų visiškai aišku, kokia dalis priklauso.

Kai kuriais atvejais skliausteliai gali būti netinkamoje vietoje, o tai gali duoti neteisingą rezultatą arba klaidą.

Kai kuriais atvejais rekomenduojama naudoti skliaustus, kad įsitikintumėte, jog formulė supranta, ką pirmiausia reikia sugrupuoti ir apskaičiuoti.

Pavyzdžiui, tarkime, kad turite šią formulę:

=5+10*50

Anksčiau pateiktoje formulėje rezultatas yra 505, nes „Excel“ pirmiausia atlieka dauginimą, o tada prideda (nes yra prioritetas, kai kalbama apie operatorius).

Jei norite, kad jis pirmiausia pridėtų, o tada daugintų, turite naudoti šią formulę:

=(5+10)*50

Kai kuriais atvejais pirmenybės tvarka gali jums padėti, tačiau vis tiek rekomenduojama naudoti skliaustus, kad išvengtumėte painiavos.

Be to, jei jus domina, toliau pateikiama eilės tvarka įvairiems operatoriams, dažnai naudojamiems formulėse:

operatorius	apibūdinimas	Pirmenybės tvarka
: (dvitaškis)	diapazonas	1
(viena erdvė)	Sankryža	2
, (kablelis)	sąjunga	3
-	Neigimas (kaip -1)	4
%	Procentas	5
^	Eksponavimas	6
* ir /	Dauginimas ir padalijimas	7
+ ir -	Sudėjimas ir atimtis	8
&	Sujungimas	9
= =	Palyginimas	10

Išsinešti / kaip pataisyti: Visada naudokite skliaustus, kad išvengtumėte painiavos, net jei žinote pirmenybės tvarką ir teisingai ją naudojate. Turint skliaustus, lengviau tikrinti formules.

Neteisingas absoliučių/santykinių langelių nuorodų naudojimas

Kai kopijuojate ir įklijuojate formules „Excel“, jis automatiškai koreguoja nuorodas. Kartais tai yra būtent tai, ko norite (dažniausiai kai kopijuojate įklijuojate formules į stulpelį), o kartais nenorite, kad tai įvyktų.

Absoliuti nuoroda yra taisant langelio nuorodą (arba diapazono nuorodą), kad ji nesikeistų kopijuojant ir įklijuojant formules, o santykinė nuoroda yra ta, kuri keičiasi.

Daugiau apie absoliučias, santykines ir mišrias nuorodas galite perskaityti čia.

Jei pamiršite pakeisti nuorodą į absoliučią (arba atvirkščiai), galite gauti neteisingą rezultatą. Tai atsitinka man gana dažnai, kai naudoju paieškos formules.

Leiskite parodyti jums pavyzdį.

Žemiau turiu duomenų rinkinį, kuriame noriu gauti 1 egzamino rezultatą pagal E stulpelyje esančius pavadinimus (paprastas VLOOKUP naudojimo atvejis)

Žemiau yra formulė, kurią naudoju ląstelėje F2, o tada nukopijuoju į visas po ja esančias ląsteles:

= VLOOKUP (E2, A2: B6,2,0)

Kaip matote, ši formulė kai kuriais atvejais pateikia klaidą.

Taip atsitinka todėl, kad neužrakinau lentelės masyvo argumento - taip A2: B6 ląstelėje F2, nors tai turėjo būti $ A $ 2: $ B $ 6

Turėdamas šiuos dolerio ženklus prieš eilutės numerį ir stulpelio abėcėlę langelio nuorodoje, aš verčiu „Excel“ išlaikyti šias langelių nuorodas. Taigi, net kai nukopijuosiu šią formulę, lentelės masyvas ir toliau nurodys A2: B6

Profesionalus patarimas: Norėdami konvertuoti santykinę nuorodą į absoliučią, pasirinkite tą langelio nuorodą langelyje ir paspauskite klavišą F4. Jūs pastebėsite, kad jis keičiasi pridėjus dolerio ženklų. Galite toliau spausti F4, kol gausite norimą nuorodą.

Neteisinga nuoroda į lapų / darbaknygių pavadinimus

Kai formulėje nurodote kitus lapus ar darbo knygas, turite laikytis tam tikro formato. Ir jei formatas yra neteisingas, gausite klaidą.

Pvz., Jei noriu nurodyti lapo A1 langelį A1, nuoroda būtų = Lapas2! A1 (kur po lapo pavadinimu yra šauktukas)

Ir jei lapo pavadinime yra keli žodžiai (tarkime, kad tai yra pavyzdiniai duomenys), nuoroda būtų = „Pavyzdiniai duomenys“! A1 (kai pavadinimas yra vienas kabutes, po kurių yra šauktukas).

Jei kalbate apie išorinę darbaknygę (tarkime, kad nurodote A1 langelį darbaknygėje „Pavyzdinis lapas“, pavadintoje „Pavyzdinė darbaknygė“), nuoroda bus tokia, kaip parodyta žemiau:

= '[Pavyzdinis darbaknygė.xlsx] Lentelės pavyzdys'! $ A $ 1

Ir jei uždarysite darbaknygę, nuoroda pasikeis ir apims visą darbaknygės kelią (kaip parodyta žemiau):

= 'C: \ Users \ sumit \ Desktop \ [Pavyzdinė darbaknygė.xlsx] Lentelės pavyzdys'! $ A $ 1

Jei galų gale pakeisite darbaknygės arba darbalapio, į kurį formulė nurodo, pavadinimą, gausite #REF! klaida.

Taip pat skaitykite: Kaip rasti išorines nuorodas ir nuorodas „Excel“

Apvaliosios nuorodos

Apskritoji nuoroda yra tada, kai nurodote (tiesiogiai ar netiesiogiai) į tą patį langelį, kuriame apskaičiuojama formulė.

Žemiau pateikiamas paprastas pavyzdys, kai aš naudoju SUM formulę A4 langelyje, naudodamas ją pačiame skaičiavime.

= SUM (A1: A4)

Nors „Excel“ rodo raginimą pranešti apie apskritą nuorodą, ji to nepadarys kiekvienu atveju. Ir tai gali duoti neteisingą rezultatą (be jokio įspėjimo).

Jei žaidžiant įtariate apskritą nuorodą, galite patikrinti, kurios ląstelės ją turi.

Norėdami tai padaryti, spustelėkite skirtuką Formulė ir grupėje „Formulių auditas“ spustelėkite išskleidžiamąją piktogramą Klaidų tikrinimas (maža rodyklė žemyn).

Užveskite pelės žymeklį ant parinkties apskritimo nuorodos ir ji parodys langelį, kuriame yra apskrito nuorodos problema.

Ląstelės suformatuotos kaip tekstas

Jei atsidursite tokioje situacijoje, kai vos įvedę formulę kaip paspaudimą „Enter“, pamatysite formulę, o ne vertę, aišku, kad langelis suformatuotas kaip tekstas.

Kai langelis suformatuotas kaip tekstas, jis laiko formulę kaip teksto eilutę ir parodo ją tokią, kokia yra.

Tai neverčia skaičiuoti ir duoti rezultato.

Ir jis turi lengvą pataisymą.

1. Pakeiskite formatą į „Bendra“ iš „Tekstas“ (jis yra skirtuko Pagrindinis skirtuke Skaičiai)
2. Eikite į langelį, kuriame yra formulė, eikite į redagavimo režimą (naudokite F2 arba dukart spustelėkite langelį) ir paspauskite „Enter“

Jei pirmiau minėti veiksmai problemos neišsprendžia, dar vienas dalykas, kurį reikia patikrinti, yra tai, ar ląstelė pradžioje turi apostrofą. Daugelis žmonių prideda apostrofą, norėdami konvertuoti formules ir skaičius į tekstą.

Jei yra apostrofas, galite jį tiesiog pašalinti.

Tekstas automatiškai konvertuojamas į datas

„Excel“ turi šį blogą įprotį konvertuoti datą į tikrąją datą. Pvz., Jei įvesite 1/1, „Excel“ jį konvertuos į einamųjų metų sausio 1 d.

Kai kuriais atvejais tai gali būti būtent tai, ko norite, o kai kuriais atvejais - prieš jus.

Kadangi „Excel“ datos ir laiko reikšmes saugo kaip skaičius, kai tik įvedate 1/1, ji konvertuoja jį į skaičių, atitinkantį einamųjų metų sausio 1 d. Mano atveju, kai tai darau, jis konvertuoja jį į skaičių 43831 (2020-01-01).

Tai gali sutrikdyti jūsų formules, jei naudojate šias langelius kaip formulės argumentą.

Kaip tai sutvarkyti?

Vėlgi, nenorime, kad „Excel“ automatiškai pasirinktų mums formatą, todėl turime aiškiai nurodyti formatą patys.

Toliau pateikiami veiksmai, kaip pakeisti formatą į tekstą, kad jis automatiškai nekonvertuotų teksto į datas:

1. Pasirinkite langelius/diapazoną, kuriuose norite pakeisti formatą
2. Spustelėkite skirtuką Pagrindinis
3. Grupėje Skaičius spustelėkite išskleidžiamąjį meniu Formatas
4. Spustelėkite Tekstas

Dabar, kai ką nors įvesite į pasirinktus langelius, jis bus laikomas tekstu ir nebus automatiškai pakeistas.

Pastaba: aukščiau aprašyti veiksmai tinka tik duomenims, įvestiems pakeitus formatą. Tai nepakeis teksto, kuris buvo pakeistas į datą prieš jums atliekant šį formatavimo pakeitimą.

Kitas pavyzdys, kai tai gali būti labai varginanti, yra įvedus tekstą/skaičių, kuriame yra nuliai. „Excel“ automatiškai pašalina šiuos nulius, nes mano, kad jie nenaudingi. Pvz., Jei langelyje įvedate 0001, „Excel“ pakeis jį į 1. Jei norite išsaugoti šiuos nulius, atlikite aukščiau nurodytus veiksmus.

Paslėptos eilutės/stulpeliai gali duoti netikėtų rezultatų

Tai ne formulės atvejis, kai gaunamas neteisingas rezultatas, bet netinkamos formulės naudojimas.

Pavyzdžiui, tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir aš noriu gauti visų C ​​stulpelyje matomų langelių sumą.

Ląstelėje C12 aš panaudojau SUM funkciją, kad gaučiau bendrą visų šių įrašų pardavimo vertę.

Kol kas viskas gerai!

Dabar elementų stulpeliui pritaikau filtrą, kad būtų rodomi tik spausdintuvo pardavimo įrašai.

Ir čia yra problema - C12 langelio formulė vis tiek rodo tą patį rezultatą - t. Y. Visų įrašų sumą.

Kaip sakiau, formulė neteikia neteisingo rezultato. Tiesą sakant, SUM funkcija veikia puikiai.

Problema ta, kad čia panaudojome neteisingą formulę.

Funkcija SUM negali atsižvelgti į filtruotus duomenis ir pateikti visų langelių (paslėptų ar matomų) rezultatą. Jei norite gauti tik matomų langelių sumą/skaičių/vidurkį, naudokite SUBTOTAL arba AGGREGATE funkcijas.

Raktas išsinešimui - Suprasti, kaip teisingai naudoti ir apriboti funkciją „Excel“.

Tai yra keletas bendrų priežasčių, kurias mačiau, kur jūsų „Excel“ formulės gali neveikti arba duoti netikėtų ar neteisingų rezultatų. Esu tikras, kad yra daug daugiau priežasčių, kodėl „Excel“ formulė neveikia arba neatnaujinama.

Jei žinote kokią nors kitą priežastį (galbūt tai, kas jus dažnai erzina), praneškite man komentarų skiltyje.

Tikimės, kad ši pamoka jums buvo naudinga!