Excel

Solu sisältää yhden monista asioista

Cell Contains One Many Things

Excel-kaava: Solu sisältää yhden monista asioistaYleinen kaava
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0
Yhteenveto

Voit testata, sisältääkö solu yhden monista merkkijonoista, käyttämällä kaavaan perustuvaa kaavaa HAE , LUKU ja YHTEENVETO toimintoja. C5: n kaava kopioituna alaspäin on:

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

missä asioita on nimetty alue E5: E7.



Selitys

Tämän esimerkin tarkoituksena on testata kukin soluista kohdassa B5: B14, onko siinä mitään merkkijonoja nimetty alue asioita (E5: E7). Nämä merkkijonot voivat näkyä missä tahansa solussa, joten tämä on kirjaimellinen 'sisältää' ongelma. C5: n kaava kopioituna alaspäin on:



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

Tämä kaava perustuu toinen kaava joka tarkistaa solun yhden alimerkkijonon suhteen. Jos solu sisältää alimerkkijonon, kaava palauttaa TOSI. Jos ei, kaava palauttaa EPÄTOSI:

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

Kun SEARCH-toiminto löytää merkkijonon, se palauttaa merkkijonon sijainnin numerona. Jos HAKU ei etsi merkkijono, se palauttaa #ARVO! virhe. Tämä tarkoittaa, että ISNUMBER palauttaa TOSI, jos ottelu on, ja EPÄTOSI, jos ei.



Tässä esimerkissä tavoitteena on tarkistaa useampi kuin yksi merkkijono, joten annamme SEARCH-funktion a merkkijonoluettelo että nimetty alue asioita . Koska sisään on 3 merkkijonoa asioita ('punainen', 'vihreä' ja 'sininen'), HAKU palauttaa 3 tulosta tulokseen taulukko kuten tämä:

excelin ehdollisen muotoilun solu sisältää tekstiä
 
{#VALUE!#VALUE!23}

Koska 'punaista' ja 'sinistä' ei löydy, HAKU palauttaa #ARVO! virhe. Koska 'vihreä' näkyy lähellä tekstin loppua solussa B5, SEARCH palauttaa arvon 23 (ts. 'Vihreä' alkaa 23. merkistä).

Tämä taulukko palautetaan suoraan Numero-funktio , joka muuntaa matriisin kohteet TOSI tai EPÄTOSI:



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

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

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

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

excel vba välittää muuttujan osaan
 
{001}

joka toimitetaan suoraan SUMPRODUCT: iin:

 
= SUMPRODUCT ({001}) // returns 1

Vain yksi taulukko käsiteltäväksi SUMPRODUCT laskee matriisin kohteet yhteen ja palauttaa tuloksen. Mikä tahansa nollasta poikkeava tulos tarkoittaa, että meillä on 'osuma', joten lisätään> 0 pakottaaksemme TOSI tai EPÄTOSI lopputuloksen:

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

Huomaa, että mikä tahansa vastaavuuksien yhdistelmä palauttaa luvun, joka on suurempi kuin nolla, ja kaavan palauttaa TOSI.

Koodatulla luettelolla

Etuja merkkijonoluettelossa ei tarvitse käyttää aluetta. Voit käyttää myös taulukon vakio . Esimerkiksi 'punaisen', 'sinisen' tai 'vihreän' tarkistamiseksi voit käyttää seuraavaa kaavaa:

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

SUM-toiminto

Historiallisesti YHTEENVETO esiintyy usein taulukon kaavat , koska se voi käsitellä matriiseja natiivisti, ilman ohjausta + vaihto + enter . Tämä tekee kaavasta 'ystävällisemmän' useimmille käyttäjille. Sisään Excel 365 , joka käsittelee taulukoita syntyperäisesti , SUM-toiminto voidaan käyttää SUMPRODUCTin sijaan ilman ohjausta + vaihto + enter:

kuinka lasketaan kesto Excelissä
 
= SUM (-- ISNUMBER ( SEARCH (things,A1)))>0

Väärien osumien estäminen

Yksi ongelma tässä lähestymistavassa on, että saatat saada vääriä vastaavuuksia alaosista, jotka näkyvät pidempien sanojen sisällä. Esimerkiksi, jos yrität löytää sanan 'dr', saatat löytää myös 'Andrea', 'drink', 'dry' jne., Koska 'dr' esiintyy näiden sanojen sisällä. Tämä tapahtuu, koska HAKU tekee automaattisesti 'sisältää' -hakun.

Nopeasta hakkeroinnista voit lisätä tilaa hakusanojen (esim. 'Dr' tai 'dr') ympärille välttääksesi sanan dr tarttumisen toiseen sanaan. Mutta tämä epäonnistuu, jos ”dr” näkyy ensin tai viimeisenä solussa tai ilmestyy välimerkkien kanssa.

Jos tarvitset tarkemman ratkaisun, yksi vaihtoehto on normalisoi teksti ensin a auttajapylväs , lisäämällä myös johtava ja takana oleva tila. Sitten käytät tämän sivun kaavaa tuloksena olevassa tekstissä.

Liitteet Tiedosto solu sisältää yhden monista asioista.xlsx Kirjailija Dave Bruns


^