01 από 03
Εύρεση δεδομένων προς τα αριστερά
Επισκόπηση φόρμας αριστερής αναζήτησης Excel
Η λειτουργία VLOOKUP του Excel χρησιμοποιείται για την εύρεση και επιστροφή πληροφοριών από έναν πίνακα δεδομένων με βάση την τιμή αναζήτησης που επιλέγετε.
Κανονικά, το VLOOKUP απαιτεί την τιμή αναζήτησης να βρίσκεται στην αριστερή στήλη του πίνακα δεδομένων και η συνάρτηση επιστρέφει ένα άλλο πεδίο δεδομένων που βρίσκεται στην ίδια γραμμή στα δεξιά αυτής της τιμής.
Συνδυάζοντας το VLOOKUP με τη λειτουργία CHOOSE ; Ωστόσο, μπορεί να δημιουργηθεί ένας τύπος αριστερά αναζήτησης που:
- επιτρέπει την επιλογή της τιμής αναζήτησης από οποιαδήποτε στήλη στον πίνακα δεδομένων
- επιστροφή πληροφοριών που βρίσκονται σε οποιαδήποτε στήλη στα αριστερά της τιμής αναζήτησης
Παράδειγμα: Χρησιμοποιώντας τις λειτουργίες VLOOKUP και CHOOSE σε έναν τύπο αριστερής αναζήτησης
Τα παρακάτω βήματα δημιουργούν τον τύπο αριστερής αναζήτησης που φαίνεται στην παραπάνω εικόνα.
Ο τύπος
= VLOOKUP ($ D $ 2, ΕΠΙΛΕΞΤΕ ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)επιτρέπει να βρεθεί το τμήμα που παρέχεται από τις διάφορες εταιρείες που αναφέρονται στη στήλη 3 του πίνακα δεδομένων.
Η δουλειά της συνάρτησης CHOOSE στη φόρμουλα είναι να εξαπατήσουμε το VLOOKUP να πιστεύει ότι η στήλη 3 είναι στην πραγματικότητα η στήλη 1. Ως αποτέλεσμα, το όνομα της εταιρείας μπορεί να χρησιμοποιηθεί ως τιμή αναζήτησης για να βρει το όνομα του τμήματος που παρέχεται από κάθε εταιρεία.
Βήματα φροντιστηρίων - Εισαγωγή δεδομένων μαθήματος
- Καταχωρίστε τις ακόλουθες επικεφαλίδες στα υποδεικνυόμενα κελιά: D1 - Προμηθευτής Ε1 - Μέρος
- Εισαγάγετε τον πίνακα των δεδομένων που βλέπετε στην παραπάνω εικόνα στα κελιά D4 έως F9
- Οι σειρές 2 και 3 παραμένουν κενές προκειμένου να ικανοποιηθούν τα κριτήρια αναζήτησης και ο τύπος αριστερής αναζήτησης που δημιουργήθηκε κατά τη διάρκεια αυτού του σεμιναρίου
Εκκίνηση της Αριστεράς Φόρμουλα Αναζήτησης - Άνοιγμα του διαλόγου VLOOKUP
Παρόλο που είναι δυνατό να πληκτρολογήσετε ακριβώς τον παραπάνω τύπο απευθείας στο κελί F1 στο φύλλο εργασίας, πολλοί άνθρωποι έχουν δυσκολία με τη σύνταξη του τύπου.
Μια εναλλακτική λύση, σε αυτήν την περίπτωση, είναι να χρησιμοποιήσετε το παράθυρο διαλόγου VLOOKUP. Σχεδόν όλες οι λειτουργίες του Excel έχουν ένα παράθυρο διαλόγου που σας επιτρέπει να εισάγετε τα επιχειρήματα της κάθε λειτουργίας σε ξεχωριστή γραμμή.
Εκπαιδευτικά βήματα
- Κάντε κλικ στο κελί E2 του φύλλου εργασίας - τη θέση όπου θα εμφανιστούν τα αποτελέσματα του αριστερού τύπου φόρμουλα αναζήτησης
- Κάντε κλικ στην καρτέλα Φόρμες της κορδέλας
- Κάντε κλικ στην επιλογή αναζήτησης και αναφοράς στην κορδέλα για να ανοίξετε την αναπτυσσόμενη λίστα λειτουργιών
- Κάντε κλικ στο VLOOKUP στη λίστα για να εμφανιστεί το παράθυρο διαλόγου της λειτουργίας
02 του 03
Εισαγωγή επιχειρημάτων στο παράθυρο διαλόγου VLOOKUP - Κάντε κλικ για να δείτε μεγαλύτερη εικόνα
Τα επιχειρήματα του VLOOKUP
Τα επιχειρήματα μιας συνάρτησης είναι οι τιμές που χρησιμοποιούνται από τη συνάρτηση για τον υπολογισμό ενός αποτελέσματος.
Στο παράθυρο διαλόγου μιας λειτουργίας, το όνομα κάθε όρου βρίσκεται σε ξεχωριστή γραμμή ακολουθούμενη από ένα πεδίο στο οποίο πρέπει να εισαχθεί μια τιμή.
Καταχωρίστε τις παρακάτω τιμές για κάθε ένα από τα επιχειρήματα του VLOOKUP στη σωστή γραμμή του πλαισίου διαλόγου όπως φαίνεται στην παραπάνω εικόνα.
Η τιμή αναζήτησης
Η τιμή αναζήτησης είναι το πεδίο πληροφοριών που χρησιμοποιείται για την αναζήτηση στη συστοιχία πίνακα. Το VLOOKUP επιστρέφει ένα άλλο πεδίο δεδομένων από την ίδια σειρά με την τιμή αναζήτησης.
Αυτό το παράδειγμα χρησιμοποιεί αναφορά κυψέλης στη θέση όπου θα εισαχθεί το όνομα της εταιρείας στο φύλλο εργασίας. Το πλεονέκτημα αυτού του γεγονότος είναι ότι καθιστά εύκολη την αλλαγή του ονόματος της εταιρείας χωρίς επεξεργασία της φόρμουλας.
Εκπαιδευτικά βήματα
- Κάντε κλικ στη γραμμή lookup_value στο παράθυρο διαλόγου
- Κάντε κλικ στο κελί D2 για να προσθέσετε αυτήν την αναφορά κελιού στη γραμμή lookup_value
- Πατήστε το πλήκτρο F4 στο πληκτρολόγιο για να κάνετε απόλυτη την αναφορά κυττάρων - $ D $ 2
Σημείωση: Οι απόλυτες αναφορές κυψελών χρησιμοποιούνται για την τιμή αναζήτησης και τα όρια πίνακα πινάκων για την αποφυγή σφαλμάτων αν η φόρτωση αναζήτησης αντιγράφεται σε άλλα κελιά του φύλλου εργασίας.
Η πίνακας πίνακα: Εισαγωγή της λειτουργίας CHOOSE
Το όρισμα πίνακα πίνακα είναι το μπλοκ συνεχόμενων δεδομένων από τα οποία ανακτώνται συγκεκριμένες πληροφορίες.
Κανονικά, το VLOOKUP βλέπει προς τα δεξιά το όρισμα της τιμής αναζήτησης για να βρει δεδομένα στον πίνακα πίνακα. Για να το δούμε αριστερά, το VLOOKUP πρέπει να εξαπατηθεί αναδιατάσσοντας τις στήλες στη συστοιχία πίνακα χρησιμοποιώντας τη λειτουργία CHOOSE.
Στον τύπο αυτό, η λειτουργία CHOOSE ολοκληρώνει δύο εργασίες:
- δημιουργεί πίνακα πίνακα που έχει πλάτος μόνο δύο στηλών - στήλες D και F
- αλλάζει το δικαίωμα στην αριστερή σειρά των στηλών στη συστοιχία πίνακα έτσι ώστε η στήλη F έρχεται πρώτη και η στήλη D είναι η δεύτερη
Λεπτομέρειες για το πώς η λειτουργία CHOOSE επιτυγχάνει αυτές τις εργασίες μπορείτε να βρείτε στη σελίδα 3 του εκπαιδευτικού προγράμματος .
Εκπαιδευτικά βήματα
Σημείωση: Όταν εισάγετε χειροκίνητα τις λειτουργίες, τα επιχειρήματα κάθε λειτουργίας πρέπει να χωρίζονται με κόμμα "," .
- Στο παράθυρο διαλόγου VLOOKUP, κάντε κλικ στη γραμμή Table_array
- Εισαγάγετε την ακόλουθη λειτουργία CHOOSE
- ΕΠΙΛΕΞΤΕ ({1,2}, $ F: $ F, $ D: $ D)
Ο Αριθμός Ευρετηρίου Στήλης
Κανονικά, ο αριθμός ευρετηρίου στήλης υποδεικνύει ποια στήλη της συστοιχίας πίνακα περιέχει τα δεδομένα που ακολουθείτε. Σε αυτόν τον τύπο. Ωστόσο, αναφέρεται στη σειρά των στηλών που έχουν οριστεί από τη λειτουργία CHOOSE.
Η συνάρτηση CHOOSE δημιουργεί μια πίνακα πίνακα που είναι πλάτος δύο στηλών με τη στήλη F να ακολουθείται πρώτα από τη στήλη D. Δεδομένου ότι οι αναζητούμενες πληροφορίες - το όνομα μέρους - βρίσκονται στη στήλη D, η τιμή του δείκτη του δείκτη στήλης πρέπει να ρυθμιστεί στο 2.
Εκπαιδευτικά βήματα
- Κάντε κλικ στη γραμμή Col_index_num στο παράθυρο διαλόγου
- Πληκτρολογήστε ένα 2 στη γραμμή αυτή
Η αναζήτηση περιοχής
Το όρισμα Range_lookup του VLOOKUP είναι μια λογική τιμή (TRUE ή FALSE μόνο) που υποδεικνύει εάν θέλετε το VLOOKUP να βρει ακριβή ή κατά προσέγγιση αντιστοιχία με την τιμή αναζήτησης.
- Εάν το TRUE ή αν παραλειφθεί αυτό το όρισμα , το VLOOKUP επιστρέφει είτε ακριβή αντιστοίχιση με την τιμή Lookup_value, είτε, αν δεν βρεθεί ακριβής αντιστοίχιση, το VLOOKUP επιστρέφει την επόμενη μεγαλύτερη τιμή. Για να γίνει αυτό, τα δεδομένα στην πρώτη στήλη του Table_array πρέπει να ταξινομούνται με αύξουσα σειρά .
- Εάν FALSE, VLOOKUP θα χρησιμοποιήσει μόνο μια ακριβή αντιστοιχία στην τιμή Lookup_value. Εάν υπάρχουν δύο ή περισσότερες τιμές στην πρώτη στήλη του Table_array που ταιριάζουν με την τιμή αναζήτησης, χρησιμοποιείται η πρώτη τιμή που βρέθηκε. Εάν δεν βρεθεί ακριβής αντιστοίχιση, επιστρέφεται σφάλμα # N / A.
Σε αυτό το σεμινάριο, δεδομένου ότι ψάχνουμε για ένα συγκεκριμένο όνομα, το Range_lookup θα οριστεί σε False, έτσι ώστε να επιστραφούν μόνο ακριβείς αντιστοιχίσεις από τον τύπο.
Εκπαιδευτικά βήματα
- Κάντε κλικ στη γραμμή Range_lookup στο παράθυρο διαλόγου
- Πληκτρολογήστε τη λέξη Ψευδές σε αυτή τη γραμμή για να υποδείξετε ότι θέλουμε το VLOOKUP να επιστρέψει μια ακριβή αντιστοιχία για τα δεδομένα που αναζητούμε
- Κάντε κλικ στο κουμπί ΟΚ για να ολοκληρώσετε το παράθυρο διαλόγου για το αριστερό πλαίσιο αναζήτησης και το κλείσιμο
- Δεδομένου ότι δεν έχουμε εισάγει ακόμη το όνομα της εταιρείας στο κελί D2, θα πρέπει να υπάρχει ένα σφάλμα # N / A στο κελί E2
03 του 03
Δοκιμάζοντας τον τύπο αριστερής αναζήτησης
Επιστροφή δεδομένων με τον τύπο αριστερής αναζήτησης
Για να βρείτε ποιες εταιρείες προμηθεύουν τα εξαρτήματα, πληκτρολογήστε το όνομα μιας εταιρείας στο κελί D2 και πατήστε το πλήκτρο ENTER στο πληκτρολόγιο.
Το όνομα του μέρους θα εμφανίζεται στο κελί E2.
Εκπαιδευτικά βήματα
- Κάντε κλικ στο κελί D2 στο φύλλο εργασίας σας
- Πληκτρολογήστε Gadgets Plus στο κελί D2 και πατήστε το πλήκτρο ENTER στο πληκτρολόγιο
- Το κείμενο Gadgets - το τμήμα που παρέχεται από την εταιρεία Gadgets Plus - πρέπει να εμφανίζεται στο κελί E2
- Δοκιμάστε περαιτέρω τον τύπο αναζήτησης πληκτρολογώντας άλλα ονόματα εταιρειών στο κελί D2 και το αντίστοιχο όνομα μέρους θα πρέπει να εμφανίζεται στο κελί E2
Μηνύματα σφάλματος VLOOKUP
Εάν εμφανιστεί μήνυμα σφάλματος όπως # N / A στο κελί E2, ελέγξτε πρώτα για ορθογραφικά λάθη στο κελί D2.
Εάν η ορθογραφία δεν είναι το πρόβλημα, αυτή η λίστα με μηνύματα σφάλματος VLOOKUP μπορεί να σας βοηθήσει να προσδιορίσετε πού βρίσκεται το πρόβλημα.
Καταργώντας τη δουλειά της λειτουργίας CHOOSE
Όπως αναφέρθηκε, σε αυτόν τον τύπο, η λειτουργία CHOOSE έχει δύο εργασίες:
- δημιουργεί πίνακα πίνακα που έχει πλάτος μόνο δύο στηλών - στήλες D και F
- αλλάζει το δικαίωμα στην αριστερή σειρά των στηλών στη συστοιχία πίνακα έτσι ώστε η στήλη F έρχεται πρώτη και η στήλη D είναι η δεύτερη
Δημιουργία πίνακα πίνακα δύο χρωμάτων
Η σύνταξη για τη λειτουργία CHOOSE είναι:
= CHOOSE (Αριθμός_καταλόγου, Τιμή1, Τιμή2, ... Τιμή254)
Η λειτουργία CHOOSE συνήθως επιστρέφει μία τιμή από τη λίστα τιμών (Value1 σε Value254) με βάση τον αριθμό ευρετηρίου που καταχωρίσατε.
Εάν ο αριθμός ευρετηρίου είναι 1, η συνάρτηση επιστρέφει Value1 από τη λίστα. εάν ο αριθμός ευρετηρίου είναι 2, η συνάρτηση επιστρέφει Value2 από τη λίστα και ούτω καθεξής.
Εισάγοντας πολλούς αριθμούς ευρετηρίου. Ωστόσο, η λειτουργία θα επαναφέρει πολλές τιμές σε οποιαδήποτε επιθυμητή σειρά. Η λήψη του CHOOSE για την επιστροφή πολλών τιμών γίνεται με τη δημιουργία ενός πίνακα .
Η είσοδος μιας συστοιχίας επιτυγχάνεται περικλείοντας τους αριθμούς που εισάγονται με σγουράκια ή βραχίονες. Εισάγονται δύο αριθμοί για τον αριθμό ευρετηρίου: {1,2} .
Θα πρέπει να σημειωθεί ότι το CHOOSE δεν περιορίζεται στη δημιουργία ενός πίνακα δύο στηλών. Με τη συμπερίληψη ενός πρόσθετου αριθμού στη συστοιχία - όπως {1,2,3} - και ενός επιπλέον εύρους στο όρισμα της τιμής, μπορεί να δημιουργηθεί ένας πίνακας με τρεις στήλες.
Πρόσθετες στήλες θα σας επέτρεπαν να επιστρέψετε διαφορετικές πληροφορίες με τον τύπο αριστερά αναζήτησης απλά αλλάζοντας το όρισμα του αριθμού ευρετηρίου στήλης VLOOKUP στον αριθμό της στήλης που περιέχει τις επιθυμητές πληροφορίες.
Αλλαγή της σειράς των στηλών με τη λειτουργία CHOOSE
Στη λειτουργία CHOOSE που χρησιμοποιείται σε αυτόν τον τύπο: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) , το εύρος για τη στήλη F παρατίθεται πριν τη στήλη D.
Δεδομένου ότι η λειτουργία CHOOSE ορίζει τον πίνακα πίνακα VLOOKUP - την πηγή δεδομένων για τη λειτουργία αυτή - η αλλαγή της σειράς των στηλών στη λειτουργία CHOOSE μεταδίδεται στο VLOOKUP.
Τώρα, όσον αφορά το VLOOKUP, η πίνακας πίνακας είναι μόνο δύο στήλες πλάτος με τη στήλη F στα αριστερά και τη στήλη D στα δεξιά. Δεδομένου ότι η στήλη F περιέχει το όνομα της εταιρείας που θέλουμε να ψάξουμε και δεδομένου ότι η στήλη D περιέχει τα ονόματα των τμημάτων, το VLOOKUP θα είναι σε θέση να εκτελέσει τα καθήκοντα κανονικής αναζήτησης κατά την εύρεση δεδομένων που βρίσκεται στα αριστερά της τιμής αναζήτησης.
Ως αποτέλεσμα, η VLOOKUP μπορεί να χρησιμοποιήσει το όνομα της εταιρείας για να βρει το τμήμα που παρέχει.