Evidenziare le violazioni da uno schema prefissato

Stefano ha un foglio di lavoro che contiene oltre diecimila righe, con ogni cella nella colonna A contenente un nome di file. Questi nomi devono seguire due regole e Stefano deve scoprire quali nomi violano tali regole.

1. Se un nome file contiene un trattino, deve anche avere un singolo spazio prima e dopo il trattino
2. se il nome contiene una virgola, non deve esserci spazio prima di essa ma un singolo spazio dopo di essa.

Steve si chiede come può evidenziare le celle che violano una (o entrambe) queste regole.

Ogni volta che qualcuno afferma di voler “evidenziare” qualcosa in un foglio di lavoro, la maggior parte delle persone pensa di utilizzare la formattazione condizionale. Questa istanza non fa eccezione. Potresti facilmente utilizzare la formattazione condizionale per evidenziare le violazioni allo schema. La chiave per sviluppare la regola di formattazione condizionale è trovare una formula che restituisca VERO se il modello viene violato. Questa formula verifica entrambe le violazioni:

=O(VAL.NUMERO(TROVA("-";SOSTITUISCI(A1;" - ";"")));VAL.NUMERO(TROVA(",";SOSTITUISCI(A1;", ";"")));VAL.NUMERO(TROVA(" ,";A1)))

La formula rimuove gli schemi corretti (spazio, trattino, spazio e virgola, spazio) dal nome del file, quindi verifica se nel nome del file rimane un trattino o una virgola. Se ne rimane uno, la formula restituisce VERO.

Puoi impostare una regola di formattazione condizionale per utilizzare la formula in questo modo:

1. Seleziona le celle che contengono tutti i nomi di file che vuoi controllare
2. Con la scheda Home della barra multifunzione visualizzata, faI clic sull’opzione Formattazione condizionale nel gruppo Stili. Excel visualizza una tavolozza di opzioni relative alla formattazione condizionale
3. Scegli Regole evidenziazione celle e quindi scegli Altre regole dal sottomenu risultante. Excel visualizza la finestra di dialogo Nuova regola di formattazione
4. Nell’area Selezionare un tipo di regola nella parte superiore della finestra di dialogo, scegli Utilizza una formula per determinare le celle da formattare
5. Nella casella Formatta i valori per cui questa formula restituisce Vero, inserisci la formula qua sopra
6. Fare clic su Formato per visualizzare la finestra di dialogo Formato celle
7. Utilizzando i controlli nella finestra di dialogo, specifica un formato che vuoi utilizzare per evidenziare le celle che violano lo schema
8. Fai clic su OK per chiudere la finestra di dialogo Formato celle. La formattazione specificata dovrebbe ora essere visualizzata nell’area di anteprima della regola
9. Fai clic su OK

Se le celle selezionate nel passaggio 1 non iniziano con la cella A1, dovrai modificare la formula utilizzata nel passaggio 5 per riflettere la tua cella iniziale (tutte e tre le istanze di A1 nella formula dovrebbero essere modificate per fare riferimento alla cella iniziale).

Ci sono due grandi “difetti” nell’usare questa formula nella tua regola di formattazione condizionale. Innanzitutto, non rileva gli spazi doppi. Quindi, per esempio, se il nome del file contenesse “spazio, spazio, trattino, spazio”, sarebbe una violazione del modello. Tuttavia, la funzione SOSTITUISCI nella formula rimuoverà lo “spazio, trattino, spazio”, lasciando lo spazio extra nella stringa risultante. Questo singolo spazio non verrebbe rilevato come una violazione del modello, anche se lo è.

La soluzione a questo sarebbe una formula molto più lunga o ignorare del tutto la strada della formattazione condizionale e iniziare a utilizzare le colonne di supporto. Questo porta direttamente al secondo “difetto”, ed è grande: se si applica la formattazione condizionale (o si aggiungono colonne di supporto contenenti formule) a diecimila righe, si noterà un notevole aumento del tempo necessario per ricalcolare il foglio di lavoro . Non c’è modo di aggirare questo quando inizi ad aggiungere così tante formule al foglio di lavoro.

Per questo motivo, potresti trovare più appropriato sviluppare una macro che evidenzi le celle. La macro potrebbe quindi essere eseguita manualmente quando si desidera controllare gli schemi, il che significa che il normale ricalcolo del foglio di lavoro non viene rallentato.

La seguente macro è progettata per essere eseguita su un intervallo selezionato di celle. Verifica che non ci siano due spazi prima di un trattino, due spazi dopo un trattino, uno spazio prima di una virgola o due spazi dopo una virgola. Quindi rimuove i trattini e le virgole correttamente aderenti allo schema dal nome del file e controlla se rimangono trattini o virgole. Se si nota una violazione di una di queste condizioni, la cella viene formattata in giallo.