Calcul tendance et cellules vides

[sweetmercy]

Bonjour à tous / toutes

Dans une feuille excel je récupère les données mensuelles d'appareil de mesure.
(1 colonne par mois / quelques lignes de données)
Pour chaque ligne de donnée je calcule en bout de ligne une tendance grace à la fonction DROITEREG qui me détermite le coeficient directeur et donc le sens de la pente.

Mon souci est que certains mois je n'ai pas de valeur. La fonction DROITEREG me renvoie alors une erreur #VALEUR ce qui me semble assez logique.

Avez vous une solution pour ignorer ces cellules vides dans mon calcul de tendance ? Une autre méthode ?

Merci à vous

 

[job75]

Re : Calcul tendance et cellules vides

Bonjour sweetmercy,

Ce fil devrait vous donner des idées :

https://www.excel-downloads.com/threads/renseigner-des-cellules-vides-dans-liste-de-chiffres.141961/

A+

 

[job75]

Re : Calcul tendance et cellules vides

Re,

Bon j'ai fabriqué un petit fichier avec cette macro :

Sub Regression()
Dim derlig%, dercol%, i%, j%, Xc(), Yc(), n%
derlig = Range("A65536").End(xlUp).Row
dercol = Range("Pente").Column - 1
Application.ScreenUpdating = False
Intersect(Range("Pente"), Range("2:65536")).Resize(, 2).ClearContents
For i = 2 To derlig
  ReDim Xc(0): ReDim Yc(0): n = 0
  '---Détermine les valeurs connues Xc et Yc---
  For j = 2 To dercol
    If Cells(i, j) <> "" Then
      ReDim Preserve Xc(n): Xc(n) = Cells(1, j).Value2
      ReDim Preserve Yc(n): Yc(n) = Cells(i, j)
      n = n + 1
    End If
  Next
  '---Calcul de DROITEREG---
  Cells(i, dercol + 1) = Application.LinEst(Yc, Xc) 'pente a
  Cells(i, dercol + 2) = Application.LinEst(Yc, Xc)(2) 'ordonnée à l'origine b
Next
End Sub

A+

 

[ROGER2327]

Re : Calcul tendance et cellules vides

Bonsoir à tous

La même chose par formules.
Pente :

[COLOR="DarkSlateGray"][B]=(MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B$1:$G$1*$B2:$G2))-MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B$1:$G$1))*MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B2:$G2)))/(MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B$1:$G$1^2))-MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B$1:$G$1))^2)[/B][/COLOR]

Ordonnée à l'origine:

[COLOR="DarkSlateGray"][B]=MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B2:$G2))-$K2*MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B$1:$G$1))[/B][/COLOR]

En prime, le coefficient de corrélation :

[COLOR="DarkSlateGray"][B]=(MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B$1:$G$1*$B2:$G2))-MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B$1:$G$1))*MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B2:$G2)))/RACINE((MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B$1:$G$1^2))-MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B$1:$G$1))^2)*(MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B2:$G2^2))-MOYENNE(SI(($B$1:$G$1<>"")*($B2:$G2<>"");$B2:$G2))^2))[/B][/COLOR]

Toutes matricielles, évidemment.​

ROGER2327
#3604

Dimanche 15 Merdre 137 (Saints Serpents d'Airain, ST)
13 Prairial An CCXVIII
2010-W22-2T23:33:07Z

 

[ROGER2327]

Re : Calcul tendance et cellules vides

Suite...

J'ai oublié le classeur !​

ROGER2327
#3606

Lundi 16 Merdre 137 (Nativité de Saint Donatien A François, SQ)
14 Prairial An CCXVIII
2010-W22-3T00:16:42Z

 

[job75]

Re : Calcul tendance et cellules vides

Bonjour sweetmercy, salut Roger,

Une autre solution par formule (fichier joint) :

1) La cellule I2 étant sélectionnée, définir (menu Insertion-Nom) le nom Matrice par :

=PETITE.VALEUR(SI(ESTNUM(Feuil1!$B2:$H2);COLONNE(Feuil1!$B2:$H2));LIGNE(INDIRECT("1:"&NB(Feuil1!$B2:$H2))))

2) Formule en I2, à tirer à droite et vers le bas :

=INDEX(DROITEREG(N(DECALER($A2;;Matrice-1));N(DECALER($A$1;;Matrice-1)));COLONNES($I:I))

Pas besoin de validation matricielle.

A+

 

[sweetmercy]

Re : Calcul tendance et cellules vides

Merci a vous !

Précision, rapidité, efficacité !

Ma solution ira vers les formules plutôt qu'une macro.

Merci encore.

 

[ROGER2327]

Re : Calcul tendance et cellules vides

Bonjour à tous

L'utilisation de plages ou de formules nommées permettent effectivement de réduire la taille des formules.
À noter que les formules proposées ne font pas toutes la même chose : une synthèse dans le classeur joint.​

ROGER2327
#3610

Lundi 16 Merdre 137 (Nativité de Saint Donatien A François, SQ)
14 Prairial An CCXVIII
2010-W22-3T13:21:07Z