Hur man jämför två listor i Excel

Excel är ett effektivt databehandlingsprogram. Och en av metoderna för informationsanalys är jämförelsen av två listor. Om du jämför två listor korrekt i Excel blir det mycket enkelt att organisera denna process. Det räcker med att bara följa några av punkterna som kommer att diskuteras i dag. Den praktiska implementeringen av denna metod beror helt på personens eller organisationens behov vid ett visst tillfälle. Därför bör flera möjliga fall övervägas.

Jämför två listor i Excel

Naturligtvis kan du jämföra två listor manuellt. Men det kommer att ta lång tid. Excel har sin egen intelligenta verktygslåda som låter dig jämföra data inte bara snabbt, utan också för att få information som inte är så lätt att få med dina ögon. Anta att vi har två kolumner med koordinaterna A och B. Vissa värden upprepas i dem.

Formulering av problemet

Så vi måste jämföra dessa kolumner. Förfarandet för att jämföra två dokument är som följer:

1. Om de unika cellerna i var och en av dessa listor är samma, och det totala antalet unika celler är detsamma, och cellerna är desamma, kan dessa listor betraktas som desamma. Ordningen i vilken värdena i denna lista staplas spelar inte så stor roll.
2. Vi kan prata om partiell sammanträffande av listor om de unika värdena själva är desamma, men antalet repetitioner är olika. Därför kan sådana listor innehålla ett annat antal element.
3. Det faktum att de två listorna inte matchar indikeras av en annan uppsättning unika värden.

Alla dessa tre villkor är samtidigt villkoren för vårt problem.

Lösningen på problemet

Låt oss generera två dynamiska intervall för att göra det lättare att jämföra listor. Var och en av dem kommer att motsvara var och en av listorna.

För att jämföra två listor, gör följande:

1. I en separat kolumn skapar vi en lista med unika värden som är specifika för båda listorna. För detta använder vi formeln: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Själva formeln måste skrivas som en matrisformel.
2. Låt oss avgöra hur många gånger varje unikt värde förekommer i datamatrisen. Här är formlerna för att göra detta: =ANTALOM(List1,D5) och =ANTALI(List2,D5).
3. Om både antalet repetitioner och antalet unika värden är samma i alla listor som ingår i dessa intervall, returnerar funktionen värdet 0. Detta indikerar att matchningen är XNUMX%. I det här fallet kommer rubrikerna på dessa listor att få en grön bakgrund.
4. Om allt unikt innehåll finns i båda listorna, returneras det av formler =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) värdet blir noll. Om E1 inte innehåller noll, men ett sådant värde finns i cellerna E2 och F2, kommer i detta fall intervallen att kännas igen som matchande, men bara delvis. I det här fallet kommer rubrikerna på motsvarande listor att bli orange.
5. Och om en av formlerna som beskrivs ovan returnerar ett värde som inte är noll, kommer listorna att vara helt icke-matchande.

Detta är svaret på frågan om hur man analyserar kolumner för matchningar med hjälp av formler. Som du kan se, med användning av funktioner, kan du implementera nästan vilken uppgift som helst som vid första anblicken inte är relaterad till matematik.

Exempel Testning

I vår version av tabellen finns det tre typer av listor av varje typ som beskrivs ovan. Den har delvis och helt matchande, såväl som icke-matchande.

För att jämföra data använder vi området A5:B19, där vi växelvis infogar dessa listpar. Om vad som kommer att bli resultatet av jämförelsen kommer vi att förstå med färgen på originallistorna. Om de är helt olika, så blir det en röd bakgrund. Om en del av informationen är densamma, då gul. Vid fullständig identitet kommer motsvarande rubriker att vara gröna. Hur gör man en färg beroende på vad resultatet blir? Detta kräver villkorlig formatering.

Att hitta skillnader i två listor på två sätt

Låt oss beskriva ytterligare två metoder för att hitta skillnader, beroende på om listorna är synkrona eller inte.

Alternativ 1. Synkrona listor

Detta är ett enkelt alternativ. Anta att vi har sådana listor.

För att bestämma hur många gånger värdena inte konvergerade, kan du använda formeln: =SUMMAPRODUKT(—(A2:A20<>B2:B20)). Om vi ​​fick 0 som ett resultat betyder det att de två listorna är desamma.

Alternativ 2: Blandade listor

Om listorna inte är identiska i ordningen på objekten de innehåller måste du använda en funktion som villkorlig formatering och färglägga dubbletter av värden. Eller använd funktionen COUNTIF, med hjälp av vilken vi bestämmer hur många gånger ett element från en lista förekommer i den andra.

Hur man jämför 2 kolumner rad för rad

När vi jämför två kolumner behöver vi ofta jämföra information som finns på olika rader. För att göra detta kommer operatören att hjälpa oss OM. Låt oss ta en titt på hur det fungerar i praktiken. För att göra detta presenterar vi flera illustrativa situationer.

Exempel. Hur man jämför 2 kolumner för matchningar och skillnader i en rad

För att analysera om värdena som finns i samma rad men olika kolumner är desamma, skriver vi funktionen IF. Formeln infogas i varje rad som placeras i hjälpkolumnen där resultaten av databehandlingen kommer att visas. Men det är inte alls nödvändigt att föreskriva det i varje rad, kopiera det bara till de återstående cellerna i den här kolumnen eller använd autokompletteringsmarkören.

Vi bör skriva ner en sådan formel för att förstå om värdena i båda kolumnerna är desamma eller inte: =OM(A2=B2, "Match", ""). Logiken i denna funktion är mycket enkel: den jämför värdena i cellerna A2 och B2, och om de är desamma visar den värdet "sammanfaller". Om data är annorlunda returnerar den inget värde. Du kan också kontrollera cellerna för att se om det finns en matchning mellan dem. I det här fallet är formeln som används: =OM(A2<>B2, “Stämmar inte”, “”). Principen är densamma, först utförs kontrollen. Om det visar sig att cellerna uppfyller kriteriet, visas värdet "Stämmer inte".

Det är också möjligt att använda följande formel i formelfältet för att visa både "Match" om värdena är desamma och "Matchar inte" om de är olika: =OM(A2=B2; "Match", "Matchar inte"). Du kan också använda olikhetsoperatorn istället för likhetsoperatorn. Endast ordningen på värdena som kommer att visas i det här fallet kommer att vara något annorlunda: =OM(A2<>B2, “Stämmar inte”, “Sammanfaller”). Efter att ha använt den första versionen av formeln blir resultatet som följer.

Denna variant av formeln är skiftlägesokänslig. Därför, om värdena i en kolumn skiljer sig från andra endast genom att de är skrivna med versaler, kommer programmet inte att märka denna skillnad. För att göra jämförelsen skiftlägeskänslig måste du använda funktionen i kriterierna EXAKT. Resten av argumenten lämnas oförändrade: =OM(EXAKT(A2,B2), "Match", "Unik").

Hur man jämför flera kolumner för matchningar på en rad

Det är möjligt att analysera värdena i listorna enligt en hel uppsättning kriterier:

1. Hitta de rader som har samma värden överallt.
2. Hitta de rader där det finns matchningar i bara två listor.

Låt oss titta på några exempel på hur man går tillväga i vart och ett av dessa fall.

Exempel. Hur man hittar matchningar på en rad i flera kolumner i en tabell

Anta att vi har en serie kolumner som innehåller den information vi behöver. Vi står inför uppgiften att bestämma de rader där värdena är desamma. För att göra detta måste du använda följande formel: =OM(OCH(A2=B2,A2=C2), "matcha", " ").

Om det finns för många kolumner i tabellen behöver du bara använda den tillsammans med funktionen IF Operatören COUNTIF: =OM(ANTALOM($A2:$C2,$A2)=3;"match";" "). Siffran som används i denna formel anger antalet kolumner som ska kontrolleras. Om det skiljer sig måste du skriva så mycket som är sant för din situation.

Exempel. Hur man hittar matchningar på en rad i två valfria kolumner i en tabell

Låt oss säga att vi måste kontrollera om värdena i en rad matchar i två kolumner från de i tabellen. För att göra detta måste du använda funktionen som ett villkor OR, där växelvis skriv likheten mellan var och en av kolumnerna till den andra. Här är ett exempel.

Vi använder denna formel: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Det kan uppstå en situation när det finns många kolumner i tabellen. I det här fallet kommer formeln att vara enorm, och det kan ta mycket tid att välja alla nödvändiga kombinationer. För att lösa detta problem måste du använda funktionen COUNTIF: =OM(ANTALOM(B2:D2,A2)+ANTALOM(C2:D2,B2)+(C2=D2)=0; “Unik sträng”; “Inte unik sträng”)

Vi ser att vi totalt sett har två funktioner COUNTIF. Med den första bestämmer vi växelvis hur många kolumner som har en likhet med A2, och med den andra kontrollerar vi antalet likheter med värdet på B2. Om vi, som ett resultat av beräkningar med denna formel, får ett nollvärde, indikerar detta att alla rader i denna kolumn är unika, om fler finns det likheter. Därför, om vi som ett resultat av att beräkna med två formler och lägga till de slutliga resultaten får ett nollvärde, returneras textvärdet "Unik sträng", om detta nummer är större, skrivs det att denna sträng inte är unik.

Hur man jämför 2 kolumner i Excel för matchningar

Låt oss nu ta ett exempel. Låt oss säga att vi har en tabell med två kolumner. Du måste kolla om de matchar. För att göra detta måste du tillämpa formeln, där funktionen också kommer att användas IFoch operatören COUNTIF: =OM(ANTALOM($B:$B,$A5)=0, "Inga matchningar i kolumn B", "Det finns matchningar i kolumn B")

Ingen ytterligare åtgärd krävs. Efter att ha beräknat resultatet med denna formel får vi if värdet av det tredje argumentet för funktionen IF tändstickor. Om det inte finns några, då innehållet i det andra argumentet.

Hur man jämför 2 kolumner i Excel för matchningar och markera med färg

För att göra det lättare att visuellt identifiera matchande kolumner kan du markera dem med en färg. För att göra detta måste du använda funktionen "Villkorlig formatering". Låt oss se i praktiken.

Hitta och markera matchningar efter färg i flera kolumner

För att bestämma matchningarna och markera dem måste du först välja dataintervallet där kontrollen ska utföras och sedan öppna alternativet "Villkorlig formatering" på fliken "Hem". Där väljer du "Duplicera värden" som cellvalsregel.

Därefter visas en ny dialogruta, där vi i den vänstra popup-listan hittar alternativet "Repeating", och i den högra listan väljer vi färgen som ska användas för valet. När vi klickar på "OK" -knappen kommer bakgrunden för alla celler med likheter att väljas. Sedan är det bara att jämföra kolumnerna med ögat.

Hitta och markera matchande linjer

Tekniken för att kontrollera om strängarna matchar är något annorlunda. Först måste vi skapa en extra kolumn, och där kommer vi att använda de kombinerade värdena med &-operatorn. För att göra detta måste du skriva en formel av formuläret: =A2&B2&C2&D2.

Vi väljer kolumnen som skapades och innehåller de kombinerade värdena. Därefter utför vi samma sekvens av åtgärder som beskrivs ovan för kolumnerna. Dubblettlinjer kommer att markeras i den färg du anger.

Vi ser att det inte är något svårt att leta efter upprepningar. Excel innehåller alla nödvändiga verktyg för detta. Det är viktigt att bara öva innan man omsätter all denna kunskap i praktiken.