Γενική φόρμουλα
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Περίληψη
Για να αναζητήσετε μια τιμή με αντιστοίχιση σε πολλές στήλες, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα βάσει των MMULT, TRANSPOSE, COLUMN και INDEX. Στο παράδειγμα που εμφανίζεται, ο τύπος στο H4 είναι:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
όπου "ονόματα" είναι η ονομαζόμενη περιοχή C4: E7 και "ομάδες" είναι η ονομαζόμενη περιοχή B4: B7. Ο τύπος επιστρέφει την ομάδα στην οποία ανήκει κάθε όνομα.
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί με το control shift enter.
Εξήγηση
Λειτουργώντας από μέσα προς τα έξω, τα λογικά κριτήρια που χρησιμοποιούνται σε αυτόν τον τύπο είναι:
--(names=G4)
όπου τα ονόματα είναι το ονομασμένο εύρος C4: E7. Αυτό δημιουργεί ένα αποτέλεσμα TRUE / FALSE για κάθε τιμή στα δεδομένα και το διπλό αρνητικό σχηματίζει τις τιμές TRUE FALSE σε 1 και 0 για να δώσει έναν πίνακα όπως αυτό:
(0,0,0;1,0,0;0,0,0;0,0,0)
Αυτός ο πίνακας είναι 4 σειρές με 3 στήλες, που ταιριάζουν με τη δομή των "ονομάτων".
Δημιουργείται ένας δεύτερος πίνακας με αυτήν την έκφραση:
TRANSPOSE(COLUMN(names)^0))
Η συνάρτηση COLUMN χρησιμοποιείται για τη δημιουργία ενός αριθμητικού πίνακα με 3 στήλες και 1 σειρά και το TRANSPOSE μετατρέπει αυτόν τον πίνακα σε 1 στήλη και 3 σειρές. Η αύξηση στο μηδέν μετατρέπει απλώς όλους τους αριθμούς της συστοιχίας σε 1. Η συνάρτηση MMULT χρησιμοποιείται στη συνέχεια για την εκτέλεση πολλαπλασιασμού μήτρας:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
και το αποτέλεσμα πηγαίνει στη συνάρτηση MATCH ως πίνακας, με 1 ως τιμή αναζήτησης:
MATCH(1,(0;1;0;0),0)
Η συνάρτηση MATCH επιστρέφει τη θέση του πρώτου αγώνα, η οποία αντιστοιχεί στη σειρά των κριτηρίων που παρέχονται στην πρώτη σειρά αντιστοίχισης. Αυτό τροφοδοτείται στο INDEX ως τον αριθμό σειράς, με την ονομαζόμενη περιοχή "ομάδες" ως τον πίνακα:
=INDEX(groups,2)
Τέλος, το INDEX επιστρέφει το "Bear", στο οποίο ανήκει ο Adam.
Κυριολεκτικά περιέχει κριτήρια
Για να ελέγξετε για συγκεκριμένες τιμές κειμένου αντί για ακριβή αντιστοίχιση, μπορείτε να χρησιμοποιήσετε τις συναρτήσεις ISNUMBER και SEARCH μαζί. Για παράδειγμα, για να ταιριάξετε κελιά που περιέχουν "μήλο" μπορείτε να χρησιμοποιήσετε:
=ISNUMBER(SEARCH("apple",data))
Αυτός ο τύπος εξηγείται εδώ.
