Είναι εύκολο να αναζητήσετε αξία με ένα μοναδικό κλειδί σε έναν πίνακα. Μπορούμε απλά να χρησιμοποιήσουμε τη συνάρτηση VLOOKUP. Αλλά όταν δεν έχετε αυτήν τη μοναδική στήλη στα δεδομένα σας και πρέπει να αναζητήσετε σε πολλές στήλες για να αντιστοιχίσετε μια τιμή, το VLOOKUP δεν βοηθά.
Έτσι, για να αναζητήσουμε μια τιμή σε έναν πίνακα με πολλαπλά κριτήρια θα χρησιμοποιήσουμε τον τύπο INDEX-MATCH-INDEX.
Γενικός τύπος για αναζήτηση πολλαπλών κριτηρίων
= INDEX (περιοχή αναζήτησης, MATCH (1, INDEX ((κριτήρια1 = εύρος1)*(κριτήρια2 = εύρος2)*(κριτήριαΝ = εύρος Ν), 0,1), 0))
περιοχή_αναζήτησης: Είναι το εύρος από το οποίο θέλετε να ανακτήσετε την τιμή.
Κριτήρια 1, Κριτήρια 2, Κριτήρια Ν: Αυτά είναι τα κριτήρια που θέλετε να ταιριάζουν στο εύρος 1, εύρος 2 και εύρος Ν. Μπορείτε να έχετε έως και 270 κριτήρια - ζεύγη εύρους.
Range1, range2, rangeN: Αυτά είναι τα εύρη στα οποία θα πληροίτε τα αντίστοιχα κριτήριά σας.
Πώς θα λειτουργήσει; Ας δούμε…
INDEX και MATCH με Παράδειγμα πολλαπλών κριτηρίων
Εδώ έχω έναν πίνακα δεδομένων. Θέλω να τραβήξω το όνομα του πελάτη χρησιμοποιώντας την ημερομηνία κράτησης, το κτίριο και την περιοχή. Εδώ λοιπόν έχω τρία κριτήρια και ένα εύρος αναζήτησης.
Γράψτε αυτόν τον τύπο στο κελί I4 πατήστε enter.
= INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Πως δουλεύει:
Γνωρίζουμε ήδη πώς λειτουργούν οι λειτουργίες INDEX και MATCH στο EXCEL, οπότε δεν πρόκειται να το εξηγήσω εδώ. Θα μιλήσουμε για το κόλπο που χρησιμοποιήσαμε εδώ.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Το κύριο μέρος είναι αυτό. Κάθε μέρος αυτής της δήλωσης επιστρέφει μια σειρά αληθινών ψευδών.
Όταν πολλαπλασιάζονται οι boolean τιμές επιστρέφουν πίνακα 0 και 1. Ο πολλαπλασιασμός λειτουργεί ως τελεστής AND. Hense όταν όλες οι τιμές είναι αληθινές μόνο τότε επιστρέφει 1 άλλο 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Αυτό θα επιστρέψει εντελώς
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}* {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; ΛΑΘΟΣ ; ΛΑΘΟΣ; ΛΑΘΟΣ; ΛΑΘΟΣ; ΛΑΘΟΣ; ALΕΥΤΙΚΟ; ΑΛΗΘΙΝΟ}* {ΛΑΘΟΣ; ΛΑΘΟΣ; ALΕΥΤΙΚΟ; ΑΛΗΘΙΝΟ; ΛΑΘΟΣ; ALΕΥΤΙΚΟ
Που θα μεταφραστεί σε
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
ΔΕΙΚΤΗΣ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Η συνάρτηση INDEX θα επιστρέψει τον ίδιο πίνακα ({0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) στη συνάρτηση MATCH ως πίνακας αναζήτησης.
ΑΓΩΝΑΣ(1,ΔΕΙΚΤΗΣ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Η συνάρτηση MATCH θα αναζητήσει 1 στον πίνακα {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Και θα επιστρέψει τον αριθμό ευρετηρίου του πρώτου 1 που βρέθηκε στον πίνακα. Αυτό είναι 8 εδώ.
ΔΕΙΚΤΗΣ(Ε2: Ε16,ΑΓΩΝΑΣ(1,ΔΕΙΚΤΗΣ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Τέλος, το INDEX θα επιστρέψει την τιμή από το δεδομένο εύρος (E2: E16) στο δείκτης (8).
Απλός????. Δυστυχώς, δεν θα μπορούσα να το κάνω πιο απλό.
Λύση πίνακα
Εάν μπορείτε να πατήσετε CTRL + SHIFT + ENTER κατά συνέπεια, μπορείτε να εξαλείψετε την εσωτερική συνάρτηση INDEX. Απλώς γράψτε αυτόν τον τύπο και πατήστε CTRL + SHIFT + ENTER.
= INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Γενικός τύπος πίνακα για αναζήτηση πολλαπλών κριτηρίων
= INDEX (εύρος αναζήτησης, MATCH (1, (κριτήρια1 = εύρος1)*(κριτήρια2 = εύρος2)*(κριτήριαΝ = εύρος Ν), 0))
Ο τύπος λειτουργεί όπως και στην παραπάνω εξήγηση.
Προσπάθησα να το εξηγήσω όσο πιο απλά γίνεται. Αλλά αν δεν ήμουν αρκετά σαφής, ενημερώστε με στην παρακάτω ενότητα σχολίων. Παρεμπιπτόντως, δεν χρειάζεται να γνωρίζετε πώς λειτουργεί ο κινητήρας για να οδηγείτε ένα αυτοκίνητο. Απλά πρέπει να ξέρεις πώς να το οδηγείς. Και το ξέρεις πολύ καλά.
Πώς να αναζητήσετε τις κορυφαίες 5 τιμές με διπλές τιμές χρησιμοποιώντας το INDEX-MATCH στο Excel
Πώς να κάνετε VLOOKUP πολλαπλές τιμές στο Excel
Πώς να κάνετε VLOOKUP με το Dynamic Col Index στο Excel
Πώς να χρησιμοποιήσετε το VLOOKUP από δύο ή περισσότερους πίνακες αναζήτησης στο Excel
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας
Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel