Γενική φόρμουλα
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Περίληψη
Για να απαγορεύσετε την εισαγωγή που περιέχει ένα από πολλά πράγματα, μπορείτε να χρησιμοποιήσετε έναν προσαρμοσμένο κανόνα επικύρωσης δεδομένων βάσει της συνάρτησης SEARCH
Στο παράδειγμα που εμφανίζεται, η επικύρωση δεδομένων που εφαρμόζεται στο B5: B11 είναι:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Εξήγηση
Οι κανόνες επικύρωσης δεδομένων ενεργοποιούνται όταν ένας χρήστης προσθέτει ή αλλάζει μια τιμή κελιού.
Αυτός ο τύπος χρησιμοποιεί τη συνάρτηση SEARCH για να ελέγξει την είσοδο του χρήστη για κάθε τιμή στην ονομαζόμενη περιοχή "λίστα". Η λογική αναζήτησης είναι "περιέχει" - όταν βρεθεί μια τιμή από τη "λίστα", το SEARCH επιστρέφει τη θέση της τιμής ως αριθμό. Εάν δεν βρεθεί, το SEARCH επιστρέφει ένα σφάλμα.
Στη συνέχεια, η συνάρτηση ISNUMBER μετατρέπει τους αριθμούς σε TRUE και τα σφάλματα σε FALSE και ο διπλός αρνητικός τελεστής αλλάζει τις τιμές TRUE FALSE σε 1s και μηδενικά. Δεδομένου ότι η ονομαζόμενη περιοχή "λίστα" περιέχει 5 τιμές, λαμβάνουμε 5 αποτελέσματα σε έναν πίνακα όπως αυτό:
(0; 0; 0; 0; 0)
Στη συνέχεια, το SUMPRODUCT συνοψίζει τα στοιχεία του πίνακα και το αποτέλεσμα ελέγχεται μηδέν. Εφόσον όλα τα στοιχεία είναι μηδέν, το SUMPRODUCT επιστρέφει μηδέν και η επικύρωση πετυχαίνει. Εάν το SUMPRODUCT επιστρέψει άλλον αριθμό (δηλαδή όταν βρεθεί ένα στοιχείο στη "λίστα"), ο τύπος επιστρέφει FALSE και η επικύρωση αποτυγχάνει.
Σημείωση: Οι αναφορές κελιών σε τύπους επικύρωσης δεδομένων σχετίζονται με το επάνω αριστερό κελί στο εύρος που επιλέγεται όταν ορίζεται ο κανόνας επικύρωσης, στην περίπτωση αυτή B5.
