Excel-formel: Kontrollera om cellen innehåller alla många saker
Om vi antar att det i Excel finns en lista över värden i kolumn E, vill du kontrollera om cellerna i kolumn B innehåller alla värden i kolumn E och returnera SANT eller FALSKT enligt nedanstående skärmdump. Här i den här handledningen ger den en formel för att lösa denna uppgift.
Generisk formel:
| =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) |
Argument
| 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. |
Returvärde:
Denna formel returnerar logiskt värde, FALSK: cellen innehåller inte alla saker; TRUE: cellen innehåller allt.
Hur den här formeln fungerar
Till exempel, i kolumn B finns det en lista med textsträngar som du vill kontrollera om de innehåller alla värden i intervallet E3:E5, använd nedanstående formel.
| =SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))=COUNTA($E$3:$E$5) |
Presse ange -tangenten, dra fyllningshandtaget över de celler som du vill kontrollera. FALSE anger att cellen inte innehåller alla värden i intervallet E3:E5, medan TURE anger att den relativa cellen innehåller alla värden.
Förklaring
SEARCH-funktionen: SEARCH-funktionen returnerar platsen för det första tecknet i textsträngen inuti en annan. Om SEARCH-funktionen hittar texten, returnerar den positionen, om inte, returnerar den #VALUE! fel. Till exempel här formeln SEARCH($E$3:$E$5,B4) kommer att söka i varje värde i intervallet E3: E5 i cell B4 och returnerar platsen för varje textsträng i cellen B4. Det returnerar ett matrisresultat så här: {1;7;12}
ISNUMBER-funktion: ISNUMBER-funktionen kontrollerar om ett värde är ett tal och returnerar TRUE o False. Här ISNUMBER(SEARCH($E$3:$E$5,B4)) returnerar arrayresultat som {true;true;true} eftersom SEARCH-funktionen hittar 3 nummer.
--ISNUMBER(SEARCH($E$3:$E$5,B4)) konverterar värdet TRUE till 1 och konverterar FALSE-värdet till 0, så den här formeln ändrar arrayresultatet till {1;1;1}.
SUMMAPRODUKT-funktion: används för att multiplicera intervall eller summera arrayer tillsammans och returnerar summan av produkter. De SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) returnerar 1 + 1 + 1 = 3.
COUNTA-funktion: returnerar antalet icke-tomma celler. COUNTA($E$3:$E$5) returnerar 3, så resultatet av SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) är lika med 3 kommer hela formeln att returnera TRUE, eller returnera FALSE.
Anmärkningar:
Formeln =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) kollar inte precis. Se skärmdump:
Provfil
Klicka för att ladda ner exempelfilen
Relativa formler
- Räkna celler lika med
Med COUNTIF-funktionen kan du räkna celler som är lika med eller inte innehåller ett angivet värde. - Räkna celler som är lika med x eller y
I vissa tider kanske du vill räkna antalet celler som uppfyller ett av två kriterier. I det här fallet kan du använda COUNTIF-funktionen. - Räkna celler lika med x och y
Här introducerar den här artikeln formeln för att räkna celler som under tiden matchar två kriterier. - Räkna celler som inte är lika med
Den här artikeln talar om hur man använder COUNTIF-funktionen för att räkna antalet celler som inte är lika med ett visst värde
De bästa Office-produktivitetsverktygen
Kutools för Excel - Hjälper dig att sticka ut från mängden
Kutools för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...
Fliken Office - Aktivera läsning och redigering av flikar i Microsoft Office (inkluderar Excel)
- En sekund att växla mellan dussintals öppna dokument!
- Minska hundratals musklick åt dig varje dag, säg adjö till mushanden.
- Ökar din produktivitet med 50 % när du visar och redigerar flera dokument.
- Ger effektiva flikar till Office (inklusive Excel), precis som Chrome, Edge och Firefox.
