Όλα τα μυστικά της συνάρτησης Excel VLOOKUP για την εύρεση δεδομένων σε έναν πίνακα και την εξαγωγή τους σε έναν άλλο

2024 Συγγραφέας: Malcolm Clapton | [email protected]. Τελευταία τροποποίηση: 2023-12-17 03:52

Αφού διαβάσετε το άρθρο, όχι μόνο θα μάθετε πώς να βρίσκετε δεδομένα σε ένα υπολογιστικό φύλλο του Excel και να τα εξαγάγετε σε άλλο, αλλά και τεχνικές που μπορούν να χρησιμοποιηθούν σε συνδυασμό με τη συνάρτηση VLOOKUP.

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

Ακόμα κι αν χρησιμοποιείτε τη συνάρτηση VLOOKUP εδώ και χρόνια, τότε με μεγάλη πιθανότητα αυτό το άρθρο θα σας φανεί χρήσιμο και δεν θα σας αφήσει αδιάφορους. Για παράδειγμα, όντας ειδικός πληροφορικής και στη συνέχεια επικεφαλής στον τομέα της πληροφορικής, χρησιμοποιώ το VLOOKUP εδώ και 15 χρόνια, αλλά κατάφερα να αντιμετωπίσω όλες τις αποχρώσεις μόνο τώρα, όταν άρχισα να διδάσκω σε ανθρώπους το Excel σε επαγγελματική βάση.

VLOOKUP είναι συντομογραφία του v κατακόρυφος NS επιθεώρηση. Ομοίως, VLOOKUP - Vertical LOOKUP. Το ίδιο το όνομα της συνάρτησης μας υποδηλώνει ότι κάνει αναζήτηση στις σειρές του πίνακα (κάθετα - επαναλαμβανόμενη πάνω από τις σειρές και σταθεροποίηση της στήλης) και όχι στις στήλες (οριζόντια - επανάληψη πάνω από τις στήλες και σταθεροποίηση της γραμμής). Να σημειωθεί ότι το VLOOKUP έχει μια αδερφή - ένα άσχημο παπάκι που δεν θα γίνει ποτέ κύκνος - αυτή είναι η λειτουργία HLOOKUP. Το HLOOKUP, σε αντίθεση με το VLOOKUP, εκτελεί οριζόντιες αναζητήσεις, αλλά η έννοια του Excel (και μάλιστα η έννοια της οργάνωσης δεδομένων) υποδηλώνει ότι οι πίνακές σας έχουν λιγότερες στήλες και πολλές περισσότερες σειρές. Γι' αυτό χρειάζεται να κάνουμε αναζήτηση ανά γραμμές πολλές φορές πιο συχνά παρά κατά στήλες. Εάν χρησιμοποιείτε τη συνάρτηση HLO πολύ συχνά στο Excel, τότε είναι πιθανό να μην καταλάβατε κάτι σε αυτή τη ζωή.

Σύνταξη

Η συνάρτηση VLOOKUP έχει τέσσερις παραμέτρους:

= VLOOKUP (;; [;]), εδώ:

- την επιθυμητή τιμή (σπάνια) ή αναφορά σε κελί που περιέχει την επιθυμητή τιμή (τη συντριπτική πλειονότητα των περιπτώσεων).

- αναφορά σε μια περιοχή κελιών (δισδιάστατος πίνακας), στη στήλη ΠΡΩΤΗ (!) της οποίας θα γίνει αναζήτηση της τιμής της παραμέτρου.

- αριθμός στήλης στο εύρος από το οποίο θα επιστραφεί η τιμή.

- Αυτή είναι μια πολύ σημαντική παράμετρος που απαντά στο ερώτημα εάν η πρώτη στήλη του εύρους είναι ταξινομημένη με αύξουσα σειρά. Εάν ο πίνακας είναι ταξινομημένος, καθορίζουμε την τιμή TRUE ή 1, διαφορετικά - FALSE ή 0. Εάν παραληφθεί αυτή η παράμετρος, ορίζεται από προεπιλογή 1.

Βάζω στοίχημα ότι πολλοί από αυτούς που γνωρίζουν τη λειτουργία του VLOOKUP ως flaky, αφού διαβάσουν την περιγραφή της τέταρτης παραμέτρου, μπορεί να αισθάνονται άβολα, καθώς έχουν συνηθίσει να τη βλέπουν με μια ελαφρώς διαφορετική μορφή: συνήθως μιλούν για ακριβή αντιστοίχιση όταν αναζήτηση (FALSE ή 0) ή σάρωση εύρους (TRUE ή 1).

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

Πώς ακριβώς λειτουργεί ο τύπος VLOOKUP;

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

  

• Φόρμουλα II. Εάν η τελευταία παράμετρος οριστεί ως 0, τότε το VLOOKUP σαρώνει την πρώτη στήλη του πίνακα διαδοχικά και σταματά αμέσως την αναζήτηση όταν βρεθεί η πρώτη ακριβής αντιστοίχιση με την παράμετρο, διαφορετικά ο κωδικός σφάλματος # N / A (# N / A) επιστρέφεται.

  

Ροές εργασιών τύπου

VPR τύπου Ι

VPR τύπου II

Συμπεράσματα για τύπους της μορφής I

1. Οι τύποι μπορούν να χρησιμοποιηθούν για τη διανομή τιμών σε περιοχές.
2. Εάν η πρώτη στήλη περιέχει διπλότυπες τιμές και έχει ταξινομηθεί σωστά, τότε θα επιστραφεί η τελευταία από τις σειρές με διπλότυπες τιμές.
3. Εάν αναζητήσετε μια τιμή που είναι προφανώς μεγαλύτερη από αυτή που μπορεί να περιέχει η πρώτη στήλη, τότε μπορείτε εύκολα να βρείτε την τελευταία γραμμή του πίνακα, η οποία μπορεί να είναι αρκετά πολύτιμη.
4. Αυτή η προβολή θα επιστρέψει το σφάλμα # N / A μόνο εάν δεν βρει τιμή μικρότερη ή ίση με την επιθυμητή.
5. Είναι μάλλον δύσκολο να καταλάβουμε ότι ο τύπος επιστρέφει λάθος τιμές εάν ο πίνακας σας δεν είναι ταξινομημένος.

Συμπεράσματα για τύπους τύπου II

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

Απόδοση VLOOKUP

Έχετε φτάσει στο αποκορύφωμα του άρθρου. Φαίνεται, λοιπόν, ποια είναι η διαφορά αν προσδιορίσω το μηδέν ή το ένα ως τελευταία παράμετρο; Βασικά, όλοι υποδεικνύουν, φυσικά, το μηδέν, καθώς αυτό είναι αρκετά πρακτικό: δεν χρειάζεται να ανησυχείτε για την ταξινόμηση της πρώτης στήλης του πίνακα, μπορείτε να δείτε αμέσως εάν η τιμή βρέθηκε ή όχι. Αλλά αν έχετε πολλές χιλιάδες τύπους VLOOKUP στο φύλλο σας, θα παρατηρήσετε ότι το VLOOKUP II είναι αργό. Ταυτόχρονα, συνήθως όλοι αρχίζουν να σκέφτονται:

• Χρειάζομαι έναν πιο ισχυρό υπολογιστή.
• Χρειάζομαι μια πιο γρήγορη φόρμουλα, για παράδειγμα, πολλοί άνθρωποι γνωρίζουν για το INDEX + MATCH, το οποίο υποτίθεται ότι είναι ταχύτερο κατά 5-10%.

Και λίγοι άνθρωποι πιστεύουν ότι μόλις αρχίσετε να χρησιμοποιείτε το VLOOKUP τύπου I και βεβαιωθείτε ότι η πρώτη στήλη είναι ταξινομημένη με οποιοδήποτε μέσο, η ταχύτητα του VLOOKUP θα αυξηθεί 57 φορές. Γράφω με λόγια - ΠΕΝΗΝΤΑ ΕΠΤΑ ΦΟΡΕΣ! Όχι 57%, αλλά 5.700%. Έλεγξα αυτό το γεγονός αρκετά αξιόπιστα.

Το μυστικό μιας τέτοιας γρήγορης εργασίας έγκειται στο γεγονός ότι ένας εξαιρετικά αποτελεσματικός αλγόριθμος αναζήτησης μπορεί να εφαρμοστεί σε έναν ταξινομημένο πίνακα, ο οποίος ονομάζεται δυαδική αναζήτηση (μέθοδος κατά το ήμισυ, μέθοδος διχοτομίας). Άρα το VLOOKUP τύπου I το εφαρμόζει και το VLOOKUP τύπου II αναζητά χωρίς καμία απολύτως βελτιστοποίηση. Το ίδιο ισχύει για τη συνάρτηση MATCH, η οποία περιλαμβάνει παρόμοια παράμετρο, και τη συνάρτηση LOOKUP, η οποία λειτουργεί μόνο σε ταξινομημένους πίνακες και περιλαμβάνεται στο Excel για συμβατότητα με το Lotus 1-2-3.

Μειονεκτήματα της φόρμουλας

Τα μειονεκτήματα του VLOOKUP είναι προφανή: πρώτον, πραγματοποιεί αναζήτηση μόνο στην πρώτη στήλη του καθορισμένου πίνακα και, δεύτερον, μόνο στα δεξιά αυτής της στήλης. Και όπως καταλαβαίνετε, μπορεί κάλλιστα η στήλη που περιέχει τις απαραίτητες πληροφορίες να βρίσκεται στα αριστερά της στήλης στην οποία θα κοιτάξουμε. Ο ήδη αναφερόμενος συνδυασμός τύπων INDEX + MATCH στερείται αυτό το μειονέκτημα, γεγονός που τον καθιστά την πιο ευέλικτη λύση για την εξαγωγή δεδομένων από πίνακες σε σύγκριση με το VLOOKUP (VLOOKUP).

Μερικές πτυχές της εφαρμογής του τύπου στην πραγματική ζωή

Αναζήτηση εύρους

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

Αναζήτηση συμβολοσειρών κειμένου

Φυσικά, το VLOOKUP δεν αναζητά μόνο αριθμούς, αλλά και κείμενο. Θα πρέπει να ληφθεί υπόψη ότι ο τύπος δεν κάνει διάκριση μεταξύ της περίπτωσης των χαρακτήρων. Εάν χρησιμοποιείτε χαρακτήρες μπαλαντέρ, μπορείτε να οργανώσετε μια ασαφή αναζήτηση. Υπάρχουν δύο χαρακτήρες μπαλαντέρ: "?" - αντικαθιστά οποιονδήποτε χαρακτήρα σε μια συμβολοσειρά κειμένου, "*" - αντικαθιστά οποιονδήποτε αριθμό χαρακτήρων.

Χώροι μάχης

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

Διαφορετική μορφή δεδομένων

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

= VLOOKUP (−− D7; Προϊόντα! $ A $ 2: $ C $ 5; 3; 0) - εάν το D7 περιέχει κείμενο και ο πίνακας περιέχει αριθμούς.

= VLOOKUP (D7 & ""); Προϊόντα $ A $ 2: $ C $ 5; 3; 0) - και αντίστροφα.

Παρεμπιπτόντως, μπορείτε να μεταφράσετε κείμενο σε αριθμό με πολλούς τρόπους ταυτόχρονα, επιλέξτε:

• Διπλή άρνηση -D7.
• Πολλαπλασιασμός με ένα D7 * 1.
• Μηδενική προσθήκη D7 + 0.
• Αύξηση στην πρώτη ισχύ D7 ^ 1.

Η μετατροπή ενός αριθμού σε κείμενο γίνεται μέσω συνένωσης με μια κενή συμβολοσειρά, η οποία αναγκάζει το Excel να μετατρέψει τον τύπο δεδομένων.

Πώς να καταστείλετε το # N / A

Αυτό είναι πολύ βολικό να γίνει με τη λειτουργία IFERROR.

Για παράδειγμα: = IFERROR (VLOOKUP (D7; Προϊόντα! $ A $ 2: $ C $ 5; 3; 0); "").

Εάν το VLOOKUP επιστρέψει τον κωδικό σφάλματος # N / A, τότε το IFERROR θα τον παρεμποδίσει και θα αντικαταστήσει την παράμετρο 2 (σε αυτήν την περίπτωση, μια κενή συμβολοσειρά) και εάν δεν παρουσιαστεί σφάλμα, τότε αυτή η συνάρτηση θα προσποιηθεί ότι δεν υπάρχει καθόλου, αλλά υπάρχει μόνο VLOOKUP που επέστρεψε κανονικό αποτέλεσμα.

Πίνακας

Συχνά ξεχνούν να κάνουν την αναφορά του πίνακα απόλυτη, και όταν τεντώνουν ο πίνακας "επιπλέει". Θυμηθείτε να χρησιμοποιήσετε $ A $ 2: $ C $ 5 αντί για A2: C5.

Είναι καλή ιδέα να τοποθετήσετε τον πίνακα αναφοράς σε ξεχωριστό φύλλο του βιβλίου εργασίας. Δεν πέφτει κάτω από τα πόδια και θα είναι πιο ασφαλές.

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

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

Λοιπόν, στα όρια της ιδιοφυΐας - να τακτοποιήσετε τη συστοιχία στη μορφή.

Χρησιμοποιώντας τη συνάρτηση COLUMN για να καθορίσετε τη στήλη προς εξαγωγή

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

Δημιουργία σύνθετου κλειδιού με & "|" &

Εάν καταστεί απαραίτητο να κάνετε αναζήτηση με πολλές στήλες ταυτόχρονα, τότε είναι απαραίτητο να δημιουργήσετε ένα σύνθετο κλειδί για την αναζήτηση. Εάν η επιστρεφόμενη τιμή δεν ήταν κείμενο (όπως συμβαίνει με το πεδίο "Κώδικας"), αλλά αριθμητική, τότε ο πιο βολικός τύπος SUMIFS θα ήταν κατάλληλος για αυτό και το κλειδί σύνθετης στήλης δεν θα απαιτούσε καθόλου.

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