'
' Programme Principal
'
Sub AsgQT()

    Dimension col, lig   ' Calcul du nombre de lignes et de colonnes
    Nettoyage col, lig   ' Nettoyage des données
    Stat col, lig        ' Statistiques élémentaires
    Calculcor col, lig   ' Calcul des coefficients de corrélation
    Anacorr col, lig     ' Analyse des coefficients de corrélation
    Coeff col, lig       ' Calcul des coefficients des meilleures liaisons linéaires

End Sub
'
' Calcul du nombre de lignes et de colonnes
'
Sub Dimension(col, lig)

    Sheets("Données").Select
    Let lig = 1
    While IsEmpty(Cells(lig, 1).Value) = False
        Let lig = lig + 1
    Wend
    Let col = 1
    While IsEmpty(Cells(1, col).Value) = False
        Let col = col + 1
    Wend
    Sheets("Statistiques").Select
    Let lig = lig - 1
    Let col = col - 1
    Cells(4, 4).Formula = lig
    Cells(5, 4).Formula = col

End Sub
'
' Nettoyage des données
'
Sub Nettoyage(col, lig)

    Sheets("Données").Select
    Range("A1").Select
    For i = 2 To col
        For j = 2 To lign
            Cells(j, i).Select
            ActiveCell.Value = Val(ActiveCell.Value)
        Next j
    Next i

End Sub

'
' Statistiques élémentaires
'
Sub Stat(col, lig)
    Sheets("Statistiques").Select
    Cells(2, 4).Formula = Fichier

    For cp1 = 2 To col

        Sheets("Données").Select
        Let nomchamp = Cells(1, cp1).Value
        Let moy = Application.Average(Range(Cells(2, cp1), Cells(lig, cp1)))
        Let ecart = Application.StDevP(Range(Cells(2, cp1), Cells(lig, cp1)))
        Let Mini = Application.Min(Range(Cells(2, cp1), Cells(lig, cp1)))
        Let Maxi = Application.Max(Range(Cells(2, cp1), Cells(lig, cp1)))

        Sheets("Statistiques").Select
        Cells(11 + cp1, 2).Formula = cp1 - 1
        Cells(11 + cp1, 3).Formula = nomchamp
        Cells(11 + cp1, 4).Formula = moy
        Cells(11 + cp1, 5).Formula = ecart
        Cells(11 + cp1, 6).Formula = 100# * ecart / moy
        Cells(11 + cp1, 7).Formula = Mini
        Cells(11 + cp1, 8).Formula = Maxi
        Cells(11 + cp1, 9).Formula = Maxi - Mini

        Sheets("Corrélations").Select
        Cells(7 + cp1, 1).Formula = nomchamp
        Cells(8, cp1).Formula = nomchamp

     Next cp1

End Sub
'
' Calcul des coefficients de corrélation linéaire
'
Sub Calculcor(col, lig)

    Sheets("Corrélations").Select
    Cells(2, 4).Formula = Fichier
    Cells(4, 4).Formula = lig
    Cells(5, 4).Formula = col

For i = 1 To col - 1

    Sheets("Statistiques").Select
    Let ux = Cells(12 + i, 4).Value
    Let sigmax = Cells(12 + i, 5).Value

    For j = i To col - 1
        Sheets("Statistiques").Select
        Let uy = Cells(12 + j, 4).Value
        Let sigmay = Cells(12 + j, 5).Value

        Sheets("Données").Select
        somm = 0
        For k = 2 To lig
            Let somm = somm + (Cells(k, 1 + i).Value - ux) * (Cells(k, 1 + j).Value - uy)
        Next k
        Let somm = somm / (lig - 1)
        Sheets("Corrélations").Select
        Cells(8 + j, 1 + i).Formula = somm / (sigmax * sigmay)
    Next j
Next i

End Sub

'
' Analyse de coefficients de corrélations
'
Sub Anacorr(col, lig)

Let k = 7
For j = 2 To col
    Sheets("Corrélations").Select
    Let nom1 = Cells(8, j).Value
    For i = 8 + j To 7 + col
        Sheets("Corrélations").Select
        Let nom2 = Cells(i, 1).Value
        Let correlation = Cells(i, j).Value

        Sheets("Meilleures corrélations").Select
        Cells(k, 2).Formula = nom1
        Cells(k, 3).Formula = nom2
        Cells(k, 4).Formula = correlation
        Let k = k + 1
    Next i
Next j

Sheets("Meilleures corrélations").Select
Let n = (col - 1) * (col - 2) / 2
Range(Cells(7, 2), Cells(7 + n, 4)).Select
Selection.Sort Key1:=Range("D7"), Order1:=xlDescending, Header _
    :=xlGuess, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
Range("A1").Select

End Sub

'
' Recherche des coefficients pour les des meilleures liaisons linéaires
'
Sub Coeff(col, lig)

Sheets("Meilleures Corrélations").Select
bornSur = Sheets("Meilleures corrélations").Cells(2, 6).Value

Let k = 0
Let poscorr = 7
Let corr = Abs(Cells(7 + k, 4).Value)
While k < col * (col - 1) / 2
    If Abs(corr) > bornSur Then

       Let y = Cells(7 + k, 2).Value
       Let x = Cells(7 + k, 3).Value
       Let corr = Cells(7 + k, 4).Value

       Sheets("Statistiques").Select
       For i = 13 To 11 + col
        If Cells(i, 3).Value = y Then
            Let moyy = Cells(i, 4).Value
            Let ecarty = Cells(i, 5).Value
        End If
        If Cells(i, 3).Value = x Then
            Let moyx = Cells(i, 4).Value
            Let ecartx = Cells(i, 5).Value
        End If
       Next i

       Sheets("Meilleures corrélations").Select
       Cells(poscorr, 6).Formula = "Corrélation"
       Cells(poscorr, 7).Formula = corr

       Let pente1 = corr * ecarty / ecartx
       Let origine1 = moyy - pente1 * moyx
       Let pente2 = corr * ecartx / ecarty
       Let origine2 = moyx - pente2 * moyy

       Let pente3 = Application.Round(pente1, 3)
       Let origine3 = Application.Round(origine1, 3)
       Let pente4 = Application.Round(pente2, 3)
       Let origine4 = Application.Round(origine2, 3)

       Cells(poscorr, 8).Formula = y & " = " & pente3 & " * " & x & " + " & origine3
       Cells(poscorr + 1, 8).Formula = x & " = " & pente4 & " * " & y & " + " & origine4

       Sheets("Meilleures Corrélations").Select
       Let poscorr = poscorr + 2

    End If
    Let k = k + 1
    Let corr = Abs(Cells(7 + k, 4).Value)
Wend

End Sub
'
' Tri en ordre décroissant
'
Sub Trimoy(col, lig)
    Let lig = Cells(4, 4).Value
    Let col = Cells(5, 4).Value
    Range(Cells(13, 2), Cells(13 + col - 2, 9)).Select
    Selection.Sort Key1:=Range("D13"), Order1:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    Range("A1").Select
End Sub
Sub Triecart(col, lig)
    Let lig = Cells(4, 4).Value
    Let col = Cells(5, 4).Value
    Range(Cells(13, 2), Cells(13 + col - 2, 9)).Select
    Selection.Sort Key1:=Range("E13"), Order1:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    Range("A1").Select
End Sub
Sub Tricdv(col, lig)
    Let lig = Cells(4, 4).Value
    Let col = Cells(5, 4).Value
    Range(Cells(13, 2), Cells(13 + col - 2, 9)).Select
    Selection.Sort Key1:=Range("F13"), Order1:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    Range("A1").Select
End Sub
Sub Trimini(col, lig)
    Let lig = Cells(4, 4).Value
    Let col = Cells(5, 4).Value
    Range(Cells(13, 2), Cells(13 + col - 2, 9)).Select
    Selection.Sort Key1:=Range("G13"), Order1:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    Range("A1").Select
End Sub
Sub Trimaxi(col, lig)
    Let lig = Cells(4, 4).Value
    Let col = Cells(5, 4).Value
    Range(Cells(13, 2), Cells(13 + col - 2, 9)).Select
    Selection.Sort Key1:=Range("H13"), Order1:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    Range("A1").Select
End Sub
Sub Trieten(col, lig)
    Let lig = Cells(4, 4).Value
    Let col = Cells(5, 4).Value
    Range(Cells(13, 2), Cells(13 + col - 2, 9)).Select
    Selection.Sort Key1:=Range("I13"), Order1:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    Range("A1").Select
End Sub