Excel propose déjà plusieurs méthodes pour trier rapidement les données.
Vous pouvez facilement trier un ensemble de données à l'aide des icônes de tri du ruban ou de la boîte de dialogue de tri.
Alors pourquoi avez-vous besoin de savoir comment faire cela en utilisant VBA ?
Savoir comment trier les données à l'aide de VBA peut être utile lorsqu'il est inclus dans votre code. Par exemple, supposons que vous obteniez un ensemble de données quotidien/hebdomadaire que vous devez formater et trier dans un ordre spécifique.
Vous pouvez créer une macro pour faire tout cela pour vous en un seul clic. Cela vous fera économiser beaucoup de temps et d'efforts à chaque fois que vous le ferez.
De plus, si vous créez des tableaux de bord Excel, vous pouvez élever la capacité de tri d'Excel à un nouveau niveau en permettant à l'utilisateur de trier les données simplement en double-cliquant sur l'en-tête (comme indiqué ci-dessous).
Je couvrirai comment créer ceci plus tard dans ce tutoriel. Voyons d'abord rapidement les bases.
Comprendre la méthode Range.Sort dans Excel VBA
Lors du tri à l'aide de VBA, vous devez utiliser la méthode Range.Sort dans votre code.
La « plage » serait les données que vous essayez de trier. Par exemple, si vous triez les données dans A1:A10, alors « Range » serait Range (« A1:A10 »).
Vous pouvez également créer une plage nommée et l'utiliser à la place des références de cellule. Par exemple, si je crée une plage nommée "DataRange" pour les cellules A1:A10, je peux également utiliser Range ("DataRange")
Avec la méthode de tri, vous devez fournir des informations supplémentaires via des paramètres. Vous trouverez ci-dessous les paramètres clés que vous devez connaître :
- Clé - ici, vous devez spécifier la colonne que vous souhaitez trier. Par exemple, si vous souhaitez trier la colonne A, vous devez utiliser key:=Range("A1")
- Ordre - ici vous spécifiez si vous voulez le tri par ordre croissant ou par ordre décroissant. Par exemple, si vous voulez le tri par ordre croissant, vous utiliserez Order:=xlAscending
- Entête - ici, vous spécifiez si votre ensemble de données a des en-têtes ou non. S'il a des en-têtes, le tri commence à partir de la deuxième ligne de l'ensemble de données, sinon il commence à partir de la première ligne. Pour spécifier que vos données ont des en-têtes, vous utiliserez Header:=xlYes
Bien que ces trois suffisent dans la plupart des cas, vous pouvez en savoir plus sur les paramètres dans cet article.
Voyons maintenant comment utiliser la méthode Range.Sort dans VBA pour trier les données dans Excel.
Tri d'une seule colonne sans en-tête
Supposons que vous ayez une seule colonne sans en-tête (comme indiqué ci-dessous).
Vous pouvez utiliser le code ci-dessous pour le trier par ordre croissant.
Sub SortDataWithoutHeader() Range("A1:A12").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Notez que j'ai spécifié la plage de données manuellement en tant que plage ("A1:A12").
Au cas où il pourrait y avoir des changements dans les données et des valeurs pourraient être ajoutées/supprimées, vous pouvez utiliser le code ci-dessous qui s'ajuste automatiquement en fonction des cellules remplies dans l'ensemble de données.
Sub SortDataWithoutHeader() Range("A1", Range("A1").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Notez qu'au lieu de Range ("A1:A12"), j'ai utilisé Range ("A1", Range ("A1").End(xlDown)).
Cela vérifiera la dernière cellule remplie consécutivement dans la colonne et l'inclura dans le tri. S'il y a des blancs, il ne considérera que les données jusqu'à la première cellule vide.
Vous pouvez également créer une plage nommée et utiliser cette plage nommée au lieu des références de cellule. Par exemple, si la plage nommée est DataSet, votre code serait désormais comme indiqué ci-dessous.
Sub SortDataWithoutHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Permettez-moi maintenant d'expliquer rapidement les paramètres utilisés dans les exemples ci-dessus :
- Key1:=Range("A1") - Spécifié A1 pour que le code sache quelle colonne trier.
- Order1:=xlAscending - Spécifie l'ordre comme xlAscending. Si vous voulez qu'il soit dans l'ordre décroissant, utilisez xlDescending.
- Header:= xlNo - Spécifie qu'il n'y a pas d'en-têtes. Il s'agit également de la valeur par défaut. Ainsi, même si vous omettez cela, vos données seront triées en considérant qu'elles n'ont pas d'en-tête.
Vous vous demandez où mettre ce code VBA et comment exécuter la macro ? Lisez ce tutoriel !
Tri d'une seule colonne avec en-tête
Dans l'exemple précédent, l'ensemble de données n'avait pas d'en-tête.
Lorsque vos données ont des en-têtes, vous devez le spécifier dans le code afin que le tri puisse commencer à partir de la deuxième ligne de l'ensemble de données.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Vous trouverez ci-dessous le code qui triera les données par ordre décroissant en fonction des ventes des magasins.
Sub SortDataWithHeader() Range("DataRange").Sort Key1:=Range("C1"), Order1:=xlDescending End Sub
Notez que j'ai créé une plage nommée - 'DataRange', et utilisé cette plage nommée dans le code.
Tri de plusieurs colonnes avec en-têtes
Jusqu'à présent dans ce tutoriel, nous avons vu comment trier une seule colonne (avec et sans en-tête).
Maintenant, que faire si vous souhaitez trier en fonction de plusieurs colonnes.
Par exemple, dans l'ensemble de données ci-dessous, que se passe-t-il si je veux d'abord trier par code d'état, puis par magasin.
Voici le code qui va trier plusieurs colonnes à la fois.
Sub SortMultipleColumns() avec ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlYes .Apply End With End Sub
Ci-dessous le résultat que vous obtiendrez.
Dans l'exemple ci-dessus, les données sont d'abord triées par code d'état (colonne A). Ensuite, dans les données de code d'état, elles sont à nouveau triées par le magasin (colonne B). Cet ordre est déterminé par le code dans lequel vous le mentionnez.
Tri des données à l'aide d'un double-clic sur l'en-tête
Si vous créez un tableau de bord ou souhaitez plus de facilité d'utilisation dans vos rapports, vous pouvez écrire un code VBA qui triera les données lorsque vous double-cliquez sur les en-têtes.
Quelque chose comme indiqué ci-dessous :
Ci-dessous le code qui vous permettra de le faire :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range("DataRange").Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range(Target.Address) Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes End If End Sub
Notez que j'ai créé une plage nommée ("DataRange") et que je l'ai utilisée dans le code au lieu d'utiliser les références de cellule.
Dès que vous double-cliquez sur l'un des en-têtes, le code désactive la fonctionnalité habituelle de double-clic (qui consiste à entrer dans le mode d'édition) et utilise cette cellule comme clé lors du tri des données.
Notez également qu'à partir de maintenant, ce code trie toutes les colonnes par ordre croissant uniquement.
Notez que le double-clic est un déclencheur qui permet à Excel d'exécuter le code spécifié. Ces déclencheurs tels que le double-clic, l'ouverture d'un classeur, l'ajout d'une nouvelle feuille de calcul, la modification d'une cellule, etc. sont appelés événements et peuvent être utilisés pour exécuter des macros dans Excel. Vous pouvez en savoir plus sur les événements Excel VBA ici.Où mettre ce code ?
Vous devez coller ce code dans la fenêtre de code de la feuille dans laquelle vous souhaitez cette fonctionnalité de tri par double-clic.
Pour faire ça:
- Cliquez avec le bouton droit sur l'onglet de la feuille.
- Cliquez sur Afficher le code.
- Collez le code dans la fenêtre de code de la feuille dans laquelle résident vos données.
Maintenant, que faire si vous voulez trier les deux premières colonnes (« État » et « Magasin ») par ordre croissant, mais la colonne « Ventes » par ordre décroissant.
Voici le code qui le fera :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range("DataRange").Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range(Target.Address) If Target.Value = "Sales" Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes, Order1: =SortOrder End If End Sub
Dans le code ci-dessus, il vérifie si la cellule sur laquelle vous cliquez deux fois est l'en-tête Sales ou non. Si oui, alors il affecte la valeur xlDescending à la variable SortOrder, sinon il la rend xlAscending.
Allons maintenant un peu plus loin et montrons un marqueur visuel (flèche et cellule colorée) dans l'en-tête lorsqu'il est trié.
Quelque chose comme indiqué ci-dessous :
Pour obtenir cela, j'ai ajouté une nouvelle feuille de calcul et apporté les modifications suivantes (vous pouvez télécharger le fichier d'exemple et suivre):
- Changement du nom de la nouvelle feuille en « BackEnd ».
- Dans la cellule B2, entrez un symbole de flèche (pour ce faire, allez dans Insérer et cliquez sur l'option 'Symbole').
- Copiez et collez les en-têtes de l'ensemble de données dans la cellule A3:C3 de la feuille « Backend ».
- Utilisez la fonction suivante dans la cellule A4:AC4 :
=SI(A3=$C$1,A3&" "&$B$1,A3)
- Le reste des cellules sera automatiquement rempli par le code VBA lorsque vous double-cliquez sur les en-têtes pour trier la colonne.
Votre feuille de backend ressemblerait à quelque chose comme indiqué ci-dessous :
Vous pouvez maintenant utiliser le code ci-dessous pour trier les données en double-cliquant sur les en-têtes. Lorsque vous double-cliquez sur un en-tête, il obtiendra automatiquement la flèche dans le texte de l'en-tête. Notez que j'ai également utilisé une mise en forme conditionnelle pour mettre également en évidence la cellule.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range("DataRange").Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Worksheets("Backend").Range("C1") = Target.Value Set KeyRange = Range(Target.Address) Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes Worksheets("BackEnd ").Range("A1") = Target.Column For i = 1 To ColumnCount Range("DataRange").Cells(1, i).Value = Worksheets("Backend").Range("A4").Offset (0, i - 1).Value Next i End If End Sub
Notez que ce code fonctionne bien pour la façon dont mes données et mon classeur sont construits. Si vous modifiez la structure des données, vous devrez modifier le code en conséquence.
Télécharger le fichier exemple
