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

Εισαγωγή της λειτουργίας VLOOKUP - Σύνταξη και Επιχειρήματα

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

Η σύνταξη της συνάρτησης VLOOKUP:

επιχειρήματα:

Σημειώσεις λειτουργίας:

Βασικά παραδείγματα VLOOKUP

Σε αυτήν την ενότητα, θα μιλήσουμε για ορισμένους τύπους Vlookup που χρησιμοποιούσατε συχνά.

2.1 Ακριβής αντιστοίχιση και κατά προσέγγιση αντιστοίχιση VLOOKUP

 2.1.1 Κάντε μια ακριβή αντιστοίχιση VLOOKUP

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

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

Αντιγράψτε και επικολλήστε τον παρακάτω τύπο σε ένα κενό κελί (εδώ, επιλέγω το G2) και πατήστε εισάγετε κλειδί για να λάβετε το αποτέλεσμα:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Πώς λειτουργεί ο τύπος VLOOKUP;

Αρχικά, αναζητά το αναγνωριστικό - C1005 στην αριστερή στήλη του πίνακα. Πηγαίνει από πάνω προς τα κάτω και βρίσκει την τιμή στο κελί A6.

Μόλις βρει την τιμή, πηγαίνει δεξιά στην τρίτη στήλη και εξάγει την τιμή σε αυτήν.

Έτσι, θα λάβετε το αποτέλεσμα όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης:

 2.1.2 Κάντε μια κατά προσέγγιση αντιστοίχιση VLOOKUP

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

Για παράδειγμα, εάν έχετε το ακόλουθο εύρος δεδομένων και οι καθορισμένες παραγγελίες δεν βρίσκονται στη στήλη Παραγγελίες, πώς να λάβετε την πλησιέστερη Έκπτωση στη στήλη Β;

Βήμα 1: Εφαρμόστε τον τύπο VLOOKUP και γεμίστε τον σε άλλα κελιά

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

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Αποτέλεσμα:

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

2.2 Εκτελέστε VLOOKUP με διάκριση πεζών-κεφαλαίων στο Excel

Από προεπιλογή, η συνάρτηση VLOOKUP εκτελεί αναζήτηση χωρίς διάκριση πεζών-κεφαλαίων που σημαίνει ότι αντιμετωπίζει τους πεζούς και κεφαλαίους χαρακτήρες ως πανομοιότυπους. Κάποια στιγμή, μπορεί να χρειαστεί να εκτελέσετε μια αναζήτηση με διάκριση πεζών-κεφαλαίων στο Excel, η κανονική συνάρτηση VLOOKUP ενδέχεται να μην το λύσει. Σε αυτήν την περίπτωση, μπορείτε να χρησιμοποιήσετε εναλλακτικές συναρτήσεις όπως το INDEX και το MATCH με τη συνάρτηση EXACT ή τις συναρτήσεις LOOKUP και EXACT.

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

Βήμα 1: Εφαρμόστε οποιονδήποτε τύπο και γεμίστε τον σε άλλα κελιά

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

Τύπος 1: Αφού επικολλήσετε τον τύπο, πατήστε Ctrl + Shift + Εισαγωγή κλειδιά.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Τύπος 2: Αφού επικολλήσετε τον τύπο, πατήστε εισάγετε κλειδί.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Αποτέλεσμα:

Τότε θα έχετε τα σωστά αποτελέσματα που χρειάζεστε. Δείτε στιγμιότυπο οθόνης:

2.3 Τιμές VLOOKUP από δεξιά προς τα αριστερά στο Excel

Η συνάρτηση VLOOKUP αναζητά πάντα μια τιμή στην πιο αριστερή στήλη μιας περιοχής δεδομένων και επιστρέφει την αντίστοιχη τιμή από μια στήλη προς τα δεξιά. Εάν θέλετε να εκτελέσετε ένα αντίστροφο VLOOKUP που σημαίνει να αναζητήσετε μια συγκεκριμένη τιμή στη δεξιά στήλη και να επιστρέψετε την αντίστοιχη τιμή της στην αριστερή στήλη όπως φαίνεται παρακάτω στιγμιότυπο οθόνης:

Κάντε κλικ για να μάθετε τις λεπτομέρειες βήμα προς βήμα σχετικά με αυτήν την εργασία…

2.4 VLOOKUP της δεύτερης, της νης ή της τελευταίας τιμής που αντιστοιχεί στο Excel

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

 2.4.1 VLOOKUP και επιστρέψτε τη δεύτερη ή την ντη τιμή που ταιριάζει

Ας υποθέσουμε ότι έχετε μια λίστα ονομάτων στη στήλη Α, το εκπαιδευτικό σεμινάριο που αγόρασαν στη στήλη Β. Τώρα, ψάχνετε να βρείτε το 2ο ή το ένατο εκπαιδευτικό μάθημα που αγόρασε ο συγκεκριμένος πελάτης. Δείτε στιγμιότυπο οθόνης:

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

Βήμα 1: Εφαρμόστε και συμπληρώστε τον τύπο σε άλλα κελιά

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

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Αποτέλεσμα:

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

 2.4.2 VLOOKUP και επιστρέψτε την τελευταία τιμή που ταιριάζει

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

2.5 VLOOKUP αντιστοίχιση τιμών μεταξύ δύο δεδομένων τιμών ή ημερομηνιών

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

 2.5.1 VLOOKUP αντιστοίχιση τιμών μεταξύ δύο δεδομένων τιμών ή ημερομηνιών με τύπο

Βήμα 1: Τακτοποιήστε τα δεδομένα και εφαρμόστε τον ακόλουθο τύπο

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

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Αποτέλεσμα:

Και τώρα, θα λάβετε όλες τις αντιστοιχισμένες εγγραφές με βάση τη δεδομένη τιμή, δείτε στιγμιότυπο οθόνης:

 2.5.2 VLOOKUP αντιστοίχιση τιμών μεταξύ δύο δεδομένων τιμών ή ημερομηνιών με ένα εύχρηστο χαρακτηριστικό

Εάν δυσκολεύεστε να θυμηθείτε και να κατανοήσετε τον παραπάνω τύπο, εδώ θα σας παρουσιάσω ένα εύκολο εργαλείο – Kutools για Excel, Με τους ΠΡΟΒΟΛΗ μεταξύ δύο τιμών χαρακτηριστικό, μπορείτε να επιστρέψετε το αντίστοιχο στοιχείο με βάση τη συγκεκριμένη τιμή ή ημερομηνία μεταξύ δύο τιμών ή ημερομηνιών με ευκολία.

1. Πατήστε Kutools > Σούπερ LOOKUP > ΠΡΟΒΟΛΗ μεταξύ δύο τιμών για να ενεργοποιήσετε αυτήν τη λειτουργία.
2. Στη συνέχεια, καθορίστε τις λειτουργίες από το παράθυρο διαλόγου με βάση τα δεδομένα σας.

2.6 Χρήση χαρακτήρων μπαλαντέρ για μερικές αντιστοιχίσεις στη συνάρτηση VLOOKUP

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

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

Βήμα 1: Εφαρμόστε και συμπληρώστε τον τύπο σε άλλα κελιά

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

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Αποτέλεσμα:

Και όλες οι ταιριασμένες βαθμολογίες έχουν επιστραφεί όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης:

2.7 Τιμές VLOOKUP από άλλο φύλλο εργασίας

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

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

Βήμα 1: Εφαρμόστε και συμπληρώστε τον τύπο σε άλλα κελιά

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

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Αποτέλεσμα:

Θα λάβετε τα αντίστοιχα αποτελέσματα όπως χρειάζεστε, δείτε στιγμιότυπο οθόνης:

2.8 Τιμές VLOOKUP από άλλο βιβλίο εργασίας

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

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

Βήμα 1: Εφαρμόστε και συμπληρώστε τον τύπο

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

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Αποτέλεσμα:

2.9 Επιστρέψτε κενό ή συγκεκριμένο κείμενο αντί για σφάλμα 0 ή #Δ/Υ

Συνήθως, όταν χρησιμοποιείτε τη συνάρτηση VLOOKUP για να επιστρέψετε μια αντίστοιχη τιμή, εάν το αντίστοιχο κελί είναι κενό, θα επιστρέψει 0. Και αν δεν βρεθεί η τιμή που ταιριάζει, θα λάβετε μια τιμή σφάλματος #N/A όπως φαίνεται στο στιγμιότυπο οθόνης παρακάτω. Εάν θέλετε να εμφανίσετε ένα κενό κελί ή μια συγκεκριμένη τιμή αντί για 0 ​​ή #N/A, αυτό VLOOKUP Για επιστροφή κενή ή συγκεκριμένη τιμή αντί για 0 ​​ή μη το σεμινάριο μπορεί να σας κάνει τη χάρη.

Προηγμένα παραδείγματα VLOOKUP

3.1 Διπλή αναζήτηση (VLOOKUP σε σειρά και στήλη)

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

Στο Excel, μπορείτε να χρησιμοποιήσετε έναν συνδυασμό συναρτήσεων VLOOKUP και MATCH για να κάνετε αμφίδρομη αναζήτηση.

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

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 Τιμή αντιστοίχισης VLOOKUP με βάση δύο ή περισσότερα κριτήρια

Είναι εύκολο για εσάς να αναζητήσετε την αντίστοιχη τιμή με βάση ένα κριτήριο, αλλά αν έχετε δύο ή περισσότερα κριτήρια, τι μπορείτε να κάνετε;

 3.2.1 Τιμή αντιστοίχισης VLOOKUP με βάση δύο ή περισσότερα κριτήρια με τύπους

Σε αυτήν την περίπτωση, οι συναρτήσεις LOOKUP ή MATCH και INDEX στο Excel μπορούν να σας βοηθήσουν να λύσετε αυτήν την εργασία γρήγορα και εύκολα.

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

Βήμα 1: Εφαρμόστε οποιονδήποτε τύπο

Τύπος 1: Αφού επικολλήσετε τον τύπο, πατήστε εισάγετε κλειδί.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Τύπος 2: Αφού επικολλήσετε τον τύπο, πατήστε Ctrl + Shift + Εισαγωγή κλειδιά.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Αποτέλεσμα:

 3.2.2 Τιμή αντιστοίχισης VLOOKUP με βάση δύο ή περισσότερα κριτήρια με έξυπνο χαρακτηριστικό

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

1. Πατήστε Kutools > Σούπερ LOOKUP > Αναζήτηση πολλαπλών συνθηκών για να ενεργοποιήσετε αυτήν τη λειτουργία.
2. Στη συνέχεια, καθορίστε τις λειτουργίες από το παράθυρο διαλόγου με βάση τα δεδομένα σας.

3.3 VLOOKUP για επιστροφή πολλαπλών τιμών με ένα ή περισσότερα κριτήρια

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

 3.3.1 VLOOKUP όλων των τιμών που ταιριάζουν με βάση μία ή περισσότερες συνθήκες οριζόντια

Υποθέτοντας ότι έχετε έναν πίνακα δεδομένων που περιέχει χώρα, πόλη και ονόματα στο εύρος A1:C14 και τώρα, θέλετε να επιστρέψετε όλα τα ονόματα οριζόντια τα οποία προέρχονται από το "ΗΠΑ", όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης. Για να λύσετε αυτήν την εργασία, παρακαλώ κάντε κλικ εδώ για να δείτε το αποτέλεσμα βήμα προς βήμα.

 3.3.2 VLOOKUP όλων των τιμών που ταιριάζουν με βάση μία ή περισσότερες συνθήκες κάθετα

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

 3.3.3 VLOOKUP όλων των τιμών που ταιριάζουν με βάση μία ή περισσότερες συνθήκες σε ένα κελί

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

3.4 VLOOKUP για να επιστρέψετε ολόκληρη τη σειρά ενός αντίστοιχου κελιού

Σε αυτήν την ενότητα, θα μιλήσω για το πώς να ανακτήσετε ολόκληρη τη σειρά μιας αντιστοιχισμένης τιμής χρησιμοποιώντας τη συνάρτηση VLOOKUP.

Βήμα 1: Εφαρμόστε και συμπληρώστε τον παρακάτω τύπο

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

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Αποτέλεσμα:

Τώρα, μπορείτε να δείτε ότι επιστρέφονται ολόκληρα τα δεδομένα της σειράς. Δείτε στιγμιότυπο οθόνης:

3.5 Ένθετο VLOOKUP στο Excel

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

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

Ο γενικός τύπος για την ένθετη συνάρτηση VLOOKUP είναι:

Βήμα 1: Εφαρμόστε και συμπληρώστε τον παρακάτω τύπο

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

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Αποτέλεσμα:

Τώρα, θα λάβετε το αποτέλεσμα όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης:

3.6 Ελέγξτε εάν η τιμή υπάρχει με βάση δεδομένα λίστας σε άλλη στήλη

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

Βήμα 1: Εφαρμόστε και συμπληρώστε τον παρακάτω τύπο

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

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Αποτέλεσμα:

Και θα έχετε το αποτέλεσμα όπως χρειάζεστε, δείτε στιγμιότυπο οθόνης:

3.7 VLOOKUP και άθροιση όλων των αντιστοιχισμένων τιμών σε γραμμές ή στήλες

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

 3.7.1 VLOOKUP και άθροιση όλων των αντιστοιχισμένων τιμών σε μια σειρά ή πολλές σειρές

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

Βήμα 1: Εφαρμόστε και συμπληρώστε τον παρακάτω τύπο

Αντιγράψτε ή εισαγάγετε τον ακόλουθο τύπο σε ένα κενό κελί και, στη συνέχεια, πατήστε Ctrl + Shift + Εισαγωγή πλήκτρα μαζί για να έχετε το πρώτο αποτέλεσμα. Στη συνέχεια, σύρετε τη λαβή πλήρωσης προς τα κάτω για να αντιγράψετε αυτόν τον τύπο σε άλλα κελιά που χρειάζεστε.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Αποτέλεσμα:

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

 3.7.2 VLOOKUP και άθροιση όλων των αντιστοιχισμένων τιμών σε μια στήλη ή πολλές στήλες

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

Βήμα 1: Εφαρμόστε τον ακόλουθο τύπο

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

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Αποτέλεσμα:

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

 3.7.3 VLOOKUP και άθροιση των πρώτων αντιστοιχισμένων ή όλων των τιμών με ένα ισχυρό χαρακτηριστικό

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

1. Πατήστε Kutools > Σούπερ LOOKUP > LOOKUP και άθροισμα για να ενεργοποιήσετε αυτήν τη λειτουργία.
2. Στη συνέχεια, καθορίστε τις λειτουργίες από το πλαίσιο διαλόγου με βάση τις ανάγκες σας.

 3.7.4 VLOOKUP και άθροιση όλων των αντιστοιχισμένων τιμών τόσο σε γραμμές όσο και σε στήλες

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

Εδώ, μπορείτε να χρησιμοποιήσετε τη συνάρτηση SUMPRODCT για να ολοκληρώσετε αυτήν την εργασία.

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

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 VLOOKUP για συγχώνευση δύο πινάκων με βάση τις στήλες κλειδιών

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

 3.8.1 VLOOKUP για συγχώνευση δύο πινάκων με βάση μία στήλη κλειδιού

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

Βήμα 1: Εφαρμόστε και συμπληρώστε τον παρακάτω τύπο

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

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Αποτέλεσμα:

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

 3.8.2 VLOOKUP για συγχώνευση δύο πινάκων που βασίζονται σε πολλαπλές στήλες κλειδιών

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

Ο γενικός τύπος είναι:

Βήμα 1: Εφαρμόστε τον ακόλουθο τύπο

Εφαρμόστε τον παρακάτω τύπο σε ένα κενό κελί όπου θέλετε να τοποθετήσετε το αποτέλεσμα και, στη συνέχεια, πατήστε Ctrl + Shift + Εισαγωγή πλήκτρα μαζί για να λάβετε την πρώτη αντιστοιχισμένη τιμή, δείτε το στιγμιότυπο οθόνης:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Βήμα 2: Συμπληρώστε τον τύπο σε άλλα κελιά

Στη συνέχεια, επιλέξτε το πρώτο κελί τύπου και σύρετε τη λαβή πλήρωσης για να αντιγράψετε αυτόν τον τύπο σε άλλα κελιά όπως χρειάζεστε:

3.9 VLOOKUP αντιστοίχιση τιμών σε πολλά φύλλα εργασίας

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

Οι αντιστοιχισμένες τιμές VLOOKUP διατηρούν τη μορφοποίηση κελιού

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

4.1 VLOOKUP αντιστοίχιση τιμής και διατήρησης χρώματος κελιού, μορφοποίησης γραμματοσειράς

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

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

Βήμα 1: Αντιγράψτε τον κωδικό 1 στη μονάδα κώδικα φύλλου

1. Στο φύλλο εργασίας περιέχει τα δεδομένα που θέλετε να VLOOKUP, κάντε δεξί κλικ στην καρτέλα του φύλλου και επιλέξτε Προβολή κωδικού από το μενού περιβάλλοντος. Δείτε το στιγμιότυπο οθόνης:
   
2. Στο άνοιγμα Microsoft Visual Basic για εφαρμογές παράθυρο, αντιγράψτε τον παρακάτω κώδικα VBA στο παράθυρο Code.
3. Κωδικός VBA 1: VLOOKUP για λήψη μορφοποίησης κελιού μαζί με τιμή αναζήτησης

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Βήμα 2: Αντιγράψτε τον κωδικό 2 στο παράθυρο Module

1. Ακόμα στο Microsoft Visual Basic για εφαρμογές παράθυρο, κάντε κλικ στην επιλογή Κύριο θέμα > Μονάδα μέτρησηςκαι, στη συνέχεια, αντιγράψτε τον παρακάτω κώδικα VBA 2 στο παράθυρο Module.
2. Κωδικός VBA 2: VLOOKUP για λήψη μορφοποίησης κελιού μαζί με τιμή αναζήτησης

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Βήμα 3: Επιλέξτε την επιλογή για το VBAproject

1. Αφού εισαγάγετε τους παραπάνω κωδικούς, κάντε κλικ Εργαλεία > αναφορές στο Microsoft Visual Basic για εφαρμογές παράθυρο. Στη συνέχεια, ελέγξτε το Χρόνος εκτέλεσης δέσμης ενεργειών Microsoft στο πλαίσιο ελέγχου Αναφορές - VBAProject κουτί διαλόγου. Δείτε στιγμιότυπα οθόνης:
2. Στη συνέχεια, κάντε κλικ στο κουμπί OK για να κλείσετε το παράθυρο διαλόγου και, στη συνέχεια, αποθηκεύστε και κλείστε το παράθυρο κώδικα.

Βήμα 4: Πληκτρολογήστε τον τύπο για να λάβετε το αποτέλεσμα

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

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 Διατηρήστε τη μορφή ημερομηνίας από μια επιστρεφόμενη τιμή VLOOKUP

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

Βήμα 1: Εφαρμόστε και συμπληρώστε τον παρακάτω τύπο

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

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Αποτέλεσμα:

Όλες οι ημερομηνίες που ταιριάστηκαν έχουν επιστραφεί όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης:

4.3 Επιστροφή σχολίου κελιού από το VLOOKUP

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

Βήμα 1: Αντιγράψτε τον κώδικα σε μια ενότητα

1. Κρατήστε πατημένο το ALT + F11 για να ανοίξετε το Microsoft Visual Basic για εφαρμογές παράθυρο.
2. Πατήστε Κύριο θέμα > Μονάδα μέτρησηςκαι, στη συνέχεια, αντιγράψτε και επικολλήστε τον ακόλουθο κώδικα στο παράθυρο της ενότητας.
   Κωδικός VBA: Vlookup και απόδοση αντιστοίχισης με σχόλιο κελιού

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Στη συνέχεια αποθηκεύστε και κλείστε το παράθυρο κώδικα.

Βήμα 2: Πληκτρολογήστε τον τύπο για να λάβετε το αποτέλεσμα

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

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 Οι αριθμοί VLOOKUP αποθηκεύονται ως κείμενο

Για παράδειγμα, έχω μια σειρά δεδομένων όπου ο αριθμός αναγνωριστικού στον αρχικό πίνακα είναι σε μορφή αριθμού και ο αριθμός αναγνωριστικού στα κελιά αναζήτησης αποθηκεύεται ως κείμενο, ενδέχεται να αντιμετωπίσετε ένα σφάλμα #N/A όταν χρησιμοποιείτε την κανονική συνάρτηση VLOOKUP. Σε αυτήν την περίπτωση, για να ανακτήσετε τις σωστές πληροφορίες, μπορείτε να τυλίξετε τις συναρτήσεις TEXT και VALUE στη συνάρτηση VLOOKUP. Παρακάτω είναι ο τύπος για να επιτευχθεί αυτό:

Βήμα 1: Εφαρμόστε και συμπληρώστε τον παρακάτω τύπο

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

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Αποτέλεσμα:

Τώρα, θα λάβετε τα σωστά αποτελέσματα όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης: