Περίληψη
Για να συνοψίσετε ανά μήνα σε στήλες μπορείτε να χρησιμοποιήσετε τη συνάρτηση SUMIFS μαζί με τη συνάρτηση EOMONTH. Στο παράδειγμα που εμφανίζεται, ο τύπος στο G5 είναι:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Αυτός ο τύπος χρησιμοποιεί τρία ονομαστικά εύρη: "ποσό" (D5: D15), "πελάτη" (B5: B15) και "ημερομηνία" (C5: C15).
Εξήγηση
Η συνάρτηση SUMIFS έχει σχεδιαστεί για να αθροίζει τις τιμές σε ένα εύρος με βάση ένα ή περισσότερα κριτήρια. Σε αυτήν την περίπτωση, χρειαζόμαστε τρία κριτήρια:
- Όνομα πελάτη = πελάτης στη στήλη F
- Ημερομηνία> = πρώτη του μήνα (από την ημερομηνία στη σειρά 4)
- Ημερομηνία <= τέλος μήνα (από την ημερομηνία στη σειρά 4)
Βασικό σημείο: τα ονόματα του μήνα στο G4: I4 είναι πραγματικά έγκυρες ημερομηνίες, μορφοποιημένα με την προσαρμοσμένη μορφή αριθμού "mmm". Αυτό μας επιτρέπει να χρησιμοποιήσουμε τις τιμές ημερομηνίας στο G4: I4 απευθείας για τα κριτήρια # 2 και # 3 παραπάνω.
Πίσω στο SUMIFS, το πρώτο ζεύγος κριτηρίων / κριτηρίων χρησιμοποιείται για τον έλεγχο του ονόματος πελάτη:
client,$F5
Σημείωση F5 είναι μια μικτή αναφορά, με τη στήλη κλειδωμένη. Αυτό επιτρέπει στη σειρά να αλλάξει καθώς ο τύπος αντιγράφεται μέσω του πίνακα, αλλά το όνομα πελάτη προέρχεται πάντα από τη στήλη F.
Το δεύτερο ζεύγος εύρους / κριτηρίων χρησιμοποιείται για τον έλεγχο ημερομηνιών έναντι της πρώτης του μήνα:
date,">="&G$4
Όπως αναφέρθηκε παραπάνω, οι τιμές στο G4: I4 είναι στην πραγματικότητα ημερομηνίες: 1 Ιανουαρίου 2019, 1 Φεβρουαρίου 2019 και 1 Μαρ 2019. Επομένως, αυτό το κριτήριο ελέγχει απλώς ημερομηνίες μεγαλύτερες από τις ημερομηνίες του πρώτου μήνα στη σειρά 4. Σημειώστε ότι αυτή είναι μια άλλη μικτή αναφορά, αυτή τη φορά με τη σειρά κλειδωμένη. Αυτό επιτρέπει στη στήλη να αλλάξει καθώς ο τύπος αντιγράφεται σε ολόκληρο τον πίνακα, αλλά διατηρεί τον αριθμό σειράς σταθερό. Η συνένωση με ένα συμπλεκτικό σύμβολο (&) είναι απαραίτητη κατά τη δημιουργία κριτηρίων που χρησιμοποιούν λογικό τελεστή και μια τιμή από άλλο κελί.
Το τρίτο ζεύγος κριτηρίων / κριτηρίων χρησιμοποιείται για τον έλεγχο ημερομηνιών έναντι της τελευταίας ημέρας του μήνα:
date,"<="&EOMONTH(G$4,0)
Για να λάβουμε την τελευταία ημέρα κάθε μήνα, χρησιμοποιούμε τη συνάρτηση EOMONTH την ημερομηνία από την κεφαλίδα της στήλης στη σειρά 4. Με μηδενικό όριο για το όρισμα των μηνών, το EOMONTH επιστρέφει την τελευταία ημέρα του ίδιου μήνα. Και πάλι, η αναφορά στο G4 αναμιγνύεται για να μην αλλάξει η σειρά.
Λύση Pivot Table
Ένας συγκεντρωτικός πίνακας θα ήταν μια εξαιρετική λύση για αυτό το πρόβλημα, επειδή μπορεί να ομαδοποιηθεί αυτόματα ανά μήνα χωρίς καθόλου τύπους. Για μια παράλληλη σύγκριση τύπων εναντίον συγκεντρωτικών πινάκων, δείτε αυτό το βίντεο: Γιατί συγκεντρωτικοί πίνακες.
