Steve a une feuille de calcul qui contient plus de dix mille lignes, chaque cellule de la colonne A contenant un nom de fichier. Ces noms doivent suivre deux règles et Steve doit découvrir quels noms violent l’une ou l’autre des règles. Si un nom de fichier contient un tiret, il doit également avoir un seul espace avant et après le tiret. La deuxième règle est que si le nom contient une virgule, il ne doit y avoir aucun espace avant mais un seul espace après.

Steve se demande comment il peut mettre en évidence les cellules qui violent l’un ou l’autre (ou les deux)

de ces règles.

Chaque fois que quelqu’un mentionne qu’il veut «mettre en évidence» quelque chose dans une feuille de calcul, la plupart des gens pensent à utiliser la mise en forme conditionnelle. Cette instance ne fait pas exception; vous pouvez facilement utiliser la mise en forme conditionnelle pour mettre en évidence les violations de modèle. La clé pour développer la règle de mise en forme conditionnelle est de proposer une formule qui renvoie True si le modèle est violé. Cette formule vérifie les deux violations:

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

J’ai divisé la formule en trois lignes ici, mais cela devrait être considéré comme une formule complète. La formule supprime les motifs corrects (espace, tiret, espace et virgule, espace) du nom de fichier, puis vérifie si un tiret ou une virgule reste dans le nom de fichier. S’il en reste un, la formule renvoie Vrai.

Vous pouvez configurer une règle de mise en forme conditionnelle pour utiliser la formule de cette manière:

  1. Sélectionnez les cellules contenant tous les noms de fichiers que vous souhaitez cocher.

  2. Avec l’onglet Accueil du ruban affiché, cliquez sur l’option Mise en forme conditionnelle dans le groupe Styles. Excel affiche une palette d’options liées à la mise en forme conditionnelle.

  3. Choisissez Mettre en évidence les règles des cellules, puis choisissez Plus de règles dans le sous-menu résultant. Excel affiche la boîte de dialogue Nouvelle règle de mise en forme.

(Voir la figure 1.)

  1. Dans la zone Sélectionner un type de règle en haut de la boîte de dialogue, choisissez Utiliser une formule pour déterminer les cellules à mettre en forme.

  2. Dans la zone Valeurs de format où cette formule est vraie, entrez la longue formule déjà décrite.

  3. Cliquez sur Format pour afficher la boîte de dialogue Format des cellules.

  4. À l’aide des commandes de la boîte de dialogue, spécifiez un format que vous souhaitez utiliser pour mettre en évidence les cellules qui ne respectent pas votre modèle.

  5. Cliquez sur OK pour fermer la boîte de dialogue Format de cellule. La mise en forme que vous avez spécifiée à l’étape 7 doit maintenant apparaître dans la zone d’aperçu de la règle.

  6. Cliquez sur OK.

Si les cellules que vous avez sélectionnées à l’étape 1 ne commencent pas par la cellule A1, vous devrez modifier la formule utilisée à l’étape 5 pour refléter votre cellule de départ. (Les trois instances de A1 dans la formule devraient être modifiées pour référencer votre cellule de début.)

Il y a deux grands «pièges» à utiliser cette formule dans votre règle de mise en forme conditionnelle. Premièrement, il ne détecte pas les doubles espaces. Ainsi, par exemple, si le nom de fichier contenait «espace, espace, tiret, espace», ce serait une violation du modèle. Cependant, la fonction SUBSTITUTE dans la formule supprimerait le «espace, tiret, espace», laissant l’espace supplémentaire dans la chaîne résultante. Cet espace unique ne serait pas détecté comme une violation du modèle, même s’il l’est.

La solution à cela serait une formule beaucoup plus longue ou le contournement total de la route de mise en forme conditionnelle et de commencer à utiliser des colonnes d’assistance. Cela alimente directement le deuxième « gotcha », et c’est un gros:

Si vous appliquez une mise en forme conditionnelle (ou ajoutez des colonnes d’aide contenant des formules) à dix mille lignes, vous remarquerez une augmentation marquée du temps nécessaire pour recalculer votre feuille de calcul. Il n’y a aucun moyen de contourner cela lorsque vous commencez à ajouter autant de formules à la feuille de calcul.

Pour cette raison, vous trouverez peut-être plus approprié de développer une macro qui met en évidence les cellules. La macro peut ensuite être exécutée manuellement lorsque vous souhaitez vérifier les modèles, ce qui signifie que le recalcul normal de votre feuille de calcul n’est pas ralenti.

La macro suivante est conçue pour être exécutée sur une plage de cellules sélectionnée.

Il vérifie qu’il n’y a pas deux espaces avant un tiret, deux espaces après un tiret, un espace avant une virgule ou deux espaces après une virgule. Il supprime ensuite tous les tirets et virgules correctement entrelacés du nom de fichier et vérifie s’il reste des tirets ou des virgules. Si une violation de l’une de ces conditions est notée, la cellule est mise en forme avec du jaune.

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

La macro peut prendre un certain temps à s’exécuter mais, encore une fois, elle ne doit être exécutée que lorsque vous souhaitez vérifier les noms de champs. Si vous ne voulez pas que la macro «gâche» la mise en forme de la cellule, alors vous pouvez vouloir une version qui insère du texte dans la colonne à droite de tout nom de fichier qui ne respecte pas le modèle souhaité.

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

Lorsqu’elle est exécutée, cette variante de la macro insère le texte «MAUVAIS» dans la cellule à droite des noms de fichiers incorrectement patterend. Vous pouvez ensuite utiliser les capacités de filtrage d’Excel pour afficher uniquement les lignes contenant le texte.

Bien sûr, vous voudrez peut-être aller plus loin et autoriser la macro à modifier les noms de fichiers mal formatés. La macro suivante fait son travail sur les cellules que vous avez sélectionnées. Il garantit que chaque tiret est entouré d’un seul espace et que chaque virgule n’est suivie que d’un seul espace.

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 est votre source pour une formation Microsoft Excel rentable.

Cette astuce (3015) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.