I. Introduction▲
Au quotidien, pour gérer mes données j'utilise les tableaux « structurés » et les fonctions qui me simplifient la vie, disponibles dans cette documentation : Fonctions en VBA pour gérer les Tableaux Structurés d’Excel.
Sous réserve que mes données ne dépassent pas la taille d'une feuille de calcul d'Excel, limitée actuellement à 1 048 576 lignes.
Ça se complique donc lorsqu'il faut gérer 12 mois glissants de 600 000 lignes de données chacun pour générer un tableau de synthèse...
Alors en attendant une version d'Excel à deux milliards de lignes, j'ai dû développer des fonctions pour gérer de gros volumes de données.
Ces fonctions, je souhaite les partager avec vous dans cette documentation.
Les données étant en mémoire, les traitements peuvent gérer plusieurs millions de lignes.
Pour coller au plus près avec la logique d'une feuille de calcul d'Excel où la première cellule est en ligne 1, colonne 1, les mémoires utilisées sont toutes à deux dimensions et en base 1. Soit une déclaration de ce style : Dim Data(1 To y, 1 To x) As Variant où y est le nombre de lignes et x le nombre de colonnes.
Gérer des mémoires en base 1 permet de charger rapidement (et de façon dynamique) l’ensemble les données d’une plage (objet Range) ou d’un tableau structuré (objet ListObject) par les fonctions du VBA, et inversement de les restituer.
En base 0, il faudrait lire les données une à une en base 1 pour les écrire en mémoire en base 0, ce qui est chronophage.
Notez que lors du chargement des données d’une plage ou d’un tableau structuré, seule la valeur de la cellule (propriété Value) est mémorisée sous forme de Variant (c’est-à-dire que les dates sont gérées comme des dates, les numériques comme des numériques, etc.). Par contre, les formules, les formats et autres propriétés sont ignorés.
Dans cette documentation, j'utiliserai souvent les termes « tableau de données » pour décrire ces mémoires et « colonnes » pour leur seconde dimension.
À l’appui d’un exercice simple, nous allons étudier dans les lignes qui suivent comment charger des données en mémoire pour les manipuler à notre guise avant, éventuellement, de les restituer sous forme de synthèse dans un tableau structuré.
Pour faciliter votre lecture, les données utilisées pour l'exercice sont restreintes, mais vous comprendrez que le principe sera identique avec de gros volumes.
II. Exercice pour mieux comprendre les fonctions utilisées▲
Prenons un cas d’école.
Les notes des élèves sont contenues dans trois tableaux différents.
Le premier est un tableau structuré nommé « Tableau1 », les deux suivants sont des plages classiques sur les feuilles « Feuil5 » et « Feuil6 » :
Le but est d’avoir un tableau de synthèse qui reprend la liste des élèves, le nombre de notes obtenues, leur moyenne et leur classement. À chaque sélection d’un élève, le détail de ses notes s’affiche dans un second tableau à gauche :
Gardez bien en mémoire ces tableaux, car ils serviront de fil conducteur dans cette documentation.
II-A. DataFromRange - Charger des données depuis une feuille de calcul▲
Pour commencer notre exercice, nous allons charger en mémoire les données de ces trois tableaux sources avec la fonction DataFromRange.
Ses arguments sont :
- Rg : La plage qui contient les données, c’est-à-dire soit un tableau structuré, soit une plage classique qui possède ou non une ligne d’en-tête ;
- ArrayColumns : (facultatif) Un Array qui contient la liste des colonnes à reprendre (dans l’ordre indiqué) ou vide pour toutes les reprendre.
La fonction renvoie un tableau de données en base 1.
Pour charger le tableau structuré, dans la mémoire « Données » préalablement déclarée par Dim Données(), nous indiquerons à la fonction de ne reprendre que les cinq premières colonnes (la colonne « Id » n’est pas souhaitée) :
Données = DataFromRange(Range("Tableau1"), Array(1, 2, 3, 4, 5))
Notez qu’il n’est pas nécessaire d’indiquer la feuille où se trouve le tableau structuré, car Excel la retrouve de lui-même.
Notez également que Range("Tableau1") peut être remplacé par [Tableau1], tout dépend de vos habitudes de programmation.
Le deuxième tableau (plus précisément une plage de cellules) est chargé dans une autre mémoire, « Tps », que l’on concatènera à « Données » par la suite. Ce tableau n’ayant pas d’en-tête, on indiquera dans l’argument « Rg » la cellule « A1 », car la fonction étendra automatiquement la plage à la région concernée. Par contre, il faudra bien tenir compte de l’ordre des colonnes à reprendre (les colonnes 4 et 5 sont inversées) :
Tps = DataFromRange(Sheets("Feuil5").Range("A1"), Array(1, 2, 3, 5, 4))
Enfin, pour le troisième tableau à charger (lui aussi une plage de cellules), l’argument « ArrayColumns » n’a pas besoin d’être renseigné puisque les colonnes sont dans l’ordre attendu. Il faut juste penser à indiquer dans l’argument « Rg » la cellule « A2 » pour ne pas reprendre la ligne d’en-tête et non pas « A1 » :
Tps = DataFromRange(Sheets("Feuil6").Range("A2"))
Astuce : La fonction DataFromTxt charge dans une mémoire un fichier texte (par défaut les champs sont délimités par une tabulation, mais vous pouvez définir le délimiteur à appliquer).
II-B. DataAddData - Concaténer deux tableaux de données▲
Revenons à l’étape où nous avons chargé le premier tableau dans la mémoire « Données » et le deuxième dans la mémoire « Tps ».
Pour ajouter les données de « Tps » à « Données », nous utilisons la fonction DataAddData.
Ses arguments sont :
- DataTable : La mémoire qui contient les données sources ;
- DataTableAdd : La mémoire qu’il faut ajouter à « DataTable » ;
- ArrayColumnCompareValue : (facultatif) Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments à ajouter (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut).
La fonction renvoie un tableau de données en base 1 qui reprend tout DataTable plus les données de DataTableAdd qui correspondent aux critères de sélection (vide pour tout reprendre).
Ce qui donne ceci pour ajouter toutes les données de « Tps » à « Données » :
Données = DataAddData(Données, Tps)
Nous ferons de même avec le troisième tableau pour obtenir une mémoire « Données » qui contient l’ensemble des données des trois sources.
Les arguments facultatifs de cette fonction permettent de limiter la concaténation à certaines données de DataTableAdd, par exemple quand les notes (colonne 3) sont supérieures à 0, soit :
Données = DataAddData(Données, Tps, Array(3, ">", 0))
Notez que les deux mémoires doivent avoir la même structure de données, c’est-à-dire le même nombre de colonnes.
Astuce : La ligne d'instruction Données = DataAddData(Données, Tps, Array(3, ">", 0)) n'est pas évidente à comprendre, car elle reprend deux variables numériques dans sa partie Array(3, ">", 0) et l'on peut confondre l'argument colonne et l'argument valeur. C'est pourquoi je propose de préfixer l'argument qui représente la colonne par "C", ce qui donne Données = DataAddData(Données, Tps, Array("C3", ">", 0)) et peut se lire plus facilement par : Colonne 3 est supérieure à 0.
Je vais utiliser cette notation, qui est facultative, dans la suite de cette documentation. Les fonctions sont adaptées pour gérer ces deux façons de faire, libre à vous d'utiliser ce qui vous convient le mieux.
- Les symboles que j'ai choisis pour identifier les opérateurs de comparaison commencent par « ?* », contient « * », termine par « *? », peuvent être remplacés par ceux de votre choix dans la fonction VbCompare s'ils ne sont pas assez explicites pour vous.
- L'instruction Données = DataAddData(Données, Tps) peut être remplacée par son équivalent en déclarant le nom des arguments utilisés :
Données = DataAddData(DataTable:=Données, DataTableAdd:=Tps)
Tout dépend de vos habitudes de programmation.
II-C. DataRemove - Supprimer des éléments▲
Les données chargées dans la mémoire « Données » incluent des notes à 0, qui signifient que l’évaluation n’a pas encore été faite. Il faut donc les supprimer pour ne pas fausser la moyenne, avec la fonction DataRemove.
Ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- ArrayColumnCompareValue : (facultatif) Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments à supprimer (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut).
La fonction renvoie le nombre d'éléments supprimés.
Et donc pour supprimer les notes qui valent 0 (c’est-à-dire la colonne 3 vaut 0) :
Call DataRemove(Données, Array("C3", "=", 0), True)
- L'argument MatchCase étant à True par défaut, il n'était pas nécessaire de le renseigner dans cet exemple.
- Et la fonction ne renvoyant rien d'utilisé, il était possible de l'appeler par :
DataRemove Données, Array("C3", "=", 0)
Ici aussi vous adapterez les codes présentés à vos habitudes de programmation.
Astuce : Utilisez la fonction DataRemoveItem pour supprimer un élément d'après sa position dans la mémoire.
II-D. DataSort - Trier les données▲
La fonction DataSort trie (par ordre croissant ou décroissant) un tableau de données sur une ou plusieurs de ses colonnes.
Ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- ArrayColumns : Les colonnes (en Array) sur lesquelles porte le tri (si la colonne est préfixée « < » alors trie par ordre décroissant) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut) ;
- ToReturn : (facultatif) Si renseigné, alors alimente directement la mémoire passée en argument, ce qui fait gagner du temps de traitement.
La fonction renvoie un tableau de données en base 1 qui contient les données triées.
Nous allons ainsi trier par ordre croissant la mémoire « Données » sur les noms, prénoms et dates :
Données = DataSort(Données, Array("C1", "C2", "C4"), True)
Cela sera nécessaire pour la restitution du détail lorsqu’un élève est sélectionné.
Astuce : Vous pouvez inverser l’ordre d'une mémoire avec la fonction DataReverse.
Vous trouverez en annexe 2 plus d'informations sur l'algorithme de tri utilisé qui respecte l'ordre d'origine des données lorsqu'il y a des égalités (on parle alors d'un tri stable), comme le fait Excel alors que l'algorithme Quick Sort n'en est pas capable.
Astuce : Gagnez du temps de traitement en passant en argument ToReturn la mémoire qui doit contenir le tri.
Par exemple : Call DataSort(Données, "C1", True, Tri)
Au lieu de : Tri = DataSort(Données, "C1", True)
II-E. DataFromData - Copier certaines colonnes▲
Pour générer la synthèse des élèves, il nous faut dans un premier temps recopier les noms et prénoms de la mémoire « Données » dans une autre mémoire que nous nommerons « Synthese ».
Les arguments de la fonction DataFromData sont :
- DataTable : La mémoire qui contient les données ;
- ArrayColumns : Les colonnes (en Array) qu’il faut reprendre et dans l’ordre désiré.
La fonction renvoie un tableau de données en base 1 qui contient les données chargées.
Ici, nous copions les colonnes 1 et 2 de « Données » dans « Synthese » :
Synthese = DataFromData(Données, Array("C1", "C2"))
Pour faire une copie intégrale d'une mémoire, utilisez tout simplement l'opérateur égal :
Dim Data
Data = Données
II-F. DataUnique - Générer une liste unique▲
Nous pouvons maintenant générer un tableau de données sans doublon pour la synthèse des élèves à partir de la mémoire « Synthese » qui ne contient que deux colonnes, pour les noms et prénoms.
La fonction DataUnique réalise cela.
Ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut).
La fonction renvoie un tableau de données en base 1 qui contient les données sans doublon classées par ordre croissant.
La mémoire « Synthese » est transformée en une liste unique, triée par ordre croissant :
Synthese = DataUnique(Synthese, True)
II-G. DataAddColumns - Ajouter (ou supprimer) des colonnes▲
Nous venons de voir que le tableau de données « Synthese » des élèves (sans doublon) ne contient que deux colonnes, pour les noms et prénoms. Or, il nous en faut trois autres : pour le nombre de notes, pour la moyenne, pour le classement.
Nous les ajoutons avec la fonction DataAddColumns.
Ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- Columns : Le nombre de colonnes à ajouter, ou à supprimer si la valeur est négative.
La fonction renvoie un tableau de données en base 1 qui correspond à DataTable avec plus ou moins de colonnes selon ce qui est demandé dans l'argument Columns.
Ajout de trois colonnes au tableau de données « Synthese » :
Synthese = DataAddColumns(Synthese, 3)
Si l'argument DataTable est une mémoire à une seule dimension, déclarée par Dim Data(1 To n), alors la fonction la transforme en un tableau de données de la forme Data(1 To y, 1 To x) comme expliqué dans l'introduction.
II-H. DataCount - Compter le nombre d’éléments▲
Dans le tableau de données « Synthese », pour alimenter la colonne du nombre des notes pour chaque élève, nous allons lire toutes les lignes (nous verrons plus loin comment faire cette boucle), et pour chacune compter le nombre d’éléments dans « Données » qui correspondent au nom et prénom de l'élève.
La fonction DataCount renvoie le nombre d’éléments qui correspondent aux critères demandés.
Ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- ArrayColumnCompareValue : Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments concernés (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut) ;
- Sorted : (facultatif) True si la mémoire passée dans l'argument DataTable est triée par ordre croissant et que l'on peut faire une recherche dichotomique sur le premier couple passé dans « ArrayColumnCompareValue », la valeur par défaut est False.
La fonction renvoie un entier long représentant le nombre d'éléments correspondant aux critères d'analyse désirés.
Soit pour chaque ligne « i », il faut compter combien de fois l’on retrouve dans la mémoire « Données » en colonne 1 son nom, correspondant à « Synthese(i, 1) », et en colonne 2 son prénom, correspondant à « Synthese(i, 2) » :
Nb = DataCount(Données, Array("C1", "=", Synthese(i, 1), "C2", "=", Synthese(i, 2)), True, True)
Astuce : Dans notre cas la mémoire « Données » où porte la recherche est triée par ordre croissant sur les noms, prénoms et dates. Pour trouver plus rapidement les éléments recherchés dans cette mémoire, au lieu de tous les lire, il est bien plus rapide de faire une recherche dichotomique pour accéder rapidement au premier élément concerné et de commencer les analyses à partir de cette position. C’est pourquoi ici l’argument « Sorted » est mis à True.
Pour faire des recherches en boucle sur de grandes listes, je vous recommande de trier au préalable le tableau de données source et de procéder ainsi, car vous gagnerez énormément en temps de traitement.
Astuce : Pour faire une boucle de la première à la dernière ligne de la mémoire « Synthese », vous pouvez utiliser l'instruction Ubound(Synthese) pour connaitre le nombre de lignes de la mémoire, ou la fonction DataSize qui fait la même chose :
For i = 1 to DataSize(Synthese)
...
Next i
II-I. DataSum - Faire la somme d’une colonne▲
Nous allons procéder de la même façon pour alimenter la colonne de la moyenne des notes, qui est le nombre de notes divisé par la somme des notes.
Nous avons déjà le nombre de notes, nous allons retrouver la somme des notes avec la fonction DataSum.
Ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- Column : La colonne dont il faut faire la somme ;
- ArrayColumnCompareValue : Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments concernés (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut) ;
- Sorted : (facultatif) True si la mémoire passée dans l'argument DataTable est triée par ordre croissant et que l'on peut faire une recherche dichotomique sur le premier couple passé dans « ArrayColumnCompareValue », la valeur par défaut est False.
La fonction renvoie un numérique de type Double qui est la somme de la colonne passée en argument correspondant aux critères d'analyse désirés.
Soit pour chaque ligne « i » de « Synthese », sachant que les noms sont en colonne 1 et les prénoms en colonne 2 dans les deux mémoires, et que les notes sont en colonne 3 dans « Données » :
Somme = DataSum(Données, "C3", Array("C1", "=", Synthese(i, 1), "C2", "=", Synthese(i, 2)), True, True)
II-J. DataUpdateItem - Mettre à jour les données▲
Nous avons déterminé pour chaque élève le nombre de notes et leur somme. Il ne reste plus qu’à mettre à jour les colonnes « Nb » et « Moyenne », colonnes 3 et 4, du tableau de données « Synthese », avec la fonction DataUpdateItem.
Ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- Column : La colonne qu'il faut modifier ;
- Action : L’action à effectuer
« = » remplacer la donnée par la valeur de l'argument "Value",
« + » ajouter à la donnée la valeur de l'argument "Value",
« - » soustraire à la donnée la valeur de l'argument "Value",
« * » multiplier la donnée par la valeur de l'argument "Value",
« / » diviser la donnée par la valeur de l'argument "Value" ; - Value : La valeur de référence pour modifier la colonne suivant l'action désirée ;
- Item : La position de l’élément à mettre à jour.
La fonction renvoie True si tout s'est bien passé, False dans le cas contraire.
Soit pour chaque ligne « i », pour remplacer la note en colonne 3 dans « Synthese » par la valeur « Nb » retrouvée :
Call DataUpdateItem(Synthese, "C3", "=", Nb, i)
Et pour remplacer la moyenne en colonne 4 (avec une précision d’un chiffre après la virgule) :
Call DataUpdateItem(Synthese, "C4", "=", Round(Somme / Nb, 1), i)
Il est possible aussi de modifier directement les mémoires par ces instructions :
Synthese(i, 3) = Nb
Synthese(i, 4) = Round(Somme / Nb, 1)
Inversement, pour connaitre le contenu d'une mémoire vous pouvez :
- soit faire un appel direct : Nb = Synthese(i, 3)
- soit utiliser la fonction DataValue : Nb = DataValue(Synthese, "C3", i)
Astuce : La fonction DataUpdateAll met à jour tout un tableau de données suivant un critère d'analyse, sans avoir besoin de faire une boucle sur ses lignes.
En plus des quatre premiers arguments de DataUpdateItem, cette fonction en comporte trois autres déjà présentés plusieurs fois : « ArrayColumnCompareValue », « MatchCase » et « Sorted ».
Par exemple pour ajouter 1 point aux notes inférieures à 10 dans « Données » :
Call DataUpdateAll(Données, "C3", "+", 1, Array("C3", "<", 10))
II-K. DataRank – Obtenir l'ordre de classement▲
Nous avons enregistré la moyenne des notes dans la mémoire « Synthese ». Cela nous permet de définir l'ordre de classement des élèves. De celui qui a la plus forte moyenne, classé premier, à celui qui a la plus faible, classé sixième.
La fonction DataRank a les arguments suivants :
- DataTable : La mémoire qui contient les données ;
- Column : La colonne dont il faut analyser l'ordre de classement, en principe elle ne contient que des données numériques
Si la colonne est préfixée « = », alors le même rang est attribué pour les égalités.
Si la colonne est préfixée « < », alors l'ordre de classement (qui est croissant par défaut) est inversé. - MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut).
La fonction renvoie un tableau de données en base 1 qui contient l'ordre de classement des données.
Pour alimenter la colonne du classement dans le tableau de synthèse :
' Création du classement (même ordre pour les égalités) d'après la moyenne en colonne 4:
Dim Classement
Classement = DataRank(Synthese, "<=C4")
' Boucle sur les élèves dans le tableau de synthèse pour actualiser le classement en colonne 5:
For i = 1 To DataSize(Synthese)
Synthese(i, 5) = Classement(i)
Next i
Notez que la colonne est préfixée « <= », car nous souhaitons attribuer le même rang aux élèves qui ont la même moyenne, et qu'il faut inverser l'ordre de classement qui par défaut est croissant, car la meilleure moyenne est celle qui est la plus élevée et non l'inverse.
II-L. DataToTable – Alimenter un tableau structuré▲
Nous avons terminé tous nos traitements, il ne reste plus qu’à alimenter le tableau structuré de synthèse par élève, nommé « TS_Synthese », avec les données de la mémoire « Synthese ».
La fonction DataToTable a les arguments suivants :
- DataTable : La mémoire qui contient les données ;
- TS : Le tableau structuré à renseigner.
- Action : L’action à effectuer suivant l'énumération Enum_DataToTable
DataToTable_Replace : Remplace les données existantes,
DataToTable_Add : Ajoute les données aux données existantes,
Les colonnes non renseignées sont vides, les colonnes en plus sont ignorées.
La fonction renvoie True si tout s'est bien passé, False dans le cas contraire.
Pour alimenter la synthèse en remplaçant les données existantes :
Call DataToTable(Synthese, [TS_Synthese], DataToTable_Replace)
Notez que [TS_Synthese] est équivalent à Range("TS_Synthese").
Plus d'informations pour gérer les tableaux structurés dans cette documentation : Fonctions en VBA pour gérer les Tableaux Structurés d’Excel.
II-M. DataIndexOf - Connaitre la position d’un élément▲
Pour connaitre la moyenne de l’élève « TERRIEUR Alain » il nous faut avant tout connaitre sa position (sa ligne) dans la liste « Synthese ».
Pour cela nous utiliserons la fonction DataIndexOf, ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- ArrayColumnCompareValue : Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments concernés (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut) ;
- Sorted : (facultatif) True si la mémoire passée dans l'argument DataTable est triée par ordre croissant et que l'on peut faire une recherche dichotomique sur le premier couple passé dans « ArrayColumnCompareValue », la valeur par défaut est False.
La fonction renvoie la position du premier élément trouvé dans le tableau de données qui répond aux critères de sélection, ou -1 si rien n’est trouvé.
Dans notre cas on obtient la position par :
i = DataIndexOf(Synthese, Array("C1", "=", "TERRIEUR", "C2", "=", "Alain"))
Puis la moyenne par :
Moyenne = Synthese(i, 4)
II-N. DataContains - Savoir si un élément existe▲
Cette fois nous souhaitons juste savoir si au moins un élève a une moyenne supérieure ou égale à 15.
Pour cela nous utiliserons la fonction DataContains, ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- ArrayColumnCompareValue : Un Array qui indique le ou les couples « Colonne », « Comparateur », « Valeur » pour identifier les éléments concernés (l’opérateur de comparaison peut être « = », « <> », « > », « < », « >= », « <= », « ?* » commence par, « * » contient, « *? » termine par) ;
- MatchCase : (facultatif) Indique s’il faut tenir compte ou non de la casse (True par défaut) ;
- Sorted : (facultatif) True si la mémoire passée dans l'argument DataTable est triée par ordre croissant et que l'on peut faire une recherche dichotomique sur le premier couple passé dans « ArrayColumnCompareValue », la valeur par défaut est False.
La fonction renvoie True si un élément répond aux critères de sélection, ou False si rien n’est trouvé.
Dans notre cas DataContains(Synthese, Array("C4", ">=", 15)) renvoie True.
II-O. DataAddItem - Ajouter un élément▲
Un nouvel élève vient d’arriver. N’ayant pas encore été noté, il n’apparait donc pas dans les listes, mais nous souhaitons le voir dans la synthèse en dernière ligne.
Nous allons l’ajouter « manuellement » avec la fonction DataAddItem.
Ses arguments sont :
- DataTable : La mémoire qui contient les données ;
- ArrayValues : Un Array des valeurs à ajouter (dans l'ordre des colonnes de l'objet) ;
- Index : (facultatif) Indique la position où insérer l'élément à modifier, vide ou 0 pour l’ajouter à la suite de la liste.
Par exemple pour ajouter un nouvel élève :
Call DataAddItem(Synthese, Array("AIMAR", "Jean"))
Et pour insérer un élève en première position :
Call DataAddItem(Synthese, Array("AIMAR", "Jean"), 1)
Remarque : les colonnes non renseignées prennent la valeur « vide ».
III. Le code source de l’exercice▲
Voici le code intégral de l’exercice :
Public Données
'----------------------------------------------------------------------------------------
Dim i As Long
Dim Tps As Variant, Synthese As Variant
Dim Nb As Long, Somme As Double
' Gestion des erreurs:
On Error GoTo Gest_Err
Err.Clear
' Charge les données à importer depuis un tableau structuré nommé "Tableau1"
' (pas besoin d'indiquer la feuille, car Excel la retrouve de lui-même, mais
' comme il y a une colonne de trop il faut indiquer les colonnes à reprendre),
' et mémorise les données dans "Données" (qui est publique, car on s'en servira
' par la suite lors d'un événement sur le tableau de synthèse):
Données = DataFromRange(Range("Tableau1"), Array("C1", "C2", "C3", "C4", "C5"))
' Charge les données à importer d'une autre liste sans en-tête
' (et dans un ordre différent que celui que l'on souhaite):
Tps = DataFromRange(Sheets("Feuil5").Range("A1"), Array("C1", "C2", "C3", "C5", "C4"))
' Concatène les deux listes dans "Données":
Données = DataAddData(DataTable:=Données, DataTableAdd:=Tps)
' Charge les données à importer d'une autre liste avec en-têtes (pas besoin d'indiquer
' l'ordre des colonnes, car elles sont dans le bon ordre et il n'y en a pas en trop):
Tps = DataFromRange(Sheets("Feuil6").Range("A2"))
' Concatène les deux listes dans "Données":
Données = DataAddData(Données, Tps)
' Supprime les notes à 0 (c'est-à-dire 0 est dans la 3è colonne):
Call DataRemove(Données, Array("C3", "=", 0), True)
' Trie la liste par nom, prénom, date:
Données = DataSort(Données, Array("C1", "C2", "C4"), True)
' Charge uniquement les noms et prénoms (pour alimenter la synthèse):
Synthese = DataFromData(Données, Array("C1", "C2"))
' Création d'une liste unique avec les noms et prénoms (triée dans l'ordre de ses colonnes):
Synthese = DataUnique(Synthese, True)
' Ajoute trois colonnes (pour contenir par la suite le nombre de notes, la moyenne et le classement)
' à cette liste qui servira de synthèse:
Synthese = DataAddColumns(Synthese, 3)
' Boucle sur les éléments de Synthese pour actualiser le nombre de notes et la moyenne:
For i = 1 To DataSize(Synthese)
' Recherche le nombre de notes pour le nom + prénom dans la liste source triée:
Nb = DataCount(Données, Array("C1", "=", Synthese(i, 1), "C2", "=", Synthese(i, 2)), True, True)
' Mise à jour de la colonne Nb (3è colonne en base 1) pour l'élément i dans la liste de synthèse:
Call DataUpdateItem(Synthese, "C3", "=", Nb, i)
' Recherche la somme des notes pour le nom + prénom dans la liste source triée:
Somme = DataSum(Données, "C3", Array("C1", "=", Synthese(i, 1), "C2", "=", Synthese(i, 2)), True, True)
' Mise à jour de la colonne Moyenne (4è colonne en base 1) pour l'élément i dans la liste de synthèse:
Call DataUpdateItem(Synthese, "C4", "=", Round(Somme / Nb, 1), i)
Next i
' Création du classement (même ordre pour les égalités) d'après la moyenne en colonne 4:
Dim Classement
Classement = DataRank(Synthese, "<=C4")
' Boucle sur les élèves dans le tableau de synthèse pour actualiser le classement:
For i = 1 To DataSize(Synthese)
Synthese(i, 5) = Classement(i)
Next i
' Affiche la synthèse en remplaçant les données existantes:
' [TS_Synthese] est équivalent à Range("TS_Synthese"):
Call DataToTable(Synthese, [TS_Synthese], DataToTable_Replace)
' Prépare le tableau de détail TS_Données:
With Range("TS_Données").ListObject
.DataBodyRange.Clear
If .ListRows.Count > 1 Then .DataBodyRange.Rows(2 & ":" & .ListRows.Count).Delete
.ShowHeaders = True
.ShowTotals = False
End With
[A12].Select
' Fin du traitement:
Gest_Err:
If Err.Number <> 0 Then
MsgBox "Erreur " & Err.Number & " : " & Err.Description, vbCritical, Err.source
End If
Err.Clear
End Sub
'------------------------------------------------------------------------------------------------
Et le code sur la feuille où se trouve le tableau de synthèse pour gérer l’événement sur sélection d’un élève :
'------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'------------------------------------------------------------------------------------------------
' Si la cellule (active) est dans un tableau structuré:
If Not Target.ListObject Is Nothing Then
' Pointe sur ce tableau:
Dim TS As Range
Set TS = Range(Target.ListObject)
' Si ce n'est pas une ligne de titre et le tableau de synthèse:
If Target.Row >= TS.Row And TS.ListObject.Name = "TS_Synthese" Then
' Retrouve la ligne dans le tableau structuré:
Dim i As Long, Nom As String, Prénom As String
i = Target.Row - TS.Row + 1
' Et le nom et prénom concernés:
Nom = TS.ListObject.DataBodyRange(i, 1)
Prénom = TS.ListObject.DataBodyRange(i, 2)
' Création d'une liste qui contient les données de l'élément cliqué:
If DataSize(Données) > 0 Then
Dim List As Variant
List = DataAddData(List, Données, Array("C1", "=", Nom, "C2", "=", Prénom))
' Affiche la liste dans le tableau structuré TS_Données:
If DataSize(List) > 0 Then Call DataToTable(List, [TS_Données], DataToTable_Replace)
End If
End If
End If
End Sub
'------------------------------------------------------------------------------------------------
IV. DataFromSQL - Exécuter une requête SQL▲
Le tableau de synthèse des notes et moyennes des élèves que nous avons obtenu ferait un bon exercice de requête SQL :
« Sélectionner dans la mémoire « Données » les champs [Nom Famille], [Prénom], Nombre de ([Note]), Moyenne de ([Note]) ; Regrouper par [Nom Famille], [Prénom] ; Trier par [Nom Famille], [Prénom] ».
Mais il n’est pas possible d’exécuter une requête SQL sur un tableau de données.
Sauf si l’on triche un peu... et sous réserve que les données ne dépassent pas la capacité d'une feuille de calcul d'Excel.
Il suffit de reverser les données de la mémoire sur une feuille de calcul provisoire, d’y ajouter une ligne d’en-tête de colonne (nommés arbitrairement C1, C2, C3, etc.), d’exécuter une requête SQL, de récupérer le jeu d’enregistrements obtenu pour le placer dans une nouvelle mémoire, et enfin de supprimer la feuille provisoire.
La fonction DataFRomSQL a les arguments suivants :
- DataTable : La mémoire qui contient les données ;
- Fields : Les champs à sélectionner ;
- StrSQL : L’instruction en SQL.
La fonction renvoie un tableau de données en base 1 qui répond à la requête SQL, ou Nothing.
Ce qui permet d’obtenir le tableau de synthèse en une seule instruction et avec de bien meilleurs temps de traitement sur les grandes listes, qu’avec la boucle que nous avons faite sur chaque ligne :
Synthese = DataFromSQL(Données, "C1, C2, Count(C3), AVG(C3)", "GROUP BY C1, C2 ORDER BY C1, C2")
Les habitués du SQL remarqueront que la commande « SELECT » n’est pas indiquée : elle est ajoutée automatiquement avant la liste des champs.
Tout comme il manque la commande « FROM » : c’est parce qu’elle doit être complétée du nom de la feuille créée temporairement, nom que l’on ne connait qu’à sa création.
Remarques sur l’argument « Fields » :
L’argument « Fields » indique les numéros des colonnes à sélectionner, en base 1 et préfixés par « C », qui correspondent donc à la dimension du tableau de données.
La première colonne est désignée « C1 », la suivante « C2 » et ainsi de suite.
Une étoile « * » permet de sélectionner directement toutes les colonnes, ce qui risque d’augmenter les temps de traitement sur de grandes listes.
Vous pouvez inclure des fonctions d’agrégation :
- AVG : Calcule la moyenne ;
- SUM : Calcule la somme ;
- COUNT : Compte le nombre d'éléments ;
- MIM : Renvoie la valeur la plus petite ;
- MAX : Renvoie la valeur la plus grande.
Remarques sur l’argument « StrSQL » :
L’argument « StrSQL » indique les instructions de la requête. Plusieurs commandes sont disponibles, les principales sont :
- GROUP BY : Fait un regroupement des données.
- ORDER BY : Trie les colonnes par ordre ascendant (par défaut) avec la clause ASC ou descendant avec la clause DESC. Il est possible d'ordonner sur plusieurs colonnes en respectant la syntaxe :
ORDER BY « colonne 1 » [ASC, DESC], « colonne 2 » [ASC, DESC]
- WHERE : Pour restreindre la sélection, vous pouvez ajouter une condition simple ou combinée avec les opérateurs AND et OR, comme en VBA.
Les données alphanumériques sont à mettre entre simples quotes. Si la donnée comporte un simple quote, il convient de le doubler. Par exemple :
WHERE « colonne » = 'Val d''Oise'
Les dates sont à mettre entre deux croisillons et au format anglo-saxon (année/mois/jour). Par exemple pour sélectionner les personnes nées à Noël 1971 :
WHERE « colonne » = #1971/12/25#
Le mot clé IN permet de sélectionner des éléments parmi une liste de valeurs, soit la syntaxe suivante :
WHERE « colonne » IN ('valeur1', 'valeur2', 'valeur3'…)
Le mot clé LIKE permet de sélectionner des chaînes de caractères en disposant des jokers :
WHERE « colonne » LIKE 'T%' renvoie les noms commençants par « T ».
WHERE « colonne » LIKE '%TOTO%' renvoie les noms qui contiennent « TOTO ».
Vous trouverez sur Internet d'autres informations sur les requêtes SQL.
J'aime bien cette adresse : http://www.1keydata.com/fr/sql/syntaxe-sql.php.
Attention, toutes les commandes SQL ne sont pas compatibles avec Excel.
La fonction DataFRomSQL utilise des liaisons tardives, vous n’avez donc pas besoin d’installer la bibliothèque « Microsoft ActiveX Data Objects 6.0 Library » dans votre projet.
Un autre exemple :
Reprenons notre cas où la mémoire « Données » contient les données de trois tableaux fusionnés et « Synthese » la liste de synthèse.
On souhaite ajouter 2 points aux notes inférieures à 10, sans dépasser 10, uniquement pour les élèves dont la moyenne est inférieure à 10.
Voici comment nous allons procéder :
- Ajouter une colonne à « Données » qui contiendra l'indexation des éléments (vous allez comprendre pourquoi ci-dessous).
- Boucler sur les élèves de la liste « Synthese » et si la moyenne (colonne 4) est inférieure à 10 alors :
- Faire une requête SQL pour récupérer dans une mémoire temporaire les notes inférieures à 10 de l'élève identifié par son nom (colonne 1) et son prénom (colonne 2).
- Pour chaque ligne de la liste obtenue :
- Récupérer l'indexation de l'évaluation contenue dans la colonne 6.
- Ajouter 2 points à la note (colonne 3) dans la limite de 10.
- Mettre à jour l'élément dans « Données » puisque l'on connait sa position (par l'indexation).
- Supprimer la colonne d'indexation dans « Données » qui n'est plus utile.
- Actualiser le tableau de synthèse par la requête SQL que nous connaissons.
- Afficher la synthèse dans le tableau structuré.
Ce qui donne :
' Ajoute une indexation à la mémoire Données:
Données = DataAddColumns(Données, 1)
For i = 1 To DataSize(Données)
Données(i, 6) = i
Next i
' Boucle sur chaque ligne de la mémoire Synthese:
For Eleve = 1 To DataSize(Synthese)
' Si la moyenne est < 10:
If Synthese(Eleve, 4) < 10 Then
' Requête SQL pour obtenir les notes < 10 de l'élève:
Rt = DataFromSQL(Données, "*", "WHERE C3 < 10 AND C1 = '" & Synthese(Eleve, 1) & "' AND C2 = '" & Synthese(Eleve, 2) & "'")
' Boucle sur chaque note:
For E = 1 To DataSize(Rt)
' Récupère l'indexe de l'évaluation:
i = Rt(E, 6)
' Ajouter 2 points à la note sans dépasser 10:
Note = Rt(E, 3) + 2
If Note > 10 Then Note = 10
' Mise à jour de la note dans la mémoire Données
Données(i, 3) = Note
Next E
End If
Next Eleve
' Supprime de la mémoire Données l'indexation:
Données = DataAddColumns(Données, -1)
' Actualise le tableau de synthèse:
Synthese = DataFromSQL(Données, "C1, C2, Count(C3), AVG(C3)", "GROUP BY C1, C2 ORDER BY C1, C2")
' Et l'affiche:
Call DataToTable(Synthese, [TS_Synthese], DataToTable_Replace)
Un dernier exemple pour vous convaincre de l'utilité des requêtes SQL :
À partir de cette liste, je souhaite une synthèse des 5 départements qui ont le plus de communes.
Dim Données
Données = DataFromRange([TS_CP])
Données = DataFromSQL(Données, "TOP 5 C5, C7, C4, Count(C5)", "WHERE C5>'' GROUP BY C5, C4, C7 ORDER BY Count(C5) DESC")
Call DataToTable(Données, [CP_Synthese], DataToTable_Replace)
V. Conclusion▲
L’exercice purement théorique repris dans cette documentation donne une idée de ce qu’il est possible de faire pour gérer des données volumineuses dépassant les capacités d'une feuille de calcul d'Excel.
Je suis bien conscient que vous obtenez le même résultat avec d’autres techniques de programmation (ou en découpant vos données en plusieurs paquets) et que je ne vous présente rien de révolutionnaire. Mon souhait est juste de vous faire découvrir une autre approche, avec ses avantages et ses inconvénients.
Toutefois, si le sujet vous intéresse, vous trouverez dans le fichier joint le module « DataXXL » qui contient les fonctions étudiées. Vous pouvez vous en inspirer pour les améliorer, les compléter, en écrire de nouvelles : n’hésitez pas à nous en faire profiter dans cette discussion.
Et pour plus d’informations sur les tableaux structurés, je vous invite à consulter cette documentation : Fonctions en VBA pour gérer les Tableaux Structurés d’Excel.
Bonne programmation.
Laurent OTT.
2025.
VI. Remerciements▲
Je remercie User pour sa relecture, ses apports et ses conseils, et f-leb pour sa correction orthographique.
Ainsi que toute l’équipe de Developpez.com qui participe à la maintenance du site.
VII. Le fichier joint▲
Le fichier DataXXL.xlsm contient les fonctions présentées ici dans le module « DataXXL ».
VIII. Annexe 1 : Liste des fonctions▲
IX. Annexe 2 : L'algorithme de classement Quick Rank▲
La fonction DataSort n’utilise pas le célèbre algorithme de tri rapide Quick Sort inventé par Charles Antony Richard Hoare, car cet algorithme a l’inconvénient de ne pas conserver l’ordre d’origine des données lorsqu’il y a des égalités, contrairement à ce que fait Excel.
Conserver l’ordre d’origine des données (soit un tri dit « stable ») est bien pratique pour faire un tri sur plusieurs colonnes, par exemple pour trier par nom de famille et prénom.
C'est pourquoi j’ai développé l’algorithme de tri stable que j'ai nommé « Quick Rank ».
Son principe de base est de ne pas déplacer les données, mais d’indiquer dans une mémoire la position de l’élément supérieur suivant (soit une liste chaînée). Bien entendu, l’insertion d’un élément nécessite la mise à jour de cette indexation.
Pour reconstituer un tri croissant, il convient de partir de l’élément le plus petit, puis de passer à la position de l’élément suivant, ainsi de suite jusqu’à la fin de la liste.
Pour un meilleur temps de traitement lorsqu’il y a des égalités, une première amélioration consiste à mémoriser également la position du prochain élément supérieur différent. Cela permet alors de sauter tous les éléments égaux pour se retrouver directement à la position désirée.
Mais impossible de rivaliser avec Quick Sort sans une amélioration décisive qui consiste à gérer parallèlement une liste sans doublon des éléments déjà triés, qui permet via une recherche dichotomique d’atteindre rapidement l’élément le plus proche de la position recherchée.
Finalement, Quick Rank n’est pas ridicule face à Quick Sort pour trier une liste idéalement aléatoire, là ou Quick Sort excelle, et est particulièrement efface sur les listes partiellement triées ou comportant de nombreuses égalités (cas souvent rencontré en pratique), tout en conservant l’ordre d’origine des données.
Voyons cela avec un exemple de nombres à trier...
Étape d'initialisation d'après les deux premières valeurs :
Identification du minimum (en vert) et du maximum (en orange), le suivant du minimum (15) est l'indice 2.
Le suivant du maximum (75) n'existe pas, il est noté -1.
Analyse de l'indice 3 qui vaut 20.
Le suivant du minimum, l'indice 2 vaut 75. C'est plus grand que la valeur à classer qui vaut 20, donc le suivant du minimum devient l'indice 3 et le suivant de l'indice 3 est l'ancien suivant du minimum = 2.
Les flèches représentent le sens de la lecture des indices pour avoir l'ordre de classement de ces trois éléments, en partant du minimum.
Analyse de l'indice 4 qui vaut 18.
Le suivant du minimum, l'indice 3 vaut 20. C'est plus grand que la valeur à classer qui vaut 18, donc le suivant du minimum devient l'indice 4 et le suivant de l'indice 4 est l'ancien suivant du minimum = 3.
Analyse de l'indice 5 qui vaut 20.
Le suivant du minimum, l'indice 4 vaut 18. Donc on continue la lecture jusqu'à atteindre une valeur plus grande que 20. Soit son suivant, l'indice 3 qui vaut 20, puis son suivant, l'indice 2 qui vaut 75.
On a atteint un plus grand donc le suivant de l'indice 5 est 2. Et on met à jour le dernier indice analysé, l'indice 3, pour indiquer que son suivant est l'indice 5.
En partant du minimum (à l'indice 1) on lit les indices 4, puis 3, puis 5, puis 2.
Analyse de l'indice 6 qui vaut 5.
C'est un nouveau minimum donc son suivant est l'indice de l'ancien minimum = 1.
Analyse de l'indice 7 qui vaut 99.
C'est un nouveau maximum. Donc on met à jour l'ancien maximum, l'indice 2, pour indiquer que son suivant est l'indice 7. Le suivant du nouveau maximum est -1.
Analyse de l'indice 8 qui vaut 5.
Comme d'habitude, on part du minimum en lisant les indices suivants pour trouver une valeur plus grande que 5.
Et on met à jour le dernier indice lu pour indiquer que son suivant est l'indice 8, et le suivant de l'indice 8 est l'ancien suivant du dernier indice lu.
Analyse de l'indice 9 qui vaut 99.
La valeur est égale au maximum. L'indice devient le nouveau maximum donc son indice suivant est -1. Et on met à jour l'indice suivant de l'ancien maximum qui devient l'indice 9.
Analyse de l'indice 10 qui vaut 20.
Comme d'habitude, on part du minimum en lisant les indices suivants pour trouver une valeur plus grande que 20.
La lecture des indices et de leur valeur donne : 6 (5) -> 8 (5) -> 1 (15) -> 4 (18) -> 3 (20) -> 5 (20) -> 2 (75).
Et on met à jour le dernier indice lu pour indiquer que son suivant est l'indice 10 et le suivant de l'indice 10 est l'ancien suivant du dernier indice lu.
Analyse de l'indice 11 qui vaut 20.
La lecture des indices et de leur valeur donne : 6 (5) -> 8 (5) -> 1 (15) -> 4 (18) -> 3 (20) -> 5 (20) -> 10 (20) -> 2 (75).
Et on met à jour le dernier indice lu (10) pour indiquer que son suivant est l'indice 11 et le suivant de l'indice 11 est l'ancien suivant du dernier indice lu = 2.
UN COUP DE POUCE
Sur le même principe, on ajoute une mémoire annexe qui contient l'indice du dernier élément de même valeur.
La lecture des indices pour avoir l'ordre de classement est identique mais l'indice du « dernier égal » permet de trouver plus rapidement l'indice de l'élément dont la valeur est supérieure lorsqu'il y a plusieurs éléments de même valeur. Comme dans le dernier exemple que nous venons de voir où la valeur 20 est présente plusieurs fois.
Reprenons nos exemples en ajoutant la connaissance de l'indice du dernier élément de valeur égale :
Analyse de l'indice 3 qui vaut 20.
Lecture de l'indice suivant du dernier égal : 1=2 (75).
Analyse de l'indice 4 puis de l'indice 5 :
Analyse de l'indice 6 qui vaut 20 également :
Lecture de l'indice suivant du dernier égal : 1=4 (18) -> 4=3 (20) -> 5=2 (75).
Analyse de l'indice 12 qui vaut 88 :
Lecture de l'indice suivant du dernier égal : 8=1 (15) -> 1=4 (18) -> 4=3 (20) -> 11=2 (75) -> 2=7 (99).
À la lecture, on saute donc plusieurs étapes puisque l'on se branche directement de l'indice 3 à l'indice 2 sans avoir à passer par les indices 5, 6, 10 et 11.
Astuce : La lecture de l'indice suivant du dernier égal donne une liste sans doublon.
AMÉLIORATION : RECHERCHE DICHOTOMIQUE
Reprenons le dernier classement analysé, avec l'ajout du coup de pouce :
Lecture de l'indice suivant du dernier égal : 8=1 (15) -> 1=4 (18) -> 4=3 (20) -> 11=2 (75) -> 2=7 (99).
On obtient une liste sans doublon de 6 éléments :
Pour ajouter dans la liste à classer un nouvel élément qui vaut 88, une recherche dichotomique dans cette liste sans doublon donne rapidement que la plus proche valeur inférieure ou égale à 88 est 75, soit l'indice 2.
L'analyse part donc directement de l'indice 2 pour trouver un élément dont la valeur est supérieure à 88.
Lecture de l'indice suivant du dernier égal : 2=7 (99).
L'avantage procuré par la recherche dichotomique est plus flagrant sur une grande liste à trier.
La liste sans doublon ne sera actualisée que de temps en temps, car elle consomme des ressources. Il faut donc trouver le juste équilibre entre le gain qu'elle apporte et le temps qu'elle prend à être générée.
Un pseudo code au format « pdf » : QuickRank.pdf.
Le module « QuickSort » du fichier joint présente une version personnalisée de l'algorithme QuickSort rendu stable, ce qui nécessite des traitements supplémentaires, et donc ne le rend pas aussi rapide que QuickRank (tests réalisés en VBA, un langage de programmation différent peut obtenir d'autres résultats).
