Kako odkriti napake v kompleksnih formulah z inteligentnim mehanizmom za odpravljanje napak v Excelu

Delo s preglednicami, polnimi formul, je lahko čudovito ... dokler ne gre kaj narobe in se pojavi skrivnostna težava. Napaka v Excelu, za katero ne veste, od kod izvira in prepoznati Najpogostejše napake v Excelovih formulahKo formule postajajo daljše in bolj zapletene, je odkrivanje vira težave na oko skoraj nemogoče, veliko časa pa se porabi za preverjanje celice za celico.

Z novim inteligentnim mehanizmom za odpravljanje napak in orodji za preverjanje napak Excel ponuja vedno več pomoči za iskanje, razumevanje in popravljanje napak v kompleksnih formulahVendar pa je za kar najboljši izkoristek bistveno, da obvladate nekaj osnovnih pravil za pisanje formul, razumete pomen različnih sporočil o napakah in znate avtomatizirati množične popravke, zlasti kadar podatki, uvoženi iz drugih programov in prihajajo v različnih nenavadnih oblikah.

Osnovni koncepti, ki zagotavljajo, da formule že od samega začetka ne bodo odpovedale

Preden se poglobimo v inteligentne mehanizme in napredne razhroščevalnike, je bistveno razumeti nekaj temeljnih pravil, ki preprečujejo večino napak. Excel ima precej strog sintaksni kodeks in vsaka majhna podrobnost lahko povzroči težave. formula se interpretira kot besedilo ali pa popolnoma ne uspe.

Prva in najpomembnejša stvar je to Vsaka formula se mora začeti z znakom enačaja (=)Če izpustite znak enačaja, Excel ne razume, da želite izvesti izračun, temveč da vnašate besedilo ali datum. Če na primer vnesete SUM(A1:A10) brez znaka enačaja, bo celica dobesedno prikazala SUM(A1:A10) in ne bo izvedla nobenega izračuna. Nekaj ​​podobnega se zgodi z datumi: če vnesete 11/2 in je oblika celice Splošna, bo Excel morda prikazal 2-nov (2. november) namesto deljenja 11 z 2, ker bo interpretiral, da mu vnašate datum in ne izračuna. To je povezano z Nenavadna napaka z datumom v Excelu.

Druga ključna točka je pravilna uporaba oklepajev. Vsaka funkcija, ki jih uporablja, mora imeti dobro postavljen odprti in zaprti oklepajPri delu z vgnezdene funkcijeZelo pogosto se zgodi, da se pusti en dodaten ali manjkajoč oklepaj, kar povzroči sintaktične napake. Predstavljajte si formulo, kot je =IF(B5<0;"Ni veljavno";B5*1,05). Če pomotoma napišete =IF(B5<0;"Ni veljavno";B5*1,05)) z dodatnim zapiralnim oklepajem, formula ne bo več delovala, ker se število oklepajev ne bo ujemalo. Vedno preverite, ali je število odprtih in zaprtih oklepajev enako in ali so na pravilnem mestu, da se izognete napakam, ki jih je težko najti.

Pomembno je tudi pravilno ravnanje z obsegi. Če se želite sklicevati na več zaporednih celic, morate uporabiti dvopičje (:) med prvo in zadnjo celicoObseg je na primer zapisano kot A1:A5. Če izpustite dvopičje in dobite nekaj takega kot =SUM(A1 A5), bo Excel to interpretiral kot neveljavne reference med nepovezanimi celicami in vrnil napako. #NIČ!, ki se pojavi, kadar obstajajo neobstoječa presečišča ali nedosledne reference.

Končno je treba upoštevati število in vrstni red argumentov za vsako funkcijo. Nekatere funkcije zahtevajo obvezni argumenti in v določenem položajuNekatere funkcije zahtevajo več argumentov, druge pa dovoljujejo neobvezne parametre. Če vam zmanjka argumentov ali jih dodate več, kot je potrebno, vas bo Excel opozoril s sporočili o napakah, povezanimi s funkcijo. Poznavanje natančne sintakse vsake funkcije (število argumentov, pričakovani podatkovni tip itd.) pomaga zmanjšati težave, preden se pojavijo.

Vrste argumentov: števila, besedilo in omejitve gnezdenja

Excelove funkcije ne zahtevajo le določenega števila argumentov, ampak pričakujejo tudi, da se jim posreduje pravilen podatkovni tip v vsakem argumentuFunkcija, namenjena seštevanju števil, ni enaka funkciji, namenjeni manipulaciji z besedilom; če ju zamenjamo, so lahko rezultati nepričakovani ali celo povzročijo napako.

Na primer, funkcije, kot so SUM, AVERAGE ali PRODUCT, zahtevajo številske argumente. Če jim posredujete besedilne vrednosti tam, kjer pričakujejo številke, lahko Excel vrne 0, prezre celice ali prikaže napako, odvisno od konteksta. Nasprotno pa so funkcije, kot so REPLACE, CONCAT ali RIGHT, zasnovane za delo z besedilnimi nizi. Pri teh mora biti vsaj eden od argumentov besedilna vrednost ali sklic na celico, ki vsebuje besedilo; če število prisilite, da se obravnava kot besedilo brez ustrezne obdelave, se funkcija morda ne bo obnašala po pričakovanjih.

Poleg tega Excel postavlja znatno omejitev pri kombiniranju funkcij med seboj. Ne morete v eni sami formuli gnezdite več kot 64 ravni funkcijTo pomeni, da če imate formulo s stavki IF znotraj stavkov IF znotraj stavkov IF in tako naprej, obstaja točka, po kateri Excel ne bo več sprejemal nobenih ravni. Čeprav v praksi le malo ljudi doseže te skrajnosti, je v zelo kompleksnih ali slabo zasnovanih modelih ta meja lahko presežena, kar povzroči napake, ki jih je težko interpretirati. Po določeni stopnji kompleksnosti je običajno pametneje logiko razdeliti na več pomožnih celic, kot pa poskušati vse skoncentrirati v eni sami, izjemno dolgi formuli; za to je priporočljivo pregledati tudi Vzroki in rešitve za težave z delovanjem v Excelu.

Tipičen primer napake zaradi vrste in števila argumenta je funkcija ABS. Ta funkcija sprejema samo en sam numerični argument in vrne njegovo absolutno vrednost. Če vnesete nekaj takega kot =ABS(-2;134), bo Excel prikazal napako, ker funkcija ne ve, kaj storiti z dvema argumentoma, ločenima s podpičjem. Pravilen način bi bil nekaj takega kot =ABS(-2134) ali =ABS(A1), če celica A1 vsebuje negativno število, ki ga želite pretvoriti v pozitivno.

Po drugi strani pa je treba upoštevati uporabo ločil glede na regionalne nastavitve. V mnogih španskih namestitvah Excela se uporablja naslednje: podpičje (;) kot ločilo argumentov in vejico (,) za decimalni del. Če formula pomotoma meša vejice in podpičja na neprimernih mestih, lahko povzroči dodatne sintaktične napake, ki otežujejo berljivost in odpravljanje napak formule.

Če povzamemo, so izbira pravega podatkovnega tipa za posredovanje vsaki funkciji, upoštevanje števila argumentov, definiranih v njeni sintaksi, in ne prekoračitev omejitev gnezdenja trije temeljni stebri za zmanjšanje pojavljanja napak v zapletenih formulah.

Sklici na druge strani in knjige: kako se izogniti napakam pri povezovanju

Ko se formule začnejo povezovati z drugimi listi v istem delovnem zvezku ali celo z zunanjimi delovnimi zvezki, je precej enostavno narediti majhne tipkarske napake, ki na koncu ustvarijo neveljavna referenčna sporočila. Excel od vas potrebuje Imena listov, knjig in poti so napisana zelo natančno da bi pravilno našli podatke.

Če se v formuli sklicujete na list, katerega ime vsebuje presledke ali nečrkovne znake (številke, vezaje, simbole ...), mora biti ime vedno vključeno. v enojnih narekovajihČe imate na primer list z imenom Četrtletni podatki, bi bil pravilen sklic na celico D3 na tem listu ='Četrtletni podatki'!D3. Podobno, če se list imenuje 123, bi napisali ='123'!A1, da bi Excel razumel ime lista in ga ne zamenjal z naključnim številom.

Poleg tega je treba, kadar koli formula kaže na drug list, takoj za imenom postaviti vezaj. klicaj (!)Klicaj (```) označuje prehod med identifikatorjem lista in določeno referenco celice. Popoln primer bi bil: ='Četrtletni podatki'!D3. Če klicaj izpustite ali postavite nepravilno, bo formula neveljavna in prišlo bo do napak pri referencah.

Ko so podatki v drugem delovnem zvezku, Excel potrebuje več konteksta. V teh primerih mora zunanji sklic vsebovati ime datoteke v oklepaju, ime lista in obsegČe želite na primer prešteti število vrstic v obsegu A1:A8 delovnega zvezka Operations T2.xlsx na listu Prodaja, lahko uporabite formulo, kot je =ROWS('Prodaja'!A1:A8). Če datoteka ni odprta, morate v formuli navesti tudi celotno pot do datoteke, na primer 'C:\Moji dokumenti\Prodaja'!A1:A8.

Tipična formula v tem kontekstu bi bila nekaj takega kot =ROWS('C:\Moji dokumenti\Prodaja'!A1:A8). Ta ukaz bo vrnil število vrstic v obsegu A1:A8 v tem drugem delovnem zvezku, kar bi bilo v tem primeru 8. Če pri vnosu poti, imena delovnega zvezka, imena lista ali oglatih oklepajev naredite kakršno koli napako, bo rezultat napaka. napaka v referenci ali vrednost, ki se ne posodablja ker Excel ne more najti zunanjega vira podatkov.

Priporočljivo je tudi spremljati, kaj se zgodi, ko datoteke s povezanimi podatki premaknete, preimenujete ali izbrišete. Mehanizem za preverjanje napak lahko zazna nekatere težave, če pa zunanja datoteka ne obstaja več na pričakovani lokaciji, boste morali ročno posodobiti povezave ali na novo definirati poti, da obnovite funkcionalnost formule.

Oblikovanje števil v formulah: napake zaradi simbolov in ločil

Ena najpogostejših težav pri preglednicah z veliko podatki, zlasti če prihajajo iz drugih aplikacij, je uporaba Nepravilne oblike zapisa števil v formulahExcel jasno loči med dejansko vrednostjo, shranjeno v celici, in oblikovanjem, ki se uporablja za prikaz te vrednosti. Zamenjava teh dveh ravni pogosto vodi do subtilnih napak.

Številk v formulah ni dovoljeno oblikovati. To pomeni, da če je vrednost 1000 evrov, se mora v formuli pojaviti kot 1000, brez simbola €, ločil tisočic ali vejic. Če napišete 1.000 ali 1,000 (odvisno od regionalnih nastavitev), bo Excel to verjetno interpretiral kot ločilo argumentov ali drugačno vrednost od tiste, ki ste jo nameravali. Številke so oblikovane z ločili valut, tisočic ali decimalnih ločil. despuésUporaba možnosti oblikovanja celic, ne znotraj izraza; za več podrobnosti o pravilni uporabi oblik glejte oblika podatkov v programu Excel 365.

Predstavljajte si, da želite vsebini celice A3 prišteti 3100. Če nagonsko vnesete =SUM(3;100;A3) in mislite, da mislite 3.100, bo Excel to interpretiral kot najprej seštevanje 3 in 100, nato pa rezultatu prištel vrednost A3. Z drugimi besedami, izračunal bo (3 + 100) + A3, kar ni enako kot A3 + 3100. Pravilna formula bi bila =SUM(3100;A3), ne da bi poskušala predstaviti obliko ločila tisočic.

Nekaj ​​podobnega se dogaja s funkcijami, ki sprejemajo le določeno število argumentov, kot je na primer ABS, ki sprejema le številsko vrednost. Če poskusite v število vnesti vejice ali podpičja, da simulirate tisočice, bo Excel to štel za več ločenih argumentov in funkcija ne bo delovala. bo vrnil sintaktično napakoZato izrazi, kot je =ABS(-2;134), ne delujejo, medtem ko je =ABS(-2134) veljaven.

To je še posebej pomembno upoštevati pri uvozu podatkov iz računovodskih sistemov, ERP-jev ali programov za izdajanje računov. Mnogi od teh izvozijo zneske z oblikovanjem, simboli valut, presledki ali celo besedo »EUR« na koncu. Vse to pretvori tisto, kar bi morala biti številska vrednost, v besedilni niz, ki ga Excel ne more neposredno uporabiti v svojih izračunih, kar vodi do tipkarskih napak, praznih rezultatov ali vsot, ki se ne seštevajo.

Najboljša praksa je, da shranjene podatke hranite kot surove številke v celicah brez posebnega oblikovanja Nato v meniju za oblikovanje uporabite vizualno oblikovanje za ločilo valut, odstotkov ali tisočic. To prepreči, da bi simboli motili izračun, in omogoči mehanizmu za napake boljše zaznavanje neskladij.

Inteligentno preverjanje napak in odpravljanje napak v Excelu

Poleg popravljanja sintakse Excel vključuje tudi sistem preverjanje napak, ki analizira formule in predlaga popravke Ko zazna nekaj nenavadnega, ta vse bolj inteligentni mehanizem pomaga najti manjkajoče reference, nedoslednosti in težave s tipi v kompleksnih formulah, s čimer skrajša čas, potreben za iskanje vira napake.

V namizni različici Excela je preverjanje napak mogoče dostopati na zavihku Formule v skupini Urejanje formul, kjer se prikaže možnost Preverjanje napak. Ta čarovnik pregleda celice z opozorili in prikaže sporočila z možnimi vzroki težave ter predlaga določene spremembe, ki jih je mogoče uporabiti z enim samim klikom. Uporaben je za zaznavanje nedoslednih obsegov, formul, ki se niso posodobile ob vlečenju, sklicev na prazne celice ali nedoslednosti v skupnih vsotah.

Vendar pa v storitvi Excel Online (spletna različica) trenutno ni mogoče konfigurirati ali uporabljati teh naprednih pravil za preverjanje napak na enak način. Storitev v oblaku ponuja osnovne funkcije, vendar ne vključuje celotnega nabora pravil za pregled formul Na voljo na namizju. Zato je pri delu z zelo zapletenimi delovnimi zvezki najbolj praktično, da jih odprete z namizno aplikacijo, da v celoti izkoristite mehanizem za odpravljanje napak.

Če imate namizno različico, lahko z gumbom »Odpri z Excelom« v programu Excel Online zaženete delovni zvezek v polni aplikaciji. Ko ste tam, lahko izvedete vsa potrebna preverjanja za odkrivanje morebitnih napak v formulah, aktivirate določena pravila in pregledate morebitna opozorila, ki jih sistem prikaže. To je učinkovit način za združevanje udobja spletnega dela z močjo namizne aplikacije.

Da bi bili na tekočem z novimi zmogljivostmi mehanizma za preverjanje in izboljšavami pametnih orodij za odpravljanje napak, je priporočljivo, da občasno preverite dokumentacijo. Uradni spletni dnevnik Microsoft ExcelTukaj so objavljene novice, posodobitve in spremembe namizne in spletne različice, kar vam pomaga vedeti, kdaj so na voljo nove funkcije za odpravljanje napak.

Če potrebujete celovitejši dostop do vseh Officeovih aplikacij (Word, Excel, PowerPoint itd.) in z njimi povezanih storitev, lahko vedno preizkusite ali kupite celoten paket prek spletnega mesta Office.com, s čimer boste imeli na voljo najnaprednejša orodja za preverjanje napak v vseh preglednicah.

Napake, ki jih povzročajo številke, shranjene kot besedilo, in kako jih popraviti v večjem obsegu

Zelo pogost scenarij, zlasti v računovodskih ali finančnih okoljih, se pojavi pri izvozu podrobne glavne knjige (podrobne glavne knjige) iz programske opreme za upravljanje. V mnogih primerih te vrste aplikacij Številske zneske izvozijo kot besediloPosledica tega je, da ko datoteko odprete v Excelu, formule, ki poskušajo sešteti, odšteti ali analizirati ta polja, ne prepoznajo vrednosti kot števil, operacije pa ne uspejo ali pa povzročijo napačne rezultate.

Vgrajeno preverjanje napak v Excelu lahko zazna to težavo in v kotu celic prikaže klasični zeleni trikotnik, ki označuje, da je »številka shranjena kot besedilo«. S klikom na ikono opozorila Excelu omogočite pretvorbo besedila v številko. Slaba stran je, da se popravek običajno uporabi, če uporabite orodje za preverjanje napak na zavihku Formule > Urejanje formul > Preverjanje napak. celica za celicokar je v GL s tisoči vrsticami lahko noro.

Poleg tega se preverjanje napak, če je izbran celoten dokument, ne osredotoča zgolj na številske stolpce, saj lahko tudi drugi stolpci, ki vsebujejo datume, besedilo in opise, sprožijo opozorila, kar še dodatno zaplete postopek množičnega popravljanja. Logično vprašanje v tem kontekstu je, ali obstaja način, da izberemo celoten list in Excel popravi vse te napake tako, da besedilo naenkrat pretvorimo v dejanske številke.

Obstaja več strategij, ki jih je mogoče uporabiti neposredno iz namiznega programa Excel za reševanje tovrstnih situacij. Ena najbolj neposrednih vključuje uporabo posebno lepilo z matematično operacijoNa primer, lahko v prazno celico vnesete številko 1, kopirate to celico, izberete obseg celic, ki vsebujejo številke, shranjene kot besedilo (na primer celoten stolpec z zneski), in uporabite Posebno lepljenje > Množenje. Ta operacija povzroči, da Excel vsak vnos »besedila« interpretira kot številko, ga pomnoži z 1 in rezultat shrani kot realno številsko vrednost, pri čemer ohrani enak znesek. To je zelo učinkovit način za pretvorbo velikih količin podatkov v velikem obsegu.

Druga možnost je uporaba funkcije VALUE v pomožnem stolpcu. Če so na primer zneski besedila v stolpcu B, lahko v stolpcu C ustvarite formulo, kot je =VALUE(B2), jo kopirate navzdol in nato rezultate kopirate in prilepite kot vrednosti v prvotni stolpec, s čimer nadomestite besedilo. Ta tehnika vam omogoča tudi vizualno preverjanje, ali se vse ujema, preden izbrišete prvotne podatke.

V nekaterih primerih opozorila »število shranjeno kot besedilo« omogočajo, da v kontekstnem meniju majhnega zelenega trikotnika izberete širši obseg in pretvorbo uporabite za več celic hkrati. Vendar pa je njihova učinkovitost odvisna od tega, kako so bili podatki ustvarjeni in ali Excel vse primere zazna kot isto vrsto napake.

Pri delu z velikimi količinami računovodskih podatkov je priporočljivo pregledati tudi regionalne nastavitve programa Excel in obliko izvoza izvornega programa. Prilagajanje teh parametrov za zagotovitev združljivosti decimalnih ločil, simbolov valut in oblik datuma močno zmanjša pojav številk v besedilni obliki in pogostih napak ter omogoča pravilno delovanje formul že od samega začetka.

Skratka, čeprav standardno preverjanje napak običajno deluje vrstico za vrstico, je mogoče kombinirati njegova opozorila s tehnikami, kot so posebne funkcije lepljenja in pretvorbe. za množično popravljanje velikih blokov napačno oblikovanih podatkov in obnoviti njihov status kot številk, ne da bi morali iti celico za celico.

Celoten sklop priporočil glede enačaja, oklepajev, obsegov, vrst argumentov, oblikovanja števil, zunanjih referenc in orodij za preverjanje napak ima skupni cilj: omogočiti Excelovemu novemu inteligentnemu mehanizmu za odpravljanje napak, da deluje z največjo učinkovitostjo. Ko so formule dobro sestavljene in podatki uporabljajo pravilno vrsto in obliko zapisa, sistem opozoril in predlogov postane močan zaveznik za hitro iskanje neskladij, nedelujočih referenc ali problematičnih celic, tudi v delovnih listih z zelo zapletenimi formulami in podatki, uvoženimi iz več virov.

Povezani članek:

Najpogostejše napake v Excelovih formulah in kako jih odpraviti

Isaac

Strasten pisec o svetu bajtov in tehnologije nasploh. Rad delim svoje znanje s pisanjem in to je tisto, kar bom počel v tem blogu, saj vam bom pokazal vse najbolj zanimive stvari o pripomočkih, programski opremi, strojni opremi, tehnoloških trendih in še več. Moj cilj je, da vam pomagam krmariti po digitalnem svetu na preprost in zabaven način.