Τύπος Excel: Καταμέτρηση κελιών που περιέχουν είτε x ή y -

Γενική φόρμουλα

=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))

Περίληψη

Για να μετρήσετε κελιά που περιέχουν είτε x ή y, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στη συνάρτηση SUMPRODUCT. Στο παράδειγμα που εμφανίζεται, ο τύπος στο κελί F5 είναι:

=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))

Αυτή είναι η λύση ενός τύπου κυττάρων, που εξηγείται παρακάτω. Είναι επίσης δυνατό να χρησιμοποιήσετε έναν απλούστερο τύπο βασισμένο σε μια βοηθητική στήλη, που εξηγείται επίσης παρακάτω.

Εξήγηση

Όταν μετράτε κελιά με "λογική Ή", πρέπει να είστε προσεκτικοί για να μην κάνετε διπλή μέτρηση. Για παράδειγμα, εάν μετράτε κελιά που περιέχουν "abc" ή "def", δεν μπορείτε απλώς να προσθέσετε δύο συναρτήσεις COUNTIF, επειδή μπορεί να μετρήσετε διπλά κελιά που περιέχουν τόσο "abc" όσο και "def".

Διάλυμα ενός κυττάρου

Για έναν μόνο τύπο, μπορείτε να χρησιμοποιήσετε το SUMPRODUCT με ISNUMBER + FIND. Ο τύπος στο F5 είναι:

=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))

Αυτός ο τύπος βασίζεται στον τύπο που εξηγείται εδώ που εντοπίζει κείμενο μέσα σε ένα κελί:

ISNUMBER(FIND("abc",B5:B11)

Όταν δοθεί μια περιοχή κελιών, αυτό το απόσπασμα θα επιστρέψει έναν πίνακα τιμών TRUE / FALSE, μία τιμή για κάθε κελί το εύρος. Εφόσον το χρησιμοποιούμε δύο φορές (μία φορά για το "abc" και μία για το "def"), θα έχουμε δύο πίνακες.

Στη συνέχεια, προσθέτουμε αυτές τις συστοιχίες μαζί (με +), η οποία δημιουργεί μια νέα σειρά αριθμών. Κάθε αριθμός σε αυτόν τον πίνακα είναι το αποτέλεσμα της προσθήκης TRUE και FALSE τιμών στις δύο αρχικές συστοιχίες μαζί. Στο παράδειγμα που εμφανίζεται, ο προκύπτων πίνακας μοιάζει με αυτό:

(2;0;2;0;1;0;2)

Πρέπει να προσθέσουμε αυτούς τους αριθμούς, αλλά δεν θέλουμε να διπλασιάσουμε. Επομένως, πρέπει να βεβαιωθούμε ότι οποιαδήποτε τιμή μεγαλύτερη από το μηδέν μετράται μόνο μία φορά. Για να το κάνουμε αυτό, πιέζουμε όλες τις τιμές να TRUE ή FALSE με "> 0", και μετά να ισχύουν στο 1/0 με το διπλό αρνητικό (-).

Τέλος, το SUMPRODUCT επιστρέφει το άθροισμα όλων των τιμών στον πίνακα.

Βοηθητική λύση στήλης

Με μια βοηθητική στήλη για τον έλεγχο κάθε κελιού ξεχωριστά, το πρόβλημα είναι λιγότερο περίπλοκο. Μπορούμε να χρησιμοποιήσουμε το COUNTIF με δύο τιμές (παρέχονται ως "σταθερά πίνακα"). Ο τύπος στο C5 είναι:

=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)

Το COUNTIF θα επιστρέψει έναν πίνακα που περιέχει δύο στοιχεία: ένα πλήθος για το "abc" και ένα πλήθος για το "def". Για να αποφευχθεί η διπλή μέτρηση, προσθέτουμε τα στοιχεία προς τα πάνω και στη συνέχεια αναγκάζουμε το αποτέλεσμα να είναι TRUE / FALSE με "> 0". Τέλος, μετατρέπουμε τις τιμές TRUE / FALSE σε 1 και 0 με διπλό αρνητικό (-).

Το τελικό αποτέλεσμα είναι είτε 1 είτε 0 για κάθε κελί. Για να λάβετε ένα σύνολο για όλα τα κελιά του εύρους, απλώς αθροίστε τη στήλη βοηθού.