Guide XP / 2003 - Niveau 3

 

  1. Les Fonctions de Recherche
  2. L'utilisation des listes de données
  3. L'automatisation des documents
  4. Les tableaux croisés dynamiques

Téléchargez le manuel complet au format pdf

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

Rechercher plus de deux valeurs pour un même champ
Calculs statistiques


Remarques préalables sur les listes de données
 

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

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

 La seule contrainte, pour pouvoir utiliser les filtres, est de donner un format différent à la première lignedu 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 formulesde 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 la liste à jour

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

Cette méthode présente l’avantage de rendre la listedynamique pour son utilisation dans des calculs statistiques et de tenir compte des formats et des formules  de calculs créés. Dans notre exemple, l’utilisation du menu Données - Formulaire fera apparaître la boîte de dialogue suivante :

 

1

2

 

 

Filtrer la liste en choisissant des critères

 Choix de critère pour un seul champ

 Pour afficher uniquement les enregistrements désirés utilisez le menu Données - filtrer - filtre automatique. Une flècheapparaît alors à droite de chaque nom de champ.

Par défaut, toutes les lignesde la listesont affichées. Si vous désirez voir s’afficher uniquement certaines fiches, indiquez un critère.

 Par exemple, si vous souhaitez obtenir uniquement la listedes personnes du service commercial : 

  1. Choisissez le menu Données - filtrer - filtre automatique

  2. Cliquez sur la flècheà droite du champ « service »

  3. Dans la listedes services existants cliquez sur « COM »

1

2

 

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

 

 On constate que dans la listequi apparaît en cliquant sur la flèche, existent d’autres options:
 

L’option « (Tous) »

Permet d’afficher l’intégralité des fiches pour le champ concerné. Dans notre exemple, toutes les fiches quel que soit le service.

 L’option « (Vides) » 

Permet d’afficher les fiches qui ne comportent rien dans le champ choisi. Dans notre exemple, les fiches des personnes n’appartenant à aucun service (aucune fiche).

 L’option « (Non vides) » 

Permet d’afficher les fiches comportant un texte quelconque dans le champ choisi. Dans notre exemple, toutes les fiches.


L’option « (Personnalisé...) »

Permet d’affiner la sélectionen multipliant les critères. Lors du choix de cette option une boîte de dialogue s’ouvre :

 

 Il est possible de travailler sur des fourchettes de critères à l’aide des options proposées (plus grand que, plus petit que,...) et de les combiner (et, ou).

 Par exemple, si vous souhaitez obtenir la listedes personnes du service commercial OU du service administratif :

 

 Vous demandez que le premier critère soit égal à « COM » OU que le second soit égal à « ADM ».

 
Ajouter un critère d’un autre champ

 Si vous désirez obtenir la listedes hommes du service commercial et du service administratif, vous combinerez le critère service avec le critère sexe.

  • Pour le service on reprendra l’exemple ci-dessus.

  • Pour le sexe, cliquez sur la flèche « Sexe » puis sur « Homme ».

 

 

 Supprimern critère

 Pour supprimerle critère « Sexe » : 

  1. Cliquez sur la flèche Sexe.

  2. Cliquez sur (Tous) pour annuler le critère

  

Annuler le filtre automatique

  1. Choisissez le menu Données - Filtrer

  2. L’option filtre automatique est précédée d’une coche

  3. Cliquez sur Filtre automatique

 

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 feuilleséparée.

 Règles à respecter :  

 

Construction de la table de critères

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

 

Exemple : Vous allez extraire de votre listeles fiches correspondant aux hommes des services COM et ADM.

  1. Cliquez sur l’ongletd’une nouvelle feuille (par exemple Feuil2).

  2. Construisez un tableau reprenant les noms de champs retenus pour le choix des critères d’extraction, et les valeurs désirées pour chacun d’eux.

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:

 

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 »

 

Construction du tableau d’extraction

  1.  Cliquez sur l’onglet d’une nouvelle feuille (par exemple Feuil3).

  2. Construisez un tableau reprenant les noms de champs que vous souhaitez voir dans votre extraction (éventuellement tous les champs de la liste).

Astuce : utilisez le copier/ coller à partir de la liste d’origine pour ne pas vous tromper dans la syntaxe des noms de champs

 

Le tableau d’extraction se présentera comme suit (ici seuls les 7 premiers champs ont été choisis) :

 


Extraction des fiches désirées

 Après avoir construit la table de critères, vous allez lancer l’extraction des fiches désirées : 

a)   Saisissez vos critères en Feuil2 comme ci-contre :

 

b)   Cliquez sur une cellule de la feuille d’extraction (Feuil3) qui sera la feuille où apparaîtra votre extraction de données.

 

c)   Cliquez sur le menu Données - Filtrer - Filtre élaboré.

 

d)   dans la boîte de dialogue  qui apparaît, cliquez dans la zone « Plages ».
Cliquez sur l’onglet
 de la feuille contenant la liste et sélectionnez sur la feuillela 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.

 

e)   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:B3 de la Feuil2).

 

 

 

f)     Cliquez sur l’option « Copiervers un autre emplacement  » de la boîte de dialogue. Puis sur « Copier dans ».

g)   Sélectionnez les noms de champ de la Feuil3.

 

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

i)     Cliquer sur OK pour lancer l’extraction.

 Résultat :

 


Calculs statistiques

 Vous pouvez 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 vous souhaitez effectuer des statistiques sur une partie seulement de la liste, il vous 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 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 l’on prend la précaution de mettre à jour la liste par l’intermédiaire du menu Données – Formulaires  les calculs porteront sur la dernière version de la liste et seront donc toujours à jour.

 

  1. Pour nommer la plage de cellules contenant la liste :

  2.  Cliquez sur l’onglet de la feuille de calcul contenant la liste,

  3. Sélectionnez la liste (en-têtes de colonnes compris),

  4. Choisissez le menu Insertion - Nom - Définir,

  5. Ecrivez Base_de_données en respectant exactement la syntaxe (accents et tirets),

  6. Validez en cliquant sur OK

 

  


 

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 l’appellerez « Critères ».

 

  1. Dans une nouvelle feuille, choisissez une cellule et écrivez le nom de champ souhaité (écrivez «Service» en A1 dans notre exemple)

  2. 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).

  3. Choisissez le menu Insertion - Nom - Définir

  4. Ecrivez Critères en respectant exactement la syntaxe (accents).

  5. Validez en cliquant sur OK

 

Construction des formulesstatistiques

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

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

Calcul
désiré

Formule
employée

Numéro de la colonne utilisée dans la liste

La somme des salaires bruts

BDSOMME()

Brut (colonne 7)

La moyenne des salaires bruts

BDMOYENNE()

Brut (colonne 7)

L’ancienneté la plus élevée

BDMAX()

Ancienneté (colonne 9)

Le salaire le plus faible

BDMIN()

Brut (colonne  7)

L’âge moyen des salariés

BDMOYENNE()

Age (colonne 11)


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.

 Dans notre exemple si vous souhaitez obtenir des statistiques portant sur le personnel du service TEChnique. La zone de critères (A1:A2) doit comporter : 

 

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

 

Statistiques selon plusieurs critères

 Dans l’exemple 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, 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

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 l’on prend la précaution de mettre à jour la liste par l’intermédiaire du menu Données - Formulaires, 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 n’est déjà fait) : 

  1. Pour nommer la plage de cellules contenant la liste :

  2.  Cliquez sur l’onglet de la feuille de calcul contenant la liste,

  3. Sélectionnez la liste (en-têtes de colonnes compris),

  4. Choisissez le menu Insertion - Nom - Définir,

  5. Ecrivez Base_de_données en respectant exactement la syntaxe (accents et tirets),

  6. Validez en cliquant sur OK

 

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) : 

 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  l’appellerez « Critères2 » (le nom « Critères » ayant déjà été donné dans le chapitre précédent. 

  1. Dans une nouvelle feuille , choisissez un emplacement  et écrivez les noms de champs souhaités (on écrit « Sexe » en B1 et « Service » en C1 dans notre exemple)

  2. Sélectionnez 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).

  3. Choisissez le menu Insertion - Nom - Définir

  4.  Ecrivez Critères2 en respectant exactement la syntaxe (accents).

  5. Validez en cliquant sur OK

 Construction des formulesstatistiques

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

 

La somme des salaires bruts

BDSOMME()

Brut (colonne 7)

La moyenne des salaires bruts

BDMOYENNE()

Brut (colonne 7)

L’ancienneté la plus élevée

BDMAX()

Ancienneté (colonne  9)

Le salaire le plus faible

BDMIN()

Brut (colonne 7)

L’âge moyen des salariés

BDMOYENNE()

Age (colonne1)

 

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

 Dans notre exemple si vous souhaitez obtenir des statistiques portant sur les Hommes du service ADMinistratif. La zone de critères (B1:C2) comporte :