Analysez vos données avec VBA

Dans le chapitre précédent, vous avez vu comment mettre en place un reporting automatisé et comment vous pouvez interagir avec d’autres logiciels de la suite Office. Pour finir, nous avons fait un point sur la sécurité de nos macros et nous avons vu comment il faut les sauvegarder.

Dans cette nouvelle partie, nous vous proposons d'aller plus loin dans l’analyse et le reporting. Maintenant que vous savez comment concaténer des fichiers, nous allons les analyser. Dans un premier temps, nous allons réaliser des analyses descriptives et nous passerons du temps sur les séries temporelles. Puis, dans un second temps, nous allons nous attarder sur les analyses bivariées et multivariées avec les tests statistiques (Pearson, Khi2 ou encore ANOVA) ainsi que sur les modélisations comme la régression linéaire.

Dans ce premier chapitre, commençons par apprendre à faire une analyse descriptive de vos données. Une fois que votre fichier de données est consolidé, il va vous falloir le comprendre pour pouvoir l’analyser.

Posez-vous alors deux questions :

• Quelles données mon fichier contient-il exactement ?

• Est-ce que les données sont justes ?

Concernant votre premier point, effectivement, si cela fait plusieurs mois ou années que vous êtes dans la même entreprise, vous êtes peut-être très à l’aise avec les données. Mais un jour, vous aurez certainement à ajouter de nouvelles données, comment allez-vous faire pour les comprendre ? C’est pour cela que nous faisons de l’analyse descriptive sur nos données.

Pour votre second point, qui est de dire que vos données sont justes, nous n’en savons rien du tout. En effet, vos données sont peut-être bonnes aujourd’hui, mais qui vous dit que cela sera toujours le cas demain ? Et s'il y avait eu un bug dans le chargement de la base de données, une erreur de saisie, des modifications des scripts sur la base de données, etc. ?

Il est préférable de toujours partir du principe que les données ne sont pas “justes”. Je veux dire par là que vos données contiennent potentiellement des erreurs (peu importe la source de l’erreur).

Demain, c’est vous qui allez mettre à disposition des données agrégées ou présenter une analyse s’appuyant sur les données que vous avez extraites. Que va-t-il se passer si vos données sont fausses ?

C’est une routine qu’il faut mettre en place quand nous recevons des données. Et vous avez déjà vu comment mettre en place ce type de routine dans la partie précédente. Nous allons simplement étoffer cette analyse.

Gardez en tête que, en analyse de données, il est nécessaire de vous rassurer sur vos données en mettant en place des routines qui vous permettent de comprendre et de trouver des erreurs potentielles. C’est justement ce que nous allons voir ensemble dans la suite de ce cours avec les variables numériques et catégorielles !

Décrivez un fichier de données

Pour commencer, nous allons maintenant décrire un fichier (connaître le nombre de colonnes et de lignes par exemple) et tester l’unicité de la clé du fichier (c’est la clé qui ne doit pas contenir de doublon).

Nous pouvons également faire la même chose, mais directement en VBA, sans passer par les formules Excel :

Sub test_doublons()

Dim rng As Range
Dim cell As Range
Dim dict As Object
Dim doublons As Range

Set rng = Range("A2:A4844")
Set dict = CreateObject("Scripting.Dictionary")

'Teste les doublons pour chaque cellule dans la range
For Each cell In rng
    If dict.Exists(cell.Value) Then
        If doublons Is Nothing Then
            Set doublons = cell
        Else
            Set doublons = Union(doublons, cell)
        End If
    Else
        dict.Add cell.Value, 1
    End If
Next cell

'Msgbox pour l'utilisateur
If Not doublons Is Nothing Then
    'Sélectionne les cellules en doublon dans la range
    doublons.Select
    MsgBox "Doublons trouvés dans la plage " & rng.Address & "."
Else
    MsgBox "Aucun doublon trouvé dans la plage " & rng.Address & "."
End If

End Sub

Comme vous avez pu le remarquer, je n’ai pas testé l’unicité de la clé avec une formule Excel, car celle-ci n’existe pas. Nous pouvons cependant utiliser la fonction “Supprimer les doublons” de l’onglet “Données” dans Excel. Le problème de cette fonction, c’est qu’elle supprime directement les doublons sans nous les montrer, mais cela peut être une autre solution.

Analysez des variables numériques

Faisons maintenant l'analyse descriptive de nos données sur des variables numériques (analyses quantitatives).

La notion de “quantitatives” fait seulement référence à des données numériques qui sont statistiquement intéressantes à analyser. Nous allons donc parler de données discrètes ou continues.

Je vais vous donner une définition des deux mots, cela sera plus facile à comprendre.

C’est important de bien comprendre ce point, car en fonction des valeurs nous n’allons pas regarder la même chose.

Imaginons par exemple que nous regardons une colonne et qu’il y a dedans les valeurs :

2 ; 1 ; 2 ; 1 ; 1 ; 2 ; 2 ; 2,36 ; 2 ; 1 ; 0.

Si je vous dis maintenant que cette colonne contient un nombre d'enfants, qu’en pensez-vous ? Est-ce que cette colonne est juste ?

Le nombre d'enfants étant une donnée discrète, on ne s’attend pas à avoir un nombre à virgule, c’est donc qu’il y a une erreur dans cette colonne.

Commençons par définir ce que nous souhaitons connaître sur nos valeurs :

• le nombre de valeurs non vides ;

• le nombre de valeurs vides ;

• le nombre de valeurs à 0 ;

• la valeur minimum ;

• la valeur maximum ;

• la valeur moyenne ;

• la valeur médiane.

Il existe deux méthodes principales pour faire cela : soit on utilise des formules dans Excel, soit on vient utiliser des formules en VBA.

Nous allons commencer par Excel, je vais utiliser des formules dans la colonne R, qui contient le nombre de lots. Je vais simplement appliquer des formules sur une plage de données, elles vont me renvoyer directement les valeurs que je cherche.

Comme vous le voyez, c’est assez simple de le faire dans Excel avec des formules qui existent déjà.

Pour automatiser cela, vous pouvez soit demander à votre code d’écrire les formules automatiquement, comme nous l’avons fait à la main, soit de calculer lui-même ces différentes valeurs directement dans le code, puis de le stocker dans une variable :

Sub indicateurs()

Dim nbre_nonvide As Integer
Dim nbre_vide As Double
Dim nbre_0 As Integer
Dim min As String
Dim max As Integer
Dim moyenne As String
Dim mediane As Integer
Dim test As String

nbre_nonvide = WorksheetFunction.CountA(Range("R2:R4844"))
nbre_vide = WorksheetFunction.CountBlank(Range("R2:R4844"))
min = WorksheetFunction.min(Range("R2:R4844"))
max = WorksheetFunction.max(Range("R2:R4844"))
moyenne = WorksheetFunction.Average(Range("R2:R4844"))
mediane = WorksheetFunction.Median(Range("R2:R4844"))

nbre_0 = 0
For i = 2 To 4844

    If Range("R" & i).Value = "0" Then
    nbre_0 = nbre_0 + 1
    End If
Next i

End Sub

Souvent en VBA, il y a des fonctions assez similaires à celles d'Excel, comme le minimum, le maximum, la médiane, etc. Par contre, certaines méthodes d’objet n'existent pas en VBA, et c’est le cas ici du nombre de 0 que nous souhaitons compter.

Pour ce faire, j'ai juste ajouté un “For” avec un “If” qui vient incrémenter la variable “nbre_0”. Nous aurions également pu créer un sous-programme pour pouvoir l’appeler, si nous devions l’utiliser souvent.

Dans la méthodologie d’analyse, que ce soient des valeurs discrètes ou continues, il n’y a pas vraiment de changement analytique. Si nous connaissons les données, nous pouvons aussi, pour des valeurs discrètes, regarder les valeurs uniques de la colonne. Cela peut nous permettre d’en apprendre un peu plus sur nos données. Nous allons aborder cette méthodologie dans le prochain paragraphe qui est justement dédié à cette technique avec les variables catégorielles.

Analysez des variables catégorielles

Nous allons continuer avec les variables catégorielles.

Pour ce type d’analyse, c’est un peu plus simple, car nous allons chercher à avoir principalement deux informations :

• le nombre de valeurs uniques (et la liste des valeurs uniques potentiellement) ;

• le mode (la valeur la plus représentée).

Pour les valeurs uniques, nous allons utiliser la formule Excel UNIQUE  (=UNIQUE(Paris!$S$2:$S$4844)) et, pour le mode, la formule  MODE  (=MODE(Paris!$S$2:$S$4844)) sur la colonne "Type Local".

Nous aurions également pu utiliser la fonction de suppression des doublons dans Excel, puis compter le nombre de valeurs uniques (et nous aurions également obtenu la liste).

Nous pouvons faire la même chose directement en VBA avec le code suivant :

Sub indicateurs_quanti()
    Dim mode As String
    Dim plage As Range
    Dim cellule As Range
    Dim test As String

mode = WorksheetFunction.mode(Range("R2:R4844"))

'Utilisation de l'objet dictionary
Dim valeursUniques As Object
Set valeursUniques = CreateObject("Scripting.Dictionary")

'Teste chaque cellule pour trouver toutes les valeurs possibles
For Each cellule In Range("S2:S4844")
valeursUniques(cellule.Value) = Empty
Next cellule

'Msgbox des valeurs uniques
For Each valeurUnique In valeursUniques.keys
    MsgBox (valeurUnique)
Next valeurUnique

End Sub

Identifiez des anomalies dans un fichier

Vous venez de voir comment réaliser une analyse descriptive de vos données. Cela vous donne une meilleure connaissance des données, mais est-ce qu’il y a des anomalies dans vos données ?

Effectivement, nous n’avons rien vu de spécial avec cette analyse, mais cela ne veut pas dire qu’il n’y a pas de problèmes. Par exemple, si nous prenons la colonne "Département", nous pouvons rapidement voir avec l’analyse descriptive que certaines cellules sont manquantes. Vous devez alors vous demander à ce stade, si c’est important dans votre analyse et si vous êtes en mesure de les corriger ?

Dans le cas de la colonne "Département", nous pouvons seulement utiliser la formule  GAUCHE d’Excel (ou LEFT en anglais), qui va récupérer les deux premiers caractères de gauche du code postal pour compléter les données (dans Excel : =GAUCHE(J49;2)).

Si vous aviez plusieurs fois cette erreur dans vos données, vous pourriez alors venir tester chaque cellule et, pour chaque cellule vide, la remplir avec cette formule ou une formule similaire en VBA.

Sub valeurs_manquantes_departement()

    Dim ligne_max As String
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ligne_max = ActiveCell.Row

'Teste toutes les cellules
For i = 1 To 4844
    If Range("L" & i) = "" Then
       Range("L" & i).Select
       'Écriture avec la méthode en VBA
        ActiveCell.FormulaR1C1 = Left(Range("J" & i).Value, 2)
       'Écriture avec la fonction Excel
        ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],2)"
    End If
   Next i
   
End Sub

Comme vous le voyez dans l’exemple, chaque colonne va demander un code spécifique en fonction des données et de la correction que vous souhaitez réaliser. Dans ce premier cas, nous avions une solution simple avec le code postal. Parfois, il n’y a pas vraiment de solution, comme lorsque vous avez des valeurs foncières vides. Il est difficile de les remplir, car il manque trop d'informations pour “fabriquer” le prix du logement.

Cela peut prendre un peu de temps au début de créer le code pour le nettoyage, mais cela va vous faire gagner beaucoup de temps par la suite quand vous n’aurez plus à le faire manuellement.

Nous allons maintenant nous concentrer sur les valeurs aberrantes ou atypiques.

C’est une très bonne question ! À partir de quand une valeur est-elle aberrante ? Si je vous dis qu’il y a un appartement vendu à Paris pour 750 000 €, est-ce une valeur aberrante ?

Regardons ensemble le résultat de l’analyse numérique ci-dessous :

En regardant notre tableau, nous pouvons nous dire qu’un appartement à 750 000 €, ce n'est pas une valeur aberrante pour Paris, car la moyenne est à 614 440 € et la médiane à 445 000 €. Nous sommes, certes, au-dessus, mais cela reste largement acceptable (+22 % par rapport à la moyenne).

Si nous prenons maintenant une valeur de 3,5 millions d’euros. Est-ce une valeur aberrante ?

Nous pouvons effectivement réfléchir comme cela, mais nous pouvons également nous tourner du côté des mathématiques, car il y a des techniques toutes prêtes qui vont nous aider !

Une des méthodes les plus utilisées est la méthode de l’écart interquartile.

Pour faire simple, cette méthode permet de calculer un palier bas et un palier haut. Nous allons estimer que si nos valeurs sont en dessous ou au-dessus des paliers, alors nous pouvons les considérer comme des valeurs aberrantes. Le terme “bornes” peut aussi être utilisé pour nommer les paliers.

Pour ce faire, nous devons commencer par calculer l’IQR. C’est l’écart interquartile, qui est la différence entre le quartile 3 et le quartile 1 :

IQR = Q3 - Q1

Puis pour trouver nos bornes, nous allons :

• chercher le 1er quartile ;

• lui soustraire 1,5 x l’IQR pour la borne basse ;

• ajouter 1,5 x l’IQR au 3e quartile pour la borne haute.

Dans notre cas, nous allons tout faire en utilisant le VBA, mais vous pouvez également le faire avec Excel en utilisant la formule quartile.

Sub ecart_interquartile()

Dim Q1 As Double
Dim Q3 As Double
Dim IQR As Double
Dim borne_basse As Double
Dim borne_haute As Double

Q1 = WorksheetFunction.Quartile(Range("E2:E4844"), 1)
Q3 = WorksheetFunction.Quartile(Range("E2:E4844"), 3)
IQR = Q3 - Q1

borne_basse = Round(Q1 - (1.5 * IQR), 0)
borne_haute = Round(Q3 + (1.5 * IQR), 0)

If borne_basse < 0 Then
   borne_basse = 0
   MsgBox ("attention borne basse inférieure à 0")
   Range("$B$1:$V$5000").AutoFilter Field:=4, Criteria1:=">" & borne_haute, _
     Operator:=xlAnd
   test = ">" & borne_haute

End If
End Sub

Dans notre cas, la borne basse est négative. Vu qu’elle est négative, j’ai préféré la mettre à 0 et faire un filtre seulement sur les valeurs hautes dans le tableau. Les différentes bornes se calculent toutes seules. Et, en plus, le code filtre automatiquement votre tableau sur les valeurs dites “aberrantes”. L’utilisateur pourra par la suite modifier, supprimer ou garder les valeurs qui ne sont peut-être pas des erreurs (surtout dans l’immobilier). Pour réduire les données, vous pourriez faire le calcul de la surface, puis refaire les bornes sur le prix au mètre carré qui sera plus juste.

Vous pouvez également changer les bornes et dire que ce n’est pas 1,5 fois l'IQR, mais 2 fois ou 3 fois, en fonction de votre domaine et de vos données. Il faut adapter l’analyse à votre milieu professionnel.

Je détaille dans le screencast la méthode de l’IQR si vous souhaitez comprendre en détail le code :

À vous de jouer !

Vous avez précédemment fabriqué un fichier pour vos collègues en automatisant la création de votre fichier d’analyse. Votre chef vous demande maintenant de vous pencher également sur ce fichier, car il souhaite faire une analyse complète des données :

• analyse descriptive ;

• analyse temporelle ;

• analyse bivariée ;

• modélisation des données.

Pour commencer, il souhaite que vous rajoutiez dans le fichier toute la partie descriptive de cette analyse. Il désire comprendre plus en détail votre fichier et les données.

En résumé

• L’analyse descriptive permet de comprendre les données.

• La description d’une variable numérique se fait au travers du minimum, du maximum, de la moyenne, du nombre de données ou encore de l’écart type.

• Pour une variable catégorielle, on va se focaliser sur la valeur qui revient le plus souvent et sur le nombre de valeurs uniques.

Ce chapitre vous a permis de comprendre l’importance des analyses descriptives sur votre jeu de données. Le prochain chapitre sera consacré aux représentations des séries temporelles.