dimanche 6 mai 2012

Comparer facilement deux colonnes.

Vérifier que deux ensembles de cellules sont strictement identiques : construisons une petite macro pour faire ça en un clin d’oeil !


Contextes d’utilisation

Imaginons qu’on a sous les yeux deux listes censées être identiques. Il semble d’ailleurs qu’elle le soient. Pourtant, à y regarder de plus près... Exemple avec les 22 régions de France métropolitaine :



Variante sur une grande échelle : dans un tableau contenant des milliers de lignes, on a deux colonnes redondantes l’une avec l’autre. Leurs étiquettes sont similaires voire identiques et les valeurs portées dans les lignes visibles à l’écran sont les mêmes deux par deux. On veut donc supprimer l’une des deux. Mais est-on vraiment certain que toutes les valeurs sont identiques ? Sur toute la hauteur de la table ?

Autre situation : on souhaite regrouper deux tables de données provenant de deux classeurs différents. Concrètement : on veut copier l’une (sans ses étiquettes de colonne) en-dessous de l’autre. Les deux ont le même colonnage. Sauf que : il arrive qu’on ne soit pas absolument certain que les deux tables comportent réellement le même colonnage. L’un des classeurs a pu être retravaillé par quelqu’un qui y aura ajouté une colonne. Ou supprimé une colonne. Ou interverti deux colonnes. C’est donc prudent, avant de regrouper les deux tables, de vérifier que les colonnes sont bien les mêmes !
 
Exemple d’une liste de villes avec leur département et leur région :



La problématique de vérification est similaire et rentre dans le cas de ce qu’on veut faire ici, à ceci près qu’il faudra d’abord copier les lignes d’étiquettes des deux tables et les coller l’une sous l’autre dans un nouveau classeur par exemple, puis transposer le bloc de données constitué.

On pourrait imaginer d’autres situations encore. L’idée reste celle-ci : vérifier que deux séries de cellules sont bien identiques.

La vérification classique

Comment est-ce qu’on fait ça à la main ? Imaginons qu’on a deux valeurs dans les cellules A1 et B1 (texte, ou nombre, ou autre chose, peut importe). Pour en faire la comparaison, on entre souvent dans C1 la formule suivante :
=SI(A1=B1;0;1)
Cette formule a pour résultat la valeur 0 si les deux cellules à comparer sont identiques, ou la valeur 1 dans le cas contraire. Faire cela a un intérêt si on veut compter le nombre de cas où les valeurs situées dans les colonnes A et B sont différentes : on recopie la formule de la cellule C1 jusqu’au bas de la table, et la somme des valeurs de la colonne C sera le nombre de ces cas.

Mais dans les situations du type de celles décrites plus haut, on veut juste une confirmation disant “oui, tout est ok” ou bien une alerte disant “attention, il y a des écarts”.

La comparaison peut donc se faire plus simplement avec la formule suivante (toujours dans la cellule C1) :
=A1=B1
Cette formule a pour résultat VRAI ou FAUX.
Si l’on recopie la formule de C1 jusqu’au bas de la table, le filtre automatique permettra de voir très vite s’il n’y a que des valeurs VRAI ou bien s’il y a au moins une valeur FAUX.

Cela étant, tout ça prend malgré tout le temps... de le faire! Voyons donc comment gagner ce temps avec :


Une macro en VBA

L’idée est de ne pas se servir d’une troisième colonne (C) pour y faire des tests, mais d’effectuer plutôt les comparaisons dans la macro et d’afficher le résultat final dans une boîte de message.

Pour faire les tests, on va parcourir la plage des cellules à comparer deux à deux. Pas même la plage entière, en fait : juste la première colonne de cette plage, ça suffit, et on comparera chaque cellule de cette première colonne à la cellule qui se trouve dans la seconde colonne, juste à côté.

Allons-y !

En termes de variables, on a besoin de deux objets Range :
- l’un pour désigner la plage des cellules,
- l’autre pour désigner une cellule qui nous servira à parcourir une à une les cellules de la première colonne de la plage.
Concrètement : Dim Plg As Range, c As Range

Pour l’instant, supposons que les deux colonnes à comparer sont sélectionnées a priori par l’utilisateur. (On verra plus loin comment faire quelque chose de souple et sympa pour repérer ces deux colonnes.).
Autrement dit : Set Plg = Selection

Parcourir les cellules de la première colonne de la plage, on va le faire comme ça :
For Each c In Plg.Columns(1).Cells
' instructions
Next

Et à l’intérieur de chaque passage de ce parcours, c’est à dire pour chaque cellule, on va comparer la cellule à sa voisine de droite, pour tester si ces deux cellules sont différentes. Cette voisine, on la localise avec la propriété Offset, qui dit ici “0 ligne vers le bas & 1 colonne vers la droite”. Comme ceci :
If c <> c.Offset(0, 1) Then
' instructions
End If

Imbriquons ces deux choses :
For Each c In Plg.Columns(1).Cells        ' parcours
If c <> c.Offset(0, 1) Then              ' test
  ' instructions
End If
Next

Voilà qui constitue le socle de la macro !
Et maintenant, qu’est-ce qu’on met dans ces instructions ?


L’esprit de la macro : pointer les écarts

Comme on va effectuer cette vérification entre des colonnes qui se ressemblent a priori beaucoup, on veut juste savoir s’il y a au moins une différence et, le cas échéant, la (les) localiser facilement. Le socle parcours & test qu’on vient de monter nous permet de repérer les cas où il y a des différences. Ces différences, on va en faire deux choses :
1. les colorer, pour qu’on les voie bien ;
2. les compter, pour pouvoir dire ensuite combien il y en a.

Pour colorer le cellule c, on travaille sur la propriété Color de la propriété Interior, en y appliquant par exemple la valeur de la constante VBA qui définit le jaune : vbYellow.
Comme ceci : c.Interior.Color = vbYellow

Et on va faire la même chose sur la cellule voisine :
c.Offset(0, 1).Interior.Color = vbYellow

En fait, on peut faire les deux d’un seul coup, en réunissant les deux cellules par la méthode Union :
Union(c, c.Offset(0, 1)).Interior.Color = vbYellow

Par ailleurs, pour compter le nombre de fois que se produit le cas cellules différentes, on utilise un compteur : un nombre entier qu’on aura préalablement dimensionné (Dim i As Long) et qu’on incrémentera à chaque fois que le cas de produit (i = i + 1).


Assemblons tout cela pour avoir un première mouture de la macro :

Sub VérificationParitéBinômes_PremièreMouture()

Dim Plg As Range, c As Range
Dim i As Long

' définition de la plage de cellules
Set Plg = Selection

' coeur de la macro
For Each c In Plg.Columns(1).Cells                 ' parcours
If c <> c.Offset(0, 1) Then                           ' test
  Union(c, c.Offset(0, 1)).Interior.Color = vbYellow  ' coloriage
  i = i + 1                                           ' comptage
End If
Next

End Sub


Cette première version fonctionnerait déjà telle quelle. Mais on a posé quelques éléments pour aller plus loin sur deux points. Les deux sont des éléments de confort pour l’utilisateur :
- la sélection de la plage au début,
- l’information à l’utilisateur, à la fin.



La sélection de la plage, au début

Prendre comme plage de données à comparer celle qui est sélectionnée supposerait que l’utilisateur sache à l’avance ce qui va se passer et se souvienne qu’il lui faut d’abord sélectionner la bonne plage de cellules avant d’exécuter la macro. C’est plus élégant de lui demander par la macro de sélectionner la bonne plage, via une fenêtre comme ceci :

Pour ça, on utilise la méthode InputBox de l’objet Application, en lui spécifiant comme argument Type la valeur 8 : celle-ci demande impérativement que la saisie corresponde à une référence de cellule.
Au lieu de la ligne Set Plg = Selection qu’on a posée plus haut, on va plutôt dire ceci :
Set Plg = Application.InputBox("Sélectionnez la plage.", Type:=8)

On pourrait pousser la prudence jusqu’à vérifier que l’utilisateur a bien sélectionné une plage de deux colonnes. Cela dit, s’il en sélectionne trois ou plus ce n’est pas bloquant : la vérification va s’opérer sur les deux premières, tout simplement. Et s’il n’en sélectionne qu’une, la vérification sera faite entre les valeurs de cette colonne et de sa voisine de droite. L’idée de ce accompagnement est uniquement de faciliter la sélection par l’utilisateur !

L’information à l’utilisateur après le test

Le compteur i va être la clé de l’histoire : si toutes les cellules sont égales deux par deux, alors les instructions du coeur de macro n’ont jamais été effectuées et donc le compteur i vaut toujours 0 à la fin (c’est la valeur qu’il prend par défaut lorsqu’on déclare la variable). Il suffit alors de tester la valeur de i :
- si elle est égale à 0 alors il faut annoncer à l’utilisateur que toutes les valeurs sont égales deux par deux ;
- sinon il faut lui annoncer qu’il y a une différence entre les deux colonnes sur un certain nombre de lignes, et ce certain nombre de lignes vaut i.

Pour afficher le message, on pourrait utiliser deux fois la fonction MsgBox (une fois dans chaque cas). Mais je trouve plus propre d’utiser la distinction entre ces deux cas pour construire des messages différenciés, puis de n’utiliser MsgBox qu’une seule fois, pour afficher le message.

Concrètement, après avoir déclaré une variable pour contenir le texte de ce message (Dim Msg As String), on fait deux choses.

1. On prépare le message :
If i = 0 Then
    Msg = "Toutes les valeurs sont OK deux par deux."
Else
    Msg = "Il y a des valeurs différentes sur " & i & " ligne(s) !"
End If

2. On utilise la fonction MsgBox pour afficher une fenêtre contenant d’une part ce message, d’autre part la constante VBA vbInformation qui ajoute à la boîte l’icone d’information (la lettre “i” dans une bulle).
Z = MsgBox(Msg, vbInformation)

Ce qui donne respectivement ceci :

ou cela :


Et voici l’ensemble pour finir :

Sub VérificationParitéBinômes()
Dim Plg As Range, c As Range
Dim i As Long, Msg As String

' définition de la plage de cellules
Set Plg = Application.InputBox("Sélectionnez la plage.", Type:=8)

' coeur de la macro
For Each c In Plg.Columns(1).Cells                    ' parcours
If c <> c.Offset(0, 1) Then                                ' test
  Union(c, c.Offset(0, 1)).Interior.Color = vbYellow      ' coloriage
  i = i + 1                                               ' comptage
End If
Next

' préparation du message
If i = 0 Then
    Msg = "Toutes les valeurs sont OK deux par deux."
Else
    Msg = "Il y a des valeurs différentes sur " & i & " lignes !"
End If

' affichage
Z = MsgBox(Msg, vbInformation)

End Sub

4 commentaires:

  1. Bonjour
    Novice dans les macro je trouve ton code super, merci aux personnes comme toi qui prennent le temps de mettre en ligne
    Merci beaucoup
    Jo de Nouvelle Calédonie

    RépondreSupprimer
  2. Ce commentaire a été supprimé par l'auteur.

    RépondreSupprimer
  3. bonjours j'ai créé un outils de planification mon problème est qu'il faudrait que je compare 2 colones une avec des nom de l'effectifs et une qui a les même nom mais dynamique (en fonction des présences) il faudrait que j'arrive a faire apparaître les personnes non présente avec le motif indiqué dans une cellule

    RépondreSupprimer