Η συνάρτηση XLOOKUP στο excel αποτελεί βελτιωμένη έκδοση της vlookup. Ουσιαστικά η xlookup κάνει την ίδια δουλειά με τη vlookup αλλά με πιο ευέλικτο τρόπο. Χρησιμοποούμε την xlookup όταν γνωρίζουμε την τιμή μιας στήλης σε έναν πίνακα και θέλουμε να πάρουμε την τιμή στην ίδια γραμμή από μια άλλη στήλη.
Σύνταξη της XLOOKUP
Στη σύνταξη της XLOOKUP υπάρχουν τρεις υποχρεωτικές παράμετροι και μία προαιρετική.
- lookup_value: Η τιμή που ψάχνουμε.
- lookup_array: Η περιοχή/στήλη μέσα στην οποία ψάχνουμε την τιμή.
- return_array: Η περιοχή/στήλη από την οποία θέλουμε να επιστραφεί το αποτέλεσμα.
- [if_not_found]: Προαιρετική. Τι θέλουμε να εμφανιστεί αν η τιμή δεν βρεθεί.
Και εδώ βλέπουμε τη σύνταξη:
=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found])
Παράδειγμα σύνταξης της XLOOKUP
Υποθέτουμε ότι έχουμε το ακόλουθο dataset:
Εφαρμόζουμε την XLOOKUP:
=XLOOKUP(1004;A2:A11;E2:E11;"Δεν βρέθηκε")
- lookup_value: 1004 Σημαίνει: Τι ψάχνω;
- lookup_array: A2:A11 Σημαίνει: Πού το ψάχνω;
- return_array: E2:E11 Σημαίνει: Από πού θέλω το αποτέλεσμα;
- if_not_found: "Δεν βρέθηκε" Σημαίνει: Τι να εμφανίσει αν δεν το βρει;
Το αποτέλεσμα είναι 2100.
Σύγκριση της XLOOKUP με άλλες συναρτήσεις στο excel
Η XLOOKUP κάνει ότι και η VLOOKUP, αλλά πιο εύκολα και πιο ευέλικτα. Με την XLOOKUP η αναζήτηση μπορεί να γίνει είτε προς δεξιά ή προς τα αριστερά ενώ στη VLOOKUP μόνο προς τα δεξιά. Επίσης η XLOOKUP επιτρέπει exact match, approximate match, custom "Not Found", αναζήτηση πρώτης/τελευταίας εμφάνισης. Παρόμοια δουλειά κάνουν και οι συναρτήσεις INDEX/MATCH.
Σύγκριση της XLOOKUP με τη VLOOKUP
Ακολουθούν παραδείγματα που βοηθούν να καταλάβουμε τη διαφορά ανάμεσα σε XLOOKUP και VLOOKUP
1. Απλή αναζήτηση προς τα δεξιά
Θέλουμε από το EmpID 1004 να βρούμε το Salary.
VLOOKUP
=VLOOKUP(1004;A2:F11;5;FALSE)
Αποτέλεσμα: 2100
XLOOKUP
=XLOOKUP(1004;A2:A11;E2:E11)
Αποτέλεσμα: 2100
Εδώ κάνουν ουσιαστικά την ίδια δουλειά. Η XLOOKUP όμως δεν χρειάζεται να μετρήσουμε ότι το Salary είναι η 5η στήλη.
2. Αναζήτηση προς τα αριστερά
Θέλουμε από το ManagerID 9005 να βρούμε το Name.
=XLOOKUP(9005;F2:F11;B2:B11)
Αποτέλεσμα: Petros
Αυτό είναι σημαντική διαφορά: η στήλη αναζήτησης F βρίσκεται δεξιά από τη στήλη αποτελέσματος B.
Η κλασική VLOOKUP:
=VLOOKUP(9005;F2:B11;...;FALSE)
δεν μπορεί να λειτουργήσει έτσι, γιατί η VLOOKUP αναζητά στην πρώτη στήλη του table array και επιστρέφει τιμή από στήλη στα δεξιά.
3. Exact match
Θέλουμε να βρούμε το Department του εργαζομένου 1008.
=XLOOKUP(1008;A2:A11;C2:C11)
Αποτέλεσμα: Marketing
Στην XLOOKUP το exact match είναι το default.
Στη VLOOKUP συνήθως γράφουμε:
=VLOOKUP(1008;A2:F11;3;FALSE)
Το FALSE είναι απαραίτητο για exact match.
4. Custom "Not Found"
Αν ψάξουμε EmpID που δεν υπάρχει, π.χ. 1050:
=XLOOKUP(1050;A2:A11;B2:B11;"Δεν βρέθηκε εργαζόμενος")
Αποτέλεσμα:
Δεν βρέθηκε εργαζόμενος
Με VLOOKUP θα χρειαζόμασταν συνήθως:
=IFERROR(VLOOKUP(1050;A2:F11;2;FALSE);"Δεν βρέθηκε εργαζόμενος")
Άρα η XLOOKUP έχει ενσωματωμένο το [if_not_found].
5. Πρώτη εμφάνιση
Εδώ το dataset σου είναι πολύ καλό παράδειγμα, επειδή υπάρχουν επαναλαμβανόμενα ManagerID.
Για το ManagerID 9004 υπάρχουν:
- Kostas — EmpID 1006
- Sofia — EmpID 1007
Αν γράψουμε:
=XLOOKUP(9004;F2:F11;B2:B11)
παίρνουμε:
Kostas
Η XLOOKUP από προεπιλογή ψάχνει από πάνω προς τα κάτω, άρα επιστρέφει την πρώτη εμφάνιση.
6. Τελευταία εμφάνιση
Αν θέλουμε για το ίδιο ManagerID την τελευταία εμφάνιση:
=XLOOKUP(9004;F2:F11;B2:B11;"";0;-1)
Αποτέλεσμα:
Sofia
Το τελευταίο όρισμα -1 σημαίνει:
Search last-to-first
Δηλαδή η XLOOKUP ξεκινά την αναζήτηση από το τέλος προς την αρχή.
Αυτό είναι μια δυνατότητα που δεν προσφέρει τόσο απλά η VLOOKUP.
7. Επιστροφή πολλών στηλών ταυτόχρονα
Ένα ακόμη πολύ καλό παράδειγμα:
=XLOOKUP(1005;A2:A11;B2:F11)
Η XLOOKUP μπορεί να επιστρέψει ολόκληρη τη γραμμή.
Σύγκριση της XLOOKUP με τη MATCH / INDEX
Σε πολύ κόσμο υπάρχει η αντίληψη ότι η XLOOKUP είναι πάντα καλύτερη. Αυτό ισχύει για τις περισσότερες καθημερινές αναζητήσεις στις οποίες η XLOOKUP είναι πιο βολική. Όμως υπάρχουν περιπτώσεις όπου το INDEX/MATCH έχει είναι πιο κατάλληλο ή πιο ευέλικτο.
1. Απλό lookup: καλύτερο το XLOOKUP
Θέλουμε το Salary του EmpID 1005.
XLOOKUP
=XLOOKUP(1005;A2:A11;E2:E11)
Επιστρέφει: 2800
INDEX/MATCH
=INDEX(E2:E11;MATCH(1005;A2:A11;0))
Επιστρέφει: 2800
Εδώ η XLOOKUP έχει σαφές πρακτικό πλεονέκτημα: λιγότερη σύνταξη και διαβάζεται ευκολότερα.
2. Δεν υπάρχει η τιμή καλύτερο το XLOOKUP
Ψάχνουμε EmpID 1050, που δεν υπάρχει.
XLOOKUP
=XLOOKUP(1050;A2:A11;B2:B11;"Δεν βρέθηκε")
INDEX/MATCH
=IFERROR(INDEX(B2:B11;MATCH(1050;A2:A11;0));"Δεν βρέθηκε")
Η XLOOKUP έχει το [if_not_found] ενσωματωμένο.
Πλεονέκτημα: XLOOKUP, ειδικά όταν θέλεις καθαρούς τύπους για business users.
3. Θέλω την τελευταία εμφάνιση καλύτερο το XLOOKUP
Στο dataset το ManagerID 9005 εμφανίζεται δύο φορές:
- Petros
- Dimitra
Θέλουμε τον τελευταίο εργαζόμενο με ManagerID 9005.
Με XLOOKUP:
=XLOOKUP(9005;F2:F11;B2:B11;"";0;-1)
Επιστρέφει: Dimitra
Το -1 λέει στην XLOOKUP να ψάξει από κάτω προς τα πάνω.
Με INDEX/MATCH το αντίστοιχο lookup τελευταίας εμφάνισης απαιτεί πιο σύνθετη τεχνική.
Πλεονέκτημα: XLOOKUP.
4. Θέλω πολλά στοιχεία μαζί καλύτερο το XLOOKUP
Θέλουμε για τον εργαζόμενο 1005 όλα τα στοιχεία από Name έως ManagerID:
=XLOOKUP(1005;A2:A11;B2:F11)
Μπορεί να κάνει spill και να επιστρέψει:
Eleni | IT | Senior | 2800 | 9003
Με ένα lookup παίρνεις πολλές στήλες.
Πλεονέκτημα: XLOOKUP, ειδικά στο σύγχρονο Excel με dynamic arrays.
Πού αρχίζει να έχει ενδιαφέρον το INDEX/MATCH;
5. Κάνω μία αναζήτηση και θέλω να χρησιμοποιήσω τη θέση πολλές φορές → INDEX/MATCH
Ας πούμε ότι στο H2 έχουμε το EmpID:
1005
Μπορούμε να βρούμε μία φορά τη θέση:
=MATCH(H2;A2:A11;0)
Επιστρέφει: 5
Και μετά να χρησιμοποιούμε αυτή τη θέση σε διαφορετικές στήλες:
=INDEX(B2:B11;5)
Επιστρέφει: Eleni
=INDEX(C2:C11;5)
Επιστρέφει: IT
=INDEX(E2:E11;5)
Επιστρέφει: 2800
Εδώ φαίνεται μια σημαντική εννοιολογική διαφορά:
XLOOKUP: «Βρες Χ και επέστρεψε Υ.»
INDEX/MATCH: «Βρες πρώτα πού βρίσκεται το Χ. Μετά χρησιμοποίησε αυτή τη θέση όπου θέλεις.»
Αυτό κάνει το INDEX/MATCH χρήσιμο όταν η θέση της εγγραφής είναι από μόνη της χρήσιμη ή θέλεις να τη χρησιμοποιήσεις σε άλλους υπολογισμούς.
6. Αναζήτηση και σε γραμμή και σε στήλη: καλύτερο το INDEX/MATCH
Εδώ το INDEX/MATCH δείχνει ένα από τα πιο ωραία πλεονεκτήματά του.
Ας υποθέσουμε ότι θέλουμε:
«Βρες τον εργαζόμενο 1005 και επέστρεψε τη στήλη που γράφει Salary.»
Δεν θέλουμε δηλαδή να βάλουμε hard-coded τη στήλη E.
Μπορούμε να γράψουμε:
=INDEX(A2:F11;
MATCH(1005;A2:A11;0);
MATCH("Salary";A1:F1;0))
Το πρώτο MATCH βρίσκει:
ποια γραμμή; Επιστρέφει: EmpID 1005
Το δεύτερο MATCH:
ποια στήλη; Επιστρέφει: Salary
και το INDEX βρίσκει την τομή.
Αυτό είναι το κλασικό two-way lookup και είναι μια περίπτωση όπου το INDEX/MATCH είναι πολύ φυσικό.
Χρήση της XLOOKUP στην ανάλυση δεδομένων
Η XLOOKUP είναι πολύ χρήσιμη στην ανάλυση δεδομένων διότι προσφέρει δυνατότητες data enrichment, mapping, joining πληροφοριών και reconciliation μεταξύ datasets.
- Data Enrichment σημαίνει: «Πρόσθεσε νέα πληροφορία στις εγγραφές μου.»
- Mapping σημαίνει:«Μετέτρεψε/αντιστοίχισε μια τιμή σε κάποια άλλη.»
- Joining σημαίνει:«Σύνδεσε δύο datasets χρησιμοποιώντας ένα κοινό key.»
- Reconciliation σημαίνει:«Σύνδεσε δύο datasets και έλεγξε αν οι τιμές τους συμφωνούν.»
Στην πράξη, data enrichment και joining επικαλύπτονται αρκετά: το joining είναι ο μηχανισμός με τον οποίο συχνά πραγματοποιούμε το enrichment. Το reconciliation έχει διαφορετικό στόχο: δεν θέλουμε απλώς να προσθέσουμε δεδομένα, αλλά να εντοπίσουμε αποκλίσεις μεταξύ δύο πηγών.
Ακολουθούν παραδείγματα που δείχνουν πως θα μπορούσαμε να προσθέσουμε αυτές τις ενέργειες ανάλυσης δεδομένων με χρήση της XLOOKUP στο παραπάνω παράδειγμα:
1. Data Enrichment — εμπλουτισμός δεδομένων
Έστω ότι έχουμε δεύτερο dataset με πληροφορίες για τα Departments:
Θέλουμε να προσθέσουμε το Location στον αρχικό πίνακα εργαζομένων.
=XLOOKUP(C2;$H$2:$H$6;$I$2:$I$6;"Unknown")
Για την Anna, η XLOOKUP παίρνει το Sales, το βρίσκει στον δεύτερο πίνακα και επιστρέφει Athens.
Ο αρχικός πίνακας εμπλουτίζεται:
Αυτό είναι data enrichment: παίρνουμε ένα dataset και του προσθέτουμε πληροφορία από άλλο dataset μέσω ενός κοινού key (Department).
2. Mapping — αντιστοίχιση τιμών
Έστω ότι θέλουμε να μετατρέψουμε τα Level σε αριθμητικό Level Score:
Χρησιμοποιούμε:
=XLOOKUP(D2;$H$2:$H$4;$I$2:$I$4;"Unknown")
και παίρνουμε:
Εδώ η XLOOKUP λειτουργεί ως mapping tool:
Junior → 1, Mid → 2, Senior → 3
Αυτό είναι ιδιαίτερα χρήσιμο όταν θέλουμε να μετατρέψουμε codes, categories ή IDs σε πιο χρήσιμες τιμές.
3. Joining — ένωση πληροφοριών από δύο datasets
Έστω ότι το HR μας στέλνει δεύτερο αρχείο:
Κοινό πεδίο μεταξύ των δύο datasets είναι το EmpID.
Στον αρχικό πίνακα γράφουμε:
=XLOOKUP(A2;$H$2:$H$6;$I$2:$I$6;"No Bonus")
Έτσι ουσιαστικά κάνουμε ένα απλό join:
Employees + Bonus μέσω EmpID
και παίρνουμε:
Η λογική είναι αντίστοιχη με ένα join βάσει key που θα κάναμε σε SQL, Power Query ή Python.
4. Reconciliation — έλεγχος δύο datasets
Αυτό είναι ίσως το πιο ωραίο παράδειγμα για data analysis.
Έστω ότι έχουμε το Salary από το HR system και θέλουμε να το συγκρίνουμε με ένα δεύτερο αρχείο από το Payroll:
Πρώτα φέρνουμε το Payroll Salary:
=XLOOKUP(A2;$H$2:$H$6;$I$2:$I$6;"Not Found")
και μετά συγκρίνουμε:
=IF(E2=G2;"OK";"Mismatch")
Αποτέλεσμα:
Αυτό είναι reconciliation: χρησιμοποιούμε ένα κοινό identifier (EmpID) για να αντιστοιχίσουμε εγγραφές από δύο πηγές και στη συνέχεια ελέγχουμε αν οι πληροφορίες συμφωνούν.
Ασκήσεις XLOOKUP
Ακολουθούν ασκήσεις για να κάνετε εξάσκηση στην XLOOKUP. Σε όλες τις ασκήσεις χρησιμοποιούμε το παραπάνω dataset:
- Βρες το Name του εργαζομένου με EmpID 1006.
- Βρες το Salary του εργαζομένου με EmpID 1008.
- Βρες το Department του εργαζομένου με EmpID 1003.
- Κάνε αναζήτηση με βάση το Name: για "Eleni", επέστρεψε το EmpID της.
- Για "Petros", επέστρεψε το Salary του.
- Πρόκληση: Ο χρήστης γράφει ένα EmpID. Αν υπάρχει, εμφάνισε το όνομα του εργαζομένου. Αν δεν υπάρχει, εμφάνισε "Employee not found".
Αν θέλετε να εξασκηθείτε περισσότερο, στην ιστοσελίδα μας θα βρείτε πολλές ασκήσεις excel.
Σεμινάρια excel για εργαζομένους
Για εργαζομένους και στελέχη που θέλουν να χρησιμοποιούν το Excel με μεγαλύτερη ταχύτητα και αποτελεσματικότητα, πραγματοποιούνται εξειδικευμένα σεμινάρια Excel για επιχειρήσεις από τον εκπαιδευτή ενηλίκων Νικόλαο Μπαλατσούκα. Το εκπαιδευτικό πρόγραμμα μπορεί να καλύψει από βασικές λειτουργίες μέχρι προχωρημένες συναρτήσεις Excel, ανάλογα με τις ανάγκες των συμμετεχόντων. Παράλληλα, έχουμε δημοσιεύσει στην ιστοσελίδα μας πολλά αναλυτικά παραδείγματα, ώστε οι επισκέπτες να έχουν πρόσβαση σε χρήσιμο υλικό για μελέτη και εξάσκηση. Εδώ μπορείτε να ενημερωθείτε για τα σεμινάρια excel για εργαζομένους.