Μετάβαση στο κύριο περιεχόμενο

Πώς να χρησιμοποιήσετε τη συνάρτηση NEW & ADVANCED XLOOKUP στο Excel (10 παραδείγματα)

Το Excel είναι νέο XLOOKUP είναι η πιο ισχυρή και ευκολότερη λειτουργία αναζήτησης που μπορεί να προσφέρει το Excel. Μέσα από αδιάκοπες προσπάθειες, η Microsoft κυκλοφόρησε τελικά αυτή τη συνάρτηση XLOOKUP για να αντικαταστήσει τις λειτουργίες VLOOKUP, HLOOKUP, INDEX+MATCH και άλλες λειτουργίες αναζήτησης.

Σε αυτό το σεμινάριο, θα σας δείξουμε ποια είναι τα πλεονεκτήματα του XLOOKUP και πώς μπορείτε να το αποκτήσετε και να το εφαρμόσετε για την επίλυση διαφορετικών προβλημάτων αναζήτησης.

Πώς να αποκτήσετε το XLOOKUP;

Σύνταξη συνάρτησης XLOOKUP

Παραδείγματα συναρτήσεων XLOOKUP

Λήψη δείγματος αρχείου XLOOKUP

Πώς να αποκτήσετε το XLOOKUP;

Από Λειτουργία XLOOKUP is διαθέσιμο μόνο in Excel για το Microsoft 365, Excel 2021, να Excel για τον Ιστό, μπορείτε να αναβαθμίσετε το Excel στη διαθέσιμη έκδοση για να λάβετε το XLOOKUP.

Σύνταξη συνάρτησης XLOOKUP

Η λειτουργία XLOOKUP αναζητά μια περιοχή ή έναν πίνακα και, στη συνέχεια, επιστρέφει την τιμή του πρώτου αποτελέσματος που ταιριάζει. ο Σύνταξη της συνάρτησης XLOOKUP είναι η εξής:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Λειτουργία xlookup 1

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

  1. Lookup_value (απαιτείται): η αξία που αναζητάτε. Μπορεί να βρίσκεται σε οποιαδήποτε στήλη του εύρους table_array.
  2. Lookup_array (απαιτείται): ο πίνακας ή το εύρος όπου αναζητάτε την τιμή αναζήτησης.
  3. Return_array (απαιτείται): ο πίνακας ή η περιοχή από όπου θέλετε να λάβετε την τιμή.
  4. If_not_found (προαιρετικό): η τιμή που επιστρέφεται όταν δεν βρεθεί έγκυρη αντιστοίχιση. Μπορείτε να προσαρμόσετε το κείμενο στο [if_not_found] ώστε να δείχνει ότι δεν υπάρχει αντιστοιχία.
    Διαφορετικά, η τιμή επιστροφής θα είναι #N/A από προεπιλογή.
  5. Match_mode (προαιρετικό): εδώ μπορείτε να καθορίσετε τον τρόπο αντιστοίχισης του lookup_value με τις τιμές στο lookup_array.
    • 0 (προεπιλογή) = Ακριβής αντιστοίχιση. Εάν δεν βρεθεί αντιστοίχιση, επιστρέψτε #N/A.
    • -1 = Ακριβής αντιστοίχιση. Εάν δεν βρεθεί αντιστοίχιση, επιστρέψτε την επόμενη μικρότερη τιμή.
    • 1 = Ακριβής αντιστοίχιση. Εάν δεν βρεθεί αντιστοίχιση, επιστρέψτε την επόμενη μεγαλύτερη τιμή.
    • 2 = Μερικός αγώνας. Χρησιμοποιήστε χαρακτήρες μπαλαντέρ όπως *, ? και ~ για να εκτελέσετε έναν αγώνα μπαλαντέρ.
  6. Search_mode (προαιρετικό): εδώ μπορείτε να καθορίσετε τη σειρά αναζήτησης που θα εκτελέσετε.
    • 1 (προεπιλογή) = Αναζήτηση της τιμής lookup_από το πρώτο στοιχείο έως το τελευταίο στοιχείο στον πίνακα_αναζήτησης.
    • -1 = Αναζήτηση της τιμής lookup_από το τελευταίο στοιχείο στο πρώτο στοιχείο. Βοηθά όταν πρέπει να λάβετε το τελευταίο αποτέλεσμα που ταιριάζει στον συστοιχία lookup_array.
    • 2 = Πραγματοποιήστε μια δυαδική αναζήτηση που απαιτεί την ταξινόμηση_συστοιχίας αναζήτησης σε αύξουσα σειρά. Εάν δεν ταξινομηθεί, το αποτέλεσμα επιστροφής θα είναι άκυρο.
    • -2 = Πραγματοποιήστε μια δυαδική αναζήτηση που απαιτεί την ταξινόμηση_συστοιχίας με φθίνουσα σειρά. Εάν δεν ταξινομηθεί, το αποτέλεσμα επιστροφής θα είναι άκυρο.

Για λεπτομερείς πληροφορίες για ορίσματα συνάρτησης XLOOKUP, κάντε τα εξής:

1. Πληκτρολογήστε το κάτω από τη σύνταξη σε ένα κενό κελί, σημειώστε ότι χρειάζεται μόνο να πληκτρολογήσετε τη μία πλευρά της αγκύλης.

=XLOOKUP(

Λειτουργία xlookup 2

2. Τύπος Ctrl + A, Τότε ένα πλαίσιο προτροπής αναδύεται που δείχνει τα επιχειρήματα συνάρτησης. Και η άλλη πλευρά του βραχίονα τελειώνει αυτόματα.

Λειτουργία xlookup 3

3. Τραβήξτε προς τα κάτω τον πίνακα δεδομένων, τότε μπορείτε να τα δείτε όλα έξι ορίσματα συνάρτησης του XLOOKUP.

Λειτουργία xlookup 4 >>> Λειτουργία xlookup 5

Παραδείγματα συναρτήσεων XLOOKUP

Είμαι βέβαιος ότι έχετε κατακτήσει τις βασικές αρχές της λειτουργίας XLOOKUP τώρα. Ας βουτήξουμε κατευθείαν στο πρακτικά παραδείγματα του XLOOKUP.

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

Εκτελέστε μια ακριβή αντιστοίχιση με το XLOOKUP

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

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

Λειτουργία xlookup 6

πληκτρολογήστε το παρακάτω τύπος στο κενό κελί F2 και πατήστε το εισάγετε κλειδί για να λάβετε το αποτέλεσμα.

=XLOOKUP(E2,A2:A10,C2:C10)

Λειτουργία xlookup 7

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

Μόνο μερικά κλικ για να λάβετε ακριβή αντιστοίχιση

Ίσως χρησιμοποιείτε μια χαμηλότερη έκδοση του Excel και δεν έχετε ακόμη σχέδιο για αναβάθμιση σε Excel 2021 ή Microsoft 365. Σε αυτή την περίπτωση, θα προτείνω ένα εύχρηστο χαρακτηριστικό - Αναζητήστε έναν τύπο τιμής στη λίστα of Kutools για Excel. Με αυτήν τη δυνατότητα, μπορείτε να έχετε το αποτέλεσμα χωρίς περίπλοκους τύπους ή πρόσβαση στο XLOOKUP.

Με οur Πρόσθετο Excel installed, κάντε τα εξής:

1. Κάντε κλικ στο κελί για να βάλετε το αντίστοιχο αποτέλεσμα.

2. Πηγαίνετε να το Kutools κάντε κλικ στην καρτέλα Βοηθός τύπου, και στη συνέχεια κάντε κλικ στο κουμπί Βοηθός τύπου στην αναπτυσσόμενη λίστα.

Λειτουργία xlookup 8

3. Στο Παράθυρο διαλόγου Βοηθός τύπων, παρακαλούμε ρυθμίστε τις παραμέτρους ως εξής:

  • Αγορά Lookup στο Ενότητα Τύπος τύπου;
  • Στο Επιλέξτε μια ενότητα τύπου, Επιλέξτε Αναζητήστε μια τιμή στη λίστα;
  • Στο Ενότητα εισαγωγής επιχειρημάτων, κάντε τα εξής:
    • Στο Πλαίσιο πίνακα_συστοιχίας, επιλέξτε το εύρος δεδομένων που περιέχει την τιμή αναζήτησης και την τιμή του αποτελέσματος.
    • Στο Πλαίσιο Lookup_value, επιλέξτε το κελί ή το εύρος της τιμής που αναζητάτε. Λάβετε υπόψη ότι πρέπει να βρίσκεται στην πρώτη στήλη του πίνακα_πίνακας.
    • Στο Κουτί στήλης, επιλέξτε τη στήλη από την οποία θα επιστρέψετε την αντιστοιχισμένη τιμή.

Λειτουργία xlookup 9

4. Κάντε κλικ στο κουμπί OK για να πάρετε το αποτέλεσμα.

Λειτουργία xlookup 10

Κάντε κλικ για λήψη του Kutools για Excel για δωρεάν δοκιμή 30 ημερών.


Παράδειγμα 2. Κατά προσέγγιση ταίριασμα

Εκτελέστε μια κατά προσέγγιση αντιστοίχιση με το XLOOKUP

Για να εκτελέσετε ένα κατά προσέγγιση αναζήτηση, Θα πρέπει να ορίστε τη λειτουργία αντιστοίχισης σε 1 ή -1 στο πέμπτο όρισμα. Όταν δεν βρεθεί ακριβής αντιστοίχιση, επιστρέφει την επόμενη μεγαλύτερη ή μικρότερη τιμή.

Σε αυτή την περίπτωση, πρέπει να γνωρίζετε τους φορολογικούς συντελεστές των εισοδημάτων του προσωπικού σας. Στην αριστερή πλευρά του υπολογιστικού φύλλου είναι τα Ομοσπονδιακά Βήματα Φόρου Εισοδήματος για το 2021. Πώς μπορείτε να βρείτε τον φορολογικό συντελεστή του προσωπικού σας στη στήλη Ε; Μην ανησυχείς. Παρακαλούμε κάντε τα εξής:

1. Πληκτρολογήστε το παρακάτω τύπος στο κενό κελί E2 και πατήστε το εισάγετε κλειδί για να λάβετε το αποτέλεσμα.
Στη συνέχεια αλλάξτε τη μορφοποίηση του επιστρεφόμενου αποτελέσματος όπως χρειάζεστε.

=XLOOKUP(D2,B2:B8,A2:A8,,1)

Λειτουργία xlookup 11 >>> Λειτουργία xlookup 12

√ Σημείωση: Το τέταρτο όρισμα [If_not_found] είναι προαιρετικό, οπότε απλώς το παραλείπω.

2. Τώρα γνωρίζετε τον φορολογικό συντελεστή του κελιού D2. Για να πάρετε τα υπόλοιπα αποτελέσματα, Θα πρέπει να μετατρέψτε τις αναφορές κελιών του συστοιχίας lookup_array και return_array σε απόλυτες.

  • Κάντε διπλό κλικ στο κελί E2 για να εμφανιστεί ο τύπος =XLOOKUP(D2,B2:B8,A2:A8,,1);
  • Επιλέξτε το εύρος αναζήτησης B2:B8 στον τύπο, πατήστε το πλήκτρο F4 για να λάβετε $B$2:$B$8;
  • Επιλέξτε το εύρος επιστροφής A2:A8 στον τύπο, πατήστε το πλήκτρο F4 για να λάβετε $A$2:$A$8;
  • Πατήστε το κουμπί εισάγετε κουμπί για να λάβετε το αποτέλεσμα του κελιού E2.
Λειτουργία xlookup 13 >>> Λειτουργία xlookup 14

3. Επειτα σύρετε τη λαβή πλήρωσης προς τα κάτω για να λάβετε όλα τα αποτελέσματα.

Λειτουργία xlookup 15

√ Σημείωση:

  • Πατώντας το πλήκτρο F4 στο πληκτρολόγιο σας επιτρέπει να αλλάξτε την αναφορά κελιού σε απόλυτη αναφορά προσθέτοντας σύμβολα δολαρίου πριν από τη γραμμή και τη στήλη.
  • Μετά την εφαρμογή της απόλυτης αναφοράς για αναζήτηση και επιστροφή εύρους, αλλάξαμε ο τύπος στο κελί Ε2 σε αυτήν την έκδοση:

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

  • Όταν σύρετε τη λαβή πλήρωσης προς τα κάτω από το κελί E2, οι τύποι σε κάθε κελί της στήλης Ε βρίσκονται άλλαξε μόνο στην πτυχή του lookup_value.
    Για παράδειγμα, ο τύπος στο E13 μετατρέπεται τώρα σε αυτό:

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

Παράδειγμα 3: Ταίριασμα μπαλαντέρ

Εκτελέστε έναν αγώνα μπαλαντέρ με το XLOOKUP

Πριν εξετάσουμε το Λειτουργία αντιστοίχισης χαρακτήρων μπαλαντέρ XLOOKUP, ας δούμε πρώτα τι είναι τα μπαλαντέρ.

Στο Microsoft Excel, Οι χαρακτήρες μπαλαντέρ είναι ένα ειδικό είδος χαρακτήρα που μπορεί να αντικαταστήσει οποιονδήποτε χαρακτήρα. Είναι ιδιαίτερα χρήσιμο όταν θέλετε να πραγματοποιήσετε μερικές αναζητήσεις αντιστοίχισης.

Υπάρχουν τρεις τύποι μπαλαντέρ: ένας αστερίσκος (*), ερωτηματικό (?), να Tilde (~).

  • Ο αστερίσκος (*) αντιπροσωπεύει οποιονδήποτε αριθμό χαρακτήρων στο κείμενο.
  • Το ερωτηματικό (?) σημαίνει οποιονδήποτε μεμονωμένο χαρακτήρα του κειμένου.
  • Το Tilde (~) χρησιμοποιείται για τη μετατροπή των χαρακτήρων μπαλαντέρ (*, ? ~) σε κυριολεκτικούς χαρακτήρες. Τοποθετήστε την περισπίδα (~) μπροστά από τους χαρακτήρες μπαλαντέρ για να εκπληρώσετε αυτήν τη λειτουργία.

Στις περισσότερες περιπτώσεις, όταν εκτελούμε τη λειτουργία αντιστοίχισης χαρακτήρων μπαλαντέρ XLOOKUP, χρησιμοποιούμε τον χαρακτήρα αστερίσκου (*). Τώρα ας δούμε πώς λειτουργεί η αντιστοίχιση μπαλαντέρ.

Ας υποθέσουμε ότι έχετε μια λίστα με την Κεφαλαιοποίηση Χρηματιστηρίου των 50 Μεγαλύτερων Αμερικανικών Εταιρειών και θέλετε να μάθετε την κεφαλαιοποίηση μερικών εταιρειών, αλλά τα ονόματα των εταιρειών είναι εν συντομία, αυτό είναι το τέλειο σενάριο για αντιστοίχιση χαρακτήρων μπαλαντέρ. Παρακαλώ ακολουθήστε με βήμα προς βήμα για να κάνετε το κόλπο.

Λειτουργία xlookup 16

√ Σημείωση: Για να εκτελέσετε μια αντιστοίχιση χαρακτήρων μπαλαντέρ, το πιο σημαντικό πράγμα είναι να ορίσετε το πέμπτο όρισμα [match_mode] σε 2.

1. Πληκτρολογήστε το παρακάτω τύπος στο κενό κελί H3 και πατήστε το εισάγετε κλειδί για να λάβετε το αποτέλεσμα.

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

Λειτουργία xlookup 17 >>> Λειτουργία xlookup 18

2. Τώρα γνωρίζετε το αποτέλεσμα του κελιού H3. Για να έχετε τα υπόλοιπα αποτελέσματα, πρέπει διορθώστε τον συστοιχία lookup_array και return_array τοποθετώντας τον κέρσορα στον πίνακα και πατώντας το πλήκτρο F4. Τότε ο τύπος στο H3 γίνεται:

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. Σύρετε τη λαβή πλήρωσης προς τα κάτω για να λάβετε όλα τα αποτελέσματα.

Λειτουργία xlookup 19

√ Σημείωση:

  • Η τιμή_αναζήτησης του τύπου στο κελί H3 είναι "*"&G3&"*". Εμείς συνδυάστε τον μπαλαντέρ αστερίσκου (*) με την τιμή G3 χρησιμοποιώντας τα σύμφωνο (&).
  • Το τέταρτο όρισμα [If_not_found] είναι προαιρετικό, οπότε απλώς το παραλείπω.
Παράδειγμα 4: Κοιτάξτε προς τα αριστερά

Κοιτάξτε προς τα αριστερά χρησιμοποιώντας το XLOOKUP

Ένας μειονέκτημα του VLOOKUP είναι ότι είναι περιορίζεται να πραγματοποιεί αναζητήσεις στα δεξιά της στήλης αναζήτησης. Εάν προσπαθήσετε να αναζητήσετε τιμές που έχουν απομείνει στη στήλη αναζήτησης, θα λάβετε το σφάλμα #N/A. Μην ανησυχείς. Το XLOOKUP είναι η τέλεια λειτουργία αναζήτησης για την επίλυση αυτού του προβλήματος.

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

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

Λειτουργία xlookup 20

Πρέπει να αναζητήσουμε τη στήλη Γ και να επιστρέψουμε την τιμή στη στήλη Α. Κάντε τα εξής:

1. Πληκτρολογήστε το παρακάτω τύπος στο κενό κελί G2.

=XLOOKUP(F2,C2:C11,A2:A11)

2. Πάτα το εισάγετε κλειδί για να λάβετε το αποτέλεσμα.

Λειτουργία xlookup 21

√ Σημείωση: Η συνάρτηση XLOOKUP look to left μπορεί να αντικαταστήσει το Index και το Match για να αναζητήσει τιμές στα αριστερά.

Αναζήτηση τιμής από τα δεξιά προς τα αριστερά με μερικά κλικ

Για όσους δεν θέλουν να θυμούνται φόρμουλες, εδώ, θα σας προτείνω ένα χρήσιμο χαρακτηριστικό - Αναζήτηση από τα δεξιά προς τα αριστερά of Kutools για Excel. Με αυτήν τη δυνατότητα, μπορείτε να πραγματοποιήσετε αναζήτηση από τα δεξιά προς τα αριστερά μέσα σε λίγα δευτερόλεπτα.

Με οur Πρόσθετο Excel installed, κάντε τα εξής:

1. Πηγαίνετε να το Kutools καρτέλα στο Excel, βρείτε Σούπερ LOOKUP, και κάντε κλικ στο κουμπί ΑΝΑΖΗΤΗΣΗ από δεξιά προς τα αριστερά στην αναπτυσσόμενη λίστα.

Λειτουργία xlookup 22

2. Στο ΑΝΑΖΗΤΗΣΗ από τα δεξιά προς τα αριστερά παράθυρο διαλόγου, πρέπει να ρυθμίσετε τις παραμέτρους ως εξής:

  • Στο Ενότητα τιμών αναζήτησης και εύρους εξόδου, καθορίστε το εύρος αναζήτησης και εύρος εξόδου;
  • Στο Ενότητα εύρους δεδομένων, είσοδος φασμα ΔΕΔΟΜΕΝΩΝ, στη συνέχεια καθορίστε τη στήλη του κλειδιού και στήλη επιστροφής;

Λειτουργία xlookup 23

3. Κάντε κλικ στο κουμπί OK για να πάρετε το αποτέλεσμα.

Λειτουργία xlookup 24

Κάντε κλικ για λήψη του Kutools για Excel για δωρεάν δοκιμή 30 ημερών.


Παράδειγμα 5: Κατακόρυφη ή Οριζόντια αναζήτηση

Εκτελέστε μια κατακόρυφη ή οριζόντια αναζήτηση με το XLOOKUP

Ως χρήστες του Excel, μπορεί να είστε εξοικειωμένοι με τις λειτουργίες VLOOKUP και HLOOKUP. Το VLOOKUP είναι να κοιτάτε κάθετα σε μια στήλη και Το HLOOKUP είναι να κοιτάτε οριζόντια σε μια σειρά.

Τώρα το νέο XLOOKUP τα συνδυάζει και τα δύο, εννοώντας χρειάζεται να χρησιμοποιήσετε μόνο μία σύνταξη για να εκτελέσετε κάθετη αναζήτηση ή οριζόντια αναζήτηση. Ιδιοφυΐα, έτσι δεν είναι;

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

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

=XLOOKUP(E1,A2:A13,B2:B13)

Λειτουργία xlookup 25

Για να εκτελέσετε μια οριζόντια αναζήτηση, πληκτρολογήστε το παρακάτω τύπος στο κενό κελί P2, πατήστε το εισάγετε κλειδί για να λάβετε το αποτέλεσμα.

=XLOOKUP(P1,B1:M1,B2:M2)

Λειτουργία xlookup 26

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

Παράδειγμα 6: Αμφίδρομη αναζήτηση

Εκτελέστε αμφίδρομη αναζήτηση με το XLOOKUP

Εξακολουθείτε να χρησιμοποιείτε Λειτουργίες INDEX και MATCH να αναζητήσετε μια τιμή σε ένα δισδιάστατο εύρος; Δοκιμάστε το βελτιωμένο XLOOKUP για να ολοκληρώσετε τη δουλειά σας πιο εύκολα.

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

Ας υποθέσουμε ότι έχετε μια λίστα με τους βαθμούς των μαθητών με διαφορετικούς κλάδους, θέλετε να μάθετε τον βαθμό του μαθήματος Χημεία του Kim.

Λειτουργία xlookup 43

Ας δούμε πώς χρησιμοποιούμε το μαγικό XLOOKUP για να κάνουμε το κόλπο.

    • Εκτελούμε το "εσωτερικό" XLOOKUP για να επιστρέψουμε τιμές μιας στήλης enter. Το XLOOKUP(H2,B1:E1,B2:E10) μπορεί να πάρει μια σειρά από βαθμούς Χημείας.
    • Φωλιάζουμε το "εσωτερικό" XLOOKUP μέσα στο "εξωτερικό" XLOOKUP χρησιμοποιώντας το "εσωτερικό" XLOOKUP ως πίνακα επιστροφής στον πλήρη τύπο.
    • Στη συνέχεια, ακολουθεί η τελική φόρμουλα:

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

  • πληκτρολογήστε το παραπάνω τύπος στο κενό κελί H3, πατήστε το εισάγετε για να πάρετε το αποτέλεσμα.

Λειτουργία xlookup 27

Ή μπορείτε να κάνετε το αντίστροφο, χρησιμοποιήστε το "εσωτερικό" XLOOKUP για να επιστρέψετε τιμές μιας ολόκληρης σειράς, οι οποίες είναι όλες οι βαθμοί θεμάτων του Kim. Στη συνέχεια, χρησιμοποιήστε το "εξωτερικό" XLOOKUP για να αναζητήσετε τον βαθμό Χημείας μεταξύ όλων των βαθμών του θέματος του Kim.

    • πληκτρολογήστε το παρακάτω τύπος στο κενό κελί H4 και πατήστε το εισάγετε για να πάρετε το αποτέλεσμα.

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

Λειτουργία xlookup 28

Η λειτουργία αμφίδρομης αναζήτησης του XLOOKUP είναι επίσης η τέλεια απεικόνιση της κάθετης και οριζόντιας λειτουργίας αναζήτησης. Δοκιμάστε αν θέλετε!

Παράδειγμα 7: Προσαρμογή μηνύματος που δεν βρέθηκε

Προσαρμόστε το μήνυμα που δεν βρέθηκε χρησιμοποιώντας το XLOOKUP

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

Με την ενσωματωμένο όρισμα [if_not_found], μπορείτε να καθορίσετε ένα προσαρμοσμένο μήνυμα για την αντικατάσταση του αποτελέσματος #N/A. Πληκτρολογήστε το κείμενο που χρειάζεστε στο προαιρετικό τέταρτο όρισμα και περικλείστε το κείμενο διπλά εισαγωγικά (").

Για παράδειγμα, η πόλη Ντένβερ δεν βρέθηκε, επομένως το XLOOKUP επιστρέφει το μήνυμα σφάλματος #N/A. Αλλά αφού προσαρμόσουμε το τέταρτο όρισμα με το κείμενο "No Match", ο τύπος θα εμφανίσει το κείμενο "No Match" αντί για το μήνυμα σφάλματος.

πληκτρολογήστε το παρακάτω τύπος στο κενό κελί F3 και πατήστε το εισάγετε για να πάρετε το αποτέλεσμα.

=XLOOKUP(E2,A2:A11,C2:C11,"No Match")

Λειτουργία xlookup 29

Προσαρμόστε το #N/A σφάλμα με μια εύχρηστη λειτουργία

Για να παρακάμψετε γρήγορα το σφάλμα #N/A με το προσαρμοσμένο μήνυμά σας, Kutools για Excel is τέλειο εργαλείο στο Excel για να σας βοηθήσουμε. Με το ενσωματωμένο του Αντικαταστήστε το 0 ή το #N/A με το χαρακτηριστικό Blank ή μια συγκεκριμένη τιμή, μπορείτε να καθορίσετε το μήνυμα που δεν βρέθηκε χωρίς περίπλοκους τύπους ή πρόσβαση στο XLOOKUP.

Με μας Πρόσθετο Excel εγκατεστημένο, κάντε τα εξής:

1. Πηγαίνετε να το Kutools καρτέλα στο Excel, βρείτε Σούπερ LOOKUP, και κάντε κλικ στο κουμπί Αντικαταστήστε το 0 ή # N / A με κενό ή συγκεκριμένη τιμή στην αναπτυσσόμενη λίστα.

Λειτουργία xlookup 30

2. Στο Αντικαταστήστε το 0 ή το #N/A με ένα παράθυρο διαλόγου Blank ή Specific Value, πρέπει να ρυθμίσετε τις παραμέτρους ως εξής:

  • Στο Ενότητα τιμών αναζήτησης και εύρους εξόδου, Επιλέξτε το εύρος αναζήτησης και εύρος εξόδου;
  • Τότε επιλέξτε Αντικατάσταση 0 ή #Δ/Υ με μια συγκεκριμένη τιμή, εισάγετε το κείμενο σου αρέσει;
  • Στο Ενότητα εύρους δεδομένων, Επιλέξτε το φασμα ΔΕΔΟΜΕΝΩΝ, στη συνέχεια καθορίστε το στήλη κλειδιού και επιστρεφόμενη στήλη.

Λειτουργία xlookup 31

3. Κάντε κλικ στο κουμπί OK κουμπί για να λάβετε το αποτέλεσμα. Το προσαρμοσμένο μήνυμα θα εμφανιστεί όταν δεν βρεθεί αντιστοίχιση.

Λειτουργία xlookup 32

Κάντε κλικ για λήψη του Kutools για Excel για δωρεάν δοκιμή 30 ημερών.


Παράδειγμα 8: Πολλαπλές τιμές

Επιστρέψτε πολλαπλές τιμές με το XLOOKUP

Άλλος πλεονέκτημα του XLOOKUP είναι η ικανότητά του να επιστροφή πολλαπλών τιμών ταυτόχρονα για τον ίδιο αγώνα. Εισαγάγετε έναν τύπο για να λάβετε το πρώτο αποτέλεσμα και μετά άλλες επιστρεφόμενες τιμές χυθεί στα γειτονικά κενά κελιά αυτόματα.

Στο παρακάτω παράδειγμα, θέλετε να λάβετε όλες τις πληροφορίες σχετικά με το αναγνωριστικό μαθητή "FG9940005". Το κόλπο είναι να παρέχετε ένα εύρος ως πίνακας επιστροφής στον τύπο αντί για μία στήλη ή γραμμή. Σε αυτήν την περίπτωση, το εύρος του πίνακα επιστροφής είναι B2:D9, συμπεριλαμβανομένων τριών στηλών.

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

=XLOOKUP(F2,A2:A9,B2:D9)

Λειτουργία xlookup 33

Όλα τα κελιά αποτελέσματος εμφανίζουν τον ίδιο τύπο. Μπορείς επεξεργαστείτε ή τροποποιήσετε ο τύπος στο πρώτο κελί, Αλλά σε άλλα κελιά, ο τύπος δεν είναι επεξεργάσιμος. Μπορείτε να δείτε ότι η γραμμή τύπων είναι γκρι, που σημαίνει ότι δεν μπορεί να γίνει καμία αλλαγή σε αυτό.

Λειτουργία xlookup 34

Συνολικά, η συνάρτηση πολλαπλών τιμών του XLOOKUP είναι α χρήσιμη βελτίωση σε σύγκριση με το VLOOKUP. Δεν μπορείτε να προσδιορίσετε τον αριθμό κάθε στήλης ξεχωριστά για κάθε τύπο. Μπράβο!

Παράδειγμα 9. Πολλαπλά κριτήρια

Εκτελέστε αναζήτηση πολλαπλών κριτηρίων χρησιμοποιώντας το XLOOKUP

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

Πρέπει να ξέρουμε την τιμή του μεσαίου μπλε βάζου. Σε αυτήν την περίπτωση, απαιτούνται τρεις τιμές αναζήτησης (κριτήρια) για την αναζήτηση αντιστοίχισης. Πληκτρολογήστε το τύπος παρακάτω στο κενό κελί I2 και, στη συνέχεια, πατήστε το εισάγετε κλειδί για να λάβετε το αποτέλεσμα.

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

Λειτουργία xlookup 35

√ Σημείωση: Το XLOOKUP μπορεί να επεξεργαστεί απευθείας πίνακες. Δεν χρειάζεται να επιβεβαιώσετε τον τύπο με Control + Shift + Enter.

Αναζήτηση πολλαπλών συνθηκών με γρήγορη μέθοδο

Υπάρχει καθόλου γρηγορότερα και ευκολότερα Τρόπος εκτέλεσης αναζήτησης πολλαπλών κριτηρίων από το XLOOKUP στο excel; Kutools για Excel παρέχει ένα εκπληκτικό χαρακτηριστικό - Αναζήτηση πολλαπλών συνθηκών. Με αυτήν τη δυνατότητα, μπορείτε να εκτελέσετε μια αναζήτηση πολλαπλών κριτηρίων με πολλά μόνο κλικ!

Με μας Πρόσθετο Excel εγκατεστημένο, κάντε τα εξής:

1. Πηγαίνετε να το Kutools καρτέλα στο Excel, βρείτε Σούπερ LOOKUP, και κάντε κλικ στο κουμπί Αναζήτηση πολλαπλών συνθηκών στην αναπτυσσόμενη λίστα.

Λειτουργία xlookup 36

2. Στο Παράθυρο διαλόγου Αναζήτηση πολλαπλών συνθηκών, κάντε τα εξής:

  • Στο Ενότητα αναζήτησης τιμών και εύρους εξόδου, Επιλέξτε το εύρος τιμών αναζήτησης και την εύρος εξόδου;
  • Στο Ενότητα Εύρος δεδομένων, κάντε τις ακόλουθες ενέργειες:
    • Επιλέξτε το αντίστοιχες βασικές στήλες που περιέχουν τις τιμές αναζήτησης μία προς μία κρατώντας το Ctrl πληκτρολογήστε το Πλαίσιο στήλης κλειδιού;
    • Καθορίστε το στήλη που περιέχει τις επιστρεφόμενες τιμές στο Πλαίσιο στήλης επιστροφής.

Λειτουργία xlookup 37

3. Κάντε κλικ στο κουμπί OK για να πάρετε το αποτέλεσμα.

Λειτουργία xlookup 38

√ Σημείωση:

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

Κάντε κλικ για λήψη του Kutools για Excel για δωρεάν δοκιμή 30 ημερών.


Παράδειγμα 10. Βρείτε την τιμή με την τελευταία αντιστοίχιση

Λάβετε το τελευταίο αποτέλεσμα που ταιριάζει με το XLOOKUP

Για να βρείτε το τελευταία τιμή αντιστοίχισης στο Excel, ορίστε το έκτο επιχείρημα στη συνάρτηση XLOOKUP για να αναζήτηση με αντίστροφη σειρά.

Από προεπιλογή, η λειτουργία αναζήτησης στο XLOOKUP έχει οριστεί σε 1, Η οποία είναι αναζήτηση από την πρώτη έως την τελευταία. Αλλά το καλό με XLOOKUP είναι αυτό η κατεύθυνση αναζήτησης μπορεί να αλλάξει. Το XLOOKUP προσφέρει το προαιρετικό όρισμα [τρόπος αναζήτησης] για τον έλεγχο της σειράς αναζήτησης. Απλώς ορίστε τη λειτουργία αναζήτησης στο έκτο όρισμα σε -1, η κατεύθυνση αναζήτησης αλλάζει σε αναζήτηση από το τελευταίο στο πρώτο.

Δείτε το παρακάτω παράδειγμα. Θέλουμε να μάθουμε την τελευταία πώληση της Emma στη βάση δεδομένων.

πληκτρολογήστε το τύπος παρακάτω στο κενό κελί G2 και, στη συνέχεια, πατήστε το εισάγετε κλειδί για να λάβετε το αποτέλεσμα.

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

Λειτουργία xlookup 39

√ Σημείωση: Το τέταρτο και το πέμπτο όρισμα είναι προαιρετικά και παραλείπονται σε αυτήν την περίπτωση. Ορίσαμε μόνο το προαιρετικό έκτο όρισμα σε -1.

Αναζητήστε εύκολα την τελευταία τιμή που ταιριάζει με ένα εκπληκτικό εργαλείο

Σε περίπτωση που δεν έχετε πρόσβαση στο XLOOKUP και επίσης δεν θέλετε να θυμάστε περίπλοκους τύπους, μπορείτε να εφαρμόσετε το Δυνατότητα αναζήτησης από κάτω προς τα πάνω of Kutools για Excel για να το πετύχεις εύκολα.

Με μας Πρόσθετο Excel εγκατεστημένο, κάντε τα εξής:

1. Πηγαίνετε να το Kutools καρτέλα στο Excel, βρείτε Σούπερ LOOKUP, και κάντε κλικ στο κουμπί Αναζήτηση από κάτω προς τα πάνω στην αναπτυσσόμενη λίστα.

Λειτουργία xlookup 40

2. Στο Πλαίσιο διαλόγου ΑΝΑΖΗΤΗΣΗ από κάτω προς τα επάνω, πρέπει να ρυθμίσετε τις παραμέτρους ως εξής:

  • Στο Ενότητα τιμών αναζήτησης και εύρους εξόδου, Επιλέξτε το εύρος αναζήτησης και εύρος εξόδου;
  • Στο Ενότητα εύρους δεδομένων, Επιλέξτε το φασμα ΔΕΔΟΜΕΝΩΝ, στη συνέχεια καθορίστε το στήλη κλειδιού και στήλη επιστροφής.

Λειτουργία xlookup 41

3. Κάντε κλικ στο κουμπί OK για να πάρετε το αποτέλεσμα.

Λειτουργία xlookup 42

√ Σημείωση: Η ενότητα Αντικατάσταση τιμής σφάλματος #N/A με μια καθορισμένη τιμή είναι προαιρετική στο πλαίσιο διαλόγου, μπορείτε να την καθορίσετε ή όχι.

Κάντε κλικ για λήψη του Kutools για Excel για δωρεάν δοκιμή 30 ημερών.


Λήψη δείγματος αρχείου XLOOKUP

XLOOKUP Examples.xlsx

Σχετικά άρθρα:


  • Super Formula Bar (επεξεργαστείτε εύκολα πολλές γραμμές κειμένου και τύπου). Διάταξη ανάγνωσης (εύκολη ανάγνωση και επεξεργασία μεγάλου αριθμού κελιών). Επικόλληση σε φιλτραρισμένο εύρος...
  • Συγχώνευση κελιών / σειρών / στηλών και τήρηση δεδομένων · Περιεχόμενο διαχωρισμού κελιών Συνδυάστε διπλές σειρές και άθροισμα / μέσος όρος... Αποτροπή διπλών κυττάρων; Συγκρίνετε τα εύρη...
  • Επιλέξτε Διπλότυπο ή Μοναδικό Σειρές; Επιλέξτε Κενές σειρές (όλα τα κελιά είναι κενά). Σούπερ εύρεση και ασαφής εύρεση σε πολλά βιβλία εργασίας. Τυχαία επιλογή ...
  • Ακριβές αντίγραφο Πολλαπλά κελιά χωρίς αλλαγή της αναφοράς τύπου. Αυτόματη δημιουργία αναφορών σε πολλαπλά φύλλα? Εισαγωγή κουκκίδων, Πλαίσια ελέγχου και άλλα ...
  • Αγαπημένα και γρήγορη εισαγωγή τύπων, Σειρά, Διαγράμματα και Εικόνες; Κρυπτογράφηση κυττάρων με κωδικό πρόσβασης Δημιουργία λίστας αλληλογραφίας και στείλτε email ...
  • Εξαγωγή κειμένου, Προσθήκη κειμένου, Κατάργηση κατά θέση, Αφαιρέστε το διάστημα; Δημιουργία και εκτύπωση υποσύνολων σελιδοποίησης. Μετατροπή περιεχομένου και σχολίων μεταξύ κελιών...
  • Σούπερ φίλτρο (αποθηκεύστε και εφαρμόστε σχήματα φίλτρων σε άλλα φύλλα). Προηγμένη ταξινόμηση ανά μήνα / εβδομάδα / ημέρα, συχνότητα και άλλα. Ειδικό φίλτρο με έντονη, πλάγια ...
  • Συνδυάστε βιβλία εργασίας και φύλλα εργασίας; Συγχώνευση πινάκων βάσει βασικών στηλών. Διαχωρίστε τα δεδομένα σε πολλά φύλλα; Μαζική μετατροπή xls, xlsx και PDF...
  • Ομαδοποίηση συγκεντρωτικού πίνακα κατά αριθμός εβδομάδας, ημέρα εβδομάδας και πολλά άλλα ... Εμφάνιση ξεκλειδωμένων, κλειδωμένων κελιών με διαφορετικά χρώματα. Επισημάνετε τα κελιά που έχουν τύπο / όνομα...
kte καρτέλα 201905
  • Ενεργοποίηση επεξεργασίας και ανάγνωσης καρτελών σε Word, Excel, PowerPoint, Publisher, Access, Visio και Project.
  • Ανοίξτε και δημιουργήστε πολλά έγγραφα σε νέες καρτέλες του ίδιου παραθύρου και όχι σε νέα παράθυρα.
  • Αυξάνει την παραγωγικότητά σας κατά 50% και μειώνει εκατοντάδες κλικ του ποντικιού για εσάς κάθε μέρα!
κάτω μέρος γραφείου
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations