Πώς να χρησιμοποιήσετε τις λειτουργίες VLOOKUP και HLOOKUP στο Excel 2016 - dummies

Οι πιο δημοφιλείς λειτουργίες αναζήτησης του Excel 2016 είναι οι λειτουργίες HLOOKUP (για οριζόντια αναζήτηση) και VLOOKUP (για κατακόρυφη αναζήτηση). Αυτές οι λειτουργίες βρίσκονται στο αναπτυσσόμενο μενού "Αναζήτηση και Αναφορά" στην καρτέλα Φόρμες της Κορδέλας καθώς και στην κατηγορία Αναζήτηση και Αναφοράς στο παράθυρο διαλόγου Εισαγωγή λειτουργιών. Είναι μέρος μιας ισχυρής ομάδας λειτουργιών που μπορούν να επιστρέψουν τις τιμές αναζητώντας τους σε πίνακες δεδομένων.

Η λειτουργία VLOOKUP αναζητά κατακόρυφα (από πάνω προς τα κάτω) την αριστερή στήλη ενός πίνακα αναζήτησης μέχρι το πρόγραμμα να εντοπίσει μια τιμή που αντιστοιχεί ή υπερβαίνει αυτή που αναζητάτε. Η λειτουργία HLOOKUP αναζητά οριζόντια (από αριστερά προς τα δεξιά) την κορυφαία σειρά ενός πίνακα αναζήτησης μέχρι να εντοπίσει μια τιμή που αντιστοιχεί ή υπερβαίνει εκείνη που ψάχνετε.

Η συνάρτηση VLOOKUP χρησιμοποιεί την ακόλουθη σύνταξη:

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Η συνάρτηση HLOOKUP ακολουθεί την σχεδόν ταυτόσημη σύνταξη:

HLOOKUP > Και στις δύο λειτουργίες, το όρισμα

lookup_value είναι η τιμή που θέλετε να αναζητήσετε στον πίνακα Lookup και table_array είναι η περιοχή κυττάρων ή το όνομα του πίνακα Lookup που περιέχει και τα δύο αξία για την αναζήτηση και την αντίστοιχη τιμή για επιστροφή. col_index_num

ορίζει τη στήλη του πίνακα αναζήτησης που περιέχει τις τιμές που επιστρέφονται από τη συνάρτηση VLOOKUP με βάση την τιμή που αντιστοιχεί στην τιμή του argument lookup_value σε σχέση με εκείνους στο όρισμα table_array. Προσδιορίζετε το όρισμα col_index_num που μετράει πόσες στήλες βρίσκεται αυτή η στήλη στα δεξιά από την πρώτη στήλη του πίνακα κατακόρυφης αναζήτησης και συμπεριλαμβάνετε την πρώτη στήλη του πίνακα αναζήτησης σε αυτήν την μέτρηση. Το όρισμα row_index_num

ορίζει τη σειρά που περιέχει τις τιμές που επιστρέφει η λειτουργία HLOOKUP σε έναν οριζόντιο πίνακα. Προσδιορίζετε το όρισμα row_index_num μετρώντας πόσες σειρές κάτω από αυτή τη σειρά είναι από την επάνω γραμμή του οριζόντιου πίνακα αναζήτησης. Και πάλι, συμπεριλαμβάνετε την κορυφαία σειρά του πίνακα "Αναζήτηση" σε αυτή την καταμέτρηση. Όταν εισάγετε τα arguments col_index_num

ή row_index_num στις λειτουργίες VLOOKUP και HLOOKUP, η τιμή που εισάγετε δεν μπορεί να υπερβαίνει τον συνολικό αριθμό στηλών ή γραμμών στον πίνακα Lookup. Το προαιρετικό όριο range_lookup

και στις λειτουργίες VLOOKUP και HLOOKUP είναι το λογικό TRUE ή FALSE που καθορίζει αν θέλετε το Excel να βρει ακριβή ή κατά προσέγγιση αντιστοιχία για το lookup_value σε το table_array.Όταν καθορίσετε TRUE ή παραλείψετε το όριο range_lookup στη λειτουργία VLOOKUP ή HLOOKUP, το Excel βρίσκει μια προσέγγιση κατά προσέγγιση. Όταν καθορίζετε το FALSE ως το όριο range_lookup , το Excel βρίσκει μόνο ακριβείς αντιστοιχίσεις. Η εύρεση προσεγγιστικών αντιστοιχιών αφορά μόνο όταν αναζητάτε αριθμητικές καταχωρίσεις (αντί για κείμενο) στην πρώτη στήλη ή στη σειρά του κατακόρυφου ή οριζόντιου πίνακα αναζήτησης. Όταν το Excel δεν εντοπίσει ακριβή αντιστοίχιση σε αυτήν τη στήλη ή γραμμή αναζήτησης, εντοπίζει την επόμενη υψηλότερη τιμή που δεν υπερβαίνει το όρισμα lookup_value

και στη συνέχεια επιστρέφει την τιμή στη στήλη ή τη γραμμή που καθορίζεται από το σύμβολο < arguments col_index_num ή row_index_num . Όταν χρησιμοποιείτε τις λειτουργίες VLOOKUP και HLOOKUP, οι καταχωρήσεις κειμένου ή αριθμοί στη στήλη Lookup ή τη σειρά (δηλαδή, η αριστερή στήλη ενός πίνακα κατακόρυφης αναζήτησης ή η κορυφαία σειρά ενός οριζόντιου πίνακα αναζήτησης) πρέπει να είναι μοναδικές. Οι ενδείξεις αυτές πρέπει επίσης να διευθετούνται ή να ταξινομούνται με αύξουσα σειρά. δηλαδή, αλφαβητική σειρά για τις καταχωρίσεις κειμένου και τη χαμηλότερη προς την υψηλότερη σειρά για αριθμητικές καταχωρίσεις. Το σχήμα δείχνει ένα παράδειγμα χρήσης της λειτουργίας VLOOKUP για να επιστρέψετε είτε ένα άκρο 15% ή 20% από ένα τραπέζι, ανάλογα με το συνολικό άθροισμα του ελέγχου. Το στοιχείο F3 περιέχει τη συνάρτηση VLOOKUP: = VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0, 15, 2, 3))

η προκαταβολική ποσότητα του ελέγχου στο κελί F2.

Χρησιμοποιώντας τη λειτουργία VLOOKUP για να επιστρέψετε το ποσό της άκρης για να προσθέσετε από έναν πίνακα Lookup.

Για να χρησιμοποιήσετε αυτόν τον πίνακα συμβουλών, εισαγάγετε το ποσοστό της συμβολής (15% ή 20%) στο κελί F1 (με όνομα Tip_Percentage) και το ποσό του ελέγχου πριν από το φόρο στο κελί F2 (με το όνομα Pretax_Total). Το Excel αναζητά τότε την τιμή που εισάγετε στο κελί Pretax_Total στην πρώτη στήλη του πίνακα Lookup, που περιλαμβάνει το εύρος κελιών A2: C101 και ονομάζεται Tip_Table.

Το Excel μετακινεί προς τα κάτω τις τιμές στην πρώτη στήλη του Tip_Table μέχρι να βρει μια αντιστοίχιση, οπότε το πρόγραμμα χρησιμοποιεί το όρισμα

col_index_num

στη λειτουργία VLOOKUP για να προσδιορίσει ποιο ποσό άκρου από αυτή τη σειρά του πίνακα επιστροφή στο κελί F3. Εάν το Excel διαπιστώσει ότι η τιμή που καταχωρήθηκε στο κελί Pretax_Total ($ 16.55 σε αυτό το παράδειγμα) δεν ταιριάζει ακριβώς με μία από τις τιμές στην πρώτη στήλη του Tip_Table, το πρόγραμμα συνεχίζει να ψάχνει προς τα κάτω το εύρος σύγκρισης μέχρι να συναντήσει την πρώτη τιμή που υπερβαίνει το συνολικό άθροισμα (17,00 στην κυψέλη Α19 σε αυτό το παράδειγμα). Το Excel κινείται στη συνέχεια πίσω στην προηγούμενη γραμμή στον πίνακα και επιστρέφει την τιμή στη στήλη που αντιστοιχεί στο όρισμα

col_index_num της λειτουργίας VLOOKUP. (Αυτό οφείλεται στο γεγονός ότι το προαιρετικό όριο range_lookup έχει παραλειφθεί από τη συνάρτηση.) Σημειώστε ότι το παράδειγμα πίνακα πινάκων στο σχήμα χρησιμοποιεί μια συνάρτηση IF για να καθορίσει το όρισμα col_index_num τη λειτουργία VLOOKUP στο κελί F3. Η συνάρτηση IF προσδιορίζει τον αριθμό της στήλης που χρησιμοποιείται στον πίνακα κορυφής προσαρμόζοντας το ποσοστό που εισάγεται στο Tip_Percentage (κελί F1) με το 0.15. Αν αντιστοιχούν, η συνάρτηση επιστρέφει το 2 ως το όρισμα

col_index_num και η συνάρτηση VLOOKUP επιστρέφει μια τιμή από τη δεύτερη στήλη (η στήλη 15% Β) στην περιοχή Tip_Table. Διαφορετικά, η συνάρτηση IF επιστρέφει 3 ως όρισμα col_index_num και η συνάρτηση VLOOKUP επιστρέφει μια τιμή από την τρίτη στήλη (η στήλη 20% C) στην περιοχή Tip_Table. Το παρακάτω σχήμα δείχνει ένα παράδειγμα που χρησιμοποιεί τη λειτουργία HLOOKUP για να αναζητήσει την τιμή κάθε στοιχείου αρτοποιίας που είναι αποθηκευμένη σε ξεχωριστό πίνακα τιμών αναζήτησης και στη συνέχεια να επιστρέψει αυτή την τιμή στη στήλη Τιμή / Doz της λίστας Καθημερινών Πωλήσεων. Το κελί F3 περιέχει τον αρχικό τύπο με τη λειτουργία HLOOKUP που στη συνέχεια αντιγράφεται στη στήλη F: Χρησιμοποιώντας τη λειτουργία HLOOKUP για να επιστρέψετε την τιμή ενός στοιχείου αρτοποιίας από έναν πίνακα Lookup. = HLOOKUP (στοιχείο, Price_table, 2, FALSE)

Σε αυτή τη συνάρτηση HLOOKUP, το όνομα εύρους που δίνεται στη στήλη Στοιχείο στην περιοχή C3: C62 ορίζεται ως argument

lookup_value

και το όνομα της περιοχής κυψελών Πίνακας τιμών που δίνεται στην περιοχή κυττάρων I1: M2 είναι το όρισμα

table_array . Το όρισμα row_index_num είναι 2 επειδή θέλετε το Excel να επιστρέψει τις τιμές στη δεύτερη σειρά του πίνακα Αναζήτηση τιμών και το προαιρετικό όρισμα range_lookup είναι FALSE επειδή το όνομα του στοιχείου στις Καθημερινές Πωλήσεις πρέπει να αντιστοιχεί ακριβώς στο όνομα του στοιχείου στον πίνακα "Αναζήτηση τιμών". Με τη χρήση της λειτουργίας HLOOKUP χρησιμοποιήστε το εύρος πίνακα τιμολογίων για να καταχωρίσετε την τιμή ανά δωδεκάδα για κάθε είδος προϊόντος αρτοποιίας στον κατάλογο ημερήσιων πωλήσεων, καθιστάτε πολύ απλό να ενημερώσετε οποιαδήποτε από τις πωλήσεις στη λίστα. Το μόνο που έχετε να κάνετε είναι να αλλάξετε το κόστος Τιμή / Δόση σε αυτό το εύρος και η λειτουργία HLOOKUP ενημερώνει αμέσως τη νέα τιμή στη λίστα Καθημερινών Πωλήσεων όπου και αν πωλείται το στοιχείο.