Microsoft 365 calculer coefficient de corrélation sur une plage de cellules avec des vides

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 24 Août 2022

• #2

Bonjour Bichette,
Une solution possible avec Indirect :

VB:

=COEFFICIENT.CORRELATION(INDIRECT("B4:B"&4+NBVAL(B:B));INDIRECT("$N$4:$N$"&4+NBVAL(N:N)))

Le Indirect reconstitue la plage de la ligne 4 à la dernière ligne.
( cela suppose qu'il n'y a pas de vide entre les nombres )

 

Pièces jointes

• Factcorrel.xlsx

  8.6 KB · Affichages: 8

Vote positif 0 Vote négatif

B

Bichette001

XLDnaute Junior

• 25 Août 2022

• #3

canon !!! un grand merci Sylvanu!
j'ai une question sur le coefficient de corrélation
la fomule fait un div/0 si N4:N7 = 5,5,5,5 qd B4:B7 sont entre 1 et 5
d'après toi pourquoi on ne peut pas corréler ?

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 25 Août 2022

• #4

Bonjour,
Regardez ici : Lien
Dans votre exemple Xi-X/ vaut 0 donc le dénominateur vaut 0, donc la formule remonte #DIV0.
Pour éviter le message d'erreur, vous pouvez mettre :

VB:

=SIERREUR(COEFFICIENT.CORRELATION(INDIRECT("B4:B"&4+NBVAL(B:B));INDIRECT("$N$4:$N$"&4+NBVAL(N:N)));"Corrélation impossible")

 

Vote positif 0 Vote négatif

B

Bichette001

XLDnaute Junior

• 25 Août 2022

• #5

top ! merci bonne journée !!

 

Vote positif 0 Vote négatif

B

Bichette001

XLDnaute Junior

• 26 Août 2022

• #6

Bonjour Sylvanu

ces calculs ont pour but de faire une matrice importance performance sur une enquête de satisfaction
(importance = coeff correlation et performance = moyenne de note
j'ai une question sur le calcul de ma médiane
j'ai des 4 qques 3 et des 5 , la mediane donne 5 ?
du coup les moyennes corrigées que sont les moyennes - la médiane sont négatives
or cela m'étonne car les notes qui sont sur 5 sont ttes au-dessus de 4.5/5
je m'attends à avoir que des moyennes corrigées positives
merci pour ton aide

 

Pièces jointes

• MAtrice importance performance .xlsx

  18.8 KB · Affichages: 4

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 26 Août 2022

• #7

Bonjour,
Sur ce coup, je ne peux guère vous aider.
Qu'une moyenne corrigée soit l'écart entre une moyenne et une médiane me dépasse totalement.
Etes vous sure de votre concept mathématique ?
En prenant quelques exemples cet écart peut être positif, nul ou négatif sans pour cela qualifier cela de "correction". Et surtout je ne vois pas ce que cela peut apporter à l'analyse des données.

 

Vote positif 0 Vote négatif

B

Bichette001

XLDnaute Junior

• 26 Août 2022

• #8

j'ai repris la méthodo de ce site : https://lirographe.wordpress.com/20...trice-satisfaction-importance-fiche-pratique/

juste la médiane ds leur site se base sur tte la matrice pas sur chaque colonne

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 26 Août 2022

• #9

Re,
1- Ne pas oublier ce qui est précisé dans l'article :

Ce schéma peut aussi, avec d’autres données et pas mal de précaution

Cliquez pour agrandir...

2- Le terme de "moyenne corrigée" n'est pas pertinent mathématiquement, il est issu de l'esprit du rédacteur.
3- Cet "indicateur" donne l'importance du critère, et non l'importance de sa valeur. ( et encore c'est discutable )
4- J'ai fait quelques essais basés sur cette méthode, et le moins que l'on puisse dire c'est qu'il est sujet à caution quant à son interprétation.
Si on prend 10 personnes qui note un critère à 0 ou 5, on voit que cet écart entre moyenne et médiane vaut 0 si :
- Tout le monde est insatisfait
- Il y a autant d'insatisfait que de satisfait.
- Tout le monde est satisfait.
Alors ... bof ! pour l'interprétation du résultat.
Comme vous le voyez ci dessous cet indicateur ne veut rien dire en temps qu'analyse satisfait/insatisfait :


Le but de la manip est d'avoir un graph avec comme origine 0 qui donnerait autant de satisfait que d'insatisfait et faire le calcul moyenne - 2.5. ( 2.5 serait la moyenne si 50% étaient satisfait et 50% insatisfait )
De cette façon l'indicateur serait positif si le nombre de satisfait était supérieur au nombre d'insatisfait et négatif si l'inverse.

 

Vote positif 0 Vote négatif

B

Bichette001

XLDnaute Junior

• 26 Août 2022

• #10

en fait cette matrice est couramment en institut de sondage ms avec des logicels comme sphinx! j'essaie de l'obtenir sur excel
j'ai trouvé une autre méthodo sur excel :

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 26 Août 2022

• #11

Vous aurez remarqué que le graphique pondère la performance ( note ) vs l'importance ( pondération ).
La seconde vidéo est parlante et le graphique est pertinent.
Dans votre fichier n'apparaissent que les valeurs et non l'importance du critère.
Donc vous ne pouvez pas appliquer cette méthode.
Idem pour la première vidéo :

Cela n'a, à mes yeux, rien à voir avec le premier article. Il est possible que cela marche dans le cas précis présenté, mais c'est un "accident" du aux valeurs utilisées, mais pas reproductible dans d'autres domaines, d'où la petite phrase "Ce schéma peut aussi, avec d’autres données et pas mal de précaution"

 

Vote positif 0 Vote négatif

B

Bichette001

XLDnaute Junior

• 26 Août 2022

• #12

la 2e video il a des notes d'importance que je n'ai pas d'où ma volonté de prendre la corrélation

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 26 Août 2022

• #13

Oui, mais toute cette approche est basée justement sur la matrice Importance/Performance.
La corrélation ne vous donnera rien de pertinent pour cette approche.
Il vous faut définir l'importance de chaque critère ( par exemple de 1 à 5 )
Par exemple, "Délai Envoi des documents" importance 2, "Disponibilité formateur" importance 5.
de toute façon ces critères d'importance ne joueront que pour définir ensuite les plans d'actions, lesquels doivent être menés en priorité. Cette priorisation est importance car on ne peut pas mener plein de plans d'actions en parallèle, sinon à faire trainer les choses.

 

Vote positif 0 Vote négatif

B

Bichette001

XLDnaute Junior

• 26 Août 2022

• #14

oui ms ds ce cas l'importance de 1 à 4 serait subjective
la corrélation d'un critère est par rapport à la satisfaction globale
du coup c'est cohérent car ts les critères se comparent à la satisfaction globale
et les corrélations mesurent l'influence d'un critère sur la satis globale

 

Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc

Supporter XLD

• 26 Août 2022

• #15

Bichette001 à dit:

oui ms ds ce cas l'importance de 1 à 4 serait subjective

Cliquez pour agrandir...

Bien évidemment, mais c'est le principe même de cette méthodologie. Ranger les critères par importance pour ensuite en tirer des plans d'actions, et permette de prioriser ceux ci.

sylvanu à dit:

Oui, mais toute cette approche est basée justement sur la matrice Importance/Performance.

Cliquez pour agrandir...

Si vous ne voulez pas mettre de priorité, alors cette approche n'est pas possible. Ensuite corréler avec la satisfaction globale j'ai du mal à en tirer des enseignements.
Pourquoi dans ce cas ne pas évaluer les différents paramètres par rapport au max ( qui serait de 5 ) et en calculer le taux de satisfaction :

Ne serait pas suffisant ?
A noter que vos résultats sont incroyables, je n'ai jamais vu de tels résultats de satisfaction !

 

Vote positif 0 Vote négatif