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

[Bichette001]

Bonjour
je voudrais trouver une formule permettant de calculer un coefficient de corrélation sur une plage de cellules.
il y a des vides dans cette plage car la formule devra calculer le coeff sur des données qui s'enrichissent ...
comment svp ignorer les cellules vides pour cette formule car sinon le résultat est en erreur à cause des des vides..?

=COEFFICIENT.CORRELATION(B4:B99;$N$4:$N$99)

ici par ex la plage prévisionnelle qui s'enrichir un peu à la fois ne contient des chiffres que jq la ligne 10 entre 11 et 99 ce sont des vides

merci pour votre précieuse aide
Bichette

 

[sylvanu]

Bonjour Bichette,
Une solution possible avec Indirect :

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

 

[Bichette001]

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 ?

 

[sylvanu]

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 :

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

 

[Bichette001]

top ! merci bonne journée !!

 

[Bichette001]

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

 

[sylvanu]

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.

 

[Bichette001]

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

 

[sylvanu]

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

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.

 

[Bichette001]

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 :

 

[sylvanu]

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"

 

[Bichette001]

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

 

[sylvanu]

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.

 

[Bichette001]

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

 

[sylvanu]

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

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.

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

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 !