Hur man jämför data på Excel

En av de många funktionerna i Microsoft Excel är möjligheten att jämföra två listor med data, identifiera matchningarna i listorna och vilka element som endast finns i en av de två. Detta kan vara användbart för att jämföra finansiella poster eller kontrollera om ett visst namn finns i en databas. Du kan använda COMPARE-funktionen för att hitta och markera data med eller utan matchningar, annars använd betingad formatering med CONTASE-funktionen. Följande steg förklarar hur du använder båda dessa metoder för att jämföra dina data.

Metod 1

Identifiera lika element med COMPARE-funktionen

Kopiera datalistor till ett kalkylblad. Excel kan fungera på flera kalkylblad inom en eller flera arksatser, men det är lättare att jämföra två listor om datakällorna finns på samma ark.

Tilldela en unik identifierare till varje element i listorna. Om de två listorna inte delar en identifieringsmetod kan du behöva lägga till en kolumn till båda elementen som identifierar elementen så att Excel kan jämföra dem. Välj identifieringssystemet baserat på vilken typ av data du vill jämföra. Du måste använda den för båda listorna.

För finansiella uppgifter som är kopplade till en viss tidsperiod, till exempel för avkastningen, kan du använda beskrivningen av tillgångarna, det datum då tillgången förvärvades eller båda. I vissa fall kan du identifiera uppgifterna med en kod - men om samma system inte används för båda listorna kan denna typ av identifiering skapa falska matchningar eller inte detektera objekt i båda listorna.

I vissa fall kan du ta del av en lista och kombinera dem med de andra för att skapa ett identifieringssystem, såsom beskrivning av en fysisk tillgång och inköpsåret. För att göra detta sammanfoga (summa eller kombinera) data för två eller flera celler med hjälp av &. För att kombinera beskrivningen av ett objekt i cell F3 med datumet i cell G3, separerat med ett mellanslag, använd formeln = F3&" "&G3 i en annan cell i samma rad, till exempel E3. Om du föredrar att bara inkludera året i identifieraren (eftersom en lista använder fullständiga datum och de andra endast åren) kan du använda funktionen YEAR, skriva = F3&" "&ÅR (G3) i cell E3.

När formeln är skapad kan du kopiera den till alla andra celler i identifieringskolonnen, välja cellen med formeln och dra fyllningsmarkören över de andra. När musknappen släpps kopieras formeln till alla celler du har valt, med rätt referenser baserade på raden.

När du har chansen, följ samma datastandard. Även om det är lätt för oss att förstå det "Corporation" och "S.p.A." De är samma sak, Excel kan inte göra detsamma om du inte skriver de två termerna på samma sätt. Av samma anledning kanske du tror att 11.950 och 11.999.95 är ganska lika värden som anses vara lika, men de kommer inte att vara Excel om du inte uttryckligen uttrycker det.

Du kan hantera vissa förkortningar, som "Soc" för "företaget" och "int" för "internationell" Använd funktionen VÄNSTER för att avkorta ytterligare tecken. Det är bättre att hantera de andra förkortningarna, till exempel "spa" för "Corporation" skapa en unik stil för att formatera data, sedan skriva ett program för att söka efter och korrigera fel.

För strängar av siffror som i vissa fall har ett suffix och nej, kan du använda funktionen VÄNSTER igen för att känna igen och jämföra endast de första siffrorna. För att sortera ut exakta liknande men inte identiska numeriska värden kan du använda RUND-funktionen för att approximera siffrorna till närmaste heltal.

Du kan ta bort extra mellanslag, till exempel ett dubbelt mellanslag mellan ord, med funktionen CANCEL.

Skapa kolumner för jämförelsesformeln. Precis som du var tvungen att skapa dem för listidentifierarna måste du göra detsamma för formeln som handlar om jämförelsen. Lägg till en kolumn efter lista.

Du kan ge titeln "Saknas?" till kolumnerna.

Ange jämförelsesformeln i alla celler. Du använder funktionen FÖRÄLJNING inom den här VAL.NON.DISP.

Formeln har formatet = ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE)), i vilken en cell i kolumnen för identifiering av den första listan jämförs med samtliga identifierings kolumnen i den andra listan , letar efter matcher. Om det inte finns någon match är elementet inte närvarande och ordet kommer att visas "SANN" i cellen. Om det är närvarande kommer ordet att visas "FALSK".

Du kan kopiera formeln till de återstående cellerna precis som du gjorde för identifieringen. I detta fall kommer endast referensnumret för identifieringscellen att ändras, eftersom dollarns tecken före de första och sista cellerna i identifieringskolumnen i den andra listan gör dem absoluta referenser.

Du kan kopiera jämförelsesformeln för den första listan i den första cellen i kolumnen för den andra listan. På den tiden kommer det att räcka för att ändra referenserna för att ersätta dem "G3" med den första cellen i identifieringskolumnen i den andra listan e "$ L $ 3: $ L $ 14" med den första och sista cellen i identifieringskolumnen i den första listan (ändra inte dollartymbolerna och kolon.

Om det är nödvändigt, sortera listorna för att visa oöverträffade värden lättare. Om listorna innehåller mycket data kan du sortera dem så att alla oöverträffade objekt grupperas ihop. Instruktionerna i följande steg omvandlar formlerna till värden för att undvika omräkningsfel och, om listorna är stora, minska tiden.

Dra musen över alla celler för att välja dem.

Välj Kopiera från Redigera-menyn i Excel 2003 eller från Urklippsgruppen i avsnittet Ribbon Home på Excel 2007 eller 2010.

Välj Klistra in special från Redigera-menyn i Excel 2003 eller från Paste-knappen i Clipboard-gruppen i avsnittet Ribbon Home på Excel 2007 eller 2010.

Välj "värden" från listan Pasta som i fönstret Klistra in. Klicka på OK för att stänga fönstret.

Välj Sortera från Data-menyn i Excel 2003 eller Sortera och filtrera gruppen från avsnittet Ribbon Data i Excel 2007 eller 2010.

Välj "Rubrikrad" från "Mitt dataintervall har" Markera i fönstret Sortera efter "Saknas?" (eller det namn du tilldelade jämförelsekolonnen) och klicka sedan på OK.

Upprepa samma steg för den andra listan.

Jämför visuellt oöverträffade objekt för att kontrollera varför de inte finns på båda listorna. Som tidigare nämnts är Excel utformat för att leta efter exakta matchningar om inte orderna ska approximera sökningen. Ojämlikheten kan helt enkelt bero på ett skrivfel eller av en självständig anledning, till exempel en bra present i en lista men inte att deklareras i den andra.

Metod 2

Villkorlig formatering med CONTASE

Kopiera datalistor till ett kalkylblad.

Bestäm i vilken lista som ska markera objekt med eller utan korrespondens. Om du vill göra det på bara en lista vill du förmodligen bara hitta poster i den där. Om du vill markera uppgifterna i båda listorna, leta efter de som visas i båda. För vårt exempel kommer vi att överväga att den första listan går från cell G3 till G14 och den andra från L3 till L14.

Markera objekten i listan där du vill markera unika eller matchade objekt. Om du vill markera element i båda listorna måste du välja dem en i taget och tillämpa jämförelsesformeln på varje (beskrivs i nästa steg).

Applicera lämplig jämförelsesformel. För att göra detta måste du öppna fönstret Villkorlig formatering av din version av Excel. I Excel 2003 kan du göra detta genom att välja Villkorlig formatering från menyn Format, medan i Excel 2007 och 2010 kan du klicka på knappen Villkorlig formatering i gruppen Stilar i avsnittet Hem i bandet. Välj regeltyp som "formeln" och ange formeln i fältet Redigera regelbeskrivning.

Om du vill markera de element som är närvarande endast i den första listan är formeln = CONTINUE ($ L $ 3: $ L $ 14, G3 = 0), med cellintervallet för den andra listan inmatad som ett absolut värde medan referensen till den första cellen av den första listan är relativt.

Om du vill markera de element som är närvarande endast i den andra listan är formeln = CONTINUE ($ G $ 3: $ G $ 14, L3 = 0), med cellintervallet för den första listan inmatad som ett absolut värde medan referensen till den första cellen av den andra listan är relativt.

Om du vill markera de element som visas i båda listorna behöver du två formler, en för den första listan och en för den andra. Den första är = CONTINUE ($ L $ 3: $ L $ 14, G3>0), medan den andra är FORTSATT ($ G $ 3: $ G $ 14, L3>0). Som tidigare nämnts måste du välja den första listan för att tillämpa dess formel, gör sedan samma med den andra.

Använd formatering som låter dig markera de celler som intresserar dig. Klicka på OK för att stänga fönstret.

tips

I stället för att använda en cellreferens med CONTASE villkorlig formatering, kan du ange ett värde för att söka efter och rapportera i en eller flera listor varje gång den visas.
För att förenkla jämförelsesformler kan du tilldela namn till listor, t.ex. "List1" och "List2". På så sätt kan du ersätta namn för absoluta cellintervall i formler.

Dela på sociala nätverk:

Relaterade