Τύπος Excel: Το κελί περιέχει ένα από τα πολλά με εξαιρέσεις -

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

=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)

Περίληψη

Για να δοκιμάσετε ένα κελί για μία από τις πολλές συμβολοσειρές, ενώ αποκλείετε άλλες, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στις συναρτήσεις SEARCH, ISNUMBER και SUMPRODUCT. Στο παράδειγμα που παρουσιάζεται ο τύπος στο C5 είναι:

=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)

όπου "include" είναι η ονομαστική περιοχή E5: E9 και "exclude" είναι η ονομαζόμενη περιοχή G5: G6.

Εξήγηση

Στον πυρήνα, αυτός ο τύπος χρησιμοποιεί τη συνάρτηση SEARCH για να αναζητήσει πολλές συμβολοσειρές μέσα σε ένα κελί. Μέσα στο αριστερό ΣΥΝΟΠΤΙΚΟ ΠΡΟΪΟΝ, η ΑΝΑΖΗΤΗΣΗ αναζητά όλες τις χορδές στην ονομαζόμενη περιοχή "include"

Στο σωστό ΣΥΝΟΠΤΙΚΟ ΠΡΟΪΟΝ, η ΑΝΑΖΗΤΗΣΗ αναζητά όλες τις συμβολοσειρές στην ονομαζόμενη περιοχή "εξαίρεση".

Και στα δύο μέρη του τύπου, το SEARCH επιστρέφει αριθμητικές θέσεις όταν εντοπίζονται συμβολοσειρές και σφάλματα όταν όχι. Η συνάρτηση ISNUMBER μετατρέπει τους αριθμούς σε TRUE και τα σφάλματα σε FALSE και το διπλό αρνητικό μετατρέπει τις τιμές TRUE FALSE σε 1 και 0.

Το αποτέλεσμα σε αυτό το σημείο μοιάζει με αυτό:

=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)

Τότε:

=(1>0)*(0=0) =TRUE*TRUE =1

Σημείωση: αυτός ο τύπος επιστρέφει είτε 1 είτε μηδέν, οι οποίοι αντιμετωπίζονται όπως TRUE και FALSE σε τύπους, μορφοποίηση υπό όρους ή επικύρωση δεδομένων.