Excel-formule: controleer of de cel al veel dingen bevat

Stel dat er in Excel een lijst met waarden is in kolom E, dan wilt u controleren of de cellen in kolom B alle waarden in kolom E bevatten en TRUE of FALSE retourneren zoals onderstaand screenshot. Hier in deze tutorial biedt het een formule om deze taak op te lossen.

Generieke formule:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things)

argumenten

Things: the list of values that you want to use to check if argument text contains.

Text: the cell or text string you want to check if containing argument things.

Winstwaarde:

Deze formule retourneert een logische waarde, FALSE: de cel bevat niet alle dingen; WAAR: de cel bevat alles.

Hoe deze formule werkt

In kolom B is er bijvoorbeeld een lijst met tekstreeksen die u wilt controleren of deze alle waarden in het bereik E3: E5 bevat, gebruik dan onderstaande formule.

=SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))=COUNTA($E$3:$E$5)

Media Enter -toets, sleep de vulgreep over de cellen die u wilt controleren. FALSE geeft aan dat de cel niet alle waarden in het bereik E3: E5 bevat, terwijl TURE aangeeft dat de relatieve cel alle waarden bevat.

Uitleg

SEARCH-functie: de SEARCH-functie retourneert de locatie van het eerste teken van de tekstreeks in een andere. Als de SEARCH-functie de tekst vindt, retourneert het de positie, zo niet, dan retourneert het #VALUE! fout. Hier bijvoorbeeld de formule SEARCH($E$3:$E$5,B4) zal elke waarde van het bereik E3: E5 in cel B4 doorzoeken en de locatie van elke tekstreeks in cel B4 retourneren. Het retourneert een array-resultaat als volgt: {1;7;12}

ISNUMBER-functie: de ISNUMBER-functie controleert of een waarde een getal is, en retourneert TRUE o False. Hier ISNUMBER(SEARCH($E$3:$E$5,B4)) zal het matrixresultaat teruggeven als {true;true;true} aangezien de functie ZOEKEN 3 getallen vindt.

--ISNUMBER(SEARCH($E$3:$E$5,B4)) converteert de TRUE-waarde naar 1 en converteert de FALSE-waarde naar 0, dus deze formule verandert het matrixresultaat in {1;1;1}.

SOMPRODUCT-functie: wordt gebruikt om bereiken of somarrays samen te vermenigvuldigen en retourneert de som van producten. De SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) geeft 1 + 1 + 1 = 3 terug.

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

Opmerkingen:

De Formule =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) is niet echt aan het controleren. Zie screenshot:

Voorbeeldbestand

Klik om een ​​voorbeeldbestand te downloaden

---

Relatieve formules

• Tel cellen gelijk aan
  Met de AANTAL.ALS-functie kunt u cellen tellen die gelijk zijn aan of geen opgegeven waarde bevatten.
• Tel cellen die gelijk zijn aan x of y
  Soms wilt u misschien het aantal cellen tellen dat aan een van de twee criteria voldoet, in dit geval kunt u de AANTAL.ALS-functie gebruiken.
• Tel cellen gelijk aan x en y
  Hier introduceert dit artikel de formule om cellen te tellen die ondertussen aan twee criteria voldoen.
• Tel cellen die niet gelijk zijn aan
  In dit artikel wordt beschreven hoe u de AANTAL.ALS-functie kunt gebruiken om het aantal cellen te tellen dat niet gelijk is aan een specifieke waarde