Excel-formel: Kontroller, om cellen indeholder alle de mange ting

Antag i Excel, der er en liste over værdier i kolonne E, du vil kontrollere, om cellerne i kolonne B indeholder alle værdierne i kolonne E, og returnere SAND eller FALSK som vist nedenstående skærmbillede. Her i denne vejledning giver det en formel til at løse denne opgave.

Generisk formel:

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

argumenter

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ærdi:

Denne formel returnerer logisk værdi, FALSK: cellen indeholder ikke alle ting; SAND ： cellen indeholder alle ting.

Sådan fungerer denne formel

For eksempel er der i kolonne B en liste over tekststrenge, som du vil kontrollere, om de indeholder alle værdier i området E3: E5, brug nedenstående formel.

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

Presse Indtast nøgle, træk udfyldningshåndtaget over de celler, som du vil kontrollere. FALSE angiver, at cellen ikke indeholder alle værdier i området E3: E5, mens TURE angiver, at den relative celle indeholder alle værdierne.

Forklaring

SEARCH-funktion: SEARCH-funktionen returnerer placeringen af ​​det første tegn i tekststrengen i en anden. Hvis SEARCH-funktionen finder teksten, returnerer den positionen, hvis ikke, returnerer den #VALUE! fejl. For eksempel her formlen SEARCH($E$3:$E$5,B4) vil søge i hver værdi i området E3: E5 i celle B4 og returnerer placeringen af ​​hver tekststreng i cellen B4. Det returnerer et array-resultat som dette: {1;7;12}

ISNUMBER-funktion: ISNUMBER-funktionen kontrollerer, om en værdi er et tal, og returnerer SAND o Falsk. Her ISNUMBER(SEARCH($E$3:$E$5,B4)) returnerer array-resultat som {true;true;true} da SEARCH-funktionen finder 3 tal.

--ISNUMBER(SEARCH($E$3:$E$5,B4)) konverterer den SANDE værdi til 1 og konverterer FALSK værdi til 0, så denne formel ændrer array-resultatet til {1;1;1}.

SUMPRODUCT-funktion: bruges til at multiplicere områder eller summen arrays sammen og returnerer summen af ​​produkter. Det SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) returnerer 1 + 1 + 1 = 3.

COUNTA-funktion: returnerer antallet af celler, der ikke er tomme. COUNTA($E$3:$E$5) returnerer 3, så resultatet af SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) er lig med 3, vil hele formlen returnere SAND eller returnere FALSK.

Bemærkninger:

Formlen =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) kontrollerer ikke ligefrem. Se skærmbillede:

Prøvefil

Klik for at downloade prøvefil

---

Relative formler

• Tæl celler svarende til
  Med COUNTIF-funktionen kan du tælle celler, der svarer til eller ikke indeholder en specificeret værdi.
• Tæl celler, der svarer til x eller y
  I nogle gange vil du muligvis tælle antallet af celler, der opfylder et af to kriterier. I dette tilfælde kan du bruge COUNTIF-funktionen.
• Tæl celler svarende til x og y
  Her introducerer denne artikel formlen til at tælle celler, der i mellemtiden matcher to kriterier.
• Tæl celler, der ikke er lig med
  Denne artikel taler om, hvordan man bruger COUNTIF-funktionen til at tælle antallet af celler, der ikke svarer til en bestemt værdi