Κεφάλαιο 17 · 17.18
Άθροισμα μόνο των φιλτραρισμένων γραμμών, και με πρόσθετη συνθήκη
Φιλτράρετε μια λίστα πωλήσεων σε μία κατηγορία, αλλά το σύνολο από κάτω εξακολουθεί να δείχνει το ποσό όλων των γραμμών. Αυτό είναι σωστό: η SUM μετρά και τις γραμμές που κρύβει το φίλτρο. Αν θέλετε να αθροίσετε μόνο ό,τι βλέπετε, χρησιμοποιήστε τη SUBTOTAL. Και αν θέλετε μέσα στις φιλτραρισμένες γραμμές μια πρόσθετη συνθήκη ή να παραλείψετε τιμές σφάλματος, υπάρχουν δύο ακόμη χρήσιμοι τύποι.
Ως παράδειγμα παίρνουμε τη λίστα πωλήσεων του ανθοπωλείου Bloemisterij Het Zonnetje: στη στήλη A η ημερομηνία, στη B το προϊόν, στη C η κατηγορία και στη D το ποσό, από τη γραμμή 2 έως και τη 300. Στη γραμμή κεφαλίδων υπάρχει φίλτρο (δείτε Ταξινόμηση και φιλτράρισμα).
Πού τοποθετείτε τους τύπους;
Όχι κάτω από τη λίστα: ένας τύπος ακριβώς κάτω από τη λίστα μπορεί να κρυφτεί ο ίδιος κατά το φιλτράρισμα και να μετακινηθεί κατά την ταξινόμηση. Αλλά ούτε απλώς δίπλα στη λίστα: ένα φίλτρο κρύβει ολόκληρες γραμμές, οπότε ένας τύπος στο F3 εξαφανίζεται κι αυτός όταν η γραμμή 3 φιλτραριστεί. Γι' αυτό τοποθετήστε τους στη γραμμή κεφαλίδων (γραμμή 1, δίπλα στις επικεφαλίδες) ή σε ξεχωριστό φύλλο εργασίας. Στο παράδειγμα βρίσκονται στη γραμμή 1, από τη στήλη F και μετά.
Το άθροισμα των ορατών γραμμών
=SUBTOTAL(109;D2:D300)
Ο πρώτος αριθμός επιλέγει τον υπολογισμό. Το 9 είναι το άθροισμα, όπως και το 109, με μία διαφορά: το 109 παραλείπει επίσης γραμμές που έχετε κρύψει χειροκίνητα (δεξί κλικ, Απόκρυψη). Το 9 παραλείπει μόνο γραμμές που κρύβει το φίλτρο. Χρησιμοποιήστε λοιπόν το 109 αν θέλετε να αθροίσετε ό,τι βλέπετε.
| Υπολογισμός | Φιλτραρισμένες | Και χειροκίνητα κρυμμένες |
|---|---|---|
| Άθροισμα | 9 | 109 |
| Πλήθος μη κενών κελιών | 3 | 103 |
| Μεσαίο σημείο | 1 | 101 |
| Μέγιστο | 4 | 104 |
Το πλήθος των ορατών γραμμών το μετράτε με =SUBTOTAL(103;B2:B300).

Ορατές γραμμές και μια πρόσθετη συνθήκη
Έχετε φιλτράρει στην κατηγορία Boeketten και θέλετε μέσα σε αυτήν να αθροίσετε μόνο τα πένθιμα στεφάνια (Rouwstuk). Η SUMIFS δεν λαμβάνει υπόψη το φίλτρο, ενώ η SUBTOTAL δεν δέχεται συνθήκη. Στο διαδίκτυο θα βρείτε για αυτό έναν τύπο με SUMPRODUCT, SUBTOTAL και OFFSET. Αυτός επιστρέφει στο Euro-Office την τιμή σφάλματος #NUM!. Με μια βοηθητική στήλη όμως λειτουργεί, και πιο απλά:
- Προσθέστε δίπλα στη λίστα μια στήλη Μπορεί να προβάλλει (ορατή), εδώ τη στήλη E. Επιλέξτε E2:E300, πληκτρολογήστε =SUBTOTAL(103;D2) και πατήστε Ctrl+Enter. Κάθε γραμμή παίρνει την τιμή 1 όσο είναι ορατή και 0 όταν την κρύβει το φίλτρο.
- Αθροίστε με τη SUMIFS και συμπεριλάβετε τη βοηθητική στήλη ως πρόσθετη συνθήκη: =SUMIFS(D2:D300;B2:B300;"Rouwstuk";E2:E300;1).
Αν αλλάξετε το φίλτρο, η βοηθητική στήλη και το άθροισμα υπολογίζονται αμέσως ξανά.
Παράλειψη τιμών σφάλματος
Αν κάπου στη στήλη υπάρχει #N/A ή #DIV/0!, τότε και η SUM και η SUBTOTAL επιστρέφουν τιμή σφάλματος. Η AGGREGATE μπορεί να παραλείψει τα σφάλματα:
=AGGREGATE(9;7;D2:D300)
Το 9 είναι πάλι το άθροισμα, το 7 σημαίνει: παράλειψη κρυμμένων γραμμών και τιμών σφάλματος.
Παγίδες
- Οι κρυμμένες στήλες μετρούν κανονικά: η SUBTOTAL λαμβάνει υπόψη μόνο τις κρυμμένες γραμμές.
