Γενική φόρμουλα
=SUMPRODUCT(expression,range)
Περίληψη
Για να φιλτράρετε τα αποτελέσματα του SUMPRODUCT με συγκεκριμένα κριτήρια, μπορείτε να εφαρμόσετε απλές λογικές εκφράσεις απευθείας σε πίνακες στη συνάρτηση, αντί να χρησιμοποιήσετε τη συνάρτηση IF. Στο παράδειγμα που παρουσιάζεται, οι τύποι στο H5: H7 είναι:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
όπου ορίζονται τα ακόλουθα εύρη τιμών:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Εάν προτιμάτε να αποφύγετε τα ονομαστικά εύρη, χρησιμοποιήστε τα παραπάνω εύρη που έχουν εισαχθεί ως απόλυτες αναφορές. Οι λογικές εκφράσεις στα H6 και H7 μπορούν να συνδυαστούν, όπως εξηγείται παρακάτω.
Εξήγηση
Αυτό το παράδειγμα δείχνει ένα από τα βασικά πλεονεκτήματα της συνάρτησης SUMPRODUCT - τη δυνατότητα φιλτραρίσματος δεδομένων με βασικές λογικές εκφράσεις αντί για τη συνάρτηση IF. Μέσα στο SUMPRODUCT, ο πρώτος πίνακας είναι μια λογική έκφραση που φιλτράρει το χρώμα "κόκκινο":
--(color="red")
Αυτό οδηγεί σε έναν πίνακα ή τιμές TRUE FALSE, οι οποίες εξαναγκάζονται σε αυτές και μηδενικά με τη διπλή αρνητική (-) λειτουργία. Το αποτέλεσμα είναι αυτός ο πίνακας:
(1;0;1;0;0;0;1;0;0;0)
Παρατηρήστε ότι ο πίνακας περιέχει 10 τιμές, μία για κάθε σειρά. Το ένα δηλώνει μια σειρά όπου το χρώμα είναι "κόκκινο" και το μηδέν δείχνει μια σειρά με οποιοδήποτε άλλο χρώμα.
Στη συνέχεια, έχουμε δύο ακόμη πίνακες: μία για ποσότητα και μία για τιμή. Μαζί με αυτά τα αποτελέσματα από τον πρώτο πίνακα, έχουμε:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Επεκτείνοντας τους πίνακες, έχουμε:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
Η βασική συμπεριφορά του SUMPRODUCT είναι ο πολλαπλασιασμός και η αθροιστική σειρά. Από τη στιγμή που εργάζονται με τρεις πίνακες, μπορούμε να απεικονίσει τη λειτουργία, όπως φαίνεται στον παρακάτω πίνακα, όπου η στήλη αποτέλεσμα είναι το αποτέλεσμα του πολλαπλασιασμού πίνακας1 * array2 * array3 :
| πίνακας1 | πίνακας2 | πίνακας3 | αποτέλεσμα |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Σημείωση Ο πίνακας1 λειτουργεί ως φίλτρο - μηδενικές τιμές εδώ "μηδενικές" τιμές σε σειρές όπου το χρώμα δεν είναι "κόκκινο". Επαναφέροντας τα αποτελέσματα στο SUMPRODUCT, έχουμε:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Το οποίο επιστρέφει ένα τελικό αποτέλεσμα 480.
Προσθήκη πρόσθετων κριτηρίων
Μπορείτε να επεκτείνετε τα κριτήρια προσθέτοντας μια άλλη λογική έκφραση. Για παράδειγμα, για να βρείτε συνολικές πωλήσεις όπου το χρώμα είναι "Κόκκινο" και η κατάσταση είναι "TX", το H6 περιέχει:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Σημείωση: Το SUMPRODUCT δεν είναι διάκριση πεζών-κεφαλαίων.
Απλοποίηση με έναν μόνο πίνακα
Οι επαγγελματίες του Excel συχνά απλοποιούν τη σύνταξη μέσα στο SUMPRODUCT λίγο πολλαπλασιάζοντας τους πίνακες απευθείας μέσα στον πίνακα1 όπως αυτό:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Αυτό λειτουργεί επειδή η μαθηματική λειτουργία (πολλαπλασιασμός) εξαναγκάζει αυτόματα τις τιμές TRUE και FALSE από τις δύο πρώτες εκφράσεις σε αυτές και μηδενικά.
