Πολλές φορές, θέλουμε να υπολογίσουμε κάποιες τιμές ανά μήνα. Όπως, πόσες πωλήσεις έγιναν σε έναν συγκεκριμένο μήνα. Λοιπόν, αυτό μπορεί να γίνει εύκολα χρησιμοποιώντας περιστροφικούς πίνακες, αλλά αν προσπαθείτε να έχετε μια δυναμική αναφορά, μπορούμε να χρησιμοποιήσουμε έναν τύπο SUMPRODUCT ή SUMIFS για να αθροίσουμε ανά μήνα.
Ας ξεκινήσουμε με τη λύση SUMPRODUCT.
Ακολουθεί ο γενικός τύπος για να λάβετε άθροισμα ανά μήνα στο Excel
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text))
Άθροισμα_διάγραμμα : Είναι το εύρος που θέλετε να αθροίσετε ανά μήνα.
Εύρος ημερομηνιών : Είναι το εύρος ημερομηνιών που θα αναζητήσετε για μήνες.
Month_text: Είναι ο μήνας σε μορφή κειμένου του οποίου θέλετε να αθροίσετε τιμές.
Τώρα ας δούμε ένα παράδειγμα:
Παράδειγμα: Άθροισμα τιμών ανά μήνα στο Excel
Εδώ έχουμε κάποια τιμή που σχετίζεται με τις ημερομηνίες. Αυτές οι ημερομηνίες είναι Ιανουαρίου, Φεβρουαρίου και Μαρτίου του έτους 2019.
Όπως μπορείτε να δείτε στην παραπάνω εικόνα, όλες οι ημερομηνίες είναι του έτους 2019. Τώρα πρέπει απλώς να αθροίσουμε τις τιμές στο Ε2: Γ2 ανά μήνες στο Ε1: Γ1.
Τώρα για να αθροίσουμε τις τιμές σύμφωνα με τους μήνες γράψτε αυτόν τον τύπο στο Ε2:
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1)))
Εάν θέλετε να το αντιγράψετε σε παρακείμενα κελιά, χρησιμοποιήστε απόλυτες αναφορές ή ονόματα εύρους όπως εσείς στην εικόνα.
Αυτό μας δίνει το ακριβές άθροισμα κάθε μήνα.
Πως δουλεύει?
Ξεκινώντας από μέσα, ας δούμε το ΚΕΙΜΕΝΟ (A2: A9, "MMM") μέρος. Εδώ η συνάρτηση TEXT εξάγει μήνα από κάθε ημερομηνία στο εύρος A2: A9 σε μορφή κειμένου σε έναν πίνακα. Μετάφραση σε τύπο σε = SUMPRODUCT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Στη συνέχεια, TEXT (A2: A9, "MMM")= Ε1: Εδώ κάθε μήνας σε πίνακα συγκρίνεται με κείμενο στο Ε1. Δεδομένου ότι το E1 περιέχει "Jan", κάθε "Jan" στον πίνακα μετατρέπεται σε TRUE και το άλλο σε FALSE. Αυτό μεταφράζει τον τύπο σε = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Επόμενο -(TEXT (A2: A9, "MMM") = E1), μετατρέπει το TRUE FALSE σε δυαδικές τιμές 1 και 0. Ο τύπος μεταφράζεται σε = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Τελικά SUMPRODUCT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): η συνάρτηση SUMPRODUCT πολλαπλασιάζει τις αντίστοιχες τιμές σε $ B $ 2: $ B $ 9 στον πίνακα {1; 1; 0; 1; 0; 0; 1; 0} και τα προσθέτει. Ως εκ τούτου παίρνουμε άθροισμα ανά αξία ως 20052 σε Ε1.
SUM IF Μήνες από διαφορετικό έτος
Στο παραπάνω παράδειγμα, όλες οι ημερομηνίες ήταν του ίδιου έτους. Τι κι αν ήταν από διαφορετικά χρόνια; Ο παραπάνω τύπος θα αθροίζει τις τιμές ανά μήνα, ανεξάρτητα από το έτος. Για παράδειγμα, θα προστεθούν Ιαν του 2018 και Ιαν του 2019, αν χρησιμοποιήσουμε τον παραπάνω τύπο. Κάτι που είναι λάθος στις περισσότερες περιπτώσεις.
Αυτό θα συμβεί επειδή δεν έχουμε κανένα κριτήριο για το έτος στο παραπάνω παράδειγμα. Αν προσθέσουμε και κριτήρια για το έτος, θα λειτουργήσει.
Ο γενικός τύπος για να λάβετε άθροισμα ανά μήνα και έτος στο Excel
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text),-(TEXT (date_range, "yyyy") = TEXT (έτος, 0)))
Εδώ, προσθέσαμε ένα ακόμη κριτήριο που ελέγχει το έτος. Όλα τα άλλα είναι ίδια.
Ας λύσουμε το παραπάνω παράδειγμα, γράψτε αυτόν τον τύπο στο κελί Ε1 για να πάρετε το άθροισμα του Ιανουαρίου το 2017.
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)))
Πριν από την αντιγραφή στα παρακάτω κελιά χρησιμοποιήστε ονόματα εύρους ή απόλυτες αναφορές. Στην εικόνα, έχω χρησιμοποιήσει εύρος ονομάτων για αντιγραφή στα παρακείμενα κελιά.
Τώρα μπορούμε να δούμε το άθροισμα της αξίας κατά μήνες και έτη επίσης.
Πώς λειτουργεί;
Το πρώτο μέρος του τύπου είναι το ίδιο με το προηγούμενο παράδειγμα. Ας κατανοήσουμε το πρόσθετο μέρος που προσθέτει τα κριτήρια του έτους.
-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)): TEXT (A2: A9, "yyyy") μετατρέπει την ημερομηνία σε A2: A9 ως έτη σε μορφή κειμένου σε πίνακα. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Τις περισσότερες φορές, το έτος γράφεται σε μορφή αριθμού. Για να συγκρίνω έναν αριθμό με κείμενο εμείς, έχω μετατρέψει το έτος int κείμενο χρησιμοποιώντας TEXT (D2,0). Στη συνέχεια συγκρίναμε αυτό το έτος κειμένου με σειρά ετών ως TEXT (A2: A9, "yyyy") = TEXT (D2,0). Αυτό επιστρέφει έναν πίνακα true-false {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Στη συνέχεια μετατρέψαμε το true false σε αριθμό χρησιμοποιώντας τον τελεστή. Αυτό μας δίνει {0; 0; 1; 1; 0; 1; 0; 1}.
Έτσι τελικά ο τύπος θα μεταφραστεί σε = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Όπου ο πρώτος πίνακας είναι οι τιμές. Ο επόμενος ταιριάζει με τον μήνα και ο τρίτος με το έτος. Τέλος παίρνουμε το άθροισμα των αξιών μας ως 2160.
Χρήση της συνάρτησης SUMIFS για άθροιση ανά μήνα
Γενικός τύπος
= SUMIFS (άθροισμα_μέτρου, ημερομηνία_διάγραμμα, ”> =” & ημερομηνία έναρξης, ημερομηνία_διάγραμμα, ”<=” & EOMONTH (ημερομηνία έναρξης, 0))
Εδώ,Άθροισμα_διάγραμμα : Είναι το εύρος που θέλετε να αθροίσετε ανά μήνα.
Εύρος ημερομηνιών : Είναι το εύρος ημερομηνιών που θα αναζητήσετε για μήνες.
Ημερομηνία έναρξης : Είναι η ημερομηνία έναρξης από την οποία θέλετε να αθροίσετε. Για αυτό το παράδειγμα, θα είναι η 1η του συγκεκριμένου μήνα.
Παράδειγμα: Άθροισμα τιμών ανά μήνα στο Excel
Εδώ έχουμε κάποια τιμή που σχετίζεται με τις ημερομηνίες. Αυτές οι ημερομηνίες είναι Ιανουαρίου, Φεβρουαρίου και Μαρτίου του έτους 2019.
Απλώς πρέπει να αθροίσουμε αυτές τις τιμές μέχρι εκεί. Τώρα ήταν εύκολο αν είχαμε μήνες και χρόνια χωριστά. Αλλά δεν είναι. Δεν μπορούμε να χρησιμοποιήσουμε καμία στήλη βοήθειας εδώ.
Έτσι, για να προετοιμάσω την αναφορά, έχω ετοιμάσει μια μορφή αναφοράς που περιέχει μήνα και άθροισμα τιμών. Στη στήλη μήνα, έχω στην πραγματικότητα ημερομηνία έναρξης του μήνα. Για να δείτε μόνο τον μήνα, επιλέξτε ημερομηνία έναρξης και πατήστε CTRL+1.
Σε προσαρμοσμένη μορφή, γράψτε "mmm".
Τώρα έχουμε έτοιμα τα δεδομένα μας. Ας αθροίσουμε τις τιμές ανά μήνα.
Γράψτε αυτόν τον τύπο στο Ε3 για άθροισμα ανά μήνα.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Χρησιμοποιήστε απόλυτες αναφορές ή ονόματα εύρους πριν αντιγράψετε τον τύπο.
Λοιπόν, τελικά πήραμε το αποτέλεσμα.
Λοιπόν, πώς λειτουργεί;
Όπως γνωρίζουμε ότι η συνάρτηση SUMIFS μπορεί να αθροίσει τιμές σε πολλαπλά κριτήρια.
Στο παραπάνω παράδειγμα, το πρώτο κριτήριο αθροίζει όλες τις τιμές στο Β3: Β10 όπου, η ημερομηνία στο Α3: Το Α10 είναι μεγαλύτερο ή ίσο με την ημερομηνία στο Δ3Το Το D3 περιέχει 1-Jan. Μεταφράζεται και αυτό.
= SUMIFS (B3: B10, A3: A10, "> =" & "1-jan-2019", A3: A10, "<=" EOMONTH (D3,0))
Τα επόμενα κριτήρια είναι άθροισμα μόνο εάν Ημερομηνία εισαγωγής A3: A10 είναι μικρότερη ή ίση με EOMONTH (D3,0). Η συνάρτηση EOMONTH επιστρέφει τον σειριακό αριθμό της τελευταίας ημερομηνίας του μήνα που παρέχεται. Τέλος, ο τύπος μεταφράζεται επίσης.
= SUMIFS (B3: B10, A3: A10, "> = 1-jan-2019", A3: A10, "<= 31-jan-2019")
Ως εκ τούτου, λαμβάνουμε το άθροισμα ανά μήνα σε excel.
Το πλεονέκτημα αυτής της μεθόδου είναι ότι μπορείτε να προσαρμόσετε την ημερομηνία έναρξης για την σύνοψη των τιμών.
Εάν τα ραντεβού σας έχουν διαφορετικά χρόνια από ό, τι είναι καλύτερο να χρησιμοποιήσετε πίνακες περιστροφής. Οι συγκεντρωτικοί πίνακες μπορούν να σας βοηθήσουν να διαχωρίσετε εύκολα τα δεδομένα σε ετήσια, τριμηνιαία και μηνιαία μορφή.
Λοιπόν, ναι, έτσι μπορείτε να αθροίσετε τις τιμές ανά μήνα. Και οι δύο τρόποι έχουν τις δικές τους ειδικότητες. Επιλέξτε τον τρόπο που σας αρέσει.
Εάν έχετε ερωτήματα σχετικά με αυτό το άρθρο ή άλλα ερωτήματα που σχετίζονται με το Excel και το VBA, η ενότητα σχολίων είναι ανοιχτή για εσάς.
Σχετικά άρθρα:
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel
SUMIFS με ημερομηνίες στο Excel
SUMIF με μη κενά κελιά
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIFS στο Excel
SUMIFS χρησιμοποιώντας τη λογική AND-OR
Δημοφιλή άρθρα
50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας: Προχωρήστε γρηγορότερα στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε το tσυνάρτηση VLOOKUP στο Excel: Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel: Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel: Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.