Συμβουλή
Δοκιμάστε να χρησιμοποιήσετε τις νέες συναρτήσεις XLOOKUP και XMATCH , βελτιωμένες εκδόσεις των συναρτήσεων που περιγράφονται σε αυτό το άρθρο. Αυτές οι νέες συναρτήσεις λειτουργούν προς οποιαδήποτε κατεύθυνση και επιστρέφουν ακριβείς αντιστοιχίσεις από προεπιλογή, καθιστώντας τις ευκολότερες και πιο εύχρηστες από τις προγενέστερες συναρτήσεις.
Ας υποθέσουμε ότι έχετε μια λίστα με αριθμούς τοποθεσίας γραφείων και πρέπει να ξέρετε ποιοι υπάλληλοι είναι σε κάθε γραφείο. Το υπολογιστικό φύλλο είναι πάρα πολύ μεγάλο, γι' αυτό μπορεί να νομίζετε ότι είναι δύσκολη εργασία. Στην πραγματικότητα είναι πολύ εύκολο να το κάνετε με μια συνάρτηση αναζήτησης.
Οι συναρτήσεις VLOOKUP και HLOOKUP , μαζί με τις συναρτήσεις INDEX και MATCH, είναι από τις πιο χρήσιμες συναρτήσεις στο Excel.
Σημείωση
Η δυνατότητα του "Οδηγού αναζήτησης" δεν είναι πλέον διαθέσιμη στο Excel.
Ακολουθεί ένα παράδειγμα χρήσης της συνάρτησης VLOOKUP.
=VLOOKUP(B2;C2:E7;3;TRUE)
Σε αυτό το παράδειγμα, το B2 είναι το πρώτο όρισμα—ένα στοιχείο δεδομένων που χρειάζεται η συνάρτηση για να λειτουργήσει. Για τη συνάρτηση VLOOKUP, αυτό το πρώτο όρισμα είναι η τιμή που θέλετε να βρείτε. Το όρισμα αυτό μπορεί να είναι μια αναφορά κελιού ή μια σταθερή τιμή, όπως "Γεωργίου" ή 21.000. Το δεύτερο όρισμα είναι η περιοχή κελιών, C2-:E7, στην οποία θα αναζητήσετε την τιμή που θέλετε να βρείτε. Το τρίτο όρισμα είναι η στήλη σε αυτή την περιοχή κελιών που περιέχει την τιμή που αναζητάτε.
Το τέταρτο όρισμα είναι προαιρετικό. Πληκτρολογήστε TRUE ή FALSE. Εάν εισαγάγετε την τιμή TRUE ή αφήσετε κενό το όρισμα, η συνάρτηση επιστρέφει μια τιμή που προσεγγίζει την τιμή που προσδιορίζει το πρώτο όρισμα. Εάν εισαγάγετε FALSE, η συνάρτηση θα εμφανίσει την τιμή που προσδιορίζει το πρώτο όρισμα. Με άλλα λόγια, εάν αφήσετε κενό το τέταρτο όρισμα ή εισαγάγετε την τιμή TRUE, παρέχετε μεγαλύτερη ευελιξία.
Αυτό το παράδειγμα δείχνει τον τρόπο λειτουργίας της συνάρτησης. Όταν εισάγετε μια τιμή στο κελί B2 (το πρώτο όρισμα), η συνάρτηση VLOOKUP αναζητά τα κελιά στην περιοχή C2:E7 (2ο όρισμα) και επιστρέφει την πλησιέστερη κατά προσέγγιση τιμή από την τρίτη στήλη στην περιοχή, τη στήλη E (3ο όρισμα).
Το τέταρτο όρισμα είναι κενό, έτσι η συνάρτηση επιστρέφει μια κατά προσέγγιση τιμή. Αν το τέταρτο όρισμα δεν ήταν κενό, τότε θα έπρεπε να εισαγάγετε μία από τις τιμές στη στήλη C ή D προκειμένου η συνάρτηση να επιστρέψει κάποιο αποτέλεσμα.
Όταν είστε εξοικειωμένοι με τη συνάρτηση VLOOKUP, η συνάρτηση HLOOKUP είναι εξίσου εύχρηστη. Εισάγετε τα ίδια ορίσματα, αλλά η αναζήτηση πραγματοποιείται σε γραμμές αντί για στήλες.
Χρήση των συναρτήσεων INDEX και MATCH αντί της συνάρτησης VLOOKUP
Υπάρχουν ορισμένοι περιορισμοί στη χρήση της συνάρτησης VLOOKUP—η συνάρτηση VLOOKUP μπορεί να αναζητήσει μια τιμή μόνο από τα αριστερά προς τα δεξιά. Αυτό σημαίνει ότι η στήλη που περιέχει την τιμή που αναζητάτε πρέπει να βρίσκεται πάντοτε στα αριστερά της στήλης που περιέχει την τιμή επιστροφής. Εάν το υπολογιστικό φύλλο σας δεν έχει δημιουργηθεί με αυτόν τον τρόπο, μην χρησιμοποιείτε τη συνάρτηση VLOOKUP. Αντί για αυτό, χρησιμοποιήστε το συνδυασμό των συναρτήσεων INDEX και MATCH.
Αυτό το παράδειγμα δείχνει μια μικρή λίστα όπου η τιμή με βάση την οποία θέλουμε να αναζητήσουμε, Σικάγο, δεν βρίσκεται στην πιο αριστερή στήλη. Επομένως, δεν μπορούμε να χρησιμοποιήσουμε τη συνάρτηση VLOOKUP. Αντί για αυτό, θα χρησιμοποιήσουμε τη συνάρτηση MATCH για να βρούμε το Σικάγο στην περιοχή B1:B11. Βρίσκεται στη γραμμή 4. Στη συνέχεια, η συνάρτηση INDEX χρησιμοποιεί αυτή την τιμή ως όρισμα αναζήτησης και βρίσκει τον πληθυσμό για το Σικάγο στην 4η στήλη (στήλη D). Ο τύπος που χρησιμοποιείται εμφανίζεται στο κελί A14.
Δοκιμάστε το
Εάν θέλετε να πειραματιστείτε με συναρτήσεις αναζήτησης πριν τις δοκιμάσετε με τα δικά σας δεδομένα, δείτε μερικά δείγματα δεδομένων.
Παράδειγμα VLOOKUP στην εργασία
Αντιγράψτε τα παρακάτω δεδομένα σε ένα κενό υπολογιστικό φύλλο.
Συμβουλή
Πριν επικολλήσετε τα δεδομένα στο Excel, ορίστε τα πλάτη των στηλών για τις στήλες A έως C στα 250 pixel και κάντε κλικ στην επιλογή Αναδίπλωση κειμένου (Κεντρική καρτέλα, ομάδα Στοίχιση ).
| Πυκνότητα | Ιξώδες | Θερμοκρασία |
|---|---|---|
| 0,457 | 3,55 | 500 |
| 0,525 | 3,25 | 400 |
| 0,606 | 2,93 | 300 |
| 0,675 | 2,75 | 250 |
| 0,746 | 2,57 | 200 |
| 0,835 | 2,38 | 150 |
| 0,946 | 2,17 | 100 |
| 1,09 | 1,95 | 50 |
| 1,29 | 1,71 | 0 |
| Τύπος | Περιγραφή | Αποτέλεσμα |
| =VLOOKUP(1;A2:C10;2) | Χρησιμοποιώντας μια κατά προσέγγιση αντιστοίχιση, πραγματοποιεί αναζήτηση για την τιμή 1 στη στήλη A, βρίσκει τη μεγαλύτερη τιμή που είναι μικρότερη από ή ίση με 1 στη στήλη A, που είναι 0,946, και έπειτα επιστρέφει την τιμή από τη στήλη B στην ίδια γραμμή. | 2,17 |
| =VLOOKUP(1;A2:C10;3;TRUE) | Χρησιμοποιώντας μια κατά προσέγγιση αντιστοίχιση, πραγματοποιεί αναζήτηση για την τιμή 1 στη στήλη A, βρίσκει τη μεγαλύτερη τιμή που είναι μικρότερη από ή ίση με 1 στη στήλη A, που είναι 0,946, και έπειτα επιστρέφει την τιμή από τη στήλη C στην ίδια γραμμή. | 100 |
| =VLOOKUP(0,7;A2:C10;3;FALSE) | Χρησιμοποιώντας μια ακριβή αντιστοίχιση, αναζητεί την τιμή 0,7 στη στήλη A. Επειδή δεν υπάρχει ακριβής τιμή στη στήλη A, επιστρέφει ένα σφάλμα. | #Δ/Υ |
| =VLOOKUP(0,1;A2:C10;2;TRUE) | Χρησιμοποιώντας μια ακριβή αντιστοίχιση, αναζητεί την τιμή 0,1 στη στήλη A. Επειδή το 0,1 είναι μικρότερο από τη μικρότερη τιμή στη στήλη A, επιστρέφει ένα σφάλμα. | #Δ/Υ |
| =VLOOKUP(2;A2:C10;2;TRUE) | Χρησιμοποιώντας μια κατά προσέγγιση αντιστοίχιση, πραγματοποιεί αναζήτηση για την τιμή 2 στη στήλη A, βρίσκει τη μεγαλύτερη τιμή που είναι μικρότερη από ή ίση με 2 στη στήλη A, που είναι 1,29, και έπειτα επιστρέφει την τιμή από τη στήλη B στην ίδια γραμμή. | 1,71 |
Παράδειγμα HLOOKUP
Αντιγράψτε όλα τα κελιά σε αυτόν τον πίνακα και επικολλήστε τον στο κελί A1 σε ένα κενό φύλλο εργασίας του Excel.
Συμβουλή
Πριν επικολλήσετε τα δεδομένα στο Excel, ορίστε τα πλάτη των στηλών για τις στήλες A έως C στα 250 pixel και κάντε κλικ στην επιλογή Αναδίπλωση κειμένου (Κεντρική καρτέλα, ομάδα Στοίχιση ).
| Άξονες | Ρουλεμάν | Μπουλόνια |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Τύπος | Περιγραφή | Αποτέλεσμα |
| =HLOOKUP("Άξονες τροχού";A1:C4;2;TRUE) | Αναζητά την τιμή "Άξονες τροχού" στη γραμμή 1 και επιστρέφει την τιμή της γραμμής 2 που βρίσκεται στην ίδια στήλη (στήλη Α). | 4 |
| =HLOOKUP("Ρουλεμάν";A1:C4;3;FALSE) | Αναζητά την τιμή "Ρουλεμάν" στη γραμμή 1 και επιστρέφει την τιμή της γραμμής 3 που βρίσκεται στην ίδια στήλη (στήλη Β). | 7 |
| =HLOOKUP("Ρ"; A1:C4; 3; TRUE) | Αναζητά το γράμμα "Ρ" στη γραμμή 1 και επιστρέφει την τιμή της γραμμής 3 που βρίσκεται στην ίδια στήλη. Επειδή δεν υπάρχει ακριβής αντιστοιχία για το γράμμα "Ρ", χρησιμοποιείται η μεγαλύτερη τιμή της γραμμής 1 που είναι μικρότερη από το "Ρ": η τιμή "Άξονες τροχού" στη στήλη A. | 5 |
| =HLOOKUP("Παξιμάδια"; A1:C4; 4) | Αναζητά την τιμή "Παξιμάδια" στη γραμμή 1 και επιστρέφει την τιμή της γραμμής 4 που βρίσκεται στην ίδια στήλη (στήλη C). | 11 |
| =HLOOKUP(3; {1;2;3\"α";"β";"γ";"δ";"ε";"ζ"};2;TRUE) | Αναζητά τον αριθμό 3 στη σταθερά πίνακα τριών γραμμών και επιστρέφει την τιμή από τη γραμμή 2 στην ίδια στήλη (στη συγκεκριμένη περίπτωση, στην τρίτη στήλη). Υπάρχουν τρεις γραμμές τιμών στη σταθερά πίνακα και κάθε γραμμή διαχωρίζεται με ερωτηματικό (;). Επειδή το "γ" υπάρχει στη γραμμή 2 και στην ίδια στήλη ως 3, επιστρέφεται η τιμή "γ". | γ |
Παραδείγματα συναρτήσεων INDEX και MATCH
Αυτό το τελευταίο παράδειγμα χρησιμοποιεί τις συναρτήσεις INDEX και MATCH μαζί για να επιστρέψει τον παλαιότερο αριθμό τιμολογίου και την αντίστοιχη ημερομηνία για κάθε μία από τις πέντε πόλεις. Επειδή η ημερομηνία επιστρέφεται ως αριθμός, χρησιμοποιούμε τη συνάρτηση TEXT για να τον μορφοποιήσουμε ως ημερομηνία. Η συνάρτηση INDEX χρησιμοποιεί το αποτέλεσμα της συνάρτησης MATCH ως όρισμα. Ο συνδυασμός των συναρτήσεων INDEX και MATCH χρησιμοποιείται δύο φορές σε κάθε τύπο – πρώτα, για να επιστρέψει τον αριθμό τιμολογίου και, στη συνέχεια, για να επιστρέψει την ημερομηνία.
Αντιγράψτε όλα τα κελιά σε αυτόν τον πίνακα και επικολλήστε τον στο κελί A1 σε ένα κενό φύλλο εργασίας του Excel.
Συμβουλή
Πριν επικολλήσετε τα δεδομένα στο Excel, ορίστε τα πλάτη των στηλών για τις στήλες A έως D στα 250 pixel και κάντε κλικ στην επιλογή Αναδίπλωση κειμένου (Κεντρική καρτέλα, ομάδα Στοίχιση ).
| Τιμολόγιο | Πόλη | Ημερομηνία τιμολογίου | Παλαιότερο τιμολόγιο κατά πόλη, με ημερομηνία |
|---|---|---|---|
| 3115 | Ατλάντα | 7/4/12 | ="Ατλάντα= "&INDEX($A$2:$C$33;MATCH("Ατλάντα";$B$2:$B$33;0);1)& "; Ημερομηνία τιμολογίου: " & TEXT(INDEX($A$2:$C$33;MATCH("Ατλάντα";$B$2:$B$33;0);3);"η/μ/εε") |
| 3137 | Ατλάντα | 9/4/12 | ="Ώστιν= "&INDEX($A$2:$C$33;MATCH("Ατλάντα";$B$2:$B$33;0);1)& "; Ημερομηνία τιμολογίου: " & TEXT(INDEX($A$2:$C$33;MATCH("Ώστιν";$B$2:$B$33;0);3);"η/μ/εε") |
| 3154 | Ατλάντα | 11/4/12 | ="Ντάλας= "&INDEX($A$2:$C$33;MATCH("Ντάλας";$B$2:$B$33;0);1)& "; Ημερομηνία τιμολογίου: " & TEXT(INDEX($A$2:$C$33;MATCH("Ντάλας";$B$2:$B$33;0);3);"η/μ/εε") |
| 3191 | Ατλάντα | 21/4/12 | ="Νέα Ορλεάνη= "&INDEX($A$2:$C$33;MATCH("Νέα Ορλεάνη";$B$2:$B$33;0);1)& "; Ημερομηνία τιμολογίου: " & TEXT(INDEX($A$2:$C$33;MATCH("Νέα Ορλεάνη";$B$2:$B$33;0);3);"η/μ/εε") |
| 3293 | Ατλάντα | 25/4/12 | ="Τάμπα= "&INDEX($A$2:$C$33;MATCH("Τάμπα";$B$2:$B$33;0);1)& "; Ημερομηνία τιμολογίου: " & TEXT(INDEX($A$2:$C$33;MATCH("Τάμπα";$B$2:$B$33;0);3);"η/μ/εε") |
| 3331 | Ατλάντα | 27/4/12 | |
| 3350 | Ατλάντα | 28/4/12 | |
| 3390 | Ατλάντα | 1/5/12 | |
| 3441 | Ατλάντα | 2/5/12 | |
| 3517 | Ατλάντα | 8/5/12 | |
| 3124 | Ώστιν | 9/4/12 | |
| 3155 | Ώστιν | 11/4/12 | |
| 3177 | Ώστιν | 19/4/12 | |
| 3357 | Ώστιν | 28/4/12 | |
| 3492 | Ώστιν | 6/5/12 | |
| 3316 | Ντάλας | 25/4/12 | |
| 3346 | Ντάλας | 28/4/12 | |
| 3372 | Ντάλας | 1/5/12 | |
| 3414 | Ντάλας | 1/5/12 | |
| 3451 | Ντάλας | 2/5/12 | |
| 3467 | Ντάλας | 2/5/12 | |
| 3474 | Ντάλας | 4/5/12 | |
| 3490 | Ντάλας | 5/5/12 | |
| 3503 | Ντάλας | 8/5/12 | |
| 3151 | Νέα Ορλεάνη | 9/4/12 | |
| 3438 | Νέα Ορλεάνη | 2/5/12 | |
| 3471 | Νέα Ορλεάνη | 4/5/12 | |
| 3160 | Τάμπα | 18/4/12 | |
| 3328 | Τάμπα | 26/4/12 | |
| 3368 | Τάμπα | 29/4/12 | |
| 3420 | Τάμπα | 1/5/12 | |
| 3501 | Τάμπα | 6/5/12 |
Δείτε επίσης
Κάρτα γρήγορης αναφοράς: Συνοπτική παρουσίαση της VLOOKUP
Συναρτήσεις αναζήτησης και αναφοράς (υλικό αναφοράς)