Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο θα σας διδάξει πώς να αναζητήσετε την τελευταία τιμή στη στήλη ή τη σειρά στο Excel.
Τελευταία τιμή στη στήλη
Μπορείτε να χρησιμοποιήσετε τη συνάρτηση LOOKUP για να βρείτε το τελευταίο μη κενό κελί σε μια στήλη.
| 1 | = LOOKUP (2,1/(B: B ""), B: B) |
Ας περάσουμε από αυτόν τον τύπο.
Το τμήμα του τύπου B: B ”” επιστρέφει έναν πίνακα που περιέχει τιμές True και False: {FALSE, TRUE, TRUE,…}, η δοκιμή κάθε κελιού στη στήλη B είναι κενή (FALSE).
| 1 | = LOOKUP (2,1/({FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE;…), B: B) |
Αυτές οι τιμές Boole μετατρέπονται σε 0 ή 1 και χρησιμοποιούνται για τη διαίρεση 1.
| 1 | = LOOKUP (2, {#DIV/0!; 1; 1; 1; 1; 1;#DIV/0!;, B: B) |
Αυτό είναι το lookup_vector για τη συνάρτηση LOOKUP. Στην περίπτωσή μας, η τιμή_αναζήτησης είναι 2, αλλά η μεγαλύτερη τιμή στο διανυσματικό σημείωμα είναι 1, οπότε η συνάρτηση LOOKUP θα ταιριάξει με την τελευταία 1 του πίνακα και θα επιστρέψει την αντίστοιχη τιμή στο διανυσματικό αποτέλεσμα.
Εάν είστε βέβαιοι ότι έχετε μόνο αριθμητικές τιμές στη στήλη σας, τα δεδομένα σας ξεκινούν από τη σειρά 1 και το εύρος δεδομένων σας συνεχόμενα, μπορείτε να χρησιμοποιήσετε έναν ελαφρώς απλούστερο τύπο με τις συναρτήσεις INDEX και COUNT.
| 1 | = INDEX (B: B, COUNT (B: B)) |
Η συνάρτηση COUNT επιστρέφει τον αριθμό των κελιών που γεμίζουν δεδομένα στο συνεχές εύρος (4) και η συνάρτηση INDEX δίνει έτσι την τιμή του κελιού στην αντίστοιχη σειρά (4η).
Για να αποφύγετε πιθανά σφάλματα όταν το εύρος των δεδομένων σας περιέχει ένα μείγμα αριθμητικών και μη αριθμητικών τιμών ή ακόμη και κάποια κενά κελιά, μπορείτε να χρησιμοποιήσετε τη συνάρτηση LOOKUP μαζί με τις συναρτήσεις ISBLANK και NOT.
| 1 | = LOOKUP (2,1/(NOT (ISBLANK (B: B)))), B: B) |
Η συνάρτηση ISBLANK επιστρέφει έναν πίνακα που περιέχει τιμές True και False, που αντιστοιχούν σε 1 και 0. Η συνάρτηση NOT αλλάζει True (δηλ. 1) σε False και False (δηλ. 0) σε True. Αν αντιστρέψουμε αυτόν τον πίνακα που προκύπτει (όταν διαιρούμε το 1 με αυτόν τον πίνακα), παίρνουμε έναν πίνακα αποτελεσμάτων που περιέχει ξανά #DIV/0! σφάλματα και 1, τα οποία μπορούν να χρησιμοποιηθούν ως πίνακας αναζήτησης (lookup_vector) στη συνάρτηση LOOKUP. Η λειτουργικότητα της συνάρτησης LOOKUP είναι τότε η ίδια όπως ήταν στο πρώτο μας παράδειγμα: επιστρέφει την τιμή του διανύσματος αποτελεσμάτων στη θέση του τελευταίου 1 στον πίνακα αναζήτησης.
Όταν χρειάζεστε τον αριθμό γραμμής με την τελευταία καταχώριση για επιστροφή, μπορείτε να τροποποιήσετε τον τύπο που χρησιμοποιείται στο πρώτο μας παράδειγμα μαζί με τη συνάρτηση ROW στο διανυσματικό σας αποτέλεσμα.
| 1 | = LOOKUP (2,1/(B: B ""), ROW (B: B)) |
Τελευταία τιμή σε σειρά
Για να λάβετε την τιμή του τελευταίου μη κενού κελιού σε μια σειρά γεμάτη με αριθμητικά δεδομένα, μπορεί να θέλετε να χρησιμοποιήσετε μια παρόμοια προσέγγιση αλλά με διαφορετικές λειτουργίες: τη συνάρτηση OFFSET μαζί με τις συναρτήσεις MATCH και MAX.
| 1 | = OFFSET (αναφορά, γραμμές, στήλες) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) |
Ας δούμε πώς λειτουργεί αυτός ο τύπος.
Λειτουργία MATCH
Χρησιμοποιούμε τη συνάρτηση MATCH για να «μετρήσουμε» πόσες τιμές κελιών είναι κάτω από 1 + το μέγιστο όλων των τιμών στη σειρά2 ξεκινώντας από το Β2.
| 1 | = MATCH (τιμή_αναζήτησης, αναζήτηση_αρρέ, [τύπος_συμφωνίας]) |
| 1 | = ΑΓΩΝΑΣ (MAX (B2: XFD2)+1, B2: XFD2,1) |
Η τιμή αναζήτησης της συνάρτησης MATCH είναι η μέγιστη από όλες τις τιμές στη γραμμή2 + 1. Δεδομένου ότι αυτή η τιμή προφανώς δεν υπάρχει στη σειρά2 και ο τύπος match_ty έχει οριστεί σε 1 (μικρότερη ή ίση με την τιμή_αναζήτησης), η συνάρτηση MATCH θα επιστρέψει το το τελευταίο "επιλεγμένο" κελί στη συστοιχία, δηλαδή τον αριθμό των κελιών που έχουν γεμίσει δεδομένα στην περιοχή B2: XFD2 (το XFD είναι η τελευταία στήλη στις νεότερες εκδόσεις του Excel).
OFFSET Λειτουργία
Στη συνέχεια, χρησιμοποιούμε τη συνάρτηση OFFSET για να λάβουμε την τιμή αυτού του κελιού, η θέση του οποίου επέστρεψε από τη συνάρτηση MATCH.
| 1 | = OFFSET (B2,0, C4-1) |
