Calculer le centile d'un tableau

Version imprimable

05/01/2008, 18h16
smiles75

Calculer le centile d'un tableau

Bonjour,

Je fais des calculs financiers en vba qui aboutissent à obtenir un tableau de dimension(100 000,15,5).
J'aurai besoin de calculer une VaR 95% sur ce tableau pour chacun des 15*5 = 75 scenarii. Sous excel, j'utilise la fonction centile (montableau, 5%) pour obtenir cette donnée. Je ne pense pas que je puisse l'utiliser dans un tableau vba !

Je ne sais trop comment procéder: je pense qu'il faut en fait juste effectuer un tri de mon tableau pour chacun des scenarii et de récupérer la 95 000 eme valeur. Dans ce cas, quelq'un aurai t il le code qui permet de trier rapidement vu la taille du tableau ?

Merci de votre conseil

bonjour,

J'ai une méthode de tri sous la main qui réalise a priori ce que tu souhaites.
Il s'agit d'un tri Shell qui n'est pas le plus performant (le meilleur est quicksort sauf dans certains cas...) mais beaucoup plus rapide que le tri bulle !

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
 
'Tri des valeurs numériques par ordre croissant
'contenues dans la troisième dimension d'un tableau 3D de type variant
Public Function ShellTriTableau3D(ByRef avTab() As Variant, _
                                  ByVal l2DNumCol As Long, _
                                  ByVal l3DNumColTri As Long) As Boolean
   Dim i As Long, j As Long, k As Long, l As Long, lInc As Long, n As Long
   Dim lMin As Long, lLowerCol As Long, lUpperCol As Long
   Dim lUpperBound As Long, lLowerBound As Long
   Dim avRefLigne As Variant
   On Error GoTo errtag
   If l2DNumCol < LBound(avTab, 2) Or l2DNumCol > UBound(avTab, 2) Then Exit Function
   lLowerCol = LBound(avTab, 3)
   lUpperCol = UBound(avTab, 3)
   If l3DNumColTri < lLowerCol Or l3DNumColTri > lUpperCol Then Exit Function
   lUpperBound = UBound(avTab)
   lLowerBound = LBound(avTab)
   n = lUpperBound - lLowerBound - 1
   ReDim avRefLigne(lLowerCol To lUpperCol)
   lInc = 1
   While lInc < n
      lInc = lInc * 3 + 1
   Wend
   While lInc > 1
      lInc = lInc \ 3
      lMin = lInc + lLowerBound
      For i = lMin To lUpperBound
         j = i
         k = j - lInc
         For l = lLowerCol To lUpperCol
            avRefLigne(l) = avTab(j, l2DNumCol, l)
         Next l
         Do While avRefLigne(l3DNumColTri) < avTab(k, l2DNumCol, l3DNumColTri)
            For l = lLowerCol To lUpperCol
               avTab(j, l2DNumCol, l) = avTab(k, l2DNumCol, l)
            Next l
            j = j - lInc
            If j < lMin Then Exit Do
            k = j - lInc
         Loop
         For l = lLowerCol To lUpperCol
            avTab(j, l2DNumCol, l) = avRefLigne(l)
         Next l
      Next i
   Wend
   ShellTriTableau3D = True
   Exit Function
errtag:
End Function

Ci-joint aussi une fonction qui retourne le centile sur le tableau trié :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
' Retourne le centile sur un tableau 3D ordonné croissant
Public Function GetCentile(avTab() As Variant, _
                           ByVal dCentile As Double, _
                           ByVal l2DNumCol As Long, _
                           ByVal l3DNumCol As Long) As Variant
   Dim dPosition As Double, dRatio As Double
   Dim n As Long, lPos As Long
   n = UBound(avTab) - LBound(avTab)
   dPosition = dCentile * n
   dRatio = dPosition - Int(dPosition)
   lPos = Int(dPosition) + LBound(avTab)
 
   GetCentile = avTab(lPos, l2DNumCol, l3DNumCol)
   If dRatio > 0 Then
      GetCentile = GetCentile + _
                  (avTab(lPos + 1, l2DNumCol, l3DNumCol) - GetCentile) * dRatio
   End If
End Function

et une fonction de test :

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
 
Function test()
   Const clLenTab As Long = 100     'Longueur du tableau à trier
   Dim i As Long, j As Long, k As Long
   Dim avTab(1 To clLenTab, 4, 6) As Variant
   Dim t As Single
   Randomize
Debug.Print "Génère données..."
   For k = LBound(avTab, 3) To UBound(avTab, 3)
      For j = LBound(avTab, 2) To UBound(avTab, 2)
         For i = LBound(avTab) To UBound(avTab)
            avTab(i, j, k) = Int(Rnd() * clLenTab) 'clLenTab - i + 1 '
         Next i
      Next j
   Next k
 
Debug.Print "Tri croissant des données..."
   t = Timer()
   If Not ShellTriTableau3D(avTab, 3, 5) Then     'Tri selon 2D=3 et 3D=5
      Debug.Print "Erreur tri !!!"
      Exit Function
   End If
   t = Timer() - t
   If UBound(avTab) <= 100 Then
      For i = LBound(avTab) To UBound(avTab)
         Debug.Print avTab(i, 3, 5) 'Affiche données selon 2D=3 et 3D=5
      Next i
   End If
Debug.Print "Centile : " & GetCentile(avTab, 0.6, 3, 5) 'Exemple : médiane (0.5 = 50%)
Debug.Print "Durée du tri : " & t & " secondes"
End Function

Remarque :
Plus il y a de dimensions en 1d, 2d et 3d, plus le temps du tri est long bien que l'on fasse le tri de qu'une seule dimension. C'est probablement du au temps de déplacement du pointeur en interne dans le tableau.
Ca serait peut être plus rapide de recopier les valeurs à trier dans un tableau à une dimension puis de le trier...

bon courage,

Philippe

06/01/2008, 00h04
Bigalo

Bonsoir,

De nombreuses fonctions Excel sont utilisables en VBA avec l’objet WorksheetFunction, suivi du nom en anglais de la fonction.

En l’occurrence, pour CENTILE(), c’est

Code:

WorksheetFunction.Percentile

Cordialement,

Citation:

Envoyé par smiles75

Bonjour,

Sous excel, j'utilise la fonction centile (montableau, 5%) pour obtenir cette donnée. Je ne pense pas que je puisse l'utiliser dans un tableau vba !

Merci de votre conseil
06/01/2008, 00h07
ucfoutu

Bonjour, Bigalo,

je crois savoir que la fonction Centile d'Excel ne peut traiter au delà d'environ 8000 articles)... mais je peux me tromper.

Edit : voilà ce que j'ai quelquepart : limitée à une matrice ne comportant pas plus de 8 191 observations...
06/01/2008, 00h24
Bigalo

Bonsoir,

Je viens de vérifier dans l’aide, tu as raison : max 8191 (2 puissance 14, moins 1)

Je n’ai jamais utilisé cette fonction, mais le demandeur semblait indiquer que la fonction convenait sous Excel. Je lui ai bêtement fait confiance :(

Cordialement,

Citation:

Envoyé par ucfoutu

Bonjour, Bigalo,

je crois savoir que la fonction Centile d'Excel ne peut traiter au delà d'environ 8000 articles)... mais je peux me tromper.
06/01/2008, 08h01
philben

bonjour,

Mise à part la limitation de taille de tableau, la fonction Centile ne s'applique pas sur un tableau 3D et la troisième raison était un aspect pédagogique...

Peut-être que Excel 2007 repousse la limite de taille de tableau passé en argument ?

Philippe
06/01/2008, 10h01
Bigalo

Bonjour,

La limite de taille que j’ai indiqué dans mon message précédent provient de l’aide d’Excel 2007. Pas de changement donc de ce côté.

HS - Je viens de découvrir le travail que tu as fait pour faire ressortir les différences entre les versions 2000, 2002, 2003 et 2007 d’Excel

http://pbserv.free.fr/dev/References/ReferenceEXCEL.pdf

Félicitations 8-)

Cordialement,

Citation:

Envoyé par philben

bonjour,

Peut-être que Excel 2007 repousse la limite de taille de tableau passé en argument ?

Philippe

Merci a tous pour vos réponse, et particulièremet philippe !

Par contre, philippe, j'ai encore une question : j'ai utilisé ta procédure test qui fonctionne très bien quand tu demandes le tri sur un tableau avec une 2D et 3D définie ((i,3,5) dans ton exemple)
Mais si je fais la même chose avec deux boucles pour les dimensions 2D et 3D ca ne marche plus ?

Voila ce que j'ai changé dans le code de la fonction test()
Code:

1 2 3 4 5 6 7 8 9 10 Debug.Print "Tri croissant des données..." t = Timer() For k = LBound(avTab, 3) To UBound(avTab, 3) For j = LBound(avTab, 2) To UBound(avTab, 2) If Not ShellTriTableau3D(avTab, j, k) Then 'Tri selon 2D=j et 3D=k Debug.Print "Erreur tri !!!" Exit Sub End If Next j Next k
Ce qui est bizzare,c'est que si je fais le tri juste sur la 2D (var j), en figeant la 3D (var k), la ca remarche...
Code:

1 2 3 4 5 6 7 8 Debug.Print "Tri croissant des données..." t = Timer() For j = LBound(avTab, 2) To UBound(avTab, 2) If Not ShellTriTableau3D(avTab, j, 1) Then 'Tri selon 2D=j et 3D=1 Debug.Print "Erreur tri !!!" Exit Sub End If Next j
Une idée ?

bonjour,

Le tableau est de la forme avTab(100000,15,5).
Une fois renseigné, on traverse toutes les diemnsions 2D et 3D ainsi :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 ... For i = LBound(avTab, 2) To UBound(avTab, 2) '2D For j = LBound(avTab, 3) To UBound(avTab, 3) '3D If ShellTriTableau3D(avTab, i, j) Then Debug.Print "Centile de avTab(," & i & "," & j & ") :" & GetCentile(avTab, 0.95, i, j) Else Debug.Print "Erreur de tri : avTab(," & i & "," & j & ") :" End If DoEvents 'Nécessaire car processus long ! Next j Next i
Bigalo a écrit :

Citation:

Je viens de découvrir le travail que tu as fait pour faire ressortir les différences entre les versions 2000, 2002, 2003 et 2007 d’Excel
...
Félicitations

Merci à toi, je garde de plus un excellent souvenir de ce projet car je me suis bien marré avec les modérateurs:king:

Amicalement,

Philippe

06/01/2008, 17h58
smiles75

Merci encore ca marche super ! Ca prend juste un peu de temps mais c'est OK !
06/01/2008, 18h42
philben

bonjour,

Très heureux d'avoir pu te rendre service,

Bonne continuation.

Philippe