Όπως έχω αναφέρει σε πολλά ιστολόγια μου ότι ένα SUMPRODUCT είναι μια πολύ ευέλικτη λειτουργία και μπορεί να χρησιμοποιηθεί για πολλαπλούς σκοπούς. Σε αυτό το άρθρο, θα δούμε πώς μπορούμε να χρησιμοποιήσουμε αυτήν τη συνάρτηση για να μετρήσουμε τιμές με πολλαπλά κριτήρια OR.
Γενικός τύπος SUMPRODUCT για να μετρήσετε με πολλαπλά ή κριτήρια
| = SUMPRODUCT (-(((κριτήρια 1)+(κριτήρια2)+… )>0) |
Κριτήρια 1: Αυτό είναι οποιοδήποτε κριτήριο που επιστρέφει έναν πίνακα TRUE και FALSE.
Κριτήρια 2: Αυτό είναι το επόμενο κριτήριο που θέλετε να ελέγξετε. Ομοίως, μπορείτε να έχετε όσα κριτήρια θέλετε.
Ο παραπάνω γενικός τύπος τροποποιείται συχνά ώστε να ανταποκρίνεται στις απαιτήσεις για καταμέτρηση με πολλαπλά κριτήρια OR. Αλλά ο βασικός τύπος είναι αυτός. Πρώτα θα δούμε πώς λειτουργεί αυτό μέσω ενός παραδείγματος και στη συνέχεια θα συζητήσουμε άλλα σενάρια όπου θα χρειαστεί να τροποποιήσετε λίγο αυτόν τον τύπο.
Παράδειγμα: Μετρήστε Χρήστες Αν Κωδικός Αντιπροσωπείας ή ΈτοςΤαιριάζει Χρήση SUMPRODUCT
Εδώ λοιπόν έχουμε ένα σύνολο δεδομένων πωλητών. Τα δεδομένα περιέχουν πολλές στήλες. Αυτό που πρέπει να κάνουμε είναι να μετρήσουμε τον αριθμό των χρηστών που έχουν κωδικό "INKA" ή έτος είναι "2016". Βεβαιωθείτε ότι εάν κάποιος έχει και τα δύο (κωδικός ως "inka" και έτος 2016) θα πρέπει να υπολογίζεται ως 1.
Έτσι, εδώ έχουμε δύο κριτήρια. Χρησιμοποιούμε τον παραπάνω τύπο SUMPRODUCT:
| = SUMPRODUCT (-((((Κωδικός = I3)+(Έτος = Κ3))> 0)) |
Εδώ, ο κωδικός και το έτος ονομάζονται εύρη.
Αυτό επιστρέφει 7.
Στα δεδομένα έχουμε 5 εγγραφές κωδικού INKA και 4 εγγραφές του έτους 2016. Αλλά 2 εγγραφές έχουν και το "INKA" και το 2016 ως κωδικό και έτος αντίστοιχα. Αυτοί οι δίσκοι υπολογίζονται ως 1. Και κάπως έτσι παίρνουμε το 7.
Πώς λειτουργεί;
Ας ρίξουμε λοιπόν μια ματιά στο πώς λύνεται ο τύπος βήμα προς βήμα, στη συνέχεια θα συζητήσω πώς λειτουργεί.
| =SUMPRODUCT(-((((Κωδικός = I3)+(Έτος = Κ3))> 0)) |
| 1=>SUMPRODUCT(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0)) |
| 2=>SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUCT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…}) |
| 4=>SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
Στο πρώτο βήμα, η τιμή του I3 ("INKA") συγκρίνεται με κάθε κελί στο εύρος κωδικών. Αυτό επιστρέφει μια σειρά TRUE και FALSE. ΑΛΗΘΕΙΑ για κάθε αγώνα. Για εξοικονόμηση χώρου δεν έχω δείξει όλα τα ΑΛΗΘΕΙΑ. Ομοίως, η τιμή του K3 (2016) ταιριάζει με κάθε κελί στο εύρος του έτους.
Στο επόμενο βήμα, προσθέτουμε αυτούς τους δύο πίνακες που έχουν ως αποτέλεσμα μια νέα σειρά αριθμητικών τιμών. Όπως ίσως γνωρίζετε, το TRUE αντιμετωπίζεται ως 1 και το FALSE ως 0 στο Excel. Όταν λοιπόν προστίθενται TRUE και TRUE παίρνουμε 2 και τα υπόλοιπα μπορείτε να τα καταλάβετε.
Στο επόμενο βήμα, ελέγχουμε ποια τιμή είναι μεγαλύτερη από 0 στον πίνακα. Αυτό μετατρέπει ξανά τον πίνακα σε πραγματικό ψευδή πίνακα. Για κάθε 0 τιμή που παίρνουμε, το False και το υπόλοιπο μετατρέπονται σε true. Τώρα ο αριθμός των TRUE τιμών στον πίνακα είναι η απάντησή μας. Πώς όμως τα υπολογίζουμε; Ιδού πώς.
Διπλά αρνητικά (-) σύμβολα χρησιμοποιούνται για τη μετατροπή των boolean τιμών σε 1s και 0s. Έτσι κάθε TRUE τιμή στον πίνακα μετατρέπεται σε 1 και FALSE σε 0.
Στο τελευταίο βήμα το SUMPRODUCT συνοψίζει αυτόν τον πίνακα και παίρνουμε την απάντησή μας ως 7.
Προσθήκη περισσότερων ή κριτηρίων για την καταμέτρηση χρησιμοποιώντας το SUMPRODUCT
Έτσι, εάν πρέπει να προσθέσετε περισσότερα ή κριτήρια για να μετρήσετε, μπορείτε απλά να προσθέσετε κριτήρια χρησιμοποιώντας το σύμβολο + στη συνάρτηση.
Για παράδειγμα, εάν θέλετε να προσθέσετε ένα άλλο κριτήριο στον παραπάνω τύπο, ώστε να προσθέσει τον αριθμό των εργαζομένων που έχουν πουλήσει περισσότερα από 5 προϊόντα. Ο τύπος SUMPRODUCT θα μοιάζει απλά με αυτό:
| = SUMPRODUCT (-((((Κωδικός = I3)+(Έτος = Κ3)+(Πωλήσεις> 5))> 0)) |
Απλός! έτσι δεν είναι;
Αλλά ας υποθέσουμε ότι θέλετε να έχετε δύο κριτήρια από Κώδικας εύρος. Ας υποθέσουμε ότι θέλετε να μετρήσετε το "INKB". Λοιπόν, πώς το κάνετε αυτό; Μια μέθοδος είναι η χρήση της παραπάνω τεχνικής αλλά αυτή θα ήταν επαναλαμβανόμενη. Ας πούμε ότι θέλω να προσθέσω 10 ακόμη κριτήρια από το ίδιο εύρος. Σε τέτοιες περιπτώσεις, αυτή η τεχνική δεν είναι τόσο έξυπνη για μέτρηση με το SUMPRODUCT.
Ας πούμε ότι έχουμε δεδομένα τακτοποιημένα έτσι.
Οι κωδικοί κριτηρίων βρίσκονται σε μία σειρά I2: J2. Η διάταξη των δεδομένων είναι σημαντική εδώ. Ο τύπος SUMPRODUCT για τις ρυθμίσεις καταμέτρησης κριτηρίων 3 OR θα είναι:
| = SUMPRODUCT (-((((Κωδικός = I2: J2)+(Έτος = I3: J3))> 0)) |
Αυτός είναι ο τύπος SUMPRODUCT για μέτρηση με πολλαπλά κριτήρια όταν πολλά κριτήρια από ένα εύρος γράφονται σε μια σειρά.
Αυτό επιστρέφει τη σωστή απάντηση που είναι 10.
Εάν πληκτρολογείτε οποιοδήποτε έτος στο J3, ο τύπος θα προσθέσει και αυτόν τον αριθμό.
Αυτό χρησιμοποιείται όταν τα κριτήρια είναι σε μία σειρά. Θα λειτουργήσει όταν τα κριτήρια βρίσκονται σε μία στήλη για το ίδιο εύρος; Όχι.
Σε αυτό το παράδειγμα έχουμε πολλούς κωδικούς για καταμέτρηση, αλλά αυτοί οι τύποι κωδικών γράφονται σε μία στήλη. Όταν χρησιμοποιούμε τον παραπάνω τύπο SUMPRODUCT, λαμβάνουμε σφάλμα ans #N/A. Δεν θα ασχοληθούμε με το πώς προέκυψε αυτό το σφάλμα, καθώς αυτό θα κάνει αυτό το άρθρο πολύ μεγάλο. Ας δούμε πώς μπορούμε να κάνουμε αυτό το έργο.
Για να λειτουργήσει αυτός ο τύπος, πρέπει να τυλίξετε τα κριτήρια κώδικα στη συνάρτηση TRANSPOSE. Αυτό θα λειτουργήσει τον τύπο.
| = SUMPRODUCT (-((((Κωδικός = TRANSPOSE (H3: H4))+(Έτος = ΜΕΤΑΦΟΡΑ (I3: I4)))> 0)) |
Αυτός είναι ο τύπος για την καταμέτρηση με πολλαπλές ή συνθήκες στο ίδιο εύρος όταν τα κριτήρια παρατίθενται σε μια στήλη.
Λοιπόν, φίλε, ελπίζω να ήμουν αρκετά σαφής και είχε νόημα. Ελπίζω να εξυπηρετεί τον σκοπό σας να είστε εδώ. Εάν αυτός ο τύπος δεν έλυσε το πρόβλημά σας, ενημερώστε με τις απαιτήσεις σας στην παρακάτω ενότητα σχολίων. Θα χαρώ περισσότερο να σας βοηθήσω με οποιονδήποτε τρόπο. Μπορείτε να αναφέρετε οποιαδήποτε αμφιβολία σχετίζεται με το Excel/VBA. Μέχρι τότε συνεχίστε να μαθαίνετε, συνεχίστε να διαπρέπετε.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT στο Excel: Επιστρέφει το SUM μετά τον πολλαπλασιασμό τιμών σε πολλούς πίνακες στο excel. Αυτή η λειτουργία μπορεί να χρησιμοποιηθεί για την εκτέλεση πολλαπλών εργασιών. Αυτή είναι μια από τις πιο ευέλικτες λειτουργίες.
COUNTIFS με εύρος δυναμικών κριτηρίων : Για να μετρήσουμε με εύρος δυναμικών κριτηρίων χρησιμοποιούμε απλώς τη συνάρτηση INDIRECT. Αυτή η λειτουργία μπορεί
COUNTIFS With OR Για πολλαπλά κριτήρια : Μετρήστε κελιά που έχουν πολλαπλά κριτήρια που ταιριάζουν χρησιμοποιώντας τη συνάρτηση OR. Για να βάλετε μια λογική OR στη συνάρτηση COUNTIFS δεν θα χρειαστεί να χρησιμοποιήσετε τη συνάρτηση OR.
Χρήση του IF με AND / OR συναρτήσεις στο Microsoft Excel : Αυτές οι λογικές συναρτήσεις χρησιμοποιούνται για τον υπολογισμό πολλαπλών κριτηρίων. Με IF οι συναρτήσεις OR και AND χρησιμοποιούνται για να συμπεριλάβουν ή να αποκλείσουν αντιστοιχίσεις.
Πώς να χρησιμοποιήσετε τη συνάρτηση OR στο Microsoft Excel : Η συνάρτηση χρησιμοποιείται για να συμπεριλάβει όλες τις τιμές TRUE σε πολλαπλά κριτήρια.
Πώς να μετρήσετε κελιά που περιέχουν αυτό ή αυτό στο Excel στο Excel : Σε κελιά που περιέχουν αυτό ή αυτό, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση SUMPRODUCT. Δείτε πώς κάνετε αυτούς τους υπολογισμούς.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε τη συνάρτηση Excel VLOOKUP| Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε το Excel Λειτουργία COUNTIF| Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.
