Excel formula: Preverite, ali celica vsebuje eno od več vrednosti, izključite pa druge vrednosti
Če obstajata dva seznama vrednosti, želite preveriti, ali celica B3 vsebuje eno od vrednosti v obsegu E3: E5, hkrati pa ne vsebuje nobene vrednosti v območju F3: F4, kot je prikazano spodaj. Ta vadnica bo zagotovila formulo za hitro obravnavo te naloge v Excelu in razložila argumente formule.
Splošna formula:
| =(SUMPRODUCT(--ISNUMBER(SEARCH(include,text)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,text)))=0) |
Argumenti
| Text: the text string you want to check. |
| Include: the values you want to check if argument text contains. |
| Exclude: the values you want to check if argument text does not contain. |
Vrnjena vrednost:
Formula vrne 1 ali 0. Ko celica vsebuje eno od vrednosti, ki jo je treba vključiti, in ne vsebuje nobenih vrednosti, ki jih je treba izključiti, vrne 1 ali vrne 0. Te formule, 1 in 0 se obravnavajo kot logične vrednosti TRUE in FALSE.
Kako deluje ta formula
Recimo, da želite preveriti, ali celica B3 vsebuje eno od vrednosti v območju E3: E5, hkrati pa izključuje vrednosti v območju F3: F4, uporabite spodnjo formulo
| =(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) |
Pritisnite Vnesite tipko, da dobite rezultat preverjanja.
Izjava
Del 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) preveri, ali celica vsebuje vrednosti v E3: E5
ISKANJE funkcija: funkcija SEARCH vrne položaj prvega znaka besedilnega niza znotraj drugega, če funkcija SEARCH najde ujemajoče se besedilo, vrne relativni položaj, v nasprotnem primeru pa vrne #VALUE! napaka. Tu je na primer formula SEARCH($E$3:$E$5,B3) bo poiskal vsako vrednost obsega E3: E5 v celici B3 in vrnil lokacijo vsakega besedilnega niza v celici B3. Vrnil bo rezultat matrike, kot sledi: {1; 7; 12}.
Funkcija ISNUMBER: funkcija ISNUMBER vrne TRUE, če je celica številka. Torej ISNUMBER(SEARCH($E$3:$E$5,B3)) vrne rezultat matrike kot {true, true, true}, saj funkcija SEARCH najde 3 številke.
--ISNUMBER(SEARCH($E$3:$E$5,B3)) pretvori vrednost TRUE v 1 in vrednost FALSE v 0, zato ta formula spremeni rezultat polja v {1; 1; 1}.
SUMPRODUCT funkcija: se uporablja za množenje obsegov ali nizov vsote in vrne vsoto izdelkov. The SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) vrne 1 + 1 + 1 = 3.
Končno primerjajte levo formulo SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) in 0, dokler je rezultat leve formule večji od 0, bo rezultat TRUE ali pa bo vrnil FALSE. Tu vrne TRUE.
Del 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) preveri, ali celica ne vsebuje vrednosti v F3: F4
Formula ISKANJE ($ F $ 3: $ F $ 4, B3) bo poiskal vsako vrednost v obsegu E3: E5 v celici B3 in vrnil lokacijo vsakega besedilnega niza v celici B3. Vrnil bo rezultat matrike, kot sledi: {#VREDNOST!; # VREDNOST!}.
ISNUMBER(SEARCH($F$3:$F$4,B3)) bo vrnil rezultat polja kot {false; false} ker funkcija SEARCH najde 0 številko.
--ISNUMBER(SEARCH($F$3:$F$4,B3)) pretvori vrednost TRUE v 1 in vrednost FALSE v 0, zato ta formula spremeni rezultat polja v {0; 0}.
SUMPRODUCT funkcija: se uporablja za množenje obsegov ali nizov vsote in vrne vsoto izdelkov. The SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) vrne 0 + 0 = 0.
Končno primerjajte levo formulo SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) in 0, dokler je rezultat leve formule enak 0, rezultat vrne TRUE ali pa vrne FALSE. Tu vrne TRUE.
3. del: Več dveh formul
=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)
=TRUE*TRUE
=1
Te formule, 1 in 0 se obravnavajo kot logični vrednosti TRUE in FALSE.
Vzorčna datoteka
Kliknite za prenos vzorčne datoteke
Relativne formule
- Preverite, ali celica vsebuje določeno besedilo
Če želite preveriti, ali celica vsebuje nekaj besedil v obsegu A, vendar ne vsebuje besedil v obsegu B, lahko uporabite matrično formulo, ki v Excelu združuje funkcijo COUNT, SEARCH in AND - Preverite, ali celica vsebuje eno od mnogih stvari
Ta vadnica vsebuje formulo za preverjanje, ali celica vsebuje eno od več vrednosti v Excelu, in razlaga argumente v formuli in kako formula deluje. - Preverite, ali celica vsebuje eno od stvari
Če v Excelu obstaja seznam vrednosti v stolpcu E, želite preveriti, ali celice v stolpcu B vsebujejo vse vrednosti v stolpcu E, in vrnite TRUE ali FALSE. - Preverite, ali celica vsebuje številko
Včasih boste morda želeli preveriti, ali celica vsebuje številske znake. Ta vadnica vsebuje formulo, ki vrne TRUE, če celica vsebuje število, FALSE, če celica ne vsebuje številke.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel - vam pomaga izstopati iz množice
Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...
Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)
- Eno sekundo za preklop med desetinami odprtih dokumentov!
- Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
- Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
- Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.
