Γενική φόρμουλα
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Περίληψη
Για να ελέγξετε ένα κελί για να δείτε εάν περιέχει μία από πολλές συμβολοσειρές, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στις συναρτήσεις SEARCH, ISNUMBER και SUMPRODUCT. Ο τύπος στο C5, αντιγράφεται, είναι:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
όπου τα πράγματα είναι το ονομαζόμενο εύρος E5: E9.
Εξήγηση
Θέλουμε να δοκιμάσουμε κάθε κελί στο B5: B11 για να δούμε αν περιέχει κάποια από τις συμβολοσειρές στα ονομασμένα εύρη πράγματα (E5: E9). Ο τύπος που χρησιμοποιούμε στο C5, αντιγράφεται, είναι:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Αυτός ο τύπος βασίζεται σε έναν τύπο (εξηγείται εδώ) που ελέγχει ένα κελί για ένα μόνο υπόστρωμα. Εάν το κελί περιέχει το substring, ο τύπος επιστρέφει TRUE. Εάν όχι, ο τύπος επιστρέφει FALSE:
ISNUMBER(SEARCH(things,B5))
Ωστόσο, σε αυτήν την περίπτωση, δίνουμε στο SEARCH μια λίστα συμβολοσειρών. Δεδομένου ότι υπάρχουν 5 συμβολοσειρές στα πράγματα , το SEARCH επιστρέφει 5 αποτελέσματα σε έναν πίνακα όπως αυτό:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Όταν το SEARCH βρίσκει μια συμβολοσειρά, επιστρέφει τη θέση αυτής της συμβολοσειράς. Εάν η ΑΝΑΖΗΤΗΣΗ δεν βρει μια συμβολοσειρά, επιστρέφει μια # ΑΞΙΑ! λάθος. Επειδή το "κίτρινο" εμφανίζεται ως η πρώτη λέξη στο B5, βλέπουμε ένα 1. Επειδή οι άλλες συμβολοσειρές δεν βρέθηκαν, τα άλλα 4 στοιχεία είναι σφάλματα.
Αυτός ο πίνακας επιστρέφεται απευθείας στη συνάρτηση ISNUMBER. Στη συνέχεια, ο ISNUMBER επιστρέφει έναν πίνακα τιμών TRUE / FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Εάν έχουμε ακόμη ένα ΑΛΗΘΕΙΑ στον πίνακα, γνωρίζουμε ότι ένα κελί περιέχει τουλάχιστον μία από τις συμβολοσειρές που αναζητούμε. Ο ευκολότερος τρόπος για να ελέγξετε το TRUE είναι να προσθέσετε όλες τις τιμές μαζί. Μπορούμε να το κάνουμε με SUMPRODUCT, αλλά πρώτα πρέπει να επιβάλουμε τις τιμές TRUE / FALSE σε 1s και 0s με διπλό αρνητικό (-) όπως αυτό:
--ISNUMBER(SEARCH(things,B5))
Αυτό αποδίδει έναν νέο πίνακα που περιέχει μόνο 1s και 0s:
(1;0;0;0;0)
παραδόθηκε απευθείας στο SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Με ένα μόνο πίνακα για επεξεργασία, το SUMPRODUCT προσθέτει τα στοιχεία του πίνακα και επιστρέφει ένα αποτέλεσμα. Οποιοδήποτε μη μηδενικό αποτέλεσμα σημαίνει ότι έχουμε ένα "χτύπημα", οπότε προσθέτουμε> 0 για να επιβάλουμε ένα τελικό αποτέλεσμα TRUE ή FALSE:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Με μια σκληρή κωδικοποιημένη λίστα
Δεν είναι απαραίτητο να χρησιμοποιήσετε ένα εύρος για τη λίστα συμβολοσειρών που θα αναζητήσετε. Μπορείτε επίσης να χρησιμοποιήσετε μια σταθερά πίνακα. Για παράδειγμα, για να ελέγξετε για "κόκκινο", "μπλε" και "πράσινο", μπορείτε να χρησιμοποιήσετε έναν τύπο όπως αυτό:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Πρόληψη ψευδών αντιστοιχιών
Ένα πρόβλημα με αυτήν την προσέγγιση είναι ότι μπορεί να λάβετε ψευδείς αντιστοιχίσεις από υποστρώματα που εμφανίζονται μέσα σε μεγαλύτερες λέξεις. Για παράδειγμα, αν προσπαθήσετε να ταιριάξετε το "dr", μπορείτε επίσης να βρείτε το "Andrea", "drink", "dry", κλπ. Καθώς το "dr" εμφανίζεται μέσα σε αυτές τις λέξεις. Αυτό συμβαίνει επειδή το SEARCH κάνει αυτόματα μια αντιστοίχιση "περιέχει".
Για μια γρήγορη εισβολή, μπορείτε να προσθέσετε χώρο γύρω από τις λέξεις αναζήτησης (π.χ. "dr" ή "dr") για να αποφύγετε να πιάσετε το "dr" με άλλη λέξη. Αλλά αυτό θα αποτύχει εάν το "dr" εμφανιστεί πρώτο ή τελευταίο σε ένα κελί ή εμφανιστεί με σημεία στίξης.
Εάν χρειάζεστε μια πιο ακριβή λύση, μια επιλογή είναι να ομαλοποιήσετε το κείμενο πρώτα σε μια βοηθητική στήλη, φροντίζοντας να προσθέσετε επίσης έναν αρχικό και τελικό χώρο. Στη συνέχεια, χρησιμοποιείτε τον τύπο σε αυτήν τη σελίδα στο κείμενο που προκύπτει.
