Excel Formula: Preverite, ali celica vsebuje veliko stvari

Recimo, da je v Excelu seznam vrednosti v stolpcu E, želite preveriti, ali celice v stolpcu B vsebujejo vse vrednosti v stolpcu E, in vrnite TRUE ali FALSE, kot je prikazano spodaj. Tu v tej vadnici vsebuje formulo za rešitev te naloge.

Splošna formula:

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

Argumenti

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.

Vrnjena vrednost:

Ta formula vrne logično vrednost FALSE: celica ne vsebuje vseh stvari; TRUE: celica vsebuje vse stvari.

Kako deluje ta formula

Na primer, v stolpcu B je seznam besedilnih nizov, ki jih želite preveriti, ali vsebujejo vse vrednosti v območju E3: E5, uporabite spodnjo formulo.

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

Pritisnite Vnesite , povlecite ročico za polnjenje čez celice, ki jih želite preveriti. FALSE pomeni, da celica ne vsebuje vseh vrednosti v območju E3: E5, medtem ko TURE označuje, da relativna celica vsebuje vse vrednosti.

Izjava

Funkcija SEARCH: funkcija SEARCH bo vrnila lokacijo prvega znaka besedilnega niza znotraj drugega. Če funkcija SEARCH najde besedilo, vrne položaj, v nasprotnem primeru pa vrne #VALUE! napaka. Tu je na primer formula SEARCH($E$3:$E$5,B4) bo poiskal vsako vrednost obsega E3: E5 v celici B4 in vrnil lokacijo vsakega besedilnega niza v celici B4. Vrnil bo rezultat matrike, kot sledi: {1;7;12}

Funkcija ISNUMBER: funkcija ISNUMBER bo preverila, ali je vrednost številka, in vrnila TRUE o False. Tukaj ISNUMBER(SEARCH($E$3:$E$5,B4)) bo vrnil rezultat polja kot {true;true;true} ker funkcija SEARCH najde 3 številke.

--ISNUMBER(SEARCH($E$3:$E$5,B4)) bo pretvoril vrednost TRUE v 1 in vrednost FALSE v 0, zato ta formula spremeni rezultat polja v {1;1;1}.

Funkcija SUMPRODUCT: uporablja se za množenje obsegov ali nizov vsote in vrne vsoto izdelkov. The SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) vrne 1 + 1 + 1 = 3.

Funkcija COUNTA: vrne število nepraznih celic. COUNTA($E$3:$E$5) vrne 3, torej rezultat SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) je enako 3, bo celotna formula vrnila TRUE ali vrnila FALSE.

Opombe:

Formula =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) ni ravno preverjanje. Oglejte si posnetek zaslona:

Vzorčna datoteka

Kliknite za prenos vzorčne datoteke

---

Relativne formule

• Štetje celic enako
  S funkcijo COUNTIF lahko štejete celice, ki so ali ne vsebujejo določene vrednosti.
• Štejte celice, ki so enake x ali y
  Včasih boste morda želeli prešteti število celic, ki ustrezajo enemu od dveh kriterijev, v tem primeru lahko uporabite funkcijo COUNTIF.
• Štejte celice, enake x in y
  Tukaj ta članek uvaja formulo za štetje celic, ki medtem ustrezata dvema kriterijema.
• Štejte celice, ki niso enake
  Ta članek govori o tem, kako uporabiti funkcijo COUNTIF za štetje števila celic, ki ni enako določeni vrednosti