Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο θα δείξει πώς να υπολογίσετε το "sumproduct if", επιστρέφοντας το άθροισμα των προϊόντων συστοιχιών ή σειρών με βάση κριτήρια.
Λειτουργία SUMPRODUCT
Η συνάρτηση SUMPRODUCT χρησιμοποιείται για να πολλαπλασιάσει πίνακες αριθμών, αθροίζοντας τον πίνακα που προκύπτει.
Για να δημιουργήσουμε ένα "Sumproduct If", θα χρησιμοποιήσουμε τη συνάρτηση SUMPRODUCT μαζί με τη συνάρτηση IF σε έναν τύπο πίνακα.
ΑΝΟΙΓΜΑ ΑΝ
Συνδυάζοντας το SUMPRODUCT και το IF σε έναν τύπο πίνακα, μπορούμε ουσιαστικά να δημιουργήσουμε μια συνάρτηση "SUMPRODUCT IF" που λειτουργεί παρόμοια με τον τρόπο λειτουργίας της ενσωματωμένης συνάρτησης SUMIF. Ας δούμε ένα παράδειγμα.
Έχουμε μια λίστα πωλήσεων που επιτυγχάνονται από διαχειριστές σε διαφορετικές περιοχές με αντίστοιχα ποσοστά προμήθειας:
Υποτίθεται ότι μας ζητείται να υπολογίσουμε το ποσό της προμήθειας για κάθε διαχειριστή, ως εξής:
Για να το πετύχουμε αυτό, μπορούμε να φωλιάσουμε μια συνάρτηση IF με το διευθυντής ως κριτήρια μας στο εσωτερικό της λειτουργίας SUMPRODUCT, έτσι:
= SUMPRODUCT (ΑΝ (=,*))
= SUMPRODUCT (ΑΝ ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Όταν χρησιμοποιείτε το Excel 2022 και παλαιότερα, πρέπει να εισαγάγετε τον τύπο πατώντας CTRL + SHIFT + ENTER για να βρείτε τις αγκύλες γύρω από τον τύπο (δείτε την επάνω εικόνα).
Πώς λειτουργεί ο τύπος;
Ο τύπος λειτουργεί αξιολογώντας κάθε κελί στο εύρος κριτηρίων μας ως ΑΛΗΘΙΝΟ ή ΛΑΘΟΣ.
Υπολογισμός της συνολικής προμήθειας για την Olivia:
= SUMPRODUCT (ΑΝ ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= SUMPRODUCT (IF ({TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}), {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})
Στη συνέχεια, η συνάρτηση IF αντικαθιστά κάθε τιμή με FALSE εάν η συνθήκη της δεν πληρούται.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Τώρα η συνάρτηση SUMPRODUCT παραλείπει τις FALSE τιμές και αθροίζει τις υπόλοιπες τιμές (2.077,40).
SUMPRODUCT IF με πολλαπλά κριτήρια
Για να χρησιμοποιήσετε το SUMPRODUCT IF με πολλαπλά κριτήρια (παρόμοιο με το πώς λειτουργεί η ενσωματωμένη συνάρτηση SUMIFS), απλώς τοποθετήστε περισσότερες συναρτήσεις IF στη συνάρτηση SUMPRODUCT, ως εξής:
= SUMPRODUCT (IF (=, IF (=, *))
(CTRL + SHIFT + ENTER)
= SUMPRODUCT (ΑΝ ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ENTER)
Μια άλλη προσέγγιση στο SUMPRODUCT IF
Συχνά στο Excel, υπάρχουν πολλοί τρόποι για να προκύψουν τα επιθυμητά αποτελέσματα. Ένας διαφορετικός τρόπος υπολογισμού του "sumproduct if" είναι να περιλαμβάνει τα κριτήρια στα πλαίσια η συνάρτηση SUMPRODUCT ως πίνακας που χρησιμοποιεί διπλή unary όπως:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Αυτή η μέθοδος χρησιμοποιεί το διπλό unary (-) για να μετατρέψει έναν TRUE FALSE πίνακα σε μηδενικά και μονά. Το SUMPRODUCT στη συνέχεια πολλαπλασιάζει τους πίνακες κριτηρίων μετατροπής μαζί:
= SUMPRODUCT ({1; 1; 0; 0; 0; 1; 0; 0; 0}, {1; 0; 1; 0; 1; 0; 0; 0; 0}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})
Συμβουλές και κόλπα:
- Όπου είναι δυνατόν, να κλειδώνετε πάντα (F4) το εύρος και τις εισόδους τύπου για να επιτρέπεται η αυτόματη συμπλήρωση.
- Εάν χρησιμοποιείτε το Excel 2022 ή νεότερο, μπορείτε να εισαγάγετε τον τύπο χωρίς Ctrl + Shift + Enter.
ΑΝΟΙΓΜΑ ΑΝ στα Υπολογιστικά φύλλα Google
Η συνάρτηση SUMPRODUCT IF λειτουργεί ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel:
