Microsoft 365 Faire varier valeur formule COEFFICIENT.DETERMINATION avec filtre de données

[jo1177]

Bonjour à tous,

Je fais appel à vous car je bloque sur ce problème :

Je souhaite déterminer le coefficient de détermination linéaire d'une série de donnée. Jusque là pas de souci car il existe la fonction "COEFFICIENT.DETERMINATION". Le problème est que je veux faire varier ma plage de donnée via un filtre, et donc mettre à jour la valeur du coefficient de détermination en fonction. Je sais qu'il existe la fonction "SOUS.TOTAL" pour les moyennes, les sommes ect, mais cela ne fonctionne pas avec "COEFFICIENT.DETERMINATION".

Une idée ? Ci-joint un exemple.

Merci beaucoup pour votre aide !

 

[Hasco]

Bonjour,

Dans le fichier joint, j'ai rajouté une colonne déterminant si la ligne est visible ou non (1 = oui; 0 = non) avec à la formule en c2 à tirer vers le bas:=AGREGAT(3;7;$B2)

Puis Votre formule est devenue : =COEFFICIENT.DETERMINATION(($C2:$C31)*($B2:$B31);A2:A31) qui va multiplié B2:B31 par 0 ou 1
Je ne sais pas comment COEFFICIENT.DETERMINATION prend en compte les valeurs 0 , donc si le résultat est juste.

[EDIT] D'après l'aide excel sur la fonction:

• Si une matrice ou une référence utilisée comme argument contient du texte, des valeurs logiques ou des cellules vides, ces valeurs ne sont pas prises en compte. En revanche, les cellules contenant la valeur 0 sont prises en compte.

solution En C2 mettre : =AGREGAT(3;7;$B2)=1
Qui renverra VRAI pour les lignes visible et ainsi les non visible ne seront pas prises en comptes.

Cordialement

 

[jo1177]

Bonjour Roblochon, et merci beaucoup pour votre retour rapide !

C'est plutôt pas mal comme idée ! Mais en réalité, j'ai une 20aine de colonne "Y" à traiter (et que je filtre toutes en mêmes temps, mon filtre est sur la colonne "X"). Du coup, il serait fastidieux de créer autant de colonne "v" que de "Y"... Comment puis-je adapter votre formule/méthode ?

Merci !

 

[Hasco]

j'ai une 20aine de colonne "Y" à traiter (et que je filtre toutes en mêmes temps, mon filtre est sur la colonne "X"). Du coup, il serait fastidieux de créer autant de colonne "v" que de "Y"... Comment puis-je adapter votre formule/méthode ?Merci !

Non la fonction est valable pour toute la ligne. Qu'elle ait 1, 20 ou 50 colonnes. Il suffit de multiplier les valeurs y par cette colonne.

Avez-vous vu ce que j'ai rajouté à mon post précédent comme solution aux valeurs 0. ?

voyez l'aide excel sur la fonction AGREGAT

cordialement

 

[jo1177]

Je n'avais pas fait attention en effet. Vu que cela renvoi un état 1 ou 0 (ou VRAI ou FAUX), en effet, cette colonne "v" est valable pour toutes les autres.

Merci beaucoup pour votre aide ! Bonne journée

 

[jo1177]

Rebonjour,

Finalement, après avoir vérifié que la formule affichait le bon résultat, cela n'est pas le cas... Quand on applique un filtre, une valeur est calculée, mais je ne trouve pas du tout la même que celle calculée avec la formule basique de COEFFICIENT.DETERMINATION.

Est-ce que la formule AGREGAT renvoi des "0" ? Ou bien ignore totalement les valeurs fausses ?

 

[Hasco]

Re,

La formule: =AGREGAT(3;7;$B2) va renvoyer soit 1 (la ligne est visible) ou 0 (la ligne est masquée)
Avec: =AGREGAT(3;7;$B2)=1 la formule renverra VRAI ou FAUX

L'aide excel nous dit :

• Si une matrice ou une référence utilisée comme argument contient du texte, des valeurs logiques ou des cellules vides, ces valeurs ne sont pas prises en compte. En revanche, les cellules contenant la valeur 0 sont prises en compte.

Il faut donc adapter Agregat pour qu'elle renvoie soit un text, un logique ou un vide(impossible).
Donc si ça marche pas avec =AGREGAT(3;7;$B2)=1
Essayez avec =si(AGREGAT(3;7;$B2)=1;1;"")
sinon eh bien je ne vois pas pour le moment

Cordialement

 

[jo1177]

Ca ne marche malheureusement pas... cela renvoi à #VALEUR.

Merci quand même.

Si quelqu'un à une autre idée...

 

[Hasco]

Bonjour,

J'obtiens des valeurs différentes avec en C2 AGREGAT(3;7;$B2) et en formule matricielle:
=COEFFICIENT.DETERMINATION(SI(($C2:$C31)>0;($B2:$B31));A2:A31)
Valider par CTRL+MAJ+ENTREE.

Et ce sera ma dernière contribution, car je ne suis pas spécialiste des Stats. Si vous trouvez une soluce ailleurs, venez nous le dire. C'est intéressant comme problème.

Bonne chance

 

[jo1177]

Ca marche !! Du moins sur l'exemple. J'essaye de l'intégrer à mon fichier original et je vous tiens au courant.

J'ai vu des discutions à propos de solutions en VBA, mais c'est trop avancé pour moi. Si je retrouve les sujets je les ajouterais également.