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