Excel

Celica vsebuje eno izmed mnogih stvari

Cell Contains One Many Things

Formula Excel: Celica vsebuje eno od mnogih stvariSplošna formula | _+_ | Povzetek

Če želite preveriti, ali celica vsebuje enega od številnih nizov, lahko uporabite formulo, ki temelji na ISKANJE , ISNUMBER in PODROČJE funkcije. Kopirana formula v C5 je:



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

kje stvari ali je imenovani obseg E5: E7.

Pojasnilo

Cilj tega primera je preizkusiti vsako celico v B5: B14 in preveriti, ali vsebuje katerega od nizov v imenovani obseg stvari (E5: E7). Ti nizi se lahko pojavijo kjerkoli v celici, zato je to dobesedna težava 'vsebuje'. Kopirana formula v C5 je:





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

Ta formula temelji na druga formula ki preveri celico za en sam podniz. Če celica vsebuje podniz, formula vrne TRUE. Če ne, formula vrne FALSE:

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

Ko Funkcija SEARCH najde niz, vrne položaj tega niza kot številko. Če IŠČI ne poiščite niz, vrne #VALUE! napaka. To pomeni, da ISNUMBER vrne TRUE, če se ujema, in FALSE, če ne.



V tem primeru je cilj preveriti več nizov, zato funkciji SEARCH dajemo a seznam nizov v imenovani obseg stvari . Ker so v njem 3 nizi stvari ('rdeča', 'zelena' in 'modra'), SEARCH vrne 3 rezultate v an matriko Všečkaj to:

kako narediti neto sedanjo vrednost v
 
 ISNUMBER ( SEARCH (substring,B5)) // test for substring

Ker 'rdeča' in 'modra' nista najdena, SEARCH vrne #VALUE! napaka. Ker pa se 'zelena' pojavi blizu konca besedila v celici B5, SEARCH vrne 23 (tj. 'Zelena' se začne pri 23. znaku).

Ta matrika se vrne neposredno v Funkcija ISNUMBER , ki pretvori elemente v matriki v TRUE ali FALSE:

 
{#VALUE!#VALUE!23}

Logično, če imamo v matriki celo eno TRUE, vemo, da celica vsebuje vsaj enega od nizov, ki jih iščemo. Najlažji način za preverjanje vrednosti TRUE je, da vse vrednosti dodate skupaj. To lahko storimo z SUMPRODUCT funkcija , najprej moramo vrednosti TRUE / FALSE prisiliti na 1s in 0s z a dvojno negativno (--) Všečkaj to:

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

Tako dobimo novo matriko, ki vsebuje samo 1s in 0s:

izračunajte časovno razliko med dvakratoma
 
--{FALSEFALSETRUE} // coerce to 1s and 0s

ki je dostavljen neposredno podjetju SUMPRODUCT:

 
{001}

S samo enim nizom za obdelavo SUMPRODUCT sešteje elemente v matriki in vrne rezultat. Vsak rezultat, ki ni nič, pomeni, da imamo 'zadetek', zato dodamo> 0, da vsilimo končni rezultat TRUE ali FALSE:

 
= SUMPRODUCT ({001}) // returns 1

Upoštevajte, da bo vsaka kombinacija ujemanj vrnila številko, večjo od nič, in povzročila, da formula vrne TRUE.

S težko kodiranim seznamom

Za seznam nizov, ki jih iščete, ni treba uporabiti obsega. Uporabite lahko tudi matrika konstanta . Na primer, če želite preveriti 'rdeče', 'modro' ali 'zeleno', lahko uporabite formulo, kot je ta:

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

Funkcija SUM

Zgodovinsko gledano PODROČJE pogosto pojavlja v matrične formule , ker lahko ročno upravlja z matrikami, brez nadzora + shift + enter . Zaradi tega je formula za večino uporabnikov prijaznejša. V Excel 365 , ki obravnava matrike domače , Funkcija SUM se lahko uporablja namesto SUMPRODUCT brez nadzora + shift + enter:

kako izračunati trajanje v
 
= SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

Preprečevanje lažnih ujemanj

Ena od težav pri tem pristopu je, da lahko dobite napačna ujemanja iz podnizov, ki se pojavljajo v daljših besedah. Na primer, če poskusite ujemati 'dr', boste morda našli tudi 'Andrea', 'pijačo', 'suho' itd., Ker se v teh besedah ​​pojavi 'dr'. To se zgodi, ker SEARCH samodejno izvede ujemanje 'vsebuje'.

Za hiter kramp lahko okoli iskalnih besed (npr. 'Dr' ali 'dr') dodate presledek, da se izognete ulovu 'dr' v drugi besedi. Toda to ne bo uspelo, če se 'dr' pojavi prvi ali zadnji v celici ali se prikaže z ločili.

Če potrebujete natančnejšo rešitev, je ena od možnosti normalizirati besedilo najprej v a pomožni stolpec , pri čemer pazimo, da dodamo tudi vodilni in zadnji prostor. Nato uporabite formulo na tej strani za nastalo besedilo.

Priloge mapa celica vsebuje eno od mnogih stvari.xlsx Avtor Dave Bruns


^