Γενική φόρμουλα
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Περίληψη
Για να μετρήσετε ορατές σειρές μόνο με κριτήρια, μπορείτε να χρησιμοποιήσετε έναν μάλλον περίπλοκο τύπο που βασίζεται σε SUMPRODUCT, SUBTOTAL και OFFSET. Στο παράδειγμα που εμφανίζεται, ο τύπος στο C12 είναι:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Πρόλογος
Η συνάρτηση SUBTOTAL μπορεί εύκολα να δημιουργήσει αθροίσματα και μετρήσεις για κρυφές και μη κρυφές σειρές. Ωστόσο, δεν είναι σε θέση να χειριστεί κριτήρια όπως COUNTIF ή SUMIF χωρίς κάποια βοήθεια. Μία λύση είναι να χρησιμοποιήσετε το SUMPRODUCT για να εφαρμόσετε τόσο τη συνάρτηση SUBTOTAL (μέσω OFFSET) όσο και τα κριτήρια. Οι λεπτομέρειες αυτής της προσέγγισης περιγράφονται παρακάτω.
Εξήγηση
Στον πυρήνα, αυτός ο τύπος λειτουργεί δημιουργώντας δύο πίνακες στο SUMPRODUCT. Ο πρώτος πίνακας εφαρμόζει κριτήρια και ο δεύτερος πίνακας χειρίζεται την ορατότητα:
=SUMPRODUCT(criteria*visibility)
Τα κριτήρια εφαρμόζονται με μέρος του τύπου:
=(C5:C8=C10)
Που δημιουργεί έναν πίνακα όπως αυτό:
(FALSE;TRUE;FALSE;TRUE)
Όπου TRUE σημαίνει "πληροί κριτήρια" Σημείωση επειδή χρησιμοποιούμε τον πολλαπλασιασμό (*) σε αυτόν τον πίνακα, οι τιμές TRUE FALSE θα μετατραπούν αυτόματα σε 1 και 0 από τη μαθηματική λειτουργία, οπότε καταλήγουμε με:
(0;1;0;1)
Το φίλτρο ορατότητας εφαρμόζεται χρησιμοποιώντας το SUBTOTAL, με αριθμό λειτουργίας 103.
Το SUBTOTAL μπορεί να εξαιρέσει κρυφές σειρές κατά την εκτέλεση υπολογισμών, οπότε μπορούμε να το χρησιμοποιήσουμε σε αυτήν την περίπτωση για να δημιουργήσουμε ένα "φίλτρο" για να αποκλείσουμε κρυφές σειρές στο εσωτερικό του SUMPRODUCT. Το πρόβλημα όμως είναι ότι το SUBTOTAL επιστρέφει έναν μόνο αριθμό, ενώ χρειαζόμαστε μια σειρά αποτελεσμάτων για να το χρησιμοποιήσουμε με επιτυχία μέσα στο SUMPRODUCT. Το κόλπο είναι να χρησιμοποιήσετε το OFFSET για να τροφοδοτήσετε SUBTOTAL μία αναφορά ανά σειρά, έτσι ώστε το OFFSET να επιστρέφει ένα αποτέλεσμα ανά σειρά.
Φυσικά, αυτό απαιτεί ένα άλλο τέχνασμα, δηλαδή να δώσει στο OFFSET έναν πίνακα που περιέχει έναν αριθμό ανά σειρά, ξεκινώντας από το μηδέν. Το κάνουμε αυτό με μια έκφραση που βασίζεται στη λειτουργία ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
που θα δημιουργήσει έναν πίνακα όπως αυτό:
(0;1;2;3)
Συνοπτικά, ο δεύτερος πίνακας (που χειρίζεται την ορατότητα χρησιμοποιώντας ΥΠΟΤΡΟΦΙΑ), δημιουργείται ως εξής:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
Και, τέλος, έχουμε:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Που επιστρέφει 1.
Πολλαπλά κριτήρια
Μπορείτε να επεκτείνετε τον τύπο για να χειριστείτε πολλά κριτήρια όπως αυτό:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Αθροίζοντας αποτελέσματα
Για να επιστρέψετε ένα άθροισμα τιμών αντί για μια καταμέτρηση, μπορείτε να προσαρμόσετε τον τύπο ώστε να περιλαμβάνει ένα εύρος αθροίσεων:
=SUMPRODUCT(criteria*visibility*sumrange)
Τα κριτήρια και οι πίνακες ορατότητας λειτουργούν το ίδιο όπως εξηγείται παραπάνω, εξαιρουμένων των κελιών που δεν είναι ορατά. Εάν χρειάζεστε μερική αντιστοίχιση, μπορείτε να δημιουργήσετε μια έκφραση χρησιμοποιώντας ISNUMBER + SEARCH, όπως εξηγείται εδώ.
