Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο θα δείξει πώς να χρησιμοποιήσετε τη συνάρτηση SUMIFS και τους χαρακτήρες μπαλαντέρ για να αθροίσετε δεδομένα που αντιστοιχούν σε κελιά που περιέχουν συγκεκριμένο κείμενο στο Excel και στα Υπολογιστικά φύλλα Google.
Άθροισμα εάν το κείμενο περιέχει
Αρχικά, θα δείξουμε πώς να αθροίζουμε δεδομένα που σχετίζονται με κελιά που περιέχουν συγκεκριμένο κείμενο χρησιμοποιώντας τη συνάρτηση SUMIFS.
Η συνάρτηση SUMIFS αθροίζει σειρές δεδομένων που πληρούν ορισμένα κριτήρια. Η σύνταξή του είναι:
Αυτό το παράδειγμα θα συνοψίσει όλα Βαθμολογίες με Όνομα κράτους που περιέχει "Dakota" χρησιμοποιώντας τη συνάρτηση SUMIFS και τον χαρακτήρα μπαλαντέρ *.
| 1 | = SUMIFS (C3: C9, B3: B9, "*Dakota*") |
Ο χαρακτήρας * επιτρέπει τη θέση του σε οποιονδήποτε αριθμό (συμπεριλαμβανομένου του μηδενός) άλλων χαρακτήρων.
Σε αυτό το παράδειγμα, χρησιμοποιείται για την εύρεση όλων των κελιών που περιλαμβάνουν το κείμενο "Dakota". Αυτή η αναζήτηση δεν έχει διάκριση πεζών-κεφαλαίων, οπότε το "dakota" θεωρείται το ίδιο με το "Dakota" ή "DAKOTA". βόρεια Ντακότα και Νότια Ντακότα περιέχουν "Ντακότα" και συνεπώς περιλαμβάνονται στο άθροισμα.
Άθροισμα εάν το κείμενο ξεκινά με
Ο χαρακτήρας * μπορεί επίσης να χρησιμοποιηθεί για αναζήτηση κελιών που ξεκινούν με το καθορισμένο κείμενο:
| 1 | = SUMIFS (C3: C9, B3: B9, "New*") |
Νέα Υόρκη, New Jersey, και Νέο Μεξικό ξεκινούν με "Νέο" και συνεπώς περιλαμβάνονται στο άθροισμα. Λάβετε υπόψη ότι, με τον όρο αναζήτησης "Νέο*", το κελί κειμένου πρέπει να ξεκινά με "Νέο". Το να περιέχει κανείς αυτούς τους χαρακτήρες δεν αρκεί.
Άθροισμα εάν το κείμενο τελειώνει με
Ομοίως, μπορούμε να συνοψίσουμε όλα Βαθμολογίες Για Κρατών τελειώνει σε "ο" χρησιμοποιώντας:
| 1 | = SUMIFS (C3: C9, B3: B9, "*o") |
Νέο Μεξικό και Οχάιο τελειώνουν με "ο" και συνεπώς περιλαμβάνονται στο άθροισμα.
Χρησιμοποιώντας το ? Χαρακτήρας μπαλαντέρ
Ο ? Ο χαρακτήρας μπορεί να χρησιμοποιηθεί για να αναπαραστήσει οποιονδήποτε χαρακτήρα σε μια συμβολοσειρά κειμένου.
Αυτό το παράδειγμα τα βρίσκει όλα Ονόματα Πολιτείας ξεκινώντας με "Νέο", ακολουθούμενο από ακριβώς 7 χαρακτήρες (συμπεριλαμβανομένων των κενών).
| 1 | = SUMIFS (C3: C9, B3: B9, "New ???????") |
New Jersey και Νέο Μεξικό πληρούν αυτά τα κριτήρια, αλλά Νέα Υόρκη όχι, αφού υπάρχουν μόνο 5 χαρακτήρες που ακολουθούν το "New" in Νέα Υόρκη.
Σημειώστε ότι * και; Οι χαρακτήρες μπαλαντέρ μπορούν να συνδυαστούν εάν απαιτείται για τη δημιουργία πολύ συγκεκριμένων εντολών αναζήτησης. Αυτό το επόμενο παράδειγμα βρίσκει Ονόματα Πολιτείας που ξεκινούν με "N" και περιέχουν ένα "o" πριν από τον τελευταίο χαρακτήρα της συμβολοσειράς. Αυτό αποκλείει Νέο Μεξικό; ξεκινά με "N" αλλά δεν έχει "o" πριν από τον τελευταίο χαρακτήρα.
| 1 | = SUMIFS (C3: C9, B3: B9, "N*o?*") |
Αυτή η λειτουργία είναι ιδιαίτερα χρήσιμη κατά την αναζήτηση κωδικών προϊόντων, ταχυδρομικών κωδικών ή σειριακών αριθμών όπου η θέση κάθε χαρακτήρα έχει μια συγκεκριμένη σημασία.
Χρήση του χαρακτήρα ~ (Tilde)
Ο ειδικός χαρακτήρας known (γνωστός ως α πεσπιρώμενη) μας επιτρέπει να αντιμετωπίσουμε το * ή; χαρακτήρες σαν να είναι απλές τιμές κειμένου και δεν συμπεριφέρονται ως μπαλαντέρ.
Στο παρακάτω παράδειγμα, πρέπει να αθροίσουμε το Επίπεδο αποθεμάτων όταν ο Ονομασία προϊόντος ταιριάζει συγκεκριμένα με το κείμενο "Προϊόν;":
| 1 | = SUMIFS (C3: C8, B3: B8, "Product ~?") |
Το ~ αμέσως πριν από ένα * ή ένα; ο χαρακτήρας το μετατρέπει ξανά σε τιμή κειμένου, οπότε ο όρος αναζήτησης "Προϊόν ~;" χρησιμοποιείται για την εύρεση ακριβούς αντιστοίχισης κειμένου με το "Προϊόν;".
Συνδυασμός SUMIFS Wildcards με αναφορές κυττάρων
Συνήθως, δεν είναι καλή πρακτική η κωδικοποίηση τιμών σε τύπους. Αντ 'αυτού, είναι πιο ευέλικτο να χρησιμοποιούμε ξεχωριστά κελιά για να καθορίσουμε τιμές για τους όρους αναζήτησης.
Για να αναζητήσουμε αν τα κελιά περιέχουν το κείμενο που βρίσκεται στο κελί Ε3, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση SUMIFS με αναφορά κελιού και * μπαλαντέρ:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Σημειώστε ότι το κείμενο "Dakota" έχει αντικατασταθεί από την αναφορά κελιού & E3 & και οι χαρακτήρες * έχουν εισαχθεί σε εισαγωγικά ("").
Πολλές αναφορές κελιών και χαρακτήρες μπαλαντέρ μπορούν επίσης να συνδυαστούν μαζί. Να βρω Ονόματα Πολιτείας που ξεκινούν με το κείμενο στο κελί Ε3 και περιέχουν το κείμενο στο κελί F3 ακολουθούμενο από τουλάχιστον 1 ακόμη χαρακτήρα, μπορεί να χρησιμοποιηθεί ο ακόλουθος τύπος:
| 1 | = SUMIFS (C3: C9, B3: B9, E3 & "*" & F3 & "?*") |
Κλείδωμα αναφορών κυττάρων
Για να διευκολύνουμε την ανάγνωση των τύπων μας, δείξαμε τους τύπους χωρίς κλειδωμένες αναφορές κελιών:
| 1 | = SUMIFS (C3: C9, B3: B9, "*" & E3 & "*") |
Αλλά αυτοί οι τύποι δεν θα λειτουργήσουν σωστά όταν αντιγράφονται και επικολλούνται αλλού στο αρχείο σας. Αντ 'αυτού, θα πρέπει να χρησιμοποιήσετε αναφορές κλειδωμένων κελιών όπως αυτό:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "*" & E3 & "*") |
Διαβάστε το άρθρο μας σχετικά με το κλείδωμα αναφορών κυττάρων για να μάθετε περισσότερα.
Άθροισμα εάν το κελί περιέχει συγκεκριμένο κείμενο χρησιμοποιώντας μπαλαντέρ στα Υπολογιστικά φύλλα Google
Αυτοί οι τύποι λειτουργούν ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel.
