Formula generica
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Sommario
Per utilizzare la convalida dei dati per consentire solo un elenco di caratteri specifici, è possibile utilizzare una formula di matrice piuttosto complicata basata sulle funzioni COUNT, MATCH e LEN. Nell'esempio mostrato, la convalida dei dati viene applicata con questa formula:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
dove "consentito" è l'intervallo denominato D5: D11.
Spiegazione
Lavorando dall'interno verso l'esterno, la funzione MID viene utilizzata per generare un array dal testo inserito in B5 con questo frammento:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
spiegato in dettaglio qui. Il risultato è un array come questo:
("A";"A";"A";"-";"1";"1";"1")
che va in MATCH come valore di ricerca. Per l'array di ricerca, utilizziamo l'intervallo denominato "consentito", concatenato a una stringa vuota (""):
allowed&""
La concatenazione converte qualsiasi numero in stringhe, in modo da far corrispondere mele a mele. Il risultato è un array come questo:
("A";"B";"C";"1";"2";"3";"-")
L'ultimo argomento in MATCH, match_type è impostato su zero per forzare una corrispondenza esatta. Poiché forniamo a MATCH più valori di ricerca, otteniamo un array con più risultati:
(1;1;1;7;4;4;4)
Ogni numero in questa matrice rappresenta una corrispondenza. Nel caso in cui non venga trovata una corrispondenza per un carattere, l'array conterrà un errore # N / D.
Infine, la funzione COUNT viene utilizzata per contare i numeri nella matrice dei risultati, che viene confrontata con un conteggio di tutti i caratteri nella cella calcolati con la funzione LEN. Quando CONFRONTA trova una corrispondenza per tutti i caratteri, i conteggi sono uguali, la formula restituisce VERO e la convalida dei dati ha esito positivo. Se CONFRONTA non trova una corrispondenza con alcun carattere, restituisce # N / D invece di un numero. In tal caso, i conteggi non corrispondono e la convalida dei dati non riesce.
Nota: questa formula si basa sulla forza bruta per portare a termine il lavoro. Se hai un approccio migliore, lascia un commento qui sotto.
