Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο θα δείξει πώς να υπολογίσετε το "subtotal if", μετρώντας μόνο τις ορατές σειρές με κριτήρια.
ΣΥΝΟΛΙΚΗ συνάρτηση
Η συνάρτηση SUBTOTAL μπορεί να εκτελέσει διάφορους υπολογισμούς σε μια σειρά δεδομένων (μέτρηση, άθροισμα, μέσος όρος κ.λπ.). Το πιο σημαντικό, μπορεί να χρησιμοποιηθεί για τον υπολογισμό μόνο σε ορατές (φιλτραρισμένες) σειρές. Σε αυτό το παράδειγμα, θα χρησιμοποιήσουμε τη συνάρτηση για την καταμέτρηση (COUNTA) ορατών σειρών ορίζοντας το όρισμα SUBTOTAL function_num σε 3 (Μπορείτε να βρείτε μια πλήρη λίστα των πιθανών συναρτήσεων εδώ.)
= SUBTOTAL (3, $ D $ 2: $ D $ 14)
Παρατηρήστε πώς αλλάζουν τα αποτελέσματα καθώς φιλτράρουμε μη αυτόματα τις γραμμές.
ΣΥΝΟΛΙΚΟ ΑΝ
Για να δημιουργήσουμε ένα "Subtotal If", θα χρησιμοποιήσουμε έναν συνδυασμό SUMPRODUCT, SUBTOTAL, OFFSET, ROW και MIN σε έναν τύπο πίνακα. Χρησιμοποιώντας αυτόν τον συνδυασμό, μπορούμε ουσιαστικά να δημιουργήσουμε μια γενική συνάρτηση "SUBTOTAL IF". Ας δούμε ένα παράδειγμα.
Έχουμε μια λίστα μελών και την κατάσταση συμμετοχής τους για κάθε εκδήλωση:
Υποτίθεται ότι μας ζητείται να μετρήσουμε τον αριθμό των μελών που έχουν παρακολουθήσει μια εκδήλωση δυναμικά καθώς φιλτράρουμε χειροκίνητα τη λίστα ως εξής:
Για να το πετύχουμε αυτό, μπορούμε να χρησιμοποιήσουμε αυτόν τον τύπο:
= SUMPRODUCT ((=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Παραβρέθηκε")*(SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Όταν χρησιμοποιείτε το Excel 2022 και παλαιότερα, πρέπει να εισαγάγετε τον τύπο πίνακα πατώντας CTRL + SHIFT + ENTER για να πείτε στο Excel ότι εισάγετε έναν τύπο πίνακα. Θα γνωρίζετε ότι ο τύπος έχει εισαχθεί σωστά ως τύπος πίνακα όταν εμφανίζονται αγκύλες γύρω από τον τύπο (δείτε την εικόνα παραπάνω).
Πώς λειτουργεί ο τύπος;
Ο τύπος λειτουργεί πολλαπλασιάζοντας δύο πίνακες στο εσωτερικό του SUMPRODUCT, όπου ο πρώτος πίνακας αφορά τα κριτήριά μας και ο δεύτερος πίνακας φιλτράρει μόνο σε ορατές σειρές:
= SUMPRODUCT (*)
Ο πίνακας κριτηρίων
Ο πίνακας κριτηρίων αξιολογεί κάθε σειρά στο εύρος τιμών μας (Κατάσταση "Attended" σε αυτό το παράδειγμα) και δημιουργεί έναν πίνακα όπως αυτός:
=(=)
= (D2: D14 = "Παρακολούθησε")
Παραγωγή:
{ΑΛΗΘΗΣ; ΨΕΥΔΗΣ; ΨΕΥΔΗΣ; ΑΛΗΘΗΣ; ΨΕΥΔΗΣ; TURE? TURE? TURE? ΨΕΥΔΗΣ; ΨΕΥΔΗΣ; ΑΛΗΘΗΣ; ΨΕΥΔΗΣ; ΑΛΗΘΗΣ}
Σημειώστε ότι η έξοδος στον πρώτο πίνακα στον τύπο μας αγνοεί εάν η γραμμή είναι ορατή ή όχι, όπου έρχεται να βοηθήσει ο δεύτερος πίνακας.
Ο πίνακας ορατότητας
Χρησιμοποιώντας το SUBTOTAL για να εξαιρέσουμε τις μη ορατές γραμμές στην περιοχή μας, μπορούμε να δημιουργήσουμε τον πίνακα ορατότητας. Ωστόσο, το SUBTOTAL από μόνο του θα επιστρέψει μία μόνο τιμή, ενώ το SUMPRODUCT αναμένει μια σειρά τιμών. Για να επιλύσουμε αυτό το πρόβλημα, χρησιμοποιούμε το OFFSET για να περάσουμε μία σειρά κάθε φορά. Αυτή η τεχνική απαιτεί τροφοδοσία του πίνακα OFFSET που περιέχει έναν αριθμό κάθε φορά. Ο δεύτερος πίνακας μοιάζει με αυτόν:
= SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))
Παραγωγή:
{1;1;0;0;1;1}
Συνδέοντας τα δύο μαζί:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
ΣΥΝΟΛΙΚΟ ΑΝ με πολλαπλά κριτήρια
Για να προσθέσετε πολλαπλά κριτήρια, απλώς πολλαπλασιάστε περισσότερα κριτήρια μαζί στο SUMPRODUCT, όπως παρακάτω:
= SUMPRODUCT ((=)*(=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((E2: E14 = "Παρακολούθησαν")*(B2: B14 = 2019)*(SUBTOTAL (3, OFFSET (E2, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) ))
ΣΥΝΟΛΙΚΟ ΑΝ στα Υπολογιστικά φύλλα Google
Η συνάρτηση SUBTOTAL IF λειτουργεί ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel:
