Άθροισμα κελιών που πληρούν πολλαπλά κριτήρια με το Excel SUMPRODUCT

Αθροίστε τα κελιά που πέφτουν μεταξύ δύο τιμών

Η συνάρτηση SUMPRODUCT στο Excel είναι μια πολύ ευέλικτη συνάρτηση που θα δώσει διαφορετικά αποτελέσματα ανάλογα με τον τρόπο εισαγωγής των ορισμάτων της συνάρτησης. Κανονικά, όπως υποδηλώνει το όνομά του, το SUMPRODUCT πολλαπλασιάζει τα στοιχεία μιας ή περισσότερων συστοιχιών για να πάρει το προϊόν τους και στη συνέχεια προσθέτει ή αθροίζει τα προϊόντα μαζί. Ωστόσο, προσαρμόζοντας τη σύνταξη της συνάρτησης, μπορεί να χρησιμοποιηθεί για να αθροίσει μόνο τα δεδομένα σε κελιά που πληρούν συγκεκριμένα κριτήρια. Από το Excel 2007, το πρόγραμμα περιείχε δύο συναρτήσεις – SUMIF και SUMIFS – που θα αθροίσουν δεδομένα σε κελιά που πληρούν ένα ή περισσότερα καθορισμένα κριτήρια. Μερικές φορές, ωστόσο, το SUMPRODUCT είναι ευκολότερο να εργαστεί όταν πρόκειται για την εύρεση πολλαπλών συνθηκών που σχετίζονται με το ίδιο εύρος όπως φαίνεται στην παραπάνω εικόνα.

SUMPRODUCT Συνάρτηση συνάρτησης σε αθροιστικά κελιά

Η σύνταξη που χρησιμοποιήθηκε για να πάρει το SUMPRODUCT να αθροίζει δεδομένα σε κελιά που πληρούν συγκεκριμένες προϋποθέσεις είναι: = SUMPRODUCT ([condition1] * [condition2] * [array]) condition1, condition2 – οι συνθήκες που πρέπει να πληρούνται πριν από τη συνάρτηση θα βρουν το προϊόν του πίνακα. πίνακας – ένα συνεχόμενο εύρος κελιών

Παράδειγμα: Σύνοψη δεδομένων σε κελιά που πληρούν πολλαπλές συνθήκες

Το παράδειγμα στην παραπάνω εικόνα προσθέτει τα δεδομένα σε κελιά στην περιοχή D1 έως E6 που κυμαίνονται μεταξύ 25 και 75.

Εισαγωγή στη συνάρτηση SUMPRODUCT

Επειδή αυτό το παράδειγμα χρησιμοποιεί μια ακανόνιστη φόρμα της συνάρτησης SUMPRODUCT, το παράθυρο διαλόγου της συνάρτησης δεν μπορεί να χρησιμοποιηθεί για την εισαγωγή της συνάρτησης και των ορισμάτων της. Αντ ‘αυτού, η συνάρτηση πρέπει να πληκτρολογηθεί χειροκίνητα σε ένα κελί φύλλου εργασίας.

1. Κάντε κλικ στο κελί Β7 στο φύλλο εργασίας για να γίνει το ενεργό κελί.
2. Εισαγάγετε τον ακόλουθο τύπο στο κελί B7:= SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75) * (A2: B6))
3. Η απάντηση 250 θα πρέπει να εμφανίζεται στο κελί B7
4. Η απάντηση λήφθηκε προσθέτοντας τους πέντε αριθμούς στην περιοχή (40, 45, 50, 55 και 60) που κυμαίνονται μεταξύ 25 και 75. Το σύνολο των οποίων είναι 250

Κατανομή του τύπου SUMPRODUCT

Όταν χρησιμοποιούνται συνθήκες για τα ορίσματά της, το SUMPRODUCT αξιολογεί κάθε στοιχείο πίνακα έναντι της συνθήκης και επιστρέφει μια τιμή Boolean (TRUE ή FALSE). Για τους σκοπούς των υπολογισμών, το Excel εκχωρεί μια τιμή 1 για εκείνα τα στοιχεία πίνακα που είναι ΑΛΗΘΕΙΑ (πληρούν την κατάσταση) και μια τιμή 0 για στοιχεία πίνακα που είναι FALSE (δεν πληρούν την κατάσταση). Για παράδειγμα, ο αριθμός 40:

• είναι ΑΛΗΘΕΙΑ για την πρώτη συνθήκη, έτσι μια τιμή του 1 έχει αντιστοιχιστεί στην πρώτη συστοιχία.
• είναι TRUE για τη δεύτερη συνθήκη, οπότε μια τιμή είναι 1 εκχωρείται στη δεύτερη σειρά.

Ο αριθμός 15:

• είναι FALSE για την πρώτη συνθήκη, οπότε μια τιμή είναι 0 έχει αντιστοιχιστεί στην πρώτη συστοιχία.
• είναι TRUE για τη δεύτερη συνθήκη, οπότε μια τιμή είναι 1 εκχωρείται στη δεύτερη σειρά.

Τα αντίστοιχα και τα μηδενικά σε κάθε πίνακα πολλαπλασιάζονται μαζί:

• Για τον αριθμό 40 – έχουμε 1 x 1 που επιστρέφει μια τιμή 1;
• Για τον αριθμό 15 – έχουμε 0 x 1 επιστρέφοντας μια τιμή 0.

Πολλαπλασιάζοντας τους Ones και Zeros με το Range

Αυτά και τα μηδενικά πολλαπλασιάζονται στη συνέχεια με τους αριθμούς στην περιοχή A2: B6.

Αυτό γίνεται για να μας δώσει τους αριθμούς που θα αθροιστούν από τη συνάρτηση. Αυτό λειτουργεί επειδή:

• 1 φορές οποιοσδήποτε αριθμός είναι ίσος με τον αρχικό αριθμό
• 0 φορές οποιοσδήποτε αριθμός ισούται με 0

Έτσι καταλήγουμε με:

• 1 * 40 = 40
  0 * 15 = 0
  0 * 22 = 0
  1 * 45 = 45
  1 * 50 = 50
  1 * 55 = 55
  0 * 25 = 0
  0 * 75 = 0
  1 * 60 = 60
  0 * 100 = 0

Συνοψίζοντας τα αποτελέσματα

Στη συνέχεια, το SUMPRODUCT συνοψίζει τα παραπάνω αποτελέσματα για να βρει την απάντηση. 40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250