Kaip aptikti klaidas sudėtingose ​​formulėse naudojant „Excel“ intelektualųjį derinimo modulį

Darbas su skaičiuoklėmis, pilnomis formulių, gali būti nuostabus... kol kas nors nepavyksta ir iškyla paslaptinga problema. „Excel“ klaida, kurios priežastis nežinoma ir atpažinti Dažniausios klaidos „Excel“ formulėseFormulėms tampant ilgesnėms ir sudėtingesnėms, problemos šaltinio nustatymas akimis tampa beveik neįmanomas, o daug laiko sugaištama tikrinant kiekvieną langelį po langelio.

Naudodama naują išmanųjį derinimo modulį ir klaidų tikrinimo įrankius, „Excel“ siūlo vis daugiau pagalbos sudėtingų formulių klaidų paieška, supratimas ir taisymasTačiau norint kuo geriau išnaudoti šias funkcijas, būtina įvaldyti kelias pagrindines formulių rašymo taisykles, suprasti, ką reiškia skirtingi klaidų pranešimai, ir žinoti, kaip automatizuoti masinį taisymą, ypač kai duomenys, importuoti iš kitų programų ir jie būna įvairių neįprastų formatų.

Pagrindinės sąvokos, užtikrinančios, kad formulės veiktų nuo pat pradžių

Prieš pasineriant į išmaniuosius variklius ir pažangias derinimo priemones, būtina suprasti keletą pagrindinių taisyklių, kurios padeda išvengti daugumos klaidų. „Excel“ gana griežtai taiko sintaksę, todėl bet kokia smulkmena gali sukelti problemų. formulė interpretuojama kaip tekstas arba visiškai nepavyksta.

Pirmas ir svarbiausias dalykas yra tai Kiekviena formulė turi prasidėti lygybės ženklu (=)Jei lygybės ženklo nėra, „Excel“ supranta ne tai, kad norite atlikti skaičiavimą, o tai, kad įvedate tekstą arba datą. Pavyzdžiui, jei įvesite SUM(A1:A10) be lygybės ženklo, langelyje bus rodoma SUM(A1:A10) ir nebus atliktas joks skaičiavimas. Panašiai nutinka ir su datomis: jei įvesite 11/2, o langelio formatas yra Bendras, „Excel“ gali rodyti 2-lapkritis (lapkričio 2 d.), o ne dalinti 11 iš 2, nes ji interpretuoja, kad nurodote datą, o ne skaičiavimą. Tai susiję su Keistas datos trikdis programoje „Excel“.

Kitas svarbus dalykas yra teisingas skliaustų naudojimas. Kiekviena funkcija, kuri juos naudoja, turi turėti gerai išdėstytas atidarymo ir uždarymo skliaustasDirbant su įdėtos funkcijosLabai dažnai paliekamas vienas papildomas arba vienas trūkstamas skliaustas, dėl ko atsiranda sintaksės klaidų. Įsivaizduokite formulę, pvz., =IF(B5<0;"Negalioja";B5*1,05). Jei netyčia parašysite =IF(B5<0;"Negalioja";B5*1,05)) su papildomu uždaromuoju skliaustu, formulė nustos veikti, nes skliaustų skaičius nesutaps. Visada tikrinkite, ar atidaromųjų ir uždaromųjų skliaustų yra tiek pat ir ar jie yra teisingoje padėtyje, kad išvengtumėte sunkiai randamų klaidų.

Taip pat svarbu teisingai tvarkyti diapazonus. Norėdami nurodyti kelias iš eilės einančias ląsteles, turite naudoti dvitaškis (:) tarp pirmos ir paskutinės ląstelėsDiapazonas rašomas, pavyzdžiui, kaip A1:A5. Jei praleisite dvitaškį ir gausite kažką panašaus į =SUM(A1 A5), „Excel“ tai interpretuos kaip negaliojančias nuorodas tarp nesusijusių langelių ir grąžins klaidą. #NULL!, kuris rodomas, kai nėra sankirtų arba yra nenuoseklių nuorodų.

Galiausiai, reikia atsižvelgti į kiekvienos funkcijos argumentų skaičių ir tvarką. Kai kurioms funkcijoms reikia privalomi argumentai ir konkrečioje pozicijojeKai kurioms funkcijoms reikia kelių argumentų, o kitoms leidžiami pasirenkami parametrai. Jei pritrūksta argumentų arba jų pridėsite daugiau nei reikia, „Excel“ įspės jus su funkcija susijusiais klaidų pranešimais. Žinant tikslią kiekvienos funkcijos sintaksę (argumentų skaičių, numatomą duomenų tipą ir kt.), galima sumažinti problemas dar prieš joms atsirandant.

Argumentų tipai: skaičiai, tekstas ir įdėjimo ribos

„Excel“ funkcijoms ne tik reikia konkretaus argumentų skaičiaus, bet jos taip pat tikisi, kad joms bus perduoti teisingas duomenų tipas kiekviename argumenteFunkcija, skirta skaičiams sudėti, nėra tokia pati kaip funkcija, skirta tekstui manipuliuoti; jei jie sukeičiami vietomis, rezultatai gali būti netikėti arba net sukelti klaidą.

Pavyzdžiui, tokios funkcijos kaip SUM, AVERAGE arba PRODUCT reikalauja skaitinių argumentų. Jei joms perduosite tekstines reikšmes ten, kur tikimasi skaičių, „Excel“ gali grąžinti 0, ignoruoti langelius arba rodyti klaidą, priklausomai nuo konteksto. Ir atvirkščiai, tokios funkcijos kaip REPLACE, CONCAT arba RIGHT yra skirtos darbui su teksto eilutėmis. Jose bent vienas iš argumentų turi būti tekstinė reikšmė arba nuoroda į langelį, kuriame yra tekstas; jei priversite skaičių laikyti tekstu tinkamai neapdoroję, funkcija gali neveikti taip, kaip tikėtasi.

Be to, „Excel“ taiko reikšmingus apribojimus funkcijų jungimui tarpusavyje. Jūs negalite į vieną formulę įdėti daugiau nei 64 funkcijų lygiusTai reiškia, kad jei turite formulę su IF sakiniais IF sakinių viduje ir panašiai, yra riba, po kurios „Excel“ nebepriims jokių lygių. Nors praktiškai nedaugelis žmonių pasiekia šias ribas, labai sudėtinguose arba prastai suprojektuotuose modeliuose ši riba gali būti viršyta, todėl gali kilti sunkiai interpretuojamų klaidų. Viršijus tam tikrą sudėtingumo lygį, paprastai protingiau logiką padalyti į kelias pagalbines ląsteles, o ne bandyti viską sukoncentruoti į vieną, itin ilgą formulę; tam taip pat patartina peržiūrėti „Excel“ našumo problemų priežastys ir sprendimai.

Tipiškas klaidos dėl argumento tipo ir skaičiaus pavyzdys yra ABS funkcija. Ši funkcija priima tik vienas skaitinis argumentas ir grąžina absoliučią reikšmę. Jei įvesite kažką panašaus į =ABS(-2;134), „Excel“ rodys klaidą, nes funkcija nežino, ką daryti su dviem argumentais, atskirtais kabliataškiu. Teisingas būdas būtų kažkas panašaus į =ABS(-2134) arba =ABS(A1), jei langelyje A1 yra neigiamas skaičius, kurį norite konvertuoti į teigiamą.

Kita vertus, atsižvelgiant į regioninius nustatymus, reikia atsižvelgti į skyriklių naudojimą. Daugelyje ispanų kalba įdiegtų „Excel“ programų naudojama: kabliataškis (;) kaip argumentų skirtukas ir kablelis (,) dešimtainei daliai. Jei formulėje klaidingai sumaišyti kableliai ir kabliataškiai netinkamose vietose, gali atsirasti papildomų sintaksės klaidų, kurios apsunkina formulės skaitomumą ir derinimą.

Apibendrinant galima teigti, kad tinkamo duomenų tipo, kurį reikia perduoti kiekvienai funkcijai, pasirinkimas, atsižvelgiant į jos sintaksėje apibrėžtą argumentų skaičių ir neviršijant įdėjimo apribojimų yra trys pagrindiniai ramsčiai, siekiant sumažinti klaidų skaičių sudėtingose ​​formulėse.

Nuorodos į kitus puslapius ir knygas: kaip išvengti susiejimo klaidų

Kai formulės pradeda susieti su kitais tos pačios darbaknygės lapais ar net su išorinėmis darbaknygėmis, gana lengva padaryti smulkių spausdinimo klaidų, dėl kurių atsiranda neteisingų nuorodų pranešimų. „Excel“ programai reikia, kad... Lapų, knygų ir maršrutų pavadinimai parašyti labai tiksliai kad duomenys būtų tinkamai surasti.

Jei formulėje nurodote lapą, kurio pavadinime yra tarpų arba ne raidžių simbolių (skaičių, brūkšnelių, simbolių ir kt.), pavadinimas visada turi būti įtrauktas. viengubose kabutėsePavyzdžiui, jei turite lapą pavadinimu „Ketvirčio duomenys“, teisinga nuoroda į langelį D3 tame lape būtų ='Ketvirčio duomenys'!D3. Panašiai, jei lapas vadinamas 123, turėtumėte rašyti ='123'!A1, kad „Excel“ suprastų lapo pavadinimą ir nesupainiotų jo su atsitiktiniu skaičiumi.

Be to, kai formulė nurodo į kitą lapą, iškart po pavadinimo turi būti dedamas brūkšnelis. šauktukas (!)Šauktukas („```“) žymi perėjimą tarp lapo identifikatoriaus ir konkrečios langelio nuorodos. Išsamus pavyzdys būtų: ='Ketvirčio duomenys'!D3. Jei šauktukas praleistas arba neteisingai įdėtas, formulė bus negaliojanti ir atsiras nuorodų klaidų.

Kai informacija yra kitoje darbaknygėje, „Excel“ reikia daugiau konteksto. Tokiais atvejais išorinėje nuorodoje turi būti failo pavadinimas skliausteliuose, lapo pavadinimas ir diapazonasPavyzdžiui, jei norite suskaičiuoti eilučių skaičių darbaknygės „Operacijos T2.xlsx“ diapazone A1:A8, lape „Pardavimai“ galite naudoti tokią formulę: =ROWS('Pardavimai'!A1:A8). Jei failas neatidarytas, formulėje taip pat turite nurodyti visą failo kelią, pvz., „C:\My Documents\Sales'!A1:A8“.

Tipinė formulė šiame kontekste būtų maždaug tokia: =ROWS('C:\My Documents\Sales'!A1:A8). Ši instrukcija grąžins eilučių skaičių diapazone A1:A8 toje kitoje darbaknygėje, kuris šiuo atveju būtų 8. Jei įvedant kelią, darbaknygės pavadinimą, lapo pavadinimą ar laužtinius skliaustus bus padaryta klaida, rezultatas bus klaida. nuorodos klaida arba reikšmė, kuri neatnaujinama nes „Excel“ negali rasti išorinio duomenų šaltinio.

Taip pat patartina stebėti, kas nutinka, kai failai, kuriuose yra susietų duomenų, yra perkeliami, pervadinami arba ištrinami. Klaidų tikrinimo modulis gali aptikti kai kurių problemų, tačiau jei išorinio failo nebėra numatytoje vietoje, turėsite rankiniu būdu atnaujinti nuorodas arba iš naujo apibrėžti kelius, kad atkurtumėte formulės funkcionalumą.

Skaičių formatavimas formulėse: klaidos dėl simbolių ir skyriklių

Viena iš dažniausiai pasitaikančių problemų, kylančių naudojant skaičiuokles su daugybe duomenų, ypač kai jie gaunami iš kitų programų, yra jų naudojimas. Neteisingi skaičių formatai formulėse„Excel“ aiškiai atskiria faktinę langelyje saugomą reikšmę ir formatavimą, naudojamą tai reikšmei rodyti. Šių dviejų lygių supainiojimas dažnai sukelia subtilių klaidų.

Skaičiai formulėse neturėtų būti formatuojami. Tai yra, jei reikšmė yra 1000 eurų, formulėje ji turi būti rodoma kaip 1000, be € simbolio, tūkstančių skirtukų ar kablelių. Jei įrašysite 1.000 arba 1 000 (priklausomai nuo regiono nustatymų), „Excel“ greičiausiai tai interpretuos kaip argumentų skirtuką arba kitą reikšmę, nei ketinote. Skaičiai formatuojami naudojant valiutą, tūkstančių skirtukus arba dešimtainius skirtukus. po toNaudojant langelių formatavimo parinktis, o ne išraiškos viduje; daugiau informacijos apie tai, kaip teisingai taikyti formatus, žr. Duomenų formatas programoje „Excel 365“.

Įsivaizduokite, kad norite prie A3 langelio turinio pridėti 3100. Jei instinktyviai įvesite =SUM(3.100,A3) manydami, kad turite omenyje 3100, „Excel“ tai interpretuos kaip pirmiausia 3 ir 100 pridėjimą, o tada A3 langelio reikšmės pridėjimą prie rezultato. Kitaip tariant, ji apskaičiuos (3 + 100) + A3, o tai nėra tas pats, kas A3 + 3100. Teisinga formulė būtų =SUM(3100,A3), nebandant pateikti tūkstančių skirtuko formato.

Panašiai nutinka ir su funkcijomis, kurios priima tik tam tikrą skaičių argumentų, pavyzdžiui, ABS, kuri priima tik skaitines reikšmes. Jei bandysite skaičių tarpus tarp kablelių ar kabliataškių, kad imituotumėte tūkstančius, „Excel“ tai laikys keliais atskirais argumentais ir funkcija neveiks. grąžins sintaksės klaidąTodėl tokios išraiškos kaip =ABS(-2;134) neveikia, o =ABS(-2134) yra tinkamos.

Tai ypač svarbu turėti omenyje importuojant duomenis iš apskaitos sistemų, ERP ar sąskaitų faktūrų išrašymo programų. Daugelis šių sistemų eksportuoja sumas su formatavimu, valiutos simboliais, tarpais ar net žodžiu „EUR“ pabaigoje. Visa tai paverčia tai, kas turėtų būti skaitinė reikšmė, teksto eilute, kurios „Excel“ negali tiesiogiai naudoti skaičiavimuose, todėl atsiranda tipo klaidų, tuščių rezultatų arba nesumuojamos sumos.

Geriausia praktika yra saugoti saugomus duomenis kaip neapdoroti skaičiai langeliuose be specialaus formatavimo Tada formatavimo meniu pritaikykite valiutos, procentų arba tūkstančių skirtuko vaizdinį formatavimą. Tai apsaugo simbolius nuo trukdžių skaičiavimui ir leidžia klaidų mechanizmui geriau aptikti neatitikimus.

Išmanus klaidų tikrinimas ir derinimas programoje „Excel“

Be sintaksės taisymo, „Excel“ apima ir sistemą klaidų tikrinimas, kuris analizuoja formules ir siūlo pataisymus Aptikusi ką nors neįprasto, ši vis išmanesnė sistema padeda rasti trūkstamas nuorodas, neatitikimus ir tipografijos problemas sudėtingose ​​formulėse, taip sutrumpindama klaidos šaltinio paieškos laiką.

„Excel“ darbalaukio versijoje klaidų tikrinimą galima pasiekti skirtuke „Formulės“, grupėje „Formulių redagavimas“, kur rodoma parinktis „Klaidų tikrinimas“. Šis vedlys nuskaito langelius su įspėjimais ir rodo pranešimus su galimomis problemos priežastimis, taip pat siūlo konkrečius pakeitimus, kuriuos galima pritaikyti vienu spustelėjimu. Jis naudingas norint aptikti nenuoseklius diapazonus, formules, kurios nebuvo atnaujintos vilkant, nuorodas į tuščius langelius arba neatitikimus sumose.

Tačiau „Excel Online“ (žiniatinklio versijoje) šiuo metu negalima konfigūruoti arba naudoti šių išplėstinių klaidų tikrinimo taisyklių tokiu pačiu būdu. Debesijos paslauga siūlo pagrindines funkcijas, bet neapima viso formulės peržiūros taisyklių rinkinio Pasiekiama darbalaukio versijoje. Todėl dirbant su labai sudėtingomis darbaknygėmis, praktiškiausia jas atidaryti naudojant darbalaukio programą, kad būtų galima visapusiškai išnaudoti derinimo modulio galimybes.

Jei turite darbalaukio versiją, galite naudoti mygtuką „Atidaryti naudojant „Excel“ programoje „Excel Online“, kad paleistumėte darbaknygę pilnoje programos versijoje. Patekę į ją, galite atlikti visus reikiamus patikrinimus, kad aptiktumėte galimas formulių klaidas, suaktyvintumėte konkrečias taisykles ir peržiūrėtumėte visus sistemos pateikiamus įspėjimus. Tai efektyvus būdas suderinti darbo internete patogumą su darbalaukio programos galia.

Norint gauti naujausią informaciją apie naujas tikrinimo modulio galimybes ir išmaniųjų derinimo įrankių patobulinimus, patartina kartkartėmis peržiūrėti dokumentaciją. Oficialus „Microsoft Excel“ tinklaraštisČia skelbiamos naujienos, atnaujinimai ir darbalaukio bei internetinių versijų pakeitimai, padedantys jums žinoti, kada atsiranda naujų klaidų taisymo funkcijų.

Jei jums reikia išsamesnės prieigos prie visų „Office“ programų („Word“, „Excel“, „PowerPoint“ ir kt.) ir su jomis susijusių paslaugų, visada galite išbandyti arba įsigyti visą paketą svetainėje Office.com, taip užtikrindami, kad visoms savo skaičiuoklėms turėsite pažangiausius klaidų tikrinimo įrankius.

Klaidos, kurias sukelia skaičiai, saugomi kaip tekstas, ir kaip jas ištaisyti masiškai

Labai dažnas scenarijus, ypač apskaitos ar finansų aplinkoje, iškyla eksportuojant išsamią didžiąją knygą iš valdymo programinės įrangos. Daugeliu atvejų tokio tipo programos Jie eksportuoja skaitines sumas kaip tekstąDėl to, atidarius failą programoje „Excel“, formulės, bandančios sudėti, atimti arba analizuoti šiuos laukus, neatpažįsta reikšmių kaip skaičių, todėl operacijos nepavyksta arba pateikia neteisingus rezultatus.

Integruotas „Excel“ klaidų tikrinimo įrankis gali aptikti šią problemą ir langelių kampe parodyti klasikinį žalią trikampį, rodantį, kad „skaičius saugomas kaip tekstas“. Spustelėjus įspėjimo piktogramą, „Excel“ programa gali konvertuoti tekstą į skaičių. Trūkumas yra tas, kad pagal numatytuosius nustatymus, jei naudojate klaidų tikrinimo įrankį iš skirtuko Formulės > Formulių redagavimas > Klaidų tikrinimas, paprastai taikomas pataisymas. ląstelė po ląstelėskas GL su tūkstančiais eilučių gali būti varginanti.

Be to, jei pasirinktas visas dokumentas, klaidų tikrinimas neapsiriboja vien skaitiniais stulpeliais, nes kiti stulpeliai, kuriuose yra datos, tekstas ir aprašymai, taip pat gali sukelti įspėjimus, o tai dar labiau apsunkina masinį taisymo procesą. Logiškas klausimas šiame kontekste yra tai, ar yra būdas pasirinkti visą lapą ir leisti „Excel“ ištaisyti visas šias klaidas vienu ypu konvertuojant tekstą į tikrus skaičius.

Yra keletas strategijų, kurias galima tiesiogiai pritaikyti iš darbalaukio „Excel“, kad būtų galima išspręsti tokias situacijas. Viena iš tiesioginių yra naudoti specialūs klijai su matematiniu veiksmuPavyzdžiui, galite įvesti skaičių 1 į tuščią langelį, nukopijuoti tą langelį, pasirinkti langelių diapazoną, kuriame yra skaičiai, saugomi kaip tekstas (pavyzdžiui, visą sumų stulpelį), ir naudoti Specialus įklijavimas > Daugyba. Ši operacija priverčia „Excel“ kiekvieną „teksto“ įrašą interpretuoti kaip skaičių, padauginti jį iš 1 ir įrašyti rezultatą kaip realiąją skaitinę reikšmę, išsaugodama tą pačią sumą. Tai labai efektyvus būdas konvertuoti didelius duomenų kiekius masiškai.

Kita alternatyva – naudoti funkciją VALUE pagalbiniame stulpelyje. Pavyzdžiui, jei teksto sumos yra B stulpelyje, galite sukurti formulę, pvz., =VALUE(B2), C stulpelyje, nukopijuoti ją žemyn, o tada nukopijuoti ir įklijuoti rezultatus kaip reikšmes į pradinį stulpelį, pakeičiant tekstą. Ši technika taip pat leidžia vizualiai patikrinti, ar viskas sutampa prieš ištrinant pradinius duomenis.

Kai kuriais atvejais įspėjimai „skaičius išsaugotas kaip tekstas“ leidžia pasirinkti platesnį diapazoną ir pritaikyti konvertavimą keliems langeliams vienu metu iš mažo žalio trikampio kontekstinio meniu. Tačiau jų veiksmingumas priklauso nuo to, kaip duomenys buvo sugeneruoti ir ar „Excel“ visus atvejus aptinka kaip to paties tipo klaidą.

Dirbant su dideliais apskaitos duomenų kiekiais, taip pat patartina peržiūrėti „Excel“ regioninius nustatymus ir šaltinio programos eksportavimo formatą. Šių parametrų koregavimas siekiant užtikrinti dešimtainių skirtukų, valiutos simbolių ir datos formatų suderinamumą labai sumažina skaičių atsiradimą teksto formatu ir plačiai paplitusias klaidas, o formulės nuo pat pradžių veikia teisingai.

Trumpai tariant, nors standartinis klaidų tikrinimas paprastai veikia eilutė po eilutės, jo įspėjimus galima derinti su tokiais metodais kaip specialios įklijavimo ir konvertavimo funkcijos. masiškai ištaisyti didelius netinkamai suformatuotų duomenų blokus ir atkurti jų būseną kaip skaičių, nereikės nagrinėti langelio po langelio.

Visas šis rekomendacijų rinkinys dėl lygybės ženklo, skliaustų, diapazonų, argumentų tipų, skaičių formatavimo, išorinių nuorodų ir klaidų tikrinimo įrankių turi bendrą tikslą: įgalinti naują išmanųjį „Excel“ derinimo modulį veikti maksimaliai efektyviai. Kai formulės yra gerai sukonstruotos, o duomenys pateikiami teisingo tipo ir formato, įspėjimų ir pasiūlymų sistema tampa galingu sąjungininku greitai nustatant neatitikimus, neveikiančias nuorodas ar probleminius langelius net ir darbalapiuose su labai sudėtingomis formulėmis ir duomenimis, importuotais iš kelių šaltinių.

Susijęs straipsnis:

Dažniausios klaidos „Excel“ formulėse ir kaip jas ištaisyti

Izaokas

Aistringas rašytojas apie baitų pasaulį ir technologijas apskritai. Man patinka dalytis savo žiniomis rašydamas, būtent tai ir darysiu šiame tinklaraštyje, parodysiu jums įdomiausius dalykus apie programėles, programinę įrangą, techninę įrangą, technologijų tendencijas ir kt. Mano tikslas – padėti jums paprastai ir smagiai naršyti skaitmeniniame pasaulyje.