Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο θα δείξει πώς να χρησιμοποιήσετε τη συνάρτηση SUMIFS για να αθροίσετε δεδομένα που αντιστοιχούν σε κενά ή κενά κελιά στο Excel και στα Υπολογιστικά φύλλα Google.
Άθροισμα αν κενό
Αρχικά, θα δείξουμε πώς να αθροίζουμε σειρές με κενά κελιά.
Η συνάρτηση SUMIFS αθροίζει δεδομένα που πληρούν ορισμένα κριτήρια.
Μπορούμε να χρησιμοποιήσουμε τη συνάρτηση SUMIFS για να αθροίσουμε όλα Βαθμολογίες Για Παίκτες χωρίς ονόματα στο παρακάτω παράδειγμα.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Χρησιμοποιούμε διπλά εισαγωγικά ("") για να αναπαραστήσουμε ένα κενό κελί στο Excel. Το παράδειγμά μας αγνοεί Παίκτες Α, Β, Γ και Δ και αθροίζει τις βαθμολογίες για άγνωστο Παίκτες.
Αντιμετώπιση διαστημάτων ως κενών κελιών - με στήλη βοηθού
Πρέπει να είστε προσεκτικοί όταν αλληλεπιδράτε με κενά κελιά στο Excel. Τα κελιά μπορεί να σας φαίνονται κενά, αλλά το Excel δεν θα τα αντιμετωπίζει ως κενά. Αυτό μπορεί να συμβεί εάν το κελί περιέχει κενά, γραμμές ή άλλους αόρατους χαρακτήρες. Αυτό είναι ένα κοινό πρόβλημα κατά την εισαγωγή δεδομένων στο Excel από άλλες πηγές.
Εάν πρέπει να αντιμετωπίσουμε οποιαδήποτε κελιά που περιέχουν μόνο κενά με τον ίδιο τρόπο σαν να ήταν κενά, τότε ο τύπος στο προηγούμενο παράδειγμα δεν θα λειτουργήσει. Παρατηρήστε πώς ο τύπος SUMIFS δεν θεωρεί το κελί B9 παρακάτω ("") ως κενό:
Για να αντιμετωπίσουμε ένα κελί που περιέχει μόνο κενά σαν να ήταν ένα κενό κελί, μπορούμε να προσθέσουμε μια στήλη βοηθού με τη συνάρτηση TRIM για να αφαιρέσουμε τα επιπλέον κενά από την τιμή κάθε κελιού:
| 1 | = TRIM (B3) |
Εφαρμόζουμε τη συνάρτηση SUMIFS στη στήλη βοηθού και τώρα υπολογίζει με ακρίβεια το άθροισμα.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
Η στήλη βοηθός είναι εύκολο να δημιουργηθεί και να διαβαστεί εύκολα, αλλά μπορεί να θέλετε να έχετε έναν μόνο τύπο για να ολοκληρώσετε την εργασία. Αυτό καλύπτεται στην επόμενη ενότητα.
Αντιμετώπιση διαστημάτων ως κενών κελιών - χωρίς στήλη βοηθού
Εάν μια στήλη βοηθού δεν είναι κατάλληλη για τις ανάγκες σας, τότε μπορείτε να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT σε συνδυασμό με τις λειτουργίες LEN και TRIM για να αθροίσετε κενές σειρές.
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9)) = 0), D3: D9) |
Σε αυτό το παράδειγμα, χρησιμοποιούμε τη συνάρτηση SUMPRODUCT για να εκτελέσουμε έναν περίπλοκο υπολογισμό "άθροισμα αν". Ας περάσουμε από τον τύπο.
Αυτή είναι η τελική μας φόρμουλα:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9)) = 0), D3: D9) |
Πρώτον, η συνάρτηση SUMPRODUCT παραθέτει τον πίνακα τιμών από τις δύο περιοχές κελιών:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 8; 17; 50) |
Στη συνέχεια, η συνάρτηση TRIM αφαιρεί τους κεντρικούς και τους πίσω χώρους από Παίχτης ονόματα:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
Η λειτουργία LEN υπολογίζει τα μήκη των κομμένων Παίχτης ονόματα:
| 1 | = SUMPRODUCT (-({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
Με τη λογική δοκιμή (= 0), οποιαδήποτε περικοπή Παίχτης τα ονόματα με 0 χαρακτήρες αλλάζουν σε TRUE:
| 1 | = SUMPRODUCT (-({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {25; 10; 15; 5; 8; 17; 50) |
Στη συνέχεια, οι διπλές παύλες (-) μετατρέπουν τις τιμές TRUE και FALSE σε 1s και 0s:
| 1 | = SUMPRODUCT ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
Η συνάρτηση SUMPRODUCT στη συνέχεια πολλαπλασιάζει κάθε ζεύγος καταχωρήσεων στους πίνακες για να παράγει έναν πίνακα Βαθμολογίες μόνο για Παίχτης ονόματα που είναι κενά ή φτιάχνονται μόνο από κενά:
| 1 | = SUMPRODUCT ({0; 0; 15; 0; 8; 0; 50) |
Τέλος, οι αριθμοί στον πίνακα αθροίζονται μαζί:
| 1 | =73 |
Περισσότερες λεπτομέρειες σχετικά με τη χρήση των δηλώσεων Boolean και της εντολής "-" σε μια συνάρτηση SUMPRODUCT μπορείτε να βρείτε εδώ.
Άθροισμα αν είναι κενό στα Υπολογιστικά φύλλα Google
Αυτοί οι τύποι λειτουργούν ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel.
