Σε αυτό το άρθρο, θα μάθουμε πώς να ανακτήσετε μια τιμή από μια λίστα που ταιριάζει με τα κριτήρια κατηγορίας και στοιχείων στο Excel.
Σενάριο:
Είναι εύκολο να βρείτε μια τιμή με ένα κριτήριο σε έναν πίνακα, για αυτό Θα μπορούσαμε απλά να χρησιμοποιήσουμε το VLOOKUP. Αλλά όταν δεν έχετε ένα μόνο κριτήριο στήλης στα δεδομένα σας και πρέπει να βρείτε κριτήρια πολλαπλών στηλών για να αντιστοιχίσετε μια τιμή, το VLOOKUP δεν βοηθά.
Γενικός τύπος τόσο για κατηγορία όσο και για αντικείμενο
| = INDEX (περιοχή αναζήτησης, MATCH (1, INDEX ((item1 = item_range) * (category2 = range_range), 0,1), 0)) |
lookup_range: Είναι το εύρος από το οποίο θέλετε να ανακτήσετε την τιμή.
Κριτήρια 1, Κριτήρια 2, Κριτήρια Ν: Αυτά είναι τα κριτήρια που θέλετε να ταιριάξετε στο εύρος 1, εύρος 2 και εύρος Ν. Μπορείτε να έχετε έως 270 κριτήρια - ζεύγη εύρους.
Range1, range2, rangeN: Αυτά είναι τα εύρη στα οποία θα ταιριάξετε τα αντίστοιχα κριτήριά σας
Παράδειγμα:
Όλα αυτά μπορεί να προκαλούν σύγχυση στην κατανόηση. Ας καταλάβουμε πώς να χρησιμοποιήσετε τη συνάρτηση χρησιμοποιώντας ένα παράδειγμα. Εδώ έχουμε έναν πίνακα δεδομένων. Θέλω να τραβήξω το Όνομα του πελάτη χρησιμοποιώντας την Ημερομηνία Κράτησης, τον Κατασκευαστή και την Περιοχή. Εδώ λοιπόν έχω τρία κριτήρια και ένα εύρος αναζήτησης.
Γράψτε αυτόν τον τύπο στο κελί 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) Αυτό θα επιστρέψει συνολικά
Που θα μεταφραστεί σε
| {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).
Εάν μπορείτε να πατήσετε CTRL + SHIFT + ENTER κατά συνέπεια, μπορείτε να εξαλείψετε την εσωτερική συνάρτηση INDEX. Απλώς γράψτε αυτόν τον τύπο και πατήστε CTRL + SHIFT + ENTER.
Τύπος
| = INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Γενικός τύπος πίνακα για αναζήτηση πολλαπλών κριτηρίων
| = INDEX (εύρος αναζήτησης, MATCH (1, (κριτήρια1 = εύρος1)*(κριτήρια2 = εύρος2)*(κριτήριαΝ = εύρος Ν), 0)) |
Εδώ είναι όλες οι σημειώσεις παρατήρησης που χρησιμοποιούν τον τύπο στο Excel
Σημειώσεις:
- Χρησιμοποιήστε το Vlookup εάν έχετε ένα κριτήριο που ταιριάζει, είναι κοινή πρακτική.
- Μπορείτε να προσθέσετε περισσότερες γραμμές και στήλες στον πίνακα αναζήτησης.
- Τα επιχειρήματα κειμένου πρέπει να δίνονται μέσα σε εισαγωγικά ("").
- Ο πίνακας VLOOKUP πρέπει να έχει τη σειρά_αναζήτησης στην αριστερή ή την πρώτη στήλη.
- Ο κατάλογος col δεν μπορεί να είναι 1 καθώς είναι ο πίνακας αναζήτησης
- Το όρισμα 0 χρησιμοποιείται για την ακριβή τιμή αντιστοίχισης. Χρησιμοποιήστε το 1 για την τιμή αντιστοίχισης κατά προσέγγιση.
- Η συνάρτηση επιστρέφει σφάλμα #N/A, εάν η τιμή αναζήτησης δεν βρίσκεται στον πίνακα αναζήτησης. Λάβετε λοιπόν το σφάλμα, εάν είναι απαραίτητο.
Ελπίζουμε ότι αυτό το άρθρο σχετικά με τον τρόπο ανάκτησης μιας τιμής από μια λίστα που ταιριάζει με τα κριτήρια κατηγορίας και στοιχείων στο Excel είναι επεξηγηματικό. Βρείτε περισσότερα άρθρα σχετικά με τον υπολογισμό των τιμών και τους σχετικούς τύπους Excel εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε τα με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email.
Πώς να ανακτήσετε την πιο πρόσφατη τιμή στο 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 είναι απαραίτητη για την προετοιμασία του ταμπλό σας.