Γενική φόρμουλα
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Περίληψη
Για να μετρήσετε πόσες φορές μια συγκεκριμένη λέξη (ή οποιοδήποτε υπόστρωμα) εμφανίζεται μέσα σε ένα εύρος κελιών, μπορείτε να χρησιμοποιήσετε έναν τύπο βασισμένο στις συναρτήσεις SUBSTITUTE, LEN και SUMPRODUCT. Στο παράδειγμα που εμφανίζεται, ο τύπος στο C11 είναι:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Σημείωση: Ο τύπος σε αυτήν τη σελίδα μετρά τις εμφανίσεις μιας λέξης σε ένα εύρος. Για παράδειγμα, εάν ένα κελί περιέχει δύο παρουσίες μιας λέξης, θα συνεισφέρει 2 στο συνολικό πλήθος. Εάν θέλετε απλώς να μετρήσετε κελιά που περιέχουν μια συγκεκριμένη λέξη, δείτε αυτόν τον απλό τύπο βασισμένο στη συνάρτηση COUNTIF.
Εξήγηση
Στη γενική έκδοση του τύπου, το rng αντιπροσωπεύει το εύρος που πρέπει να ελέγξετε και το txt είναι η λέξη ή το substring που μετρά.
Στο παράδειγμα που εμφανίζεται, το B5: B8 είναι το εύρος που πρέπει να ελέγξετε και το C2 περιέχει το κείμενο (λέξη ή υπόστρωμα) για μέτρηση.
Για κάθε κελί στην περιοχή, το SUBSTITUTE αφαιρεί το υπόστρωμα από το αρχικό κείμενο και το LEN υπολογίζει το μήκος του κειμένου χωρίς το υπόστρωμα. Αυτός ο αριθμός αφαιρείται στη συνέχεια από το μήκος του αρχικού κειμένου. Το αποτέλεσμα είναι ο αριθμός των χαρακτήρων που καταργήθηκαν από SUBSTITUTE.
Στη συνέχεια, ο αριθμός των χαρακτήρων που αφαιρούνται διαιρείται με το μήκος του υποστρώματος. Επομένως, εάν μια συμβολοσειρά ή μια λέξη έχει μήκος 5 χαρακτήρων και λείπουν 10 χαρακτήρες μετά την αφαίρεσή του από το αρχικό κείμενο, γνωρίζουμε ότι η συμβολοσειρά / λέξη εμφανίστηκε δύο φορές στο αρχικό κείμενο.
Επειδή ο παραπάνω υπολογισμός είναι τυλιγμένος στη συνάρτηση SUMPRODUCT, το αποτέλεσμα είναι ένας πίνακας που περιέχει έναν αριθμό για κάθε κελί στο εύρος. Αυτοί οι αριθμοί αντιπροσωπεύουν τον αριθμό των εμφανίσεων του substring σε κάθε κελί. Για αυτό το παράδειγμα, ο πίνακας μοιάζει με αυτό: (1; 1; 0; 1)
Τέλος, το SUMPRODUCT συγκεντρώνει όλα τα στοιχεία του πίνακα για να πάρει τις συνολικές εμφανίσεις του substring στο εύρος των κελιών.
Αγνοώντας την υπόθεση
Το SUBSTITUTE είναι μια συνάρτηση με διάκριση πεζών-κεφαλαίων, οπότε θα ταιριάζει με την περίπτωση κατά την εκτέλεση μιας αντικατάστασης Εάν πρέπει να μετρήσετε τόσο τις κεφαλαίες όσο και τις πεζά συμβάντα μιας λέξης ή υποστρώματος, χρησιμοποιήστε τη συνάρτηση UPPER μέσα στο SUBSTITUTE για να μετατρέψετε το κείμενο σε κεφαλαία πριν εκτελέσετε την αντικατάσταση:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
