[XL-2007] Macro VBA - Création d'identifiants

Macro VBA - Création d'identifiants

Bonjour à tous,

Je suis débutant en VBA, je réalise une macro dans un but professionnel et peut-être pourrez vous m'aider sur mon problème actuel.

J'ai des tableaux contenant un ensemble de données statistiques sous Excel. Chaque donnée représente un indicateur statistique qui est déterminée selon une commune et l’attribut d'un critère.
                              Nombre de personnes
                   -20 ans         20-60 ans           + de 60 ans
Commune 1        10                40                         5
Commune 2        20                30                        30
L'ensemble de ces données sont contenues dans plusieurs tableurs, dont l'organisation interne peut être très différente. (Modification de l'orientation Lignes-Colonnes, etc.)

Dans le but de réorganiser ces données, je souhaite assigner un identifiant unique à chacune de ces donnée statistique, qui serait la combinaison d'un identifiant de l'indicateur, d'un identifiant de commune, et d'un identifiant de critère (ou plus précisément de son attribut).

Je désire donc remplacer toutes les cellules de mon tableau, afin de pouvoir générer ces identifiants. Dans l'absolu je souhaiterais donc arriver au tableau suivant :
                                 Nombre de personnes (id =1)
			-20 ans(id =1)		20-60 ans(id =2) 	+de 60ans (id =3)
Commune 1 (Id=1)		111			112			113
Commune 2 (Id=2)		121			122			123
Dans ce but, j'ai déjà crée une macro permettant d'attribuer un identifiant à chaque élément différent qui compose une donnée, quelque soit la forme de mon tableau. En résumé cela ressemble à ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Sub Macro1() Dim Monexcel As Application Dim Monclasseur As Workbook Dim Mafeuille As Worksheet Dim p As Integer Set Monexcel = New Excel.Application Monexcel.ReferenceStyle = xlR1C1 Set Monclasseur = ActiveWorkbook Set Mafeuille = Monclasseur.Worksheets(1) Set donnees1 = Application.InputBox("Selectionnez la plage souhaitée (sans les entêtes de ligne ou de colonnes).", Type:=8) donnees1.Interior.ColorIndex = 6 donnees1.Select p = 1 'Pour chaque cellule de la colonne For Each donnees1 In Selection 'récupération de la valeur donnees1.Value = p p = p + 1 Next End Sub
Dans une macro plus détaillée, je repète alors cette action autant de fois que nécessaire.

Pour le moment je n'ai donc que le tableau suivant :
                                 Nombre de personnes (id =1)
			-20 ans(id =1)		20-60 ans(id =2) 	+de 60ans (id =3)
Commune 1 (Id=1)		10			40			5
Commune 2 (Id=2)		20			30			30
Désormais, je voudrais attribuer à l'ensemble de mes données statistiques leur identifiant final, qui correspond donc au croisement des cellules des colonne et des lignes auxquelles viennent d'être associées les identifiants de commune, d'indicateur, et d'attribut de critères.

De fait, je cherche à passer du tableau 2 au tableau 3.

Essentiel : A noter que comme chaque tableau peut être organisé différemment, je ne peut pas sélectionner de cellule de façon absolue. Il me faut donc faire référence aux cellules qui viennent d'être précédemment modifiées.

Ma question est donc là. Comment faire référence à ces cellules ?
J'ai pensé à la faire référence aux cellules les plus proches de chaque plages concernées (Les plages correspondant à l'indicateur, aux communes, et aux attributs de critère), mais je ne vois pas comment cela est possible.

Merci à ceux qui auront lu mon pavé, et ceux qui m'aideront.
En espérant avoir été assez clair, car vu de l'extérieur, cela ne doit pas être évident. :oops:

Bonjour,
Quand je regarde ton code, après mis en couleur la plage sélectionnée par l'utilisateur, tu écrases les données de cette sélection en mettant un nombre représentant la valeur de la variable P.
De plus à quoi bon faire un donnees1.Select alors que l'utilisateur vient de faire une sélection. Tu peux supprimer cette ligne.
As-tu tester cette procédure Macro1 et répond-elle à tes attentes ?
Parce-que, si j'ai bien compris, tu veux créer des plages nommées ?

Info-bulle d'identifiant unique dans une matrice 3D dans Excel

Sheikyo, dans chaque étape des tableaux, on a pris la valeur souhaitée de la cellule D4 :

Tableau n° 1 : Range("D4").Value = 30 nombres de personnes dans la commune 2 de + de 60 ans.
Tableau n° 2 : Range("D4").Value = 123 soit la concaténation des indices d'une matrice à trois dimensions colorées selon la formule

Code:

identifiant de l'indicateur * 100 + identifiant de commune * 10 + attribut identifiant de critère

Tableau n° 3 : Range("D4").Value 4 = 30 ' Retour à la valeur initiale

Citation:

Envoyé par sheikyo

De fait, je cherche à passer du tableau 2 au tableau 3.

Pourquoi effacer la valeur 30 de D4 du Tableau n° 1 afin d'y mettre l'identifiant unique 123 dans le tableau n° 2 pour finalement revenir à la valeur initiale 30 dans le tableau n° 3 ?

On pourrait mettre l'identifiant unique en info-bulle comme cela on ne modifie pas la valeur initiale de D4 parce qu'elle est également la cible souhaitée dans le tableau final n° 3.

Il reste à qualifier les en-têtes de colonnes et de rangées pour indicer chaque dimension de la matrice en 3D :
Pour l'en-tête de colonne principale :

B1 : "nombres de personnes" aurait pour info-bulle l'identifiant de l'indicateur "id = 1"

Pour l'en-tête de rangées :

A3 : "commune 1" aurait pour info-bulle l'identifiant de commune "id = 1"
A4 : "commune 2" aurait pour info-bulle l'identifiant de commune "id = 2"

Pour l'en-tête de colonne secondaire :

B2 : "- de 20 ans" aurait pour info-bulle l'attribut identifiant de critère "id = 1"
C2 : "20-60 ans" aurait pour info-bulle l'attribut identifiant de critère "id = 2"
D2 : "+ de 60 ans" aurait pour info-bulle l'attribut identifiant de critère "id = 3"

Citation:

Envoyé par sheikyo

Comment faire référence à ces cellules ?

La référence entre la valeur de la statistique et l'identifiant unique de la matrice en 3D se fait directement en survolant la cellule avec le pointeur de la souris pour faire apparaître l'info-bulle.
___________

Si la discussion est résolue, vous pouvez cliquer sur le bouton :resolu:

En bas de ce message s'il vous a apporté des éléments de réponse pertinents, pensez également à voter en cliquant sur le bouton vert http://www.developpez.net/forums/ima.../vote1left.gif ci-dessous.

Merci beaucoup pour ces premières réponses.

Citation:

Envoyé par corona

Bonjour,
Quand je regarde ton code, après mis en couleur la plage sélectionnée par l'utilisateur, tu écrases les données de cette sélection en mettant un nombre représentant la valeur de la variable P.
De plus à quoi bon faire un donnees1.Select alors que l'utilisateur vient de faire une sélection. Tu peux supprimer cette ligne.
As-tu tester cette procédure Macro1 et répond-elle à tes attentes ?
Parce-que, si j'ai bien compris, tu veux créer des plages nommées ?

Merci de la précision. J'ai conscience que ma macro est loin d'être optimisée. :oops: Je l'ai testée et elle a l'avantage de marcher, c'est déjà pas mal me concernant. Pour le moment donc,oui, elle répond à mes attentes.
Je cherche effectivement à renommer mes plages, mais cela va sans doute un peu plus loin (voir-ci dessous).

Citation:

Envoyé par MattChess

Pourquoi effacer la valeur 30 de D4 du Tableau n° 1 afin d'y mettre l'identifiant unique 123 dans le tableau n° 2 pour finalement revenir à la valeur initiale 30 dans le tableau n° 3 ?

En fait le tableau 3 n'est pas le tableau final. Ce que je souhaite comme résultat correspond plutôt au tableau 2, et pour l'instant je n'en suis qu'a une étape intermédiaire qui est le tableau 3.

Concernant l'info-bulle, effectivement cela pourrait-être plus correct et un peu plus clair. Étant débutant je procède par étape dans mon code. Mon idée était déjà de pouvoir remplacer les cellules existantes par les identifiants souhaités, et de s'occuper de l'optimisation du code par la suite.

Néanmoins, pour aller un peu plus loin ces données doivent ensuite être intégrées dans un SGBD. Pour que cette intégration soit possible j'aimerais en fait au final créer quatre tableaux ou il y aurait dans les entêtes de colonnes :

Indicateur -- id_ indicateur
Communes -- id_commune
Attribut -- id_attribut
Donnes -- id_donnees

D'où mon idée de départ de modifier les valeur de mon tableau d'origine, et de créer ces nouvelles tables ensuite par comparaison.

Le problème majeur étant donc de pouvoir "automatiser la formule pour attribuer l'identifiant final de chaque donnée :

Code:

identifiant de l'indicateur * 100 + identifiant de commune * 10 + attribut identifiant de critère

Et ce, quelque soit l'emplacement de mes identifiants, les tableaux n'ayant pas tous la même structure d'organisation. :?
D’où le besoin de faire référence aux plages de cellules tout juste modifiées.

Encore merci à ceux qui prennent de leur temps. Cela m'est d'une aide inestimable.

Intersection de données - Création d'identifiant

Bon pour upper et simplifier mon sujet,
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Sub Macro1() Dim Monexcel As Application Dim Monclasseur As Workbook Dim Mafeuille As Worksheet Dim donnees1 As Range Dim donnees2 As Range Dim donnees3 As Range Dim donnees4 As Range Set Monexcel = New Excel.Application Monexcel.ReferenceStyle = xlR1C1 Set Monclasseur = ActiveWorkbook Set Mafeuille = Monclasseur.Worksheets(1) Set donnees1 = Application.InputBox("selectionnez la plage1 (sans les entêtes de colonnes).", Type:=8) Set donnees2 = Application.InputBox("selectionnez la plage 2 (sans les entêtes de colonnes).", Type:=8) Set donnees3 = Application.InputBox("selectionnez la plage 3 (sans les entêtes de colonnes).", Type:=8) Set donnees4 = Application.Intersect(donnees1, donnees2, donnees3) For Each donnees4 In Selection donnees4.Value = ???? Next
Je souhaiterais donner comme valeur pour chaque cellule à l'intersection des trois plages précédemment sélectionnée (à la souris), la concaténation des valeurs qui se trouvent dans ces plages.

Je tente actuellement d'utiliser la méthode Intersect, mais je ne pense pas être sur la bonne voie.

La syntaxe de la première ligne en orange ne semble pas bonne, et je vois pas comment calculer mes valeurs par la suite.

:?

Indexation des données de tableaux à en-têtes différentes en Excel VBA

1. Exposé du problème

Sheikyo, avouez que la comparaison entre votre premier message et celui de n° #4 montre une certaine (r)évolution dans l'exposé du problème :

Citation:

Envoyé par sheikyo

De fait, je cherche à passer du tableau 2 au tableau 3.

Citation:

Envoyé par sheikyo

En fait le tableau 3 n'est pas le tableau final. Ce que je souhaite comme résultat correspond plutôt au tableau 2, et pour l'instant je n'en suis qu'a une étape intermédiaire qui est le tableau 3.

Il aurait été plus compréhensible que l'étape intermédiaire courante soit entre l'étape initiale et l'étape finale c-a-d l'objectif recherché.

2. La macro fournie ne correspond pas l'étape courante

Une autre difficulté pour suivre la discussion est que dans l'étape intermédiaire du tableau n° 3, on ne voit que les entêtes qui ont été indicées par un n° id. Les valeurs statistiques restent inchangées.

Hors dans la Macro1() --en passant, vous pourriez trouver un nom plus significatif--, on trouve la demande de saisie d'un Range qui concerne les valeurs statistiques à l'exclusion des entêtes.

Code:

Set donnees1 = Application.InputBox("Selectionnez la plage souhaitée (sans les entêtes de ligne ou de colonnes).", Type:=8)

Il faudrait fournir la macro qui correspond à l'étape intermédiaire courante annotant les entêtes.

3. L'indexation doit être sans perte de données

Citation:

Envoyé par sheikyo

D'où mon idée de départ de modifier les valeur de mon tableau d'origine, et de créer ces nouvelles tables ensuite par comparaison.

Si on efface la valeur de la cellule D4, c'est que l'on n'en pas plus besoin. On peut penser qu'au final, l'utilisateur s'intéresse à la valeur de la statistique et non à un système d'indexation. Cela signifie qu'on ne peut pas modifier, effacer, écraser l'information clé.

Il faut trouver un moyen pour indexer l'information sans perte de données.
Pour visualiser la troisième dimension de l'indexation, on avait proposé de mettre l'identifiant dans une info-bulle. On pourrait tout aussi bien créer une nouvelle feuille d'identifiant pour chaque feuille de données. Dans ce cas, la référence à la valeur statistique est la même référence que celle de l'identifiant en changeant le nom de feuille.

Dans ce qui suit, on développe l'idée selon laquelle on n'a même pas besoin d'écrire ces indices d'indexation dans une info-bulle ou une nouvelle feuille si on arrive à spécifier les fonctions de conversion entre d'une part le n° id et d'autre part la rangée et la colonne de la valeur statistique selon le type du tableau.

4. Le cœur du problème est la reconnaissance du type des entêtes

Citation:

Envoyé par sheikyo

Le problème majeur étant donc de pouvoir "automatiser la formule pour attribuer l'identifiant final de chaque donnée :

Le cœur du problème est l'indexation des entêtes : une fois que l'on a identifié l'organisation des n° id par colonne primaire et secondaire ainsi que par rangée, la distribution des indices correspondant à chaque valeur statistique est de la combinatoire obéissant à une règle de priorité selon le type des entêtes.

5. Exemple de reconnaissance du type des entêtes sur deux tableaux typiques ?

Citation:

Envoyé par sheikyo

Et ce, quelque soit l'emplacement de mes identifiants, les tableaux n'ayant pas tous la même structure d'organisation.

Donnez deux tableaux simplifiés mais ayant des organisations différentes.

Indiquez comment vous allez identifier l'en-tête de colonne principale et l'en-tête de colonnes secondaire ainsi que l'en-tête de rangée pour le type d'organisation d'en-tête à 1 rangée * 2 colonnes.
Pour le second tableau construit selon un autre type d'organisation, indiquez comment vous allez identifier l'en-tête de rangée puis de colonne si telle est son organisation d'en-tête à 1 rangée * 1 colonne.

S'il y a d'autres types d'organisation, listez les afin d'élaborer l'algorithme de reconnaissance des structures des tableaux puis de l'optimiser.

Etant donné que la structure d'organisation des tableaux est variable, il va falloir les identifier selon les types des entêtes. Le problème revient donc à identifier le type de chaque entête : colonne primaire, colonne secondaire ou unique, rangée primaire, rangée secondaire ou unique pour des organisations acceptant de un à deux niveaux d'en-tête.

S'il y a plus de deux niveaux d'en-tête, déclinez l'algorithme de reconnaissance des entêtes en prenant en compte leurs niveaux supplémentaires.

Décrire chaque organisation de structure de tableau par un type. Donnez les fonctions de conversion de n° id NNNN vers le n° de rangée row et le numéro de colonne col soit en style de référence de coordonnées R1C1 : RrowCcol et réciproquement.

6. Usage de l'indexation et fonctions de conversion vers et depuis le n° id

Citation:

Envoyé par sheikyo

pour aller un peu plus loin ces données doivent ensuite être intégrées dans un SGBD. Pour que cette intégration soit possible j'aimerais en fait au final créer quatre tableaux ou il y aurait dans les entêtes de colonnes :

Indicateur -- id_ indicateur
Communes -- id_commune
Attribut -- id_attribut
Donnes -- id_donnees

C'est cette vision qu'il faut développer.

Montrez l'utilisation finale de l'indexation après attribution d'un n° id à chaque valeur statistique sur deux tableaux ayant des organisations différentes en gardant à l'esprit que le but d'une indexation est d'accès à la l'information clé, à savoir la valeur statistique, ici à droite du symbole d'affectation "=".

J'ai l'index 123 dans le premier tableau qui me permet d'accéder à la cellule D4 = R4C4 = 30 ans
J'ai l'index NNNN dans un second tableau qui me permet d'accéder à la cellule RrowCcol = value

Qu'est-ce que vous allez faire concrètement du premier index 123 par rapport au second NNNN ? Pourquoi ? Comment ?
Par ces questions, on demande au système d'indexation d'être suffisamment générique pour accepter de représenter des organisations différentes que l'on a au préalable clairement recensées et identifiées.

7. Maquette pour l'indexation de données de tableaux à en-têtes différentes

Notez les constantes en début de module qui décrivent les types de tableaux potentiellement à indexer. Seuls les deux premiers types de tableaux -- dont le type d'en-tête header1Row_2Col (1 en-tête de rangée et deux en-têtes de colonne primaire puis secondaire) -- ont été développés. Le reste donne un canevas pour des futures évolutions.

VBE menu "Insérer" > "Module"
Dans les propriétés du Module1, renommer le en ModIdx.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 Option Explicit ' Indexation de données de tableaux à en-têtes différentes Public Const strTitleApp = "Table header indexer" ' by MattChess, August 10, 2011 ' Type d'organisation selon la structure de chaque entête du tableau Public Const header1Row_1Col As Byte = 1 ' Une Rangée d'en-tête * une Colonne d'en-tête Public Const header1Row_2Col As Byte = header1Row_1Col + 1 ' Une Rangée * (Colonne principale + Colonnes secondaires) Public Const header2Row_1Col As Byte = header1Row_2Col + 1 ' Deux Rangées d'en-tête * une Colonne d'en-tête Public Const header2Row_2Col As Byte = header2Row_1Col + 1 ' Deux Rangées d'en-tête * deux Colonnes d'en-tête ' ... Décrire les autres organisations de structure de tableaux Type TabType ' Type tableau = nom, organisation de l'en-tête, plage de cellules Name As String ' Nom du tableau TypeTab As Byte ' Type du tableau parmi header1Row_1Col, header1Row_2Col, ... RngTab As Range ' Plage de cellules du tableau y compris les en-têtes RngHeaderRow As Range ' Plage des en-têtes de rangées automatiquement calculée RngHeaderCol As Range ' Plage des en-têtes de colonnes automatiquement calculée RngHeaderCorner As Range ' Plage du coin supérieur gauche à l'intersection des en-têtes End Type ' Clé d'indexation basée sur le nombre maximum de rangées et de colonnes d'un tableau Const nbrMaxRow As Long = 10, nbrMaxCol As Long = 10 ' doivent être une puissance de la base 10 Public tabPeopleAgePlace As TabType, tabSimple As TabType ' Exemple de tableaux à structure différente Sub TableHeaderIndexer() ' Point d'entrée de la maquette ' Colonne primaire "Nombre de personnes" au-dessus des colonnes secondaires "-20 ans", "20-60", "+60" TabInit "Nbr Personnes dans Commune par Age", header1Row_2Col, Range("A1:D4"), tabPeopleAgePlace ' Autre tableau ayant une seule entête de colonne et de rangée TabInit "Autre statistique", header1Row_1Col, Range("B6:E8"), tabSimple End Sub Sub TabInit(ByVal strName As String, ByVal typeOfTab As Byte, ByVal rngOfTab As Range, _ ByRef tableau As TabType) ' Initialise la structure d'un tableau, c-a-d sa description With tableau .Name = strName .TypeTab = typeOfTab Set .RngTab = rngOfTab Set .RngHeaderRow = RangeHeaderRow(tableau) Set .RngHeaderCol = RangeHeaderCol(tableau) Set .RngHeaderCorner = RangeHeaderCorner(tableau) End With Debug.Print "La structure du tableau """ + tableau.Name + """ a été décrite." End Sub ' Indexation de la cellule de la rangée indRow et de la colonne indCol dans le tableau Function IdFromRowCol(ByVal indRow As Long, ByVal indCol As Long, tableau As TabType) As Long Dim rngCell As Range Set rngCell = Cells(indRow, indCol) IdFromRowCol = IdFromCell(rngCell, tableau) End Function Function IdFromCell(ByVal rngCell As Range, tableau As TabType) As Long ' Indexation de cellule Dim rngDataNoHeader As Range IdFromCell = 0 ' Not found If Intersect(tableau.RngTab, rngCell) Is Nothing Then Exit Function Set rngDataNoHeader = RangeDataNoHeader(tableau) If Intersect(rngDataNoHeader, rngCell) Is Nothing Then IdFromCell = IdHeaderFromCell(rngCell, tableau) ' N° id dans en-tête Else IdFromCell = IdDataFromCell(rngCell, tableau) ' N° id dans donnée statistique End If End Function Function IdHeaderFromCell(ByVal rngCell As Range, tableau As TabType) As Long ' Indexation d'en-tête IdHeaderFromCell = 0 ' Cellule hors zone With tableau Select Case .TypeTab Case header1Row_1Col If Not Intersect(rngCell, .RngHeaderCorner) Is Nothing Then Exit Function ' Dans le coin supérieur gauche If Intersect(rngCell, .RngHeaderCol) Is Nothing Then IdHeaderFromCell = rngCell.Row - .RngHeaderRow.Row + 1 ' Unique rangée d'en-tête Else IdHeaderFromCell = rngCell.Column - .RngHeaderRow.Column ' Unique colonne d'en-tête End If Case header1Row_2Col If Not Intersect(rngCell, .RngHeaderCorner) Is Nothing Then Exit Function ' Dans le coin supérieur gauche If Intersect(rngCell, .RngHeaderCol) Is Nothing Then IdHeaderFromCell = rngCell.Row - .RngHeaderRow.Row + 1 ' Unique rangée d'en-tête ElseIf Intersect(rngCell, .RngTab.Rows(1)) Is Nothing Then IdHeaderFromCell = rngCell.Column - .RngHeaderRow.Column ' Colonne secondaire d'en-tête Else IdHeaderFromCell = 1 ' Colonne primaire d'en-tête End If Case header2Row_1Col IdHeaderFromCell = 0 ' Extraire le n° id selon la rangée et la colonne dans cet autre type d'organisation Case header2Row_2Col IdHeaderFromCell = 0 ' Idem Case Else Warning "1000: Type de tableau inconnu dans IdHeaderFromCell": Stop End Select End With End Function Function IdDataFromCell(ByVal rngCell As Range, tableau As TabType) As Long ' Indexation de donnée Dim rngColSecondary As Range IdDataFromCell = 0 With tableau Select Case .TypeTab Case header1Row_1Col ' indRow, indCol -> n° id rangée n° id colonne IdDataFromCell = _ IdHeaderFromCell(.RngHeaderRow.Rows(rngCell.Row - .RngHeaderRow.Row + 1), tableau) * nbrMaxRow + _ IdHeaderFromCell(.RngHeaderCol.Columns(rngCell.Column - .RngHeaderCol.Column + 1), tableau) Case header1Row_2Col ' indRow, indCol -> n° id colonne primaire n° id rangée n° id colonne secondaire Set rngColSecondary = Intersect(.RngHeaderCol.Columns(rngCell.Column - .RngHeaderCol.Column + 1), _ .RngTab.Rows(2)) If rngColSecondary Is Nothing Then Exit Function IdDataFromCell = _ IdHeaderFromCell(.RngHeaderCol.Columns(1), tableau) * nbrMaxRow * nbrMaxCol + _ IdHeaderFromCell(.RngHeaderRow.Rows(rngCell.Row - .RngHeaderRow.Row + 1), tableau) * nbrMaxRow + _ IdHeaderFromCell(rngColSecondary, tableau) Case header2Row_1Col IdDataFromCell = 0 ' Extraire le n° id selon la rangée et la colonne dans cet autre type d'organisation Case header2Row_2Col IdDataFromCell = 0 ' Ditto Case Else Warning "1100: Type de tableau inconnu dans IdDataFromCell": Stop End Select End With End Function Sub RowColFromId(ByVal idCell As Long, tableau As TabType, _ ByRef indRow As Long, ByRef indCol As Long) Dim rngCell As Range Set rngCell = RangeFromId(idCell, tableau) If rngCell Is Nothing Then indRow = 0: indCol = 0 ' idCell incorrect Else indRow = rngCell.Row indCol = rngCell.Column End If End Sub Function RangeFromId(ByVal idCell As Long, tableau As TabType) As Range Const prodRowColMax As Long = nbrMaxRow * nbrMaxCol Dim indRow As Integer, indCol As Integer, indColPrimary As Integer, indColSecondary As Integer Set RangeFromId = Nothing Select Case tableau.TypeTab Case header1Row_1Col ' n° id rangée n° id colonne -> indRow, indCol indRow = idCell \ nbrMaxRow + 1 If indRow <= 1 Then Exit Function indCol = idCell Mod nbrMaxRow + 1 If indCol < 1 Then Exit Function Set RangeFromId = tableau.RngTab.Cells(indRow, indCol) Case header1Row_2Col ' n° id colonne primaire n° id rangée n° id colonne secondaire -> indRow, indCol indColPrimary = idCell \ prodRowColMax If indColPrimary <> 1 Then Exit Function indRow = (idCell Mod prodRowColMax) \ nbrMaxRow + 2 If indRow <= 2 Then Exit Function indColSecondary = idCell Mod nbrMaxRow + 1 If indColSecondary < 1 Then Exit Function Set RangeFromId = tableau.RngTab.Cells(indRow, indColSecondary) Case header2Row_1Col Set RangeFromId = Nothing ' Extraire le n° de rangée de l'idCell dans cet autre type d'organisation Case header2Row_2Col Set RangeFromId = Nothing ' Idem Case Else Warning "1200: Type de tableau inconnu dans RangeFromId": Stop End Select End Function Function RowFromId(ByVal idCell As Long, tableau As TabType) As Long Dim rngCell As Range Set rngCell = RangeFromId(idCell, tableau) If rngCell Is Nothing Then RowFromId = 0 ' idCell incorrect Else RowFromId = rngCell.Row End If End Function Function ColFromId(ByVal idCell As Long, tableau As TabType) As Long Dim rngCell As Range Set rngCell = RangeFromId(idCell, tableau) If rngCell Is Nothing Then RowFromId = 0 ' idCell incorrect Else RowFromId = rngCell.Column End If End Function Function RangeDataNoHeader(tableau As TabType) As Range ' Range utile des données hors en-tête With tableau Select Case tableau.TypeTab Case header1Row_1Col Set RangeDataNoHeader = Intersect(.RngTab, .RngTab.Offset(1, 1)) Case header1Row_2Col Set RangeDataNoHeader = Intersect(.RngTab, .RngTab.Offset(2, 1)) Case header2Row_1Col Set RangeDataNoHeader = Nothing Case header2Row_2Col Set RangeDataNoHeader = Nothing Case Else Warning "1300: Type de tableau inconnu dans RangeDataNoHeader": Stop End Select End With End Function Function RangeHeaderRow(tableau As TabType) As Range ' Zone d'en-tête de rangées With tableau Select Case .TypeTab Case header1Row_1Col Set RangeHeaderRow = Intersect(.RngTab.Columns(1), .RngTab.Offset(1, 0)) Case header1Row_2Col Set RangeHeaderRow = Intersect(.RngTab.Columns(1), .RngTab.Offset(2, 0)) Case header2Row_1Col Set RangeHeaderRow = Nothing Case header2Row_2Col Set RangeHeaderRow = Nothing Case Else Warning "1400: Type de tableau inconnu dans RangeHeaderRow": Stop End Select End With End Function Function RangeHeaderCol(tableau As TabType) As Range ' Zone d'en-tête de colonnes With tableau Select Case .TypeTab Case header1Row_1Col Set RangeHeaderCol = Intersect(.RngTab.Rows(1), .RngTab.Offset(0, 1)) Case header1Row_2Col Set RangeHeaderCol = .RngTab.Range(Cells(1, 2), Cells(2, .RngTab.Columns.Count)) Case header2Row_1Col Set RangeHeaderCol = Nothing Case header2Row_2Col Set RangeHeaderCol = Nothing Case Else Warning "1500: Type de tableau inconnu dans RangeHeaderCol": Stop End Select End With End Function Function RangeHeaderCorner(tableau As TabType) As Range ' Zone du coin supérieur gauche With tableau Select Case .TypeTab Case header1Row_1Col Set RangeHeaderCorner = .RngTab.Cells(1, 1) Case header1Row_2Col Set RangeHeaderCorner = .RngTab.Range(Cells(1, 1), Cells(2, 1)) Case header2Row_1Col Set RangeHeaderCorner = Nothing Case header2Row_2Col Set RangeHeaderCorner = Nothing Case Else Warning "1600: Type de tableau inconnu dans RangeHeaderCorner": Stop End Select End With End Function Sub Warning(ByVal strMsg As String) ' Common error management Const lenErr = 4 ' Number of digits of the error code beginning the message If Err.Number <> 0 Then strMsg = strMsg + vbCrLf + "Error " + Str(Err.Number) + ": " + Err.Description End If MsgBox Mid(strMsg, lenErr + 3), vbExclamation, strTitleApp + " warning " + Left(strMsg, lenErr) End Sub
Dans cette maquette on n'a surtout pas cherché à optimiser. Vous pourrez le faire dans une seconde phase. Il faut d'abord valider le principe d'indexation pour des tableaux de structure différente.

8 Usage de la maquette Table header indexer

8.1 Description de la feuille Excel avec deux tableaux de données
Citation:
Envoyé par sheikyo
Code:

1 2 3 4 Nombre de personnes -20 ans 20-60 ans + de 60 ans Commune 1 10 40 5 Commune 2 20 30 30
Dans la feuille Excel, mettre le tableau de données suivant :
Commençons par les deux entêtes de colonne principale puis secondaire.
B1 : "Nombre de personnes". C'est l'en-tête de colonne dite principale.

B2 : " -20 ans" avec un espace blanc avant le moins. C'est la première colonne dite secondaire.
C2 : " 20-60 ans" avec un espace blanc avant l'âge
D2 : " + de 60 ans" C'est la dernière colonne dite secondaire.

Décrivons l'unique en-tête de rangée.
A3 : "Commune 1" C'est l'en-tête de la première rangée de données.
A4 : "Commune 2" C'est l'en-tête de la dernière rangée de données.

Finalement, entrons les données sur trois colonnes et deux rangées à partir de B3 jusqu'à D4.
Code:

1 2 3 4 B1 : Nbr personnes B2 à D2 : B C D ou " -20 ans" " 20-60 ans" " + de 60 ans" B3 à D3 : 10 40 5 B4 à D4 : 20 30 30
On a utilisé le séparateur blanc, pour indiquer un changement de colonne.
En B3, on a la valeur numérique 10. En D4, on a 30.

Sélectionner et copier la plage de cellules de A2 à D4, soit le tableau précédent à l'exception de sa première en-tête de colonne principale.
Coller dans la cellule B6.
Le tableau initial a été dupliqué une rangée dessous avec un décalage d'une colonne vers la droite.
Ce second tableau est donc de B6 à E8 avec les mêmes données de C7 à E8.

8.2 Structure du tableau tabPeopleAgePlace et indexation de ses en-têtes

Dans la fenêtre d'Exécution immédiate (Ctrl+G) du VBE d'Excel, copier-coller et valider par ENTER :

Code:

TableHeaderIndexer

La structure du tableau "Nbr Personnes dans Commune par Age" a été décrite.
La structure du tableau "Autre statistique" a été décrite.

On a indiqué en bleu foncé ce que la macro affiche dans la fenêtre d'Exécution immédiate.

Qu'appelle-t-on structure d'un tableau ?

Code:

? tabPeopleAgePlace.RngTab.Address

$A$1:$D$4
C'est la plage du tableau -- y compris les en-têtes --.

Code:

? tabPeopleAgePlace.TypeTab

2
Ce nombre 2 correspond au type d'en-tête header1Row_2Col.
Ce tableau est dit de seconde génération, c-a-d qu'il a une colonne d'en-tête principale " Nombre de personnes" au-dessus de colonnes d'en-tête secondaire qui affine l'en-tête principale en différents critères : -20 ans, de 20 à 60 ans, + de 60 ans.

Code:

? IdFromCell(Range("D1"), tabPeopleAgePlace)

1
Il y a une en-tête de colonne principale.

Comptons le nombre d'en-tête de colonnes :

Code:

? RangeHeaderCol(tabPeopleAgePlace).Rows.Count

2
Cela signifie qu'il y a deux rangées de colonnes d'en-têtes.

On peut déterminer le nombre de colonnes secondaires par :

Code:

? RangeHeaderCol(tabPeopleAgePlace).Columns.Count

3
Mais également par l'indexation de la dernière colonne d'en-tête.

Code:

? IdFromCell(Range("D2"), tabPeopleAgePlace)

3

Il n'a qu'une entête de rangée.

Code:

? RangeHeaderRow(tabPeopleAgePlace).Columns.Count

1
qui s'étend sur la plage suivante en nombre de rangées :

Code:

? RangeHeaderRow(tabPeopleAgePlace).Rows.Count

2
Le tableau a deux lignes "Commune 1" et "Commune 2".

Code:

? Range("A3").Value

Commune 1

Ce libellé est une en-tête de rangée indexée par le n° id :

Code:

? IdFromCell(Range("A3"), tabPeopleAgePlace)

1

Code:

? IdFromCell(Range("A4"), tabPeopleAgePlace)

2

Code:

? IdFromCell(Range("A5"), tabPeopleAgePlace)

0
Le n° id zéro signifie que la cellule A5 est en dehors du tableau.
Par mesure de protection, on ne veut pas pouvoir accéder au contenu des en-têtes pour ne pas les confondre avec les données statistiques.

Code:

? RangeFromId(IdFromCell(Range("A3"), tabPeopleAgePlace), tabPeopleAgePlace) Is Nothing

True

8.3 L'indexation des données du tableau tabPeopleAgePlace

Code:

? Range("D4").Value

30
Trente personnes dans la Commune 2 ont de + de 60 ans.

Code:

? IdFromCell(Range("D4"), tabPeopleAgePlace)

123
C'est le n° id que vous avez vous-même défini pour cette valeur de la cellule D4 dans ce tableau.

Code:

? RangeFromId(123, tabPeopleAgePlace).Value

30
C'est la valeur de la cellule D4 accédée par son n° id.

Code:

? IdFromRowCol(4, 3, tabPeopleAgePlace)

122
C'est le n° id de la cellule R4C3.

Code:

For Each cellule In RangeDataNoHeader(tabPeopleAgePlace): ? cellule & " -> " & IdFromCell(cellule, tabPeopleAgePlace): Next

10 -> 111
40 -> 112
5 -> 113
20 -> 121
30 -> 122
30 -> 123
Cette liste donne l'équivalence entre donnée et le n° id associé à partir du Range de la cellule.
Le balayage par le For Each se fait rangée par rangée.

Code:

For Each id in Array(111, 112, 113, 121, 122, 123): ? id & " -> " & RangeFromId(id, tabPeopleAgePlace).Value: Next

111 -> 10
112 -> 40
113 -> 5
121 -> 20
122 -> 30
123 -> 30
A partir du n° id, on peut remonter à la valeur de l'unique donnée indexée par ce n°.

La feuille peut être en lecture seule. On n'a rien écrit dans la feuille. Aucune valeur statistique n'a été écrasée par l'indexation puisqu'on peut retrouver la valeur des données statistiques à partir du n° id.

Code:

For Each id in Array(111, 112, 113, 121, 122, 123): ? id & " -> " & RangeFromId(id, tabPeopleAgePlace).Address(ReferenceStyle:=xlR1C1): Next

111 -> R3C2
112 -> R3C3
113 -> R3C4
121 -> R4C2
122 -> R4C3
123 -> R4C4
A partir du n° id, on établit son équivalence avec les coordonnées de la cellule indexée en style de référence R1C1.

8.4 L'indexation des données du tableau tabSimple d'un autre type simplifié

Prenons le second tableau en B6:E8 pour comparer le système d'indexation.

Le tableau tabSimple a les mêmes données que le tableau que tabPeopleAgePlace, mais elles sont décalées en B6:E8. tabSimple est dit de première génération avec son type d'en-tête header1Row_1Col, c-a-d une seule entête de rangée et de colonne.

Code:

For Each cellule In RangeDataNoHeader(tabSimple): ? cellule & " -> " & IdFromCell(cellule, tabSimple): Next

10 -> 11
40 -> 12
5 -> 13
20 -> 21
30 -> 22
30 -> 23

Code:

For Each id in Array(11, 12, 13, 21, 22, 23): ? id & " -> " & RangeFromId(id, tabSimple).Address(ReferenceStyle:=xlR1C1): Next

11 -> R7C3
12 -> R7C4
13 -> R7C5
21 -> R8C3
22 -> R8C4
23 -> R8C5
L'indexation des données se fait en remontant à l'indexation des entêtes plutôt que d'optimiser selon la rangée et la colonne de la valeur statistique.

Cela permet de focaliser le processus d'indexation sur les entêtes c-a-d sur la structure du tableau et non sur les coordonnées des valeurs des éléments du tableau.

9. Critique de la composition arithmétique des types d'en-tête dans le n° id

Si on arrive au limite d'une indexation numérique par composition arithmétique du n° id d'en-tête de colonne primaire puis le n° id d'en-tête de rangée et finalement le n° d'en-tête de colonne secondaire, on pourra opter pour une indexation à clé alphanumérique (salting) unique ou autre système à clé numérique unique (par fonction de hachage bijective) qui autoriserait éventuellement à remonter au contenu des en-têtes à partir de leur n° id unique.

Dans la version actuelle de la composition du n° id, la fonction d'indexation n'est qu'injective pour retrouver les données d'un tableau donné tout en bloquant l'accès au libellé des en-têtes via leur n° id. A partir du n° id = 1, on ne peut pas remonter au type de l'en-tête pour savoir s'il s'agit d'une en-tête de colonne primaire ou secondaire ou d'une en-tête de rangée.

Notez que le choix du n° id 123 pour la colonne D4 dans le tableau à deux en-têtes de colonne principale et secondaires impose que le nombre de colonnes secondaires ou rangées ne dépasse pas 10 sinon on peut perdre la propriété injective de l'indexation.

Code:

Const nbrMaxRow As Long = 10, nbrMaxCol As Long = 10 ' doivent être une puissance de la base 10

S'il y a des tableaux à plus de 10 (*) colonnes ou rangées, il faudra revoir la définition des constantes nbrMaxRow et nbrMaxCol selon les puissances de 10 afin de maintenir la bijection entre le n° id et la donnée correspondante. C'est une question d'encodage et de décodage des types d'en-têtes :

Extrait de l'encodage du n° id dans la fonction IdDataFromCell() selon le type du tableau header1Row_2Col :
Code:

1 2 3 4 5 6 7 8 Case header1Row_2Col ' indRow, indCol -> n° id colonne primaire n° id rangée n° id colonne secondaire Set rngColSecondary = Intersect(.RngHeaderCol.Columns(rngCell.Column - .RngHeaderCol.Column + 1), _ .RngTab.Rows(2)) If rngColSecondary Is Nothing Then Exit Function IdDataFromCell = _ IdHeaderFromCell(.RngHeaderCol.Columns(1), tableau) * nbrMaxRow * nbrMaxCol + _ IdHeaderFromCell(.RngHeaderRow.Rows(rngCell.Row - .RngHeaderRow.Row + 1), tableau) * nbrMaxRow + _ IdHeaderFromCell(rngColSecondary, tableau)
(*) : Notez dans la composition que l'on commence par le n° id de l'en-tête de colonne principale pour les centaines, puis le n° id de l'en-tête de rangée pour les dizaines et enfin le n° id de l'en-tête de colonne secondaire.

Extrait du décodage correspondant du n° id dans la fonction RangeFromId() :
Code:

1 2 3 4 5 6 7 8 Case header1Row_2Col ' n° id colonne primaire n° id rangée n° id colonne secondaire -> indRow, indCol indColPrimary = idCell \ prodRowColMax If indColPrimary <> 1 Then Exit Function indRow = (idCell Mod prodRowColMax) \ nbrMaxRow + 2 If indRow <= 2 Then Exit Function indColSecondary = idCell Mod nbrMaxRow + 1 If indColSecondary < 1 Then Exit Function Set RangeFromId = tableau.RngTab.Cells(indRow, indColSecondary)
Si vous êtes intéressé par un encodage / décodage du n° id en base 16 hexadécimal, précisez le.
Le n° id sera plus compact qu'en base 10 et pourra prendre en compte des tableaux de colonnes ou rangées pouvant atteindre jusqu'à 16 éléments (ou un multiple de 16).

10. Conclusion

On pourra développer la maquette TableHeaderIndexer() pour reconnaître davantage de structures de tableaux telles que le tableau à plusieurs en-têtes de colonnes principales qui se découpent en plusieurs colonnes secondaires au-dessus des données ou le tableau à plusieurs en-têtes de rangées principales qui se décomposent en plusieurs rangées secondaires. Si l'en-tête reflète une fusion de cellules, on pourra faire une indexation unique pour les cellules fusionnées c-a-d factorisées.

Soit une macro VBA peut analyser les tableaux de chaque feuille et syntaxiquement identifier automatiquement les entêtes des tableaux.
Soit l'utilisateur aura à sélectionner ou décrire la plage de chaque tableau -- y compris les en-têtes --.

Parce que les en-têtes sont les éléments clés pour l'indexation.
Les en-têtes sont la représentation de la structure de chaque tableau.
___________

Si la discussion est résolue, vous pouvez cliquer sur le bouton :resolu:

En bas de ce message s'il vous a apporté des éléments de réponse pertinents, pensez également à voter en cliquant sur le bouton vert http://www.developpez.net/forums/ima.../vote1left.gif ci-dessous.

Bonjour,

Réponse tardive néanmoins vu la qualité de la réponse, je me suis senti obligé.

J'avais entre-temps résolu mon problèmes par mes moyens, mais les questions que vous soulevez pourront peut-être aussi se poser par la suite dans mon projet.

J'étudis ça de pres. Merci pour tout.
:ccool: