Formula Excel: verificați dacă o celulă conține una din mai multe valori, dar excludeți alte valori

Presupunând că există două liste de valori, doriți să verificați dacă celula B3 conține una dintre valorile din intervalul E3: E5, dar, în același timp, nu conține valori ale nimănui din intervalul F3: F4, așa cum se arată în imaginea de mai jos. Acest tutorial va oferi o formulă pentru a gestiona rapid această sarcină în Excel și pentru a explica argumentele formulei.

Formula generică:

Argumente

Valoare returnată:

Formula returnează 1 sau 0. Când celula conține una dintre valorile necesare pentru a fi incluse și nu conține valori care trebuie excluse, returnează 1 sau returnează 0. Această formulă, 1 și 0 sunt tratate ca valori logice Adevarat si fals.

Cum funcționează această formulă

Presupunând că doriți să verificați dacă celula B3 conține una dintre valorile din intervalul E3: E5, dar excludeți simultan valorile din intervalul F3: F4, vă rugăm să utilizați formula de mai jos

Anunturi Intrați cheie pentru a obține rezultatul verificării.

Explicație

Partea 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) verifică dacă celula conține valori în E3: E5

Căutare funcție: funcția CĂUTARE returnează poziția primului caracter al șirului de text în interiorul altuia, dacă funcția CĂUTARE găsește textul potrivit, returnează poziția relativă, dacă nu, returnează #VALUE! eroare. De exemplu, aici formula SEARCH($E$3:$E$5,B3) va căuta fiecare valoare a intervalului E3: E5 în celula B3 și returnează locația fiecărui șir de text din celula B3. Se va returna un rezultat matricial astfel: {1; 7; 12}.

Funcția ISNUMBER: funcția ISNUMBER returnează TRUE atunci când o celulă este un număr. Asa de ISNUMBER(SEARCH($E$3:$E$5,B3)) va returna rezultatul matricei ca {adevărat, adevărat, adevărat}, deoarece funcția CĂUTARE găsește 3 numere.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) convertește valoarea ADEVĂRATĂ la 1 și convertește valoarea FALSĂ la 0, deci această formulă schimbă rezultatul matricei în {1; 1; 1}.

SUMPRODUCT funcție: este utilizată pentru a multipla intervale sau matrici de sume împreună și returnează suma produselor. SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) returnează 1 + 1 + 1 = 3.

În cele din urmă, comparați formula din stânga SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) și 0, atâta timp cât rezultatul formulei din stânga este mai mare decât 0, rezultatul va fi ADEVĂRAT sau va reveni FALS. Aici revine ADEVĂRAT.

Partea 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) verifică dacă celula nu conține valori în F3: F4

Formula CĂUTARE ($ F $ 3: $ F $ 4, B3) va căuta fiecare valoare din intervalul E3: E5 în celula B3 și returnează locația fiecărui șir de text din celula B3. Se va returna un rezultat matricial astfel: {#VALUE!; # VALUE!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) va returna rezultatul matricei ca {fals; fals} deoarece funcția CĂUTARE găsește 0 număr.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) convertește valoarea ADEVĂRATĂ la 1 și convertește valoarea FALSĂ la 0, deci această formulă schimbă rezultatul matricei în {0; 0}.

SUMPRODUCT funcție: este utilizată pentru a multipla intervale sau matrici de sume împreună și returnează suma produselor. SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) returnează 0 + 0 = 0.

În cele din urmă, comparați formula din stânga SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) și 0, atâta timp cât rezultatul formulei stânga este egal cu 0, rezultatul va reveni ADEVĂRAT sau va reveni FALS. Aici revine ADEVĂRAT.

Partea 3: Multiple două formule

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

Această formulă, 1 și 0 sunt tratate ca valori logice TRUE și FALSE.

Fișier exemplu

Faceți clic pentru a descărca fișierul eșantion

Formule relative

• Verificați dacă o celulă conține un anumit text
  Pentru a verifica dacă o celulă conține unele texte din intervalul A, dar nu conține textele din intervalul B, puteți utiliza o formulă matrice care combină funcția COUNT, CĂUTARE și ȘI în Excel
• Verificați dacă celula conține unul dintre multele lucruri
  Acest tutorial oferă o formulă pentru a verifica dacă o celulă conține una din mai multe valori în Excel și explică argumentele din formulă și modul în care funcționează formula.
• Verificați dacă celula conține unul dintre lucruri
  Presupunând că în Excel, există o listă de valori în coloana E, doriți să verificați dacă celulele din coloana B conțin toate valorile din coloana E și să returnați ADEVĂRAT sau FALS.
• Verificați dacă celula conține număr
  Uneori, poate doriți să verificați dacă o celulă conține caractere numerice. Acest tutorial oferă o formulă care va reveni ADEVĂRAT dacă celula conține număr, FALS dacă celula nu conține număr.