En av Microsoft Excel många möjligheter är möjligheten att jämföra två datalistor, identifiera matchningar mellan listorna och identifiera vilka objekt som finns i endast en lista. Detta är användbart när man 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 varje för att matcha dina data.
Steg
Metod 1 av 2: Identifiera poster med MATCH -funktionen
Steg 1. Kopiera 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.
Steg 2. Ge 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 ytterligare kolumn i varje datalista som identifierar det objektet i Excel så att det kan se om ett objekt i en given 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 skatteregistreringar, kan detta vara beskrivningen av en tillgång, datumet 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 den här identifieraren skapa matchningar där det inte finns några eller ignorera matchningar som bör 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 fysisk tillgångsbeskrivning och året då den köptes. 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 (&). Om du vill 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 på den raden, till exempel E3. Om du bara vill inkludera året i identifieraren (eftersom en lista använder fullständiga datum och den andra bara använder år), skulle du inkludera YEAR -funktionen genom att ange '= F3 & "" & YEAR (G3)' i cell E3 istället. (Inkludera inte de enda 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 fyllningshandtaget över de andra cellerna i kolumnen där du vill kopiera formeln. När du släpper musknappen fylls varje cell som du drog över med formeln, med cellreferenser justerade till lämpliga celler på samma rad.
Steg 3. Standardisera data där det är möjligt
Medan sinnet erkänner att "Inc." och "Införlivad" betyder samma sak, Excel gör det inte om du inte formaterar om det ena eller det andra ordet. På samma sätt kan du anse värden som $ 11, 950 och $ 11, 999,95 som tillräckligt nära för att matcha, men Excel kommer inte att göra det om du inte säger till 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ängfunktionen för att avkorta ytterligare tecken. Andra förkortningar, till exempel" Assn "för" Association ", kan bäst vara hanteras genom att upprätta en datainmatningsstilguide och sedan skriva ett program för att slå upp och korrigera felaktiga format.
- För siffror, till exempel postnummer där vissa poster inkluderar ZIP+4 -suffixet och andra inte, kan du igen använda VÄNSTER -strängfunktionen för att bara känna igen och 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 RUND -funktionen för att runda stänga vä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 hjälp av TRIM -funktionen.
Steg 4. Skapa 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?"
Steg 5. Ange jämförelseformeln i varje cell
För jämförelseformeln använder du MATCH -funktionen som finns i en annan Excel -funktion, ISNA.
- Formeln har formen "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", där en cell i identifierarkolumnen i den första listan jämförs med var och en av identifierarna i den andra listan med se om det stämmer överens med någon av dem. Om den inte matchar saknas en post och ordet "TRUE" visas i den cellen. Om det stämmer finns posten närvarande och ordet "FALSE" visas. (När du anger formeln, inkludera inte de bifogade citaten.)
- 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 sätta dollarstecknen 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 "$ L $ 3: $ L $ 14" ersätts med den första och sista identifieringscellen i andra listan. (Lämna dollartecknen och kolon i fred.) Du kan sedan kopiera den redigerade formeln till de återstående cellerna i jämförelseraden i den andra listan.
Steg 6. Sortera 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 sätta ihop alla icke-matchande värden. Instruktionerna i delstegen nedan kommer att omvandla formlerna till värden för att undvika omberäkningsfel, och om dina listor är stora kommer det att undvika en lång omräkningstid.
- Dra musen över alla celler i en lista för att markera den.
- Välj Kopiera från Redigera -menyn i Excel 2003 eller från Urklipp -gruppen på startbandet i Excel 2007 eller 2010.
- Välj Klistra in special från Redigera -menyn i Excel 2003 eller från rullgardinsmenyn Klistra in i Urklipp -gruppen i Excel 2007 eller 2010 -startsidan.
- Välj "Värden" från listan Klistra in som i dialogrutan Klistra in special. Klicka på OK för att stänga dialogrutan.
- Välj Sortera på menyn Data i Excel 2003 eller gruppen Sortera och filtrera i datafältet i Excel 2007 eller 2010.
- Välj "Rubrikrad" från listan "Mitt dataintervall har" i dialogrutan Sortera efter, välj "Saknas?" (eller namnet du faktiskt gav jämförelsekolumnhuvudet) och klicka på OK.
- Upprepa dessa steg för den andra listan.
Steg 7. Jämför objekt som inte matchar visuellt för att se varför de inte matchar
Som nämnts tidigare är Excel utformat för att leta efter exakta datamatchningar om 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, till exempel att kontrollera om listade tillgångar måste rapporteras i första hand.
Metod 2 av 2: Villkorlig formatering med COUNTIF
Steg 1. Kopiera datalistorna till ett enda kalkylblad
Steg 2. Bestäm i vilken lista du vill markera matchande eller icke-matchande poster
Om du bara vill markera poster i en lista vill du förmodligen markera posterna som är unika för 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. I detta exempel antar vi att den första listan tar upp cellerna G3 till G14 och den andra listan tar upp cellerna L3 till och med L14.
Steg 3. Markera 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) på varje lista.
Steg 4. Tillä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 startbandet. 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 ($ L $ 3: $ L $ 14, G3 = 0)", med cellintervallet i den andra listan som absolutvärden och referensen till den första cellen i den första listan som ett relativt värde. (Ange inte de slutliga citaten.)
- Om du vill markera poster som är unika för den andra listan skulle formeln vara "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", med cellintervallet i den första listan återgivet som absoluta värden och referensen till den första cellen i den andra listan som ett relativt värde. (Ange inte stängningstalen.)
- 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 listan. Formeln för den första listan är "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", medan formeln för den andra listan är COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Som tidigare noterat väljer du den första listan för att tillämpa dess formel och sedan den andra listan för att tillämpa dess formel.
- Använd vilken formatering du vill för att markera de poster som flaggas. Klicka på OK för att stänga dialogrutan.
Video - Genom att använda denna tjänst kan viss information delas med YouTube
Tips
- Istället för att använda en cellreferens med COUNTIF villkorlig formateringsmetod kan du ange ett värde som ska sökas efter och flagga en eller flera listor för instanser av det värdet.
- För att förenkla jämförelseformuläret kan du skapa namn för din lista, till exempel "List1" och "List2". När du skriver formlerna kan dessa listnamn ersätta de absoluta cellområden som används i exemplen ovan.
