Στη συνάρτηση VLOOKUP, συχνά ορίζουμε col_index_no static. Το κωδικοποιήσαμε σκληρά μέσα στον τύπο VLOOKUP, όπως το VLOOKUP (id, δεδομένα,3, 0). Το πρόβλημα προκύπτει όταν εισάγουμε ή διαγράφουμε μια στήλη στα δεδομένα. Εάν αφαιρέσουμε ή προσθέσουμε μια στήλη πριν ή μετά την 3η στήλη, η 3η στήλη δεν θα αναφέρεται πλέον στην προβλεπόμενη στήλη. Αυτό είναι ένα πρόβλημα. Άλλο είναι όταν έχετε πολλές στήλες για αναζήτηση. Θα χρειαστεί να επεξεργαστείτε το ευρετήριο στήλης σε κάθε τύπο. Η απλή επικόλληση αντιγραφής δεν θα βοηθήσει.
Τι θα λέγατε, αν μπορείτε να πείτε στο VLOOKUP να εξετάσει τις επικεφαλίδες και να επιστρέψει μόνο την αντίστοιχη τιμή επικεφαλίδων. Αυτό ονομάζεται αμφίδρομη VLOOKUP.
Για παράδειγμα, εάν έχω έναν τύπο VLOOKUP για τοσημάδια στήλη, τότε το VLOOKUP πρέπει να αναζητηθεί σημάδια στήλη στα δεδομένα και επιστρέφει την τιμή από αυτήν τη στήλη. Αυτό θα λύσει το πρόβλημά μας.
Χμμ … Εντάξει, πώς το κάνουμε αυτό; Χρησιμοποιώντας τη συνάρτηση Match εντός της συνάρτησης VLOOKUP.
Γενικός τύπος
=VLOOKUP(τιμή_αναζήτησης, πίνακας_σειράς, ΜΑΤΣΙ (κεφαλίδα αναζήτησης, κεφαλίδες πίνακα, 0), 0)
Lookup_value: η τιμή αναζήτησης στην πρώτη στήλη του πίνακα_σειρά.
Table_array: το εύρος στο οποίο θέλετε να κάνετε αναζήτηση. Π.χ., Α2, Δ10.
Επικεφαλίδα αναζήτησης: την επικεφαλίδα που θέλετε να αναζητήσετε στις επικεφαλίδες του table_array.
Επικεφαλίδες πίνακα: Αναφορά των επικεφαλίδων στον πίνακα πίνακα. Π.χ. αν ο πίνακας είναι A2, D10 και επικεφαλίδες στο πάνω μέρος κάθε στήλης, τότε είναι A1: D1.
Έτσι, τώρα γνωρίζουμε τι χρειαζόμαστε για το δυναμικό col_index, ας ξεκαθαρίσουμε τα πάντα με ένα παράδειγμα.
Παράδειγμα δυναμικής VLOOKUP
Για αυτό το παράδειγμα, έχουμε αυτόν τον πίνακα που περιέχει δεδομένα μαθητών στο εύρος A4: E16.
Χρησιμοποιώντας τον αριθμό no και την επικεφαλίδα, θέλω να ανακτήσω δεδομένα από αυτόν τον πίνακα. Για αυτήν την περίπτωση, στο κελί Η4, θέλω να λάβω δεδομένα του αριθμού ρολού γραμμένα στο κελί G4 και της επικεφαλίδας στο Η3. Εάν αλλάξω την επικεφαλίδα, τα δεδομένα από το αντίστοιχο εύρος θα πρέπει να ανακτηθούν στο κελί Η4.
Γράψτε αυτόν τον τύπο στο κελί Η4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Δεδομένου ότι ο πίνακας πίνακα είναι B4: E16, ο πίνακας επικεφαλίδων μας γίνεται B3: E3.
Σημείωση: Εάν τα δεδομένα σας είναι καλά δομημένα, τότε οι επικεφαλίδες των στηλών θα έχουν τον ίδιο αριθμό στηλών και είναι η πρώτη σειρά του πίνακα.
Πως δουλεύει:
Έτσι, το κύριο μέρος είναι η αυτόματη αξιολόγηση του αριθμού ευρετηρίου της στήλης. Για να το κάνουμε αυτό, χρησιμοποιήσαμε τη συνάρτηση MATCH.
ΑΓΩΝΑΣ (H3, B3: E3,0): Δεδομένου ότι το H3 περιέχει "μαθητή", το MATCH θα επιστρέψει 2. Αν το H3 το είχε "Βαθμολογήσει", θα επέστρεφε 4 και ούτω καθεξής. Ο τύπος VLOOKUP θα έχει τελικά το col_index_num.
= VLOOKUP (G4, B4: E16,2,0)
Όπως γνωρίζουμε, η συνάρτηση MATCH επιστρέφει τον αριθμό ευρετηρίου μιας δεδομένης τιμής στο παρεχόμενο μονοδιάστατο εύρος. Ως εκ τούτου, το MATCH θα αναζητήσει οποιαδήποτε τιμή γραμμένη στο H3 στο εύρος B3: E3 και θα επιστρέψει τον αριθμό ευρετηρίου του.
Τώρα όποτε αλλάζετε την επικεφαλίδα στο H3, εάν είναι στις επικεφαλίδες, αυτός ο τύπος θα επιστρέψει μια τιμή από την αντίστοιχη στήλη. Διαφορετικά, θα έχετε σφάλμα #N/A.
VLOOKUP σε πολλές στήλες γρήγορα
Στο παραπάνω παράδειγμα, χρειαζόμασταν την απάντηση από μια τιμή στήλης. Αλλά τι γίνεται αν θέλετε να λάβετε πολλές στήλες ταυτόχρονα. Αν αντιγράψετε τον παραπάνω τύπο, θα επιστρέψει σφάλματα. Πρέπει να κάνουμε κάποιες μικρές αλλαγές σε αυτό για να το κάνουμε φορητό.
Χρήση Απόλυτων Αναφορών με το VLOOKUP
Γράψτε τον παρακάτω τύπο στο κελί Η2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Τώρα αντιγράψτε το H2 σε όλα τα κελιά της περιοχής H2: J6 για να το γεμίσετε με δεδομένα.
Πως δουλεύει:
Εδώ έχω δώσει απόλυτη αναφορά κάθε εύρους εκτός από τη γραμμή αναζήτησης για το VLOOKUP ($ G2) και στήλη στο lookup_value για MATCH (1 $ H).
$ G2: Αυτό θα επιτρέψει στη σειρά να αλλάξει για την τιμή αναζήτησης για τη λειτουργία VLOOKUP κατά την αντιγραφή προς τα κάτω, αλλά θα περιορίσει την αλλαγή της στήλης όταν αντιγράφεται στα δεξιά. Αυτό θα κάνει το VLOOKUP να αναζητήσει Id από τη στήλη G μόνο με τη σχετική γραμμή.
Ομοίως, H $ 1 θα επιτρέψει στη στήλη να αλλάξει όταν αντιγράφεται οριζόντια και θα περιορίσει τη σειρά όταν αντιγράφεται προς τα κάτω.
Χρήση ονομαστικών σειρών
Το παραπάνω παράδειγμα λειτουργεί καλά, αλλά είναι δύσκολο να διαβάσετε και να γράψετε αυτόν τον τύπο. Και αυτό δεν είναι καθόλου φορητό. Αυτό μπορεί να απλοποιηθεί χρησιμοποιώντας ονόματα εύρους.
Θα κάνουμε κάποια ονομασία εδώ πρώτα. Για αυτό το παράδειγμα, ονόμασα
$ B $ 2: $ E $ 14: ως Δεδομένα
$ B $ 1: $ E $ 1: ως επικεφαλίδες
H $ 1: Ονομάστε το ως επικεφαλίδα. Κάντε τις στήλες σχετικές. Για να το κάνετε αυτό, επιλέξτε H1. Πατήστε CTRL+F3, κάντε κλικ στο νέο, στην ενότητα Αναφορές σε αφαιρέστε το '$' από το μπροστινό μέρος του H.
$ G2: Ομοίως, ονομάστε το ως RollNo. Αυτός ο χρόνος καθιστά τη σειρά σχετική αφαιρώντας το '$' από το μπροστινό μέρος του 2.
Τώρα, όταν έχετε όλα τα ονόματα στο φύλλο, γράψτε αυτόν τον τύπο οπουδήποτε στο αρχείο excel. Θα παίρνει πάντα τη σωστή απάντηση.
= VLOOKUP (RollNo, Data, MATCH (Heading, Headings, 0), 0)
Βλέπετε, ο καθένας μπορεί να το διαβάσει και να το καταλάβει.
Έτσι, χρησιμοποιώντας αυτές τις μεθόδους, μπορείτε να κάνετε το col_index_num δυναμικό. Ενημερώστε με αν αυτό ήταν χρήσιμο στην παρακάτω ενότητα σχολίων.
Πώς να χρησιμοποιήσετε το tσυνάρτηση VLOOKUP στο Excel
Σχετική και απόλυτη αναφορά στο Excel
Με το όνομα Ranges In Excel
Πώς να κάνετε VLOOKUP από διαφορετικό φύλλο Excel
VLOOKUP Πολλαπλές τιμές
Δημοφιλή άρθρα
50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας : Προχωρήστε γρηγορότερα στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε το tσυνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.