Αναζήτηση στο Excel για μια τιμή ανά συνθήκη. Αναζήτηση στο Microsoft Excel. Δημιουργία πρόσθετων στηλών

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

Συνάρτηση VLOOKUP().

Ας υποθέσουμε ότι έχετε έναν πίνακα με δεδομένα υπαλλήλων. Η πρώτη στήλη αποθηκεύει τον αριθμό προσωπικού του υπαλλήλου, τα υπόλοιπα - άλλα δεδομένα (πλήρες όνομα, τμήμα κ.λπ.). Εάν έχετε αριθμό προσωπικού, μπορείτε να χρησιμοποιήσετε τη συνάρτηση VLOOKUP για να επιστρέψετε ορισμένες πληροφορίες σχετικά με τον υπάλληλο. Σύνταξη τύπου =VLOOKUP(τιμή_αναζήτησης, πίνακας, αριθμός_στήλης, [interval_lookup]).Λέει στο Excel: «Βρείτε μέσα τραπέζιμια σειρά της οποίας το πρώτο κελί ταιριάζει αναζήτηση_τιμής,και επιστρέψτε την τιμή του κελιού με τον σειριακό αριθμό στήλη_αριθμός».

Υπάρχουν όμως περιπτώσεις που έχετε το όνομα ενός υπαλλήλου και πρέπει να επιστρέψετε τον αριθμό προσωπικού. Στο σχήμα στο κελί A10 είναι το όνομα του υπαλλήλου και πρέπει να προσδιορίσετε τον αριθμό προσωπικού στο κελί B10.

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

Συνάρτηση INDEX().

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

Ας ξεκινήσουμε με τη συνάρτηση INDEX. Τρομερό όνομα. Όταν κάποιος λέει "ευρετήριο", δεν εμφανίζεται ούτε ένας συσχετισμός στο μυαλό μου σχετικά με το τι κάνει αυτή η λειτουργία. Και απαιτεί τρία επιχειρήματα: =INDEX(πίνακας, αριθμός_γραμμής, [αριθμός_στήλης]).

Με απλά λόγια, το Excel μεταβαίνει σε έναν πίνακα δεδομένων και επιστρέφει την τιμή στη διασταύρωση μιας καθορισμένης γραμμής και στήλης. Φαίνεται απλό. Έτσι, ο τύπος =INDEX($A$2:$C$6,4,2) θα επιστρέψει την τιμή που βρίσκεται στο κελί B5.

Σε σχέση με το πρόβλημά μας, για να επιστρέψουμε τον αριθμό προσωπικού του υπαλλήλου, ο τύπος θα πρέπει να μοιάζει με αυτό =INDEX($A$2:$A$6;?;1). Μοιάζει σαν ανοησία, αλλά αν αντικαταστήσουμε το ερωτηματικό με τον τύπο ΜΑΤΣ, έχουμε λύση.

Συνάρτηση MATCH().

Η σύνταξη αυτής της συνάρτησης είναι: =MATCH(τιμή_αναζήτησης, συστοιχία_αναζήτησης, [τύπος_αντιστοιχίας]).

Λέει στο Excel: «Βρε αναζήτηση_τιμήςστον πίνακα δεδομένων και επιστρέψτε τον αριθμό σειράς του πίνακα στον οποίο εμφανίζεται αυτή η τιμή." Έτσι, για να βρείτε ποια γραμμή είναι το όνομα του υπαλλήλου στο κελί A10, πρέπει να γράψετε τον τύπο =MATCH(A10, $B$2:$B$6, 0). Εάν το όνομα "Colin Farel" βρίσκεται στο κελί A10, τότε το MATCH θα επιστρέψει την 5η σειρά του πίνακα B2:B6.

Λοιπόν, βασικά, αυτό είναι. Η συνάρτηση MATCH λέει στη συνάρτηση INDEX ποια σειρά να αναζητήσει μια τιμή. Αντικαταστήστε το ερωτηματικό στον τύπο INDEX με τον τύπο SEARCH και θα λάβετε το ισοδύναμο ενός VLOOKUP με τη δυνατότητα αναζήτησης δεδομένων που βρίσκονται στα αριστερά της στήλης κλειδιού. Θα βγει ως εξής:

INDEX($A$2:$B$6,MATCH(A10,$B$2:$B$6,0),1)

Στην αρχή ήταν ασυνήθιστο, υπήρχε ένας τεράστιος πειρασμός να μπει μια άλλη στήλη και να δουλέψει όπως πάντα. Όμως με την πάροδο του χρόνου, η χρήση της συνάρτησης INDEX έγινε συνήθεια. Αποδείχθηκε ότι ήταν πιο γρήγορο και απαιτούσε λιγότερους χειρισμούς. Έτσι, την επόμενη φορά που θέλετε να ορίσετε έναν αριθμό στήλης σε έναν αρνητικό αριθμό σε ένα VLOOKUP, χρησιμοποιήστε έναν συνδυασμό δύο περίεργων συναρτήσεων, INDEX και MATCH, για να λύσετε το πρόβλημά σας.

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

Σε αυτό το άρθρο

Βρείτε τιμές σε μια λίστα κατακόρυφα με την ακριβή αντιστοίχιση

Μπορείτε να χρησιμοποιήσετε τη συνάρτηση VLOOKUP ή έναν συνδυασμό των συναρτήσεων INDEX και MATCH για να εκτελέσετε αυτήν την εργασία.

Παραδείγματα χρήσης της συνάρτησης VLOOKUP

Λειτουργία VLOOKUP.

Παραδείγματα ΕΥΡΕΤΗΡΙΩΝ και ΑΓΩΝΕΣ

Τι σημαίνει:

=INDEX(πρέπει να επιστρέψετε την τιμή από το C2:C10, το οποίο θα αντιστοιχεί στο MATCH (η πρώτη τιμή "Cabbage" στον πίνακα B2:B10))

Ο τύπος αναζητά την πρώτη τιμή στο κελί C2:C10 που αντιστοιχεί λάχανο(στο B7) και επιστρέφει την τιμή στο C7 ( 100 ) - η πρώτη τιμή που αντιστοιχεί λάχανο.

Για περισσότερες πληροφορίες, δείτε τη συνάρτηση INDEX και τη λειτουργία MATCH.

Βρείτε τιμές σε μια λίστα κατακόρυφα κατά προσέγγιση

Για να το κάνετε αυτό, χρησιμοποιήστε τη συνάρτηση VLOOKUP.

Σπουδαίος:Βεβαιωθείτε ότι οι τιμές στην πρώτη σειρά είναι ταξινομημένες με αύξουσα σειρά.

Στο παραπάνω παράδειγμα, η συνάρτηση VLOOKUP αναζητά το όνομα ενός μαθητή που έχει 6 καθυστερήσεις στην περιοχή A2:B7. Δεν υπάρχει εγγραφή στον πίνακα για 6 καθυστερεί, οπότε η συνάρτηση VLOOKUP αναζητά την επόμενη υψηλότερη αντιστοίχιση κάτω από το 6 και βρίσκει την τιμή 5 που σχετίζεται με το όνομα Ντέιβ, και επομένως επιστρέφει Ντέιβ.

Για περισσότερες πληροφορίες, δείτε τη λειτουργία VLOOKUP.

Εύρεση κατακόρυφων τιμών σε λίστα άγνωστου μεγέθους με ακριβή αντιστοίχιση

Για να εκτελέσετε αυτήν την εργασία, χρησιμοποιήστε τις λειτουργίες OFFSET και MATCH.

Σημείωση:Αυτή η προσέγγιση χρησιμοποιείται εάν τα δεδομένα βρίσκονται σε ένα εξωτερικό εύρος δεδομένων που ενημερώνετε καθημερινά. Γνωρίζετε ότι η στήλη Β έχει Τιμή, αλλά δεν γνωρίζετε πόσες σειρές δεδομένων επιστρέφει ο διακομιστής και η πρώτη στήλη δεν ταξινομείται αλφαβητικά.

Γ1είναι το επάνω αριστερό κελί της περιοχής (ονομάζεται επίσης αρχικό κελί).

Ταίριασμα ("πορτοκάλια"; C2: C7; 0)αναζητά πορτοκαλί χρώμα στην περιοχή C2:C7. Δεν πρέπει να συμπεριλάβετε το αρχικό κελί στην περιοχή.

1 - τον αριθμό των στηλών στα δεξιά του αρχικού κελιού για τις οποίες πρέπει να επιστραφεί η επιστρεφόμενη τιμή. Στο παράδειγμά μας, η τιμή επιστροφής βρίσκεται στη στήλη D, Εκπτώσεις.

Βρείτε τιμές σε μια λίστα οριζόντια κατά ακριβή αντιστοίχιση

Για την εκτέλεση αυτής της εργασίας, χρησιμοποιείται η συνάρτηση GLOOKUP. Παρακάτω είναι ένα παράδειγμα.

Η συνάρτηση LOOKUP αναζητά μια στήλη Εκπτώσειςκαι επιστρέφει την τιμή από τη γραμμή 5 στο καθορισμένο εύρος.

Για περισσότερες πληροφορίες, δείτε Λειτουργίες GLOOKUP.

Δημιουργήστε έναν τύπο αναζήτησης χρησιμοποιώντας τον Οδηγό αναζήτησης (μόνο Excel 2007)

Σημείωση:Το πρόσθετο του Οδηγού αναζήτησης έχει διακοπεί στο Excel 2010. Αυτή η λειτουργία έχει αντικατασταθεί από τον Οδηγό συναρτήσεων και τις διαθέσιμες δυνατότητες αναζήτησης και αναφοράς (αναφοράς).

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

Έχουμε έναν πίνακα που καταγράφει τους όγκους πωλήσεων ορισμένων προϊόντων σε διαφορετικούς μήνες. Πρέπει να βρείτε δεδομένα στον πίνακα και τα κριτήρια αναζήτησης θα είναι οι επικεφαλίδες γραμμής και στήλης. Αλλά η αναζήτηση πρέπει να γίνει χωριστά ανά εύρος γραμμής ή στήλης. Δηλαδή, θα χρησιμοποιηθεί μόνο ένα από τα κριτήρια. Επομένως, η συνάρτηση INDEX δεν μπορεί να χρησιμοποιηθεί εδώ, αλλά χρειάζεται ένας ειδικός τύπος.

Εύρεση τιμών σε πίνακα Excel

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

Φύλλο με πίνακα για αναζήτηση τιμών κάθετα και οριζόντια:

Πάνω από τον ίδιο τον πίνακα υπάρχει μια γραμμή με τα αποτελέσματα. Στο κελί Β1 εισάγουμε το κριτήριο για το ερώτημα αναζήτησης, δηλαδή την επικεφαλίδα της στήλης ή το όνομα της γραμμής. Και στο κελί D1, ο τύπος αναζήτησης θα πρέπει να επιστρέψει το αποτέλεσμα του υπολογισμού της αντίστοιχης τιμής. Μετά από αυτό, ο δεύτερος τύπος θα λειτουργήσει στο κελί F1, το οποίο θα χρησιμοποιεί ήδη τις τιμές των κελιών B1 και D1 ως κριτήρια για την αναζήτηση για τον αντίστοιχο μήνα.

Εύρεση τιμής σε μια συμβολοσειρά του Excel

Τώρα ανακαλύπτουμε σε ποιον μέγιστο όγκο και σε ποιο μήνα ήταν η μέγιστη πώληση του Προϊόντος 4.

Για αναζήτηση ανά στήλες:

Βρέθηκε σε ποιο μήνα και ποια ήταν η μεγαλύτερη πώληση του Προϊόντος 4 σε διάστημα δύο τριμήνων.

Η αρχή του τύπου για την εύρεση μιας τιμής σε μια σειρά του Excel:

Το πρώτο όρισμα της συνάρτησης VLOOKUP (Κάθετη προβολή) καθορίζει έναν σύνδεσμο προς το κελί όπου βρίσκεται το κριτήριο αναζήτησης. Το δεύτερο όρισμα καθορίζει το εύρος των κελιών που θα προβληθούν κατά την αναζήτηση. Το τρίτο όρισμα της συνάρτησης VLOOKUP πρέπει να υποδεικνύει τον αριθμό της στήλης από τον οποίο θα πρέπει να ληφθεί η τιμή έναντι της γραμμής με το όνομα Προϊόν 4. Επειδή όμως δεν γνωρίζουμε αυτόν τον αριθμό εκ των προτέρων, χρησιμοποιούμε τη συνάρτηση COLUMN για να δημιουργήσουμε έναν πίνακα αριθμών στηλών για το εύρος B4:G15.

Αυτό επιτρέπει στη συνάρτηση VLOOKUP να συλλέξει μια ολόκληρη σειρά τιμών. Ως αποτέλεσμα, όλες οι αντίστοιχες τιμές για κάθε στήλη στη σειρά Προϊόν 4 αποθηκεύονται στη μνήμη (δηλαδή: 360; 958; 201; 605; 462; 832). Μετά από αυτό, η συνάρτηση MAX μπορεί να πάρει μόνο τον μέγιστο αριθμό από αυτόν τον πίνακα και να τον επιστρέψει ως τιμή για το κελί D1, ως αποτέλεσμα του υπολογισμού του τύπου.

Όπως μπορείτε να δείτε, ο σχεδιασμός του τύπου είναι απλός και συνοπτικός. Με βάση αυτό, μπορείτε να βρείτε άλλους δείκτες για ένα συγκεκριμένο προϊόν με παρόμοιο τρόπο. Για παράδειγμα, η ελάχιστη ή μέση τιμή του όγκου πωλήσεων χρησιμοποιώντας τις συναρτήσεις MIN ή AVERAGE. Τίποτα δεν σας εμποδίζει να εφαρμόσετε αυτήν τη φόρμουλα σκελετού χρησιμοποιώντας πιο σύνθετες λειτουργίες για να εφαρμόσετε την πιο άνετη ανάλυση της αναφοράς πωλήσεων.

Πώς να αποκτήσετε κεφαλίδες στηλών με βάση την αρχή ενός κελιού;

Για παράδειγμα, πόσο αποτελεσματικά εμφανίσαμε τον μήνα κατά τον οποίο υπήρξε μέγιστη πώληση χρησιμοποιώντας τον δεύτερο τύπο. Δεν είναι δύσκολο να παρατηρήσουμε ότι στον δεύτερο τύπο χρησιμοποιήσαμε τον σκελετό του πρώτου τύπου χωρίς τη συνάρτηση MAX. Η κύρια δομή του τύπου είναι: VLOOKUP(B1,A5:G14,COLUMN(B5:G14),0). Αντικαταστήσαμε τη συνάρτηση MAX με MATCH, η οποία χρησιμοποιεί την τιμή που ελήφθη από τον προηγούμενο τύπο ως πρώτο όρισμα. Λειτουργεί πλέον ως κριτήριο αναζήτησης του μήνα. Και ως αποτέλεσμα, η συνάρτηση ΑΝΑΖΗΤΗΣΗ μας επιστρέφει τον αριθμό της στήλης 2, όπου βρίσκεται η μέγιστη τιμή του όγκου πωλήσεων για το προϊόν 4 Μετά την οποία περιλαμβάνεται η συνάρτηση INDEX στην εργασία, η οποία επιστρέφει την τιμή κατά ημερομηνία και αριθμό στήλης από το εύρος που καθορίζεται στα ορίσματά του. Εφόσον έχουμε αριθμό στήλης 2 και ο αριθμός σειράς στο εύρος όπου είναι αποθηκευμένα τα ονόματα των μηνών θα είναι σε κάθε περίπτωση, τότε πρέπει να χρησιμοποιήσουμε μόνο τη συνάρτηση INDEX για να λάβουμε την αντίστοιχη τιμή από το εύρος B4. G4 - Φεβρουάριος (δεύτερος μήνας).

Εύρεση τιμής σε στήλη Excel

Η δεύτερη έκδοση της εργασίας θα ήταν η αναζήτηση στον πίνακα χρησιμοποιώντας το όνομα του μήνα ως κριτήριο. Σε τέτοιες περιπτώσεις, πρέπει να αλλάξουμε τον σκελετό του τύπου μας: αντικαταστήστε τη συνάρτηση VLOOKUP με HLOOKUP και τη συνάρτηση COLUMN με ROW.

Αυτό θα μας επιτρέψει να μάθουμε τι όγκο και ποιο προϊόν ήταν οι μέγιστες πωλήσεις σε έναν συγκεκριμένο μήνα.

Για να βρείτε ποιο προϊόν είχε τον μέγιστο όγκο πωλήσεων σε έναν συγκεκριμένο μήνα, θα πρέπει:

Πώς λειτουργεί ο τύπος για την εύρεση μιας τιμής σε μια στήλη του Excel:

Στο πρώτο όρισμα της συνάρτησης HLOO (Οριζόντια προβολή), υποδεικνύουμε έναν σύνδεσμο προς το κελί με το κριτήριο αναζήτησης. Το δεύτερο όρισμα καθορίζει μια αναφορά στο εύρος του πίνακα που προβάλλεται. Το τρίτο όρισμα δημιουργείται από τη συνάρτηση ROW, η οποία δημιουργεί έναν πίνακα 10 στοιχείων αριθμών γραμμών στη μνήμη. Αφού έχουμε 10 σειρές στο τμήμα του πίνακα.

Στη συνέχεια, η συνάρτηση GLOOKUP, χρησιμοποιώντας κάθε αριθμό γραμμής με τη σειρά του, δημιουργεί μια σειρά από αντίστοιχες τιμές πωλήσεων από τον πίνακα για έναν συγκεκριμένο μήνα (Ιούνιο). Στη συνέχεια, η συνάρτηση MAX χρειάζεται μόνο να επιλέξει τη μέγιστη τιμή από αυτόν τον πίνακα.

ΠΡΟΣΟΧΗ! Όταν χρησιμοποιείτε τον τύπο σκελετού για άλλα προβλήματα, προσέχετε πάντα το δεύτερο και το τρίτο όρισμα της συνάρτησης αναζήτησης GLOOKUP. Ο αριθμός των σειρών που καλύπτονται στο εύρος που καθορίζεται στο όρισμα πρέπει να ταιριάζει με τον αριθμό των σειρών στον πίνακα. Και επίσης η αρίθμηση πρέπει να ξεκινά από τη δεύτερη γραμμή!

Ένας από τους πιο δημοφιλείς τελεστές μεταξύ των χρηστών του Excel είναι η συνάρτηση ΑΝΑΖΗΤΗΣΗ. Τα καθήκοντά του περιλαμβάνουν τον προσδιορισμό του αριθμού θέσης ενός στοιχείου σε έναν δεδομένο πίνακα δεδομένων. Αποφέρει το μεγαλύτερο όφελος όταν χρησιμοποιείται σε συνδυασμό με άλλους χειριστές. Ας καταλάβουμε ποια είναι η λειτουργία ΑΝΑΖΗΤΗΣΗ, και πώς μπορεί να χρησιμοποιηθεί στην πράξη.

Χειριστής ΑΝΑΖΗΤΗΣΗανήκει στην κατηγορία συναρτήσεων "Σύνδεσμοι και πίνακες". Αναζητά ένα δεδομένο στοιχείο στον καθορισμένο πίνακα και εμφανίζει τον αριθμό της θέσης του σε αυτό το εύρος σε ξεχωριστό κελί. Μάλιστα, ακόμη και το όνομά του το δηλώνει αυτό. Επίσης, αυτή η συνάρτηση, όταν χρησιμοποιείται σε συνδυασμό με άλλους τελεστές, τους λέει τον αριθμό θέσης ενός συγκεκριμένου στοιχείου για μετέπειτα επεξεργασία αυτών των δεδομένων.

Σύνταξη χειριστή ΑΝΑΖΗΤΗΣΗμοιάζει με αυτό:

MATCH(τιμή_αναζήτησης, συστοιχία_αναζήτησης, [τύπος_αντιστοιχίας])

Τώρα ας δούμε καθένα από αυτά τα τρία επιχειρήματα ξεχωριστά.

"Αξία αναζήτησης"- αυτό είναι το στοιχείο που πρέπει να βρεθεί. Μπορεί να έχει κείμενο, αριθμητική μορφή ή να έχει λογική τιμή. Αυτό το όρισμα μπορεί επίσης να είναι μια αναφορά σε ένα κελί που περιέχει οποιαδήποτε από τις παραπάνω τιμές.

"Πίνακας περιήγησης"– αυτή είναι η διεύθυνση του εύρους στο οποίο βρίσκεται η επιθυμητή τιμή. Είναι η θέση αυτού του στοιχείου σε αυτόν τον πίνακα που πρέπει να καθορίσει ο χειριστής ΑΝΑΖΗΤΗΣΗ.

"Τύπος που ταιριάζει"υποδεικνύει μια ακριβή αντιστοίχιση προς αναζήτηση ή μια ανακριβή. Αυτό το επιχείρημα μπορεί να έχει τρεις έννοιες: "1", «0» Και "-1". Όταν αξία «0» ο χειριστής αναζητά μόνο μια ακριβή αντιστοίχιση. Εάν έχει καθοριστεί μια τιμή "1", τότε ελλείψει ακριβούς αντιστοίχισης ΑΝΑΖΗΤΗΣΗεπιστρέφει το στοιχείο που βρίσκεται πιο κοντά σε αυτό με φθίνουσα σειρά. Εάν έχει καθοριστεί μια τιμή "-1", τότε εάν δεν βρεθεί ακριβής αντιστοίχιση, η συνάρτηση επιστρέφει το στοιχείο που βρίσκεται πλησιέστερα σε αυτήν με αύξουσα σειρά. Είναι σημαντικό, εάν δεν αναζητάτε μια ακριβή τιμή, αλλά για μια κατά προσέγγιση, ο πίνακας που προβάλλεται να ταξινομηθεί σε αύξουσα σειρά (τύπος αντιστοίχισης "1") ή φθίνουσα (τύπος αντιστοίχισης "-1").

Διαφωνία "Τύπος που ταιριάζει"είναι προαιρετική. Μπορεί να παραλειφθεί εάν δεν χρειάζεται. Σε αυτήν την περίπτωση η προεπιλεγμένη τιμή του είναι "1". Εφαρμόστε το επιχείρημα "Τύπος που ταιριάζει", πρώτα απ 'όλα, έχει νόημα μόνο κατά την επεξεργασία αριθμητικών τιμών, όχι κειμένου.

Αν ΑΝΑΖΗΤΗΣΗμε τις δεδομένες ρυθμίσεις δεν μπορεί να βρει το απαιτούμενο στοιχείο, ο χειριστής εμφανίζει ένα σφάλμα στο κελί "#Δ/Υ".

Κατά τη διεξαγωγή μιας αναζήτησης, ο χειριστής δεν κάνει διάκριση μεταξύ πεζών χαρακτήρων. Εάν υπάρχουν πολλές ακριβείς αντιστοιχίσεις στον πίνακα, τότε ΑΝΑΖΗΤΗΣΗεμφανίζει τη θέση του πρώτου από αυτά στο κελί.

Μέθοδος 1: Εμφάνιση της θέσης ενός στοιχείου σε μια περιοχή δεδομένων κειμένου

Ας δούμε την απλούστερη περίπτωση ως παράδειγμα, κατά τη χρήση ΑΝΑΖΗΤΗΣΗμπορείτε να προσδιορίσετε τη θέση του καθορισμένου στοιχείου σε μια σειρά δεδομένων κειμένου. Ας μάθουμε ποια θέση στην περιοχή στην οποία βρίσκονται τα ονόματα των προϊόντων καταλαμβάνει η λέξη "Ζάχαρη".

Μέθοδος 2: Αυτοματοποίηση της χρήσης του τελεστή MATCH

Παραπάνω εξετάσαμε την πιο πρωτόγονη περίπτωση χρήσης του τελεστή ΑΝΑΖΗΤΗΣΗ, αλλά ακόμη και αυτό μπορεί να αυτοματοποιηθεί.

Μέθοδος 3: Χρήση του τελεστή MATCH για αριθμητικές παραστάσεις

Τώρα ας δούμε πώς μπορείτε να χρησιμοποιήσετε ΑΝΑΖΗΤΗΣΗγια εργασία με αριθμητικές εκφράσεις.

Το καθήκον είναι να βρείτε ένα προϊόν με αξία πώλησης 400 ρούβλια ή το πλησιέστερο σε αύξουσα σειρά σε αυτό το ποσό.

Με παρόμοιο τρόπο, μπορείτε να αναζητήσετε την πλησιέστερη θέση "400"Φθίνων. Μόνο για αυτό πρέπει να φιλτράρετε τα δεδομένα με αύξουσα σειρά και στο πεδίο "Τύπος που ταιριάζει"ορίσματα συνάρτησης τιμή ορισμού "1".

Μέθοδος 4: Χρήση σε συνδυασμό με άλλους χειριστές

Είναι πιο αποτελεσματικό να χρησιμοποιείτε αυτήν τη συνάρτηση με άλλους τελεστές ως μέρος μιας σύνθετης φόρμουλας. Χρησιμοποιείται συχνότερα σε συνδυασμό με τη συνάρτηση ΔΕΙΚΤΗΣ. Αυτό το όρισμα εμφανίζει τα περιεχόμενα της περιοχής που καθορίζεται από τον αριθμό σειράς ή στήλης στο καθορισμένο κελί. Επιπλέον, η αρίθμηση, όπως σε σχέση με τον χειριστή ΑΝΑΖΗΤΗΣΗ, δεν εκτελείται σε σχέση με ολόκληρο το φύλλο, αλλά μόνο εντός του εύρους. Η σύνταξη αυτής της συνάρτησης είναι η εξής:

INDEX(πίνακας, αριθμός_σειράς, αριθμός_στήλης)

Επιπλέον, εάν ο πίνακας είναι μονοδιάστατος, τότε μόνο ένα από τα δύο ορίσματα μπορεί να χρησιμοποιηθεί: "Αριθμός σειράς"ή "Αριθμός στήλης".

Χαρακτηριστικό μιας δέσμης λειτουργιών ΔΕΙΚΤΗΣΚαι ΑΝΑΖΗΤΗΣΗείναι ότι το τελευταίο μπορεί να χρησιμοποιηθεί ως όρισμα για το πρώτο, δηλαδή να δείχνει τη θέση μιας γραμμής ή στήλης.

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

Όπως βλέπουμε, ο χειριστής ΑΝΑΖΗΤΗΣΗείναι μια πολύ βολική συνάρτηση για τον προσδιορισμό του σειριακού αριθμού ενός καθορισμένου στοιχείου σε έναν πίνακα δεδομένων. Αλλά τα οφέλη του αυξάνονται σημαντικά εάν χρησιμοποιείται σε σύνθετες φόρμουλες.