Δημιουργία προσαρμοσμένων συναρτήσεων στο Excel

Οι προσαρμοσμένες συναρτήσεις, όπως οι μακροεντολές, χρησιμοποιούν τη γλώσσα προγραμματισμού της Visual Basic for Applications (VBA ). Διαφέρουν από τις μακροεντολές με δύο σημαντικούς τρόπους. Πρώτα, χρησιμοποιούν διαδικασίες συναρτήσεων αντί για δευτερεύουσες διαδικασίες. Δηλαδή, ξεκινούν με μια πρόταση συνάρτησης αντί για μια δευτερεύουσα πρόταση και τελειώνουν με τη συνάρτηση End αντί για την end sub. Δεύτερον, εκτελούν υπολογισμούς αντί να κάνουν ενέργειες. Ορισμένα είδη προτάσεων, όπως οι προτάσεις που επιλέγουν και μορφοποιούν περιοχές, εξαιρούνται από τις προσαρμοσμένες συναρτήσεις. Σε αυτό το άρθρο, θα μάθετε πώς μπορείτε να δημιουργήσετε και να χρησιμοποιήσετε προσαρμοσμένες συναρτήσεις. Για να δημιουργήσετε συναρτήσεις και μακροεντολές, εργάζεστε με την Επεξεργασία της Visual Basic (VBE), η οποία ανοίγει σε ένα νέο παράθυρο ξεχωριστά από το Excel.

Ας υποθέσουμε ότι η εταιρεία σας προσφέρει έκπτωση ποσότητας 10 τοις εκατό για την πώληση ενός προϊόντος, υπό την προϋπόθεση ότι η παραγγελία είναι για περισσότερες από 100 μονάδες. Στις ακόλουθες παραγράφους, θα δείξουμε μια συνάρτηση για τον υπολογισμό αυτής της έκπτωσης.

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

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

1. Πατήστε το συνδυασμό πλήκτρων Alt+F11 για να ανοίξετε την Επεξεργασία Visual Basic (σε Mac, πατήστε το συνδυασμό πλήκτρων FN+ALT+F11) και, στη συνέχεια, κάντε κλικ στην επιλογή Εισαγωγή > Λειτουργική μονάδα. Ένα νέο παράθυρο λειτουργικής μονάδας εμφανίζεται στη δεξιά πλευρά της Επεξεργασίας της Visual Basic.

2. Αντιγράψτε και επικολλήστε τον παρακάτω κώδικα στη νέα λειτουργική μονάδα.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Τώρα είστε έτοιμοι να χρησιμοποιήσετε τη νέα συνάρτηση DISCOUNT. Κλείστε την Επεξεργασία της Visual Basic, επιλέξτε το κελί G7 και πληκτρολογήστε τα εξής:

=DISCOUNT(D7;E7)

Το Excel υπολογίζει την έκπτωση 10 τοις εκατό σε 200 μονάδες στα 47,50 $ ανά μονάδα και επιστρέφει 950,00 $.

Στην πρώτη γραμμή του κώδικα VBA, function DISCOUNT(ποσότητα; τιμή), υποδείξατε ότι η συνάρτηση DISCOUNT απαιτεί δύο ορίσματα, ποσότητα και τιμή. Όταν καλείτε τη συνάρτηση σε ένα κελί φύλλου εργασίας, πρέπει να συμπεριλάβετε αυτά τα δύο ορίσματα. Στον τύπο =DISCOUNT(D7;E7), το κελί D7 είναι το όρισμα ποσότητα και το E7 είναι το όρισμα τιμή . Τώρα μπορείτε να αντιγράψετε τον τύπο DISCOUNT στο G8:G13 για να δείτε τα αποτελέσματα που εμφανίζονται παρακάτω.

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

Η πρόταση If στο ακόλουθο μπλοκ κώδικα εξετάζει το όρισμα ποσότητα και καθορίζει εάν ο αριθμός των ειδών που πωλήθηκαν είναι μεγαλύτερος ή ίσος με 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

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

Discount = quantity * price * 0.1

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

Εάν η ποσότητα είναι μικρότερη από 100, η VBA εκτελεί την ακόλουθη πρόταση:

Discount = 0

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

Discount = Application.Round(Discount, 2)

Η VBA δεν έχει καμία συνάρτηση ROUND, αλλά το Excel διαθέτει. Επομένως, για να χρησιμοποιήσετε τη συνάρτηση ROUND σε αυτή την πρόταση, πείτε στη VBA να αναζητήσει τη μέθοδο Round (συνάρτηση) στο αντικείμενο Application (Excel). Αυτό μπορείτε να το κάνετε προσθέτοντας τη λέξη Εφαρμογή πριν από τη λέξη Γύρος. Χρησιμοποιήστε αυτήν τη σύνταξη όποτε θέλετε να αποκτήσετε πρόσβαση σε μια συνάρτηση του Excel από μια λειτουργική μονάδα VBA.

Μια προσαρμοσμένη συνάρτηση πρέπει να ξεκινά με μια πρόταση συνάρτησης και να τελειώνει με μια πρόταση End Function. Εκτός από το όνομα της συνάρτησης, η πρόταση Συνάρτηση συνήθως καθορίζει ένα ή περισσότερα ορίσματα. Μπορείτε, ωστόσο, να δημιουργήσετε μια συνάρτηση χωρίς ορίσματα. Το Excel περιλαμβάνει πολλές ενσωματωμένες συναρτήσεις — rand και NOW, για παράδειγμα — που δεν χρησιμοποιούν ορίσματα.

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

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

Η μόνη ενέργεια που μπορεί να κάνει μια διαδικασία συνάρτησης (εκτός από την εκτέλεση υπολογισμών) είναι η εμφάνιση ενός παραθύρου διαλόγου. Μπορείτε να χρησιμοποιήσετε μια πρόταση InputBox σε μια προσαρμοσμένη συνάρτηση ως μέσο εισαγωγής δεδομένων από το χρήστη που εκτελεί τη συνάρτηση. Μπορείτε να χρησιμοποιήσετε μια πρόταση MsgBox ως μέσο μεταφοράς πληροφοριών στο χρήστη. Μπορείτε επίσης να χρησιμοποιήσετε προσαρμοσμένα παράθυρα διαλόγου ή Φόρμες χρήστη, αλλά αυτό είναι ένα θέμα πέρα από το πεδίο εφαρμογής αυτής της εισαγωγής.

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

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

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

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

Για να χρησιμοποιήσετε μια προσαρμοσμένη συνάρτηση, το βιβλίο εργασίας που περιέχει τη λειτουργική μονάδα στην οποία δημιουργήσατε τη συνάρτηση πρέπει να είναι ανοιχτό. Εάν αυτό το βιβλίο εργασίας δεν είναι ανοιχτό, λαμβάνετε ένα #NAME; όταν προσπαθείτε να χρησιμοποιήσετε τη συνάρτηση. Εάν αναφέρετε τη συνάρτηση σε διαφορετικό βιβλίο εργασίας, πρέπει να προηγείτε το όνομα της συνάρτησης με το όνομα του βιβλίου εργασίας στο οποίο βρίσκεται η συνάρτηση. Για παράδειγμα, εάν δημιουργήσετε μια συνάρτηση που ονομάζεται DISCOUNT σε ένα βιβλίο εργασίας που ονομάζεται Personal.xlsb και καλέσετε αυτήν τη συνάρτηση από άλλο βιβλίο εργασίας, πρέπει να πληκτρολογήσετε =personal.xlsb!discount(), όχι απλώς =discount().

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

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

Αφού ακολουθήσετε αυτά τα βήματα, οι προσαρμοσμένες συναρτήσεις σας θα είναι διαθέσιμες κάθε φορά που εκτελείτε το Excel. Εάν θέλετε να προσθέσετε στη βιβλιοθήκη συναρτήσεων, επιστρέψτε στην Επεξεργασία της Visual Basic. Εάν ανατρέξετε στην Επεξεργασία της Visual Basic στην Εξερεύνηση έργων κάτω από την επικεφαλίδα VBAProject, θα δείτε μια λειτουργική μονάδα με το όνομα του αρχείου πρόσθετου. Το πρόσθετο θα έχει την επέκταση .xlam.

Κάνοντας διπλό κλικ σε αυτήν τη λειτουργική μονάδα στην Εξερεύνηση έργων, εμφανίζεται ο κώδικας της συνάρτησης από την Επεξεργασία της Visual Basic. Για να προσθέσετε μια νέα συνάρτηση, τοποθετήστε το σημείο εισαγωγής μετά την πρόταση End Function που τερματίζει την τελευταία συνάρτηση στο παράθυρο "Κώδικας" και αρχίστε να πληκτρολογείτε. Μπορείτε να δημιουργήσετε όσες συναρτήσεις χρειάζεστε με αυτόν τον τρόπο και θα είναι πάντα διαθέσιμες στην κατηγορία Ορισμός από το χρήστη στο παράθυρο διαλόγου Εισαγωγή συνάρτησης .

Αυτό το περιεχόμενο συντάχθηκε αρχικά από τους Mark Dodge και Craig Stinson ως μέρος του βιβλίου τους Microsoft Office Excel 2007 Inside Out. Από τότε έχει ενημερωθεί ώστε να ισχύει και για νεότερες εκδόσεις του Excel.