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 |
|
 |
 |
-
Les
champs calculés ne sont pas autorisés en saisie.
-
Pour se déplacerdans la
listeon
utilisera les boutons 
ou 
.
-
Pour entrer une
nouvelle fiche on cliquera sur le bouton
.
-
Pour supprimerla
fiche affichée on cliquera sur le bouton 
Filtrer la liste
en choisissant des critères
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 :
-
Choisissez le
menu Données - filtrer - filtre automatique
-
Cliquez sur la
flècheà
droite du champ « service »
-
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 ».
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 ».
|
 |
Pour
supprimerle
critère « Sexe » :
-
Cliquez sur la
flèche
Sexe.
-
Cliquez sur
(Tous) pour annuler le critère
Annuler le filtre
automatique
-
Choisissez le
menu Données - Filtrer
-
L’option filtre
automatique est précédée d’une coche
-
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 :
-
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 sexe Homme), 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 lignesou de
cellules vides.
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.
-
Cliquez sur
l’ongletd’une
nouvelle feuille (par exemple Feuil2).
-
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 »
-
Cliquez
sur l’onglet
d’une nouvelle feuille
(par exemple Feuil3).
-
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) :
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 feuilleet 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.
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é.
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 :
-
Cliquez sur l’onglet
de la feuille
de calcul contenant la
liste,
-
Sélectionnez la liste
(en-têtes de colonnes
compris),
-
Choisissez le menu Insertion - Nom - Définir,
-
Ecrivez Base_de_données
en respectant exactement la
syntaxe (accents et tirets),
-
Validez en cliquant sur OK
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 ».
-
Dans
une nouvelle feuille, choisissez une cellule et écrivez le nom de champ souhaité
(écrivez «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).
-
Choisissez le menu Insertion - Nom - Définir
-
Ecrivez Critères en respectant exactement la syntaxe (accents).
-
Validez en cliquant sur OK
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 :
|
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) |
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 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.
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é.
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) :
-
Pour nommer la plage de
cellules contenant la liste :
-
Cliquez sur l’onglet
de la feuille
de calcul contenant la
liste,
-
Sélectionnez la liste
(en-têtes de colonnes
compris),
-
Choisissez le menu Insertion - Nom - Définir,
-
Ecrivez Base_de_données
en respectant exactement la
syntaxe (accents et tirets),
-
Validez en cliquant sur OK
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 vous écrirez 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ères2 » (le nom « Critères » ayant déjà été donné dans le
chapitre précédent.
-
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)
-
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).
-
Choisissez le menu Insertion - Nom - Définir
-
Ecrivez Critères2 en respectant exactement la syntaxe (accents).
-
Validez en cliquant sur OK
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).
|
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) |
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’effectueront 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’effectueront uniquement sur les fiches de la liste
répondant à ce(s)
critère(s).
Dans notre exemple si vous
souhaitez 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 ».
