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

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

Δημιουργία απλής προσαρμοσμένης συνάρτησης

Οι προσαρμοσμένες συναρτήσεις, όπως οι μακροεντολές, χρησιμοποιούν τη γλώσσα προγραμματισμού Visual Basic for Applications (VBA). Διαφέρουν από τις μακροεντολές με δύο σημαντικούς τρόπους. Πρώτον, χρησιμοποιούν διαδικασίες συνάρτησης αντί για δευτερεύουσες διαδικασίες. Δηλαδή, ξεκινούν με μια πρόταση συνάρτησης αντί για μια πρόταση Sub και τελειώνουν με τη συνάρτηση 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 (συνάρτηση) στο αντικείμενο εφαρμογής (Excel). Αυτό μπορείτε να το κάνετε προσθέτοντας τη λέξη Εφαρμογή πριν από τη λέξη Round. Χρησιμοποιήστε αυτήν τη σύνταξη κάθε φορά που θέλετε να αποκτήσετε πρόσβαση σε μια συνάρτηση του Excel από μια λειτουργική μονάδα VBA.

Κατανόηση των προσαρμοσμένων κανόνων συναρτήσεων

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

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

Χρήση λέξεων-κλειδιών VBA σε προσαρμοσμένες συναρτήσεις

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

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

Τεκμηρίωση μακροεντολών και προσαρμοσμένων συναρτήσεων

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

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

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

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

Διάθεση των προσαρμοσμένων συναρτήσεων σας οπουδήποτε

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

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

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

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

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

Σχετικά με τους συγγραφείς

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

Χρειάζεστε περισσότερη βοήθεια;

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