Περιεχόμενα
- Διόρθωση σφάλματος #N/A στη συνάρτηση VLOOKUP στο Excel
- 1. Η επιθυμητή τιμή είναι ανορθόγραφη
- 2. #N/A σφάλμα κατά την αναζήτηση για μια κατά προσέγγιση αντιστοίχιση με το VLOOKUP
- 3. #N/A σφάλμα κατά την αναζήτηση ακριβούς αντιστοίχισης με το VLOOKUP
- 4. Η στήλη αναζήτησης δεν είναι το αριστερό
- 5. Οι αριθμοί μορφοποιούνται ως κείμενο
- 6. Υπάρχει ένα κενό στην αρχή ή στο τέλος
- Σφάλμα #VALUE! σε τύπους με VLOOKUP
- Σφάλμα #NAME; σε VLOOKUP
- Το VLOOKUP δεν λειτουργεί (περιορισμοί, προειδοποιήσεις και αποφάσεις)
- VLOOKUP – εργασία με τις συναρτήσεις IFERROR και ISERROR
Αυτό το μάθημα εξηγεί πώς να αντιμετωπίσετε γρήγορα μια κατάσταση όπου μια συνάρτηση VPR (VLOOKUP) δεν θέλει να λειτουργήσει στο Excel 2013, 2010, 2007 και 2003 και πώς να εντοπίσει και να διορθώσει κοινά σφάλματα και να ξεπεράσει τους περιορισμούς VPR.
Σε πολλά προηγούμενα άρθρα, έχουμε εξερευνήσει τις διάφορες πτυχές της συνάρτησης VPR στο Excel. Εάν τα έχετε διαβάσει προσεκτικά, θα πρέπει τώρα να είστε ειδικός σε αυτόν τον τομέα. Ωστόσο, δεν είναι άδικο που πιστεύουν πολλοί ειδικοί του Excel VPR ένα από τα πιο σύνθετα χαρακτηριστικά. Έχει ένα σωρό περιορισμούς και χαρακτηριστικά που γίνονται η πηγή πολλών προβλημάτων και σφαλμάτων.
Σε αυτό το άρθρο θα βρείτε απλές εξηγήσεις για τα σφάλματα #ΑΤ (# N/A), # ΟΝΟΜΑ? (#NAME;) και #ΑΞΙΑ! (#VALUE!) που εμφανίζονται κατά την εργασία με τη συνάρτηση VPR, καθώς και τεχνικές και μεθόδους αντιμετώπισής τους. Θα ξεκινήσουμε με τις πιο συνηθισμένες περιπτώσεις και τους πιο προφανείς λόγους. VPR δεν λειτουργεί, επομένως είναι καλύτερο να μελετήσετε τα παραδείγματα με τη σειρά που δίνονται στο άρθρο.
Διόρθωση σφάλματος #N/A στη συνάρτηση VLOOKUP στο Excel
Σε φόρμουλες με VPR μήνυμα σφάλματος #ΑΤ (# N/A) σημαίνει δεν είναι διαθέσιμο (χωρίς δεδομένα) – εμφανίζεται όταν το Excel δεν μπορεί να βρει την τιμή που αναζητάτε. Αυτό μπορεί να συμβεί για διάφορους λόγους.
1. Η επιθυμητή τιμή είναι ανορθόγραφη
Καλή ιδέα είναι να ελέγξετε πρώτα αυτό το στοιχείο! Συχνά συμβαίνουν τυπογραφικά λάθη όταν εργάζεστε με πολύ μεγάλες ποσότητες δεδομένων, που αποτελούνται από χιλιάδες γραμμές, ή όταν η τιμή που αναζητάτε γράφεται σε έναν τύπο.
2. #N/A σφάλμα κατά την αναζήτηση για μια κατά προσέγγιση αντιστοίχιση με το VLOOKUP
Εάν χρησιμοποιείτε έναν τύπο με μια κατά προσέγγιση συνθήκη αναζήτησης αντιστοίχισης, π.χ εύρος_αναζήτησης (range_lookup) είναι TRUE ή δεν καθορίζεται, ο τύπος σας μπορεί να αναφέρει ένα σφάλμα # N / A σε δύο περιπτώσεις:
- Η τιμή προς αναζήτηση είναι μικρότερη από τη μικρότερη τιμή στον πίνακα που αναζητείται.
- Η στήλη αναζήτησης δεν ταξινομείται με αύξουσα σειρά.
3. #N/A σφάλμα κατά την αναζήτηση ακριβούς αντιστοίχισης με το VLOOKUP
Αν ψάχνετε για ακριβή αντιστοίχιση, δηλ. επιχείρημα εύρος_αναζήτησης (range_lookup) είναι FALSE και η ακριβής τιμή δεν βρέθηκε, ο τύπος θα αναφέρει επίσης ένα σφάλμα # N / A. Μάθετε περισσότερα σχετικά με τον τρόπο αναζήτησης ακριβών και κατά προσέγγιση αντιστοιχίσεων με μια συνάρτηση VPR.
4. Η στήλη αναζήτησης δεν είναι το αριστερό
Όπως ίσως γνωρίζετε, ένας από τους πιο σημαντικούς περιορισμούς VPR είναι ότι δεν μπορεί να είναι στραμμένη προς τα αριστερά, επομένως η στήλη αναζήτησης στον πίνακά σας πρέπει να είναι στην αριστερή πλευρά. Στην πράξη, συχνά το ξεχνάμε αυτό, το οποίο οδηγεί σε μια φόρμουλα που δεν λειτουργεί και σε σφάλμα. # N / A.
Απόφαση: Εάν δεν είναι δυνατή η αλλαγή της δομής δεδομένων έτσι ώστε η στήλη αναζήτησης να είναι στην αριστερή πλευρά, μπορείτε να χρησιμοποιήσετε έναν συνδυασμό συναρτήσεων ΔΕΊΚΤΗΣ (ΕΥΡΕΤΗΣ) και ΠΙΟ ΕΚΤΙΘΕΤΑ (MATCH) ως πιο ευέλικτη εναλλακτική για VPR.
5. Οι αριθμοί μορφοποιούνται ως κείμενο
Μια άλλη πηγή λάθους # N / A σε φόρμουλες με VPR είναι αριθμοί σε μορφή κειμένου στον κύριο πίνακα ή στον πίνακα αναζήτησης.
Αυτό συμβαίνει συνήθως όταν εισάγετε πληροφορίες από εξωτερικές βάσεις δεδομένων ή όταν πληκτρολογείτε μια απόστροφο πριν από έναν αριθμό για να διατηρήσετε το μηδέν.
Τα πιο εμφανή σημάδια ενός αριθμού σε μορφή κειμένου φαίνονται στο παρακάτω σχήμα:
Επιπλέον, οι αριθμοί μπορούν να αποθηκευτούν στη μορφή General (Γενικός). Σε αυτήν την περίπτωση, υπάρχει μόνο ένα αξιοσημείωτο χαρακτηριστικό – οι αριθμοί είναι στοιχισμένοι στο αριστερό άκρο του κελιού, ενώ από προεπιλογή ευθυγραμμίζονται στη δεξιά άκρη.
Απόφαση: Εάν είναι μία τιμή, απλώς κάντε κλικ στο εικονίδιο σφάλματος και επιλέξτε Μετατροπή σε αριθμό (Μετατροπή σε Αριθμός) από το μενού περιβάλλοντος.
Εάν αυτή είναι η κατάσταση με πολλούς αριθμούς, επιλέξτε τους και κάντε δεξί κλικ στην επιλεγμένη περιοχή. Στο μενού περιβάλλοντος που εμφανίζεται, επιλέξτε κύτταρα μορφή (Μορφοποίηση κελιών) > καρτέλα αριθμός (Αριθμός) > μορφή αριθμός (Αριθμητικό) και πατήστε OK.
6. Υπάρχει ένα κενό στην αρχή ή στο τέλος
Αυτός είναι ο λιγότερο προφανής λόγος για το σφάλμα. # N / A σε λειτουργία VPR, καθώς είναι οπτικά δύσκολο να δεις αυτούς τους επιπλέον χώρους, ειδικά όταν εργάζεσαι με μεγάλα τραπέζια, όταν τα περισσότερα δεδομένα είναι εκτός οθόνης.
Λύση 1: Επιπλέον κενά στον κύριο πίνακα (όπου βρίσκεται η συνάρτηση VLOOKUP)
Εάν εμφανίζονται επιπλέον κενά στον κύριο πίνακα, μπορείτε να βεβαιωθείτε ότι οι τύποι λειτουργούν σωστά εσωκλείοντας το όρισμα lookup_value (lookup_value) σε μια συνάρτηση TRIM (ΤΑΚΤΟΠΟΙΗΣΗ):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Λύση 2: Επιπλέον κενά στον πίνακα αναζήτησης (στη στήλη αναζήτησης)
Εάν υπάρχουν επιπλέον κενά στη στήλη αναζήτησης - απλοί τρόποι # N / A στον τύπο με VPR δεν μπορεί να αποφευχθεί. Αντί VPR Μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα με συνδυασμό συναρτήσεων ΔΕΊΚΤΗΣ (ΔΕΙΚΤΗΣ), ΠΙΟ ΕΚΤΙΘΕΤΑ (ΑΓΩΝΑΣ) και TRIM (ΤΑΚΤΟΠΟΙΗΣΗ):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Επειδή πρόκειται για τύπο πίνακα, μην ξεχάσετε να πατήσετε Ctrl + Shift + Εισαγωγή αντί για τα συνηθισμένα εισάγετεγια να εισάγετε σωστά τον τύπο.
Σφάλμα #VALUE! σε τύπους με VLOOKUP
Στις περισσότερες περιπτώσεις, το Microsoft Excel αναφέρει ένα σφάλμα #ΑΞΙΑ! (#VALUE!) όταν η τιμή που χρησιμοποιείται στον τύπο δεν ταιριάζει με τον τύπο δεδομένων. Σχετικά με VPR, τότε συνήθως υπάρχουν δύο λόγοι για το σφάλμα #ΑΞΙΑ!.
1. Η τιμή που αναζητάτε είναι μεγαλύτερη από 255 χαρακτήρες
Προσοχή: λειτουργία VPR δεν μπορεί να αναζητήσει τιμές που περιέχουν περισσότερους από 255 χαρακτήρες. Εάν η τιμή που αναζητάτε υπερβαίνει αυτό το όριο, θα λάβετε ένα μήνυμα σφάλματος. #ΑΞΙΑ!.
Απόφαση: Χρησιμοποιήστε μια δέσμη λειτουργιών ΕΥΡΕΤΗΡΙΑ+ΑΝΤΙΣΤΟΙΧΙΑ (ΕΥΡΕΤΗΣ + ΑΓΩΝΑΣ). Παρακάτω είναι ένας τύπος που θα κάνει μια χαρά για αυτήν την εργασία:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Η πλήρης διαδρομή προς το βιβλίο εργασίας αναζήτησης δεν έχει καθοριστεί
Εάν ανακτάτε δεδομένα από άλλο βιβλίο εργασίας, πρέπει να καθορίσετε την πλήρη διαδρομή προς αυτό το αρχείο. Πιο συγκεκριμένα, πρέπει να συμπεριλάβετε το όνομα του βιβλίου εργασίας (συμπεριλαμβανομένης της επέκτασης) σε αγκύλες [ ], ακολουθούμενο από το όνομα του φύλλου, ακολουθούμενο από ένα θαυμαστικό. Όλη αυτή η κατασκευή πρέπει να περικλείεται σε απόστροφους, σε περίπτωση που το όνομα του βιβλίου ή του φύλλου περιέχει κενά.
Εδώ είναι η πλήρης δομή της συνάρτησης VPR για αναζήτηση σε άλλο βιβλίο:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Ο πραγματικός τύπος μπορεί να μοιάζει με αυτό:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Αυτός ο τύπος θα αναζητήσει την τιμή του κελιού A2 σε μια στήλη B στο φύλλο Sheet1 στο βιβλίο εργασίας Νέες Τιμές και εξάγετε την αντίστοιχη τιμή από τη στήλη D.
Εάν παραληφθεί οποιοδήποτε μέρος της διαδρομής του πίνακα, η συνάρτησή σας VPR δεν θα λειτουργήσει και θα αναφέρει ένα σφάλμα #ΑΞΙΑ! (ακόμα κι αν το βιβλίο εργασίας με τον πίνακα αναζήτησης είναι ανοιχτό).
Για περισσότερες πληροφορίες σχετικά με τη λειτουργία VPRΑναφορικά με άλλο αρχείο Excel, δείτε το μάθημα: Αναζήτηση άλλου βιβλίου εργασίας χρησιμοποιώντας το VLOOKUP.
3. Το όρισμα Column_num είναι μικρότερο από 1
Είναι δύσκολο να φανταστεί κανείς μια κατάσταση όπου κάποιος εισάγει μια τιμή μικρότερη από 1για να υποδείξετε τη στήλη από την οποία θα εξαχθεί η τιμή. Αν και είναι δυνατό εάν η τιμή αυτού του ορίσματος υπολογίζεται από μια άλλη συνάρτηση Excel που είναι ένθετη μέσα VPR.
Έτσι, αν συμβεί ότι το επιχείρημα col_index_num (αριθμός_στήλης) λιγότερο από 1λειτουργία VPR θα αναφέρει επίσης ένα σφάλμα #ΑΞΙΑ!.
Αν το επιχείρημα col_index_num (αριθμός_στήλης) είναι μεγαλύτερος από τον αριθμό των στηλών στον δεδομένο πίνακα, VPR θα αναφέρει ένα σφάλμα # ΑΝΑΦ! (#SSYL!).
Σφάλμα #NAME; σε VLOOKUP
Η πιο απλή περίπτωση είναι ένα λάθος # ΟΝΟΜΑ? (#NAME;) – θα εμφανιστεί εάν γράψετε κατά λάθος ένα όνομα συνάρτησης με σφάλμα.
Η λύση είναι προφανής - ελέγξτε την ορθογραφία σας!
Το VLOOKUP δεν λειτουργεί (περιορισμοί, προειδοποιήσεις και αποφάσεις)
Εκτός από τη μάλλον περίπλοκη σύνταξη, VPR έχει περισσότερους περιορισμούς από οποιαδήποτε άλλη συνάρτηση του Excel. Λόγω αυτών των περιορισμών, φαινομενικά απλοί τύποι με VPR συχνά οδηγούν σε απροσδόκητα αποτελέσματα. Παρακάτω θα βρείτε λύσεις για πολλά κοινά σενάρια όπου VPR είναι λάθος.
1. Το VLOOKUP δεν κάνει διάκριση πεζών-κεφαλαίων
Λειτουργία VPR δεν κάνει διάκριση μεταξύ πεζών και κεφαλαίων χαρακτήρων ως ίδιους. Επομένως, εάν υπάρχουν πολλά στοιχεία στον πίνακα που διαφέρουν μόνο σε περίπτωση, η συνάρτηση VLOOKUP θα επιστρέψει το πρώτο στοιχείο που βρέθηκε, ανεξάρτητα από την περίπτωση.
Απόφαση: Χρησιμοποιήστε μια άλλη συνάρτηση Excel που μπορεί να εκτελέσει μια κάθετη αναζήτηση (ΑΝΑΖΗΤΗΣΗ, SUMPRODUCT, INDEX και MATCH) σε συνδυασμό με ΑΚΡΙΒΗΣΜια που ξεχωρίζει περίπτωση. Για περισσότερες λεπτομέρειες, μπορείτε να μάθετε από το μάθημα – 4 τρόποι για να κάνετε το VLOOKUP με διάκριση πεζών-κεφαλαίων στο Excel.
2. Το VLOOKUP επιστρέφει την πρώτη τιμή που βρέθηκε
Οπως ήδη γνωρίζετε, VPR επιστρέφει την τιμή από τη δεδομένη στήλη που αντιστοιχεί στην πρώτη αντιστοίχιση που βρέθηκε. Ωστόσο, μπορείτε να το βάλετε να εξαγάγει τη 2η, 3η, 4η ή οποιαδήποτε άλλη επανάληψη της τιμής που θέλετε. Εάν πρέπει να εξαγάγετε όλες τις διπλότυπες τιμές, θα χρειαστείτε έναν συνδυασμό συναρτήσεων ΔΕΊΚΤΗΣ (ΔΕΙΚΤΗΣ), ΕΛΑΧΙΣΤΑ (ΜΙΚΡΟ) και LINE (ΣΕΙΡΑ).
3. Μια στήλη προστέθηκε ή αφαιρέθηκε στον πίνακα
Δυστυχώς, οι τύποι VPR σταματά να λειτουργεί κάθε φορά που προστίθεται ή αφαιρείται μια νέα στήλη στον πίνακα αναζήτησης. Αυτό συμβαίνει επειδή η σύνταξη VPR απαιτεί να καθορίσετε το πλήρες εύρος της αναζήτησης και τον συγκεκριμένο αριθμό στήλης για εξαγωγή δεδομένων. Φυσικά, τόσο το δεδομένο εύρος όσο και ο αριθμός στήλης αλλάζουν όταν διαγράφετε μια στήλη ή εισάγετε μια νέα.
Απόφαση: Και πάλι οι λειτουργίες βιάζονται να βοηθήσουν ΔΕΊΚΤΗΣ (ΕΥΡΕΤΗΣ) και ΠΙΟ ΕΚΤΙΘΕΤΑ (ΑΓΩΝΑΣ). Στη φόρμουλα ΕΥΡΕΤΗΡΙΑ+ΑΝΤΙΣΤΟΙΧΙΑ Ορίζετε ξεχωριστά τις στήλες αναζήτησης και ανάκτησης και, ως εκ τούτου, μπορείτε να διαγράψετε ή να εισαγάγετε όσες στήλες θέλετε χωρίς να ανησυχείτε μήπως χρειαστεί να ενημερώσετε όλους τους σχετικούς τύπους αναζήτησης.
4. Οι αναφορές κελιών αλλοιώνονται κατά την αντιγραφή ενός τύπου
Αυτή η επικεφαλίδα εξηγεί εξαντλητικά την ουσία του προβλήματος, σωστά;
Απόφαση: Να χρησιμοποιείτε πάντα απόλυτες αναφορές κελιών (με το σύμβολο $) στις εγγραφές το εύρος, για παράδειγμα $ A $ 2: $ C $ 100 or $ A: $ C. Στη γραμμή τύπων, μπορείτε να αλλάξετε γρήγορα τον τύπο συνδέσμου κάνοντας κλικ F4.
VLOOKUP – εργασία με τις συναρτήσεις IFERROR και ISERROR
Εάν δεν θέλετε να τρομάξετε τους χρήστες με μηνύματα λάθους # N / A, #ΑΞΙΑ! or # ΟΝΟΜΑ?, μπορείτε να εμφανίσετε ένα κενό κελί ή το δικό σας μήνυμα. Μπορείτε να το κάνετε αυτό τοποθετώντας VPR σε μια συνάρτηση ΑΦΑΝΙΣΤΗΡΙΟ (IFERROR) στο Excel 2013, 2010 και 2007 ή χρησιμοποιήστε μια δέσμη συναρτήσεων IF+ISERROR (IF+ISERROR) σε παλαιότερες εκδόσεις.
VLOOKUP: εργασία με τη συνάρτηση IFERROR
Σύνταξη συνάρτησης ΑΦΑΝΙΣΤΗΡΙΟ (IFERROR) είναι απλό και μιλάει από μόνο του:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Δηλαδή, για το πρώτο όρισμα εισάγετε την τιμή που πρέπει να ελεγχθεί για σφάλμα και για το δεύτερο όρισμα καθορίζετε τι θα επιστρέψετε εάν εντοπιστεί σφάλμα.
Για παράδειγμα, αυτός ο τύπος επιστρέφει ένα κενό κελί εάν δεν βρεθεί η τιμή που αναζητάτε:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Εάν θέλετε να εμφανίσετε το δικό σας μήνυμα αντί για το τυπικό μήνυμα σφάλματος της συνάρτησης VPR, βάλτε το σε εισαγωγικά, όπως:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: εργασία με τη συνάρτηση ISERROR
Από τη λειτουργία ΑΦΑΝΙΣΤΗΡΙΟ εμφανίστηκε στο Excel 2007, όταν εργάζεστε σε προηγούμενες εκδόσεις θα πρέπει να χρησιμοποιήσετε τον συνδυασμό IF (ΑΝ) και EOSHIBKA (ISERROR) όπως αυτό:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Για παράδειγμα, ο τύπος IF+ISERROR+VLOOKUP, παρόμοια με τον τύπο IFERROR+VLOOKUPόπως φαίνεται παραπάνω:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Αυτά για σήμερα. Ελπίζω αυτό το σύντομο σεμινάριο να σας βοηθήσει να αντιμετωπίσετε όλα τα πιθανά λάθη. VPR και κάντε τους τύπους σας να λειτουργούν σωστά.