Όπως έχω αναφέρει σε πολλά ιστολόγια μου ότι ένα SUMPRODUCT είναι μια πολύ ευέλικτη λειτουργία και μπορεί να χρησιμοποιηθεί για πολλαπλούς σκοπούς. Σε αυτό το άρθρο, θα δούμε πώς μπορούμε να χρησιμοποιήσουμε αυτήν τη συνάρτηση για να μετρήσουμε τιμές με πολλαπλά ή κριτήρια.
Γενικός τύπος SUMPRODUCT για να μετρήσετε με πολλαπλά ή κριτήρια
| = SUMPRODUCT (-((((κριτήρια1)+(κριτήρια2)+…)> 0) |
Κριτήρια 1: Αυτό είναι οποιοδήποτε κριτήριο που επιστρέφει έναν πίνακα TRUE και FALSE.
Κριτήρια2: Αυτό είναι το επόμενο κριτήριο που θέλετε να ελέγξετε. Ομοίως, μπορείτε να έχετε όσα κριτήρια θέλετε.
Ο παραπάνω γενικός τύπος τροποποιείται συχνά ώστε να ανταποκρίνεται στις απαιτήσεις για καταμέτρηση με πολλαπλά κριτήρια OR. Αλλά ο βασικός τύπος είναι αυτός. Πρώτα θα δούμε πώς λειτουργεί αυτό μέσω ενός παραδείγματος και στη συνέχεια θα συζητήσουμε άλλα σενάρια όπου θα χρειαστεί να τροποποιήσετε λίγο αυτόν τον τύπο.
Πρόβλημα:
Η τιμή σε ένα κελί πρέπει να διαιρεθεί με ακρίβεια σε 5 παρόμοιες πληρωμές. Όταν απλά διαιρούμε την τιμή 88,04 $ με 5 και στρογγυλοποιούμε το αποτέλεσμα σε δύο δεκαδικά ψηφία, λαμβάνουμε ατομικές πληρωμές 17,61 $. Ωστόσο, αυτό θα προσθέσει συνολική πληρωμή 88,05 $, το οποίο δεν είναι το ακριβές ποσό που ξεκινάμε. Επομένως, θέλουμε να υπολογίσουμε 5 παρόμοιους αριθμούς που αθροίζονται στο συνολικό ποσό.
Λύση:
Πρώτα Υπολογίστε την πρώτη πληρωμή διαιρώντας την τιμή με 5 και στρογγυλοποιώντας το αποτέλεσμα.
Χρησιμοποιήστε τη συνάρτηση ROUND στον ακόλουθο τύπο:
= ΓΥΡΟΣ (Β1/5,2)
Βήμα 2:
Υπολογίστε κάθε πληρωμή που απομένει.
Χρησιμοποιήστε τις συναρτήσεις ROUND, SUM και ROW στον ακόλουθο τύπο:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-ROW ()+ROW ($ B $ 2)), 2)
Παράδειγμα:
Μετρήστε Χρήστες Αν Κωδικός Αντιπροσωπείας Ταιριάζει ή Έτος Χρησιμοποιώντας 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))
- SUMPRODUCT(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0))
- SUMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUCT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…})
- SUMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Τα διπλά αρνητικά (-) σύμβολα χρησιμοποιούνται για τη μετατροπή των boolean τιμών σε 1s και 0s. Έτσι κάθε TRUE τιμή στον πίνακα μετατρέπεται σε 1 και FALSE σε 0.
Προσθήκη περισσότερων ή κριτηρίων για την καταμέτρηση χρησιμοποιώντας το 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. Εξερευνήστε περισσότερα άρθρα σχετικά με την αξία αναζήτησης Excel & τις λειτουργίες του Excel 2019 εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email.
Χρησιμοποιήστε INDEX και MATCH για αναζήτηση τιμής : Λειτουργία INDEX & MATCH για αναζήτηση τιμής όπως απαιτείται.
Εύρος SUM με INDEX στο Excel : Χρησιμοποιήστε τη συνάρτηση INDEX για να βρείτε το άθροισμα των τιμών όπως απαιτείται.
Πώς να χρησιμοποιήσετε τη συνάρτηση INDEX στο Excel : Βρείτε το INDEX ενός πίνακα χρησιμοποιώντας τη συνάρτηση INDEX που εξηγείται με ένα παράδειγμα.
Πώς να χρησιμοποιήσετε τη συνάρτηση MATCH στο Excel : Βρείτε το MATCH στον πίνακα χρησιμοποιώντας την τιμή INDEX μέσα στη συνάρτηση MATCH που εξηγείται με παράδειγμα.
Πώς να χρησιμοποιήσετε τη λειτουργία LOOKUP στο Excel : Βρείτε την τιμή αναζήτησης στον πίνακα χρησιμοποιώντας τη συνάρτηση LOOKUP που εξηγείται με ένα παράδειγμα.
Δημοφιλή άρθρα:
Πώς να χρησιμοποιήσετε τη συνάρτηση IF στο Excel : Η δήλωση IF στο Excel ελέγχει την κατάσταση και επιστρέφει μια συγκεκριμένη τιμή εάν η συνθήκη είναι TRUE ή επιστρέφει μια άλλη συγκεκριμένη τιμή εάν είναι FALSE.
Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.