Συνάρτηση πίνακα UDF με επιχειρήματα στο Excel
Όπως υποδηλώνει ο τίτλος, θα μάθουμε πώς να δημιουργούμε μια συνάρτηση που ορίζεται από το χρήστη στο Excel που επιστρέφει έναν πίνακα. Έχουμε ήδη μάθει πώς να δημιουργείτε μια συνάρτηση που καθορίζεται από το χρήστη στο VBA. Έτσι, χωρίς να χάνουμε χρόνο, ας ξεκινήσουμε με το σεμινάριο.
Τι είναι μια συνάρτηση πίνακα;
Οι συναρτήσεις πίνακα είναι οι συναρτήσεις που επιστρέφουν έναν πίνακα όταν χρησιμοποιούνται. Αυτή η συνάρτηση χρησιμοποιείται με συνδυασμούς πλήκτρων CTRL+SHIFT+ENTER και αυτός είναι ο λόγος για τον οποίο προτιμούμε την κλήση συνάρτησης πίνακα ή τύπων ως συνάρτηση και τύποι CSE.
Η λειτουργία του πίνακα excel είναι συχνά τύποι συστοιχιών πολλαπλών κυττάρων. Ένα παράδειγμα είναι η συνάρτηση TRANSPOSE.
Δημιουργία συνάρτησης πίνακα UDF στο VBA
Έτσι, το σενάριο είναι ότι θέλω απλώς να επιστρέψω τους πρώτους 3 ζυγούς αριθμούς χρησιμοποιώντας τη συνάρτηση ThreeEven ().
Ο κώδικας θα μοιάζει με αυτόν.
Λειτουργία ThreeEven () Ως ακέραιος () 'καθορισμός πίνακα Dim αριθμοί (2) Ως ακέραιος' Αντιστοίχιση τιμών σε αριθμούς πίνακα (0) = 0 αριθμούς (1) = 2 αριθμούς (2) = 4 'τιμές επιστροφής ThreeEven = αριθμούς Τέλος συνάρτησης
Ας χρησιμοποιήσουμε αυτήν τη συνάρτηση στο φύλλο εργασίας.
Μπορείτε να δείτε ότι, επιλέγουμε πρώτα τρία κελιά (οριζόντια, για κάθετα πρέπει να χρησιμοποιήσουμε δισδιάστατο πίνακα. Το έχουμε καλύψει παρακάτω.). Στη συνέχεια αρχίζουμε να γράφουμε τον τύπο μας. Στη συνέχεια πατάμε CTRL+SHIFT+ENTER. Αυτό γεμίζει τα επιλεγμένα κελιά με τις τιμές του πίνακα.
Σημείωση:
- Στην πράξη, δεν θα ξέρετε πόσα κύτταρα θα χρειαστείτε. Σε αυτήν την περίπτωση, επιλέξτε πάντα περισσότερα κελιά από το αναμενόμενο μήκος πίνακα. Η συνάρτηση θα γεμίσει τα κελιά με πίνακα και τα επιπλέον κελιά θα εμφανίσουν σφάλμα #N/A.
- Από προεπιλογή, αυτή η συνάρτηση πίνακα επιστρέφει τιμές σε έναν οριζόντιο πίνακα. Εάν προσπαθήσετε να επιλέξετε κάθετα κελιά, όλα τα κελιά θα εμφανίζουν μόνο την πρώτη τιμή του πίνακα.
Πως δουλεύει?
Για να δημιουργήσετε μια συνάρτηση πίνακα πρέπει να ακολουθήσετε αυτήν τη σύνταξη.
Function functionName (μεταβλητές) Ως returnType () dim resultArray (length) as dataType 'Εκχωρήστε τιμές στον πίνακα εδώ functionName = resultArray End Function
Η δήλωση συνάρτησης πρέπει να είναι όπως ορίζεται παραπάνω. Αυτό δήλωσε ότι είναι μια συνάρτηση πίνακα.
Ενώ το χρησιμοποιείτε στο φύλλο εργασίας, πρέπει να χρησιμοποιήσετε συνδυασμό πλήκτρων CTRL+SHIFT+ENTER. Διαφορετικά, θα επιστρέψει μόνο την πρώτη τιμή του πίνακα.
Λειτουργία πίνακα VBA για επιστροφή κάθετου πίνακα
Για να κάνετε τη λειτουργία του πίνακα UDF να λειτουργεί κάθετα, δεν χρειάζεται να κάνετε πολλά. Απλώς δηλώστε τους πίνακες ως δισδιάστατο πίνακα. Στη συνέχεια, στην πρώτη διάσταση προσθέστε τις τιμές και αφήστε κενή την άλλη διάσταση. Έτσι το κάνετε:
Λειτουργία ThreeEven () Ως ακέραιος () 'καθορισμός πίνακα Dim αριθμοί (2,0) Ως ακέραιος' Εκχωρήστε τιμές σε αριθμούς πίνακα (0,0) = 0 αριθμούς (1,0) = 2 αριθμούς (2,0) = 4 ' επιστροφή τιμών ThreeEven = αριθμοί Λειτουργία λήξης
Έτσι το χρησιμοποιείτε στο φύλλο εργασίας.
Συνάρτηση πίνακα UDF με επιχειρήματα στο Excel
Στα παραπάνω παραδείγματα, απλά εκτυπώσαμε τις στατικές τιμές αθροίσματος στο φύλλο. Ας υποθέσουμε ότι θέλουμε η συνάρτηση μας να αποδεχθεί ένα όρισμα εύρους, να εκτελέσει κάποιες λειτουργίες σε αυτά και να επιστρέψει τον πίνακα που προκύπτει.
Παράδειγμα Προσθέστε το "-done" σε κάθε τιμή του εύρους
Τώρα, ξέρω ότι αυτό μπορεί να γίνει εύκολα, αλλά μόνο για να σας δείξω πώς μπορείτε να χρησιμοποιήσετε συναρτήσεις πίνακα VBA που καθορίζονται από το χρήστη για την επίλυση προβλημάτων.
Λοιπόν, εδώ θέλω μια συνάρτηση πίνακα που παίρνει ένα εύρος ως όρισμα και προσθέτει "-τελέχτηκε" σε κάθε τιμή στο εύρος. Αυτό μπορεί να γίνει εύκολα χρησιμοποιώντας τη συνάρτηση συνένωσης, αλλά εδώ θα χρησιμοποιήσουμε μια συνάρτηση πίνακα.
Λειτουργία CONCATDone (rng As Range) As Variant () Dim resulArr () As Variant 'Δημιουργία συλλογής Dim col As New Collection' Προσθήκη τιμών στη συλλογή On Error Resume Next For Every v In rng col.Add v Next On Error GoTo 0 ' ολοκλήρωση της λειτουργίας σε κάθε τιμή και προσθήκη τους στο Array ReDim resulArr (col.Count - 1, 0) For i = 0 To col.Count - 1 resulArr (i, 0) = col (i + 1) & "-done" Next CONCATDone = Λειτουργία λήξης resulArr
Εξήγηση:
Η παραπάνω συνάρτηση θα δεχτεί ένα εύρος ως όρισμα και θα προσθέσει "-τελέχτηκε" σε κάθε τιμή στο εύρος.
Μπορείτε να δείτε ότι χρησιμοποιήσαμε μια συλλογή VBA εδώ για να κρατήσουμε τις τιμές του πίνακα και στη συνέχεια κάναμε τη λειτουργία μας σε κάθε τιμή και τις προσθέσαμε ξανά σε έναν δισδιάστατο πίνακα.
Λοιπόν, ναι, έτσι μπορείτε να δημιουργήσετε μια προσαρμοσμένη συνάρτηση πίνακα VBA που μπορεί να επιστρέψει έναν πίνακα. Ελπίζω να ήταν αρκετά επεξηγηματικό. Εάν έχετε ερωτήσεις σχετικά με αυτό το άρθρο, τοποθετήστε το στην παρακάτω ενότητα σχολίων.
Κάντε κλικ στον παρακάτω σύνδεσμο για να κατεβάσετε το αρχείο εργασίας:
Δημιουργία συνάρτησης VBA για επιστροφή πίνακαArrays in Excel Formul | Μάθετε τι είναι οι πίνακες στο excel.
Πώς να δημιουργήσετε μια συνάρτηση καθορισμένη από το χρήστη μέσω VBA | Μάθετε πώς μπορείτε να δημιουργήσετε συναρτήσεις που καθορίζονται από το χρήστη στο Excel
Χρήση συνάρτησης καθορισμένης από το χρήστη (UDF) από άλλο βιβλίο εργασίας χρησιμοποιώντας VBA στο Microsoft Excel | Χρησιμοποιήστε τη συνάρτηση που ορίζεται από το χρήστη σε άλλο βιβλίο εργασίας του Excel
Επιστρέψτε τιμές σφάλματος από συναρτήσεις που ορίζονται από τον χρήστη χρησιμοποιώντας VBA στο Microsoft Excel | Μάθετε πώς μπορείτε να επιστρέψετε τιμές σφάλματος από μια συνάρτηση που ορίζεται από το χρήστη
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας
Η συνάρτηση VLOOKUP στο Excel
COUNTIF στο Excel 2016
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel