Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε το Excel ΣΥΝΟΛΙΚΗ συνάρτηση στο Excel για τον υπολογισμό συνοπτικών στατιστικών.
SUBTOTAL Επισκόπηση συνάρτησης
Η συνάρτηση SUBTOTAL Υπολογίζει μια συνοπτική στατιστική για μια σειρά δεδομένων. Οι διαθέσιμες στατιστικές περιλαμβάνουν, αλλά δεν περιορίζονται στο μέσο όρο, τυπική απόκλιση, μέτρηση, ελάχιστο και μέγιστο. Δείτε την πλήρη λίστα παρακάτω στην ενότητα εισόδων λειτουργίας:
Για να χρησιμοποιήσετε τη συνάρτηση SUBTOTAL Excel Worksheet, επιλέξτε ένα κελί και πληκτρολογήστε:
(Παρατηρήστε πώς εμφανίζονται οι εισαγωγές τύπου)
ΣΥΝΟΛΙΚΗ Σύνταξη και Εισόδους Λειτουργίας:
| 1 | = SUBTOTAL (αριθμός_συνάρτησης, REF1) |
function_num - Ένας αριθμός που αντιπροσωπεύει ποια λειτουργία πρέπει να εκτελεστεί.
REF1 - Εύρος ή αναφορές που περιέχουν δεδομένα για υπολογισμό.
Τι είναι η συνάρτηση SUBTOTAL;
Το SUBTOTAL είναι μία από τις μοναδικές συναρτήσεις στα υπολογιστικά φύλλα, επειδή μπορεί να ξεχωρίσει τη διαφορά μεταξύ κρυφών κελιών και μη κρυφών κελιών. Αυτό μπορεί να αποδειχθεί αρκετά χρήσιμο όταν ασχολείστε με φιλτραρισμένα εύρη ή όταν πρέπει να ρυθμίσετε υπολογισμούς με βάση διαφορετικές επιλογές χρηστών. Δεδομένου ότι ξέρει επίσης να αγνοεί άλλες ΣΥΝΟΛΙΚΕΣ συναρτήσεις από τους υπολογισμούς του, μπορούμε επίσης να το χρησιμοποιήσουμε σε μεγάλα συνοπτικά δεδομένα χωρίς φόβο διπλής καταμέτρησης.
Βασική περίληψη με SUBTOTAL
Ας υποθέσουμε ότι είχατε έναν πίνακα ταξινομημένων πωλήσεων προϊόντων και θέλετε να δημιουργήσετε σύνολα για κάθε προϊόν, καθώς και να δημιουργήσετε ένα συνολικό σύνολο. Θα μπορούσατε να χρησιμοποιήσετε έναν Συγκεντρωτικό Πίνακα ή μπορείτε να εισαγάγετε ορισμένους τύπους. Εξετάστε αυτήν τη διάταξη:
Έχω τοποθετήσει μερικές ΣΥΝΟΛΙΚΕΣ συναρτήσεις στα κελιά Β5 και Β8 που μοιάζουν
| 1 | = ΣΥΝΟΛΙΚΟ (9, Β2: Β4) |
Από τη σύνταξη, μπορείτε να χρησιμοποιήσετε μια ποικιλία αριθμών για το πρώτο όρισμα. Στη συγκεκριμένη περίπτωση, χρησιμοποιούμε το 9 για να δηλώσουμε ότι θέλουμε να κάνουμε ένα άθροισμα.
Ας επικεντρωθούμε στο κελί Β9. Έχει αυτόν τον τύπο, ο οποίος περιλαμβάνει ολόκληρο το εύρος δεδομένων της στήλης Β, αλλά δεν περιλαμβάνει τα άλλα υποσύνολα.
| 1 | = ΣΥΝΟΛΙΚΟ (9, Β2: Β8) |
ΣΗΜΕΙΩΣΗ: Εάν δεν θέλετε να γράψετε μόνοι σας όλους τους τύπους περίληψης, μπορείτε να μεταβείτε στην κορδέλα Δεδομένων και να χρησιμοποιήσετε τον οδηγό Outline - Subtotal. Θα εισαγάγει αυτόματα σειρές και θα τοποθετήσει τους τύπους για εσάς.
Διαφορά στα πρώτα επιχειρήματα
Στο πρώτο παράδειγμα, χρησιμοποιήσαμε ένα 9 για να δείξουμε ότι θέλουμε να κάνουμε ένα άθροισμα. Η διαφορά μεταξύ της χρήσης 9 και 109 θα ήταν στο πώς θέλουμε η συνάρτηση να χειρίζεται κρυφές σειρές. Εάν χρησιμοποιείτε τους προσδιορισμούς 1XX, η συνάρτηση δεν θα περιλαμβάνει γραμμές που έχουν κρυφτεί ή φιλτραριστεί με μη αυτόματο τρόπο.
Εδώ είναι το τραπέζι μας από πριν. Έχουμε αλλάξει τις συναρτήσεις για να δούμε τη διαφορά μεταξύ των ορισμάτων 9 και 109. Με όλα ορατά, τα αποτελέσματα είναι τα ίδια.
Εάν εφαρμόσουμε ένα φίλτρο για να φιλτράρουμε την τιμή 6 στη στήλη Β, οι δύο συναρτήσεις παραμένουν οι ίδιες.
Αν κρύψουμε χειροκίνητα τις γραμμές, βλέπουμε τη διαφορά. Η συνάρτηση 109 μπόρεσε να αγνοήσει την κρυφή σειρά ενώ η συνάρτηση 9 όχι.
Αλλαγή λειτουργίας μαθηματικών με SUBTOTAL
Mightσως σας αρέσει μερικές φορές να μπορείτε να δώσετε στον χρήστη σας τη δυνατότητα να αλλάξει τον τύπο των υπολογισμών που εκτελούνται. Για παράδειγμα, θέλουν να πάρουν το άθροισμα ή τον μέσο όρο. Δεδομένου ότι το SUBTOTAL ελέγχει τη μαθηματική λειτουργία με έναν αριθμό ορίσματος, μπορείτε να το γράψετε σε έναν μόνο τύπο. Εδώ είναι η ρύθμιση μας:
Δημιουργήσαμε ένα αναπτυσσόμενο μενού στο D2 όπου ο χρήστης μπορεί να επιλέξει είτε "Άθροισμα" είτε "Μέσος όρος". Ο τύπος στο Ε2 είναι:
| 1 | = SUBTOTAL (IF (D2 = "Μέσος όρος", 1, IF (D2 = "Άθροισμα", 9)), B2: B4) |
Εδώ, η συνάρτηση IF πρόκειται να καθορίσει ποιο αριθμητικό όρισμα θα δώσει στο ΣΥΝΟΛΙΚΟ. Εάν το A5 είναι "Μέσος όρος", τότε θα βγάλει 1 και SUBTOTAL θα δώσει τον μέσο όρο B2: B4. Or, αν το Α5 ισούται με το "άθροισμα", τότε το IF εξάγει ένα 9 και έχουμε ένα διαφορετικό αποτέλεσμα.
Θα μπορούσατε να επεκτείνετε αυτήν τη δυνατότητα χρησιμοποιώντας έναν πίνακα αναζήτησης για να παραθέσετε ακόμη περισσότερους τύπους λειτουργιών που θέλετε να εκτελέσετε. Ο πίνακας αναζήτησης μπορεί να μοιάζει με αυτόν
Στη συνέχεια, μπορείτε να αλλάξετε τον τύπο στο E2 για να είναι
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Τύποι υπό όρους με SUBTOTAL
Ενώ το SUBTOTAL έχει πολλές λειτουργίες που μπορεί να κάνει, δεν μπορεί να ελέγξει τα κριτήρια από μόνο του. Ωστόσο, μπορούμε να το χρησιμοποιήσουμε σε μια στήλη βοηθού για να εκτελέσουμε αυτήν τη λειτουργία. Όταν έχετε μια στήλη δεδομένων που γνωρίζετε πάντα έχετε ένα κομμάτι δεδομένων, μπορείτε να χρησιμοποιήσετε τη δυνατότητα SUBTOTAL για τον εντοπισμό κρυφών σειρών.
Ακολουθεί ο πίνακας με τον οποίο θα εργαστούμε σε αυτό το παράδειγμα. Τελικά, θα θέλαμε να μπορούμε να αθροίσουμε τις τιμές για το "Apple", αλλά και να αφήσουμε τον χρήστη να φιλτράρει τη στήλη Ποσότητα.
Αρχικά, δημιουργήστε μια στήλη βοηθού στην οποία θα βρίσκεται η συνάρτηση SUBTOTAL. Στο C2, ο τύπος είναι:
| 1 | = ΣΥΝΟΛΙΚΟ (103, Α2) |
Θυμηθείτε ότι το 103 σημαίνει ότι θέλουμε να κάνουμε μια COUNTA. Σας συνιστώ να χρησιμοποιείτε το COUNTA επειδή μπορείτε στη συνέχεια να γεμίσετε το κελί αναφοράς του Α2 είτε αριθμούς ή κείμενο. Τώρα θα έχετε έναν πίνακα που μοιάζει με αυτόν:
Αυτό δεν φαίνεται χρήσιμο στην αρχή γιατί όλες οι τιμές είναι μόλις 1. Ωστόσο, εάν αποκρύψουμε τη γραμμή 3, αυτό το "1" στο C3 θα αλλάξει σε 0 επειδή δείχνει προς μια κρυφή σειρά. Παρόλο που είναι αδύνατο να υπάρχει μια εικόνα που να δείχνει την τιμή του συγκεκριμένου κρυμμένου κελιού, μπορείτε να την ελέγξετε αποκρύπτοντας τη σειρά και στη συνέχεια να γράψετε έναν βασικό τύπο όπως αυτός για έλεγχο.
| 1 | = C3 |
Τώρα που έχουμε μια στήλη που θα αλλάξει σε αξία ανάλογα με το αν είναι κρυμμένη ή όχι, είμαστε έτοιμοι να γράψουμε την τελική εξίσωση. Τα SUMIFS μας θα μοιάζουν με αυτό
Σε αυτόν τον τύπο, θα αθροίσουμε τιμές μόνο από τη στήλη Β όταν η στήλη Α ισούται με "Μήλο", και η τιμή στη στήλη C είναι 1 (δηλαδή, η σειρά δεν είναι κρυφή). Ας πούμε ότι ο χρήστης μας θέλει να φιλτράρει το 600, επειδή φαίνεται ασυνήθιστα υψηλό. Μπορούμε να δούμε ότι ο τύπος μας δίνει το σωστό αποτέλεσμα.
Με αυτήν τη δυνατότητα, θα μπορούσατε να εφαρμόσετε μια επιταγή σε COUNTIFS, SUMIFS ή ακόμα και σε SUMPRODUCT. Προσθέτετε τη δυνατότητα να επιτρέπετε στους χρήστες σας να ελέγχουν ορισμένους τεμαχιστές πίνακα και είστε έτοιμοι να δημιουργήσετε έναν εκπληκτικό πίνακα ελέγχου.
SUBTOTAL στα Υπολογιστικά φύλλα Google
Η συνάρτηση SUBTOTAL λειτουργεί ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel:
ΥΠΟΒΟΛΑ Παραδείγματα σε VBA
Μπορείτε επίσης να χρησιμοποιήσετε τη συνάρτηση SUBTOTAL στο VBA. Τύπος:application.worksheetfunction.subtotal (function_num, reh1)
Εκτέλεση των ακόλουθων δηλώσεων VBA
| 1234567891011121314151617 | Εύρος ("C7") = Application.WorksheetFunction.Subtotal (1, Range ("C2: C5"))Εύρος ("C8") = Application.WorksheetFunction.Subtotal (2, Range ("C2: C5"))Εύρος ("C9") = Application.WorksheetFunction.Subtotal (4, Range ("C2: C5"))Εύρος ("C10") = Application.WorksheetFunction.Subtotal (5, Range ("C2: C5"))Εύρος ("C11") = Application.WorksheetFunction.Subtotal (9, Range ("C2: CE5"))Εύρος ("D7") = Application.WorksheetFunction.Subtotal (1, Range ("D2: D5"))Εύρος ("D8") = Application.WorksheetFunction.Subtotal (2, Range ("D2: D5"))Εύρος ("D9") = Application.WorksheetFunction.Subtotal (4, Range ("D2: D5"))Εύρος ("D10") = Application.WorksheetFunction.Subtotal (5, Range ("D2: D5"))Εύρος ("D11") = Application.WorksheetFunction.Subtotal (9, Range ("D2: D5"))Εύρος ("E7") = Application.WorksheetFunction.Subtotal (1, Range ("E2: E5"))Εύρος ("E8") = Application.WorksheetFunction.Subtotal (2, Range ("E2: E5"))Εύρος ("E9") = Application.WorksheetFunction.Subtotal (4, Range ("E2: E5"))Εύρος ("E10") = Application.WorksheetFunction.Subtotal (5, Range ("E2: E5"))Εύρος ("E11") = Application.WorksheetFunction.Subtotal (9, Range ("E2: E5")) |
θα παράγει τα ακόλουθα αποτελέσματα
Για τα ορίσματα της συνάρτησης (function_num, κ.λπ.), μπορείτε είτε να τα εισαγάγετε απευθείας στη συνάρτηση είτε να ορίσετε μεταβλητές που θα χρησιμοποιηθούν.
Επιστρέψτε στη λίστα όλων των λειτουργιών στο Excel
