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
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
