Γενική φόρμουλα
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Περίληψη
Για να κατηγοριοποιήσετε το κείμενο χρησιμοποιώντας λέξεις-κλειδιά με αντιστοίχιση "περιέχει", μπορείτε να χρησιμοποιήσετε τη συνάρτηση SEARCH, με τη βοήθεια των INDEX και MATCH. Στο παράδειγμα που εμφανίζεται, ο τύπος στο C5 είναι:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
όπου οι λέξεις-κλειδιά είναι το ονομαζόμενο εύρος E5: E14 και οι κατηγορίες είναι η ονομαζόμενη περιοχή F5: F14
Σημείωση: αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter.
Εξήγηση
Στον πυρήνα, αυτή είναι μια συνάρτηση INDEX και MATCH.
Μέσα από τη λειτουργία MATCH, χρησιμοποιούμε τη λειτουργία αναζήτησης για να αναζητήσετε τα κύτταρα της στήλης Β για κάθε εισηγμένη λέξη-κλειδί στην περιοχή με όνομα λέξεις-κλειδιά (Ε5: E14):
SEARCH(keywords,B5)
Επειδή ψάχνουμε για πολλά στοιχεία (στις λέξεις-κλειδιά με την ονομασία εύρους ), θα λάβουμε πολλά αποτελέσματα όπως αυτό:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Η αξία! παρουσιάζεται σφάλμα όταν η ΑΝΑΖΗΤΗΣΗ δεν μπορεί να βρει το κείμενο. Όταν η ΑΝΑΖΗΤΗΣΗ βρίσκει αντιστοιχία, επιστρέφει έναν αριθμό που αντιστοιχεί στη θέση του κειμένου μέσα στο κελί.
Για να αλλάξουμε αυτά τα αποτελέσματα σε πιο εύχρηστη μορφή, χρησιμοποιούμε τη συνάρτηση ISNUMBER, η οποία μετατρέπει όλες τις τιμές σε TRUE / FALSE όπως:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Αυτός ο πίνακας μπαίνει στη συνάρτηση MATCH ως lookup_array, με την τιμή lookup_value να είναι TRUE. Το MATCH επιστρέφει τη θέση της πρώτης ΑΛΗΘΗΣ που βρίσκει στη συστοιχία (7 σε αυτήν την περίπτωση) που παρέχεται στη συνάρτηση INDEX ως η σειρά_num:
=INDEX(categories,7)
Το INDEX επιστρέφει το 7ο στοιχείο σε κατηγορίες , "Auto", ως τελικό αποτέλεσμα.
Με XLOOKUP
Με τη συνάρτηση XLOOKUP, αυτός ο τύπος μπορεί να απλοποιηθεί κάπως. Το XLOOKUP μπορεί να χρησιμοποιήσει την ίδια λογική που χρησιμοποιείται στη συνάρτηση MATCH παραπάνω, οπότε ο αντίστοιχος τύπος είναι:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
Το XLOOKUP εντοπίζει το πρώτο TRUE στον πίνακα και επιστρέφει την αντίστοιχη τιμή από κατηγορίες .
Πρόληψη ψευδών αντιστοιχιών
Ένα πρόβλημα με αυτήν την προσέγγιση είναι ότι μπορεί να λάβετε ψευδείς αντιστοιχίσεις από υποστρώματα που εμφανίζονται μέσα σε μεγαλύτερες λέξεις. Για παράδειγμα, αν προσπαθήσετε να ταιριάξετε το "dr", μπορείτε επίσης να βρείτε το "Andrea", "drink", "dry", κλπ. Καθώς το "dr" εμφανίζεται μέσα σε αυτές τις λέξεις. Αυτό συμβαίνει επειδή το SEARCH κάνει αυτόματα μια αντιστοίχιση "περιέχει".
Για μια γρήγορη εισβολή, μπορείτε να προσθέσετε χώρο γύρω από τις λέξεις αναζήτησης (π.χ. "dr" ή "dr") για να αποφύγετε να πιάσετε το "dr" με άλλη λέξη. Αλλά αυτό θα αποτύχει εάν το "dr" εμφανίζεται πρώτο ή τελευταίο σε ένα κελί, ή εμφανίζεται με σημεία στίξης κ.λπ.
Εάν χρειάζεστε μια πιο ακριβή λύση, μια επιλογή είναι να ομαλοποιήσετε το κείμενο πρώτα σε μια βοηθητική στήλη, φροντίζοντας να προσθέσετε επίσης έναν αρχικό και τελικό χώρο. Στη συνέχεια, μπορείτε να αναζητήσετε ολόκληρες λέξεις που περιβάλλονται από κενά.
