Αυτό το θέμα περιγράφει τους πιο κοινούς λόγους της συνάρτησης VLOOKUP για ένα εσφαλμένο αποτέλεσμα και παρέχει προτάσεις για τη χρήση των συναρτήσεων INDEX και MATCH .
: Επίσης, ανατρέξτε στην Κάρτα γρήγορης αναφοράς: Συμβουλές αντιμετώπισης προβλημάτων VLOOKUP που παρουσιάζει τους συνήθεις λόγους για προβλήματα #NA σε ένα πρόχειρο αρχείο PDF. Μπορείτε να κοινοποιήσετε το PDF ή να το εκτυπώσετε για δική σας αναφορά.
Πρόβλημα: Η τιμή αναζήτησης δεν βρίσκεται στην πρώτη στήλη του ορίσματος συστοιχία_πίνακα
Ένας περιορισμός της συνάρτησης VLOOKUP είναι ότι μπορεί να αναζητήσει τιμές μόνο στην πιο αριστερή στήλη της συστοιχίας πίνακα. Εάν η τιμή αναζήτησής σας δεν βρίσκεται στην πρώτη στήλη της συστοιχίας, θα δείτε το σφάλμα #N/A.
Στον παρακάτω πίνακα, θέλουμε να ανακτήσουμε τον αριθμό των μονάδων που πωλήθηκαν για το κέιλ.
Το σφάλμα #N/A προκύπτει επειδή η τιμή αναζήτησης "κέιλ" εμφανίζεται στη δεύτερη στήλη (Παραγωγή) του ορίσματος συστοιχία_πίνακα A2:C10. Σε αυτή την περίπτωση, το Excel το αναζητά στη στήλη A και όχι στη στήλη B.
Λύση: Μπορείτε να προσπαθήσετε να διορθώσετε αυτό το πρόβλημα προσαρμόζοντας τη συνάρτηση VLOOKUP σας ώστε να έχει ως πηγή αναφοράς τη σωστή στήλη. Εάν αυτό δεν είναι δυνατό, δοκιμάστε να μετακινήσετε τις στήλες σας. Αυτό μπορεί επίσης να είναι εξαιρετικά ανέφικτο, εάν έχετε μεγάλα ή σύνθετα υπολογιστικά φύλλα όπου οι τιμές κελιών εκφράζουν αποτελέσματα άλλων υπολογισμών ή ίσως υπάρχουν άλλοι λογικοί λόγοι για τους οποίους απλώς δεν μπορείτε να μετακινήσετε τις στήλες. Η λύση είναι να χρησιμοποιήσετε ένα συνδυασμό συναρτήσεων INDEX και MATCH, οι οποίες μπορούν να αναζητήσουν μια τιμή σε μια στήλη ανεξάρτητα από τη θέση της στον πίνακα αναζήτησης. Δείτε την επόμενη ενότητα.
Αντ' αυτής, εξετάστε το ενδεχόμενο να χρησιμοποιήσετε τον συνδυασμό συναρτήσεων INDEX/MATCH
Οι συναρτήσεις INDEX και MATCH είναι καλές για πολλές περιπτώσεις στις οποίες η συνάρτηση VLOOKUP δεν ικανοποιεί τις ανάγκες σας. Το βασικό πλεονέκτημα του συνδυασμού συναρτήσεων INDEX/MATCH είναι ότι μπορείτε να αναζητήσετε μια τιμή σε μια στήλη σε οποιαδήποτε θέση στον πίνακα αναζήτησης. Η συνάρτηση INDEX επιστρέφει μια τιμή από έναν καθορισμένο πίνακα/περιοχή, σύμφωνα με τη θέση της. Η συνάρτηση MATCH επιστρέφει τη σχετική θέση μιας τιμής σε έναν πίνακα/ μια περιοχή. Χρησιμοποιήστε τις συναρτήσεις INDEX και MATCH μαζί σε έναν τύπο για αναζήτηση μιας τιμής σε έναν πίνακα/συστοιχία, καθορίζοντας τη σχετική θέση της τιμής στον πίνακα/συστοιχία.
Υπάρχουν πολλά πλεονεκτήματα από τη χρήση του συνδυασμού συναρτήσεων INDEX/MATCH αντί για τη συνάρτηση VLOOKUP:
-
Με τις συναρτήσεις INDEX και MATCH, η τιμή επιστροφής δεν χρειάζεται να βρίσκεται στην ίδια στήλη με τη στήλη αναζήτησης. Αυτό είναι διαφορετικό από τη συνάρτηση VLOOKUP, στην οποία η τιμή επιστροφής πρέπει να βρίσκεται στην καθορισμένη περιοχή. Τι σημασία έχει αυτό; Με τη συνάρτηση VLOOKUP, πρέπει να γνωρίζετε τον αριθμό στήλης που περιέχει την τιμή επιστροφής. Παρόλο που αυτό μπορεί να μην φαίνεται δύσκολο, μπορεί να είναι πολύπλοκο όταν έχετε έναν μεγάλο πίνακα και πρέπει να μετρήσετε τον αριθμό των στηλών. Επίσης, εάν προσθέσετε/καταργήσετε μια στήλη στον πίνακά σας, πρέπει να μετρήσετε ξανά και να ενημερώσετε το όρισμα col_index_num. Με τις συναρτήσεις INDEX και MATCH, δεν απαιτείται καταμέτρηση, καθώς η στήλη LOOKUP είναι διαφορετική από τη στήλη που έχει την τιμή επιστροφής.
-
Με τις συναρτήσεις INDEX και MATCH, μπορείτε να καθορίσετε μια γραμμή ή μια στήλη σε μια συστοιχία ή να καθορίσετε και τα δύο. Αυτό σημαίνει ότι μπορείτε να αναζητήσετε τιμές κατακόρυφα και οριζόντια.
-
Οι συναρτήσεις INDEX και MATCH μπορούν να χρησιμοποιηθούν για την αναζήτηση τιμών σε οποιαδήποτε στήλη. Σε αντίθεση με τη συνάρτηση VLOOKUP, στην οποία μπορείτε να αναζητήσετε μια τιμή μόνο στην πρώτη στήλη ενός πίνακα, οι συναρτήσεις INDEX και MATCH είναι κατάλληλες εάν η τιμή αναζήτησης βρίσκεται στην πρώτη στήλη, την τελευταία ή οπουδήποτε ενδιάμεσα.
-
Οι συναρτήσεις INDEX και MATCH σας παρέχουν την ευελιξία να κάνετε δυναμική αναφορά στη στήλη που περιέχει την τιμή επιστροφής. Αυτό σημαίνει ότι μπορείτε να προσθέσετε στήλες στον πίνακά σας χωρίς να διακόψετε τις συναρτήσεις INDEX και MATCH. Από την άλλη, η συνάρτηση VLOOKUP διακόπτεται εάν θέλετε να προσθέσετε μια στήλη στον πίνακα, καθώς κάνει μια στατική αναφορά στον πίνακα.
-
Οι συναρτήσεις INDEX και MATCH προσφέρουν μεγαλύτερη ευελιξία με τις αντιστοιχίες. Οι συναρτήσεις INDEX και MATCH μπορούν να βρουν μια ακριβή αντιστοιχία ή μια τιμή που είναι μεγαλύτερη ή μικρότερη από την τιμή αναζήτησης. Η συνάρτηση VLOOKUP θα αναζητήσει μόνο την πλησιέστερη αντιστοιχία σε μια τιμή (από προεπιλογή) ή μια ακριβή τιμή. Η συνάρτηση VLOOKUP υποθέτει επίσης από προεπιλογή ότι η πρώτη στήλη στη συστοιχία πίνακα ταξινομείται αλφαβητικά και, ας υποθέσουμε ότι ο πίνακάς σας δεν έχει ρυθμιστεί με αυτόν τον τρόπο, η συνάρτηση VLOOKUP θα επιστρέψει την πρώτη πλησιέστερη αντιστοιχία στον πίνακα, η οποία μπορεί να μην εκφράζει τα δεδομένα που αναζητάτε.
Σύνταξη
Για να δομήσετε σύνταξη για τον συνδυασμό συναρτήσεων INDEX/MATCH, πρέπει να χρησιμοποιήσετε το όρισμα συστοιχία/αναφορά από τη συνάρτηση INDEX και να ενσωματώσετε τη σύνταξη MATCH μέσα σε αυτήν. Αυτή η μορφή είναι η εξής:
=INDEX(πίνακας ή αναφορά, MATCH(τιμή_αναζήτησης,πίνακας_αναζήτησης,[τύπος_αντιστοιχίας])
Ας χρησιμοποιήσουμε τον συνδυασμό συναρτήσεων INDEX/MATCH για να αντικαταστήσουμε τη συνάρτηση VLOOKUP από το παραπάνω παράδειγμα. Η σύνταξη θα μοιάζει κάπως έτσι:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
Στα απλά Αγγλικά σημαίνει:
=INDEX(επιστρέφει μια τιμή από τα κελιά C2:C10, η οποία θα (αντιστοιχιστεί) MATCH(Κέιλ, που βρίσκεται κάπου στον πίνακα B2:B10, όπου η τιμή επιστροφής είναι η πρώτη τιμή που αντιστοιχεί στο κέιλ))
Ο τύπος αναζητά την πρώτη τιμή στο C2:C10 που αντιστοιχεί στο Κέιλ (στο B7) και επιστρέφει την τιμή στο κελί C7 (100), η οποία είναι η πρώτη τιμή που συμφωνεί με το κέιλ.
Πρόβλημα: Η ακριβής αντιστοιχία δεν βρέθηκε
Όταν το όρισμα αναζήτηση_περιοχής είναι FALSE και η συνάρτηση VLOOKUP δεν μπορεί να βρει ακριβή αντιστοιχία στα δεδομένα σας, επιστρέφει το σφάλμα #N/A.
Λύση: Εάν είστε βέβαιος ότι τα σχετικά δεδομένα υπάρχουν στο υπολογιστικό φύλλο σας και η συνάρτηση VLOOKUP δεν τα εντοπίζει, αφιερώστε χρόνο για να επαληθεύσετε ότι τα αναφερόμενα κελιά δεν έχουν κρυφά κενά διαστήματα ή μη εκτυπώσιμους χαρακτήρες. Επίσης, βεβαιωθείτε ότι τα κελιά ακολουθούν τον σωστό τύπο δεδομένων. Για παράδειγμα, τα κελιά με αριθμούς πρέπει να μορφοποιηθούν ως Αριθμός και όχι ως Κείμενο.
Επίσης, εξετάστε το ενδεχόμενο να χρησιμοποιήσετε τη συνάρτηση CLEAN ή την TRIM για να εκκαθαρίσετε τα δεδομένα από κελιά.
Πρόβλημα: Η τιμή αναζήτησης είναι μικρότερη από τη μικρότερη τιμή στον πίνακα
Εάν το όρισμα αναζήτηση_περιοχής έχει οριστεί σε TRUE και η τιμή αναζήτησης είναι μικρότερη από τη μικρότερη τιμή στη διάταξη, θα δείτε το σφάλμα #N/A. Η συνάρτηση TRUE αναζητά μια κατά προσέγγιση αντιστοιχία στη συστοιχία και επιστρέφει την πλησιέστερη τιμή που είναι μικρότερη από την τιμή αναζήτησης.
Στο παρακάτω παράδειγμα, η τιμή αναζήτησης είναι 100, αλλά δεν υπάρχουν τιμές στην περιοχή B2:C10 μικρότερες από 100, εξ ου και το σφάλμα.
Λύση:
-
Διορθώστε την τιμή αναζήτησης όπως απαιτείται.
-
Εάν δεν μπορείτε να αλλάξετε την τιμή αναζήτησης και χρειάζεστε μεγαλύτερη ευελιξία με τις αντιστοιχίες τιμών, εξετάστε το ενδεχόμενο να χρησιμοποιήσετε τον συνδυασμό συναρτήσεων INDEX/MATCH αντί για τη συνάρτηση VLOOKUP. Ανατρέξτε στην παραπάνω ενότητα σε αυτό το άρθρο. Με τον συνδυασμό συναρτήσεων INDEX/MATCH, μπορείτε να αναζητήσετε τιμές μεγαλύτερες από, μικρότερες ή ίσες με την τιμή αναζήτησης. Για περισσότερες πληροφορίες σχετικά με τη χρήση του συνδυασμού συναρτήσεων INDEX/MATCH αντί της συνάρτησης VLOOKUP, ανατρέξτε στην προηγούμενη ενότητα σε αυτό το θέμα.
Πρόβλημα: Η στήλη αναζήτησης δεν είναι ταξινομημένη με αύξουσα σειρά
Εάν το όρισμα αναζήτηση_περιοχής έχει οριστεί σε TRUE και μία από τις στήλες αναζήτησης δεν έχει ταξινομηθεί με αύξουσα σειρά (A-Z), θα δείτε το σφάλμα #N/A.
Λύση:
-
Αλλάξτε τη συνάρτηση VLOOKUP για να αναζητήσετε μια ακριβή αντιστοιχία. Για να το κάνετε αυτό, ορίστε το όρισμα αναζήτηση_περιοχής σε FALSE. Δεν απαιτείται ταξινόμηση για FALSE.
-
Χρησιμοποιήστε τον συνδυασμό συναρτήσεων INDEX/MATCH για να αναζητήσετε μια τιμή σε έναν μη ταξινομημένο πίνακα.
Πρόβλημα: Η τιμή είναι ένας μεγάλος αριθμός κινητής υποδιαστολής
Εάν έχετε τιμές χρόνου ή μεγάλους δεκαδικούς αριθμούς σε κελιά, το Excel επιστρέφει το σφάλμα #N/A λόγω ακρίβειας κινητής υποδιαστολής. Οι αριθμοί κινητής υποδιαστολής είναι αριθμοί που ακολουθούν μετά από μια υποδιαστολή. (Το Excel αποθηκεύει τιμές χρόνου ως αριθμούς κινητής υποδιαστολής.) Το Excel δεν μπορεί να αποθηκεύσει αριθμούς με πολύ μεγάλους αριθμούς κινητής υποδιαστολής, επομένως, για να λειτουργήσει σωστά η συνάρτηση, οι αριθμοί κινητής υποδιαστολής θα πρέπει να στρογγυλοποιηθούν σε 5 δεκαδικά ψηφία.
Λύση: Μικρύνετε τους αριθμούς στρογγυλοποιώντας τους έως πέντε δεκαδικά ψηφία με τη συνάρτηση ROUND.
Χρειάζεστε περισσότερη βοήθεια;
Μπορείτε ανά πάσα στιγμή να ρωτήσετε έναν ειδικό στην Κοινότητα τεχνικής υποστήριξης του Excel ή να λάβετε υποστήριξη στις Κοινότητες.
