Πώς να χρησιμοποιήσετε τη συνάρτηση XLOOKUP στο Excel 2016

Το Excel 2016 για συνδρομητές του Office 365 σε Windows και Mac υποστηρίζει τώρα μια νέα λειτουργία XLOOKUP, που διαφημίζεται ως μια πολύ απλούστερη και πιο ευέλικτη αντικατάσταση της πολύ δημοφιλής (αλλά συχνά κακοποιημένης) συνάρτησης κάθετης αναζήτησης, VLOOKUP (δεν ξέρω τι είναι το X στο XLOOKUP σημαίνει εκτενής, ίσως;).

Για όσους από εσάς δεν είστε εξοικειωμένοι με το VLOOKUP (που θεωρείται η τρίτη πιο χρησιμοποιούμενη συνάρτηση αμέσως μετά το SUM και το AVERAGE), αυτή η συνάρτηση πραγματοποιεί αναζήτηση κάθετα κατά σειρά στην αριστερή στήλη ενός καθορισμένου πίνακα αναζήτησης από πάνω προς τα κάτω μέχρι να βρει μια τιμή σε στήλη αναζήτησης που ορίζεται από έναν αριθμό μετατόπισης που ταιριάζει ή υπερβαίνει αυτόν που αναζητάτε. Αν και είναι εξαιρετικά χρήσιμη για τον εντοπισμό συγκεκριμένων στοιχείων σε μια μακρά λίστα ή στήλη ενός πίνακα δεδομένων στο φύλλο εργασίας σας, η συνάρτηση VLOOKUP έχει αρκετούς περιορισμούς που δεν κοινοποιούνται από αυτήν τη νέα συνάρτηση αναζήτησης, όπως το XLOOKUP:

• Προεπιλογές για την εύρεση ακριβών αντιστοιχίσεων για την τιμή αναζήτησης στο εύρος αναζήτησης
• Μπορεί να πραγματοποιήσει αναζήτηση τόσο κάθετα (κατά σειρά) όσο και οριζόντια (κατά στήλη) σε έναν πίνακα, αντικαθιστώντας έτσι την ανάγκη χρήσης της συνάρτησης HLOOKUP κατά την οριζόντια αναζήτηση ανά στήλη
• Μπορεί να πραγματοποιήσει αναζήτηση αριστερά ή δεξιά, έτσι ώστε η περιοχή αναζήτησης στον πίνακα αναζήτησης να μην χρειάζεται να βρίσκεται σε μια στήλη στα αριστερά από αυτήν που έχει οριστεί ως περιοχή επιστροφής προκειμένου να λειτουργήσει η συνάρτηση
• Όταν χρησιμοποιείται η προεπιλογή ακριβούς αντιστοίχισης, λειτουργεί ακόμη και όταν οι τιμές στο εύρος αναζήτησης δεν ταξινομούνται με συγκεκριμένη σειρά
• Μπορεί να πραγματοποιήσει αναζήτηση από την κάτω σειρά προς την κορυφή στο εύρος του πίνακα αναζήτησης, χρησιμοποιώντας ένα προαιρετικό όρισμα λειτουργίας αναζήτησης

Η συνάρτηση XLOOKUP έχει πέντε πιθανά ορίσματα, τα τρία πρώτα από τα οποία είναι απαραίτητα και τα δύο τελευταία προαιρετικά, χρησιμοποιώντας την ακόλουθη σύνταξη:

XLOOKUP ( lookup_value , Lookup_Array , return_array , [ match_mode ], [ search_mode ])

Το απαιτούμενο όρισμα lookup_value προσδιορίζει την τιμή ή το στοιχείο για το οποίο κάνετε αναζήτηση. Το όρισμα του απαιτούμενου πίνακα look_up προσδιορίζει το εύρος των κελιών προς αναζήτηση για αυτήν την τιμή αναζήτησης και το όρισμα return_array προσδιορίζει το εύρος των κελιών που περιέχουν την τιμή που θέλετε να επιστραφεί όταν το Excel βρίσκει μια ακριβή αντιστοίχιση.

* Λάβετε υπόψη σας όταν προσδιορίζετε τα ορίσματα του συστοιχίας αναζήτησης και του πίνακα επιστροφής στη συνάρτηση XLOOKUP, και τα δύο εύρη πρέπει να έχουν ίσο μήκος, διαφορετικά το Excel θα επιστρέψει το #VALUE! σφάλμα στον τύπο σας. Αυτός είναι πολύ περισσότερο ο λόγος για να χρησιμοποιείτε ονόματα εύρους ή ονόματα στηλών ενός καθορισμένου πίνακα δεδομένων κατά τον ορισμό αυτών των ορισμάτων αντί να τα επισημαίνετε ή να πληκτρολογείτε τις αναφορές κελιών τους .

Το προαιρετικό όρισμα match_mode μπορεί να περιέχει οποιαδήποτε από τις ακόλουθες τέσσερις τιμές:

• 0 για ακριβή αντιστοίχιση (η προεπιλογή, όπως όταν δεν έχει οριστεί όρισμα match_mode )
• -1 για ακριβή αντιστοίχιση ή επόμενη μικρότερη τιμή
• 1 για ακριβή αντιστοίχιση ή επόμενη μεγαλύτερη τιμή
• 2 για μερική αντιστοιχία με χρήση μπαλαντέρ χαρακτήρες ενώνονται με αναφορά κελιού στο lookup_value επιχείρημα

Το προαιρετικό όρισμα search_mode μπορεί να περιέχει οποιαδήποτε από τις ακόλουθες τέσσερις τιμές:

• 1 για αναζήτηση από το πρώτο έως το τελευταίο, δηλαδή από πάνω προς τα κάτω (η προεπιλογή, όπως όταν δεν έχει οριστεί όρισμα search_mode )
• -1 για αναζήτηση από τελευταίο προς πρώτο, δηλαδή από κάτω προς τα πάνω
• 2 για δυαδική αναζήτηση με αύξουσα σειρά
• -2 για δυαδική αναζήτηση με φθίνουσα σειρά

Ο καλύτερος τρόπος για να κατανοήσετε τη δύναμη και την ευελιξία της νέας συνάρτησης XLOOKUP είναι να τη δείτε σε δράση σε ένα φύλλο εργασίας του Excel. Στο παρακάτω σχήμα, έχω ένα φύλλο εργασίας με έναν απλό πίνακα δεδομένων πωλήσεων 2019 ταξινομημένο ανά χώρα. Για να χρησιμοποιήσετε το XLOOKUP για να επιστρέψετε τις συνολικές πωλήσεις από αυτόν τον πίνακα στο κελί E4 με βάση τη χώρα που εισάγετε στο κελί D4 του φύλλου εργασίας, ακολουθήστε τα εξής βήματα:

Τοποθετήστε τον κέρσορα κελιού στο κελί E4 του φύλλου εργασίας

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

Κάντε κλικ στο κελί D4 στο φύλλο εργασίας για να εισαγάγετε την αναφορά του κελιού του στο πλαίσιο κειμένου ορίσματος Lookup_value.

Πατήστε Tab για να επιλέξετε το πλαίσιο κειμένου ορίσματος Lookup_array και, στη συνέχεια, κάντε κλικ στο κελί A4 και κρατήστε πατημένο το Shift καθώς πατάτε Ctrl-κάτω βέλος για να επιλέξετε A4:A8 ως εύρος αναζήτησης (επειδή η περιοχή A3:B8 ορίζεται ως πίνακας δεδομένων του Excel, Ο Πίνακας1[Χώρα] εμφανίζεται στο πλαίσιο κειμένου στη θέση του εύρους A4:A8).

Πατήστε Tab για να επιλέξετε το πλαίσιο κειμένου επιχείρημα Return_array, μετά κάντε κλικ στο κελί B4 και κρατήστε πατημένο το Shift καθώς πατάτε το κάτω βέλος Ctrl για να επιλέξετε B4:B8 ως το εύρος που περιέχει τις τιμές που θα επιστραφούν με βάση τα αποτελέσματα της αναζήτησης (που εμφανίζεται ως Πίνακας 1[Συνολικές πωλήσεις] στο πλαίσιο κειμένου).

Κάντε κλικ στο OK για να εισαγάγετε τον τύπο XLOOKUP στο κελί E4.

Δημιουργία τύπου με το XLOOKUP στο κελί E4 που επιστρέφει τις πωλήσεις με βάση τη χώρα που έχει εισαχθεί στο κελί D4.

Το Excel εισάγει τον τύπο XLOOKUP στο κελί E4 του φύλλου εργασίας και επιστρέφει 4900 ως αποτέλεσμα, επειδή η Κόστα Ρίκα έχει εισαχθεί αυτήν τη στιγμή στο κελί αναζήτησης D4 και όπως μπορείτε να δείτε στον πίνακα πωλήσεων του 2019, αυτή είναι πράγματι οι συνολικές πωλήσεις που πραγματοποιήθηκαν για αυτήν τη χώρα.

Επειδή το XLOOKUP λειτουργεί εξίσου καλά από δεξιά προς τα αριστερά καθώς και από αριστερά προς τα δεξιά, μπορείτε να χρησιμοποιήσετε αυτήν τη συνάρτηση εξίσου καλά για να επιστρέψετε τη χώρα από αυτόν τον πίνακα πωλήσεων με βάση έναν συγκεκριμένο αριθμό πωλήσεων. Το παρακάτω σχήμα σας δείχνει πώς το κάνετε αυτό. Αυτή τη φορά, δημιουργείτε τον τύπο XLOOKUP στο κελί D4 και ορίζετε την τιμή που έχει εισαχθεί στο κελί E4 (11.000, σε αυτήν την περίπτωση) ως το όρισμα lookup_value.

Επιπλέον, εισάγετε -1 ως το όρισμα match_mode για να παρακάμψετε την προεπιλογή ακριβούς αντιστοίχισης της συνάρτησης, έτσι ώστε το Excel να επιστρέφει τη χώρα με μια ακριβή αντιστοίχιση στην τιμή πωλήσεων που έχει εισαχθεί στο κελί αναζήτησης E4 ή σε αυτήν με τις επόμενες χαμηλότερες συνολικές πωλήσεις (Μεξικό με 10.000 $ σε αυτήν την περίπτωση, καθώς δεν υπάρχει χώρα σε αυτόν τον πίνακα με 11.000 $ συνολικών πωλήσεων). Χωρίς να ορίσει ένα όρισμα match_mode για αυτόν τον τύπο, το Excel θα επέστρεφε #NA ως αποτέλεσμα, επειδή δεν υπάρχει ακριβής αντιστοίχιση στα 11.000 $ σε αυτόν τον πίνακα πωλήσεων.

Δημιουργία τύπου με XLOOKUP στο κελί D4 που επιστρέφει τη χώρα με βάση τις πωλήσεις που έχουν εισαχθεί στο κελί E4

Επειδή η συνάρτηση XLOOKUP είναι εξίσου άνετη για την οριζόντια αναζήτηση ανά στήλη όπως και για κάθετη αναζήτηση, μπορείτε να τη χρησιμοποιήσετε για να δημιουργήσετε έναν τύπο που εκτελεί αμφίδρομη αναζήτηση (αντικαθιστά την ανάγκη δημιουργίας ενός τύπου που συνδυάζει τις συναρτήσεις INDEX και MATCH ως στο παρελθόν). Το παρακάτω σχήμα, που περιέχει τον πίνακα χρονοδιαγράμματος παραγωγής 2019 για τους αριθμούς ανταλλακτικών, AB-100 έως AB-103 για τους μήνες Απρίλιο έως Δεκέμβριο, σας δείχνει πώς γίνεται αυτό.

Δημιουργία τύπου με ένθετες συναρτήσεις XLOOKUP για επιστροφή του αριθμού των μονάδων που παράγονται για ένα μέρος σε έναν συγκεκριμένο μήνα

Στο κελί B12, δημιούργησα τον ακόλουθο τύπο:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(ημερομηνία_αναζήτηση,$B$2:$J$2,$B$3:$J$6))

Αυτός ο τύπος ξεκινά ορίζοντας μια συνάρτηση XLOOKUP που αναζητά κάθετα ανά σειρά για μια ακριβή αντιστοίχιση με την καταχώριση τμήματος που έγινε στο κελί με το όνομα part_lookup (κελί B10, σε αυτήν την περίπτωση) στην περιοχή κελιών $A$3:$A$6 του πίνακα παραγωγής . Σημειώστε, ωστόσο, ότι το όρισμα return_array για αυτήν την αρχική συνάρτηση LOOKUP είναι το ίδιο μια δεύτερη συνάρτηση XLOOKUP.

Αυτή η δεύτερη, ένθετη συνάρτηση XLOOKUP αναζητά το εύρος κελιών $B$2:$J$2 οριζόντια ανά στήλη για ακριβή αντιστοίχιση με την καταχώριση ημερομηνίας που έγινε στο κελί με το όνομα date_lookup (κελί B11, σε αυτήν την περίπτωση). Το όρισμα return_array για αυτήν τη δεύτερη, ένθετη συνάρτηση XLOOKUP είναι $B$3:$J$6, η περιοχή κελιών όλων των τιμών παραγωγής στον πίνακα.

Ο τρόπος που λειτουργεί αυτός ο τύπος είναι ότι το Excel υπολογίζει πρώτα το αποτέλεσμα της δεύτερης, ένθετης συνάρτησης XLOOKUP εκτελώντας μια οριζόντια αναζήτηση που, σε αυτήν την περίπτωση, επιστρέφει τον πίνακα στην περιοχή κελιών D3: D6 της στήλης Jun-19 (με τις τιμές: 438, 153, 306 και 779) ως αποτέλεσμα. Αυτό το αποτέλεσμα, με τη σειρά του, γίνεται το όρισμα return_array για την αρχική συνάρτηση XLOOKUP που εκτελεί μια κατακόρυφη αναζήτηση ανά σειρά για μια ακριβή αντιστοίχιση με την καταχώριση αριθμού εξαρτήματος που έγινε στο κελί B11 (με όνομα part_lookup). Επειδή, σε αυτό το παράδειγμα, αυτό το κελί part_lookup περιέχει AB-102, ο τύπος επιστρέφει μόνο την τιμή παραγωγής Jun-19, 306, από το αποτέλεσμα της δεύτερης, επόμενης συνάρτησης XLOOKUP.

Ορίστε το! Μια πρώτη ματιά στο XLOOKUP, μια ισχυρή, ευέλικτη και αρκετά εύχρηστη νέα συνάρτηση αναζήτησης που μπορεί όχι μόνο να κάνει τις αναζητήσεις μίας τιμής που εκτελούνται από τις συναρτήσεις VLOOKUP και HLOOKUP, αλλά και τις αμφίδρομες αναζητήσεις τιμών που εκτελούνται με το συνδυασμό των Λειτουργίες INDEX και MATCH επίσης.

* Δυστυχώς, η συνάρτηση XLOOKUP δεν είναι συμβατή με παλαιότερες εκδόσεις του Microsoft Excel που υποστηρίζουν μόνο τις λειτουργίες VLOOKUP και HLOOKUP ή συμβατή με τρέχουσες εκδόσεις που δεν την περιλαμβάνουν ακόμη ως μία από τις λειτουργίες αναζήτησης, όπως το Excel 2019 και το Excel Online . Αυτό σημαίνει ότι εάν μοιράζεστε ένα βιβλίο εργασίας που περιέχει τύπους XLOOKUP με συναδέλφους ή πελάτες που χρησιμοποιούν μια έκδοση του Excel που δεν περιλαμβάνει αυτήν τη νέα συνάρτηση αναζήτησης, όλοι αυτοί οι τύποι θα επιστρέψουν #NAME; τιμές σφάλματος όταν ανοίγουν το φύλλο εργασίας του.