Excel-formule: Controleer of een cel een van meerdere waarden bevat, maar sluit andere waarden uit

Stel dat er twee lijsten met waarden zijn, en je wilt controleren of cel B3 een van de waarden in het bereik E3:E5 bevat, maar tegelijkertijd geen van de waarden in het bereik F3:F4 bevat, zoals te zien is in de onderstaande schermafbeelding. Deze handleiding biedt een formule om deze taak snel af te handelen in Excel en legt de argumenten van de formule uit.

Algemene formule:

Argumenten

Retourwaarde:

De formule retourneert 1 of 0. Wanneer de cel een van de waarden bevat die moeten worden opgenomen en geen van de waarden die moeten worden uitgesloten, retourneert deze 1, anders 0. In deze formule worden 1 en 0 behandeld als logische waarden WAAR en ONWAAR.

Hoe deze formule werkt

Stel dat je wilt controleren of cel B3 een van de waarden in het bereik E3:E5 bevat, maar tegelijkertijd de waarden in het bereik F3:F4 uitsluit, gebruik dan de volgende formule

Druk op Enter toets om het controleerresultaat te krijgen.

Uitleg

Deel 1: (SOMPRODUCT(--ISGETAL(ZOEKEN($E$3:$E$5;B3)))>0) controleert of cel waarden in E3:E5 bevat

ZOEKEN functie: de ZOEKEN functie retourneert de positie van het eerste teken van de tekststring binnen een andere. Als de ZOEKEN functie de overeenkomende tekst vindt, retourneert deze de relatieve positie, zo niet, dan retourneert deze een #WAARDE! fout. Bijvoorbeeld, hier zal de formule ZOEKEN($E$3:$E$5;B3) elke waarde van het bereik E3:E5 zoeken in cel B3 en de locatie van elke tekststring in cel B3 retourneren. Het retourneert een array-resultaat zoals dit: {1;7;12}.

ISGETAL functie: de ISGETAL functie retourneert WAAR wanneer een cel een getal is. Dus ISGETAL(ZOEKEN($E$3:$E$5;B3)) zal een array-resultaat retourneren als {true,true,true} omdat de ZOEKEN functie 3 getallen vindt.

--ISGETAL(ZOEKEN($E$3:$E$5;B3)) converteert de WAAR-waarde naar 1 en de ONWAAR-waarde naar 0, dus verandert deze formule het array-resultaat in {1;1;1}.

SOMPRODUCT functie: wordt gebruikt om bereiken te vermenigvuldigen of arrays samen op te tellen en retourneert de som van producten. De SOMPRODUCT(--ISGETAL(ZOEKEN($E$3:$E$5;B3))) retourneert 1+1+1=3.

Tot slot, vergelijk de linkerformule SOMPRODUCT(--ISGETAL(ZOEKEN($E$3:$E$5;B3))) met 0, zolang het resultaat van de linkerformule groter is dan 0, zal het resultaat WAAR zijn, anders retourneert het ONWAAR. Hier retourneert het WAAR.

Deel 2: (SOMPRODUCT(--ISGETAL(ZOEKEN($F$3:$F$4;B3)))=0) controleert of cel geen waarden in F3:F4 bevat

De formule ZOEKEN($F$3:$F$4;B3) zal elke waarde in het bereik E3:E5 zoeken in cel B3 en de locatie van elke tekststring in cel B3 retourneren. Het retourneert een array-resultaat zoals dit: {#WAARDE!;#WAARDE!}.

ISGETAL(ZOEKEN($F$3:$F$4;B3)) zal een array-resultaat retourneren als {false;false} omdat de ZOEKEN functie 0 getallen vindt.

--ISGETAL(ZOEKEN($F$3:$F$4;B3)) converteert de WAAR-waarde naar 1 en de ONWAAR-waarde naar 0, dus verandert deze formule het array-resultaat in {0;0 }.

SOMPRODUCT functie: wordt gebruikt om bereiken te vermenigvuldigen of arrays samen op te tellen en retourneert de som van producten. De SOMPRODUCT(--ISGETAL(ZOEKEN($F$3:$F$4;B3))) retourneert 0+0=0.

Tot slot, vergelijk de linkerformule SOMPRODUCT(--ISGETAL(ZOEKEN($F$3:$F$4;B3))) met 0, zolang het resultaat van de linkerformule gelijk is aan 0, zal het resultaat WAAR retourneren, anders retourneert het ONWAAR. Hier retourneert het WAAR.

Deel 3: Vermenigvuldig twee formules

=(SOMPRODUCT(--ISGETAL(ZOEKEN($E$3:$E$5;B3)))>0)*(SOMPRODUCT(--ISGETAL(ZOEKEN($F$3:$F$4;B3)))=0)

=WAAR*WAAR

=1

In deze formule worden 1 en 0 behandeld als logische waarden WAAR en ONWAAR.

Voorbeeldbestand

Klik om voorbeeldbestand te downloaden

Gerelateerde Formules

• Controleer of een cel specifieke tekst bevat
  Om te controleren of een cel sommige teksten in bereik A bevat, maar niet de teksten in bereik B, kun je een array-formule gebruiken die de AANTAL, ZOEKEN en EN functies combineert in Excel.
• Controleer of cel een van vele dingen bevat
  Deze handleiding biedt een formule om te controleren of een cel een van meerdere waarden in Excel bevat, en legt de argumenten in de formule uit en hoe de formule werkt.
• Controleer of cel een van de dingen bevat
  Stel dat er in Excel een lijst met waarden in kolom E is, en je wilt controleren of de cellen in kolom B alle waarden in kolom E bevatten en WAAR of ONWAAR retourneren.
• Controleer of cel een getal bevat
  Soms wil je misschien controleren of een cel numerieke tekens bevat. Deze handleiding biedt een formule die WAAR retourneert als de cel een getal bevat, en ONWAAR als de cel geen getal bevat.