Στο προηγούμενο άρθρο μάθαμε πώς να αθροίζουμε τις τιμές Ν πάνω ή κάτω. Σε αυτό το άρθρο προσπαθούμε να συνοψίσουμε τις τιμές Ν πάνω ή κάτω με ένα κριτήριο.
Άθροισμα τιμών TOP N με κριτήρια
Πώς να λύσετε το πρόβλημα;
Για αυτό το άρθρο θα πρέπει να χρησιμοποιήσουμε τη συνάρτηση SUMPRODUCT. Τώρα θα φτιάξουμε έναν τύπο από αυτές τις συναρτήσεις. Εδώ μας δίνεται ένα εύρος και ένα κριτήριο. Πρέπει να πάρουμε τις 5 κορυφαίες τιμές στο εύρος και να πάρουμε το άθροισμα των τιμών με βάση τα δεδομένα κριτήρια.
Γενικός τύπος:
= SUMPRODUCT (ΜΕΓΑΛΟ ((λίστα = κριτήρια) * (εύρος), {1, 2,…., N}})
λίστα: λίστα κριτηρίων
Κριτήρια: κριτήρια που ταιριάζουν
εύρος: εύρος τιμών
αξίες: οι αριθμοί διαχωρίζονται χρησιμοποιώντας τα κόμματα, όπως εάν θέλετε να βρείτε τις κορυφαίες 3 τιμές, χρησιμοποιήστε {1, 2, 3}.
Παράδειγμα:
Εδώ έχουμε τις τιμές του συνόλου δεδομένων από το A1: D50.
Πρώτον, πρέπει να βρούμε τις πέντε κορυφαίες τιμές χρησιμοποιώντας τη συνάρτηση ΜΕΓΑΛΗ που ταιριάζει με την πόλη "Βοστώνη" και, στη συνέχεια, το άθροισμα να εκτελεστεί σε αυτές τις 5 τιμές. Τώρα θα χρησιμοποιήσουμε τον ακόλουθο τύπο για να πάρουμε το άθροισμα
Χρησιμοποιήστε τον τύπο:
= SUMPRODUCT (ΜΕΓΑΛΟ ((Πόλη = "Βοστώνη") * (ποσότητα), {1, 2, 3, 4, 5}))
Εξήγηση:
- Το "Boston" της πόλης ταιριάζει με την περιοχή που αναφέρθηκε. Αυτό επιστρέφει μια σειρά αληθινών και ψευδών.
- Η συνάρτηση LARGE επιστρέφει τις κορυφαίες 5 αριθμητικές τιμές από το εύρος ποσότητας και επιστρέφει τον πίνακα στη συνάρτηση SUMPRODUCT.
= SUMPRODUCT {193, 149, 138, 134, 123}
- Η συνάρτηση SUMPRODUCT λαμβάνει έναν πίνακα από τις κορυφαίες 5 τιμές, με έναν πίνακα από τους κορυφαίους 5 αριθμούς που επιστρέφει το άθροισμα αυτών των αριθμών.
Εδώ το εύρος Πόλης & ποσότητας δίνεται ως το ονομαζόμενο εύρος. Πατήστε Enter για να λάβετε το άθροισμα των κορυφαίων 5 αριθμών.
Όπως μπορείτε να δείτε στο παραπάνω στιγμιότυπο, το άθροισμα αυτό είναι 737. Το άθροισμα των τιμών 193 + 149 + 138 + 134 + 123 = 737.
Μπορείτε να ελέγξετε τις παραπάνω τιμές στο σύνολο δεδομένων χρησιμοποιώντας την επιλογή φίλτρου excel. Εφαρμόστε το φίλτρο στην κεφαλίδα Πόλη & ποσότητα και κάντε κλικ στο κουμπί βέλους στην κεφαλίδα της πόλης που εμφανίζεται. Ακολουθήστε τα βήματα όπως φαίνεται παρακάτω.
Βήματα:
- Επιλέξτε το κελί κεφαλίδας πόλης. Εφαρμογή φίλτρου χρησιμοποιώντας συντόμευση Ctrl + Shift + L
- Κάντε κλικ στο βέλος που εμφανίζεται ως επιλογή φίλτρου.
- Επιλέξτε την επιλογή (Select All).
- Επιλέξτε μόνο την πόλη της Βοστώνης.
- Επιλέξτε την κεφαλίδα ποσότητας τώρα.
- Ταξινόμηση της λίστας από τη μεγαλύτερη στη μικρότερη και μπορείτε να δείτε όλες τις κορυφαίες 5 τιμές που υπολογίσαμε χρησιμοποιώντας τον τύπο.
Όπως μπορείτε να δείτε στο παραπάνω gif και οι 5 τιμές που ταιριάζουν με τα δεδομένα κριτήρια. Αυτό σημαίνει επίσης ότι ο τύπος λειτουργεί καλά για να μετρήσει αυτές τις τιμές
ΜΕΓΑΛΟΙ Ν αριθμοί
Η παραπάνω διαδικασία χρησιμοποιείται για τον υπολογισμό του αθροίσματος μερικών αριθμών από την κορυφή. Αλλά για υπολογισμό για ν (μεγάλος) αριθμός τιμών σε μεγάλο εύρος.
Χρησιμοποιήστε τον τύπο:
= SUMPRODUCT (ΜΕΓΑΛΟ ((Πόλη = "Βοστώνη") * (ποσότητα), ΣΕΙΡΑ (ΕΜΠΡΟΣ ("1:10"))
Εδώ δημιουργούμε άθροισμα κορυφαίων 10 τιμών μέσω της απόκτησης ενός πίνακα από 1 έως 10 {1. 2; 3; 4; 5; 6; 7; 8; 9; 10} χρησιμοποιώντας τις συναρτήσεις ROW & INDIRECT Excel.
Εδώ έχουμε το άθροισμα των κορυφαίων 10 αριθμών που προκύπτει σε 1147.
Άθροισμα των κατώτατων τιμών Ν με κριτήρια
Πώς να λύσετε το πρόβλημα;
Για αυτό το άρθρο θα πρέπει να χρησιμοποιήσουμε τη συνάρτηση SUMPRODUCT. Τώρα θα φτιάξουμε έναν τύπο από αυτές τις συναρτήσεις. Εδώ μας δίνεται ένα εύρος και πρέπει να κατεβάσουμε 5 τιμές στο εύρος και να πάρουμε το άθροισμα των τιμών.
Γενικός τύπος:
{= SUM (ΜΙΚΡΟ (ΑΝ (Πόλη = "Βοστώνη", ποσότητα), {1, 2, 3, 4, 5}))}
Εύρος: εύρος τιμών
Τιμές: αριθμοί διαχωρισμένοι με κόμματα, όπως εάν θέλετε να βρείτε τις 3 κάτω τιμές, χρησιμοποιήστε {1, 2, 3}.
Παράδειγμα:
Όλα αυτά μπορεί να προκαλούν σύγχυση στην κατανόηση. Έτσι, ας δοκιμάσουμε αυτόν τον τύπο εκτελώντας τον στο παράδειγμα που φαίνεται παρακάτω.
Εδώ έχουμε μια σειρά τιμών από το A1: D50.
Εδώ το εύρος Πόλης & ποσότητας δίνεται ως το εργαλείο excel που ονομάζεται εύρος.
Πρώτον, πρέπει να βρούμε τις πέντε κάτω τιμές χρησιμοποιώντας τη συνάρτηση SMALL που ταιριάζει με τα κριτήρια και, στη συνέχεια, η άθροιση να εκτελείται σε αυτές τις 5 τιμές. Τώρα θα χρησιμοποιήσουμε τον ακόλουθο τύπο για να πάρουμε το άθροισμα
Χρησιμοποιήστε τον τύπο:
{= SUM (ΜΙΚΡΟ (ΑΝ (Πόλη = "Βοστώνη", ποσότητα), {1, 2, 3, 4, 5}))}
ΜΗΝ χρησιμοποιείτε χειροκίνητα σγουρά στηρίγματα. Σγουρά σιδεράκια εφαρμόζονται χρησιμοποιώντας το Ctrl + Shift + Enter στη θέση του μόλις Εισαγω.
Εξήγηση:
- Η συνάρτηση SMALL με συνάρτηση IF επιστρέφει τις 5 κάτω αριθμητικές τιμές που ταιριάζουν με το City "Boston" και επιστρέφει τον πίνακα στη συνάρτηση SUM.
= SUM ({23, 27, 28, 28, 30}))
- Η συνάρτηση SUM λαμβάνει τον πίνακα των τιμών κάτω 5, η οποία έχει έναν πίνακα αριθμών κάτω 5 επιστρέφει το άθροισμα αυτών των αριθμών που χρησιμοποιούνται με CTRL + SHIFT + ENTER.
Εδώ το εύρος Πόλης & ποσότητας δίνεται ως το ονομαζόμενο εύρος. Τύπος Ctrl + Shift + Enter για να πάρετε το άθροισμα των 5 κάτω αριθμών καθώς αυτός είναι ένας τύπος πίνακα.
Όπως μπορείτε να δείτε στο παραπάνω στιγμιότυπο, το άθροισμα είναι 136.
Η παραπάνω διαδικασία χρησιμοποιείται για τον υπολογισμό του αθροίσματος μερικών αριθμών από το κάτω μέρος. Αλλά για υπολογισμό για ν (μεγάλος) αριθμός τιμών σε μεγάλο εύρος.
Χρησιμοποιήστε τον τύπο:
{ = SUM (ΜΙΚΡΟ (ΑΝ (Πόλη = "Βοστώνη", ποσότητα), ΣΕΙΡΑ (ΕΜΠΡΟΣ ("1:10")))) }
ΜΗΝ χρησιμοποιείτε τις σγουρές αγκύλες με μη αυτόματο τρόπο. Χρησιμοποιήστε τα Ctrl + Shift + Enter στη θέση του Enter.
Εδώ δημιουργούμε άθροισμα των τιμών κάτω από 10, παίρνοντας έναν πίνακα από 1 έως 10 {1. 2; 3; 4; 5; 6; 7; 8; 9; 10} χρησιμοποιώντας τις συναρτήσεις ROW & INDIRECT Excel.
Εδώ έχουμε το άθροισμα των 10 κάτω αριθμών που θα έχουν ως αποτέλεσμα 155.
Ακολουθούν μερικές παρατηρήσεις παρατήρησης που φαίνονται παρακάτω.
Σημειώσεις:
- Ο τύπος λειτουργεί μόνο με αριθμούς.
- Ο τύπος λειτουργεί μόνο όταν δεν υπάρχουν διπλότυπα στον πίνακα αναζήτησης
- Η συνάρτηση SUMPRODUCT θεωρεί μη αριθμητικές τιμές (όπως κείμενο abc) και τιμές σφάλματος (όπως #NUM!, #NULL!) Ως μηδενικές τιμές.
- Η συνάρτηση SUMPRODUCT θεωρεί τη λογική τιμή TRUE ως 1 και False ως 0.
- Ο πίνακας ορίσματος πρέπει να έχει το ίδιο μήκος με τη συνάρτηση.
Ελπίζουμε ότι αυτό το άρθρο σχετικά με τον τρόπο επιστροφής αθροίσματος των κορυφαίων 5 τιμών ή των κάτω 5 τιμών με κριτήρια στο Excel είναι επεξηγηματικό. Βρείτε περισσότερα άρθρα σχετικά με τις συναρτήσεις SUMPRODUCT εδώ. Μοιραστείτε το ερώτημά σας παρακάτω στο πλαίσιο σχολίων. Θα σας βοηθήσουμε.
Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT στο Excel: Επιστρέφει το SUM μετά τον πολλαπλασιασμό τιμών σε πολλούς πίνακες στο excel.
SUM εάν η ημερομηνία είναι μεταξύ : Επιστρέφει το άθροισμα των τιμών μεταξύ συγκεκριμένων ημερομηνιών ή περιόδου στο excel.
Άθροισμα εάν η ημερομηνία είναι μεγαλύτερη από τη δεδομένη ημερομηνία: Επιστρέφει το άθροισμα αξιών μετά τη δεδομένη ημερομηνία ή περίοδο στο excel.
2 τρόποι για άθροισμα ανά μήνα στο Excel: Επιστρέφει το άθροισμα αξιών μέσα σε ένα συγκεκριμένο μήνα στο excel.
Πώς να αθροίσετε πολλαπλές στήλες με συνθήκη: Επιστρέφει το άθροισμα των τιμών σε πολλές στήλες με συνθήκη στο Excel
Πώς να χρησιμοποιήσετε μπαλαντέρ στο excel : Μετρήστε κελιά που ταιριάζουν φράσεις χρησιμοποιώντας τους μπαλαντέρ στο excel
Δημοφιλή άρθρα
50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας
Επεξεργασία αναπτυσσόμενης λίστας
Απόλυτη αναφορά στο Excel
Εάν με μορφοποίηση υπό όρους
Αν με μπαλαντέρ
Vlookup κατά ημερομηνία
Μετατρέψτε ίντσες σε πόδια και ίντσες στο Excel 2016
Ενώστε το όνομα και το επώνυμο στο excel
Μετρήστε κελιά που ταιριάζουν είτε με το Α είτε με το Β
