Αυτό το σεμινάριο θα σας δείξει πώς να χρησιμοποιήσετε τη συνάρτηση μέσου Excel στο VBA.
Η συνάρτηση Excel AVERAGE χρησιμοποιείται για τον υπολογισμό ενός μέσου όρου από ένα εύρος κελιών στο φύλλο εργασίας σας που έχουν τιμές σε αυτά. Στο VBA, είναι προσβάσιμο χρησιμοποιώντας τη μέθοδο WorksheetFunction.
AVERAGE Λειτουργία φύλλου εργασίας
Το αντικείμενο WorksheetFunction μπορεί να χρησιμοποιηθεί για να καλέσετε τις περισσότερες από τις λειτουργίες του Excel που είναι διαθέσιμες στο παράθυρο διαλόγου Εισαγωγή συνάρτησης στο Excel. Η συνάρτηση AVERAGE είναι μία από αυτές.
| 123 | Sub TestFunctionΕύρος ("D33") = Application.WorksheetFunction.Average ("D1: D32")Τέλος υπο |
Μπορείτε να έχετε έως 30 ορίσματα στη συνάρτηση AVERAGE. Κάθε ένα από τα ορίσματα πρέπει να αναφέρεται σε μια περιοχή κελιών.
Αυτό το παράδειγμα παρακάτω θα παράγει τον μέσο όρο του αθροίσματος των κελιών Β11 έως Ν11
| 123 | Sub TestAverage ()Εύρος ("O11") = Application.WorksheetFunction.Average (Εύρος ("B11: N11"))Τέλος υπο |
Το παρακάτω παράδειγμα θα παράγει κατά μέσο όρο το άθροισμα των κυττάρων στο Β11 έως Ν11 και το άθροισμα των κυττάρων στο Β12: Ν12. Εάν δεν πληκτρολογήσετε το αντικείμενο εφαρμογής, θα θεωρηθεί ως δεδομένο.
| 123 | Sub TestAverage ()Εύρος ("O11") = WorksheetFunction.Average (Range ("B11: N11"), Range ("B12: N12"))Τέλος υπο |
Εκχώρηση μέσου αποτελέσματος σε μεταβλητή
Μπορεί να θέλετε να χρησιμοποιήσετε το αποτέλεσμα του τύπου σας αλλού στον κώδικα αντί να το γράψετε απευθείας πίσω σε μια περιοχή Excel. Εάν συμβαίνει αυτό, μπορείτε να αντιστοιχίσετε το αποτέλεσμα σε μια μεταβλητή για χρήση αργότερα στον κώδικά σας.
| 1234567 | Sub AssignAverage ()Αμυδρό αποτέλεσμα ως ακέραιος«Εκχωρήστε τη μεταβλητήαποτέλεσμα = WorksheetFunction.Average (Εύρος ("A10: N10"))«Δείξτε το αποτέλεσμαMsgBox "Ο μέσος όρος για τα κελιά σε αυτό το εύρος είναι" & αποτέλεσμαΤέλος υπο |
ΜΕΣΟΣ με αντικείμενο εύρους
Μπορείτε να αντιστοιχίσετε μια ομάδα κελιών στο αντικείμενο εύρους και, στη συνέχεια, να χρησιμοποιήσετε αυτό το αντικείμενο εύρους με το Φύλλο εργασίας αντικείμενο.
| 123456789 | Sub TestAverageRange ()Dim rng As Range«εκχωρήστε το εύρος των κελιώνΡύθμιση rng = Εύρος ("G2: G7")"χρησιμοποιήστε το εύρος στον τύποΕύρος ("G8") = WorksheetFunction.Average (rng)«απελευθερώστε το αντικείμενο εύρουςΡύθμιση rng = ΤίποταΤέλος υπο |
ΜΕΣΟΙ Αντικείμενα πολλαπλής εμβέλειας
Ομοίως, μπορείτε να υπολογίσετε τον μέσο όρο των κελιών από πολλαπλά αντικείμενα εύρους.
| 123456789101112 | Sub TestAverageMultipleRanges ()Dim rngA As RangeDim rngB ως Εύρος«εκχωρήστε το εύρος των κελιώνΡύθμιση rngA = Εύρος ("D2: D10")Ρύθμιση rngB = Εύρος ("E2: E10")"χρησιμοποιήστε το εύρος στον τύποΕύρος ("E11") = WorksheetFunction.Average (rngA, rngB)«απελευθερώστε το αντικείμενο εύρουςΡύθμιση rngA = ΤίποταΡύθμιση rngB = ΤίποταΤέλος υπο |
Χρησιμοποιώντας το AVERAGEA
Η συνάρτηση AVERAGEA διαφέρει από τη συνάρτηση AVERAGE στο ότι δημιουργεί ένα μέσο όρο από όλα τα κελιά σε ένα εύρος, ακόμη και αν σε ένα από τα κελιά υπάρχει κείμενο - αντικαθιστά το κείμενο με μηδέν και περιλαμβάνει αυτό στον υπολογισμό του μέσου όρου. Η συνάρτηση AVERAGE θα αγνοούσε αυτό το κελί και δεν θα το συνιστούσε στον υπολογισμό.
| 123 | Sub TestAverageA ()Εύρος ("B8) = Application.WorksheetFunction.AverageA (Range (" A10: A11 "))Τέλος υπο |
Στο παρακάτω παράδειγμα, η συνάρτηση AVERAGE επιστρέφει μια διαφορετική τιμή στη συνάρτηση AVERAGEA όταν χρησιμοποιείται ο υπολογισμός στα κελιά A10 έως A11
Η απάντηση για τον τύπο AVERAGEA είναι χαμηλότερη από τον τύπο AVERAGE καθώς αντικαθιστά το κείμενο στο A11 με μηδέν, και ως εκ τούτου έχει μέσο όρο πάνω από 13 τιμές και όχι τις 12 τιμές στις οποίες υπολογίζεται η Μέση τιμή.
Χρησιμοποιώντας το AVERAGEIF
Η συνάρτηση AVERAGEIF σάς επιτρέπει να υπολογίσετε κατά μέσο όρο το άθροισμα ενός εύρους κελιών που πληρούν ορισμένα κριτήρια.
| 123 | Υπό AverageIf ()Εύρος ("F31") = WorksheetFunction.AverageIf (Εύρος ("F5: F30"), "Εξοικονόμηση", Εύρος ("G5: G30"))Τέλος υπο |
Η παραπάνω διαδικασία θα υπολογίσει κατά μέσο όρο τα κελιά της περιοχής G5: G30 όπου το αντίστοιχο κελί στη στήλη F φέρει τη λέξη «Εξοικονόμηση». Τα κριτήρια που χρησιμοποιείτε πρέπει να είναι σε εισαγωγικά.
Μειονεκτήματα της Λειτουργίας φύλλου εργασίας
Όταν χρησιμοποιείτε το Φύλλο εργασίας για τον μέσο όρο των τιμών σε ένα εύρος στο φύλλο εργασίας σας, επιστρέφεται μια στατική τιμή, όχι ένας ευέλικτος τύπος. Αυτό σημαίνει ότι όταν αλλάζουν τα στοιχεία σας στο Excel, η τιμή που έχει επιστραφεί από το Φύλλο εργασίας δεν θα αλλάξει
Στο παραπάνω παράδειγμα, η διαδικασία TestAverage διαδικασία δημιούργησε τον μέσο όρο του B11: M11 και έβαλε την απάντηση στο N11. Όπως μπορείτε να δείτε στη γραμμή τύπων, αυτό το αποτέλεσμα είναι ένα σχήμα και όχι ένας τύπος.
Εάν κάποια από τις τιμές αλλάξει επομένως στο εύρος (Β11: Μ11), τα αποτελέσματα στο Ν11 θα αλλάξουν ΔΕΝ αλλαγή.
Αντί να χρησιμοποιήσετε το Φύλλο εργασίας Λειτουργία. Μέσος όρος, μπορείτε να χρησιμοποιήσετε το VBA για να εφαρμόσετε τη συνάρτηση AVERAGE σε ένα κελί χρησιμοποιώντας το Τύπος ή ΤύποςR1C1 μεθόδους.
Χρήση της μεθόδου Formula
Η μέθοδος τύπου σας επιτρέπει να δείξετε συγκεκριμένα μια σειρά κελιών π.χ.: B11: M11 όπως φαίνεται παρακάτω.
| 123 | Sub TestAverageFormula ()Εύρος ("N11"). Τύπος = "= Μέσος όρος (B11: M11)"Τέλος υπο |
Χρησιμοποιώντας τη μέθοδο FormulaR1C1
Η μέθοδος FomulaR1C1 είναι πιο ευέλικτη, διότι δεν σας περιορίζει σε ένα ορισμένο εύρος κελιών. Το παρακάτω παράδειγμα θα μας δώσει την ίδια απάντηση με την παραπάνω.
| 123 | Sub TestAverageFormula ()Εύρος ("N11"). Τύπος = "= Μέσος όρος (RC [-12]: RC [-1])"Τέλος υπο |
Ωστόσο, για να κάνουμε τον τύπο πιο ευέλικτο, θα μπορούσαμε να τροποποιήσουμε τον κώδικα έτσι ώστε:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Αριθμός (R [-11] C: R [-1] C)"Τέλος υπο |
Όπου και αν βρίσκεστε στο φύλλο εργασίας σας, ο τύπος θα υπολογίσει κατά μέσο όρο τις τιμές στα 12 κελιά ακριβώς στα αριστερά του και θα τοποθετήσει την απάντηση στο ActiveCell. Το εύρος μέσα στη συνάρτηση AVERAGE πρέπει να αναφέρεται στη σύνταξη της γραμμής (R) και της στήλης (C).
Και οι δύο αυτές μέθοδοι σάς επιτρέπουν να χρησιμοποιείτε τύπους Dynamic Excel εντός VBA.
Τώρα θα υπάρχει ένας τύπος στο N11 αντί για τιμή.
