Αυτό το σεμινάριο θα σας δείξει πώς να χρησιμοποιήσετε τις λειτουργίες Excel SUMIF και SUMIFS στο VBA
Το VBA δεν έχει ισοδύναμο των λειτουργιών SUMIF ή SUMIFS που μπορείτε να χρησιμοποιήσετε - ένας χρήστης πρέπει να χρησιμοποιήσει τις ενσωματωμένες λειτουργίες Excel στο VBA χρησιμοποιώντας το Λειτουργία φύλλου εργασίας αντικείμενο.
Λειτουργία φύλλου εργασίας SUMIF
Το αντικείμενο WorksheetFunction μπορεί να χρησιμοποιηθεί για να καλέσετε τις περισσότερες από τις λειτουργίες του Excel που είναι διαθέσιμες στο παράθυρο διαλόγου Εισαγωγή συνάρτησης στο Excel. Η συνάρτηση SUMIF είναι μία από αυτές.
| 123 | Sub TestSumIf ()Εύρος ("D10") = Application.WorksheetFunction.SumIf (Εύρος ("C2: C9"), 150, Εύρος ("D2: D9"))Τέλος υπο |
Η παραπάνω διαδικασία θα προσθέσει τα κελιά στο εύρος (D2: D9) μόνο εάν το αντίστοιχο κελί στη στήλη C = 150.
Ανάθεση αποτελέσματος SUMIF σε μεταβλητή
Μπορεί να θέλετε να χρησιμοποιήσετε το αποτέλεσμα του τύπου σας αλλού στον κώδικα αντί να το γράψετε απευθείας πίσω και στο Εύρος Excel. Εάν συμβαίνει αυτό, μπορείτε να αντιστοιχίσετε το αποτέλεσμα σε μια μεταβλητή για χρήση αργότερα στον κώδικά σας.
| 1234567 | Sub AssignSumIfVariable ()Αμυδρό αποτέλεσμα ως Διπλό«Εκχωρήστε τη μεταβλητήαποτέλεσμα = WorksheetFunction.SumIf (Εύρος ("C2: C9"), 150, Εύρος ("D2: D9"))«Δείξτε το αποτέλεσμαMsgBox "Το σύνολο του αποτελέσματος που αντιστοιχεί στον κωδικό πωλήσεων 150 είναι" & αποτέλεσμαΤέλος υπο |
Χρήση SUMIFS
Η συνάρτηση SUMIFS είναι παρόμοια με τη συνάρτηση φύλλου εργασίας SUMIF, αλλά σας επιτρέπει να ελέγχετε για περισσότερα από ένα κριτήρια. Στο παρακάτω παράδειγμα, ψάχνουμε να προσθέσουμε την τιμή πώλησης εάν ο κωδικός πώλησης είναι 150 ΚΑΙ η τιμή κόστους είναι μεγαλύτερη από 2. Παρατηρήστε ότι σε αυτόν τον τύπο, το εύρος των κελιών που πρέπει να προσθέσετε είναι μπροστά από τα κριτήρια, ενώ στη συνάρτηση SUMIF, είναι πίσω.
| 123 | Sub MultipleSumIfs ()Εύρος ("D10") = WorksheetFunction.SumIfs (Range ("D2: D9"), Range ("C2: C9"), 150, Range ("E2: E9"), "> 2")Τέλος υπο |
Χρήση SUMIF με αντικείμενο εύρους
Μπορείτε να αντιστοιχίσετε μια ομάδα κελιών στο αντικείμενο εύρους και, στη συνέχεια, να χρησιμοποιήσετε αυτό το αντικείμενο εύρους με το Φύλλο εργασίας αντικείμενο.
| 123456789101112 | Sub TestSumIFRange ()Κριτήρια ως εύροςDim rngSum ως εύρος«εκχωρήστε το εύρος των κελιώνΟρισμός rngCriteria = Range ("C2: C9")Ορισμός rngSum = Εύρος ("D2: D9")"χρησιμοποιήστε το εύρος στον τύποΕύρος ("D10") = WorksheetFunction.SumIf (rngCriteria, 150, rngSum)«απελευθερώστε τα αντικείμενα εύρουςΟρισμός rngCriteria = ΤίποταΡύθμιση rngSum = ΤίποταΤέλος υπο |
Χρήση SUMIFS σε αντικείμενα πολλαπλής εμβέλειας
Ομοίως, μπορείτε να χρησιμοποιήσετε SUMIFS σε πολλά αντικείμενα εύρους.
| 123456789101112131415 | Sub TestSumMultipleRanges ()Dim rngCriteria1 As RangeDim rngCriteria2 as RangeDim rngSum ως εύρος«εκχωρήστε το εύρος των κελιώνΟρισμός rngCriteria1 = Εύρος ("C2: C9")Ορισμός rngCriteria2 = Εύρος ("E2: E10")Ορισμός rngSum = Εύρος ("D2: D10")"χρησιμοποιήστε τα εύρη στον τύποΕύρος ("D10") = WorksheetFunction.SumIfs (rngSum, rngCriteria1, 150, rngCriteria2, "> 2")«απελευθερώστε το αντικείμενο εύρουςΟρισμός rngCriteria1 = ΤίποταΟρισμός rngCriteria2 = ΤίποταΡύθμιση rngSum = ΤίποταΤέλος υπο |
Παρατηρήστε ότι επειδή χρησιμοποιείτε ένα πρόσημο μεγαλύτερο από, τα κριτήρια μεγαλύτερα από 2 πρέπει να βρίσκονται σε παρένθεση.
SUMIF Formula
Όταν χρησιμοποιείτε το WorksheetFunction.SUMIF για να προσθέσετε ένα άθροισμα σε μια περιοχή στο φύλλο εργασίας σας, επιστρέφεται ένα στατικό άθροισμα, όχι ένας ευέλικτος τύπος. Αυτό σημαίνει ότι όταν αλλάζουν τα στοιχεία σας στο Excel, η τιμή που έχει επιστραφεί από το Φύλλο εργασίας δεν θα αλλάξει
Στο παραπάνω παράδειγμα, η διαδικασία πρόσθεσε το εύρος (D2: D9) όπου ο SaleCode ισούται με 150 στη στήλη C και το αποτέλεσμα τέθηκε στο D10. Όπως μπορείτε να δείτε στη γραμμή τύπων, αυτό το αποτέλεσμα είναι ένα σχήμα και όχι ένας τύπος.
Εάν κάποια από τις τιμές αλλάξει είτε στο εύρος (D2: D9) είτε στο εύρος (C2: D9), το αποτέλεσμα στο D10 θα ΔΕΝ αλλαγή.
Αντί να χρησιμοποιήσετε το WorksheetFunction.SumIf, μπορείτε να χρησιμοποιήσετε το VBA για να εφαρμόσετε μια συνάρτηση SUMIF σε ένα κελί χρησιμοποιώντας το Τύπος ή ΤύποςR1C1 μεθόδους.
Μέθοδος τύπου
Η μέθοδος τύπου σας επιτρέπει να δείξετε συγκεκριμένα μια περιοχή κελιών π.χ.: D2: D10 όπως φαίνεται παρακάτω.
| 123 | Sub TestSumIf ()Εύρος ("D10"). FormulaR1C1 = "= SUMIF (C2: C9.150, D2: D9)"Τέλος υπο |
Μέθοδος FormulaR1C1
Η μέθοδος FormulaR1C1 είναι πιο ευέλικτη διότι δεν σας περιορίζει σε ένα σύνολο κυψελών. Το παρακάτω παράδειγμα θα μας δώσει την ίδια απάντηση με την παραπάνω.
| 123 | Sub TestSumIf ()Εύρος ("D10"). ΤύποςR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C ) "Τέλος υπο |
Ωστόσο, για να κάνουμε τον τύπο πιο ευέλικτο, θα μπορούσαμε να τροποποιήσουμε τον κώδικα έτσι ώστε:
| 123 | Sub TestSumIf ()ActiveCell.FormulaR1C1 = "= SUMIF (R [-8] C [-1]: R [-1] C [-1], 150, R [-8] C: R [-1] C)"Τέλος υπο |
Όπου και αν βρίσκεστε στο φύλλο εργασίας σας, ο τύπος θα προσθέσει στη συνέχεια τα κελιά που πληρούν τα κριτήρια ακριβώς πάνω από αυτό και θα τοποθετήσει την απάντηση στο ActiveCell. Το εύρος μέσα στη συνάρτηση SUMIF πρέπει να αναφέρεται με τη σύνταξη της γραμμής (R) και της στήλης (C).
Και οι δύο αυτές μέθοδοι σάς επιτρέπουν να χρησιμοποιείτε τύπους Dynamic Excel εντός VBA.
Τώρα θα υπάρχει ένας τύπος στο D10 αντί για τιμή.
