Σε αυτό το άρθρο, θα μάθουμε πώς να αναζητούμε ακριβείς αντιστοιχίσεις χρησιμοποιώντας τη συνάρτηση SUMPRODUCT στο Excel.
Σενάριο:
Με απλά λόγια, κατά την εργασία με πίνακες δεδομένων, μερικές φορές πρέπει να αναζητήσουμε τιμές με κεφαλαία γράμματα στο Excel. Οι λειτουργίες αναζήτησης όπως οι συναρτήσεις VLOOKUP ή MATCH δεν βρίσκουν την ακριβή αντιστοίχιση, καθώς δεν είναι συναρτήσεις με διάκριση πεζών -κεφαλαίων. Ας υποθέσουμε ότι δουλεύουμε σε δεδομένα όπου 2 ονόματα διαφοροποιούνται με βάση την ευαισθησία πεζών, δηλαδή τα Jerry & JERRY είναι δύο διαφορετικά ονόματα. Μπορείτε να εκτελέσετε εργασίες όπως λειτουργίες σε πολλά εύρη χρησιμοποιώντας τον τύπο που εξηγείται παρακάτω.
Πώς να λύσετε το πρόβλημα;
Για αυτό το πρόβλημα, θα πρέπει να χρησιμοποιήσουμε τη συνάρτηση SUMPRODUCT & EXACT. Τώρα θα κάνουμε έναν τύπο από τη συνάρτηση. Εδώ μας δίνεται ένας πίνακας και πρέπει να βρούμε τιμές που αντιστοιχούν στην ακριβή αντιστοίχιση στον πίνακα στο Excel. Η συνάρτηση SUMPRODUCT επιστρέφει το SUM των αντίστοιχων TRUE τιμών (ως 1) και αγνοεί τις τιμές που αντιστοιχούν σε FALSE τιμές (ως 0) στον πίνακα που επιστρέφεται
Γενικός τύπος:
| = SUMPRODUCT ( - (ΑΚΡΙΒΗ (τιμή_αναζήτησης, αναζήτηση_συσκευασίας)), [επιστροφή_συστοιχίας]) |
lookup_value: τιμή προς αναζήτηση.
lookup_array: πίνακας αναζήτησης για τιμή αναζήτησης.
return_array: array, τιμή επιστροφής που αντιστοιχεί στον πίνακα αναζήτησης.
-: Το αρνητικό (-) char αλλάζει τιμές, TRUEs ή 1s σε FALSEs ή 0s και FALSEs ή 0s σε TRUEs ή 1s.
Παράδειγμα:
Όλα αυτά μπορεί να προκαλούν σύγχυση στην κατανόηση. Έτσι, ας δοκιμάσουμε αυτόν τον τύπο εκτελώντας τον στο παράδειγμα που φαίνεται παρακάτω. Εδώ έχουμε δεδομένα που έχουν Ποσότητα & Τιμή προϊόντων που έχουν ληφθεί τις ημερομηνίες. Εάν οποιοδήποτε προϊόν αγοράζεται δύο φορές έχει 2 ονόματα. Εδώ πρέπει να βρούμε τον αριθμό των αντικειμένων για όλα τα βασικά είδη. Έτσι, για αυτό έχουμε εκχωρήσει 2 λίστες ως τη λίστα λήψης και τα απαραίτητα που απαιτούνται σε μια στήλη για τον τύπο. Τώρα θα χρησιμοποιήσουμε τον παρακάτω τύπο για να πάρουμε την Ποσότητα του "γάλακτος" από τον πίνακα.
Χρησιμοποιήστε τον τύπο:
| = SUMPRODUCT ( - (ΑΚΡΙΒΩΣ (F5, B3: B11)), (C3: C11)) |
F6: αναζητήστε την τιμή στο κελί F6
Β3: Β11: ο πίνακας αναζήτησης είναι Στοιχεία
C3: C11: ο πίνακας επιστροφής είναι Ποσότητα
-: Το αρνητικό (-) char αλλάζει τιμές, TRUEs ή 1s σε FALSEs ή 0s και FALSEs ή 0s σε TRUEs ή 1s.
Εδώ το εύρος δίνεται ως αναφορά κελιού. Πατήστε Enter για να λάβετε την Ποσότητα.
Όπως μπορείτε να δείτε, 58 είναι η ποσότητα που αντιστοιχεί στην τιμή "γάλα" στο κελί Β5 και όχι το "Γάλα" στο κελί Β9. Θα χρειαστεί να αναζητήσετε την τιμή "Γάλα" εάν χρειάζεστε την ποσότητα "54" στο κελί C9.
Μπορείτε να αναζητήσετε την τιμή που αντιστοιχεί στην τιμή "γάλα" χρησιμοποιώντας τον τύπο που φαίνεται παρακάτω.
Χρησιμοποιήστε τον τύπο:
| = SUMPRODUCT ( - (ΑΚΡΙΒΩΣ (F5, B3: B11)), (D3: D11)) |
F6: αναζητήστε την τιμή στο κελί F6
Β3: Β11: ο πίνακας αναζήτησης είναι Στοιχεία
D3: D11: ο πίνακας επιστροφής είναι Price
Εδώ έχουμε το 58 είναι η τιμή που αντιστοιχεί στην τιμή "γάλα" στο κελί Β5 και όχι στο "γάλα" στο κελί Β9. Θα πρέπει να αναζητήσετε την τιμή "Γάλα" εάν χρειάζεστε την τιμή "15.58" στο κελί D9.
Μοναδικές τιμές στον πίνακα αναζήτησης
Ομοίως, μπορείτε να βρείτε τις μοναδικές τιμές χρησιμοποιώντας τον τύπο. Εδώ η τιμή αναζήτησης "EGGS" χρησιμοποιείται ως παράδειγμα.
Στην παραπάνω εικόνα, έχουμε τις τιμές που προσαρμόζουν τον ίδιο τύπο. Αλλά το πρόβλημα προκύπτει εάν έχει μια μοναδική τιμή και δεν αναζητάτε τη σωστή τιμή αναζήτησης. Όπως εδώ, χρησιμοποιώντας την τιμή "readωμί" στον τύπο για αναζήτηση στον πίνακα.
Ο τύπος επιστρέφει 0 ως ποσότητα και τιμή, αλλά αυτό δεν σημαίνει ότι η ποσότητα και η τιμή του ψωμιού είναι 0. Απλώς ο τύπος επιστρέφει 0 ως τιμή όταν η τιμή που αναζητάτε δεν βρίσκεται. Χρησιμοποιήστε λοιπόν αυτόν τον τύπο μόνο για να αναζητήσετε την ακριβή αντιστοίχιση.
Μπορείτε επίσης να εκτελέσετε ακριβείς αντιστοιχίσεις αναζήτησης χρησιμοποιώντας τη λειτουργία INDEX και MATCH στο Excel. Μάθετε περισσότερα σχετικά με τον τρόπο πραγματοποίησης αναζήτησης ευαίσθητων περιπτώσεων χρησιμοποιώντας τη λειτουργία INDEX & MATCH στο Excel. Μπορείτε επίσης να αναζητήσετε μερικούς αγώνες χρησιμοποιώντας τα μπαλαντέρ στο Excel.
Ακολουθούν μερικές παρατηρήσεις παρατήρησης που φαίνονται παρακάτω.
Σημειώσεις:
- Ο τύπος λειτουργεί μόνο για να αναζητήσετε την ακριβή αντιστοίχιση.
- Η συνάρτηση SUMPRODUCT θεωρεί τις μη αριθμητικές τιμές ως 0.
- Η συνάρτηση SUMPRODUCT θεωρεί τη λογική τιμή TRUE ως 1 και False ως 0.
- Ο πίνακας ορίσματος πρέπει να έχει το ίδιο μέγεθος, διαφορετικά η συνάρτηση επιστρέφει σφάλμα.
- Η συνάρτηση SUMPRODUCT επιστρέφει την τιμή που αντιστοιχεί στις τιμές TRUE στον πίνακα που επιστρέφεται αφού λάβετε μεμονωμένα προϊόντα στον αντίστοιχο πίνακα.
- Χειριστές όπως ισούται με ( = ), λιγότερο από ίσο με ( <= ), μεγαλύτερος από ( > ) ή δεν ισούται με () μπορεί να εκτελεστεί μέσα σε έναν τύπο που εφαρμόζεται, με αριθμούς μόνο.
Ελπίζουμε ότι αυτό το άρθρο σχετικά με τον τρόπο αναζήτησης ακριβούς αντιστοίχισης χρησιμοποιώντας τη συνάρτηση SUMPRODUCT στο Excel είναι επεξηγηματικό. Βρείτε περισσότερα άρθρα σχετικά με τους τύπους καταμέτρησης εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT στο Excel: Επιστρέφει το SUM μετά τον πολλαπλασιασμό τιμών σε πολλούς πίνακες στο excel.
Χρησιμοποιήστε INDEX και MATCH για αναζήτηση τιμής : Ο τύπος INDEX-MATCH χρησιμοποιείται για να αναζητήσει δυναμικά και με ακρίβεια μια τιμή στον δεδομένο πίνακα. Αυτή είναι μια εναλλακτική λύση στη λειτουργία VLOOKUP και ξεπερνά τις αδυναμίες της συνάρτησης VLOOKUP.
Άθροισμα κατά ομάδες OFFSET σε γραμμές και στήλες : Η συνάρτηση OFFSET μπορεί να χρησιμοποιηθεί για να αθροίσει δυναμικά την ομάδα κελιών. Αυτές οι ομάδες μπορούν να βρίσκονται οπουδήποτε στο φύλλο.
Πώς να ανακτήσετε κάθε Nth τιμή σε μια περιοχή στο Excel: Χρησιμοποιώντας τη συνάρτηση OFFSET του Excel μπορούμε να ανακτήσουμε τιμές από εναλλασσόμενες γραμμές ή στήλες. Αυτός ο τύπος απαιτεί τη βοήθεια της συνάρτησης ROW για εναλλαγή γραμμών και συνάρτηση ΣΤΗΛΗ για εναλλαγή σε στήλες.
Πώς να χρησιμοποιήσετε τη λειτουργία OFFSET στο Excel : Η συνάρτηση OFFSET είναι μια ισχυρή συνάρτηση Excel που υποτιμάται από πολλούς χρήστες. Αλλά οι ειδικοί γνωρίζουν τη δύναμη της συνάρτησης OFFSET και πώς μπορούμε να χρησιμοποιήσουμε αυτήν τη λειτουργία για να κάνουμε μερικές μαγικές εργασίες σε τύπο Excel. Ακολουθούν τα βασικά της λειτουργίας OFFSET.
Πώς να χρησιμοποιήσετε μπαλαντέρ στο excel : Μετρήστε κελιά που ταιριάζουν φράσεις χρησιμοποιώντας τους μπαλαντέρ στο excel
Δημοφιλή άρθρα
50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας : Προχωρήστε γρηγορότερα στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε το tσυνάρτηση VLOOKUP στο Excel : Αυτή είναι μία από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικές περιοχές και φύλλα στο Excel.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές στο Excel. Η λειτουργία COUNTIF είναι απαραίτητη για την προετοιμασία του πίνακα ελέγχου σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές με συγκεκριμένες συνθήκες.
