Voici une liste de données d'employés d'une entreprise. Comme pour l'image
précédente, la première ligne d'une liste de données contient toujours le
nom des champs. Chaque ligne suivante contient un enregistrement qui décrit une
personne, une chose ou un événement selon les champs que vous avez choisis. Une
liste de données ne doit jamais avoir de lignes vides. C'est-à-dire que tous les
enregistrements doivent se suivre sans interruption.
Excel vous permet de trier rapidement le contenu d'une liste de données pour
vous aider à retrouver rapidement ce que vous recherchez, de mettre de l'ordre
et d'organiser les données.
Vous pouvez utiliser les boutons  sur la barre d'outils
pour trier une colonne en ordre croissant ou décroissant. Tous les
enregistrements de la liste de données vont se déplacer selon l'ordre que vous
avez choisi. Il faut cependant faire attention de ne pas faire un bloc et
ensuite de trier. Sinon, seulement les données et non les enregistrements seront
triées. Cela aura pour effet de mélanger les données. Pas recommandé.
Excel vous offre une autre façon plus élaborée de trier la liste de données.
Le prochain exercice consiste à trier les enregistrements selon le nom de
famille et le prénom.
 Placez le pointeur sur n'importe quelle cellule de la liste de
données.
Du menu Données, sélectionnez l'option Trier.
Vous pouvez trier jusqu'à trois champs en même temps en ordre croissant ou
décroissant.
Pour
le premier tri, sélectionnez le champ Nom.
Conservez l'ordre
croissant.
Pour le second tri, sélectionnez le champ Prénom.
Conservez
l'ordre croissant.
Le tri de la première case va avoir la priorité sur le second tri. Cela veut
dire qu'Excel va premièrement trier sur le nom de famille. Pour les
enregistrements ayant le même nom, Excel va ensuite trier ces enregistrements
selon leurs prénoms.
Appuyez sur le bouton Options.
Ceci est juste pour vous démontrer que vous pouvez trier selon l'ordre normal
ou selon une liste que vous avez préparée à l'avance.
Appuyez sur le bouton Annuler.
Pour
accomplir le triage, appuyez sur le bouton OK.
La liste de données est maintenant triée selon les critères choisis. Vous
pouvez accomplir un triage jusqu'à trois champs en même temps.
Les filtres
Il peut être frustrant d'essayer de retrouver des informations sur une longue
liste de données. Le filtre automatique est une option pour vous aider à
retrouver rapidement l'information désirée.
Le filtre automatique
Placez le pointeur sur n'importe quelle cellule de la liste de
données.
Du menu Données, sélectionnez les options Filtre et
Filtre automatique.
À la première ligne de la liste de données, il y a maintenant des boîtes de
sélection ayant le nom des champs. En cliquant sur le bouton à la droite du nom
du champ, vous pouvez limiter ce qui est affiché à la liste à ce dont vous avez
besoin.
De
la boîte de sélection du champ Nom, sélectionnez Bibeau.
Comme vous pouvez le constater, il y a deux enregistrements, ou personnes
dans ce cas, qui répondent à la demande. Il est toujours possible de combiner
des filtres pour limiter encore plus les résultats.
De
la boîte de sélection du champ Titre, sélectionnez l'option
Administrateur.
Du
menu Données, sélectionnez les options Filtre et Afficher
tout.
OU
Des boîtes de sélection des champs Nom et Titre,
sélectionnez l'option (Tous).
Vous pouvez accomplir un peu plus à partir du filtre automatique. Vous pouvez
filtrer un champ sur deux conditions.
Placez le pointeur n'importe où sur la colonne du champ Nom.
De
la boîte de sélection du champ Nom, sélectionnez l'option
Personnalisé.
Une nouvelle fenêtre s'ouvre vous offrant de placer jusqu'à deux conditions.
De plus, vous pouvez choisir parmi plusieurs conditions celle qui répond mieux à
vos besoins.
Pour
la première case, sélectionnez la condition égal.
Pour la seconde case,
sélectionnez le nom Bibeau.
Entre les deux conditions, sélectionnez l'option Ou.
Pour
la troisième case, sélectionnez la condition égal.
Pour la quatrième
case, sélectionnez le nom Dupuis.
Pour
filtrer selon ces conditions, appuyez sur le bouton OK.
Le filtre automatique vous permet de réaliser des recherches intéressantes.
Mais le filtre élaboré vous offre encore plus de possibilités et de
flexibilités.
Du
menu Données, sélectionnez les options Filtre et Afficher
tout.
OU
De la boîte de sélection du champ Nom, sélectionnez
l'option (Tous).
Le filtre élaboré
Excel vous permet aussi d'accomplir des recherches plus complexes. Vous
pouvez choisir les champs de votre choix pour les critères et entrer des
valeurs. Vous pouvez aussi utiliser les conditions ET et OU sous
la forme d'un grillage.
Avant d'utiliser un filtre élaboré, vous avez besoin d'au moins deux choses:
une liste de données et une zone de critère.
Ajoutez les informations suivantes dans les cellules appropriées.
La première ligne de la zone de critères doit contenir le nom des champs dont
vous avez besoin pour les critères. Pour cet exercice, il s'agit des champs
Titre et Catégorie. Faites attention à l'orthographe! Si le nom n'est pas
identique au nom du champ de la liste de données, Excel ne vous affichera pas le
résultat que vous voulez.
Les lignes suivantes incluent les critères. Par défaut, Excel présume le
signe =. Pour l'exemple, Titre = Administrateur. Vous pouvez aussi utiliser
d'autres manières d'évaluer tel que >, <, <=, >=, <>. Pour
appliquer les conditions ET et OU, vous devez utiliser le grillage
d'Excel. Les critères qui sont sur la même ligne utilisent le critère ET.
Dans l'exemple, la troisième ligne de la zone de critère équivaut à Titre =
Ouvrier ET Catégorie = 4. Les critères qui sont sur des lignes
différentes utilisent la condition OU. Cela permet d'avoir le résultat de
plusieurs conditions en même temps. Pour cet exercice, tous les critères
équivalent à Titre = Administrateur OU Titre = Ouvrier ET
Catégorie = 4.
Placez le pointeur sur l'une des cellules de la liste de données.
Sinon, Excel ne voudra pas accomplir le filtre.
Du
menu Données, sélectionnez les options Filtre et Filtre
élaboré.
Le filtre élaboré vous permet deux actions: filtrer la liste ou copier les
données à un autre emplacement. Pour la prochaine étape, laissez l'action à
filtrer. L'exercice suivant consistera à utiliser l'action copiée. Normalement,
Excel devrait avoir correctement choisi la bonne plage de cellules pour la liste
de données. Mais, il faut toujours vérifier. Il ne faut pas de contenu dans les
cellules autour de la liste de données. Il faut aussi s'assurer qu'Excel a
correctement choisi la bonne zone de critères. Sinon, ...
Cliquez dans la case de zone de critères.
Faites un bloc avec les
cellules nécessaires pour la zone de critères. Pour les besoins de cet exercice,
sélectionnez les cellules I1 à J3.
Appuyez sur la touche
Entrée.
Il ne faut pas prendre plus de cellules qu'il faut pour la zone de critères.
Sinon, le résultat ne sera pas ce que vous attendez.
Puisqu'il n'y a pas d'autres actions à accomplir, appuyez sur le bouton
OK.
Un filtre élaboré vous donne plus d'options qu'un filtre automatique.
Du
menu Données, sélectionnez les options Filtre et Afficher
tout.
Le filtre élaboré offre aussi une autre option intéressante. Au lieu de
masquer les enregistrements qui ne répondent pas aux critères, le filtre peut
recopier les "bons" enregistrements à un autre endroit sur la feuille de calcul.
Le prochain exercice consiste à utiliser le filtre élaboré avec l'option de
copier les enregistrements.
Ajoutez les informations suivantes dans les cellules appropriées.
La première ligne de la destination du filtre élaboré doit contenir le nom
des champs, correctement orthographié, dont vous voulez voir le résultat. Les
cellules en dessous de celles-ci doivent être libres. C'est dans celles-ci que
les enregistrements qui répondent aux critères seront recopiés.
Placez le pointeur sur l'une des cellules de la liste de
données.
Du menu Données, sélectionnez les options Filtre et
Filtre élaboré.
De
la liste des actions possibles, sélectionnez Copier vers un autre
emplacement.
La plage de la liste de données et de la zone de critères devrait être
correcte. Mais il faut toujours vérifier. Il reste à déterminer la
destination.
Placez le pointeur dans la case Destination.
Sélectionnez les
cellules ayant les noms des champs dont vous voulez voir le résultat. Pour les
besoins de l'exercice, sélectionnez les cellules I6 à K6.
Appuyez sur
la touche Entrée.
Activez l'option Extraction sans
doublon.
Cette dernière option évite de voir plusieurs fois un même enregistrement si
celui-ci répond à plusieurs critères que vous avez inscrits.
Appuyez sur le bouton OK.
Note:
Agrandissez la colonne des salaires si vous voyez le symbole
#.
Excel recopie les enregistrements à l'endroit que vous avez demandé.
La grille
Au lieu d'entrer, de modifier et de retirer les données directement dans les
cellules d'une feuille de calcul, Excel vous permet de gérer vos listes de
données en utilisant une grille. La prochaine partie consiste à vous démontrer
le fonctionnement de cette grille.
Placez le pointeur sur l'une des cellules de la liste de
données.
Du menu Données, sélectionnez l'option Grille.
Note :
L'option s'appelle Formulaire à paritr d'Excel XP
(2002) au lieu de Grille.
Excel vous offre maintenant la possibilité de modifier les données de chacun
des enregistrements à partir de ce petit formulaire. Voici une courte
description des boutons.
| Nouvelle |
Ajouter un enregistrement à la liste de données. Cet
enregistrement sera placé à la fin de la liste. |
| Supprimer |
Supprimer un enregistrement de la liste de données.
Excel va demander une confirmation avant de procéder. |
| Rétablir |
Permets de rétablir les valeurs d'un enregistrement
avant des modifications que vous avez apportées. Dois être utilisé avant de
changer d'enregistrement. |
| Précédente |
Passer à l'enregistrement précédent. |
| Suivante |
Passer à l'enregistrement suivant. |
| Critères |
Permets d'établir des critères de recherche et
afficher seulement les enregistrements requis. |
| Fermer |
Fermer la grille pour revenir à la feuille de
calcul. |
Les prochains exercices consistent à mettre en pratique cette grille.
Appuyez sur le bouton Suivante.
Le prochain enregistrement apparaît dans les cases de la gauche de la
fenêtre. Vous pouvez changer leurs contenus à votre choix. Cependant, vous ne
pouvez pas changer le contenu du champ Catégorie puisqu'il s'agit d'une
formule.
En
utilisant les boutons Précédente et Suivante, déplacez-vous à
travers les enregistrements pour changer le salaire de Josée Dupuis de 22 500
$ à 24 500 $.
C'est avec regret que nous apprenons que M. Paul Cardinal quitte
l'entreprise. M. Réjean Sauvageau a été engagé pour le remplacer. Voici les
informations pertinentes à ajouter à la liste de données.
| NAS: |
211211001 |
| NOM: |
Sauvageau |
| PRENOM: |
Réjean |
| SEXE: |
M |
| TITRE: |
Ouvrier |
| SALAIRE: |
22 000 $ |
Placez le pointeur sur l'une des cellules de la liste de
données.
Du menu Données, sélectionnez l'option Grille.
Afin d'accélérer la recherche, l'option des critères sera utilisée pour
chercher plus rapidement l'enregistrement recherché.
Appuyez sur le bouton Critères.
Entrez le texte: Cardinal dans la case NOM:
Appuyez sur
le bouton Suivante.
L'enregistrement de Paul Cardinal va apparaître sur la grille. Il faut
maintenant le retirer de la liste de données.
Appuyez sur le bouton Supprimer.
Excel vous demande de confirmer la suppression de l'enregistrement.
Appuyez sur le bouton OK.
Il faut maintenant ajouter le nouvel employé.
Appuyez sur le bouton Nouvelle.
Entrez les données dans les cellules appropriées.
Appuyez sur la touche
Entrée.
Appuyez sur le bouton Fermer.
M. Cardinal n'est plus dans la liste et M. Sauvageau a été ajouté à la fin de
la liste de données. Comme vous le constatez, il est plus facile de gérer les
données en utilisant la grille qu'en cherchant sur la feuille de calcul.
Les fonctions de base de données
Une fois que vous avez compris les listes de données et les zones de
critères, vous pouvez ensuite accomplir des analyses avec les fonctions
disponibles d'Excel. Il y en a 13 en tout. Voici une courte description de
quelques-unes de ces fonctions.
| =BDSOMME(étendue de la liste de données;numéro
de la colonne à additionner;étendue de la zone de critères) |
Affiche le total d'un certain champ numérique selon
les critères demandés. |
| =BDMOYENNE(étendue de la liste de
données;numéro de la colonne à trouver la moyenne;étendue de la zone de
critères) |
Affiche la moyenne d'un certain champ numérique selon
les critères demandés. |
| =BDMAX(étendue de la liste de données;numéro de
la colonne à trouver la plus grande valeur;étendue de la zone de critères) |
Affiche la plus grande valeur d'un certain champ
numérique selon les critères demandés. |
| =BDMIN(étendue de la liste de données;numéro de
la colonne à trouver la plus petite valeur;étendue de la zone de critères) |
Affiche la plus petite valeur d'un certain champ
numérique selon les critères demandés. |
| =BDNB(étendue de la liste de données;numéro de
la colonne à trouver le nombre;étendue de la zone de critères) |
Affiche le nombre d'enregistrements numériques qui
répondent aux critères demandés. |
| =BDNBVAL(étendue de la liste de données;numéro
de la colonne à trouver le nombre;étendue de la zone de critères) |
Affiche le nombre d'enregistrements ayant un contenu
autre que des chiffres qui répondent aux critères demandés. |
| =BDECARTYPE(étendue de la liste de
données;numéro de la colonne à trouver l'écart type;étendue de la zone de
critères) |
Affiche l'écart type d'enregistrements numériques qui
répondent aux critères demandés. |
| =BDVAR(étendue de la liste de données;numéro de
la colonne à trouver la variance;étendue de la zone de critères) |
Affiche la variance d'enregistrements numériques qui
répondent aux critères demandés. |
Le prochain exercice consiste à vous démontrer comment utiliser ces fonctions
avec =BDSomme(). Il s'agit de savoir quelle est la masse salariale totale des
administrateurs et des ouvriers de catégorie 4. La liste de données, de
A1 à G16, et la zone de critères, de I1 à J3, ont
déjà été établies dans les exercices précédents.
Pour utiliser la fonction =BDSomme(), et la plupart des autres, vous avez
besoins de trois données ou paramètres: l'étendue de la liste de données, le nom
du champ à additionner et l'étendue de la zone de critères. Voici comment
l'écrire en utilisant l'assistant des fonctions.
Placez le pointeur sur la cellule I4.
De la barre d'outils,
appuyez sur le bouton  .
OU
Du menu Insertion,
sélectionnez l'option Fonction.
La première colonne décrit les catégories de fonctions couvertes par Excel.
La seconde colonne contient la liste des fonctions que vous pouvez utiliser.
De
la liste des catégories de fonctions, sélectionnez l'option Base de
données.
De la liste des fonctions, sélectionnez BDSOMME.
Appuyez sur
le bouton OK.
Une nouvelle fenêtre apparaît vous demandant les trois paramètres mentionnés
auparavant. Au bas de celle-ci, il y a une description des éléments que vous
devez entrer dans chacune des cases. La première case est pour la liste de
données.
Cliquez dans la case Base de données.
OU
Appuyez sur
le bouton à la droite de la case Base de données.
Sélectionnez l'étendu de A1 jusqu'à G16.
Appuyez sur
la touche Entrée ou le bouton selon le cas.
Le pointeur retourne à la fenêtre de description de la fonction
=BDSomme().
Cliquez sur la case Champ.
Remarquez que la description au bas de la fenêtre donne maintenant de
l'information pour la seconde case. Vous pouvez aussi appuyer sur le bouton pour
aller chercher l'information requise.
Cliquez sur la cellule F1.
Appuyez sur la touche
Entrée.
Pour le nom du champ, vous pouvez entrer le nom du champ entre guillemets (
"salaire" ) ou entrer le numéro de la colonne. Puisque le champ salaire est la
sixième colonne de la liste de données, vous pouvez aussi entrer le
chiffre 6.
Toutes les informations requises sont entrées. Vous avez même le résultat au
bas de la fenêtre.
Appuyez sur le bouton OK.
Le résultat est de 185 000 $. Si vous regardez le contenu de la barre de
formule, vous verrez que la formule ressemble à
=BDSOMME(A1:G16;F1;I1:J3). Elle fonctionnerait tout aussi bien sous cette
forme =BDSOMME(A1:G16;6;I1:J3) ou
=BDSOMME(A1:G16;"salaire";I1:J3). Il y a plusieurs façons de décrire le
champ à additionner. Essayez maintenant avec les autres fonctions de la
catégorie.
Analyse sur des regroupements
Les fonctions d'analyses peuvent vous sortir des données intéressantes.
Cependant, cela peut prendre du temps si vous voulez la même information pour
plusieurs regroupements de données. Par exemple, il vous faut plusieurs
fonctions pour connaître la masse salariale par catégorie d'employés. Excel vous
offre la possibilité d'afficher rapidement des sous totaux de plusieurs
fonctions ( somme, moyenne ... ) par regroupement.
Avant même de commencer des analyses, il faut premièrement trier les
enregistrements sur le champ dont vous voulez voir les sous totaux. Pour les
besoins de l'exercice, il faut trier sur le champ Catégorie.
Placez le pointeur sur l'un des enregistrements sous le champ
Catégorie.
En utilisant les boutons, trier en ordre
croissant le champ Catégorie.
Voici ce que devrait ressembler la liste de données si vous avez complété
toutes les opérations de cette page Web. Il est maintenant le temps de voir le
total des salaires par catégorie.
Placez le pointeur sur une cellule de la liste de données.
Du menu
Données, sélectionnez l'option Sous Totaux.
De
la première case, sélectionnez le champ Catégorie.
Pour la deuxième case,
sélectionnez que vous voulez la fonction Somme.
De la troisième case,
sélectionnez que vous voulez la somme du champ Salaire.
La fenêtre des sous totaux vous offre aussi la possibilité de remplacer les
sous totaux d'analyses précédentes. Vous pouvez aussi accumuler ces analyses
pour avoir plus d'informations. Par exemple, vous pouvez avoir la somme et la
moyenne en même temps sur la feuille de calcul. Il faut faire attention à la
fonction NBVal qui est censée donner le nombre d'enregistrements par
regroupement. Cependant, elle ne semble pas vouloir fonctionner correctement
avec d'autres fonctions.
Vous pouvez aussi demander d'avoir un saut de page après chaque regroupement.
Ceci est intéressant si vous songez à imprimer les résultats. Vous pouvez aussi
avoir à la fin de la liste de données une synthèse des fonctions que vous avez
demandée.
Appuyez sur le bouton OK.
Les options à la gauche de la feuille de calcul vous permettent de masquer (
- ) et de réafficher ( + ) les catégories. Vous pouvez ensuite
vous concentrer les éléments que vous jugez importants. Les boutons 1, 2 et 3
permettent de masquer et d'afficher la synthèse, les sous totaux et les
enregistrements. Appuyez sur ces boutons pour voir leurs effets.
Pour revenir à la présentation initiale:
Placez le pointeur sur une cellule de la liste de données.
Du menu
Données, sélectionnez l'option Sous Totaux.
Appuyez sur le bouton
Supprimer tout.
La liste réapparaît sans les sommes des catégories. Vous pouvez maintenant
pousser vos analyses un peu plus loin grâce à la liste de données et ses
options.
La validation
La validation est très pratique lorsque vous préparez un modèle pour d'autres
utilisateurs moins expérimentés. Vous pouvez placer des bornes pour l'entrée de
données afin de réduire les erreurs. La validation fournit aussi un message pour
guider les utilisateurs au moment de l'entrée de données. La prochaine partie
consiste en un petit exercice pour démontrer comment cette option peut être
pratique. L'exercice consiste à limiter l'entrée de données à des entiers entre
les valeurs 0 et 25.
Création de la validation
Sélectionnez la troisième feuille de calcul en cliquant sur l'onglet
Feuil3.
OU
Appuyez sur les touches Ctrl
et PgDn (Page Down) deux fois.
Placez le pointeur dans la cellule A1.
Du menu Données,
sélectionnez l'option Validation.
Les options pour une validation sont séparées en trois onglets. L'onglet
Option détermine les bornes et les conditions qui doivent être respectées.
L'onglet Message de saisie affiche est pour donner de l'information
supplémentaire à l'utilisateur sur les valeurs qui devraient se retrouver dans
la cellule validée. Malgré cela, l'utilisateur peut toujours entrer un chiffre
qui n'est pas permis. L'onglet Alerte d'erreur sert donc à rappeler
l'utilisateur quelles sont les valeurs permises.
Sélectionnez les options et entrez les chiffres dans les cases
appropriées.
Sélectionnez l'onglet Message de saisie.
Entrez le texte "Nombre d'employés" dans la case
Titre.
Entrez le texte suivant dans la case Message de saisie: "Vous
pouvez engager jusqu'à 25 employés dans cette unité".
Sélectionnez l'onglet
Alerte d'erreur.
Entrez dans la case Titre le texte suivant: "Nombre
incorrect".
Entrez le texte suivant dans la case Message d'erreur:
"Le chiffre que vous avez entré n'est pas entre 0 et 25 ou n'est pas un
entier".
Appuyez sur le bouton OK.
Placez le pointeur dans la cellule A1.
Les options pour la validation ont été choisies. Il faut maintenant
l'essayer. Le texte qui a été écrit dans la case message de saisie va apparaître
à côté donnant une indication à l'utilisateur.
Entrez le chiffre 45 dans la cellule A1.
Ce chiffre est à l'extérieur des bornes permises par la validation. Le
message d'erreur va donc apparaître et demander à l'utilisateur s'il veut entrer
une autre valeur ou annuler l'entrée d'information. Il faut aussi être capable
de retirer la validation lorsqu'elle devient inutile.
Placez le pointeur dans la cellule ayant la validation.
Du menu
Données, sélectionnez l'option Validation.
Appuyez sur le bouton
Effacer tout.
Liste déroulante
Il est parfois plus intéressant pour les utilisateurs de choisir parmi une
liste de choix prédéterminés. Il est possible de créer une liste déroulante avec
les valeurs de votre choix en utilisant la validation. Cependant, il faut
premièrement entrer les valeurs dans les cellules avant de pouvoir créer une
liste déroulante.
Entrez les chiffres dans les cellules appropriées.
Placez le pointeur sur
la cellule A1.
Il faut maintenant utiliser la validation pour créer une liste
déroulante.
Du
menu Données, sélectionnez l'option Validation.
Sélectionnez l'onglet
Options.
De
la case Autoriser, sélectionnez l'option Liste.
Pour la case Source,
sélectionnez les cellules A3 à A6.
Appuyez sur le bouton
OK.
La liste est prête. Il faut maintenant l'essayer.
Placez le pointeur sur la cellule A1.
Sélectionnez une valeur de
la liste déroulante.
Vous pouvez changer la valeur en tout temps et utiliser le contenu de cette
cellule dans des formules.
Copier la validation
Il est aussi possible de copier la validation que vous venez de créer sur
plusieurs cellules. Il suffit de copier et d'utiliser l'option de collage
spécial pour l'appliquer à l'endroit de votre choix. Voici les étapes à suivre
pour recopier une validation.
Placez le pointeur dans la cellule ayant la validation.
Du menu
Edition, sélectionnez l'option Copier.
OU
Appuyez sur
le bouton  .
OU
Appuyez sur les
touches Ctrl et C.
OU
Appuyez sur le bouton droit de
la souris.
Du menu contextuel, sélectionnez l'option Copier.
Sélectionnez les cellules dans lesquelles vous voulez recopier la
validation.
Du menu Edition, sélectionnez l'option Collage
spécial.
OU
Appuyez sur le bouton droit de
la souris.
Du menu contextuel, sélectionnez l'option Collage spécial.
De
la liste, sélectionnez l'option Validation.
Appuyez sur le bouton
OK.
La validation est maintenant recopiée à plusieurs cellules. Vous pouvez
ensuite apporter des modifications à celles-ci si cela est nécessaire.
| 
Cliquez ici pour voir les produits
