Γενική φόρμουλα
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Περίληψη
Για να ελέγξετε ένα κελί για μία από τις πολλές συμβολοσειρές και να επιστρέψετε ένα προσαρμοσμένο αποτέλεσμα για τον πρώτο αγώνα που βρέθηκε, μπορείτε να χρησιμοποιήσετε έναν τύπο INDEX / MATCH με βάση τη συνάρτηση SEARCH. Στο παράδειγμα που εμφανίζεται, ο τύπος στο C5 είναι:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
όπου τα πράγματα (E5: E8) και τα αποτελέσματα (F5: F8) ονομάζονται εύρη.
Αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με Control + Shift + Enter.
Εξήγηση
Αυτός ο τύπος χρησιμοποιεί δύο ονόματα εύρους: πράγματα και αποτελέσματα . Εάν μεταφέρετε αυτόν τον τύπο απευθείας, βεβαιωθείτε ότι χρησιμοποιείτε τα ονομαστικά εύρη με τα ίδια ονόματα (καθορίζονται με βάση τα δεδομένα σας). Εάν δεν θέλετε να χρησιμοποιήσετε ονομασμένες περιοχές, χρησιμοποιήστε τις απόλυτες αναφορές.
Ο πυρήνας αυτού του τύπου είναι αυτό το απόσπασμα:
ISNUMBER(SEARCH(things,B5)
Αυτό βασίζεται σε έναν άλλο τύπο (εξηγείται λεπτομερώς εδώ) που ελέγχει ένα κελί για ένα μόνο υπόστρωμα. Εάν το κελί περιέχει το substring, ο τύπος επιστρέφει TRUE. Εάν όχι, ο τύπος επιστρέφει FALSE.
Επειδή δίνουμε στη λειτουργία ΑΝΑΖΗΤΗΣΗ περισσότερα από ένα πράγματα που πρέπει να αναζητήσουμε, στα ονομασμένα εύρη πράγματα , θα μας δώσει περισσότερα το ένα αποτέλεσμα, σε έναν πίνακα που μοιάζει με αυτό:
(#VALUE!;9;#VALUE!;#VALUE!)
Οι αριθμοί αντιπροσωπεύουν αγώνες σε πράγματα , τα σφάλματα αντιπροσωπεύουν στοιχεία που δεν βρέθηκαν.
Για να απλοποιήσουμε τον πίνακα, χρησιμοποιούμε τη συνάρτηση ISNUMBER για να μετατρέψουμε όλα τα στοιχεία του πίνακα σε TRUE ή FALSE. Οποιοσδήποτε έγκυρος αριθμός γίνεται ΑΛΗΘΟΣ, και οποιοδήποτε σφάλμα (δηλαδή ένα πράγμα που δεν βρέθηκε) γίνεται ΛΑΘΟΣ. Το αποτέλεσμα είναι ένας πίνακας ως εξής:
(FALSE;TRUE;FALSE;FALSE)
που πηγαίνει στη συνάρτηση MATCH ως όρισμα lookup_array, με μια τιμή αναζήτησης TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
Στη συνέχεια, το MATCH επιστρέφει τη θέση του πρώτου TRUE που βρέθηκε, 2 σε αυτήν την περίπτωση.
Τέλος, χρησιμοποιούμε τη συνάρτηση INDEX για να ανακτήσουμε ένα αποτέλεσμα από τα ονομαζόμενα αποτελέσματα εύρους στην ίδια θέση:
=INDEX(results,2) // returns "found red"
Μπορείτε να προσαρμόσετε το εύρος αποτελεσμάτων με οποιεσδήποτε τιμές έχουν νόημα στην περίπτωση χρήσης σας.
Πρόληψη ψευδών αντιστοιχιών
Ένα πρόβλημα με αυτήν την προσέγγιση με την προσέγγιση ISNUMBER + SEARCH είναι ότι ενδέχεται να λάβετε ψευδείς αντιστοιχίες από μερικούς αγώνες μέσα σε μεγαλύτερες λέξεις. Για παράδειγμα, αν προσπαθήσετε να ταιριάξετε το "dr", μπορείτε επίσης να βρείτε το "Andrea", "drank", "drip", κ.λπ. καθώς το "dr" εμφανίζεται μέσα σε αυτές τις λέξεις. Αυτό συμβαίνει επειδή η ΑΝΑΖΗΤΗΣΗ κάνει αυτόματα μια αντιστοίχιση "περιέχει-τύπο".
Για μια γρήγορη επιδιόρθωση, μπορείτε να τυλίξετε λέξεις αναζήτησης με χαρακτήρες διαστήματος (π.χ. "dr" ή "dr") για να αποφύγετε την εύρεση του "dr" με άλλη λέξη. Αλλά αυτό θα αποτύχει εάν το "dr" εμφανίζεται πρώτο ή τελευταίο σε ένα κελί.
Εάν χρειάζεστε μια πιο στιβαρή λύση, μία επιλογή είναι να ομαλοποιήσετε το κείμενο πρώτα σε μια βοηθητική στήλη και να προσθέσετε έναν αρχικό και τελικό χώρο. Στη συνέχεια, χρησιμοποιήστε τον τύπο σε αυτήν τη σελίδα στο κείμενο στη στήλη βοηθού, αντί για το αρχικό κείμενο.
