Hur matchar jag data i Excel?
Om du vill markera poster i båda listorna vill du markera poster som matchar varandra.
En av Microsoft Excels många funktioner är möjligheten att jämföra två datalistor, identifiera matchningar mellan listorna och identifiera vilka objekt som bara finns i en lista. Detta är användbart när du jämför finansiella poster eller kontrollerar om ett visst namn finns i en databas. Du kan använda MATCH-funktionen för att identifiera och markera matchande eller icke-matchande poster, eller så kan du använda konditioneringsformatering med COUNTIF-funktionen. Följande steg beskriver hur du använder var och en för att matcha dina data.
Metod 1 av 2: identifiera poster med MATCH-funktionen
- 1Kopiera datalistorna till ett enda kalkylblad. Excel kan fungera med flera kalkylblad i en enda arbetsbok eller med flera arbetsböcker, men det är lättare att jämföra listorna om du kopierar deras information till ett enda kalkylblad.
- 2Ge varje listobjekt en unik identifierare. Om dina två listor inte delar ett vanligt sätt att identifiera dem kan du behöva lägga till en extra kolumn i varje datalista som identifierar det objektet i Excel så att det kan se om ett objekt i en viss lista är relaterat till ett objekt i den andra listan. Typen av denna identifierare beror på vilken typ av data du försöker matcha. Du behöver en identifierare för varje kolumnlista.
- För finansiella data som är associerade med en viss period, till exempel med skatteregister, kan detta vara en beskrivning av en tillgång, det datum då tillgången förvärvades eller båda. I vissa fall kan en post identifieras med ett kodnummer. Om samma system inte används för båda listorna kan emellertid denna identifierare skapa matchningar där det inte finns några eller ignorera matchningar som ska göras.
- I vissa fall kan du ta objekt från en lista och kombinera dem med objekt från en annan lista för att skapa en identifierare, till exempel en beskrivning av fysiska tillgångar och det år den köpte. För att skapa en sådan identifierare sammanfogar du (lägger till, kombinerar) data från två eller flera celler med hjälp av ampersand (&). För att kombinera en artikelbeskrivning i cell F3 med ett datum i cell G3, åtskilt av ett mellanslag, anger du formeln '= F3 & "" & G3' i en annan cell i den raden, till exempel E3. Om du bara vill inkludera året i identifieraren (eftersom den ena listan använder fullständiga datum och den andra endast använder år), skulle du inkludera YEAR-funktionen genom att ange '= F3 & "" & YEAR (G3)' i cell E3 istället. (Inkludera inte de enskilda citaten; de finns bara för att ange exemplet.)
- När du har skapat formeln kan du kopiera den till alla andra celler i identifierarkolumnen genom att markera cellen med formeln och dra påfyllningshanteringen över de andra cellerna i kolumnen där du vill kopiera formeln. När du släpper musknappen fylls varje cell du drar över med formeln, med cellreferenser anpassade till lämpliga celler i samma rad.
- 3Standardisera data där det är möjligt. Medan sinnet känner igen att "Inc." och "Incorporated" betyder samma sak, Excel gör inte om du inte har det omformatera det ena eller det andra ordet. På samma sätt kan du betrakta värden som 8920€ och 8960€ som tillräckligt nära för att matcha, men Excel kommer inte om du inte berättar det.
- Du kan hantera vissa förkortningar, till exempel "Co" för "Company" och "Inc" för " Incorporated genom att använda VÄNSTER strängfunktion för att avkorta de ytterligare tecknen. Andra förkortningar, som" Assn "för" Association, "kan bäst vara hanteras genom att skapa en guide för datainmatningsstil och sedan skriva ett program för att leta upp och korrigera felaktiga format.
- För strängar av siffror, till exempel postnummer där vissa poster inkluderar ZIP + 4-suffixet och andra inte, kan du återigen använda VÄNSTER strängfunktionen för att känna igen och bara matcha de primära postnumren. För att Excel ska känna igen numeriska värden som är nära men inte samma, kan du använda funktionen RUND för att avrunda stängningsvärden till samma nummer och matcha dem.
- Extra mellanslag, som att skriva två mellanslag mellan ord istället för ett, kan tas bort med TRIM-funktionen.
Följande steg beskriver hur du använder var och en för att matcha dina data. - 4Skapa kolumner för jämförelseformeln. Precis som du var tvungen att skapa kolumner för listidentifierarna måste du skapa kolumner för formeln som gör jämförelsen för dig. Du behöver en kolumn för varje lista.
- Du vill märka dessa kolumner med något som "Saknas?"
- 5Ange jämförelsesformeln i varje cell. För jämförelseformeln använder du MATCH-funktionen inbäddad i en annan Excel-funktion, ISNA.
- Formeln har formen av "= ISNA (MATCH (G3, $ L2,20€: $ L10€, FALSE))", där en cell i identifierarkolumnen i den första listan jämförs mot var och en av identifierarna i den andra lista för att se om den matchar en av dem. Om den inte matchar saknas en post och ordet "TRUE" visas i den cellen. Om den matchar är posten närvarande och ordet "FALSE" visas. (När du anger formeln, inkludera inte de bifogade offerten.)
- Du kan kopiera formeln till de återstående cellerna i kolumnen på samma sätt som du kopierade cellidentifieringsformeln. I det här fallet ändras endast cellreferensen för identifieringscellen, eftersom att placera dollartecknen framför raden och kolumnreferenser för de första och sista cellerna i listan över de andra cellidentifierarna gör dem till absoluta referenser.
- Du kan kopiera jämförelseformeln för den första listan till den första cellen i kolumnen för den andra listan. Du måste sedan redigera cellreferenser så att "G3" ersätts med referensen för den första identifieringscellen i den andra listan och "$ L2,20€: $ L10€" ersätts med den första och sista identifieringscellen i den andra listan. (Lämna dollartecknen och kolon ensamma.) Du kan sedan kopiera den redigerade formeln till de återstående cellerna i jämförelseraden i den andra listan.
- 6Sortera listorna för att se icke-matchande värden lättare, om det behövs. Om dina listor är stora kan du behöva sortera dem för att sammanställa alla värden som inte matchar. Instruktionerna i delstegen nedan konverterar formlerna till värden för att undvika omberäkningsfel, och om dina listor är stora kommer en lång omberäkningstid att undvikas.
- Dra musen över alla celler i en lista för att välja den.
- Välj Kopiera från Redigera-menyn i Excel 2003 eller från Urklipp-gruppen i menyfliksområdet Hem i Excel 2007 eller 2010.
- Välj Klistra in Special från Redigera-menyn i Excel 2003 eller från rullgardinsmenyn Klistra in i Urklippsgruppen i Excel 2007 eller 2010-menyfliksområdet.
- Välj "Värden" i listan Klistra in i dialogrutan Klistra in special. Klicka på OK för att stänga dialogrutan.
- Välj Sortera från Data-menyn i Excel 2003 eller gruppen Sortera och filtrera i datafältet i Excel 2007 eller 2010.
- Välj "Rubrikrad" i listan "Mitt dataområde har" i dialogrutan Sortera efter, välj "Saknas?" (eller namnet du faktiskt gav jämförelsekolumnrubriken) och klicka på OK.
- Upprepa dessa steg för den andra listan.
- 7Jämför objekt som inte matchar visuellt för att se varför de inte matchar. Som tidigare nämnts är Excel utformat för att leta efter exakta dataträffar såvida du inte ställer in det för att leta efter ungefärliga. Din matchning kan vara så enkel som en oavsiktlig transponering av bokstäver eller siffror. Det kan också vara något som kräver oberoende verifiering, som att kontrollera om listade tillgångar behövde rapporteras från början.
Typen av denna identifierare beror på vilken typ av data du försöker matcha.
Metod 2 av 2: villkorlig formatering med countif
- 1Kopiera datalistan till ett enda kalkylblad.
- 2Bestäm i vilken lista du vill markera matchande eller icke-matchande poster. Om du bara vill markera poster i en lista kommer du antagligen att markera de poster som är unika för den listan. det vill säga poster som inte matchar poster i den andra listan. Om du vill markera poster i båda listorna vill du markera poster som matchar varandra. Vid tillämpningen av detta exempel antar vi att den första listan tar upp cellerna G3 till och med G14 och den andra listan tar upp cellerna L3 till och med L14.
- 3Välj objekten i listan som du vill markera unika eller matchande objekt i. Om du vill markera matchande objekt i båda listorna måste du välja listorna en i taget och tillämpa jämförelseformeln (beskrivs i nästa steg) till varje lista.
- 4Tillämpa lämplig jämförelseformel. För att göra detta måste du komma åt dialogrutan Villkorlig formatering i din version av Excel. I Excel 2003 gör du det genom att välja Villkorlig formatering från menyn Format, medan du i Excel 2007 och 2010 klickar på knappen Villkorlig formatering i gruppen Stilar i Hem-menyfliksområdet. Välj regeltyp som "Formel" och ange din formel i fältet Redigera regelbeskrivning.
- Om du vill markera poster som är unika för den första listan, skulle formeln vara "= COUNTIF ($ L2,20€: $ L10€, G3 = 0)", med cellområdet i den andra listan gjorda som absoluta värden och referensen till den första cellen i den första listan som ett relativt värde. (Ange inte de nära offerten.)
- Om du vill markera poster som är unika för den andra listan skulle formeln vara "= COUNTIF ($ G2,20€: $ G10€, L3 = 0)", med cellintervallet i den första listan gjorda som absoluta värden och referensen till den första cellen i den andra listan som ett relativt värde. (Ange inte de nära offerten.)
- Om du vill markera posterna i varje lista som finns i den andra listan behöver du två formler, en för den första listan och en för den andra. Formeln för den första listan är "= COUNTIF ($ L2,20€: $ L10€, G3> 0)", medan formeln för den andra listan är COUNTIF ($ G2,20€: $ G10€, L3> 0 Som tidigare nämnts väljer du den första listan för att tillämpa formeln och sedan den andra listan för att tillämpa formeln.
- Använd vilken formatering du vill för att markera de poster som flaggas. Klicka på OK för att stänga dialogrutan.
För att skapa en sådan identifierare sammanfogar du (lägger till, kombinerar) data från två eller flera celler med hjälp av ampersand (&).
- I stället för att använda en cellreferens med COUNTIF-villkorsformateringsmetoden kan du ange ett värde som ska sökas och flagga en eller flera listor för instanser av det värdet.
- För att förenkla jämförelseformulären kan du skapa namn för din lista, till exempel "List1" och "List2". När du sedan skriver formlerna kan dessa listnamn ersätta de absoluta cellområdena som används i exemplen ovan.
Läs också: Hur tar jag en skärmdump (skärmdump)?
