Τρόπος χρήσης της συνάρτησης XLOOKUP στο Microsoft Excel

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

Τι είναι το XLOOKUP;

Η νέα συνάρτηση XLOOKUP έχει λύσεις για ορισμένους από τους μεγαλύτερους περιορισμούς του VLOOKUP. Επιπλέον, αντικαθιστά επίσης το HLOOKUP. Για παράδειγμα, το XLOOKUP μπορεί να κοιτάξει προς τα αριστερά του, από προεπιλογή σε ακριβή αντιστοίχιση και σας επιτρέπει να καθορίσετε μια περιοχή κελιών αντί για έναν αριθμό στήλης. Το VLOOKUP δεν είναι τόσο εύκολο στη χρήση ή ως ευέλικτο. Θα σας δείξουμε πώς λειτουργεί όλα.

Προς το παρόν, το XLOOKUP είναι διαθέσιμο μόνο σε χρήστες του προγράμματος Insiders. Οποιοσδήποτε μπορεί να συμμετάσχει στο πρόγραμμα Insiders για πρόσβαση στις νεότερες δυνατότητες του Excel μόλις γίνουν διαθέσιμες. Η Microsoft θα ξεκινήσει σύντομα να το κυκλοφορεί σε όλους τους χρήστες του Office 365.

Τρόπος χρήσης της συνάρτησης XLOOKUP

Ας βουτήξουμε κατευθείαν με ένα παράδειγμα XLOOKUP σε δράση. Δείτε τα παρακάτω παραδείγματα. Θέλουμε να επιστρέψουμε το τμήμα από τη στήλη F για κάθε αναγνωριστικό στη στήλη A.

Αυτό είναι ένα κλασικό παράδειγμα ακριβούς αναζήτησης. Η συνάρτηση 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 περιορίστηκε αναζητώντας την πιο αριστερή στήλη ενός πίνακα και στη συνέχεια επιστρέφοντας από έναν καθορισμένο αριθμό στηλών προς τα δεξιά.

Στο παρακάτω παράδειγμα, πρέπει να αναζητήσουμε ένα αναγνωριστικό (στήλη Ε) και να επιστρέψουμε το όνομα του ατόμου (στήλη Δ).

Ο ακόλουθος τύπος μπορεί να επιτύχει αυτό: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Τι να κάνετε αν δεν βρεθεί

Οι χρήστες των λειτουργιών αναζήτησης είναι πολύ εξοικειωμένοι με το μήνυμα σφάλματος # N / A που τους υποδέχεται όταν το VLOOKUP ή η λειτουργία MATCH δεν μπορούν να βρουν αυτό που χρειάζεται. Και συχνά υπάρχει ένας λογικός λόγος για αυτό.

Επομένως, οι χρήστες ερευνούν γρήγορα πώς να αποκρύψουν αυτό το σφάλμα επειδή δεν είναι σωστό ή χρήσιμο. Και, φυσικά, υπάρχουν τρόποι να το πράξουν.

Το XLOOKUP έρχεται με το δικό του ενσωματωμένο όρισμα «αν δεν βρεθεί» για τον χειρισμό τέτοιων σφαλμάτων. Ας το δούμε σε δράση με το προηγούμενο παράδειγμα, αλλά με ένα λανθασμένο αναγνωριστικό.

Ο ακόλουθος τύπος θα εμφανίσει το κείμενο "Λανθασμένο αναγνωριστικό" αντί για το μήνυμα σφάλματος: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Χρήση του 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 έχει ένα έκτο όρισμα που ονομάζεται λειτουργία αναζήτησης. Αυτό μας δίνει τη δυνατότητα να αλλάξουμε την αναζήτηση για να ξεκινήσουμε στο κάτω μέρος και να αναζητήσουμε μια λίστα για να βρούμε την τελευταία εμφάνιση μιας τιμής.

Στο παρακάτω παράδειγμα, θα θέλαμε να βρούμε το επίπεδο αποθεμάτων για κάθε προϊόν στη στήλη A.

Ο πίνακας αναζήτησης είναι σε σειρά ημερομηνίας και υπάρχουν πολλοί έλεγχοι αποθεμάτων ανά προϊόν. Θέλουμε να επιστρέψουμε το επίπεδο των αποθεμάτων από την τελευταία φορά που ελέγχθηκε (τελευταία εμφάνιση του αναγνωριστικού προϊόντος).

Το έκτο όρισμα της συνάρτησης XLOOKUP παρέχει τέσσερις επιλογές. Μας ενδιαφέρει να χρησιμοποιήσουμε την επιλογή «Αναζήτηση από πρώτο σε πρώτο».

Ο ολοκληρωμένος τύπος εμφανίζεται εδώ: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

Σε αυτόν τον τύπο, το τέταρτο και το πέμπτο επιχείρημα αγνοήθηκαν. Είναι προαιρετικό και θέλαμε την προεπιλογή ενός ακριβούς αγώνα.

Μάνδρισμα ζώων

Η συνάρτηση XLOOKUP είναι ο ανυπόμονος διάδοχος των λειτουργιών VLOOKUP και HLOOKUP.

Μια ποικιλία παραδειγμάτων χρησιμοποιήθηκαν σε αυτό το άρθρο για να καταδειχθούν τα πλεονεκτήματα του XLOOKUP. Ένα από αυτά είναι ότι το XLOOKUP μπορεί να χρησιμοποιηθεί σε φύλλα, βιβλία εργασίας και επίσης με πίνακες. Τα παραδείγματα διατηρήθηκαν απλά στο άρθρο για να βοηθήσουν στην κατανόησή μας.

Λόγω της σύντομης εισαγωγής δυναμικών συστοιχιών στο Excel, μπορεί επίσης να επιστρέψει μια σειρά τιμών. Αυτό είναι σίγουρα κάτι που αξίζει να εξερευνήσετε περαιτέρω.

Οι ημέρες του VLOOKUP αριθμούνται. Το XLOOKUP είναι εδώ και σύντομα θα είναι ο de facto τύπος αναζήτησης.