Note: The other languages of the website are Google-translated. Back to English
Σύνδεση  \/ 
x
or
x
Εγγραφή  \/ 
x

or

Λειτουργία VLOOKUP με ορισμένα βασικά και προηγμένα παραδείγματα στο Excel

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

Πίνακας περιεχομένων:

1. Εισαγωγή της συνάρτησης VLOOKUP - Σύνταξη και Επιχειρήματα

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

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

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

5. Λήψη δειγμάτων αρχείων VLOOKUP


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

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

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

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

Αναζήτηση_τιμής: Η τιμή που θέλετε να αναζητήσετε. Πρέπει να βρίσκεται στην πρώτη στήλη του εύρους table_array.

Table_array: Το εύρος δεδομένων ή ο πίνακας όπου εντοπίζεται η στήλη τιμής αναζήτησης και η στήλη τιμής αποτελέσματος.

Col_index_num: Ο αριθμός της στήλης από την οποία θα επιστραφεί η αντιστοιχισμένη τιμή. Ξεκινά με 1 από την αριστερή στήλη του πίνακα πίνακα.

Εύρος_αναζήτησης: Μια λογική τιμή που καθορίζει εάν αυτή η συνάρτηση VLOOKUP θα επιστρέψει μια ακριβή αντιστοίχιση ή μια κατά προσέγγιση αντιστοίχιση.

  • Κατά προσέγγιση αντιστοίχιση - 1 / Αλήθεια: Εάν δεν βρεθεί ακριβής αντιστοίχιση, ο τύπος αναζητά την πλησιέστερη αντιστοίχιση - τη μεγαλύτερη τιμή που είναι μικρότερη από την τιμή αναζήτησης. Σε αυτήν την περίπτωση, πρέπει να ταξινομήσετε τη στήλη αναζήτησης με αύξουσα σειρά.
    = VLOOKUP (lookup_value, table_array, col_index, TRUE)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • Ακριβής αντιστοίχιση - 0 / ΛΑΘΟΣ: Χρησιμοποιείται για την αναζήτηση μιας τιμής ακριβώς ίσης με την τιμή αναζήτησης. Εάν δεν βρεθεί ακριβής αντιστοίχιση, θα επιστραφεί η τιμή σφάλματος # Δ / Α.
    = VLOOKUP (lookup_value, table_array, col_index, FALSE)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

:

  • 1. Η συνάρτηση Vlookup αναζητά μόνο τιμή από αριστερά προς τα δεξιά.
  • 2. Εάν υπάρχουν πολλές τιμές αντιστοίχισης με βάση την τιμή αναζήτησης, μόνο η πρώτη αντιστοίχιση θα επιστραφεί χρησιμοποιώντας τη συνάρτηση Vlookup.
  • 3. Θα επιστρέψει την τιμή σφάλματος # N / A εάν δεν είναι δυνατή η εύρεση της τιμής αναζήτησης.

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

1. Κάντε το Vlookup ακριβούς αγώνα και το Vlookup αντιστοίχισης κατά προσέγγιση

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

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

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

1. Εφαρμόστε τον παρακάτω τύπο σε ένα κενό κελί όπου θέλετε να έχετε το αποτέλεσμα:

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

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

:

  • 1. Στον παραπάνω τύπο, F2 είναι η τιμή που θέλετε να επιστρέψετε την αντίστοιχη τιμή, Α2: Δ7 είναι ο πίνακας πίνακα, ο αριθμός 3 είναι ο αριθμός στήλης από τον οποίο επιστρέφεται η αντιστοιχισμένη τιμή σας και το ΨΕΥΔΗΣ αναφέρεται στην ακριβή αντιστοίχιση.
  • 2. Εάν η τιμή κριτηρίου σας δεν βρεθεί στο εύρος δεδομένων, θα εμφανιστεί μια τιμή σφάλματος # Δ / Α.

Κάντε κατά προσέγγιση αντιστοίχιση Vlookup στο Excel

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

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

1. Εισαγάγετε τον ακόλουθο τύπο σε ένα κελί όπου θέλετε να βάλετε το αποτέλεσμα:

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

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

:

  • 1. Στον παραπάνω τύπο, D2 είναι η τιμή που θέλετε να επιστρέψετε τις σχετικές πληροφορίες της, Α2: Β9 είναι το εύρος δεδομένων, ο αριθμός 2 υποδεικνύει τον αριθμό της στήλης ότι η αντίστοιχη τιμή σας επιστρέφεται και το ΑΛΗΘΙΝΗ αναφέρεται στην κατά προσέγγιση αντιστοίχιση.
  • 2. Η κατά προσέγγιση αντιστοίχιση θα επιστρέψει τη μεγαλύτερη τιμή που είναι μικρότερη από τη συγκεκριμένη τιμή αναζήτησης.
  • 3. Για να χρησιμοποιήσετε τη συνάρτηση Vlookup για να λάβετε μια τιμή αντιστοίχισης κατά προσέγγιση, πρέπει να ταξινομήσετε την αριστερή στήλη του εύρους δεδομένων σε αύξουσα σειρά, διαφορετικά θα επιστρέψει ένα λάθος αποτέλεσμα.

2. Κάντε ένα Vlookup με πεζά γράμματα στο Excel

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

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

Τύπος 1: Χρησιμοποιώντας τις συναρτήσεις EXACT, INDEX, MATCH

1. Εισαγάγετε ή αντιγράψτε τον παρακάτω τύπο πίνακα σε ένα κενό κελί όπου θέλετε να λάβετε το αποτέλεσμα:

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

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

:

  • 1. Στον παραπάνω τύπο, A2: A10 είναι η στήλη που περιέχει τις συγκεκριμένες τιμές στις οποίες θέλετε να αναζητήσετε, F2 είναι η τιμή αναζήτησης, C2: C10 είναι η στήλη από την οποία θα επιστραφεί το αποτέλεσμα.
  • 2. Εάν βρεθούν πολλοί αγώνες, αυτός ο τύπος θα επιστρέφει πάντα τον πρώτο αγώνα.

Τύπος 2: Χρήση λειτουργιών αναζήτησης και ακριβείας

1. Εφαρμόστε τον παρακάτω τύπο σε ένα κενό κελί όπου θέλετε να λάβετε το αποτέλεσμα:

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

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

:

  • 1. Στον παραπάνω τύπο, A2: A10 είναι η στήλη που περιέχει τις συγκεκριμένες τιμές στις οποίες θέλετε να αναζητήσετε, F2 είναι η τιμή αναζήτησης, C2: C10 είναι η στήλη από την οποία θα επιστραφεί το αποτέλεσμα.
  • 2. Εάν βρεθούν πολλοί αγώνες, αυτός ο τύπος θα επιστρέφει πάντα τον τελευταίο αγώνα.

3. Τιμές Vlookup από δεξιά προς τα αριστερά στο Excel

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

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


4. Δείτε τη δεύτερη, την XNUMXη ή την τελευταία τιμή αντιστοίχισης στο Excel

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

Προβλέψτε και επιστρέψτε τη δεύτερη ή την αντίστοιχη τιμή αντιστοίχισης

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

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

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

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

Σημείωση:

  • Σε αυτόν τον τύπο, A2: A14 είναι το εύρος με όλες τις τιμές για αναζήτηση, Β2: Β14 είναι το εύρος των τιμών αντιστοίχισης από τις οποίες θέλετε να επιστρέψετε, E2 είναι η τιμή αναζήτησης και ο τελευταίος αριθμός 2 υποδεικνύει τη δεύτερη αντιστοιχισμένη τιμή που θέλετε να λάβετε, εάν θέλετε να επιστρέψετε την τρίτη τιμή αντιστοίχισης, απλά πρέπει να την αλλάξετε σε 3 όπως χρειάζεστε.

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

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


5. Vlookup που ταιριάζουν τιμές μεταξύ δύο δεδομένων τιμών ή ημερομηνιών

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

Vlookup που ταιριάζει τιμές μεταξύ δύο δεδομένων τιμών ή ημερομηνιών με τον τύπο

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

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

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

:

  • 1. Στον παραπάνω τύπο, A2: A6 είναι το εύρος των μικρότερων τιμών και Β2: Β6 είναι το εύρος μεγαλύτερων αριθμών στο εύρος δεδομένων σας, το E2 είναι η δεδομένη τιμή που θέλετε να λάβετε την αντίστοιχη τιμή, C2: C6 είναι τα δεδομένα στηλών από τα οποία θέλετε να εξαγάγετε.
  • 2. Αυτός ο τύπος μπορεί επίσης να χρησιμοποιηθεί για την εξαγωγή αντιστοιχισμένων τιμών μεταξύ δύο ημερομηνιών όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης:

Vlookup που ταιριάζουν τιμές μεταξύ δύο δεδομένων τιμών ή ημερομηνιών με μια χρήσιμη δυνατότητα

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


6. Χρήση μπαλαντέρ για μερικούς αγώνες στη λειτουργία Vlookup

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

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

1. Η κανονική συνάρτηση Vlookup δεν λειτουργεί σωστά, πρέπει να συνδεθείτε με το κείμενο ή την αναφορά κελιού με μπαλαντέρ, αντιγράψτε ή εισαγάγετε τον ακόλουθο τύπο σε ένα κενό κελί:

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

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

:

  • 1. Στον παραπάνω τύπο, Ε2 & "*" είναι η τιμή αναζήτησης, η τιμή σε E2 και το * μπαλαντέρ ("*" δηλώνει οποιονδήποτε χαρακτήρα ή χαρακτήρες), Α2: Γ11 είναι το εύρος αναζήτησης, ο αριθμός 3 τη στήλη που περιέχει την τιμή για επιστροφή.
  • 2. Vlookup κατά τη χρήση μπαλαντέρ, πρέπει να ορίσετε τη λειτουργία ακριβούς αντιστοίχισης με FALSE ή 0 για το τελευταίο όρισμα στη λειτουργία Vlookup.

Συμβουλές:

1. Βρείτε και επιστρέψτε τις αντίστοιχες τιμές που τελειώνουν με μια συγκεκριμένη τιμή, εφαρμόστε αυτόν τον τύπο: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Για να αναζητήσετε και να επιστρέψετε την αντιστοιχισμένη τιμή με βάση μέρος της συμβολοσειράς κειμένου, ανεξάρτητα από το αν το καθορισμένο κείμενο βρίσκεται μπροστά, πίσω ή στη μέση της συμβολοσειράς κειμένου, πρέπει απλώς να ενώσετε δύο * χαρακτήρες γύρω από την αναφορά κελιού ή το κείμενο. Κάντε με αυτόν τον τύπο: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Τιμές Vlookup από άλλο φύλλο εργασίας

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

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

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

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

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

Σημείωση: Στον παραπάνω τύπο:

  • A2 αντιπροσωπεύει την τιμή αναζήτησης.
  • Φύλλο δεδομένων είναι το όνομα του φύλλου εργασίας από το οποίο θέλετε να αναζητήσετε δεδομένα, (Εάν το όνομα φύλλου περιέχει χαρακτήρες διαστήματος ή σημεία στίξης, θα πρέπει να επισυνάψετε μεμονωμένα εισαγωγικά γύρω από το όνομα του φύλλου, διαφορετικά, μπορείτε να χρησιμοποιήσετε απευθείας το όνομα φύλλου όπως = VLOOKUP (A2, Φύλλο δεδομένων! $ A $ 2: $ C $ 15,3,0));
  • Α2: Γ15 είναι το εύρος δεδομένων στο Φύλλο δεδομένων όπου αναζητούμε δεδομένα.
  • ο αριθμός 3 είναι ο αριθμός στήλης που περιέχει αντιστοιχισμένα δεδομένα από τα οποία θέλετε να επιστρέψετε.

8. Τιμές Vlookup από άλλο βιβλίο εργασίας

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

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

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

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

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

:

  • 1. Στον παραπάνω τύπο:
    B2 αντιπροσωπεύει την τιμή αναζήτησης.
    [Λίστα προϊόντων.xlsx] Φύλλο1 είναι το όνομα του βιβλίου εργασίας και του φύλλου εργασίας από το οποίο θέλετε να αναζητήσετε δεδομένα, (Η αναφορά στο βιβλίο εργασίας περικλείεται σε αγκύλες και ολόκληρο το φύλλο εργασίας + φύλλο περικλείεται σε εισαγωγικά);
    Α2: Β6 είναι το εύρος δεδομένων στο φύλλο εργασίας ενός άλλου βιβλίου εργασίας όπου αναζητούμε δεδομένα ·
    ο αριθμός 2 είναι ο αριθμός στήλης που περιέχει αντιστοιχισμένα δεδομένα από τα οποία θέλετε να επιστρέψετε.
  • 2. Εάν το βιβλίο εργασίας αναζήτησης είναι κλειστό, η πλήρης διαδρομή αρχείου για το βιβλίο εργασίας αναζήτησης θα εμφανίζεται στον τύπο όπως φαίνεται το ακόλουθο στιγμιότυπο οθόνης:

9. Vlookup και επιστροφή κενού ή συγκεκριμένου κειμένου αντί για τιμή σφάλματος 0 ή # N / A

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


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

1. Αμφίδρομη αναζήτηση με λειτουργία Vlookup (Vlookup σε σειρά και στήλη)

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

Τύπος 1: Χρήση συναρτήσεων VLOOKUP και MATCH

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

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Σημείωση: Στον παραπάνω τύπο:

  • H1: η τιμή αναζήτησης στη στήλη στην οποία θέλετε να λάβετε την αντίστοιχη τιμή,
  • Α2: Ε6: το εύρος δεδομένων συμπεριλαμβανομένων των κεφαλίδων σειράς.
  • H2: η τιμή αναζήτησης στη σειρά στην οποία θέλετε να λάβετε την αντίστοιχη τιμή,
  • Α1: Ε1: τα κελιά των κεφαλίδων στηλών.

Τύπος 2: Χρήση συναρτήσεων INDEX και MATCH

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

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Σημείωση: Στον παραπάνω τύπο:

  • Β2: Ε6: το εύρος δεδομένων για την επιστροφή του αντιστοιχισμένου στοιχείου από?
  • H1: η τιμή αναζήτησης στη στήλη στην οποία θέλετε να λάβετε την αντίστοιχη τιμή,
  • A2: A6: οι κεφαλίδες σειράς περιέχουν το προϊόν που θέλετε να αναζητήσετε.
  • H2: η τιμή αναζήτησης στη σειρά στην οποία θέλετε να λάβετε την αντίστοιχη τιμή,
  • Β1: Ε1: οι κεφαλίδες στηλών περιέχουν το τέταρτο που θέλετε να αναζητήσετε.

2. Η τιμή αντιστοίχισης Vlookup βασίζεται σε δύο ή περισσότερα κριτήρια

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

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

Τύπος 1: Χρήση της συνάρτησης LOOKUP

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

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

:

  • 1. Στον παραπάνω τύπο:
    A2: A12 = G1: σημαίνει αναζήτηση των κριτηρίων του G1 στο εύρος A2: A12;
    B2: B12 = G2: σημαίνει αναζήτηση των κριτηρίων του G2 στο εύρος B2: B12;
    D2: D12: το εύρος που θέλετε να επιστρέψετε την αντίστοιχη τιμή.
  • 2. Εάν έχετε περισσότερα από δύο κριτήρια, απλά πρέπει να ενσωματώσετε τα άλλα κριτήρια στον τύπο, όπως: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Τύπος 2: Χρήση συναρτήσεων INDEXT και MATCH

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

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

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

:

  • 1. Στον παραπάνω τύπο:
    A2: A12 = G1: σημαίνει αναζήτηση των κριτηρίων του G1 στο εύρος A2: A12;
    B2: B12 = G2: σημαίνει αναζήτηση των κριτηρίων του G2 στο εύρος B2: B12;
    D2: D12: το εύρος που θέλετε να επιστρέψετε την αντίστοιχη τιμή.
  • 2. Εάν έχετε περισσότερα από δύο κριτήρια, απλά πρέπει να ενσωματώσετε τα νέα κριτήρια στον τύπο, όπως: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup για επιστροφή πολλαπλών τιμών αντιστοίχισης με μία ή περισσότερες συνθήκες

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

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

Προβάλετε όλες τις τιμές που ταιριάζουν με βάση μια συνθήκη οριζόντια:

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

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Note: m είναι ο αριθμός σειράς του πρώτου κελιού στο εύρος επιστροφής μείον 1.
      n είναι ο αριθμός στήλης του πρώτου τύπου κελιού μείον 1.

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

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

Συμβουλές:

Εάν υπάρχουν διπλές αντιστοιχισμένες τιμές στη λίστα που επιστρέφεται, για να αγνοήσετε τα διπλότυπα, χρησιμοποιήστε αυτούς τους τύπους και, στη συνέχεια, πατήστε εισάγετε για να λάβετε το πρώτο αποτέλεσμα: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Εισαγάγετε αυτόν τον τύπο: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") σε ένα κελί δίπλα στο πρώτο αποτέλεσμα και, στη συνέχεια, πατήστε Ctrl + Shift + Εισαγωγή για να λάβετε το δεύτερο αποτέλεσμα και, στη συνέχεια, σύρετε αυτόν τον τύπο στα δεξιά κελιά για να λάβετε όλες τις άλλες αντιστοιχισμένες τιμές έως ότου εμφανιστεί κενό κελί, δείτε το στιγμιότυπο οθόνης


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

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

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Note: m είναι ο αριθμός σειράς του πρώτου κελιού στο εύρος επιστροφής μείον 1.
      n είναι ο αριθμός στήλης του πρώτου τύπου κελιού μείον 1.

1. Εφαρμόστε τον ακόλουθο τύπο σε ένα κενό κελί όπου θέλετε να εξάγετε το αποτέλεσμα:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

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

Note: Για περισσότερα κριτήρια, απλώς πρέπει να εντάξετε το lookup_value και το lookup_range στον τύπο, όπως: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


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

Προβάλετε όλες τις τιμές που ταιριάζουν με βάση μία κατάσταση κάθετα:

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

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Note: m είναι ο αριθμός σειράς του πρώτου κελιού στο εύρος επιστροφής μείον 1.
      n είναι ο αριθμός σειράς του πρώτου τύπου κελιού μείον 1.

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

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

Συμβουλές:

Για να αγνοήσετε τα αντίγραφα στις επιστρεφόμενες τιμές αντιστοίχισης, χρησιμοποιήστε αυτούς τους τύπους: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

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


Δείτε όλες τις τιμές που ταιριάζουν με βάση δύο ή περισσότερες συνθήκες κάθετα:

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

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Note: m είναι ο αριθμός σειράς του πρώτου κελιού στο εύρος επιστροφής μείον 1.
      n είναι ο αριθμός σειράς του πρώτου τύπου κελιού μείον 1.

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

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

Note: Για περισσότερα κριτήρια, απλώς πρέπει να εντάξετε το lookup_value και το lookup_range στον τύπο, όπως: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


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

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

Προβλέψτε όλες τις αντίστοιχες τιμές με βάση μια συνθήκη σε ένα κελί:

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

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Συμβουλές:

Για να αγνοήσετε τα αντίγραφα στις επιστρεφόμενες τιμές αντιστοίχισης, χρησιμοποιήστε αυτούς τους τύπους: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Δείτε όλες τις τιμές που ταιριάζουν με βάση δύο ή περισσότερες συνθήκες σε ένα κελί:

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

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

:

1. Η συνάρτηση TEXTJOIN είναι διαθέσιμη μόνο στο Excel 2019 και στο Office 365.

2. Εάν χρησιμοποιείτε το Excel 2016 και παλαιότερες εκδόσεις, χρησιμοποιήστε τη λειτουργία που καθορίζεται από τον χρήστη των παρακάτω άρθρων:


4. Vlookup για να επιστρέψετε ολόκληρη ή ολόκληρη τη σειρά ενός αντιστοιχισμένου κελιού

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

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

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

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

Note: Στον παραπάνω τύπο, F2 είναι η τιμή αναζήτησης στην οποία θέλετε να επιστρέψετε ολόκληρη τη σειρά, Α1: Δ12 είναι το εύρος δεδομένων που θέλετε να χρησιμοποιήσετε, A1 υποδεικνύει τον πρώτο αριθμό στήλης εντός του εύρους δεδομένων σας.

Συμβουλές:

Εάν βρέθηκαν πολλές σειρές με βάση την αντιστοιχισμένη τιμή, για να επιστρέψετε όλες τις αντίστοιχες σειρές, εφαρμόστε αυτόν τον τύπο: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), και στη συνέχεια πατήστε Ctrl + Shift + Εισαγωγή πλήκτρα μαζί για να λάβετε το πρώτο αποτέλεσμα και, στη συνέχεια, σύρετε τη λαβή πλήρωσης απευθείας στα κελιά, δείτε το στιγμιότυπο οθόνης:

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


5. Κάντε πολλαπλή λειτουργία Vlookup (ένθετο Vlookup) στο Excel

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

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

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Σημείωση:

  • lookup_value: η τιμή που ψάχνετε;
  • Table1, Table2, Table3, ...: οι πίνακες στους οποίους υπάρχει η τιμή αναζήτησης και η τιμή επιστροφής.
  • διάσελο: ο αριθμός στήλης στον πίνακα από τον οποίο θέλετε να επιστρέψετε την αντίστοιχη τιμή.
  • 0: Χρησιμοποιείται για ακριβή αντιστοίχιση.

1. Εφαρμόστε τον ακόλουθο τύπο σε ένα κενό κελί όπου θέλετε να βάλετε το αποτέλεσμα:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

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

:

  • 1. Στον παραπάνω τύπο, J3 είναι η αξία που ψάχνετε? Α3: Β7, Δ3: Ε7, G3: Η7 είναι τα εύρη πινάκων στα οποία υπάρχει η τιμή αναζήτησης και η τιμή επιστροφής · Ο αριθμός 2 είναι ο αριθμός στήλης στο εύρος για να επιστρέψετε την αντίστοιχη τιμή από.
  • 2. Εάν δεν είναι δυνατή η εύρεση της τιμής αναζήτησης, εμφανίζεται μια τιμή σφάλματος, για να αντικαταστήσετε το σφάλμα με ένα αναγνώσιμο κείμενο, χρησιμοποιήστε αυτόν τον τύπο: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup για να ελέγξετε αν υπάρχει τιμή βάσει δεδομένων λίστας σε άλλη στήλη

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

1. Εφαρμόστε τον ακόλουθο τύπο σε ένα κενό κελί:

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

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

Note: Στον παραπάνω τύπο, C2 είναι η τιμή αναζήτησης που θέλετε να ελέγξετε. A2: A10 είναι η λίστα εύρους από όπου θα βρεθούν οι τιμές αναζήτησης. ο αριθμός 1 είναι ο αριθμός στήλης από τον οποίο θέλετε να λάβετε τιμή στο εύρος σας.


7. Προβλέψτε και αθροίστε όλες τις αντιστοιχισμένες τιμές σε σειρές ή στήλες

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

Προβλέψτε και αθροίστε όλες τις αντίστοιχες τιμές σε μια σειρά ή πολλές σειρές

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

Προβλέψτε και αθροίστε τις πρώτες αντιστοιχισμένες τιμές στη σειρά:

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

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

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

Note: Στον παραπάνω τύπο: H2 είναι το κελί που περιέχει την τιμή που αναζητάτε; Α2: F9 είναι το εύρος δεδομένων (χωρίς κεφαλίδες στηλών) που περιλαμβάνουν την τιμή αναζήτησης και τις αντίστοιχες τιμές · Ο αριθμός 2,3,4,5,6 {} είναι αριθμοί στηλών που χρησιμοποιούνται για τον υπολογισμό του συνόλου του εύρους.


Προβλέψτε και αθροίστε όλες τις αντιστοιχισμένες τιμές σε πολλές σειρές:

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

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

Note: Στον παραπάνω τύπο: H2 είναι η τιμή αναζήτησης που αναζητάτε. A2: A9 είναι οι κεφαλίδες σειράς που περιέχουν την τιμή αναζήτησης. B2: F9 το εύρος δεδομένων των αριθμητικών τιμών που θέλετε να αθροίσετε.


Προβλέψτε και αθροίστε όλες τις αντίστοιχες τιμές σε μια στήλη ή πολλές στήλες

Προβλέψτε και αθροίστε τις πρώτες αντιστοιχισμένες τιμές σε μια στήλη:

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

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

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

Note: Στον παραπάνω τύπο: H2 είναι η τιμή αναζήτησης που αναζητάτε. B1: F1 είναι οι κεφαλίδες στηλών που περιέχουν την τιμή αναζήτησης. B2: F9 το εύρος δεδομένων των αριθμητικών τιμών που θέλετε να αθροίσετε.


Προβλέψτε και αθροίστε όλες τις αντιστοιχισμένες τιμές σε πολλές στήλες:

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

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

Note: Στον παραπάνω τύπο: H2 είναι η τιμή αναζήτησης που αναζητάτε. B1: F1 είναι οι κεφαλίδες στηλών που περιέχουν την τιμή αναζήτησης. B2: F9 το εύρος δεδομένων των αριθμητικών τιμών που θέλετε να αθροίσετε.


Vlookup και αθροίστε τις πρώτες ή όλες τις αντιστοιχισμένες τιμές με ένα ισχυρό χαρακτηριστικό

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


Προβλέψτε και αθροίστε όλες τις αντίστοιχες τιμές τόσο σε σειρές όσο και σε στήλες

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

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

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

Note: Στον παραπάνω τύπο: B2: F9 είναι το εύρος δεδομένων των αριθμητικών τιμών που θέλετε να αθροίσετε · B1: F1 is Οι κεφαλίδες στηλών περιέχουν την τιμή αναζήτησης στην οποία θέλετε να αθροίσετε βάσει. I2 είναι η τιμή αναζήτησης στις κεφαλίδες στηλών που αναζητάτε. A2: A9 είναι οι κεφαλίδες σειράς που περιέχουν την τιμή αναζήτησης στην οποία θέλετε να αθροίσετε με βάση; H2 είναι η τιμή αναζήτησης στις κεφαλίδες σειράς που αναζητάτε.


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

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

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

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

Τύπος 1: Χρήση της λειτουργίας VLOOKUP

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

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Note: Στον παραπάνω τύπο, A2 είναι η αξία που ψάχνετε, Ε2: F8 είναι ο πίνακας προς αναζήτηση, ο αριθμός 2 είναι ο αριθμός στήλης στον πίνακα από τον οποίο μπορείτε να ανακτήσετε την τιμή.

Τύπος 2: Χρήση συναρτήσεων INDEX και MATCH

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

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

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

Note: Στον παραπάνω τύπο, A2 είναι η τιμή αναζήτησης που ψάχνετε, E2: E8 είναι ένα εύρος δεδομένων που θέλετε να επιστρέψετε, F2: F8 είναι εύρος αναζήτησης που περιέχει την τιμή αναζήτησης.


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

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

Ο γενικός τύπος για τη συγχώνευση δύο πινάκων με βάση πολλές στήλες κλειδιών είναι:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

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

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

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

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

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

9. Τιμές αντιστοίχισης Vlookup σε πολλά φύλλα εργασίας

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


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

1. Vlookup για λήψη μορφοποίησης κελιού (χρώμα κελιού, χρώμα γραμματοσειράς) μαζί με την τιμή αναζήτησης

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

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

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

2. Στο άνοιγμα Microsoft Visual Basic για εφαρμογές παράθυρο, αντιγράψτε παρακάτω τον κώδικα VBA στο παράθυρο Κωδικός.

Κωδικός VBA 1: Vlookup για λήψη μορφοποίησης κελιού μαζί με τιμή αναζήτησης

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

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

Κωδικός VBA 2: Vlookup για λήψη μορφοποίησης κελιού μαζί με τιμή αναζήτησης

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

5. Στη συνέχεια, κάντε κλικ στο κουμπί OK για να κλείσετε το παράθυρο διαλόγου και, στη συνέχεια, αποθηκεύστε και κλείστε το παράθυρο κώδικα, τώρα, επιστρέψτε το φύλλο εργασίας και, στη συνέχεια, εφαρμόστε αυτόν τον τύπο: =LookupKeepFormat(E2,$A$1:$C$10,3) σε ένα κενό κελί όπου θέλετε να εξάγετε το αποτέλεσμα και, στη συνέχεια, πατήστε το πλήκτρο Enter. Δείτε το στιγμιότυπο οθόνης:

Note: Στον παραπάνω τύπο, E2 είναι η αξία που θα αναζητήσετε, Α1: Γ10 είναι το εύρος του πίνακα και ο αριθμός 3 είναι ο αριθμός στήλης του πίνακα στον οποίο θέλετε να επιστραφεί η αντιστοιχισμένη τιμή.

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


2. Διατηρήστε τη μορφή ημερομηνίας από μια επιστρεφόμενη τιμή Vlookup

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

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

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

Note: Στον παραπάνω τύπο, E2 είναι η τιμή εμφάνισης, Α2: Γ9 είναι το εύρος αναζήτησης, ο αριθμός 3 είναι ο αριθμός στήλης που θέλετε να επιστραφεί η τιμή, mm/dd/yyy είναι η μορφή ημερομηνίας που θέλετε να διατηρήσετε.


3. Vlookup και απόδοση αντιστοίχισης με σχόλιο κελιού

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

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. Στη συνέχεια, αποθηκεύστε και κλείστε το παράθυρο κώδικα, εισαγάγετε αυτόν τον τύπο: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) σε ένα κενό κελί για να εντοπίσετε το αποτέλεσμα και, στη συνέχεια, σύρετε τη λαβή πλήρωσης για να αντιγράψετε αυτόν τον τύπο σε άλλα κελιά, τώρα, οι αντίστοιχες τιμές καθώς και τα σχόλια επιστρέφονται ταυτόχρονα, δείτε το στιγμιότυπο οθόνης:

Note: Στον παραπάνω τύπο, D2 είναι η τιμή αναζήτησης που θέλετε να επιστρέψετε την αντίστοιχη τιμή της, Α2: Β9 είναι ο πίνακας δεδομένων που θέλετε να χρησιμοποιήσετε, ο αριθμός 2 είναι ο αριθμός στήλης που περιέχει την αντιστοιχισμένη τιμή που θέλετε να επιστρέψετε.


4. Αντιμετωπίστε το κείμενο και τους πραγματικούς αριθμούς στο Vlookup

Για παράδειγμα, έχω μια σειρά δεδομένων, ο αριθμός ταυτότητας στον αρχικό πίνακα είναι μορφή αριθμού, στο κελί αναζήτησης που αποθηκεύεται ως κείμενο, κατά την εφαρμογή της κανονικής λειτουργίας Vlookup, εμφανίζεται ένα αποτέλεσμα σφάλματος # N / A όπως φαίνεται παρακάτω απεικονίζεται. Σε αυτήν την περίπτωση, πώς θα μπορούσατε να λάβετε τις σωστές πληροφορίες εάν ο αριθμός αναζήτησης και ο αρχικός αριθμός στον πίνακα έχουν τη διαφορετική μορφή δεδομένων;

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

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

:

  • 1. Στον παραπάνω τύπο, D2 είναι η τιμή αναζήτησης που θέλετε να επιστρέψετε την αντίστοιχη τιμή της, Α2: Β8 είναι ο πίνακας δεδομένων που θέλετε να χρησιμοποιήσετε, ο αριθμός 2 είναι ο αριθμός στήλης που περιέχει την αντιστοιχισμένη τιμή που θέλετε να επιστρέψετε.
  • 2. Αυτός ο τύπος λειτουργεί επίσης καλά εάν δεν είστε σίγουροι πού έχετε αριθμούς και πού έχετε κείμενο.

Λήψη δειγμάτων αρχείων VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Super Formula Bar (επεξεργαστείτε εύκολα πολλές γραμμές κειμένου και τύπου). Διάταξη ανάγνωσης (εύκολη ανάγνωση και επεξεργασία μεγάλου αριθμού κελιών). Επικόλληση σε φιλτραρισμένο εύρος...
  • Συγχώνευση κελιών / σειρών / στηλών και τήρηση δεδομένων · Περιεχόμενο διαχωρισμού κελιών Συνδυάστε διπλές σειρές και άθροισμα / μέσος όρος... Αποτροπή διπλών κυττάρων; Συγκρίνετε τα εύρη...
  • Επιλέξτε Διπλότυπο ή Μοναδικό Σειρές; Επιλέξτε Κενές σειρές (όλα τα κελιά είναι κενά). Σούπερ εύρεση και ασαφής εύρεση σε πολλά βιβλία εργασίας. Τυχαία επιλογή ...
  • Ακριβές αντίγραφο Πολλαπλά κελιά χωρίς αλλαγή της αναφοράς τύπου. Αυτόματη δημιουργία αναφορών σε πολλαπλά φύλλα? Εισαγωγή κουκκίδων, Πλαίσια ελέγχου και άλλα ...
  • Αγαπημένα και γρήγορη εισαγωγή τύπων, Σειρά, Διαγράμματα και Εικόνες; Κρυπτογράφηση κυττάρων με κωδικό πρόσβασης Δημιουργία λίστας αλληλογραφίας και στείλτε email ...
  • Εξαγωγή κειμένου, Προσθήκη κειμένου, Κατάργηση κατά θέση, Αφαιρέστε το διάστημα; Δημιουργία και εκτύπωση υποσύνολων σελιδοποίησης. Μετατροπή περιεχομένου και σχολίων μεταξύ κελιών...
  • Σούπερ φίλτρο (αποθηκεύστε και εφαρμόστε σχήματα φίλτρων σε άλλα φύλλα). Προηγμένη ταξινόμηση ανά μήνα / εβδομάδα / ημέρα, συχνότητα και άλλα. Ειδικό φίλτρο με έντονη, πλάγια ...
  • Συνδυάστε βιβλία εργασίας και φύλλα εργασίας; Συγχώνευση πινάκων βάσει βασικών στηλών. Διαχωρίστε τα δεδομένα σε πολλά φύλλα; Μαζική μετατροπή xls, xlsx και PDF...
  • Ομαδοποίηση συγκεντρωτικού πίνακα κατά αριθμός εβδομάδας, ημέρα εβδομάδας και πολλά άλλα ... Εμφάνιση ξεκλειδωμένων, κλειδωμένων κελιών με διαφορετικά χρώματα. Επισημάνετε τα κελιά που έχουν τύπο / όνομα...
kte καρτέλα 201905
  • Ενεργοποίηση επεξεργασίας και ανάγνωσης καρτελών σε Word, Excel, PowerPoint, Publisher, Access, Visio και Project.
  • Ανοίξτε και δημιουργήστε πολλά έγγραφα σε νέες καρτέλες του ίδιου παραθύρου και όχι σε νέα παράθυρα.
  • Αυξάνει την παραγωγικότητά σας κατά 50% και μειώνει εκατοντάδες κλικ ποντικιού για εσάς κάθε μέρα!
κάτω μέρος γραφείου
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.