Note: The other languages of the website are Google-translated. Back to English

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.
doc preveri, če vsebuje eno od stvari, vendar izključi 1

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.
doc preveri, če vsebuje eno od stvari, vendar izključi 2

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.
doc preveri, če vsebuje eno od stvari, vendar izključi 3

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.
doc preveri, če vsebuje eno od stvari, vendar izključi 4

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

doc vzorecKliknite za prenos vzorčne datoteke


Relativne formule


Najboljša orodja za pisarniško produktivnost

Kutools za Excel - vam pomaga izstopati iz množice

Bi radi svoje vsakodnevno delo opravili hitro in popolno? Kutools za Excel prinaša 300 zmogljivih naprednih funkcij (združevanje delovnih zvezkov, seštevanje po barvi, razdelitev vsebine celice, pretvorba datuma itd.) in vam prihrani 80 % časa.

  • Zasnovan za 1500 delovnih scenarijev vam pomaga rešiti 80 % Excelovih težav.
  • Vsak dan zmanjšajte na tisoče klikov na tipkovnici in miški, razbremenite utrujene oči in roke.
  • V 3 minutah postanite strokovnjak za Excel. Ni vam več treba zapomniti nobenih bolečih formul in kod VBA.
  • 30-dnevno neomejeno brezplačno preskusno obdobje. 60-dnevno jamstvo za vračilo denarja. Brezplačna nadgradnja in podpora za 2 leti.
Trak Excela (z nameščenim Kutools za Excel)

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, Firefox in New Internet Explorer.
Posnetek zaslona programa Excel (z nameščenim zavihkom Office)
Komentarji (1)
Ocene še ni. Bodite prvi in ​​ocenite!
Ta komentar je moderator na spletnem mestu minimiziral
avec les fonctions en français ça donne : SOMMEPROD(--ESTNUM(CHERCHE(Liste;B2)))
Tu še ni objavljenih komentarjev
Pustite vaše komentarje
Objava kot gost
×
Ocenite to objavo:
0   Znaki
Predlagane lokacije

Sledi nam

Copyright © 2009 - www.extendoffice.com. | Vse pravice pridržane. Poganja ga ExtendOffice. | Kazalo
Microsoft in logotip Office sta blagovni znamki ali registrirani blagovni znamki družbe Microsoft Corporation v ZDA in / ali drugih državah.
Zaščiteno s Sectigo SSL