Tri des données dans Excel VBA
Excel dispose d'un excellent moyen de trier une gamme de données tabulaires à l'aide du ruban sur le front-end d'Excel, et à un moment donné, vous voudrez probablement utiliser cette fonctionnalité dans votre code VBA. Heureusement, c'est très facile à faire.
La boîte de dialogue frontale se trouve en cliquant sur l'icône "Trier" dans le groupe "Trier et filtrer" de l'onglet "Données" du ruban Excel. Vous devez d'abord sélectionner une plage de données tabulaires.
Vous pouvez également utiliser Alt-A-S-S pour afficher la boîte de dialogue pour un tri personnalisé.
La méthode de tri a été grandement améliorée dans les versions ultérieures d'Excel. Le tri était auparavant limité à trois niveaux, mais vous pouvez désormais saisir autant de niveaux que vous le souhaitez, et cela s'applique également dans VBA.
Vous pouvez intégrer toutes les fonctions de tri proposées dans la boîte de dialogue Trier Excel dans votre code VBA. La fonction de tri dans Excel est rapide et plus rapide que tout ce que vous pourriez écrire vous-même en VBA, alors profitez de la fonctionnalité.
Notez que lorsque vous effectuez un tri dans VBA, les paramètres de tri restent les mêmes dans la boîte de dialogue de tri frontale. Ils sont également enregistrés lors de l'enregistrement du classeur.
Si un utilisateur sélectionne la même plage de données tabulaires et clique sur l'icône Trier, il verra tous vos paramètres qui ont été saisis par votre code VBA. S'ils veulent faire une sorte de leur propre design, alors ils devront d'abord supprimer tous vos niveaux de tri, ce qui sera très ennuyeux pour eux.
De plus, si vous ne modifiez pas les paramètres dans votre code et que vous vous fiez aux valeurs par défaut, vous constaterez peut-être que l'utilisateur a apporté des modifications qui se refléteront dans votre tri VBA et peuvent donner des résultats inattendus, ce qui peut être très difficile à déboguer .
Heureusement, il existe une méthode Clear dans VBA pour réinitialiser tous les paramètres de tri afin que l'utilisateur voie une boîte de dialogue de tri propre
| 1 | Feuilles de calcul("Feuille1").Sort.SortFields.Clear |
Il est recommandé d'effacer les paramètres de tri dans VBA avant et après la fin du tri.
Utilisation pratique de la méthode de tri en VBA
Lorsque des données tabulaires sont importées dans Excel, elles sont souvent dans un ordre très aléatoire. Il peut être importé à partir d'un fichier CSV (valeurs séparées par des virgules) ou il peut provenir d'un lien vers une base de données ou une page Web. Vous ne pouvez pas compter sur un ordre défini d'une importation à l'autre.
Si vous présentez ces données à un utilisateur dans votre feuille de calcul, l'utilisateur peut avoir du mal à regarder et à comprendre une énorme quantité de données qui, en termes d'ordre, sont omniprésentes. Ils peuvent vouloir regrouper les données ou en couper et coller certaines sections dans une autre application.
Ils voudront peut-être également voir, par exemple, l'employé le mieux payé ou l'employé ayant le plus d'ancienneté.
En utilisant la méthode Sort dans VBA, vous pouvez proposer des options pour permettre un tri facile pour l'utilisateur.
Exemples de données pour illustrer le tri Excel avec VBA
Nous avons d'abord besoin de quelques exemples de données à saisir dans une feuille de calcul, afin que le code puisse démontrer toutes les fonctionnalités disponibles dans VBA.
Copiez ces données dans une feuille de calcul (appelée « Sheet1 ») exactement comme indiqué.
Notez que différentes couleurs d'arrière-plan de cellule et couleurs de police ont été utilisées, car elles peuvent également être utilisées comme paramètres de tri. Le tri à l'aide des couleurs de cellule et de police sera démontré plus loin dans l'article. Notez également que sur la cellule E3, le nom du département est tout en minuscules.
Vous n'avez pas besoin de l'intérieur des cellules et des couleurs de police si vous ne souhaitez pas utiliser les exemples de tri par cellule et couleur de police.
Enregistrer une macro pour un tri VBA
Le code VBA pour le tri peut devenir assez compliqué, et il peut parfois être judicieux de faire le tri sur le front-end d'Excel et d'enregistrer une macro pour vous montrer comment fonctionne le code.
Malheureusement, la fonction d'enregistrement peut générer une énorme quantité de code car elle définit pratiquement tous les paramètres disponibles, même si les valeurs par défaut de nombreux paramètres sont acceptables pour votre opération de tri.
Cependant, cela vous donne une très bonne idée de ce qu'implique l'écriture du code de tri VBA, et l'un des avantages est que le code enregistré fonctionnera toujours pour vous. Votre propre code peut nécessiter des tests et un débogage pour qu'il fonctionne correctement.
N'oubliez pas que pour une opération effectuée en VBA, il n'y a pas de fonction d'annulation, c'est donc une bonne idée de faire une copie des données tabulaires sur une autre feuille de calcul avant de commencer à écrire votre code de tri.
Par exemple, si vous avez effectué un tri simple sur les exemples de données ci-dessus, en triant par employé, l'enregistrement générerait le code suivant :
| 123456789101112131415161718 | Sous-Macro1()Plage ("A1:E6").SélectionnezActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key :=Range("A2:A6"), _SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormalAvec ActiveWorkbook.Worksheets("Sheet1").Tri.SetRange Range("A1:E6").En-tête = xlOui.MatchCase = Faux.Orientation = xlHautVersBas.SortMethod = xlPinYin.AppliquerTerminer parFin du sous-marin |
Il s'agit d'un gros morceau de code, et une grande partie est inutile en raison des paramètres par défaut utilisés. Cependant, si vous êtes pressé par le temps pour terminer un projet et que vous avez besoin d'un code rapide qui fonctionne, vous pouvez facilement le coller dans votre propre code VBA.
Cependant, si vous souhaitez rendre votre code compréhensible et plus élégant, d'autres options sont disponibles.
Code VBA pour effectuer un tri à un seul niveau
Si vous souhaitez trier l'exemple de code basé sur Employé uniquement comme avant lors de l'enregistrement d'une macro, le code est très simple :
| 1234567 | Sous SingleLevelSort()Feuilles de calcul("Feuille1").Sort.SortFields.ClearPlage("A1:E6").Clé de tri1 :=Plage("A1"), En-tête :=xlOuiFin du sous-marin |
Ceci est beaucoup plus facile à comprendre que le code enregistré car il accepte les valeurs par défaut, par exemple, le tri par ordre croissant, il n'est donc pas nécessaire de définir les paramètres sur les valeurs par défaut. Cela suppose que vous ayez utilisé une déclaration « Clear » au préalable.
La méthode « Effacer » est utilisée initialement pour garantir que chaque paramètre de tri de cette feuille de calcul est rétabli aux valeurs par défaut. Un utilisateur peut avoir précédemment défini les paramètres sur des valeurs différentes, ou un tri antérieur dans VBA peut les avoir modifiés. Il est important de commencer à partir d'une position par défaut lors du tri, sinon vous pourriez facilement vous retrouver avec des résultats incorrects.
La méthode Clear ne réinitialise pas le paramètre Header, et il est conseillé de l'inclure dans votre code, sinon Excel peut essayer de deviner si une ligne d'en-tête est présente ou non.
Exécutez ce code sur les exemples de données et votre feuille de calcul ressemblera à ceci :
Code VBA pour effectuer un tri à plusieurs niveaux
Vous pouvez ajouter autant de niveaux de tri que nécessaire dans votre code. Supposons que vous souhaitiez trier d'abord par rayon puis par date de début mais par ordre croissant pour le rayon et décroissant pour la date de début :
| 12345678 | Sous MultiLevelSor()Feuilles de calcul("Feuille1").Sort.SortFields.ClearPlage ("A1:E6").Trier Clé1:=Plage ("E1"), Clé2:=Plage ("C1"), En-tête:=xlOui, _Order1:=xlAscending, Order2:=xlDescendingFin du sous-marin |
Notez qu'il y a maintenant deux clés dans l'instruction de tri (Key1 et Key2). Key1 (colonne de service E) est triée en premier, puis Key2 (colonne de date de début C) est triée en fonction du premier tri.
Il existe également deux paramètres de commande. Order1 s'associe à Key1 (Département) et Order2 s'associe à Key2 (Date de début). Il est important de s'assurer que les clés et les commandes sont en phase les unes avec les autres.
Exécutez ce code sur les exemples de données et votre feuille de calcul ressemblera à ceci :
La colonne Service (E) est en ordre croissant et la colonne Date de début (C) est en ordre décroissant.
L'effet de ce genre est le plus visible lorsque l'on regarde Jane Halfacre (rangée 3) et John Sutherland (rangée 4). Ils sont tous les deux en Finance mais Jane Halfacre a commencé avant John Sutherland et les dates sont affichées par ordre décroissant.
Si la plage de données tabulaires peut être de n'importe quelle longueur, vous pouvez utiliser l'objet UsedRange pour définir la plage de tri. Cela ne fonctionnera que s'il n'y a que les données tabulaires sur la feuille de calcul, car toute valeur en dehors des données donnera des résultats incorrects pour le nombre de lignes et de colonnes.
| 1234567 | Sous MultiLevelSor()Feuilles de calcul("Feuille1").Sort.SortFields.ClearWorksheets("Sheet1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _Order1:=xlAscending, Order2:=xlDescendingFin du sous-marin |
Cela évite le problème si vous utilisez la méthode 'End(xlDown)' pour définir la plage de tri. S'il y a une cellule vide au milieu des données, tout ce qui se trouve après la cellule vide ne sera pas inclus, tandis que UsedRange descend jusqu'à la dernière cellule active de la feuille de calcul.
Tri par couleur de cellule
Depuis Excel 2007, le tri par couleur de fond d'une cellule est désormais possible, ce qui offre une énorme flexibilité lors de la conception de votre code de tri en VBA.
| 123456789101112 | Sous SingleLevelSortByCellColor()Feuilles de calcul("Feuille1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key :=Range("A2:A6"), _SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormalAvec ActiveWorkbook.Worksheets("Sheet1").Tri.SetRange Range("A1:E6").AppliquerTerminer parFin du sous-marin |
Ce code triera l'échantillon de plage de données (A2:A6) en fonction de la couleur d'arrière-plan de la cellule. Notez qu'il existe maintenant un paramètre supplémentaire appelé « SortOn » qui a la valeur « xlSortOnCellColor ».
Notez que le paramètre « SortOn » ne peut être utilisé que par un objet de feuille de calcul et non par un objet de plage.
De ce fait, le code est plus compliqué que pour un tri utilisant des valeurs de cellule.
Ce code utilise une valeur clé pour le tri qui couvre toute la plage de données, mais vous pouvez spécifier des colonnes individuelles comme clé pour le tri par couleur d'arrière-plan et utiliser plusieurs niveaux comme indiqué précédemment.
Après avoir exécuté ce code, votre feuille de calcul ressemblera maintenant à ceci :
Tri par couleur de police
La fonction de tri dans Excel VBA offre encore plus de flexibilité dans la mesure où vous pouvez trier par couleurs de police :
| 1234567891011121314 | Sous SingleLevelSortByFontColor()Feuilles de calcul("Feuille1").Sort.SortFields.ClearActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("A2:A6"), _xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RVB (0, 0, 0)Avec ActiveWorkbook.Worksheets("Sheet1").Tri.SetRange Range("A1:E6").En-tête = xlOui.Orientation = xlHautVersBas.AppliquerTerminer parFin du sous-marin |
Le code de tri par couleur de police est beaucoup plus compliqué que pour la couleur d'arrière-plan de la cellule. Le paramètre 'SortOn' contient maintenant la valeur de 'xlSortOnFontColor'.
Notez que vous devez spécifier l'orientation comme « xlTopToBottom » et vous devez spécifier une couleur sur laquelle trier. Ceci est spécifié en termes RVB (Rouge, Vert, Noir) avec des valeurs allant de 0 à 255.
Après avoir exécuté ce code sur les exemples de données, votre feuille de calcul ressemblera désormais à ceci :
Le tri à l'aide des couleurs en VBA est bien plus compliqué qu'un tri à plusieurs niveaux, mais si votre code de tri ne fonctionnera pas (ce qui peut arriver si un paramètre est manquant ou si vous n'avez pas saisi le code correctement) alors vous pouvez toujours vous rabattre sur l'enregistrement une macro et intégrer le code enregistré dans votre VBA.
Utilisation d'autres paramètres dans le tri VBA
Il existe un certain nombre de paramètres facultatifs que vous pouvez utiliser dans votre code VBA pour personnaliser votre tri.
Trier sur
SortOn choisit si le tri utilisera les valeurs des cellules, les couleurs d'arrière-plan des cellules ou les couleurs de police des cellules. Le paramètre par défaut est Valeurs de cellule.
| 1 | SortOn = xlSortOnValues |
Commander
Ordre choisit si le tri sera effectué dans l'ordre croissant ou décroissant. La valeur par défaut est Croissant.
| 1 | Ordre = xlAscendant |
Option de données
DataOption choisit comment le texte et les nombres sont triés. Le paramètre xlSortNormal trie les données numériques et textuelles séparément. Le paramètre xlSortTextAsNumbers traite le texte comme des données numériques pour le tri. La valeur par défaut est xlSortNormal.
| 1 | DataOption = xlSortNormal |
Entête
En-tête choisit si la plage de données tabulaires a une ligne d'en-tête ou non. S'il y a une ligne d'en-tête, vous ne voulez pas qu'elle soit incluse dans le tri.
Les valeurs des paramètres sont xlYes, xlNo et xlYesNoGuess. xlYesNoGuess laisse à Excel le soin de déterminer s'il existe une ligne d'en-tête, ce qui pourrait facilement conduire à des résultats incohérents. L'utilisation de cette valeur n'est pas recommandée.
La valeur par défaut est XNo (pas de ligne d'en-tête dans les données). Avec les données importées, il y a généralement une ligne d'en-tête, alors assurez-vous de définir ce paramètre sur xlYes.
| 1 | En-tête = xlOui |
Cas de correspondance
Ce paramètre détermine si le tri est sensible à la casse ou non. Les valeurs des options sont True ou False. Si la valeur est False, les valeurs en minuscules sont considérées comme identiques aux valeurs en majuscules. Si la valeur est True, le tri affichera la différence entre les valeurs majuscules et minuscules dans le tri. La valeur par défaut est Faux.
| 1 | MatchCase = Faux |
Orientation
Ce paramètre détermine si le tri s'effectuera vers le bas dans les lignes ou dans toutes les colonnes. La valeur par défaut est xlTopToBottom (trier les lignes). Vous pouvez utiliser xlLeftToRight si vous souhaitez trier horizontalement. Les valeurs telles que xlRows et xlColumns ne fonctionnent pas pour ce paramètre.
| 1 | Orientation = xlHautVersBas |
Méthode de tri
Ce paramètre n'est utilisé que pour le tri des langues chinoises. Il a deux valeurs, xlPinYin et xlStroke. xlPinYin est la valeur par défaut.
xlPinYin trie les caractères en utilisant l'ordre de tri chinois phonétique. xlStroke trie par la quantité de traits dans chaque caractère.
Si vous enregistrez une macro de tri, ce paramètre sera toujours inclus dans le code, et vous vous êtes peut-être demandé ce que cela signifiait. Cependant, à moins que vous ne traitiez de données en chinois, cela n'a que peu d'utilité.
| 1 | Méthode de tri = xlPinYin |
Utilisation d'un événement double-clic pour trier les données tabulaires
Dans toutes les fonctionnalités que Microsoft a incluses dans les méthodes de tri pour VBA, il n'y avait pas de moyen simple de double-cliquer sur un en-tête de colonne et de trier l'ensemble des données tabulaires en fonction de cette colonne particulière.
C'est une fonctionnalité vraiment utile, et il est facile d'écrire le code pour le faire.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)'On suppose que les données commencent à la cellule A1'Créez trois variables pour capturer la colonne cible sélectionnée et la colonne et la ligne maximales de _' les données tabulairesDim Col As Integer, RCol As Long, RRow As Long'Vérifiez que l'utilisateur a double-cliqué sur la ligne d'en-tête - ligne 1 sinon quittez le sousSi Target.Row 1 puis quittez Sub'Capturez le maximum de lignes dans la plage de données tabulaires à l'aide de l'objet 'UsedRange'RCol = ActiveSheet.UsedRange.Columns.Count'Capturez le maximum de colonnes dans la plage de données tabulaires à l'aide de l'objet 'UsedRange'RRow = ActiveSheet.UsedRange.Rows.Count'Vérifiez que l'utilisateur n'a pas double-cliqué sur une colonne en dehors de la plage de données tabulairesSi Target.Column > RCol Then Exit Sub'Capturez la colonne sur laquelle l'utilisateur a double-cliquéCol = Cible.Colonne'Effacer les paramètres de tri précédentsActiveSheet.Sort.SortFields.Clear'Trier la plage tabulaire telle que définie par le nombre maximal de lignes et de colonnes de l'objet 'UsedRange''Trier les données tabulaires en utilisant la colonne double-cliquée par l'utilisateur comme clé de triActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Sort Key1:=Cells(1, Col), Header:=xlYes'Sélectionnez la cellule A1 - ceci permet de s'assurer que l'utilisateur n'est pas laissé en mode édition après le tri est _' complétéActiveSheet.Range("A1").SélectionnezFin du sous-marin |
Ce code doit être placé sur l'événement double-clic sur la feuille contenant les données tabulaires. Pour ce faire, cliquez sur le nom de la feuille de calcul dans la fenêtre de l'Explorateur de projet (coin supérieur gauche de l'écran VBE), puis sélectionnez « Feuille de calcul » dans le premier menu déroulant de la fenêtre de code. Sélectionnez « Avant DoubleClick » dans la deuxième liste déroulante, et vous pouvez ensuite entrer votre code.
Notez qu'aucun nom, plage ou référence de cellule n'est codé en dur dans ce code, à l'exception du déplacement du curseur vers la cellule A1 à la fin du code. Le code est conçu pour obtenir toutes les informations requises à partir des coordonnées de la cellule sur lesquelles l'utilisateur a double-cliqué et de la taille de la plage de données tabulaires.
Peu importe la taille de la plage de données tabulaires. Le code récupère toujours toutes les informations requises et peut être utilisé sur des données stockées n'importe où dans votre classeur sans avoir à coder en dur les valeurs.
La seule hypothèse est qu'il existe une ligne d'en-tête dans les données tabulaires et que la plage de données commence à la cellule A1, mais la position de départ de la plage de données peut être facilement modifiée dans le code.
Tout utilisateur sera convenablement impressionné par cette nouvelle fonctionnalité de tri !
Extension de la fonction de tri à l'aide de VBA
Microsoft a permis une grande flexibilité dans le tri à l'aide d'un large éventail de paramètres. Cependant, dans VBA, vous pouvez aller plus loin.
Supposons que vous vouliez trier toutes les valeurs avec une police en gras en haut de vos données. Il n'y a aucun moyen de le faire dans Excel, mais vous pouvez écrire le code VBA pour le faire :
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sous-trier par gras()'Créer des variables pour contenir le nombre de lignes et de colonnes pour les données tabulairesDim RRow aussi long, RCol aussi long, N aussi long'Désactivez la mise à jour de l'écran afin que l'utilisateur ne puisse pas voir ce qui se passe - il peut voir _' les valeurs sont altérées et je me demande pourquoiApplication.ScreenUpdating = Faux'Capturer le nombre de colonnes dans la plage de données tabulairesRCol = ActiveSheet.UsedRange.Columns.Count'Capturer le nombre de lignes dans la plage de données tabulairesRRow = ActiveSheet.UsedRange.Rows.Count'Itérer à travers toutes les lignes de la plage de données tabulaires en ignorant la ligne d'en-têtePour N = 2 à RRow'Si une cellule a une police en gras, placez une valeur 0 devant la valeur de la celluleSi ActiveSheet.Cells(N, 1).Font.Bold = True ThenActiveSheet.Cells(N, 1).Value = "0" & ActiveSheet.Cells(N, 1).ValueFin siN suivant'Effacer tous les paramètres de tri précédentsActiveSheet.Sort.SortFields.Clear'Trier la plage de données tabulaires. Toutes les valeurs avec un 0 en tête se déplaceront vers le hautActiveSheet.Range(Cells(1, 1), Cells(RCol, RRow)).Sort Key1:=Cells(1, 1), Header:=xlYes'Itérer à travers toutes les lignes de la plage de données tabulaires en ignorant la ligne d'en-têtePour N = 2 à RRow'Si une cellule a une police en gras, supprimez la valeur 0 de la valeur de la cellule à _' restaurer les valeurs d'origineSi ActiveSheet.Cells(N, 1).Font.Bold = True ThenActiveSheet.Cells(N, 1).Value = Mid(ActiveSheet.Cells(N, 1).Value, 2)Fin siN suivant'Réactiver la mise à jour de l'écranApplication.ScreenUpdating = TrueFin du sous-marin |
Le code calcule la taille de la plage de données tabulaires à l'aide de l'objet « UsedRange », puis parcourt toutes les lignes qu'il contient. Lorsqu'une police en gras est trouvée, un zéro non significatif est placé devant la valeur de la cellule.
Un tri a alors lieu. Comme le tri est dans l'ordre croissant, tout ce qui commence par un zéro ira en haut de la liste.
Le code parcourt ensuite toutes les lignes et supprime les zéros non significatifs, restaurant les données à leurs valeurs d'origine.
Ce code trie en utilisant les polices en gras comme critère, mais vous pouvez facilement utiliser d'autres caractéristiques de cellule de la même manière, par exemple la police italique, la taille du texte en points, la police de soulignement, le nom de la police, etc.
