Analysez des séries temporelles :

Vous avez vu précédemment comment réaliser une analyse descriptive de vos données. Ce type d'analyse permet de comprendre les données avant de réaliser d'autres types d'analyses.

Comme nous avons des dates dans nos données, nous pouvons réaliser une analyse temporelle, qui va se décomposer en série temporelle et en moyenne mobile. Pour finir, nous utiliserons la feuille de prévision d'Excel pour prédire des valeurs.

Appréhendez la spécificité des séries temporelles :

Les séries temporelles sont monnaie courante dans les différentes analyses que vous allez faire. Pour faire une analyse temporelle, vous aurez besoin d'une variable de temps.

C'est facile, on va utiliser des dates ! Effectivement, nous allons souvent utiliser des dates, mais surtout toutes les déclinaisons des dates. Cette variable temporelle va pouvoir prendre plusieurs formes par exemple :

• des secondes;

• des minutes;

• des jours;

• des semaines;

• des mois;

• des années;

• etc.

Vous pouvez également ajouter d'autres formes de variables temporelles, telles que les numéros des semaines, les jours de la semaine ou encore des plages horaires. Il y a assez peu de limites à la variable temporelle que vous allez prendre.

Certaines entreprises utilisent les clôtures des comptes, qui peuvent arriver le 30 avril, comme variables temporelles. Ce n'est pas un trimestre, ce n'est pas un semestre, c'est juste une période définie par l'entreprise, qui va aller du 1er au 30 avril de l'année suivante. Cette période est une variable temporelle pour l'entreprise.

La majorité du temps, vous allez vous retrouver avec des variables sous la forme de dates ou de timestamp (ou horodatage en français).

Le timestamp est un système temporel qui s'écrit comme cela : 1686167081. Ce timestamp par exemple donne la date du 07-06-2023 à 21h44 et 41 secondes. Ce chiffre de 1686147081 est le nombre de secondes depuis la date du 1er janvier 1970 à 00h00 et 00 seconde. Si on ajoute 1686167081 secondes à cette date, on tombe sur le 07-06-2023 à 21h44 et 41 secondes.

Souvent, vous allez devoir modifier votre fichier pour calculer les semaines, les mois ou encore les jours.

Dans notre cas, j'ai ajouté une formule Excel dans la colonne "mois (=MOIS("votre date")) et dans la colonne "semaine" (=NO.SEMAINE("votre date"),11).

Bien sûr, dans le cadre de l'automatisation de votre fichier d'analyse, vous auriez pu faire cela en VBA. Je vous laisse le faire si vous le souhaitez.

La particularité des données temporelles, c'est quelles s'apprécient dans un certain ordre. Si vous regardez l'évolution des ventes du mois de janvier, vous soihaitez commencer par le 1er janvier, puis le 2 janvier, puis le 3 janvier, etc.

Il faut donc que vos données soient initialement triées afin que vous puissiez voir leur évolution dans le temps. Contrairement aux autres données, si celles-ci ne sont pas triées, elles ne sont pas vraiment exploitables. Contrairement aux autres données, si celles-ci ne sont pas triées, elles ne sont pas vraiment exploitables, car ce que nous recherchons dans ce genre d'analyse, c'est l'évolution dans le temps des données.

Maintenant que nous avons transformé nos données temporelles, nous allons pouvoir les exploiter plus facilement.

Pour commencer, nous allons faire un premier graphique à partir d'un tableau croisé dynamique (TCD pour les intimes). L'avantage du TCD, c'est qu'il permet d'agréger les données par jour.

En effet, si nous regardons nos données, nous allons nous apercevoir que pour le 2 janvier, nous avons eu 20 ventes. Nous, nous souhaitons n'avoir qu'une seule donnée pour ce jour-là et pas 20 données. Nous pouvons alors choisir la façon de les agréger en utilisant par exemple une somme, une moyenne, un minimum, une médiane, le nombre de transactions, etc. Dans notre cas, nous allons choisir dans un premier temps de regarder la moyenne des valeurs foncières par jour. Je vous explique ci-dessous comment faire un TCD en affichant les jours et la moyenne des valeurs foncières.

Pour ce faire, nous allons commencer par sélectionner l'ensemble du data set, puis nous allons aller dans "Insertion", cliquer sur "Tableau croisé dynamique" et faire "OK" pour le pmettre dans une nouvelle feuille de calcul.

Dans cette nouvelle feuille, nous allons créer notre TCD en utilisant les dates de mutation que nous souhaitons voir.

On va ensuite utiliser la valeur foncière que nous allons paramétrer pour afficher la moyenne.

Nous allons ensuite ouvrir l'ensemble de notre TCD afin d'avoir toutes les valeurs, puis je vais venir supprimer la partie jour parce que je voudrais avoir, en lecture directe, la vraie date.

À partir de là, on voit qu'on a un TCD qui contient l'ensemble de nos données moyennes de vente par jour à Paris.

Il nous reste simplement à venir cliquer sur "Graphique croisé dynamique", puis à choisir une courbe et faire "OK".

On se retrouve maintenant avec un graphique croisé dynamique qui a été généré en quelques clics par nous-mêmes, puis on a juste besoin de potentiellement écrire un morceau de code, si un jour, on venait à changer les valeurs, pour mettre à jour notre TCD.

Donc, ça vous montre une première série temporelle avec les jours, auxquels nous avons ajouté les mois afin de mieux découper l'analyse.

Nous pouvons voir que cette moyenne oscille le plus souvent entre 40000€ et 800000€. Nous notons égalemnt de gros pics à 1,4 million et des minimums autour des 100000 à 200000€. Ce graphique nous montre les transactions moyennes sur Paris par jour :

Nous pouvons retirer la notion de jour pour avoir un graphique plus lisible avec seulement la valeur foncière moyenne par mois.

Les deux graphiques utilisent les mêmes données, à savoir la moyenne des valeurs foncières. Dans un cas, cela nous permet de voir les variations qu'il peut y avoir avec le premier graphique, alors que le deuxième, plus simple, nous permet de mieux comprendre les tendances.

Maîtrisez le concept de moyenne mobile :

Nous avons vu précedemment comment faire une analyse temporelle. Cependant, il arrive parfois que cette analyse ne soit pas suffisante pour comprendre facilement nos données.

Nous allons reprendre notre exemple, mais, cette fois, à la pla place de la moyenne des valeurs foncières par jour, nous allons mettre le nombre total de transactions par jour.

Contrairement au premier graphique, celui-là est plus compliqué à comprendre. On peut dire que dans le graphique nos valeurs vont de 20 à 80 avec des maximums à plus de 100 et des minimums proches de 0. Il n'y a pas de tendance claire qui se dégage de ce graphique.

Nous observons cependant qu'un pattern revient tout le temps : un chiffre proche de 0, puis cela remonte doucement pour atteindre un maximum et cela redescend. Cette forme se répète de façon plus ou moins intense tout au long de notre graphique.

En regardant de plus près, nous voyons que les minimums tombent toujours les samedis, et il n'y a pas de données les dimanches. Si nous mettons un peu de contexte, nous savons que les notaires sont les plus souvent fermés le samedi ou ouverts le samedi matin, cela est donc logique qu'il y ait un peu de transactions. De même, tous les notaires sont fermés le dimanche, c'est ainsi normal que nous n'ayons pas de transactions.

Ajoutons à présent le numéro des semaines dans une nouvelle colonne avec la formule JOURSEM (=JOURSEM("votre date"), "choix début"). Cette fonction permet de renvoyer le numéro du premier de la semaine. Ce premier jour est paramétrable avec le deuxième argument dans la formule. De mon côté, j'ai choisi le numéro 2 qui permet de renvoyer un 1 pour le lundi, un 2 pour le mardi, etc.

Nous allons mainteant refaire le graphique, toujours à l'aide de notre TCD, mais cette fois en affichant seulemnt les numéros des semaines et les jours de la semaine.

Nous voyons très bien dans ce cas qu'il y a une saisonnalité dans nos données, car le schéma se répète chaque semaine.

Ce n'est pas mal cet exemple, mais vous ne savez pas vraiment où on veut en venir. Nous y arrivons, je peux vous demander maintenant de me dire ce que vous pensez de cette courbe et quelle est sa tendance, mais, personnellement, j'ai du mal visuellement à savoir ce qu'il se passe.

Il existe une méthode pour lisser les courbes qui s'appelle la moyenne mobile. Je vous laisse me suivre dans les explications ci-dessous pour la création de cette moyenne :

Pour commencer, on va toujours récupérer notre dataset classique dans lequel nous allons toujours créer un TCD.

Cette fois, nous allons utiliser les semaines et les jours de la semaine en lignes et utiliser la clé que nous allons passer en nombres afin de visualiser dans le TCD, ici, le nombre de transactions qui ont été faites par jour de la semaine et donc par semaine.

Je vais mettre ce graphique croisé dynamique en courbe. Ce que j'observe de loin, c'est que ma courbe a l'air d'avoir une certaine forme qui se répète un petit peu tout le temps.

Pour essayer de voir ça d'un détail, je vais venir filtrer sur des mois. Je vais prendre par exemple deux mois en particulier, on va prendre janvier et février. Là, c'est assez flagrant, on voit facilement que notre courbe commencer par augmenter sur les premiers jours de la semaine et vient s'écraser sur le sixième jour de la semaine.

Le problème, c'est que cette courbe, à la lecture, elle est difficile de savoir si je suis en train d'augmenter le nombre de ventes ou si je reste stable.

Pour essayer de mieux comprendre ça, on utilise des moyennes mobiles. Pour créer une moyenne mobile, on vient "Ajouter une courbe de tendance" et on vient cliquer sur "Moyenne mobile", ici.

On voit qu'on a une période. La période qu'on doit paramétrer ici correspond à la périodicité de notre phénomène. Comme c'est tous les six jours, on va mettre la périodicité à la valeur "6".

Et qu'est-ce que nous avons ? QUand on met une période à 6, on voit que notre courbe se lisse énormément.

On a donc une saisonnalité sur notre jeu de données, une forme qui revient tout le temps, tous les six jours.

Dans notre exemple, nous avons fait une moyenne mobile visuelle sur un graphique. Nous aurions également pu fabriquer nous-mêmes cette moyenne mobile avec la formule MOYENNE. Chaque jour, vous allez faire la moyenne des six derniers jours. Alors, vous allez me demander pourquoi nous ne faisons pas la moyenne des sept derniers jours, vu que c'est une récurrence sur chaque semaine. Car il n'y a pas de données pour le dimanche, la ligne n'existe pas, donc je ne peux pas compter ce jour-là.

Effectuez une prévision temporelle dans Excel :

Maintenant que nous avons analysé en détail notre série temporelle, nous souhaitons faire une modélisation. Nous désirons faire une prévision sur les trois prochains mois du nombre de transactions qu'il va y avoir à Paris.

Pour cela, nous allons utiliser les mêmes données, à savoir, le nombre de transactions par jour. Il y a dans Excel un module de prévision directement intégré, que vous pouvez retrouver en allant dans "Données", puis en cliquant sur "Feuille de prévision". Je vous laisse me rejoindre les explications suivantes pour que nous paramétrions ensemble cette prévision :

Pour ce faire, on va commencer par remodeler nos données en faisant un TCD. Nous allons utiliser la date, avec la vraie date cette fois, puis nous allons compter le nombre de clés. À l'écran sera affiché un TCD contenant d'une part les date et de l'autre le nombre de clés.

On crée le graphique croisé dynamique et là, on voit le nombre de ventes par jour sur notre graphique avec pas mal de ventes sur les deux premiers mois de l'année, moins de ventes au mois de mars et une reprise du nombre de ventes.

Ce qu'on veut faire, c'est prédire ce qui va se passer potentiellement après. Nous allons choisir toutes nos données, sans le total général, puis on va dans "Données", "Feuille de prévision".

On vot que nous avons notre prévision qui a été faite en rouge sur le côté droit de la feuille.

Nous, on veut une fin de prévision au 31/07/2023, un début de prévision bien au 01/07, un intervalle de confiance à 95%, le caractère saisonnier, je préfère le maintenir à bien 7 mais moi-même, la plage de chronologie et la plage de valeur sont les bonnes plages, ce sont les plages A et B et on clique sur "Créer".

On voit maintenant qu'on a notre graphique avec la prévision qui a été faite du nombre de transactions qu'on pourrait théoriquement venir faire sur le mois de juillet.

On va vouloir maintenant lancer automatiquement ce genre d'analyse. On va repasser dans notre code et dans notre code, on a la fonction CreateForecastSheet, on va venir lui spécifier les feuilles.

Sub prevision()

ActiveWorkbook.CreateForecastSheet Timeline:Sheets("Feuil5").Range( _
    "A4:A159"), Values:=Sheets("Feuil5").Range("B4:B159"), ForecastEnd _
    :="31/07/2023", ConfInt:=0.95, Seasonality=7; ChartType:= _
    xlForecastChartTypeLine, Aggregation:=xlForecastChartTypeLine, _
    DataCompletion:=xlForecastDataCompletionInterpolate, ShowStatsTable:=False

End Sub

La prévision n'est pas parfaite. Cela reste une prévision simple dans Excel sur des données qui ne varient pas énormément.

Nous pouvons également lancer l'outil de simulation directement en VBA en utilisnt "Workbook.CreateForeCastSheet" avec les différents paramètres de cette fonction.

ActiveWorkbook.CreateForecastSheet Timeline:Sheets("P2C2 graph").Range( _
    "A490:A645"), Values:=Sheets("P2C2 graph").Range("B490:B645"), ForecastEnd _
    :="30/09/2023", ConfInt:=0.95, Seasonality=7; ChartType:= _
    xlForecastChartTypeLine, Aggregation:=xlForecastAggregationAverage, _
    DataCompletion:=xlForecastDataCompletionInterpolate, ShowStatsTable:=False

Voici un lien vers le site de Microsoft avec les différents paramètres de la fonction "CreateForecastSheet".

Réalisez une anlayse bivariée :

Vous venez de voir comment comprendre et analyser une série temporelle.

Cherchons à présent à comprendre comment analyser :

• deux variables numériques;

• une variable numérique avec une variable catégorielle;

• deux variables catégorielles.

Pour chaque anamyse, nous allons voir comment trouver la meilleure visualisation, et surtout comment nous pouvons réaliser un test statistique sur les données.

Un test statistique, mais pourquoi doit-on faire cela ? Pour plusieurs raisons, premièrement, car nous ne pouvons nous limiter à une seule analyse graphique pour prendre une décision. Ensuite, il est toujours bien de pouvori confirmer une hypothèse sur la base de notre test statistique.

Analysez deux variables numériques :

Comemnçons par les tests numériques. Quand nous parlons de test numérique, nous parlons de test sur deux variables quantitatives.

Pour ce test, nous allons nous intéresser au lien de dépendance entre les deux variables quantitatives :

• le nombre de pièces dans un appartement;

• le prix des appartements au mètre carré.

Transformons tout d'abord nos données pour créer un tableau exploitable. Pour ce faire, nous allons refaire un TCD avec en ligne la variable "Nombre de pièces principales" et en valeur la "Moyenne de la surface Carrez au mètre carré".

Si vous vous demander ce qu'est la surface Carrez, vous pouvez consulter ce site d'une agence immobilière.

Dans cette analyse, nous allons supprimer les valeurs extrêmes qui n'ont pas vraiment de sens, avec le nombre de pièces à 0 ainsi que le nombre de pièces égal à 10, car cela correspond à une surface Carrez de 15 m² (ce qui fait de petites pièces).

Maintenant que nous avons nos données, nous allons créer un nuage de points pour les visualiser plus facilement.

Comme nous pouvons le voir sur ce graphique, tous les points ont l'air d'être assez logiques, en suivant une courbe que nous pourrions tracer entre tous les points; c'est la courbe en pointillé que j'ai ajoutée. C'est une courbe de tendance linéaire que nous pouvons directement ajouter à notre graphique. Au lieu d'insérer une courbe de moyenne mobile comme précédemment, nous ajoutons plutôt une courbe linéaire.

Cette courbe de tendance essaye de passer au maximum par tous les points ou au plus près de tous les points pour réduire au maximum l'écart entre les points.

Visuellement, nous pouvons supposer qu'il y a une corrélation entre les deux variables. En effet, la surface moyenne augmente bien proportionnellement au nombre de pièces. Mais à quel point cette corrélation est-elle forte ? C'est exactement ce que nous souhaitons savoir.

Pour connaître la valeur de la corrélation, nous allons utiliser le test statistique de Pearson. Ce test va nous donner la force de la corrélation entre les deux variables, sous la forme du coefficient de corrélation de Pearson. Le résultat de ce test peut aller de -1 jusqu'à 1 en passant bien sûr par le 0 :

• plus le chiffre est proche de 0 et moins il y a une corrélation;

• plus le chiffre s'approche de 1 ou de -1 et plus la corrélation est forte.

Il n'y a pas de consensus clair, mais nous pouvons dire qu'un coefficient de -0,5 ou +0,5 est une corrélation moyenne et, à partir de -0,75 ou +0,75, la corrélation est forte.

Je vous explique la réalisation du test et l'interprétation du résultat : le test de Pearson permet donc de confirmer ce que nous avions supposé visuellement. Nous avons fait ici ce test dans Excel, mais nous pouvons également le faire en VBA avec le code suivant, avec toujours "WorksheetFunction.Pearson" :

Sub test_pearson()

Dim test_pearson As Single

test_pearson = WorksheetFunction.Pearson(Range("A4:A12"), Range("B4:B12"))
MsgBox ("le coefficient de Pearson est de : " & test_pearson)

End Sub

Analysez une variable catégorielle et une variable numérique :

Vous comprenez maintenant l'importance d'un test statistique, il va vous permettre de confirmer une corrélation ou une hypothèse. En fonction des variables, nous allons avoir des tests différents. Lançons-nous donc dans la réalisation d'un test statistique entre une variable numérique (quantitative) et une variable catégorielle (qualitative).

Pour ce faire, analysons la différence entre deux moyennes. Dans notre cas, nous allons faire l'analyse de la moyenne du prix des appartements et de celle des maisons. Ce test s'appelle le test t de Student. Il permet de mesurer s'il y a une égalité entre les moyennes.

Pour commencer à faire ce test, nous avons besoin de modeler nos données afin d'avoir deux colonnes :

• une colonne avec le prix des appartements;

• une colonne avec le prix des maisons.

Je vous laisse choisir la technique que vous préférez : utiliser des filtres ou encore faire deux TCD différents. Pour ma part, j'ai fait des filtres et j'ai collé les données sur une nouvelle feuille :

Mais pourquoi faut-il faire cela en fait ? Étant donné que nous souhaitons analyser les moyennes des deux jeux de données (appartement et maison), nous devons les séparer pour en faire deux variables qui ne sont pas dans le même groupe.

Avant de continuer, nous avons besoin de faire un point sur un nouvel indicateur que nous allons suivre : la variance.

La variance, c'est une mesure de dispersion (c'est le carré de l'écart type). Pour faire très simple, nous pouvons dire que cela mesure la dispersion autour de la moyenne.

Le test t de Student a deux versions possibles :

• une version pour les variances égales;

• une autre version pour les variances différentes.

Dans notre cas, nous ne savons pas si les variances sont égales ou non pour le moment. C'est pourquoi nous allons réaliser un test préliminaire pour comprendre nos jeux de données, il s'appelle le test f de Fisher. Ce test a pour but de nous dire si les variances entre les deux de données sont égales ou non.

Pour réaliser ce test, ainsi que le test de Student associé, je vous propose de me suivre dans les explications suivantes.

Dans un premier temps, on va commencer par transformer nos données. Pour transformer nos données, comme d'habitude, nous allons utiliser un TCD dans lequel nous allons mettre les ID, puis les prix des maisons, et nous allons utilser le type local pour pouvoir splitter appartements ou maisons.

Dans un cas, je commence par copier tous mes appartements dans une nouvelle feuille. Puis je vais faire la même chose avec les maisons.Je vais garder tous les appartements et les maisons qui ont bien des prix (c'est-à-dire pas de valeurs vides).

Je vais maintenant faire un premier test de Fisher qui va me permettre de voir s'il y a une égalité ou non entre mes variances.

Pour faire ce test, ce sera le test F de Fisher, donc F.test, qui me demande les deux matrices de prix.

Plus mon résultat de mon test est proche de zéro, plus je peux rejeter l'hypothèse d'égalité des variances.

Donc, je peux conclure qu'il n'y a pas d'égalité entre mes deux variances.

Je vais maintenant passer au test de Student, avec toujours la même chose. La formule est =T.TEST avec comme premiers arguments les deux matrices de prix. Je choisis une distribution unilatérale dans mon cas. Et le type, ça va être, est-ce que mes deux variances sont égales ? Donc, non, elles ne sont pas égales. Je vais prendre variances inégales à deux exemples.

Je vois que ce test est également très petit, très inférieur à 0,05. Donc, on peut rejeter le fait que nos moyennes soient égales et le test est très robuste vu que ce résultat de4,43 * 10^-102 est très, très inférieur à notre intervalle de confiance de 0,05. Il y a donc très peu de chance que ce soit aléatoire d'avoir ce résultat.

Le résultat de nos moyennes entre nos maisons et nos appartements sont bien des résultats différents entre les deux.

Vous savez mainteant comment faire un test statistique pour savoir si les deux moyennes (et variances) sont égales ou non.

Description alternative de l'illustration sur l'utilisation des tests de Student et de Fisher.

Analysez deux variables catégorielles :

Nous avons vu ensemble comment nous pouvons analyser deux variables quantitatives, puis une variable quantitative et une variable qualitative. Il nous reste maintenant un dernier test pour deux variables quantitatives, c'est le test du khi2.

Pour réaliser ce test, nous allons analyser la variable "Nature de la mutation" et la variable "Type de bien" (qui peut avoir comme valeur "maison" ou "appartement"). Concernant la variable "nature de la mutation", elle peut avoir comme valeur "Échange", "Vente" et "Adjudication" (cente aux enchères).

Nous voulons savoir s'il y a une relation entre les deux variables. Nous allons faire un test du Khi2. Pour commencer, nous devons faire ce qui s'appelle un tableau de contingence avec les valeurs. Pour ce faire, vous devez faire un TCD avec le type de bien en ligne et la nature de mutation en colonne. Comme valeur, nous allons compter le nombre de clés.

Excel n'étant pas spécialisé dans les tests statistiques, nous allons devoir passer par des étapes intermédiaires pour faire ce calcul.

Je vous laisse me suivre dans les explications suivantes pour voir la procédure et le résultat :

Nous allons voir ensemeble comment faire un test du khi2 dans Excel. Pour rappel, le test du khi2 vise à comprendre s'il y a une dépendance ou une indépendance entre nos variables. Dans notre cas, nous allons regarder les variables type local et nature mutation et essayer de comprendre s'il y a un lien de dépendance ou d'indépendance entre ces deux variables.

Pour commencer, nous allons toujours créer un TCD, dans lequel nous allons mettre notre nature mutation en colonne, notre type local en ligne et nous allons mettre le nombre de ventes. Nous avons donc un TCD qui nous donne le nombre, par exemple de ventes d'appartements ou de maisons, d'échanges et d'Adjudications.

Nous allons copier toutes ces informations, car nous allons devoir faire des modifications pour avoir les effectifs ovservés et les effectifs théoriques et ensuite appliquer notre test du khi2.

Les tests du khi2 dans Excel ne se font pas aussi facilement que les autres tests, il y a de la manipulation à faire avant.

Je copie donc mon tableau, je viens le coller en valeur et donc je vais venir dire ici que ça, c'est la partie Observé. Je nettpie mon tableau et donc ça, c'est le réel, c'est ce qui s'est réellement passé et ce que je vois dans mes données.

Je copie encore une fois tout ce tableau que je vais mettre en dessous et là, ça va être la partie Théorie.

Dans la partie Théorie, je supprime toutes mes valeurs et je viens calculer théoriquement ce que j'aurais dû avoir en pourcentage comme Adjudications, échanges et ventes.

Pour ce faire, ça, c'est un premier calcul assez simple, je prends les adjudications, que je divise par le nombre total que je bloque, et je vois rapidement que j'ai 6% d'adjudications, 4% d'échanges, 90% de ventes.

Moi, ce que je vais vouloir calculer maintenant, c'est, théoriquement, combien j'aurais dû avoir de ventes, par exemple, pour avoir ce chiffre-là.

Je vais venir calculer encore une fois en fonction de mon tableau que je viens de faire, de mes 6%, je vais venir multiplier par mon total général.

Qu'est-ce que je vois ici ? Je vois que, théoriquement, j'aurais dû avoir 277 adjudications quand, en réalité, j'en ai eu 280.

Je vais bloquertoutes ces formules et je vais passer cette formule juste à côté. Je vois que pareil, théoriquement, j'aurais dû avoir 176 échanges en théorie et 3949 ventes, quand j'en ai eu, par exemple, 3943.

Je vais faire la même chose en dessous en changeant légèrement ma formule.

Et voilà la théorie. Normalement, si tout va bien, la somme de tout ça doit toujours égal à 4844. Donc je vérifie, je vois que j'ai exacteemnt la même somme, mais j'ai des écarts entre les observations et la théorie.

Maintenant, je vais appliquer mes tests de khi2. Le premier test que je vais faire, c'est le test de comparaison entre les observations et la théorie. Donc ça sera un test qui va s'appeler =TEST.KHIDEUX et donc je lui donne ma plage réelle de données et je lui donne ma plage attendue théorique. Là, j'obtiens un chiffre de 0,53. Je vais venir réutiliser ce chiffre dans un autre test statistique.

Donc, avant de faire ça, je vais calculer le degré de liberté. Le dégré de liberté est un calcul qui dit que c'est le nombre de colonnes, donc à savoir 3, vu que j'ai adjudications, échanges et ventes, moins 1, multiplié par le nombre de lignes, donc dans mon cas c'est 2, j'ai appartement et maison, fois 1. Et donc je tombe sur un degré de liberté de 2.

Maintenant, je viens calculer la valeur de mon khi2 avec la formule qui s'appelle =KHIDEUX.INVERSE. Elle me demande la probabilité, qui est ce que j'ai calculé, 0.53, et le degré de lieberté que j'ai calculé. Et là, j'ai ma valeur de khi2.

La valeur de mon khi2, je vais la comparer à la valeur critique de mon khi2 à 5%, qui est, toujours la même chose, mon khi2 avec ma probabilité de 0.05 et avec mon degré de liberté. Je vois que ma valeur critique est de 5,99.

Je peux donc maintenant regarder les probabilités et me dire que si la valeur de mon khi, donc 1.25, est supérieure à ma valeur critique, 5.99, alors mes deux variables sont dépendantes. Si ce n'est pas le cas, alors mes deux variables sont bien indépendantes. C'est que je peux voir ici, c'est-à-dire que ma valeur de khi2 est inférieure à ma valeur critique à 5%. Donc je valide l'hypothèse H0 qui dit que les deux variables sont donc indépendantes.

Nous en avons enfin fini avec les tests statistiques de chaque type de variable.

Ah cool, ce n'était pas si compliqué en fait et il n'y a que trois tests ! Hum... Je vous arrête tout de suite sur ce dernier point, il n'y a pas que trois tests, bien au contraire. Il y a beaucoup d'autres tests statistiques ! C'est ce que nous allons brièvement voir.

Allez plus loin grâce aux tests statistiques :

Dans ce paragraphe, opubliez la vision d'Excel pour les tests statistiques. En effet, Excel n'est pas l'outil parfait pour faire des tests, même si certains tests sont désormais natifs dans l'outil. Le plus souvent, pour faire des tests statistiques, on a tendance à utiliser des logiciels dédiées à la statistique comme R ou encore Python. Si nous souhaitez pas utiliser un langage de programmation, vous pouvez utiliser une extension (payante) d'Excel qui s'appelle XLSTAT. Elle permet de réaliser une plus grande variété de tests avec une interface plus travaillée.

Mais pourquoi devez-vous faire d'autres tests ? Car, en réalité, il existe de nombreuses autres solutions à tous les tests. Si nous reprenons notre premier exemple, nous avons testé la corrélation de Pearson entre deux variables quantitatives. Mais ce test de corrélation peut se faire seulement avec des données qui suivent une loi normale.

C'est la différence entre un test paramétrique (données qui suivent une loi normale) et non paramétrique (données qui ne suivent pas une loi normale).

Dans notre exemple, Pearson, c'est le test paramétrique, alors que Spearman est le test non paramétrique.

Pourquoi ne pas en avoir parlé avant ? Simplement car Excel ne permet pas de faire le test de Spearman, il est seulement possible d'utiliser le test de Pearson.

Voici la liste des tests statistiques qui sont intéressants à connaître si vous avez envie de vous former :

Toutes les versions paramétriques des tests ci-dessus sont disponibles dans Excel (avec quelques variations comme le test de normalité).

Réalisez une modélisation :

Nous allons voir maintenant comment nous pouvons utiliser la régression linéaire pour faire des prévisions ou encore pour trouver des valeurs manquantes. Pour commencer, explorons les valeurs atypiques de notre jeu de données avec la technique de l'@cart interquartile.

Effectuez des modélisations performantes :

Nous allons faire une régression linéaire afin de prédire la valeur d'un bien en fonction des mètres carrés des appartements pour Paris.

Commençons donc par extraire tous les appartements avec leurs valeurs foncières et les surfaces Carrez en faisant un filtre sur Paris.

Nous avons donc un tableau de 4401 lignes. Créez un graphique sous forme de nuage de points afin de visualiser toutes les données.

Comme nous pouvons le voir psur le graphique, il y a une grosse concentration des données à gauche, puis quelques points se baladent un peu partout sur notre nuage de points.

En calculant notre coefficient de Pearson (avec la formule Pearson), nous constatons un coefficient de 0,63. Il y a donc une corrélation postive entre le prix et la surface.

Le problème, c'est que les modèles n'aiment pas vraiment les valeurs dites aberrantes ou atypiques.

Prenons par exemple le cas de notre point tout en bas à droite du graphique. Nous pouvons voir que c'est un appartement qui fait 9 m² Carrez pour un prix de 9 millions d'euros. Pour nous représenter ce chiffre, nous pouvons essayer de calculer le prix au mètre carré pour ce bien. Il est de 1 million d'euros. Sans être un spécialiste, nous pouvons facilement nous dire que ce n'est pas vraiment le prix moyen dans la capitale française.

Ok, vous voyez où on veut en venir, mais vous n'êtes pas un spécialiste de l'immobilier parisien et vous n'avez pas envie de me questionner sur tous les appartements. Je suis complètement d'accord avec vous, et c'est pour cela qu'il existe plusieurs techniques pour trouver des valeurs aberrantes ou atypiques. Dans notre cas, nous allons utiliser l'écart interquartile, que nous avons vu précédemment dans ce cours.

Voici le code pour la colonne "Valeur foncière" :

Sub ecart_interquartile()

Dim Q1 As Double
Dim Q3 As Double
Dim IQ As Double
Dim borne_basse As Double
Dim borne_haute As Double

Q1 = WorksheetFunction.Quartile(Range("B2:B4402"), 1)
Q3 = WorksheetFunction.Quartile(Range("B2:B4402"), 3)
IQ = Q3 - Q1

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

If borne_basse < O Then
    borne_basse = 0
    MsgBox ("attention borne basse inférieure à 0")
    Range("$A$1:$C$4845").AutoFilter Field:=1, Criteria1:=">" & borne_haute, _Operator:=xlAnd
    test = ">" & borne_haute
End If

End Sub

Nous voyons qu'il y a 137 valeurs qui sont considérées comme des valeurs aberrantes, nous allons les supprimer pour améliorer notre modélisation.

Je vous laisse faire la même chose pour la colonne "Surface Carrez", en supprimant également les données : normalement 11 valeurs.

Nous pouvons maintenat calculer un nouveau coefficient de Pearson sur nos données pour voir la différence, avec seulement 148 valeurs considérées comme atypiques. Nous trouvons un coefficient de Pearson de 0,89. Nous sommes maintenant en présence d'une corrélation très forte entre les deux variables.

Pour synthétiser, nous avons supprimé 3% des valeurs et le coefficient est passé de 0,64, qui est une corrélation moyenne, à 0,89, qui est une corrélation très forte.

Pour mieux comprendre, je vous propose de refaire un nuage de points avec ce nouveau jeu de données nettoyé.

Ce graphique confirme bien que le nettoyage des données que nous avons réalisé a eu un impact sur les données que nous avons gardées. Elles sont beaucoup plus homogènes.

Appréhendez la régression linéaire simple :

Maintenant que nous avons un jeu de données qui est propre, nous allons pouvoir passer à la régression linéaire.

Après les tests statiques, vous croyez vraiment qu'on veut essayer de vous perdre avec la régression linéaire ? Mais non, je vous rassure, le nom fait plus peur que la technique et nous allons voir tout cela ensemble.

Pour commencer, à quoi cela sert une régression linéaire ?

La régression linéaire permet de comprendre la dépendance entre deux variables, puis d'utiliser cette dépendance pour prédire des valeurs.

L'idée générale, c'est qu'une variable (la variable dépendante) est dépendante d'une ou plusieurs variables explicatives. Il est alors possible d'écrire l'équation linéaire qui permet de trouver les valeurs de la variable dépendante en fonction de la ou des variables explicatives.

Cette technique consiste à trouver la droite qui permet de réduire au maximum la somme des valeurs résiduelles au carré (c'est la méthode des moindres carrés ordinaires).

Comme nous pouvons le voir sur le graphique ci-dessus, la courbe en rouge essaye de se rapprocher le plus possible de l'ensemble des points. Nous pouvons voir qu'il y a une relation linéaire forte entre les deux variables dans notre graphique, car l'ensemble des points sont très proches de notre courbe. Le coefficient de Pearson sur ce graphique est 0,95 : la corrélation est donc très forte !

Maintenant que nous savons que nos deux variables sont très corrélées, nous pouvons supposer que s'il nous manque une valeur de Y, nous allons pouvoir la compléter en fonction de la valeur de X. C'est ce qu'on appelle la régression linéaire simple.

On dit que la régression linéaire est simple, car c'est une variable qui explique une autre variable. Nous verrons juste après le cas des régressions linéaires multiples.

Je vous laisse me suivre dans les explications pour la réalisation de la régression linéaire simple :

Nous allons voir ensemble comment nous allons pouvoir faire une régression linéaire dans Excel entre deux variables quantitatives. Nos deux variables vont être la surface Carrez et la valeur foncière.

Pour ce faire, nous allons commencer par, comme d'habitude, créer un nouveau TCD pour afficher ces valeurs. Je vais donc récupérer la clé, la valeur foncière, puis la surface Carrez de nos données.

Je vais récupérer toutes ces données que je vais coller dans une autre feuille. Et donc, je vois que je garde dans cette feuille les informations de mon ID, ma valeur foncière et ma surface Carrez. Je sais qu'à l'intérieur de mes données, j'ai certains vides, donc je vais venir faire du nettoyage rapidement en supprimant les vides. Et je vois que je n'ai pas mon total, je n'ai plus de vides dans le reste de mes données. Donx, je vais pouvoir faire ma régression linéaire.

Pour faire ma régression linéaire, je vais venir écrire et simplifier mes titres, pour que ce soit plus facilement compréhensible et je vais lancer, comme d'habitude, le module qu'on va retrouver dans "Données" et "Utilitaire d'analyse". Donc, je clique dessus, je clique sur "Régression linéaire", et là, je vois que je vais pourvoir choisir deux pages, ma plage Y et ma plage X. Dans mon cas, ma plage Y est ma valeur foncière. Donc, je vais venir la choisir sans les titres. Ma plage X est ma surface Carrez. Je vais venir la choisir également sans les titres. Je vais prendre, dedans, l'analyse des résidus, les résidus normalisés, courbes des résidus et courbes de régression, et je vais cliquer sur "OK".

On peut voir que Excel nous a créé, dans une nouvelle feuille, un rapport détaillé de l'intégralité de la régression linéaire.

Pour commencer, je vois que j'ai un coefficient de détermination multiple qui est à 0,82. Et ce coefficient, ce n'est ni plus ni moins que le coefficient de Pearson,et on peut voir que c'est exactement le même coefficient. Nous avons donc une forte corrélation entre les deux variables.

Ensuite, on voit en dessous un coefficient de détermination R² à 0,67. Ce coefficient peut aller entre 0 et 100, et c'est le coefficient qui explique la variance de notre modèle, ce qui veut dire que notre modèle, comme on le voit là, explique à peu près 70% de la variance de nos données. Pourquoi ça n'explique pas plus de 70% de la vairance de nos données ? Eh bien car on a une partie de nos donénes qui sont assez éloignées de la courbe de notre régression linéaire.

Si on regarde cette courbe, qui est la courbe de prévision versus le réel, on peut voir que notre courbe de régression linéaire est en orange, mais certaines données sont très très éloignées de cette courbe.

Qu'est-ce qu'on observe ensuite ? On observe ce deuxième graphique, qui est le graphique des résidus qui essaie de nous montrer justement les résidus, c'est-à-dire l'écart entre l'attendu et le réel.

Notre régression linéaire a donc de bons paramètres (le R² ainsi que la p-valeur). Nous pouvons donc en conclure que cette régression permet de bien expliquer les différentes valeurs et donc également d'en prédire. L'équation que nous avons vue permet de choisir, pour n'importe quelle valeur de X, une valeur de Y. Nous pouvons donc prédire pour chaque mètre carré à Paris une valeur de vente théorique. Nous aalons détailler ce calcul dans la prochaine section.

Réalisez une prédiction avec la régression linéaire :

Vous êtes maintenant capable de réaliser une régression linéaire et de comprendre les principaux paramètres de cette régression. Nous allons maintenant essayer de prédire des valeurs grâce à l'équation de la régression linéaire simple.

Ah ! Enfin la prévision ! Nous allons refaire notre régression linéaire, mais cette fois nous allons cocher "Résidus", puis "OK". En plus de notre rapprt détaillé et de l'analyse de la variance, nous obtenons un gros tableau avec la prévision des valeurs foncières et les résidus.

Les résidus ? Mais on devait pas parler de prévision ?! Nous allons y arriver : justement, la première colonne de ce gros tableau, c'est la prévision de notre modèle. Cette prévision est faite sur les valeurs que nous lui avons donné à étudier. Le modèle a utilisé les données pour faire une prédiction sur le réel.

Si nous prenons la première valeur, nous pouvons voir que la prévision est de 29533€, alors que dans notre tableau initial la valeur foncière est de 336740€. Il y a un écart assez considérable entre les deux valeurs, cet écart justement s'appelle le résidu, et il est de 307207€ pour cette première valeur.

Si nous regardons la moyenne des valeurs absolues (formule ABS) des résidus, nous pouvons voir qu'en moyenne notre modèle se trompe de 70000€ sur les prévisions.

Cela peut paraître beaucoup, mais notre modèle se base sur la totalité des ventes à Paris, et nous savons que le prix de vente dans le 16e arrondissement n'est pas le même que dans le 20e arrondissement. Si nous souhaitons affiner notre modèle, nous pouvons réaliser une régression linéaire multioke en rentrant de nouveaux paramètres explicatifs du prix (nombre de pièces, jardin, balcon, arrondissement, etc.).

Pour faire la prévision, il n'y a rien de plus simple : nous allons écrire l'équation qui a permis de faire la prévision.

Cette équation est sous la forme y = ax + b (dans le cas d'une régression linéaire simple).

Je vous détaille cela dans les explications ci-dessous :

Nous allons maintenant nous intéresser, après avoir vu les différentes statistiques de la régression linéaire, à savoir nos coefficients de détermination multiples, donc notre Pearson et le coefficient de détermination R², nous allons essayer de comprendre l'analyse des résidus et ce qu'on peut peut en faire pour faire de la prévision.

Dans un premier temps, on peut voir dans l'analyse des résidus que nous avons les observations, les prévisions pour Y et les résidus.

Pour essayer de comprendre tout ça, on va refaire les formules à la main. On va donc prendre dans un premier temps une partie des données, ou toutes les données, si vous voulez, qu'on va venir remettre dans la feuille.

Qu'est-ce que dans un premier temps une prévision pour Y ? La prévision pour Y, c'est le calcul que l'on va faire en utilisant les différents coefficients qui ont été calculés pour notre régression linéaire.

Donc, nous allons récupérer la surface Carrez, que nous allons multiplier par le coefficient de la variable X 1, sur lequel je vais la bloquer, et pour laquelle je vais également ajouter la constante de -73000. Ça, ce sont des données qui ont été fournies directement par régression linéaire. Quand je fais "Enter", j'observe que j'ai exactement le même chiffre que la prévision.

Maintenant, qu'est-ce que les résidus ? Les résidus, c'est juste l'écart entre ma valeur foncière et ma valeur valeur théorique.

Maintenant, pourquoi on regarde ça ? Parce qu'on vient de trouver ensemble comment on peut faire pour calculer la valeur foncière théorie de cet appartement.

Voilà un peu comment on peut essayer de faire de la prévision avec notre régression linéaire.

Maintenant, imaginons que j'aie une liste d'appartements pour lesquels je voudrais avoir un prix, je n'ai qu'à utiliser ma régression linéaire pour, dans un premier temps, essayer de fabriquer un prix qui permettra de coller avec les courbes de régression.

Pour améliorer ma prévision, je pourrais toujours essayer d'améliorer mon coefficient de détermination multiple et mon coefficient de détermination R² en, par exemple, essayent de supprimer dans un premier temps les autres (R), en relançant ma régression linéaire pour voir si elle est meilleure, etc. L'idée étant d'arriver à un coefficient de détermination R² le plus près possible de 1, puis ensuite, d'utiliser les données de la régresion linéaire pour faire de la prévision sur, dans notre cas par exemple, de la valeur foncière.

C'est donc Surface Carrez = Valeur foncière * coefficient de la surface Carrez + la constante.

Pour la première ligne nous avons donc : Prévision valeur foncière = 0,5 * 10287 + 24390.

Ce calcul nous donne 29533, exactement la valeur de la prévision réalisée par la régression linéaire.

Si maintenant je veux connaître le prix de vente d'un appartement de 54,45 m², il me suffit de remplacer la taille de l'appartement :

Prévision valeur fonctière = 54,47 * 10287 + 24390. Ce qui nous donne une prévision de prix pour l'appartement de 584722€.

Créez une base de données relationnelle avec Access :

Précédemment, nous avons appréhender l'automatisation d'une analyse. Nous sommes parties des extractions brutes que nous avons transformées et concaténées, puis analysées.

Dans la suite du cours, nous allons nous attarder sur deux outils que nous n'avons pas encore vus, mais qui sont souvent très utilisés dans les entreprises. La bonne nouvelle, c'est que le VBA peut également interagir avec ! Il s'agit d'Access et de Power Query.

Maintenant, nous allons voir ensemble pourquoi il est parfois préférable d'utiliser une base de données Access plutôt que des fichiers Excel. Nous verrons que nous allons pouvoir nous connecter à la base de données en VBA pour ajouter des données à certaines tables ou encore récupérer le résultat des requêtes SQL que nous aurons au préalable créées dans Access.

Appréhendez le concept de base de données relationnelle :

Qu'est-ce qu'une base de données relationnelle ? Commençons par définir le concept même d'une base de données.

Une base de données c'est un ensemble d'informations (des données) qui sont organisées et accessibles. Les données sont organisées dans des tables (on peut faire le parallèle avec une feuille Excel). Dans chaque table, il peut y avoir plusieurs centaines/milliers/milliards de lignes et de colonnes.

Une base de données permet de stocker une grosse quantité d'informations. Nous parlons de base de données relationnelle quand il y a des liaisons entre les différentes tables qui permettent de les mettre en relation.

Reprenons le cas de notre agence immobilière qui a des données sur Marseille, Toulouse et Montpellier, où chaque ligne de la base de données correspond à une vente.

Posons-nous maintenant la question : qu'est-ce qu'il se passe si un bien est vendu deux fois ? L'intégralité des informations est rentrée dans une nouvelle ligne avec les mêmes informations (le bien n'a pas changé d'adresse), sauf la valeur du bien, qui a pu changer, et la date de la signature de l'acte de vente.

Si nous souhaitons optimiser nos données, nous n'avons pas besoin de stocker l'adresse uen deuxième fois. C'est pourquoi nous avons créé une relation qui nous permet de garder l'information de la première vente, sans nous coûter plus de stockage pour la seconde vente.

Pour des applications vraiment gourmandes en ressources, on va souvent se tourner vers des bases de données performantes comme Oracle, MySQL, Microsoft SQL Server, PostGreSQL. Pour des applications plus simples, on utilise souvent Access, qui est directement inclus dans la suite Microsoft, ou des logiciels comme SQLite.

Créez des bases de données Microsoft Access :

Nous allons donc créer cette base de données ensemble.

Nous allons voir ensemble comment nous allons pouvoir créer une base de données dans Access, créer des tables associées, puis venir remplir ces tables avec nos données.

Pour faire cela, on a besoin de deux fichiers CSV, adresse.csv et vf.csv, à gauche et à droite nous avons la base de données que nous venons juste d'enregistrer.

Pour créer la première table, nous allons aller dans "Créer", cliquer sur "Table". Nous allons faire un clic droit sur "Table 1", appuyer sur "Enregistrer" et donner le même nom que le premier fichier, à savoir "Adresse". Je clique sur "OK", je refais un clic droit et je retourne en mode création.

L'important maintenant, c'est que le nom des champs soit égal au nom des colonnes. Je vais donc créer mon premier champ qui va s'appeler "ID_Adresse" et je vais lui donner un type pour mes données, c'est donc du numérique. Je vais faire la même chose avec les autres colonnes, "ID_vf", c'est également du numérique. Le "No_voie", c'est également du numérique.

Puis on passe sur le "type_voie" qui, cette fois, c'est du texte. La "voie", ça sera du texte. Le "code_postal", que je vais considérer comme du texte, et la "Commune" qui est du texte.

Je vais donc maintenant un clic droit sur ma table et je clique sur "Enregistrer".

Je vais maintenant créer une deuxième table, en recliquant sur "Table", je refais la même chose, "Enregistrer". Cette fois, elle va s'appeler "VF", je clique sur "OK", reclic droit "Mode création".

C'est comme tout à l'heure, l'idée, c'est que la table VF, les champs dans le CSV correspondent également au nom des champs de la table.

Le premier champ, c'est "id_vf", c'est un numérique. Ensuite, nous avons la date de mutation qui, cette fois, est une date. La nature de la mutation, qui est un texte.Pour finir, la valeur foncière qui est numérique.

Je vais faire un clic droit "Enregistrer", puis je vais fermer mes deux tables et je vais venir faire un clic droit à gauche sur la table "Adresse", et importer fichier texte, vu que c'est du CSV.

Dans cette nouvelle page, je viens lui donner le lien de mon fichier, et je viens lui spécifier que ce fichier doit être enregistré dans la table Adresse que nous venons de créer. Je clique sur "OK", je vois que j'ai mon CSV qui s'affiche, je vais cliquer sur "Suivant", et je vais faire "Terminer".

Je vois que Microsoft Access me dit qu'il y a des problèmes avec certains enregistrements, mais vais quand même continuer cet enregistrement-là. Je dis "OK" et je ferme.

Je vois qu'une nouvelle table a été créée, qui s'appelle la table d'erreur d'importation, et je vais ouvrir cette table et je vois que ça correspond en fait à ma première ligne.

Et c'est normal, vu que la première ligne de "ID_Adresse" ne correspond pas au type de fichier qui a été défini au départ. Donc la ligne 1 n'a pas été importée. Ce qui est normal, c'était mes en-têtes de colonne. Jusque là, rien de très grave. Je vais faire un clic droit et le supprimer.

Je vais faire la même chose pour la table "VF" en important le fichier "VF.csv" et je supprime l'erreur d'importation car c'est encore la première ligne qui pose problème.

Nous venons donc ensemble de créer une première base de données qui contient deux tables. Et les deux tables ont été remplies avec les différentes données des fichiers CSV associées.

Je partage avec vous les fichiers "adresse.csv" et "VF.csv" à télécharger pour charger avec moi notre base.

Nous voilà donc maintenant en présence d'une base de données, mais sans relation encore.

Pour créer la reltion, Access a besoin de savoir quelles sont les colonnes qu'il doit utiliser. Dans notre cas, c'est la colonne "ID_VF" (VF correspondant à Valeur Foncière).

La table "Adresse" contient les adresses des biens et la table "VF" contient seulement la date, la nature de la vente et la valeur de cette vente. Ainsi, si un bien est vendu plusieurs fois, il ne va créer de doublon dans les adresses (cela permet d'optimiser les données).

Pour faire la jointure, il me suffit de prendre la colonne "ID_VF" de la table "Adresse" et de la mettre sur la ligne "ID_VF" de la table "VF".

La jointure que vous venez de créer, c'est la relation entre les deux tables. Pour que les deux tables communiquent ensemble, vous avez créé une liaison.

Si vous double-cliquez sur la jointure, vous obtenez les informations suivantes :

Nous avons la table de gauche avec le nom de la colonne ("ID_VF") qui est jointe avec la table de droite par la colonne du même nom ("ID_VF").

Les jointures contiennent toujours une clé primaire et une clé étrang!re dans les tables.

Maintenant que notre base de données est prête et fonctionnelle, nous allons pouvoir l'exploiter.

Pour créer une requête, il y a deux possibilités :

• l'éditeur de requête Access;

• le SQL.

Nous allons voir dans un premier temps l'éditeur de requête Access, qui permet de réaliser des requêtes, sans faire de code SQL.

Pour ce faire, vous devez :

• aller dans l'onglet "Créer", puis "Création de requêtes";

• dans cette nouvelle fenêtre avec vos deux tables, sélectionner les deux et cliquer sur "Ajouter";

• cliquer sur "Fermer".

Pour la création de la requête, je vous invite à suivre les explications ci-dessous :

Nous allons maintenant repartir de notre base de données contenant deux tables, la table "Adresse" et la table "VF", et nous allons faire notre première première requête SQL ensemble.

Dans notre cas, ça va être un petit peu plus simple. Nous n'allons pas vraiment écrire du SQL, mais nous allons utiliser l'assistant de requête pour nous aider à crée une requête SQL.

Nous allons donc cliquer sur "Création de requêtes". Nous allons importer les deux tables, la table "Adresse" et la table "VF", et nous allons commencer par regarder nos tables. On peut voir qu'il y a un lien qui a été créé entre les deux, et le lien a été fait entre les deux colonnes, qui a trouvé en commun, à savoir la colonne "ID_VF" de chaque côté. Ce qui est très bien.

Pour ma première requête, je souhaite connaître le nombre d'enregistrements qui ont été faits en fonction des communes. Je vais venir choisir, par exemple, ma table "Adresse" et prendre mes communes.

Si je prends seulemnt comme ça et que j'exécute ma requête, je vois que j'ai eu toutes les communes qui existent dans ma table.

Moi, ce que je veux, c'est avoir le nombre d'enregistrements en fonction de chacune des communes. Je vais donc refaire un clic droit sur ma requête, repartir en mode création et je vais rajouter une nouvelle information. Je vais venir compter le nombre à chaque fois qu'il y a dedans. Je vais venir compter le nombre d'ID.

Et cette fois, je ne vais pas prendre le regroupement, mais je vais faire un calcul. Je vais prendre le compte pour compter. Je vais également appliquer un tri qui va être décroissant et je vais réexécuyer ma requête.

Quand je la réexécute, je vois que j'ai bien le nom de mes communes. J'ai le compte, le nombre d'enregistrements. Et le tout classé du plus grand au plus petit.

Si je veux exécuter cette requête plusieurs fois, par exemple, je peux enregistrer ma requête, on va l'appeler "test", par exemple, dans notre cas, et je vois que ma requête est arrivée à gauche.

Si par hasard, je venais à fermer ma requête et que je voulais la réexécuter, je n'ai qu'à double-cliquer dessus et le calcul se refait automatiquement.

Si je veux la remodifier, je peux passer par le mode création et pouvoir rajouter d'autres informations, comme par exemple la moyenne de la valeur foncière. Cliquer sur "Exécuter". Et là, nous voyons la moyenne de la valeur foncière pour chacune des communes.

Si maintenant, je voulais venir écrire du SQL ou voir comment le SQL a été écrit, je peux faire un clic droit sur ma requête, passer à "mode SQL" et là, je vois le code SQL associé à cette requête.

Voilà, on a fait ensemble notre première requête SQL avec le "mode création" ou le "mode SQL" si on a envie directement de modifier le SQL généré par Access.

Il n'y avait pas une autre méthode aussi ? Bien vu ! L'éditeur de requête Access permet de faire des requêtes simples. Quand on a besoin de faire des requêtes plus compliquées ou qu'on est habitué au SQL, c'est plus facile de l'écrire directement.

Par exemple, la requête que nous avons faite plus hait ne prend que quelques lignes.

SELECT Adresse.commune, count(VF.Valeur_fonciere)
FROM Adresse INNER JOIN VF ON Adresse.ID_VF = VF.[ID_VF]
GROUP BY Adresse.commune;

Dans notre cas, nous avons créé une base de données sur Access pour pouvoir utiliser facilement le VBA et récupérer des tabels ou faire des calculs avec nos données. C'est une base de données qui a l'avantage d'avoir une interface graphique simple et qui permet, sans savoir écrire de SQL, de faire des requêtes (des calculs, des filtres, etc.) dans notre base de données.

Utilisez vos connaissances en VBA pour interagir avec Access :

Maintenant que notre base de données est fonctionnelle, nous allons pouvoir l'utiliser en VBA.

Tout comme les autres applications de la suite Office, Access est accessible en VBA via Excel. (On peut également créer des macros directement dans Access.)

Nous allons donc repartir dans le VBE pour récupérer les données d'une table.

Pour cela, nous allons utiliser la méthode "OpenDatabase" de l'objet "Workbook".

Voici le code associé :

Sub extract_table()

Dim sTableName As String
Dim oWbk As Workbook
Dim sWbkName As String

'   Ouvre la table sélectionnée dans un nouveau Workbook
sTableName = "Adresse"
Set oWbk = Workbooks.OpenDatabase(Filename:="I:\BDD Access\BDD - Reproting.accdb", CommandText:=Array(sTableName), CommandType:= xlCmdTable)

'   Enregistre et ferme le fichier
sWbkName = "I:\BDD Access\test.xlsx"
oWb.SaveAs sWbkName
oWbk.Close

End Sub

Comme vous pouvez le voir, c'est assez simple d'extraire des données d'une table dans Access. Vous pouvez également exécuter des macros VBA qui vont lancer une requête et transférer le résultat directement dans Excel ou encore extraire des données, les traiter et les renvoyer dans une nouvelle table sur Access.

Comme nous l'avons vu depuis le début de ce cours, il suffit de chercher les objets disponibles et leur fonctionnement pour interagir avec tous les logiciels.

Déployez vos analyses grâce à Power Query :

Nous avons vu précédemment comment nous pouvons utiliser Access pour stocker nos données. Nous allons voir maintenant un nouvel outil qui s'appelle Power Query. Cet outil est un ETL (pour Extract Transform Load). Il existe plusieurs logiciels d'ETL, comme Altervx, Knime, Dataiku ou encore Power Query à plus petite échelle.

Découvrez les fonctionnalités de Power Query :

Power Query est présent depuis plusieurs années chez Microsoft est particulièrement important.

Cet outil permet de se connecter à des sources de données et de faire une transformation des données directeemnt dans Excel.

Ce qui est encore plus important, c'est que cet outil est maintenant présent dans l'outil de visualisation de données de Microsoft Power BI. En effet, Power Query est une des briques principales de Power BI.

Nous allons voir dans la suite de ce cours comment nous pouvons importer des données et commencer à les traiter. Mais il faudrait un cours complet pour pouvoir tout comprendre sur cet outil (transformation avanacée, langage DAX, etc.).

En fait, c'est encore un outil qui permet de stocker des données et de les traiter ? Dans l'ensemble, je peux vous répondre oui, mais cela va plus loin. Demandons-nous pourquoi nous avons besoin d'un outil comme celui-là ? Qu'est-ce qui a changé cette dernière décennie ?

Vous auriez etndance à dire le volume des données peut-être, ce qu'on appelle le big data ? Le big data est effectivement une tendance des dernières années avec la multiplication du volume des données. Mais nous pouvons également noter la diversité des données. Il y a quelques années, nous n'utilisons que "quelques" sources de données, aujourd'hui nous avons des données partout, et c'est justement sur ce point que Power Query devient intéressant. Cet outil permet de faire la jonction entre de gros volumes de données (sans exagérer, il ne s'agit pas de bases de données de plusieurs dizaines de Go) et, surtout, plusieurs sources de données.

Nous pouvons donc rapidement connecter une base Access avec trois fichiers Excel, une base MySQL et une base de données Azure.

Voilà la vraie valeur des logiciels d'ETL, ils permettent de faire la jonction entre toutes les sources de données, tout en étant assez puissants pour les transformer, puis les mettre à disposition pour l'analyse.

Pour réaliser les manipulations en même temps que moi, je vous invite à télécharger la base de données "Département.xlxs".

Ajouter des données dans Power Query :

De notre côté, nous allons utiliser cet outil dans Excel.

Pour ce faire, nous allons commencer par lancer cet outil :

• aller dans l'onglet "Données";

• cliquer sur "Obtenir des données";

• puis lancer l'éditeur Power Query.

Après quelques secondes de chargement, nous nous retrouvons avec une fenêtre comme celle-ci dessous :

Mais, il n'y a rien dans cette fenêtre ? C'est normal, pour le moment, notre outil est vide. Cet outil s'appelle un ETL, donc nous allons commencer par la première lettre, "E" pour Extract (extraction des données).

Commençons ensemble par charger les données de notre base de données Access :

• cliquer sur l'onglet "Accueil";

• puis cliquer sur "Nouvelle source";

• choisir "Base de données";

• cliquer sur "Access".

Il nous suffit maintenant d'indiquer à l'outil où est notre base de données sur l'ordinateur, puis de cliquer sur "Ouvrir".

Il ne vous reste plus qu'à choisir les deux tables et à cliquer sur "OK". Ajoutons maintenant un fichier Excel contenant :

• le numéro du département;

• le nom du département;

• le nom de la région;

• le regroupement.

Nous avons maintenant accès à une base de données ainsi qu'à un fichier de données directement dans Excel.

Automatiser le nettoyage des données :

Nos données sont maintenant accessibles dans Power Query. Nous allons passer à la deuxième lettre d'ETL avec le "T" pour Transform (transformation). Imaginons que nous souhaitons trouver pour chaque ville quel est le département de rattachement en utilisant les données du fichier Excel.

Si nous regardons notre table "Adresse", nous voyons que nous n'avons pas le département, mais seulement le code postal.

Nous allons donc créer une nouvelle colonne qui va récupérer les deux premiers caractères du code postal. Nous allons appeler cette colonne "Code département".

Pour ce faire nous allons :

• cliquer sur la colonne "Code_Postal";

• allzr dans l'onglet "Ajouter une colonne";

• puis "Extraire" et "Plage";

• "Index de départ" : 0;

• "Nombre de caractères" : 2.

Nous venons de créer une colonne qui contient maintenant le code département. Nous allons renommer cette colonne "Département".

Dans la fenêtre de droite, nous pouvons voir l'historique, les "étapes appliquées".

Si nous cliquons maintenant sur "Navigation" dans cette fenêtre, nous pouvons voir que la colonne a disparu. Si nous cliquons à nouveau sur "Colonnes renommées", la colonne revient.

Powser Query garde en historique les différentes actions que vous avez réalisées dans le fichier.

C'est très utile pour plusieurs raisons.

La première, c'est que c'est intéressant de remonter le nettoyage des données que nous avons mise en place. Ainsi, si nous changeons les données dans notre base Access, nous n'aurons pas besoin de refaire le nettoyage, mais seulement d'actualiser Power Query. Il se chargera alors de refaire lui-même les mêmes étapes de nettoyage.

Il nous manque maintenant la liaison entre notre département du fichier "Adresse" que nous avons créé et le code du département dans le fichier "Département".

Nous allons faire un clic droit sur la partie "Requête", puis choisir "Nouvelle requête", aller sur "Combiner" et pour finir "Fusionner les requêtes comme nouvelles".

Dans cette nouvelle interface, nous allons choisir en premier la table "Adresse", puis la table "Département". Pour finir, nous allons cliquer sur la clé en commun entre les deux tables : le code département.

Vous devriez avoir une fenêtre qui ressemble à cela :

Vous n'avez plus qu'à cliquer sur "OK" pour obtenir une nouvelle table qui est la fusion entre la table "Adresse" et la table "Département".

Maintenant que notre fichier contient toutes les informations (départements et régions), nous pouvons l'utiliser sur Excel pour notre analyse.

Vous pouvez également essayer d'ajouter une nouvelle adresse dans votre base de données dans la base Access (pour plus de facilité, vous pouvez ajouter une adresse hors départements 30, 34 et 13 que nous avons déjà traités).

Vous n'avez plus qu'à repartir dans Power Query, puis aller dans "Accueil" et cliquer sur "Actualiser", puis "Actualiser tout". Si tout s'est bien passé, vous devriez avoir une nouvelle ligne avec votre adresse et les informations relatives au département et à la région de votre code postal.

Pour finir et pour faire le lien avec le VBA, nous pouvons mainteant demander à notre code VBA de lancer une mise à jour automatique sur nos données.

La ligne de code est assez simple pour faire la mise à jour :

ActiveWorkbook.Connections("Requête - Adresse avec département").Refresh

Nous n'avons fait qu'effleurer le potentiel de Power Query.