4 Λειτουργίες αναζήτησης Excel για την αναζήτηση υπολογιστικών φύλλων αποτελεσματικά

• Παραγωγικότητα

Διαφήμιση

Τις περισσότερες φορές, η αναζήτηση ενός υπολογιστικού φύλλου του Microsoft Excel είναι αρκετά εύκολη. Εάν δεν μπορείτε να σαρώσετε μόνο τις σειρές και τις στήλες για αυτό, μπορείτε να χρησιμοποιήσετε το Ctrl + F για να το αναζητήσετε. Εάν εργάζεστε με ένα πολύ μεγάλο υπολογιστικό φύλλο, μπορείτε να εξοικονομήσετε πολύ χρόνο για να χρησιμοποιήσετε μία από αυτές τις τέσσερις λειτουργίες αναζήτησης.

Ξεκλειδώστε το φύλλο εξαπατήσεων "Essential Excel Formulas" τώρα!

Αυτό θα σας υπογράψει στο newsletter μας

Μόλις μάθετε πώς να κάνετε αναζήτηση στο Excel χρησιμοποιώντας την αναζήτηση, δεν θα επηρεάσει πόσο μεγάλα τα υπολογιστικά φύλλα σας έχουν, θα είστε πάντα σε θέση να βρείτε κάτι στο Excel!

1. Η λειτουργία VLOOKUP

Αυτή η λειτουργία σάς επιτρέπει να καθορίσετε μια στήλη και μια τιμή και θα επιστρέψετε μια τιμή από την αντίστοιχη σειρά μιας διαφορετικής στήλης (αν αυτό δεν έχει νόημα, θα γίνει σαφής σε μια στιγμή). Δύο παραδείγματα όπου μπορείτε να κάνετε κάτι τέτοιο αναζητούν το επώνυμο του υπαλλήλου από τον αριθμό του υπαλλήλου του ή βρίσκοντας έναν αριθμό τηλεφώνου, καθορίζοντας ένα επώνυμο.

Εδώ είναι η σύνταξη της συνάρτησης:

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

• [lookup_value] είναι η πληροφορία που έχετε ήδη. Για παράδειγμα, εάν πρέπει να ξέρετε ποια κατάσταση είναι μια πόλη, θα ήταν το όνομα της πόλης.
• [table_array] σας επιτρέπει να καθορίσετε τα κελιά στα οποία η λειτουργία θα αναζητήσει τις τιμές αναζήτησης και επιστροφής. Όταν επιλέγετε το εύρος σας, βεβαιωθείτε ότι η πρώτη στήλη που περιλαμβάνεται στη συστοιχία σας είναι αυτή που θα περιλαμβάνει την τιμή αναζήτησης σας!
• [col_index_num] είναι ο αριθμός της στήλης που περιέχει την τιμή επιστροφής.
• [range_lookup] είναι ένα προαιρετικό όρισμα και παίρνει 1 ή 0. Εάν εισάγετε 1 ή παραλείψετε αυτό το όρισμα, η λειτουργία αναζητά την τιμή που εισάγατε ή τον επόμενο χαμηλότερο αριθμό. Έτσι στην παρακάτω εικόνα, ένα VLOOKUP που αναζητά ένα σκορ SAT 652 θα επιστρέψει 646, καθώς είναι ο πλησιέστερος αριθμός στη λίστα που είναι μικρότερος από 652, και η [range_lookup] προεπιλογή στο 1.

Ας ρίξουμε μια ματιά στο πώς μπορείτε να χρησιμοποιήσετε αυτό. Αυτό το υπολογιστικό φύλλο περιέχει αριθμούς ταυτότητας, όνομα και επώνυμο, βαθμολογία πόλης, πολιτείας και SAT. Ας υποθέσουμε ότι θέλετε να βρείτε το σκορ SAT ενός ατόμου με το επώνυμο "Winters". Το VLOOKUP το διευκολύνει. Ακολουθεί ο τύπος που χρησιμοποιείτε:

 =VLOOKUP("Winters", C2:F101, 4, 0) 

Επειδή οι βαθμολογίες SAT είναι η τέταρτη στήλη πάνω από τη στήλη του τελευταίου ονόματος, 4 είναι το όρισμα δείκτη στήλης. Λάβετε υπόψη ότι όταν αναζητάτε κείμενο, η ρύθμιση [range_lookup] στο 0 είναι μια καλή ιδέα. Χωρίς αυτό, μπορείτε να πάρετε τα κακά αποτελέσματα.

Εδώ είναι το αποτέλεσμα:

Επέστρεψε 651, το σκορ SAT που ανήκει στον φοιτητή Kennedy Winters, ο οποίος βρίσκεται στη σειρά 92 (εμφανίζεται στο παραπάνω φύλλο). Θα χρειαζόταν πολύ περισσότερο να περιηγηθείτε μέσα από την αναζήτηση του ονόματος από ό, τι έκανε για να πληκτρολογήσετε γρήγορα τη σύνταξη!

Σημειώσεις για το VLOOKUP

Μερικά πράγματα είναι καλά να θυμάστε όταν χρησιμοποιείτε το VLOOKUP. Βεβαιωθείτε ότι η πρώτη στήλη στην περιοχή σας είναι εκείνη που περιλαμβάνει την τιμή αναζήτησης. Εάν δεν βρίσκεται στην πρώτη στήλη, η λειτουργία θα επιστρέψει λανθασμένα αποτελέσματα. Αν οι στήλες σας είναι καλά οργανωμένες, αυτό δεν πρέπει να είναι πρόβλημα.

Επίσης, να θυμάστε ότι το VLOOKUP θα επιστρέψει ποτέ μόνο μία αξία. Αν χρησιμοποιούσατε τη "Γεωργία" ως αξία αναζήτησης, θα επέστρεφε το αποτέλεσμα του πρώτου φοιτητή από τη Γεωργία και δεν έδειξε ότι υπάρχουν στην πραγματικότητα δύο φοιτητές από τη Γεωργία.

2. Η λειτουργία HLOOKUP

Όπου το VLOOKUP βρίσκει αντίστοιχες τιμές σε άλλη στήλη, το HLOOKUP βρίσκει αντίστοιχες τιμές σε διαφορετική σειρά. Επειδή είναι συνήθως πιο εύκολο να σαρώσετε τις επικεφαλίδες των στηλών μέχρι να βρείτε το σωστό και να χρησιμοποιήσετε ένα φίλτρο για να βρείτε αυτό που ψάχνετε, το HLOOKUP χρησιμοποιείται καλύτερα όταν έχετε πραγματικά μεγάλα υπολογιστικά φύλλα ή εργάζεστε με αξίες που οργανώνονται με το χρόνο .

Εδώ είναι η σύνταξη της συνάρτησης:

 =HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup]) 

• [lookup_value] είναι η τιμή που γνωρίζετε και θέλετε να βρείτε μια αντίστοιχη τιμή για.
• [table_array] είναι τα κελιά στα οποία θέλετε να αναζητήσετε.
• [row_index_num] καθορίζει τη σειρά από την οποία θα προκύψει η τιμή επιστροφής.
• [range_lookup] είναι το ίδιο όπως στο VLOOKUP, αφήστε το κενό για να πάρετε την πλησιέστερη τιμή όταν είναι δυνατόν ή πληκτρολογήστε 0 για να αναζητήσετε μόνο ακριβείς αντιστοιχίσεις.

Αυτό το υπολογιστικό φύλλο περιέχει μια σειρά για κάθε κράτος, μαζί με μια βαθμολογία SAT κατά τα έτη 2000-2014. Μπορείτε να χρησιμοποιήσετε το HLOOKUP για να βρείτε το μέσο σκορ στη Μινεσότα το 2013. Δείτε πώς θα το κάνουμε:

 =HLOOKUP(2013, A1:P51, 24) 

Όπως μπορείτε να δείτε στην παρακάτω εικόνα, η βαθμολογία επιστρέφεται:

Οι Μινεσότες κατά μέσο όρο βαθμολογούν το 1014 το 2013. Σημειώστε ότι το 2013 δεν είναι σε εισαγωγικά επειδή είναι ένας αριθμός, και όχι μια συμβολοσειρά. Επίσης, τα 24 προέρχονται από τη Μινεσότα που βρίσκεται στην 24η σειρά.

Σημειώσεις για το HLOOKUP

Όπως και με το VLOOKUP, η τιμή αναζήτησης πρέπει να βρίσκεται στην πρώτη σειρά του πίνακα πίνακα. Αυτό είναι σπάνια πρόβλημα με το HLOOKUP, καθώς συνήθως χρησιμοποιείτε έναν τίτλο στήλης για μια τιμή αναζήτησης. Το HLOOKUP επιστρέφει μόνο μία μόνο τιμή.

3-4. Οι λειτουργίες INDEX και MATCH

Το INDEX και το MATCH είναι δύο διαφορετικές λειτουργίες, αλλά όταν χρησιμοποιούνται μαζί μπορούν να κάνουν πολύ πιο γρήγορα την αναζήτηση σε ένα μεγάλο υπολογιστικό φύλλο. Και οι δύο λειτουργίες έχουν μειονεκτήματα, αλλά συνδυάζοντάς τα θα βασιστούμε στις δυνάμεις και των δύο.

Πρώτον, η σύνταξη και των δύο λειτουργιών:

 =INDEX([array], [row_number], [column_number]) 

• [array] είναι ο πίνακας στον οποίο θα ψάξετε.
• [row_number] και [column_number] μπορούν να χρησιμοποιηθούν για να περιορίσουν την αναζήτησή σας (θα ρίξουμε μια ματιά σε αυτό σε μια στιγμή.)

 =MATCH([lookup_value], [lookup_array], [match_type]) 

• [lookup_value] είναι ένας όρος αναζήτησης που μπορεί να είναι μια συμβολοσειρά ή ένας αριθμός.
• [lookup_array] είναι ο πίνακας στον οποίο το Microsoft Excel θα αναζητήσει τον όρο αναζήτησης.
• [match_type] είναι ένα προαιρετικό όρισμα που μπορεί να είναι 1, 0 ή -1. 1 θα επιστρέψει τη μεγαλύτερη τιμή που είναι μικρότερη ή ίση με τον όρο αναζήτησης. 0 θα επιστρέψει μόνο τον ακριβή σας όρο και το -1 θα επιστρέψει τη μικρότερη τιμή που είναι μεγαλύτερη ή ίση με τον όρο αναζήτησης.

Μπορεί να μην είναι σαφές πώς θα χρησιμοποιήσουμε αυτές τις δύο λειτουργίες μαζί, γι 'αυτό θα το βάλω εδώ. Το MATCH παίρνει έναν όρο αναζήτησης και επιστρέφει μια αναφορά κελί. Στην παρακάτω εικόνα, μπορείτε να δείτε ότι σε μια αναζήτηση για την τιμή 646 στη στήλη F, MATCH επιστρέφει 4.

Ο INDEX, από την άλλη πλευρά, κάνει το αντίθετο: παίρνει μια αναφορά κυψέλης και επιστρέφει την τιμή σε αυτό. Μπορείτε να δείτε εδώ ότι, όταν λέγεται να επιστρέψει το έκτο κελί της στήλης City, INDEX επιστρέφει "Anchorage", την τιμή από τη γραμμή 6.

Αυτό που πρόκειται να κάνουμε είναι να συνδυάσουμε τα δύο έτσι ώστε το MATCH να επιστρέψει μια αναφορά κυψελών και ο INDEX χρησιμοποιεί αυτή την αναφορά για να αναζητήσει την τιμή σε ένα κελί. Ας πούμε ότι θυμάστε ότι υπήρχε ένας φοιτητής του οποίου το επώνυμο ήταν το Waters και θέλετε να δείτε ποια ήταν η βαθμολογία του φοιτητή. Εδώ είναι ο τύπος που θα χρησιμοποιήσουμε:

 =INDEX(F:F, MATCH("Waters", C:C, 0)) 

Θα παρατηρήσετε ότι ο τύπος αντιστοίχισης έχει οριστεί σε 0 εδώ. Όταν ψάχνετε για μια συμβολοσειρά, αυτό θα θέλετε να χρησιμοποιήσετε. Εδώ έχουμε τι παίρνουμε όταν τρέχουμε αυτή τη λειτουργία:

Όπως μπορείτε να δείτε από το ένθετο, Owen Waters σκόραρε 1720, ο αριθμός που εμφανίζεται όταν τρέχουμε τη λειτουργία. Αυτό μπορεί να μην φαίνεται όλα χρήσιμα όταν μπορείτε να δείτε μόνο μερικές στήλες, αλλά φανταστείτε πόσο χρόνο θα αποθηκεύσετε αν έπρεπε να το κάνετε 50 φορές σε ένα μεγάλο φύλλο δεδομένων Excel Excel. Πρόσβαση - Μπορεί ένα υπολογιστικό φύλλο να αντικαταστήσει μια βάση δεδομένων; Excel Vs. Πρόσβαση - Μπορεί ένα υπολογιστικό φύλλο να αντικαταστήσει μια βάση δεδομένων; Ποιο εργαλείο πρέπει να χρησιμοποιήσετε για τη διαχείριση δεδομένων; Η πρόσβαση και το Excel διαθέτουν χαρακτηριστικά φιλτραρίσματος δεδομένων, ταξινόμηση και ερώτηση. Θα σας δείξουμε ποια είναι η πιο κατάλληλη για τις ανάγκες σας. Διαβάστε περισσότερα που περιείχαν αρκετές εκατοντάδες στήλες!

Αφήστε τις αναζητήσεις του Excel να ξεκινήσουν

Το Microsoft Excel διαθέτει πολλές εξαιρετικά ισχυρές λειτουργίες για τον χειρισμό δεδομένων, και οι τέσσερις που αναφέρονται πιο πάνω απλά γρατζουνίζουν την επιφάνεια. Η εκμάθηση του τρόπου χρήσης τους θα κάνει τη ζωή σας πολύ πιο εύκολη.

Εάν πραγματικά θέλετε να κυριαρχήσετε το Microsoft Excel, θα μπορούσατε πραγματικά να επωφεληθείτε από τη διατήρηση του Essential Excel Cheat Sheet Οι βασικοί τύποι και λειτουργίες του Microsoft Excel Cheat Sheet Οι βασικοί τύποι και λειτουργίες του Microsoft Excel Cheat Sheet Κάντε λήψη αυτού του Excel φόρμες εξαπατήσει για να πάρετε μια συντόμευση μέσω του το αγαπημένο πρόγραμμα υπολογιστικών φύλλων στον κόσμο. Διαβάστε περισσότερα κοντά στο χέρι!

Πιστωτική εικόνα: Cico / Shutterstock

Εξερευνήστε περισσότερα σχετικά με: το Microsoft Excel, τα κόλπα αναζήτησης, το υπολογιστικό φύλλο.