Περιεχόμενα
Όλες οι κλασικές λειτουργίες αναζήτησης και αντικατάστασης τύπων VPR (VLOOKUP), GPR (HLOOKUP), ΠΙΟ ΕΚΤΙΘΕΤΑ (ΑΓΩΝΑΣ) και τέτοιοι έχουν ένα σημαντικό χαρακτηριστικό – αναζητούν από την αρχή μέχρι το τέλος, δηλαδή από αριστερά προς τα δεξιά ή από πάνω προς τα κάτω στα δεδομένα προέλευσης. Μόλις βρεθεί η πρώτη αντιστοίχιση, η αναζήτηση σταματά και εντοπίζεται μόνο η πρώτη εμφάνιση του στοιχείου που χρειαζόμαστε.
Τι να κάνουμε αν χρειαστεί να βρούμε όχι το πρώτο, αλλά το τελευταίο περιστατικό; Για παράδειγμα, η τελευταία συναλλαγή για τον πελάτη, η τελευταία πληρωμή, η πιο πρόσφατη παραγγελία κ.λπ.;
Μέθοδος 1: Εύρεση της τελευταίας σειράς με τύπο πίνακα
Εάν ο αρχικός πίνακας δεν έχει στήλη με ημερομηνία ή αύξοντα αριθμό σειράς (παραγγελία, πληρωμή…), τότε το καθήκον μας είναι, στην πραγματικότητα, να βρούμε την τελευταία σειρά που ικανοποιεί τη δεδομένη συνθήκη. Αυτό μπορεί να γίνει με τον ακόλουθο τύπο πίνακα:
Εδώ:
- Λειτουργία IF (ΑΝ) ελέγχει όλα τα κελιά μιας στήλης ένα προς ένα Πελάτης και εμφανίζει τον αριθμό γραμμής εάν περιέχει το όνομα που χρειαζόμαστε. Ο αριθμός γραμμής στο φύλλο μας δίνεται από τη συνάρτηση LINE (ΣΕΙΡΑ), αλλά επειδή χρειαζόμαστε τον αριθμό της σειράς στον πίνακα, πρέπει επιπλέον να αφαιρέσουμε 1, επειδή έχουμε μια κεφαλίδα στον πίνακα.
- Στη συνέχεια η συνάρτηση MAX (ΜΕΓΙΣΤΗ) επιλέγει τη μέγιστη τιμή από το σχηματισμένο σύνολο αριθμών σειρών, δηλαδή τον αριθμό της πιο πρόσφατης γραμμής του πελάτη.
- Λειτουργία ΔΕΊΚΤΗΣ (ΔΕΙΚΤΗΣ) επιστρέφει τα περιεχόμενα του κελιού με τον τελευταίο αριθμό που βρέθηκε από οποιαδήποτε άλλη απαιτούμενη στήλη πίνακα (Κωδικός παραγγελίας).
Όλα αυτά πρέπει να εισαχθούν ως τύπος πίνακα, δηλαδή:
- Στο Office 365 με εγκατεστημένες τις πιο πρόσφατες ενημερώσεις και υποστήριξη για δυναμικούς πίνακες, μπορείτε απλά να πατήσετε εισάγετε.
- Σε όλες τις άλλες εκδόσεις, αφού εισαγάγετε τον τύπο, θα πρέπει να πατήσετε τη συντόμευση πληκτρολογίου Ctrl+αλλαγή+εισάγετε, το οποίο θα προσθέσει αυτόματα σγουρά τιράντες σε αυτό στη γραμμή τύπων.
Μέθοδος 2: Αντίστροφη αναζήτηση με τη νέα συνάρτηση LOOKUP
Έχω ήδη γράψει ένα μεγάλο άρθρο με ένα βίντεο σχετικά με μια νέα δυνατότητα ΔΕΙΤΕ (XLOOKUP), το οποίο εμφανίστηκε στις τελευταίες εκδόσεις του Office για να αντικαταστήσει το παλιό VLOOKUP (VLOOKUP). Με τη βοήθεια του BROWSE, το έργο μας λύνεται αρκετά στοιχειωδώς, γιατί. για αυτήν τη συνάρτηση (σε αντίθεση με το VLOOKUP), μπορείτε να ορίσετε ρητά την κατεύθυνση αναζήτησης: από πάνω προς τα κάτω ή από κάτω προς τα πάνω – το τελευταίο της όρισμα (-1) είναι υπεύθυνο για αυτό:
Μέθοδος 3. Αναζητήστε μια συμβολοσειρά με την πιο πρόσφατη ημερομηνία
Εάν στα δεδομένα προέλευσης έχουμε μια στήλη με σειριακό αριθμό ή μια ημερομηνία που παίζει παρόμοιο ρόλο, τότε η εργασία τροποποιείται – δεν πρέπει να βρούμε την τελευταία (χαμηλότερη) γραμμή με αντιστοιχία, αλλά τη γραμμή με την πιο πρόσφατη ( μέγιστη) ημερομηνία.
Έχω ήδη συζητήσει λεπτομερώς πώς να το κάνω αυτό χρησιμοποιώντας κλασικές συναρτήσεις και τώρα ας προσπαθήσουμε να χρησιμοποιήσουμε τη δύναμη των νέων συναρτήσεων δυναμικού πίνακα. Για μεγαλύτερη ομορφιά και ευκολία, μετατρέπουμε επίσης τον αρχικό πίνακα σε "έξυπνο" τραπέζι χρησιμοποιώντας μια συντόμευση πληκτρολογίου Ctrl+T ή εντολές Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα).
Με τη βοήθειά τους, αυτό το «ζεύγος δολοφόνων» λύνει το πρόβλημά μας πολύ χαριτωμένα:
Εδώ:
- Λειτουργία πρώτα FILTER (ΦΙΛΤΡΟ) επιλέγει μόνο αυτές τις γραμμές από τον πίνακά μας που βρίσκονται στη στήλη Πελάτης – το όνομα που χρειαζόμαστε.
- Στη συνέχεια η συνάρτηση ΒΑΘΜΟΣ (ΕΙΔΟΣ) ταξινομεί τις επιλεγμένες σειρές κατά ημερομηνία με φθίνουσα σειρά, με την πιο πρόσφατη συμφωνία στην κορυφή.
- Λειτουργία ΔΕΊΚΤΗΣ (ΔΕΙΚΤΗΣ) εξάγει την πρώτη σειρά, δηλαδή επιστρέφει την τελευταία συναλλαγή που χρειαζόμαστε.
- Και, τέλος, η συνάρτηση εξωτερικού ΦΙΛΤΡΟΥ αφαιρεί την επιπλέον 1η και 3η στήλη από τα αποτελέσματα (Κωδικός παραγγελίας и Πελάτης) και αφήνει μόνο την ημερομηνία και το ποσό. Για αυτό, χρησιμοποιείται ένας πίνακας σταθερών. {0;1;0;1}, ορίζοντας ποιες στήλες θέλουμε να εμφανίζονται (1) ή όχι (0).
Μέθοδος 4: Εύρεση της τελευταίας αντιστοίχισης στο Power Query
Λοιπόν, για λόγους πληρότητας, ας δούμε μια λύση στο πρόβλημα της αντίστροφης αναζήτησης χρησιμοποιώντας το πρόσθετο Power Query. Με τη βοήθειά της όλα λύνονται πολύ γρήγορα και όμορφα.
1. Ας μετατρέψουμε τον αρχικό μας πίνακα σε «έξυπνο» χρησιμοποιώντας μια συντόμευση πληκτρολογίου Ctrl+T ή εντολές Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα).
2. Φορτώστε το στο Power Query με το κουμπί Από Πίνακας/Εύρος αυτί ημερομηνία (Δεδομένα — Από πίνακα/εύρος).
3. Ταξινομούμε (μέσω της αναπτυσσόμενης λίστας του φίλτρου στην κεφαλίδα) τον πίνακα μας με φθίνουσα σειρά ημερομηνίας, έτσι ώστε οι πιο πρόσφατες συναλλαγές να βρίσκονται στην κορυφή.
4… Στην καρτέλα Μεταμόρφωση επιλέξτε μια ομάδα Ομαδοποίηση από (Μετατροπή — Ομαδοποίηση κατά) και ορίστε την ομαδοποίηση κατά πελάτες και ως συνάρτηση αθροίσματος, επιλέξτε την επιλογή Όλες οι γραμμές (Όλες οι σειρές). Μπορείτε να ονομάσετε τη νέα στήλη ό,τι θέλετε – για παράδειγμα Περιγραφή.
Μετά την ομαδοποίηση, θα λάβουμε μια λίστα με μοναδικά ονόματα των πελατών μας και στη στήλη Περιγραφή – πίνακες με όλες τις συναλλαγές καθενός από αυτούς, όπου η πρώτη γραμμή θα είναι η πιο πρόσφατη συναλλαγή, που είναι αυτό που χρειαζόμαστε:
5. Προσθέστε μια νέα υπολογισμένη στήλη με το κουμπί Προσαρμοσμένη στήλη αυτί Προσθήκη στήλης (Προσθήκη στήλης — Προσθήκη προσαρμοσμένης στήλης)και εισάγετε τον ακόλουθο τύπο:
Εδώ Περιγραφή – αυτή είναι η στήλη από την οποία παίρνουμε πίνακες ανά πελάτες και 0 {} είναι ο αριθμός της σειράς που θέλουμε να εξαγάγουμε (η αρίθμηση σειρών στο Power Query ξεκινά από το μηδέν). Παίρνουμε μια στήλη με εγγραφές (Ρεκόρ), όπου κάθε καταχώρηση είναι η πρώτη σειρά από κάθε πίνακα:
Απομένει να επεκτείνετε τα περιεχόμενα όλων των εγγραφών με το κουμπί με διπλά βέλη στην κεφαλίδα της στήλης Τελευταία συμφωνία επιλέγοντας τις επιθυμητές στήλες:
… και, στη συνέχεια, διαγράψτε τη στήλη που δεν χρειάζεται πλέον Περιγραφή κάνοντας δεξί κλικ στον τίτλο του – Κατάργηση στηλών (Κατάργηση στηλών).
Μετά τη μεταφόρτωση των αποτελεσμάτων στο φύλλο μέσω Αρχική σελίδα — Κλείσιμο και φόρτωση — Κλείσιμο και φόρτωση (Αρχική — Κλείσιμο & Φόρτωση — Κλείσιμο & Φόρτωση σε…) θα πάρουμε έναν τόσο ωραίο πίνακα με μια λίστα με πρόσφατες συναλλαγές, όπως θέλαμε:
Όταν αλλάζετε τα δεδομένα προέλευσης, δεν πρέπει να ξεχάσετε να ενημερώσετε τα αποτελέσματα κάνοντας δεξί κλικ πάνω τους – στην εντολή Ενημέρωση & Αποθήκευση (Φρεσκάρω) ή συντόμευση πληκτρολογίου Ctrl+άλλος+F5.
- Η συνάρτηση LOOKUP είναι απόγονος του VLOOKUP
- Πώς να χρησιμοποιήσετε τις νέες συναρτήσεις δυναμικού πίνακα SORT, FILTER και UNIC
- Εύρεση του τελευταίου μη κενού κελιού σε μια γραμμή ή στήλη με τη συνάρτηση LOOKUP