Όταν το φύλλο εργασίας του Excel περιέχει κρυφές σειρές, φιλτραρισμένα δεδομένα ή ομαδοποιημένα δεδομένα, χρησιμοποιήστε τη συνάρτηση SUBTOTAL του Excel. Η συνάρτηση SUBTOTAL μπορεί να περιλαμβάνει ή να αποκλείει κρυφές τιμές στους υπολογισμούς. Εκτός από την εύρεση του συνόλου μιας ομάδας δεδομένων, το Excel μπορεί να υπολογίσει τον μέσο όρο, τη μέγιστη, την ελάχιστη, την τυπική απόκλιση και τη διακύμανση των δεδομένων σας. Δείτε πώς μπορείτε να εισαγάγετε υποσύνολα στο Excel.
Οι οδηγίες σε αυτό το άρθρο ισχύουν για το Excel για Microsoft 365, Excel 2019 και Excel 2016.
Σύνταξη της συνάρτησης SUBTOTAL
Χρησιμοποιήστε τη συνάρτηση SUBTOTAL στο Excel για να συνοψίσετε τις τιμές σε ένα φύλλο εργασίας με διαφορετικούς τρόπους. Είναι ιδιαίτερα χρήσιμο όταν το φύλλο εργασίας σας περιέχει κρυφές σειρές που θέλετε να συμπεριλάβετε στον υπολογισμό.
Η σύνταξη της συνάρτησης SUBTOTAL είναι: SUBTOTAL(συνάρτηση_αριθμός, ref1, ref2,…)
Το όρισμα function_num απαιτείται και καθορίζει τον τύπο της μαθηματικής πράξης που θα χρησιμοποιηθεί για το υποσύνολο. Η συνάρτηση SUBTOTAL μπορεί να προσθέσει αριθμούς, να υπολογίσει τη μέση τιμή των επιλεγμένων αριθμών, να βρει τις μέγιστες και ελάχιστες τιμές σε μια περιοχή, να μετρήσει τον αριθμό των τιμών σε μια επιλεγμένη περιοχή και πολλά άλλα.
Η συνάρτηση SUBTOTAL αγνοεί κελιά που δεν περιέχουν δεδομένα και κελιά με μη αριθμητικές τιμές.
Αυτό το όρισμα είναι ένας αριθμός και εξαρτάται από το αν θέλετε να συμπεριλάβετε κρυφές σειρές στο αποτέλεσμα ή να εξαιρέσετε κρυφές σειρές από το αποτέλεσμα. Αυτές οι σειρές ενδέχεται να αποκρύπτονται με μη αυτόματο τρόπο ή από ένα φίλτρο.
Τα ορίσματα function_num περιλαμβάνουν:
| Εργασία λειτουργίας | function_num | function_num |
|---|---|---|
| (περιλαμβάνει κρυφές τιμές) | (εξαιρούνται οι κρυφές τιμές) | |
| ΜΕΣΟΣ | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Τα ορίσματα αναφοράς function_num 1 έως 11 περιλαμβάνουν τιμές μόνο σε κρυφές σειρές όταν χρησιμοποιείται η εντολή Απόκρυψη για απόκρυψη σειρών. Όταν χρησιμοποιείτε την εντολή Φίλτρο, οι υπολογισμοί SUBTOTAL δεν περιλαμβάνουν αποτελέσματα κρυφών φίλτρων.
Το όρισμα ref1 απαιτείται. Αυτά είναι τα κελιά που χρησιμοποιούνται για τον υπολογισμό των αποτελεσμάτων του επιλεγμένου ορίσματος function_num. Αυτό το όρισμα μπορεί να είναι μια τιμή, ένα μεμονωμένο κελί ή μια περιοχή κελιών.
Τα ορίσματα ref2, … είναι προαιρετικά. Αυτά είναι πρόσθετα κελιά που περιλαμβάνονται στον υπολογισμό.
Χρήση της συνάρτησης SUBTOTAL με κρυφές σειρές
Οι συναρτήσεις του Excel μπορούν να εισαχθούν χειροκίνητα ή με τη βοήθεια του παραθύρου διαλόγου Επιχειρήματα συνάρτησης. Για να επεξηγήσετε τον τρόπο εισαγωγής της συνάρτησης με μη αυτόματο τρόπο χρησιμοποιώντας τη γραμμή τύπων, το ακόλουθο παράδειγμα χρησιμοποιεί το όρισμα COUNT function_num για να μετρήσει τον αριθμό των τιμών σε ορατές σειρές και σε ορατές και κρυφές σειρές.
Για να χρησιμοποιήσετε τη συνάρτηση SUBTOTAL για να μετρήσετε τον αριθμό των σειρών σε ένα φύλλο εργασίας:
-
Ξεκινήστε με ένα φύλλο εργασίας που περιέχει πολλές σειρές δεδομένων.
- Επιλέξτε το κελί που θα περιέχει τον αριθμό των ορατών σειρών.
-
Στη γραμμή συναρτήσεων, πληκτρολογήστε =SUBTOTAL. Καθώς πληκτρολογείτε, το Excel προτείνει μια συνάρτηση. Κάντε διπλό κλικ στη συνάρτηση SUBTOTAL.
Για να χρησιμοποιήσετε το πλαίσιο διαλόγου Function Arguments για να εισαγάγετε τη συνάρτηση SUBTOTAL, μεταβείτε στο Formulas και επιλέξτε Math & Trig >SUBTOTAL.
Image -
Στο αναπτυσσόμενο μενού που εμφανίζεται, κάντε διπλό κλικ στο όρισμα 102 – COUNT function_num.
Image -
Πληκτρολογήστε κόμμα (,).
Image -
Στο φύλλο εργασίας, επιλέξτε τα κελιά που θα συμπεριληφθούν στον τύπο.
Image -
Πατήστε Enter για να δείτε το αποτέλεσμα στο κελί που επιλέξατε στο βήμα 2.
Image - Επιλέξτε το κελί που θα περιέχει τον αριθμό των ορατών και των κρυφών σειρών.
- Στη γραμμή συναρτήσεων, πληκτρολογήστε =SUBTOTAL. Καθώς πληκτρολογείτε, το Excel προτείνει μια συνάρτηση. Κάντε διπλό κλικ στη συνάρτηση SUBTOTAL.
- Στο αναπτυσσόμενο μενού που εμφανίζεται, κάντε διπλό κλικ στο όρισμα 2 – COUNT function_num και, στη συνέχεια, πληκτρολογήστε ένα κόμμα (,).
-
Στο φύλλο εργασίας, επιλέξτε τα κελιά που θα συμπεριληφθούν στον τύπο και, στη συνέχεια, πατήστε Enter.
Image -
Απόκρυψη πολλών σειρών δεδομένων. Σε αυτό το παράδειγμα, οι σειρές με πωλήσεις μόνο κάτω από 100.000 $ ήταν κρυφές.
Image
Χρήση της συνάρτησης SUBTOTAL με φιλτραρισμένα δεδομένα
Η χρήση της συνάρτησης SUBTOTAL σε φιλτραρισμένα δεδομένα αγνοεί τα δεδομένα σε σειρές που έχουν αφαιρεθεί από το φίλτρο. Κάθε φορά που αλλάζουν τα κριτήρια φίλτρου, η συνάρτηση υπολογίζεται εκ νέου για να εμφανίσει το υποσύνολο για τις ορατές σειρές.
Για να χρησιμοποιήσετε τη συνάρτηση SUBTOTAL για να δείτε τις διαφορές στα αποτελέσματα υπολογισμού κατά το φιλτράρισμα δεδομένων:
-
Δημιουργία SUBTOTAL τύπων. Για παράδειγμα, δημιουργήστε τύπους για να προσδιορίσετε το υποσύνολο και τις μέσες τιμές των φιλτραρισμένων δεδομένων.
Δεν έχει σημασία αν χρησιμοποιείτε το όρισμα function_num για ορατές ή κρυφές σειρές. Και τα δύο ορίσματα παρέχουν το ίδιο αποτέλεσμα σε φιλτραρισμένα δεδομένα.
Image - Επιλέξτε οποιοδήποτε κελί στο σύνολο δεδομένων.
-
Μεταβείτε στο Home και, στη συνέχεια, επιλέξτε Ταξινόμηση & Φίλτρο > Φίλτρο.
Image -
Χρησιμοποιήστε τα αναπτυσσόμενα βέλη για να φιλτράρετε τα δεδομένα του φύλλου εργασίας.
Image -
Παρατηρήστε πώς αλλάζουν οι τιμές κάθε φορά που επιλέγετε διαφορετικά κριτήρια φίλτρου.
Image
Χρήση της συνάρτησης SUBTOTAL με ομαδοποιημένα δεδομένα
Όταν τα δεδομένα ομαδοποιούνται, υπάρχει ένας τρόπος να εφαρμοστεί η συνάρτηση SUBTOTAL σε κάθε μεμονωμένη ομάδα και στη συνέχεια να υπολογιστεί το συνολικό σύνολο για ολόκληρο το σύνολο δεδομένων.
- Επιλέξτε οποιοδήποτε κελί στο σύνολο δεδομένων.
-
Επιλέξτε Data > Subtotal για να ανοίξετε το πλαίσιο διαλόγου Subtotal..
Image - Επιλέξτε το αναπτυσσόμενο βέλος Σε κάθε αλλαγή στο και επιλέξτε την ομαδοποίηση στην οποία θα υπολογιστεί κάθε υποσύνολο.
- Επιλέξτε το αναπτυσσόμενο βέλος Use function και επιλέξτε μια συνάρτηση_αριθμός.
- Στη λίστα Προσθήκη υποσυνόλου στη λίστα, επιλέξτε τη στήλη στην οποία θα εφαρμοστεί ο τύπος.
-
Επιλέξτε OK.
Image -
Τα υποσύνολα εισάγονται για κάθε ομάδα δεδομένων και ένα γενικό σύνολο εισάγεται στο κάτω μέρος του συνόλου δεδομένων.
Image - Για να αλλάξετε τη συνάρτηση_αριθμός, επισημάνετε οποιοδήποτε κελί στο σύνολο δεδομένων και επιλέξτε Data > Υποσύνολο. Στη συνέχεια, κάντε τις επιλογές σας στο πλαίσιο διαλόγου Subtotal.
