Παρακολούθηση βίντεο - Λειτουργία Excel XLOOKUP (10 παραδείγματα XLOOKUP)
Προέχω Λειτουργία XLOOKUP έφτασε επιτέλους.
Εάν χρησιμοποιούσατε VLOOKUP ή INDEX/MATCH, είμαι βέβαιος ότι θα σας αρέσει η ευελιξία που παρέχει η λειτουργία XLOOKUP.
Σε αυτό το σεμινάριο, θα καλύψω όλα όσα πρέπει να γνωρίζετε για τη λειτουργία XLOOKUP και ορισμένα παραδείγματα που θα σας βοηθήσουν να ξέρετε πώς να την χρησιμοποιήσετε καλύτερα.
Ας ξεκινήσουμε λοιπόν!
Τι είναι το XLOOKUP;
Το XLOOKUP είναι μια νέα λειτουργία του Office 365 και είναι μια νέα και βελτιωμένη έκδοση της λειτουργίας VLOOKUP/HLOOKUP.
Κάνει όλα όσα συνήθιζε να κάνει το VLOOKUP και πολλά άλλα.
Το XLOOKUP είναι μια συνάρτηση που σας επιτρέπει να αναζητήσετε γρήγορα μια τιμή σε ένα σύνολο δεδομένων (κάθετη ή οριζόντια) και να επιστρέψετε την αντίστοιχη τιμή σε κάποια άλλη γραμμή/στήλη.
Για παράδειγμα, εάν έχετε λάβει τις βαθμολογίες για μαθητές σε μια εξέταση, μπορείτε να χρησιμοποιήσετε το XLOOKUP για να ελέγξετε γρήγορα πόσα έχει κερδίσει ένας μαθητής χρησιμοποιώντας το όνομα του μαθητή.
Η δύναμη αυτής της συνάρτησης θα γίνει ακόμη πιο ξεκάθαρη καθώς θα βουτήξω σε μερικές Παραδείγματα XLOOKUP αργότερα σε αυτό το σεμινάριο.
Αλλά πριν μπω στα παραδείγματα, υπάρχει ένα μεγάλο ερώτημα - πώς μπορώ να αποκτήσω πρόσβαση στο XLOOKUP;
Πώς να αποκτήσετε πρόσβαση στο XLOOKUP;
Μέχρι τώρα, το XLOOKUP είναι διαθέσιμο μόνο για τους χρήστες του Office 365.
Έτσι, εάν χρησιμοποιείτε προηγούμενες εκδόσεις του Excel (2010/2013/2016/2019), δεν θα μπορείτε να χρησιμοποιήσετε αυτήν τη λειτουργία.
Δεν είμαι επίσης σίγουρος αν αυτό θα κυκλοφορούσε ποτέ για προηγούμενες εκδόσεις ή όχι (ίσως η Microsoft μπορεί να δημιουργήσει ένα πρόσθετο με τον τρόπο που έκανε για το Power Query). Αλλά από τώρα, μπορείτε να το χρησιμοποιήσετε μόνο εάν είστε στο Office 365.
Κάντε κλικ εδώ για αναβάθμιση στο Office 365
Σε περίπτωση που βρίσκεστε ήδη στο Office 365 (έκδοση Home, Personal ή University) και δεν έχετε πρόσβαση σε αυτό, μπορείτε να μεταβείτε στην καρτέλα Αρχείο και, στη συνέχεια, να κάνετε κλικ στο Λογαριασμός.
Θα υπήρχε ένα πρόγραμμα του Office Insider και μπορείτε να κάνετε κλικ και να συμμετάσχετε στο πρόγραμμα του Office Insider. Αυτό θα σας δώσει πρόσβαση στη λειτουργία XLOOKUP.
Αναμένω ότι το XLOOKUP θα είναι σύντομα διαθέσιμο σε όλες τις εκδόσεις του Office 365.
Σημείωση: Το XLOOKUP είναι επίσης διαθέσιμο για το Office 365 για Mac και Excel για τον Ιστό (Excel online)Σύνταξη λειτουργίας XLOOKUP
Παρακάτω είναι η σύνταξη της συνάρτησης XLOOKUP:
= XLOOKUP (τιμή_αναζήτησης, αναζήτηση_συστοιχίας, επιστροφή_συστοιχίας, [αν_όχι_βρέθηκε], [αντιστοίχιση], [τρόπος_αναζήτησης])
Εάν έχετε χρησιμοποιήσει το VLOOKUP, θα παρατηρήσετε ότι η σύνταξη είναι αρκετά παρόμοια, με μερικές εκπληκτικές πρόσθετες δυνατότητες φυσικά.
Μην ανησυχείτε αν η σύνταξη και το επιχείρημα φαίνονται λίγο υπερβολικά. Τα καλύπτω με μερικά εύκολα παραδείγματα XLOOKUP αργότερα σε αυτό το σεμινάριο που θα το κάνουν πεντακάθαρο.Η συνάρτηση XLOOKUP μπορεί να παραθέσει 6 ορίσματα (3 υποχρεωτικά και 3 προαιρετικά):
- αναζήτηση_τιμής - την αξία που ψάχνετε
- lookup_array - ο πίνακας στον οποίο αναζητάτε την τιμή αναζήτησης
- return_array - ο πίνακας από τον οποίο θέλετε να λάβετε και να επιστρέψετε την τιμή (που αντιστοιχεί στη θέση όπου βρίσκεται η τιμή αναζήτησης)
- [if_not_found] - η τιμή για επιστροφή σε περίπτωση που δεν βρεθεί η τιμή αναζήτησης. Σε περίπτωση που δεν καθορίσετε αυτό το όρισμα, θα εμφανιστεί σφάλμα #N/A
- [match_mode] - Εδώ μπορείτε να καθορίσετε τον τύπο αντιστοίχισης που θέλετε:
- 0 - Ακριβής αντιστοίχιση, όπου η τιμή_αναζήτησης θα πρέπει να ταιριάζει ακριβώς με την τιμή της σειράς αναζήτησης. Αυτή είναι η προεπιλεγμένη επιλογή.
- -1 - Αναζητά την ακριβή αντιστοίχιση, αλλά αν βρεθεί, επιστρέφει το επόμενο μικρότερο στοιχείο/τιμή
- 1 - Αναζητά την ακριβή αντιστοίχιση, αλλά αν βρεθεί, επιστρέφει το επόμενο μεγαλύτερο στοιχείο/τιμή
- 2 - Για μερική αντιστοίχιση χρησιμοποιώντας μπαλαντέρ (* ή ~)
- [τρόπος_αναζήτησης] - Εδώ καθορίζετε τον τρόπο με τον οποίο η λειτουργία XLOOKUP θα πρέπει να πραγματοποιεί αναζήτηση στο lookup_array
- 1 - Αυτή είναι η προεπιλεγμένη επιλογή όπου η συνάρτηση αρχίζει να αναζητά την τιμή αναζήτησης από την κορυφή (πρώτο στοιχείο) έως το κάτω μέρος (τελευταίο στοιχείο) στο lookup_array
- -1 - Η αναζήτηση από κάτω προς τα πάνω. Χρήσιμο όταν θέλετε να βρείτε την τελευταία τιμή αντιστοίχισης στον πίνακα lookup_array
- 2 - Εκτελεί δυαδική αναζήτηση όπου τα δεδομένα πρέπει να ταξινομηθούν με αύξουσα σειρά. Εάν δεν ταξινομηθεί, αυτό μπορεί να δώσει σφάλμα ή λάθος αποτελέσματα
- -2 - Εκτελεί δυαδική αναζήτηση όπου τα δεδομένα πρέπει να ταξινομηθούν κατά φθίνουσα σειρά. Εάν δεν ταξινομηθεί, αυτό μπορεί να δώσει σφάλμα ή λάθος αποτελέσματα
Παραδείγματα λειτουργιών XLOOKUP
Πάμε τώρα στο ενδιαφέρον μέρος - μερικά πρακτικά παραδείγματα XLOOKUP.
Αυτά τα παραδείγματα θα σας βοηθήσουν να κατανοήσετε καλύτερα πώς λειτουργεί το XLOOKUP, πώς διαφέρει από το VLOOKUP και το INDEX/MATCH και μερικές βελτιώσεις και περιορισμούς αυτής της λειτουργίας.
Κάντε κλικ εδώ για να κάνετε λήψη του παραδείγματος αρχείου και ακολουθήστε
Παράδειγμα 1: Λήψη τιμής αναζήτησης
Ας υποθέσουμε ότι έχετε το ακόλουθο σύνολο δεδομένων και θέλετε να λάβετε τη μαθηματική βαθμολογία για τον Greg (η τιμή αναζήτησης).
Παρακάτω είναι ο τύπος που το κάνει αυτό:
= XLOOKUP (F2, A2: A15, B2: B15)
Στον παραπάνω τύπο, μόλις χρησιμοποίησα τα υποχρεωτικά ορίσματα όπου αναζητά το όνομα (από πάνω προς τα κάτω), βρίσκει μια ακριβή αντιστοίχιση και επιστρέφει την αντίστοιχη τιμή από το B2: B15.
Μια προφανής διαφορά που έχουν οι λειτουργίες XLOOKUP και VLOOKUP είναι ο τρόπος χειρισμού του πίνακα αναζήτησης. Στο VLOOKUP, έχετε ολόκληρο τον πίνακα όπου η τιμή αναζήτησης βρίσκεται στην πιο αριστερή στήλη και, στη συνέχεια, καθορίζετε τον αριθμό στήλης από όπου θέλετε να λάβετε το αποτέλεσμα. Το XLOOKUP, από την άλλη πλευρά, σας επιτρέπει να επιλέξετε το lookup_array και το return_array ξεχωριστάΈνα άμεσο όφελος από το να έχετε το lookup_array και το return_array ως ξεχωριστά επιχειρήματα σημαίνει ότι τώρα μπορείτε κοίτα αριστερά. Το VLOOKUP είχε αυτόν τον περιορισμό όπου μπορείτε μόνο να αναζητήσετε και να βρείτε μια τιμή που βρίσκεται στα δεξιά. Αλλά με το XLOOKUP, αυτός ο περιορισμός έχει φύγει.
Εδώ είναι ένα παράδειγμα. Έχω το ίδιο σύνολο δεδομένων, όπου το όνομα βρίσκεται στα δεξιά και η σειρά επιστροφής στα αριστερά.
Παρακάτω είναι ο τύπος που μπορώ να χρησιμοποιήσω για να λάβω τη βαθμολογία για τον Γκρεγκ στα Μαθηματικά (που σημαίνει να κοιτάζω αριστερά από την τιμή αναζήτησης)
= XLOOKUP (F2, D2: D15, A2: A15)
Το XLOOKUP επιλύει ένα άλλο σημαντικό ζήτημα - Σε περίπτωση που εισάγετε μια νέα στήλη ή μετακινήσετε στήλες, τα δεδομένα που προκύπτουν θα εξακολουθούν να είναι σωστά. Το VLOOKUP πιθανότατα θα σπάσει ή θα δώσει λανθασμένο αποτέλεσμα σε τέτοιες περιπτώσεις, καθώς τις περισσότερες φορές η τιμή του δείκτη στήλης είναι σκληρά κωδικοποιημένη.
Παράδειγμα 2: Αναζήτηση και Λήψη ολόκληρης εγγραφής
Ας πάρουμε τα ίδια δεδομένα ως παράδειγμα.
Σε αυτή την περίπτωση, δεν θέλω να αποκτήσω απλώς τη βαθμολογία του Γκρεγκ στα Μαθηματικά. Θέλω να πάρω τις βαθμολογίες σε όλα τα μαθήματα.
Σε αυτήν την περίπτωση, μπορώ να χρησιμοποιήσω τον παρακάτω τύπο:
= XLOOKUP (F2, A2: A15, B2: D15)
Ο παραπάνω τύπος χρησιμοποιεί ένα εύρος return_array που είναι περισσότερο από μια στήλη (B2: D15). Έτσι, όταν η τιμή αναζήτησης βρίσκεται στο A2: A15, ο τύπος επιστρέφει ολόκληρη τη σειρά από τον πίνακα return_array.
Επίσης, δεν μπορείτε να διαγράψετε μόνο κελιά που αποτελούν μέρος του πίνακα που συμπληρώθηκαν αυτόματα. Σε αυτό το παράδειγμα, δεν μπορείτε να διαγράψετε το H2 ή το I2. Αν προσπαθήσετε, τίποτα δεν θα συμβεί. Εάν επιλέξετε αυτά τα κελιά, ο τύπος στη γραμμή τύπων θα έχει γκρι χρώμα (υποδεικνύοντας ότι δεν μπορεί να αλλάξει)
Μπορείτε να διαγράψετε τον τύπο στο κελί G2 (όπου τον εισαγάγαμε αρχικά), θα διαγράψει ολόκληρο το αποτέλεσμα.
Αυτό είναι μια χρήσιμη βελτίωση όπως νωρίτερα με το VLOOKUP, θα πρέπει να καθορίσετε τον αριθμό στήλης ξεχωριστά για κάθε τύπο.
Παράδειγμα 3: Αμφίδρομη αναζήτηση με χρήση XLOOKUP (Οριζόντια και κάθετη αναζήτηση)
Παρακάτω είναι ένα σύνολο δεδομένων όπου θέλω να μάθω τη βαθμολογία του Greg στα Μαθηματικά (το θέμα στο κελί G2).
Αυτό μπορεί να γίνει χρησιμοποιώντας μια αμφίδρομη αναζήτηση όπου αναζητώ το όνομα στη στήλη Α και το όνομα του θέματος στη σειρά 1. Το όφελος αυτής της αμφίδρομης αναζήτησης είναι ότι το αποτέλεσμα είναι ανεξάρτητο από το όνομα μαθητή του ονόματος του θέματος. Εάν αλλάξω το όνομα του θέματος σε Χημεία, αυτός ο αμφίδρομος τύπος XLOOKUP θα εξακολουθούσε να λειτουργεί και να μου δίνει το σωστό αποτέλεσμα.
Παρακάτω είναι ο τύπος που θα εκτελέσει την αμφίδρομη αναζήτηση και θα δώσει το σωστό αποτέλεσμα:
= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))
Αυτός ο τύπος χρησιμοποιεί ένα ένθετο XLOOKUP, όπου πρώτα το χρησιμοποιώ για να φέρω όλα τα σημάδια του μαθητή στο κελί F2.
Έτσι, το αποτέλεσμα του XLOOKUP (F2, A2: A15, B2: D15) είναι {21,94,81}, το οποίο είναι μια σειρά βαθμολογιών που σημείωσε ο Greg σε αυτήν την περίπτωση.
Στη συνέχεια χρησιμοποιείται ξανά στον εξωτερικό τύπο XLOOKUP ως πίνακας επιστροφής. Στον εξωτερικό τύπο XLOOKUP, αναζητώ το όνομα του θέματος (το οποίο βρίσκεται στο κελί G1) και ο πίνακας αναζήτησης είναι B1: D1.
Εάν το όνομα του θέματος είναι Μαθηματικά, αυτός ο εξωτερικός τύπος XLOOKUP λαμβάνει την πρώτη τιμή από τον πίνακα επιστροφής - που είναι {21,94,81} σε αυτό το παράδειγμα.
Αυτό κάνει το ίδιο που μέχρι τώρα επιτυγχάνονταν χρησιμοποιώντας το συνδυασμό INDEX και MATCH
Κάντε κλικ εδώ για να κάνετε λήψη του παραδείγματος αρχείου και ακολουθήστε
Παράδειγμα 4: Όταν η τιμή αναζήτησης δεν βρέθηκε (Χειρισμός σφαλμάτων)
Ο χειρισμός σφαλμάτων προστέθηκε τώρα στον τύπο XLOOKUP.
Το τέταρτο όρισμα στη συνάρτηση XLOOKUP είναι [if_not_found], όπου μπορείτε να καθορίσετε τι θέλετε σε περίπτωση που η αναζήτηση δεν μπορεί να βρεθεί.
Ας υποθέσουμε ότι έχετε το σύνολο δεδομένων όπως φαίνεται παρακάτω, όπου θέλετε να λάβετε τη βαθμολογία των Μαθηματικών σε περίπτωση που ο αγώνας ταιριάζει και σε περίπτωση που το όνομα δεν βρεθεί, θέλετε να επιστρέψετε - "Δεν εμφανίστηκε"
Ο παρακάτω τύπος θα το κάνει αυτό:
= XLOOKUP (F2, A2: A15, B2: B15, "Δεν εμφανίστηκε")
Σε αυτή την περίπτωση, έχω σκληρογράψει αυτό που θέλω να πάρω σε περίπτωση που δεν υπάρχει αντιστοιχία. Μπορείτε επίσης να χρησιμοποιήσετε μια αναφορά κελιού σε ένα κελί ή έναν τύπο.
Παράδειγμα 5: Ένθετο XLOOKUP (Αναζήτηση σε πολλαπλές περιοχές)
Η ιδιοφυΐα του να έχεις το επιχείρημα [if_not_found] είναι ότι σου επιτρέπει να το χρησιμοποιείς ένθετο τύπο XLOOKUP.
Για παράδειγμα, ας υποθέσουμε ότι έχετε δύο ξεχωριστές λίστες όπως φαίνεται παρακάτω. Ενώ έχω αυτούς τους δύο πίνακες στο ίδιο φύλλο, μπορείτε να τους έχετε σε ξεχωριστά φύλλα ή ακόμα και σε βιβλία εργασίας.
Παρακάτω είναι ο ένθετος τύπος XLOOKUP που θα ελέγξει για το όνομα και στους δύο πίνακες και θα επιστρέψει την αντίστοιχη τιμή από την καθορισμένη στήλη.
= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))
Στον παραπάνω τύπο, χρησιμοποίησα το όρισμα [if_not_found] για να χρησιμοποιήσω έναν άλλο τύπο XLOOKUP. Αυτό σας επιτρέπει να προσθέσετε το δεύτερο XLOOKUP στον ίδιο τύπο και να σαρώσετε δύο πίνακες με έναν μόνο τύπο.
Δεν είμαι σίγουρος πόσα ένθετα XLOOKUPs μπορείτε να χρησιμοποιήσετε σε έναν τύπο. Προσπάθησα μέχρι τις 10 και δούλεψε, μετά το παράτησα
Παράδειγμα 6: Βρείτε την τελευταία τιμή αντιστοίχισης
Αυτό ήταν πολύ απαραίτητο και το XLOOKUP το έκανε δυνατό. Τώρα δεν χρειάζεται να βρείτε περίπλοκους τρόπους για να λάβετε την τελευταία τιμή αντιστοίχισης σε ένα εύρος.
Ας υποθέσουμε ότι έχετε το σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να ελέγξετε πότε προσλήφθηκε το τελευταίο άτομο σε κάθε τμήμα και ποια ήταν η ημερομηνία πρόσληψης.
Ο παρακάτω τύπος θα αναζητήσει την τελευταία τιμή για κάθε τμήμα και θα δώσει το όνομα της τελευταίας πρόσληψης:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)
Και ο παρακάτω τύπος θα δώσει την ημερομηνία πρόσληψης της τελευταίας πρόσληψης για κάθε τμήμα:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)
Δεδομένου ότι το XLOOKUP διαθέτει μια ενσωματωμένη λειτουργία για τον καθορισμό της κατεύθυνσης της αναζήτησης (από το πρώτο έως το τελευταίο ή από το τελευταίο προς το πρώτο), αυτό γίνεται με έναν απλό τύπο. Με κάθετα δεδομένα, το VLOOKUP και το INDEX/MATCH φαίνονται πάντα από πάνω προς τα κάτω, αλλά με το XLOOKUP και μπορούν επίσης να καθορίσουν την κατεύθυνση από κάτω προς τα πάνω επίσης.
Παράδειγμα 7: Κατά προσέγγιση αντιστοίχιση με XLOOKUP (Εύρεση φορολογικού συντελεστή)
Μια άλλη αξιοσημείωτη βελτίωση με το XLOOKUP είναι ότι τώρα υπάρχουν τέσσερις λειτουργίες αντιστοίχισης (το VLOOKUP έχει 2 και το MATCH έχει 3).
Μπορείτε να καθορίσετε οποιοδήποτε από τα τέσσερα ορίσματα για να αποφασίσετε τον τρόπο αντιστοίχισης της τιμής αναζήτησης:
- 0 - Ακριβής αντιστοίχιση, όπου η τιμή_αναζήτησης θα πρέπει να ταιριάζει ακριβώς με την τιμή της σειράς αναζήτησης. Αυτή είναι η προεπιλεγμένη επιλογή.
- -1 - Αναζητά την ακριβή αντιστοίχιση, αλλά αν βρεθεί, επιστρέφει το επόμενο μικρότερο στοιχείο/τιμή
- 1 - Αναζητά την ακριβή αντιστοίχιση, αλλά αν βρεθεί, επιστρέφει το επόμενο μεγαλύτερο στοιχείο/τιμή
- 2 - Για μερική αντιστοίχιση χρησιμοποιώντας μπαλαντέρ (* ή ~)
Παρακάτω έχω ένα σύνολο δεδομένων όπου θέλω να βρω την προμήθεια κάθε ατόμου - και η προμήθεια πρέπει να υπολογιστεί χρησιμοποιώντας τον πίνακα στα δεξιά.
Παρακάτω είναι ο τύπος που θα το κάνει αυτό:
= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2
Αυτό χρησιμοποιεί απλώς την αξία πωλήσεων ως αναζήτηση και κοιτάζει μέσα από τον πίνακα αναζήτησης στα δεξιά. Σε αυτόν τον τύπο, έχω χρησιμοποιήσει -1 ως το πέμπτο όρισμα ([match_mode]), που σημαίνει ότι θα αναζητήσει μια ακριβή αντιστοίχιση και όταν δεν το βρει, θα επιστρέψει την τιμή ακριβώς μικρότερη από την τιμή αναζήτησης .
Και όπως είπα, δεν χρειάζεται να ανησυχείτε αν τα δεδομένα σας δεν έχουν ταξινομηθεί.
Κάντε κλικ εδώ για να κάνετε λήψη του παραδείγματος αρχείου και ακολουθήστε
Παράδειγμα 8: Οριζόντια αναζήτηση
Το XLOOKUP μπορεί να κάνει κάθετη αλλά και οριζόντια αναζήτηση.
Παρακάτω έχω ένα σύνολο δεδομένων όπου έχω ονόματα μαθητών και τις βαθμολογίες τους σε σειρές και θέλω να φέρω τη βαθμολογία για το όνομα στο κελί B7.
Ο παρακάτω τύπος θα το κάνει αυτό:
= XLOOKUP (B7, B1: O1, B2: O2)
Αυτό δεν είναι παρά μια απλή αναζήτηση (παρόμοια με αυτή που είδαμε στο Παράδειγμα 1), αλλά οριζόντια.
Όλα τα παραδείγματα που καλύπτω για την κάθετη αναζήτηση μπορούν επίσης να γίνουν με μια οριζόντια αναζήτηση χρησιμοποιώντας το XLOOKUP (αντίο στο VLOOKUP και το HLOOKUP).
Παράδειγμα 9: Αναζήτηση υπό όρους (Χρήση XLOOKUP με άλλους τύπους)
Αυτό είναι ένα ελαφρώς προηγμένο παράδειγμα και δείχνει επίσης τη δύναμη του XLOOKUP όταν πρέπει να κάνετε περίπλοκες αναζητήσεις.
Παρακάτω είναι ένα σύνολο δεδομένων όπου έχω τα ονόματα των μαθητών και τις βαθμολογίες τους και θέλω να μάθω το όνομα του μαθητή που έχει συγκεντρώσει το μέγιστο σε κάθε μάθημα και τον αριθμό των μαθητών που έχουν σημειώσει περισσότερα από 80 σε κάθε μάθημα.
Παρακάτω είναι ο τύπος που θα δώσει το όνομα του μαθητή με τις υψηλότερες βαθμολογίες σε κάθε μάθημα:
= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15))), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)
Δεδομένου ότι το XLOOKUP μπορεί να χρησιμοποιηθεί για την επιστροφή ολόκληρου πίνακα, το χρησιμοποίησα για να λάβω πρώτα όλες τις βαθμολογίες για το απαιτούμενο θέμα.
Για παράδειγμα, για τα Μαθηματικά, όταν χρησιμοποιώ το XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), μου δίνει όλες τις βαθμολογίες στα μαθηματικά. Στη συνέχεια, μπορώ να χρησιμοποιήσω τη συνάρτηση MAX για να βρω τη μέγιστη βαθμολογία σε αυτό το εύρος.
Αυτός ο μέγιστος βαθμός γίνεται στη συνέχεια η τιμή αναζήτησης και το εύρος αναζήτησης θα είναι ο πίνακας που επιστρέφεται από το XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)
Το χρησιμοποιώ μέσα σε έναν άλλο τύπο XLOOKUP για να φέρω το όνομα του μαθητή που έχει συγκεντρώσει τους μέγιστους βαθμούς.
Και για να μετρήσετε τον αριθμό των μαθητών που έχουν σημειώσει περισσότερους από 80, χρησιμοποιήστε τον παρακάτω τύπο:
= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")
Αυτό χρησιμοποιεί απλώς τον τύπο XLOOKUP για να λάβει μια σειρά από όλες τις τιμές για το συγκεκριμένο θέμα. Στη συνέχεια, το τυλίγει σε συνάρτηση COUNTIF για να πάρει τον αριθμό των βαθμολογιών που είναι πάνω από 80.
Παράδειγμα 10: Χρήση Wildcard στο XLOOKUP
Όπως μπορείτε να χρησιμοποιήσετε χαρακτήρες μπαλαντέρ σε VLOOKUP και MATCH, μπορείτε επίσης να το κάνετε με το XLOOKUP.
Υπάρχει όμως διαφορά.
Στο XLOOKUP, πρέπει να καθορίσετε ότι χρησιμοποιείτε χαρακτήρες μπαλαντέρ (στο πέμπτο όρισμα). Εάν δεν το καθορίσετε, το XLOOKUP θα σας δώσει ένα σφάλμα.
Παρακάτω είναι ένα σύνολο δεδομένων όπου έχω ονόματα εταιρειών και την κεφαλαιοποίησή τους στην αγορά.
Θέλω να αναζητήσω ένα όνομα εταιρείας στη στήλη D και να αποκτήσω την κεφαλαιοποίηση της αγοράς από τον πίνακα στα αριστερά. Και δεδομένου ότι τα ονόματα στη στήλη D δεν ταιριάζουν ακριβώς, θα πρέπει να χρησιμοποιήσω χαρακτήρες μπαλαντέρ.
Παρακάτω είναι ο τύπος που θα το κάνει αυτό:
= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)
Στον παραπάνω τύπο, χρησιμοποίησα χαρακτήρα μπαλαντέρ αστερίσκου (*) πριν, όπως μετά το D2 (πρέπει να είναι εντός διπλών εισαγωγικών και να συνδέεται με το D2 χρησιμοποιώντας αμπερσάντερ).
Αυτό λέει στον τύπο να κοιτάξει όλα τα κελιά και αν περιέχει τη λέξη στο κελί D2 (που είναι η Apple), θεωρήστε την ακριβή αντιστοίχιση. Δεν έχει σημασία πόσοι και ποιοι χαρακτήρες είναι πριν και μετά το κείμενο στο κελί D2.
Και για να βεβαιωθείτε ότι το XLOOKUP δέχεται χαρακτήρες μπαλαντέρ, το πέμπτο όρισμα έχει οριστεί σε 2 (αντιστοίχιση χαρακτήρων μπαλαντέρ).
Παράδειγμα 11: Βρείτε την τελευταία τιμή στη στήλη
Δεδομένου ότι το XLOOKUP σάς επιτρέπει να κάνετε αναζήτηση από κάτω προς τα πάνω, μπορείτε εύκολα να βρείτε την τελευταία τιμή σε μια λίστα, καθώς και να λάβετε την αντίστοιχη τιμή από μια στήλη.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να μάθετε ποια είναι η τελευταία εταιρεία και ποια είναι η κεφαλαιοποίηση αυτής της τελευταίας εταιρείας.
Ο παρακάτω τύπος θα σας δώσει το όνομα της τελευταίας εταιρείας:
= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)
Και ο παρακάτω τύπος θα δώσει το ανώτατο όριο αγοράς της τελευταίας εταιρείας στη λίστα:
= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)
Αυτοί οι τύποι χρησιμοποιούν ξανά χαρακτήρες μπαλαντέρ. Σε αυτά, έχω χρησιμοποιήσει τον αστερίσκο (*) ως τιμή αναζήτησης, πράγμα που σημαίνει ότι αυτό θα θεωρούσε το πρώτο κελί που συναντά ως ακριβή αντιστοίχιση (καθώς ο αστερίσκος θα μπορούσε να είναι οποιοσδήποτε χαρακτήρας και οποιοσδήποτε αριθμός χαρακτήρων).
Και δεδομένου ότι η κατεύθυνση είναι από κάτω προς τα πάνω (για τα κάθετα διατεταγμένα δεδομένα), θα επιστρέψει την τελευταία τιμή στη λίστα.
Και ο δεύτερος τύπος από τότε χρησιμοποιεί ένα ξεχωριστό return_range για να λάβει το ανώτατο όριο αγοράς του επωνύμου στη λίστα.
Κάντε κλικ εδώ για να κάνετε λήψη του παραδείγματος αρχείου και ακολουθήστε
Τι γίνεται αν δεν έχετε XLOOKUP;
Δεδομένου ότι το XLOOKUP πιθανότατα θα είναι διαθέσιμο μόνο στους χρήστες του Office 365, ένας τρόπος για να το αποκτήσετε είναι η αναβάθμιση σε Office 365.
Εάν έχετε ήδη έκδοση Office 365 Home, Personal ή University, έχετε ήδη πρόσβαση στο XLOOKUP. Το μόνο που χρειάζεται να κάνετε είναι να εγγραφείτε στο πρόγραμμα Office Insider.
Για να το κάνετε αυτό, μεταβείτε στην καρτέλα Αρχείο, κάντε κλικ στο Λογαριασμός και, στη συνέχεια, κάντε κλικ στην επιλογή Office Insider. Θα υπήρχε μια επιλογή για συμμετοχή στο πρόγραμμα εσωτερικών πληροφοριών.
Σε περίπτωση που έχετε άλλες συνδρομές στο Office 365 (όπως το Enterprise), είμαι βέβαιος ότι το XLOOKUP και άλλες εκπληκτικές δυνατότητες (όπως δυναμικοί πίνακες, τύποι όπως SORT και FILTER) θα γίνουν σύντομα διαθέσιμες.
Σε περίπτωση που χρησιμοποιείτε το Excel 2010/2013/2016/2019, δεν θα έχετε XLOOKUP και θα πρέπει να συνεχίσετε να χρησιμοποιείτε το συνδυασμό VLOOKUP, HLOOKUP και INDEX/MATCH για να αξιοποιήσετε καλύτερα τους τύπους αναζήτησης.
Συμβατότητα XLOOKUP Backward
Αυτό είναι ένα πράγμα που πρέπει να προσέξετε - το XLOOKUP είναι ΟΧΙ συμβατό προς τα πίσω.
Αυτό σημαίνει ότι εάν δημιουργήσετε ένα αρχείο και χρησιμοποιήσετε τον τύπο XLOOKUP και, στη συνέχεια, το ανοίξετε σε μια έκδοση που δεν έχει XLOOKUP, θα εμφανίσει σφάλματα.
Δεδομένου ότι το XLOOKUP είναι ένα τεράστιο βήμα προς τη σωστή κατεύθυνση, πιστεύω ότι αυτό θα γίνει η προεπιλεγμένη φόρμουλα αναζήτησης, αλλά σίγουρα θα χρειαστούν μερικά χρόνια για να υιοθετηθεί ευρέως. Μετά από όλα, εξακολουθώ να βλέπω μερικούς ανθρώπους να χρησιμοποιούν το Excel 2003.
Αυτά λοιπόν είναι 11 παραδείγματα XLOOKUP που μπορούν να σας βοηθήσουν να κάνετε όλα τα πράγματα αναζήτησης και αναφοράς που γίνονται πιο γρήγορα και επίσης να το κάνουν εύκολο στη χρήση.
Ελπίζω να βρήκατε χρήσιμο αυτό το σεμινάριο!
