Corrélation entre deux séries de chifres binaires
Bonjour,
Voici les données : j'ai un tableau d'accidents. les accidents sont numérotés sur la 1ere ligne. Sur la 1ere colonne sont notés des variables(exemple : matériel défectueux). Si la variable est vrai, je note 1, si la variable est fausse je note 0. Jusqu'ici tout va bien.
Le problème : je souhaite connaitre le coefficient de corrélation entre 2 variables et le faire pour toutes les variables (dans un tableau avec les variables en abscisse et en ordonnée). MAIS, le coefficient de corrélation est erroné (il me semble), car il considère que deux variables égales à 0 sont corrélées (je suppose). Or pour moi, il doit y avoir corrélation uniquement si les deux variables sont égales à 1. Concrètement, j'ai deux variables qui n'ont rien en commun, je devrais donc obtenir 0, eh bien non...
Questions : ai-je bien compris le principe du coefficient de corrélation ? Y a t-il une formule capable de répondre à ma demande (calculer le pourcentage de corrélation en éliminant du calcul les variables ayant pour nombre identique le 0) ?
Enfin, si le calcul existe, est-il possible de l'automatiser pour gagner du temps (70 accidents, 93 variables) ?
J'ai beau me retourner le cerveau depuis des jours, je n'y arrive pas. Quelqu'un pourrait-il éclairer ma lanterne ?
Merci d'avance et si besoin est, je peut joindre le fichier, peut-être que ce sera plus clair...
Pierre
Bonjour Pierre, bienvenu sur le Forum
J'ai beau me retourner le cerveau depuis des jours, je n'y arrive pas. Quelqu'un pourrait-il éclairer ma lanterne ?
Je pense que tu auras une chance d'avoir de l'aide si tu joins un fichier en exemple.
Pour cela, regarde joindre un fichier, en haut de cette page.
Car, si ta lanterne n'éclaire plus beaucoup, la mienne a du mal à éclairer le tableau.
Cordialement
hello
en fait tu cherches, par exemple pour les variables "alcoolémie" et "vitesse", pour les 70 accidents, le nombre de fois où il y a alcoolémie ET vitesse et le nombre de fois où il y a alcoolémie OU vitesse (et pas les 2 à la fois).
la division de ces2 chiffres donne une "corrélation"
les accidents sans vitesse NI alcoolémie ne comptent pas pour ces 2 variables
pas trop difficile à rentrer en Excel
idem pour chaque couple de variables,ça fait quand même dans les 5000 couples à étudier !
vive Excel
Bonjour,
merci pour la rapidité de vos réponses.
voici le fichier en question :
https://www.excel-pratique.com/~files/doc/quantification2.xlsx
Il y a deux onglets : le premier est mon tableau d'accidents, le second est le fameux tableau que je souhaite compléter avec les % de corrélation.
Effectivement jmd, tu as bien compris ce que je voulais faire, mais en utilisant la fonction % de corrélation de excel, je n'est pas le résultat escompté. Je m'explique : vous verrez dans le tableau d'accidents qu'il y a 4 sources de danger possibles (environnement, humain, matériel, organisationnel). Quand je fais la fonction % de corrélation entre par exemple environnement et humain, je devrais obtenir 0 car il n'y a qu'une source de danger possible par accident...donc aucune corrélation, OR ce n'est pas le cas. Ou alors, je n'applique pas du tout la bonne fonction pour ce type de statistique....
pas trop difficile à rentrer en Excel
Peut-être, mais je n'ai pas trouvé comment faire, et surtout, vu le nombre de données, il faut que je puisse l'automatiser...
idem pour chaque couple de variables,ça fait quand même dans les 5000 couples à étudier !
Avec un code couleur je ferai ressortir les corrélation intéressantes.
J'espère en tout cas avoir été plus clair, jmd a bien compris mon problème mais moi toujours pas la solution.
cordialement,
Pierre
re,
ton fichier en .xlsx perd certaines fonctions quand je l'ouvre avec Excel 2003, dommage
comparer les couples de variables A et B (attention à mettre 0 ou 1 partout)
dénombrer quand A=1 et B=1 : =SI(ET(A;B);1;0)
dénombrer quand A=1 ou exclusif B=1 : =SI( OU((A;NON(B));(NON(A);B) ;1;0)
diviser l'un par l'autre
tester pour débusquer s'il y a une erreur.
une fois fait pour Comparer 2 lignes, copier/coller près de 500 fois
(mais il peut y avoir des astuces pour aller + vite)
courage
re,
le .xlsx c'est parce que c'est moins lourd, le fichier dépasse les 100ko sinon.
bon, je crois avoir trouvé un début de solution grâce à ton aide...
1/ SI(OU(A1;B1);1;0)+SI(OU(A1;B1);1;0)+ etc... = C
2/ somme de A+B =D
3/ % de corrélation= (D/C) - 1
Pour l'exemple concret, voici le fichier, c'est l'onglet "test"
https://www.excel-pratique.com/~files/doc/yRuJkquantification2.xlsx
Le problème c'est le nombre de démarche à faire pour obtenir ce résultat...
J'ai pensé à une formule regroupant tout mais ça ne fonctionne pas :
=((SOMME((SI(OU(A1;B1);1;0)):(SI(OU(A6;B6);1;0)))/(SOMME(A1;A6)+SOMME(B1;B6)))-1
J'attends vos avis et solutions possibles, merci d'avance
Pierre
Salut le forum
A tester
=(SOMMEPROD((A1:A6)+(B1:B6))/(SOMMEPROD((A1:A6)+(B1:B6))-SOMMEPROD((A1:A6)*(B1:B6))))-1Mytå
Bonjour,
merci Mytå,
ta formule fonctionne et me donne le bon résultat sur ma feuille 'test'. J'ai testé sur quelques lignes de la feuille 'corrélation' et ça fonctionne.
Par contre, impossible d'incrémenter sur tout le tableau,
j'ai réussi en ligne avec :
C2=ARRONDI((SOMMEPROD(('quanti détaillée'!$B3:$B60)+('quanti détaillée'!C3:C60))/(SOMMEPROD(('quanti détaillée'!$B3:$B60)+('quanti détaillée'!C3:C60))-SOMMEPROD(('quanti détaillée'!$B3:$B60)*('quanti détaillée'!C3:C60))))-1;2)Mais ensuite il faut modifier de nouveau la formule pour chaque ligne suivante ou pour chaque colonne.
N'y a t-il pas une solution pour gagner du temps, en sachant que si je dois rajouter des cas d'accidents je ne veux pas devoir tout modifier...
Pierre
Salut le forum
Pierre45, j'ai regardé ton fichier à nouveau, la seule solution une petite macro.
Il reste à mettre en place l'ajout de colonne aditionnelle donc à tester pour la base.
-Version Compatible 97-2003 format Zip
Mytå
P.S. Je sais je n'ai pas déclarer mes variables..... ( Pour la suite je verrai )
Bonjour,
Je me doutais bien qu'il allait falloir passer par une macro... mais c'est au delà de mes compétences (pour le moment !).
Merci, Mytå, la macro fonctionne à part que quelques "bugs" :
- des résultats ne s'affiche pas (###)
- des chiffres sont superieurs à 1 (alors que le coefficient doit être entre 0 et 1 uniquement), je crois que c'est dû à la fonction valeur absolue ABS).
Merci encore
Pierre
Salut le forum
Dans ton tableau, tu as des cellules qui contiennent des sommes d'ou le plus grand
que 1 dans certaines cellules.
Pour les ###, c'est du a des divisions par zéro. (Tu veux afficher quoi à la place)
Essaye avec cette macro à la place, (équivalent de nombre de valeurs)
Sub Correlation()
For ligne = 2 To 95
For colonne = 2 To 95
If ligne = colonne Then
Cells(ligne, colonne) = 1
Else
Cells(ligne, colonne).FormulaR1C1 = _
"=ROUND((SUMPRODUCT(('quanti détaillée'!R[" & 3 - ligne & "]C[" & ligne - colonne & "]:R[" & 60 - ligne & "]C[" & ligne - colonne & "]>0)" & _
"+('quanti détaillée'!R[" & 3 - ligne & "]C[0]:R[" & 60 - ligne & "]C[0]>0))" & _
"/(SUMPRODUCT(('quanti détaillée'!R[" & 3 - ligne & "]C[" & ligne - colonne & "]:R[" & 60 - ligne & "]C[" & ligne - colonne & "]>0)" & _
"+('quanti détaillée'!R[" & 3 - ligne & "]C[0]:R[" & 60 - ligne & "]C[0]>0))" & _
"-SUMPRODUCT(('quanti détaillée'!R[" & 3 - ligne & "]C[" & ligne - colonne & "]:R[" & 60 - ligne & "]C[" & ligne - colonne & "]>0)" & _
"*('quanti détaillée'!R[" & 3 - ligne & "]C[0]:R[" & 60 - ligne & "]C[0]>0))))-1,2)"
End If
Next colonne
Next ligne
End SubMytå
bonjour tous le monde,
Pour les ###, si on peut afficher des zéro, ce serait super.
Je viens d'essayer la nouvelle macro Mytå, cela a modifié les valeurs : désolé mais maintenant je suis perdu, quel macro affiche les bons coefficients de corrélations ?
Et dernière question, après j'arrête
Merci
Pierre
Re le forum
Pierre as-toi de me donner la solution ici sur colonne F et G (Transposer dans le fichier)
Ta colonne F contient une formule somme dans le fichier d'origine (pas juste 0 et 1)
Tu as tes formules et mon SommeProd...
Pour les ###, je m'occupe de mettre des zéros et pour
le nombre de lignes toujours égales au nombre de colonnes (J'espère)
https://www.excel-pratique.com/~files/doc/Correlation_Reponse.xls
Mytå
P.S. pour ###
If IsError(Cells(Ligne, Colonne)) Then Cells(Ligne, Colonne) = 0bonjour,
Il me semble que le SommeProd se raproche plus de la formule coefficient.correlation de excel. Ce qui m'a dérangé, c'est que des corrélations qui étaient majeures ne le sont plus et vis versa. Ça change l'interprétation des résultats...
Pour les #### , je n'ai pas réussi à faire fonctionné ta ligne de code Mytå.
Pour les lignes, ce que je voulais dire c'est que dans ma feuille 'quantification détaillée", j'ai 60 accidents et je vais certainement en rajouter(j'en ai déjà rajouter d'ailleurs). Dans la feuille 'corrélation' la macro ne fait le calcul que sur ces 60 lignes là (si j'ai bien compris le code...). Je posai la question s'il était possible que la macro prennent les lignes supplémentaires en compte sans que j'ai à modifier le tout code manuellement à chaque fois ?
Pierre
Re le forum
pierre45 a écrit :Il me semble que le SommeProd se raproche plus de la formule coefficient.correlation de excel.
Oui le SommeProd, mais lequel dans le fichier tu veux utiliser.
Merci de me préciser la cellule sur le fichier Correlation-Reponse.
Pour le nombre de ligne/colonnes et les ###, la macro est corrigée.
A te relire
Mytå
Bonjour Mytå et le forum,
Donc, il faut utiliser la colonne H (la deuxième solution). Excuse moi de ne pas l'avoir mentionné dans mon précédent message.
Pierre
Salut le forum
Pierre, le fichier comme j'avais pensé la solution #2
https://www.excel-pratique.com/~files/doc/Quantification_V4.0.xlsm
Le bouton mettre à jour les formules est à utiliser si ajout ou supression de colonnes
ou de lignes, sinon pas besoin, les formules sont dans les cellules et sont mises à jour sur changement de valeurs sur la feuille de base.
La colonne et la ligne doivent être ajouter/supprimer manuellement dans la feuille corrélation.
Sinon on pourrait rebatir la feuille corrélation avec la macro au besoin.
Mytå
Re le forum,
ça fonctionne en partie : dans la feuille corrélation, les données ne vont pas jusqu'en bas mais s'arrêtent à la ligne 64. Mytå, c'est possible de corriger ça stp?
Pierre
Re Pierre
Ca ne devrait pas bloquer, je ne vois pas le problème la si la colonne A est remplie!
( sans cellule vide )
Mytå
Re Mytå,
La colonne A de la feuillle 'quanti détaillée' s'arrête à la ligne 64. Je n'en rajouterai plus pour le moment (plus le temps, je présente les résultats bientôt).
Sur la feuille 'corrélation', quand j'appuie sur le bouton "mise à jour des formules", je t'assure que les résultats s'affichent que jusqu'à la ligne 67.
Pierre