Calculer le centile d'un tableau

**smiles75** · 05/01/2008, 18h16

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

**philben** · 05/01/2008, 23h16

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 :

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

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

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

**Bigalo** · 06/01/2008, 00h04

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 :

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

    WorksheetFunction.Percentile

Cordialement,

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

**ucfoutu** · 06/01/2008, 00h07

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

**Bigalo** · 06/01/2008, 00h24

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,

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.

**philben** · 06/01/2008, 08h01

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

**Bigalo** · 06/01/2008, 10h01

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

Cordialement,

Envoyé par philben

bonjour,

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

Philippe