Περίληψη: σε αυτό το σεμινάριο, θα μάθετε πώς να χρησιμοποιείτε έναν τύπο του Excel για να βρείτε την τελευταία εμφάνιση ενός στοιχείου σε μια λίστα.
Πρόσφατα, δούλευα πάνω σε μια ατζέντα συνάντησης.
Έχω μια λίστα στο Excel με μια λίστα ατόμων και τις ημερομηνίες που «συνάντησαν προέδρους».
Πρέπει επίσης να γνωρίζω πότε ένα άτομο ήταν η τελευταία "πρόεδρος της συνεδρίασης" λόγω διπλών στη λίστα (που σημαίνει ότι ένα άτομο έχει προεδρεύσει της συνεδρίασης πολλές φορές).
Αυτό συμβαίνει επειδή πρέπει να βεβαιωθώ ότι δεν θα επανατοποθετηθεί ο πιο πρόσφατος πρόεδρος.
Έτσι αποφάσισα να χρησιμοποιήσω κάποια μαγεία της συνάρτησης Excel για να κάνω τη δουλειά.
Παρακάτω είναι το τελικό αποτέλεσμα, μπορώ να επιλέξω ένα όνομα από το αναπτυσσόμενο μενού και μου δίνει την ημερομηνία της τελευταίας εμφάνισης αυτού του ονόματος στη λίστα.
Εάν κατανοείτε καλά τις συναρτήσεις του Excel, γνωρίζετε ότι δεν υπάρχει συνάρτηση Excel που να μπορεί να το κάνει αυτό.Εδώ κάνουμε θαύματα.
Σε αυτό το σεμινάριο, θα σας δείξω τρεις τρόπους για να το κάνετε αυτό.
Περιεχόμενα
Βρείτε την τελευταία εμφάνιση - χρησιμοποιώντας τη συνάρτηση MAX
Ακολουθεί ο τύπος του Excel που θα επιστρέψει την τελευταία τιμή στη λίστα:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Δείτε πώς λειτουργεί αυτός ο τύπος:
- Η συνάρτηση MAX χρησιμοποιείται για την εύρεση του αριθμού γραμμής του τελευταίου ονόματος που ταιριάζει.Για παράδειγμα, εάν το όνομα είναι Glen, θα επιστρέψει 11 επειδή βρίσκεται στη γραμμή 11.Εφόσον η λίστα μας ξεκινά από τη δεύτερη σειρά, αφαιρείται το 1.Επομένως, η τελευταία εμφάνιση του Glen είναι 10 στη λίστα μας.
- Το SUMPRODUCT χρησιμοποιείται για να βεβαιωθείτε ότι δεν χρειάζεται να χρησιμοποιήσετε τα Control + Shift + Enter επειδή το SUMPRODUCT μπορεί να χειριστεί τύπους πίνακα.
- Η συνάρτηση INDEX χρησιμοποιείται τώρα για την εύρεση της ημερομηνίας του τελευταίου ονόματος που ταιριάζει.
Βρείτε την τελευταία εμφάνιση - χρησιμοποιώντας τη συνάρτηση LOOKUP
Εδώ είναι ένας άλλος τύπος που κάνει την ίδια δουλειά:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Δείτε πώς λειτουργεί αυτός ο τύπος:
- Η τιμή αναζήτησης είναι 2 (θα δείτε γιατί.. συνεχίστε να διαβάζετε)
- Το εύρος αναζήτησης είναι 1/($A$2:$A$14=$D$3) - επιστρέφει 1 όταν βρει ένα αντίστοιχο όνομα και, διαφορετικά, ένα σφάλμα.Έτσι καταλήγετε σε έναν πίνακα.例如，查找值為 Glen，數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
- Η τρίτη παράμετρος ([result_vector]) είναι το εύρος στο οποίο δίνει το αποτέλεσμα, σε αυτήν την περίπτωση την ημερομηνία.
Ο λόγος που λειτουργεί αυτός ο τύπος είναι ότι η συνάρτηση LOOKUP χρησιμοποιεί μια κατά προσέγγιση τεχνική αντιστοίχισης.Αυτό σημαίνει ότι εάν μπορεί να βρει μια ακριβή αντιστοίχιση, θα επιστρέψει, αλλά αν δεν μπορεί, θα σαρώσει ολόκληρο τον πίνακα μέχρι το τέλος και θα επιστρέψει την επόμενη μεγαλύτερη τιμή που είναι μικρότερη από την τιμή αναζήτησης.
Σε αυτήν την περίπτωση, η τιμή αναζήτησης είναι 2 και στον πίνακα μας, λαμβάνουμε απλώς 1 ή ένα σφάλμα.Έτσι σαρώνει ολόκληρο τον πίνακα και επιστρέφει τη θέση του τελευταίου 1 - που είναι η τελευταία τιμή που ταιριάζει για το όνομα.
Εύρεση τελευταίας εμφάνισης - Χρήση προσαρμοσμένης συνάρτησης (VBA)
Επιτρέψτε μου να σας δείξω και έναν άλλο τρόπο.
Μπορούμε να δημιουργήσουμε προσαρμοσμένες συναρτήσεις (γνωστές και ως συναρτήσεις που καθορίζονται από το χρήστη) χρησιμοποιώντας VBA.
Το πλεονέκτημα της δημιουργίας προσαρμοσμένων λειτουργιών είναι η ευκολία χρήσης.Δεν χρειάζεται να ανησυχείτε για τη δημιουργία πολύπλοκων τύπων κάθε φορά, επειδή το μεγαλύτερο μέρος της δουλειάς συμβαίνει στο backend της VBA.
Δημιούργησα έναν απλό τύπο (όπως ο τύπος VLOOKUP).
Για να δημιουργήσετε προσαρμοσμένες συναρτήσεις, χρειάζεστε κώδικα VBA στο πρόγραμμα επεξεργασίας VB.Θα σας δώσω τον κώδικα και τα βήματα για να τον μεταφέρετε στο πρόγραμμα επεξεργασίας VB αργότερα, αλλά επιτρέψτε μου να σας δείξω πρώτα πώς λειτουργεί:
Εδώ είναι ο τύπος που θα σας δώσει το αποτέλεσμα:
=LastItemLookup($D$3,$A$2:$B$14,2)
Ο τύπος παίρνει τρεις παραμέτρους:
- Βρείτε την τιμή (αυτό θα είναι το όνομα στο κελί D3)
- Βρείτε το εύρος (αυτό θα είναι το εύρος με όνομα και ημερομηνία – A2:B14)
- αριθμός στήλης (αυτή είναι η στήλη για την οποία θέλουμε το αποτέλεσμα)
Αφού δημιουργήσετε τον τύπο και βάλετε τον κώδικα στο πρόγραμμα επεξεργασίας VB, μπορείτε να τον χρησιμοποιήσετε όπως οποιαδήποτε άλλη κανονική συνάρτηση φύλλου εργασίας του Excel.
Εδώ είναι ο κώδικας για τον τύπο:
Συνάρτηση LastItemLookup(Lookupvalue ως συμβολοσειρά, LookupRange ως εύρος, ColumnNumber ως ακέραιος) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1 Lookup) Τότε Last = LookupRange.Cells(i, ColumnNumber) Έξοδος από συνάρτηση Τέλος Αν Επόμενη Συνάρτηση Τέλος i
Ακολουθούν τα βήματα για να τοποθετήσετε αυτόν τον κώδικα στον επεξεργαστή VB:
- Μεταβείτε στην καρτέλα προγραμματιστή.
- Κάντε κλικ στην επιλογή Επιλογές της Visual Basic.Αυτό θα ανοίξει το πρόγραμμα επεξεργασίας VB στο backend.
- Στο παράθυρο του Project Explorer του προγράμματος επεξεργασίας VB, κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο του βιβλίου εργασίας όπου θέλετε να εισαγάγετε κώδικα.Εάν δεν βλέπετε το Project Explorer, μεταβείτε στην καρτέλα Προβολή και κάντε κλικ στο Project Explorer.
- Μεταβείτε στο Insert και κάντε κλικ στο Modules.Αυτό θα εισαγάγει ένα αντικείμενο μονάδας για το βιβλίο εργασίας σας.
- Αντιγράψτε και επικολλήστε τον κώδικα στο παράθυρο της μονάδας.
Τώρα ο τύπος θα είναι διαθέσιμος σε όλα τα φύλλα του βιβλίου εργασίας.
Σημειώστε ότι θα χρειαστεί να αποθηκεύσετε το βιβλίο εργασίας σε μορφή .XLSM καθώς περιέχει μακροεντολές.Επίσης, εάν θέλετε αυτός ο τύπος να είναι διαθέσιμος σε όλα τα βιβλία εργασίας που χρησιμοποιείτε, μπορείτε να τον αποθηκεύσετε στο προσωπικό σας βιβλίο εργασίας μακροεντολών ή να δημιουργήσετε ένα πρόσθετο από αυτό.
