Formula generică
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
rezumat
Pentru a nu permite intrarea care conține unul dintre multele lucruri, puteți utiliza o regulă de validare a datelor personalizată bazată pe funcția CĂUTARE.
În exemplul prezentat, validarea datelor aplicate la B5: B11 este:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Explicaţie
Regulile de validare a datelor sunt declanșate atunci când un utilizator adaugă sau modifică o valoare a celulei.
Această formulă folosește funcția SEARCH pentru a testa intrarea utilizatorului pentru fiecare valoare din intervalul numit „listă”. Logica de căutare este „conține” - când se găsește o valoare din „listă”, SEARCH returnează poziția valorii ca număr. Dacă nu este găsit, SEARCH returnează o eroare.
Funcția ISNUMBER convertește apoi numerele în TRUE și erorile în FALSE, iar operatorul dublu negativ modifică valorile TRUE FALSE la 1s și zerouri. Deoarece intervalul numit „listă” conține 5 valori, obținem înapoi 5 rezultate într-o matrice ca aceasta:
(0; 0; 0; 0; 0)
SUMPRODUCT apoi rezumă articolele din matrice și rezultatul este testat contra zero. Atâta timp cât toate articolele sunt zero, SUMPRODUCT returnează zero și validarea reușește. Dacă SUMPRODUCT returnează un alt număr (adică când se găsește un element din „listă”) formula returnează FALS și validarea eșuează.
Notă: Referințele de celule din formulele de validare a datelor sunt relative la celula din stânga sus în intervalul selectat atunci când regula de validare este definită, în acest caz B5.
