Γενική φόρμουλα
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Περίληψη
Για να μετρήσετε κελιά που δεν περιέχουν πολλές διαφορετικές συμβολοσειρές, μπορείτε να χρησιμοποιήσετε έναν μάλλον περίπλοκο τύπο που βασίζεται στη συνάρτηση MMULT. Στο παράδειγμα που εμφανίζεται, ο τύπος στο F5 είναι:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
όπου "δεδομένα" είναι η ονομαζόμενη περιοχή B5: B14 και "exclude" είναι η ονομαστική περιοχή D5: D7.
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί με το control + shift + enter
Πρόλογος
Αυτός ο τύπος περιπλέκεται από την απαίτηση "περιέχει". Εάν χρειάζεστε απλώς έναν τύπο για να μετρήσετε κελιά που δεν ισούνται με * πολλά πράγματα, μπορείτε να χρησιμοποιήσετε έναν πιο απλό τύπο βασισμένο στη συνάρτηση MATCH. Επίσης, εάν έχετε περιορισμένο αριθμό συμβολοσειρών για εξαίρεση, μπορείτε να χρησιμοποιήσετε τη συνάρτηση COUNTIFS ως εξής:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Ωστόσο, με αυτήν την προσέγγιση, πρέπει να εισαγάγετε ένα νέο ζευγάρι ορίσματα εύρους / κριτηρίων για κάθε συμβολοσειρά για εξαίρεση. Αντίθετα, ο τύπος που εξηγείται παρακάτω μπορεί να χειριστεί έναν μεγάλο αριθμό συμβολοσειρών για να αποκλείσει τις καταχωρήσεις απευθείας στο φύλλο εργασίας.
Τέλος, αυτός ο τύπος είναι περίπλοκος. Ενημερώστε με εάν έχετε μια απλούστερη φόρμουλα για να προτείνετε :)
Εξήγηση
Ο πυρήνας αυτού του τύπου είναι ISNUMBER και SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Εδώ, μεταφέρουμε τα στοιχεία στην ονομαζόμενη περιοχή "exclude" και μετά τροφοδοτούμε το αποτέλεσμα στο SEARCH ως "εύρεση κειμένου", με "δεδομένα" ως "μέσα στο κείμενο". Η συνάρτηση ΑΝΑΖΗΤΗΣΗ επιστρέφει μια 2η σειρά τιμών TRUE και FALSE, 10 σειρές από 3 στήλες, ως εξής:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Για κάθε τιμή στα "δεδομένα", έχουμε 3 αποτελέσματα (ένα ανά συμβολοσειρά αναζήτησης) που είναι #VALUE σφάλματα ή αριθμοί. Οι αριθμοί αντιπροσωπεύουν τη θέση μιας συμβολοσειράς κειμένου που βρέθηκε και τα σφάλματα αντιπροσωπεύουν συμβολοσειρές κειμένου που δεν βρέθηκαν. Παρεμπιπτόντως, η συνάρτηση TRANSPOSE απαιτείται για τη δημιουργία της σειράς 10 x 3 πλήρων αποτελεσμάτων.
Αυτός ο πίνακας τροφοδοτείται στο ISNUMBER για να πάρει τιμές TRUE FALSE, τις οποίες μετατρέπουμε σε 1s και 0s με έναν διπλό αρνητικό (-) τελεστή. Το αποτέλεσμα είναι ένας πίνακας ως εξής:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
που πηγαίνει στη λειτουργία MMULT ως πίνακας1. Ακολουθώντας τους κανόνες του πολλαπλασιασμού της μήτρας, ο αριθμός των στηλών στον πίνακα 1 πρέπει να ισούται με τον αριθμό των γραμμών στον πίνακα2. Για τη δημιουργία array2 , χρησιμοποιούμε τη συνάρτηση ROW ως εξής:
ROW(exclude)^0
Αυτό αποδίδει έναν πίνακα 1s, 3 σειρών ανά 1 στήλη:
(1;1;1)
η οποία πηγαίνει σε MMULT ως πίνακας2 . Μετά τον πολλαπλασιασμό πίνακα, έχουμε έναν πίνακα που έχει διαστάσεις ώστε να ταιριάζει με τα αρχικά δεδομένα:
(2;1;0;0;1;1;0;0;0;2)
Σε αυτόν τον πίνακα, οποιοσδήποτε μη μηδενικός αριθμός αντιπροσωπεύει μια τιμή όπου έχει βρεθεί τουλάχιστον μία από τις εξαιρούμενες συμβολοσειρές. Τα μηδενικά δείχνουν ότι δεν βρέθηκαν εξαιρούμενες συμβολοσειρές. Για να επιβάλουμε όλες τις μη μηδενικές τιμές στο 1, χρησιμοποιούμε περισσότερες από μηδέν:
(2;1;0;0;1;1;0;0;0;2)>0
η οποία δημιουργεί ακόμη έναν άλλο πίνακα ή τιμές ΑΛΗΘΗΣ και ΛΑΘΟΣ:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Ο τελικός μας στόχος είναι να μετράμε μόνο τιμές κειμένου όπου δεν βρέθηκαν εξαιρούμενες συμβολοσειρές, επομένως πρέπει να αντιστρέψουμε αυτές τις τιμές. Αυτό το κάνουμε αφαιρώντας τον πίνακα από το 1. Αυτό είναι ένα παράδειγμα λογικής boolean. Η μαθηματική λειτουργία εξαναγκάζει αυτόματα TRUE και FALSE τιμές σε 1s και 0s, και τελικά έχουμε έναν πίνακα για να επιστρέψουμε στη συνάρτηση SUM:
=SUM((0;0;1;1;0;0;1;1;1;0))
Η συνάρτηση SUM επιστρέφει ένα τελικό αποτέλεσμα 5.
