Μετάβαση στο κύριο περιεχόμενο

20+ Παραδείγματα VLOOKUP για αρχάριους και προχωρημένους χρήστες του Excel

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


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

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

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

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

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

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

Πίνακας_συστοιχίας (απαιτείται): Το εύρος δεδομένων ή ο πίνακας όπου βρίσκεται η στήλη τιμής αναζήτησης και η στήλη τιμής αποτελέσματος.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • F2 είναι το κελί που περιέχει την τιμή C1005 που θέλετε να αναζητήσετε.
  • Α2: Δ7 είναι ο πίνακας στον οποίο εκτελείτε την αναζήτηση.
  • 3 είναι ο αριθμός στήλης από τον οποίο επιστρέφεται η αντιστοιχισμένη τιμή σας. (Μόλις η συνάρτηση εντοπίσει το ID - C1005, θα μεταβεί στην τρίτη στήλη του πίνακα πίνακα και θα επιστρέψει τις τιμές στην ίδια σειρά με αυτή του ID - C1005. )
  • ΨΕΥΔΗΣ αναφέρεται στην ακριβή αντιστοίχιση.

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

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

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

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

Σημείωση: Εάν η τιμή αναζήτησης δεν βρεθεί στην αριστερή στήλη, επιστρέφει ένα σφάλμα #N/A.
Δημοφιλή χαρακτηριστικά: Εύρεση, επισήμανση ή αναγνώριση διπλότυπων   |  Διαγραφή κενών γραμμών   |  Συνδυάστε στήλες ή κελιά χωρίς απώλεια δεδομένων   |   Γύρος χωρίς φόρμουλα ...
Σούπερ Αναζήτηση: VLookup πολλαπλών κριτηρίων  |   VLookup πολλαπλών τιμών  |   VLookup σε πολλά φύλλα   |   Ασαφής αναζήτηση ...
Σύνθετη αναπτυσσόμενη λίστα: Γρήγορη δημιουργία αναπτυσσόμενης λίστας   |  Εξαρτημένη αναπτυσσόμενη λίστα   |  Πολλαπλή αναπτυσσόμενη λίστα ...
Διαχειριστής στήλης: Προσθήκη συγκεκριμένου αριθμού στηλών  |  Μετακίνηση στηλών   |  Αναίρεση απόκρυψης στηλών  |  Συγκρίνετε εύρη και στήλες ...
Επιλεγμένα Χαρακτηριστικά: Εστίαση πλέγματος   |  Προβολή σχεδίου   |   Μεγάλη Formula Bar   |  Διαχείριση βιβλίου εργασίας & φύλλου  |  Βιβλιοθήκη πόρων   |  Επιλογή ημερομηνίας  |  Συνδυάστε φύλλα εργασίας   |  Κρυπτογράφηση/Αποκρυπτογράφηση κελιών    Αποστολή email ανά λίστα   |  Σούπερ φίλτρο   |   Ειδικό φίλτρο (με έντονη/πλάγια γραφή...) ...
Top 15 σετ εργαλείων12 Κείμενο Εργαλεία (Προσθήκη κειμένου, Κατάργηση χαρακτήρων, ...)   |   50 + Διάγραμμα Τύποι (Gantt διάγραμμα, ...)   |   40+ Πρακτικό ΜΑΘΗΜΑΤΙΚΟΙ τυποι (Υπολογίστε την ηλικία με βάση τα γενέθλια, ...)   |   19 Εισαγωγή Εργαλεία (Εισαγωγή κωδικού QR, Εισαγωγή εικόνας από το μονοπάτι, ...)   |   12 Μετατροπή Εργαλεία (Αριθμοί σε λέξεις, Μετατροπή Συναλλάγματος, ...)   |   7 Συγχώνευση & διαχωρισμός Εργαλεία (Σύνθετες σειρές συνδυασμού, Διαίρεση κελιών, ...)   |   Πολλά περισσότερα...

Το Kutools για το Excel διαθέτει πάνω από 300 δυνατότητες, Διασφάλιση ότι αυτό που χρειάζεστε είναι μόνο ένα κλικ μακριά...

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

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

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

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

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

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

Αποτέλεσμα:

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

:

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

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)

Αποτέλεσμα:

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

:

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

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))

Αποτέλεσμα:

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

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

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

Αποτέλεσμα:

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

:

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

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

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


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

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

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

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

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

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

Αποτέλεσμα:

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

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

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

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


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

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

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

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

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

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

Αποτέλεσμα:

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

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

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

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

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

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

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

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

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

Αποτέλεσμα:

:

  • Στον παραπάνω τύπο:
    • B2 αντιπροσωπεύει την τιμή αναζήτησης.
    • '[Λίστα προϊόντων.xlsx]Φύλλο1'!A2:B6 υποδεικνύει αναζήτηση από την περιοχή A2:B6 στο φύλλο με το όνομα Sheet1 από τη λίστα προϊόντων του βιβλίου εργασίας. (Η αναφορά στο βιβλίο εργασίας περικλείεται σε αγκύλες και ολόκληρο το βιβλίο εργασίας + το φύλλο περικλείεται σε μονά εισαγωγικά.)
    • 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)

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

  • G2 είναι η τιμή αναζήτησης στη στήλη στην οποία θέλετε να λάβετε την αντίστοιχη τιμή με βάση.
  • Α2: Ε7 είναι ο πίνακας δεδομένων από τον οποίο θα κοιτάξετε.
  • H1 είναι η τιμή αναζήτησης στη σειρά στην οποία θέλετε να λάβετε την αντίστοιχη τιμή με βάση.
  • Α2: Ε2 είναι τα κελιά των κεφαλίδων στηλών.
  • ΨΕΥΔΗΣ υποδεικνύει να λάβετε μια ακριβή αντιστοίχιση.

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))

Αποτέλεσμα:

:

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

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

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


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)

Αποτέλεσμα:

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

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

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

Συμβουλές:

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

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

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

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

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

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

Σημείωση:

  • lookup_value είναι η αξία που ψάχνετε?
  • Πίνακας_συστοιχίας1, Πίνακας_συστοιχίας2 είναι οι πίνακες στους οποίους υπάρχει η τιμή αναζήτησης και η τιμή επιστροφής.
  • col_index_num1 υποδεικνύει τον αριθμό της στήλης στον πρώτο πίνακα για την εύρεση των ενδιάμεσων κοινών δεδομένων.
  • col_index_num2 υποδεικνύει τον αριθμό της στήλης στον δεύτερο πίνακα που θέλετε να επιστρέψετε την αντίστοιχη τιμή.
  • 0 χρησιμοποιείται για ακριβή αντιστοίχιση.

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

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

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

Αποτέλεσμα:

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

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

  • G3 περιέχει την τιμή που αναζητάτε.
  • Α3: Β7, Δ3: Ε7 είναι οι περιοχές του πίνακα στις οποίες υπάρχουν η τιμή αναζήτησης και η τιμή επιστροφής.
  • 2 είναι ο αριθμός στήλης στο εύρος από το οποίο επιστρέφεται η αντίστοιχη τιμή.
  • 0 υποδεικνύει VLOOKUP ακριβή μαθηματικά.

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

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

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

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

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

Αποτέλεσμα:

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

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

  • C2 είναι η τιμή αναζήτησης που θέλετε να ελέγξετε.
  • A2: A10 είναι η λίστα εύρους από όπου μπορείτε να ελέγξετε εάν θα βρεθούν οι τιμές αναζήτησης ή όχι.
  • ΨΕΥΔΗΣ υποδεικνύει να λάβετε μια ακριβή αντιστοίχιση.

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

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

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

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

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

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

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

Αποτέλεσμα:

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

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

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

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

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 VLOOKUP και άθροιση όλων των αντιστοιχισμένων τιμών σε μια στήλη ή πολλές στήλες

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

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

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

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

Αποτέλεσμα:

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

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

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

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

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 VLOOKUP και άθροιση των πρώτων αντιστοιχισμένων ή όλων των τιμών με ένα ισχυρό χαρακτηριστικό

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

  1. Πατήστε Kutools > Σούπερ LOOKUP > LOOKUP και άθροισμα για να ενεργοποιήσετε αυτήν τη λειτουργία.
  2. Στη συνέχεια, καθορίστε τις λειτουργίες από το πλαίσιο διαλόγου με βάση τις ανάγκες σας.
Note: Για να εφαρμόσετε αυτήν τη δυνατότητα, θα πρέπει να κάνετε λήψη Kutools για Excel με δωρεάν δοκιμή 30 ημερών Πρώτα.
 3.7.4 VLOOKUP και άθροιση όλων των αντιστοιχισμένων τιμών τόσο σε γραμμές όσο και σε στήλες

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

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

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

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

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

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

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

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

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

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

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

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

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

Αποτέλεσμα:

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

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

  • A2 είναι η τιμή αναζήτησης που αναζητάτε.
  • F2: F8 είναι το εύρος δεδομένων που θέλετε να επιστρέψετε τις τιμές που ταιριάζουν.
  • E2: E8 είναι εύρος αναζήτησης που περιέχει την τιμή αναζήτησης.
 3.8.2 VLOOKUP για συγχώνευση δύο πινάκων που βασίζονται σε πολλαπλές στήλες κλειδιών

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

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

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

Σημείωση:

  • πίνακας αναζήτησης είναι το εύρος δεδομένων που περιέχει τα δεδομένα αναζήτησης και τις αντίστοιχες εγγραφές.
  • αναζήτηση_τιμής1 είναι το πρώτο κριτήριο που ψάχνεις.
  • αναζήτηση_εύρος 1 είναι η λίστα δεδομένων περιέχει τα πρώτα κριτήρια?
  • αναζήτηση_τιμής2 είναι το δεύτερο κριτήριο που ψάχνετε?
  • αναζήτηση_εύρος 2 είναι η λίστα δεδομένων περιέχει το δεύτερο κριτήριο?
  • Επιστροφή_αριθμός_στήλης υποδεικνύει τον αριθμό της στήλης στον πίνακα lookup_table που θέλετε να επιστρέψετε την αντίστοιχη τιμή.

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

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

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

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

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

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

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
    

Βήμα 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
    

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

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

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

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

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

  • E2 είναι η τιμή που θα αναζητήσετε.
  • Α1: Γ10 είναι το εύρος του πίνακα.
  • 3 είναι ο αριθμός στήλης του πίνακα από τον οποίο θέλετε να ανακτήσετε την αντιστοιχισμένη τιμή.

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

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

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

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

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

Αποτέλεσμα:

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

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

  • E2 είναι η τιμή αναζήτησης.
  • Α2: Γ9 είναι το εύρος αναζήτησης.
  • 3 είναι ο αριθμός στήλης που θέλετε να επιστραφεί η τιμή.
  • ΨΕΥΔΗΣ υποδεικνύει να λάβετε μια ακριβή αντιστοίχιση.
  • mm/dd/yyy είναι η μορφή ημερομηνίας που θέλετε να διατηρήσετε.

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)

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

  • D2 είναι η τιμή αναζήτησης που θέλετε να επιστρέψετε την αντίστοιχη τιμή της.
  • Α2: Β9 είναι ο πίνακας δεδομένων που θέλετε να χρησιμοποιήσετε.
  • 2 είναι ο αριθμός της στήλης που περιέχει την αντιστοιχισμένη τιμή που θέλετε να επιστρέψετε.
  • ΨΕΥΔΗΣ υποδεικνύει να λάβετε μια ακριβή αντιστοίχιση.

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))

Αποτέλεσμα:

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

:

  • Στον παραπάνω τύπο:
    • D2 είναι η τιμή αναζήτησης που θέλετε να επιστρέψετε την αντίστοιχη τιμή της.
    • Α2: Β8 είναι ο πίνακας δεδομένων που θέλετε να χρησιμοποιήσετε.
    • 2 είναι ο αριθμός της στήλης που περιέχει την αντιστοιχισμένη τιμή που θέλετε να επιστρέψετε.
    • 0 υποδεικνύει να λάβετε μια ακριβή αντιστοίχιση.
  • Αυτός ο τύπος λειτουργεί επίσης καλά εάν δεν είστε σίγουροι πού έχετε αριθμούς και πού έχετε κείμενο.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

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