Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο θα δείξει πώς να χρησιμοποιήσετε τη συνάρτηση SUMIFS για να αθροίσετε δεδομένα που αντιστοιχούν σε συγκεκριμένους μήνες στο Excel και στα Υπολογιστικά φύλλα Google.
Άθροισμα αν ανά μήνα
Αρχικά, θα δείξουμε πώς να αθροίζουμε δεδομένα που αντιστοιχούν σε ημερομηνίες που εμπίπτουν σε ένα συγκεκριμένο μήνα και έτος.
Μπορούμε να χρησιμοποιήσουμε τη συνάρτηση SUMIFS, μαζί με τις συναρτήσεις DATE, YEAR, MONTH και EOMONTH για να αθροίσουμε το Αριθμός πωλήσεων μέσα στο καθένα Μήνας.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Για να δημιουργήσουμε τον παραπάνω τύπο, ξεκινάμε καθορίζοντας το εύρος ημερομηνιών για κάθε μήνα. Χρησιμοποιούμε τη συνάρτηση DATE για να ορίσουμε την πρώτη ημέρα του μήνα (π.χ. 5/1/2021). Μπορούμε να το κάνουμε αυτό με "σκληρή κωδικοποίηση" της ημερομηνίας στον τύπο:
| 1 | "> =" & ΗΜΕΡΟΜΗΝΙΑ (2021,5,1) |
Or, ακόμα καλύτερα, μπορούμε να κάνουμε τα κριτήρια ευέλικτα με αναφορά σε μια ημερομηνία εντός του κελιού Ε3, εξαγωγή του έτους και του μήνα της ημερομηνίας (και ρύθμιση της ημέρας ίση με 1), όπως φαίνεται στο παραπάνω παράδειγμα:
| 1 | "> =" & ΗΜΕΡΟΜΗΝΙΑ (ΕΤΟΣ (Ε3), ΜΗΝΑΣ (Ε3), 1) |
Για να ορίσουμε την τελευταία ημέρα του μήνα, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Συγκεντρώνοντας όλα αυτά τα κριτήρια, μπορούμε να γράψουμε τον ακόλουθο τύπο SUMIFS:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Κλείδωμα αναφορών κυττάρων
Για να διευκολύνουμε την ανάγνωση των τύπων μας, δείξαμε τους τύπους χωρίς κλειδωμένες αναφορές κελιών:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (YEAR (E3), MONTH (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Αλλά αυτοί οι τύποι δεν θα λειτουργήσουν σωστά όταν αντιγράφονται και επικολλούνται αλλού στο αρχείο σας. Αντ 'αυτού, θα πρέπει να χρησιμοποιήσετε αναφορές κλειδωμένων κελιών όπως αυτό:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & ΗΜΕΡΟΜΗΝΙΑ (ΕΤΟΣ (Ε3), ΜΗΝΑΣ (Ε3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Διαβάστε το άρθρο μας σχετικά με το κλείδωμα αναφορών κυττάρων για να μάθετε περισσότερα.
Μορφοποίηση τιμών μήνα
Σε αυτό το παράδειγμα, έχουμε απαριθμήσει τους μήνες στη στήλη E. Αυτές οι τιμές μηνών είναι στην πραγματικότητα ημερομηνίες που έχουν διαμορφωθεί για να παραλείψουν την ημέρα, χρησιμοποιώντας προσαρμοσμένη μορφοποίηση αριθμών.
Η προσαρμοσμένη μορφή δεδομένων είναι "mmm yyyyy" για εμφάνιση Μαΐου 2022.
Σύνολο ανά μήνα για πολλά χρόνια
Το παραπάνω παράδειγμα συνόψισε δεδομένα με ημερομηνίες που εμπίπτουν σε συγκεκριμένο μήνα και έτος. Αντ 'αυτού, μπορείτε να αθροίσετε δεδομένα με ημερομηνίες που εμπίπτουν σε ένα μήνα σε οποιοδήποτε έτος χρησιμοποιώντας τη συνάρτηση SUMPRODUCT.
| 1 | = SUMPRODUCT (C3: C8,-(MONTH (B3: B8) = MONTH (G3))) |
Σε αυτό το παράδειγμα, χρησιμοποιούμε τη συνάρτηση SUMPRODUCT για να εκτελέσουμε περίπλοκους υπολογισμούς "άθροισμα αν". Ας περάσουμε από τον τύπο.
Αυτή είναι η τελική μας φόρμουλα:
| 1 | = SUMPRODUCT (C3: C8,-(MONTH (B3: B8) = MONTH (G3))) |
Πρώτον, η συνάρτηση SUMPRODUCT παραθέτει τα Αριθμός πωλήσεων για κάθε Ημερομηνία πώλησης και στη συνέχεια συγκρίνει το μήνα του καθενός Ημερομηνία πώλησης έναντι των καθορισμένων Μήνας, επιστρέφοντας TRUE αν ταιριάζουν οι μήνες ή FALSE αν δεν:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Στη συνέχεια, οι διπλές παύλες (-) μετατρέπουν τις τιμές TRUE και FALSE σε 1s και 0s:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Στη συνέχεια, η συνάρτηση SUMPRODUCT πολλαπλασιάζει κάθε ζεύγος καταχωρήσεων στους πίνακες:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Τέλος, οι αριθμοί στον πίνακα συνοψίζονται:
| 1 | =98 |
Περισσότερες λεπτομέρειες σχετικά με τη χρήση των δηλώσεων Boolean και της εντολής "-" σε μια συνάρτηση SUMPRODUCT μπορείτε να βρείτε εδώ
Άθροισμα αν κατά μήνα στα Υπολογιστικά φύλλα Google
Αυτοί οι τύποι λειτουργούν ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel.
