Comparer une colonne à plusieurs

[Baloup]

Bonjour à tous,

Après avoir exposé un premier problème sur lequel j'ai pu être aidé et ou j'avance doucement mais dans le bonne voie, je reviens vers vous concernant une nouvelle question.

J'ai un classeur contenant pour l'instant 3 feuilles (il est possible d'en avoir plus ultérieurement). Sur la première j'ai une bête série de 6 chiffres, sur la feuille 2 j'ai 3 colonnes contenant également 6 chiffres et 4 colonnes sur la troisième feuille, toujours avec 6 chiffres.

Mon objectif est de comparer la première série de valeurs (de la première feuille donc) avec chacune des colonnes des feuilles 2 et 3 et d'afficher la colonne pour laquelle le coefficient de corrélation est le plus élevé.

Pour l'instant j'arrive à comparer ma première colonne avec une autre colonne de la feuille 2 ... Mon code se résume seulement à ça pour le moment :

Sub coeff_cor()

Dim rep As Double
Dim A As Range
Dim Col As Range

Set A = Sheets("feuil1").Range("A2:A7")
Set Col = Sheets("feuil2").Range("A2:A7")

rep = Application.WorksheetFunction.Correl(A, Col)
MsgBox rep

End Sub

Il est donc normal que je compare seulement avec la première colonne vu qu'elle est définie mais je n'arrive pas à lancer une boucle qui fonctionnerait sur plusieurs feuilles et qui me garderait uniquement le coefficient le plus élevé ...

Je vous remercie d'avance pour votre aide et n'hésitez pas si ma demande n'est pas vraiment clair. Vous trouverez ci-joint un fichier exemple avec annotations.

Bonne journée,

Cordialement.

PS : fichier test sans la macro

 

[Pierrot93]

Re : Comparer une colonne à plusieurs

Bonjour,

peut être avec ceci :

Set A = Sheets("feuil1").Range("A2:A7")
Set Col1 = Sheets("feuil2").Range("A2:A7")
Set Col2 = Sheets("feuil3").Range("A2:A7")

MsgBox  Application.Max(Application.Correl(A, Col1), Application.Correl(A, Col2))

bonne journée
@+

 

[Baloup]

Re : Comparer une colonne à plusieurs

Bonjour Pierrot, tout le monde,

Super ça marche impeccable, j'avais pas pensé à l'application "max" !

Seulement la je dois comparer avec 7 colonnes, je suis obligé de toutes les déclarer du coup ? Deuxième petite question (juste un bonus on va dire), il est possible d'afficher l'en-tête de la colonne qui a donné le meilleur résultat ?

En tout cas, super, merci pour ta rapidité ! Je vais essayer en déclarant toutes les colonnes pour voir !

Merci,

Cordialement

 

[Pierrot93]

Re : Comparer une colonne à plusieurs

Re,

j'ai supprimé le = dans le code... tes 7 colonnes sont sur 7 feuilles ?

 

[Baloup]

Re : Comparer une colonne à plusieurs

J'ai du mal m'exprimer désolé,

J'en ai 3 sur le deuxième et 4 la troisième et en fait je souhaite comparer la colonne A2:A7 de la feuille 1 avec les trois colonnes de la feuille 2 soit A2:A7 puis B2:B7 puis C2:C7. Pareil pour la feuille 3, du coup j'ai modifié le code pour comparer avec les trois colonnes de la feuille 2 et ça marche impeccable mais du coup ça fait formule à rallonge !

J'avais bien vu pour le égal, merci c'est super !

Je te remercie d'avance,

Cordialement.

 

[Pierrot93]

Re : Comparer une colonne à plusieurs

Re,

pour éviter la formule à rallonge...

Option Explicit
Sub test2()
Dim t(0 To 7) As Variant, i As Byte, r As Double
t(0) = Sheets("feuil1").Range("A2:A7")
t(1) = Sheets("feuil2").Range("A2:A7")
t(2) = Sheets("feuil2").Range("B2:B7")
t(3) = Sheets("feuil2").Range("C2:C7")
t(4) = Sheets("feuil3").Range("A2:A7")
t(5) = Sheets("feuil3").Range("B2:B7")
t(6) = Sheets("feuil3").Range("C2:C7")
t(7) = Sheets("feuil3").Range("D2:D7")
For i = 1 To 7
    r = Application.Max(Application.Correl(t(0), t(i)), r)
Next i
MsgBox r
End Sub

 

[Baloup]

Re : Comparer une colonne à plusieurs

Rien à dire c'est top ! En plus tu as fais ça à une vitesse folle, merci beaucoup ! Du coup je trouve en effet que c'est plus clair de déclarer tout plutôt que d'avoir une formule à rallonge !

Plus qu'à trouver comment renvoyer l'en tête de la colonne dans la msgbox afin de savoir de quelle colonne il s'agit ou de renvoyer directement dessus en cliquant sur ok ...

En tout cas, un grand merci vraiment, c'est super de pouvoir avoir des réponses rapidement à ses problèmes !

Merci,

Cordialement.

 

[Pierrot93]

Re : Comparer une colonne à plusieurs

Re,

Plus qu'à trouver comment renvoyer l'en tête de la colonne dans la msgbox afin de savoir de quelle colonne il s'agit ou de renvoyer directement dessus en cliquant sur ok ...

sans doute via une autre variable dans la boucle qui stockerait la dernière valeur de i si r est >, je regarderais un peu plus tard si tu n'y arrive pas....

 

[Pierrot93]

Re : Comparer une colonne à plusieurs

Re,

essaye en modifiant comme suit :

Option Explicit
Sub test2()
Dim t(0 To 7) As Variant, i As Byte, r As Double, r2 As Double, i2 As Byte
Set t(0) = Sheets("feuil1").Range("A2:A7")
Set t(1) = Sheets("feuil2").Range("A2:A7")
Set t(2) = Sheets("feuil2").Range("B2:B7")
Set t(3) = Sheets("feuil2").Range("C2:C7")
Set t(4) = Sheets("feuil3").Range("A2:A7")
Set t(5) = Sheets("feuil3").Range("B2:B7")
Set t(6) = Sheets("feuil3").Range("C2:C7")
Set t(7) = Sheets("feuil3").Range("D2:D7")
For i = 1 To 7
    r = Application.Max(Application.Correl(t(0), t(i)), r)
    If r > r2 Then r2 = r: i2 = i
Next i
MsgBox r
Application.Goto Range(t(i2).Address(external:=True))(0)
End Sub

bon après midi
@+

 

[Baloup]

Re : Comparer une colonne à plusieurs

Alors la bravo, c'est super ! Merci ! J'étais parti sur un If mais j'arrivais pas à renvoyer sur la cellule, d'ailleurs si c'estt pas trop te demander tu peux m'expliquer rapidement comment marche la dernière ligne de ton code ? J'ai un peu du mal à saisir le "external" ?

Promis après je classe ça en résolu et j'arrête de t'embêter ! Quoi qu'il en soit, un grand merci ! C'est super !

Bonne journée,

Cordialement.

 

[Pierrot93]

Re : Comparer une colonne à plusieurs

Re,

tu peux m'expliquer rapidement comment marche la dernière ligne de ton code ? J'ai un peu du mal à saisir le "external" ?

"external", c'est un argument de la propriété "address" de l'objet "range", permet de ramené le classeur et la feuille de la cellule concernée.... sinon, renverrai simplemt $A$2:B$7$... mais sur quelle feuille.... le (0) permet de selectionner la cellule du dessus de la plage en question....

 

[Baloup]

Re : Comparer une colonne à plusieurs

Rien à dire c'est top !

Merci beaucoup !

 

[Baloup]

Re : Comparer une colonne à plusieurs

Bonjour à tous !

Je relance le sujet car je viens de trouver un problème dans ma macro ... Elle fonctionne bien, sauf que parfois je n'ai pas toutes les valeurs dans ma colonne et j'ai l'impression qu'il compare quand même les cases vides ... Enfin je veux dire, je n'ai pas l'impression qu'il compare ligne à ligne mais bien colonne à colonne !

Je ne sais pas si j'ai été très clair, mais le fichier est toujours en pièce jointe dans le premier poste ...

Merci d'avance,

Bonne journée.