Γενική φόρμουλα
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Περίληψη
Κατά μέσο όρο ανά μήνα, μπορείτε να χρησιμοποιήσετε έναν τύπο βασισμένο στη συνάρτηση AVERAGEIFS, με τη βοήθεια της συνάρτησης EOMONTH. Στο παράδειγμα που εμφανίζεται, ο τύπος στο F4 είναι:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Αυτός ο τύπος χρησιμοποιεί τα ονομαζόμενα εύρη "ποσά" (D5: D104) και "ημερομηνίες" (C5: C104).
Εξήγηση
Η συνάρτηση AVERAGEIFS μπορεί να κυμαίνεται κατά μέσο όρο βάσει πολλαπλών κριτηρίων. Σε αυτήν την περίπτωση, διαμορφώνουμε τα AVERAGEIFS σε μέσο όρο ανά μήνα χρησιμοποιώντας δύο κριτήρια: (1) ημερομηνίες αντιστοίχισης μεγαλύτερες ή ίσες με την πρώτη ημέρα του μήνα, (2) ημερομηνίες αντιστοίχισης μικρότερες ή ίσες με την τελευταία ημέρα του μήνα. Εάν έχουμε κωδικοποιήσει ημερομηνίες για τον Ιανουάριο του 2016 στον τύπο χρησιμοποιώντας τη συνάρτηση DATE, θα μοιάζει με αυτό.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Αλλά δεν θέλουμε να κάνουμε σκληρές ημερομηνίες, θέλουμε το Excel να δημιουργήσει αυτές τις ημερομηνίες για εμάς. Κανονικά, αυτό είναι πόνο, γιατί αν προσθέσετε ονόματα μήνα ως κείμενο (δηλ. "Ιανουάριος", "Φεβρουάριος", "Μάρτιος", κ.λπ.) στη στήλη ΣΤ, πρέπει να πάτε σε επιπλέον πρόβλημα για να δημιουργήσετε ημερομηνίες που μπορείτε να χρησιμοποιήσετε για κριτήρια .
Ωστόσο, σε αυτήν την περίπτωση, χρησιμοποιούμε ένα απλό τέχνασμα για να διευκολύνουμε τα πράγματα: Στη στήλη F, αντί να πληκτρολογούμε ονόματα μήνα, προσθέτουμε πραγματικές ημερομηνίες για την πρώτη κάθε μήνα (1/1/2016, 2/1/2016, 3 / 1/2016, κ.λπ.), και χρησιμοποιήστε μια προσαρμοσμένη μορφή ημερομηνίας ("mmm") για να εμφανίσετε τα ονόματα του μήνα.
Αυτό διευκολύνει τη δημιουργία των κριτηρίων που χρειαζόμαστε για το AVERAGEIFS. Για να αντιστοιχίσουμε ημερομηνίες μεγαλύτερες ή ίσες με την πρώτη του μήνα, χρησιμοποιούμε:
">="&E4
Και για να αντιστοιχίσουμε ημερομηνίες μικρότερες ή ίσες με την τελευταία ημέρα του μήνα, χρησιμοποιούμε:
"<="&EOMONTH(E4,0)
Ο EOMONTH επιστρέφει αυτόματα την τελευταία ημέρα του ίδιου μήνα επειδή παρέχουμε μηδέν για το όρισμα των μηνών.
Σημείωση: η συνένωση με ένα ampersand (&) είναι απαραίτητη κατά τη δημιουργία κριτηρίων βάσει μιας αναφοράς κελιού.
Λύση Pivot Table
Ένας συγκεντρωτικός πίνακας είναι μια εξαιρετική λύση όταν πρέπει να συνοψίσετε δεδομένα ανά έτος, μήνα, τρίμηνο και ούτω καθεξής, επειδή οι συγκεντρωτικοί πίνακες παρέχουν στοιχεία ελέγχου για αυτόματη ομαδοποίηση κατά ημερομηνία. Για μια παράλληλη σύγκριση τύπων εναντίον συγκεντρωτικών πινάκων, δείτε αυτό το βίντεο: Γιατί συγκεντρωτικοί πίνακες.
