Γενική φόρμουλα
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Περίληψη
Για να ελέγξετε ένα κελί για ένα από πολλά πράγματα και να επιστρέψετε τον πρώτο αγώνα που βρίσκεται στη λίστα, μπορείτε να χρησιμοποιήσετε έναν τύπο INDEX / MATCH που χρησιμοποιεί το SEARCH ή το FIND για να εντοπίσει έναν αγώνα. Στο παράδειγμα που εμφανίζεται, ο τύπος στο C5 είναι:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
όπου "πράγματα" είναι η ονομαζόμενη περιοχή E5: E9.
Σημείωση: αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με Control + Shift + Enter.
Εξήγηση
Σε αυτό το παράδειγμα, έχουμε μια λίστα χρωμάτων σε μια ονομαστική σειρά που ονομάζεται "πράγματα" (E5: E9). Θέλουμε να ελέγξουμε το κείμενο στη στήλη Β για να δούμε αν περιέχει οποιοδήποτε από αυτά τα χρώματα. Εάν ναι, θέλουμε να επιστρέψουμε το όνομα του πρώτου χρώματος που βρέθηκε.
Δουλεύοντας από μέσα προς τα έξω, αυτός ο τύπος χρησιμοποιεί τη συνάρτηση ISNUMBER και SEARCH για να αναζητήσει το κείμενο στο B5 για κάθε χρώμα που αναφέρεται σε "πράγματα" όπως αυτό:
ISNUMBER(SEARCH(things,B5)
Αυτή η έκφραση βασίζεται σε έναν τύπο (εξηγείται λεπτομερώς εδώ) που ελέγχει ένα κελί για ένα μόνο υπόστρωμα. Εάν το κελί περιέχει το substring, η έκφραση επιστρέφει TRUE. Εάν όχι, η έκφραση επιστρέφει FALSE.
Όταν δώσουμε σε αυτήν την ΑΝΑΖΗΤΗΣΗ μια λίστα πραγμάτων (αντί για ένα πράγμα) θα μας δώσει πίσω μια σειρά αποτελεσμάτων. Κάθε χρώμα που βρέθηκε θα δημιουργήσει μια αριθμητική θέση και τα χρώματα που δεν βρέθηκαν θα δημιουργήσουν ένα σφάλμα:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Στη συνέχεια, η συνάρτηση ISNUMBER μετατρέπει τα αποτελέσματα σε τιμές TRUE / FALSE. Οποιοσδήποτε αριθμός γίνεται ΑΛΗΘΟΣ και οποιοδήποτε σφάλμα (δεν βρέθηκε) γίνεται ΛΑΘΟΣ. Το αποτέλεσμα είναι ένας πίνακας ως εξής:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Αυτός ο πίνακας επιστρέφεται στη συνάρτηση MATCH ως όρισμα πίνακα. Η τιμή αναζήτησης είναι ΑΛΗΘΕΙΑ και ο τύπος αντιστοίχισης ορίζεται στο μηδέν για να επιβάλει έναν ακριβή αγώνα. Όταν υπάρχει ένα χρώμα που ταιριάζει, το MATCH επιστρέφει την πρώτη θέση που βρέθηκε ΑΛΗΘΕΙΑ Αυτή η τιμή τροφοδοτείται στη συνάρτηση INDEX ως αριθμός σειράς, με την ονομαζόμενη περιοχή "πράγματα" να παρέχεται ως πίνακας. Όταν υπάρχει τουλάχιστον ένας αγώνας, το INDEX επιστρέφει το χρώμα σε αυτήν τη θέση. Όταν δεν υπάρχει αντιστοιχία, αυτός ο τύπος επιστρέφει το σφάλμα # N / A.
Με σκληρές κωδικές τιμές
Εάν δεν θέλετε να ρυθμίσετε μια εξωτερική ονομαστική περιοχή όπως "πράγματα" σε αυτό το παράδειγμα, μπορείτε να προσθέσετε τιμές στον κώδικα ως "σταθερές συστοιχιών" όπως αυτό:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Λάβετε τον πρώτο αγώνα στο κελί
Η γλώσσα εδώ είναι αρκετά συγκεχυμένη, αλλά ο παραπάνω τύπος θα επιστρέψει τον πρώτο αγώνα που βρίσκεται στη λίστα με τα πράγματα που πρέπει να αναζητήσετε. Αν αντίθετα θέλετε να επιστρέψετε τον πρώτο αγώνα που βρίσκεται στο κελί που δοκιμάζεται, μπορείτε να δοκιμάσετε έναν τύπο όπως αυτό:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Σε αυτήν την έκδοση του τύπου, η συνάρτηση MATCH έχει ρυθμιστεί για να αναζητήσει το αποτέλεσμα αυτού του αποσπάσματος:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
που χρησιμοποιεί τη συνάρτηση AGGREGATE για να πάρει την ελάχιστη τιμή στα αποτελέσματα που επιστρέφονται από το SEARCH. Χρειαζόμαστε το AGGREGATE εδώ, επειδή ο εισερχόμενος πίνακας πιθανότατα θα περιέχει σφάλματα (επιστρέφονται από την ΑΝΑΖΗΤΗΣΗ όταν δεν εντοπίζονται τα πράγματα) και χρειαζόμαστε μια συνάρτηση που θα αγνοεί αυτά τα σφάλματα και εξακολουθεί να μας δίνει την ελάχιστη αριθμητική τιμή.
Το αποτέλεσμα από το AGGREGATE επιστρέφεται απευθείας στο MATCH ως τιμή αναζήτησης, μαζί με τον ίδιο πίνακα που επιστρέφεται από το SEARCH. Το τελικό αποτέλεσμα είναι ο πρώτος αγώνας που βρέθηκε στο κελί και όχι ο πρώτος αγώνας που βρέθηκε στη λίστα πραγμάτων.
