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

Περίληψη

Η συνάρτηση Excel XLOOKUP είναι μια σύγχρονη και ευέλικτη αντικατάσταση παλαιότερων συναρτήσεων όπως VLOOKUP, HLOOKUP και LOOKUP. Το XLOOKUP υποστηρίζει κατά προσέγγιση και ακριβή αντιστοίχιση, μπαλαντέρ (*?) Για μερικές αντιστοιχίες και αναζητήσεις σε κάθετες ή οριζόντιες περιοχές.

Σκοπός

Τιμές αναζήτησης σε εύρος ή πίνακα

Τιμή επιστροφής

Τιμές αντιστοίχισης από τον πίνακα επιστροφής

Σύνταξη

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Επιχειρήματα

  • lookup - Η τιμή αναζήτησης.
  • lookup_array - Ο πίνακας ή το εύρος προς αναζήτηση.
  • return_array - Ο πίνακας ή το εύρος για επιστροφή.
  • not_found - (προαιρετικό) Τιμή για επιστροφή εάν δεν βρέθηκε αντιστοιχία.
  • match_mode - (προαιρετικό) 0 = ακριβής αντιστοίχιση (προεπιλογή), -1 = ακριβής αντιστοίχιση ή επόμενη μικρότερη, 1 = ακριβής αντιστοίχιση ή επόμενη μεγαλύτερη, 2 = αγώνας μπαλαντέρ.
  • search_mode - (προαιρετικό) 1 = αναζήτηση από την πρώτη (προεπιλογή), -1 = αναζήτηση από την τελευταία, 2 = δυαδική αναζήτηση αύξουσα, -2 = δυαδική αναζήτηση φθίνουσα.

Εκδοχή

Excel 365

Σημειώσεις χρήσης

Το XLOOKUP είναι μια σύγχρονη αντικατάσταση για τη λειτουργία VLOOKUP. Είναι μια ευέλικτη και ευέλικτη λειτουργία που μπορεί να χρησιμοποιηθεί σε μια μεγάλη ποικιλία καταστάσεων.

Το XLOOKUP μπορεί να βρει τιμές σε κατακόρυφες ή οριζόντιες περιοχές, μπορεί να εκτελέσει κατά προσέγγιση και ακριβείς αντιστοιχίσεις και υποστηρίζει μπαλαντέρ (*?) Για μερικούς αγώνες. Επιπλέον, το XLOOKUP μπορεί να αναζητήσει δεδομένα ξεκινώντας από την πρώτη τιμή ή την τελευταία τιμή (δείτε τον τύπο αντιστοίχισης και λεπτομέρειες τρόπου αναζήτησης παρακάτω). Σε σύγκριση με παλαιότερες λειτουργίες όπως VLOOKUP, HLOOKUP και LOOKUP, το XLOOKUP προσφέρει πολλά βασικά πλεονεκτήματα.

Δεν βρέθηκε μήνυμα

Όταν το XLOOKUP δεν μπορεί να βρει αντιστοίχιση, επιστρέφει το σφάλμα # N / A, όπως και άλλες λειτουργίες αντιστοίχισης στο Excel. Σε αντίθεση με τις άλλες λειτουργίες αντιστοίχισης, το XLOOKUP υποστηρίζει ένα προαιρετικό όρισμα που ονομάζεται not_found το οποίο μπορεί να χρησιμοποιηθεί για να παρακάμψει το σφάλμα # N / A όταν θα εμφανιζόταν διαφορετικά. Οι τυπικές τιμές για το not_found μπορεί να είναι "Δεν βρέθηκε", "Χωρίς αντιστοίχιση", "Χωρίς αποτέλεσμα", κ.λπ. Όταν παρέχετε μια τιμή για το not_found, επισυνάψτε το κείμενο σε διπλά εισαγωγικά ("").

Σημείωση: Προσέξτε εάν παρέχετε μια κενή συμβολοσειρά ("") για not_found. Εάν δεν βρεθεί αντιστοιχία, το XLOOKUP δεν θα εμφανίζει τίποτα αντί για # N / A. Εάν θέλετε να δείτε το σφάλμα # N / A όταν δεν υπάρχει αντιστοιχία, παραλείψτε εντελώς το όρισμα.

Τύπος αντιστοίχισης

Από προεπιλογή, το XLOOKUP θα εκτελέσει έναν ακριβή αγώνα. Η συμπεριφορά αγώνα ελέγχεται από ένα προαιρετικό όρισμα που ονομάζεται match_type, το οποίο έχει τις ακόλουθες επιλογές:

Τύπος αντιστοίχισης η ΣΥΜΠΕΡΙΦΟΡΑ
0 (προεπιλογή) Ακριβής αντιστοίχιση. Θα επιστρέψει # N / A εάν δεν υπάρχει αγώνας.
-1 Ακριβής αντιστοίχιση ή επόμενο μικρότερο στοιχείο.
1 Ακριβής αντιστοίχιση ή επόμενο μεγαλύτερο αντικείμενο.
2 Αγώνας μπαλαντέρ (*,?, ~)

Λειτουργία αναζήτησης

Από προεπιλογή, το XLOOKUP θα ξεκινήσει την αντιστοίχιση από την πρώτη τιμή δεδομένων. Η συμπεριφορά αναζήτησης ελέγχεται από ένα προαιρετικό όρισμα που ονομάζεται search_mode , το οποίο παρέχει τις ακόλουθες επιλογές:

Λειτουργία αναζήτησης η ΣΥΜΠΕΡΙΦΟΡΑ
1 (προεπιλογή) Αναζήτηση από την πρώτη τιμή
-1 Αναζήτηση από την τελευταία τιμή (αντίστροφη)
2 Οι δυαδικές τιμές αναζήτησης ταξινομούνται σε αύξουσα σειρά
-2 Οι δυαδικές τιμές αναζήτησης ταξινομούνται με φθίνουσα σειρά

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

Παράδειγμα # 1 - βασική ακριβής αντιστοίχιση

Από προεπιλογή, το XLOOKUP θα εκτελέσει έναν ακριβή αγώνα. Στο παρακάτω παράδειγμα, το XLOOKUP χρησιμοποιείται για την ανάκτηση πωλήσεων βάσει ακριβούς αντιστοίχισης στην ταινία. Ο τύπος στο H5 είναι:

=XLOOKUP(H4,B5:B9,E5:E9)

Λεπτομερέστερη εξήγηση εδώ.

Παράδειγμα # 2 - βασική κατά προσέγγιση αντιστοίχιση

Για να ενεργοποιήσετε μια αντιστοίχιση κατά προσέγγιση, δώστε μια τιμή για το όρισμα "match_mode". Στο παρακάτω παράδειγμα, το XLOOKUP χρησιμοποιείται για τον υπολογισμό μιας έκπτωσης βάσει της ποσότητας, η οποία απαιτεί κατά προσέγγιση αντιστοιχία. Ο τύπος στο F5 παρέχει -1 για το match_mode για να επιτρέψει κατά προσέγγιση αντιστοίχιση με τη συμπεριφορά "ακριβής αντιστοίχιση ή το επόμενο μικρότερο":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Λεπτομερέστερη εξήγηση εδώ.

Παράδειγμα # 3 - πολλαπλές τιμές

Το XLOOKUP μπορεί να επιστρέψει περισσότερες από μία τιμές ταυτόχρονα για τον ίδιο αγώνα. Το παρακάτω παράδειγμα δείχνει πώς μπορεί να ρυθμιστεί το XLOOKUP ώστε να επιστρέφει τρεις τιμές αντιστοίχισης με έναν μόνο τύπο. Ο τύπος στο C5 είναι:

=XLOOKUP(B5,B8:B15,C8:E15)

Παρατηρήστε ότι ο πίνακας επιστροφής (C8: E15) περιλαμβάνει 3 στήλες: Πρώτη, Τελευταία, Τμήμα. Και οι τρεις τιμές επιστρέφονται και χύνονται στην περιοχή C5: E5.

Παράδειγμα # 4 - αμφίδρομη αναζήτηση

Το XLOOKUP μπορεί να χρησιμοποιηθεί για την εκτέλεση αμφίδρομης αναζήτησης, τοποθετώντας ένα XLOOKUP μέσα σε ένα άλλο. Στο παρακάτω παράδειγμα, το "εσωτερικό" XLOOKUP ανακτά μια ολόκληρη σειρά (όλες οι τιμές για το Glass), η οποία παραδίδεται στο "εξωτερικό" XLOOKUP ως πίνακας επιστροφής. Το εξωτερικό XLOOKUP βρίσκει την κατάλληλη ομάδα (B) και επιστρέφει την αντίστοιχη τιμή (17.25) ως τελικό αποτέλεσμα.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Περισσότερες λεπτομέρειες εδώ.

Παράδειγμα # 5 - δεν βρέθηκε μήνυμα

Όπως και άλλες λειτουργίες αναζήτησης, εάν το XLOOKUP δεν εντοπίσει μια τιμή, επιστρέφει το σφάλμα # N / A. Για να εμφανίσετε ένα προσαρμοσμένο μήνυμα αντί για # N / A, δώστε μια τιμή για το προαιρετικό όρισμα "not found", που περικλείεται σε διπλά εισαγωγικά (""). Για παράδειγμα, για να εμφανιστεί η ένδειξη "Δεν βρέθηκε" όταν δεν βρέθηκε ταινία που να ταιριάζει, με βάση το παρακάτω φύλλο εργασίας, χρησιμοποιήστε:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Μπορείτε να προσαρμόσετε αυτό το μήνυμα όπως θέλετε: "Χωρίς αντιστοίχιση", "Η ταινία δεν βρέθηκε" κ.λπ.

Παράδειγμα # 6 - σύνθετα κριτήρια

Με τη δυνατότητα χειρισμού εγγενών συστοιχιών, το XLOOKUP μπορεί να χρησιμοποιηθεί με πολύπλοκα κριτήρια. Στο παρακάτω παράδειγμα, το XLOOKUP αντιστοιχεί στην πρώτη εγγραφή όπου: ο λογαριασμός ξεκινά με "x" και η περιοχή είναι "ανατολικά" και ο μήνας δεν είναι Απρίλιος:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Λεπτομέρειες: (1) απλό παράδειγμα, (2) πιο περίπλοκο παράδειγμα.

Οφέλη XLOOKUP

Το XLOOKUP προσφέρει πολλά σημαντικά πλεονεκτήματα, ειδικά σε σύγκριση με το VLOOKUP:

  • Το XLOOKUP μπορεί να αναζητήσει δεδομένα δεξιά ή αριστερά των τιμών αναζήτησης
  • Το XLOOKUP μπορεί να επιστρέψει πολλά αποτελέσματα (παράδειγμα # 3 παραπάνω)
  • Οι προεπιλογές XLOOKUP σε ακριβή αντιστοίχιση (οι προεπιλογές VLOOKUP είναι κατά προσέγγιση)
  • Το XLOOKUP μπορεί να λειτουργήσει με κάθετα και οριζόντια δεδομένα
  • Το XLOOKUP μπορεί να πραγματοποιήσει μια αντίστροφη αναζήτηση (τελευταία έως πρώτη)
  • Το XLOOKUP μπορεί να επιστρέψει ολόκληρες σειρές ή στήλες, όχι μόνο μία τιμή
  • Το XLOOKUP μπορεί να λειτουργεί με συστοιχίες εγγενώς για την εφαρμογή σύνθετων κριτηρίων

Σημειώσεις

  1. Το XLOOKUP μπορεί να λειτουργήσει τόσο με κάθετες όσο και με οριζόντιες συστοιχίες.
  2. Το XLOOKUP θα επιστρέψει # N / A εάν δεν βρεθεί η τιμή αναζήτησης.
  3. Το lookup_array πρέπει να έχει μια διάσταση συμβατή με το όρισμα return_array , διαφορετικά το XLOOKUP θα επιστρέψει # VALUE!
  4. Εάν το XLOOKUP χρησιμοποιείται μεταξύ βιβλίων εργασίας, και τα δύο βιβλία εργασίας πρέπει να είναι ανοιχτά, διαφορετικά το XLOOKUP θα επιστρέψει #REF !.
  5. Όπως και η συνάρτηση INDEX, το XLOOKUP επιστρέφει μια αναφορά ως αποτέλεσμα.

Σχετικά βίντεο

Βασικό παράδειγμα XLOOKUP Σε αυτό το βίντεο, θα ρυθμίσουμε τη συνάρτηση XLOOKUP με ένα βασικό παράδειγμα. Αντιστοιχεί στο όνομα της πόλης, θα ανακτήσουμε τη χώρα και τον πληθυσμό Βασική κατά προσέγγιση αντιστοίχιση XLOOKUP Σε αυτό το βίντεο, θα ρυθμίσουμε τη συνάρτηση XLOOKUP για να εκτελέσουμε μια κατά προσέγγιση αντιστοίχιση προκειμένου να υπολογίσουμε μια έκπτωση βάσει ποσότητας. XLOOKUP με λογική boolean Σε αυτό το βίντεο θα δούμε πώς να χρησιμοποιήσουμε τη συνάρτηση XLOOKUP με λογική Boolean για την εφαρμογή πολλαπλών κριτηρίων. XLOOKUP με πολλαπλές τιμές αναζήτησης Σε αυτό το βίντεο, θα ρυθμίσουμε το XLOOKUP για την επιστροφή πολλαπλών τιμών σε μια δυναμική συστοιχία, παρέχοντας ένα εύρος τιμών αναζήτησης αντί για μία τιμή αναζήτησης.

ενδιαφέροντα άρθρα...