Το Vlookup είναι μια λειτουργία φύλλου εργασίας στο excel, αλλά μπορεί επίσης να χρησιμοποιηθεί σε VBA, η λειτουργικότητα του Vlookup είναι παρόμοια με τη λειτουργικότητα στο VBA και στο φύλλο εργασίας και τα δύο, καθώς είναι μια συνάρτηση φύλλου εργασίας, η μέθοδος χρήσης του Vlookup στο VBA είναι μέσω του Application.WorksheetFunction μέθοδος και τα επιχειρήματα παραμένουν τα ίδια.
Λειτουργία VLOOKUP στο Excel VBA
Η συνάρτηση VLOOKUP στο Excel χρησιμοποιείται για την αναζήτηση μιας τιμής σε έναν πίνακα και την επιστροφή της αντίστοιχης τιμής από άλλη στήλη. Η τιμή προς αναζήτηση πρέπει να υπάρχει στην πρώτη στήλη. Απαιτείται επίσης να αναφερθεί εάν θα αναζητηθεί ακριβής αντιστοίχιση ή κατά προσέγγιση αντιστοιχία. Η συνάρτηση VLOOKUP του φύλλου εργασίας μπορεί να χρησιμοποιηθεί στην κωδικοποίηση VBA. Η συνάρτηση δεν είναι ενσωματωμένη στο VBA και ως εκ τούτου μπορεί να καλέσει μόνο χρησιμοποιώντας το φύλλο εργασίας.
Η συνάρτηση VLOOKUP στο Excel έχει την ακόλουθη σύνταξη:
Στην οποία, lookup_value είναι η τιμή που πρέπει να αναζητήσετε, table_arrray είναι ο πίνακας, το col_index_num είναι ο αριθμός στήλης της τιμής επιστροφής, το range_lookup υποδηλώνει εάν η αντιστοίχιση είναι ακριβής ή κατά προσέγγιση. range_lookup μπορεί να είναι TRUE / FALSE ή 0/1.
Στον κώδικα VBA, η συνάρτηση VLOOKUP μπορεί να χρησιμοποιηθεί ως:
Application.WorksheetFunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Πώς να χρησιμοποιήσετε το VLookup στο Excel VBA;
Ακολουθούν μερικά παραδείγματα του κώδικα VLookup στο Excel VBA.
Κωδικός VLookup στο Excel VBA Παράδειγμα # 1
Ας δούμε πώς μπορούμε να καλέσουμε τη συνάρτηση φύλλου εργασίας VLOOKUP στο Excel VBA.
Ας υποθέσουμε ότι τα δεδομένα σχετικά με το αναγνωριστικό, το όνομα και τους μέσους βαθμούς που έλαβαν οι μαθητές.
Τώρα, θέλετε να αναζητήσετε τα σημάδια που αποκτήθηκαν από έναν μαθητή με αναγνωριστικό 11004.
Για να αναζητήσετε την τιμή, ακολουθήστε τα παρακάτω βήματα:
- Μεταβείτε στην καρτέλα προγραμματιστή και κάντε κλικ στην Visual Basic.
- Κάτω από το παράθυρο VBA, μεταβείτε στο Insert και κάντε κλικ στο Module.
- Τώρα, γράψτε τον κωδικό VBA VLOOKUP. Μπορεί να χρησιμοποιηθεί ο ακόλουθος κωδικός VBA VLOOKUP.
Sub vlookup1 ()
Dim student_id As Long
Dim σημάδια As Long
student_id = 11004
Set myrange = Range (“B4: D8”)
marks = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
End Sub
Αρχικά, ορίστε ένα αναγνωριστικό μαθητή, το οποίο είναι η τιμή αναζήτησης. Ως εκ τούτου, ορίζουμε,
student_id = 11004
Στη συνέχεια, ορίζουμε το εύρος στο οποίο υπάρχει η τιμή και η τιμή επιστροφής. Δεδομένου ότι τα δεδομένα μας υπάρχουν στα κελιά B4: D8, ορίζουμε ένα εύρος εύρους ως:
Ορισμός myrange = Range ("B4: D8")
Τέλος, εισάγουμε τη συνάρτηση VLOOKUP χρησιμοποιώντας τη συνάρτηση φύλλου εργασίας σε μια μεταβλητή, επισημαίνει ως:
σημάδια = Application.WorksheetFunction.VLookup (student_id, myrange, 3, False)
Για να εκτυπώσετε τα σημάδια σε ένα πλαίσιο μηνυμάτων, ας χρησιμοποιήσουμε την ακόλουθη εντολή:
MsgBox "Μαθητής με αναγνωριστικό:" & student_id & "αποκτήθηκε"
Θα επιστρέψει:
Ένας μαθητής με αναγνωριστικό: 11004 έλαβε 85 βαθμούς.
Τώρα, κάντε κλικ στο κουμπί εκτέλεσης.
Θα παρατηρήσετε ότι θα εμφανιστεί ένα πλαίσιο μηνύματος στο φύλλο Excel.
Κωδικός VLookup στο Excel VBA Παράδειγμα # 2
Ας υποθέσουμε ότι έχετε τα στοιχεία για τα ονόματα των υπαλλήλων και τον μισθό τους. Σε αυτά τα δεδομένα δίνονται οι στήλες B και C. Τώρα, πρέπει να γράψετε έναν κωδικό VBA VLOOKUP έτσι ώστε με το όνομα του υπαλλήλου σε ένα κελί, F4, ο μισθός του υπαλλήλου θα επιστραφεί στο κελί G4.
Ας γράψουμε τον κωδικό VBA VLOOKUP.
- Ορίστε το εύρος στο οποίο υπάρχουν οι τιμές, δηλαδή, οι στήλες B και C.
Ορισμός myrange = Range ("B: C")
- Ορίστε το όνομα του υπαλλήλου και εισαγάγετε το όνομα από το κελί F4.
Ορισμός ονόματος = Εύρος ("F4")
- Ορίστε τον μισθό ως κελί G4.
Ορισμός μισθού = Εύρος ("G4")
- Τώρα, καλέστε τη συνάρτηση VLOOKUP χρησιμοποιώντας το WorksheetFunction στο VBA και εισαγάγετέ το σε μισθό. Αυτό θα επιστρέψει την τιμή (έξοδος της συνάρτησης Vlookup) στο κελί G4. Μπορεί να χρησιμοποιηθεί η ακόλουθη σύνταξη:
gaji.Value = Application.WorksheetFunction.VLookup (όνομα, myrange, 2, False)
- Τώρα, εκτελέστε τη λειτουργική μονάδα. Το κελί G4 θα περιέχει το μισθό του υπαλλήλου μετά την εκτέλεση του κωδικού VBA VLOOKUP.
Ας υποθέσουμε ότι αλλάζετε την τιμή του κελιού F4 σε "David" στο φύλλο εργασίας και εκτελέστε ξανά τον κωδικό και θα επιστρέψει τον μισθό του David.
Κωδικός VLookup στο Excel VBA Παράδειγμα # 3
Ας υποθέσουμε ότι έχετε τα στοιχεία του υπαλλήλου της εταιρείας σας, που έχουν το αναγνωριστικό, τα ονόματά τους, το Τμήμα και το μισθό τους. Χρησιμοποιώντας το Vlookup στο VBA, θέλετε να λάβετε τα στοιχεία του μισθού ενός υπαλλήλου χρησιμοποιώντας το όνομα και το τμήμα του.
Δεδομένου ότι η συνάρτηση vlookup στο excel αναζητά την τιμή lookup_value σε μία μόνο στήλη, η οποία είναι η πρώτη στήλη του πίνακα_array, απαιτείται πρώτα να δημιουργήσετε μια στήλη που περιέχει το "Όνομα" και το "Τμήμα" κάθε υπαλλήλου.
Στο VBA, ας εισαγάγουμε τις τιμές "Όνομα" και "Τμήμα" στη στήλη Β του φύλλου εργασίας.
Για να το κάνετε αυτό, μεταβείτε στην καρτέλα Προγραμματιστής και κάντε κλικ στην Visual Basic. Στη συνέχεια, μεταβείτε στο Insert και κάντε κλικ στο Module για να ξεκινήσετε μια νέα ενότητα.
Ας γράψουμε τώρα κώδικα, έτσι ώστε η στήλη Β να περιέχει τις τιμές της στήλης Δ (όνομα) και της στήλης Ε.
Η σύνταξη δίνεται ως:
Πρώτον, χρησιμοποιήστε ένα «υπέρ» βρόχου από i = 4 καθώς οι τιμές που ξεκινώντας από την 4 ου σειρά σε αυτή την περίπτωση. Ο βρόχος θα συνεχιστεί μέχρι το τέλος της τελευταίας σειράς της στήλης Γ. Έτσι, η μεταβλητή I μπορεί να χρησιμοποιηθεί ως αριθμός σειράς μέσα στο βρόχο «για».
Στη συνέχεια, εισαγάγετε την τιμή που θα αντιστοιχιστεί για το κελί (αριθμός_ευρώματος, στήλη Β), η οποία μπορεί να δοθεί ως κελιά (i, "B"). Τιμή, ως κελί (αριθμός_ευθείας, στήλη D) & "_" & κελί (αριθμός_αριθμός, στήλη E ).
Ας υποθέσουμε ότι θέλετε να αντιστοιχίσετε το κελί B5 = D5 & "_" & E5, μπορείτε απλά να χρησιμοποιήσετε τον κωδικό ως:
Κελιά (5, "B"). Τιμή = Κελιά (5, "D"). Τιμή & "_" & Κελιά (5, "E"). Τιμή
Τώρα, ας αναζητήσουμε την τιμή αναζήτησης στον πίνακα B5: E24. Πρέπει πρώτα να εισαγάγετε την τιμή αναζήτησης. Ας πάρουμε την τιμή (Όνομα και Τμήμα) από τον χρήστη. Για να το κάνω αυτό,
- ορίστε τρεις μεταβλητές, όνομα, τμήμα και lookup_val ως συμβολοσειρά.
- Πάρτε το όνομα από τον χρήστη. Χρησιμοποιήστε τον κωδικό:
name = InputBox ("Εισαγάγετε το όνομα του υπαλλήλου")
Το περιεχόμενο στο πλαίσιο εισαγωγής "Enter the…" θα εμφανίζεται στο πλαίσιο προτροπής όταν εκτελείτε τον κωδικό. Η συμβολοσειρά που εισάγεται στην προτροπή θα αντιστοιχιστεί στη μεταβλητή ονόματος.
- Πάρτε το τμήμα από τον χρήστη. Το μπορεί να γίνει με τον ίδιο τρόπο όπως παραπάνω.
τμήμα = InputBox ("Εισαγάγετε το τμήμα του υπαλλήλου")
- Αντιστοιχίστε το όνομα και το τμήμα με το “_” ως διαχωριστικό στη μεταβλητή lookup_val χρησιμοποιώντας την ακόλουθη σύνταξη:
lookup_val = όνομα & "_" & τμήμα
- Γράψτε τη σύνταξη vlookup για να αναζητήσετε το διάστημα αναζήτησης στο εύρος B5: Το E24 το επιστρέφει με μεταβλητό μισθό.
Αρχικοποιήστε τον μεταβλητό μισθό:
Αμυδρό μισθό όσο καιρό
Χρησιμοποιήστε τη λειτουργία Vlookup για να βρείτε το lookup_val. Το table_array μπορεί να δοθεί ως Range ("B: F") και ο μισθός είναι παρών στην 5η στήλη. Έτσι μπορεί να χρησιμοποιηθεί η ακόλουθη σύνταξη:
μισθός = Application.WorksheetFunction.VLookup (look__val, Range ("B: F"), 5, False)
- Για να εκτυπώσετε το μισθό σε ένα πλαίσιο μηνύματος, χρησιμοποιήστε τη σύνταξη:
MsgBox (Ο μισθός του υπαλλήλου είναι "& μισθός)
Τώρα, εκτελέστε τον κωδικό. Θα εμφανιστεί ένα πλαίσιο προτροπής στο φύλλο εργασίας στο οποίο μπορείτε να εισαγάγετε το όνομα. Αφού εισαγάγετε το όνομα (Say Sashi) και κάντε κλικ στο OK.
Θα ανοίξει ένα άλλο πλαίσιο στο οποίο μπορείτε να μπείτε στο τμήμα. Αφού μπείτε στο τμήμα, πείτε IT.
Θα εκτυπώσει τον μισθό του υπαλλήλου.
Εάν το Vlookup μπορεί να βρει οποιονδήποτε υπάλληλο με το όνομα και το τμήμα, θα δώσει σφάλμα. Ας υποθέσουμε ότι δώσετε το όνομα "Vishnu" και το τμήμα "IT", θα επιστρέψει το σφάλμα χρόνου εκτέλεσης '1004'.
Για να αντιμετωπίσετε αυτό το ζήτημα, μπορείτε να καθορίσετε στον κώδικα, ότι σε αυτόν τον τύπο σφάλματος, εκτυπώστε "Value not found". Για να το κάνω αυτό,
- Πριν χρησιμοποιήσετε τη σύνταξη vlookup, χρησιμοποιήστε τον ακόλουθο κώδικα-
Σφάλμα GoTo μήνυμα
Ελεγχος:
Ο τελικός κώδικας (του Check :) θα παρακολουθείται και εάν λάβει ένα σφάλμα, θα μεταβεί στη δήλωση "μήνυμα"
- Στο τέλος του κωδικού (Before End Sub), καθορίστε ότι εάν ο αριθμός σφάλματος είναι 1004, τότε εκτυπώστε στο πλαίσιο μηνύματος, "Δεν υπάρχουν δεδομένα υπαλλήλων". Αυτό μπορεί να γίνει χρησιμοποιώντας τη σύνταξη:
Μήνυμα:
Εάν Err.Number = 1004 τότε
MsgBox ("Δεν υπάρχουν δεδομένα υπαλλήλων")
Τέλος εαν
Ενότητα 1:
Sub vlookup3 ()For i = 4 To Cells (Rows.Count, "C"). End (xlUp). Row
Cells (i, "B"). Value = Cells (i, "D"). Value & "_ "& Cells (i," E "). Τιμή
Επόμενο όνομα iDim As String
Dim department As String
Dim lookup_val As String
Dim gaji As Longname = InputBox (" Εισαγάγετε το όνομα του υπαλλήλου ")
τμήμα = InputBox (" Εισαγάγετε το τμήμα του ο υπάλληλος ")
lookup_val = name &" _ "& department On Error GoTo Message
check:
gaji = Application.WorksheetFunction.VLookup (lookup_val, Range (" B: F "), 5, False)
MsgBox (" Ο μισθός του υπαλλήλου είναι "& Μισθός) Μήνυμα:
Εάν Err.Number = 1004, τότε
MsgBox (" Δεν υπάρχουν δεδομένα υπαλλήλων ")
Τερματισμός υποδιαίρεσης IfEnd
Πράγματα που πρέπει να θυμάστε για το VLookup στο Excel VBA
- Η συνάρτηση Vlookup μπορεί να κληθεί στο Excel VBA χρησιμοποιώντας το WorksheetFunction.
- Η σύνταξη της συνάρτησης vlookup παραμένει η ίδια στο Excel VBA.
- Όταν ο κώδικας VBA vlookup δεν μπορεί να βρει την τιμή_αναζήτησης, θα δώσει σφάλμα 1004.
- Το σφάλμα στη συνάρτηση vlookup μπορεί να αντιμετωπιστεί χρησιμοποιώντας μια δήλωση goto εάν επιστρέψει ένα σφάλμα.
