Yleinen kaava
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Yhteenveto
Jos haluat testata solun nähdäksesi, sisältääkö se kaikki luettelon kohteet, voit tehdä sen kaavalla, joka käyttää HAKU-toimintoa ISNUMBER-, SUMPRODUCT- ja COUNTA-toimintojen avulla.
Asiayhteys
Oletetaan, että sinulla on luettelo tekstimerkkijonoja alueella B5: B8, ja haluat selvittää, sisältävätkö nämä solut kaikki toisen alueen sanat, E5: E7.
Voisit rakentaa kaavan, joka käyttää sisäkkäisiä IF-käskyjä jokaisen kohteen tarkistamiseen, mutta se ei skaalaa hyvin, jos sinulla on paljon etsimistä. Joka kerta kun lisäät etsittävän sanan, sinun on lisättävä toinen sisäkkäinen IF ja muutettava sulkeita.
Ratkaisu
Ratkaisu on luoda kaava, joka laskee kaikki ottelut kerralla. Kun se on saatu, verrataan yksinkertaisesti kyseistä määrää etsimiemme tuotteiden määrään. Jos ne sopivat yhteen, tiedämme, että solu sisältää kaikki kohteet.
Esitetyssä esimerkissä käytämme kaavaa:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Selitys
Avain on tämä katkelma:
ISNUMBER(SEARCH(things,B5)
Tämä perustuu toiseen kaavaan (selitetty yksityiskohtaisesti tässä), joka yksinkertaisesti tarkistaa solun yhden alimerkinnän suhteen. Jos solu sisältää alimerkkijonon, kaava palauttaa TOSI. Jos ei, kaava palauttaa EPÄTOSI.
Kuitenkin, jos annamme samalle kaavalle luettelon asioista (tässä tapauksessa käytämme nimettyä aluetta nimeltä "asiat", E5: E7), se antaa meille takaisin luettelon TOSI / EPÄTOSI-arvoista, yhden kullekin kohteelle . Tuloksena on taulukko, joka näyttää tältä:
(TOSI; TOSI; TOSI)
Missä kukin TOSI edustaa löydettyä kohdetta ja jokainen TOSI edustaa tuotetta, jota ei löydy.
Voimme pakottaa TOSI / EPÄTOSI-arvot arvoon 1 ja 0 kaksinkertaisella negatiivisella (-, jota kutsutaan myös kaksinkertaiseksi unaariksi):
--ISNUMBER(SEARCH(things,B5))
joka tuottaa tällaisen taulukon:
(1; 1; 1)
Seuraavaksi käsittelemme tätä taulukkoa SUMPRODUCTilla, mikä antaa meille kokonaissumman. Jos tämä summa on sama kuin nimettyjen alueiden "asiat" kohteiden määrä, tiedämme, että olemme löytäneet kaikki asiat ja voimme palauttaa TOSI. Tapa, jolla teemme tämän, on verrata kahta numeroa suoraan. Saamme määrän tyhjiä soluja "asioissa" käyttämällä COUNTA:
=COUNTA(things)
Koodatulla luettelolla
Ei ole vaatimusta, että käytät alueita luetteloasi asioista. Jos etsit vain pientä määrää asioita, voit käyttää taulukon muodossa olevaa luetteloa, jota kutsutaan taulukon vakiona. Esimerkiksi, jos etsit vain punaisia, sinisiä ja vihreitä värejä, voit käyttää ("punainen", "sininen", "vihreä") näin:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
