Recherche Formule avec décalage pour remplir matrice

[jojo2006]

Bonjour à tous,

J’aurai besoin d’un coup de pouce pour construire une formule qui calcul sur ligne et colonne en même temps et qui me permet d’alimenter ma matrice sans le faire à la main ligne par ligne.

J’ai créé un exemple de fichier qui parlera mieux qu’un long discours où j’ai construit « manuellement » les 3 première lignes de la matrice.

Cette matrice calcul en fait un coefficient entre 2 colonnes à chaque fois et à la fin de la construction je dois avoir une belle ligne de 1 sur la diagonale (coefficient de 1 car on compare la même colonne).

Pourriez-vous s’il vous plaît m’aider à trouver la bonne formule (avec le bon décalage après avoir étendu) pour construire cette matrice.

Pour info, j’ai au moins 1000 paramètres ( ce nombre peut également varié en différentes études).

Je vous remercie d’avance pour votre aide et vous souhaite une bonne journée

Cordialement
Jojo

 

[Dugenou]

Bonjour,
Merci pour le fichier : c'est parfait pour te répondre :
en B3 :

=COEFFICIENT.CORRELATION(DECALER(donnees!$A2:$A91;;EQUIV($A3;donnees!$A$1:$N$1;0)-1);DECALER(donnees!$A2:$A91;;EQUIV(B$2;donnees!$A$1:$N$1;0)-1))

Cordialement

 

[eriiic]

Bonjour,

bravo à Dugenou.

J'ai une question, il se passe un phénomène étrange sur mon excel 2010...
Pourquoi en B18 =DECALER(donnees!$A2:$A91;;COLONNE()-2) me retourne une matrice d'une cellule ={0.026} et non de 90 lignes ??
Par contre si je remplace COLONNE()-2 par sa valeur 0 j'obtiens bien ={0;0.001;0.012;0.015;0.018;0.016;
Comme si un bug était apparu (?!?) Ou alors j'ai écrit un truc avec une énorme erreur mais je ne vois pas :-s
En fait Colonne() me retourne une matrice : {2}, normal ?
Pas de soucis avec le equiv()
eric

 

[Dugenou]

Hello Eriiic,
Déjà j'ai oublié de verrouiller la plage avec donnees!$A$2:$A$91, ensuite s'il y a 1000 parametres, il faudra peut être faire un choix et que ce ne sera pas toujours le parametre 1 en première ligne/colonne.

Mais sur le fond tu as raison : l'utilisation de ligne-x et colonne-y ne fonctionne pas, même en décalant les données pour commencer à partir de 1 au lieu de 0

 

[eriiic]

Non, je ne parle pas de ta formule, mais d'une partie de celle sur laquelle j'étais parti.
=DECALER(donnees!$A2:$A91;;COLONNE()-2) ne me retourne pas ce qui est attendu : une matrice d'autant de lignes, décalée de x colonnes.

 

[Dugenou]

Je suis d'accord et je ne sais pas l'expliquer.
C'est étrange on dirait que le résultat de colonne() n'est pas vraiment un chiffre.
Parce que ça fonctionne bien avec

=DECALER(donnees!$A$3:$A$92;;NBVAL($A$3:$A3)-1)

 

[eriiic]

oui, colonne retourne une matrice ={2} au lieu de =2
Je n'ai jamais eu ce type de problème, c'est comme si un bug était apparu.

 

[jojo2006]

Bonjour

un grand merci à vous et en particulier à Dugenou pour cette réponse ultra rapide et performante !!!

Comme cette formule marche bien je voudrai savoir si on pouvait optimiser l'affichage de la matrice qui vous l'avez compris est symétrique (et donc redondante sur la partie basse de la diagonale)

Autrement dit partie basse = partie haute.

Pour alléger un peu le visuel de la matrice est il possible d'afficher simplement la partie haute avec la diagonale de 1 (et donc en supprimant la partie basse) ?

Merci encore
jojo

 

[Dugenou]

Oui c'est possible :
en B3 :

=SI(LIGNE()-2>COLONNE()-1;"";COEFFICIENT.CORRELATION(DECALER(donnees!$A$2:$A$91;;EQUIV($A3;donnees!$A$1:$N$1;0)-1);DECALER(donnees!$A$2:$A$91;;EQUIV(B$2;donnees!$A$1:$N$1;0)-1)))

Cordialement

 

[jojo2006]

Re bonjour,


En fait j’ai peut être été trop vite.


J’ai reconstruit la matrice « manuellement » et visiblement je ne trouve pas les mêmes résulats en comparant la matrice manuelle avec celle construite en étirant la formule (voir plsu bas dans le fichier la comparaison)


J’ai peut être loupé quelque chose en étirant mal la formule.


Pouvez vous m’aider à trouver l’erreur.


Au passage la matrice construite avec la formule n’est pas symétrique alors qu’elle devrait l’être.


Je vous remercie d’avance pour votre aide

 

[Dugenou]

Bonsoir,
Comme je l'ai dit plus haut : j'ai oublié de verrouiller la base à décaler sur les lignes :

donnees!$A$2:$A$91

voir pj

 

[jojo2006]

Ok pardon. Maintenant c'est clair

Un grand merci !!!!