Activité 2c: Croiser les données

Lorsqu’on dispose de deux tables qui ont au moins un descripteur en commun, on peut obtenir une nouvelle information, on peut créer une nouvelle table en rajoutant un ou plusieurs descripteurs absents dans une des deux tables.

Prenons les deux tables suivantes :

Question 6 : Quel est le nombre d’habitants de la ville du musée Alpin ?
Question 7 : Dans quelle région se trouve le musée qui a le plus petit nombre d’entrées ?
Question 8 : Combien y a-t-il de musées dans la ville qui a le plus grand nombre d’habitants ?

Dès que les tables contiennent un nombre important de données, il est impossible de faire des recherche manuellement.
 

Dans Libre Office, on aura besoin d’une fonction qui vérifie si une valeur spécifique est contenue dans la première colonne d’une matrice. Elle renvoie alors la valeur dans la même ligne de la colonne désignée par index.

La formule correspondante à écrire dans une cellule est  =RECHERCHEV(critère_de_recherche;matrice;index;trié)

• critère_de_recherche est la valeur recherchée dans la première colonne de la matrice.
• matrice  est la référence qui doit comprendre au moins deux colonnes.
•  index  est le numéro de la colonne dans la matrice qui contient les valeurs devant être renvoyées. La première colonne a le numéro 1.
•  trié est un paramètre facultatif qui indique si la première colonne de la matrice est triée en ordre croissant.

Saisissez la valeur logique FAUX ou 0 si la première colonne n’est pas triée en ordre croissant. Les colonnes triées peuvent être recherchées plus rapidement et la fonction renvoie toujours une valeur, même si la valeur de recherche ne correspond pas  exactement. Dans les listes non triées, la valeur de recherche doit correspondre exactement. Sinon la fonction renvoie ce message Erreur: valeur non disponible.

Les fichier téléchargés sont à mettre dans un dossier « Mon travail » sur le bureau de votre ordinateur

Sur le site https://sql.sh/736-base-donnees-villes-francaises, télécharger la liste des villes françaises sous le format csv : le nom du fichier est « villes_france.csv ». Attention ce fichier ne contient pas de descripteurs à la première ligne mais les données du fichier sont décrites sur la page du site.

Question 9: Dans quelle colonne retrouve-t-on le code postal de la ville?

Sur le site de l’INSEE https://www.insee.fr/fr/accueil, dans la partie

• Définitions, méthodes et qualité,
• Géographie administrative et d’étude,
• Téléchargement,
• Code officiel Géographique,
• Millésime 2019 : Téléchargement des fichiers,

télécharger les fichiers sur les régions et les départements avec l’extension .csv.

Les fichiers après décompression ont pour noms « region2019.csv » et « departement2019.csv ».

Ouvrir « region2019.csv » avec Libre Office puis sauvegarder ce fichier sous le nom « ActivitesRegions.csv » (attention à l’encodage).
Ensuite, insérer les fichiers « departement2019.csv » et « villes_ france.csv » chacun dans une nouvelle feuille.
Pour cela, choisir dans la barre de menu "Feuille" puis "Inserer une feuille" puis cocher la case "A partir d’un fichier ...."

Dans la feuille "departement2019", on souhaite rajouter le nom des régions, ce qui possible puisque les deux collections départements et régions ont en commun le descripteur donnant le numéro des régions.

• Dans la cellule "H1" de la feuille "departement2019", tapez le nom du descripteur à rajouter par exemple "NomRegion".
• Dans la cellule "H2", taper la formule "=RECHERCHEV(B2;region2019.A$2:F$19;4;0)"
• ATTENTION SI UTILISATION D'EXCEL taper la formule "=RECHERCHEV(B2;region2019!A$2:F$19;4;0)"

Explication de la formule :
Cette instruction effectue une recherche du contenu de la cellule B2 (c’est-à-dire ici 84) dans les cellules A2 à F19 de la feuille "region2019". Elle trouve cette valeur 84 à la cellule A17. Elle affecte alors à la cellule H2 le contenu de la cellule D17, c’est-à-dire AUVERGNE-RHONE-ALPES, situé dans la 4ème colonne de la zone de recherche qui est la plage A$2 :F$19 (d’où le "4" dans la formule). Le nom AUVERGNE-RHONE-ALPES devrait donc apparaître dans la cellule H2 si vous avez tapé correctement cette formule. Le dernier paramètre, 0, spécifie que la colonne A dans laquelle la recherche se fait n’est pas triée par ordre croissant. Les $ sont ici pour que par recopie vers le bas la plage de recherche reste inchangée.

• Recopier la formule de la cellule H2 vers le bas jusqu’à la dernière ligne remplie.

Pour cela, copier la cellule H2 (ctrl+c) , sélectionner la cellule H3 et appuyer en même temps sur ctrl+Maj+fin (ce qui sélectionne la plage H3 :H976) puis coller (ctrl+v)

Question 10 : Rajouter le nom du département dans colonne « AB » de la feuille « villes_france » en utilisant la feuille « departement2019 » Noter la formule utilisée sur votre document réponse

Question 11 : Rajouter le nom de la région dans colonne « AC » de la feuille « villes_france » en utilisant la feuille « region2019 » Noter la formule utilisée sur votre document réponse

• Utiliser un filtre pour ne voir que les villes de votre département dans la feuille "villes_france".
• Insérer une nouvelle feuille que vous appellerez "Loire" et y copier-coller les lignes sélectionnées par le filtre.

Question 12: Dans cette dernière feuille, calculer :

• Le nombre d’habitants dans le département en 2010 (colonne O) (fonction Somme)
• Le nombre moyen d’habitants dans une ville de la loire en 2012 (colonne Q) (fonction Moyenne)
• Quelle est la ville du département ayant l’altitude la plus basse (colonne Z)
• Quelle est la ville du département ayant l’altitude maximale (Colonne AA)

Pour aller plus loin...