Kā atklāt kļūdas sarežģītās formulās, izmantojot Excel viedās atkļūdošanas programmu

Darbs ar izklājlapām, kas pilnas ar formulām, var būt brīnišķīgs… līdz brīdim, kad kaut kas noiet greizi un parādās noslēpumaina problēma. Kļūda programmā Excel, kuras cēlonis nav zināms un atpazīt Visbiežāk sastopamās kļūdas Excel formulāsFormulām kļūstot garākām un sarežģītākām, problēmas avota noteikšana ar aci kļūst gandrīz neiespējama, un daudz laika tiek tērēts, pārbaudot katru šūnu atsevišķi.

Ar jauno viedās atkļūdošanas programmu un kļūdu pārbaudes rīkiem Excel piedāvā arvien vairāk palīdzības atrast, izprast un labot kļūdas sarežģītās formulāsTomēr, lai no tiem gūtu maksimālu labumu, ir svarīgi apgūt dažus formulu rakstīšanas pamatnoteikumus, saprast dažādu kļūdu ziņojumu nozīmi un zināt, kā automatizēt masveida labojumus, īpaši, ja dati, kas importēti no citām programmām un tie ir pieejami dažādos neparastos formātos.

Pamatjēdzieni, lai nodrošinātu, ka formulas jau no paša sākuma nekļūdās

Pirms pievērsties viedajiem dzinējiem un uzlabotiem atkļūdotājiem, ir svarīgi saprast dažus pamatnoteikumus, kas novērš lielāko daļu kļūdu. Excel ir diezgan stingra attiecībā uz sintaksi, un jebkura maza detaļa var radīt problēmas. formula tiek interpretēta kā teksts vai pilnībā neizdodas.

Pirmā un vissvarīgākā lieta ir tā Katrai formulai jāsākas ar vienādības zīmi (=)Ja vienādības zīme ir izlaista, programma Excel nesaprot, ka vēlaties veikt aprēķinu, bet gan to, ka ievadāt tekstu vai datumu. Piemēram, ja ierakstāt SUM(A1:A10) bez vienādības zīmes, šūnā burtiski tiks parādīts SUM(A1:A10) un netiks veikts neviens aprēķins. Kaut kas līdzīgs notiek ar datumiem: ja ierakstāt 11/2 un šūnas formāts ir Vispārīgi, programma Excel var parādīt 2-nov (2. novembris), nevis dalīt 11 ar 2, jo tā interpretē, ka norādāt datumu, nevis aprēķinu. Tas ir saistīts ar Excel neparastā datuma kļūme.

Vēl viens svarīgs aspekts ir pareiza iekavu lietošana. Katrai funkcijai, kas tās izmanto, ir jābūt labi novietota atverošā un aizverošā iekavaStrādājot ar ligzdotas funkcijasĻoti bieži tiek atstāta viena lieka vai viena trūkstoša iekava, kas rada sintakses kļūdas. Iedomājieties formulu, piemēram, =IF(B5<0;"Nav derīgs";B5*1,05). Ja nejauši ierakstāt =IF(B5<0;"Nav derīgs";B5*1,05)) ar papildu aizverošo iekavu, formula pārstās darboties, jo iekavu skaits nesakritīs. Vienmēr pārbaudiet, vai ir vienāds atverošo un aizverošo iekavu skaits un vai tās atrodas pareizajā pozīcijā, lai izvairītos no grūti atrodamām kļūdām.

Ir svarīgi arī pareizi apstrādāt diapazonus. Lai atsauktos uz vairākām secīgām šūnām, jāizmanto kols (:) starp pirmo un pēdējo šūnuDiapazons tiek rakstīts, piemēram, kā A1:A5. Ja izlaižat kolu un iegūstat kaut ko līdzīgu =SUM(A1 A5), Excel to interpretēs kā nederīgas atsauces starp nesaistītām šūnām un atgriezīs kļūdu. #NULL!, kas parādās, ja nav krustpunktu vai ir nekonsekventas atsauces.

Visbeidzot, ir jāievēro katras funkcijas argumentu skaits un secība. Dažām funkcijām ir nepieciešams obligāti argumenti un konkrētā pozīcijāDažām funkcijām ir nepieciešami vairāki argumenti, savukārt citas atļauj papildu parametrus. Ja jums pietrūkst argumentu vai pievienojat vairāk nekā nepieciešams, Excel brīdinās ar kļūdas ziņojumiem, kas saistīti ar funkciju. Zinot katras funkcijas precīzu sintakses sintakses (argumentu skaits, paredzamais datu tips utt.), var samazināt problēmas, pirms tās rodas.

Argumentu veidi: skaitļi, teksts un ligzdošanas ierobežojumi

Excel funkcijām ne tikai ir nepieciešams noteikts argumentu skaits, bet tās arī sagaida, ka tām tiks nodots pareizs datu tips katrā argumentāFunkcija, kas paredzēta skaitļu saskaitīšanai, nav tas pats, kas funkcija, kas paredzēta teksta apstrādei; ja tās tiek mainītas vietām, rezultāti var būt negaidīti vai pat radīt kļūdu.

Piemēram, tādām funkcijām kā SUM, AVERAGE vai PRODUCT ir nepieciešami skaitliski argumenti. Ja tām nododat teksta vērtības tur, kur tās sagaida skaitļus, Excel var atgriezt 0, ignorēt šūnas vai parādīt kļūdu atkarībā no konteksta. Turpretī tādas funkcijas kā REPLACE, CONCAT vai RIGHT ir paredzētas darbam ar teksta virknēm. Šajās funkcijās vismaz vienam no argumentiem ir jābūt teksta vērtībai vai atsaucei uz šūnu, kurā ir teksts; ja piespiedīsiet skaitli apstrādāt kā tekstu bez atbilstošas ​​apstrādes, funkcija var nedarboties, kā paredzēts.

Turklāt programmā Excel ir ievērojami ierobežojumi funkciju apvienošanai savā starpā. Jūs nevarat vienā formulā ievietot vairāk nekā 64 funkciju līmeņusTas nozīmē, ka, ja jums ir formula ar IF priekšrakstiem IF priekšrakstu iekšpusē IF priekšrakstu iekšpusē utt., ir robeža, pēc kuras Excel vairs nepieņems citus līmeņus. Lai gan praksē tikai retais sasniedz šīs galējības, ļoti sarežģītos vai slikti izstrādātos modeļos šo robežu var pārsniegt, radot kļūdas, kuras ir grūti interpretēt. Pārsniedzot noteiktu sarežģītības līmeni, parasti ir prātīgāk sadalīt loģiku vairākās palīgšūnās, nevis mēģināt visu koncentrēt vienā, ārkārtīgi garā formulā; šim nolūkam ieteicams arī pārskatīt Excel veiktspējas problēmu cēloņi un risinājumi.

Tipisks kļūdas piemērs argumenta tipa un skaitļa dēļ ir ABS funkcija. Šī funkcija pieņem tikai viens skaitlisks arguments un atgriež tā absolūto vērtību. Ja ierakstīsiet kaut ko līdzīgu =ABS(-2;134), programma Excel parādīs kļūdu, jo funkcija nezina, ko darīt ar diviem argumentiem, kas atdalīti ar semikolu. Pareizais veids būtu kaut kas līdzīgs =ABS(-2134) vai =ABS(A1), ja šūnā A1 ir negatīvs skaitlis, kuru vēlaties pārvērst par pozitīvu.

No otras puses, atkarībā no reģionālajiem iestatījumiem jāņem vērā atdalītāju lietošana. Daudzās Excel instalācijās Spānijā tiek izmantots šāds teksts: semikols (;) kā argumentu atdalītājs un komats (,) decimāldaļai. Ja formulā kļūdaini komati un semikoli tiek sajaukti nepareizās pozīcijās, tā var radīt papildu sintakses kļūdas, kas sarežģī formulas lasāmību un atkļūdošanu.

Rezumējot, pareizā datu tipa izvēle, ko nodot katrai funkcijai, tās sintaksē definētā argumentu skaita ievērošana un ligzdošanas ierobežojumu nepārsniegšana ir trīs pamatprincipi kļūdu rašanās samazināšanai sarežģītās formulās.

Atsauces uz citām lapām un grāmatām: kā izvairīties no saišu kļūdām

Kad formulas sāk veidot saites uz citām lapām tajā pašā darbgrāmatā vai pat uz ārējām darbgrāmatām, ir diezgan viegli pieļaut nelielas drukas kļūdas, kas galu galā ģenerē nederīgus atsauču ziņojumus. Programmai Excel ir nepieciešams, lai jūs Lapu, grāmatu un maršrutu nosaukumi ir uzrakstīti ļoti precīzi lai pareizi atrastu datus.

Ja formulā jūs atsaucaties uz lapu, kuras nosaukumā ir atstarpes vai rakstzīmes, kas nav burti (cipari, defises, simboli…), nosaukums vienmēr ir jāiekļauj. vienpēdiņāsPiemēram, ja jums ir lapa ar nosaukumu “Ceturkšņa dati”, pareizā atsauce uz šūnu D3 šajā lapā būtu ='Ceturkšņa dati'!D3. Līdzīgi, ja lapas nosaukums ir 123, jums jāraksta ='123'!A1, lai Excel saprastu lapas nosaukumu un nejauktu to ar nejaušu skaitli.

Turklāt, ja formula norāda uz citu lapu, tūlīt aiz nosaukuma ir jāievieto defise. izsaukuma zīme (!)Izsaukuma zīme (```) norāda pāreju starp lapas identifikatoru un konkrēto šūnas atsauci. Pilnīgs piemērs būtu: ='Ceturkšņa dati'!D3. Ja izsaukuma zīme ir izlaista vai novietota nepareizi, formula būs nederīga un radīsies atsauču kļūdas.

Ja informācija atrodas citā darbgrāmatā, programmai Excel ir nepieciešams vairāk konteksta. Šādos gadījumos ārējā atsaucē ir jāiekļauj faila nosaukums iekavās, lapas nosaukums un diapazonsPiemēram, ja vēlaties saskaitīt rindu skaitu darbgrāmatas Operations T2.xlsx diapazonā A1:A8, pārdošanas lapā varat izmantot formulu =ROWS('Pārdošana'!A1:A8). Ja fails nav atvērts, formulā ir jānorāda arī pilns faila ceļš, piemēram, 'C:\My Documents\Sales'!A1:A8'.

Tipiska formula šajā kontekstā būtu kaut kas līdzīgs =ROWS('C:\My Documents\Sales'!A1:A8). Šī instrukcija atgriezīs rindu skaitu diapazonā A1:A8 šajā citā darbgrāmatā, kas šajā gadījumā būtu 8. Ja, ierakstot ceļu, darbgrāmatas nosaukumu, lapas nosaukumu vai kvadrātiekavas, tiek pieļauta kļūda, rezultāts būs kļūda. atsauces kļūda vai vērtība, kas netiek atjaunināta jo Excel nevar atrast ārējo datu avotu.

Ieteicams arī uzraudzīt, kas notiek, ja faili, kas satur saistītus datus, tiek pārvietoti, pārdēvēti vai dzēsti. Kļūdu pārbaudes programma var atklāt dažas problēmas, taču, ja ārējais fails vairs nepastāv paredzētajā atrašanās vietā, jums būs manuāli jāatjaunina saites vai jāpārdefinē ceļi, lai atjaunotu formulas funkcionalitāti.

Skaitļu formatēšana formulās: kļūdas simbolu un atdalītāju dēļ

Viena no visbiežāk sastopamajām problēmām izklājlapās ar lielu datu apjomu, īpaši, ja tie nāk no citām lietojumprogrammām, ir Nepareizi skaitļu formāti formulāsProgramma Excel skaidri nošķir šūnā saglabāto faktisko vērtību un formatējumu, kas tiek izmantots šīs vērtības attēlošanai. Šo divu līmeņu sajaukšana bieži vien rada nelielas kļūdas.

Formulās skaitļi nav jāformatē. Tas ir, ja vērtība ir 1000 eiro, formulā tai jāparādās kā 1000, bez € simbola, tūkstošu atdalītājiem vai komatiem. Ja rakstāt 1.000 vai 1,000 (atkarībā no reģionālajiem iestatījumiem), programma Excel, visticamāk, to interpretēs kā argumentu atdalītāju vai citu vērtību, nekā paredzēts. Skaitļi tiek formatēti, izmantojot valūtu, tūkstošu atdalītājus vai decimāldaļu atdalītājus. pēcIzmantojot šūnu formatēšanas opcijas, nevis izteiksmē; lai iegūtu sīkāku informāciju par to, kā pareizi lietot formātus, skatiet sadaļu datu formāts programmā Excel 365.

Iedomājieties, ka vēlaties pieskaitīt 3100 šūnas A3 saturam. Ja instinktīvi ierakstāt =SUM(3.100,A3), domājot, ka domājat 3100, Excel to interpretēs kā vispirms 3 un 100 pieskaitīšanu un pēc tam A3 vērtības pievienošanu rezultātam. Citiem vārdiem sakot, tā aprēķinās (3 + 100) + A3, kas nav tas pats, kas A3 + 3100. Pareizā formula būtu =SUM(3100,A3), nemēģinot attēlot tūkstošu atdalītāja formātu.

Kaut kas līdzīgs notiek ar funkcijām, kas pieņem tikai noteiktu argumentu skaitu, piemēram, ABS, kas pieņem tikai skaitliskas vērtības. Ja mēģināsiet skaitļa iekšpusē ievietot komatus vai semikolu, lai simulētu tūkstošus, Excel to uzskatīs par vairākiem atsevišķiem argumentiem, un funkcija nedarbosies. atgriezīs sintaktisku kļūduTāpēc tādas izteiksmes kā =ABS(-2;134) nedarbojas, savukārt =ABS(-2134) ir derīgas.

Tas ir īpaši svarīgi paturēt prātā, importējot datus no grāmatvedības sistēmām, ERP vai rēķinu izrakstīšanas programmām. Daudzas no šīm sistēmām eksportē summas ar formatējumu, valūtas simboliem, atstarpēm vai pat vārdu "EUR" beigās. Tas viss pārveido skaitlisku vērtību teksta virknē, ko Excel nevar tieši izmantot savos aprēķinos, kā rezultātā rodas tipa kļūdas, tukši rezultāti vai summas, kas nesummējas.

Labākā prakse ir saglabāt saglabātos datus kā neapstrādāti skaitļi šūnās bez īpaša formatējuma Pēc tam no formatēšanas izvēlnes lietojiet vizuālo formatējumu valūtai, procentam vai tūkstošu atdalītājam. Tas novērš simbolu traucēkļus aprēķinā un ļauj kļūdu dzinējam labāk noteikt neatbilstības.

Inteliģenta kļūdu pārbaude un atkļūdošana programmā Excel

Papildus sintakses labošanai programmā Excel ir iekļauta arī sistēma kļūdu pārbaude, kas analizē formulas un iesaka labojumus Kad šī arvien inteliģentākā programma atklāj kaut ko neparastu, tā palīdz atrast trūkstošās atsauces, neatbilstības un tipa problēmas sarežģītās formulās, samazinot laiku, kas nepieciešams kļūdas avota atrašanai.

Excel datora versijā kļūdu pārbaudei var piekļūt no cilnes Formulas grupā Formulu rediģēšana, kur ir redzama opcija Kļūdu pārbaude. Šis vednis skenē šūnas ar brīdinājumiem un parāda ziņojumus ar iespējamiem problēmas cēloņiem, kā arī iesaka konkrētas izmaiņas, ko var lietot ar vienu klikšķi. Tas ir noderīgi, lai noteiktu nekonsekventus diapazonus, formulas, kas netiek atjauninātas, velkot, atsauces uz tukšām šūnām vai nekonsekventas summas.

Tomēr programmā Excel Online (tīmekļa versijā) pašlaik nav iespējams konfigurēt vai izmantot šos papildu kļūdu pārbaudes noteikumus tādā pašā veidā. Mākoņpakalpojums piedāvā pamatfunkcijas, taču neietver pilnu formulas pārskatīšanas noteikumu kopumu Pieejams darbvirsmā. Tāpēc, strādājot ar ļoti sarežģītām darbgrāmatām, vispraktiskāk ir tās atvērt darbvirsmas lietojumprogrammā, lai pilnībā izmantotu atkļūdošanas programmas priekšrocības.

Ja jums ir datora versija, varat izmantot pogu “Atvērt ar Excel” programmā Excel Online, lai palaistu darbgrāmatu pilnajā lietojumprogrammā. Kad tā ir atvērta, varat veikt visas nepieciešamās pārbaudes, lai noteiktu iespējamās formulu kļūdas, aktivizētu konkrētus noteikumus un pārskatītu visus sistēmas sniegtos brīdinājumus. Tas ir efektīvs veids, kā apvienot tiešsaistes darba ērtības ar datora lietojumprogrammas iespējām.

Lai būtu lietas kursā par jaunajām verifikācijas programmas iespējām un viedo atkļūdošanas rīku uzlabojumiem, ieteicams laiku pa laikam pārbaudīt dokumentāciju. Microsoft Excel oficiālais emuārsŠeit tiek publicēti jaunumi, atjauninājumi un izmaiņas datora un tiešsaistes versijās, palīdzot jums uzzināt, kad ir pieejamas jaunas kļūdu labošanas funkcijas.

Ja jums nepieciešama plašāka piekļuve visām Office lietojumprogrammām (Word, Excel, PowerPoint u. c.) un ar tām saistītajiem pakalpojumiem, vienmēr varat izmēģināt vai iegādāties pilnu komplektu vietnē Office.com, nodrošinot, ka visām jūsu izklājlapām ir pieejami vismodernākie kļūdu pārbaudes rīki.

Kļūdas, ko rada skaitļu saglabāšana teksta formātā, un to labošana vairumā

Ļoti bieži sastopama situācija, īpaši grāmatvedības vai finanšu vidē, rodas, eksportējot detalizētu virsgrāmatu (detalizētu VG) no vadības programmatūras. Daudzos gadījumos šāda veida lietojumprogrammas Viņi eksportē skaitliskās summas kā tekstuRezultātā, atverot failu programmā Excel, formulas, kas mēģina saskaitīt, atņemt vai analizēt šos laukus, neatpazīst vērtības kā skaitļus, un darbības neizdodas vai rada nepareizus rezultātus.

Iebūvētā kļūdu pārbaude programmā Excel var noteikt šo problēmu un šūnu stūrī parādīt klasisko zaļo trīsstūri, kas norāda, ka "skaitlis ir saglabāts kā teksts". Noklikšķinot uz brīdinājuma ikonas, programma Excel iegūst iespēju pārvērst tekstu par skaitli. Trūkums ir tāds, ka pēc noklusējuma, ja izmantojat kļūdu pārbaudes rīku no cilnes Formulas > Formulu rediģēšana > Kļūdu pārbaude, labojums parasti tiek lietots. šūna pa šūnaikas GL ar tūkstošiem rindu var būt ļoti kaitinoši.

Turklāt, ja ir atlasīts viss dokuments, kļūdu pārbaude nekoncentrējas tikai uz skaitliskajām kolonnām, jo ​​arī citas kolonnas, kas satur datumus, tekstu un aprakstus, var izraisīt brīdinājumus, vēl vairāk sarežģot masveida labošanas procesu. Loģisks jautājums šajā kontekstā ir, vai ir veids, kā atlasīt visu lapu un likt Excel izlabot visas šīs kļūdas, vienā piegājienā konvertējot tekstu faktiskajos skaitļos.

Šāda veida situāciju risināšanai ir vairākas stratēģijas, ko var tieši pielietot no darbvirsmas Excel versijas. Viena no tiešākajām ir saistīta ar īpaša līme ar matemātisku darbībuPiemēram, varat ierakstīt skaitli 1 tukšā šūnā, kopēt šo šūnu, atlasīt šūnu diapazonu, kurā ir skaitļi, kas saglabāti kā teksts (piemēram, visa summu kolonna), un izmantot Īpašā ielīmēšana > Reizināt. Šī darbība liek programmai Excel interpretēt katru "teksta" ierakstu kā skaitli, reizināt to ar 1 un saglabāt rezultātu kā reālu skaitlisku vērtību, saglabājot to pašu summu. Tas ir ļoti efektīvs veids, kā vairumā konvertēt lielu datu apjomu.

Vēl viena alternatīva ir izmantot funkciju VALUE palīgkolonnā. Piemēram, ja teksta summas ir B kolonnā, varat izveidot formulu, piemēram, =VALUE(B2), C kolonnā nokopēt to un pēc tam kopēt un ielīmēt rezultātus kā vērtības sākotnējā kolonnā, aizstājot tekstu. Šī metode ļauj arī vizuāli pārbaudīt, vai viss sakrīt, pirms dzēšat sākotnējos datus.

Dažos gadījumos brīdinājumi "skaitlis saglabāts kā teksts" ļauj atlasīt plašāku diapazonu un lietot konvertēšanu vairākām šūnām vienlaikus no mazās zaļās trīsstūra konteksta izvēlnes. Tomēr to efektivitāte ir atkarīga no tā, kā dati tika ģenerēti un vai Excel visus gadījumus nosaka kā viena veida kļūdu.

Strādājot ar lieliem grāmatvedības datu apjomiem, ieteicams pārskatīt arī Excel reģionālos iestatījumus un avota programmas eksporta formātu. Pielāgojot šos parametrus, lai nodrošinātu decimāldaļu atdalītāju, valūtas simbolu un datuma formātu saderību, ievērojami samazinās skaitļu rašanās teksta formātā un plaši izplatītas kļūdas, kā arī ļauj formulām darboties pareizi jau no paša sākuma.

Īsāk sakot, lai gan standarta kļūdu pārbaude parasti darbojas pa rindām, tās brīdinājumus ir iespējams apvienot ar tādām metodēm kā īpašas ielīmēšanas un konvertēšanas funkcijas. lai vairumā labotu lielus nepareizi formatētu datu blokus un atjaunot to statusu kā skaitļus, neizejot cauri šūnai pa šūnai.

Visam ieteikumu kopumam attiecībā uz vienādības zīmi, iekavām, diapazoniem, argumentu tipiem, skaitļu formatējumu, ārējām atsaucēm un kļūdu pārbaudes rīkiem ir kopīgs mērķis: nodrošināt, lai Excel jaunā viedā atkļūdošanas programma darbotos maksimāli efektīvi. Kad formulas ir labi konstruētas un dati izmanto pareizo tipu un formātu, brīdinājumu un ieteikumu sistēma kļūst par spēcīgu sabiedroto, lai ātri atrastu neatbilstības, bojātas atsauces vai problemātiskas šūnas pat darblapās ar ļoti sarežģītām formulām un datiem, kas importēti no vairākiem avotiem.

Saistītais raksts:

Visbiežāk pieļautās kļūdas Excel formulās un to labošana

Isaac

Kaislīgs rakstnieks par baitu pasauli un tehnoloģiju kopumā. Man patīk dalīties savās zināšanās rakstot, un tieši to es darīšu šajā emuārā, parādot visu interesantāko informāciju par sīkrīkiem, programmatūru, aparatūru, tehnoloģiju tendencēm un daudz ko citu. Mans mērķis ir palīdzēt jums vienkāršā un izklaidējošā veidā orientēties digitālajā pasaulē.