EXCEL -KAAVA: SOLUSSA ON YKSI MONISTA ASIOISTA

Yleinen kaava | _+_ | Yhteenveto

Voit testata, sisältääkö solu yhden monista merkkijonoista käyttämällä kaavaa, joka perustuu HAE , ISNUMBER ja SUMPRODUCT toimintoja. C5: n kaava kopioituina on:

= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0

missä asioita on nimetty alue E5: E7.

Selitys

Tämän esimerkin tavoitteena on testata jokainen solu B5: B14: ssä nähdäkseen, sisältääkö se jotakin nimetty alue asioita (E5: E7). Nämä merkkijonot voivat näkyä missä vain solussa, joten tämä on kirjaimellinen 'sisältää' -ongelma. C5: n kaava kopioituina on:

 = SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Tämä kaava perustuu toinen kaava joka tarkistaa solun yksittäisen alimerkkijonon osalta. Jos solu sisältää alimerkkijonon, kaava palauttaa arvon TOSI. Jos ei, kaava palauttaa EPÄTOSI:

 = SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Kun SEARCH -toiminto löytää merkkijonon, se palauttaa kyseisen merkkijonon sijainnin numerona. Jos ETSI ei Etsi merkkijono, se palauttaa #ARVO! virhe. Tämä tarkoittaa, että ISNUMBER palauttaa arvon TOSI, jos osuma on olemassa, ja epätosi, jos ei.

Tässä esimerkissä tavoitteena on tarkistaa useampi kuin yksi merkkijono, joten annamme SEARCH -funktion a merkkijonojen luettelo kohdassa nimetty alue asioita . Koska siinä on 3 merkkijonoa asioita ('punainen', 'vihreä' ja 'sininen'), SEARCH palauttaa 3 tulosta matriisi kuten tämä:

excelin ehdollisen muotoilun solu sisältää tekstiä

  ISNUMBER ( SEARCH (substring,B5)) // test for substring

Koska 'punaista' ja 'sinistä' ei löydy, HAKU palauttaa #ARVO! virhe. Koska 'vihreä' näkyy kuitenkin tekstin lopussa solussa B5, SEARCH palauttaa 23 (eli 'vihreä' alkaa 23. merkistä).

Tämä taulukko palautetaan suoraan ISNUMBER -funktio , joka muuntaa taulukon kohteet joko TOSIIN tai EPÄTOSIIN:

 {#VALUE!#VALUE!23}

Loogisesti, jos taulukossa on vain yksi TOSI, tiedämme, että solu sisältää ainakin yhden etsimistämme merkkijonoista. Helpoin tapa tarkistaa TOSI on lisätä kaikki arvot yhteen. Voimme tehdä sen SUMPRODUCT -toiminto , mutta ensin meidän on pakotettava TRUE / FALSE -arvot arvoihin 1s ja 0s a: lla kaksinkertainen negatiivinen (--) kuten tämä:

  ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

Tämä tuottaa uuden taulukon, joka sisältää vain 1s ja 0s:

excel vba välittää muuttujan osaan

 --{FALSEFALSETRUE} // coerce to 1s and 0s

joka toimitetaan suoraan SUMPRODUCTille:

 {001}

SUMPRODUCT laskee yhteen taulukon kohteet ja palauttaa tuloksen vain yhden taulukon käsittelemiseksi. Mikä tahansa tulos, joka ei ole nolla, tarkoittaa, että saamme osuman, joten lisäämme> 0 pakottaaksesi lopputuloksen TOSI tai EPÄTOSI:

 = SUMPRODUCT ({001}) // returns 1

Huomaa, että mikä tahansa osumien yhdistelmä palauttaa luvun, joka on suurempi kuin nolla, ja kaava palauttaa arvon TOSI.

Kovalla koodilla varustetulla luettelolla

Etsittävän merkkijonoluettelon ei tarvitse käyttää aluetta. Voit myös käyttää matriisi vakio . Jos haluat esimerkiksi tarkistaa 'punaisen', 'sinisen' tai 'vihreän', voit käyttää seuraavanlaista kaavaa:

 = SUMPRODUCT ({001})>0 // returns TRUE

SUM -toiminto

Historiallisesti, SUMPRODUCT esiintyy usein sisään matriisikaavat , koska se pystyy käsittelemään matriiseja luonnollisesti, ilman ohjausta + vaihto + enter . Tämä tekee kaavasta 'ystävällisemmän' useimmille käyttäjille. Sisään Excel 365 , joka käsittelee matriiseja alkuperäisesti , SUM -toiminto voidaan käyttää SUMPRODUCT: n sijasta ilman ohjausta + vaihto + enter:

kuinka lasketaan kesto Excelissä

 = SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

Väärien osumien estäminen

Yksi ongelma tässä lähestymistavassa on se, että saatat saada vääriä vastaavuuksia alijonoista, jotka näkyvät pidempien sanojen sisällä. Jos esimerkiksi yrität yhdistää 'dr', saatat löytää myös 'Andrea', 'drink', 'dry' jne., Koska 'dr' näkyy näiden sanojen sisällä. Tämä johtuu siitä, että SEARCH tekee automaattisesti 'sisältää' -haun.

Nopeaan hakkerointiin lisäämällä välilyöntiä hakusanojen (eli 'dr' tai 'dr') ympärille välttääksesi dr. Mutta tämä epäonnistuu, jos 'dr' näkyy ensin tai viimeisenä solussa tai välimerkillä.

Jos tarvitset tarkemman ratkaisun, yksi vaihtoehto on normalisoi teksti ensin a auttajapylväs , huolehtien siitä, että lisätään myös johtava ja perään jäävä tila. Käytä sitten tämän sivun kaavaa tuloksena olevassa tekstissä.

Liitteet