Γενική φόρμουλα
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Περίληψη
Εάν θέλετε να δοκιμάσετε ένα κελί για να δείτε αν περιέχει όλα τα στοιχεία σε μια λίστα, μπορείτε να το κάνετε με έναν τύπο που χρησιμοποιεί τη συνάρτηση SEARCH, με τη βοήθεια των λειτουργιών ISNUMBER, SUMPRODUCT και COUNTA.
Συμφραζόμενα
Ας υποθέσουμε ότι έχετε μια λίστα συμβολοσειρών κειμένου στην περιοχή B5: B8 και θέλετε να μάθετε εάν αυτά τα κελιά περιέχουν όλες τις λέξεις σε άλλο εύρος, E5: E7.
Θα μπορούσατε να δημιουργήσετε έναν τύπο που χρησιμοποιεί ένθετες δηλώσεις IF για έλεγχο για κάθε στοιχείο, αλλά αυτό δεν θα κλιμακωθεί καλά αν έχετε πολλά πράγματα να αναζητήσετε. Κάθε φορά που προσθέτετε μια λέξη για αναζήτηση, θα πρέπει να προσθέτετε ένα άλλο ένθετο IF και να προσαρμόζετε παρενθέσεις.
Λύση
Η λύση είναι να δημιουργήσετε έναν τύπο που μετρά όλους τους αγώνες ταυτόχρονα. Μόλις το έχουμε, συγκρίνουμε απλώς αυτό το πλήθος με το πλήθος των αντικειμένων που αναζητούμε. Αν ταιριάζουν, γνωρίζουμε ότι ένα κελί περιέχει όλα τα στοιχεία.
Στο παράδειγμα που εμφανίζεται, ο τύπος που χρησιμοποιούμε είναι:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Εξήγηση
Το κλειδί είναι αυτό το απόσπασμα:
ISNUMBER(SEARCH(things,B5)
Αυτό βασίζεται σε έναν άλλο τύπο (εξηγείται λεπτομερώς εδώ) που απλά ελέγχει ένα κελί για ένα μόνο υπόστρωμα. Εάν το κελί περιέχει το substring, ο τύπος επιστρέφει TRUE. Εάν όχι, ο τύπος επιστρέφει FALSE.
Ωστόσο, εάν δώσουμε στον ίδιο τύπο μια λίστα πραγμάτων (σε αυτήν την περίπτωση, χρησιμοποιούμε μια ονομαζόμενη περιοχή που ονομάζεται "πράγματα", E5: E7), θα μας δώσει μια λίστα τιμών TRUE / FALSE, μία για κάθε στοιχείο στο . Το αποτέλεσμα είναι ένας πίνακας που μοιάζει με αυτό:
(TRUE; TRUE; TRUE)
Όπου κάθε TRUE αντιπροσωπεύει ένα στοιχείο που βρέθηκε και κάθε FALSE αντιπροσωπεύει ένα στοιχείο που δεν βρέθηκε.
Μπορούμε να επιβάλουμε τις τιμές TRUE / FALSE σε 1s και 0s με διπλό αρνητικό (-, που ονομάζεται επίσης διπλό unary):
--ISNUMBER(SEARCH(things,B5))
που αποδίδει έναν πίνακα όπως αυτό:
(1; 1; 1)
Στη συνέχεια, επεξεργαζόμαστε αυτόν τον πίνακα με SUMPRODUCT, το οποίο θα μας δώσει ένα συνολικό άθροισμα. Εάν αυτό το άθροισμα είναι ίσο με τον αριθμό των αντικειμένων στην ονομαζόμενη περιοχή "πράγματα", γνωρίζουμε ότι βρήκαμε όλα τα πράγματα και μπορούμε να επιστρέψουμε ΑΛΗΘΕΙΑ. Ο τρόπος που το κάνουμε αυτό είναι να συγκρίνουμε τους δύο αριθμούς απευθείας. Λαμβάνουμε έναν αριθμό κενών κελιών στα "πράγματα" χρησιμοποιώντας το COUNTA:
=COUNTA(things)
Με μια σκληρή κωδικοποιημένη λίστα
Δεν υπάρχει απαίτηση να χρησιμοποιείτε μια σειρά για τη λίστα των πραγμάτων σας. Εάν αναζητάτε μόνο ένα μικρό αριθμό πραγμάτων, μπορείτε να χρησιμοποιήσετε μια λίστα σε μορφή πίνακα, η οποία ονομάζεται σταθερά πίνακα. Για παράδειγμα, εάν ψάχνετε απλώς τα χρώματα κόκκινο, μπλε και πράσινο, μπορείτε να χρησιμοποιήσετε ("κόκκινο", "μπλε", "πράσινο") ως εξής:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
