dimanche 27 novembre 2011

Numéroter les lignes d’un bloc de données



Quand on travaille sur une table de données dans une feuille Excel, on a parfois besoin de manipuler les lignes : les retoucher, traiter successivement des cas de figure différents, ces cas étant situés dans des lignes éparpillées sur toute la hauteur de la feuille, modifier une donnée sur telle colonne lorsque telle autre colonne contient une valeur particulière, etc.


Le filtre automatique est une aide évidente pour ça : grâce à lui, on isole des blocs de la table et on les traite l’un après l’autre. Mais il peut ralentir notablement les manipulations, surtout lorsqu’on “défiltre” pour afficher toutes les lignes. Par exemple, s’il y a des formules et si la table est vraiment grande, cette manip-là lance le recalcul de la feuille, ce qui peut prendre beaucoup de temps. Certes, on peut désactiver le calcul automatique, mais pour peu que, à l’étape suivante, on ait besoin que les formules soient recalculées, on est obligé d’y perdre ce temps malgré tout.

Une idée consiste à effectuer un tri selon la bonne colonne : les lignes sont alors regroupées selon les valeurs de cette colonne-là et on peut aisément identifier des blocs de données à travailler. Le retraitement en est facilité et on évite les filtres.

Tout cela étant dit, on veut parfois que, à la fin, les lignes soient à nouveau dans leur ordre initial - l’ordre dans lequel elles se trouvaient avant qu’on ne commence à manipuler la table.

C’est facile si les lignes sont initialement triées selon les valeurs d’une colonne donnée - des dates par exemple : un clic suffit à les remettre dans l’ordre. Et c’est à peine plus compliqué si les lignes doivent être triées selon plusieurs colonnes - une colonne de date et une colonne d’heure par exemple : quelques clics et c’est fait.

Mais il peut arriver que la table de données ne contienne aucune colonne dont les valeurs puissent servir de référence pour l’ordre des lignes. Par exemple, la table peut rassembler des lignes représentant chacune une facture identifée par un numéro, mais sans que l’ordre (numérique ou alphabétique) de ces numéros ne soit pertinent. Ou bien, autre exemple, on pourrait se référer à une colonne de dates mais plusieurs lignes comportent la même date et le travail à effectuer risque de mélanger ces lignes : on ne pourra pas redéfinir ensuite leur ordre initial.

Dans ce cas, l’astuce est de créer une colonne pour ça !
  1. Sur la gauche de la table par exemple, on ajoute une colonne dans laquelle on met simplement les nombres 1, 2, 3... jusqu’au bas de la table,
  2. on effectue ensuite les manip qu’ont doit faire sur les groupes de lignes devenus faciles à identifier sans utiliser de filtre,
  3. puis on se sert de cette colonne pour remettre les lignes dans l’ordre
  4. et on peut finalement la supprimer.


Penchons-nous sur le point 1 ci-dessus : ajouter une colonne puis y insérer des numéros. Certes c’est la clé de l’opération puisque c’est ce qui ouvre la voie au “vrai” travail qui suit. Pour autant, il s’agit d’une opération strictement technique : elle n’a en soi aucune valeur ajoutée ! Ce serait donc bien de la réaliser de la manière la plus rapide et la plus fiable possible, non ?


Comment procéder de manière classique

D’abord, on insère une colonne, par exemple sur la gauche de la table :
- à la souris, ça se fait par un clic droit sur l’en-tête de colonne, puis “Insertion” ;
- au clavier, c’est Ctrl Espace pour sélectionner la colonne, puis Ctrl+ pour insérer une colonne.

Ensuite, on numérote les lignes dans cette nouvelle colonne :
- soit on entre la valeur 1 dans la première cellule, la valeur 2 dans la deuxième, puis on sélectionne ces deux cellules et on double-clique sur le carré dans le coin inférieur droit de la sélection, pour qu’Excel incrémente cette numérotation jusqu’au bas de la colonne ;

- soit on entre la valeur 1 dans la première cellule, on saisit dans la deuxième une formule qui va incrémenter de ligne en ligne (par exemple, si on a entré la valeur 1 dans la cellule A1, la formule dans A2 sera =A1+1”) puis on recopie cette formule jusqu’au bas de la colonne.

Il ne faut pas oublier ensuite de copier-coller en valeur la plage des cellules contenant des formules, sinon ces numéros resteront systématiquement dans cet ordre, quel que soit le tri effectué sur la table !

(Pour le même résultat, on peut éventuellement utiliser la fonction LIGNE() sans argument, qui renverra dans chaque cellule le numéro de la ligne. Là aussi, il faut copier-coller en valeur pour que la numérotation reste “en dur” dans les cellules.)

Bref : à la souris c’est au minimum une douzaine de clics qu’il faut faire, et au clavier c’est une quantité équivalente de séquences de touches. Si on doit répéter souvent l’opération, par exemple dans le cadre de traitements réguliers, c’est fastidieux.


Voyons maintenant comment effectuer la même chose en VBA !

Pas à pas, cela consiste à :
1. identifier la plage de cellules occupée par la table,
2. insérer une colonne sur sa gauche,
3. numéroter les cellules de cette colonne, sur toute la hauteur de la table.

Allons-y en détail.

1. Pour identifier la plage de cellules : on va se servir de la propriété CurrentRegion. Cette propriété est souvent bien pratique : à partir d’une cellule lambda dans un tableau, elle renvoie la zone en cours.

La zone en cours est le plus grand ensemble rectangulaire de cellules non-vides contiguës qui sont situées “autour” de la cellule active. Pour le dire autrement, c’est le plus petit ensemble de cellules qui contient la cellule active et qui s’inscrit dans un cadre de lignes et de colonnes vides. Dans Excel (hors VBA), on peut matérialiser très facilement cette zone à partir de n’importe quelle cellule d’un tableau : il suffit d’appeler Edition / Atteindre / Cellules / Zone en cours - ou le raccourci clavier Ctrl *.


Revenons en VBA. Comme la cellule active est ActiveCell, la zone en cours “autour” de cette cellule active est tout simplement ActiveCell.CurrentRegion. Et comme on va utiliser cette plage-là un certain nombre de fois, c’est plus simple de définir une variable pour ça. Concrètement, on la déclare :
Dim Plg As Range
puis on la définit :
Set Plg = ActiveCell.CurrentRegion

2. Pour insérer une colonne, le raisonnement va être le suivant :
- dans la plage qu’on vient de définir, on se focalise sur la première colonne uniquement :
Plg.Columns(1)
(ceci pour insérer une colonne et une seule ; si on se basait sur une plage de X colonnes, l’étape suivante insérerait X nouvelles colonnes !)
- et on insère avec la méthode Insert, comme ceci :
Plg.Columns(1).EntireColumn.Insert
(On pourrait faire ça sans le EntireColumn : le résultat serait le même, sauf qu’on aurait un simple décalage du bloc de départ et  les largeurs de colonnes ne seraient pas respectées. Cet élément permet de conserver les bonnes largeurs sur les bonnes colonnes.)

3. Numéroter les cellules est le coeur de la macro !
On va parcourir les cellules de la plage située dans la colonne qu’on vient d’insérer, et dans chaque cellule on va entrer un nombre, en faisant augmenter ce nombre à chaque tour.

Pour ça, on va avoir besoin de :
- la plage de cellules située immédiatement à gauche de cette plage.
En français, c’est “le décalage sur 0 ligne et 1 colonne vers gauche (soit -1 colonne vers la droite) des cellules de la première colonne de la plage Plg”. Traduction en VBA :
Plg.Columns(1).Cells.Offset(0, -1)
- une variable “cellule” pour parcourir cette plage (Dim c as Range),
- et une variable “nombre entier” qui servira de compteur pour la numérotation (Dim i as Long).

Et maintenant passons à l’action :
- on initialise le compteur : i = 0
- on parcourt la plage comme ceci :
    For Each c In Plg.Columns(1).Cells.Offset(0, -1)
(... action pour chaque passage...)
    Next c
- et à chaque passage, c’est à dire pour chaque cellule c :
- on entre i comme valeur de la cellule c : c.Value = i
- et on incrémente le compteur : i = i + 1

Assemblons tout cela pour constituer la macro :
Sub Numéroter_lignes_bloc()
Dim Plg As Range, c As Range, i As Long

' définition de la plage
Set Plg = ActiveCell.CurrentRegion

' insertion d’une colonne à gauche
Plg.Columns(1).Insert

' coeur de la macro
i = 0
For Each c In Plg.Columns(1).Cells.Offset(0, -1)
c = i
i = i + 1
Next c

End Sub

Arrivé là, il y a quelques détails qu’on peut affiner .

D’abord, éviter de numéroter une plage de cellules vides ou une plage qui ne contiendrait qu’une seule ligne de données. Ça, on peut le détecter immédiatement après avoir défini la plage : il suffit de regarder si, par hasard, cette plage compte une seule ligne (ce qui est le minimum : elle ne peut pas en compter zéro). Lorsque le cas se présente, il suffit par exemple de déclencher un bref message l’alerte puis de stopper la macro.
If Plg.Rows.Count = 1 Then
MsgBox "La plage sélectionnée ne contient qu’une seule ligne !"
Exit Sub
End If

Ensuite, comme la plage qu’on a définie au début ne nous servira plus et comme on va par contre travailler sur celle de la numérotation, autant redéfinir la variable Plg pour qu’elle corresponde à cette plage-ci.
Set Plg = Plg.Columns(1).Cells.Offset(0, -1)
L’endroit idéal pour faire ça c’est : juste après l’insertion de colonne. Du coup, ce qui suit (coeur de macro) pourra se faire sur cette variable, ce qui allège joliment le code.

Enfin, deux finitions cosmétiques : la première consiste à s’assurer que le format de nombre utilisé pour la numérotation est lisible, c’est à dire éviter les pourcentages, décimales intempestives, etc. On peut faire ça tout à la fin en travaillant sur la fameuse plage et en lui appliquant le code de format “nombre sans décimale, avec séparateur de milliers” :
Plg.NumberFormat = "#,##0"

La seconde consiste à ajuster la largeur de la colonne :
Plg.Columns.AutoFit



Il ne reste plus qu’à mettre tout ça ensemble :

Sub Numéroter_lignes_bloc()
Dim Plg As Range, c As Range, i As Long

' définition de la plage
Set Plg = ActiveCell.CurrentRegion

' cas de sortie : plage à 1 seule ligne
If Plg.Rows.Count = 1 Then
MsgBox "La plage sélectionnée ne contient qu’une seule ligne !"
Exit Sub
End If

' insertion d’une colonne à gauche
Plg.Columns(1).Insert

' redéfinition de la variable
Set Plg = Plg.Columns(1).Cells.Offset(0, -1)

' coeur de la macro
i = 0
For Each c In Plg
c = i
i = i + 1
Next c

' finitions
Plg.NumberFormat = "#,##0"
Plg.Columns.AutoFit

End Sub



Macro prête à l’usage ! Remarque en guise de conclusion : dans mon environnement de travail, j’ai cette macro de numérotation à portée de doigts, dans une barre d’outils personnelle. Je l’appelle avec un raccourci clavier de trois touches, ce qui la rend “évidente” : il m’arrive parfois même de l’utiliser pour de petits blocs de données !

Aucun commentaire:

Enregistrer un commentaire