Η λειτουργία XLOOKUP είναι αποκλειστική για το πρόγραμμα insider του office 365. Η συνάρτηση LOOKUP έχει πολλές λειτουργίες που ξεπερνούν πολλές από τις αδυναμίες της λειτουργίας VLOOKUP και HLOOKUP αλλά δυστυχώς δεν είναι διαθέσιμη προς το παρόν. Αλλά μην ανησυχείτε, μπορούμε να δημιουργήσουμε μια συνάρτηση XLOOKUP που λειτουργεί ακριβώς το ίδιο με την επερχόμενη συνάρτηση XLOOKUP MS Excel. Θα προσθέσουμε λειτουργίες σε αυτό ένα προς ένα.
Κωδικός VBA της λειτουργίας XLOOKUP
Η παρακάτω λειτουργία αναζήτησης UDF θα λύσει πολλά προβλήματα. Αντιγράψτε το ή κατεβάστε το πρόσθετο xl στο παρακάτω αρχείο παρακάτω.
Λειτουργία XLOOKUP (lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) End Function
Εξήγηση:
Ο παραπάνω κώδικας είναι απλώς βασικός δείκτης INDEX-MATCH που χρησιμοποιείται στο VBA. Αυτό απλοποιεί πολλά από τα πράγματα που αντιμετωπίζει ένας νέος χρήστης. Αν επιλύσει την πολυπλοκότητα της συνάρτησης INDEX-MATCH και χρησιμοποιεί μόνο τρία ορίσματα. Μπορείτε να το αντιγράψετε στο αρχείο excel ή να κατεβάσετε το αρχείο .xlam παρακάτω και να το εγκαταστήσετε ως πρόσθετο Excel. Εάν δεν ξέρετε πώς να δημιουργήσετε και να χρησιμοποιήσετε ένα πρόσθετο, κάντε κλικ εδώ, θα σας βοηθήσει.
Πρόσθετο XLOOKUP
ας δούμε πώς λειτουργεί στο φύλλο εργασίας excel.
Σύνταξη του XLOOKUP
| = XLOOKUP (τιμή_αναζήτησης, αναζήτηση_αρρέας, αποτέλεσμα_συσκευής) |
lookup_value: Αυτή είναι η τιμή που θέλετε να αναζητήσετε στο lookup_array.
lookup_array: Είναι ένα μονοδιάστατο εύρος στο οποίο θέλετε να κάνετε αναζήτηση αναζήτηση_αξίας.
result_array: Είναι επίσης ένα μονοδιάστατο εύρος. Αυτό είναι το εύρος από το οποίο θέλετε να ανακτήσετε την τιμή.
Ας δούμε αυτήν τη λειτουργία XLOOKUP σε δράση.
Παραδείγματα XLOOKUP:
Εδώ, έχω έναν πίνακα δεδομένων στο excel. Ας εξερευνήσουμε κάποια λειτουργικότητα χρησιμοποιώντας αυτόν τον πίνακα δεδομένων.
Λειτουργικότητα 1. Ακριβής Αναζήτηση στην αριστερή και δεξιά πλευρά της τιμής αναζήτησης.
Όπως γνωρίζουμε ότι η συνάρτηση Excel VLOOKUP δεν μπορεί να ανακτήσει τιμές από τα αριστερά της τιμής αναζήτησης. Για αυτό, πρέπει να χρησιμοποιήσετε τον σύνθετο συνδυασμό INDEX-MATCH. Αλλά όχι πια.
Υποθέτοντας ότι πρέπει να ανακτήσουμε όλες τις διαθέσιμες πληροφορίες στον πίνακα ορισμένων αριθμών ρολών. Σε αυτήν την περίπτωση, θα πρέπει επίσης να ανακτήσετε την περιοχή, η οποία βρίσκεται στα αριστερά της στήλης αριθμού ρολού.
Γράψτε αυτόν τον τύπο, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Λαμβάνουμε το αποτέλεσμα Βόρεια για τον αριθμό ρολού 112. Αντιγράψτε ή σύρετε προς τα κάτω τον τύπο στα παρακάτω κελιά για να τα συμπληρώσετε με τις αντίστοιχες περιοχές.
Πώς λειτουργεί;
Ο μηχανισμός είναι απλός. Αυτή η συνάρτηση αναζητά το αναζήτηση_τιμής σε lookup_array και επιστρέφει το ευρετήριο μιας πρώτης ακριβούς αντιστοίχισης. Στη συνέχεια, χρησιμοποιεί αυτό το ευρετήριο για να ανακτήσει την τιμή από result_arrayΤο Αυτή η λειτουργία λειτουργεί τέλεια με τα ονόματα εύρους.
Ομοίως, χρησιμοποιήστε αυτόν τον τύπο για να ανακτήσετε την τιμή από κάθε στήλη.
Λειτουργικότητα 2. Ακριβής Οριζόντιος Αναζητήστε πάνω και κάτω την τιμή αναζήτησης.
Το XLOOKUP λειτουργεί επίσης ως ακριβής συνάρτηση HLOOKUP. Η συνάρτηση HLOOKUP έχει τον ίδιο περιορισμό με το VLOOKUP. Δεν είναι δυνατή η λήψη τιμής από πάνω από την τιμή αναζήτησης. Αλλά το XLOOKUP όχι μόνο λειτουργεί ως HLOOKUP, αλλά ξεπερνά και αυτή την αδυναμία. Ας δούμε πώς.
Υποθετικά, αν θέλετε να συγκρίνετε δύο δίσκους. Η εγγραφή αναζήτησης που έχετε ήδη. Η εγγραφή με την οποία θέλετε να συγκρίνετε είναι πάνω από το εύρος αναζήτησης. Χρησιμοποιήστε αυτόν τον τύπο σε αυτήν την περίπτωση.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
σύρετε προς τα κάτω τον τύπο και έχετε ολόκληρη την εγγραφή σύγκρισης γραμμής.
Λειτουργικότητα 3. Δεν χρειάζεται αριθμός στήλης και προεπιλεγμένη ακριβής αντιστοίχιση.
Όταν χρησιμοποιείτε τη λειτουργία VLOOKUP, πρέπει να πείτε τον αριθμό στήλης από την οποία θέλετε να λάβετε τις τιμές. Για αυτό, πρέπει να μετρήσετε τις στήλες ή να χρησιμοποιήσετε μερικά κόλπα, να λάβετε τη βοήθεια άλλων λειτουργιών. Με αυτό το UDF XLOOKUP, δεν χρειάζεται να το κάνετε αυτό.
Εάν χρησιμοποιείτε το VLOOKUP μόνο για να λάβετε κάποια τιμή από μία στήλη ή για να ελέγξετε εάν υπάρχει μια τιμή στη στήλη, αυτή είναι η καλύτερη λύση σύμφωνα με εμένα.
Λειτουργικότητα 4. Αντικαθιστά τη λειτουργία INDEX-MATCH, VLOOKUP, HLOOKUP
Για απλές εργασίες, η λειτουργία XLOOKUP αντικαθιστά τις παραπάνω λειτουργίες.
Περιορισμοί του XLOOKUP:
Όταν πρόκειται για σύνθετους τύπους, όπως το VLOOKUP με το Dynamic Col Index, όπου το VLOOKUP προσδιορίζει τη στήλη αναζήτησης με κεφαλίδες, αυτό το XLOOKUP θα αποτύχει.
Ένας άλλος περιορισμός είναι ότι εάν πρέπει να αναζητήσετε πολλές τυχαίες στήλες ή σειρές από τον πίνακα, αυτή η συνάρτηση θα είναι άχρηστη καθώς πρέπει να γράφετε αυτόν τον τύπο ξανά και ξανά. Αυτό μπορεί να ξεπεραστεί χρησιμοποιώντας ονόματα εύρους.
Προς το παρόν, δεν έχουμε προσθέσει την κατά προσέγγιση λειτουργικότητα, οπότε φυσικά, δεν μπορείτε να λάβετε την κατά προσέγγιση αντιστοίχιση. Θα το προσθέσουμε πολύ σύντομα.
Εάν η συνάρτηση XLOOKUP αποτύχει να βρει την τιμή αναζήτησης, θα επιστρέψει σφάλμα #VALUE όχι #N/A.
Λοιπόν, ναι, έτσι χρησιμοποιείτε το XLOOKUP για να ανακτήσετε, να αναζητήσετε και να επικυρώσετε τιμές σε πίνακες excel. Μπορείτε να χρησιμοποιήσετε αυτήν τη συνάρτηση που ορίζεται από το χρήστη για αναζήτηση χωρίς προβλήματα στα αριστερά ή πάνω από την τιμή αναζήτησης. Εάν εξακολουθείτε να έχετε οποιαδήποτε αμφιβολία ή κάποια συγκεκριμένη απαίτηση που σχετίζεται με αυτήν τη λειτουργία ή το ερώτημα που σχετίζεται με το EXCEL 2010/2013/2016/2019/365 ή το VBA, ρωτήστε το στην παρακάτω ενότητα σχολίων. Σίγουρα θα λάβετε απάντηση.
Δημιουργία συνάρτησης VBA για επιστροφή πίνακα | Για να επιστρέψουμε έναν πίνακα από τη συνάρτηση που ορίζεται από τον χρήστη, πρέπει να τον δηλώσουμε όταν ονομάσουμε το UDF.
Arrays in Excel Formul | Μάθετε τι είναι οι πίνακες στο excel.
Πώς να δημιουργήσετε μια συνάρτηση καθορισμένη από το χρήστη μέσω VBA | Μάθετε πώς μπορείτε να δημιουργήσετε συναρτήσεις που καθορίζονται από το χρήστη στο Excel
Χρήση συνάρτησης καθορισμένης από το χρήστη (UDF) από άλλο βιβλίο εργασίας χρησιμοποιώντας VBA στο Microsoft Excel | Χρησιμοποιήστε τη συνάρτηση που ορίζεται από το χρήστη σε άλλο βιβλίο εργασίας του Excel
Επιστρέψτε τιμές σφάλματος από συναρτήσεις που ορίζονται από τον χρήστη χρησιμοποιώντας VBA στο Microsoft Excel | Μάθετε πώς μπορείτε να επιστρέψετε τιμές σφάλματος από μια συνάρτηση που ορίζεται από το χρήστη
Δημοφιλή άρθρα:
Διαχωρίστε το φύλλο Excel σε πολλά αρχεία με βάση τη στήλη χρησιμοποιώντας VBA | Αυτός ο κώδικας VBA διαχωρίζει τη βάση φύλλου excel σε μοναδικές τιμές σε μια καθορισμένη στήλη. Κατεβάστε το αρχείο εργασίας.
Απενεργοποιήστε τα μηνύματα προειδοποίησης χρησιμοποιώντας το VBA στο Microsoft Excel 2016 | Για να απενεργοποιήσουμε τα προειδοποιητικά μηνύματα που διακόπτουν τον τρέχοντα κώδικα VBA, χρησιμοποιούμε την κλάση Εφαρμογή.
Προσθήκη και αποθήκευση νέου βιβλίου εργασίας χρησιμοποιώντας το VBA στο Microsoft Excel 2016 | Για να προσθέσετε και να αποθηκεύσετε βιβλία εργασίας χρησιμοποιώντας VBA, χρησιμοποιούμε την τάξη βιβλίων εργασίας. Τετράδια εργασίας. Προσθέστε εύκολα νέο βιβλίο εργασίας, ωστόσο…