Zoek en markeer gegevens in Excel (met voorwaardelijke opmaak)

Video bekijken - Gegevens zoeken en markeren met voorwaardelijke opmaak

Als u met grote datasets werkt, kan het nodig zijn om een ​​zoekfunctionaliteit te creëren waarmee u snel cellen/rijen voor de gezochte term kunt markeren.

Hoewel er geen directe manier is om dit in Excel te doen, kunt u zoekfunctionaliteit maken met behulp van voorwaardelijke opmaak.

Stel dat u een dataset heeft zoals hieronder weergegeven (in de afbeelding). Het heeft kolommen voor Productnaam, Verkoopvertegenwoordiger en Land.

Nu kunt u voorwaardelijke opmaak gebruiken om naar een trefwoord te zoeken (door het in cel C2 in te voeren) en alle cellen met dat trefwoord te markeren.

Iets zoals hieronder weergegeven (waar ik de itemnaam in cel B2 invoer en op Enter druk, wordt de hele rij gemarkeerd):

In deze zelfstudie laat ik u zien hoe u deze zoek- en markeerfunctionaliteit in Excel kunt maken.

Later in de zelfstudie gaan we een beetje geavanceerd en kijken we hoe we het dynamisch kunnen maken (zodat het wordt gemarkeerd terwijl u in het zoekvak typt).

Klik hier om het voorbeeldbestand te downloaden en volg mee.

Zoek en markeer overeenkomende cellen

In deze sectie. Ik zal u laten zien hoe u alleen de overeenkomende cellen in een gegevensset kunt zoeken en markeren.

Iets zoals hieronder weergegeven:

Dit zijn de stappen om alle cellen met de overeenkomende tekst te zoeken en te markeren:

1. Selecteer de dataset waarop u Voorwaardelijke opmaak wilt toepassen (A4:F19 in dit voorbeeld).
2. Klik op het tabblad Start.
3. Klik in de groep Stijlen op Voorwaardelijke opmaak.
4. Klik in de vervolgkeuzelijst op Nieuwe regel.
   
5. Klik in het dialoogvenster 'Nieuwe opmaakregel' op de optie 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
6. Voer de volgende formule in: =A4=$B$1
7. Klik op de knop 'Formaat…'.
8. Geef de opmaak op (om cellen te markeren die overeenkomen met het gezochte trefwoord).
9. Klik OK.

Typ nu iets in cel B1 en druk op enter. Het markeert de overeenkomende cellen in de dataset die het trefwoord in B1 bevatten.

Hoe werkt dit?

Voorwaardelijke opmaak wordt toegepast wanneer de formule die erin is gespecificeerd TRUE retourneert.

In het bovenstaande voorbeeld controleren we elke cel met behulp van de formule: =A4=$B$1

Voorwaardelijke opmaak controleert elke cel en controleert of de inhoud in de cel hetzelfde is als die in cel B1. Als het hetzelfde is, retourneert de formule TRUE en wordt de cel gemarkeerd. Als het niet hetzelfde is, retourneert de formule FALSE en gebeurt er niets.

Klik hier om het voorbeeldbestand te downloaden en volg mee.

Zoek en markeer rijen met overeenkomende gegevens

Als u de hele rij wilt markeren in plaats van alleen de overeenkomende cellen, kunt u dat doen door de formule een beetje aan te passen.

Hieronder ziet u een voorbeeld waarbij de hele rij wordt gemarkeerd als het producttype overeenkomt met dat in cel B1.

Dit zijn de stappen om de hele rij te zoeken en te markeren:

1. Selecteer de dataset waarop u Voorwaardelijke opmaak wilt toepassen (A4:F19 in dit voorbeeld).
2. Klik op het tabblad Start.
3. Klik in de groep Stijlen op Voorwaardelijke opmaak.
4. Klik in de vervolgkeuzelijst op Nieuwe regel.
   
5. Klik in het dialoogvenster 'Nieuwe opmaakregel' op de optie 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
6. Voer de volgende formule in: =$B4=$B$1
7. Klik op de knop 'Formaat…'.
8. Geef de opmaak op (om cellen te markeren die overeenkomen met het gezochte trefwoord).
9. Klik OK.

De bovenstaande stappen zoeken naar het opgegeven item in de gegevensset en als het het overeenkomende item vindt, wordt de hele rij gemarkeerd.

Merk op dat dit alleen voor de itemkolom zal controleren. Als u hier een naam van een vertegenwoordiger invoert, werkt deze niet. Als u wilt dat het werkt voor de naam van de verkoopvertegenwoordiger, moet u de formule wijzigen in: =$C4=$B$1

Opmerking: de reden dat het de hele rij markeert en niet alleen de overeenkomende cel, is dat we een $ -teken hebben gebruikt voor de kolomverwijzing ($ B4). Wanneer voorwaardelijke opmaak nu cellen in een rij analyseert, wordt gecontroleerd of de waarde in kolom B van die rij gelijk is aan de waarde in cel B1. Dus zelfs wanneer het A4 of B4 of C4 enzovoort analyseert, controleert het alleen de B4-waarde (omdat we kolom B hebben vergrendeld met behulp van het dollarteken).

U kunt hier meer lezen over absolute, relatieve en gemengde verwijzingen.

Zoek en markeer rijen (gebaseerd op gedeeltelijke overeenkomst)

In sommige gevallen wilt u misschien rijen markeren op basis van een gedeeltelijke overeenkomst.

Als u bijvoorbeeld items heeft zoals White Board, Green Board en Gray Board en u wilt deze allemaal markeren op basis van het woord Board, dan kunt u dit doen met de functie ZOEKEN.

Iets zoals hieronder weergegeven:

Hier zijn de stappen om dit te doen:

1. Selecteer de dataset waarop u Voorwaardelijke opmaak wilt toepassen (A4:F19 in dit voorbeeld).
2. Klik op het tabblad Start.
3. Klik in de groep Stijlen op Voorwaardelijke opmaak.
4. Klik in de vervolgkeuzelijst op Nieuwe regel.
   
5. Klik in het dialoogvenster 'Nieuwe opmaakregel' op de optie 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
6. Voer de volgende formule in: =EN($B$1””,ISNUMMER(ZOEKEN($B$1,$B4)))
7. Klik op de knop 'Formaat…'.
8. Geef de opmaak op (om cellen te markeren die overeenkomen met het gezochte trefwoord).
9. Klik OK.

Hoe werkt dit?

• De functie ZOEKEN zoekt naar de zoekreeks/het trefwoord in alle cellen op een rij. Het retourneert een fout als het zoekwoord niet wordt gevonden, en retourneert een getal als het een overeenkomst vindt.
• De functie ISNUMBER converteert de fout naar ONWAAR en de numerieke waarden naar TRUE.
• EN-functie controleert op een aanvullende voorwaarde - dat cel C2 niet leeg mag zijn.

Dus wanneer u nu een trefwoord in cel B1 typt en op Enter drukt, worden alle rijen gemarkeerd met de cellen die dat trefwoord bevatten.

Bonustip: Als u de zoekopdracht hoofdlettergevoelig wilt maken, gebruikt u de FIND-functie in plaats van ZOEKEN.

Klik hier om het voorbeeldbestand te downloaden en volg mee.

Dynamische zoek- en markeringsfunctionaliteit (highlights terwijl u typt)

Met dezelfde trucs voor voorwaardelijke opmaak die hierboven zijn beschreven, kun je ook een stap verder gaan en het dynamisch maken.

U kunt bijvoorbeeld een zoekbalk maken waarin de overeenkomende gegevens worden gemarkeerd terwijl u in de zoekbalk typt.

Iets zoals hieronder weergegeven:

Dit kan worden gedaan met behulp van ActiveX-besturingselementen en kan een goede functionaliteit zijn om te gebruiken bij het maken van rapporten of dashboards.

Hieronder zie je een video waarin ik laat zien hoe je dit kunt maken:

Vond je deze tutorial nuttig? Laat me je mening weten in de comments.

Misschien vind je de volgende Excel-zelfstudies misschien ook leuk:

• Dynamisch Excel-filter - extraheert gegevens terwijl u typt.
• Maak een vervolgkeuzelijst met zoeksuggestie.
• Een hittekaart maken in Excel.
• Markeer rijen op basis van een celwaarde in Excel.
• Markeer de actieve rij en kolom in een gegevensbereik in Excel.
• Hoe lege cellen in Excel te markeren