Excel formulas nedarbojas: iespējamie iemesli un kā to labot!

Ja strādājat ar formulām programmā Excel, agrāk vai vēlāk jūs saskarsities ar problēmu, kad Excel formulas nedarbojas vispār (vai sniedz nepareizu rezultātu).

Lai gan būtu bijis lieliski, ja būtu bijuši tikai daži iespējamie formulu nepareizas darbības iemesli. Diemžēl ir pārāk daudz lietu, kas var noiet greizi (un tas bieži notiek).

Bet, tā kā mēs dzīvojam pasaulē, kurā tiek ievērots Pareto princips, pārbaudot dažas izplatītas problēmas, tas, visticamāk, atrisinās 80% (vai varbūt pat 90% vai 95% problēmu, kurās formulas nedarbojas programmā Excel).

Un šajā rakstā es uzsvēršu tās kopējās problēmas, kas, iespējams, ir jūsu cēlonis Excel formulas nedarbojas.

Tātad, sāksim!

Nepareiza funkcijas sintakse

Sākšu, norādot uz acīmredzamo.

Katrai Excel funkcijai ir noteikta sintakse - piemēram, argumentu skaits, ko tā var pieņemt, vai argumentu veids, ko tā var pieņemt.

Un daudzos gadījumos iemesls, kāpēc jūsu Excel formulas nedarbojas vai dod nepareizu rezultātu, var būt nepareizs arguments (vai trūkstoši argumenti).

Piemēram, funkcijai VLOOKUP tiek izmantoti trīs obligāti argumenti un viens fakultatīvs arguments.

Ja jūs sniedzat nepareizu argumentu vai nenorādāt izvēles argumentu (kur tas ir nepieciešams, lai formula darbotos), tas sniegs jums nepareizu rezultātu.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un jums jāzina atzīmes rezultāts 2. eksāmenā (šūnā F2).

Ja es izmantošu zemāk esošo formulu, es iegūšu nepareizu rezultātu, jo trešajā argumentā (kurā tiek prasīts kolonnas indeksa numurs) es izmantoju nepareizu vērtību.

= MEKLĒT (A2, A2: C6,2, FALSE)

Šajā gadījumā formula aprēķina (jo tā atgriež vērtību), bet rezultāts ir nepareizs (2. eksāmena rezultātu vietā tas dod rezultātu 1. eksāmenā).

Vēl viens piemērs, kurā jums jābūt piesardzīgam attiecībā uz argumentiem, ir, izmantojot VLOOKUP ar aptuveno atbilstību.

Tā kā jums ir jāizmanto fakultatīvs arguments, lai norādītu, kur vēlaties, lai VLOOKUP veiktu precīzu vai aptuvenu atbilstību, tā nenorādīšana (vai nepareiza argumenta izmantošana) var radīt problēmas.

Zemāk ir piemērs, kur man ir dažu skolēnu atzīmju dati un es vēlos iegūt atzīmes 1. eksāmenā studentiem tabulā pa labi.

Kad es izmantoju zemāk esošo VLOOKUP formulu, dažiem nosaukumiem tiek parādīta kļūda.

= MEKLĒŠANA (E2, $ A $ 2: $ C $ 6,2)

Tas notiek, jo neesmu norādījis pēdējo argumentu (kas tiek izmantots, lai noteiktu, vai veikt precīzu vai aptuvenu atbilstību). Ja nenorādāt pēdējo argumentu, tas pēc noklusējuma automātiski veic aptuvenu atbilstību.

Tā kā šajā gadījumā mums bija jāveic precīza atbilstība, formula dažiem nosaukumiem atgriež kļūdu.

Lai gan esmu ņēmis piemēru funkcijai VLOOKUP, šajā gadījumā to var izmantot daudzām Excel formulām, kurām ir arī izvēles argumenti.

Lasiet arī: Kļūdu identificēšana, izmantojot Excel formulu atkļūdošanu

Papildu vietas, kas rada negaidītus rezultātus

Vadošās, beigu atstarpes ir grūti noskaidrot, un tās var radīt problēmas, ja izmantojat šūnu, kuras formulas ir šādas.

Piemēram, zemāk redzamajā piemērā, ja es mēģinu izmantot VLOOKUP, lai iegūtu Marka rezultātu, tas man dod kļūdu #N/A (kļūda nav pieejama).

Lai gan es redzu, ka formula ir pareiza un nosaukums “Atzīmēt” ir skaidri redzams, saraksts nav redzams, bet šūnā, kurai ir nosaukums (šūnā D2), ir beigu vieta.

Excel neuzskata šo divu šūnu saturu vienādu, un tāpēc uzskata, ka tā ir neatbilstība, iegūstot vērtību, izmantojot funkciju VLOOKUP (vai arī tā var būt jebkura cita uzmeklēšanas formula).

Lai novērstu šo problēmu, jums ir jānoņem šīs papildu atstarpes rakstzīmes.

To var izdarīt, izmantojot kādu no šīm metodēm:

1. Pirms izmantojat formulās, notīriet šūnu un noņemiet visus priekšējos/beigu atstarpes
2. Izmantojiet TRIM funkciju formulā, lai pārliecinātos, ka netiek ignorētas visas priekšējās/beigu/dubultās atstarpes.

Iepriekš minētajā piemērā varat izmantot zemāk esošo formulu, lai pārliecinātos, ka tā darbojas.

= SKATĪT (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

Lai gan esmu izmantojis VLOOKUP piemēru, šī ir arī izplatīta problēma, strādājot ar TEXT funkcijām.

Piemēram, ja es izmantoju funkciju LEN, lai saskaitītu kopējo rakstzīmju skaitu šūnā, ja ir priekšā vai aiz tās esošās atstarpes, arī tās tiktu skaitītas un sniegtu nepareizu rezultātu.

Līdzņemšana / kā salabot: Ja iespējams, notīriet savus datus, pirms to izmantošanas formulās, noņemot visus priekšējos/pēdējos vai dubultos atstarpes. Ja nevarat mainīt sākotnējos datus, izmantojiet TRIM funkciju formulās, lai par to parūpētos.

Izmantojot manuālo aprēķinu, nevis automātisko

Šis iestatījums var padarīt jūs traku (ja nezināt, ka tas izraisa visas problēmas).

Programmai Excel ir divi aprēķinu režīmi - Automātiski un Rokasgrāmata.

Pēc noklusējuma automātiskais režīms ir iespējots, kas nozīmē, ka gadījumā, ja es izmantoju formulu vai veicu izmaiņas esošajās formulās, tā automātiski (un uzreiz) veic aprēķinu un sniedz rezultātu.

Tas ir iestatījums, pie kura mēs visi esam pieraduši.

Automātiskajā iestatījumā ikreiz, kad veicat kādas izmaiņas darblapā (piemēram, ievadāt jaunu formulu pat kādam šūnas tekstam), Excel automātiski pārrēķina visu (jā, viss).

Bet dažos gadījumos cilvēki iespējo manuālā aprēķina iestatījumu.

Tas galvenokārt tiek darīts, ja jums ir smags Excel fails ar daudziem datiem un formulām. Šādos gadījumos, iespējams, nevēlaties, lai programma Excel visu pārrēķinātu, veicot nelielas izmaiņas (jo var paiet dažas sekundes vai pat minūtes), lai šī pārrēķināšana tiktu pabeigta.

Ja iespējosit manuālo aprēķinu, programma Excel neaprēķinās, ja vien jūs to nepiespiedīsit.

Un tas var likt domāt, ka jūsu formula netiek aprēķināta.

Viss, kas jums jādara, šajā gadījumā ir vai nu iestatīt automātisko aprēķinu, vai piespiest pārrēķinu, nospiežot taustiņu F9.

Tālāk ir norādītas darbības, lai aprēķinu mainītu no manuāla uz automātisku:

1. Noklikšķiniet uz cilnes Formula
2. Noklikšķiniet uz Aprēķina opcijas
3. Izvēlieties Automātiski

Svarīgi: ja maināt aprēķinu no manuāla uz automātisku, ieteicams izveidot darbgrāmatas dublējumkopiju (tikai gadījumā, ja jūsu darbgrāmata uzkaras vai Excel avarē)

Līdzņemšana / kā salabot: Ja pamanāt, ka jūsu formulas nesniedz gaidīto rezultātu, izmēģiniet kaut ko vienkāršu jebkurā šūnā (piemēram, pievienojot 1 esošai formulai. Kad esat identificējis problēmu kā tādu, kurā jāmaina aprēķina režīms, veiciet spēka aprēķinu izmantojot F9.

Rindu/kolonnu/šūnu dzēšana, kas noved pie #REF! Kļūda

Viena no lietām, kas var postoši ietekmēt jūsu esošās formulas programmā Excel, ir dzēst jebkuru rindu/kolonnu, kas izmantota aprēķinos.

Kad tas notiek, dažreiz Excel pati pielāgo atsauci un pārliecinās, ka formulas darbojas labi.

Un dažreiz… tā nevar.

Par laimi, viena skaidra norāde, ko jūs saņemat, kad tiek pārtrauktas formulas, dzēšot šūnas/rindas/kolonnas, ir #REF! kļūda šūnās. Šī ir atsauces kļūda, kas norāda, ka formulā esošajām atsaucēm ir kāda problēma.

Ļaujiet man parādīt, ko es domāju, izmantojot piemēru.

Zemāk esmu izmantojis SUM formulu, lai pievienotu šūnas A2: A6.

Tagad, ja izdzēsīšu kādu no šīm šūnām/rindām, SUM formula atgriezīs #REF! kļūda. Tas notiek tāpēc, ka, izdzēšot rindu, formula nezina, uz ko tagad atsaukties.

Var redzēt, ka trešais arguments formulā ir kļuvis #REF! (kas agrāk atsaucās uz šūnu, kuru mēs izdzēsām).

Līdzņemšana / kā salabot: Pirms dzēsāt formulās izmantotos datus, pārliecinieties, vai pēc dzēšanas nav kļūdu. Ieteicams arī regulāri izveidot sava darba dublējumkopiju, lai pārliecinātos, ka jums vienmēr ir uz ko atkāpties.

Nepareizs iekavas izvietojums (BODMAS)

Tā kā jūsu formulas sāk kļūt lielākas un sarežģītākas, ieteicams izmantot iekavas, lai būtu pilnīgi skaidrs, kura daļa pieder kopā.

Dažos gadījumos iekavas var būt nepareizā vietā, kas var vai nu dot nepareizu rezultātu, vai kļūdu.

Un dažos gadījumos ieteicams izmantot iekavas, lai pārliecinātos, ka formula saprot, kas vispirms jāgrupē un jāaprēķina.

Piemēram, pieņemsim, ka jums ir šāda formula:

=5+10*50

Iepriekšminētajā formulā rezultāts ir 505, jo Excel vispirms veic reizināšanu un pēc tam saskaitīšanu (jo attiecībā uz operatoriem pastāv prioritāšu secība).

Ja vēlaties, lai vispirms tiktu veikta saskaitīšana un pēc tam reizināšana, jums jāizmanto šāda formula:

=(5+10)*50

Dažos gadījumos prioritātes secība var jums noderēt, taču joprojām ir ieteicams izmantot iekavas, lai izvairītos no neskaidrībām.

Turklāt, ja jūs interesē, zemāk ir norādīta secība dažādiem operatoriem, kurus bieži izmanto formulās:

Operators	Apraksts	Prioritātes kārtība
: (kols)	Diapazons	1
(viena atstarpe)	Krustojums	2
, (komats)	savienība	3
-	Noliegums (kā -1)	4
%	Procenti	5
^	Eksponēšana	6
* un /	Reizināšana un dalīšana	7
+ un -	Saskaitīšana un atņemšana	8
&	Saskaņošana	9
= =	Salīdzinājums	10

Līdzņemšana / kā salabot: Vienmēr izmantojiet iekavas, lai izvairītos no neskaidrībām, pat ja zināt prioritāšu secību un pareizi to lietojat. Izmantojot iekavas, ir vieglāk pārbaudīt formulas.

Nepareiza absolūto/relatīvo šūnu atsauču izmantošana

Kopējot un ielīmējot formulas programmā Excel, tā automātiski pielāgo atsauces. Dažreiz tas ir tieši tas, ko vēlaties (galvenokārt, kad kopējat un ielīmējat formulas slejā), un dažreiz nevēlaties, lai tas notiktu.

Absolūta atsauce ir tad, kad tiek labota šūnas atsauce (vai diapazona atsauce), lai tā nemainītos, kopējot un ielīmējot formulas, un relatīvā atsauce mainās.

Vairāk par absolūtām, relatīvām un jauktajām atsaucēm varat lasīt šeit.

Jūs varat iegūt nepareizu rezultātu, ja aizmirstat mainīt atsauci uz absolūtu (vai otrādi). Tas notiek ar mani diezgan bieži, kad izmantoju uzmeklēšanas formulas.

Ļaujiet man parādīt piemēru.

Tālāk man ir datu kopa, kurā es vēlos iegūt rezultātu 1. eksāmenā par nosaukumiem E slejā (vienkāršs VLOOKUP lietošanas gadījums)

Zemāk ir formula, kuru es izmantoju šūnā F2 un pēc tam kopiju uz visām šūnām zem tās:

= MEKLĒŠANA (E2, A2: B6,2,0)

Kā redzat, šī formula dažos gadījumos rada kļūdu.

Tas notiek tāpēc, ka neesmu bloķējis tabulas masīva argumentu - tas ir A2: B6 šūnā F2, kamēr tam vajadzēja būt $ A $ 2: $ B $ 6

Ja šūnu atsaucē ir šīs dolāru zīmes pirms rindas numura un kolonnu alfabēta, es lieku Excel saglabāt šīs šūnu atsauces nemainīgas. Tātad, pat ja es nokopēju šo formulu uz leju, tabulas masīvs turpinās atsaukties uz A2: B6

Pro padoms: Lai relatīvo atsauci pārvērstu absolūtā, šūnā atlasiet šo atsauci un nospiediet taustiņu F4. Jūs pamanīsit, ka tas mainās, pievienojot dolāra zīmes. Jūs varat turpināt nospiest F4, līdz iegūstat vajadzīgo atsauci.

Nepareiza atsauce uz lapu / darbgrāmatu nosaukumiem

Formulā atsaucoties uz citām lapām vai darbgrāmatām, jums jāievēro noteikts formāts. Un, ja formāts ir nepareizs, jūs saņemsit kļūdu.

Piemēram, ja es vēlos atsaukties uz lapu A1 šūnā 2, atsauce būtu = 2. lapa! A1 (ja aiz lapas nosaukuma ir izsaukuma zīme)

Un, ja lapas nosaukumā ir vairāki vārdi (pieņemsim, ka tie ir datu piemēri), atsauce būtu = 'Datu piemēri'! A1 (kur nosaukums ir iekļauts pēdiņās, kam seko izsaukuma zīme).

Ja jūs atsaucaties uz ārēju darbgrāmatu (pieņemsim, ka atsaucaties uz šūnu A1 darblapas “Piemēra lapa” darbgrāmatas nosaukumā “Darbgrāmatas paraugs”), atsauce būs šāda:

= '[Piemērs darbgrāmatai.xlsx] Lapas piemērs'! $ A $ 1

Un, ja aizverat darbgrāmatu, atsauce mainīsies, iekļaujot visu darbgrāmatas ceļu (kā parādīts zemāk):

= 'C: \ Users \ sumit \ Desktop \ [Piemērs darbgrāmata.xlsx] Lapas piemērs'! $ A $ 1

Ja galu galā maināt darbgrāmatas nosaukumu vai darblapu, uz kuru formula attiecas, tas dos jums #REF! kļūda.

Lasiet arī: Kā atrast ārējās saites un atsauces programmā Excel

Apļveida atsauces

Apļveida atsauce ir tad, ja jūs (tieši vai netieši) atsaucaties uz to pašu šūnu, kurā tiek aprēķināta formula.

Zemāk ir vienkāršs piemērs, kur es izmantoju SUM formulu šūnā A4, vienlaikus izmantojot to aprēķinā.

= SUMMA (A1: A4)

Lai gan programmā Excel tiek parādīta uzvedne, kas ļauj jums uzzināt par apļveida atsauci, tā to nedarīs katrā gadījumā. Un tas var dot nepareizu rezultātu (bez brīdinājuma).

Ja jums ir aizdomas par apļveida atsauci spēlē, varat pārbaudīt, kurās šūnās tā ir.

Lai to izdarītu, noklikšķiniet uz cilnes Formula un grupā “Formulu audits” noklikšķiniet uz nolaižamās ikonas Kļūdu pārbaude (mazā lejupvērstā bultiņa).

Novietojiet kursoru virs opcijas Apļveida atsauce, un tā parādīs šūnu, kurai ir apļveida atsauces problēma.

Šūnas, kas formatētas kā teksts

Ja atrodaties situācijā, kad, tiklīdz ievadāt formulu kā hit Enter, vērtības vietā redzat formulu, tas ir skaidrs gadījums, kad šūna tiek formatēta kā teksts.

Kad šūna tiek formatēta kā teksts, tā formulu uzskata par teksta virkni un parāda to tādu, kāda tā ir.

Tas neliek to aprēķināt un sniegt rezultātu.

Un tam ir vienkāršs labojums.

1. Mainiet formātu uz “Vispārīgi” no “Teksts” (tas atrodas cilnes Sākums grupā Skaitļi)
2. Dodieties uz šūnu, kurā ir formula, nokļūstiet rediģēšanas režīmā (izmantojiet F2 vai veiciet dubultklikšķi uz šūnas) un nospiediet taustiņu Enter

Ja iepriekš minētās darbības neatrisina problēmu, jāpārbauda vēl viena lieta, vai šūnas sākumā ir apostrofs. Daudzi cilvēki pievieno apostrofu, lai formulas un ciparus pārvērstu tekstā.

Ja ir apostrofs, varat to vienkārši noņemt.

Teksts tiek automātiski pārveidots datumos

Programmai Excel ir šis sliktais ieradums konvertēt datumu, kas izskatās kā datums, par faktisko datumu. Piemēram, ja ievadāt 1/1, programma Excel to pārvērstu par kārtējā gada 1. janvāri.

Dažos gadījumos tas var būt tieši tas, ko vēlaties, un dažos gadījumos tas var darboties pret jums.

Tā kā programmā Excel datuma un laika vērtības tiek saglabātas kā skaitļi, tiklīdz ievadāt 1/1, tā pārvērš to par skaitli, kas apzīmē kārtējā gada 1. janvāri. Manā gadījumā, kad es to daru, tas to pārvērš par numuru 43831 (01.01.2020.).

Tas var sajaukt jūsu formulas, ja izmantojat šīs šūnas kā argumentu formulā.

Kā to labot?

Atkal mēs nevēlamies, lai Excel automātiski izvēlētos formātu, tāpēc mums pašiem skaidri jānorāda formāts.

Tālāk ir norādītas darbības, lai mainītu formātu uz tekstu, lai tas automātiski nepārvērstu tekstu par datumiem:

1. Atlasiet šūnas/diapazonu, kurā vēlaties mainīt formātu
2. Noklikšķiniet uz cilnes Sākums
3. Grupā Skaitlis noklikšķiniet uz nolaižamās izvēlnes Formāts
4. Noklikšķiniet uz Teksts

Tagad, kad kaut ko ievadāt atlasītajās šūnās, tas tiks uzskatīts par tekstu un netiks automātiski mainīts.

Piezīme. Iepriekš minētās darbības derēs tikai datiem, kas ievadīti pēc formatējuma maiņas. Tas nemainīs tekstu, kas ir pārveidots par datumu pirms šo formatēšanas izmaiņu veikšanas.

Vēl viens piemērs, kad tas var būt patiešām nomākts, ir, ievadot tekstu/ciparu ar nullēm. Excel automātiski noņem šīs sākuma nulles, jo uzskata, ka tās ir bezjēdzīgas. Piemēram, ja šūnā ievadāt 0001, programma Excel to nomainīs uz 1. Ja vēlaties saglabāt šīs sākuma nulles, veiciet iepriekš norādītās darbības.

Slēptās rindas/kolonnas var dot negaidītus rezultātus

Tas nav gadījums, kad formula sniedz jums nepareizu rezultātu, bet gan izmantojot nepareizu formulu.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un es vēlos iegūt visu C slejā redzamo šūnu summu.

Šūnā C12 esmu izmantojis funkciju SUM, lai iegūtu visu šo ierakstu kopējo pārdošanas vērtību.

Tik tālu, labi!

Tagad es izmantoju filtru vienumu kolonnā, lai tiktu parādīti tikai printeru pārdošanas ieraksti.

Un šeit ir problēma - formula šūnā C12 joprojām parāda to pašu rezultātu - t.i., visu ierakstu summu.

Kā jau teicu, formula nesniedz nepareizu rezultātu. Faktiski SUM funkcija darbojas lieliski.

Jautājums ir tāds, ka mēs šeit esam izmantojuši nepareizu formulu.

Funkcija SUM nevar ņemt vērā filtrētos datus un sniegt rezultātu visām šūnām (slēptām vai redzamām). Ja vēlaties iegūt tikai redzamo šūnu summu/skaitu/vidējo vērtību, izmantojiet SUBTOTAL vai AGGREGATE funkcijas.

Atslēgu līdzņemšana - Izprotiet funkcijas pareizu izmantošanu un ierobežojumus programmā Excel.

Šie ir daži no izplatītākajiem cēloņiem, kurus esmu redzējis jūsu vietā Excel formulas var nedarboties vai dot negaidītus vai nepareizus rezultātus. Esmu pārliecināts, ka ir daudz citu iemeslu, kāpēc Excel formula nedarbojas vai tiek atjaunināta.

Ja jūs zināt kādu citu iemeslu (varbūt kaut ko, kas jūs bieži satrauc), informējiet mani komentāru sadaļā.

Ceru, ka šī apmācība jums šķita noderīga!