Όποιος είναι πολύ τεμπέλης ή δεν έχει χρόνο να διαβάσει - δείτε το βίντεο. Λεπτομέρειες και αποχρώσεις βρίσκονται στο παρακάτω κείμενο.
Διατύπωση του προβλήματος
Έτσι, έχουμε δύο πίνακες - πίνακας παραγγελιών и λίστα τιμών:
Το καθήκον είναι να αντικαταστήσετε αυτόματα τις τιμές από τον τιμοκατάλογο στον πίνακα παραγγελιών, εστιάζοντας στο όνομα του προϊόντος, ώστε αργότερα να μπορείτε να υπολογίσετε το κόστος.
Λύση
Στο σύνολο συναρτήσεων Excel, κάτω από την κατηγορία Αναφορές και πίνακες (Αναζήτηση και αναφορά) υπάρχει μια λειτουργία VPR (VLOOKUP).Αυτή η συνάρτηση αναζητά μια δεδομένη τιμή (στο παράδειγμά μας, αυτή είναι η λέξη "Μήλα") στην αριστερή στήλη του καθορισμένου πίνακα (τιμοκατάλογος) που κινείται από πάνω προς τα κάτω και, αφού τη βρει, εμφανίζει τα περιεχόμενα του διπλανού κελιού (23 ρούβλια) .Σχηματικά, η λειτουργία αυτής της συνάρτησης μπορεί να αναπαρασταθεί Έτσι:
Για ευκολία περαιτέρω χρήσης της λειτουργίας, κάντε ένα πράγμα ταυτόχρονα – δώστε το δικό σας όνομα στο εύρος των κελιών στον τιμοκατάλογο. Για να το κάνετε αυτό, επιλέξτε όλα τα κελιά του τιμοκαταλόγου εκτός από την "κεφαλίδα" (G3: H19), επιλέξτε από το μενού Εισαγωγή – Όνομα – Εκχώρηση (Εισαγωγή — Όνομα — Ορισμός) ή πατήστε CTRL + F3 και εισάγετε οποιοδήποτε όνομα (χωρίς κενά) όπως Τιμή… Τώρα, στο μέλλον, μπορείτε να χρησιμοποιήσετε αυτό το όνομα για να συνδεθείτε με τον τιμοκατάλογο.
Τώρα χρησιμοποιούμε τη συνάρτηση VPR… Επιλέξτε το κελί όπου θα εισαχθεί (D3) και ανοίξτε την καρτέλα Τύποι – Εισαγωγή συναρτήσεων (Τύποι — Εισαγωγή συνάρτησης)… Στην κατηγορία Αναφορές και πίνακες (Αναζήτηση και αναφορά) βρείτε τη συνάρτηση VPR (VLOOKUP) και πατήστε το πλήκτρο OK… Θα εμφανιστεί ένα παράθυρο για την εισαγωγή ορισμάτων για τη συνάρτηση:
Τα γεμίζουμε με τη σειρά:
- Επιθυμητή αξία (Αξία αναζήτησης) – το όνομα του προϊόντος που πρέπει να βρει η συνάρτηση στην αριστερή στήλη του τιμοκαταλόγου. Στην περίπτωσή μας, η λέξη "Μήλα" από το κελί B3.
- Τραπέζι (Πίνακας πίνακα) – έναν πίνακα από τον οποίο έχουν ληφθεί οι επιθυμητές τιμές uXNUMXbuXNUMX, δηλαδή ο τιμοκατάλογος μας. Για αναφορά, χρησιμοποιούμε το δικό μας όνομα "Τιμή" που δόθηκε προηγουμένως. Εάν δεν δώσατε όνομα, μπορείτε απλώς να επιλέξετε τον πίνακα, αλλά μην ξεχάσετε να πατήσετε το κουμπί F4για να καρφιτσώσετε το σύνδεσμο με τα σημάδια του δολαρίου, γιατί διαφορετικά, θα γλιστρήσει προς τα κάτω κατά την αντιγραφή του τύπου μας προς τα κάτω στα υπόλοιπα κελιά της στήλης D3:D30.
- Αριθμός_στήλης (Αριθμός ευρετηρίου στήλης) – αύξων αριθμός (όχι γράμμα!) Της στήλης του τιμοκαταλόγου από την οποία θα πάρουμε τιμές τιμών. Η πρώτη στήλη του τιμοκαταλόγου με τα ονόματα είναι με αριθμό 1, επομένως χρειαζόμαστε την τιμή από τη στήλη με αριθμό 2.
- interval_lookup (Αναζήτηση εύρους) – Μόνο δύο τιμές μπορούν να εισαχθούν σε αυτό το πεδίο: FALSE ή TRUE:
- Εάν εισαχθεί μια τιμή 0 or ΞΑΠΛΩΜΕΝΗ (ΨΕΥΔΗΣ), τότε στην πραγματικότητα αυτό σημαίνει ότι επιτρέπεται μόνο η αναζήτηση ακριβής αντιστοίχιση, δηλαδή εάν η συνάρτηση δεν βρει το μη τυπικό είδος που καθορίζεται στον πίνακα παραγγελιών στον τιμοκατάλογο (αν έχει εισαχθεί, για παράδειγμα, "Καρύδα", θα δημιουργήσει το σφάλμα #N/A (χωρίς δεδομένα).
- Εάν εισαχθεί μια τιμή 1 or ΑΛΗΘΙΝΗ (ΑΛΗΘΗΣ), τότε αυτό σημαίνει ότι επιτρέπετε την αναζήτηση όχι για το ακριβές, αλλά κατά προσέγγιση ταίριασμα, δηλαδή στην περίπτωση του "coconut", η συνάρτηση θα προσπαθήσει να βρει ένα προϊόν με όνομα που να είναι όσο το δυνατόν πιο κοντά στο "coconut" και να επιστρέψει την τιμή για αυτό το όνομα. Στις περισσότερες περιπτώσεις, μια τέτοια κατά προσέγγιση αντικατάσταση μπορεί να παίξει ένα κόλπο στον χρήστη αντικαθιστώντας την αξία του λάθος προϊόντος που υπήρχε στην πραγματικότητα! Επομένως, για τα περισσότερα πραγματικά επιχειρηματικά προβλήματα, είναι καλύτερο να μην επιτρέπεται η κατά προσέγγιση αναζήτηση. Η εξαίρεση είναι όταν αναζητούμε αριθμούς και όχι κείμενο – για παράδειγμα, κατά τον υπολογισμό των εκπτώσεων στα βήματα.
Τα παντα! Μένει να πατήσετε OK και αντιγράψτε την εισαγόμενη συνάρτηση σε ολόκληρη τη στήλη.
# N / A σφάλματα και η καταστολή τους
Λειτουργία VPR (VLOOKUP) επιστρέφει σφάλμα #N/A (# N/A) αν ένα:
- Ενεργοποιήθηκε η ακριβής αναζήτηση (επιχείρημα Προβολή διαστήματος = 0) και το επιθυμητό όνομα δεν είναι μέσα Τραπέζι.
- Περιλαμβάνεται χονδρική αναζήτηση (Προβολή διαστήματος = 1), αλλά Τραπέζι, στο οποίο πραγματοποιείται η αναζήτηση δεν ταξινομείται κατά αύξουσα σειρά ονομάτων.
- Η μορφή του κελιού από όπου προέρχεται η απαιτούμενη τιμή του ονόματος (για παράδειγμα, B3 στην περίπτωσή μας) και η μορφή των κελιών της πρώτης στήλης (F3: F19) του πίνακα είναι διαφορετική (για παράδειγμα, αριθμητική και κείμενο ). Αυτή η περίπτωση είναι ιδιαίτερα χαρακτηριστική όταν χρησιμοποιούνται αριθμητικοί κωδικοί (αριθμοί λογαριασμού, αναγνωριστικά, ημερομηνίες κ.λπ.) αντί για ονόματα κειμένου. Σε αυτήν την περίπτωση, μπορείτε να χρησιμοποιήσετε τις λειτουργίες Ч и ΚΕΙΜΕΝΟ για να μετατρέψετε μορφές δεδομένων. Θα μοιάζει κάπως έτσι:
=VLOOKUP(TEXT(B3),τιμή,0)
Μπορείτε να διαβάσετε περισσότερα για αυτό εδώ.
- Η συνάρτηση δεν μπορεί να βρει την απαιτούμενη τιμή επειδή ο κώδικας περιέχει κενά ή αόρατους μη εκτυπώσιμους χαρακτήρες (σπασίματα γραμμής κ.λπ.). Σε αυτήν την περίπτωση, μπορείτε να χρησιμοποιήσετε συναρτήσεις κειμένου TRIM (ΤΑΚΤΟΠΟΙΗΣΗ) и ΕΚΤΥΠΩΣΗ(ΚΑΘΑΡΗ) για να τα αφαιρέσετε:
=VLOOKUP(TRIMSPACES(CLEAN(B3)),τιμή,0)
=VLOOKUP(TRIM(CLEAN(B3));τιμή;0)
Για να αποκρύψετε το μήνυμα σφάλματος # N / A (# N/A) σε περιπτώσεις όπου η συνάρτηση δεν μπορεί να βρει ακριβή αντιστοίχιση, μπορείτε να χρησιμοποιήσετε τη συνάρτηση ΑΦΑΝΙΣΤΗΡΙΟ (IFERROR)… Έτσι, για παράδειγμα, αυτή η κατασκευή παρεμποδίζει τυχόν σφάλματα που δημιουργούνται από το VLOOKUP και τα αντικαθιστά με μηδενικά:
= IFERROR (VLOOKUP (B3, τιμή, 2, 0), 0)
= IFERROR (VLOOKUP (B3; τιμή; 2; 0); 0)
PS
Εάν χρειάζεται να εξαγάγετε όχι μία τιμή, αλλά ολόκληρο το σύνολο ταυτόχρονα (αν υπάρχουν πολλά διαφορετικά), τότε θα πρέπει να σαμανοποιήσετε με τον τύπο πίνακα. ή χρησιμοποιήστε τη νέα δυνατότητα XLOOKUP από το Office 365.
- Μια βελτιωμένη έκδοση της λειτουργίας VLOOKUP (VLOOKUP 2).
- Γρήγορος υπολογισμός των εκπτώσεων βημάτων (εύρος) χρησιμοποιώντας τη λειτουργία VLOOKUP.
- Πώς να δημιουργήσετε ένα "αριστερό VLOOKUP" χρησιμοποιώντας τις συναρτήσεις INDEX και MATCH
- Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP για να συμπληρώσετε τις φόρμες με δεδομένα από τη λίστα
- Πώς να βγάλετε όχι την πρώτη, αλλά όλες τις τιμές από τον πίνακα ταυτόχρονα
- Λειτουργίες VLOOKUP2 και VLOOKUP3 από το πρόσθετο PLEX