Naar aanleiding van een video van MVP Deb Ashby vroeg ik me af of je met het invoeren van een paar letters Excel laten zoeken in meerdere kolommen van een tabel en de achtergrond van de hele rij laten wijzigen? Na wat testen bleek dat het mogelijk is met een wat uitgebreidere formule in de voorwaardelijke opmaak.
In bovenstaand voorbeeld kan in cel H3 een tekst ingevoerd worden en als die tekst aanwezig is in de kolom Voornaam, Achternaam of Plaats, dan zal de hele rij een gele achtergrond krijgen.
Voor de geavanceerde Excel-gebruikers die geen uitleg over de formule nodig hebben:
- Selecteer het gebied met de data
- Vermeld de volgende formule bij de voorwaardelijke opmaak:
=EN(OF(ISFOUT(VIND.SPEC($H$3;$B6))=ONWAAR;ISFOUT(VIND.SPEC($H$3;$C6))=ONWAAR;ISFOUT(VIND.SPEC($H$3;$H6))=ONWAAR);$H$3<>"")
Inleiding:
Als je in voorwaardelijke opmaak een formule gebruikt, dan wordt de opmaak toegepast als het antwoord van de formule WAAR is. Als het niet waar is, dan wordt de opmaak niet toegepast.
Bij een voorwaardelijke opmaak-formule is het handig om eerst het gebied te selecteren waarvoor de voorwaardelijke opmaak moet gelden. Dan wordt automatisch ingevuld bij de opmaak, anders moet dit handmatig worden ingevuld wat lastiger is.
Als een voorwaarde moet gelden voor een heel gebied, dan moet er goed nagedacht worden over welke celadressen absoluut (b.v. $A$1), gedeeltelijk absoluut (b.v. $A1 of A$1) of relatief (b.v. A1) moeten zijn. Wil je meer informatie hierover, kijk dan eens op de website van Jan Bolhuis op ExcelXL.
Als een voorwaarde moet gelden voor een heel gebied, dan moet er goed nagedacht worden over welke celadressen absoluut (b.v. $A$1), gedeeltelijk absoluut (b.v. $A1 of A$1) of relatief (b.v. A1) moeten zijn. Wil je meer informatie hierover, kijk dan eens op de website van Jan Bolhuis op ExcelXL.
Hoe gebruik je de formule:
In het voorbeeld is te zien dat het zoekwoord ingevoerd kan worden in cel H3. Het gebied met de gegevens (de adresgegevens zijn fictief en gemaakt met de 100.000 voorbeeldnamendatabase die je kunt vinden op mijn website Excelbestanden) loopt van B6 t/m H17. Als jouw gebied of zoekveld anders zijn, dan zul je de formule er op aan moeten passen. Let op dat zoekcel altijd absoluut is ($H$3) en dat bij de verwijzing naar de kolommen alleen de kolomletters absoluut zijn ($B6, $C6 en $H6).
Kopieer de formule zoals het hierboven staat. Pas het eventueel aan. Selecteer de gegevens in de tabel en klik in het Startlint op Voorwaardelijke Opmaak > Nieuwe Regel > Een formule gebruiken om te bepalen welke cellen worden opgemaakt. In de invoerveld plak je de formule. Via de Opmaak-button bepaal je de opmaak en daarna zou het moeten werken.
Uitleg over de formule:
Zoals vermeld moet de uitkomst van de formule WAAR of ONWAAR zijn. Stel we zouden als voorwaardelijke opmaakformule =$B6=$H$3 gebruiken, dan zal Excel de opmaak toevoegen aan die cellen waarbij in kolom C waarin precies hetzelfde staat als in cel H3. Dat is handig als je naar een specifieke tekst zoek. Maar als je wilt zoeken in meerdere kolommen en waar de tekst minimaal hetzelfde moet bevatten als de tekst in H3, dan moet de formule uitgebreid worden.
Excel heeft voor het zoeken van de tekst de functie =VIND.SPEC(zoektekst ; in_tekst ; [begingetal]). Daarmee wordt aangegeven op welke positie van de tekst de zoektekst wordt gevonden.
=VIND.SPEC("as" ; "Jassen") zal als antwoord 2 geven want op de 2e positie begint de zoektekst. Als de tekst niet de zoektekst bevat, dan geeft de formule de foutmelding #WAARDE!.
Als we als voorwaardelijke opmaakformule =VIND.SPEC($H$3,$B6) zouden invoeren, dan komt er òf een cijfer uit als de tekst wordt gevonden (WAAR is) of een foutmelding als de tekst niet wordt gevonden (ONWAAR is).
Deze formule werkt goed als je alleen maar wilt zoeken in kolom B. Maar wat als je ook wilt zoeken of de tekst in kolom B òf kolom C òf kolom H moet staan? Dan kan het zijn dat de zoektekst in geen, één, twee of drie kolommen staan. Je zou denken dat dit met een OF-formule opgelost zou kunnen worden b.v. met =OF(VIND.SPEC($H$3;$B6);VIND.SPEC($H$3;$c6);VIND.SPEC($H$3;$H6)). Maar als er één of meerdere cellen voldoen, dan leveren deze cellen geen WAAR op maar de positie waar de zoektekst in de tekst begint. En voorwaardelijk opmaak ziet een getal als ONWAAR.
Als we als voorwaardelijke opmaakformule =VIND.SPEC($H$3,$B6) zouden invoeren, dan komt er òf een cijfer uit als de tekst wordt gevonden (WAAR is) of een foutmelding als de tekst niet wordt gevonden (ONWAAR is).
Deze formule werkt goed als je alleen maar wilt zoeken in kolom B. Maar wat als je ook wilt zoeken of de tekst in kolom B òf kolom C òf kolom H moet staan? Dan kan het zijn dat de zoektekst in geen, één, twee of drie kolommen staan. Je zou denken dat dit met een OF-formule opgelost zou kunnen worden b.v. met =OF(VIND.SPEC($H$3;$B6);VIND.SPEC($H$3;$c6);VIND.SPEC($H$3;$H6)). Maar als er één of meerdere cellen voldoen, dan leveren deze cellen geen WAAR op maar de positie waar de zoektekst in de tekst begint. En voorwaardelijk opmaak ziet een getal als ONWAAR.
Het zou mooi zijn als er een functie bestaat die controleert of een formule een goed antwoord oplevert, maar die is er niet. Er is wel een functie die kan controleren of een formule een fout oplevert, dus het omgekeerde. En dat is de functie =ISFOUT(formule).
Dus =VIND.SPEC("as";"Jassen") geeft als antwoord een 2. =ISFOUT(VIND.SPEC("as";"Jassen")) geeft als antwoord ONWAAR want het is niet fout. En =ISFOUT(VIND.SPEC("el";"Jassen")) geeft als antwoord WAAR want de VIND.SPEC-formule levert een fout op.
Dus =VIND.SPEC("as";"Jassen") geeft als antwoord een 2. =ISFOUT(VIND.SPEC("as";"Jassen")) geeft als antwoord ONWAAR want het is niet fout. En =ISFOUT(VIND.SPEC("el";"Jassen")) geeft als antwoord WAAR want de VIND.SPEC-formule levert een fout op.
Voorwaardelijk opmaak wordt uitgevoerd als de formule WAAR is. We moeten dus het omgekeerde van =ISFOUT() hebben als antwoord. Dat kan door de formule uit te breiden met =ISFOUT(formule)=ONWAAR. Als =VIND.SPEC() een getal oplevert, dan is =ISFOUT(VIND.SPEC()) onwaar en is =ISFOUT(VIND.SPEC())=ONWAAR dus waar.
De formule: =OF(ISFOUT(VIND.SPEC($H$3;$B6))=ONWAAR;ISFOUT(VIND.SPEC($H$3;$C6))=ONWAAR;ISFOUT(VIND.SPEC($H$3;$H6))=ONWAAR) levert dus een goed resultaat op als de zoektekst in één van de drie cellen op de betreffende rij wordt gevonden. Maar door de dubbele ontkenning wordt ook de opmaak uitgevoerd als in het zoekveld geen tekst wordt ingevoerd. Daarom moet de formule nog uitgebreid geworden. De OF-formule moet WAAR zijn maar ook dat er in cel $H$3 een tekst staat ofwel $H$3 mag niet leeg zijn. Daarom wordt de gehele formule:
=EN(OF(ISFOUT(VIND.SPEC($H$3;$B6))=ONWAAR;ISFOUT(VIND.SPEC($H$3;$C6))=ONWAAR;ISFOUT(VIND.SPEC($H$3;$H6))=ONWAAR);$H$3<>"")
