Εμφάνιση μιας ενιαίας τιμής με VLOOKUP και HLOOKUP στο Excel - dummies

Οι πιο δημοφιλείς από τις λειτουργίες αναζήτησης στο Excel 2013 είναι οι λειτουργίες 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 είναι ο αριθμός της στήλης των οποίων οι τιμές συγκρίνονται με το όριο lookup_value σε κάθετο πίνακα. Το όρισμα row_index_num στη λειτουργία HLOOKUP είναι ο αριθμός της γραμμής των οποίων οι τιμές συγκρίνονται με την τιμή lookup_value σε έναν οριζόντιο πίνακα. Όταν εισάγετε τα arguments col_index_num

ή row_index_num στις λειτουργίες VLOOKUP και HLOOKUP, πρέπει να εισαγάγετε μια τιμή μεγαλύτερη από το μηδέν που δεν υπερβαίνει τον συνολικό αριθμό στηλών ή γραμμών Πίνακας αναζήτησης. Το προαιρετικό όριο 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.

Για να χρησιμοποιήσετε αυτόν τον πίνακα συμβουλών, εισαγάγετε το ποσοστό της συμβολής (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,50 σε αυτό το παράδειγμα) δεν ταιριάζει ακριβώς με μία από τις τιμές στην πρώτη στήλη του 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 (στοιχείο, Price_table, 2, FALSE) Σε αυτή τη συνάρτηση HLOOKUP, στην περιοχή C3: C62 ορίζεται το όρισμα

lookup_value

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

table_array . Το όρισμα row_index_num είναι 2 επειδή θέλετε το Excel να επιστρέψει τις τιμές στη δεύτερη σειρά του πίνακα "Αναζήτηση" και το προαιρετικό

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