Οι συναρτήσεις όπως VLOOKUP, COUNTIF, SUMIF ονομάζονται συναρτήσεις φύλλου εργασίας. Γενικά, οι συναρτήσεις που είναι προκαθορισμένες στο Excel και είναι έτοιμες για χρήση σε ένα φύλλο εργασίας είναι συναρτήσεις φύλλου εργασίας. Δεν μπορείτε να αλλάξετε ή να δείτε τον κώδικα πίσω από αυτές τις λειτουργίες στο VBA.
Από την άλλη πλευρά, οι καθορισμένες από το χρήστη λειτουργίες και λειτουργίες συγκεκριμένες για VBA όπως το MsgBox ή το InputBox είναι συναρτήσεις VBA.
Όλοι γνωρίζουμε πώς να χρησιμοποιούμε τις λειτουργίες VBA στο VBA. Τι γίνεται όμως αν θέλουμε να χρησιμοποιήσουμε το VLOOKUP σε ένα VBA. Πώς θα το κάνουμε αυτό; Σε αυτό το άρθρο, θα διερευνήσουμε ακριβώς αυτό.
Χρήση λειτουργιών φύλλου εργασίας σε VBA
Για να αποκτήσουμε πρόσβαση στη συνάρτηση φύλλου εργασίας χρησιμοποιούμε μια κλάση εφαρμογών. Σχεδόν όλες οι λειτουργίες του φύλλου εργασίας παρατίθενται στην κλάση Application.WorksheetFunction. Και χρησιμοποιώντας τελεστή τελείας, μπορείτε να έχετε πρόσβαση σε όλα.
Σε οποιοδήποτε δευτερεύον, γράψτε Application.WorksheetFunction. Και ξεκινήστε να γράφετε το όνομα της συνάρτησης. Το intellisense της VBA θα εμφανίσει το όνομα των διαθέσιμων προς χρήση λειτουργιών. Μόλις επιλέξετε το όνομα της συνάρτησης, θα ζητήσει τις μεταβλητές, όπως κάθε συνάρτηση στο excel. Αλλά θα πρέπει να περάσετε μεταβλητές σε κατανοητή μορφή VBA. Για παράδειγμα, εάν θέλετε να περάσετε ένα εύρος A1: A10, θα πρέπει να το περάσετε ως αντικείμενο εύρους όπως το Range ("A1: A10").
Ας χρησιμοποιήσουμε λοιπόν μερικές συναρτήσεις φύλλου εργασίας για να το καταλάβουμε καλύτερα.
Πώς να χρησιμοποιήσετε τη λειτουργία VLOOKUP στο VBA
Για να αποδείξετε πώς μπορείτε να χρησιμοποιήσετε μια λειτουργία VLOOKUP στο VBA, εδώ έχω δείγματα δεδομένων. Πρέπει να εμφανίσω το όνομα και την πόλη του αναγνωριστικού σύνδεσης σε ένα πλαίσιο μηνύματος χρησιμοποιώντας VBA. Τα δεδομένα διαδίδονται στο εύρος A1: K26.
Πατήστε ALT+F11 για να ανοίξετε το VBE και να εισαγάγετε μια μονάδα.
Δείτε τον παρακάτω κώδικα.
Sub WsFuncitons () Dim loginID As String Dim name, city As String loginID = "AHKJ_1-3357042451" 'Χρησιμοποιώντας τη λειτουργία VLOOKUP για να λάβετε το όνομα του δεδομένου αναγνωριστικού στο όνομα πίνακα = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26") ), 2, 0) 'Χρήση της συνάρτησης VLOOKUP για να λάβετε την πόλη του δεδομένου αναγνωριστικού στην πόλη του πίνακα = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & "Πόλη:" & πόλη) Τέλος υποσ
Όταν εκτελείτε αυτόν τον κώδικα, θα έχετε αυτό το αποτέλεσμα.
Μπορείτε να δείτε πόσο γρήγορα το VBA εκτυπώνει το αποτέλεσμα σε ένα πλαίσιο μηνυμάτων. Τώρα ας εξετάσουμε τον κώδικα.
Πώς λειτουργεί;
1.
Dim loginID ως συμβολοσειρά
Αμυδρό όνομα, πόλη As String
Αρχικά έχουμε δηλώσει δύο μεταβλητές τύπου συμβολοσειράς για να αποθηκεύσουμε το αποτέλεσμα που επιστρέφεται από τη συνάρτηση VLOOKUP. Έχω χρησιμοποιήσει μεταβλητές τύπου συμβολοσειράς επειδή είμαι βέβαιος ότι το αποτέλεσμα που επιστρέφεται από το VLOOKUP θα είναι μια τιμή συμβολοσειράς. Εάν η συνάρτηση φύλλου εργασίας σας αναμένεται να επιστρέψει τον αριθμό, την ημερομηνία, το εύρος κλπ., Χρησιμοποιήστε αυτό το είδος μεταβλητής για να αποθηκεύσετε το αποτέλεσμα. Εάν δεν είστε βέβαιοι, τι είδους τιμή θα επιστρέψει η συνάρτηση φύλλου εργασίας, χρησιμοποιήστε μεταβλητές τύπου παραλλαγής.
2.
loginID = "AHKJ_1-3357042451"
Στη συνέχεια, χρησιμοποιήσαμε τη μεταβλητή loginID για να αποθηκεύσουμε την τιμή αναζήτησης. Εδώ έχουμε χρησιμοποιήσει μια σκληρή κωδικοποιημένη τιμή. Μπορείτε επίσης να χρησιμοποιήσετε αναφορές. Για παράδειγμα. Μπορείτε να χρησιμοποιήσετε το εύρος ("A2"). Τιμή για δυναμική ενημέρωση της τιμής αναζήτησης από το εύρος A2.
3.
όνομα = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 2, 0)
Εδώ χρησιμοποιούμε τη συνάρτηση VLOOKUP για να πάρουμε. Τώρα, όταν διορθώσετε τη συνάρτηση και ανοίξετε την παρένθεση, θα εμφανιστούν τα απαιτούμενα ορίσματα, αλλά όχι τόσο περιγραφικά όσο εμφανίζονται στο Excel. Κοιταξε και μονος σου.
Πρέπει να θυμάστε πώς και ποια μεταβλητή πρέπει να χρησιμοποιήσετε. Μπορείτε πάντα να επιστρέψετε στο φύλλο εργασίας για να δείτε τις περιγραφικές λεπτομέρειες της μεταβλητής.
Εδώ, η τιμή αναζήτησης είναι Arg1. Για το Arg1 χρησιμοποιούμε το loginID. Ο πίνακας αναζήτησης είναι Arg2. Για το Arg2 χρησιμοποιήσαμε το Range ("A1: K26"). Σημειώστε ότι δεν χρησιμοποιήσαμε απευθείας το A2: K26 όπως κάνουμε στο Excel. Ο δείκτης στηλών είναι Arg3. Για το Arg3 χρησιμοποιήσαμε 2, αφού το όνομα βρίσκεται στη δεύτερη στήλη. Ο τύπος αναζήτησης είναι Arg4. Χρησιμοποιήσαμε το 0 ως Arg4.
city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0)
Παρομοίως παίρνουμε το όνομα της πόλης.
4.
MsgBox ("Όνομα:" & όνομα & vbLf & "Πόλη:" & πόλη)
Τέλος, εκτυπώνουμε όνομα και πόλη χρησιμοποιώντας το Messagebox.
Γιατί να χρησιμοποιήσετε τη λειτουργία φύλλου εργασίας στο VBA;
Οι λειτουργίες του φύλλου εργασίας διαθέτουν δύναμη τεράστιων υπολογισμών και δεν θα είναι έξυπνο να αγνοήσουμε τη δύναμη των συναρτήσεων του φύλλου εργασίας. Για παράδειγμα, εάν θέλουμε την τυπική απόκλιση ενός συνόλου δεδομένων και θέλετε να γράψετε ολόκληρο τον κώδικα για αυτό, μπορεί να σας πάρει ώρες. Αλλά αν γνωρίζετε πώς να χρησιμοποιήσετε τη συνάρτηση φύλλου εργασίας STDEV.P στο VBA για να κάνετε τον υπολογισμό με τη μία.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub
Χρήση πολλαπλών λειτουργιών φύλλου εργασίας VBA
Ας υποθέσουμε ότι πρέπει να χρησιμοποιήσουμε μια αντιστοίχιση ευρετηρίου για να ανακτήσουμε ορισμένες τιμές. Τώρα πώς θα γράφατε τον τύπο στο VBA. Αυτό υποθέτω ότι θα γράψετε:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
Αυτό δεν είναι λάθος αλλά είναι μακρύ. Ο σωστός τρόπος χρήσης πολλαπλών συναρτήσεων είναι χρησιμοποιώντας ένα μπλοκ με. Δείτε το παρακάτω παράδειγμα:
Sub IndMtch () With Application.WorksheetFunction Val = .Index (range_range, .Match (lookup_value, lookup_range, match_type)) val2 = .VLookup (arg1, arg2, arg3) val4 = .StDev_P (αριθμοί) Τέλος με τέλος υπο
Όπως μπορείτε να δείτε, έχω χρησιμοποιήσει με το μπλοκ για να πω στην VBA ότι θα χρησιμοποιώ τις ιδιότητες και τις λειτουργίες του Application.WorksheetFunction. Δεν χρειάζεται λοιπόν να το ορίσω παντού. Μόλις χρησιμοποίησα τον τελεστή τελείας για πρόσβαση στις λειτουργίες INDEX, MATCH, VLOOKUP και STDEV.P. Μόλις χρησιμοποιήσουμε τη δήλωση Τέλος με, δεν μπορούμε να έχουμε πρόσβαση σε συναρτήσεις χωρίς να χρησιμοποιήσουμε πλήρως αναγνωρισμένα ονόματα συναρτήσεων.
Έτσι, εάν πρέπει να χρησιμοποιήσετε πολλές λειτουργίες φύλλου εργασίας στο VBA, χρησιμοποιήστε το με μπλοκ.
Δεν είναι όλες οι λειτουργίες του φύλλου εργασίας διαθέσιμες μέσω της εφαρμογής. Λειτουργία φύλλου εργασίας
Ορισμένες λειτουργίες φύλλου εργασίας είναι άμεσα διαθέσιμες για χρήση σε VBA. Δεν χρειάζεται να χρησιμοποιήσετε το αντικείμενο Application.WorksheetFunction.
Για παράδειγμα, συναρτήσεις όπως το Len () που χρησιμοποιείται για να λάβει τον αριθμό των χαρακτήρων σε μια συμβολοσειρά, αριστερά, δεξιά, μέσα, περικοπή, μετατόπιση κλπ. Αυτές οι λειτουργίες μπορούν να χρησιμοποιηθούν απευθείας στο VBA. Εδώ είναι ένα παράδειγμα.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) End Sub
Δείτε, εδώ χρησιμοποιήσαμε τη συνάρτηση LEN χωρίς να χρησιμοποιήσουμε το αντικείμενο Application.WorksheetFunction.
Ομοίως, μπορείτε να χρησιμοποιήσετε άλλες λειτουργίες, όπως αριστερά, δεξιά, μέσα, κάρτα κ.λπ.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) Debug.Print (left (strng, 2)) Debug.Print (right (strng, 1)) Debug.Print (Mid (strng, 3, 2)) Τέλος υπο
Όταν εκτελέσετε το παραπάνω υπο, θα επιστρέψει:
5 Εκείνος
Λοιπόν, ναι, έτσι μπορείτε να χρησιμοποιήσετε τη συνάρτηση φύλλου εργασίας του Excel στο VBA. Ελπίζω να ήμουν αρκετά επεξηγηματικός και αυτό το άρθρο σας βοήθησε. Εάν έχετε οποιεσδήποτε ερωτήσεις σχετικά με αυτό το άρθρο ή οτιδήποτε άλλο σχετικό με VBA, ρωτήστε στην παρακάτω ενότητα σχολίων. Μέχρι τότε μπορείτε να διαβάσετε για άλλα σχετικά θέματα παρακάτω.
Τι είναι η λειτουργία CSng στο Excel VBA | Η συνάρτηση SCng είναι μια συνάρτηση VBA που μετατρέπει κάθε τύπο δεδομένων σε έναν αριθμό κυμαινόμενου σημείου μίας ακριβείας ("δεδομένου ότι είναι αριθμός"). Χρησιμοποιώ κυρίως τη λειτουργία CSng για τη μετατροπή αριθμών μορφοποιημένου κειμένου σε πραγματικούς αριθμούς.
Πώς να αποκτήσετε κείμενο και αριθμό αντίστροφα μέσω VBA στο Microsoft Excel | Για να αντιστρέψουμε τον αριθμό και το κείμενο, χρησιμοποιούμε βρόχους και συνάρτηση mid στο VBA. Το 1234 θα μετατραπεί σε 4321, το "εσείς" θα μετατραπεί σε "uoy". Εδώ είναι το απόσπασμα.
Μορφοποιήστε δεδομένα με προσαρμοσμένες μορφές αριθμών χρησιμοποιώντας VBA στο Microsoft Excel | Για να αλλάξετε τη μορφή αριθμού συγκεκριμένων στηλών στο Excel, χρησιμοποιήστε αυτό το απόσπασμα VBA. Καλύπτει τη μορφή αριθμών που έχει καθοριστεί σε συγκεκριμένη μορφή με ένα κλικ.
Χρήση συμβάντος αλλαγής φύλλου εργασίας για εκτέλεση μακροεντολής όταν πραγματοποιείται οποιαδήποτε αλλαγή | Έτσι, για να εκτελέσετε τη μακροεντολή σας κάθε φορά που ενημερώνεται το φύλλο, χρησιμοποιούμε τα Συμβάντα φύλλου εργασίας του VBA.
Εκτελέστε μακροεντολή εάν πραγματοποιηθεί οποιαδήποτε αλλαγή στο φύλλο σε καθορισμένο εύρος | Για να εκτελέσετε τον κώδικα μακροεντολής όταν αλλάζει η τιμή σε ένα καθορισμένο εύρος, χρησιμοποιήστε αυτόν τον κωδικό VBA. Εντοπίζει οποιαδήποτε αλλαγή πραγματοποιηθεί στο καθορισμένο εύρος και θα ενεργοποιήσει το συμβάν.
Απλούστερος κώδικας VBA για να επισημάνετε τη χρήση της τρέχουσας γραμμής και στήλης | Χρησιμοποιήστε αυτό το μικρό απόσπασμα VBA για να επισημάνετε την τρέχουσα γραμμή και στήλη του φύλλου.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Η συνάρτηση VLOOKUP στο Excel | Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
COUNTIF στο Excel 2016 | Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.