Du kan hitta och markera dubblettposterna mellan två kolumner med funktionen Villkorlig formatering i Google Kalkylark.
När du arbetar i Google Sheets med stora datamängder stöter du förmodligen på ett problem där du måste hantera många dubbletter av värden. Medan vissa dubbletter av poster placeras avsiktligt medan andra är misstag. Detta gäller särskilt när du samarbetar på samma ark med ett team.
När det gäller att analysera data på Google Sheets kan det vara viktigt och bekvämt att filtrera bort dubbletter. Även om Google Sheets inte har något inbyggt stöd för att hitta dubbletter i ark, erbjuder det flera sätt att jämföra, identifiera och ta bort dubbletter av data i celler.
Ibland vill du jämföra varje värde i en kolumn med en annan kolumn och se om det finns några dubbletter i den och vice versa. I Google Sheets kan du enkelt hitta dubbletter mellan två kolumner med hjälp av funktionen för villkorlig formatering. I den här artikeln kommer vi att visa dig hur du jämför två kolumner i Google Sheets och hittar dubbletter mellan dem.
Hitta dubbletter av poster mellan två kolumner med villkorlig formatering
Villkorlig formatering är en funktion i Google Kalkylark som gör att användaren kan tillämpa specifika formateringar som teckensnittsfärg, ikoner och datafält på en cell eller cellintervall baserat på vissa villkor.
Du kan använda denna villkorliga formatering för att markera dubblettposterna mellan två kolumner, antingen genom att fylla cellerna med färg eller ändra textfärgen. Du måste jämföra varje värde i en kolumn mot en annan kolumn och se om något värde upprepas. För att detta ska fungera måste du tillämpa villkorlig formatering på varje kolumn separat. Följ dessa steg för att göra det:
Öppna kalkylarket du vill kontrollera efter dubbletter i Google Kalkylark. Välj först den första kolumnen (A) för att kontrollera med kolumn B. Du kan markera hela kolumnen genom att klicka på kolumnbokstaven ovanför den.
Klicka sedan på "Format"-menyn i menyraden och välj "Villkorlig formatering".
Menyn för villkorlig formatering öppnas på höger sida av Googles ark. Du kan bekräfta att cellintervallet är det du valde under alternativet "Använd på intervall". Om du vill ändra intervallet, klicka på "intervallikonen" och välj ett annat intervall.
Klicka sedan på rullgardinsmenyn under "Formatregler" och välj alternativet "Anpassad formel är".
Nu måste du ange en anpassad formel i rutan "Värde eller formel".
Om du valde en hel kolumn (B:B), skriv in följande COUNTIF-formel i rutan "Värde eller formel" under Formatregler:
=countif($B:$B,$A2)>0Eller,
Om du valde ett cellintervall i en kolumn (säg hundra celler, A2:A30), använd den här formeln:
=ANTALOM($B$2:$B$30, $A2)>0När du anger formeln, se till att ersätta alla instanser av bokstaven "B" i formeln med bokstaven i kolumnen du har markerat. Vi lägger till "$"-tecknet före cellreferenserna för att göra dem till ett absolut intervall, så att det inte ändrar att vi tillämpar formeln.
I avsnittet Formateringsstil kan du välja formateringsstil för att markera dubblettobjekten. Som standard kommer den att använda den gröna fyllningsfärgen.
Du kan välja en av de förinställda formateringsstilarna genom att klicka på "Standard" under alternativen "Formateringsstil" och sedan välja en av förinställningarna.
Eller så kan du använda något av de sju formateringsverktygen (fet, kursiv, understruken, genomstruken, textfärg, fyllningsfärg) under avsnittet "Formateringsstil" för att markera dubbletterna.
Här väljer vi en fyllningsfärg för dubblettcellerna genom att klicka på ikonen "Fyllfärg" och välja den "gula" färgen.
När du har valt formateringen klickar du på "Klar" för att markera cellerna.
COUNTIF-funktionen räknar hur många gånger varje cellvärde i "kolumn A" visas i "kolumn B". Så om ett objekt förekommer ens en gång i kolumn B, returnerar formeln TRUE. Sedan kommer det objektet att markeras i "Kolumn A" baserat på den formatering du valde.
Detta markerar inte dubbletterna, utan det framhäver snarare de objekt som har dubbletter i kolumn B. Det betyder att varje gult markerat objekt har dubbletter i kolumn B.
Nu måste vi tillämpa villkorlig formatering på kolumn B med samma formel. För att göra det, välj den andra kolumnen (B2:B30), gå till menyn "Format" och välj "Villkorlig formatering".
Alternativt klickar du på knappen "Lägg till en annan regel" under rutan "Villkorliga formatregler".
Bekräfta sedan intervallet (B2:B30) i rutan "Apply to range".
Ställ sedan in alternativet "Formatera celler om..." till "Anpassad formel är" och ange formeln nedan i formelrutan:
=COUNTIF($A$2:$A$30, $B2)>0Här använder vi kolumn A-intervall ($A$2:$A$30) i det första argumentet och '$B2' i det andra argumentet. Den här formeln kontrollerar cellvärdet i kolumn B mot varje cell i kolumn A. Om en matchning (dubblett) hittas, kommer villkorlig formatering att höja objektet i kolumn B
Ange sedan formateringen i alternativen "Formateringsstil" och klicka på "Klar". Här väljer vi den orange färgen för kolumn B.
Detta kommer att markera kolumn B-objekt som har dubbletter i kolumn A. Nu har du hittat och markerat dubbletter mellan två kolumner.
Du har säkert märkt, även om det finns en dubblett för 'Arcelia' i kolumn A, är den inte markerad. Det beror på att duplikatvärdet bara finns i en kolumn (A) inte mellan kolumner. Därför är det inte markerat.
Markera dubbletter mellan två kolumner i samma rad
Du kan också markera de rader som har samma värden (dubbletter) mellan två kolumner med villkorlig formatering. Den villkorliga formateringsregeln kan kontrollera varje rad och markerar de rader som har matchande data i båda kolumnerna. Så här gör du:
Välj först båda kolumnerna som du vill jämföra, gå sedan till menyn "Format" och välj "Villkorlig formatering".
I rutan Villkorliga formatregler bekräftar du intervallet i rutan "Använd på intervall" och väljer "Anpassad formel är" från rullgardinsmenyn "Formelceller om...".
Ange sedan formeln nedan i rutan "Värde eller formel":
=$A2=$B2Denna formel kommer att jämföra de två kolumnerna rad för rad och markera rader som har identiska värden (dubbletter). Som du kan se är formeln som anges här endast för den första raden i det valda intervallet, men formeln kommer automatiskt att tillämpas på alla rader i det valda intervallet av funktionen för villkorlig formatering.
Ange sedan formateringen från alternativen "Formateringsstil" och klicka på "Klar".
Som du kan se kommer bara de rader som har matchande data (dubbletter) mellan två kolumner att markeras och alla andra dubbletter kommer att ignoreras.
Markera Duplicera celler i flera kolumner
När du arbetar med större kalkylblad med många kolumner kanske du vill markera alla dubbletter som visas över flera kolumner istället för bara en eller två kolumner. Du kan fortfarande använda villkorlig formatering för att markera dubbletten i flera kolumner.
Välj först intervallet för alla kolumner och rader du vill söka efter dubbletter istället för bara en eller två kolumner. Du kan välja hela kolumner genom att hålla ned Ctrl-tangenten och sedan klicka på bokstaven överst i varje kolumn. Alternativt kan du också klicka på den första och sista cellen i ditt område samtidigt som du håller ned Skift-tangenten för att välja flera kolumner samtidigt.
I exemplet väljer vi A2:C30.
Klicka sedan på alternativet "Format" i menyn och välj "Villkorlig formatering".
I reglerna för villkorligt format ställer du in formatreglerna till "Anpassad formel är" och anger sedan följande formel i rutan "Värde eller formel":
=countif($A$2:$C$30,A2)>Vi lägger till "$"-tecknet före cellreferenserna för att göra dem till absoluta kolumner, så att det inte ändrar att vi tillämpar formeln. Du kan också ange formeln utan "$"-tecknen, det fungerar åt båda hållen.
Välj sedan formateringen där du vill markera dubblettcellerna med hjälp av alternativen "Formateringsstil". Här väljer vi "Gul" fyllningsfärg. Efter det klickar du på "Klar".
Detta kommer att markera dubbletterna i alla kolumner du valt, som visas nedan.
Efter att ha tillämpat den villkorliga formateringen kan du redigera eller ta bort den villkorliga formateringsregeln när du vill.
Om du vill redigera den aktuella regeln för villkorlig formatering, välj valfri cell med villkorlig formatering, gå till "Format" på menyn och välj "Villkorlig formatering".
Detta öppnar rutan "Villkorliga formatregler" till höger med en lista över formatregler som tillämpas på det aktuella valet. När du för musen över regeln visar den raderingsknappen, klicka på raderingsknappen för att ta bort regeln. Eller, om du vill redigera regeln som för närvarande visas, klicka på själva regeln.
Om du vill lägga till ytterligare en villkorlig formatering över den aktuella regeln klickar du på knappen "Lägg till en annan regel".
Räkna dubbletter mellan två kolumner
Ibland vill du räkna antalet gånger ett värde i en kolumn upprepas i en annan kolumn. Det kan enkelt göras med samma COUNTIF-funktion.
För att ta reda på hur många gånger ett värde i kolumn A finns i kolumn B anger du följande formel i en cell i en annan kolumn:
=COUNTIF($B$2:$B$30,$A2)Ange denna formel i cell C2. Den här formeln räknar antalet gånger värdet i cell A2 finns i kolumnen (B2:B30) och returnerar antalet i cell C2.
När du skriver formeln och trycker på Enter, kommer Autofyll-funktionen att visas, klicka på "Bocken" för att autofylla denna formel till resten av cellerna (C3:C30).
Om autofyll-funktionen inte visas klickar du på den blå fyrkanten i det nedre högra hörnet av cell C2 och drar den nedåt för att kopiera formeln i cell C2 till cellerna C3:C30.
'Jämförelse 1' kolumn (C) visar dig nu hur många gånger varje motsvarande värde i kolumn A visas i kolumn B. Till exempel, värdet på A2 eller "Franklyn" finns inte i kolumn B, så COUNTIF-funktionen returnerar "0". Och värdet "Loreta" (A5) finns två gånger i kolumn B, och returnerar därför "2".
Nu måste vi upprepa samma steg för att hitta dubbletter av kolumn B. För att göra det, skriv in följande formel i cell D2 i kolumn D (jämförelse 2):
=COUNTIF($A$2:$A$30,$B2)I den här formeln ersätter du intervallet från '$B$2:$B$30' till '$A$2:$A$30' och '$B2' till '$A2'. Funktionen räknar antalet gånger värdet i cell B2 finns i kolumn A (A2:A30) och returnerar antalet i cell D2.
Fyll sedan i formeln automatiskt till resten av cellerna (D3:D30) i kolumn D. Nu kommer 'Jämförelse 2' att visa dig hur många gånger varje motsvarande värde i kolumn B visas i kolumn A. Till exempel , värdet på B2 eller "Stark" hittas två gånger i kolumn A, så funktionen COUNTIF returnerar "2".
Notera: Om du vill räkna dubbletterna över alla kolumner eller flera kolumner behöver du bara ändra intervallet i det första argumentet för COUNTIF-funktionen till flera kolumner istället för bara en kolumn. Ändra till exempel intervallet från A2:A30 till A2:B30, vilket kommer att räkna alla dubbletter i två kolumner istället för bara en.
Det är allt.