Γενική φόρμουλα
=SUMPRODUCT(costs,--(range="x"))
Περίληψη
Για να υπολογίσετε την τιμή δέσμης προϊόντων χρησιμοποιώντας ένα απλό "x" για να συμπεριλάβετε ή να αποκλείσετε ένα προϊόν, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στη συνάρτηση SUMPRODUCT. Στο παράδειγμα που εμφανίζεται, ο τύπος στο D11 είναι:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Εξήγηση
Η συνάρτηση SUMPRODUCT πολλαπλασιάζει τα εύρη ή τις σειρές μαζί και επιστρέφει το άθροισμα των προϊόντων. Αυτό ακούγεται βαρετό, αλλά το SUMPRODUCT είναι μια κομψή και ευέλικτη λειτουργία, την οποία αυτό το παράδειγμα απεικονίζει όμορφα.
Σε αυτό το παράδειγμα, το SUMPRODUCT διαμορφώνεται με δύο πίνακες. Η πρώτη σειρά είναι το εύρος που διατηρεί την τιμολόγηση προϊόντων:
$C$5:$C$9
Σημειώστε ότι η αναφορά είναι απόλυτη για την αποτροπή αλλαγών καθώς ο τύπος αντιγράφεται στα δεξιά. Αυτό το εύρος αξιολογείται στον ακόλουθο πίνακα:
(99;69;129;119;49)
Ο δεύτερος πίνακας δημιουργείται με αυτήν την έκφραση:
--(D5:D9="x")
Το αποτέλεσμα του D5: D9 = "x" είναι ένας πίνακας τιμών TRUE FALSE όπως:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Το διπλό αρνητικό (-) μετατρέπει αυτές τις τιμές TRUE FALSE σε 1s και 0s:
(1;1;0;0;0)
Έτσι, μέσα στο SUMPRODUCT έχουμε:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
Στη συνέχεια, η συνάρτηση SUMPRODUCT πολλαπλασιάζει τα αντίστοιχα στοιχεία σε κάθε πίνακα μαζί:
=SUMPRODUCT((99;69;0;0;0))
και επιστρέφει το άθροισμα των προϊόντων, 168 σε αυτήν την περίπτωση.
Αποτελεσματικά, ο δεύτερος πίνακας ενεργεί ως φίλτρο για τις τιμές στον πρώτο πίνακα. Τα μηδενικά στον πίνακα2 ακυρώνουν τα στοιχεία στον πίνακα1 και τα 1 στον πίνακα 2 επιτρέπουν στις τιμές από τον πίνακα 1 να περάσουν στο τελικό αποτέλεσμα.
Με έναν μόνο πίνακα
Το SUMPRODUCT έχει ρυθμιστεί για να δέχεται πολλαπλές συστοιχίες, αλλά μπορείτε να απλοποιήσετε αυτόν τον τύπο λίγο παρέχοντας έναν μόνο πίνακα στην αρχή:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Η μαθηματική λειτουργία (πολλαπλασιασμός) εξαναγκάζει αυτόματα τις τιμές TRUE FALSE στη δεύτερη έκφραση σε αυτές και μηδενικά, χωρίς να χρειάζεται διπλό αρνητικό.
