Σε αυτό το βίντεο, θα δούμε πώς να υπολογίζουμε και να επισημαίνουμε τις ημερομηνίες λήξης.
Ας υποθέσουμε ότι η εταιρεία σας έχει ξεκινήσει κάποιο πρόγραμμα συμμετοχής κάποιου είδους και το αφεντικό σας μόλις σας έστειλε ένα σύνολο δεδομένων. Σας έχει δώσει μια λίστα με 1000 άτομα που έχουν ανανεώσει την ιδιότητα μέλους τον περασμένο χρόνο και ψάχνει πολλά πράγματα.
Πρώτον, θέλει να υπολογίσετε μια ημερομηνία λήξης στο μέλλον ένα έτος, την τελευταία ημέρα του ίδιου μήνα η συνδρομή ανανεώθηκε.
Δεύτερον, θέλει να δει πόσες ημέρες μένουν πριν από τη λήξη.
Τρίτον, θέλει να δει την κατάσταση "Έληξε" για τυχόν συνδρομές που έχουν ήδη λήξει και "Λήγει σύντομα" για τυχόν συνδρομές που λήγουν τις επόμενες 30 ημέρες.
Τέλος, είπε ότι θα ήταν ωραίο να βλέπουμε τα ληγμένα μέλη να επισημαίνονται με ροζ χρώμα και αυτά που λήγουν σύντομα να επισημαίνονται με κίτρινο χρώμα.
Επίσης - μπορεί να το έχει πριν από τη μεσημεριανή συνάντησή της το μεσημέρι;
Σίγουρα.
Αρχικά, ας μετατρέψουμε αυτά τα δεδομένα σε έναν κατάλληλο πίνακα Excel. Αυτό θα κάνει τους τύπους πολύ πιο εύκολο να εισαχθούν, καθώς το Excel θα τους αντιγράψει αυτόματα, καθώς πηγαίνουμε.
Τώρα ας υπολογίσουμε τις ημερομηνίες λήξης. Υποτίθεται ότι θα είναι στο τέλος του ίδιου μήνα ένα χρόνο αργότερα, αλλά ας χρησιμοποιήσουμε πρώτα ένα απλό hack, για να μπείτε στο ballpark. Όπως έχετε δει σε προηγούμενα βίντεο, οι ημερομηνίες είναι απλώς σειριακοί αριθμοί, έτσι μπορούμε απλώς να εισαγάγουμε έναν τύπο που προσθέτει 365 ημέρες στην ημερομηνία Ανανέωσης.
Αυτή είναι μια καλή αρχή. Μπορούμε να ολοκληρώσουμε τη λύση και να επιστρέψουμε για να το διορθώσουμε αργότερα.
Όταν επιλύετε ένα πιο περίπλοκο πρόβλημα στο Excel, είναι καλή ιδέα να επικυρώσετε τη συνολική προσέγγιση και, στη συνέχεια, επιστρέψτε στις λεπτομέρειες στο τέλος. Δεν θέλετε να κολλήσετε σε ένα μικρό πράγμα στην αρχή, ειδικά εάν η προσέγγιση μπορεί να αλλάξει.
Τώρα που έχουμε ημερομηνία λήξης, μπορούμε πλέον να υπολογίσουμε τις ημέρες που απομένουν. Αυτό χρειάζεται ενημέρωση αυτόματα στο μέλλον, οπότε θα χρησιμοποιήσουμε τη συνάρτηση ΣΗΜΕΡΑ, η οποία επιστρέφει πάντα στην σημερινή ημερομηνία.
Ο τύπος είναι απλά E5 μείον ΣΗΜΕΡΑ (). Όταν χτυπήσω την επιστροφή, έχουμε ημέρες που απομένουν πριν από τη λήξη. Οι αρνητικοί αριθμοί υποδεικνύουν ότι η ιδιότητα μέλους έχει ήδη λήξει.
Για κατάσταση, θα χρησιμοποιήσουμε έναν απλό ένθετο τύπο IF. Εάν οι μέρες που απομένουν είναι μικρότερες από το μηδέν, η συνδρομή λήγει. Διαφορετικά, εάν οι μέρες που απομένουν είναι μικρότερες από 30, η κατάσταση θα πρέπει να είναι "Λήγει σύντομα". Διαφορετικά, η κατάσταση δεν είναι τίποτα.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Στη συνέχεια πρέπει να δημιουργήσουμε κανόνες μορφοποίησης υπό όρους που επισημαίνουν αυτές τις τιμές.
Αρχικά, επιλέξτε τα δεδομένα και ορίστε το ενεργό κελί στην επάνω δεξιά γωνία. Στη συνέχεια, δημιουργήστε έναν κανόνα τύπου που ελέγχει το ενεργό κελί για την τιμή "Έληξε". Η στήλη πρέπει να είναι κλειδωμένη.
Τώρα επαναλάβετε την ίδια διαδικασία για τις συνδρομές που "Λήγει σύντομα".
Αυτό φαίνεται καλό. Απλώς πρέπει να καθορίσουμε τις ημερομηνίες λήξης για να προσγειωθούμε την τελευταία ημέρα του μήνα.
Λοιπόν, αποδεικνύεται ότι υπάρχει μια δροσερή λειτουργία που ονομάζεται EOMONTH (για το τέλος του μήνα) που παίρνει την τελευταία ημέρα ενός μήνα στο παρελθόν ή στο μέλλον.
Η ημερομηνία έναρξης ανανεώνεται και 12 μήνες.
Και εκεί το έχετε, όλα όσα ήθελε το αφεντικό σας, και έχετε ακόμα χρόνο για ένα φλιτζάνι καφέ πριν από το μεσημεριανό γεύμα.
Σειρά μαθημάτων
Βασικός τύποςΣχετικές συντομεύσεις
Εισαγωγή πίνακα Ctrl + T ⌃ + T Ανάπτυξη ή σύμπτυξη κορδέλας Ctrl + F1 ⌘ + ⌥ + R Επιλογή πίνακα Ctrl + A ⌘ + A Μετακίνηση ενεργού κελιού δεξιόστροφα στην επιλογή Ctrl + . ⌃ + .
