: Δοκιμάστε να χρησιμοποιήσετε τις νέες συναρτήσεις XLOOKUP και XMATCH , βελτιωμένες εκδόσεις των συναρτήσεων που περιγράφονται σε αυτό το άρθρο. Αυτές οι νέες συναρτήσεις λειτουργούν προς οποιαδήποτε κατεύθυνση και επιστρέφουν ακριβείς αντιστοιχίες από προεπιλογή, καθιστώντας τις πιο εύκολες και πιο βολικές από τις προαπαιτούμενες.
Ας υποθέσουμε ότι έχετε μια λίστα με αριθμούς τοποθεσίας γραφείου και πρέπει να γνωρίζετε ποιοι υπάλληλοι βρίσκονται σε κάθε γραφείο. Το υπολογιστικό φύλλο είναι τεράστιο, επομένως μπορεί να πιστεύετε ότι είναι δύσκολη εργασία. Στην πραγματικότητα, είναι πολύ εύκολο να το κάνετε με μια λειτουργία αναζήτησης.
Οι συναρτήσεις VLOOKUP και HLOOKUP , μαζί με τις συναρτήσεις INDEX και MATCH, είναι ορισμένες από τις πιο χρήσιμες συναρτήσεις στο Excel.
: Η δυνατότητα "Οδηγός αναζήτησης" δεν είναι πλέον διαθέσιμη στο Excel.
Ακολουθεί ένα παράδειγμα του τρόπου χρήσης της συνάρτησης VLOOKUP.
=VLOOKUP(B2;C2:E7;3;TRUE)
Σε αυτό το παράδειγμα, το B2 είναι το πρώτο όρισμα — ένα στοιχείο δεδομένων που πρέπει να λειτουργήσει η συνάρτηση. Για τη συνάρτηση VLOOKUP, αυτό το πρώτο όρισμα είναι η τιμή που θέλετε να βρείτε. Αυτό το όρισμα μπορεί να είναι μια αναφορά κελιού ή μια σταθερή τιμή, όπως "smith" ή 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.
Για περισσότερα παραδείγματα σχετικά με τη χρήση των συναρτήσεων INDEX και MATCH αντί για τη συνάρτηση VLOOKUP, ανατρέξτε στο άρθρο https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ από τον Bill Jelen, MVP της Microsoft.
Δοκιμάστε το
Εάν θέλετε να πειραματιστείτε με συναρτήσεις αναζήτησης πριν τις δοκιμάσετε με τα δικά σας δεδομένα, δείτε ορισμένα δείγματα δεδομένων.
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 |
