XL 2010 Courbe de tendance exponentielle

[lili28]

Bonjour,

Je fais appel à votre expertise concernant les courbes de tendance sur Excel.
En effet, si j’ai réussi à insérer une courbe de tendance exponentielle dans un nuage de points (et c’est déjà un exploit pour moi!!), je souhaiterais ajouter sur le même nuage une courbe à +20% de la courbe exponentielle et une autre à -20% de la courbe exponentielle.
Si cela est simple pour la courbe linéaire, cela dépasse mes compétences s’agissant d’une courbe exponentielle!

Est-ce possible ? Et si oui, existe-t-il un moyen « simple » de le faire?

Merci par avance pour votre aide

Cordialement

 

[sylvanu]

Bonjour Lili,
Auriez vous un petit fichier test ? Ce serait plus simple.
Votre courbe de tendance fut obtenue dans le graphique ou par équations ?

 

[job75]

Bonjour,

@ernest-92 on a parlé de courbe de tendance exponentielle

Par ailleurs pour les +20% et -20% il ne faut pas que les points soient visibles.

A+

 

[Membre supprimé 341069]

Ok, je me suis gouré, mille excuses....

 

[lili28]

Bonjour Sylvanu, Ernest-92 et job75,

Merci bcp de vos retours ultra-rapides.

Il se trouve que job75 m'est venu en aide il y a 2 ans pour un besoin sur la fonction "DROITEREG"; il en a résulté le fichier ci-joint.
J'y ai juste ajouté les droites à + et -20% de la tendance centrale sur le nuage de points (mais c'était très facile!).

Aujourd'hui, je ne dois plus utiliser la courbe de tendance linéaire mais la courbe de tendance exponentielle et y ajouter sur le nuage de points les courbes à + et - 20% de cette courbe exponentielle. Et là ... je sèche bien sûr.

Est-ce possible selon vous ?
Un grand merci de votre aide

Cordialement

 

[sylvanu]

Bonjour Lili, Ernest-92 et Job75,
XL sait faire en auto une linéarisation avec une droite (Droitereg) ou un Log (Logreg) mais pas en Exp.
Deux solutions possibles ( entre autres ) en PJ.
Feuil1 : Linéarisation par polynôme d'ordre 4 ( ou plus si on veut ), par formules.
L'approximation n'est pas Exp mais un polynôme qui se rapproche le plus des points donnés.
Feuil2 : Approximation par Exp ( mais avec macro ). On charge XL de faire la courbe de tendance Exp , et on vient "piquer" l'équation trouvée pour en extraire a et b
( avec y=b e^a ) et on trace les deux enveloppes à +/-20%.
La macro est automatique quand une valeur des colonnes A et B est modifiée. avec :

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B1000")) Is Nothing Then
        Dim Chaine$, tablo
        With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Trendlines(1)
            .DisplayEquation = True
            Chaine = .DataLabel.Text                                    ' Extrait la formule du graphique
            [G1] = Chaine                                               ' Equation
            tablo = Split(Chaine, "e")                                  ' Sépare Eq droite et gauche
            [H2] = Val(Replace((Replace(tablo(1), "x", "")), ",", ".")) ' Coef a
            [H3] = Val(Replace(Mid(tablo(0), 5), ",", "."))             ' Coef b
        End With
    End If
End Sub

 

[Membre supprimé 341069]

Bonsoir,
Si cela peut faire avancer un peu le schmilblick...
Pour ne pas voir les points, les colorer en blanc.
Cordialement.

 

[lili28]

Merci bcp Ernest, j'avais bien en tête cette astuce.

Merci bcp Sylvanu
J'essaie de comprendre (cette macro pourra aussi m'aider) pourquoi les points (feuille 2 colonne 2) sont issus d'une formule.
En effet, les abscisses et ordonnées des points de ma base de données sont fixes.

Si qqn est ok pour m'aiguiller quant à la mise à jour du fichier déposé afin de remplacer la fonction "droitereg" dans la macro par la courbe exponentielle éventuellement en y ajoutant les 2 courbes à + et - 20%, je suis preneuse car je me sers quasi quotidiennement d'un outil inspiré de ce fichier.

Encore merci de votre aide
Cordialement

 

[sylvanu]

Ce n'est qu'une maquette. Cela donne une exo bruitee.
Les points de A et B sont à supprimer.
Le mieux serait d'avoir un fichier représentatif.

 

[job75]

Ci-joint le fichier du post #5 modifié.

J'ai créé les 2 colonnes y + 20% et y - 20% dans la feuille "bdd".

Le graphique a 3 séries et 3 courbes de tendance exponentielles créées manuellement.

Les points des séries 1 et 3 sont masqués.

 

[sylvanu]

Re,
En PJ une version "nettoyée" en complément du post #10.
Seule les colonnes A et B sont à modifier. Le reste est automatique.

 

[Dranreb]

Bonsoir
Mon essai issu de votre classeur

 

[Dranreb]

Rectification: il n'y peut être pas lieu de considérer les Log(X)

 

[lili28]

Bonjour à tous!

Sylvanu merci bcp!
Une de mes bases de données contient un peu moins de 5000 lignes. Du coup, y a-t-il une astuce pour utiliser cette formule ?

Job75 merci bcp!
Je vois que la notion de "haut" et "bas" (>+20% et <-20% de la courbe exp calculée sur la base de la sélection opérée dans l'onglet nuage) a été supprimée du fichier. Est-ce compliqué de la rajouter?

Dranreb merci bcp!
Est-ce que la notion de "haut" et "bas" (calculée sur la base de la courbe exponentielle issue de la sélection opérée dans O7 et O8 de l'onglet nuage) reste correcte?
Quand je compare les 2 fichiers, les courbes ne sont pas positionnées tout à fait au même endroit; du coup, quelle version est la plus fiable?

Encore merci à tous de votre aide précieuse
Belle semaine

 

[job75]

Bonjour lili28, le forum,

Job75 merci bcp!
Je vois que la notion de "haut" et "bas" (>+20% et <-20% de la courbe exp calculée sur la base de la sélection opérée dans l'onglet nuage) a été supprimée du fichier. Est-ce compliqué de la rajouter?

Oui c'est un peu compliqué car il faut récupérer l'équation de la courbe de tendance de la 2ème série.

Voyez ce fichier 2 et la macro de la feuille "nuage" :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [O7:O8]) Is Nothing Then Exit Sub
Dim crit1$, crit2$, tablo, n&, i&, j%, f$
crit1 = [O7] & "*": crit2 = [O8] & "*"
tablo = Sheets("bdd").[A1].CurrentRegion.Resize(, 6).FormulaR1C1 'matrice, plus rapide
n = 1
For i = 2 To UBound(tablo)
    If tablo(i, 1) Like crit1 And tablo(i, 2) Like crit2 Then
        n = n + 1
        For j = 1 To 6
            tablo(n, j) = tablo(i, j)
        Next j
    End If
Next i
With Sheets("Filtre")
    Application.ScreenUpdating = False
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    .UsedRange.ClearContents 'RAZ
    .[A1].Resize(n, 6).FormulaR1C1 = tablo
    If n > 1 Then
        .[C2].Resize(n - 1).Name = "Y" 'plage nommée
        .[D2].Resize(n - 1).Name = "X" 'plage nommée
        .[E2].Resize(n - 1).Name = "Yplus20" 'plage nommée
        .[F2].Resize(n - 1).Name = "Ymoins20" 'plage nommée
        .[G1] = "TC": .[H1] = "Y/TC": .[I1] = "Commentaire"
        f = ChartObjects(1).Chart.SeriesCollection(2).Trendlines(1).DataLabel.Text
        f = Replace(Mid(f, 5), ",", ".")
        f = Replace(f, "e", "*EXP(")
        f = "=" & Replace(f, "x", "*X") & ")"
        .[G2].Resize(n - 1).FormulaArray = f 'formule matricielle
        .[H2].Resize(n - 1) = "=RC[-5]/RC[-1]"
        .[I2].Resize(n - 1) = "=IF(RC[-1]>1.2,""HAUT"",IF(RC[-1]<0.8,""BAS"",""""))"
    End If
    .Columns.AutoFit
End With
End Sub

A+