Το νέο XLOOKUP του Excel θα αντικαταστήσει το VLOOKUP, παρέχοντας μια ισχυρή αντικατάσταση μιας από τις πιο δημοφιλείς λειτουργίες του Excel. Αυτή η νέα λειτουργία λύνει ορισμένους από τους περιορισμούς του VLOOKUP και έχει επιπλέον λειτουργικότητα. Εδώ είναι τι πρέπει να ξέρετε.
Πίνακας περιεχομένων
Τι είναι το XLOOKUP;
Η νέα λειτουργία XLOOKUP έχει λύσεις για μερικούς από τους μεγαλύτερους περιορισμούς του VLOOKUP. Επιπλέον, αντικαθιστά επίσης το HLOOKUP. Για παράδειγμα, το XLOOKUP μπορεί να κοιτάξει στα αριστερά του, να ορίζει μια ακριβή αντιστοίχιση και σας επιτρέπει να καθορίσετε μια περιοχή κελιών αντί για έναν αριθμό στήλης. Το VLOOKUP δεν είναι τόσο εύκολο στη χρήση ή ως ευέλικτο. Θα σας δείξουμε πώς λειτουργούν όλα.
Προς το παρόν, το XLOOKUP είναι διαθέσιμο μόνο σε χρήστες του προγράμματος Insiders. Οποιοσδήποτε μπορεί εγγραφείτε στο πρόγραμμα Insiders για πρόσβαση στις πιο πρόσφατες δυνατότητες του Excel μόλις γίνουν διαθέσιμες. Η Microsoft θα αρχίσει σύντομα να το διαθέτει σε όλους τους χρήστες του Office 365.
Πώς να χρησιμοποιήσετε τη συνάρτηση XLOOKUP
Ας βουτήξουμε κατευθείαν με ένα παράδειγμα XLOOKUP σε δράση. Πάρτε τα παραδείγματα δεδομένων παρακάτω. Θέλουμε να επιστρέψουμε το τμήμα από τη στήλη F για κάθε ID στη στήλη Α.
Αυτό είναι ένα κλασικό παράδειγμα αναζήτησης ακριβούς αντιστοίχισης. Η λειτουργία XLOOKUP απαιτεί μόνο τρεις πληροφορίες.
Η παρακάτω εικόνα δείχνει το XLOOKUP με έξι ορίσματα, αλλά μόνο τα τρία πρώτα είναι απαραίτητα για μια ακριβή αντιστοίχιση. Ας εστιάσουμε λοιπόν σε αυτά:
Lookup_value: Αυτό που ψάχνετε.
Lookup_array: Πού να ψάξετε.
Return_array: το εύρος που περιέχει την τιμή προς επιστροφή.
Ο ακόλουθος τύπος θα λειτουργήσει για αυτό το παράδειγμα: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Ας εξερευνήσουμε τώρα μερικά πλεονεκτήματα που έχει το XLOOKUP έναντι του VLOOKUP εδώ.
Δεν υπάρχει άλλος αριθμός ευρετηρίου στήλης
Το περιβόητο τρίτο όρισμα του VLOOKUP ήταν να καθορίσει τον αριθμό στήλης των πληροφοριών που θα επιστραφούν από έναν πίνακα πίνακα. Αυτό δεν αποτελεί πλέον πρόβλημα, επειδή το XLOOKUP σάς δίνει τη δυνατότητα να επιλέξετε το εύρος από το οποίο θα επιστρέψετε (στήλη F σε αυτό το παράδειγμα).
Και μην ξεχνάτε, το XLOOKUP μπορεί να προβάλει τα δεδομένα αριστερά από το επιλεγμένο κελί, σε αντίθεση με το VLOOKUP. Περισσότερα για αυτό παρακάτω.
Επίσης, δεν έχετε πλέον το ζήτημα ενός κατεστραμμένου τύπου όταν εισάγονται νέες στήλες. Εάν αυτό συνέβη στο υπολογιστικό φύλλο σας, το εύρος επιστροφής θα προσαρμοστεί αυτόματα.
Η ακριβής αντιστοίχιση είναι η προεπιλογή
Ήταν πάντα μπερδεμένο όταν μάθαινε το VLOOKUP γιατί έπρεπε να προσδιορίσεις μια ακριβή αντιστοίχιση.
Ευτυχώς, το XLOOKUP έχει ως προεπιλογή μια ακριβή αντιστοίχιση – τον πολύ πιο συνηθισμένο λόγο για να χρησιμοποιήσετε έναν τύπο αναζήτησης). Αυτό μειώνει την ανάγκη απάντησης σε αυτό το πέμπτο όρισμα και διασφαλίζει λιγότερα λάθη από χρήστες που είναι νέοι στον τύπο.
Με λίγα λόγια, το XLOOKUP κάνει λιγότερες ερωτήσεις από το VLOOKUP, είναι πιο φιλικό προς το χρήστη και είναι επίσης πιο ανθεκτικό.
Το XLOOKUP μπορεί να κοιτάζει προς τα αριστερά
Η δυνατότητα επιλογής μιας περιοχής αναζήτησης καθιστά το XLOOKUP πιο ευέλικτο από το VLOOKUP. Με το XLOOKUP, η σειρά των στηλών του πίνακα δεν έχει σημασία.
Το VLOOKUP περιορίστηκε με αναζήτηση στην αριστερή στήλη ενός πίνακα και στη συνέχεια επιστροφή από έναν καθορισμένο αριθμό στηλών προς τα δεξιά.
Στο παρακάτω παράδειγμα, πρέπει να αναζητήσουμε ένα αναγνωριστικό (στήλη E) και να επιστρέψουμε το όνομα του ατόμου (στήλη D).
Ο ακόλουθος τύπος μπορεί να το πετύχει: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Τι να κάνετε εάν δεν βρέθηκε
Οι χρήστες των συναρτήσεων αναζήτησης είναι πολύ εξοικειωμένοι με το μήνυμα σφάλματος #N/A που τους υποδέχεται όταν το VLOOKUP ή η λειτουργία τους MATCH δεν μπορούν να βρουν αυτό που χρειάζεται. Και συχνά υπάρχει ένας λογικός λόγος για αυτό.
Επομένως, οι χρήστες ερευνούν γρήγορα πώς να αποκρύψουν αυτό το σφάλμα επειδή δεν είναι σωστό ή χρήσιμο. Και, φυσικά, υπάρχουν τρόποι για να γίνει αυτό.
Το XLOOKUP έρχεται με το δικό του ενσωματωμένο όρισμα «αν δεν βρέθηκε» για να χειριστεί τέτοια σφάλματα. Ας το δούμε στην πράξη με το προηγούμενο παράδειγμα, αλλά με λάθος πληκτρολόγιο ID.
Ο ακόλουθος τύπος θα εμφανίσει το κείμενο “Εσφαλμένο αναγνωριστικό” αντί για το μήνυμα σφάλματος: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,”Εσφαλμένο αναγνωριστικό”)
Χρήση XLOOKUP για αναζήτηση εύρους
Αν και δεν είναι τόσο συνηθισμένο όσο η ακριβής αντιστοίχιση, μια πολύ αποτελεσματική χρήση ενός τύπου αναζήτησης είναι η αναζήτηση μιας τιμής σε εύρη. Πάρτε το ακόλουθο παράδειγμα. Θέλουμε να επιστρέψουμε την έκπτωση ανάλογα με το ποσό που δαπανήθηκε.
Αυτή τη φορά δεν ψάχνουμε για μια συγκεκριμένη τιμή. Πρέπει να γνωρίζουμε πού εμπίπτουν οι τιμές στη στήλη Β μέσα στα εύρη της στήλης Ε. Αυτό θα καθορίσει την έκπτωση που κερδίσατε.
Το XLOOKUP έχει ένα προαιρετικό πέμπτο όρισμα (θυμηθείτε, είναι προεπιλεγμένο για την ακριβή αντιστοίχιση) που ονομάζεται λειτουργία αντιστοίχισης.
Μπορείτε να δείτε ότι το XLOOKUP έχει μεγαλύτερες δυνατότητες με κατά προσέγγιση αντιστοιχίσεις από αυτό του VLOOKUP.
Υπάρχει η επιλογή να βρείτε την πλησιέστερη αντιστοίχιση μικρότερη από (-1) ή την πλησιέστερη μεγαλύτερη από (1) την τιμή που αναζητήσατε. Υπάρχει επίσης μια επιλογή χρήσης χαρακτήρων μπαλαντέρ (2) όπως το ? ή το *. Αυτή η ρύθμιση δεν είναι ενεργοποιημένη από προεπιλογή όπως ήταν με το VLOOKUP.
Ο τύπος σε αυτό το παράδειγμα επιστρέφει το πλησιέστερο μικρότερο από την τιμή που αναζητήθηκε εάν δεν βρεθεί μια ακριβής αντιστοίχιση: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
Ωστόσο, υπάρχει ένα λάθος στο κελί C7 όπου επιστρέφεται το σφάλμα #N/A (το όρισμα “αν δεν βρέθηκε” δεν χρησιμοποιήθηκε). Αυτό θα έπρεπε να είχε επιστρέψει έκπτωση 0%, επειδή η δαπάνη 64 δεν πληροί τα κριτήρια για οποιαδήποτε έκπτωση.
Ένα άλλο πλεονέκτημα της συνάρτησης XLOOKUP είναι ότι δεν απαιτεί το εύρος αναζήτησης να είναι σε αύξουσα σειρά όπως το VLOOKUP.
Εισαγάγετε μια νέα σειρά στο κάτω μέρος του πίνακα αναζήτησης και, στη συνέχεια, ανοίξτε τον τύπο. Επεκτείνετε το εύρος που χρησιμοποιείται κάνοντας κλικ και σύροντας τις γωνίες.
Ο τύπος διορθώνει αμέσως το σφάλμα. Δεν είναι πρόβλημα να έχετε το «0» στο κάτω μέρος του εύρους.
Προσωπικά, θα εξακολουθούσα να ταξινομώ τον πίνακα κατά τη στήλη αναζήτησης. Το να έχω «0» στο κάτω μέρος θα με τρέλανε. Αλλά το γεγονός ότι η φόρμουλα δεν έσπασε είναι λαμπρό.
Το XLOOKUP Αντικαθιστά επίσης τη λειτουργία HLOOKUP
Όπως αναφέρθηκε, η συνάρτηση XLOOKUP είναι επίσης εδώ για να αντικαταστήσει το HLOOKUP. Μία λειτουργία για αντικατάσταση δύο. Εξοχος!
Η συνάρτηση HLOOKUP είναι η οριζόντια αναζήτηση, που χρησιμοποιείται για αναζήτηση κατά μήκος σειρών.
Δεν είναι τόσο γνωστό όσο το αδελφό του VLOOKUP, αλλά χρήσιμο για παραδείγματα όπως παρακάτω, όπου οι κεφαλίδες βρίσκονται στη στήλη Α και τα δεδομένα βρίσκονται κατά μήκος των σειρών 4 και 5.
Το XLOOKUP μπορεί να κοιτάξει και προς τις δύο κατευθύνσεις – κάτω στήλες και επίσης κατά μήκος σειρών. Δεν χρειαζόμαστε πλέον δύο διαφορετικές λειτουργίες.
Σε αυτό το παράδειγμα, ο τύπος χρησιμοποιείται για την επιστροφή της τιμής πώλησης που σχετίζεται με το όνομα στο κελί A2. Φαίνεται στη γραμμή 4 για να βρει το όνομα και επιστρέφει την τιμή από τη σειρά 5: =XLOOKUP(A2,B4:E4,B5:E5)
Το XLOOKUP μπορεί να κοιτάξει από κάτω προς τα πάνω
Συνήθως, πρέπει να αναζητήσετε μια λίστα για να βρείτε την πρώτη (συχνά μοναδική) εμφάνιση μιας τιμής. Το XLOOKUP έχει ένα έκτο όρισμα που ονομάζεται λειτουργία αναζήτησης. Αυτό μας δίνει τη δυνατότητα να αλλάξουμε την αναζήτηση για να ξεκινήσει από το κάτω μέρος και να αναζητήσουμε μια λίστα για να βρούμε την τελευταία εμφάνιση μιας τιμής.
Στο παρακάτω παράδειγμα, θα θέλαμε να βρούμε το επίπεδο αποθέματος για κάθε προϊόν στη στήλη Α.
Ο πίνακας αναζήτησης είναι με σειρά ημερομηνίας και υπάρχουν πολλαπλοί έλεγχοι αποθεμάτων ανά προϊόν. Θέλουμε να επιστρέψουμε το επίπεδο αποθέματος από την τελευταία φορά που ελέγχθηκε (τελευταία εμφάνιση του αναγνωριστικού προϊόντος).
Το έκτο όρισμα της συνάρτησης XLOOKUP παρέχει τέσσερις επιλογές. Μας ενδιαφέρει να χρησιμοποιήσουμε την επιλογή «Αναζήτηση από τελευταία σε πρώτη».
Ο ολοκληρωμένος τύπος εμφανίζεται εδώ: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
Σε αυτόν τον τύπο, το τέταρτο και το πέμπτο όρισμα αγνοήθηκαν. Είναι προαιρετικό και θέλαμε την προεπιλογή μιας ακριβούς αντιστοίχισης.
Μάνδρισμα ζώων
Η συνάρτηση XLOOKUP είναι η τον πολυαναμενόμενο διάδοχο και στις δύο συναρτήσεις VLOOKUP και HLOOKUP.
Σε αυτό το άρθρο χρησιμοποιήθηκαν διάφορα παραδείγματα για να δείξουν τα πλεονεκτήματα του XLOOKUP. Ένα από αυτά είναι ότι το XLOOKUP μπορεί να χρησιμοποιηθεί σε φύλλα, βιβλία εργασίας και επίσης με πίνακες. Τα παραδείγματα διατηρήθηκαν απλά στο άρθρο για να βοηθήσουν στην κατανόησή μας.
Εξαιτίας δυναμικοί πίνακες που εισάγονται στο Excel σύντομα, μπορεί επίσης να επιστρέψει μια σειρά τιμών. Αυτό είναι σίγουρα κάτι που αξίζει να εξερευνήσετε περαιτέρω.
Οι μέρες του VLOOKUP είναι μετρημένες. Το XLOOKUP είναι εδώ και σύντομα θα είναι η de facto φόρμουλα αναζήτησης.