Excel par l'exemple Manuel niveau 3 utilisation des listes de données

copyleft

UTILISATION DES LISTES DE DONNEES

Remarques préalables sur les listes de données
Mettre la liste à jour
Filtrer la liste en choisissant des critères

Choix de critère pour un seul champ
Ajouter un critère dun autre champ
Supprimer un critère
Annuler le filtre automatique
Rechercher plus de deux valeurs pour un même champ

Construction de la table de critères
Extraction des fiches désirées
Extraction sélective sur certains champs

Calculs statistiques

Statistiques sur un critère

Définition de la plage de cellule contenant la liste
Définition de la plage de cellule contenant le critère
Construction des formules statistiques
Choix du critère

Statistiques selon plusieurs critères

Définition de la plage de cellule contenant la liste
Définition de la plage de cellule contenant les critères
Construction des formules statistiques
Choix du critère

Sommaire

Remarques préalables sur les listes de données

Tout tableau contenant une série dinformations peut-être utilisé comme une base de données afin den extraire des informations et des statistiques.

 Les informations pourront être extraites à laide des fonctions de recherche (voir chapitre précédent) ou bien à laide 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).

 Nous allons travailler sur un exemple de liste contenant le fichier des membres dune entreprise.

Télécharger le fichier exemple

 Dans ce tableau nous allons 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 lutilisation 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 la liste à jour

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

• Saisir directement une nouvelle fiche à la suite des autres selon la méthode classique de saisie sur Excel.
• Saisir par lintermédiaire du menu Données - Grille. Cette méthode présente lavantage de rendre la liste dynamique pour son utilisation dans des calculs statistiques (voir page *) et de tenir compte des formats et des formules de calculs créés. Dans notre exemple, lutilisation du menu Données - Grille fera apparaître la boîte de dialogue suivante :

• Les champs calculés ne sont pas autorisés en saisie.
• Pour se déplacer dans la liste on utilisera les boutons ou
• Pour entrer une nouvelle fiche on cliquera sur le bouton .
• Pour supprimer la fiche affichée on cliquera sur le bouton

Filtrer la liste en choisissant des critères

Choix de critère pour un seul champ

 Pour afficher uniquement les enregistrement désirés on utilise le menu Données - filtre - filtre automatique. Une flèche apparaît alors à droite de chaque nom de champ.

Par défaut, toutes les lignes de la liste sont affichées. Si lon désire voir safficher uniquement les fiches désirées, on va indiquer un critère.

Par exemple, si lon souhaite obtenir uniquement la liste des personnes du service commercial :

• Choisir le menu Données - filtre - filtre automatique
• Cliquer sur la flèche à droite du champ " service "
• Dans la liste des services existants ciquer sur " COM "

La liste du personnel du service " commercial " apparaît :

On constate que dans la liste qui apparaît en cliquant sur la flèche, existent dautres options :

 

Il est possible de travailler sur des fourchettes de critères à laide des signes opérateurs (plus grand que, plus petit que,...) et de les combiner (et, ou).

Par exemple, si lon souhaite avoir la liste des personnes du service commercial ET du service administratif :

On demande que le premier critère soit " COM " ET que le second soit " ADM ".

 Sommaire

 Ajouter un critère dun autre champ

 Si on désire la liste des hommes du service commercial et du service administratif, on combinera le critère service avec le critère sexe.

• Pour le service on reprendra lexemple ci-dessus.
• Pour le sexe, on clique sur la flèche SEXE puis sur " homme ".

Sommaire

Supprimer un critère

 Pour supprimer le critère SEXE :

• Cliquer sur la flèche SEXE.
• Cliquer sur (tout) pour annuler le critère

 Sommaire

Annuler le filtre automatique

• Choisir le menu Données - Filtre
• Loption filtre automatique est précédée dune coche
• Cliquer sur Filtre automatique

  Sommaire

Rechercher plus de deux valeurs pour un même champ

 Dans ce cas il va falloir 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.

    Sommaire

Construction de la table de critères

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

 Exemple : On va extraire de notre liste, les fiches correspondant aux hommes des services COM et ADM.

• Cliquer sur longlet dune nouvelle feuille.
• Construire un tableau reprenant les noms de champs retenus pour le choix des critères dextraction, et les valeurs désirées pour chacun deux.

La table de critères se présentera comme suit :

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 "

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 "

   Sommaire

Extraction des fiches désirées

 Après avoir construit la table de critères, on va lancer lextraction des fiches désirées :

• Cliquer sur une cellule quelconque de la feuille contenant la table de critères.
• Cliquer sur le menu Données - Filtre - Filtre élaboré.
• Si nécessaire, dans la boîte de dialogue qui apparaît, cliquer dans la zone " Plages ".
• Cliquer sur longlet de la feuille contenant la liste.
• Sélectionner sur la feuille la plage contenant la liste (dans notre exemple A1:L22).
• La référence de la feuille et de la plage de cellules sélectionnées apparaît.
• Cliquer 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:B3).
• La référence de la feuille et de la plage de cellules sélectionnées apparaît.
• Cliquer sur loption " Copier vers un autre emplacement " de la boîte de dialogue.
• Cliquer dans la zone " Destination " de la boîte de dialogue.
• Cliquer sur une cellule qui sera langle supérieur gauche du tableau qui va apparaître.
• Cliquer sur OK pour lancer lextraction.

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

  Sommaire

Extraction sélective sur certains champs

 Dans lexemple précédent, toutes les colonnes (ou champs) de la liste sont extraits, dans lordre de la liste. Il est néanmoins possible de nextraire que les colonnes (ou champs) désirées, et ce, dans lordre désiré.

 Exemple : En reprenant lexemple ci-dessus, on désire avoir le NOM, le PRENOM et lAGE des " Hommes " des services " COM " et " ADM ".

Sur une nouvelle feuille construire la table de critères correspondante

Puis, sur la ligne désirée (ligne 5 dans notre exemple), taper le nom des colonnes désirées en respectant la syntaxe de la liste.

• En A6, taper Nom
• En B6, taper Prénom
• En C6, taper Age
• En D6, taper Service

La procédure dextraction va être la même que dans lexemple précédent. Seule la destination de lextraction va changer :

•  Cliquer sur une cellule quelconque de la feuille contenant la table de critères.
•  Cliquer sur le menu Données - Filtre - Filtre élaboré.
•  Si nécessaire, dans la boîte de dialogue qui apparaît, cliquer dans la zone " Plages ".
•  Cliquer sur longlet de la feuille contenant la liste.
•  Sélectionner sur la feuille la plage contenant la liste (dans notre exemple A1:L22).
•  La référence de la feuille et de la plage de cellules sélectionnées apparaît.
•  Cliquer 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:B3).
•  La référence de la feuille et de la plage de cellules sélectionnées apparaît.
•  Cliquer sur loption " Copier vers un autre emplacement " de la boîte de dialogue.
•  Cliquer dans la zone " Destination " de la boîte de dialogue.
•  Sélectionner les cellules contenant le nom des colonnes (dans notre exemple A6:D6).
•  Cliquer sur OK pour lancer lextraction.

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

Après extraction, le résultat est le suivant :

 

  Sommaire

Calculs statistiques

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

 Sommaire

Statistiques sur un critère

 Dans lexemple ci-dessous nous allons réaliser un certain nombre de calculs. Ces calculs porteront sur une partie seulement de la liste. Le critère retenu sera le service, cest à dire que les calculs porteront sur les fiches correspondant au service désiré.

 Sommaire

Définition de la plage de cellule contenant la liste

 Il est préférable de donner un nom à la plage de cellule contenant la liste. Ce nom sera de préférence " Base_de_données ". Si on utilise le nom " Base_de_données ", et que lon prend la précaution de mettre à jour la liste par lintermédiaire du menu Données - Grille (voir page *), les calculs porteront sur la dernière version de la liste et seront donc toujours à jour.

Pour nommer la plage de cellules contenant la liste :

•  Cliquer sur longlet de la feuille de calcul contenant la liste.
• Sélectionner la liste (en-têtes de colonnes compris).
• Choisir le menu Insertion - Nom - Définir
• Ecrire Base_de_données en respectant exactement la syntaxe (accents et tirets).
• Valider en cliquant sur OK

   Sommaire

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 len-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 nimporte 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 lappellerons " Critères ".

•  Choisir une cellule et écrire le nom de champ souhaité (on écrit "Service" en A1 dans notre exemple)
•  Sélectionner un plage de cellules comprenant la cellule contenant le nom du champ ainsi que la cellule située immédiatement dessous (ici A1:A2).
•  Choisir le menu Insertion - Nom - Définir
•  Ecrire Critères en respectant exactement la syntaxe (accents).
•  Valider en cliquant sur OK

  Sommaire

Construction des formules statistiques

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

•  La plage de cellules contenant la liste (dans notre exemple cette zone est nommée Base_de_données).
•  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 Critères).

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

Sommaire

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 seffectuerons sur la totalité de la liste.
•  Si un critère est saisi dans la zone de critère (dans la cellule A2), les calculs seffectuerons uniquement sur les fiches de la liste répondant à ce critère.

Dans notre exemple nous avons souhaité obtenir des statistiques portant sur le personnel du service TEChnique. La zone de critères (A1:A2) comporte :

• En A1 len-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.

  Sommaire

Statistiques selon plusieurs critères

 Dans lexemple ci-dessous nous allons 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, cest à 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

 Il est préférable de donner un nom à la plage de cellule contenant la liste. Ce nom sera de préférence " Base_de_données ". Si on utilise le nom " Base_de_données ", et que lon prend la précaution de mettre à jour la liste par lintermédiaire du menu Données - Grille (cliquer ici pour voir ce chapitre), les calculs porteront sur la dernière version de la liste et seront donc toujours à jour.

Pour nommer la plage de cellules contenant la liste (si ce nest déjà fait) :

•  Cliquer sur longlet de la feuille de calcul contenant la liste.
•  Sélectionner la liste (en-têtes de colonnes compris).
•  Choisir le menu Insertion - Nom - Définir
•  Ecrire Base_de_données en respectant exactement la syntaxe (accents et tirets).
•  Valider en cliquant sur OK

    Sommaire

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 len-tête de colonne (ou nom de champ) désiré dans la liste (respecter la syntaxe).
•  Deux cellules, situées sous les première, dans lesquelles on écrira le critère désiré.

Cette zone peut être placée nimporte 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 lappellerons " Critères2 " (le nom " Critères " ayant déjà été donné dans le chapitre précédent,.

•  Choisir un emplacement et écrire les noms de champs souhaités (on écrit " Sexe " en B1 et " Service " en C1 dans notre exemple)
•  Sélectionner un 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).
•  Choisir le menu Insertion - Nom - Définir
•  Ecrire Critères2 en respectant exactement la syntaxe (accents).
•  Valider en cliquant sur OK

    Sommaire

Construction des formules statistiques

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

•  La plage de cellules contenant la liste (dans notre exemple cette zone est nommée Base_de_données).
•  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 Critères2).

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

  Sommaire

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 seffectuerons 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 seffectuerons 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 len-tête de colonne de la liste correspondant au sexe : " Sexe ".
• En C1 len-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 ".

 

Les calculs vont porter uniquement sur les Hommes du service ADMinistratif.

  Sommaire