Excel-formule: controleren of cel alle waarden bevat

Stel dat u in Excel een lijst met waarden hebt in kolom E en u wilt controleren of de cellen in kolom B alle waarden uit kolom E bevatten, met als resultaat WAAR of ONWAAR, zoals in de onderstaande afbeelding. In deze handleiding vindt u een formule om deze taak eenvoudig uit te voeren.

Algemene formule:

Argumenten

Retourneerwaarde:

Deze formule retourneert een logische waarde: WAAR als de cel alle waarden bevat, en anders ONWAAR.

Hoe deze formule werkt

Bijvoorbeeld: in kolom B staat een lijst met tekstreeksen waarvan u wilt controleren of ze alle waarden uit het bereik E3:E5 bevatten. Gebruik hiervoor de volgende formule.

Druk op Enter en sleep de vulgreep over de cellen die u wilt controleren. ONWAAR geeft aan dat de cel niet alle waarden uit het bereik E3:E5 bevat, terwijl WAAR betekent dat de cel wél alle waarden bevat.

Uitleg

ZOEKEN-functie: de ZOEKEN-functie geeft de positie terug van het eerste teken van een tekstreeks binnen een andere tekstreeks. Als de functie de opgegeven tekst vindt, retourneert ze de positie; als dat niet lukt, verschijnt de fout #WAARDE!. Bijvoorbeeld: de formule SEARCH($E$3:$E$5,B4) zoekt elke waarde uit het bereik E3:E5 in cel B4 en retourneert de positie van elke gevonden tekstreeks in die cel. Het resultaat is een matrix zoals deze: {1;7;12}

ISGETAL-functie: de ISGETAL-functie controleert of een waarde een getal is en retourneert WAAR of ONWAAR. In dit geval retourneert ISNUMBER(SEARCH($E$3:$E$5,B4)) een matrixresultaat als {true;true;true}, omdat de ZOEKEN-functie drie getallen vindt.

--ISNUMBER(SEARCH($E$3:$E$5,B4)) zet de WAAR-waarde om naar 1 en de ONWAAR-waarde naar 0, waardoor deze formule het matrixresultaat verandert in {1;1;1}.

SOMPRODUCT-functie: vermenigvuldigt bereiken of matrices en geeft de som van de producten terug. De SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) levert 1+1+1=3 op.

AANTALARG-functie: retourneert het aantal niet-lege cellen. COUNTA($E$3:$E$5) geeft 3 terug, dus het resultaat van SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) is gelijk aan 3; de volledige formule levert WAAR of ONWAAR op.

Opmerkingen:

De formule =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) controleert niet exact. Zie de afbeelding:

Voorbeeldbestand

Klik om het voorbeeldbestand te downloaden

Gerelateerde formules

• Tel cellen die gelijk zijn aan
  Met de AANTAL.ALS-functie telt u eenvoudig het aantal cellen dat exact overeenkomt met een opgegeven waarde — of juist afwijkt van die waarde.
• Tel cellen die gelijk zijn aan x of y
  Soms wilt u het aantal cellen tellen dat voldoet aan één van twee criteria. Gebruik dan de AANTAL.ALS-functie.
• Tel cellen die gelijk zijn aan x en y
  In dit artikel leggen we uit hoe je cellen telt die tegelijkertijd aan twee criteria voldoen.
• Tel cellen die niet gelijk zijn aan
  Dit artikel laat zien hoe u de AANTAL.ALS-functie gebruikt om het aantal cellen te tellen dat niet gelijk is aan een specifieke waarde.