Yleinen kaava
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Yhteenveto
Jos haluat testata solua yhdelle monista merkkijonoista, poislukien muut, voit käyttää kaavaa, joka perustuu SEARCH-, ISNUMBER- ja SUMPRODUCT-funktioihin. Esitetyssä esimerkissä kaava kohdassa C5 on:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
missä "sisälly" on nimetty alue E5: E9 ja "poissuljettu" on nimetty alue G5: G6.
Selitys
Ytimessä tämä kaava käyttää SEARCH-toimintoa etsimään useita merkkijonoja solun sisällä. Vasemman SUMPRODUCT-laitteen sisällä SEARCH etsii kaikkia merkkijonoja nimellisestä alueesta "include".
Oikeassa SUMPRODUCT-ohjelmassa SEARCH etsii kaikkia nimettyjen alueiden merkkijonoja "poissulkea".
Kaavan molemmissa osissa HAKU palauttaa numeeriset sijainnit, kun merkkijonoja löytyy, ja virheet, kun ei. ISNUMBER-funktio muuntaa luvut TOSiksi ja virheet EPÄTOSI, ja kaksoisnegatiivi muuntaa TOSI EPÄTOSI arvoksi 1 ja 0.
Tulos tässä vaiheessa näyttää tältä:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Sitten:
=(1>0)*(0=0) =TRUE*TRUE =1
Huomaa: tämä kaava palauttaa joko 1 tai nolla, joita käsitellään kuten TOSI ja EPÄTOSI kaavoissa, ehdollisessa muotoilussa tai tietojen validoinnissa.
