Kuidas tuvastada vigu keerukates valemites Exceli intelligentse silumismootori abil

Valemitest tulvil arvutustabelitega töötamine võib olla imeline ... kuni midagi läheb valesti ja ilmneb salapärane probleem. Exceli viga, mille päritolu te ei tea ja tunnistada Kõige levinumad vead Exceli valemitesValemite pikemaks ja keerukamaks muutudes muutub probleemi allika silma järgi tuvastamine peaaegu võimatuks ning lahtri haaval kontrollimisele kulub palju aega.

Uue intelligentse silumismootori ja veakontrolli tööriistadega pakub Excel üha rohkem abi keeruliste valemite vigade leidmine, mõistmine ja parandamineNendest maksimumi võtmiseks on aga oluline omandada mõned valemite kirjutamise põhireeglid, mõista erinevate veateadete tähendust ja teada, kuidas automatiseerida hulgiparandusi, eriti kui teistest programmidest imporditud andmed ja need tulevad mitmesugustes ebatavalistes vormingutes.

Põhimõisted, mis tagavad valemite ebaõnnestumise algusest peale

Enne intelligentsete mootorite ja täiustatud silujate juurde sukeldumist on oluline mõista mõningaid põhireegleid, mis aitavad vältida enamikku vigu. Excel on süntaksi suhtes üsna range ja iga väike detail võib probleeme tekitada. Valemit tõlgendatakse tekstina või see ebaõnnestub täielikult.

Esimene ja kõige tähtsam on see Iga valem peab algama võrdusmärgiga (=)Kui võrdusmärk puudub, ei saa Excel aru, et soovite teha arvutust, vaid et sisestate teksti või kuupäeva. Näiteks kui tippite SUM(A1:A10) ilma võrdusmärgita, kuvab lahter sõna-sõnalt SUM(A1:A10) ja ei tee ühtegi arvutust. Midagi sarnast juhtub kuupäevadega: kui tippite 11/2 ja lahtri vorming on Üldine, võib Excel kuvada 2-nov (2. november) 11 jagamise asemel 2-ga, kuna see tõlgendab seda nii, nagu sisestaksite kuupäeva, mitte arvutuse. See on seotud Exceli kummaline kuupäevatõrge.

Teine oluline punkt on sulgude õige kasutamine. Igal funktsioonil, mis neid kasutab, peab olema hästi paigutatud avav ja sulgev sulguTöötades koos pesastatud funktsioonidÜhe lisa- või puuduva sulgu jätmine on väga tavaline, mis põhjustab süntaksivigu. Kujutage ette valemit nagu =IF(B5<0;"Kehtimatu";B5*1,05). Kui kirjutate kogemata =IF(B5<0;"Kehtimatu";B5*1,05)) koos täiendava sulguga, siis valem lakkab töötamast, kuna sulgude arv ei ühti. Raskesti leitavate vigade vältimiseks on oluline alati kontrollida, et ava- ja sulgemissulge oleks sama palju ja et need oleksid õiges asendis.

Samuti on oluline vahemikke õigesti käsitleda. Mitmele järjestikusele lahtrile viitamiseks peate kasutama koolon (:) esimese ja viimase lahtri vahelVahemik kirjutatakse näiteks kujul A1:A5. Kui koolon ära jätta ja tulemuseks on midagi sellist nagu =SUM(A1 A5), tõlgendab Excel seda sobimatute viidetena omavahel mitteseotud lahtrite vahel ja tagastab vea. #NULL!, mis kuvatakse olematute ristumiste või vastuoluliste viidete korral.

Lõpuks tuleb iga funktsiooni puhul arvestada argumentide arvu ja järjekorda. Mõned funktsioonid nõuavad kohustuslikud argumendid ja kindlas positsioonisMõned funktsioonid nõuavad mitut argumenti, teised aga lubavad valikulisi parameetreid. Kui argumentidest saavad otsa või lisate neid rohkem kui vaja, hoiatab Excel teid funktsiooniga seotud veateadetega. Iga funktsiooni täpse süntaksi (argumentide arv, eeldatav andmetüüp jne) tundmine aitab probleeme enne nende tekkimist minimeerida.

Argumentide tüübid: arvud, tekst ja pesastamise piirangud

Exceli funktsioonid mitte ainult ei nõua kindlat arvu argumente, vaid eeldavad ka nende edastamist. õige andmetüüp igas argumendisArvude liitmiseks mõeldud funktsioon ei ole sama mis teksti manipuleerimiseks mõeldud funktsioon; kui need omavahel ära vahetada, võivad tulemused olla ootamatud või isegi põhjustada vea.

Näiteks funktsioonid nagu SUM, AVERAGE või PRODUCT vajavad numbrilisi argumente. Kui edastate neile tekstiväärtused seal, kus nad ootavad numbreid, võib Excel olenevalt kontekstist tagastada väärtuse 0, ignoreerida lahtreid või kuvada vea. Seevastu funktsioonid nagu REPLACE, CONCAT või RIGHT on loodud töötama tekstistringidega. Nendes peab vähemalt üks argumentidest olema tekstiväärtus või viide teksti sisaldavale lahtrile; kui sunnite arvu tekstina käsitlema ilma korraliku käsitluseta, ei pruugi funktsioon ootuspäraselt käituda.

Lisaks seab Excel funktsioonide omavahelisele kombineerimisele märkimisväärse piirangu. Te ei saa pesastada ühte valemisse rohkem kui 64 funktsioonitasetSee tähendab, et kui teil on valem, kus IF-laused on IF-lausete sees IF-lausete sees jne, siis on olemas punkt, millest alates Excel enam tasemeid ei aktsepteeri. Kuigi praktikas jõuavad vähesed inimesed nende äärmusteni, võib väga keerukates või halvasti kavandatud mudelites see piir ületada, põhjustades raskesti tõlgendatavaid vigu. Teatud keerukusastmest alates on tavaliselt targem jagada loogika mitmeks abirakuks, selle asemel, et proovida kõike koondada ühte äärmiselt pikka valemisse; selleks on soovitatav üle vaadata ka Exceli jõudlusprobleemide põhjused ja lahendused.

Tüüpiline näide argumendi tüübist ja arvust tingitud veast on ABS-funktsioon. See funktsioon aktsepteerib ainult üks numbriline argument ja tagastab selle absoluutväärtuse. Kui sisestate midagi sellist nagu =ABS(-2;134), kuvab Excel vea, kuna funktsioon ei tea, mida teha kahe semikooloniga eraldatud argumendiga. Õige oleks midagi sellist nagu =ABS(-2134) või =ABS(A1), kui lahter A1 sisaldab negatiivset arvu, mille soovite positiivseks teisendada.

Teisest küljest tuleb eraldajate kasutamist arvestada piirkondlikest sätetest olenevalt. Paljudes Exceli hispaaniakeelsetes installides kasutatakse järgmist: semikoolon (;) argumendi eraldajana ja koma (,) kümnendmurru jaoks. Kui valemis on komad ja semikoolonid kogemata valedes kohtades segamini, võib see tekitada täiendavaid süntaksivigu, mis raskendavad valemi loetavust ja silumist.

Kokkuvõttes on igale funktsioonile edastatava õige andmetüübi valimine, süntaksis määratletud argumentide arvu austamine ja pesastamispiirangute mitte ületamine kolm peamist sammast keerukate valemite vigade esinemise vähendamiseks.

Viited teistele lehtedele ja raamatutele: kuidas vältida linkimisvigu

Kui valemid hakkavad linkima sama töövihiku teistele lehtedele või isegi välistele töövihikutele, on üsna lihtne teha väikeseid trükivigu, mis lõpuks genereerivad sobimatuid viiteteateid. Excel vajab teid Lehtede, raamatute ja marsruutide nimed on kirjutatud väga täpselt andmete õigeks leidmiseks.

Kui valemis viidatakse lehele, mille nimi sisaldab tühikuid või mitte-tähti (numbreid, sidekriipse, sümboleid...), tuleb nimi alati lisada. üksikjutumärkidesNäiteks kui teil on leht nimega Kvartaliandmed, siis õige viide lahtrile D3 sellel lehel oleks ='Kvartaliandmed'!D3. Samamoodi, kui lehe nimi on 123, peaksite kirjutama ='123'!A1, et Excel mõistaks lehe nime ega ajaks seda segi juhusliku arvuga.

Lisaks tuleb alati, kui valem viitab teisele lehele, nime järele lisada sidekriips. hüüumärk (!)Hüüumärk (```) tähistab üleminekut lehe identifikaatori ja konkreetse lahtri viite vahel. Täielik näide oleks: ='Kvartaliandmed'!D3. Kui hüüumärk puudub või on valesti paigutatud, on valem kehtetu ja tekivad viitevead.

Kui teave asub teises töövihikus, vajab Excel rohkem konteksti. Sellistel juhtudel peab väline viide sisaldama järgmist sulgudes olev failinimi, lehe nimi ja vahemikNäiteks kui soovite loendada töövihiku Toimingud T2.xlsx vahemikus A1:A8 olevate ridade arvu müügilehel, saate kasutada valemit =READ('Müük'!A1:A8). Kui fail pole avatud, peate valemis sisestama ka täieliku failitee, näiteks 'C:\Minu dokumendid\Müük'!A1:A8'.

Tüüpiline valem selles kontekstis oleks midagi sellist nagu =READ('C:\Minu dokumendid\Müük'!A1:A8). See käsk tagastab teises töövihikus vahemiku A1:A8 ridade arvu, mis antud juhul oleks 8. Kui tee, töövihiku nime, lehe nime või nurksulgude sisestamisel tehakse viga, on tulemuseks viga. viiteviga või väärtus, mis ei uuene sest Excel ei leia välist andmeallikat.

Samuti on soovitatav jälgida, mis juhtub lingitud andmeid sisaldavate failide teisaldamisel, ümbernimetamisel või kustutamisel. Veakontrollimootor võib tuvastada mõningaid probleeme, kuid kui väline fail enam oodatud asukohas ei eksisteeri, peate valemi funktsionaalsuse taastamiseks linke käsitsi värskendama või teed uuesti määratlema.

Numbrite vormindamine valemites: sümbolite ja eraldajate tõttu tekkinud vead

Üks levinumaid probleeme suure hulga andmetega arvutustabelites, eriti kui andmed pärinevad teistest rakendustest, on Valemites olevad valed arvuvormingudExcel eristab selgelt lahtris talletatud tegelikku väärtust ja selle väärtuse kuvamiseks kasutatavat vormingut. Nende kahe taseme segi ajamine toob sageli kaasa peeneid vigu.

Numbreid ei tohiks valemites vormindada. See tähendab, et kui väärtus on 1000 eurot, peab see valemis kuvama kujul 1000, ilma € sümboli, tuhandeliste eraldajate või komadeta. Kui kirjutate 1.000 või 1,000 (sõltuvalt piirkondlikest sätetest), tõlgendab Excel seda tõenäoliselt argumendi eraldajana või erineva väärtusena kui see, mida te kavatsesite. Arvud vormindatakse valuuta, tuhandeliste eraldajate või kümnendkohtade eraldajatega. pärastLahtri vormindusvalikute kasutamine, mitte avaldise sees; lisateavet vormingute õige rakendamise kohta leiate jaotisest andmevorming Excel 365-s.

Kujutage ette, et soovite lahtri A3 sisule liita arvu 3100. Kui sisestate instinktiivselt =SUM(3;100;A3), arvates, et peate silmas arvu 3.100, tõlgendab Excel seda nii, et kõigepealt liidate 3 ja 100 ning seejärel liidate tulemusele lahtri A3 väärtuse. Teisisõnu, see arvutab (3 + 100) + A3, mis ei ole sama mis A3 + 3100. Õige valem oleks =SUM(3100;A3), ilma tuhandeliste eraldaja vormingut proovimata.

Midagi sarnast juhtub ka funktsioonidega, mis aktsepteerivad ainult teatud arvu argumente, näiteks ABS, mis aktsepteerib ainult arvväärtusi. Kui proovite tuhandete simuleerimiseks arvu sisse kirjutada koma või semikooloni, käsitleb Excel seda mitme eraldi argumendina ja funktsioon ei tööta. tagastab süntaksiveaSeega avaldised nagu =ABS(-2;134) ei tööta, samas kui =ABS(-2134) on kehtivad.

See on eriti oluline meeles pidada andmete importimisel raamatupidamissüsteemidest, ERP-dest või arveldusprogrammidest. Paljud neist ekspordivad summasid vormingu, valuutasümbolite, tühikute või isegi sõnaga "EUR" lõpus. Kõik see muudab numbrilise väärtuse tekstistringiks, mida Excel ei saa oma arvutustes otse kasutada, mis põhjustab trükivigu, tühje tulemusi või summasid, mis ei klapi.

Parim tava on hoida salvestatud andmeid sellisena, nagu toorarvud lahtrites ilma spetsiaalse vorminduseta Seejärel rakendage vormindusmenüüst valuuta, protsendi või tuhandete eraldaja visuaalne vorming. See hoiab ära sümbolite segamise arvutustes ja võimaldab veamootoril paremini tuvastada vastuolusid.

Intelligentne veakontroll ja silumine Excelis

Lisaks süntaksi parandamisele sisaldab Excel ka süsteemi veakontroll, mis analüüsib valemeid ja soovitab parandusi Kui see üha intelligentsem mootor tuvastab midagi ebatavalist, aitab see leida puuduvaid viiteid, vastuolusid ja tüübiprobleeme keerukates valemites, vähendades veaallika leidmiseks kuluvat aega.

Exceli töölauaversioonis pääseb veakontrollile ligi menüü Valemid kaudu jaotises Valemi redigeerimine, kus kuvatakse suvand Veakontroll. See viisard skannib lahtreid hoiatustega ja kuvab teateid probleemi võimalike põhjustega ning soovitab konkreetseid muudatusi, mida saab rakendada ühe klõpsuga. See on kasulik ebajärjekindlate vahemike, lohistamisel mittevärskendatud valemite, tühjade lahtrite viidete või kokkuvõtete ebajärjekindluse tuvastamiseks.

Excel Online'is (veebiversioonis) ei ole aga praegu võimalik neid täiustatud veakontrolli reegleid samal viisil konfigureerida ega kasutada. Pilveteenus pakub põhifunktsioone, kuid ei sisalda valemi ülevaatamise reeglite täielikku komplekti Saadaval töölaual. Seetõttu on väga keerukate töövihikutega töötamisel kõige otstarbekam need avada töölauarakendusega, et silumismootorit täiel määral ära kasutada.

Kui teil on töölauaversioon, saate Excel Online'is töövihiku täisversiooni avamiseks kasutada nuppu „Ava Exceliga“. Seal saate teha kõik vajalikud kontrollid võimalike valemivigade tuvastamiseks, aktiveerida konkreetseid reegleid ja vaadata üle kõik süsteemi kuvatavad hoiatused. See on tõhus viis ühendada võrgus töötamise mugavus töölauarakenduse võimsusega.

Uute verifitseerimismootori võimaluste ja nutikate silumistööriistade täiustuste kursis püsimiseks on soovitatav aeg-ajalt dokumentatsiooni kontrollida. Microsoft Exceli ametlik ajaveebSiin avaldatakse uudiseid, värskendusi ja muudatusi töölaua- ja veebiversioonides, mis aitavad teil teada saada, millal on saadaval uued veaparandusfunktsioonid.

Kui vajate ulatuslikumat juurdepääsu kõigile Office'i rakendustele (Word, Excel, PowerPoint jne) ja nendega seotud teenustele, saate alati proovida või osta kogu komplekti Office.com-i kaudu, tagades, et teil on kõigi arvutustabelite jaoks kõige kaasaegsemad veakontrolli tööriistad.

Tekstina salvestatud numbrite põhjustatud vead ja kuidas neid hulgi parandada

Väga levinud stsenaarium, eriti raamatupidamis- või finantskeskkondades, tekib detailse pearaamatu (detailse GL) eksportimisel haldustarkvarast. Paljudel juhtudel on seda tüüpi rakendused Nad ekspordivad numbrilised summad tekstinaTulemuseks on see, et faili Excelis avamisel ei tuvasta valemid, mis üritavad neid välju liita, lahutada või analüüsida, väärtusi numbritena ning toimingud ebaõnnestuvad või annavad valesid tulemusi.

Exceli sisseehitatud veakontroll suudab selle probleemi tuvastada ja kuvada lahtrite nurgas klassikalise rohelise kolmnurga, mis näitab, et "number on salvestatud tekstina". Hoiatusikooni klõpsamine annab Excelile võimaluse tekst numbriks teisendada. Puuduseks on see, et kui kasutate veakontrolli tööriista vahekaardil Valemid > Valemite redigeerimine > Veakontroll, rakendatakse parandus tavaliselt vaikimisi. rakk raku haavalmis tuhandete ridadega GL-is võib olla hullumeelne.

Lisaks, kui kogu dokument on valitud, ei keskendu veakontroll ainult numbrilistele veergudele, kuna ka teised kuupäevade, teksti ja kirjeldustega veerud võivad käivitada hoiatusi, mis raskendab hulgiparandusprotsessi veelgi. Loogiline küsimus selles kontekstis on, kas on võimalik valida kogu leht ja lasta Excelil kõik need vead parandada, teisendades teksti ühe hoobiga tegelikeks numbriteks.

Selliste olukordade lahendamiseks on mitu strateegiat, mida saab otse töölaua Excelist rakendada. Üks otsesemaid hõlmab järgmist: spetsiaalne liim matemaatilise tehtegaNäiteks võite tippida tühja lahtrisse arvu 1, kopeerida selle lahtri, valida tekstina salvestatud numbreid sisaldava lahtrite vahemiku (näiteks kogu summade veeru) ja kasutada käsku Kleebi teisiti > Korruta. See toiming paneb Exceli iga tekstikirjet arvuna tõlgendama, selle 1-ga korrutama ja tulemuse reaalse arvväärtusena salvestama, säilitades sama summa. See on väga tõhus viis suurte andmemahtude hulgi teisendamiseks.

Teine võimalus on kasutada VALUE funktsiooni abiveerus. Kui näiteks tekstisummad on veerus B, saate luua C veerus valemi, näiteks =VALUE(B2), kopeerida selle alla ja seejärel kopeerida ja kleepida tulemused väärtustena algsesse veergu, asendades teksti. See tehnika võimaldab teil enne algsete andmete kustutamist visuaalselt kontrollida, kas kõik sobib.

Mõnel juhul võimaldavad hoiatused „number salvestatud tekstina” valida laiema vahemiku ja rakendada teisendust korraga mitmele lahtrile väikese rohelise kolmnurga kontekstimenüüst. Nende tõhusus sõltub aga sellest, kuidas andmed genereeriti ja kas Excel tuvastab kõik juhtumid sama tüüpi veana.

Suure hulga raamatupidamisandmetega töötades on soovitatav üle vaadata ka Exceli piirkondlikud sätted ja lähteprogrammi ekspordivorming. Nende parameetrite kohandamine kümnenderaldajate, valuutasümbolite ja kuupäevavormingute ühilduvuse tagamiseks vähendab oluliselt numbrite esinemist tekstivormingus ja laialt levinud vigu ning võimaldab valemitel algusest peale õigesti toimida.

Lühidalt, kuigi standardne veakontroll töötab tavaliselt rida-realt, on selle hoiatuste kombineerimine selliste tehnikatega nagu spetsiaalsed kleepimis- ja teisendusfunktsioonid võimalik. suurte valesti vormindatud andmete plokkide hulgi parandamiseks ja taastada nende numbriline staatus ilma lahtrit lahtri haaval läbi töötamata.

Kõik need soovitused võrdusmärgi, sulgude, vahemike, argumentide tüüpide, arvuvormingu, väliste viidete ja veakontrolli tööriistade kohta jagavad ühist eesmärki: võimaldada Exceli uuel intelligentsel silumismootoril töötada maksimaalse efektiivsusega. Kui valemid on hästi üles ehitatud ja andmed kasutavad õiget tüüpi ja vormingut, saab teadete ja soovituste süsteemist võimas liitlane vastuolude, katkiste viidete või problemaatiliste lahtrite kiireks leidmiseks isegi töölehtedel, mis sisaldavad väga keerulisi valemeid ja mitmest allikast imporditud andmeid.

Seotud artikkel:

Kõige levinumad vead Exceli valemites ja kuidas neid parandada

Isaac

Kirglik kirjanik baitide maailmast ja üldse tehnoloogiast. Mulle meeldib jagada oma teadmisi kirjutamise kaudu ja just seda ma selles ajaveebis teengi, näitan teile kõike kõige huvitavamat vidinate, tarkvara, riistvara, tehnoloogiliste suundumuste ja muu kohta. Minu eesmärk on aidata teil digimaailmas lihtsal ja meelelahutuslikul viisil navigeerida.