Üldine valem
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Kokkuvõte
Kui soovite lahtrit testida, et näha, kas see sisaldab kõiki loendis olevaid üksusi, saate seda teha valemiga, mis kasutab funktsiooni OTSI, funktsioonide ISNUMBER, SUMPRODUCT ja COUNTA abiga.
Sisu
Oletame, et teil on nimekiri tekstistringidest vahemikus B5: B8 ja soovite teada saada, kas need lahtrid sisaldavad kõiki sõnu teises vahemikus E5: E7.
Võite luua valemi, mis kasutab iga üksuse kontrollimiseks pesastatud IF-lauseid, kuid see ei muutu hästi, kui teil on palju asju, mida otsida. Iga kord, kui lisate sõna otsimiseks, peate lisama teise pesastatud IF-i ja kohandama sulgudes.
Lahendus
Lahendus on luua valem, mis loeb kõiki vasteid korraga. Kui see on olemas, võrdleme seda arvu lihtsalt otsitavate üksuste arvuga. Kui need sobivad, teame, et lahter sisaldab kõiki üksusi.
Näidatud näites on meie kasutatav valem järgmine:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Selgitus
Peamine on see koodilõik:
ISNUMBER(SEARCH(things,B5)
See põhineb teisel valemil (mida on siin üksikasjalikult selgitatud), mis lihtsalt kontrollib lahtrit ühe alamstringi suhtes. Kui lahter sisaldab alamstringi, tagastab valem TÕENE. Kui ei, tagastab valem vale.
Kui aga anname samale valemile loendi asjadest (antud juhul kasutame nimega vahemikku nimega "asjad", E5: E7), annab see meile tagasi tõeliste / valede väärtuste loendi, ühe iga üksuse kohta . Tulemuseks on massiiv, mis näeb välja selline:
(TRUE; TRUE; TRUE)
Kus iga TRUE tähistab leitud üksust ja iga FALSE tähistab üksust, mida ei leitud.
Saame sundida TRUE / FALSE väärtused 1-le ja 0-le topeltnegatiiviga (-, mida nimetatakse ka topeltunariks):
--ISNUMBER(SEARCH(things,B5))
mis annab sellise massiivi:
(1; 1; 1)
Järgmisena töötleme seda massiivi SUMPRODUCTiga, mis annab meile summa. Kui see summa on võrdne nimega vahemikus "asjad" olevate üksuste arvuga, siis teame, et oleme kõik asjad leidnud ja võime tagastada TÕENE. See, kuidas me seda teeme, on kahe numbri otsene võrdlemine. COUNTA abil saame "asjades" arv tühje lahtreid:
=COUNTA(things)
Kõvakodeeritud loendiga
Asjade loendi jaoks ei ole nõutav vahemiku kasutamine. Kui otsite vaid vähest arvu asju, võite kasutada massiivivormingus loendit, mida nimetatakse massiivikonstandiks. Näiteks kui otsite ainult punaseid, siniseid ja rohelisi värve, saate kasutada (punane, sinine, roheline) järgmiselt:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
