U kunt de dubbele vermeldingen tussen twee kolommen vinden en markeren met de functie Voorwaardelijke opmaak in Google Spreadsheets.
Tijdens het werken in Google Spreadsheets met grote datasets, loop je waarschijnlijk tegen een probleem aan waarbij je te maken krijgt met veel dubbele waarden. Terwijl sommige dubbele vermeldingen opzettelijk zijn geplaatst, terwijl andere fouten zijn. Dit is vooral het geval wanneer u met een team aan hetzelfde blad samenwerkt.
Als het gaat om het analyseren van gegevens op Google Spreadsheets, kan het essentieel en handig zijn om duplicaten uit te filteren. Hoewel Google Spreadsheets geen native ondersteuning biedt voor het vinden van duplicaten in bladen, biedt het verschillende manieren om dubbele gegevens in cellen te vergelijken, identificeren en verwijderen.
Soms wilt u elke waarde in een kolom vergelijken met een andere kolom en zien of er dubbele waarden in staan en vice versa. In Google Spreadsheets kunt u eenvoudig duplicaten tussen twee kolommen vinden met behulp van de voorwaardelijke opmaakfunctie. In dit artikel laten we u zien hoe u twee kolommen in Google Spreadsheets kunt vergelijken en dubbele kolommen kunt vinden.
Vind dubbele vermeldingen tussen twee kolommen met behulp van voorwaardelijke opmaak
Voorwaardelijke opmaak is een functie in Google Spreadsheets waarmee de gebruiker specifieke opmaak, zoals tekstkleur, pictogrammen en gegevensbalken, kan toepassen op een cel of celbereik op basis van bepaalde voorwaarden.
U kunt deze voorwaardelijke opmaak gebruiken om dubbele vermeldingen tussen twee kolommen te markeren, door de cellen met kleur te vullen of door de tekstkleur te wijzigen. U moet elke waarde in een kolom vergelijken met een andere kolom en nagaan of een waarde wordt herhaald. Om dit te laten werken, moet u voorwaardelijke opmaak op elke kolom afzonderlijk toepassen. Volg deze stappen om dat te doen:
Open de spreadsheet die u op duplicaten wilt controleren in Google Spreadsheets. Selecteer eerst de eerste kolom (A) om te controleren met kolom B. U kunt de hele kolom markeren door op de kolomletter erboven te klikken.
Klik vervolgens op het menu 'Formaat' in de menubalk en selecteer 'Voorwaardelijke opmaak'.
Het menu Voorwaardelijke opmaak wordt geopend aan de rechterkant van de Google-bladen. U kunt bevestigen dat het celbereik is wat u hebt geselecteerd onder de optie 'Toepassen op bereik'. Als u het bereik wilt wijzigen, klikt u op het 'bereikpictogram' en kiest u een ander bereik.
Klik vervolgens op de vervolgkeuzelijst onder 'Regels opmaken' en selecteer de optie 'Aangepaste formule is'.
Nu moet u een aangepaste formule invoeren in het vak 'Waarde of formule'.
Als u een hele kolom (B:B) hebt geselecteerd, voert u de volgende AANTAL.ALS-formule in het vak 'Waarde of formule' onder Opmaakregels in:
=countif($B:$B,$A2)>0Of,
Als u een celbereik in een kolom hebt geselecteerd (bijvoorbeeld honderd cellen, A2:A30), gebruikt u deze formule:
=AANTAL.ALS($B$2:$B$30, $A2)>0Wanneer u de formule invoert, moet u ervoor zorgen dat alle instanties van de letter 'B' in de formule worden vervangen door de letter van de kolom die u hebt gemarkeerd. We voegen het '$'-teken toe voor de celverwijzingen om ze een absoluut bereik te maken, dus het verandert niet dat we de formule toepassen.
In het gedeelte Opmaakstijl kunt u de opmaakstijl kiezen om de dubbele items te markeren. Standaard wordt de groene vulkleur gebruikt.
U kunt een van de vooraf ingestelde opmaakstijlen kiezen door op 'Standaard' onder de opties 'Opmaakstijl' te klikken en vervolgens een van de voorinstellingen te selecteren.
Of u kunt een van de zeven opmaakhulpmiddelen (vet, cursief, onderstrepen, doorhalen, tekstkleur, vulkleur) onder het gedeelte 'Opmaakstijl' gebruiken om de duplicaten te markeren.
Hier kiezen we een opvulkleur voor de dubbele cellen door op het pictogram 'Opvulkleur' te klikken en de 'gele' kleur te selecteren.
Nadat u de opmaak hebt geselecteerd, klikt u op 'Gereed' om de cellen te markeren.
De AANTAL.ALS-functie telt hoe vaak elke celwaarde in 'Kolom A' wordt weergegeven in 'Kolom B'. Dus als een item maar één keer voorkomt in kolom B, retourneert de formule TRUE. Vervolgens wordt dat item gemarkeerd in 'Kolom A' op basis van de opmaak die u hebt gekozen.
Dit markeert niet de duplicaten, maar markeert eerder de items die duplicaten hebben in kolom B. Dat betekent dat elk geel gemarkeerd item duplicaten heeft in kolom B.
Nu moeten we voorwaardelijke opmaak toepassen op kolom B met dezelfde formule. Selecteer hiervoor de tweede kolom (B2:B30), ga naar het menu 'Formaat' en selecteer 'Voorwaardelijke opmaak'.
U kunt ook op de knop 'Nog een regel toevoegen' klikken onder het deelvenster 'Voorwaardelijke opmaakregels'.
Bevestig vervolgens het bereik (B2:B30) in het veld 'Toepassen op bereik'.
Stel vervolgens de optie 'Cellen opmaken als..' in op 'Aangepaste formule is' en voer de onderstaande formule in het formulevak in:
=AANTAL.ALS($A$2:$A$30, $B2)>0Hier gebruiken we kolom A-bereik ($A$2:$A$30) in het eerste argument en '$B2' in het tweede argument. Deze formule controleert de celwaarde in 'kolom B' tegen elke cel in kolom A. Als een overeenkomst (duplicaat) wordt gevonden, zal voorwaardelijke opmaak dat item in 'kolom B' plaatsen
Geef vervolgens de opmaak op in de opties voor 'Opmaakstijl' en klik op 'Gereed'. Hier kiezen we de oranje kleur voor kolom B.
Hiermee worden de items in kolom B gemarkeerd die duplicaten hebben in kolom A. Nu hebt u dubbele items tussen twee kolommen gevonden en gemarkeerd.
Het is je waarschijnlijk opgevallen, hoewel er een duplicaat is voor 'Arcelia' in kolom A, is deze niet gemarkeerd. Dit komt omdat de dubbele waarde zich slechts in één kolom (A) bevindt, niet tussen kolommen. Daarom is het niet gemarkeerd.
Markeer duplicaten tussen twee kolommen in dezelfde rij
U kunt ook de rijen met dezelfde waarden (duplicaten) tussen twee kolommen markeren met behulp van voorwaardelijke opmaak. De voorwaardelijke opmaakregel kan elke rij controleren en markeert de rijen met overeenkomende gegevens in beide kolommen. Hier is hoe je dit doet:
Selecteer eerst beide kolommen die je wilt vergelijken, ga dan naar het menu ‘Opmaak’ en selecteer ‘Voorwaardelijke opmaak’.
Bevestig in het deelvenster Regels voor voorwaardelijke opmaak het bereik in het vak 'Toepassen op bereik' en kies 'Aangepaste formule is' in de vervolgkeuzelijst 'Formulecellen als...'.
Voer vervolgens de onderstaande formule in het vak 'Waarde of formule' in:
=$A2=$B2Deze formule vergelijkt de twee kolommen rij voor rij en markeert rijen met identieke waarden (duplicaten). Zoals u kunt zien, is de hier ingevoerde formule alleen voor de eerste rij van het geselecteerde bereik, maar de formule wordt automatisch toegepast op alle rijen in het geselecteerde bereik door de voorwaardelijke opmaakfunctie.
Geef vervolgens de opmaak op bij de opties voor 'Opmaakstijl' en klik op 'Gereed'.
Zoals u kunt zien, worden alleen de rijen met overeenkomende gegevens (duplicaten) tussen twee kolommen gemarkeerd en worden alle andere duplicaten genegeerd.
Markeer dubbele cellen in meerdere kolommen
Wanneer u met grotere spreadsheets met veel kolommen werkt, wilt u misschien alle duplicaten markeren die in meerdere kolommen verschijnen in plaats van slechts één of twee kolommen. U kunt nog steeds voorwaardelijke opmaak gebruiken om het duplicaat in meerdere kolommen te markeren.
Selecteer eerst het bereik van alle kolommen en rijen waarin u naar duplicaten wilt zoeken in plaats van slechts één of twee kolommen. U kunt hele kolommen selecteren door de Ctrl-toets ingedrukt te houden en vervolgens op de letter boven aan elke kolom te klikken. Als alternatief kunt u ook op de eerste en laatste cellen in uw bereik klikken terwijl u de Shift-toets ingedrukt houdt om meerdere kolommen tegelijk te selecteren.
In het voorbeeld selecteren we A2:C30.
Klik vervolgens in het menu op de optie 'Formaat' en selecteer 'Voorwaardelijke opmaak'.
Stel in de Voorwaardelijke opmaakregels de Opmaakregels in op 'Aangepaste formule is' en voer vervolgens de volgende formule in het vak 'Waarde of formule' in:
=countif($A$2:$C$30,A2)>We voegen het '$'-teken toe voor de celverwijzingen om ze absolute kolommen te maken, dus het verandert niet dat we de formule toepassen. Je kunt de formule ook invoeren zonder de '$'-tekens, het werkt hoe dan ook.
Kies vervolgens de opmaak waarin u de dubbele cellen wilt markeren met behulp van de 'Opmaakstijl'-opties. Hier kiezen we de opvulkleur 'Geel'. Klik daarna op 'Gereed'.
Hiermee worden de duplicaten gemarkeerd in alle kolommen die u hebt geselecteerd, zoals hieronder wordt weergegeven.
Nadat u de voorwaardelijke opmaak hebt toegepast, kunt u de regel voor voorwaardelijke opmaak op elk gewenst moment bewerken of verwijderen.
Als u de huidige regel voor voorwaardelijke opmaak wilt wijzigen, selecteert u een cel met voorwaardelijke opmaak, gaat u naar 'Opmaak' in het menu en selecteert u 'Voorwaardelijke opmaak'.
Hierdoor wordt het deelvenster 'Voorwaardelijke opmaakregels' aan de rechterkant geopend met een lijst met opmaakregels die op de huidige selectie zijn toegepast. Wanneer u met uw muis over de regel beweegt, ziet u de verwijderknop, klik op de verwijderknop om de regel te verwijderen. Of, als u de regel die momenteel wordt weergegeven, wilt bewerken, klikt u op de regel zelf.
Als u nog een voorwaardelijke opmaak wilt toevoegen aan de huidige regel, klikt u op de knop 'Nog een regel toevoegen'.
Tel de duplicaten tussen twee kolommen
Soms wilt u het aantal keren tellen dat een waarde in de ene kolom wordt herhaald in een andere kolom. Het kan eenvoudig worden gedaan met dezelfde AANTAL.ALS-functie.
Om het aantal keren te vinden dat een waarde in kolom A in kolom B voorkomt, voert u de volgende formule in een cel in een andere kolom in:
=AANTAL.ALS($B$2:$B$30,$A2)Voer deze formule in cel C2 in. Deze formule telt het aantal keren dat de waarde in cel A2 voorkomt in de kolom (B2:B30) en retourneert de telling in cel C2.
Wanneer u de formule typt en op Enter drukt, verschijnt de functie Automatisch aanvullen, klik op het 'vinkje' om deze formule automatisch in de rest van de cellen in te vullen (C3:C30).
Als de functie voor automatisch aanvullen niet verschijnt, klikt u op het blauwe vierkant in de rechterbenedenhoek van cel C2 en sleept u het naar beneden om de formule in cel C2 naar cellen C3:C30 te kopiëren.
In kolom 'Vergelijking 1' (C) ziet u nu het aantal keren dat elke corresponderende waarde in kolom A in kolom B voorkomt. De waarde van A2 of 'Franklyn' wordt bijvoorbeeld niet gevonden in kolom B, dus de AANTAL.ALS-functie retourneert "0". En de waarde "Loreta" (A5) wordt twee keer gevonden in kolom B, daarom wordt "2" geretourneerd.
Nu moeten we dezelfde stappen herhalen om de dubbele tellingen van kolom B te vinden. Om dat te doen, voert u de volgende formule in cel D2 in kolom D in (vergelijking 2):
=AANTAL.ALS($A$2:$A$30,$B2)Vervang in deze formule het bereik van '$B$2:$B$30' naar '$A$2:$A$30' en '$B2' naar '$A2'. De functie telt het aantal keren dat de waarde in cel B2 voorkomt in kolom A (A2:A30) en retourneert de telling in cel D2.
Vul vervolgens de formule automatisch in voor de rest van de cellen (D3:D30) in kolom D. Nu toont de 'Vergelijking 2' u het aantal keren dat elke corresponderende waarde in kolom B in kolom A voorkomt. Bijvoorbeeld , de waarde van B2 of "Stark" wordt twee keer gevonden in kolom A, dus de AANTAL.ALS-functie retourneert "2".
Opmerking: Als u de duplicaten over alle kolommen of meerdere kolommen wilt tellen, hoeft u alleen het bereik in het eerste argument van de AANTAL.ALS-functie te wijzigen in meerdere kolommen in plaats van slechts één kolom. Wijzig bijvoorbeeld het bereik van A2:A30 in A2:B30, waarbij alle duplicaten in twee kolommen worden geteld in plaats van slechts één.
Dat is het.