Macro VBA - Création d'identifiants

**sheikyo** · 09/08/2011, 02h03

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**Philippe Tulliez** · 09/08/2011, 05h01

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 ?

**MattChess** · 09/08/2011, 07h40

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 : Sélectionner tout - Visualiser dans une fenêtre à part
```
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

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"

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

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

ci-dessous.

**sheikyo** · 09/08/2011, 10h07

Merci beaucoup pour ces premières réponses.

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.

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).

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**sheikyo** · 09/08/2011, 17h43

Bon pour upper et simplifier mon sujet,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**MattChess** · 10/08/2011, 13h02

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 :

Envoyé par sheikyo

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

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 ?

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Envoyé par sheikyo

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

? tabPeopleAgePlace.RngTab.Address

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? 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 :

Sélectionner tout - Visualiser dans une fenêtre à part

? 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 :

Sélectionner tout - Visualiser dans une fenêtre à part

? 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 :

Sélectionner tout - Visualiser dans une fenêtre à part

? RangeHeaderCol(tabPeopleAgePlace).Columns.Count

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

3

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? RangeHeaderRow(tabPeopleAgePlace).Columns.Count

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? RangeHeaderRow(tabPeopleAgePlace).Rows.Count

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? Range("A3").Value

Commune 1

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? 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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

True

8.3 L'indexation des données du tableau tabPeopleAgePlace

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? Range("D4").Value

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? 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 :

Sélectionner tout - Visualiser dans une fenêtre à part

? RangeFromId(123, tabPeopleAgePlace).Value

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

? IdFromRowCol(4, 3, tabPeopleAgePlace)

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

ci-dessous.

**sheikyo** · 19/08/2011, 15h56

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.

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

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager