Steve 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 Steve deve scoprire quali nomi violano una delle regole. Se il nome di un file contiene un trattino, deve anche contenere un solo spazio prima e dopo il trattino. La seconda regola è che se il nome contiene una virgola, non deve esserci spazio prima ma un singolo spazio dopo di essa.

Steve si chiede come può evidenziare le celle che violano uno (o entrambi)

di 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 usare la formattazione condizionale per evidenziare le violazioni del pattern. La chiave per sviluppare la regola di formattazione condizionale è trovare una formula che restituisca True se il pattern viene violato. Questa formula controlla entrambe le violazioni:

=OR(ISNUMBER(FIND("-",SUBSTITUTE(A1," - ",""))), ISNUMBER(FIND(",",SUBSTITUTE(A1,", ",""))), ISNUMBER(FIND(" ,",A1)))

Ho suddiviso la formula in tre righe qui, ma dovrebbe essere considerata una formula completa. La formula rimuove i modelli corretti (spazio, trattino, spazio e virgola, spazio) dal nome del file, quindi controlla se nel nome del file rimane un trattino o una virgola. Se ne rimane uno, la formula restituisce True.

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 desideri controllare.

  2. Con la scheda Home della barra multifunzione visualizzata, fare clic sull’opzione Formattazione condizionale nel gruppo Stili. Excel visualizza una tavolozza di opzioni relative alla formattazione condizionale.

  3. Scegli Evidenzia regole celle, quindi scegli Altre regole dal sottomenu risultante. Excel visualizza la finestra di dialogo Nuova regola di formattazione.

(Vedi figura 1.)

  1. Nell’area Seleziona un tipo di regola nella parte superiore della finestra di dialogo, scegli Usa una formula per determinare quali celle formattare.

  2. Nella casella Formato valori dove questa formula è vera, immettere la formula lunga già discussa.

  3. Fare clic su Formato per visualizzare la finestra di dialogo Formato celle.

  4. Utilizzando i controlli nella finestra di dialogo, specificare un formato che si desidera utilizzare per evidenziare le celle che violano il modello.

  5. Fare clic su OK per chiudere la finestra di dialogo Formato celle. La formattazione specificata nel passaggio 7 dovrebbe ora apparire nell’area di anteprima per la regola.

  6. Fare clic su OK.

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

Ci sono due grandi “trucchi” nell’utilizzo di questa formula nella regola di formattazione condizionale. Innanzitutto, non rileva i doppi spazi. 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 aggiuntivo 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 bypassando del tutto il percorso di formattazione condizionale e iniziando a utilizzare le colonne helper. Questo alimenta il secondo “gotcha”, ed è grande:

Se applichi la formattazione condizionale (o aggiungi colonne helper contenenti formule) a diecimila righe, noterai un marcato 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 i modelli, 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 tutti i trattini e le virgole correttamente modellati dal nome del file e controlla se rimangono trattini o virgole. Se viene rilevata una violazione di una di queste condizioni, la cella viene formattata in giallo.

Sub CheckFilenames1()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If Instr(sTemp1, "  -") > 0 Then bBad = True         If Instr(sTemp1, "-  ") > 0 Then bBad = True         If Instr(sTemp1, " ,") > 0 Then bBad = True         If Instr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If Instr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If Instr(sTemp2, ",") > 0 Then bBad = True

If bBad Then             c.Interior.Color = vbYellow         Else             c.Interior.Color = xlColorIndexNone         End If     Next c End Sub

L’esecuzione della macro potrebbe richiedere del tempo ma, di nuovo, deve essere eseguita solo quando si desidera controllare i nomi dei file. Se non vuoi che la macro “rovini” la formattazione della cella, allora potresti volere una versione che inserisca del testo nella colonna a destra di tutti i nomi di file che violano il pattern desiderato.

Sub CheckFilenames2()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If InStr(sTemp1, "  -") > 0 Then bBad = True         If InStr(sTemp1, "-  ") > 0 Then bBad = True         If InStr(sTemp1, " ,") > 0 Then bBad = True         If InStr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If InStr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If InStr(sTemp2, ",") > 0 Then bBad = True

If bBad Then c.Offset(0, 1) = "BAD"

Next c End Sub

Quando viene eseguita, questa variazione della macro inserisce il testo “BAD” nella cella a destra dei nomi di file impaginati in modo non corretto. È quindi possibile utilizzare le funzionalità di filtro di Excel per visualizzare solo le righe che contengono il testo.

Ovviamente, potresti voler fare un ulteriore passo avanti e consentire alla macro di modificare i nomi di file formattati in modo errato. La seguente macro funziona su qualsiasi cella che hai selezionato. Assicura che ogni trattino sia circondato da un singolo spazio e che ogni virgola sia seguita solo da un singolo spazio.

Sub FixFilenames()

Dim myArry() As String     Dim sTemp As String     Dim c As Range     Dim s As Variant

For Each c In Selection         myArry = Split(c, "-")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & " - " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         myArry = Split(sTemp, ",")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & ", " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         c = sTemp     Next c End Sub

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (3015) si applica a Microsoft Excel 2007, 2010, 2013 e 2016.