Ένας αναγνώστης μου έστειλε ένα ενδιαφέρον πρόβλημα φόρμουλας αυτήν την εβδομάδα, οπότε σκέφτηκα ότι θα το μοιραζόμουν ως φόρμουλα πρόκληση. Το πρόβλημα είναι αυτό:
Έχετε μια σταθερή μηνιαία πληρωμή, μια ημερομηνία έναρξης και έναν συγκεκριμένο αριθμό μηνών. Τι τύπο μπορείτε να χρησιμοποιήσετε για να αθροίσετε τις συνολικές πληρωμές ανά έτος, με βάση το ακόλουθο φύλλο εργασίας:
Με άλλα λόγια, ποιος τύπος λειτουργεί στο E5, αντιγράφεται στο I5, για να λαμβάνει ένα ποσό για κάθε έτος που εμφανίζεται;
Ήρθα με μια φόρμουλα, αλλά θα ήθελα επίσης να δω τις ιδέες σας. Αν σας ενδιαφέρει, αφήστε ένα σχόλιο με τον τύπο που προτείνετε.
Μπορείτε να χρησιμοποιήσετε τα ακόλουθα εύρη τιμών στον τύπο σας εάν θέλετε: mos (C5), ποσό (C6), έναρξη (C7), τέλος (C8).
Μπορείτε να κατεβάσετε το φύλλο εργασίας παρακάτω.
Απάντηση (κάντε κλικ για επέκταση)Τόσες υπέροχες φόρμουλες! Ευχαριστώ όλους όσους αφιέρωσαν χρόνο για να υποβάλουν μια απάντηση. Ακολουθούν οι ασυνήθιστες σκέψεις μου για το πρόβλημα και μερικές από τις παρακάτω λύσεις.
Σημείωση: Ποτέ δεν διευκρίνισα πώς πρέπει να χειρίζονται τα όρια του μήνα. Ακολούθησα ένα άλλο φύλλο εργασίας ως παράδειγμα. Βασικές πληροφορίες είναι 30 πληρωμές, από 1 Μαρτίου 10 πληρωμές το 2017, 12 πληρωμές το 2018 και 8 πληρωμές (το υπόλοιπο) το 2019.
Έτσι, εάν προσπαθείτε να καταλάβετε πώς μπορείτε να προσπαθήσετε να λύσετε ένα πρόβλημα σαν αυτό, εστιάστε πρώτα στις πληρωμές. Μόλις μάθετε πόσες πληρωμές είναι σε ένα χρόνο, μπορείτε απλά να πολλαπλασιάσετε αυτόν τον αριθμό με το ποσό και τελειώσατε.
Λοιπόν, πώς μπορείτε να βρείτε τον αριθμό των πληρωμών σε ένα δεδομένο έτος; Στα παρακάτω σχόλια θα βρείτε πολλές καλές ιδέες. Υπάρχουν πολλά μοτίβα που παρατήρησα και έχω αναφέρει μερικά παρακάτω. Αυτό είναι ένα έργο σε εξέλιξη…
Σχεδιαστικά πρότυπα
IF + AND/OR + YEAR + MONTH
Το IF είναι μια αξιόπιστη κατάσταση αναμονής σε τόσους πολλούς τύπους και χρησιμοποιείται σε πολλούς από τους προτεινόμενους τύπους για να διαπιστώσει εάν το έτος ενδιαφέροντος είναι "σε όρια" των ημερομηνιών έναρξης και λήξης. Σε πολλές περιπτώσεις, το IF συνδυάζεται με OR ή AND για να διατηρεί τους τύπους συμπαγείς.
IFERROR + DATEDIF + MAX + MIN
Το DATEDIF μπορεί να επιστρέψει τη διαφορά μεταξύ δύο ημερομηνιών σε μήνες, οπότε η ιδέα εδώ είναι να χρησιμοποιήσετε τα MAX και MIN (για συντομία, αντί για IF) για να υπολογίσετε μια ημερομηνία έναρξης και μια ημερομηνία λήξης για κάθε έτος και να αφήσετε το DATEDIF να πάρει τους μήνες μεταξύ. Το DATEDIF ρίχνει ένα σφάλμα #NUM όταν η ημερομηνία έναρξης δεν είναι μικρότερη από την ημερομηνία λήξης, οπότε το IFERROR χρησιμοποιείται για να πιάσει το σφάλμα και να επιστρέψει μηδέν. Δείτε τους τύπους των 闫 强, Arun και David παρακάτω.
MAX + MIN + YEAR + MONTH
Οι φόρμουλες του Robert και του Peter κάνουν σχεδόν όλη τη δουλειά με τα MAX και MIN, χωρίς να υπάρχει IF. Φοβερο. Εάν η ιδέα της χρήσης MAX και MIN για αντικατάσταση IF είναι νέα για εσάς, αυτό το άρθρο εξηγεί την ιδέα.
DAYS360
Η συνάρτηση Excel DAYS360 επιστρέφει τον αριθμό ημερών μεταξύ δύο ημερομηνιών με βάση ένα έτος 360 ημερών. Είναι ένας τρόπος υπολογισμού μηνών με βάση την ιδέα ότι κάθε μήνα έχει 30 ημέρες.
SUM + DATE
Αυτή είναι η αναποτελεσματική (αλλά κομψή!) Προσέγγισή μου χρησιμοποιώντας τη συνάρτηση DATE και μια σταθερά πίνακα με έναν αριθμό για κάθε μήνα. Η συνάρτηση DATE ξεκινά μια ημερομηνία για κάθε μήνα ενός έτους χρησιμοποιώντας μια σταθερά πίνακα και χρησιμοποιείται λογική boolean για τον έλεγχο της αλληλεπικάλυψης.
