Εάν διαβάζετε συμβουλές για το Excel για λίγο, έχετε πάντα βρει κάποιον που μιλά για τη χρήση των λειτουργιών Excel INDEX () & MATCH () αντί του Excel VLOOKUP. Μιλώντας για τον εαυτό μου, ήταν πάντα πολύ δύσκολο να δοκιμάσω ταυτόχρονα ΔΥΟ νέες λειτουργίες. Όμως, είναι ένα ωραίο κόλπο. Δώσε μου πέντε λεπτά και θα προσπαθήσω να το εξηγήσω στα απλά αγγλικά.
Η κριτική 30 δευτερολέπτων του VLOOKUP
Ας υποθέσουμε ότι έχετε έναν πίνακα με αρχεία υπαλλήλων. Η πρώτη στήλη είναι ένας αριθμός υπαλλήλου και οι υπόλοιπες στήλες είναι διάφορα δεδομένα σχετικά με τον υπάλληλο. Κάθε φορά που έχετε έναν αριθμό υπαλλήλου στο φύλλο εργασίας, μπορείτε να χρησιμοποιήσετε το VLOOKUP για να επιστρέψετε ένα συγκεκριμένο στοιχείο σχετικά με τον υπάλληλο. Η σύνταξη είναι VLOOKUP (τιμή, εύρος δεδομένων, αριθ. Col, FALSE). Λέει στο Excel, "Πηγαίνετε στο εύρος δεδομένων. Βρείτε μια σειρά που έχει (τιμή) στην πρώτη στήλη του εύρους δεδομένων. Επιστρέψτε την τιμή (αρ. Στήλης) από αυτήν τη σειρά. Μόλις λάβετε την αναμονή, είναι πολύ απλό και ισχυρό.
Το πρόβλημα
Μια μέρα, έχετε μια κατάσταση όπου έχετε το όνομα του υπαλλήλου, αλλά χρειάζεστε τον αριθμό του υπαλλήλου. Στην παρακάτω εικόνα, έχετε ένα όνομα στο A10 και πρέπει να βρείτε τον αριθμό υπαλλήλου στο B10.
Όταν το πεδίο κλειδιού βρίσκεται στα δεξιά των δεδομένων που θέλετε να ανακτήσετε, το VLOOKUP δεν θα λειτουργήσει. Εάν μόνο το VLOOKUP θα δεχόταν το -1 ως αριθμό στήλης, δεν θα υπήρχε πρόβλημα. Όμως, δεν είναι. Μία κοινή λύση είναι να εισαγάγετε προσωρινά μια νέα στήλη Α, να αντιγράψετε τη στήλη ονομάτων στη νέα στήλη Α, να συμπληρώσετε με VLOOKUP, Επικόλληση ειδικών τιμών και, στη συνέχεια, να διαγράψετε την προσωρινή στήλη A. Οι επαγγελματίες του Excel πιθανώς να κάνουν αυτήν την κίνηση στον ύπνο τους.
Θα σας προτείνω να ακολουθήσετε την πρόκληση και να προσπαθήσετε να χρησιμοποιήσετε αυτήν τη μέθοδο ενός βήματος. Ναι, θα πρέπει να επιτύχετε τη φόρμουλα στον τοίχο σας για μερικές εβδομάδες, αλλά το κάνατε και με το VLOOKUP πριν από πολύ καιρό, έτσι δεν είναι;
Νομίζω ότι ο λόγος που είναι τόσο δύσκολος είναι ότι χρησιμοποιείτε δύο λειτουργίες που πιθανότατα δεν έχετε χρησιμοποιήσει ποτέ πριν. Λοιπόν, επιτρέψτε μου να το χωρίσω σε δύο κομμάτια.
Πρώτον, υπάρχει η συνάρτηση INDEX (). Αυτή είναι μια φρικιαστικά χαρακτηρισμένη λειτουργία. Όταν κάποιος λέει "ευρετήριο", δεν δημιουργεί τίποτα στο μυαλό μου που είναι παρόμοιο με αυτό που κάνει αυτή η λειτουργία. Το ευρετήριο απαιτεί τρία ορίσματα.
=INDEX(data range, row number, column number)
Στα Αγγλικά, το Excel μεταβαίνει στο εύρος δεδομένων και σας επιστρέφει την τιμή στη διασταύρωση της (αριθμός σειράς) της σειράς και της (αριθμός στήλης) της στήλης. Γεια σου, σκεφτείτε το - αυτό είναι πολύ απλό, έτσι; =INDEX($A$2:$C$6,4,2)θα σας δώσει την τιμή στο B5.
Εφαρμόζοντας INDEX () στο πρόβλημά μας, μπορείτε να υπολογίσετε ότι για να επιστρέψει τον αριθμό των εργαζομένων από την περιοχή, μπορείτε να χρησιμοποιήσετε αυτό: =INDEX($A$2:$A$6,?,1). Στην πραγματικότητα, αυτό το κομμάτι φαίνεται τόσο ασήμαντο που φαίνεται άχρηστο. Αλλά, όταν αντικαθιστάτε το ερωτηματικό με μια συνάρτηση MATCH (), έχετε τη λύση.
Εδώ είναι η σύνταξη:
=MATCH(Value, Single-column data range, FALSE)
Λέει στο Excel, "Αναζήτηση στο εύρος δεδομένων και πείτε μου τον σχετικό αριθμό σειράς όπου βρίσκετε μια αντιστοίχιση για (δεδομένα). Έτσι, για να βρείτε ποια σειρά έχει ο υπάλληλος στο A10, θα χρησιμοποιούσατε =MATCH(A10,$B$2:$B$6,FALSE). Ναι, αυτό είναι πιο περίπλοκο από το ευρετήριο , αλλά θα πρέπει να είναι ακριβώς στο δρομάκι των επαγγελματιών του VLOOKUP. Εάν το A10 περιέχει "Miller, Bob", τότε αυτό το MATCH θα επιστρέψει ότι βρίσκεται στην 3η σειρά της σειράς B2: B6.
Αυτό είναι - η συνάρτηση MATCH () λέει στη συνάρτηση Index σε ποια σειρά θα κοιτάξει - τελειώσατε. Πάρτε τη συνάρτηση ευρετηρίου, αντικαταστήστε το ερωτηματικό μας με τη συνάρτηση MATCH και τώρα μπορείτε να κάνετε το ισοδύναμο των VLOOKUPs όταν το πεδίο κλειδιού δεν βρίσκεται στην αριστερή στήλη. Εδώ είναι η λειτουργία που πρέπει να χρησιμοποιήσετε:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Η κολλώδης νότα στον τοίχο μου το δείχνει στην πραγματικότητα ως δύο γραμμές. Πρώτα έγραψα την εξήγηση για MATCH (). Παρακάτω έγραψα την εξήγηση για το INDEX (). Στη συνέχεια σχεδίασα ένα σχήμα διοχέτευσης μεταξύ των δύο για να δείξω ότι η συνάρτηση MATCH () πέφτει στο 2ο όρισμα της συνάρτησης INDEX ().
Τις πρώτες φορές που έπρεπε να κάνω ένα από αυτά, μπήκα στον πειρασμό απλώς να χτυπήσω μια νέα προσωρινή στήλη Α εκεί, αλλά πέρασα από τον πόνο να το κάνω έτσι. Είναι ταχύτερο και απαιτεί λιγότερους χειρισμούς. Έτσι, την επόμενη φορά που θα θέλατε να βάλετε έναν αρνητικό αριθμό στη συνάρτηση VLOOKUP, δοκιμάστε αυτόν τον παράξενο συνδυασμό INDEX και MATCH για να λύσετε τα προβλήματά σας.
