Συνδυασμοί INDEX & MATCH στο Excel (10 εύκολα παραδείγματα)

Το Excel έχει πολλές λειτουργίες - περίπου 450+ από αυτές.

Και πολλά από αυτά είναι απλά φοβερά. Η ποσότητα της εργασίας που μπορείτε να ολοκληρώσετε με μερικούς τύπους εξακολουθεί να με εκπλήσσει (ακόμη και μετά τη χρήση του Excel για 10+ χρόνια).

Και ανάμεσα σε όλες αυτές τις καταπληκτικές λειτουργίες, ξεχωρίζει ο συνδυασμός λειτουργιών INDEX MATCH.

Είμαι τεράστιος οπαδός του συνδυασμού INDEX MATCH και το έχω ξεκαθαρίσει πολλές φορές.

Έγραψα ακόμη και ένα άρθρο για το Index Match Vs VLOOKUP το οποίο προκάλεσε λίγη συζήτηση (μπορείτε να ελέγξετε την ενότητα σχολίων για κάποιο πυροτέχνημα).

Και σήμερα, γράφω αυτό το άρθρο που επικεντρώνεται αποκλειστικά στο Index Match για να σας δείξω μερικά απλά και προηγμένα σενάρια όπου μπορείτε να χρησιμοποιήσετε αυτόν τον ισχυρό συνδυασμό φόρμουλας και να ολοκληρώσετε τη δουλειά.

Σημείωση: Υπάρχουν άλλοι τύποι αναζήτησης στο Excel - όπως VLOOKUP και HLOOKUP και αυτοί είναι υπέροχοι. Πολλοί άνθρωποι βρίσκουν το VLOOKUP πιο εύκολο στη χρήση (και αυτό ισχύει επίσης). Πιστεύω ότι το INDEX MATCH είναι μια καλύτερη επιλογή σε πολλές περιπτώσεις. Αλλά επειδή οι άνθρωποι το βρίσκουν δύσκολο, συνηθίζει λιγότερο. Προσπαθώ λοιπόν να το απλοποιήσω χρησιμοποιώντας αυτό το σεμινάριο.

Τώρα, πριν σας δείξω πώς ο συνδυασμός του INDEX MATCH αλλάζει τον κόσμο των αναλυτών και των επιστημόνων δεδομένων, επιτρέψτε μου πρώτα να σας παρουσιάσω τα μεμονωμένα μέρη - τις συναρτήσεις INDEX και MATCH.

Συνάρτηση INDEX: Βρίσκει την τιμή βάσει συντεταγμένων

Ο ευκολότερος τρόπος για να καταλάβετε πώς λειτουργεί η λειτουργία ευρετηρίου είναι να το σκεφτείτε ως δορυφόρο GPS.

Μόλις πείτε στον δορυφόρο τις συντεταγμένες γεωγραφικού πλάτους και γεωγραφικού μήκους, θα γνωρίζει ακριβώς πού να πάει και θα βρει αυτήν τη θέση.

Έτσι, παρά το γεγονός ότι είχε έναν εκπληκτικό αριθμό συνδυασμών με μεγάλη διάρκεια, ο δορυφόρος θα ήξερε ακριβώς πού να ψάξει.

Έψαξα γρήγορα για την τοποθεσία εργασίας μου και αυτό είναι που πήρα.

Τέλος πάντων, αρκετά από τη γεωγραφία.

Ακριβώς όπως ένας δορυφόρος χρειάζεται συντεταγμένες γεωγραφικού πλάτους και γεωγραφικού μήκους, η συνάρτηση INDEX στο Excel θα χρειαστεί τον αριθμό γραμμής και στήλης για να γνωρίζει σε ποια κυψέλη αναφέρεστε.

Και αυτή είναι η συνάρτηση Excel INDEX σε κέλυφος καρυδιού.

Επιτρέψτε μου λοιπόν να το ορίσω με απλά λόγια για εσάς.

Η συνάρτηση INDEX θα χρησιμοποιήσει τον αριθμό σειράς και τον αριθμό στήλης για να βρει ένα κελί στο δεδομένο εύρος και να επιστρέψει την τιμή σε αυτό.

Από μόνο του, το INDEX είναι μια πολύ απλή λειτουργία, χωρίς χρησιμότητα. Άλλωστε, στις περισσότερες περιπτώσεις, δεν είναι πιθανό να γνωρίζετε τους αριθμούς γραμμών και στηλών.

Αλλά…

Το γεγονός ότι μπορείτε να το χρησιμοποιήσετε με άλλες λειτουργίες (υπόδειξη: MATCH) που μπορούν να βρουν τον αριθμό γραμμής και τον αριθμό στήλης καθιστά το INDEX μια εξαιρετικά ισχυρή συνάρτηση Excel.

Παρακάτω είναι η σύνταξη της συνάρτησης INDEX:

= INDEX (array, row_num, [col_num]) = INDEX (array, row_num, [col_num], [area_num])

• πίνακας - ένα εύρος κελιών ή σταθερά πίνακα.
• row_num - τον αριθμό σειράς από τον οποίο πρέπει να ανακτηθεί η τιμή.
• [col_num] - τον αριθμό στήλης από την οποία πρέπει να ανακτηθεί η τιμή. Αν και αυτό είναι ένα προαιρετικό όρισμα, αλλά εάν δεν παρέχεται το row_num, πρέπει να δοθεί.
• [area_num] - (Προαιρετικό) Εάν το όρισμα πίνακα αποτελείται από πολλά εύρη, αυτός ο αριθμός θα χρησιμοποιηθεί για την επιλογή της αναφοράς από όλα τα εύρη.

Η συνάρτηση INDEX έχει 2 συντάξεις (μόνο FYI).

Το πρώτο χρησιμοποιείται στις περισσότερες περιπτώσεις. Το δεύτερο χρησιμοποιείται μόνο σε προηγμένες περιπτώσεις (όπως η αναζήτηση τριών κατευθύνσεων), τα οποία θα καλύψουμε σε ένα από τα παραδείγματα αργότερα σε αυτό το σεμινάριο.

Αλλά αν είστε νέοι σε αυτήν τη λειτουργία, απλώς θυμηθείτε την πρώτη σύνταξη.

Παρακάτω είναι ένα βίντεο που εξηγεί πώς να χρησιμοποιήσετε τη λειτουργία INDEX

Λειτουργία MATCH: Βρίσκει τη θέση που έχει οριστεί σε μια τιμή αναζήτησης

Επιστρέφοντας στο προηγούμενο παράδειγμα γεωγραφικού μήκους και γεωγραφικού πλάτους, το MATCH είναι η συνάρτηση που μπορεί να βρει αυτές τις θέσεις (στον κόσμο του υπολογιστικού φύλλου Excel).

Σε απλή γλώσσα, η συνάρτηση Excel MATCH μπορεί να βρει τη θέση ενός κελιού σε ένα εύρος.

Και σε ποια βάση θα έβρισκε τη θέση ενός κυττάρου;

Με βάση την τιμή αναζήτησης.

Για παράδειγμα, εάν έχετε μια λίστα όπως φαίνεται παρακάτω και θέλετε να βρείτε τη θέση του ονόματος ‘Mark’ σε αυτήν, τότε μπορείτε να χρησιμοποιήσετε τη συνάρτηση MATCH.

Η συνάρτηση επιστρέφει 3, καθώς αυτή είναι η θέση του κελιού με το όνομα Mark σε αυτό.

Η συνάρτηση MATCH αρχίζει να αναζητά από πάνω προς τα κάτω την τιμή αναζήτησης (η οποία είναι «Σήμανση») στο καθορισμένο εύρος (που είναι A1: A9 σε αυτό το παράδειγμα). Μόλις βρει το όνομα, επιστρέφει τη θέση στο συγκεκριμένο εύρος.

Παρακάτω είναι η σύνταξη της συνάρτησης MATCH στο Excel.

= MATCH (τιμή_αναζήτησης, αναζήτηση_αρρέ, [τύπος_συμφωνίας])

• αξία_αναζήτησης - Η τιμή για την οποία αναζητάτε μια αντιστοίχιση στο lookup_array.
• lookup_array - Το εύρος των κελιών στα οποία αναζητάτε την τιμή αναζήτησης.
• [match_type] - (Προαιρετικό) Αυτό καθορίζει τον τρόπο με τον οποίο το excel πρέπει να αναζητά μια τιμή που ταιριάζει. Μπορεί να πάρει τρεις τιμές -1, 0 ή 1.

Κατανόηση του ορίσματος τύπου αντιστοίχισης στη συνάρτηση MATCH

Υπάρχει ένα επιπλέον πράγμα που πρέπει να γνωρίζετε για τη συνάρτηση MATCH και είναι για το πώς περνάει μέσα από τα δεδομένα και βρίσκει τη θέση του κελιού.

Το τρίτο όρισμα της συνάρτησης MATCH μπορεί να είναι 0, 1 ή -1.

Παρακάτω είναι μια εξήγηση για το πώς λειτουργούν αυτά τα επιχειρήματα:

• 0 - αυτό θα αναζητήσει μια ακριβή αντιστοίχιση της τιμής. Εάν βρεθεί ακριβής αντιστοίχιση, η συνάρτηση MATCH θα επιστρέψει τη θέση του κελιού. Διαφορετικά, θα επιστρέψει ένα σφάλμα.
• 1 - αυτό βρίσκει τη μεγαλύτερη τιμή που είναι μικρότερη ή ίση με την τιμή αναζήτησης. Για να λειτουργήσει αυτό, το εύρος δεδομένων σας πρέπει να ταξινομηθεί με αύξουσα σειρά.
• -1 - αυτό βρίσκει τη μικρότερη τιμή που είναι μεγαλύτερη ή ίση με την τιμή αναζήτησης. Για να λειτουργήσει αυτό, το εύρος δεδομένων σας πρέπει να ταξινομηθεί κατά φθίνουσα σειρά.

Παρακάτω είναι ένα βίντεο που εξηγεί τον τρόπο χρήσης της συνάρτησης MATCH (μαζί με το όρισμα τύπου αντιστοίχισης)

Για να συνοψίσουμε και να το πούμε με απλά λόγια:

• Το INDEX χρειάζεται τη θέση κελιού (αριθμός σειράς και στήλης) και δίνει την τιμή κελιού.
• Το MATCH βρίσκει τη θέση χρησιμοποιώντας μια τιμή αναζήτησης.

Ας τα συνδυάσουμε για να δημιουργήσουμε ένα Powerhouse (INDEX + MATCH)

Τώρα που έχετε μια βασική κατανόηση του τρόπου με τον οποίο λειτουργούν οι λειτουργίες INDEX και MATCH, ας συνδυάσουμε αυτά τα δύο και θα μάθουμε για όλα τα υπέροχα πράγματα που μπορεί να κάνει.

Για να το καταλάβω καλύτερα, έχω μερικά παραδείγματα που χρησιμοποιούν τον συνδυασμό INDEX MATCH.

Θα ξεκινήσω με ένα απλό παράδειγμα και στη συνέχεια θα σας δείξω επίσης μερικές προχωρημένες περιπτώσεις χρήσης.

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Παράδειγμα 1: Απλή αναζήτηση χρησιμοποιώντας συνδυασμό INDEX MATCH

Ας κάνουμε μια απλή αναζήτηση με INDEX/MATCH.

Παρακάτω είναι ένας πίνακας όπου έχω τις βαθμολογίες για δέκα μαθητές.

Από αυτόν τον πίνακα, θέλω να βρω τα σημάδια για τον Jim.

Παρακάτω είναι ο τύπος που μπορεί εύκολα να το κάνει αυτό:

= INDEX ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)

Τώρα, αν νομίζετε ότι αυτό μπορεί εύκολα να γίνει χρησιμοποιώντας μια λειτουργία VLOOKUP, έχετε δίκιο! Αυτή δεν είναι η καλύτερη χρήση της εκπληκτικότητας INDEX MATCH. Παρά το γεγονός ότι είμαι οπαδός του INDEX MATCH, είναι λίγο πιο δύσκολο από το VLOOKUP. Εάν η ανάκτηση δεδομένων από μια στήλη στα δεξιά είναι το μόνο που θέλετε να κάνετε, σας συνιστώ να χρησιμοποιήσετε το VLOOKUP.

Ο λόγος που έδειξα αυτό το παράδειγμα, το οποίο μπορεί επίσης εύκολα να γίνει με το VLOOKUP είναι να σας δείξω πώς λειτουργεί το INDEX MATCH σε μια απλή ρύθμιση.

Τώρα επιτρέψτε μου να δείξω ένα όφελος του INDEX MATCH.

Ας υποθέσουμε ότι έχετε τα ίδια δεδομένα, αλλά αντί να τα έχετε σε στήλες, τα έχετε σε σειρές (όπως φαίνεται παρακάτω).

Ξέρετε τι, μπορείτε ακόμα να χρησιμοποιήσετε το συνδυασμό INDEX MATCH για να λάβετε τα σήματα του Jim.

Παρακάτω είναι ο τύπος που θα σας δώσει το αποτέλεσμα:

= INDEX ($ B $ 1: $ K $ 2,2, MATCH ("Jim", $ B $ 1: $ K $ 1,0))

Σημειώστε ότι πρέπει να αλλάξετε το εύρος και να αλλάξετε τα τμήματα γραμμής/στήλης για να λειτουργήσει αυτός ο τύπος και για οριζόντια δεδομένα.

Αυτό δεν μπορεί να γίνει με το VLOOKUP, αλλά μπορείτε ακόμα να το κάνετε εύκολα με το HLOOKUP.

Ο συνδυασμός INDEX MATCH μπορεί εύκολα να χειριστεί οριζόντια αλλά και κάθετα δεδομένα.

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Παράδειγμα 2: Αναζήτηση προς τα αριστερά

Είναι πιο συνηθισμένο από ό, τι νομίζετε.

Πολλές φορές, μπορεί να σας ζητηθεί να ανακτήσετε τα δεδομένα από μια στήλη που βρίσκεται στα αριστερά της στήλης που έχει την τιμή αναζήτησης.

Κάτι όπως φαίνεται παρακάτω:

Για να μάθετε τις πωλήσεις του Michael, θα πρέπει να κάνετε μια αναζήτηση στα αριστερά.

Αν σκέφτεστε το VLOOKUP, επιτρέψτε μου να σταματήσω εκεί.

Το VLOOKUP δεν είναι φτιαγμένο για να αναζητά και να παίρνει τις τιμές στα αριστερά.

Μπορείτε ακόμα να το κάνετε χρησιμοποιώντας το VLOOKUP;

Ναι μπορείς!

Αλλά αυτό μπορεί να μετατραπεί σε μια μακρά και άσχημη φόρμουλα.

Έτσι, εάν θέλετε να κάνετε μια αναζήτηση και να λάβετε δεδομένα από τις στήλες στα αριστερά, καλύτερα να χρησιμοποιήσετε το συνδυασμό INDEX MATCH.

Παρακάτω είναι ο τύπος που θα λάβει τον αριθμό πωλήσεων του Michael:

= INDEX ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)

Ένα άλλο σημείο εδώ για INDEX MATCH. Το VLOOKUP μπορεί να ανακτήσει τα δεδομένα μόνο από τις στήλες που βρίσκονται στα δεξιά της στήλης που έχουν την τιμή αναζήτησης.

Παράδειγμα 3: Αμφίδρομη αναζήτηση

Μέχρι στιγμής, έχουμε δει τα παραδείγματα όπου θέλαμε να φέρουμε τα δεδομένα από τη στήλη δίπλα στη στήλη που έχει την τιμή αναζήτησης.

Αλλά στην πραγματική ζωή, τα δεδομένα εκτείνονται συχνά σε πολλαπλές στήλες.

Το INDEX MATCH μπορεί εύκολα να χειριστεί μια αμφίδρομη αναζήτηση.

Παρακάτω είναι ένα σύνολο δεδομένων των βαθμών του μαθητή σε τρία διαφορετικά θέματα.

Εάν θέλετε να αποκτήσετε γρήγορα τα σήματα ενός μαθητή και στα τρία μαθήματα, μπορείτε να το κάνετε με το INDEX MATCH.

Ο παρακάτω τύπος θα σας δώσει τα σημεία για τον Jim και για τα τρία θέματα (αντιγράψτε και επικολλήστε σε ένα κελί και σύρετε για να γεμίσετε άλλα κελιά ή αντιγράψτε και επικολλήστε σε άλλα κελιά).

= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))

Επιτρέψτε μου να εξηγήσω επίσης γρήγορα αυτόν τον τύπο.

Ο τύπος INDEX χρησιμοποιεί το εύρος B2: D11.

Το πρώτο MATCH χρησιμοποιεί το όνομα (Jim στο κελί F3) και παίρνει τη θέση του στη στήλη με τα ονόματα (A2: A11). Αυτός γίνεται ο αριθμός σειράς από τον οποίο πρέπει να ληφθούν τα δεδομένα.

Ο δεύτερος τύπος MATCH χρησιμοποιεί το όνομα του θέματος (στο κελί G2) για να πάρει τη θέση του συγκεκριμένου ονόματος θέματος στο B1: D1. Για παράδειγμα, τα Μαθηματικά είναι 1, η Φυσική είναι 2 και η Χημεία είναι 3.

Δεδομένου ότι αυτές οι θέσεις MATCH εισάγονται στη συνάρτηση INDEX, επιστρέφει τη βαθμολογία με βάση το όνομα του μαθητή και το όνομα του θέματος.

Αυτός ο τύπος είναι δυναμικός, πράγμα που σημαίνει ότι αν αλλάξετε το όνομα του μαθητή ή τα ονόματα των θεμάτων, θα εξακολουθήσει να λειτουργεί και να λαμβάνει τα σωστά δεδομένα.

Ένα μεγάλο πράγμα με τη χρήση INDEX/MATCH είναι ότι ακόμη και αν αλλάξετε τα ονόματα των θεμάτων, θα συνεχίσει να σας δίνει το σωστό αποτέλεσμα.

Παράδειγμα 4: Τιμή αναζήτησης από πολλαπλή στήλη/κριτήρια

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να φέρετε τα σημάδια για το «Mark Long».

Δεδομένου ότι τα δεδομένα βρίσκονται σε δύο στήλες, δεν μπορώ να αναζητήσω το Mark και να λάβω τα δεδομένα.

Αν το κάνω με αυτόν τον τρόπο, θα λάβω τα δεδομένα των σημείων για τον Mark Frost και όχι για το Mark Long (επειδή η συνάρτηση MATCH θα μου δώσει το αποτέλεσμα για το MARK που συναντά).

Ένας τρόπος για να γίνει αυτό είναι να δημιουργήσετε μια στήλη βοηθού και να συνδυάσετε τα ονόματα. Μόλις έχετε τη στήλη βοηθού, μπορείτε να χρησιμοποιήσετε το VLOOKUP και να λάβετε τα δεδομένα των σημείων.

Εάν ενδιαφέρεστε να μάθετε πώς να το κάνετε αυτό, διαβάστε αυτό το σεμινάριο σχετικά με τη χρήση του VLOOKUP με πολλά κριτήρια.

Αλλά με τον συνδυασμό INDEX/MATCH, δεν χρειάζεστε στήλη βοηθού. Μπορείτε να δημιουργήσετε έναν τύπο που χειρίζεται πολλά κριτήρια στον ίδιο τον τύπο.

Ο παρακάτω τύπος θα δώσει το αποτέλεσμα.

= INDEX ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

Επιτρέψτε μου να εξηγήσω γρήγορα τι κάνει αυτός ο τύπος.

Το τμήμα MATCH του τύπου συνδυάζει την τιμή αναζήτησης (Mark and Long) καθώς και ολόκληρο τον πίνακα αναζήτησης. Όταν $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 χρησιμοποιείται ως πίνακας αναζήτησης, ελέγχει στην πραγματικότητα την τιμή αναζήτησης με τη συνδυασμένη συμβολοσειρά ονόματος και επώνυμου (χωρίζεται με το σύμβολο σωλήνα).

Αυτό διασφαλίζει ότι έχετε το σωστό αποτέλεσμα χωρίς να χρησιμοποιήσετε στήλες βοηθού.

Μπορείτε να κάνετε αυτό το είδος αναζήτησης (όπου υπάρχουν πολλές στήλες/κριτήρια) και με το VLOOKUP, αλλά πρέπει να χρησιμοποιήσετε μια στήλη βοηθού. Ο συνδυασμός INDEX MATCH διευκολύνει ελαφρώς αυτό χωρίς στήλες βοηθού.

Παράδειγμα 5: Λάβετε τιμές από ολόκληρη σειρά/στήλη

Στα παραπάνω παραδείγματα, χρησιμοποιήσαμε τη συνάρτηση INDEX για να λάβουμε τιμή από ένα συγκεκριμένο κελί. Παρέχετε τον αριθμό σειράς και στήλης και επιστρέφει την τιμή στο συγκεκριμένο κελί.

Αλλά μπορείτε να κάνετε περισσότερα.

Μπορείτε επίσης να χρησιμοποιήσετε τη συνάρτηση INDEX για να λάβετε τις τιμές από μια ολόκληρη σειρά ή στήλη.

Και πώς μπορεί να είναι χρήσιμο αυτό ρωτάτε!

Ας υποθέσουμε ότι θέλετε να μάθετε τη συνολική βαθμολογία του Jim και στα τρία θέματα.

Μπορείτε να χρησιμοποιήσετε τη συνάρτηση INDEX για να λάβετε πρώτα όλα τα σήματα του Jim και στη συνέχεια να χρησιμοποιήσετε τη συνάρτηση SUM για να λάβετε ένα σύνολο.

Ας δούμε πώς να το κάνουμε αυτό.

Παρακάτω έχω τις βαθμολογίες όλων των μαθητών σε τρία μαθήματα.

Ο παρακάτω τύπος θα μου δώσει τη συνολική βαθμολογία του Jim και στα τρία θέματα.

= SUM (INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

Επιτρέψτε μου να εξηγήσω πώς λειτουργεί αυτός ο τύπος.

Το κόλπο εδώ είναι να χρησιμοποιήσετε το 0 ως αριθμό στήλης.

Όταν χρησιμοποιείτε το 0 ως αριθμό στήλης στη συνάρτηση INDEX, θα επιστρέψει όλες τις τιμές γραμμής. Ομοίως, εάν χρησιμοποιείτε το 0 ως αριθμό σειράς, θα επιστρέψει όλες τις τιμές στη στήλη.

Έτσι, το παρακάτω μέρος του τύπου επιστρέφει έναν πίνακα τιμών - {97, 70, 73}

INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Εάν εισαγάγετε αυτόν τον παραπάνω τύπο σε ένα κελί στο Excel και πατήσετε enter, θα δείτε μια #ΑΞΙΑ! λάθος. Αυτό συμβαίνει επειδή δεν επιστρέφει μια μόνο τιμή, αλλά μια σειρά αξίας.

Αλλά μην ανησυχείτε, η σειρά αξιών είναι ακόμα εκεί. Μπορείτε να το ελέγξετε επιλέγοντας τον τύπο και πατήστε το πλήκτρο F9. Θα σας δείξει το αποτέλεσμα του τύπου που σε αυτήν την περίπτωση είναι ένας πίνακας τριών τιμών - {97, 70, 73}

Τώρα, εάν τυλίξετε αυτόν τον τύπο INDEX στη συνάρτηση SUM, θα σας δώσει το άθροισμα όλων των βαθμών που σημείωσε ο Jim.

Μπορείτε επίσης να χρησιμοποιήσετε το ίδιο για να λάβετε τις υψηλότερες, χαμηλότερες και μέσες βαθμολογίες του Jim.

Ακριβώς όπως το κάναμε αυτό για έναν μαθητή, μπορείτε επίσης να το κάνετε για ένα θέμα. Για παράδειγμα, εάν θέλετε τη μέση βαθμολογία σε ένα θέμα, μπορείτε να διατηρήσετε τον αριθμό γραμμής ως 0 στον τύπο INDEX και θα σας δώσει όλες τις τιμές στηλών αυτού του θέματος.

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Παράδειγμα 6: Βρείτε το βαθμό του μαθητή (Τεχνική κατά προσέγγιση αντιστοίχισης)

Μέχρι στιγμής, έχουμε χρησιμοποιήσει τον τύπο MATCH για να λάβουμε την ακριβή αντιστοίχιση της τιμής αναζήτησης.

Αλλά μπορείτε επίσης να το χρησιμοποιήσετε για να κάνετε έναν κατά προσέγγιση αγώνα.

Τώρα, τι στο διάολο είναι το Approximate Match;

ΑΣΕ με να εξηγήσω.

Όταν ψάχνετε για πράγματα όπως ονόματα ή αναγνωριστικά, αναζητάτε μια ακριβή αντιστοίχιση. Αλλά μερικές φορές, πρέπει να γνωρίζετε το εύρος στο οποίο βρίσκονται οι τιμές αναζήτησης. Αυτό συμβαίνει συνήθως με τους αριθμούς.

Για παράδειγμα, ως καθηγητής τάξης, μπορεί να θέλετε να μάθετε ποιος είναι ο βαθμός κάθε μαθητή σε ένα μάθημα και ο βαθμός αποφασίζεται με βάση τη βαθμολογία.

Παρακάτω είναι ένα παράδειγμα, όπου θέλω τον βαθμό για όλους τους μαθητές και η βαθμολογία αποφασίζεται με βάση τον πίνακα στα δεξιά.

Έτσι, εάν ένας μαθητής πάρει λιγότερους από 33, ο βαθμός είναι F και αν αυτός/αυτή παίρνει λιγότερο από 50 αλλά περισσότερο από 33, είναι Ε, και ούτω καθεξής.

Παρακάτω είναι ο τύπος που θα το κάνει αυτό.

= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)

Επιτρέψτε μου να εξηγήσω πώς λειτουργεί αυτός ο τύπος.

Στη συνάρτηση MATCH, χρησιμοποιήσαμε το 1 ως όρισμα [match_type]. Αυτό το όρισμα θα επιστρέψει τη μεγαλύτερη τιμή που είναι μικρότερη ή ίση με την τιμή αναζήτησης.

Αυτό σημαίνει ότι ο τύπος MATCH περνάει από το εύρος σημείων και μόλις βρει ένα εύρος σημείων ίσο ή μικρότερο από την τιμή των σημείων αναζήτησης, θα σταματήσει εκεί και θα επιστρέψει τη θέση του.

Έτσι, αν η τιμή του σήματος αναζήτησης είναι 20, η συνάρτηση MATCH θα επιστρέψει 1 και αν είναι 85, θα επιστρέψει 5.

Και η συνάρτηση INDEX χρησιμοποιεί αυτήν την τιμή θέσης για να πάρει τον βαθμό.

ΣΗΜΑΝΤΙΚΟ: Για να λειτουργήσει αυτό, τα δεδομένα σας πρέπει να ταξινομηθούν με αύξουσα σειρά. Εάν δεν είναι, μπορείτε να έχετε λάθος αποτελέσματα.

Σημειώστε ότι τα παραπάνω μπορούν επίσης να γίνουν χρησιμοποιώντας τον παρακάτω τύπο VLOOKUP:

= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, TRUE)

Αλλά η συνάρτηση MATCH μπορεί να προχωρήσει ένα βήμα παραπέρα όταν πρόκειται για αντιστοίχιση κατά προσέγγιση.

Μπορείτε επίσης να έχετε φθίνουσα δεδομένα και να χρησιμοποιήσετε το συνδυασμό INDEX MATCH για να βρείτε το αποτέλεσμα. Για παράδειγμα, αν αλλάξω τη σειρά του πίνακα βαθμολογίας (όπως φαίνεται παρακάτω), μπορώ ακόμα να βρω τους βαθμούς των μαθητών.

Για να γίνει αυτό, το μόνο που έχω να κάνω είναι να αλλάξω το όρισμα [match_type] σε -1.

Παρακάτω είναι ο τύπος που έχω χρησιμοποιήσει:

= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)

Το VLOOKUP μπορεί επίσης να κάνει μια κατά προσέγγιση αντιστοίχιση, αλλά μόνο όταν τα δεδομένα είναι ταξινομημένα με αύξουσα σειρά (αλλά δεν λειτουργεί εάν τα δεδομένα έχουν ταξινομηθεί με φθίνουσα σειρά).

Παράδειγμα 7: Αναζητήσεις ευαίσθητων περιπτώσεων

Μέχρι τώρα όλες οι αναζητήσεις που κάναμε ήταν αδιάκριτες.

Αυτό σημαίνει ότι αν η τιμή αναζήτησης ήταν Jim ή JIM ή jim, δεν είχε σημασία. Θα έχετε το ίδιο αποτέλεσμα.

Αλλά τι γίνεται αν θέλετε η αναζήτηση να είναι κεφαλαία.

Αυτό συμβαίνει συνήθως όταν έχετε μεγάλα σύνολα δεδομένων και δυνατότητα επανάληψης ή διακριτά ονόματα/αναγνωριστικά (με μόνη διαφορά την περίπτωση)

Για παράδειγμα, ας υποθέσουμε ότι έχω το ακόλουθο σύνολο δεδομένων μαθητών όπου υπάρχουν δύο μαθητές με το όνομα Jim (η μόνη διαφορά είναι ότι ο ένας εισάγεται ως Jim και ένας άλλος ως jim).

Σημειώστε ότι υπάρχουν δύο μαθητές με το ίδιο όνομα - Jim (κελιά Α2 και Α5).

Δεδομένου ότι μια κανονική αναζήτηση δεν θα λειτουργήσει, πρέπει να κάνετε μια αναζήτηση με διάκριση πεζών -κεφαλαίων.

Παρακάτω είναι ο τύπος που θα σας δώσει το σωστό αποτέλεσμα. Δεδομένου ότι αυτός είναι ένας τύπος πίνακα, πρέπει να χρησιμοποιήσετε Control + Shift + Enter.

= INDEX ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)

Επιτρέψτε μου να εξηγήσω πώς λειτουργεί αυτός ο τύπος.

Η συνάρτηση EXACT ελέγχει για μια ακριβή αντιστοίχιση της τιμής αναζήτησης (η οποία είναι "jim" σε αυτή την περίπτωση). Περνάει από όλα τα ονόματα και επιστρέφει ΛΑΘΟΣ αν δεν είναι αντιστοίχιση και ΑΛΗΘΕΙΑ αν είναι αγώνας.

Έτσι, η έξοδος της συνάρτησης ΑΚΡΙΒΗ σε αυτό το παράδειγμα είναι - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Σημειώστε ότι υπάρχει μόνο μία ΑΛΗΘΙΝΗ, δηλαδή όταν η συνάρτηση ΑΚΡΙΒΗ βρήκε το τέλειο ταίριασμα.

Στη συνέχεια, η συνάρτηση MATCH βρίσκει τη θέση TRUE στον πίνακα που επιστρέφεται από τη συνάρτηση EXACT, η οποία είναι 4 σε αυτό το παράδειγμα.

Μόλις έχουμε τη θέση, η συνάρτηση INDEX τη χρησιμοποιεί για να βρει τα σημάδια.

Παράδειγμα 8: Βρείτε το πλησιέστερο ταίριασμα

Ας προχωρήσουμε λίγο τώρα.

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπου θέλετε να βρείτε το άτομο που έχει την εργασιακή εμπειρία πιο κοντά στην απαιτούμενη εμπειρία (αναφέρεται στο κελί Δ2).

Ενώ οι τύποι αναζήτησης δεν έχουν σχεδιαστεί για να το κάνουν αυτό, μπορείτε να το συνδυάσετε με άλλες λειτουργίες (όπως MIN και ABS) για να το κάνετε αυτό.

Παρακάτω είναι ο τύπος που θα βρει το άτομο με την εμπειρία πλησιέστερα στην απαιτούμενη και θα επιστρέψει το όνομα του ατόμου. Σημειώστε ότι η εμπειρία πρέπει να είναι η πλησιέστερη (η οποία μπορεί να είναι είτε μικρότερη είτε μεγαλύτερη).

= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Δεδομένου ότι αυτός είναι ένας τύπος πίνακα, πρέπει να χρησιμοποιήσετε Control + Shift + Enter.

Το κόλπο σε αυτόν τον τύπο είναι να αλλάξετε την τιμή αναζήτησης και τον πίνακα αναζήτησης για να βρείτε την ελάχιστη διαφορά εμπειρίας στις απαιτούμενες και πραγματικές τιμές.

Πριν εξηγήσω τον τύπο, ας καταλάβουμε πώς θα το κάνατε χειροκίνητα.

Θα περάσετε από κάθε κελί στη στήλη Β και θα βρείτε τη διαφορά στην εμπειρία ανάμεσα σε αυτό που απαιτείται και σε αυτό που έχει ένα άτομο. Μόλις έχετε όλες τις διαφορές, θα βρείτε αυτό που είναι το ελάχιστο και θα βρείτε το όνομα αυτού του ατόμου.

Αυτό ακριβώς κάνουμε με αυτόν τον τύπο.

ΑΣΕ με να εξηγήσω.

Η τιμή αναζήτησης στον τύπο MATCH είναι MIN (ABS (D2-B2: B15)).

Αυτό το μέρος σας δίνει την ελάχιστη διαφορά μεταξύ της δεδομένης εμπειρίας (η οποία είναι 2,5 χρόνια) και όλων των άλλων εμπειριών. Σε αυτό το παράδειγμα, επιστρέφει 0,3

Σημειώστε ότι έχω χρησιμοποιήσει ABS για να βεβαιωθώ ότι ψάχνω το πλησιέστερο (το οποίο μπορεί να είναι περισσότερο ή λιγότερο από τη δεδομένη εμπειρία).

Τώρα, αυτή η ελάχιστη τιμή γίνεται η τιμή αναζήτησης.

Ο πίνακας αναζήτησης στη συνάρτηση MATCH είναι ABS (D2- $ B $ 2: $ B $ 15).

Αυτό μας δίνει μια σειρά αριθμών από τους οποίους έχει αφαιρεθεί το 2,5 (η απαιτούμενη εμπειρία).

Έτσι, τώρα έχουμε μια τιμή αναζήτησης (0.3) και έναν πίνακα αναζήτησης ({6.8; 0.8; 19.5; 21.8; 14.5; 11.2; 0.3; 9.2; 2; 9.8; 14.8; 0.4; 23.8; 2.9})

Η συνάρτηση MATCH βρίσκει τη θέση 0,3 σε αυτόν τον πίνακα, η οποία είναι επίσης η θέση του ονόματος του ατόμου που έχει την πλησιέστερη εμπειρία.

Αυτός ο αριθμός θέσης χρησιμοποιείται στη συνέχεια από τη λειτουργία INDEX για να επιστρέψει το όνομα του ατόμου.

Σχετικά Διαβάστε: Βρείτε την πιο κοντινή αντιστοίχιση στο Excel (παραδείγματα χρησιμοποιώντας τύπους αναζήτησης)

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Παράδειγμα 9: Χρησιμοποιήστε INDEX MATCH με χαρακτήρες μπαλαντέρ

Εάν θέλετε να αναζητήσετε μια τιμή όταν υπάρχει μερική αντιστοίχιση, τότε πρέπει να χρησιμοποιήσετε χαρακτήρες μπαλαντέρ.

Για παράδειγμα, παρακάτω είναι ένα σύνολο δεδομένων με το όνομα της εταιρείας και τις κεφαλαιοποιήσεις της αγοράς τους και θέλετε να αποκτήσετε το ανώτατο όριο αγοράς. στοιχεία για τις τρεις εταιρείες στα δεξιά.

Δεδομένου ότι δεν πρόκειται για ακριβείς αντιστοιχίσεις, δεν μπορείτε να κάνετε τακτική αναζήτηση σε αυτήν την περίπτωση.

Ωστόσο, μπορείτε ακόμα να λάβετε τα σωστά δεδομένα χρησιμοποιώντας έναν αστερίσκο (*), ο οποίος είναι χαρακτήρας μπαλαντέρ.

Παρακάτω είναι ο τύπος που θα σας δώσει τα δεδομένα, αντιστοιχίζοντας τα ονόματα των εταιρειών από την κύρια στήλη και μεταφέροντας το όριο της τιμής αγοράς για αυτό.

= INDEX ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Επιτρέψτε μου να εξηγήσω πώς λειτουργεί αυτός ο τύπος.

Δεδομένου ότι δεν υπάρχει ακριβής αντιστοίχιση των τιμών αναζήτησης, έχω χρησιμοποιήσει D2 & "*" ως τιμή αναζήτησης στη συνάρτηση MATCH.

Ο αστερίσκος είναι ένας χαρακτήρας μπαλαντέρ που αντιπροσωπεύει οποιονδήποτε αριθμό χαρακτήρων. Αυτό σημαίνει ότι το Apple* στον τύπο θα σήμαινε οποιαδήποτε συμβολοσειρά κειμένου που ξεκινά με τη λέξη Apple και μπορεί να έχει οποιονδήποτε αριθμό χαρακτήρων μετά από αυτήν.

Οπότε πότε Μήλο* χρησιμοποιείται ως τιμή αναζήτησης και ο τύπος MATCH την αναζητά στη στήλη Α, επιστρέφει τη θέση της «Apple Inc.», όπως ξεκινά με τη λέξη Apple.

Μπορείτε επίσης να χρησιμοποιήσετε χαρακτήρες μπαλαντέρ για να βρείτε συμβολοσειρές κειμένου όπου η τιμή αναζήτησης βρίσκεται ενδιάμεσα. Για παράδειγμα, εάν χρησιμοποιείτε * Apple * ως τιμή αναζήτησης, θα βρείτε οποιαδήποτε συμβολοσειρά που περιέχει τη λέξη μήλο οπουδήποτε μέσα της.

Σημείωση: Αυτή η τεχνική λειτουργεί καλά όταν έχετε μόνο μία εμφάνιση αντιστοίχισης. Αλλά αν έχετε πολλές περιπτώσεις αντιστοίχισης (για παράδειγμα Apple Inc και Apple Corporation, τότε η συνάρτηση MATCH θα επιστρέψει τη θέση της πρώτης αντιστοίχισης μόνο.

Παράδειγμα 10: Three Way Lookup

Αυτή είναι μια προηγμένη χρήση του INDEX MATCH, αλλά θα το καλύψω ακόμα για να σας δείξω τη δύναμη αυτού του συνδυασμού.

Θυμηθείτε είπα ότι η συνάρτηση INDEX έχει δύο συντακτικά:

= INDEX (array, row_num, [col_num]) = INDEX (array, row_num, [col_num], [area_num])

Μέχρι τώρα σε όλα τα παραδείγματά μας, έχουμε χρησιμοποιήσει μόνο το πρώτο.

Αλλά για μια αναζήτηση τριών κατευθύνσεων, πρέπει να χρησιμοποιήσετε τη δεύτερη σύνταξη.

Επιτρέψτε μου πρώτα να εξηγήσω τι σημαίνει τριμερής εμφάνιση.

Σε μια αμφίδρομη αναζήτηση, χρησιμοποιούμε τον τύπο INDEX MATCH για να λάβουμε τα σημάδια όταν έχουμε το όνομα του μαθητή και το όνομα του θέματος. Για παράδειγμα, η λήψη των σημάτων του Jim στα Μαθηματικά είναι αμφίδρομη αναζήτηση.

Μια τρισδιάστατη εμφάνιση θα προσθέσει μια άλλη διάσταση σε αυτό. Για παράδειγμα, ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να μάθετε τη βαθμολογία του Jim στα Μαθηματικά στις ενδιάμεσες εξετάσεις, τότε αυτό θα ήταν αναζήτηση τριών κατευθύνσεων.

Παρακάτω είναι ο τύπος που θα δώσει το αποτέλεσμα.

= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH ($ 4 $ , $ B $ 2: $ D $ 2,0), (ΕΑΝ (G $ 3 = "Δοκιμή μονάδας", 1, IF (G $ 3 = "Μεσοπρόθεσμα", 2,3))))

Ο παραπάνω τύπος ελέγχεται για τρία πράγματα - το όνομα του μαθητή, το θέμα και την εξέταση. Αφού βρει τη σωστή τιμή, την επιστρέφει στο κελί.

Επιτρέψτε μου να εξηγήσω πώς λειτουργεί αυτός ο τύπος διασπώντας τον τύπο σε μέρη.

• πίνακας - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Αντί να χρησιμοποιήσω έναν μόνο πίνακα, σε αυτήν την περίπτωση, έχω χρησιμοποιήσει τρεις πίνακες εντός παρένθεσης.
• row_num - MATCH ($ 5 $ F, $ A $ 3: $ A $ 7,0): Η συνάρτηση MATCH χρησιμοποιείται για να βρει τη θέση του ονόματος του μαθητή στο κελί $ 5 $ στη λίστα με το όνομα του μαθητή.
• col_num - MATCH ($ 4, $ B $ 2: $ D $ 2,0): Η συνάρτηση MATCH χρησιμοποιείται για να βρει τη θέση του ονόματος του θέματος στο κελί $ B $ 2 στη λίστα του ονόματος του θέματος.
• [area_num] - IF (G $ 3 = "Test Unit", 1, IF (G $ 3 = "Mid term", 2,3)): Η τιμή του αριθμού περιοχής υποδεικνύει τη συνάρτηση INDEX ποια από τις τρεις συστοιχίες να χρησιμοποιήσει για την ανάκτηση της τιμής. Εάν η εξέταση είναι Unit Term, η συνάρτηση IF θα επιστρέψει 1 και η συνάρτηση INDEX θα χρησιμοποιήσει τον πρώτο πίνακα για να πάρει την τιμή. Εάν η εξέταση είναι μεσοπρόθεσμη, ο τύπος IF θα επιστρέψει 2, αλλιώς θα επιστρέψει 3.

Αυτό είναι ένα προηγμένο παράδειγμα χρήσης του INDEX MATCH και είναι απίθανο να βρείτε μια κατάσταση όταν πρέπει να το χρησιμοποιήσετε. Αλλά είναι ακόμα καλό να γνωρίζουμε τι μπορούν να κάνουν οι τύποι Excel.

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Γιατί το INDEX/MATCH είναι καλύτερο από το VLOOKUP;

Ή μήπως είναι?

Ναι, είναι - στις περισσότερες περιπτώσεις.

Θα παρουσιάσω την υπόθεσή μου σε λίγο.

Αλλά πριν το κάνω αυτό, επιτρέψτε μου να το πω αυτό - Το VLOOKUP είναι μια εξαιρετικά χρήσιμη λειτουργία και το λατρεύω. Μπορεί να κάνει πολλά πράγματα στο Excel και το χρησιμοποιώ κάθε τόσο. Τούτου λεχθέντος, δεν σημαίνει ότι δεν μπορεί να υπάρξει κάτι καλύτερο και το INDEX/MATCH (με μεγαλύτερη ευελιξία και λειτουργικότητα) είναι καλύτερο.

Έτσι, εάν θέλετε να κάνετε κάποια βασική αναζήτηση, καλύτερα να χρησιμοποιήσετε το VLOOKUP.

Το INDEX/MATCH είναι VLOOKUP σε στεροειδή. Και μόλις μάθετε INDEX/MATCH, μπορεί πάντα να προτιμάτε τη χρήση του (ειδικά λόγω της ευελιξίας που έχει).

Χωρίς να το επεκτείνω πολύ, επιτρέψτε μου να σας δώσω γρήγορα τους λόγους για τους οποίους το INDEX/MATCH είναι καλύτερο από το VLOOKUP.

INDEX/MATCH μπορεί να κοιτάξει προς τα αριστερά (καθώς και προς τα δεξιά) της τιμής αναζήτησης

Το κάλυψα σε ένα από τα παραπάνω παραδείγματα.

Εάν έχετε μια τιμή που βρίσκεται στα αριστερά της τιμής αναζήτησης, δεν μπορείτε να το κάνετε με το VLOOKUP

Τουλάχιστον όχι μόνο με το VLOOKUP.

Ναι, μπορείτε να συνδυάσετε το VLOOKUP με άλλους τύπους και να το κάνετε, αλλά γίνεται περίπλοκο και ακατάστατο.

Το INDEX/MATCH, από την άλλη πλευρά, είναι κατασκευασμένο για αναζήτηση παντού (αριστερά, δεξιά, πάνω ή κάτω)

Το INDEX/MATCH μπορεί να λειτουργήσει με κάθετες και οριζόντιες περιοχές

Και πάλι, με πλήρη σεβασμό στο VLOOKUP, δεν είναι φτιαγμένο να το κάνει αυτό.

Εξάλλου, το V στο VLOOKUP σημαίνει κάθετο.

Το VLOOKUP μπορεί να περάσει μόνο από δεδομένα που είναι κάθετα, ενώ το INDEX/MATCH μπορεί να περάσει από δεδομένα κάθετα αλλά και οριζόντια.

Φυσικά, υπάρχει η λειτουργία HLOOKUP για τη φροντίδα της οριζόντιας αναζήτησης, αλλά δεν είναι VLOOKUP τότε… σωστά;

Μου αρέσει το γεγονός ότι ο συνδυασμός INDEX MATCH είναι αρκετά ευέλικτος για να λειτουργεί τόσο με κάθετα όσο και με οριζόντια δεδομένα.

Το VLOOKUP δεν μπορεί να λειτουργήσει με φθίνουσα δεδομένα

Όταν πρόκειται για τον κατά προσέγγιση αγώνα, το VLOOKUP και το INDEX/MATCH βρίσκονται στο ίδιο επίπεδο.

Αλλά το INDEX MATCH παίρνει το σημείο καθώς μπορεί επίσης να χειριστεί δεδομένα που είναι σε φθίνουσα σειρά.

Το δείχνω σε ένα από τα παραδείγματα σε αυτό το σεμινάριο όπου πρέπει να βρούμε τον βαθμό των μαθητών με βάση τον πίνακα βαθμολόγησης. Εάν ο πίνακας έχει ταξινομηθεί με φθίνουσα σειρά, το VLOOKUP δεν θα λειτουργούσε (αλλά το INDEX MATCH θα λειτουργούσε).

Το INDEX/MATCH μπορεί να είναι ελαφρώς γρηγορότερο

Θα είμαι ειλικρινής. Δεν έκανα μόνος μου αυτό το τεστ.

Βασίζομαι στη σοφία ενός Excel master - Charley Kyd.

Η διαφορά ταχύτητας στο VLOOKUP και στο INDEX/MATCH είναι ελάχιστα αισθητή όταν έχετε μικρά σύνολα δεδομένων. Αλλά αν έχετε χιλιάδες σειρές και πολλές στήλες, αυτό μπορεί να είναι ένας αποφασιστικός παράγοντας.

Στο άρθρο του, ο Charley Kyd αναφέρει:

«Στη χειρότερη περίπτωση, η μέθοδος INDEX-MATCH είναι τόσο γρήγορη όσο το VLOOKUP. στα καλύτερά του, είναι πολύ πιο γρήγορο ».

INDEX/MATCH είναι ανεξάρτητο από την πραγματική θέση στήλης

Εάν έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω καθώς παίρνετε τη βαθμολογία του Jim στη Φυσική, μπορείτε να το κάνετε χρησιμοποιώντας το VLOOKUP.

Και για να το κάνετε αυτό, μπορείτε να καθορίσετε τον αριθμό στήλης ως 3 στο VLOOKUP.

Ολα είναι καλά.

Τι γίνεται όμως αν διαγράψω τη στήλη Μαθηματικά.

Σε αυτήν την περίπτωση, ο τύπος VLOOKUP θα σπάσει.

Γιατί; - Επειδή ήταν σκληρή η χρήση της τρίτης στήλης και όταν διαγράφω μια στήλη ενδιάμεσα, η τρίτη στήλη γίνεται η δεύτερη στήλη.

Η χρήση INDEX/MATCH, σε αυτήν την περίπτωση, είναι καλύτερη καθώς μπορείτε να κάνετε τον αριθμό στήλης δυναμικό χρησιμοποιώντας MATCH. Έτσι, αντί για έναν αριθμό στήλης, ελέγχει το όνομα του θέματος και το χρησιμοποιεί για να επιστρέψει τον αριθμό της στήλης.

Σίγουρα μπορείτε να το κάνετε αυτό συνδυάζοντας το VLOOKUP με το MATCH, αλλά αν το συνδυάσετε ούτως ή άλλως, γιατί να μην το κάνετε με το INDEX που είναι πολύ πιο ευέλικτο.

Όταν χρησιμοποιείτε INDEX/MATCH, μπορείτε να εισαγάγετε/διαγράψετε με ασφάλεια στήλες στο σύνολο δεδομένων σας.

Παρά όλους αυτούς τους παράγοντες, υπάρχει ένας λόγος που το VLOOKUP είναι τόσο δημοφιλές.

Και είναι ένας μεγάλος λόγος.

Το VLOOKUP είναι πιο εύκολο στη χρήση

Το VLOOKUP λαμβάνει το πολύ τέσσερα ορίσματα. Αν μπορείτε να τυλίξετε το κεφάλι σας γύρω από αυτά τα τέσσερα, είστε έτοιμοι.

Και δεδομένου ότι οι περισσότερες από τις βασικές περιπτώσεις αναζήτησης χειρίζονται επίσης το VLOOKUP, έγινε γρήγορα η πιο δημοφιλής συνάρτηση Excel.

Το ονομάζω King of Excel συναρτήσεις.

Το INDEX/MATCH, από την άλλη πλευρά, είναι λίγο πιο δύσκολο στη χρήση. Εάν αρχίσετε να το χρησιμοποιείτε, μπορεί να κολλήσετε, αλλά για αρχάριους, το VLOOKUP είναι πολύ πιο εύκολο να εξηγηθεί και να μάθει.

Και αυτό δεν είναι ένα παιχνίδι μηδενικού αθροίσματος.

Έτσι, εάν είστε νέοι στον κόσμο αναζήτησης και δεν ξέρετε πώς να χρησιμοποιήσετε το VLOOKUP, μάθετε καλύτερα αυτό.

Έχω έναν λεπτομερή οδηγό για τη χρήση του VLOOKUP στο Excel (με πολλά παραδείγματα)

Ο σκοπός μου σε αυτό το άρθρο δεν είναι να βάλω δύο τρομερές λειτουργίες μεταξύ τους. Wantedθελα να σας δείξω τη δύναμη του συνδυασμού INDEX MATCH και όλα τα υπέροχα πράγματα που μπορεί να κάνει.

Ελπίζω να βρήκατε χρήσιμο αυτό το άρθρο.

Πείτε μου τις σκέψεις σας στην ενότητα σχολίων και σε περίπτωση που βρείτε κάποιο λάθος σε αυτό το σεμινάριο, ενημερώστε με.