Σε αυτό το άρθρο, θα μάθουμε πώς να αποκτήσετε την τελευταία καταχώριση ανά μήνα στο Excel.
Σενάριο:
Κατά την εργασία με σύνολα δεδομένων, μερικές φορές χρειάζεται να εξάγουμε κάποια αποτελέσματα με βάση διαφορετικά κριτήρια. Εδώ τα κριτήρια προβλήματος είναι ότι η τιμή πρέπει να είναι η τελευταία καταχώριση που αντιστοιχεί σε ένα δεδομένο μήνα. Το πρόβλημα μπορεί να ερμηνευτεί με δύο τρόπους.
- Τελευταία καταχώριση στη στήλη ανά μήνα
- Τιμή τελευταίας ημερομηνίας μήνα στη στήλη
Πρέπει να σκέφτεστε ποια είναι η διαφορά μεταξύ των δύο. Η διαφορά είναι ότι το πρώτο πρόβλημα εξάγει την τελευταία καταχώριση από τη στήλη που ταιριάζει με το όνομα του μήνα, ενώ το δεύτερο πρόβλημα εξάγει την πλησιέστερη τιμή ημερομηνίας από την ημερομηνία του τέλους του μήνα. Θα καταλάβουμε και τις δύο καταστάσεις μία προς μία. Πρώτον, θα εξετάσουμε τον τρόπο λήψης της τελευταίας καταχώρισης στη στήλη ανά μήνα.
Πώς να αποκτήσετε την τελευταία καταχώριση στη στήλη ανά μήνα στο Excel;
Για αυτό θα χρησιμοποιήσουμε τη συνάρτηση TEXT και τη συνάρτηση LOOKUP. Εδώ χρησιμοποιούμε ένα χαρακτηριστικό της αναζήτησης συνάρτησης LOOKUP για την τελευταία τιμή. Η συνάρτηση LOOKUP λαμβάνει 3 ορίσματα τιμή αναζήτησης, πίνακα αναζήτησης και πίνακα αποτελεσμάτων. Έτσι χρησιμοποιούμε το όρισμα πίνακα αναζήτησης ως πίνακας 1/αναζήτησης. Για περισσότερες λεπτομέρειες, δείτε τη σύνταξη του παρακάτω τύπου:
σύνταξη τύπου:
| = LOOKUP (2,1/(TEXT (ημερομηνίες, "mmyyyy") = TEXT (month, "mmyyyy")), τιμές) |
ημερομηνίες: πίνακας ημερομηνιών στα δεδομένα
τιμές: πίνακας αποτελεσμάτων στα δεδομένα
μήνα: κριτήρια μήνα
Παράδειγμα:
Όλα αυτά μπορεί να προκαλούν σύγχυση στην κατανόηση. Ας καταλάβουμε πώς να χρησιμοποιήσετε αυτόν τον τύπο σε ένα παράδειγμα. Εδώ έχουμε δεδομένα με τιμές ημερομηνίας και τιμής. Για αυτό χρειαζόμαστε την πιο πρόσφατη καταχώριση του μήνα Ιανουάριος 2019 που θα είναι η τελευταία καταχώριση με ημερομηνία τον Ιανουάριο του 2019. Εδώ για τον πίνακα ημερομηνιών και τον πίνακα τιμών χρησιμοποιήσαμε ονόματα εύρους.
Χρησιμοποιήστε τον τύπο:
| = LOOKUP (2,1/(TEXT (ημερομηνίες, "mmyyyy") = TEXT (F9, "mmyyyy")), Τιμή) |
Εξήγηση:
- ΚΕΙΜΕΝΟ (ημερομηνίες, "μμυιιιιιιιιιιιιιιιιιιιιιιιιιιι") θα επιστρέψει τον πίνακα των τιμών σε μορφή "μμυιιιιιιιιιιιιιιιιιιιιη μετατροπή του σε τιμές κειμένου που είναι
{ "012019" ; "012019" ; "012019" ; "012019" ; "022019" ; "022019" ; "022019" ; "022019" ; "032019" ; "032019" ; "032019" ; "032019" ; "032019" }
- Το TEXT (F9, "mmyyyy") θα επιστρέψει την τιμή ημερομηνίας (στο κελί F9) σε μορφή "mmyyyy" μετά τη μετατροπή της τιμής ημερομηνίας αναζήτησης που είναι "012019" και αυτή η τιμή θα αντιστοιχιστεί με τον παραπάνω αναφερόμενο πίνακα.
- Λαμβάνοντας τη διαίρεση με 1, θα μετατρέψετε τον πίνακα των τιμών True σε 1s και των λανθασμένων τιμών σε σφάλμα #DIV/0. Έτσι θα πάρουμε τον πίνακα που επιστρέφεται ως.
{1; 1; 1; 1; #DIV/0! ? #DIV/0! ? #DIV/0! ? #DIV/0! ? #DIV/0! ? #DIV/0! ? #DIV/0! ? #DIV/0! ? #DIV/0! }
- Τώρα η συνάρτηση LOOKUP βρίσκει την τιμή 2 στον πίνακα που δεν θα βρεθεί. Έτσι επιστρέφει την καταχώριση που αντιστοιχεί στην τελευταία 1 τιμή.
Χρησιμοποιούνται ονόματα εύρους
ημερομηνίες: C3: C15
Τιμή: D3: D15
Η τελευταία τιμή είναι 78,48 που αντιστοιχεί στην 31-01-2019. Αντιγράψτε τον τύπο σε άλλα κελιά χρησιμοποιώντας το Ctrl + D ή σύροντας προς τα κάτω από την κάτω δεξιά γωνία του κελιού.
Όπως μπορείτε να δείτε, ο τύπος επιστρέφει όλες τις απαιτούμενες τιμές. Η συνάρτηση επιστρέφει σφάλμα #N/A, εάν η τιμή του μήνα αναζήτησης δεν ταιριάζει με καμία καταχώριση ημερομηνίας. Στο παραπάνω παράδειγμα, οι καταχωρήσεις ημερομηνίας είναι ταξινομημένες. Έτσι, η τελευταία καταχώρηση του μήνα ταιριάζει με την τελευταία ημερομηνία του μήνα. Τώρα εάν οι καταχωρήσεις ημερομηνίας δεν είναι ταξινομημένες, τότε θα ταξινομήσουμε πρώτα τις τιμές ημερομηνίας και θα χρησιμοποιήσουμε τον παραπάνω τύπο.
Πώς να λάβετε την καταχώριση Τελευταία ημερομηνία ανά μήνα στη στήλη στο Excel;
Για αυτό θα χρησιμοποιήσουμε τη συνάρτηση VLOOKUP και EOMONTH. Η συνάρτηση EOMONTH λαμβάνει την τιμή ημερομηνίας του μήνα και επιστρέφει την τελευταία ημερομηνία του απαιτούμενου μήνα. Η συνάρτηση VLOOKUP βρίσκει την τελευταία ημερομηνία του μήνα ή την πλησιέστερη προηγούμενη ημερομηνία και επιστρέφει μια τιμή που αντιστοιχεί σε αυτήν την τιμή.
Χρησιμοποιήστε τον τύπο:
| = VLOOKUP (EOMONTH (F3,0), πίνακας, 2) |
Επεξήγηση:
- Το EOMONTH (F3,0) επιστρέφει την τελευταία ημερομηνία του ίδιου μήνα. 0 όρισμα που χρησιμοποιήθηκε για να επιστρέψει την ημερομηνία από τον ίδιο μήνα.
- Τώρα η τιμή αναζήτησης γίνεται η τελευταία ημερομηνία του συγκεκριμένου μήνα.
- Τώρα η τιμή θα αναζητηθεί στην πρώτη στήλη του πίνακα και θα αναζητήσει την τελευταία ημερομηνία, αν βρεθεί επιστρέφει την τιμή που αντιστοιχεί στην τιμή και αν δεν βρεθεί τότε επιστρέφει το τελευταίο αποτέλεσμα αντιστοίχισης, πλησιέστερο στην τιμή αναζήτησης και επιστρέφει το αντίστοιχο αποτέλεσμα.
- Η συνάρτηση επιστρέφει την τιμή από τη 2η στήλη του πίνακα καθώς το τρίτο όρισμα είναι 2.
Εδώ ο τύπος επιστρέφει το 78.48 ως αποτέλεσμα. Τώρα αντιγράψτε τον τύπο σε άλλα κελιά χρησιμοποιώντας τη συντόμευση Ctrl + D ή σύροντας προς τα κάτω από τη δεξιά κάτω γωνία του κελιού.
Όπως μπορείτε να δείτε, ο τύπος λειτουργεί καλά. Υπάρχει μόνο ένα πρόβλημα. Καθώς αναζητούμε την τελευταία ημερομηνία που αντιστοιχεί στον μήνα Απρίλιο, η συνάρτηση επιστρέφει την τιμή που αντιστοιχεί στον Μάρτιο, καθώς δεν υπάρχει καταχώριση ημερομηνίας Απριλίου στον πίνακα. Βεβαιωθείτε ότι έχετε πιάσει αυτά τα αποτελέσματα.
Εδώ είναι όλες οι σημειώσεις παρατήρησης σχετικά με τη χρήση του τύπου.
Σημειώσεις:
- Χρησιμοποιήστε τον τύπο που αντιστοιχεί στο πρόβλημα που αναφέρεται στο άρθρο.
- Οι συνάρτηση VLOOKUP και LOOKUP είναι συναρτήσεις όταν η τιμή αναζήτησης είναι αριθμός και δεν υπάρχει στον πίνακα αναζήτησης, επιστρέφει την τιμή που αντιστοιχεί στον πλησιέστερο αριθμό μικρότερο από την τιμή αναζήτησης.
- Το Excel αποθηκεύει τις τιμές ημερομηνίας ως αριθμούς.
- Οι δύο παραπάνω δηλωμένες συναρτήσεις επιστρέφουν μόνο μία τιμή.
- Χρησιμοποιήστε το 4ο όρισμα στη συνάρτηση VLOOKUP ως 0 για να λάβετε την ακριβή αντιστοίχιση της τιμής αναζήτησης στον πίνακα.
Ελπίζω ότι αυτό το άρθρο σχετικά με τον τρόπο λήψης της τελευταίας καταχώρισης ανά μήνα στο Excel είναι επεξηγηματικό. Βρείτε περισσότερα άρθρα σχετικά με την εξαγωγή τιμών από τον πίνακα χρησιμοποιώντας τύπους εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email
Βρείτε την τελευταία σειρά με μικτά δεδομένα στο Excel : εργασία με αριθμούς, κείμενο ή κενό κελί στον ίδιο πίνακα. Εξαγάγετε την τελευταία σειρά με μη κενό κελί χρησιμοποιώντας τη συνάρτηση MATCH στο Excel.
Εύρεση της τελευταίας ημέρας ενός δεδομένου μήνα : Η συνάρτηση EOMONTH επιστρέφει την τελευταία ημερομηνία του μήνα, της δεδομένης τιμής ημερομηνίας.
Πώς να αποκτήσετε την τελευταία τιμή στη στήλη : Τελευταία καταχώριση σε ένα μεγάλο σύνολο δεδομένων, Ένας συνδυασμός συνάρτησης CELL και INDEX επιστρέφει την τελευταία τιμή σε μια στήλη ή μια λίστα δεδομένων.
Διαφορά με το τελευταίο μη κενό κελί : παίρνει την εναλλακτική διαφορά από την τελευταία τιμή στη λίστα με αριθμούς χρησιμοποιώντας τη συνάρτηση IF και LOOKUP στο Excel.
Βρείτε την τελευταία σειρά δεδομένων με τιμές κειμένου στο Excel : Σε έναν πίνακα τιμών κειμένου και κενών κελιών, επιστρέψτε την τελευταία τιμή στον πίνακα χρησιμοποιώντας τη συνάρτηση MATCH και REPT στο excel.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT στο Excel: Επιστρέφει το SUM αφού λάβετε το γινόμενο των αντίστοιχων τιμών σε πολλούς πίνακες στο excel.
Πώς να χρησιμοποιήσετε μπαλαντέρ στο excel : Αναζήτηση, μέτρηση, άθροισμα, μέσος όρος και πιο μαθηματική λειτουργία σε κελιά που ταιριάζουν με φράσεις χρησιμοποιώντας τα μπαλαντέρ στο excel.
Δημοφιλή άρθρα:
Πώς να χρησιμοποιήσετε τη συνάρτηση IF στο Excel : Η δήλωση IF στο Excel ελέγχει την κατάσταση και επιστρέφει μια συγκεκριμένη τιμή εάν η συνθήκη είναι TRUE ή επιστρέφει μια άλλη συγκεκριμένη τιμή εάν είναι FALSE.
Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.