Γενική φόρμουλα
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Περίληψη
Για να επισημάνετε κελιά που περιέχουν μία από πολλές συμβολοσειρές κειμένου, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στις συναρτήσεις ISNUMBER και SEARCH, μαζί με τη συνάρτηση SUMPRODUCT. Στο παράδειγμα που εμφανίζεται, η μορφοποίηση υπό όρους που εφαρμόζεται στο B4: B11 βασίζεται σε αυτόν τον τύπο:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Εξήγηση
Δουλεύοντας από μέσα προς τα έξω, αυτό το μέρος του τύπου αναζητά κάθε κελί στο B4: B11 για όλες τις τιμές στην ονομαζόμενη περιοχή "πράγματα":
--ISNUMBER(SEARCH(things,B4)
Η συνάρτηση ΑΝΑΖΗΤΗΣΗ επιστρέφει τη θέση της τιμής αν βρεθεί, και το σφάλμα # ΑΞΙΑ εάν δεν βρεθεί. Για το B4, τα αποτελέσματα επανέρχονται σε έναν πίνακα όπως αυτό:
(8;#VALUE!;#VALUE!)
Η συνάρτηση ISNUMBER αλλάζει όλα τα αποτελέσματα σε TRUE ή FALSE:
(TRUE;FALSE;FALSE)
Το διπλό αρνητικό μπροστά από το ISNUMBER αναγκάζει TRUE / FALSE στο 1/0:
(1;0;0)
Στη συνέχεια, η συνάρτηση SUMPRODUCT προσθέτει τα αποτελέσματα, τα οποία ελέγχονται έναντι μηδέν:
=SUMPRODUCT((1;0;0))>0
Οποιοδήποτε μη μηδενικό αποτέλεσμα σημαίνει ότι βρέθηκε τουλάχιστον μία τιμή, οπότε ο τύπος επιστρέφει TRUE, ενεργοποιώντας τον κανόνα.
Αγνοήστε τα κενά πράγματα
Για να αγνοήσετε τα κενά κελιά στην ονομαζόμενη περιοχή "πράγματα", μπορείτε να δοκιμάσετε έναν τροποποιημένο τύπο όπως αυτό:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Αυτό λειτουργεί εφ 'όσον οι τιμές κειμένου που δοκιμάζετε δεν περιέχουν τη συμβολοσειρά "FALSE". Εάν συμβαίνει αυτό, μπορείτε να επεκτείνετε τη συνάρτηση IF για να συμπεριλάβετε μια τιμή εάν είναι ψευδές γνωστό ότι δεν συμβαίνει στο κείμενο (π.χ. "zzzz", "####", κ.λπ.)
Επιλογή με διάκριση πεζών-κεφαλαίων
Η αναζήτηση δεν είναι ευαίσθητη σε πεζά. Για να ελέγξετε επίσης την περίπτωση, αντικαταστήστε το SEARCH με το FIND όπως:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Πρόληψη ψευδών αντιστοιχιών
Ένα πρόβλημα με αυτήν την προσέγγιση είναι ότι μπορεί να δείτε ψευδείς αντιστοιχίες που προκαλούνται από υποστρώματα που εμφανίζονται σε μεγαλύτερες λέξεις. Για παράδειγμα, αν προσπαθήσετε να ταιριάξετε το "dr", μπορείτε επίσης να βρείτε το "Andrea", "drink", "dry", κλπ. Καθώς το "dr" εμφανίζεται μέσα σε αυτές τις λέξεις. Αυτό συμβαίνει επειδή το SEARCH εκτελεί αυτόματα έναν αγώνα "περιέχει".
Για μερική επιδιόρθωση, μπορείτε να προσθέσετε χώρο γύρω από τις λέξεις αναζήτησης (π.χ. "dr" ή "dr") για να αποφύγετε να πιάσετε το "dr" με άλλη λέξη. Αλλά αυτό θα αποτύχει εάν το "dr" εμφανίζεται πρώτο ή τελευταίο σε ένα κελί ή εμφανίζεται δίπλα στη στίξη. Αυτό μπορεί να αντιμετωπιστεί εν μέρει προσθέτοντας χώρο και γύρω από το αρχικό κείμενο. Για να προσθέσετε χώρο στην αρχή και στο τέλος και των δύο ταυτόχρονα, μπορείτε να δοκιμάσετε έναν τύπο όπως αυτό:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Ωστόσο, αυτό δεν θα διορθώσει προβλήματα που προκαλούνται από σημεία στίξης.
Εάν χρειάζεστε μια πιο ολοκληρωμένη λύση, μια επιλογή είναι να ομαλοποιήσετε το κείμενο πρώτα σε μια βοηθητική στήλη, φροντίζοντας να προσθέσετε επίσης έναν αρχικό και τελικό χώρο. Στη συνέχεια, μπορείτε να αναζητήσετε ολόκληρες λέξεις που περιβάλλονται από κενά.
