9. Manipulation de données

---

Au cours de ce chapitre, nous verrons des techniques de travail avec les données à savoir :

• Le tri de données
• Le filtrage de données
• Validation de données
• Ajout de sous-totales

Soit l’extrait de classeur suivant :

Vous pouvez télécharger ce fichier ici

9.1. Tri de données

Faisons par exemple un tri de cette liste par date d’adhésion. Pour cela :

• Sélectionnez une cellule de la colonne « Date d’adhésion »
• Cliquez sur l’onglet Données du ruban
• Cliquez sur la commande Trier du plus ancien au plus récent. Ou bien sur le bouton Trier du plus récent au plus ancien; selon l’ordre que vous voulez

NB. Les commandes Trier sont disponibles également au niveau de l’onglet Accueil du ruban ; selon le chemin : Onglet accueil/Trier et filtrer/Trier…

Maintenant, comment faire pour trier selon deux colonnes ? par exemple, on veut trier toujours selon la date d’adhésion mais avec chaque secteur d’activité à part. il faut donc trier selon le secteur d’activité et selon la date d’adhésion comme suit :

• Sélectionnez n’importe quelle cellule de la liste
• Cliquez sur l’onglet Données du ruban
• Cliquez sur la commande Trier

 

• La boite de dialogue Tri apparait. Assurez vous que la case Mes données ont des en-têtes est bien cochée.
• Sélectionnez la colonne « Secteur d’activité » au niveau de la liste Trier par.
• Cliquez ensuite sur le bouton Ajouter un niveau.

• Un second niveau de tri est ajouté. Sélectionnez la colonne « Date d’adhésion » au niveau de la liste Puis par.
• Validez enfin en cliquant sur le bouton OK.

NB. Vous pouvez, bien entendu, ajouter d’autres niveaux de tri.

9.2. Filtrage de données

Cette technique nous permet d’afficher un sous-ensemble de notre liste de données selon un ou des critères. A titre d’exemples, nous voulons afficher uniquement :

• Les adhérents de sexe féminin
• Les adhérents d’un ou plusieurs secteurs d’activité
• Les personnes adhérées en 2016 par exemple
• Comme on peut également faire une combinaison de deux ou plusieurs critères

Voilà comment faire :

• Sélectionnez une cellule de votre liste
• Cliquez sur l’onglet Données du ruban
• Cliquez sur la commande Filtrer
• Les entêtes de colonnes auront des boutons qui permettent d'ouvrir un menu de filtrage , cliquez par exemple sur le bouton à l’entête de la colonne « Secteur d’activité »

 

• Une liste des valeurs uniques triées par ordre est affichée, avec une case à cocher devant chaque valeur.
• Cliquez sur la case à cocher Sélectionner tout pour décocher toutes les cases à cocher
• Cliquez sur la ou les cases à cocher que vous voulez. A titre d’exemple cochez les cases des valeurs « Commerce » et « Services ».
• Validez par le bouton OK. Remarquez que sont affichées uniquement les lignes ayant l’une ou l’autre des valeurs « Commerce » et « Services » au niveau de la colonne « Secteur d’activité »

Vous pouvez ajoutez d’autre critères. Pour afficher par exemple, les adhérents des secteurs d’activité « Commerce » et « Services » habitant à « Paris » :

• Cliquez sur le bouton ouvrant le menu de filtrage  à l’entête de la colonne « Ville »
• Cliquez sur la case à cochez Sélectionner tout pour décocher toutes les cases à cocher
• Cliquez sur la case à cocher « Paris »

• Cliquez sur le bouton OK pour valider. Remarquez qu’en bas de l’écran, Excel affiche le nombre d’enregistrements (= de lignes) trouvés :

Je vous invite maintenant à voir les options du menu Filtres textuels. Cliquez sur le bouton ouvrant le menu de filtrage  à l’entête d’une colonne remplie de texte brut (ne comportant pas de dates ou nombres) par exemple la colonne « Secteur d’activité », puis sur le menu Filtres textuels :

Remarquez également les options disponibles pour une colonne de type Nombres. Cliquez sur le bouton ouvrant le menu de filtrage  à l’entête de la colonne « Dons en € », puis sur le menu Filtres numériques :

Remarquez également les options disponibles pour une colonne de type Dates. Cliquez sur le bouton ouvrant le menu de filtrage  à l’entête de la colonne « Date d’adhésion », puis sur le menu Filtres chronologiques :

Pour annuler le filtre d’une colonne :

• Cliquez sur le bouton ouvrant le menu de filtrage  à l’entête d’une colonne impliquée dans le filtrage. Soit par exemple la colonne Secteur d’activité
• Cliquez sur la commande Effacer le filtre de « Secteur d’activité »

NB. Pour annuler tous les filtres, cliquez sur le bouton Filtrer de l’onglet Données.

9.3. Validation de données

La validation de données est une possibilité qui nous permet d’imposer des restrictions quant aux valeurs acceptées au niveau d’une colonne. Nous pouvons, à titre d’exemple, imposer que les valeurs entrées en une colonne soient des nombres d’une plage de valeurs voulues. C’est utile également lorsqu’une colonne ne peut prendre qu’un nombre limité de valeurs comme c’est le cas de la colonne « Sexe » qui doit avoir uniquement les valeurs M ou F.

Mais pourquoi imposer des contraintes, notamment si c’est moi qui va remplir mon fichier ?
Normalement que ce soit vous qui va remplir le fichier ou que vous le donner à d’autres personnes pour le remplir, sachez que l’erreur est humaine et que nous avons besoin de travailler avec une certaine rapidité. Pour une colonne de dates par exemple, on entre par erreur des valeurs comme « 30/02/2018 » ou « 11/15/2018 ». Dans ce cas, Excel vous dira tout de suite que la valeur entrée n’est pas correcte.

Comme nous allons voir avec des exemples, il y a possibilité d’afficher un message explicatif, dès que la cellule concernée est active. Nous pouvons également spécifier un message d’erreur qui s’affiche lorsqu’elle est saisie une valeur incorrecte.

Considérons toujours le même exemple de ce chapitre :

• Sélectionnez la colonne « Date d’adhésion »
• Cliquez sur l’onglet données du ruban
• Cliquez sur la commande Validation des données. La boite de dialogue Validation des données est ouverte

 

Au niveau de la boite de dialogue Validation des données :

• Choisissons « Date » de la liste Autoriser.
  NB. Des champs sont ajoutés selon la valeur choisie de la liste Autoriser.
• Pour la liste Données, laissons l’option « comprise entre »
• En supposons que l’association désignée par ce classeur est créée le 14/02/2015, personne n’est adhéré avant cette date. Mettons donc dans le champ Date de début de la boite de dialogue Validation de données, la valeur « 14/02/2015 ».
• Dans le champ Date de fin, mettons « =AUJOURDHUI() ». La fonction prédéfinie AUJOURDHUI retourne la date d’aujourd’hui ; celle configurée au niveau de votre système Windows.
• Passez enfin à l’onglet Message de saisie de cette boite de dialogue

 

L’onglet Message de saisie de la boite de dialogue Validation de données permet de définir un message qui apparaît lorsque la cellule concernée est sélectionnée :

• Entrez un Titre
• Entrez un Message
• Passez ensuite à l’onglet Alerte d’erreur de cette boite de dialogue

 

L’onglet Alerte d’erreur de la boite de dialogue Validation de données permet de définir un message qui apparaît lorsqu’une valeur ne respectant pas la règle de validation est saisie en une cellule concernée par cette validation :

• Entrez un Titre
• Entrez un Message
• Validez ensuite en cliquant sur le bouton OK.

 

Il y a également possibilité de validation de données par rapport à une liste de valeurs. C’est adéquat pour les colonnes « Sexe », « Secteur d’activité » et « Ville » de notre exemple qui acceptent chacune un nombre limité de valeurs.

Illustrons cela avec le cas de la colonne « Secteur d’activité ». Commençons par saisir les valeurs sur une plage de cellules. Et puisque je préfère saisir cette liste en une autre feuille, je vais d’abord :

• Ajoutez une nouvelle feuille de calcul. Cliquez pour cela sur le bouton  se trouvant en bas à gauche de la fenêtre Excel
• A la feuille créée, saisissez les valeurs comme suit :

 

• Réactivez la feuille 1, cliquez pour cela sur l’onglet de la feuille 1 en bas à gauche de la fenêtre d’Excel
• Sélectionnez la colonne « Secteur d’activité »
• Ouvrez la boite de dialogue Validation de données
• Choisissez « Liste » de la liste Autoriser. Laissez les cases « Ignorer si vide » et « Liste déroulante dans la cellule » cochées
• Entrez au niveau du champ Source de la boite de dialogue, l’adresse de la plage A2:A7 de la feuille 2 ; là où nous avons saisi la liste de valeurs de secteur d’activité. Je vous rappelle que si vous voulez que Excel ajoute cette adresse :
  • Cliquez au niveau du champ Source de la boite de dialogue Validation des données
  • Réactivez la feuille 2, cliquez pour cela sur l’onglet de la feuille 2 en bas à gauche de la fenêtre d’Excel
  • Sélectionnez la plage de cellules A2:A7
  • Validez en cliquant sur le bouton OK.

 

Vous pouvez ici encore ajouter un Message de saisie et une Alerte d’erreur comme à l’exemple précédent.

Vérifiez maintenant l’effet de votre configuration :

• Cliquez sur une cellule de la colonne « Secteur d’activité ». Remarquez le bouton qui apparait à droite de la cellule
• Cliquez sur ce bouton pour ouvrir la liste. Remarquez que la liste contient les entrées que vous avez saisi à la plage A2:A7 de la feuille 2
• Cliquez sur une entrée de la liste et remarquez que cette valeur est écrite au niveau de la cellule

9.4. Ajout de sous-totales

Utilisons le même exemple et ajoutons des sous-totales pour les « Dons » par « Ville » et par « Secteur d’activité ».

Mais, il convient pour notre cas présent d’effectuer auparavant un tri par « Ville » et par « Secteur d’activité ». Je vous rappelle la procédure pour faire ce tri :

• Cliquez sur une cellule de la liste de données
• Cliquez sur l’onglet Données
• Cliquez sur la commande Trier. La boite de dialogue Tri apparait.

 

Au niveau de la boite de dialogue Tri :

• Assurez-vous que la case Mes données ont des en-têtes est bien cochée.
• Sélectionnez la colonne « Ville » au niveau de la liste Trier par.
• Cliquez ensuite sur le bouton Ajouter un niveau

• Un second niveau de tri est ajouté. Sélectionnez la colonne « Secteur d’activité » au niveau de la liste Puis par.
• Validez enfin en cliquant sur le bouton OK.

Ajoutons maintenant les sous-totales pour les « Dons » par « Ville » et par « Secteur d’activité » :

• Cliquez sur une cellule de la liste de données
• Cliquez sur l’onglet Données
• Cliquez sur la commande Sous-total. La boite de dialogue Sous-total apparait.

 

Au niveau de la boite de dialogue Sous-total qui apparait :

• Sélectionnez « Ville » de la liste A chaque changement de
• Sélectionnez « Somme » de la liste Utiliser la fonction
• Cochez « Dons en € » de la liste Ajouter un sous-total à
• Cliquez sur le bouton OK

 

Remarquez l’ajout à votre feuille d’une ligne avec un sous-total pour chaque changement de valeur à la colonne « Ville ». Remarquez également l’ajout de symboles de groupement à gauche de la fenêtre d’Excel. Cliquez sur chacun de ces symboles de groupement , , ,  et  pour voir le résultat.

 

Ajoutons maintenant les sous-totales par « Secteur d’activité ». Pour cela cliquez encore une fois sur le bouton Sous-total de l’onglet Données du ruban. La boite de dialogue Sous-total apparait. Remplissez-la comme suit :

• Sélectionnez « Secteur d’activité » de la liste A chaque changement de
• Sélectionnez « Somme » de la liste Utiliser la fonction
• Cochez « Dons en € » de la liste Ajouter un sous-total à
• Décochez Remplacer les sous-totaux existants, pour que Excel n’annule pas le sous-total précédent
• Cliquez sur le bouton OK

 

Remarquez l’ajout de sous-totaux par « Ville » et par « Secteur d’activité ». Et remarquez l’ajout du quatrième niveau pour les symboles de groupement.

 

---