Σε αυτό το άρθρο, θα μάθουμε πώς να αναζητάτε την τιμή μεταξύ δύο αριθμών στο Excel.
Σενάριο:
Είναι εύκολο να αναζητήσετε μια τιμή με ένα κριτήριο σε έναν πίνακα. Μπορούμε απλά να χρησιμοποιήσουμε το VLOOKUP. Αλλά τι θα μπορούσαμε να κάνουμε αν αυτά τα κριτήρια πολλαπλών στηλών ταιριάζουν στα δεδομένα σας και πρέπει να αναζητήσουμε σε πολλές στήλες για να αντιστοιχίσουμε μια τιμή. Ας μάθουμε πώς μπορεί να λυθεί αυτό το πρόβλημα χρησιμοποιώντας διαφορετικές εκδόσεις αναζήτησης του excel
LOOKUP τιμή μεταξύ δύο αριθμών
Χρησιμοποιώντας τον τύπο VLOOKUP
Στην κατά προσέγγιση αντιστοίχιση VLOOKUP, αν δεν βρεθεί μια τιμή, αντιστοιχεί η τελευταία τιμή που είναι μικρότερη από την τιμή αναζήτησης και επιστρέφεται η τιμή από το δεδομένο ευρετήριο στήλης.
Γενικός τύπος για την τιμή LOOKUP μεταξύ δύο αριθμών:
| = VLOOKUP (τιμή, πίνακας, lookup_col, 1) |
Και ένα ακόμη πράγμα για το Vlookup είναι ότι αναζητά την τιμή στη στήλη και αν δεν βρει την τιμή στον πίνακα στηλών, τότε ταιριάζει και επιστρέφει την τιμή που είναι μικρότερη από αυτήν την τιμή στον πίνακα πίνακα.
Σημείωση: Το VLOOKUP από προεπιλογή ταιριάζει κατά προσέγγιση, αν παραλείψουμε τη μεταβλητή αναζήτησης εύρους. Μια κατά προσέγγιση αντιστοίχιση είναι χρήσιμη όταν θέλετε να κάνετε έναν κατά προσέγγιση αγώνα και όταν έχετε ταξινομήσει τον πίνακα πίνακα με αύξουσα σειρά.
Χρησιμοποιώντας τον τύπο INDEX και MATCH
Ο τύπος INDEX και MATCH κάνει το ίδιο έργο με το παραπάνω αλλά με διαφορετική σύνταξη. Αλλά αν πρέπει να επιλέξετε μόνο αυτό με το οποίο λειτουργίες σας βολεύουν
Generic Formula to LOOKUP τιμή μεταξύ δύο αριθμών
| = INDEX (περιοχή_αναζήτησης, MATCH (1, INDEX ((κριτήρια1 = εύρος 1)*(κριτήρια2 = εύρος2),0,1),0)) |
lookup_range: το εύρος από το οποίο θέλετε να ανακτήσετε την τιμή.
Κριτήρια 1, Κριτήρια 2, Κριτήρια Ν: Αυτά είναι τα κριτήρια που θέλετε να ταιριάξετε στο εύρος 1, εύρος 2 και εύρος Ν. Μπορείτε να έχετε έως 270 κριτήρια - ζεύγη εύρους.
Range1, range2, rangeN: Αυτά είναι τα εύρη στα οποία θα ταιριάξετε τα αντίστοιχα κριτήριά σας.
Παράδειγμα:
Όλα αυτά μπορεί να προκαλούν σύγχυση στην κατανόηση. Ας καταλάβουμε πώς να χρησιμοποιήσετε τη συνάρτηση χρησιμοποιώντας ένα παράδειγμα. Εδώ έχουμε φοιτητική ταυτότητα και τις αντίστοιχες βαθμολογίες τους σε ένα τεστ. Τώρα έπρεπε να πάρουμε τους βαθμούς για κάθε μαθητή αναζητώντας τον πίνακα του βαθμολογικού συστήματος.
Για να το κάνουμε αυτό, θα χρησιμοποιήσουμε ένα χαρακτηριστικό της συνάρτησης VLOOKUP, το οποίο είναι εάν η συνάρτηση VLOOKUP δεν βρει την ακριβή αντιστοίχιση, αναζητά την αντιστοίχιση περίπου στον πίνακα μόνο και μόνο αν το τελευταίο όρισμα της συνάρτησης είναι είτε ΑΛΗΘΗΣ ή 1.
Ο πίνακας βαθμολογιών / βαθμολογιών πρέπει να έχει αύξουσα σειρά
Χρησιμοποιήστε τον τύπο:
| = VLOOKUP (Β2, πίνακας, 2, 1) |
Πώς λειτουργεί;
Η συνάρτηση Vlookup αναζητά την τιμή 40 στη στήλη των σημείων και επιστρέφει την αντίστοιχη τιμή, αν ταιριάζει. Εάν δεν ταιριάζει, τότε η συνάρτηση αναζητά τη μικρότερη τιμή από την τιμή αναζήτησης (δηλαδή 40) και επιστρέφει το αποτέλεσμά της.
Εδώ το table_array ονομάζεται εύρος ως τραπέζι στον τύπο και το Β2 δίνεται ως αναφορά κυττάρων.
Όπως μπορείτε να δείτε, πήραμε τον βαθμό για τον πρώτο μαθητή. Τώρα για να πάρουμε όλους τους άλλους Βαθμούς, θα χρησιμοποιήσουμε συντόμευση Ctrl + D ή χρησιμοποιήστε την επιλογή drag down cell στο excel.
Ακολουθούν όλοι οι Βαθμοί της τάξης που χρησιμοποιούν τη συνάρτηση VLOOKUP.
LOOKUP value μεταξύ δύο αριθμών στον πίνακα εργαζομένων
Έχουμε έναν πίνακα που περιέχει τα στοιχεία όλων των εργαζομένων σε έναν οργανισμό σε ξεχωριστό φύλλο. Η πρώτη στήλη περιέχει το αναγνωριστικό αυτών των υπαλλήλων. Έχω ονομάσει αυτόν τον πίνακα ως emp_data.
Τώρα το φύλλο αναζήτησής μου πρέπει να πάρει τις πληροφορίες του υπαλλήλου του οποίου το αναγνωριστικό είναι γραμμένο στο κελί B3. Έχω ονομάσει B3 ως αναγνωριστικό.
Για ευκολία κατανόησης, όλες οι επικεφαλίδες της στήλης είναι ακριβώς στην ίδια σειρά με τον πίνακα emp_data.
Τώρα γράψτε τον παρακάτω τύπο στο κελί C3 για να ανακτήσετε τη ζώνη του αναγνωριστικού υπαλλήλου που γράφεται στο Β3.
| = VLOOKUP (ID, Emp_Data, 2,0) |
Αυτό θα επιστρέψει τη ζώνη του Id υπαλλήλου 1-1830456593 επειδή η στήλη αριθμός 2 στη βάση δεδομένων περιέχει τη ζώνη των εργαζομένων.
Αντιγράψτε αυτόν τον τύπο στα υπόλοιπα κελιά και αλλάξτε τον αριθμό στήλης στον τύπο για να λάβετε όλες τις πληροφορίες των εργαζομένων.
Μπορείτε να δείτε όλες τις πληροφορίες που σχετίζονται με το αναφερόμενο αναγνωριστικό στο κελί B3. Όποιο αναγνωριστικό και αν γράψετε στο κελί B3, όλες οι πληροφορίες θα ανακτηθούν χωρίς να γίνει καμία αλλαγή στον τύπο.
Πως λειτουργεί αυτό?
Δεν υπάρχει τίποτα περίπλοκο. Απλώς χρησιμοποιούμε τη συνάρτηση VLOOKUP για αναζήτηση αναγνωριστικού και στη συνέχεια ανάκτηση της αναφερόμενης στήλης. Εξασκηθείτε στο VLOOKUP χρησιμοποιώντας τέτοια δεδομένα για να κατανοήσετε περισσότερο το VLOOKUP.
Ανάκτηση δεδομένων εργαζομένων χρησιμοποιώντας επικεφαλίδες
Στο παραπάνω παράδειγμα, είχαμε όλες τις στήλες οργανωμένες με την ίδια σειρά, αλλά θα υπάρξουν στιγμές που θα έχετε μια βάση δεδομένων που θα έχει εκατοντάδες στήλες. Σε τέτοιες περιπτώσεις, αυτή η μέθοδος ανάκτησης πληροφοριών εργαζομένων δεν θα είναι καλή. Θα ήταν καλύτερο εάν ο τύπος μπορεί να εξετάσει την επικεφαλίδα της στήλης και να λάβει δεδομένα από αυτήν τη στήλη από τον πίνακα εργαζομένων.
Έτσι, για να ανακτήσουμε την τιμή από τον πίνακα χρησιμοποιώντας την επικεφαλίδα στήλης, θα χρησιμοποιήσουμε μια μέθοδο αναζήτησης διπλής κατεύθυνσης ή θα πούμε δυναμική στήλη VLOOKUP.
Χρησιμοποιήστε αυτόν τον τύπο στο κελί C3 και αντιγράψτε στα υπόλοιπα κελιά. Δεν χρειάζεται να αλλάξετε τίποτα στον τύπο, όλα θα ληφθούν από το thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Αυτός ο τύπος ανακτά απλώς όλες τις πληροφορίες από αντιστοιχισμένες στήλες. Μπορείτε να μπερδέψετε τις κεφαλίδες στην αναφορά, αυτό δεν θα κάνει καμία διαφορά. Όποια επικεφαλίδα είναι γραμμένη στο παραπάνω κελί, τα αντίστοιχα δεδομένα περιέχουν.
Πως λειτουργεί αυτό?
Αυτό είναι απλά ένα δυναμικό VLOOKUP. Μπορείτε να διαβάσετε για αυτό εδώ. Αν το εξηγήσω εδώ, θα γίνει ένα πολύ μεγάλο άρθρο.
Ανάκτηση ταυτότητας υπαλλήλου με Partial Match
Μπορεί να συμβεί να μην θυμάστε ολόκληρο το αναγνωριστικό ενός υπαλλήλου, αλλά εξακολουθείτε να θέλετε να ανακτήσετε τις πληροφορίες κάποιου αναγνωριστικού. Σε τέτοιες περιπτώσεις το μερικό ταίριασμα VLOOKUP είναι η καλύτερη λύση.
Για παράδειγμα, εάν γνωρίζω ότι κάποιο αναγνωριστικό περιέχει 2345 αλλά δεν γνωρίζω ολόκληρο το αναγνωριστικό. Εάν εισαγάγω αυτόν τον αριθμό στο Cell C3, η έξοδος θα είναι όπως.
Δεν παίρνουμε τίποτα. Αφού τίποτα δεν ταιριάζει με τον 2345 στον πίνακα. Τροποποιήστε τον παραπάνω τύπο με αυτόν τον τρόπο.
| =VLOOKUP("*"&ταυτότητα&"*", Emp_Data,ΑΓΩΝΑΣ(C2, Emp_Data_Headers, 0), 0) |
Αντιγράψτε αυτό σε ολόκληρη τη σειρά. Και τώρα έχετε τις πληροφορίες του πρώτου υπαλλήλου που περιέχει αυτόν τον αριθμό.
Λάβετε υπόψη ότι θα λάβουμε το πρώτο αναγνωριστικό που περιέχει τον αντίστοιχο αριθμό στη στήλη Emp Id. Εάν οποιοδήποτε άλλο αναγνωριστικό περιέχει τον ίδιο αριθμό, αυτός ο τύπος δεν θα ανακτήσει τις πληροφορίες αυτού του υπαλλήλου.
Εάν θέλετε να λάβετε όλα τα αναγνωριστικά υπαλλήλου που περιέχουν τον ίδιο αριθμό, χρησιμοποιήστε τον τύπο που αναζητά όλες τις αντιστοιχισμένες τιμές.
Χρησιμοποιώντας τον τύπο INDEX και MATCH
Εδώ έχουμε έναν πίνακα δεδομένων. Θέλω να τραβήξω το Όνομα του πελάτη χρησιμοποιώντας την Ημερομηνία Κράτησης, τον Κατασκευαστή και την Περιοχή. Εδώ λοιπόν έχω τρία κριτήρια και ένα εύρος αναζήτησης.
Γράψτε αυτόν τον τύπο στο κελί I4 πατήστε enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Πως δουλεύει:
Γνωρίζουμε ήδη πώς λειτουργούν οι λειτουργίες INDEX και MATCH στο EXCEL, οπότε δεν πρόκειται να το εξηγήσω εδώ. Θα μιλήσουμε για το κόλπο που χρησιμοποιήσαμε εδώ.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Το κύριο μέρος είναι αυτό. Κάθε μέρος αυτής της δήλωσης επιστρέφει έναν πίνακα αληθούς ψευδούς.
Όταν πολλαπλασιάζονται οι τιμές boole επιστρέφουν έναν πίνακα 0 και 1. Ο πολλαπλασιασμός λειτουργεί ως τελεστής AND. Hense όταν όλες οι τιμές είναι αληθινές μόνο τότε επιστρέφει 1 άλλο 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Αυτό θα επιστρέψει συνολικά
| {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}*
{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {ΛΑΘΟΣ; ΛΑΘΟΣ; ΛΑΘΟΣ; ΑΛΗΘΙΝΟΣ; ALΕΥΔΙΚΟΣ |
Που θα μεταφραστεί σε
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Η συνάρτηση INDEX θα επιστρέψει τον ίδιο πίνακα ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) στη συνάρτηση MATCH ως πίνακας αναζήτησης.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Η συνάρτηση MATCH θα αναζητήσει 1 στον πίνακα {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Και θα επιστρέψει τον αριθμό ευρετηρίου του πρώτου 1 που βρέθηκε στον πίνακα. Ο οποίος είναι 8 εδώ
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Τέλος, το INDEX θα επιστρέψει την τιμή από το δεδομένο εύρος (E2: E16) στον ευρεθέντα δείκτη (8).
Εδώ είναι όλες οι σημειώσεις παρατήρησης που χρησιμοποιούν τον τύπο στο Excel
Σημειώσεις:
- Η στήλη πίνακα σημείων / βαθμολογιών πρέπει να είναι σε αύξουσα σειρά, διαφορετικά η συνάρτηση μπορεί να δώσει το λάθος αποτέλεσμα.
- Η συνάρτηση VLOOKUP αναζητά την τιμή στην πρώτη σειρά του Table_array και εξάγει τις αντίστοιχες τιμές μόνο δεξιά της περιοχής αναζήτησης.
- Το τελευταίο όρισμα της συνάρτησης VLOOKUP της συνάρτησης πρέπει να οριστεί σε TRUE ή 1 για να λάβει την κατά προσέγγιση αντιστοίχιση.
- Η συνάρτηση VLOOKUP επιστρέφει σφάλμα εάν η διεύθυνση του βιβλίου εργασίας δεν είναι έγκυρη ή λανθασμένη.
- Η συνάρτηση VLOOKUP επιστρέφει ένα σφάλμα εάν η τιμή δεν ταιριάζει.
Ελπίζουμε ότι αυτό το άρθρο σχετικά με τον τρόπο ΑΝΑΚΟΙΝΩΣΗΣ της τιμής μεταξύ δύο αριθμών στο Excel είναι επεξηγηματικό. Βρείτε περισσότερα άρθρα σχετικά με τον υπολογισμό των τιμών και τους σχετικούς τύπους Excel εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε τα με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email.
Τρόπος χρήσης της λειτουργίας Vlookup στην Επικύρωση δεδομένων : Περιορίστε τους χρήστες να επιτρέπουν τιμές από τον πίνακα αναζήτησης χρησιμοποιώντας το πλαίσιο τύπου φόρτωσης δεδομένων στο Excel. Το πλαίσιο τύπου στην επικύρωση δεδομένων σάς επιτρέπει να επιλέξετε τον τύπο του απαιτούμενου περιορισμού.
Πώς να ανακτήσετε την πιο πρόσφατη τιμή στο Excel : Είναι συνηθισμένο να ενημερώνονται οι τιμές σε οποιαδήποτε επιχείρηση και η χρήση των πιο πρόσφατων τιμών για οποιαδήποτε αγορά ή πώληση είναι απαραίτητη. Για να ανακτήσετε την πιο πρόσφατη τιμή από μια λίστα στο Excel, χρησιμοποιούμε τη συνάρτηση LOOKUP. Η λειτουργία LOOKUP συγκεντρώνει την πιο πρόσφατη τιμή.
Συνάρτηση VLOOKUP για τον υπολογισμό της βαθμολογίας στο Excel : Για τον υπολογισμό των βαθμών IF και IFS δεν είναι οι μόνες συναρτήσεις που μπορείτε να χρησιμοποιήσετε. Το VLOOKUP είναι πιο αποτελεσματικό και δυναμικό για τέτοιους υπολογισμούς υπό όρους. Για να υπολογίσετε βαθμούς χρησιμοποιώντας το VLOOKUP μπορούμε να χρησιμοποιήσουμε αυτόν τον τύπο.
17 πράγματα για το Excel VLOOKUP : Το VLOOKUP χρησιμοποιείται συχνότερα για την ανάκτηση αντιστοιχισμένων τιμών, αλλά το VLOOKUP μπορεί να κάνει πολλά περισσότερα από αυτό. Ακολουθούν 17 πράγματα για το VLOOKUP που πρέπει να γνωρίζετε για να τα χρησιμοποιήσετε αποτελεσματικά.
ΔΕΙΤΕ το πρώτο κείμενο από μια λίστα στο Excel : Η λειτουργία VLOOKUP λειτουργεί καλά με χαρακτήρες μπαλαντέρ. Μπορούμε να το χρησιμοποιήσουμε για να εξαγάγουμε την πρώτη τιμή κειμένου από μια συγκεκριμένη λίστα στο excel. Εδώ είναι ο γενικός τύπος.
LOOKUP ημερομηνία με την τελευταία τιμή στη λίστα : Για να ανακτήσουμε την ημερομηνία που περιέχει την τελευταία τιμή χρησιμοποιούμε τη συνάρτηση LOOKUP. Αυτή η συνάρτηση ελέγχει για το κελί που περιέχει την τελευταία τιμή σε ένα διάνυσμα και στη συνέχεια χρησιμοποιεί αυτήν την αναφορά για να επιστρέψει την ημερομηνία.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας : Γίνετε ταχύτεροι στις εργασίες σας στο Excel. Αυτές οι συντομεύσεις θα σας βοηθήσουν να αυξήσετε την αποδοτικότητα της εργασίας σας στο Excel.
Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε τη συνάρτηση IF στο Excel : Η δήλωση IF στο Excel ελέγχει την κατάσταση και επιστρέφει μια συγκεκριμένη τιμή εάν η συνθήκη είναι TRUE ή επιστρέφει μια άλλη συγκεκριμένη τιμή εάν είναι FALSE.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.