---
description: >-
  PA-AM 2 : Module complémentaire de formation proposé dans le cadre du Parcours
  Agents Métier, développé par ODF pour CultureD
---

# PA-AM 2 : Maitriser les fonctions de base d'un tableur

> Source : OpenDataFrance - Licence : CC-BY-SA Version : v0.1, date : oct. 2023

## 1 - Introduction

### 1.1 Objectif du module

Il est proposé ici de s'approprier quelques fonctions avancées du logiciel bureautique Excel permettant des traitements dans un fichier tabulaire (d'ùo le nom de tableur).&#x20;

Les exercices présentés içi sont des manipulations assez courantes lorsque l'on souhaite récupérer un fichier de données au format tabulaire, faire des traitements et produire de nouvelles à des fins de partage (interne ou externe).

Nous présentons ici un exemple à partir du logiciel Excel qui est très utilisé. Des fonctions à peu près équivalantes sont disponibles dans les autres tableurs du marché, modulo une syntaxe légèrement différente. Par exemple; la fonction =Somme(...) de Excel peut s'appeler =Sum(...) dans GoogleSheet ou LibreOffice)

### 1.2 Cible&#x20;

Toute personne devant réaliseer des traitements un peu avancés avec un tableau de données sans utilisation de code (no code).&#x20;

### 1.3 Prérequis&#x20;

Ce module nécessite une première connaissance des fonctions basiques du tableur Excel. Le participant pourra s'appuyer sur la vidéo d'initiation suivante :&#x20;

{% embed url="https://youtu.be/rvg-ZsAqInk?si=I6gNtU9FsyFiUx_a" %}

### 1.4 Durée

L'exercice peut être fait en 20 minutes.

### 1.5 Ressources nécessaires

* le logiciel Excel de micosoft
* le fichier de test nécessaire aux exercices

{% file src="https://276279626-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fputnv5eNKVYKKe38EvqV%2Fuploads%2FPT0S8fL0Fu3zdaRVbzYA%2Fexemple_subventions_avec_erreurs_cellules.xlsx?alt=media&token=f9d91387-7d1c-4090-b9ef-a7c8dc70d494" %}

* repérer le standard de données (schéma) qui normalise un tel jeu de données

{% embed url="https://schema.data.gouv.fr/scdl/subventions/2.1.1/documentation.html" %}
[https://schema.data.gouv.fr/scdl/subventions/2.1.1/documentation.html](https://schema.data.gouv.fr/scdl/subventions/2.1.1/documentation.html)
{% endembed %}

* Le participant pourra utilement rechercher dans des ressources gratuites en ligne des tutoriels présentation des fonctions avancées ici mobilisées (RechercheV,&#x20;

## 2 - Déroulé de l'excercice

### Exercice 1

Ouvrir le fichier « Contrôle\_Subvention » au format csv avec LibreOffice. Choisir le format de séparateur qui permet une présentation de toutes les colonnes&#x20;

{% hint style="info" %}
Aide : à l'ouverture du fichier avec libreoffrice, utiliser le éparateur = Comma (qui veut dire « virgule ») puis sauvegarder au format excel.
{% endhint %}

### Exercice 2

Ouvrir le fichier Contrôle-Subvention.xls (format xls). Constater les erreurs : date incorrecte, ligne en doublon, nomination non homogène,  identifiant incorrect, format des montants incorrects, codification incorrecte de la notification UE, format pourcentage incorrect (plus quelques autres erreurs que vous saurez repérer)

### Exercice 3

Remplacer la chaine de caractère « Ville de Poitiers » par « Poitiers ».&#x20;

{% hint style="info" %}
Aide : Edition/Rechercher/Remplacer
{% endhint %}

S’assurer que tous les libellés de nomAttribuant (ligne 1, colonne A) sont homogènes&#x20;

### Exercice 4

Modifier l’affichage de la colonne idAttribuant (colonne B). Il s’agit d’un numéro de SIRET sur 14 caractères.  Le faire apparaitre sous le forme non scientifique : xxxxxxxxxxxxxx.&#x20;

{% hint style="info" %}
Aide : sélection la colonne B, aller à Mise en Forme/Cellule, choisir Nombre, sans décimale et sans séparateur
{% endhint %}

### Exercice 5

Modifier le format d’affichage d’une date : Colonne DateConvention (C), sélection la/les dates incorrectes, modifier le format&#x20;

{% hint style="info" %}
Aide : sélection la colonne C, aller à Mise en Forme/Cellule, choisir Date, format 2012-03-14)
{% endhint %}

### Exercice 6&#x20;

Mettre le SIRET au bon format d’affichage dans la colonne F (IdBeneficiaire). Détecter l’erreur (identifiant 32402138500000.0), d’où vient l’erreur ?

{% hint style="info" %}
AIde : le format de la cellule est bien Nombre, sans décimale -qui serait séparée par une virgule- mais il a été rajouté un « .0 ». Il suffit de supprimer cette chaine de caractère)
{% endhint %}

### Exercice 7

colonne N (pourcentageSubvention), le schéma stipule numérique entre 0 (0%) et 1 (100%), séparateur « . » (point). Repérer les erreurs (>80, 100, 0,5). Corriger les valeurs&#x20;

{% hint style="info" %}
Aide : commencer par normaliser les formats dans la colonne, remplacer les «.» par «,», créer une nouvelle colonne temporaire O, insérer dans chaque cellule le calcul : =N$/100, copier par "remplacement les valeurs" calculées de la colonne O vers la colonne N. On peut supprimer alors la colonne O.  Colonne N : remplacer le type de chaine de "Nombre" par "texte". Remplacer enfinles «,» par «.»
{% endhint %}

### Exercice 8

colonne notification\_UE : constater la non-conformité des champs par rapport au schéma (on ne doit avoir que « oui » ou « non »). Trier les lignes et regrouper les lignes selon la valeur notification\_UE (col. M)&#x20;

{% hint style="info" %}
Aide : Sélectionner toute la table (en Haut à Gauche), Données/Trier colonne notificationUE. Corriger les valeurs incorrecte (remplacer no par non, yes par oui).


{% endhint %}

{% hint style="warning" %}
Rappel : Appuyez-vous sur l’aide en ligne d’Excel pour trouver la syntaxe exacte des fonctions que vous utilisez.
{% endhint %}

### Exercice 9

Créer un nouveau champ Validation, qui possèdera les valeurs suivantes (liste fermée : Non validée, En cours, Validée). \
Ajouter aléatoirement les valeurs à chaque ligne&#x20;

{% hint style="info" %}
Aide : Données, Validation, Autoriser : liste, Liste déroulante dans la cellule, Sources :  Validée; En cours; Non Validée
{% endhint %}

### Exercice 10

Créer des indicateurs :&#x20;

* Créer un nouvel onglet nommé Indicateur,&#x20;
* Insérer un graphique de type Barre
* Représenter les colonnes G (Bénéficiaires) et Q (montant) sous forme de graphe
* Mettez à jour les titres : du graphique, des axes
* Adapter la présentation avant publication finale
* Exporter le graphique au format image

{% hint style="info" %}
Aide : créer une colonne de travail avec les montants au format numérique (donc avec des "," comme séparateur), trier les données suivant l'ordre croissant des montants attribués, sélectionner les plage G1:G14 et Q1:Q14, insérer un graphique de type barre. Cliquez sur les sections (le titre, le bloc de l'abscisse ou le bloc de l'axe horizontal) pour faire apparaitre les options, dans le cadre général (hors bloc), option : imprimer sous forme d'image.&#x20;
{% endhint %}

## 3 - Corrections et conseils

### Fichier corrigé

Onglet : ex\_subventions\_corrigées

{% file src="https://276279626-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fputnv5eNKVYKKe38EvqV%2Fuploads%2FRGpwepg4h0GOj5fllCFb%2Fexemple_corrige%CC%81_subventions_avec_erreurs_cellules.xlsx?alt=media&token=65f8b536-68c8-4042-a1a7-f626e6ac083d" %}

### Exercice 2 : modifier le format d'affichage d'un champ (nombre)

<figure><img src="https://276279626-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fputnv5eNKVYKKe38EvqV%2Fuploads%2FClmlwpOUPoedndrC2HNq%2FCapture%20d%E2%80%99e%CC%81cran%202023-10-13%20a%CC%80%2018.18.17.png?alt=media&#x26;token=729599da-361f-4743-b4f7-603a969a0534" alt=""><figcaption></figcaption></figure>

### Exercice 5 : modifier le format d'affichage d'un champ (date)

<figure><img src="https://276279626-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fputnv5eNKVYKKe38EvqV%2Fuploads%2FzKhyocYo3C2gUtU6AMaG%2Fimage%20e%CC%81cran%20format%20data.png?alt=media&#x26;token=73655ceb-66d0-4046-97f5-d823f0c7fac9" alt=""><figcaption></figcaption></figure>

### Exercice 8 : effectuer un tri sur une colonne

<figure><img src="https://276279626-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fputnv5eNKVYKKe38EvqV%2Fuploads%2FTKSi31QsC8tJwLM7Y71R%2Fimage%20e%CC%81cran%20tri.png?alt=media&#x26;token=8e0cf1f5-bbaf-474a-b21b-6cc98319c980" alt=""><figcaption></figcaption></figure>

### Exercice 9 : insertion d'une liste fermée

<figure><img src="https://276279626-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fputnv5eNKVYKKe38EvqV%2Fuploads%2Fw2t6Wm9eVwpdNxU6zIrR%2Fimage%20e%CC%81cran%20insertion%20liste%20ferme%CC%81e.png?alt=media&#x26;token=a151577f-b2a6-4451-a12a-ad89c1cb83b4" alt=""><figcaption></figcaption></figure>

## 4 - Evaluation des connaissances

1. Ouvrir un fichier de type cvs et xsl avec l’outil Excel, s’adapter au format du fichier (séparateur de colonne et de ligne)
2. Manipuler les colonnes et des lignes pour un affichage adapté. Modifier l’affichage (cacher), faire des tris et de regroupement
3. Faire des calculs simples pour générer des indicateurs : totaux par catégorie (nombre ou somme)
4. Compléter les données par des informations externes : ajouter une géolocalisation à une adresse et inversement,
5. Contrôler la qualité des données
6. Manipuler les données pour des traitements avancées
7. Création d'un graophique simple et présentation
