Περιεχόμενα
- Αναζήτηση στο Excel με πολλαπλά κριτήρια
- Εξάγουμε τις τιμές 2η, 3η κ.λπ. χρησιμοποιώντας το VLOOKUP
- Ανακτήστε όλες τις επαναλήψεις της επιθυμητής τιμής
- XNUMXD αναζήτηση ανά γνωστή γραμμή και στήλη
- Χρήση πολλαπλών VLOOKUP σε έναν τύπο
- Δυναμική αντικατάσταση δεδομένων από διαφορετικούς πίνακες με χρήση VLOOKUP και INDIRECT
Στο δεύτερο μέρος του σεμιναρίου μας για τη συνάρτηση VPR (VLOOKUP) στο Excel, θα αναλύσουμε μερικά παραδείγματα που θα σας βοηθήσουν να κατευθύνετε όλη την ισχύ VPR για την επίλυση των πιο φιλόδοξων εργασιών του Excel. Τα παραδείγματα προϋποθέτουν ότι έχετε ήδη βασικές γνώσεις για το πώς λειτουργεί αυτή η δυνατότητα. Εάν όχι, μπορεί να σας ενδιαφέρει να ξεκινήσετε με το πρώτο μέρος αυτού του σεμιναρίου, το οποίο εξηγεί τη σύνταξη και τη βασική χρήση. VPR. Λοιπόν, ας ξεκινήσουμε.
Αναζήτηση στο Excel με πολλαπλά κριτήρια
Λειτουργία VPR στο Excel είναι ένα πραγματικά ισχυρό εργαλείο για την εκτέλεση αναζητήσεων για μια συγκεκριμένη τιμή σε μια βάση δεδομένων. Ωστόσο, υπάρχει ένας σημαντικός περιορισμός - η σύνταξή του σας επιτρέπει να αναζητήσετε μόνο μία τιμή. Τι γίνεται αν θέλετε να κάνετε αναζήτηση βάσει πολλαπλών συνθηκών; Θα βρείτε τη λύση παρακάτω.
Παράδειγμα 1: Αναζήτηση με 2 διαφορετικά κριτήρια
Ας υποθέσουμε ότι έχουμε μια λίστα με παραγγελίες και θέλουμε να βρούμε Ποσότητα εμπορευμάτων (Ποσ.), με βάση δύο κριτήρια – Ονομα Πελάτη (Πελάτης) и Название продукт (Προϊόν). Το θέμα περιπλέκεται από το γεγονός ότι καθένας από τους αγοραστές παρήγγειλε διάφορους τύπους αγαθών, όπως φαίνεται από τον παρακάτω πίνακα:
τακτική λειτουργία VPR δεν θα λειτουργήσει σε αυτό το σενάριο επειδή θα επιστρέψει την πρώτη τιμή που θα βρει που ταιριάζει με τη δεδομένη τιμή αναζήτησης. Για παράδειγμα, εάν θέλετε να μάθετε την ποσότητα ενός αντικειμένου Γλυκα'παραγγελθεί από τον αγοραστή Τζέρεμι Χιλ, γράψτε τον ακόλουθο τύπο:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– αυτός ο τύπος θα επιστρέψει το αποτέλεσμα 15που αντιστοιχεί στο προϊόν μήλα, γιατί είναι η πρώτη τιμή που ταιριάζει.
Υπάρχει μια απλή λύση - δημιουργήστε μια πρόσθετη στήλη στην οποία θα συνδυάσετε όλα τα επιθυμητά κριτήρια. Στο παράδειγμά μας, αυτές είναι οι στήλες Ονομα Πελάτη (Πελάτης) и Название продукт (Προϊόν). Μην ξεχνάτε ότι η συγχωνευμένη στήλη πρέπει πάντα να είναι η πιο αριστερή στήλη στο εύρος αναζήτησης, καθώς είναι η αριστερή στήλη που η συνάρτηση VPR κοιτάζει ψηλά όταν ψάχνει για μια τιμή.
Έτσι, προσθέτετε μια βοηθητική στήλη στον πίνακα και αντιγράφετε τον ακόλουθο τύπο σε όλα τα κελιά του: =B2&C2. Εάν θέλετε η συμβολοσειρά να είναι πιο ευανάγνωστη, μπορείτε να διαχωρίσετε τις συνδυασμένες τιμές με ένα κενό: =B2&» «&C2. Μετά από αυτό, μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Πού είναι το κελί B1 περιέχει τη συνενωμένη τιμή του ορίσματος lookup_value (lookup_value) και 4 - Διαφωνία col_index_num (column_number), δηλαδή ο αριθμός της στήλης που περιέχει τα προς ανάκτηση δεδομένα.
Παράδειγμα 2: VLOOKUP με δύο κριτήρια με τον πίνακα να προβάλλεται σε άλλο φύλλο
Εάν πρέπει να ενημερώσετε τον κύριο πίνακα (Κύριος πίνακας) προσθέτοντας δεδομένα από τον δεύτερο πίνακα (πίνακας αναζήτησης), ο οποίος βρίσκεται σε άλλο φύλλο ή σε άλλο βιβλίο εργασίας του Excel, τότε μπορείτε να συλλέξετε την επιθυμητή τιμή απευθείας στον τύπο που εισάγετε στο κεντρικό τραπέζι.
Όπως και στο προηγούμενο παράδειγμα, θα χρειαστείτε μια βοηθητική στήλη στον πίνακα Αναζήτηση με τις συνδυασμένες τιμές. Αυτή η στήλη πρέπει να είναι η πιο αριστερή στήλη στην περιοχή αναζήτησης.
Ο τύπος λοιπόν με VPR θα μπορούσε να είναι έτσι:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Εδώ, οι στήλες Β και Γ περιέχουν τα ονόματα πελατών και τα ονόματα προϊόντων, αντίστοιχα, και τον σύνδεσμο Παραγγελίες!$A&$2:$D$2 ορίζει έναν πίνακα για αναζήτηση σε άλλο φύλλο.
Για να κάνετε τον τύπο πιο ευανάγνωστο, μπορείτε να δώσετε ένα όνομα στο εύρος προβολής και, στη συνέχεια, ο τύπος θα φαίνεται πολύ πιο απλός:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Για να λειτουργήσει ο τύπος, οι τιμές στην πιο αριστερή στήλη του πίνακα που εξετάζετε πρέπει να συνδυαστούν με τον ίδιο ακριβώς τρόπο όπως στα κριτήρια αναζήτησης. Στο παραπάνω σχήμα, συνδυάσαμε τις τιμές u2bu2band τοποθετώντας ένα κενό μεταξύ τους, με τον ίδιο τρόπο που πρέπει να κάνετε στο πρώτο όρισμα της συνάρτησης (BXNUMX& "" & CXNUMX).
Θυμάμαι! Λειτουργία VPR περιορίζεται στους 255 χαρακτήρες, δεν μπορεί να αναζητήσει μια τιμή μεγαλύτερη από 255 χαρακτήρες. Λάβετε αυτό υπόψη και βεβαιωθείτε ότι το μήκος της επιθυμητής τιμής δεν υπερβαίνει αυτό το όριο.
Συμφωνώ ότι η προσθήκη μιας βοηθητικής στήλης δεν είναι η πιο κομψή και όχι πάντα αποδεκτή λύση. Μπορείτε να κάνετε το ίδιο χωρίς τη βοηθητική στήλη, αλλά αυτό θα απαιτούσε έναν πολύ πιο περίπλοκο τύπο με συνδυασμό συναρτήσεων ΔΕΊΚΤΗΣ (ΕΥΡΕΤΗΣ) και MATCH (ΠΕΡΙΣΣΟΤΕΡΑ ΕΚΤΙΘΕΤΑΙ).
Εξάγουμε τις τιμές 2η, 3η κ.λπ. χρησιμοποιώντας το VLOOKUP
Το γνωρίζετε ήδη VPR μπορεί να επιστρέψει μόνο μία τιμή που αντιστοιχεί, πιο συγκεκριμένα, την πρώτη που βρέθηκε. Τι γίνεται όμως αν αυτή η τιμή επαναληφθεί πολλές φορές στον πίνακα που προβλήθηκε και θέλετε να εξαγάγετε τη 2η ή την 3η από αυτές; Τι κι αν όλες οι αξίες; Το πρόβλημα φαίνεται περίπλοκο, αλλά η λύση υπάρχει!
Ας υποθέσουμε ότι μια στήλη του πίνακα περιέχει τα ονόματα των πελατών (Όνομα πελάτη) και η άλλη στήλη περιέχει τα προϊόντα (Προϊόν) που αγόρασαν. Ας προσπαθήσουμε να βρούμε το 2ο, το 3ο και το 4ο προϊόντα που αγοράστηκαν από έναν συγκεκριμένο πελάτη.
Ο ευκολότερος τρόπος είναι να προσθέσετε μια βοηθητική στήλη πριν από τη στήλη Όνομα πελάτη και συμπληρώστε το με ονόματα πελατών με τον αριθμό επανάληψης κάθε ονόματος, για παράδειγμα, John Doe1, John Doe2 κλπ. Θα κάνουμε το κόλπο με την αρίθμηση χρησιμοποιώντας τη συνάρτηση COUNTIF (COUNTIF), δεδομένου ότι τα ονόματα πελατών βρίσκονται στη στήλη Β:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Μετά από αυτό μπορείτε να χρησιμοποιήσετε την κανονική λειτουργία VPRγια να βρείτε την απαιτούμενη παραγγελία. Για παράδειγμα:
- Εύρεση 2-ου αντικείμενο παραγγελίας από τον πελάτη Νταν Μπράουν:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Εύρεση 3-ου αντικείμενο παραγγελίας από τον πελάτη Νταν Μπράουν:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Στην πραγματικότητα, μπορείτε να εισαγάγετε μια αναφορά κελιού ως τιμή αναζήτησης αντί για κείμενο, όπως φαίνεται στην παρακάτω εικόνα:
Αν ψάχνετε μόνο για 2-e επανάληψη, μπορείτε να το κάνετε χωρίς τη βοηθητική στήλη δημιουργώντας έναν πιο περίπλοκο τύπο:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Σε αυτόν τον τύπο:
- $ F $ 2 – ένα κελί που περιέχει το όνομα του αγοραστή (είναι αμετάβλητο, σημειώστε – ο σύνδεσμος είναι απόλυτος).
- $ B $ – στήλη Όνομα πελάτη;
- Table4 – Το τραπέζι σας (αυτό το μέρος μπορεί επίσης να είναι μια κανονική σειρά)
- C16 $ – το τελικό κελί του πίνακα ή του εύρους σας.
Αυτός ο τύπος βρίσκει μόνο τη δεύτερη τιμή που ταιριάζει. Εάν πρέπει να εξαγάγετε τις υπόλοιπες επαναλήψεις, χρησιμοποιήστε την προηγούμενη λύση.
Εάν χρειάζεστε μια λίστα με όλες τις αντιστοιχίσεις - η συνάρτηση VPR Αυτό δεν είναι βοηθητικό, αφού επιστρέφει μόνο μία τιμή κάθε φορά – περίοδο. Αλλά το Excel έχει μια λειτουργία ΔΕΊΚΤΗΣ (INDEX), το οποίο μπορεί εύκολα να αντιμετωπίσει αυτό το έργο. Πώς θα μοιάζει ένας τέτοιος τύπος, θα μάθετε στο παρακάτω παράδειγμα.
Ανακτήστε όλες τις επαναλήψεις της επιθυμητής τιμής
Οπως αναφέρθηκε προηγουμένως VPR δεν είναι δυνατή η εξαγωγή όλων των διπλότυπων τιμών από τη σαρωμένη περιοχή. Για να το κάνετε αυτό, χρειάζεστε έναν ελαφρώς πιο περίπλοκο τύπο, που αποτελείται από πολλές συναρτήσεις του Excel, όπως π.χ ΔΕΊΚΤΗΣ (ΔΕΙΚΤΗΣ), ΜΙΚΡΌ (ΜΙΚΡΟ) και ΣΕΙΡΑ (ΓΡΑΜΜΗ)
Για παράδειγμα, ο παρακάτω τύπος βρίσκει όλες τις επαναλήψεις της τιμής από το κελί F2 στην περιοχή B2:B16 και επιστρέφει το αποτέλεσμα από τις ίδιες σειρές στη στήλη C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Εισαγάγετε αυτόν τον τύπο πίνακα σε πολλά γειτονικά κελιά, όπως τα κελιά F4: F8όπως φαίνεται στο παρακάτω σχήμα. Ο αριθμός των κελιών πρέπει να είναι ίσος ή μεγαλύτερος από τον μέγιστο δυνατό αριθμό επαναλήψεων της τιμής που αναζητήθηκε. Μην ξεχάσετε να κάνετε κλικ Ctrl + Shift + Εισαγωγήγια να εισάγετε σωστά τον τύπο του πίνακα.
Αν σας ενδιαφέρει να κατανοήσετε πώς λειτουργεί, ας βουτήξουμε λίγο στις λεπτομέρειες του τύπου:
1 Μέρος:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – συγκρίνετε την τιμή στο κελί F2 με καθεμία από τις τιμές στην περιοχή B2:B16. Αν βρεθεί αντιστοιχία, τότε η έκφραση STRING(C2:C16)-1 επιστρέφει τον αριθμό της αντίστοιχης γραμμής (τιμή -1 σας επιτρέπει να μην συμπεριλάβετε τη γραμμή κεφαλίδας). Εάν δεν υπάρχουν αντιστοιχίες, η συνάρτηση IF Το (IF) επιστρέφει μια κενή συμβολοσειρά.
Αποτέλεσμα λειτουργίας IF (ΑΝ) θα υπάρχει ένας τέτοιος οριζόντιος πίνακας: {1,"",3,"",5,"","","","","","",12,"","",""}
2 Μέρος:
ROW()-3
СТРОКА()-3
Εδώ η συνάρτηση ΣΕΙΡΑ Το (LINE) λειτουργεί ως πρόσθετος μετρητής. Εφόσον ο τύπος αντιγράφεται στα κελιά F4:F9, αφαιρούμε τον αριθμό 3 από το αποτέλεσμα της συνάρτησης για να πάρετε τιμή 1 στο κελί F4 (γραμμή 4, αφαιρέστε 3) για να πάρετε 2 στο κελί F5 (γραμμή 5, αφαιρέστε 3) και ούτω καθεξής.
3 Μέρος:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Λειτουργία ΜΙΚΡΌ (ΜΙΚΡΟ) επιστρέφει ν-ω τη μικρότερη τιμή στον πίνακα δεδομένων. Στην περίπτωσή μας, ποια θέση (από τη μικρότερη) να επιστρέψουμε καθορίζεται από τη συνάρτηση ΣΕΙΡΑ (LINE) (βλ. Μέρος 2). Έτσι, για ένα κελί F4 λειτουργία SMALL({array},1) Επιστροφές 1-ου (μικρότερο) στοιχείο πίνακα, π.χ 1. Για κελί F5 Επιστροφές 2-ου το μικρότερο στοιχείο του πίνακα, δηλαδή 3Κ.λπ.
4 Μέρος:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Λειτουργία ΔΕΊΚΤΗΣ (INDEX) απλώς επιστρέφει την τιμή ενός συγκεκριμένου κελιού σε έναν πίνακα Γ2: Γ16. Για κελί F4 λειτουργία INDEX ($C$2:$C$16) θα επιστρέψει μήλαΓια F5 λειτουργία INDEX ($C$2:$C$16) θα επιστρέψει Γλυκα' και ούτω καθεξής.
5 Μέρος:
IFERROR()
ЕСЛИОШИБКА()
Τέλος, βάζουμε τον τύπο μέσα στη συνάρτηση ΑΦΑΝΙΣΤΗΡΙΟ (IFERROR), γιατί είναι απίθανο να μείνετε ευχαριστημένοι με το μήνυμα σφάλματος #ΑΤ (# N/A) εάν ο αριθμός των κελιών στα οποία αντιγράφεται ο τύπος είναι μικρότερος από τον αριθμό των διπλότυπων τιμών στο εύρος που προβάλλεται.
XNUMXD αναζήτηση ανά γνωστή γραμμή και στήλη
Η εκτέλεση μιας αναζήτησης XNUMXD στο Excel περιλαμβάνει την αναζήτηση μιας τιμής από έναν γνωστό αριθμό γραμμής και στήλης. Με άλλα λόγια, εξάγετε την τιμή του κελιού στη διασταύρωση μιας συγκεκριμένης γραμμής και στήλης.
Λοιπόν, ας στραφούμε στον πίνακα μας και ας γράψουμε έναν τύπο με μια συνάρτηση VPR, το οποίο θα βρει πληροφορίες για το κόστος των λεμονιών που πωλούνται τον Μάρτιο.
Υπάρχουν διάφοροι τρόποι για να πραγματοποιήσετε μια αναζήτηση XNUMXD. Δείτε τις επιλογές και επιλέξτε αυτή που σας ταιριάζει καλύτερα.
Λειτουργίες VLOOKUP και MATCH
Μπορείτε να χρησιμοποιήσετε μια δέσμη λειτουργιών VPR (VLOOKUP) και ΠΙΟ ΕΚΤΙΘΕΤΑ (MATCH) για να βρείτε την τιμή στη διασταύρωση των πεδίων Название продукт (χορδή) και Μήνας (στήλη) του εν λόγω πίνακα:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Ο παραπάνω τύπος είναι μια κανονική συνάρτηση VPR, το οποίο αναζητά μια ακριβή αντιστοίχιση της τιμής "Lemons" στα κελιά A2 έως A9. Αλλά επειδή δεν γνωρίζετε σε ποια στήλη βρίσκονται οι εκπτώσεις Μαρτίου, δεν θα μπορείτε να ορίσετε τον αριθμό στήλης για το τρίτο όρισμα συνάρτησης. VPR. Αντίθετα, χρησιμοποιείται η συνάρτηση ΠΙΟ ΕΚΤΙΘΕΤΑγια να ορίσετε αυτή τη στήλη.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Μεταφρασμένος στην ανθρώπινη γλώσσα, αυτός ο τύπος σημαίνει:
- Αναζητούμε τους χαρακτήρες «Mar» – επιχείρημα lookup_value (τιμή_αναζήτησης);
- Ψάχνοντας σε κελιά από A1 έως I1 – όρισμα συστοιχία αναζήτησης (lookup_array);
- Επιστροφή ακριβούς αντιστοίχισης – επιχείρημα match_type (τύπος_ταιριάσματος).
Χρησιμοποιώντας 0 στο τρίτο όρισμα, λες συναρτήσεις ΠΙΟ ΕΚΤΙΘΕΤΑ αναζητήστε την πρώτη τιμή που ταιριάζει ακριβώς με την τιμή που αναζητάτε. Αυτό ισοδυναμεί με την τιμή ΨΕΥΔΗΣ (FALSE) για το τέταρτο όρισμα VPR.
Αυτός είναι ο τρόπος με τον οποίο μπορείτε να δημιουργήσετε έναν τύπο αμφίδρομης αναζήτησης στο Excel, γνωστό και ως αναζήτηση δύο διαστάσεων ή αμφίδρομη αναζήτηση.
Λειτουργία SUMPRODUCT
Λειτουργία ΑΝΤΙΠΡΟΣΩΠΟΣ (SUMPRODUCT) επιστρέφει το άθροισμα των γινομένων των επιλεγμένων πινάκων:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Λειτουργίες INDEX και MATCH
Στο επόμενο άρθρο θα εξηγήσω λεπτομερώς αυτές τις λειτουργίες, οπότε προς το παρόν μπορείτε απλώς να αντιγράψετε αυτόν τον τύπο:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Ονομασμένες περιοχές και ο τελεστής τομής
Εάν δεν είστε σε όλους αυτούς τους πολύπλοκους τύπους του Excel, μπορεί να σας αρέσει αυτός ο οπτικός και αξέχαστος τρόπος:
- Επιλέξτε τον πίνακα, ανοίξτε την καρτέλα ΜΑΘΗΜΑΤΙΚΟΙ τυποι (Τύποι) και κάντε κλικ Δημιουργία από Επιλογή (Δημιουργία από επιλογή).
- Επιλέξτε τα πλαίσια Κορυφαία σειρά (στη γραμμή παραπάνω) και Αριστερή στήλη (στη στήλη στα αριστερά). Το Microsoft Excel θα εκχωρήσει ονόματα στις περιοχές από τις τιμές στην επάνω σειρά και την αριστερή στήλη του υπολογιστικού φύλλου σας. Τώρα μπορείτε να κάνετε αναζήτηση χρησιμοποιώντας αυτά τα ονόματα απευθείας χωρίς να δημιουργείτε τύπους.
- Σε οποιοδήποτε κενό κελί, γράψτε =όνομα_σειράς_όνομα_στήλης, για παράδειγμα όπως αυτό:
=Λεμόνια Μαρ
… ή αντιστρόφως:
=Mar Lemons
Θυμηθείτε ότι τα ονόματα των γραμμών και των στηλών πρέπει να χωρίζονται με ένα κενό, το οποίο σε αυτήν την περίπτωση λειτουργεί όπως ο τελεστής τομής.
Όταν εισάγετε ένα όνομα, το Microsoft Excel θα εμφανίσει μια επεξήγηση εργαλείου με μια λίστα ονομάτων που ταιριάζουν, ακριβώς όπως όταν εισάγετε έναν τύπο.
- Τύπος εισάγετε και ελέγξτε το αποτέλεσμα
Γενικά, όποια από τις παραπάνω μεθόδους και αν επιλέξετε, το αποτέλεσμα μιας δισδιάστατης αναζήτησης θα είναι το ίδιο:
Χρήση πολλαπλών VLOOKUP σε έναν τύπο
Συμβαίνει ο κύριος πίνακας και ο πίνακας αναζήτησης να μην έχουν μία κοινή στήλη και αυτό σας εμποδίζει να χρησιμοποιήσετε τη συνήθη συνάρτηση VPR. Ωστόσο, υπάρχει ένας άλλος πίνακας που δεν περιέχει τις πληροφορίες που μας ενδιαφέρουν, αλλά έχει κοινή στήλη με τον κύριο πίνακα και τον πίνακα αναζήτησης.
Ας ρίξουμε μια ματιά στο παρακάτω παράδειγμα. Έχουμε έναν Κύριο πίνακα με μια στήλη SKU (νέο), όπου θέλετε να προσθέσετε μια στήλη με τις αντίστοιχες τιμές από άλλο πίνακα. Επιπλέον, έχουμε 2 πίνακες αναζήτησης. Το πρώτο (πίνακας αναζήτησης 1) περιέχει ενημερωμένους αριθμούς SKU (νέο) και ονόματα προϊόντων, και το δεύτερο (Πίνακας αναζήτησης 2) – ονόματα προϊόντων και παλιοί αριθμοί SKU (παλιό).
Για να προσθέσετε τιμές από τον δεύτερο πίνακα αναζήτησης στον κύριο πίνακα, πρέπει να εκτελέσετε μια ενέργεια γνωστή ως διπλή VPR ή φωλιασμένο VPR.
- Γράψτε μια συνάρτηση VPR, το οποίο βρίσκει το όνομα του προϊόντος στον πίνακα Πίνακας αναζήτησης 1χρησιμοποιώντας Κωδικός, ως επιθυμητή τιμή:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Εδώ New_SKU – ονομαζόμενη περιοχή $A:$B στο τραπέζι Πίνακας αναζήτησης 1, 2 – αυτή είναι η στήλη Β, η οποία περιέχει τα ονόματα των εμπορευμάτων (δείτε την παραπάνω εικόνα)
- Γράψτε έναν τύπο για να εισαγάγετε τιμές από έναν πίνακα Πίνακας αναζήτησης 2 με βάση γνωστές ονομασίες προϊόντων. Για να το κάνετε αυτό, επικολλήστε τον τύπο που δημιουργήσατε νωρίτερα ως τιμή αναζήτησης για τη νέα συνάρτηση VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Εδώ Τιμή – ονομαζόμενη περιοχή $ A: $ C στο τραπέζι Πίνακας αναζήτησης 2, 3 είναι η στήλη Γ που περιέχει τιμές.
Το παρακάτω σχήμα δείχνει το αποτέλεσμα που επιστρέφεται από τον τύπο που δημιουργήσαμε:
Δυναμική αντικατάσταση δεδομένων από διαφορετικούς πίνακες με χρήση VLOOKUP και INDIRECT
Αρχικά, ας διευκρινίσουμε τι εννοούμε με την έκφραση «Δυναμική αντικατάσταση δεδομένων από διαφορετικούς πίνακες» για να βεβαιωθούμε ότι κατανοούμε ο ένας τον άλλον σωστά.
Υπάρχουν περιπτώσεις όπου υπάρχουν πολλά φύλλα με δεδομένα της ίδιας μορφής και είναι απαραίτητο να εξαχθούν οι απαραίτητες πληροφορίες από ένα συγκεκριμένο φύλλο, ανάλογα με την τιμή που έχει εισαχθεί σε ένα δεδομένο κελί. Νομίζω ότι είναι πιο εύκολο να το εξηγήσω με ένα παράδειγμα.
Φανταστείτε ότι έχετε αναφορές πωλήσεων για πολλές περιοχές με τα ίδια προϊόντα και την ίδια μορφή. Θέλετε να βρείτε στοιχεία πωλήσεων για μια συγκεκριμένη περιοχή:
Εάν έχετε μόνο δύο τέτοιες αναφορές, τότε μπορείτε να χρησιμοποιήσετε έναν επαίσχυντα απλό τύπο με συναρτήσεις VPR и IF (IF) για να επιλέξετε την επιθυμητή αναφορά για αναζήτηση:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Που:
- $D$2 είναι ένα κελί που περιέχει το όνομα του προϊόντος. Σημειώστε ότι εδώ χρησιμοποιούμε απόλυτες αναφορές για να αποφύγουμε την αλλαγή της τιμής αναζήτησης κατά την αντιγραφή του τύπου σε άλλα κελιά.
- Δ3 $ είναι ένα κελί με το όνομα της περιοχής. Χρησιμοποιούμε μια απόλυτη αναφορά στήλης και μια σχετική αναφορά γραμμής επειδή σκοπεύουμε να αντιγράψουμε τον τύπο σε άλλα κελιά της ίδιας στήλης.
- FL_Sales и CA_Sales – τα ονόματα των πινάκων (ή των ονομασμένων σειρών) που περιέχουν τις αντίστοιχες αναφορές πωλήσεων. Μπορείτε, φυσικά, να χρησιμοποιήσετε τα συνήθη ονόματα φύλλων και αναφορές εύρους κελιών, για παράδειγμα 'Φύλλο FL'!$A$3:$B$10, αλλά οι επώνυμες σειρές είναι πολύ πιο βολικές.
Ωστόσο, όταν υπάρχουν πολλοί τέτοιοι πίνακες, η συνάρτηση IF δεν είναι η καλύτερη λύση. Αντίθετα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση ΕΜΜΕΣΟΣ (ΕΜΜΕΣΗ) για να επιστρέψετε το επιθυμητό εύρος αναζήτησης.
Όπως ίσως γνωρίζετε, η λειτουργία ΕΜΜΕΣΟΣ χρησιμοποιείται για την επιστροφή ενός συνδέσμου που δίνεται από μια συμβολοσειρά κειμένου, που είναι ακριβώς αυτό που χρειαζόμαστε τώρα. Αντικαταστήστε λοιπόν με τόλμη στον παραπάνω τύπο την έκφραση με τη συνάρτηση IF για σύνδεση με συνάρτηση ΕΜΜΕΣΟΣ. Εδώ είναι ένας συνδυασμός VPR и ΕΜΜΕΣΟΣ λειτουργεί υπέροχα με:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Που:
- $D$2 – αυτό είναι ένα κελί με το όνομα του προϊόντος, είναι αμετάβλητο λόγω του απόλυτου συνδέσμου.
- Δ3 $ είναι το κελί που περιέχει το πρώτο μέρος του ονόματος της περιοχής. Στο παράδειγμά μας αυτό FL.
- _Εκπτώσεις – το κοινό μέρος του ονόματος όλων των ονομαζόμενων περιοχών ή πινάκων. Όταν συνδυάζεται με την τιμή στο κελί D3, σχηματίζει το πλήρως αναγνωρισμένο όνομα της απαιτούμενης περιοχής. Ακολουθούν ορισμένες λεπτομέρειες για όσους είναι νέοι στη λειτουργία ΕΜΜΕΣΟΣ.
Πώς λειτουργούν το INDIRECT και το VLOOKUP
Αρχικά, επιτρέψτε μου να σας υπενθυμίσω τη σύνταξη της συνάρτησης ΕΜΜΕΣΟΣ (ΕΜΜΕΣΟΣ):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Το πρώτο όρισμα μπορεί να είναι μια αναφορά κελιού (στυλ A1 ή R1C1), ένα όνομα περιοχής ή μια συμβολοσειρά κειμένου. Το δεύτερο όρισμα καθορίζει το στυλ σύνδεσης που περιέχεται στο πρώτο όρισμα:
- A1αν το επιχείρημα είναι ΑΛΗΘΙΝΟΣ ΚΩΔΙΚΟΣ (TRUE) ή δεν προσδιορίζεται.
- R1C1, Εάν FAS Ε (ΨΕΥΔΗΣ).
Στην περίπτωσή μας, ο σύνδεσμος έχει το στυλ A1, έτσι μπορείτε να αφήσετε έξω το δεύτερο επιχείρημα και να εστιάσετε στο πρώτο.
Ας επιστρέψουμε λοιπόν στις αναφορές πωλήσεών μας. Αν θυμάστε, τότε κάθε αναφορά είναι ένας ξεχωριστός πίνακας που βρίσκεται σε ξεχωριστό φύλλο. Για να λειτουργήσει σωστά ο τύπος, πρέπει να ονομάσετε τους πίνακες (ή τις περιοχές σας) και όλα τα ονόματα πρέπει να έχουν ένα κοινό μέρος. Για παράδειγμα, όπως αυτό: CA_Sales, FL_Sales, TX_Sales και ούτω καθεξής. Όπως μπορείτε να δείτε, το "_Sales" υπάρχει σε όλα τα ονόματα.
Λειτουργία ΕΜΜΕΣΟΣ συνδέει την τιμή στη στήλη D και τη συμβολοσειρά κειμένου "_Sales", λέγοντας έτσι VPR σε ποιον πίνακα να αναζητήσετε. Εάν το κελί D3 περιέχει την τιμή "FL", ο τύπος θα πραγματοποιήσει αναζήτηση στον πίνακα FL_Sales, εάν "CA" - στον πίνακα CA_Sales και ούτω καθεξής.
Το αποτέλεσμα των λειτουργιών VPR и ΕΜΜΕΣΟΣ θα είναι το εξής:
Εάν τα δεδομένα βρίσκονται σε διαφορετικά βιβλία Excel, τότε πρέπει να προσθέσετε το όνομα του βιβλίου πριν από το ονομαζόμενο εύρος, για παράδειγμα:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Εάν η συνάρτηση ΕΜΜΕΣΟΣ αναφέρεται σε άλλο βιβλίο εργασίας, αυτό το βιβλίο εργασίας πρέπει να είναι ανοιχτό. Εάν είναι κλειστή, η συνάρτηση θα αναφέρει ένα σφάλμα. # ΑΝΑΦ! (#SSYL!).