Exemple1

1. Exemple de données ouvertes

Connectez-vous sur le site ici.

Étudiez le site et les informations présentes. Ci-suivent une liste non exhaustives de points qui peuvent être examinés :

Peut-on se fier au site ?
Quel est le but général du site ?
Qu'est-ce que des données ouvertes ? (open data)
Peut-on se fier aux données ?
Avons-nous le droit d'exploiter les données ?
Que savons-nous sur les données ?
Sous quels formats les données sont accessibles ? En existe-t-il un exploitable par un tableur ?
Est-ce que le site est la source originel des données ? Si non, quelle est la source des données ?

2. Récupérez les données

2.1. Récupérez le fichier au format CSV disponible sur le site. (En cas d'indisponibilité du site, vous pouvez aussi récupérer les données ici).

Remarque : des explications sur le format CSV sont disponibles ici et notamment des explications sur l'ouverture d'un fichier au format CSV avec Open Office/Libre Office et avec Microsoft Excel.

2.2. Ouvrez le fichier CSV avec le logiciel de votre choix).

Attention à l'encodage (accents s'affichant correctement) et au séparateur (unique).

2.3. Ajoutez une feuille que vous nommerez source et dans laquelle vous écrirez l'URL du site où vous avez récupéré les données (utile si nous avons besoin de revenir au site ou de le citer ultérieurement) et, si ce n'est pas le même site, le site originel des données.

2.4. Sauvegardez les données dans un format plus adéquat (ods si vous utilisez Libre Office Calc ou Apache Open Office Calc ; xlsx si vous utilisez Microsoft Excel). Pensez par la suite à enregistrer régulièrement votre fichier (apprenez le raccourci clavier d'enregistrement).

2.5. Supprimez la colonne K intitulée geom.

2.6. Observez que le format des nombres de la colonne J intitulée prix_de_revient_moyen_en_eur_ttc_logt_2016_2018 est incorrect : le séparateur de décimales est le point et non la virgule comme d'usage en France (si la langue de base de votre tableur n'est pas le français, vous pouvez ne pas rencontrer ce problème). En écrivant en L2 la formule =J2+1, vous pouvez vérifier que le nombre est considéré comme du texte et non comme un nombre (une erreur se produit). Pour la suite, supprimez cette formule. Notons que nous aurions pu éviter ce problème lors de l'ouverture du fichier CSV mais, en pratique, on découvre souvent le problème après ouverture.

Pour corriger ce problème, remplacez les points par des virgules dans toutes les cellules de la colonne K et uniquement dans ces cellules (sélectionnez la colonne et utiliser la fonction de recherche-remplacement).

Si vous n'avez pas réussi les manipulations précédentes, vous pouvez continuer la suite de l'exemple en travaillant avec ce fichier au format xlsx ou celui-ci au format ods.

3. Appréhendez et structurez les données (sans formules)

Modifiez les en-têtes de colonne de manière à ne pas avoir de tiret bas et en remettant les accents.
Centrez horizontalement les noms de colonne et mettez les en gras.
Retrouvez les unités manquantes dans les en-têtes de colonne (voir le site originel).
Adaptez les largeurs des colonnes à leur contenu ?
- Si nécessaire, formatez les cellules de titre pour obliger une écriture sur plusieurs lignes.
- Centrez les titres verticalement.
- Si nécessaire, redimensionnez la ligne de titre
Assurez-vous que les 4 colonnes contenant des pourcentages aient la même largeur ?
Allez en J96. À quoi correspond la valeur de cette cellule ?
Placez des volets de manière à faciliter la lecture des données ? (Que pensez-vous de se placer en E4 avant de ne placer les volets (figer/fixe lignes et colonnes) ?)
Placez un autofiltre. Utilisez-le pour détecter (ou vérifier) la colonne qui n'a qu'une seule valeur. Reportez cette valeur dans la feuille source puis supprimer la colonne.
Est-ce que tous les codes de département sont définis ? Si non, sélectionnez les départements concernés et saisissez leurs codes ? Si nécessaire, alignez à gauche les codes.
Pour les colonnes indiquant des pourcentages de financement où des prix de revient ne sont pas indiqués, saisissez le texte NR.
Formatez les cellules de prix de revient en format monétaire avec un chiffre après la virgule. Si des ### apparaissent, que signifient-ils ?
Séparez les latitudes et longitudes dans la colonne de géolocalisation (utiliser Données/Convertir avec Microsoft Excel et Données/Texte en colonnes avec Libre Office Calc ou Apache Open Office Calc).
Appelez latitude département et longitude département les colonnes contenant des données de géolocalisation (de manière cohérente aux données).
Formatez les largeurs de colonne de manère adaptée au contenu. Alignez les valeurs à droite.
Formatez les cellules de manière à encadrer tout le tableau d'un gros trait bleu et les traits internes en pointillés rouge fins.
Faites en sorte que la bordure sous les titres soient également un gros trait bleu.

4. Quelques interrogations (et réponses) sur les données (toujours sans calcul)

Quel est le département dont le prix de revient moyen d'un logement est le plus cher ?
Quel est le département dont le prix de revient moyen d'un logement est le moins cher ?
Quel est le nombre de départements ayant moins de 3% de financement en fond propres ?
Quel est le nombre de départements ayant plus de 25% de financement en fond propres ?
Triez les données par ordre décroissant des prix de revient des départements. Puis :
- Observez les effets sur le formatage des cellules. Quel conseil pouvons-nous en déduire pour le futur ? Réponse ici
- Observez en particulier le formatage sur la colonne longitude. Quel problème révèle-t-il ?.
- Annulez l'opération de tri. Replacez l'auto-filtre. Re-triez les données.
- Triez les données par ordre croissant des numéros de département. Est-ce que cela s'est passé correctement ? Regardez ici si vous ne voyez pas ce qui pose souci
- Transformez tous les codes de département en texte et alignez-les à droite.
- Ré-effectuez le tri sur les codes de département et analysez les problèmes (avec Libre Office Calc, vous pouvez essayer le tri naturel pour améliorer le résultat du tri).
- Triez les données par ordre croissant des numéros des régions et pour chaque région par ordre croissant des noms des départements.

5. Vérification de cohérence des données

Les 4 colonnes contenant des pourcentages de financement par origine semblent couvrir tous les cas de financement. La somme des pourcentages semble faire 100. Est-ce le cas ? Si non, pourquoi ?
Pour résoudre cette question, n'hésitez pas à calculer la somme des pourcentages dans de nouvelles cellules et à analyser les résultats.

Vous trouverez ci-après l'état des fichiers après la réalisation des 5 premières étapes au format xlsx, soit au format ods.

Ces fichiers peuvent vous servir de correction. Vous pouvez continuer. Il peut être Pour continuer, vous êtes invités à travailler avec les fichiers contenant une correction de ce qui précède disponibles, selon votre choix, soit

Quelques notions et manipulations générales vues ou revues pendant les 5 premières étapes

Référence/adresse d'une cellule : nom de la colonne + numéro de ligne ; par exemple J96.
Accès direct à une cellule : dans la zone de nom (où on voit la référence de la cellule), on peut saisir la référence d'une cellule à la quelle on veut accéder.
Le raccourci clavier CTRL-flèche vers le bas sous Windows et CMD-flèche vers le bas sous Mac permet de se placer en bas d'un tableau si toutes les valeurs de la colonne où nous sommes initialement placés contient des valeurs (si ce n'est pas le cas, on accède à la cellule qui précède une cellule non remplie.
Volets (voir menu ou onglet Affichage avec Libre Office Calc ou Excel) : cette fonctionnalité permet de figer/fixer des lignes en haut de la fenêtre d'affichage ou des colonnes à gauche ; son usage facilite la lecture des données et les opérations de sélection. À utiliser pour gagner du temps et du confort d'utilisation.
Il est possible de sélectionner une plage de cellules. Cela entraîne très régulièrement un défilement long, pénible et peu productif de l'affichage. La technique Clic suivi de SHIFT-clic est très souvent beaucoup plus adaptée (clic = sélection à la souris de la cellule le plus en haut à gauche ; SHIFT-clic = enfoncement de la touche temporaire de changement de casse temporaire (passage de minuscule à majuscule) puis en laissant cette touche enfoncée, sélection à la sourcis de la cellule le plus en bas à droite). Notamment cette technique est très adaptée si des volets ont été placés et que l'affichage permet de voir la première et la dernière ligne de la plage de cellules à sélectionner.
La sélection de colonnes s'effectue au niveau des noms des colonnes (A). Idem pour des noms de lignes.
Pour redimensionner une colonne, il suffit de déplacer le trait à droite du nom de la colonne. Si plusieurs colonnes sont sélectionnées, le redimensionnement s'impose de manière uniforme à toutes les colonnes.
Similairement pour des lignes.
Auto-filtre : Fonctionnalité permettant d'une part de trier des données selon un critère simple (attention à bien placer l'auto-filtre sur l'ensemble des colonnes du tableau sinon les données peuvent être modifiées et, en conséquence, être erronées) et d'autre part dafficher des données correspondant à des critères.
Cet auto-filtre est à utiliser pour répondre aux questions 3.9, 3.10, 4.1 à 4.4 et à la question 5 (pour cette question 5, après avoir effectué mis en place une formule calculant la somme des 4 pourcentages sur toutes les lignes, l'auto-filtre permet de vérifier que seules les valeurs 100 et 0 sont calculées et que 0 n'apparaît que dans le cas où les données ne sont pas renseignées).
Si des ### apparaissent, cela signifie que la largeur de la colonne n'est pas assez grande pour voir le contenu de la cellule vu le format demandé.
Mise en place d'une première formule. Le signe = en début de formule est là pour indiquer au tableur qu'il faut calculer et afficher le résultat de ce qui suit.
Pour faire une somme, on peut utiliser le symbole + tout simplement. On peut également utiliser la fonction SOMME. Cette dernière remplace, si nécessaire, les textes par la valeur 0 pour effectuer un calcul ce qui évite d'avoir une erreur.

6. Transformation des données en pourcentage

Usuellement dans un tableur, les cellules contenant des pourcentages sont affichés via des options de format suivi du symbole %. Faites en sorte que ce soit le cas.
Pour cela, vous aurez besoin d'utiliser des cellules intermédiaires pour : diviser les valeurs par 100. Le changement des cellules de données s'obtiendra via un collage spécial de valeurs avant de formater les cellules.
Aspect avancé. On peut observer qu'il y a un problème pour les cellules qui contiennent du texte. Il est possible de traiter les cellules contenant du texte avec la fonction SIERREUR.
Remarque. On peut se demander ce qu'il se serait passer si on n'avait pas écrit NR dans des cellules. N'aurions-nous pas introduit une erreur dans la transformation proposée précédemment ?

Ci-suit une correction de cette étape : au format xlsx, au format ods.

7. Un tableau de bord

Nos données sont prêtes (on pourrait refaire un formatage des cellules). Faisons quelques calculs de synthèse.

Créez une feuille appelée synthèse. Dans la suite, travaillez la présentation des données dans cette feuille.
Calculez le nombre de départements concernés par l'étude (indication : utilisez la fonction NBVAL).
Calculez le nombre de départements pour lesquels les données de prix de revient sont renseignées (indication : utilisez la fonction NB).
Pour chaque source de financement, calculez le pourcentage moyen d'utilisation sur l'ensemble des départements. Présentez les résultats dans un tableau.
Ajoutez dans ce tableau les pourcentages médians pour chaque source de financement.
Utilisez un graphique pour afficher les moyennes contenues dans le tableau précédent.
Créez un tableau permettant de calculer à partir des données la moyenne des prix de revient par région (indication : utilisez un tableau croisé dynamique) : affichez les résultats en face des noms de région.
Attention ! Ici nous réalisons un calcul qui n'est pas économiquement interprétable. Pour avoir une moyenne plus exacte, il faudrait revenir aux données initiales (que nous n'avons pas) sur les logements et, pour chaque région, sélectionner les logements et calculer la moyenne de ces prix de revient.
Remarque : le tableau croisé dynamique nous permet de récupérer entre autres la liste des noms de région
Calculez la moyenne des moyennes obtenues dans le tableau. Est-ce que c'est la même que la moyenne globale indiquée ? (Normalement non et cela souligne le problème indiqué précédemment)
Cachez ce dernier calcul de moyenne. Nous n'allons pas l'afficher dans notre synthèse.
Question avancée. Obtenez le même tableau que précédemment en n'utilisant pas un tableau croisé dynamique mais en utilisant la fonction MOYENNE.SI.

Ci-suit une correction de cette étape : au format xlsx, au format ods.

Reproduire le travail précédent ?

Le site data.gouv.fr consulté en début d'exemple indiquait que les données mises à disposition existait aussi pour la période 2017-2019. Le travail précédent pourrait être repris.

Si les données sont présentées de la même manière, en copiant-collant les données de 2017-2019 à la place des données 2016-2018, notre tableau de bord devrait effectuer la synthèse 2017-2019 sans avoir à réécrire de formule (mais peut-être en réactualisant les tableaux croisés dynamiques).

Il est également possible de chercher à regrouper les informations sur un même tableau et d'étudier l'évolution mais cela nécessite d'autres techniques.