Γενική φόρμουλα
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Περίληψη
Για τον μέσο όρο των χρόνων απόκρισης ανά μήνα, μπορείτε να χρησιμοποιήσετε έναν τύπο βασισμένο στη συνάρτηση AVERAGEIFS, μαζί με τη συνάρτηση EOMONTH.
Στο παράδειγμα που εμφανίζεται, ο τύπος στο G5 είναι:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Εξήγηση
Αυτός ο τύπος χρησιμοποιεί τα ονομαζόμενα εύρη "ημερομηνίες" (B5: B25) και "διάρκεια" (D5: D25). Η διάρκεια της στήλης D είναι σε λεπτά, υπολογίζοντας αφαιρώντας την ημερομηνία που άνοιξε από την ημερομηνία κλεισίματος.
Η συνάρτηση AVERAGEIFS έχει σχεδιαστεί για μέσες περιοχές βάσει πολλαπλών κριτηρίων. Σε αυτήν την περίπτωση, διαμορφώνουμε το AVERAGEIFS για τη μέση διάρκεια ανά μήνα χρησιμοποιώντας δύο κριτήρια: (1) ημερομηνίες αντιστοίχισης μεγαλύτερες ή ίσες με την πρώτη ημέρα του μήνα, (2) ημερομηνίες αντιστοίχισης μικρότερες ή ίσες με την τελευταία ημέρα του μήνα.
Για να ορίσουμε ημερομηνίες ανά μήνα, χρησιμοποιούμε ένα απλό τέχνασμα για να κάνουμε τα πράγματα πιο εύκολα: Στη στήλη F, αντί να πληκτρολογούμε ονόματα μήνα ("Jan", "Feb", Mar ", κ.λπ.) προσθέτουμε προσθέτουμε πραγματικές ημερομηνίες για την πρώτη κάθε μήνα (1/1/2016, 2/1/2016, 3/1/2016, κ.λπ.). Στη συνέχεια, χρησιμοποιούμε την προσαρμοσμένη μορφή ημερομηνίας ("mmm") για να εμφανίσουμε τα ονόματα του μήνα.
Αυτό καθιστά πολύ πιο εύκολο να δημιουργήσουμε τα κριτήρια που χρειαζόμαστε για το AVERAGEIFS χρησιμοποιώντας τιμές στη στήλη F. Για να αντιστοιχίσουμε ημερομηνίες μεγαλύτερες ή ίσες με την πρώτη του μήνα, χρησιμοποιούμε:
">="&F5
Για να αντιστοιχίσουμε ημερομηνίες μικρότερες ή ίσες με την τελευταία ημέρα του μήνα, χρησιμοποιούμε:
"<="&EOMONTH(F5,0)
Παίρνουμε τον EOMONTH να επιστρέψει την τελευταία ημέρα του ίδιου μήνα, παρέχοντας μηδέν για το όρισμα των μηνών .
Σημείωση: η συνένωση με ένα ampersand (&) είναι απαραίτητη κατά τη δημιουργία κριτηρίων βάσει μιας αναφοράς κελιού.
Λύση Pivot Table
Ένας συγκεντρωτικός πίνακας είναι μια εξαιρετική λύση όταν πρέπει να συνοψίσετε ή να υπολογίσετε τα μέσα δεδομένα ανά έτος, μήνα, τρίμηνο και ούτω καθεξής, επειδή οι συγκεντρωτικοί πίνακες παρέχουν στοιχεία ελέγχου για την ομαδοποίηση ημερομηνιών αυτόματα. Για μια παράλληλη σύγκριση τύπων εναντίον συγκεντρωτικών πινάκων, δείτε αυτό το βίντεο: Γιατί συγκεντρωτικοί πίνακες.
