Fiche de manipulations – TD Excel & Access

Référence rapide pour l’évaluation : niveaux de vie, population, sphères présentielle/productive, CSP & courbes de Lorenz, jointures sous Microsoft Access.

0. Gestes techniques de base (Excel)

0.1 Filtrer un tableau

  1. Sélectionner la ligne d’en-têtes.
  2. Onglet Données → Filtrer.
  3. Utiliser les flèches de filtre pour sélectionner : communes, années, territoires (CCSG, Gironde, France…), etc.

0.2 Trier

0.3 Formules de base

0.4 Graphique rapide

  1. Sélectionner les données (y compris les étiquettes si possible).
  2. Onglet Insertion → choisir le type de graphique : courbes, nuage de points, barres, etc.
  3. Vérifier que l’axe des X est correct (années ou communes).

1. Niveaux de vie, déciles & rapport interdécile

1.1 Médiane, D1, D9, taux de pauvreté

Dans le fichier de niveaux de vie (type CCSGFILO21) :

Rapport interdécile pour une ligne :

1.2 Correction de l’inflation (2016 → 2021)

Objectif : ramener le niveau de vie 2016 en euros 2021 à partir de l’IPC.

  1. Dans une feuille IPC, par exemple :
    • B2 = IPC 2016
    • B3 = IPC 2021
  2. Dans la feuille de niveaux de vie :
    • C2 = niveau de vie 2016 (en € 2016).
    • Créer une colonne NV_2016_corrigé_2021 :

    =C2 * ($B$3 / $B$2)

  3. Comparer ensuite :
    • NV_2016_corrigé_2021 vs NV_2021
    • Calculer un taux de variation réel : =(NV_2021 - NV_2016_corrigé_2021) / NV_2016_corrigé_2021

1.3 Nuage de points médiane / rapport interdécile

  1. Préparer un tableau du type :
    CommuneAnnéeMédianeD9_D1
    Commune A2016……
    Commune A2021……
  2. Sélectionner les colonnes Médiane et D9_D1 (avec leurs lignes).
  3. Insertion → Nuage de points (XY) :
    • Axe X : Médiane
    • Axe Y : D9_D1
    • Une série par année (2016, 2021) si souhaité.
  4. Ajouter les étiquettes de données pour afficher le nom des communes si besoin.

2. Population, structure & évolutions

2.1 Part (%) dans la population

Exemples : 0–14 ans, propriétaires, une CSP particulière.

  1. Disposer des colonnes :
    • effectif_categorie
    • population_totale
  2. Ajouter une colonne %_categorie :

    =[cellule_effectif] / [cellule_population_totale]

  3. Formater la colonne en pourcentage.
  4. Répéter la même méthode pour commune, CCSG, Gironde, etc. et construire un tableau comparatif.

2.2 Taux de variation entre deux dates

Exemple : évolution de la population entre 1999 et 2022.

  1. Colonnes :
    • POP_1999
    • POP_2022
  2. Variation absolue :

    =POP_2022 - POP_1999

  3. Taux de variation global :

    =(POP_2022 - POP_1999) / POP_1999

  4. Taux de variation annuel moyen (TVAM) sur n années :

    =((POP_2022 / POP_1999)^(1/n) - 1)

2.3 Graphiques

Histogramme comparatif (structure par âge, CSP, etc.) :

  1. Tableau : classes d’âge ou CSP en lignes, parts (%) par territoire en colonnes.
  2. Insertion → graphique en colonnes groupées.
  3. Une série par territoire (commune, CCSG, Gironde…).

Courbe d’évolution :

  1. Tableau : Année en colonne A, Valeur (population, effectifs CSP, etc.) en colonne B.
  2. Insertion → graphique en courbes.

3. Sphères présentielle & productive (emploi)

3.1 Part des sphères dans l’emploi total

Colonnes typiques :

Pour chaque ligne (territoire, année) :

3.2 Taux de variation dans le temps

  1. Pour une période (ex. 1999–2022), pour une sphère :

    TV = (EMP_2022 - EMP_1999) / EMP_1999

  2. Dupliquer pour les autres périodes : 1999–2011, 2011–2016, 2016–2022, etc.
  3. Éventuellement, ajouter une mise en forme conditionnelle (rouge/négatif, vert/positif).

3.3 Graphiques

Barres empilées (part des sphères) :

  1. Tableau : Communes en lignes, % présentiel et % productif en colonnes.
  2. Insertion → histogrammes empilés.

Courbe d’évolution d’une sphère :

  1. Tableau : Années + EMP_PRES (ou EMP_PROD).
  2. Insertion → graphique en courbes.

4. Courbes de Lorenz pour les CSP

4.1 Indicateur de spécificité (IS)

Pour une CSP donnée (ex. « employés »), pour chaque commune i :

Utiliser des sommes avec références absolues pour CSP_total_CCSG et POP_total_CCSG.

4.2 Tri des communes

  1. Trier le tableau sur la colonne IS (croissant ou décroissant, mais de façon cohérente).
  2. La suite des calculs se fait sur ce tableau trié.

4.3 Fréquences relatives & cumulées

Pour chaque commune i :

Ensuite :

4.4 Construction de la courbe de Lorenz (Excel)

  1. Sélectionner deux colonnes :
    • Axe X : F_pop
    • Axe Y : F_CSP
  2. Insertion → Nuage de points (XY) → type « points + lignes ».
  3. Ajouter la droite d’égalité :
    • Créer un mini-tableau : (0 ; 0) et (1 ; 1).
    • Ajouter cette série au graphique comme seconde série.
  4. Pour comparer plusieurs CSP sur le même graphique :
    • Répéter le calcul des F_CSP pour chaque CSP.
    • Ajouter une série par CSP (même axe X = F_pop).

5. Manipulations sous Microsoft Access (jointures Excel)

5.1 Importer des feuilles Excel dans Access

  1. Ouvrir Access → Base de données vide.
  2. Onglet Données externes → Nouveau fichier de données → Excel.
  3. Choisir le fichier Excel.
  4. Sélectionner : Importer les données source dans une nouvelle table de la base de données active.
  5. Choisir la feuille (onglet). Répéter pour chaque tableau à joindre.

5.2 Types de champs

  1. Ouvrir chaque table en Mode création.
  2. Vérifier :
    • Champs numériques en type Nombre.
    • Identifiants territoriaux (codes INSEE, EPCI, etc.) en type Texte.
  3. Les champs utilisés pour les jointures doivent avoir le même type dans chaque table.

5.3 Créer une jointure (requête)

  1. Onglet Créer → Création de requête.
  2. Ajouter les tables à joindre.
  3. Faire glisser le champ commun d’une table vers le champ identique dans l’autre : une ligne apparaît (jointure).
  4. Double-cliquer sur la ligne de jointure pour choisir le type (classique : « n’inclure que les lignes où les champs joints sont égaux »).

5.4 Choisir les champs utiles

  1. Dans la grille de requête, cocher les colonnes à conserver (nom de commune, population, revenu, etc.).
  2. Clique sur Exécuter (icône éclair rouge) pour afficher le résultat.

5.5 Exporter vers Excel

  1. Une fois la requête correcte, la laisser sélectionnée.
  2. Onglet Données externes → Excel.
  3. Exporter en .xlsx et ouvrir dans Excel si besoin.

5.6 Jointures avec 3 tables ou plus

  1. Ajouter toutes les tables nécessaires dans la fenêtre de requête.
  2. Créer toutes les lignes de jointure entre les clés communes.
  3. Vérifier qu’il n’y a pas de clé manquante (sinon certaines lignes seront exclues).

5.7 Contrôles rapides avant évaluation

6. Récap des formules-clés