Βρείτε την τελευταία εμφάνιση μιας τιμής αναζήτησης μιας λίστας στο Excel

Σε αυτό το σεμινάριο, θα μάθετε πώς μπορείτε να βρείτε την τελευταία εμφάνιση ενός στοιχείου σε μια λίστα χρησιμοποιώντας τύπους Excel.

Πρόσφατα, δούλευα για τον καθορισμό της ατζέντας για μια συνάντηση.

Είχα μια λίστα στο Excel όπου είχα μια λίστα με άτομα και τις ημερομηνίες κατά τις οποίες λειτουργούσαν ως «Πρόεδρος της Συνάντησης».

Δεδομένου ότι υπήρχε επανάληψη στη λίστα (πράγμα που σημαίνει ότι ένα άτομο ήταν Πρόεδρος συνεδριάσεων πολλές φορές), έπρεπε επίσης να γνωρίζω πότε ήταν η τελευταία φορά που ένα άτομο ενήργησε ως «Πρόεδρος της Συνάντησης».

Αυτό συνέβη επειδή έπρεπε να διασφαλίσω ότι κάποιος που προήδρευσε πρόσφατα δεν ανατέθηκε ξανά.

Έτσι αποφάσισα να χρησιμοποιήσω κάποια μαγική λειτουργία Excel για να το πετύχω αυτό.

Παρακάτω είναι το τελικό αποτέλεσμα όπου μπορώ να επιλέξω ένα όνομα από το αναπτυσσόμενο μενού και μου δίνει την ημερομηνία της τελευταίας εμφάνισης αυτού του ονόματος στη λίστα.

Εάν έχετε καλή κατανόηση των λειτουργιών του Excel, θα γνωρίζετε ότι δεν υπάρχει καμία συνάρτηση Excel που μπορεί να το κάνει αυτό.

Είστε όμως στην ενότητα Formula Hack και εδώ κάνουμε το μαγικό να συμβεί.

Σε αυτό το σεμινάριο, θα σας δείξω τρεις τρόπους για να το κάνετε αυτό.

Βρείτε το τελευταίο συμβάν - Χρησιμοποιώντας τη συνάρτηση MAX

Η πίστωση αυτής της τεχνικής ανήκει σε ένα άρθρο του Excel MVP Charley Kyd.

Εδώ είναι ο τύπος Excel που θα επιστρέψει την τελευταία τιμή από τη λίστα:

= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ 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!
• Το τρίτο όρισμα ([result_vector]) είναι το εύρος από το οποίο δίνει το αποτέλεσμα, οι οποίες είναι ημερομηνίες σε αυτήν την περίπτωση.

Ο λόγος που λειτουργεί αυτός ο τύπος είναι ότι η συνάρτηση LOOKUP χρησιμοποιεί την τεχνική κατά προσέγγιση αντιστοίχισης. Αυτό σημαίνει ότι εάν μπορεί να βρει την ακριβή τιμή αντιστοίχισης, θα το επέστρεφε, αλλά αν δεν μπορεί, θα σαρώσει ολόκληρο τον πίνακα μέχρι το τέλος και θα επιστρέψει την επόμενη μεγαλύτερη τιμή που είναι χαμηλότερη από την τιμή αναζήτησης.

Σε αυτήν την περίπτωση, η τιμή αναζήτησης είναι 2 και στον πίνακα μας, θα λάβουμε μόνο 1 ή σφάλματα. Έτσι, σαρώνει ολόκληρο τον πίνακα και επιστρέφει τη θέση του τελευταίου 1 - που είναι η τελευταία τιμή αντιστοίχισης του ονόματος.

Βρείτε την τελευταία εμφάνιση - χρησιμοποιώντας προσαρμοσμένη συνάρτηση (VBA)

Επιτρέψτε μου επίσης να σας δείξω έναν άλλο τρόπο να το κάνετε αυτό.

Μπορούμε να δημιουργήσουμε μια προσαρμοσμένη συνάρτηση (ονομάζεται επίσης συνάρτηση καθορισμένη από το χρήστη) χρησιμοποιώντας το VBA.

Το όφελος από τη δημιουργία μιας προσαρμοσμένης λειτουργίας είναι ότι είναι εύκολο στη χρήση. Δεν χρειάζεται να ανησυχείτε για τη δημιουργία μιας σύνθετης φόρμουλας κάθε φορά, καθώς το μεγαλύτερο μέρος της εργασίας συμβαίνει στο backend του VBA.

Έχω δημιουργήσει έναν απλό τύπο (που μοιάζει πολύ με τον τύπο VLOOKUP).

Για να δημιουργήσετε μια προσαρμοσμένη λειτουργία, πρέπει να έχετε τον κωδικό VBA στο πρόγραμμα επεξεργασίας VB. Θα σας δώσω τον κώδικα και τα βήματα για να τον τοποθετήσετε στο VB Editor σε λίγο, αλλά επιτρέψτε μου πρώτα να σας δείξω πώς λειτουργεί:

Αυτός είναι ο τύπος που θα σας δώσει το αποτέλεσμα:

= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)

Ο τύπος παίρνει τρία επιχειρήματα:

• Τιμή αναζήτησης (αυτό θα ήταν το όνομα στο κελί D3)
• Εύρος αναζήτησης (αυτό θα είναι το εύρος που έχει τα ονόματα και τις ημερομηνίες - A2: B14)
• Αριθμός στήλης (αυτή είναι η στήλη από την οποία θέλουμε το αποτέλεσμα)

Αφού δημιουργήσετε τον τύπο και βάλετε τον κώδικα στο VB Editor, μπορείτε να τον χρησιμοποιήσετε όπως και κάθε άλλη κανονική λειτουργία φύλλου εργασίας του Excel.

Εδώ είναι ο κωδικός για τον τύπο:

"Αυτός είναι ένας κωδικός για μια συνάρτηση που βρίσκει την τελευταία εμφάνιση μιας τιμής αναζήτησης και επιστρέφει την αντίστοιχη τιμή από την καθορισμένη στήλη" Κώδικας που δημιουργήθηκε από το Sumit Bansal (https://trumpexcel.com) Function LastItemLookup (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Colunns (1) .Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Then LastItemLookup = LookupRange.Cells (i, ColumnNumber) Έξοδος από τη Λειτουργία Τέλος Αν Επόμενο Λειτουργία Τέλος

Ακολουθούν τα βήματα για να τοποθετήσετε αυτόν τον κωδικό στο VB Editor:

1. Μεταβείτε στην καρτέλα Προγραμματιστής.
2. Κάντε κλικ στην επιλογή Visual Basic. Αυτό θα ανοίξει τον επεξεργαστή VB στο backend.
3. Στο παράθυρο Project Explorer στο VB Editor, κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο για το βιβλίο εργασίας στο οποίο θέλετε να εισαγάγετε τον κώδικα. Εάν δεν βλέπετε το Project Explorer, μεταβείτε στην καρτέλα Προβολή και κάντε κλικ στο Project Explorer.
4. Μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Αυτό θα εισαγάγει ένα αντικείμενο ενότητας για το βιβλίο εργασίας σας.
5. Αντιγράψτε και επικολλήστε τον κώδικα στο παράθυρο της λειτουργικής μονάδας.

Τώρα ο τύπος θα είναι διαθέσιμος σε όλο το φύλλο εργασίας του βιβλίου εργασίας.

Σημειώστε ότι πρέπει να αποθηκεύσετε το βιβλίο εργασίας σε μορφή .XLSM καθώς έχει μια μακροεντολή σε αυτό. Επίσης, εάν θέλετε αυτός ο τύπος να είναι διαθέσιμος σε όλα τα βιβλία εργασίας που χρησιμοποιείτε, μπορείτε είτε να τον αποθηκεύσετε στο Personal Macro Workbook είτε να δημιουργήσετε ένα πρόσθετο από αυτό.