Γενική φόρμουλα
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Περίληψη
Για να χρησιμοποιήσετε την επικύρωση δεδομένων για να επιτρέψετε μόνο μια λίστα συγκεκριμένων χαρακτήρων, μπορείτε να χρησιμοποιήσετε έναν μάλλον περίπλοκο τύπο πίνακα βάσει των συναρτήσεων COUNT, MATCH και LEN. Στο παράδειγμα που εμφανίζεται, η επικύρωση δεδομένων εφαρμόζεται με αυτόν τον τύπο:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
όπου "επιτρέπεται" είναι η ονομαζόμενη περιοχή D5: D11.
Εξήγηση
Δουλεύοντας από μέσα προς τα έξω, η συνάρτηση MID χρησιμοποιείται για τη δημιουργία ενός πίνακα από κείμενο που έχει εισαχθεί στο B5 με αυτό το απόσπασμα:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
εξήγησε λεπτομερώς εδώ. Το αποτέλεσμα είναι ένας πίνακας ως εξής:
("A";"A";"A";"-";"1";"1";"1")
που πηγαίνει στο MATCH ως τιμή αναζήτησης. Για πίνακα αναζήτησης, χρησιμοποιούμε την ονομαστική περιοχή "επιτρεπόμενη", σε συνδυασμό με μια κενή συμβολοσειρά (""):
allowed&""
Η συνένωση μετατρέπει τους αριθμούς σε συμβολοσειρές, έτσι ώστε να ταιριάζουμε με μήλα σε μήλα. Το αποτέλεσμα είναι ένας πίνακας ως εξής:
("A";"B";"C";"1";"2";"3";"-")
Το τελευταίο όρισμα στο MATCH, match_type ορίζεται στο μηδέν για να επιβάλει την ακριβή αντιστοίχιση. Επειδή δίνουμε MATCH πολλαπλές τιμές αναζήτησης, παίρνουμε πίσω έναν πίνακα με πολλαπλά αποτελέσματα:
(1;1;1;7;4;4;4)
Κάθε αριθμός σε αυτόν τον πίνακα αντιπροσωπεύει έναν αγώνα. Σε περίπτωση που δεν βρεθεί αντιστοίχιση για έναν χαρακτήρα, ο πίνακας θα περιέχει σφάλμα # N / A.
Τέλος, η συνάρτηση COUNT χρησιμοποιείται μετράει τους αριθμούς στο πίνακα αποτελεσμάτων, ο οποίος συγκρίνεται με έναν αριθμό όλων των χαρακτήρων στο κελί που υπολογίζονται με τη συνάρτηση LEN. Όταν το MATCH βρίσκει μια αντιστοίχιση για όλους τους χαρακτήρες, οι μετρήσεις είναι ίσες, ο τύπος επιστρέφει ΑΛΗΘΕΙΑ και η επικύρωση δεδομένων πετυχαίνει. Εάν το MATCH δεν βρει κανένα ταίριασμα χαρακτήρα, επιστρέφει # N / A αντί για έναν αριθμό. Σε αυτήν την περίπτωση, οι μετρήσεις δεν ταιριάζουν και η επικύρωση δεδομένων αποτυγχάνει.
Σημείωση: αυτός ο τύπος βασίζεται στην ωμή δύναμη για να ολοκληρώσει τη δουλειά. Εάν έχετε μια καλύτερη προσέγγιση, αφήστε ένα σχόλιο παρακάτω.
