Formula generică
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
rezumat
Pentru a testa o celulă pentru una dintre multele șiruri, în timp ce excludeți altele, puteți utiliza o formulă bazată pe funcțiile SEARCH, ISNUMBER și SUMPRODUCT. În exemplul prezentat, formula din C5 este:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
unde „include” este intervalul numit E5: E9 și „exclude” este domeniul numit G5: G6.
Explicaţie
La bază, această formulă folosește funcția SEARCH pentru a căuta mai multe șiruri în interiorul unei celule. În interiorul SUMPRODUCT din stânga, SEARCH caută toate șirurile din intervalul numit „include”.
În SUMPRODUCT dreapta, SEARCH caută toate șirurile din intervalul numit „exclude”.
În ambele părți ale formulei, SEARCH returnează poziții numerice atunci când sunt găsite șiruri și erori când nu. Funcția ISNUMBER convertește numerele în TRUE și erorile în FALSE, iar negativul dublu convertește valorile TRUE FALSE în 1 și 0.
Rezultatul în acest moment arată astfel:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Apoi:
=(1>0)*(0=0) =TRUE*TRUE =1
Notă: această formulă returnează fie 1, fie zero, care sunt tratate ca ADEVĂRAT și FALS în formule, formatare condiționată sau validare de date.
