Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε το Λειτουργία HLOOKUP του Excel στο Excel για να αναζητήσετε μια τιμή.
Επισκόπηση λειτουργίας HLOOKUP
Η συνάρτηση HLOOKUP Hlookup σημαίνει οριζόντια αναζήτηση. Αναζητά μια τιμή στην επάνω σειρά ενός πίνακα. Στη συνέχεια επιστρέφει μια τιμή έναν καθορισμένο αριθμό γραμμών προς τα κάτω από την τιμή που βρέθηκε. Είναι το ίδιο με το vlookup, εκτός από το ότι αναζητά τιμές οριζόντια αντί για κάθετα.
(Παρατηρήστε πώς εμφανίζονται οι εισαγωγές τύπου)
Σύνταξη και είσοδος λειτουργίας HLOOKUP:
| 1 | = HLOOKUP (τιμή_αναζήτησης, πίνακας_συστοιχίας, row_index_num, range_lookup) |
αναζήτηση_αξίας - Η τιμή που θέλετε να αναζητήσετε.
πίνακας_συστοιχίας -Ο πίνακας από τον οποίο μπορείτε να ανακτήσετε δεδομένα.
row_index_num - Ο αριθμός σειράς από τον οποίο μπορείτε να ανακτήσετε δεδομένα.
range_lookup -[προαιρετικό] Ένα boolean για να υποδείξει ακριβή αντιστοίχιση ή κατά προσέγγιση αντιστοίχιση. Προεπιλογή = TRUE = κατά προσέγγιση αντιστοίχιση.
Τι είναι η λειτουργία HLOOKUP;
Ως μία από τις παλαιότερες συναρτήσεις στον κόσμο των υπολογιστικών φύλλων, χρησιμοποιείται η συνάρτηση HLOOKUP Ηοριζόντια Αναζητήσεις. Έχει μερικούς περιορισμούς που συχνά ξεπερνιούνται με άλλες λειτουργίες, όπως INDEX/MATCH. Επίσης, τα περισσότερα τραπέζια είναι χτισμένα κάθετα, αλλά μερικές φορές όταν είναι χρήσιμο να κάνετε οριζόντια αναζήτηση.
Βασικό παράδειγμα
Ας δούμε ένα δείγμα δεδομένων από ένα βιβλίο βαθμολογίας. Θα ασχοληθούμε με αρκετά παραδείγματα για την εξαγωγή πληροφοριών για συγκεκριμένους μαθητές.
Αν θέλουμε να βρούμε σε ποια τάξη βρίσκεται ο Bob, θα γράφαμε τον τύπο:
| 1 | = HLOOKUP ("Bob", A1: E3, 2, FALSE) |
Σημαντικά πράγματα που πρέπει να θυμόμαστε είναι ότι το στοιχείο που ψάχνουμε (Bob), πρέπει να βρίσκεται στην πρώτη σειρά του εύρους αναζήτησης (A1: E3). Είπαμε στη συνάρτηση ότι θέλουμε να επιστρέψουμε μια τιμή από το 2nd γραμμή του εύρους αναζήτησης, η οποία σε αυτή την περίπτωση είναι η σειρά 2. Τέλος, υποδείξαμε ότι θέλουμε να κάνουμε ένα ακριβές ταίριασμα τοποθετώντας το False ως το τελευταίο επιχείρημα. Εδώ, η απάντηση θα είναι "Ανάγνωση".
Πλευρική συμβουλή: Μπορείτε επίσης να χρησιμοποιήσετε τον αριθμό 0 αντί για False ως τελικό όρισμα, καθώς έχουν την ίδια τιμή. Μερικοί άνθρωποι το προτιμούν καθώς είναι πιο γρήγορο να γράφουν. Απλά να ξέρετε ότι και τα δύο είναι αποδεκτά.
Μετατοπισμένα δεδομένα
Για να προσθέσετε κάποια διευκρίνιση στο πρώτο μας παράδειγμα, το στοιχείο αναζήτησης δεν χρειάζεται να βρίσκεται στη σειρά 1 του υπολογιστικού φύλλου σας, μόνο την πρώτη σειρά του εύρους αναζήτησης. Ας χρησιμοποιήσουμε το ίδιο σύνολο δεδομένων:
Τώρα, ας βρούμε τον βαθμό για την τάξη των Επιστημών. Η φόρμουλα μας θα ήταν
| 1 | = HLOOKUP ("Science", A2: E3, 2, FALSE) |
Αυτός εξακολουθεί να είναι έγκυρος τύπος, καθώς η πρώτη σειρά του εύρους αναζήτησης είναι η σειρά 2, όπου θα βρεθεί ο όρος αναζήτησης "Επιστήμη". Επιστρέφουμε μια τιμή από το 2nd γραμμή του εύρους αναζήτησης, η οποία σε αυτή την περίπτωση είναι η σειρά 3. Η απάντηση τότε είναι "A-".
Χρήση μπαλαντέρ
Η συνάρτηση HLOOKUP υποστηρίζει τη χρήση των μπαλαντέρ «*» και «;» όταν κάνετε αναζητήσεις. Για παράδειγμα, ας πούμε ότι είχαμε ξεχάσει πώς να γράψουμε το όνομα του Φρανκ και απλώς θέλαμε να αναζητήσουμε ένα όνομα που ξεκινά με "F". Θα μπορούσαμε να γράψουμε τον τύπο
| 1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
Αυτό θα ήταν σε θέση να βρει το όνομα Frank στη στήλη E και στη συνέχεια να επιστρέψει την τιμή από το 2nd σχετική σειρά. Σε αυτή την περίπτωση, η απάντηση θα είναι "Επιστήμη".
Μη ακριβής αντιστοίχιση
Τις περισσότερες φορές, θα θέλετε να βεβαιωθείτε ότι το τελευταίο όρισμα στο HLOOKUP είναι False (ή 0), ώστε να έχετε μια ακριβή αντιστοίχιση. Ωστόσο, υπάρχουν μερικές φορές που μπορεί να ψάχνετε για μια μη ακριβή αντιστοίχιση. Εάν έχετε μια λίστα με ταξινομημένα δεδομένα, μπορείτε επίσης να χρησιμοποιήσετε το HLOOKUP για να επιστρέψετε το αποτέλεσμα για το στοιχείο που είναι είτε το ίδιο είτε το επόμενο μικρότερο. Αυτό χρησιμοποιείται συχνά όταν πρόκειται για αυξανόμενα εύρη αριθμών, όπως σε φορολογικό πίνακα ή επιδόματα προμήθειας.
Ας υποθέσουμε ότι θέλετε να βρείτε το φορολογικό συντελεστή για ένα εισόδημα που εισάγεται στο κελί Η2. Ο τύπος στο Η4 μπορεί να είναι:
| 1 | = HLOOKUP (H2, B1: F2, 2, TRUE) |
Η διαφορά σε αυτόν τον τύπο είναι ότι το τελευταίο μας επιχείρημα είναι "True". Στο συγκεκριμένο μας παράδειγμα, μπορούμε να δούμε ότι όταν οι ατομικές μας εισροές εισοδήματος 45.000 δολαρίων θα έχουν φορολογικό συντελεστή 15%.
Σημείωση: Παρόλο που συνήθως θέλουμε μια ακριβή αντιστοίχιση με το ρήμα ως επιχείρημα, ξεχνάτε να καθορίσετε το 4ου όρισμα σε ένα HLOOKUP, η προεπιλογή είναι True. Αυτό μπορεί να σας προκαλέσει κάποια απροσδόκητα αποτελέσματα, ειδικά όταν ασχολείστε με τιμές κειμένου.
Δυναμική σειρά
Το HLOOKUP απαιτεί να δώσετε ένα επιχείρημα που να λέει από ποια σειρά θέλετε να επιστρέψετε μια τιμή, αλλά η περίσταση μπορεί να προκύψει όταν δεν γνωρίζετε πού θα είναι η σειρά ή θέλετε να επιτρέψετε στον χρήστη σας να αλλάξει από ποια σειρά θα επιστρέψει. Σε αυτές τις περιπτώσεις, μπορεί να είναι χρήσιμο να χρησιμοποιήσετε τη συνάρτηση MATCH για να καθορίσετε τον αριθμό γραμμής.
Ας εξετάσουμε ξανά το παράδειγμα του βιβλίου βαθμολογίας μας, με μερικές εισόδους στο G2 και G4. Για να λάβουμε τον αριθμό στήλης, θα μπορούσαμε να γράψουμε έναν τύπο του
| 1 | = ΑΓΩΝΑΣ (G2, A1: A3, 0) |
Αυτό θα προσπαθήσει να βρει την ακριβή θέση του "Βαθμού" εντός του εύρους A1: A3. Η απάντηση θα είναι 3. Γνωρίζοντας αυτό, μπορούμε να το συνδέσουμε σε μια συνάρτηση HLOOKUP και να γράψουμε έναν τύπο στο G6, ως εξής:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Έτσι, η συνάρτηση MATCH θα αξιολογηθεί σε 3 και αυτό λέει στο HLOOKUP να επιστρέψει ένα αποτέλεσμα από το 3rd σειρά στην περιοχή A1: E3. Συνολικά, τότε έχουμε το επιθυμητό αποτέλεσμα "C". Ο τύπος μας είναι δυναμικός τώρα στο ότι μπορούμε να αλλάξουμε είτε τη σειρά προς εξέταση είτε το όνομα για αναζήτηση.
Περιορισμοί HLOOKUP
Όπως αναφέρθηκε στην αρχή του άρθρου, η μεγαλύτερη πτώση του HLOOKUP είναι ότι απαιτεί ο όρος αναζήτησης να βρίσκεται στην πιο αριστερή στήλη του εύρους αναζήτησης. Ενώ υπάρχουν μερικά φανταχτερά κόλπα που μπορείτε να κάνετε για να το ξεπεράσετε, η κοινή εναλλακτική λύση είναι να χρησιμοποιήσετε INDEX και MATCH. Αυτός ο συνδυασμός σας δίνει μεγαλύτερη ευελιξία και μερικές φορές μπορεί να είναι ακόμη πιο γρήγορος υπολογισμός.
HLOOKUP στα Υπολογιστικά φύλλα Google
Η συνάρτηση HLOOKUP λειτουργεί ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel:
επιπρόσθετες σημειώσεις
Χρησιμοποιήστε τη συνάρτηση HLOOKUP για να εκτελέσετε μια οριζόντια αναζήτηση. Εάν είστε ήδη εξοικειωμένοι με τη συνάρτηση VLOOKUP, ένα HLOOKUP λειτουργεί με τον ίδιο ακριβώς τρόπο, εκτός του ότι η αναζήτηση εκτελείται οριζόντια αντί για κάθετα. Το HLOOKUP αναζητά μια ακριβή αντιστοίχιση (range_lookup = FALSE) ή η πλησιέστερη αντιστοίχιση που είναι ίση ή μικρότερη από την τιμή αναζήτησης (range_lookup = TRUE, αριθμητικές τιμές μόνο) στην πρώτη σειρά του πίνακα_σειρά. Στη συνέχεια επιστρέφει μια αντίστοιχη τιμή, n αριθμό γραμμών κάτω από την αντιστοίχιση.
Όταν χρησιμοποιείτε ένα HLOOKUP για να βρείτε μια ακριβή αντιστοίχιση, πρώτα ορίστε μια αναγνωριστική τιμή που θέλετε να αναζητήσετε ως αναζήτηση_αξίαςΤο Αυτή η αναγνωριστική τιμή μπορεί να είναι ένα SSN, αναγνωριστικό υπαλλήλου, όνομα ή κάποιο άλλο μοναδικό αναγνωριστικό.
Στη συνέχεια, ορίζετε το εύρος (που ονομάζεται πίνακας_συστοιχίας) που περιέχει τα αναγνωριστικά στην επάνω σειρά και οποιεσδήποτε τιμές θέλετε τελικά να αναζητήσετε στις γραμμές κάτω από αυτήν. ΣΗΜΑΝΤΙΚΟ: Τα μοναδικά αναγνωριστικά πρέπει να βρίσκονται στην επάνω σειρά. Εάν δεν είναι, πρέπει είτε να μετακινήσετε τη σειρά στην κορυφή είτε να χρησιμοποιήσετε MATCH / INDEX αντί για το HLOOKUP.
Τρίτον, καθορίστε τον αριθμό σειράς (row_index) απο πίνακας_συστοιχίας που θέλεις να επιστρέψεις. Λάβετε υπόψη ότι η πρώτη σειρά, που περιέχει τα μοναδικά αναγνωριστικά είναι η σειρά 1. Η δεύτερη σειρά είναι η σειρά 2 κ.λπ.
Τέλος, πρέπει να υποδείξετε αν θα αναζητήσετε ακριβή αντιστοίχιση (FALSE) ή πλησιέστερη αντιστοίχιση (TRUE) στο range_lookupΤο Εάν είναι επιλεγμένη η επιλογή ακριβούς αντιστοίχισης και δεν βρεθεί ακριβής αντιστοίχιση, επιστρέφεται σφάλμα (#N/A). Για να εμφανιστεί ο τύπος κενός ή "δεν βρέθηκε" ή οποιαδήποτε άλλη τιμή αντί της τιμής σφάλματος (#N/A) χρησιμοποιήστε τη συνάρτηση IFERROR με το HLOOKUP.
Για να χρησιμοποιήσετε τη συνάρτηση HLOOKUP για να επιστρέψετε ένα σύνολο κατά προσέγγιση αντιστοίχισης: range_lookup = ΑΛΗΘΕΙΑ Αυτή η επιλογή είναι διαθέσιμη μόνο για αριθμητικές τιμές. Οι τιμές πρέπει να ταξινομηθούν με αύξουσα σειρά.
Παραδείγματα HLOOKUP σε VBA
Μπορείτε επίσης να χρησιμοποιήσετε τη λειτουργία HLOOKUP στο VBA. Τύπος:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Εκτέλεση των ακόλουθων δηλώσεων VBA
| 123456 | Εύρος ("G2") = Application.WorksheetFunction.HLookup (Εύρος ("C1"), Εύρος ("A1: E3"), 1)Εύρος ("H2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 2)Εύρος ("I2") = Application.WorksheetFunction.HLookup (Εύρος ("C1"), Εύρος ("A1: E3"), 3)Εύρος ("G3") = Application.WorksheetFunction.HLookup (Εύρος ("D1"), Εύρος ("A1: E3"), 1)Εύρος ("H3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 2)Range ("I3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 3) |
θα παράγει τα ακόλουθα αποτελέσματα
Για τα ορίσματα συνάρτησης (τιμή_αναζήτησης, κ.λπ.), μπορείτε είτε να τα εισαγάγετε απευθείας στη συνάρτηση είτε να ορίσετε μεταβλητές που θα χρησιμοποιηθούν.
Επιστρέψτε στη λίστα όλων των λειτουργιών στο Excel
