Γενική φόρμουλα
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Περίληψη
Αναζητήσεις με πεζά γράμματα στο Excel
Από προεπιλογή, οι τυπικές αναζητήσεις στο Excel δεν είναι διάκριση πεζών-κεφαλαίων. Τόσο το VLOOKUP όσο και το INDEX / MATCH θα επιστρέψουν απλά τον πρώτο αγώνα, αγνοώντας την περίπτωση.
Ένας άμεσος τρόπος αντιμετώπισης αυτού του περιορισμού, είναι να χρησιμοποιήσετε έναν τύπο πίνακα που βασίζεται στο INDEX / MATCH με ΑΚΡΙΒΩΣ. Ωστόσο, εάν αναζητάτε μόνο αριθμητικές τιμές, το SUMPRODUCT + EXACT παρέχει επίσης έναν ενδιαφέρον και ευέλικτο τρόπο για να κάνετε μια αναζήτηση με κεφαλαία γράμματα.
Στο παράδειγμα, χρησιμοποιούμε τον ακόλουθο τύπο
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Αν και αυτός ο τύπος είναι ένας τύπος πίνακα, δεν χρειάζεται να εισαχθεί με Control + Shift + Enter, καθώς το SUMPRODUCT χειρίζεται τους πίνακες εγγενώς.
Εξήγηση
Το SUMPRODUCT έχει σχεδιαστεί για να λειτουργεί με συστοιχίες, τις οποίες πολλαπλασιάζει και μετά αθροίζει.
Σε αυτήν την περίπτωση, είμαστε δύο πίνακες με SUMPRODUCT: B3: B8 και C3: C8. Το κόλπο είναι να εκτελέσετε μια δοκιμή στις τιμές στη στήλη Β και, στη συνέχεια, να μετατρέψετε τις προκύπτουσες τιμές ΑΛΗΘΕΙΑ / ΛΑΘΟΣ σε 1 και 0 Εκτελούμε το τεστ με ΑΚΡΙΒΩΣ όπως:
EXACT(E3,B3:B8)
Που παράγει αυτόν τον πίνακα:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Σημειώστε ότι η πραγματική τιμή στη θέση 3 είναι η αντιστοιχία μας. Στη συνέχεια, χρησιμοποιούμε το διπλό αρνητικό (δηλαδή -, το οποίο είναι τεχνικά "διπλό unary") για να εξαναγκάσουμε αυτές τις τιμές TRUE / FALSE σε 1 και 0. Το αποτέλεσμα είναι αυτός ο πίνακας:
(0; 0; 1; 0; 0; 0)
Σε αυτό το σημείο στον υπολογισμό, ο τύπος SUMPRODUCT μοιάζει με αυτό:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
Στη συνέχεια, το SUMPRODUCT πολλαπλασιάζει απλά τα στοιχεία σε κάθε πίνακα για να παράγει έναν τελικό πίνακα:
(0; 0; 775; 0; 0; 0)
Ποιο SUMPRODUCT αθροίζει και επιστρέφει 775.
Έτσι, η ουσία αυτού του τύπου είναι ότι οι τιμές FALSE χρησιμοποιούνται για να ακυρώσουν όλες τις άλλες τιμές. Οι μόνες τιμές που επιβιώνουν είναι αυτές που ήταν ΑΛΗΘΕΣ.
Λάβετε υπόψη ότι επειδή χρησιμοποιούμε το SUMPRODUCT, αυτός ο τύπος συνοδεύεται από μια μοναδική ανατροπή: εάν υπάρχουν πολλαπλοί αγώνες, το SUMPRODUCT θα επιστρέψει το άθροισμα αυτών των αγώνων. Αυτό μπορεί ή όχι να είναι αυτό που θέλετε, οπότε προσέξτε εάν περιμένετε πολλούς αγώνες!
Θυμηθείτε, αυτός ο τύπος λειτουργεί μόνο για αριθμητικές τιμές, επειδή το SUMPRODUCT δεν χειρίζεται κείμενο. Εάν θέλετε να ανακτήσετε κείμενο, χρησιμοποιήστε το INDEX / MATCH + EXACT.
