Hitta avvikelser i Excel – 5 enkla tekniker förklarade

När man arbetar med stora datamängder i Microsoft Excel är det oundvikligt att stöta på avvikelser i data. Att identifiera och åtgärda dessa inkonsekvenser är avgörande för att säkerställa korrekt rapportering och analys.

De goda nyheterna? kommer med en mängd olika verktyg och tekniker som hjälper dig att hitta och lösa dessa problem. I den här artikeln kommer du att lära dig om några av de mest effektiva metoderna. Detta inkluderar filtrering, villkorlig formatering och användning av avancerade formler.

Genom att införliva dessa tekniker i ditt dagliga arbetsflöde blir du mer effektiv i att hantera och analysera dina data. I slutändan leder till bättre beslutsfattande och förbättrade resultat för dina projekt.

Innehållsförteckning

• Förstå avvikelser i Excel
• 5 sätt att hitta avvikelser i Excel
• #1 – Hitta avvikelser via manuell granskning i Excel
• #2 – Använda filter i Excel för att upptäcka och åtgärda inkonsekventa data
• 3# – Markera avvikelser med villkorlig formatering
• #4 – Hitta avvikelser med avancerade Excel-funktioner
  • IF och IS funktioner
  • SÖKUPPSÖKNING, SÖKUPPSÖKNING & XLOOKUP
  • MATCH
• #5 – Använda Excel-tillägg för att upptäcka avvikelser
• Hur kan du skapa regler för datavalidering för att förhindra avvikelser?
• Vårt sista ord

Förstå avvikelser i Excel

Avvikelser i rader och kolumner kan bero på mänskliga inmatningsfel, dubbla poster eller oegentligheter i dataformateringen.

Ta en titt på följande data. Du kommer att märka att Walter Whites och Wayne Cenas kön är felstavat – Mael istället för Male. Det är faktiskt enkelt att lägga märke till och åtgärda felen i det här scenariot. Men det är inte fallet med kalkylblad som innehåller tusentals rader och kolumner.

För att hitta och hantera dessa avvikelser kan du använda följande funktioner:

• Filter: Använd filter på specifika kolumner för att se unika värden och snabbt upptäcka inkonsekventa data.
• Villkorlig formatering: Använd formateringsregler för att markera celler som uppfyller specifika kriterier eller som inte matchar data mellan kolumner.
• Avancerade Excel-funktioner: Använd funktioner som MATCH och för att jämföra datamängder och identifiera avvikelser mellan dem.

Genom att förstå hur du använder dessa funktioner i Excel kan du effektivt upptäcka och korrigera avvikelser i dina datamängder.

5 sätt att hitta avvikelser i Excel

Det finns 5 huvudsakliga sätt vi vill hantera avvikelser i Excel:

1. Manuell granskning
2. Filtrering
3. Villkorlig formatering
4. Avancerade Excel-funktioner
5. Excel-tillägg

Låt oss gå igenom var och en av dem så att du kan träna det bästa sättet som passar dig.

#1 – Hitta avvikelser via manuell granskning i Excel

När du arbetar med kan en manuell granskning vara en användbar metod för att identifiera avvikelser i dina data. Denna process kräver noggrann undersökning av datamängderna för att upptäcka eventuella inkonsekvenser eller fel.

Här är några steg du kan följa för att utföra en manuell granskning:

• Börja med att tydligt förstå de datamängder du arbetar med. Gör dig bekant med raderna, kolumnerna och formateringen.
• Bläddra igenom data och var noga uppmärksam på informationen som presenteras i varje cell. Du kan använda verktyg som cellmarkering med fyllningsfärg, textformatering eller villkorlig formatering för att göra data lättare att läsa.
• Jämför en rad mot en annan eller en kolumn mot en annan. Leta efter felaktig information, dubbletter av poster, etc. som kan orsaka felaktigheter i dina Excel-ark.
• När du identifierar avvikelser, skapa en lista eller använd kommentarer för att dokumentera dem. Senare kan dessa fungera som viktiga referenser för att rätta till problemen.

När du manuellt granskar data för avvikelser i ett Excel-kalkylblad är tålamod nyckeln. Denna process är tidskrävande. Men det är en bra utgångspunkt för att identifiera potentiella fel.

#2 – Använda filter i Excel för att upptäcka och åtgärda inkonsekventa data

Ett av de enklaste sätten att upptäcka inkonsekventa data är att använda Excels filter .

Med Filter isolerar vi de felaktiga uppgifterna i kolumnen Kön i följande datamängd:

1. Välj Sortera och filtrera på fliken Hem .

2. Välj Filter – se till att markören är inom dataområdet.

3. Klicka på pilen i listrutan i kolumnen med inkonsekventa data.

4. Avmarkera nu alla korrekta matchande data. I vårt fall är det Man . Klicka sedan på OK för att spara ändringarna.

5. Ditt Excel-kalkylblad kommer nu bara att visa rader med felaktiga värden. För att åtgärda dem, klicka på Sök och välj på fliken Hem .

6. Klicka på Ersätt när rullgardinsmenyn visas.

7. Skriv in det felaktiga värdet i Hitta vad och det rätta i Ersätt med . Klicka sedan på Ersätt alla .

8. En dialogruta visas som bekräftar ändringarna. Klicka på OK för att fortsätta.

3# – Markera avvikelser med villkorlig formatering

Ett annat sätt att identifiera kolumn- och radskillnader är att använda villkorlig formatering.

Du kan komma åt regler som Markeringsceller, Topp/botten, Datafält, Färgskalor & Iron Sets. Dessutom kan du skapa en ny regel och hantera eller rensa tidigare regler.

Vi använder en exempeltabell för att visa kraften i villkorlig formatering:

1. Välj din tabell eller önskade rader och kolumner. Vi väljer kolumn A (företag) och kolumn B (land). Välj sedan Villkorlig formatering på fliken Hem .

2. Välj en regeltyp och sedan regeln. Vi väljer höjdpunktscellregler och dubbletter av värden...

3. Nu måste vi välja om du vill markera Duplicera eller Unika värden och fyllningsfärg och teckensnitt för att formatera celler. När du är klar klickar du på OK .

4. Alla dubblettvärden är nu markerade med ljusröd fyllning med mörkröd text.

#4 – Hitta avvikelser med avancerade Excel-funktioner

IF och IS funktioner

För att jämföra celler och identifiera skillnader kan du använda IF- och IS-funktioner.

Till exempel har vi använt formeln =IF(A2=B2,"MATCH","NOT MATCHED")för att jämföra celler A2 och B2. Och vi har dragit formeln för att tillämpa den på andra celler. Så, identiska celler returnerar MATCH annars INTE MATCHED .

Du kan kombinera IF med IS-funktioner som ISNUMBER eller ISTEXT för att söka efter specifika datatyper. =IF(ISNUMBER(B2), "Number", "NaN")Till exempel kommer formeln att returnera Number i kolumn c om B2 är numeriskt och NaN annars.

SÖKUPPSÖKNING, SÖKUPPSÖKNING & XLOOKUP

VLOOKUP (vertikal) och HLOOKUP (horisontell) funktioner är användbara när du jämför värden i olika kolumner eller rader i två tabeller. För att göra det, använd följande formel: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) eller =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Ersätt lookup_value med värdet du vill hitta i den andra tabellen, table_array med cellintervallet i den andra tabellen, col_index_num eller row_index_num med indexnumret för kolumnen eller raden du vill returnera ett värde från, och [range_lookup] med FALSK för exakta matchningar eller TRUE för en ungefärlig matchning.

I följande handledning använder vi SÖK UPP-funktionen i kolumn E för att jämföra anställdas namn i kolumn A och kolumn D för att dra ut deras löner från kolumn B om en matchning hittas.

XLOOKUP är en uppdaterad version av SLOOKUP och HLOOKUP som finns på Excel 2021 och Excel 365. Till XLOOKUP formeln är: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Ersätt lookup_value med värdet du vill hitta i den andra tabellen, lookup_array med cellintervallet att söka i den andra tabellen, return_array med arrayen du vill returnera ett värde från och [if_nout_found] med ett textvärde om inget matchar värden hittas.

Här är ett snabbt exempel på XLOOKUP med samma tabeller:

MATCH

MATCH-funktionen kan också användas för att jämföra två listor för avvikelser.

MATCH-funktionen, , =MATCH(lookup_value, lookup_array, [match_type])söker efter ett specificerat objekt i ett intervall och returnerar den relativa positionen för objektet inom det intervallet.

Här hittar vi positionen för vattenmeloner i arrayen A2:A8 med hjälp av MATCH. Vi använder match_type som 0 för att hitta det första värdet som är exakt lika med lookup_value .

#5 – Använda Excel-tillägg för att upptäcka avvikelser

Excel erbjuder olika tillägg och inbyggda verktyg som kan hjälpa dig att upptäcka och analysera avvikelser mer effektivt. I det här avsnittet kommer vi att diskutera några av dessa användbara tillägg.

Överväg först att använda tillägget Spreadsheet Inquire . Detta kan hjälpa dig att jämföra två arbetsböcker och markera skillnader cell för cell. Så här aktiverar du tillägget Spreadsheet Inquire:

1. Klicka på fliken Arkiv .

2. Välj Alternativ och klicka sedan på kategorin Tillägg .

3. I rutan Hantera väljer du COM-tillägg och klickar på Gå .

4. Markera rutan Spreadsheet Enquire och klicka på OK .

5. När det är aktiverat, navigera till fliken Fråga för att använda kommandot Jämför filer .

Ett annat användbart verktyg för att upptäcka avvikelser är Analysis ToolPak . Detta tillägg tillhandahåller avancerade statistiska funktioner och dataanalysverktyg, som kan vara användbara vid analys av avvikelser.

För att aktivera Analysis ToolPak, följ samma steg som att aktivera Spreadsheet Inquire-tillägget. Men välj Excel-tillägg i rutan Hantera och markera rutan Analysis ToolPak .

Sammanfattningsvis erbjuder Excel olika tillägg för att hjälpa dig att upptäcka och hantera avvikelser i dina data. Du kan förbättra din datanoggrannhet genom att bekanta dig med dessa verktyg och formler.

Hur kan du skapa regler för datavalidering för att förhindra avvikelser?

För att skapa datavalideringsregler i Excel som kan hjälpa till att förhindra avvikelser, följ dessa steg:

1. Välj de celler du vill begränsa. Detta kan vara en enskild cell, ett cellintervall eller en hel kolumn.

2. Gå till fliken Data i verktygsfältet och klicka på knappen Datavalidering (markerad med två horisontella rutor, en grön bock och en röd kryssad cirkel).

3. Se till att du är på fliken Inställningar i fönstret Datavalidering. Här kommer du att ha möjlighet att definiera dina valideringskriterier utifrån dina behov.

Det finns olika typer av valideringskriterier tillgängliga i Excel, av vilka några inkluderar:

• Heltal – tillåter endast heltal inom ett specificerat intervall.
• Decimal – tillåter endast decimaltal inom ett specificerat intervall.
• Lista – begränsar inmatningen till en fördefinierad lista med acceptabla värden.
• Datum – kräver datum inom ett specifikt datumintervall.
• Tid – accepterar endast tider inom ett visst tidsintervall.
• Textlängd – upprätthåller begränsningar för längden på den inmatade texten.
• Anpassad – gör att du kan skapa anpassade valideringsregler med hjälp av Excel-formler.

När du har valt lämpliga valideringskriterier, specificera parametrarna efter behov. Till exempel, om du väljer Hela antalet, måste du ställa in Minimum och Maximum med en dataintervallvalidator som mellan, lika med, mindre än, etc.

Utöver valideringskriterierna kan du också ställa in anpassade felmeddelanden och inmatningstips för att hjälpa användare att förstå och följa dina valideringsregler. För att göra detta, växla till flikarna Inmatningsmeddelande och Error Alert för att ange önskade meddelanden.

Genom att implementera regler för datavalidering minskar du avsevärt sannolikheten för datainkonsekvenser och förbättrar den övergripande noggrannheten i dina Excel-kalkylblad.

Vårt sista ord

I den här artikeln har du lärt dig olika metoder för att hitta och åtgärda avvikelser i dina Excel-data. Låt oss sammanfatta dessa nyckelstrategier:

• Använda filterrullgardinsmenyer i kolumner för att identifiera unika värden och inkonsekventa data.
• Använder villkorlig formatering i markerade celler för att markera avvikelser med hjälp av ett gäng regelmallar.
• Tillämpa avancerade Excel-funktioner som MATCH , VLOOKUP , XLOOKUP , etc. funktioner för att korskontrollera data i två listor eller tabeller.
• Använda Excel-tillägg för att upptäcka avvikelser.
• Ställa in datavalideringsregler för att förhindra fel i data

Med dessa tekniker i din Excel-kunskapsuppsättning blir dina data mer exakta och pålitliga.

Kom ihåg att öva på dessa metoder regelbundet för att bli effektivare på att upptäcka avvikelser i dina kalkylblad.

Fortsätt förfina dina kunskaper genom att registrera dig för vår , och fortsätt utforska nya funktioner och funktioner för att bli ännu mer skickliga i Excel.