Les listes de données

Tout tableau contenant une série d’informations peut être utilisé comme une base de données afin d’en extraire des informations et des statistiques.

Les informations pourront être extraites à l’aide des fonctions de recherche ou bien à l’aide de filtres ou de formules de calcul appropriées.

La seule contrainte, pour pouvoir utiliser les filtres, est de donner un format différent à la première ligne du tableau. Cette ligne sera ainsi reconnue automatiquement comme contenant les noms de champs (ou en-têtes de colonnes).

Vous allez travailler sur un exemple de liste contenant le fichier des membres d’une entreprise.

Dans ce tableau vous allez utiliser des formats de nombres particuliers et des formules de calculs élaborées qui sont résumés sur la page suivante. Ces formats et ces formules sont présentés pour faire connaître quelques possibilités du logiciel et ne sont pas indispensable pour l’utilisation ce tableau comme base de données

Sur la page suivante est représenté le tableau sur lequel vont être fondés les exemples qui vont suivre.

Mettre sous forme de tableau

Mettre la liste à jour

Pour ajouter ou supprimer des fiches supplémentaires dans une liste, vous pouvez procéder de deux manières :

• Saisir directement une nouvelle fiche directement dans le tableau selon la méthode classique de saisie sur Excel.
• Saisir par l’intermédiaire d’un outil Formulaire.

 L’outil Formulaire n’est pas présent par défaut dans Excel 2013. Il faut l’ajouter à votre Ruban personnel.

a) Cliquez sur une cellule de la plage ou de la table à laquelle vous souhaitez ajouter le formulaire.

b) Pour ajouter le bouton Formulaire à la barre d’outils Accès rapide, procédez comme suit :

c) Cliquez sur la flèche en regard de la barre d’outils Accès rapide, puis cliquez sur Autres commandes.

d) Dans la zone Choisir les commandes dans les catégories suivantes, cliquez sur Toutes les commandes, puis sélectionnez le bouton Formulaire dans la liste.

Filtrer la liste en choisissant des critères

Choix de critère pour un seul champ

Vous obtenez à droite de chaque nom de champ une flèche ouvrant un menu déroulant permettant de choisir des critères de tri ou de sélection des données :

La liste du personnel du service « commercial » apparaît :

Choix de critère sur 2 champs

Comme pour le choix d’un critère pour 1 champ, (voir ci-dessus) vous pouvez combiner 2 choix de critères. Par exemple demander l’affichage des femmes du service COM.

Pour cela répétez l’opération ci-dessus en cochant « COM » dans la colonne « Service » et « Femme »’ dans la colonne « Sexe ».

Vous devriez n’obtenir qu’une seule fiche :

Critères avancés et personnalisés

Vous pouvez multiplier les critères en créant des filtres élaborés.

Selon le type de champ que vous souhaitez filtrer, les options seront différentes.

1^er exemple : Vous souhaitez filtrer les fiches ayant un salaire compris entre 1500 et 2000 €.

Déroulez la flèche à coté du champ « Brut » et laissez vous guider :

2e exemple : Vous souhaitez filtrer les fiches des salariés appartenant soit au service COM soit au service ADM :

 

Rechercher plus de 2 valeurs pour un même champ

Dans ce cas vous allez devoir construire une table de critères. Pour plus de facilité, cette table de critères sera construite sur une feuille séparée.

 

Règles à respecter :

• Les en-têtes de colonnes (noms de champs) de la zone de critère doivent être identiques à ceux de la liste
• Lorsque les critères que vous souhaitez sont de nature : ET (un critère ET un autre critère, par exemple service COM ET service ADM), la table de critères doit être construite de sorte que les valeurs cherchées soit sur une même ligne
• Lorsque les critères que vous souhaitez sont de nature : OU (un critère OU un autre critère, par exemple service COM OU service ADM), la table de critères doit être construite de sorte que les valeurs cherchées soit sur des lignes différentes.
• Une table de critères ne doit jamais contenir de lignes ou de cellules vides.

Construction de la table de critère

Cette table va être construite sur une autre feuille que celle contenant la liste

Exemple : Vous allez extraire de notre liste, les fiches correspondant aux hommes des services COM et ADM et TEC.

ATTENTION ! Les noms de champs doivent avoir la même syntaxe que dans la liste

Les critères ET se trouvent sur la même ligne :

• A la fois « Homme » ET du service « COM »
• A la fois « Homme » ET du service « ADM »
• A la fois « Homme » ET du service « TEC »

Les critères OU se trouvent sur des lignes différentes. Dans le résultat de mon extraction je veux :

Des « Hommes du service « COM » OU des « Hommes » du service « ADM » OU des « Hommes » du service « TEC »

Extraction des fiches désirées

Préparez une zone d’extraction sur laquelle vous allez écrire le nom des champs que vous souhaitez voir apparaître pour chaque fiche. Dans l’exemple ci-dessous tous les champs de la liste originale ont été pris. Vous verrez dans l’exemple suivant que seuls certains champs ont été retenus.

f) La référence de la feuille et de la plage de cellules sélectionnées apparaît sous la forme « Tableau1 »[#Tout] »

g) Cliquez dans la zone « Zone de critères » de la boîte de dialogue Sélectionner la table de critères préalablement construite (dans notre exemple A1:B4).

h) La référence de la feuille et de la plage de cellules sélectionnées apparaît.

i) Cliquez sur l’option « Copier vers un autre emplacement » de la boîte de dialogue.

j) Cliquez dans la zone « Copier dans » de la boîte de dialogue.

k) Sélectionnez les cellules contenant les noms de champ souhaités.

l) Cliquez sur OK pour lancer l’extraction.

La boîte de dialogue se présente ainsi :

Dans l’exemple tous les champs ont été repris mais vous pouvez également ne choisir, et donc écrire, que ceux qui vous intéressent.

 

Extraction sélective sur certains champs

Dans l’exemple précédent, toutes les colonnes (ou champs) de la liste sont extraits, dans l’ordre de la liste. Il est néanmoins possible de n’extraire que les colonnes (ou champs) désirées, et ce, dans l’ordre désiré.

Exemple : En reprenant la liste de données de l’exemple ci-dessus, vous désirez voir s’afficher le NOM, le PRENOM et le SERVICE des « Hommes » des services « COM » et « ADM » dont le salaire excède 1500 €

La procédure d’extraction va être la même que dans l’exemple précédent. Seule la destination de l’extraction va changer :

1. Cliquez sur une cellule quelconque de la feuille contenant la table de critères.
2. Cliquez sur le Ruban Données – outil - Avancé.
3. Si nécessaire, dans la boîte de dialogue qui apparaît, cliquer dans la zone « Plages ».
4. Sélectionnez sur la feuille la plage contenant la liste (dans notre exemple A1:L22).
5. La référence de la feuille et de la plage de cellules sélectionnées apparaît sous la forme « Tableau1 »[#Tout] »
6. Cliquez dans la zone « Zone de critères » de la boîte de dialogue. Sélectionnez la table de critères préalablement construite (dans notre exemple A1:C3).
7. La référence de la feuille et de la plage de cellules sélectionnées apparaît.
8. Cliquez sur l’option « Copier vers un autre emplacement » de la boîte de dialogue.
9. Cliquez dans la zone « Copier dans» de la boîte de dialogue
10. Sélectionnez les cellules contenant le nom des colonnes (dans notre exemple A6:D6).
11. Cliquez sur OK pour lancer l’extraction.

 

Calculs statistiques

On peut obtenir une série de calculs statistiques sur la plupart des informations de la liste. Ces calculs peuvent porter sur l’ensemble de la liste ou bien sur une seule partie de celle-ci. Si l’on souhaite effectuer des statistiques sur une partie seulement de la liste, il faudra définir un ou plusieurs critères.

Statistiques sur un critère

Dans l’exemple ci-dessous vous allez réaliser un certain nombre de calculs. Ces calculs porteront sur une partie seulement de la liste. Le critère retenu sera le service, c’est à dire que les calculs porteront sur les fiches correspondant au service désiré.

 

Définition de la plage de cellule contenant le critère

La zone contenant le critère se compose de deux cellules :

• Une cellule contenant le nom de l’en-tête de colonne (ou nom de champ) désiré dans la liste (respecter la syntaxe).
• Une cellule, située sous la première, dans laquelle on écrira le critère désiré.

Cette zone peut être placée n’importe où sur la feuille de calcul. Pour plus de clarté, il est préférable de donner un nom à cette zone. Dans notre exemple vous allez l’appeler « Choix ».

Construction des formules statistiques

Les formules de calculs du type BD....() vont requérir trois arguments :

• La plage de cellules contenant la liste.
• Le numéro de colonne contenant les données à traiter (par exemple le salaire brut).
• La zone contenant le critère (dans notre exemple cette zone est nommée Choix).

Les formules peuvent être de plusieurs types et concerner plusieurs champs de la liste. Dans l’ exemple nous allons calculer :

Choix du critère

Le résultat affiché sera fonction du critère choisi et écrit dans la zone de critères. Dans notre exemple dans la cellule A2.

• Si la zone de critère est vide (rien dans la cellule A2), les calculs s’effectueront sur la totalité de la liste
• Si un critère est saisi dans la zone de critère (dans la cellule A2), les calculs s’effectueront uniquement sur les fiches de la liste répondant à ce critère.

Dans l’exemple vous souhaitez obtenir des statistiques portant sur le personnel du service TEChnique. La zone de critères (A1:A2) comporte :

En A1 l’en-tête de colonne de la liste correspondant au service : « Service ».

En A2 le critère à retenir dans la colonne Service de la liste : « TEC ».

Les calculs vont porter uniquement sur les membres du service TEChnique.

Statistiques sur plusieurs critères

Dans l’exemple ci-dessous vous allez réaliser un certain nombre de calculs. Ces calculs porteront sur une partie seulement de la liste. Les critères retenus seront le sexe et le service, c’est à dire que les calculs porteront sur les fiches correspondant au sexe et au service désiré.

Définition de la plage de cellule contenant la liste

Même procédure que précédemment.

 

Définition de la plage de cellule contenant les critères

La zone contenant les critères se compose de plusieurs cellules (4 au total pour notre exemple):

• Deux cellules côte à côte contenant chacune le nom de l’en-tête de colonne (ou nom de champ) désiré dans la liste (respecter la syntaxe).
• Deux cellules, situées sous les premières, dans lesquelles on écrira le critère désiré.

Cette zone peut être placée n’importe où sur la feuille de calcul. Pour plus de clarté, il est préférable de donner un nom à cette zone. Dans notre exemple nous l’appellerons « Choix2 » (le nom « Choix » ayant déjà été donné dans le chapitre précédent, voir page 23).

1. Choisissez un emplacement et écrire les noms de champs souhaités (on écrit « Sexe » en B1 et « Service » en C1 dans notre exemple)
2. Sélectionnez une plage de cellules comprenant les cellules contenant les noms de champs ainsi que les cellules situées immédiatement dessous (dans notre exemple B1:C2).
3. Choisissez le Ruban Formules –puis l’outil  Définir un nom
4. Écrivez Choix2 en respectant exactement la syntaxe (accents si le nom choisi en comporte).
5. Validez en cliquant sur OK

 

Construction des formules statistiques

Même procédure que précédemment

 

Choix du critère

Le résultat affiché sera fonction des critères choisis et écrits dans la zone de critères. Dans notre exemple dans la cellule B2 ou (et) C2

• Si la zone de critère est vide (rien dans les cellules B2 et C2), les calculs s’effectuerons sur la totalité de la liste
• Si un critère est saisi dans la zone de critère (dans les cellules B2 ou (et) C2), les calculs s’effectuerons uniquement sur les fiches de la liste répondant à ce(s) critère(s).

Dans notre exemple nous avons souhaité obtenir des statistiques portant sur les Hommes du service Administratif. La zone de critères (B1:C2) comporte

En B1 l’en-tête de colonne de la liste correspondant au sexe : « Sexe ».

En C1 l’en-tête de colonne de la liste correspondant au service : « Service ».

En B2 le critère à retenir dans la colonne Sexe de la liste: « Homme ».

En C2 le critère à retenir dans la colonne Service de la liste : « ADM ».