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
| Option Explicit
Private Type VALEURS
Etat As String
Annee As String
Couleur As Long
Code As Integer
Gouv As String
End Type
Sub Traitement()
Dim i As Integer, j As Integer, N As Integer, M As Integer
Dim BD As Range, PAYS As Range, ANNEES As Range
Dim Tb() As VALEURS
Dim Tbk
With Worksheets("Feuille 1")
Set BD = .Range("BD")
Set PAYS = .Range("PAYS")
Set ANNEES = .Range("ANNEES")
Tbk = ColorCode(.Range("CODES"))
End With
N = BD.Rows.Count
M = BD.Columns.Count
ReDim Tb(1 To M, 1 To N)
For j = 1 To M
For i = 1 To N
With Tb(j, i)
.Etat = PAYS(, j)
.Annee = ANNEES(i)
.Couleur = BD(i, j).MergeArea(1, 1).Interior.Color
.Code = Codage(Tbk, .Couleur)
.Gouv = BD(i, j).MergeArea(1, 1).Value
End With
Next i
Next j
Set BD = Nothing
Set PAYS = Nothing
Set ANNEES = Nothing
For i = 1 To N
TriRapide Tb, 1, M, i
Next i
With Worksheets("RESULTATS")
.UsedRange.Clear
For j = 1 To M
For i = 1 To N
With .Cells(j, i)
.Value = Tb(j, i).Etat & " (" & Tb(j, i).Gouv & "-" & Tb(j, i).Annee & ")"
.Interior.Color = Tb(j, i).Couleur
End With
Next i
Next j
End With
End Sub
Private Function ColorCode(ByVal Rng As Range)
Dim Tmp() As Long
Dim c As Range
Dim p As Integer
ReDim Tmp(1 To Rng.Count)
For Each c In Rng
p = p + 1
Tmp(p) = c.Interior.Color
Next c
ColorCode = Tmp
End Function
Private Function Codage(ByVal Tmp, ByVal Coul As Long) As Integer
Dim i As Integer
For i = 1 To UBound(Tmp)
If Tmp(i) = Coul Then
Codage = i
Exit For
End If
Next i
End Function
Private Sub TriRapide(Tbl() As VALEURS, G As Integer, D As Integer, k As Integer)
Dim T As Long
Dim Tmp As VALEURS
Dim L As Integer
Dim H As Integer
L = G
H = D
T = Tbl((G + D) \ 2, k).Code
Do While L <= H
Do While Tbl(L, k).Code < T And L < D
L = L + 1
Loop
Do While T < Tbl(H, k).Code And H > G
H = H - 1
Loop
If L <= H Then
Tmp = Tbl(L, k)
Tbl(L, k) = Tbl(H, k)
Tbl(H, k) = Tmp
L = L + 1
H = H - 1
End If
Loop
If G < H Then TriRapide Tbl, G, H, k
If L < D Then TriRapide Tbl, L, D, k
End Sub |
Exploitation graphique d'un tableau (Gouvernements 1965-2015)
.
). Le tout contient des erreurs, essentiellement issues de la saisie initiale, mais c'est plutôt prometteur.
. De son propre aveu, mon frère m'a fait un code un peu alambiqué qu'on pourrait rendre plus simple. Avez-vous une piste pour passer du tableau initial au résultat en Feuil2 par un autre chemin ?
Répondre avec citation
Envoyé par JokerJet
J'ai quand même bien replacé et nommé les plages comme tu m'indiques.
Partager