Hur man upptäcker fel i komplexa formler med Excels intelligenta felsökningsmotor

Att arbeta med kalkylblad fulla av formler kan vara underbart ... tills något går fel och ett mystiskt problem dyker upp. Ett fel i Excel som du inte vet varifrån det kommer och känna igen Vanligaste felen i Excel-formlerAllt eftersom formler blir längre och mer komplexa blir det nästan omöjligt att upptäcka problemets källa med ögat, och mycket tid slösas bort på att kontrollera cell för cell.

Med den nya intelligenta felsökningsmotorn och felkontrollverktygen erbjuder Excel alltmer hjälp för lokalisera, förstå och korrigera fel i komplexa formlerFör att få ut det mesta av dem är det dock viktigt att behärska några grundläggande regler för att skriva formler, förstå vad de olika felmeddelandena betyder och veta hur man automatiserar masskorrigeringar, särskilt när data importerad från andra program och de finns i en mängd olika ovanliga format.

Grundläggande koncept för att säkerställa att formler inte misslyckas från början

Innan man dyker ner i intelligenta sökmotorer och avancerade felsökare är det viktigt att förstå några grundläggande regler som förhindrar de flesta fel. Excel är ganska strikt med syntax, och varje liten detalj kan orsaka problem. en formel tolkas som text eller misslyckas helt.

Det första och viktigaste är det Varje formel måste börja med likhetstecknet (=)Om likhetstecknet utelämnas förstår Excel inte att du vill utföra en beräkning, utan snarare att du skriver in text eller ett datum. Om du till exempel skriver SUMMA(A1:A10) utan likhetstecknet kommer cellen bokstavligen att visa SUMMA(A1:A10) och utför ingen beräkning. Något liknande händer med datum: om du skriver 11/2 och cellformatet är Allmänt kan Excel visa 2-nov (2 november) istället för att dividera 11 med 2, eftersom det tolkar som att du ger det ett datum och inte en beräkning. Detta är relaterat till Excels märkliga datumfel.

En annan viktig punkt är korrekt användning av parenteser. Varje funktion som använder dem måste ha en välplacerad inledande och avslutande parentesNär man arbetar med kapslade funktionerDet är mycket vanligt att man lämnar en extra parentes eller en parentes som saknas, vilket orsakar syntaxfel. Tänk dig en formel som =OM(B5<0,"Inte giltig",B5*1,05). Om du av misstag skriver =OM(B5<0,"Inte giltig",B5*1,05)) med en extra avslutande parentes, kommer formeln att sluta fungera eftersom antalet parenteser inte matchar. Att alltid kontrollera att det finns samma antal inledande och avslutande parenteser och att de är på rätt plats är viktigt för att undvika fel som är svåra att hitta.

Det är också viktigt att hantera intervall korrekt. För att referera till flera celler i följd måste du använda kolon (:) mellan den första och sista cellenIntervallet skrivs till exempel som A1:A5. Om du utelämnar kolon och skriver något i stil med =SUMMA(A1 A5), kommer Excel att tolka detta som ogiltiga referenser mellan orelaterade celler och returnera ett fel. #NULL!, vilket är den som visas när det finns obefintliga skärningspunkter eller inkonsekventa referenser.

Slutligen måste antalet och ordningen på argumenten för varje funktion respekteras. Vissa funktioner kräver obligatoriska argument och i en specifik positionVissa funktioner kräver flera argument, medan andra tillåter valfria parametrar. Om du får slut på argument, eller lägger till fler än nödvändigt, varnar Excel dig med felmeddelanden relaterade till funktionen. Att känna till den exakta syntaxen för varje funktion (antal argument, förväntad datatyp etc.) hjälper till att minimera problem innan de uppstår.

Argumenttyper: siffror, text och kapslingsgränser

Excel-funktioner kräver inte bara ett specifikt antal argument, utan de förväntar sig också att skickas vidare rätt datatyp i varje argumentEn funktion som är utformad för att addera tal är inte densamma som en som är utformad för att manipulera text; om de byts ut kan resultaten bli oväntade eller till och med ge ett fel.

Till exempel kräver funktioner som SUMMA, MEDEL eller PRODUKT numeriska argument. Om du skickar textvärden till dem där de förväntar sig tal, kan Excel returnera 0, ignorera celler eller visa ett fel beroende på sammanhanget. Omvänt är funktioner som ERSÄTT, SAMMANFÖRD eller HÖGER utformade för att fungera med textsträngar. I dessa måste minst ett av argumenten vara ett textvärde eller en referens till en cell som innehåller text. Om du tvingar ett tal att behandlas som text utan korrekt hantering kanske funktionen inte beter sig som förväntat.

Dessutom har Excel en betydande begränsning på hur man kan kombinera funktioner inom varandra. kapsla fler än 64 funktionsnivåer i en enda formelDet betyder att om du har en formel med OM-satser inuti OM-satser inuti OM-satser, och så vidare, finns det en punkt bortom vilken Excel inte accepterar fler nivåer. Även om få personer i praktiken når dessa extremer, kan denna gräns överskridas i mycket komplexa eller dåligt utformade modeller, vilket orsakar fel som är svåra att tolka. Utöver en viss komplexitetsnivå är det vanligtvis klokare att dela upp logiken i flera hjälpceller snarare än att försöka koncentrera allt till en enda, extremt lång formel; för detta är det också lämpligt att granska Orsaker och lösningar för prestandaproblem i Excel.

Ett typiskt exempel på ett fel på grund av argumenttyp och -nummer är ABS-funktionen. Denna funktion accepterar endast ett enda numeriskt argument och returnerar dess absoluta värde. Om du skriver något i stil med =ABS(-2;134) kommer Excel att visa ett fel eftersom funktionen inte vet vad den ska göra med två argument separerade med semikolon. Det korrekta sättet skulle vara något i stil med =ABS(-2134) eller =ABS(A1) om cell A1 innehåller det negativa tal du vill konvertera till positivt.

Å andra sidan måste användningen av avgränsare beaktas beroende på de regionala inställningarna. I många spanska installationer av Excel används följande: semikolon (;) som argumentavgränsare och kommatecken (,) för decimaldelen. Om en formel av misstag blandar kommatecken och semikolon på felaktiga platser kan det generera ytterligare syntaxfel som komplicerar formelns läsbarhet och felsökning.

Sammanfattningsvis är det tre grundläggande pelare för att minska förekomsten av fel i sofistikerade formler att välja rätt datatyp att skicka till varje funktion, respektera antalet argument som definieras i dess syntax och inte överskrida kapslingsgränserna.

Referenser till andra sidor och böcker: hur man undviker länkfel

När formler börjar länka till andra ark i samma arbetsbok eller till och med till externa arbetsböcker är det ganska lätt att göra små skrivfel som i slutändan genererar ogiltiga referensmeddelanden. Excel behöver dig att Namnen på ark, böcker och rutter är skrivna på ett mycket exakt sätt för att hitta informationen korrekt.

Om du i en formel refererar till ett ark vars namn innehåller mellanslag eller tecken som inte är bokstäver (siffror, bindestreck, symboler...), måste namnet alltid inkluderas. inom enkla citationsteckenOm du till exempel har ett ark som heter Kvartalsdata, skulle den korrekta referensen till cell D3 på det arket vara ='Kvartalsdata'!D3. På samma sätt, om arket heter 123, skulle du skriva ='123'!A1 så att Excel förstår arknamnet och inte förväxlar det med ett slumptal.

Dessutom, när en formel pekar på ett annat blad, måste ett bindestreck placeras omedelbart efter namnet. utropstecken (!)Utropstecknet (```) anger övergången mellan arkidentifieraren och den specifika cellreferensen. Ett komplett exempel skulle vara: ='Kvartalsdata'!D3. Om utropstecknet utelämnas eller placeras felaktigt blir formeln ogiltig och referensfel kommer att uppstå.

När informationen finns i en annan arbetsbok behöver Excel mer kontext. I dessa fall måste den externa referensen inkludera filnamnet inom parentes, arknamnet och intervalletOm du till exempel vill räkna antalet rader i intervallet A1:A8 i arbetsboken Operations T2.xlsx kan du använda en formel som =RADER('Försäljning'!A1:A8) i försäljningsarket. Om filen inte är öppen måste du också ange den fullständiga sökvägen, till exempel 'C:\Mina dokument\Försäljning'!A1:A8, i formeln.

En typisk formel i detta sammanhang skulle vara något i stil med =RADER('C:\Mina dokument\Försäljning'!A1:A8). Denna instruktion returnerar antalet rader i intervallet A1:A8 i den andra arbetsboken, vilket i det här fallet skulle vara 8. Om något misstag görs vid inmatning av sökvägen, arbetsbokens namn, bladnamnet eller hakparenteserna blir resultatet ett fel. referensfel eller ett värde som inte uppdateras eftersom Excel inte kan hitta den externa datakällan.

Det är också lämpligt att övervaka vad som händer när filer som innehåller länkade data flyttas, byter namn eller raderas. Felkontrollmotorn kan upptäcka vissa problem, men om den externa filen inte längre finns på den förväntade platsen måste du manuellt uppdatera länkarna eller omdefiniera sökvägarna för att återställa formelns funktionalitet.

Nummerformatering i formler: fel på grund av symboler och avgränsare

Ett av de vanligaste problemen i kalkylblad med mycket data, särskilt när det kommer från andra program, är användningen av Felaktiga talformat i formlerExcel skiljer tydligt mellan det faktiska värdet som lagras i en cell och den formatering som används för att visa det värdet. Att blanda ihop dessa två nivåer leder ofta till subtila fel.

Tal ska inte formateras i formler. Det vill säga, om ett värde är 1000 euro måste det visas i formeln som 1000, utan €-tecknet, tusentalsavgränsare eller kommatecken. Om du skriver 1 000 eller 1 000 (beroende på de nationella inställningarna) kommer Excel sannolikt att tolka det som en argumentavgränsare eller ett annat värde än det du avsåg. Tal formateras med valuta, tusentalsavgränsare eller decimalavgränsare. sedanAnvända cellformateringsalternativ, inte inom uttrycket; för mer information om hur du tillämpar format korrekt, se dataformat i Excel 365.

Tänk dig att du vill lägga till 3100 till innehållet i cell A3. Om du instinktivt skriver =SUMMA(3.100,A3) och tror att du menar 3100, kommer Excel att tolka detta som att man först lägger till 3 och 100, och sedan lägger till värdet av A3 till resultatet. Med andra ord kommer den att beräkna (3 + 100) + A3, vilket inte är samma sak som A3 + 3100. Den korrekta formeln skulle vara =SUMMA(3100,A3), utan att försöka representera tusentalsavgränsarformatet.

Något liknande händer med funktioner som bara accepterar ett specifikt antal argument, till exempel ABS, som bara accepterar ett numeriskt värde. Om du försöker skriva kommatecken eller semikolon i talet för att simulera tusentals, kommer Excel att betrakta det som flera separata argument, och funktionen kommer inte att fungera. kommer att returnera ett syntaxfelDärför fungerar inte uttryck som =ABS(-2;134), medan =ABS(-2134) är giltiga.

Detta är särskilt viktigt att tänka på när man importerar data från redovisningssystem, ERP-system eller faktureringsprogram. Många av dessa exporterar belopp med formatering, valutasymboler, mellanslag eller till och med ordet "EUR" i slutet. Allt detta omvandlar det som borde vara ett numeriskt värde till en textsträng som Excel inte kan använda direkt i sina beräkningar, vilket leder till skrivfel, tomma resultat eller summor som inte summerar.

Det bästa är att spara lagrad data som råa tal i celler utan specialformatering Använd sedan den visuella formateringen för valuta, procent eller tusentalsavgränsare från formateringsmenyn. Detta förhindrar att symboler stör beräkningen och gör att felsökningsmotorn bättre kan upptäcka inkonsekvenser.

Intelligent felkontroll och felsökning i Excel

Förutom att korrigera syntax innehåller Excel ett system av felkontroll som analyserar formler och föreslår korrigeringar När den här alltmer intelligenta motorn upptäcker något ovanligt hjälper den till att lokalisera saknade referenser, inkonsekvenser och typproblem i komplexa formler, vilket minskar den tid det tar att hitta felkällan.

I skrivbordsversionen av Excel kan felkontrollen nås från fliken Formler, i gruppen Formelredigering, där alternativet Felkontroll visas. Den här guiden skannar celler med varningar och visar meddelanden med möjliga orsaker till problemet, samt föreslår specifika ändringar som kan tillämpas med ett enda klick. Den är användbar för att upptäcka inkonsekventa intervall, formler som inte har uppdaterats när de dras, referenser till tomma celler eller inkonsekvenser i totaler.

I Excel Online (webbversionen) är det dock för närvarande inte möjligt att konfigurera eller använda dessa avancerade felkontrollregler på samma sätt. Molntjänsten erbjuder grundläggande funktioner, men inkluderar inte den fullständiga uppsättningen regler för formelgranskning Tillgänglig på skrivbordet. Därför är det mest praktiskt att öppna arbetsböcker med skrivbordsapplikationen när man arbetar med mycket komplexa arbetsböcker för att dra full nytta av felsökningsmotorn.

Om du har skrivbordsversionen kan du använda knappen "Öppna med Excel" i Excel Online för att starta arbetsboken i det fullständiga programmet. Där kan du utföra alla nödvändiga kontroller för att upptäcka potentiella formelfel, aktivera specifika regler och granska eventuella varningar som systemet ger. Det är ett effektivt sätt att kombinera bekvämligheten med att arbeta online med kraften i skrivbordsprogrammet.

För att hålla dig uppdaterad om nya funktioner i verifieringsmotorn och förbättringar av smarta felsökningsverktyg är det lämpligt att kontrollera dokumentationen då och då. Microsoft Excel officiella bloggDet är här nyheter, uppdateringar och ändringar av skrivbords- och onlineversionerna publiceras, vilket hjälper dig att veta när nya buggfixarfunktioner är tillgängliga.

Om du behöver mer omfattande åtkomst till alla Office-program (Word, Excel, PowerPoint etc.) och deras tillhörande tjänster finns det alltid möjlighet att prova eller köpa hela sviten via Office.com, vilket säkerställer att du har de mest avancerade felkontrollverktygen för alla dina kalkylblad.

Fel orsakade av siffror lagrade som text och hur man korrigerar dem samtidigt

Ett mycket vanligt scenario, särskilt i redovisnings- eller finansmiljöer, uppstår när man exporterar en detaljerad huvudbok (detaljerad GL) från hanteringsprogram. I många fall är den här typen av applikationer De exporterar de numeriska beloppen som textResultatet blir att när filen öppnas i Excel, så känner formler som försöker addera, subtrahera eller analysera dessa fält inte igen värdena som tal, och operationerna misslyckas eller ger felaktiga resultat.

Excels inbyggda felkontroll kan upptäcka detta problem och visa den klassiska gröna triangeln i hörnet av cellerna, vilket indikerar att "talet lagras som text". Genom att klicka på varningsikonen får Excel möjlighet att konvertera texten till ett tal. Nackdelen är att korrigeringen vanligtvis tillämpas som standard om du använder felkontrollverktyget från fliken Formler > Redigera formler > Felkontroll. cell för cellvilket i en GL med tusentals rader kan vara galen.

Dessutom, om hela dokumentet är markerat, fokuserar felkontrollen inte enbart på numeriska kolumner, eftersom andra kolumner som innehåller datum, text och beskrivningar också kan utlösa varningar, vilket ytterligare komplicerar masskorrigeringsprocessen. Den logiska frågan i detta sammanhang är om det finns ett sätt att markera hela arket och få Excel att korrigera alla dessa fel genom att konvertera texten till faktiska siffror i ett svep.

Det finns flera strategier som kan tillämpas direkt från Excel på datorn för att lösa den här typen av situationer. En av de mest direkta involverar att använda speciallim med matematisk beräkningDu kan till exempel skriva in siffran 1 i en tom cell, kopiera den cellen, markera cellområdet som innehåller tal som lagrats som text (till exempel hela kolumnen med belopp) och använda Klistra in special > Multiplicera. Den här åtgärden gör att Excel tolkar varje "text"-post som ett tal, multiplicerar det med 1 och sparar resultatet som ett reellt numeriskt värde, vilket bevarar samma belopp. Det är ett mycket effektivt sätt att konvertera stora mängder data i bulk.

Ett annat alternativ är att använda VÄRDE-funktionen i en hjälpkolumn. Om till exempel textbeloppen finns i kolumn B kan du skapa en formel som =VÄRDE(B2) i kolumn C, kopiera den ner och sedan kopiera och klistra in resultaten som värden i den ursprungliga kolumnen, och ersätta texten. Denna teknik låter dig också visuellt verifiera att allt matchar innan du tar bort originaldata.

I vissa fall tillåter varningarna "tal lagrat som text" dig att välja ett bredare intervall och tillämpa konverteringen på flera celler samtidigt från den lilla gröna triangelns snabbmeny. Deras effektivitet beror dock på hur informationen genererades och om Excel identifierar alla fall som samma typ av fel.

När man arbetar med stora volymer redovisningsdata är det också lämpligt att granska Excels regionala inställningar och källprogrammets exportformat. Att justera dessa parametrar för att säkerställa kompatibilitet mellan decimalavgränsare, valutasymboler och datumformat minskar avsevärt förekomsten av siffror i textformat och utbredda fel, och gör att formler fungerar korrekt från början.

Kort sagt, även om standardfelkontroll tenderar att fungera rad för rad, är det möjligt att kombinera dess varningar med tekniker som speciella klistra- och konverteringsfunktioner att korrigera stora block av felformaterad data i bulk och återställa deras status som nummer utan att behöva gå cell för cell.

Hela denna uppsättning rekommendationer gällande likhetstecken, parenteser, intervall, argumenttyper, talformatering, externa referenser och felkontrollverktyg har ett gemensamt mål: att göra det möjligt för Excels nya intelligenta felsökningsmotor att arbeta med maximal effektivitet. När formler är välkonstruerade och data använder rätt typ och format blir systemet med varningar och förslag en kraftfull allierad för att snabbt hitta inkonsekvenser, trasiga referenser eller problematiska celler, även i kalkylblad med mycket komplexa formler och data importerade från flera källor.

Relaterad artikel:

Vanligaste felen i Excel-formler och hur man åtgärdar dem

Isaac

Passionerad författare om bytesvärlden och tekniken i allmänhet. Jag älskar att dela med mig av min kunskap genom att skriva, och det är vad jag kommer att göra i den här bloggen, visa dig alla de mest intressanta sakerna om prylar, mjukvara, hårdvara, tekniska trender och mer. Mitt mål är att hjälpa dig att navigera i den digitala världen på ett enkelt och underhållande sätt.